阿里巴巴工程师教你认识mysql慢查询

1.为什么聊这个话题

  在工作中，我们经常遇到各种线上问题，如cpu 和 load都变高了，full gc频繁，线程池满了等，经过各种排查可能最后有挺多原因是跟数据库出现了慢查询或者不合理的查询导致等一系列等连锁反应。

在各种大促性的活动我们会做各种手段去保证系统的稳定性，如压测，限流，缓存，消息异步化，很多情况下除了计算密集型等应用，很多都是为了防止我们等数据库挂掉。 数据库性能提升是我们能够放心做应用服务器水平扩容的一个最重要的前提。否则如果你的数据库查询性能差的话，一个慢查询就可能导致mysql服务器运行中线程数飙高，cpu飙高，rt延长。进而导致应用服务器的处理线程耗时延长，最终导致线程池满，cpu，load都会飙高。如果依赖你的服务的其它服务器没有做好降级熔断处理，那可想而知，他的应用也会挂掉。这个慢sql就是压死你的最后一棵稻草了。 后面作为一个专题和大家一起探讨聊聊mysql的慢查询和我在工作中遇到的实际问题的案例分享。今天先聊下 慢查询的一些基础和我的一些小套路。

2.查询慢的常见原因

   一次数据库的查询大体需要经历客户端，应用服务端，mysql服务端sql解析，优化器索引评估，执行计划生成，执行查询，返回给调用端。
  我们重点关注请求到mysql之后，mysql需要从存储引擎（目前大多使用innodb）中读取数据，可能还需要在mysql服务端进行过滤，排序等。而知道innodb的主键索引即是具体的数据存储的地方，在mysql内存空间有限的情况下，我们需要进行读取磁盘上的数据，就会导致io增多等。
  最核心的查询慢的原因：
  1、应用服务端一次性访问过多数据
  2、mysql服务端存储引擎过多数据

2.1 应用服务端一次性访问过多数据

2.1.1 查询过多行

   常见案例：一次从数据库中获取到很多数据，在应用服务器中做排序什么的，最终只取其中很小一部分数据
   举个我犯过的一个真实案例：之前有个定时任务需要从数据库中扫描数据并定的处理，当时是分页查询的，当时为了减少对数据库的访问次数，就将一次分页查询返回的条数设置了2万条，且单台应用服务器有多个线程在拉取数据并做处理，记得应该有二三十个线程，后来观察发现服务器开始有频繁的full gc发生了。这个就是服务器本身处理能力有限，却一次性都捞到内存中不断等待被处理。这种情况下数据库本身也有压力，同时应用服务器也承受了他不能承受的。

2.1.2 查询过多列

  关于这一点，目前工作中用的不多，大部分情况下我们还是会返回所有列。
  大部分情况下为了做到查询接口在应用端的接口复用。不太可能去写多个不同的sql返回不同的列并实现多个不同的接口吧。
  另外大部分情况下我们一行的数据大小都是很小的，比如100字节。且本身innodb就是行存储类型，即不管你是需要获取哪些列，最终还是需要在存储引擎层读取一整行的数据，能减少的主要是网络io大小。当然如果你本身只需要返回的列全是索引中的列，且这种查询比较频繁，倒是可以考虑这种优化。减少了本身但回表查询主键索引获取其它列的操作。且网络io本身也减少了。

2.2 mysql服务端访问存储引擎过多数据

这种主要是说mysql需要扫描的行数和实际真实返回的行数的差别。最典型的例子如limit 10000,10分页查询，实际返回10条数据，但是mysql需要扫描至少10010条记录。这里说的至少是当你的查询条件完全命中索引的情况下。即走覆盖索引情况下。否则可能还会扫描更多行数。 这种也是我们工作中会遇到的最多的导致慢查询的原因了。会有多种原因导致这种情况，最可能的原因如： 1、 索引本身设计不合理、 可能导致回表查询（指的是需要回主键索引查询，因为主键索引才存储了具体的数据行），最好的策略 是我们尽量的使用覆盖索引。其次就是命中更多的索引字段。减少扫描行数量。 2、查询语句本身写的不好 最常见的就是各种范围查询，没有关注或者理解索引在什么时候生效的情况下的查询。最终会导致即使有 个好的索引。也无法很好的利用到。导致扫描行数增加。 我们一定要尽可能减少扫描行数。后面sql性能优化实战中会一一和大家聊的更具体点。

2.3 mysql服务端并发访问量大的问题

这种情况比较少见，常常我们理解的慢查询可能都是单条sql本身访问的时候就是很慢。但是有些情况下，单条sql访问一点都不慢，可能10ms一下就返回了，但是当这个sql并发访问量很大，就会导致同一时刻的扫描行数也会很大，这个时候这个sql也本省成为了慢sql。 举一个真实的案例：之前淘宝图片空间我们会需要查询一个商家的所有图片的文件夹记录的情况，大部分情况下一个图用户的图片空间的文件夹的数量其实是很少的，但是却出现了一个时间点并发访问量很大的调用。导致mysql的行扫大增，执行线程数飙高，cpu飙高等一系列现象。 这类慢查询你就无法单纯优化单条sql了，因为他已经索引最优，查询合理了。无法优化了。只能想办法从其它角度减少并发访问量。如增加缓存，及其限流处理。

2.4 mysql同一条记录频繁更新导致的

这种更多的出现在库存更新场景。由于热点商品库存扣减导致的行锁等待造成的。多个线程会同一时间内都在等待行锁这种资源的释放导致。

3. 排查慢查询的套路

套路其实挺简单的。 第一步、快速识别出来你哪个sql是真正的慢查询 有些情况下可能某个慢查询会导致整个mysql的服务端cpu load都会升高。这种情况下很多sql都会变慢 每个sql可能耗时都是1s以上。这个时候快速识别慢查询的时候如何不要被其它慢查询干扰你的判断呢。 除了关注到耗时之外，需要同时关注sql的执行计划中的预估扫描行数和实际扫描行数来协助你的判断。 大部分情况下都会发现预估扫描行数远远大于实际扫描行数的情况。 第二步、explain查看慢sql的具体执行计划

第三步、根据查询执行计划确认是否索引设计不合理或者查询语句使用不当等

第四步、索引优化或者修改查询语句

4. 总结

首先上面文章中有不对的地方，也欢迎指出，共同进步。 这篇文章先和大家聊了下mysql的慢查询，让大家对慢查询有个基本的认识，如到底什么时候会出现慢查询，大体的可能原因有哪些。以及基本的排查处理套路。 同时也是希望借助这篇文章让大家对mysql有更多的兴趣以及意识到在我们工作中的重要性。 也非常有助于面试哦。 后面也是会介绍一些自己在工作中遇到的实际慢查询的原理和优化实战。希望持续关注哦。

关注公众号：Tpark技术工匠。每周都会推送原创内容哦！！！另外可以内推阿里哦，快来发送简历：[e-mail]