编辑切换为居中
添加图片注释,不超过 140 字(可选)
前言 最近,开发人员需要定期的删除表里一定时间以前的数据,SQL如下: 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依然非常慢,为什么呢,我们来一步步分析验证下。 分析 表上的字段既然都有索引,那么按照之前的文章分析,是两个字段都可以走上索引的。 既然能够利用索引,表的总大小也就是200M左右,那么为什么形成了慢查呢? 我们查看执行计划,去掉limit 后,发现他选择了走全表扫描。 mysql > desc select * 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 row in set (0.00 sec) -- 只查询biz_date -- 关键点:rows:980626;type:ALL mysql > desc select * 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 row in set (0.00 sec) -- 查询biz_date + status -- 关键点:rows:980632;type:ALL mysql > desc select * from testtable WHERE biz_date <= '2017-08-21 00:00:00' and status = 2 limit 100; +----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+ | 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 row in set (0.00 sec) -- 查询biz_date + status+ limit -- 关键点:rows:490319; mysql > select count(*) from testtable WHERE biz_date <= '2017-08-21 00:00:00' and status = 2; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.34 sec) mysql > select count(*) from testtable WHERE biz_date <= '2017-08-21 00:00:00'; +----------+ | count(*) | +----------+ | 970183 | +----------+ 1 row in set (0.33 sec) mysql > select count(*) from testtable; +----------+ | count(*) | +----------+ | 991421 | +----------+ 1 row in set (0.19 sec) mysql > select distinct biz_status from testtable; +------------+ | biz_status | +------------+ | 1 | | 2 | | 4 | +------------+ 通过以上查询,我们可以发现如下几点问题:
-
通过 biz_date 预估出来的行数 和 biz_date + status=2 预估出来的行数几乎一样,为98w。
-
实际查询表 biz_date + status=2 一条记录都没有。
-
整表数据量达到了99万,MySQL发现通过索引扫描需要98w行(预估)
因此,MySQL通过统计信息预估的时候,发现需要扫描的索引行数几乎占到了整个表,放弃了使用索引,选择了走全表扫描。 那是不是他的统计信息有问题呢?我们重新收集了下表统计信息,发现执行计划的预估行数还是一样,猜测只能根据组合索引的第一个字段进行预估(待确定)。 那我们试下直接强制让他走索引呢? 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 force index(idx_bizdate_st) WHERE biz_date <= '2017-08-21 00:00:00' and status = 2; Empty set (0.16 sec) 我们发现,强制指定索引后,查询耗时和没有强制索引比较,的确执行速度快了很多,因为没有强制索引是全表扫描嘛!但是!依然非常慢! 那么还有什么办法去优化这个本来应该很快的查询呢? 大家应该都听说过要选择性好的字段放在组合索引的最前面? 选择性好的索引在前面并不是对所有的场景都通用的,这个场景可以将status放前面,sql速度会更快。 那,能不能让他不要扫描索引的那么多范围呢?之前的索引模型中也说过,MySQL是通过索引去确定一个扫描范围,如果能够定位到尽可能小的范围,那是不是速度上会快很多呢? 并且业务逻辑上是定期删除一定日期之前的数据。所以逻辑上来说,每次删除都是只删除一天的数据,直接让SQL扫描一天的范围。那么我们就可以改写SQL啦! 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 row in 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 row in set (0.00 sec) -- 即使没有status,也是肯定走索引啦 小结 这个问题,我原本打算用hint,强制让他走索引,但是实际上强制走索引的执行时间并不能带来满意的效果。结合业务逻辑,来优化SQL,是最好的方式,也是终极法宝,一定要好好利用。不了解业务的DBA,不是一个好DBA...
资源获取:
大家 点赞、收藏、关注、评论啦 、 查看👇🏻👇🏻👇🏻 微信公众号获取联系方式👇🏻👇🏻👇🏻
精彩专栏推荐订阅:在 下方专栏👇🏻👇🏻👇🏻👇🏻
每天学四小时:Java+Spring+JVM+分布式高并发,架构师指日可待