前言

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

最近在线上环境遇到了一次 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 为何抉择这个索引的起因后，咱们就能够依据下面的思路来列举出解决办法了。

次要有两个大方向：

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 面试
• 设计模式 / 数据结构 / 算法题解
• 爬虫 / 边缘计算 / 物联网
• 读书笔记 / 逸闻趣事 / 程序人生

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

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