[[209138]]
一、前言
开发需要定期的删除表里一定时间以前的数据，SQL 如下
mysql>deletefromtesttableWHEREbiz_date<='2017-08-2100:00:00'ANDstatus=2limit500\G
mysql > delete from testtable WHERE biz_date <= ‘2017-08-21 00:00:00’ AND status = 2 limit 500\G前段时间在优化的时候，已经在相应的查询条件上加上了索引
KEY`idx_bizdate_st`(`biz_date`,`status`)
但是实际执行的 SQL 依然非常慢，为什么呢，我们来一步步分析验证下
二、分析
表上的字段既然都有索引，那么按照之前的文章分析，是两个字段都可以走上索引的。如果有疑问，请参考文章 10 分钟让你明白 MySQL 是如何利用索引的
既然能够利用索引，表的总大小也就是 200M 左右，那么为什么形成了慢查呢?
我们查看执行计划，去掉 limit 后，发现他选择了走全表扫描。
mysql>descselect*fromtesttableWHEREbiz_date<='2017-08-2100: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|Usingwhere|+----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+1rowinset(0.00sec)--只查询biz_date--关键点：rows:980626;type:ALLmysql>descselect*fromtesttableWHEREbiz_date<='2017-08-2100:00:00'andstatus=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|Usingwhere|+----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+1rowinset(0.00sec)--查询biz_date+status--关键点：rows:980632;type:ALLmysql>descselect*fromtesttableWHEREbiz_date<='2017-08-2100:00:00'andstatus=2limit100;+----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+|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|Usingindexcondition|+----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+1rowinset(0.00sec)--查询biz_date+status+limit--关键点：rows:490319;mysql>selectcount(*)fromtesttableWHEREbiz_date<='2017-08-2100:00:00'andstatus=2;+----------+|count(*)|+----------+| 0|+----------+1rowinset(0.34sec)mysql>selectcount(*)fromtesttableWHEREbiz_date<='2017-08-2100:00:00';+----------+|count(*)|+----------+| 970183|+----------+1rowinset(0.33sec)mysql>selectcount(*)fromtesttable;+----------+|count(*)|+----------+| 991421|+----------+1rowinset(0.19sec)mysql>selectdistinctbiz_statusfromwhwtestbuffer;+------------+|biz_status|+------------+| 1|| 2|| 4|+------------+
通过以上查询，我们可以发现如下几点问题：
通过 biz_date 预估出来的行数 和 biz_date + status=2 预估出来的行数几乎一样，为 98w。
实际查询表 biz_date + status=2 一条记录都没有。
整表数据量达到了99万，MySQL 发现通过索引扫描需要98w行(预估)
因此，MySQL 通过统计信息预估的时候，发现需要扫描的索引行数几乎占到了整个表，放弃了使用索引，选择了走全表扫描。
那是不是他的统计信息有问题呢?我们重新收集了下表统计信息，发现执行计划的预估行数还是一样，猜测只能根据组合索引的***个字段进行预估(待确定)
那我们试下直接强制让他走索引呢?
mysql>select*fromtesttableWHEREbiz_date<='2017-08-2100:00:00'andstatus=2;Emptyset(0.79sec)mysql>select*fromtesttableforceindex(idx_bizdate_st)WHEREbiz_date<='2017-08-2100:00:00'andstatus=2;Emptyset(0.16sec)
我们发现，强制指定索引后，查询耗时和没有强制索引比较，的确执行速度快了很多，因为没有强制索引是全表扫描嘛!但是!依然非常慢!
那么还有什么办法去优化这个本来应该很快的查询呢?
大家应该都听说过要选择性好的字段放在组合索引的最前面?
是的，相对于 status 字段，biz_date 的选择性更加不错，那组合索引本身已经没有好调整了
那，能不能让他不要扫描索引的那么多范围呢?之前的索引模型中也说过，MySQL 是通过索引去确定一个扫描范围，如果能够定位到尽可能小的范围，那是不是速度上会快很多呢?
并且业务逻辑上是定期删除一定日期之前的数据。所以逻辑上来说，每次删除都是只删除一天的数据，直接让 SQL 扫描一天的范围。那么我们就可以改写 SQL 啦!
mysql>select*fromtesttableWHEREbiz_date>='2017-08-2000:00:00'andbiz_date<='2017-08-2100:00:00'andstatus=;Emptyset(0.00sec)mysql>descselect*fromtesttableWHEREbiz_date>='2017-08-2000:00:00'andbiz_date<=2017-08-2100:00:00'andstatus=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|Usingindexcondition|+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+1rowinset(0.00sec)--rows降低了很多，乖乖的走了索引mysql>descselect*fromtesttableWHEREbiz_date>='2017-08-2000:00:00'andbiz_date<='2017-08-2100: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|Usingindexcondition|+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+1rowinset(0.00sec)--即使没有status，也是肯定走索引啦
三、小结
这个问题，我原本打算用 hint，强制让他走索引，但是实际上强制走索引的执行时间并不能带来满意的效果。结合业务逻辑，来优化 SQL，是***的方式，也是***法宝，一定要好好利用。不了解业务的 DBA，不是一个好 DBA… 继续去补业务知识去了。