前言
最近,开发人员需要定期的删除表里一定时间以前的数据,SQL如下:
<code>mysql >delete
from
testtable WHERE biz_date <='2017-08-21 00:00:00'
AND status =2
limit500
\G/<code>
前段时间在优化的时候,我们已经在相应的查询条件上加上了索引,如下:
<code>KEY`idx_bizdate_st`
(`biz_date`
,`status`
)/<code>
但是实际执行的SQL依然非常慢,为什么呢,我们来一步步分析验证下。
分析
表上的字段既然都有索引,那么按照之前的文章分析,是两个字段都可以走上索引的。
既然能够利用索引,表的总大小也就是200M左右,那么为什么形成了慢查呢?
我们查看执行计划,去掉limit 后,发现他选择了走全表扫描。
<code>mysql > descselect
* from testtable WHERE biz_date <='2017-08-21 00:00:00'
; + | id | select_type |table
|type
| possible_keys | key | key_len | ref | rows | Extra | + |1
| SIMPLE | testtable | ALL | idx_bizdate_st | NULL | NULL | NULL |980626
| Using where | +1
rowin
set (0.00
sec) mysql > descselect
* from testtable WHERE biz_date <='2017-08-21 00:00:00'
and
status
=2
; + | id | select_type |table
|type
| possible_keys | key | key_len | ref | rows | Extra | + |1
| SIMPLE | testtable | ALL | idx_bizdate_st | NULL | NULL | NULL |980632
| Using where | +1
rowin
set (0.00
sec) mysql > descselect
* from testtable WHERE biz_date <='2017-08-21 00:00:00'
and
status
=2
limit100
; + | id | select_type |table
|type
| possible_keys | key | key_len | ref | rows | Extra | + |1
| SIMPLE | testtable | range | idx_bizdate_st | idx_bizdate_st |6
| NULL |490319
| Using index condition | +1
rowin
set (0.00
sec) mysql >select
count(*) from testtable WHERE biz_date <='2017-08-21 00:00:00'
and
status
=2
; + | count(*) | + |0
| +1
rowin
set (0.34
sec) mysql >select
count(*) from testtable WHERE biz_date <='2017-08-21 00:00:00'
; + | count(*) | + |970183
| +1
rowin
set (0.33
sec) mysql >select
count(*) from testtable; + | count(*) | + |991421
| +1
rowin
set (0.19
sec) mysql >select
distinct biz_status from testtable; + | biz_status | + |1
| |2
| |4
| +/<code>
通过以上查询,我们可以发现如下几点问题:
- 通过 biz_date 预估出来的行数 和 biz_date + status=2 预估出来的行数几乎一样,为98w。
- 实际查询表 biz_date + status=2 一条记录都没有。
- 整表数据量达到了99万,MySQL发现通过索引扫描需要98w行(预估)
因此,MySQL通过统计信息预估的时候,发现需要扫描的索引行数几乎占到了整个表,放弃了使用索引,选择了走全表扫描。
那是不是他的统计信息有问题呢?我们重新收集了下表统计信息,发现执行计划的预估行数还是一样,猜测只能根据组合索引的第一个字段进行预估(待确定)。
那我们试下直接强制让他走索引呢?
<code>mysql >select
*from
testtable WHERE biz_date <='2017-08-21 00:00:00'
and status =2
;Empty
set
(0.79
sec) mysql >select
*from
testtable forceindex
(idx_bizdate_st
) WHERE biz_date <='2017-08-21 00:00:00'
and status =2
;Empty
set
(0.16
sec )/<code>
我们发现,强制指定索引后,查询耗时和没有强制索引比较,的确执行速度快了很多,因为没有强制索引是全表扫描嘛!但是!依然非常慢!
那么还有什么办法去优化这个本来应该很快的查询呢?
大家应该都听说过要选择性好的字段放在组合索引的最前面?
选择性好的索引在前面并不是对所有的场景都通用的,这个场景可以将status放前面,sql速度会更快。
那,能不能让他不要扫描索引的那么多范围呢?之前的索引模型中也说过,MySQL是通过索引去确定一个扫描范围,如果能够定位到尽可能小的范围,那是不是速度上会快很多呢?
并且业务逻辑上是定期删除一定日期之前的数据。所以逻辑上来说,每次删除都是只删除一天的数据,直接让SQL扫描一天的范围。那么我们就可以改写SQL啦!
<code>mysql > select * from testtable WHERE biz_date >='2017-08-20 00:00:00'
and
biz_date <='2017-08-21 00:00:00'
and
status =2
; Empty set (0
.00
sec) mysql > desc select * from testtable WHERE biz_date >='2017-08-20 00:00:00'
and
biz_date <='2017-08-21 00:00:00'
and
status =2
; +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+| id |
select_type| table |
type| possible_keys |
key| key_len |
ref| rows |
Extra| +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ |
1
| SIMPLE |
testtable| range |
idx_bizdate_st| idx_bizdate_st |
6
| NULL |
789
| Using index condition |
+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+1
rowin
set (0
.00
sec) -- rows降低了很多,乖乖的走了索引 mysql > desc select * from testtable WHERE biz_date >='2017-08-20 00:00:00'
and
biz_date <='2017-08-21 00:00:00'
; +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+| id |
select_type| table |
type| possible_keys |
key| key_len |
ref| rows |
Extra| +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ |
1
| SIMPLE |
testtable| range |
idx_bizdate_st| idx_bizdate_st |
5
| NULL |
1318
| Using index condition |
+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+1
rowin
set (0
.00
sec) -- 即使没有status,也是肯定走索引啦/<code>
小结
这个问题,我原本打算用hint,强制让他走索引,但是实际上强制走索引的执行时间并不能带来满意的效果。结合业务逻辑,来优化SQL,是最好的方式,也是终极法宝,一定要好好利用。不了解业务的DBA,不是一个好DBA...