前言我又遇见你了！又是两周过去了，我的云笔记里又多了几篇写到一半的文章草稿。有的因为质量没有达到预期而准备添加更多内容，有的则是纯粹的灵感，根本没有内容。羡慕很多一周能出五六篇文章的大佬，连两肝都给不了。好了，废话不多说了。。。最近线上环境遇到了SQL查询慢导致的数据库故障，影响了线上业务。经过排查，确定原因是“执行SQL时，MySQL优化器选择了错误的索引（不应该说是‘错误’，而是实际执行时间更长的索引）”。调研过程中查阅了很多资料，也学习了MySQL优化器选择索引的基本原理，分享本文的解题思路。我对MySQL的了解是有限的。如有错误，欢迎理性讨论和指正。“在这次事故中，我们也充分看出了深入理解MySQL运行原理的重要性，这是遇到问题时能够独立解决问题的关键。”想象一个风雨交加的夜晚，公司的线路突然挂了，而你的同事都不在线，只有你一个人具备解决问题??的条件。这时候你要是被工程师的基本功卡住了，我就问你是不是不好意思……》本文主要内容：《故障描述》故障原因排查MySQL索引选择原则解决思路及摘要文字故障描述7月24日11:00，某在线数据库突然收到大量告警，慢查询数超标，导致连接数激增，导致数据库响应缓慢数据库。影响生意。看图表，慢查询在高峰时达到了每分钟14万次，正常情况下慢查询的次数只有两位数以下，如下图所示：快速查看慢SQL记录，发现是同类型语句导致的所有慢查询（私有数据比如表名，我已经隐藏了）：select*fromsample_tablewhere1=1and(city_id=565)and(type=13)orderbyiddesclimit0,1好像是语句很简单，没什么特别的。但是每次执行的查询时间高达44秒。简直耸人听闻，这已经超出了“慢”的形容了……接下来查看表数据信息，如下图：可以看到表数据量很大，估计行数为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*fromsample_tablewherecity_id=565andtype=13orderbyiddesclimit0,1得到结果：可以看出possiblekey虽然有我们的索引，但是主键索引最终没有了。而且表是千万级的，“而且查询条件最后返回的其实是空数据”，也就是MySQL在主键索引上的实际检索时间很长，导致查询慢。我们可以使用forceindex(idx_city_id_type)让这个语句选择我们设置的联合索引：select*fromsample_tableforceindex(idx_city_id_type)where(((1=1)and(city_id=565))and(type=13))orderbyiddesclimit0,1This这一次，执行速度明显很快。分析语句：实际执行时间为0.00175714s。去掉联合索引后，就不再是慢查询了。问题找到了，总结为：“MySQL优化器认为，在limit1的情况下，使用主键索引可以更快的找到数据，如果使用联合索引，则需要扫描索引并排序，而主键索引本身就是有序的，所以优化器综合考虑了主键索引，其实MySQL遍历了8000w条数据，也没有找到被选中的人（符合条件的数据），所以很多浪费时间。”MySQL索引选择原理optimizerindexSelectioncriteriaMySQL中一条语句的执行流程大致如下图所示，“查询优化器”就是选择索引的地方：引用一个参考解释：首先，选择索引是MySQL优化器的工作。优化器选择索引的目的是找到一个最优的执行计划，执行代价最小的语句。在数据库中，扫描行数是影响执行成本的因素之一。更少的扫描行意味着更少的磁盘数据访问和更少的CPU资源消耗。“当然，扫描行数并不是判断的唯一标准，优化器还会根据是否使用临时表、是否排序等因素综合判断。”综上所述，优化器选择考虑的因素很多：“扫描行数、是否使用临时表、是否排序等”。回头看看刚才的两个explain截图：去掉“主键索引”的查询语句，rows中的预估行数为1833，强制“联合索引”中的行数为45640，以及Extra信息中，Usingfilesort需要额外的排序，因此，在没有强制索引的情况下，“优化器选择主键索引是因为它认为主键索引扫描的行少，不需要额外的排序操作。主键索引本来就是有序的。”行是怎么估算的？同学们就要问了，为什么只有1833行，明明其实是扫描了整个主键索引，行数远不止几行thousand.其实explain中的rows是MySQL中“预估”的行数，“综合考虑查询条件、索引、limits的预估行数”，mysql如何获取索引的基数?在这里，给大家简单介绍一下MySQL抽样统计的方法，为什么要抽样统计呢，因为把整张表拿出来逐行统计，虽然可以得到准确的结果，但是成本太高了，所以我们只能选择“抽样统计”，在抽样统计的时候，InnoDB会默认选择N个数据页，统计这些页上的不同值，取一个平均值，然后乘以这个页数获取t的基数的索引他的指数数据表会不断更新，索引统计不会固定不变。因此，当变化的数据行数超过1/M时，会自动触发新的索引统计。在MySQL中，有两种存储索引统计信息的方式，可以通过设置参数innodb_stats_persistent的值来选择：当设置为on时，表示统计信息将被持久化存储。此时默认N为20，M为10。设置为off时，表示统计信息只存储在内存中。此时默认N为8，M为16。由于是抽样统计，无论N是20还是8，这个基数都容易不准确。我们可以使用analyzetablet命令，它可以用来重新统计索引信息。但是这个命令的生产环境需要联系DBA，所以我就不做实验了，大家可以自己实验。索引要考虑orderby的字段。你为什么这么说？因为如果我表中的索引是city_id,type,id的联合索引，优化器会使用这个联合索引，因为索引已经排序了。改变limitsize能解决问题吗？调整限制会不会极大地影响估计的行数，从而影响优化器索引的选择？答案是肯定的。我们执行limit10select*fromsample_tablewherecity_id=565andtype=13orderbyiddesclimit0，图10的行变成18211，增加了10倍。如果使用100的限制会怎样？优化器选择联合索引。初步估计行数会翻倍，所以优化器放弃了主键索引。我宁愿使用联合索引来排序，也不想使用主键索引。为什么突然出现异常慢的查询？Q：这条查询语句在线上稳定运行了很长时间。为什么这次突然查询慢了？答：前面语句返回的查询条件都不为空，limit1很快就能找到那条数据并返回结果。但是这次代码中查询条件的实际结果为空，导致扫描了所有的主键索引。解决方案在知道了MySQL为什么选择这个索引之后，我们就可以根据以上的思路列出解决方案了。主要有两个方向：Mandatoryspecifiedindex干扰优化器选择forcedindex：forceindex就像我上面的初始操作一样，我们直接使用forceindex让语句去到我们想去的索引。select*fromsample_tableforceindex(idx_city_id_type)where(((1=1)and(city_id=565))and(type=13))orderbyiddesclimit0,1这样做的好处是见效快，可以立马解决问题。缺点也很明显：耦合度高，这种写在代码中的语句会变得难以维护，如果索引名称发生变化，或者没有这个索引，就得反复修改代码。是硬编码的。很多代码用框架封装了SQL，强制index()不好加。“我们换个方式，让我们引导优化器选择一个联合索引。”干扰优化器选择：增加限制通过增加限制，我们可以快速增加预估的扫描行数，例如改为如下限制0,1000SELECT*FROMsample_tablewherecity_id=565andtype=13orderbyiddescLIMIT0,1000这样会导致联合index然后sort，但是limit是这样强制增加的，其实总有一种黑箱调优的感觉。我们有更优雅的解决方案吗？干扰优化器选择：添加一个包含orderbyid字段的联合索引我们的慢查询使用了orderbyid，但是我们没有将id字段添加到联合索引中，导致优化器认为需要在排序之后联合索引，所以它根本不想使用这个联合索引。我们可以创建一个city_id、type和id的联合索引来解决这个问题。这也有一定的缺点。比如我的表有8000w条数据，建索引就非常耗时，通常索引有3.4g。如果无限制地使用索引来解决问题，可能会带来新的问题。表中的索引不应该太多。干扰优化器选择：有没有其他方法可以将其写成子查询？我们可以使用子查询，首先在子查询中使用city_id和type的联合索引，得到结果集后在limit1中选择第一项。但是使用子查询是有风险的，第一版DBA不推荐使用子查询。建议您在代码逻辑中完成复杂的查询。当然，我们这句也不复杂~Select*Fromsample_tableWhereidin(SelectidFrom`newhome_db`.`af_hot_price_region`where(city_id=565andtype=13))limit0,1解决方案很多...SQL优化是个大工程，我们有解决这个慢查询问题的方法还有很多，这里就不一一展开了。留给大家思考。小结本文带大家回顾一起MySQL优化器选错索引导致的线上慢查询事故。可见，MySQL优化器并不是单纯依赖某种标准来选择索引，而是综合选择的结果。我自己对这方面的了解还不是很深，还需要多多学习，力争能够对指数的选择（挖坑）做一个很好的总结。不说了，拿起那巨大的厚《高性能MySQL》，开始往我的方便面下压……》最后做个文章总结：慢查询语句中orderbyid的使用导致优化器比较主键索引和city_id和type的联合索引存在权衡，最终导致选择较慢的索引。可以通过强制指定索引，创建包含id的联合索引，增加limit来解决。在平时的开发中，尤其是数据量较大的表，要注意SQL语句的规范和索引的建立，以免发生意外。本文转载自微信公众号《后端技术漫谈》，可通过以下二维码关注。转载本文请联系后端技术讲座公众号。三剑三剑
