前言

又和大家见面了！又两周过来了，我的云笔记里又多了几篇写了一半的文章草稿。有的是因为品质没有达到预期还筹备再加点内容，有的则齐全是一个灵感而已，内容齐全木有。艳羡很多大佬们，一周能产出五六篇文章，给我两个肝我都不够。好了，不多说废话了...

最近在线上环境遇到了一次SQL慢查问引发的数据库故障，影响线上业务。通过排查后，确定起因是SQL在执行时，MySQL优化器抉择了谬误的索引（不应该说是“谬误”，而是抉择了理论执行耗时更长的索引）。在排查过程中，查阅了许多材料，也学习了下MySQL优化器抉择索引的基本准则，在本文中进行解决问题思路的分享。自己MySQL理解深度无限，如果谬误欢送感性探讨和斧正。

在这次事变中也能充沛看出深刻理解MySQL运行原理的重要性，这是遇到问题时是否独立解决问题的要害。 试想一个月黑风高的夜晚，公司线上忽然挂了，而你的共事们都不在线，就你一个人有条件解决问题，这时候如果被工程师的基本功把你卡住了，就问你尴不难堪...

本文的次要内容：

  • 故障形容
  • 问题起因排查
  • MySQL索引抉择原理
  • 解决方案
  • 思考与总结
请大家多多反对我的原创技术公众号：后端技术漫谈

注释

故障形容

在7月24日11点线上某数据库忽然收到大量告警，慢查问数超标，并且引发了连接数暴增，导致数据库响应迟缓，影响业务。看图表慢查问在顶峰达到了每分钟14w次，在平时失常状况下慢查问数仅在两位数以下，如下图：

连忙查看慢SQL记录，发现都是同一类语句导致的慢查问(隐衷数据例如表名，我曾经隐去)：

select  *from  sample_tablewhere    1 = 1    and (city_id = 565)    and (type = 13)order by  id desclimit  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为何抉择这个索引的起因后，咱们就能够依据下面的思路来列举出解决办法了。

次要有两个大方向：

  1. 强制指定索引
  2. 干预优化器抉择

强制抉择索引：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优化器 limit影响的case：

https://www.cnblogs.com/xpchi...

mysql中走与不走索引的状况会集(待全量试验)：

https://www.cnblogs.com/gxyan...

MySQL ORDER BY主键id加LIMIT限度走错索引：

https://www.jianshu.com/p/caf...

【业务学习】对于MySQL order by limit 走错索引的探讨：

https://segmentfault.com/a/11...

MySQL为什么有时候会选错索引？：

https://www.cnblogs.com/a-php...

关注我

我是一名后端开发工程师。次要关注后端开发，数据安全，爬虫，物联网，边缘计算等方向，欢送交换。

各大平台都能够找到我

  • 微信公众号：后端技术漫谈
  • Github：@qqxx6661
  • CSDN：@蛮三刀把刀
  • 知乎：@后端技术漫谈
  • 简书：@蛮三刀把刀
  • 掘金：@蛮三刀把刀
  • 腾讯云+社区：@后端技术漫谈

原创文章次要内容

  • 后端开发
  • Java面试
  • 设计模式/数据结构/算法题解
  • 爬虫/边缘计算/物联网
  • 读书笔记/逸闻趣事/程序人生

集体公众号：后端技术漫谈

如果文章对你有帮忙，无妨珍藏，转发，在看起来~