线上又炸了,原来是索引的“锅”
送福利啦
关注鸿蒙技术社区,回复【鸿蒙】送价值399元的鸿蒙开发板套件(数量有限,先到先得),还可以免费下载鸿蒙入门资料!
👇扫码立刻关注
👇
专注开源技术,共建鸿蒙生态
最近在线上环境遇到了一次 SQL 慢查询引发的数据库故障,影响线上业务。
图片来自 Pexels
经过排查后,确定原因是:SQL 在执行时,MySQL 优化器选择了错误的索引(不应该说是“错误”,而是选择了实际执行耗时更长的索引)。
排查过程中,查阅了许多资料,也学习了下 MySQL 优化器选择索引的基本准则,在本文中进行解决问题思路的分享。
PS:本人 MySQL 了解深度有限,如有错误欢迎在评论区理性讨论和指正。
在这次事故中也能充分看出深入了解 MySQL 运行原理的重要性,这是遇到问题时能否独立解决问题的关键。
试想一个月黑风高的夜晚,公司线上突然挂了,而你的同事们都不在线,就你一个人有条件解决问题,这时候如果被工程师的基本功把你卡住了,就问你尴不尴尬...
本文的主要内容:
- 故障描述
- 问题原因排查
- MySQL 索引选择原理
- 解决方案
- 思考与总结
看图表慢查询在高峰达到了每分钟 14w 次,在平时正常情况下慢查询数仅在两位数以下,如下图:
赶紧查看慢 SQL 记录,发现都是同一类语句导致的慢查询(隐私数据例如表名,我已经隐去):
select
*
from
sample_table
where
1 = 1
and (city_id = 565)
and (type = 13)
orderby
iddesc
limit
0, 1
看起来语句很简单,没什么特别的,但是每个执行的查询时间达到了惊人的 44s。
首先当然要怀疑会不会该语句没走索引,查看建表 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`)
- select_type:查询类型,有简单查询、联合查询、子查询等。
- key:使用的索引。
- rows:预计需要扫描的行数。
我们使用 Explain 分析该语句:
select * from sample_table where city_id = 565andtype = 13orderbyiddesclimit0,1
得到结果:
我们可以使用 force index(idx_city_id_type) 让该语句选择我们设置的联合索引:
select * from sample_table forceindex(idx_city_id_type) where ( ( (1 = 1) and (city_id = 565) ) and (type = 13) ) orderbyiddesclimit0, 1
这次明显执行得飞快,分析语句:
问题找到了,总结下来就是:
MySQL 优化器认为在 limit 1 的情况下,走主键索引能够更快的找到那一条数据,并且如果走联合索引需要扫描索引后进行排序,而主键索引天生有序,所以优化器综合考虑,走了主键索引。
实际上,MySQL 遍历了 8000w 条数据也没找到那个天选之人(符合条件的数据),所以浪费了很多时间。
MySQL 一条语句的执行流程大致如下图,而查询优化器则是选择索引的地方:
而优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。
我们回头看刚才的两个 Explain 截图:
同学们就要问了,为什么 rows 只有 1833,明明实际扫描了整个主键索引啊,行数远远不止几千行。
- 设置为 on 的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10。
- 设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。
由于是采样统计,所以不管 N 是 20 还是 8,这个基数都是很容易不准的。
我们可以使用 analyze table t 命令,可以用来重新统计索引信息。但是这条命令生产环境需要联系 DBA,所以我就不做实验了,大家可以自行实验。
③索引要考虑 order by 的字段
为什么这么说?因为如果我这个表中的索引是 city_id,type 和 id 的联合索引,那优化器就会走这个联合索引,因为索引已经做好了排序。
④更改 limit 大小能解决问题?
把 limit 数量调大会影响预估行数 rows,进而影响优化器索引的选择吗?
答案是会。
select * from sample_table where city_id = 565andtype = 13orderbyiddesclimit0,10
图中 rows 变为了 18211,增长了 10 倍。如果使用 limit 100,会发生什么?
知道了 MySQL 为何选择这个索引的原因后,我们就可以根据上面的思路来列举出解决办法了。
- 强制指定索引
- 干涉优化器选择
就像上面我最开始的操作那样,我们直接使用 force index,让语句走我们想要走的索引。
select * from sample_table forceindex(idx_city_id_type) where ( ( (1 = 1) and (city_id = 565) ) and (type = 13) ) orderbyiddesclimit0, 1
- 高耦合,这种语句写在代码里,会变得难以维护,如果索引名变化了,或者没有这个索引了,代码就要反复修改。属于硬编码。
- 很多代码用框架封装了 SQL,force index() 并不容易加进去。
通过增大 limit,我们可以让预估扫描行数快速增加,比如改成下面的 limit 0, 1000:
SELECT * FROM sample_table where city_id = 565andtype = 13orderbyiddescLIMIT0,1000
但是子查询使用有风险,一般 DBA 也不建议使用子查询,会建议大家在代码逻辑中完成复杂的查询。当然我们这句并不复杂啦!
Select * From sample_table Whereidin (SelectidFrom`newhome_db`.`af_hot_price_region`where (city_id = 565andtype = 13)) limit0, 1
- 该慢查询语句中使用 order by id 导致优化器在主键索引和 city_id 和 type 的联合索引中有所取舍,最终导致选择了更慢的索引。
- 可以通过强制指定索引,建立包含 id 的联合索引,增大 limit 等方式解决问题。
- 平时开发时,尤其是对于特大数据量的表,要注意 SQL 语句的规范和索引的建立,避免事故的发生。
编辑:陶家龙
出处:转载自公众号后端技术漫谈(ID:Rude3Knife)
精彩文章推荐:
最新评论
推荐文章
作者最新文章
你可能感兴趣的文章
Copyright Disclaimer: The copyright of contents (including texts, images, videos and audios) posted above belong to the User who shared or the third-party website which the User shared from. If you found your copyright have been infringed, please send a DMCA takedown notice to [email protected]. For more detail of the source, please click on the button "Read Original Post" below. For other communications, please send to [email protected].
版权声明:以上内容为用户推荐收藏至CareerEngine平台,其内容(含文字、图片、视频、音频等)及知识版权均属用户或用户转发自的第三方网站,如涉嫌侵权,请通知[email protected]进行信息删除。如需查看信息来源,请点击“查看原文”。如需洽谈其它事宜,请联系[email protected]。
版权声明:以上内容为用户推荐收藏至CareerEngine平台,其内容(含文字、图片、视频、音频等)及知识版权均属用户或用户转发自的第三方网站,如涉嫌侵权,请通知[email protected]进行信息删除。如需查看信息来源,请点击“查看原文”。如需洽谈其它事宜,请联系[email protected]。