近期笔者部门进行大调（cai）整（yuan），大量遗留系统需要维护。整体业务需求减少了，但是需要少数人支撑大量业务系统，需要面对问题的包括业务咨询、遗留的已知线上问题处置方案、未知的线上问题（隐患）。
本篇主要针对可能的数据库层面的隐患排查，总结一下存量慢 SQL 的治理经验。
关于慢 SQL
集团对于慢 SQL 的定义，执行超过 1s 的 SQL 为慢 SQL。
慢SQL由于执行时间长，会导致：
- 系统的响应时间延迟，影响用户体验。
- 资源占用增加，增高了系统的负载，其他请求响应时间也可能会收到影响。
- 慢SQL占用数据库连接的时间长,如果有大量慢SQL查询同时执行，可能会导致数据库连接池的连接被全部占用，导致数据连接池打满、缓冲区溢出等问题，使数据库无法响应其他请求慢。
- 还有可能造成锁竞争增加、数据不一致等问题。
常见慢 SQL 产生的原因
- 缺乏索引/索引未生效，导致数据库全表扫描，会产生大量的IO消耗，产生慢SQL。
- 单表数据量太大，会导致加索引的效果不够明显。
- SQL语句书写不当，例如join或者子查询过多、in元素过多、limit深分页问题、order by导致文件排序、group by使用临时表等。
- 数据库在刷“脏页”，redo log写满了，导致所有系统更新被堵住，无法写入了。
- 执行SQL的时候，遇到表锁或者行锁，只能等待锁被释放，导致了慢SQL。
如何发现慢 SQL
一般公司都会开启数据库的慢 SQL 日志，或者可以在 ORM 框架拦截记录 SQL 执行时间并结合使用监控发现具体的慢 SQL case。
如何治理
由于系统比较多，遗留了大量慢 SQL。直接进入治理工作量比较大，所以需要分批处理，那么应该如何分呢？
我们采用的是集团在线数据平台提供的能力，集团采集慢 SQL后，会根据 SQL 的执行次数（频率）、扫描行数、执行时长确定一个优先级。分为高危、中危、低危等。我们可以根据优先级依次分批处理。
最常见的问题就是查询没有建立对应的索引，这里列举几个在治理过程发现的 case 和解决方案（表名等脱敏处理）：
- case 1: select A, B, C from source_table limit 103300,100;
平均扫描W级数据，实际每次返回100行，最大耗时 2s +；
定时任务处理线索表，业务需要每天遍历几十万行数据，出现深分页问题
解决方案：select * from ins_sale_clue where id between start_id and end_id;
改为指定 ID 滚动处理，ID 批自增 100。
-
case 2: select A, B, C from order where plate_number like ‘%123%’ and … ;
业务上需要通过车牌号模糊查询对应单据，数据量比较大查询十分低效。
解决方案：业务上无法接受仅后缀匹配，于是考虑切为通过 ES 查询，场景是车牌号，直接分词的话无法支持模糊查询，例如 苏A12333 ⇒ [”苏”, “A12333”]。
这里采用 analysis-ngram-tokenizer 分词器，实现对字符串进行完整的切割，这里配置分词器：
"min_gram": 4, // 最短分词长度，业务至少需要输入4个字符 "max_gram": 8, // 最长分词长度 "token_chars": [ // 对哪类字符分词 "letter", "digit" ]效果如 苏A12333 ⇒ [”苏A12”, “苏A123”, “A123”, “1233”, “2333”… “苏A12333”]。
-
case 3: select A, B, C from stock_out_table where stock_out_qty > stock_need_qty and store = xxx;
出库数量大于需求数量即需要退库的数量，即这里要查询的是需要退库的单据，使用列值进行计算，虽然用到门店 ID 的索引，但是门店数据量最大也有百万级，耗时高达 10s +。
解决方案：由于没有维护状态或者待退库数量的字段，导致只能间接查询退库单据。所以这里选择新增待退库数量字段，修改索引为 门店ID + 待退库数量，另外需要修改代码来支持。
思考
以上是存量慢 SQL 的治理方案，特点是量大、能够被监控，但是治理中的风险会比较大。那么对于增量，比如开发时新增的可能有风险的慢 SQL 要如何治理呢？
笔者目前能想到的方案是通过监控开发环境新部署的代码，diff 主干代码，对于新增的 SQL 进行解析，然后匹配事先制定好的规则，比如是否执行索引、是否发生 filesort、rt 检测、表字段是否符合规范等等。
总结
慢SQL可能引起很严重的系统性能问题，影响系统可用性和稳定性，因此，及时发现和治理慢SQL是十分重要的。治理过程中需要对 SQL 运行的机制有一定了解，才能够快速定位原因，使用合理的解决方案。