的“锅”。图片来自Pexels。是选择一个实际上需要更长执行时间的索引）。调研过程中查阅了很多资料，也学习了MySQL优化器选择索引的基本原理，分享本文的解题思路。PS：本人对MySQL的了解有限。如有错误，欢迎在评论区理性讨论、指正。在这次事故中，我们也充分看出了深入理解MySQL运行原理的重要性，这是遇到问题时能够独立解决问题的关键。想象一下，在一个月的风雨交加的夜晚，公司的在线服务突然挂了，你的同事都不在线，只有你一个人具备解决问题??的条件。如果这时候你被工程师的基本功卡住了，我就问你尴尬不尴尬。。。本文主要内容：故障描述TroubleshootingMySQL索引选择原则解决思路与总结连接数过多，导致数据库响应变慢，影响业务。看图表，慢查询在高峰时达到了每分钟14万次，正常情况下慢查询的次数只有两位数以下，如下图所示：快速查看慢SQL记录，发现是同类型语句（私有数据比如表名，我已经隐藏）导致的所有慢查询：select*fromsample_tablewhere1=1and(city_id=565)and(type=13)orderbyiddesclimit0,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来分析语句：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索引选择原则①优化器索引选择标准MySQL中一条语句的执行流程大致如下图所示，查询优化器是选择索引的地方：引用参考资料解释：首先，选择索引是MySQL优化器的工作。优化器选择索引的目的是找到一个最优的执行计划，执行代价最小的语句。在数据库中，扫描行数是影响执行成本的因素之一。扫描的行越少意味着对磁盘数据的访问越少，CPU消耗也越少。当然，扫描行数并不是判断的唯一标准。优化器还会根据是否使用临时表、是否排序等因素进行综合判断。综上所述，优化器的选择需要考虑的因素有很多：扫描的行数、是否使用临时表、是否排序等等。回头看看刚才的两张Explain截图：去掉主键索引的查询语句，rows中的预估行数为1833，强制使用联合索引的行数为45640，额外的信息显示需要使用filesort进行额外排序。所以，在没有强制索引的情况下，优化器选择主键索引，因为它认为主键索引扫描的行数少，不需要额外的排序操作，主键索引本身就是有序的。②行数是怎么估计的？同学们会问，为什么只有1833行，虽然实际上扫描了整个主键索引，但行数远不止几千行。实际上，explain中的rows是MySQL中预估的行数，是综合考虑查询条件、索引、limits的预估行数。MySQL如何获取索引的基数？下面就给大家简单介绍下MySQL抽样统计的方法。为什么需要抽样统计？因为把整张表拿出来逐行统计，虽然可以得到准确的结果，但是成本太高，所以只能选择“抽样统计”。在抽样统计的时候，InnoDB会默认选取N个数据页，统计这些页上不同的值，取一个平均值，然后乘以这个索引中的页数，得到这个索引的基数。数据表会不断更新，索引统计不会固定不变。因此，当变化的数据行数超过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的联合索引，那么优化器就会使用这个联合索引，因为索引已经排序了。④改变极限尺寸能解决问题吗？调整limitnumber会不会影响预估行数，进而影响优化器索引的选择？答案是肯定的。我们执行limit10：select*fromsample_tablewherecity_id=565andtype=13orderbyiddesclimit0，图10的行变成18211，增加了10倍。如果使用limit100会怎样？优化器选择联合索引。初步估计是行数会翻倍，所以优化器放弃了主键索引。我宁愿使用联合索引来排序，也不想使用主键索引。⑤为什么查询突然出现异常慢？Q：这条查询语句在线上稳定运行了很长时间。为什么这次突然查询慢了？A：前面语句查询条件返回的结果不为空，limit1很快就能找到那条数据并返回结果。但是这次代码中查询条件的实际结果为空，导致扫描了所有的主键索引。解决方案在知道了MySQL为什么选择这个索引之后，我们就可以根据以上的思路列出解决方案了。主要有两个方向：强制索引规范干扰优化器选择①强制索引选择：强制索引就像我上面的初始操作一样，我们直接使用强制索引，让语句使用我们想要的索引。select*fromsample_tableforceindex(idx_city_id_type)where(((1=1)and(city_id=565))and(type=13))orderbyiddesclimit0,1这样做的好处是见效快，可以立马解决问题。缺点也很明显：耦合度高，这种写在代码中的语句会变得难以维护，如果索引名称发生变化，或者没有这个索引，就得反复修改代码。是硬编码的。很多代码用框架封装了SQL，强制index()不好加。让我们改变引导优化器选择联合索引的方式。②干扰优化器选择：增加limit通过增加limit，我们可以快速增加预估的扫描行数，例如改为如下limit0,1000:SELECT*FROMsample_tablewherecity_id=565andtype=13orderbyiddescLIMIT0,1000这样会导致joint建立索引，然后排序，但是这样强行增加limit，其实总有一种黑箱调优的感觉。我们有更优雅的解决方案吗？③干扰优化器选择：添加一个包含orderbyid字段的联合索引我们的慢查询使用了orderbyid，但是我们并没有将id字段添加到联合索引中，导致优化器认为需要联合索引排序，所以它根本不想使用这个联合索引。我们可以通过创建一个新的city_id、type和id的联合索引来解决这个问题。这也有一定的缺点。比如我的表有8000w条数据，建索引非常耗时，通常索引有3.4G，如果不加限制地使用索引解决问题，可能会带来新的问题。表中的索引不应该太多。④干扰优化器选择：有没有其他办法写成子查询？我们可以使用子查询，先到city_id的联合索引中键入子查询，得到结果集后选择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语句的规范和建立指标，避免事故发生。作者：漫三刀编辑：陶佳龙来源：转载自公众号后端技术漫谈（ID：Rude3Knife）
