📖【线上问题】慢查询引发的数据库崩溃
2021-5-17
| 2024-6-9
0  |  Read Time 0 min
type
status
date
slug
summary
tags
category
icon
password

问题原因

某一天晚上,我们突然收到了线上数据库的频繁报警,这个报警的意思大致就是说,数据库突然涌现出了大量的慢查询,而且因为大量的慢查询,导致每一个数据库连接执行一个慢查询都要耗费很久。
这样的话,必然会导致突然过来的很多查询需要让数据库开辟出来更多的连接。因此,这个时候报警也告诉我们,数据库的连接突然也暴增了,而且每个连接都打满,每个连接都要执行一个慢查询,慢查询还跑得特别慢。
接着引发的问题,就是数据库的连接全部打满,没法开辟新的连接了,但是还持续有新的查询发送过来,导致数据库没法处理新的查询,很多查询发到数据库直接就阻塞然后超时了。这也直接导致线上的商品系统频繁的报警,出现了大量的数据库查询超时报错的异常!
当时看到这一幕报警,让人是非常揪心的,因为这种情况基本意味着商品数据库以及商品系统濒临崩溃了。大量慢查询耗尽了数据库的连接资源,而且一直阻塞在数据库里执行,数据库没法执行新的查询,商品数据库没法执行查询,用户没法使用商品系统,也就没法查询和筛选电商网站里的商品了!
而且大家要知道,当时正好是晚上晚高峰的时候!也就是一个电商网站比较繁忙的时候,虽说商品数据是有多级缓存架构的,但是实际上在下单等过程中,还是会大量的请求商品系统的,所以晚高峰的时候,商品系统本身TPS大致是在每秒几千的。
因此这个时候,发现数据库的监控里显示,每分钟的慢查询超过了10w+,也就是说商品系统大量的查询都变成了慢查询。
那么慢查询的都是一些什么语句呢?其实主要就是下面这条语句,大家可以看一下,我们做了一个简化:
这其实是一个很稀松平常的SQL语句,它就是用户在电商网站上根据商品的品类以及子类在进行筛选。当然,真实的SQL语句里可能还包含其他的一些字段的筛选,比如品牌以及销售属性之类的,我们这里是做了一个简化,然后按id倒序排序,最后是分页,就这么一个语句。
这个语句执行的商品表里大致是1亿左右的数据量,这个量级已经稳定了很长时间了,主要也就是这么多商品,但是上面的那个语句居然一执行就是几十秒!
几十秒,这还得了?基本上数据库的连接全部被慢查询打满,一个连接要执行几十秒的SQL,然后才能执行下一个SQL,此时数据库基本就废了,没法执行什么查询了!!!
 
在一个亿级数据量的商品表里执行,需要耗时几十秒,结果导致了数据库的连接资源全部打满,商品系统无法运行,处于崩溃状态。
现在就得来分析一下,到底为什么会出现这样的一个情况。首先要要解释一下,这个表当时肯定是对经常用到的查询字段都建立好了索引的。那么,针对这里简化后的SQL语句,你可以认为如下的一个索引 KEY index_category(category, sub_category) 肯定是存在的,所以基本可以确认上面的SQL绝对是可以用上索引的。
因为如果你一旦用上了品类的那个索引,那么按品类和子类去在索引里筛选,其实第一,筛选很快速,第二,筛出来的数据是不多的。按说这个语句应该执行的速度是很快的,即使表有亿级数据,但是执行时间也最多不应该超过1秒。
但是现在这个SQL语句跑了几十秒,那说明它肯定就没用我们建立的那个索引,所以才会这么慢。那么它到底是怎么执行的呢?我们来看一下它的执行计划:
最核心的信息:它的possible_keys里是有我们的index_category的,结果实际用的key不是这个索引,而是PRIMARY!! 而且Extra里清晰写Using where。
到此为止,这个SQL语句为什么性能这么差,就真相大白了。它其实本质上就是在主键的聚簇索引上进行扫描,一边扫描,一边还用了where条件里的两个字段去进行筛选。所以这么扫描的话,那必然就是会耗费几十秒了!
因此此时为了快速解决这个问题,就需要强制性地改变MySQL自动选择这个不合适的聚簇索引进行扫描的行为。那么怎么改变呢?那么可以使用FORCE INDEX语法,如下:
使用上述语法过后,强制让SQL语句使用了你指定的索引,此时再次执行这个SQL语句,会发现它仅仅耗费100多毫秒而已!性能瞬间就提升上来了!
因此当时在紧急关头中,一下子就把这个问题给解决了。这里也是告诉大家这样的一个实战技巧,就是你如何去强制改变MySQL的执行计划。之前就有一个朋友来问我们说,面试官问我,如果MySQL使用了错误的执行计划,应该怎么办?
其实答案很简单,就是这个案例里的情况,方法就是FORCE INDEX语法就可以了。但是这个案例还没完,这里还遗留了很多的问题,比如:
  • 为什么在这个案例中MySQL默认会选择对主键的聚簇索引进行扫描?
  • 为什么没使用index_category这个二级索引进行扫描?
  • 即使用了聚簇索引,为什么这个SQL以前没有问题,现在突然就有问题了?
 
