简介： 这里的慢SQL指的是MySQL慢查问，是运行工夫超过long_query_time值的SQL。实在的慢SQL通常会随同着大量的行扫描、临时文件排序或者频繁的磁盘flush，间接影响就是磁盘IO升高，让失常的SQL变成了慢SQL，大面积执行超时。本文将和大家分享慢SQL的治理过程。

作者 | 史伟民
起源 | 阿里技术公众号

一 为什么要做这个事件

1 什么是慢SQL？

这里指的是MySQL慢查问，具体指运行工夫超过long_query_time值的SQL。

咱们常听常见的MySQL中有二进制日志binlog、中继日志relaylog、重做回滚日志redolog、undolog等。针对慢查问，还有一种慢查问日志slowlog，用来记录在MySQL中响应工夫超过阀值的语句。

大家不要被慢查问这个名字误导，认为慢查问日志只会记录select语句，其实也会记录执行工夫超过了long_query_time设定的阈值的insert、update等DML语句。

查看慢SQL是否开启

show variables like "slow_query_log%";

查看慢查问设定的阈值 单位:秒

show variables like "long_query_time";
对于咱们应用的AliSQL-X-Cluster即XDB来说，默认慢查问是开启的，long_query_time设置为1秒。

2 慢查问为何会导致故障？

实在的慢SQL往往会随同着大量的行扫描、临时文件排序或者频繁的磁盘flush，间接影响就是磁盘IO升高，失常SQL也变为了慢SQL，大面积执行超时。

去年双11后，针对技术侧裸露的问题，菜鸟CTO线推出多个专项治理，CTO-D各领一项作为sponsor，我所在的大团队负责慢SQL治理这个专项。

二 要做到什么水平

1 怎么来掂量一个利用的慢SQL重大水平？

微均匀

sum(aone利用慢SQL执行次数)-----------------------sum(aone利用SQL执行次数)

咱们认为，该值越大，影响越大；该值越小，影响可能小。

极其状况就是利用里每次执行的SQL全是慢SQL，该值为1；利用里每次执行的SQL全不是慢SQL，该值为0。

然而这个指标带来的问题是区分度不佳，尤其是对SQL QPS很高且大多数状况下SQL都不是慢查问的状况，偶发的慢SQL会被吞没。

另外一个问题，偶发的慢SQL是真的慢SQL吗？咱们遇到很多被慢查问日志记录的SQL，实际上可能受到其余慢SQL影响、MySQL磁盘抖动、优化器抉择等起因使得惯例查问下体现显然不是慢SQL的变成了慢SQL。

宏均匀

sum(慢SQL 1执行次数)    sum(慢SQL n执行次数)-----------------  +  ------------------sum(SQL 1执行次数)      sum(SQL n执行次数)---------------------------------------                   n

这个算法建设在被抓到的慢SQL有肯定执行次数的根底上，能够缩小假性慢SQL的影响。

当某些利用QPS很低，即一天执行SQL的次数很少，如果碰到假性SQL就会引起统计误差。

执行次数

sum(aone利用慢SQL执行次数)-----------------------           7

统计最近一周均匀每天的慢SQL执行次数，能够打消掉宏均匀带来的假性SQL问题。

慢SQL模板数量

以上维度均有个工夫限定范畴，为了追溯慢SQL历史解决状况，咱们还引入了全局慢SQL模板数量维度。

count(distinct(aone利用慢SQL模板) )

2 指标

外围利用：解决掉所有的慢SQL
一般利用：微平均指标降落50%

3 CTO报表

以CTO-D为单位依据以上多维度指标统计汇总利用的加权均匀，由低到高得出排名，突出头尾top3，每周播报。

三 为什么由我来做

猜想可能与我的背景无关，有C/C++背景，曾在上家公司负责过公司层面异地多活架构的设计和落地，对于MySQL比拟理解一些。

另外可能是利益无关，我所在小团队业务刚起步，不存在慢SQL，这样能够插入到各个业务线去。

四 口头撑持

1 团体MySQL规约

索引规约摘录局部：

【强制】超过三个表禁止join。须要join的字段，数据类型放弃相对统一；多表关联查问时，保障被关联的字段须要有索引。

阐明：即便双表join也要留神表索引、SQL性能。

【强制】在varchar字段上建设索引时，必须指定索引长度，没必要对全字段建设索引，依据理论文本区分度决定索引长度。

阐明：索引的长度与区分度是一对矛盾体，个别对字符串类型数据，长度为20的索引，区分度会高达90%以上，能够应用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

【强制】页面搜寻严禁左含糊或者全含糊，如果须要请走搜索引擎来解决。

阐明：索引文件具备B-Tree的最左前缀匹配个性，如果右边的值未确定，那么无奈应用此索引。

【举荐】避免因字段类型不同造成的隐式转换，导致索引生效。

【参考】创立索引时防止有如下极其误会：

