光环大数据分享MySQL 慢查分析与解决
光环大数据（大数据培训班的专业机构）， 一、前言开发需要定期的删除表里一定时间以前的数据，SQL 如下mysql delete from testtable WHERE biz_date = '2017-08-21 00:00:00' AND status = 2 limit 500/G前段时间在优化的时候，已经在相应的查询条件上加上 ...
SQL MySQL
一、前言开发需要定期的删除表里一定时间以前的数据，SQL 如下
前段时间在优化的时候，已经在相应的查询条件上加上了索引
但是实际执行的 SQL 依然非常慢，为什么呢，我们来一步步分析验证下
二、分析表上的字段既然都有索引，那么按照之前的文章分析，是两个字段都可以走上索引的。如果有疑问，请参考文章 10 分钟让你明白 MySQL 是如何利用索引的
既然能够利用索引，表的总大小也就是 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 whwtestbuffer;+------------+| 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 字段，biz_date 的选择性更加不错，那组合索引本身已经没有好调整了
那，能不能让他不要扫描索引的那么多范围呢？之前的索引模型中也说过，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... 继续去补业务知识去了。
大数据时代，大数据培训机构，就选光环大数据！
原创文章，转载请注明出处：光环大数据官网。
大数据培训、人工智能培训、Python培训、大数据培训机构、大数据培训班、数据分析培训、大数据可视化培训，就选光环大数据！光环大数据，聘请专业的大数据领域知名讲师，确保教学的整体质量与教学水准。讲师团及时掌握时代潮流技术，将前沿技能融入教学中，确保学生所学知识顺应时代所需。通过深入浅出、通俗易懂的教学方式，指导学生更快的掌握技能知识，成就上万个高薪就业学子。 更多问题咨询，欢迎点击------>>>>在线客服！
