由于MySQL选错索指导致的线上慢查询变乱
mysql视频教程栏目为大家介绍MySQL选错索指导致的线上慢查询变乱
前言
又和大家见面了！又两周过去了，我的云笔记里又多了几篇写了一半的文章草稿。有的是由于质量没有到达预测还预备再加点内容，有的则完全是一个灵感罢了，内容完全木有。恋慕许多大佬们，一周能产出五六篇文章，给我两个肝我都不足。好了，不多说废话了...
比来在线上环境碰到了一次SQL慢查询激发的数据库故障，影响线上业务。经过排查后，肯定缘由是SQL在施行时，MySQL优化器选中了错误的索引（不该该说是“错误”，而是选中了实际施行耗时更长的索引）。在排查历程中，查阅了很多材料，也学习了下MySQL优化器选中索引的根本原则，在本文中停止解决问题思绪的分享。本人MySQL理解深度有限，假如错误欢迎理性计议和指正。
在这次变乱中也能充分看出深入理解MySQL运转道理的重要性，这是碰到问题时能否独立解决问题的关键。 试想一个月黑风高的夜晚，公司线上忽然挂了，而你的同事们都不在线，就你一个人有前提解决问题，这时候假如被工程师的根本功把你卡住了，就问你尴不为难...
本文的主要内容：
- 故障描写
- 问题缘由排查
- MySQL索引选中道理
- 解决方案
- 思索与总结
正文
故障描写
在7月24日11点线上某数据库忽然收到大量告警，慢查询数超标，并且激发了连接数暴增，致使数据库响应迟缓，影响业务。看图表慢查询在顶峰到达了每分钟14w次，在平常正常状况下慢查询数仅在两位数以下，如下图：
抓紧查看慢SQL记载，发明都是统一类语句致使的慢查询(隐私数据例如表名，我已经隐去)：
select
*
from
sample_table
where
1 = 1
and (city_id = 565)
and (type = 13)
order by
id desc
limit
0, 1复制代码看起来语句很简便，没什么特殊的。但是每个施行的查询时间到达了惊人的44s。
几乎骇人听闻，这已经不是“慢”能形容的了...
接下来查看表数据信息，如下图：
可以看到表数据量较大，预估行数在83683240，也就是8000w摆布，千万数据量的表。
大致状况就是这样，下面进入排查问题的环节。
问题缘由排查
第一当然要嫌疑会不会该语句没走索引，查看建表DML中的索引：
KEY `idx_1` (`city_id`,`type`,`rank`), KEY `idx_log_dt_city_id_rank` (`log_dt`,`city_id`,`rank`), KEY `idx_city_id_type` (`city_id`,`type`)复制代码
请忽略idx_1和idx_city_id_type两个索引的反复，这都是历史遗留问题了。
可以看到是有idx_city_id_type和idx_1索引的，我们的查询前提是city_id和type，这两个索引都是能走到的。
但是，我们的查询前提真的只要思考city_id和type吗？（机灵的小伙伴应当留意到问题所在了，先往下讲，留给大家思索）
既然有索引，接下来就该看该语句实际有没有走到索引了，MySQL供给了Explain可以剖析SQL语句。Explain 用来剖析 SELECT 查询语句。
Explain比力重要的字段有：
- select_type : 查询类型，有简便查询、结合查询、子查询等
- key : 使用的索引
- rows : 估计需要扫描的行数
更多具体Explain介绍可以参照 ：MySQL 机能优化神器 Explain 使用剖析
我们使用Explain剖析该语句：
select * from sample_table where city_id = 565 and type = 13 order by id desc limit 0,1复制代码
得到结果：
可以看出，虽然possiblekey有我们的索引，但是最后走了主键索引。而表是千万级别，并且该查询前提最后实际是返回的空数据，也就是MySQL在主键索引上实际检索时间很长，致使了慢查询。
我们可以使用force index(idx_city_id_type)让该语句选中我们设定的结合索引：
select * from sample_table force index(idx_city_id_type) where ( ( (1 = 1) and (city_id = 565) ) and (type = 13) ) order by id desc limit 0, 1复制代码
这次明显施行的飞速，剖析语句：
实际施行时间0.00175714s，走了结合索引后，不再是慢查询了。
问题寻到了，总结下来就是：MySQL优化器认为在limit 1的状况下，走主键索引能够更快的寻到那一条数据，并且假如走结合索引需要扫描索引后停止排序，而主键索引天生有序，所以优化器综合思考，走了主键索引。实际上，MySQL遍历了8000w条数据也没寻到阿谁天选之人（相符前提的数据），所以白费了许多时间。
MySQL索引选中道理
优化器索引选中的原则
MySQL一条语句的施行流程大致如下图，而查询优化器则是选中索引的地方：
援用参照 文献一段说明：
第一要知道，选中索引是MySQL优化器的工作。
而优化器选中索引的目的，是寻到一个最优的施行方案，并用最小的代价去施行语句。在数据库里面，扫描行数是影响施行代价的因素之一。扫描的行数越少，意味着拜访磁盘数据的次数越少，耗损的CPU资源越少。
当然，扫描行数并不是独一的推断标准，优化器还会结合可否使用暂时表、可否排序等因素停止综合推断。
总结下来，优化器选中有很多思考的因素：扫描行数、可否使用暂时表、可否排序等等
我们回过头看方才的两个explain截图：
走了主键索引的查询语句，rows预估行数1833，而强迫走结合索引行数是45640，并且Extra信息中，显示需要Using filesort停止额外的排序。所以在不增强制索引的状况下，优化器选中了主键索引，由于它觉得主键索引扫描行数少，并且不需要额外的排序操纵，主键索引天生有序。
rows是如何预估出来的
同学们就要问了，为什么rows只要1833，明明实际扫描了整个主键索引啊,行数远远不止几千行。实际上explain的rows是MySQL预估的行数，是按照查询前提、索引和limit综合思考出来的预估行数。
MySQL是怎样得到索引的基数的呢？这里，我给你简便介绍一下MySQL采样统计的办法。 为什么要采样统计呢？由于把整张表取出来一行行统计，虽然可以得到准确的结果，但是代价太高了，所以只能选中“采样统计”。 采样统计的时候，InnoDB默许会选中N个数据页，统计这些页面上的不一样值，得到一个均匀值，然后乘以这个索引的页面数，就得到了这个索引的基数。 而数据表是会连续更新的，索引统计信息也不会牢固不变。所以，当变动的数据行数超越1/M的时候，会主动触发从新做一次索引统计。 在MySQL中，有两种储备索引统计的方式，可以通过设定参数innodb_stats_persistent的值来选中： 设定为on的时候，表示统计信息会耐久化储备。这时，默许的N是20，M是10。 设定为off的时候，表示统计信息只储备在内存中。这时，默许的N是8，M是16。 由于是采样统计，所以不管N是20还是8，这个基数都是很容易不准的。复制代码
我们可以使用analyze table t 命令，可以用来从新统计索引信息。但是这条命令生产环境需要联络DBA，所以我就不做实验了,大家可以自行实验。
索引要思考 order by 的字段
为什么这么说？由于假如我这个表中的索引是city_id,type和id的结合索引，那优化器就会走这个结合索引，由于索引已经做好了排序。
更换limit大小能解决问题？
把limit数目调大会影响预估行数rows，进而影响优化器索引的选中吗？
答案是会。
我们施行limit 10
select * from sample_table where city_id = 565 and type = 13 order by id desc limit 0,10复制代码
图中rows变为了18211，增长了10倍。假如使用limit 100，会发生什么？
优化器选中了结合索引。初步估量是rows还会翻倍，所以优化器舍弃了主键索引。甘愿用结合索引后排序，也不肯意用主键索引了。
为什么忽然显现非常慢查询
问：这个查询语句已经在线上不乱运转了非常长的时间，为什么这次忽然显现了慢查询？
答：之前的语句查询前提返回结果都不为空，limit1很快就能寻到那条数据，返回结果。而这次代码中查询前提实际结果为空，致使了扫描了全部的主键索引。
解决方案
知道了MySQL为什么选中这个索引的缘由后，我们就可以按照上面的思绪来列举出解决方法了。
主要有两个大标的目的：
- 强迫指定索引
- 干预优化器选中
强迫选中索引：force index
就像上面我最开端的操纵那样，我们直接使用force index，让语句走我们想要走的索引。
select * from sample_table force index(idx_city_id_type) where ( ( (1 = 1) and (city_id = 565) ) and (type = 13) ) order by id desc limit 0, 1复制代码
这样做的长处是见效快，问题立刻就能解决。
缺陷也很明显：
- 高耦合，这种语句写在代码里，会变得难以保护，假如索引名转变了，或者没有这个索引了，代码就要重复修改。属于硬编码。
- 许多代码用框架封装了SQL，
force index()并不容易加进去。
我们换一种方法，我们去指导优化器选中结合索引。
干预优化器选中：增大limit
通过增大limit，我们可以让预估扫描行数快速增添，比方改成下面的limit 0, 1000
SELECT * FROM sample_table where city_id = 565 and type = 13 order by id desc LIMIT 0,1000复制代码
这样就会走上结合索引，然后排序，但是这样强行增长limit，其实总有种面向黑盒调参的感受。我们还有更美丽的解决方案吗？
干预优化器选中：增添包括order by id字段的结合索引
我们这句慢查询使用的是order by id，但是我们却没有在结合索引中参加id字段，致使了优化器认为结合索引后还要排序，干脆就不太想走这个结合索引了。
我们可以创建city_id,type和id的结合索引，来解决这个问题。
这样也有必然的弊端，比方我这个表到了8000w数据，创立索引非常耗时，并且平常索引就有3.4个g，假如无穷制的用索引解决问题，大概会带来新的问题。表中的索引不宜过多。
干预优化器选中：写成子查询
还有什么方法？我们可以用子查询，在子查询里先走city_id和type的结合索引，得到结果集后在limit1选出第一条。
但是子查询使用有风险，一版DBA也不倡议使用子查询，会倡议大家在代码逻辑中完成复杂的查询。当然我们这句并不复杂啦~
Select * From sample_table Where id in (Select id From `newhome_db`.`af_hot_price_region` where (city_id = 565 and type = 13)) limit 0, 1复制代码
还有许多解决方法...
SQL优化是个很大的工程，我们还有非常多的方法能够解决这句慢查扣问题，这里就不一一展开了。留给大家做为思索题了。
总结
本文带大家回忆了一次MySQL优化器选错索指导致的线上慢查询变乱，可以看出MySQL优化器关于索引的选中并不单单依托某一个标准，而是一个综合选中的结果。我本人也对这方面理解不深入，还需要很多学习，夺取能够好好的做一个索引选中的总结（挖坑）。不说了，拿起巨厚的《高机能MySQL》,开端...
压住我的泡面...
最后做个文章总结：
- 该慢查询语句中使用order by id致使优化器在主键索引和city_id和type的结合索引中有所取舍，终究致使选中了更慢的索引。
- 可以通过强迫指定索引，创立包括id的结合索引，增大limit等方式解决问题。
- 平常开发时，特别是关于特大数据量的表，要留意SQL语句的标准和索引的创立，幸免变乱的发生。
相关免费学习引荐：mysql视频教程
以上就是由于MySQL选错索指导致的线上慢查询变乱的具体内容，更多请关注百分百源码网其它相关文章！