首先,第一个问题,为什么针对以下SQL语句,MySQL要选择对聚簇索引进行扫描呢?
其实关于这个逻辑,说起来也并不是太复杂。因为大家都知道,这个表是一个亿级数据量的大表,那么对于它来说,index_category这个二级索引也是比较大的。
所以此时对于MySQL来说,它有这么一个判断:它觉得如果要是从index_category二级索引里来查找到符合WHERE条件的一波数据,接着还得回表,回到聚簇索引里去。因为SQL语句是要SELECT *,所以这里必然涉及到一次回表操作,回到聚簇索引里去把所有字段的数据都查出来。但是在回表之前,它必然要做完ORDER BY id DESC LIMIT xx, xx这个操作。
举个例子吧,比如它根据WHERE category = 'xx' AND sub_category = 'xx',从index_category二级索引里查找出了一大波数据。比如从二级索引里假设找出来了几万条数据,接着因为二级索引里是包含主键id值的,所以此时它就得按照ORDER BY id DESC这个排序方式,对这几万条数据基于临时磁盘文件进行filesort磁盘排序,排序完了之后,再按照LIMIT xx, xx语法,把指定位置的几条数据拿出来,假设就是LIMIT 0, 10,那么就是把10条数据拿出来。
拿出来10条数据之后,再回到聚簇索引里去根据id查找,把这10条数据的完整字段都查出来。这就是MySQL认为如果你使用index_category的话,可能会发生的一个情况。
所以它担心的是,你根据WHERE category = 'xx' AND sub_category = 'xx',从index_category二级索引里查出来的数据太多了,还得在临时磁盘里排序,可能性能会很差。因此MySQL就把这种方式判定为一种不太好的方式。
因此它才会选择换一种方式,也就是说,直接扫描主键的聚簇索引。因为聚簇索引都是按照id值有序的,所以扫描的时候,直接按ORDER BY id DESC这个倒序顺序扫描过去就可以了,然后因为它知道你是LIMIT 0, 10的,也就知道你仅仅只要拿到10条数据就行了。
所以它在按顺序扫描聚簇索引的时候,就会对每一条数据都采用Using where的方式,跟WHERE category = 'xx' AND sub_category = 'xx'条件进行比对,符合条件的就直接放入结果集里去,最多就是放10条数据进去就可以返回了。此时MySQL认为,按顺序扫描聚簇索引,拿到10条符合WHERE条件的数据,应该速度是很快的,很可能比使用index_category二级索引那个方案更快,因此此时它就采用了扫描聚簇索引的这种方式。
那接下来我们又要考虑一个问题了。那就是这个SQL语句,实际上之前在线上系统运行一直没什么问题,也就是说,之前在线上系统而言,即便采用扫描聚簇索引的方案,其实这个SQL语句也确实一般都运行不慢,最起码是不会超过1秒的。那么为什么会在某一天晚上突然的就大量报慢查询,耗时几十秒了呢?
原因也很简单,其实就是因为之前的时候,WHERE category = 'xx' AND sub_category = 'xx'这个条件通常都是有返回值的,也就是说根据条件里的取值,扫描聚簇索引的时候,通常都是很快就能找到符合条件的值以及返回的,所以之前其实性能也没什么问题。
但是后来可能是商品系统里的运营人员,在商品管理的时候加了几种商品分类和子类,但是这几种分类和子类的组合其实没有对应的商品。也就是说,那一天晚上,很多用户使用这种分类和子类去筛选商品,WHERE category = '新分类' AND sub_category = '新子类'这个条件实际上是查不到任何数据的!
所以说,底层在扫聚簇索引的时候,扫来扫去都扫不到符合WHERE条件的结果,一下子就把聚簇索引全部扫描了一遍。于是上亿数据全表扫描了一遍,都没找到符合WHERE category = '新分类' AND sub_category = '新子类'这个条件的数据。也正是因为如此,才导致这个SQL语句频繁地出现几十秒的慢查询,进而导致MySQL连接资源打满,商品系统崩溃。
 
  • 线上问题排查
  • 推荐
  • 从混乱到整洁:理解整洁架构什么是线程池,如何实现的?
    Loading...
    Catalog