关于sql:慢SQL治理分享

简介：这里的慢 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         =  4
timestamp 容许 NULL     =  4 + 1(NULL)
timestamp 不容许 NULL   =  4
datatime 容许 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 治理专项也被屡次点名褒扬。

七 小结

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

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