1） 索引宁滥勿缺

认为一个查问就须要建一个索引。

2） 悭吝索引的创立

认为索引会耗费空间、重大拖慢更新和新增速度。

3） 抵制惟一索引

认为惟一索引一律须要在应用层通过“先查后插”形式解决。

2 DB变更规范

DDL须要管制变更速度，留神灰度和并发管制，变更公布须要在规定的变更公布窗口内。

五 分享一些我参加优化的例子

1 数据分布不平均


1）分库分表不合理

该业务数据分了8个库，每个库分了16张表，通过查看表空间能够看到数据简直都散布在各个库的某2张表中。分库分表的策略有问题，另外过高预估了业务增量，这个持保留意见。

2）索引不合理

单表创立了idx_logistics_corp_id_special_id的联结索引，但即使这样区分度仍然太低，依据试验及业务反馈(logistics_corp_id,transport_type_id)字段组合区分度十分高，且业务存在transport_type_id的单查场景。

2 索引问题

SELECT  COUNT(0) AS `tmp_count`FROM(    SELECT      `table_holder`.`user_id`,      `table_holder`.`sc_item_id`,      SUM(        CASE          `table_holder`.`inventory_type`          WHEN 1 THEN `table_holder`.`quantity`          ELSE 0        END      ) AS `saleable_quantity`,      SUM(        CASE          `table_holder`.`inventory_type`          WHEN 1 THEN `table_holder`.`lock_quantity`          ELSE 0        END      ) AS `saleable_lock_quantity`,      SUM(        CASE          `table_holder`.`inventory_type`          WHEN 401 THEN `table_holder`.`quantity`          ELSE 0        END      ) AS `transfer_on_way_quantity`,      `table_holder`.`store_code`,      MAX(`table_holder`.`gmt_modified`) AS `gmt_modified`    FROM      `table_holder`    WHERE(`table_holder`.`is_deleted` = 0)      AND(`table_holder`.`quantity` > 0)      AND `table_holder`.`user_id` IN(3405569954)      AND `table_holder`.`store_code` IN('ZJJHBHYTJJ0001', '...1000多个')    GROUP BY      `table_holder`.`user_id`,      `table_holder`.`sc_item_id`    ORDER BY      `table_holder`.`user_id` ASC,      `table_holder`.`sc_item_id` ASC  ) `a`;

这个case对应的表有store_code索引，因而认为没问题，没方法优化了。实则通过执行打算，咱们发现MySQL抉择了全表扫描。针对该case实际发现，当范畴查问的个数超过200个时，索引优化器将不再应用该字段索引。

最终通过拉取最近一段时间的相干查问SQL，联合业务的数据分布，咱们发现采纳(is_deleted,quantity)即可解决。

判断执行打算采纳的索引长度：key_len的长度计算公式(>=5.6.4)

char(10)容许NULL      =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 1(NULL)char(10)不容许NULL    =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1)varchr(10)容许NULL    =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 1(NULL) + 2(变长字段)varchr(10)不容许NULL  =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 2(变长字段)int容许NULL           =  4 + 1(NULL)int不容许NULL         =  4timestamp容许NULL     =  4 + 1(NULL)timestamp不容许NULL   =  4datatime容许NULL      =  5 + 1(NULL)datatime不容许NULL    =  5

3 被人影响

用到了索引却仍然被爆出扫描2千万行：

索引字段区分度很高：

同期间惯例SQL变为了慢查问：

DB数据盘拜访状况：

排查共用物理机其余实例的状况，发现有个库在问题工夫左近有很多慢sql须要排序，写临时文件刚好写入了2GB：

多个MySQL实例leader节点混合部署在同一台物理机，尽管通过docker隔离了CPU、MEM等资源，但目前还没有做到buffer io的隔离。

4 无奈解决

通过汇总剖析高频的查问并联合业务得出适合的索引往往可能解决日常遇到的慢查问，但这并不是万能的。

比方有可能索引越加越多，乃至成了这样：

有些场景，比方反对多个字段组合查问，又没有必填项，如果都要通过索引来反对显然是不合理的。

查问场景下，将区分度较高的字段设定为必填项是个好习惯；查问组合很多的状况下思考走搜寻支持性更好的存储或者搜索引擎。

六 日常化解决

随着各个CTO-D线的深刻治理，各项指标较之前均有十分大的改观，比方外围利用实现慢查问清零，影响最大的一些慢SQL被得以解决，而我所在的团队排名也由最后的尾部top3进入到头部top3。

慢SQL治理进入日常化，通过每周固定推送慢SQL工单、owner接手解决、结单，根本造成了定期清零的习惯和气氛，慢SQL治理专项也被屡次点名褒扬。

七 小结

这是一篇早退的总结，当初回头看感觉这外面的策略制订、问题剖析和解决的过程还是蛮值得拿进去和大家分享下。

原文链接
本文为阿里云原创内容，未经容许不得转载。