1. 入夜遇袭 惊魂时刻
果然,经过进一步查看,发现会话中有很多耗时较长的SQL正在执行中,占比较大的SQL其样本如下:
同时,SHOW ENGINE INNODB STATUS显示此时SEMAPHORES一节有较多线程在等待btr0sea.c创建的RW-latch:
“果然如此,‘老朋友’,别来无恙啊。”
2. 应急处理 妙手回春
“大夫,怎么样?被慢SQL刺伤而已,怎么严重到不能动了呢?”
只见神医瑶池没有多言,打开数小库内置的DAS系统,首先进行SQL限流,限制了问题SQL模板的并发度,并KILL了会话中正在执行的问题SQL,以快速降低CPU消耗恢复数小库的生命体征,让城中业务顺利开展。
“主要是因为慢SQL和AHI频繁维护同时发难。”谈话间,手起刀落,诊疗结束,神医瑶池对凌霄城一众人缓缓道明此次病因——
之前,问题SQL未曾出现在慢日志中,问题发生阶段,该模板有较多SQL出现在慢日志记录中,通过EXPLAIN可以看到,问题SQL有索引idx_example(instance_id, user_id, is_deleted_by_user, days)可用。通过SQL洞察对比发现,以前问题SQL执行时,翻页较少,即LIMIT语句的OFFSET较小;此次问题时段某些特殊实例的数据量较大,翻页较多,虽然索引是一样的,但语句中LIMIT后的OFFSET值较大执行耗时增加,该类慢SQL大量执行,导致数小库压力突增。
雪上加霜的是,在问题发生时,异常快照里SHOW ENGINE INNODB STATUS结果中SEMAPHORES一节有较多线程在等待btr0sea.c创建的RW-latch,说明数据库频繁维护自适应哈希索引(Adaptive Hash Index),锁竞争激烈,也消耗了大量CPU资源。
对了,顺便提下,我派的DAS推出了新版SQL洞察功能,可快速了解过往SQL执行情况。通过冷热数据分离存储,使用成本相比旧版可下降17%~83%。
“怪不得,原来如此~真是明‘枪’易躲,暗‘箭’难防啊!”
3. 探源溯流 防患未然
慢日志中,该问题SQL改写前执行耗时2063毫秒,改写后语句执行耗时133毫秒,耗时降低93%。
“大夫,为什么改写后的SQL语句耗时降低这么多?”
“也罢,时间还够,和大家聊聊我们瑶池派的诊疗心法吧,知其然也要知其所以然嘛~咱们就从索引结构、Server层与存储引擎层交互方式、回表、覆盖索引这四个方面,简单说说吧。”
3.1 InnoDB的索引结构
MySQL的索引是在存储引擎层实现的,InnoDB中,每一个索引都对应一棵B+树,根据叶子节点的内容,索引可以分为主键索引和非主键索引。主键索引的叶子节点存放的是整行数据,也称聚簇索引;非主键索引的叶子节点存放的是主键的值,也称二级索引。
因为叶子节点存放的数据不同,基于主键索引查询时只需要搜索主键索引对应的B+树,然后返回数据,而通过非主键索引查询时,先搜索非主键索引对应的B+树,找到叶子节点后拿到主键ID,再使用主键ID查询一次。查询了二级索引后回到主键索引树获取数据的过程被称为回表。
id | v | c |
---|---|---|
100 | 3 | cd |
200 | 12 | la |
400 | 7 | os |
500 | 16 | um |
600 | 28 | dt |
3.2 Server层存储引擎层的交互方式
3.3 回表的性能问题
3.4 覆盖索引
3.5 改写前后SQL对比
改写前问题SQL执行过程:
a. 优化器选择了使用idx_example(instance_id, user_id, is_deleted_by_user, days)索引;
b. 选择条件和排序条件都在索引中,直接排序;
3.5.2 改写后SQL执行过程
瑶池优化后SQL如下:
▷ 获取排序后的第OFFSET+1行到第OFFSET+LIMIT行的id列表;
b. 根据id列表匹配INNER JOIN前后的两个SELECT语句, 获取其他需要查询的字段,回表只在这一步进行,只回表1000次。
可以执行以下两条语句验证下回表次数对耗时的影响:
3.6 Adaptive Hash Index
对于参数设置,如innodb_adaptive_hash_index,可参考DAS参数诊断推荐服务。
一番深入讲解,凌霄城众人对数小库此次症状和瑶池派有了更为深刻的认知。
言毕,妙手神医瑶池作别众人,翻身跃入数据江湖。烟波浩渺,纷争还在继续,瑶池派和各个城池的数据故事还在上演。
阿里云瑶池拥有国内强大且丰富的云数据库产品家族,涵盖关系型数据库、非关系型数据库、数据仓库、数据库生态工具四大版块,可以为企业提供一站式数据管理与服务。
其中,数据库自治服务DAS能够帮助用户消除数据库管理的复杂性及人工操作引发的服务故障,有效保障数据库服务的稳定、安全及高效。