携程酒店慢查询治理之路

​作者简介 | xuqi，携程资深数据库工程师，关注MySQL、分布式数据库的优化、运维；

              潘达鸣，携程资深数据库工程师，关注数据库性能优化、高可用性领域；

              康男，携程数据库专家，关注数据库性能调优领域。

一、背景

慢查询指的是数据库中查询时间超过了指定的阈值的SQL，这类SQL通常伴随着执行时间长、服务器资源占用高、业务响应慢等负面影响。随着携程酒店业务的不断扩张，再加上大量的SQLServer转MySQL项目的推进，慢查询的数量正在飞速增长，每日的报警量也居高不下，因此慢查询的治理优化已经是刻不容缓，此文主要针对MySQL。

二、慢查询治理实践

2.1 SQL上线流程优化

之前的流程发布比较快捷，但是随着质量差的SQL发布\迁移得越来越多，告警和回退数量也随之变多，综合下来，数据库风险方面不容乐观，该流程需要优化。

和旧流程相比，新增了一个SQLReview的环节，将潜在的慢查询提前筛选出来优化，确保上线的SQL质量，在此流程保障下，所有上线到生产的SQL性能都能在DBA评估后的可控范围内，在研发提交审核后，会收到审批的事件单。

携程目前是存在自动化review审核的平台，但是由于酒店业务场景比较复杂，研发对于SQL的理解水平层次不齐，平台给出的建议并不能做到面面俱到，因此还没有被广泛使用于流程中，仅作为一个参考。

2.2 理解查询语句

要优化慢查询，首先要知道慢查询是如何产生的，执行计划是怎么样的，最后考虑如何去优化查询。

SQL流程及查询优化器

一条sql的执行主要分成如图几个步骤：

• SQL语法的缓存查询（QC）
• 语法解析（SQL的编写、关键字的语法之类）
• 生成执行计划
• 执行查询
• 输出结果

通常慢查询都发生在“执行查询”这步，读懂查询计划，可以有效地帮助我们分析SQL性能差的原因。

执行计划

在SQL前面加上EXPLAIN，就可以查看执行计划，计划以“表”的形式展示：

具体字段含义可以参考MySQL官方的解释，这里不多赘述。

2.3 优化慢查询

通过执行计划就可以定位到问题点，通常可以分为这几种常见的原因。

(1) 索引层面

索引缺失

这个查询由于缺少name字段索引，产生了全表扫描：

select * from hotel where name=’xc’;

补上索引之后，提示使用到了索引。

索引失效

如图所示，索引失效的大致原因可以分为八类，这些场景通过查看执行计划都会发现产生type=ALL或者type=index的全表扫描。

Like、or、非操作符、函数

explain select * from hotel where name like '%酒店%';
explain select * from hotel where name like '%酒店%'or Bookable='T';
explain select * from hotel where name  <>'酒店';
explain select * from hotel where substring(name,1,2)='酒店';

参数类型不匹配

create table t1 (
col1 varchar(3) primary key
)engine=innodb default charset=utf8mb4;

t1表的col1为varchar类型，但是参数传入的是数值类型，结果产生了隐形转换，索引失效导致type=index的全表扫描。

联合索引

Where条件不符合“最左匹配原则”，则索引会失效。

alter table hotel add index idx_hotelid_name_isdel(hotelid,name,status);

以下条件均可以命中联合索引：

explain select * from hotel where hotelid=10000 and name='ctrip' and status='T';
explain select * from hotel where hotelid=10000 and name='ctrip';
explain select * from hotel where hotelid=10000;

但是以下条件无法使用到联合索引：

explain select * from hotel where name='ctrip' and status='T';
explain select * from hotel where name='ctrip';
explain select * from hotel where status='T';

数据分布和数据量

索引字段的数据分布不均匀，表数据量过小的情况下，MYSQL查询优化器可能认为返回的数据量本身就很多，通过索引扫描并不能减少多少开销，此时选择全表扫描的权重会提高很多。

查询不带where条件

不带where条件直接查询\修改全表是很危险的操作，表数据量够大的话，尽量拆分成多批次操作。

优化中遇到的案例：

某天发现有一台DB服务器IO异常，服务器链接开始堆积，引发了大量应用报错

监控显示此时repl延迟已经有25分钟，集群几乎处于无高可用状态，非常的危险。

登陆服务器排查后发现有一条全表删除的SQL在通过JOB系统跑，该表的数据量很大:

-tarpresqls "delete from XXXXXX"

最后紧急Kill这条SQL后恢复正常，直接在生产删除全表是很危险的操作。

强制使用索引

MySQL中存在force index()、ignore index（）方式来强制使用/忽略特定的索引。

这种方式可能会导致执行计划选择不到最优的索引，从而导致计划走偏。

性能差索引的Index Merge

Index merge方法可以对同一个表使用多个索引分别进行条件扫描，检索多个范围扫描并将结果合并为一个。

但是，当遇到如图2个索引字段分布都很差的情况时（status与bookable的区分度都很低），2个索引的结果集存在大量数据需要merge，性能就会变得很糟糕。

(2) SQL频率

• 业务代码while、for循环的结束条件不正确，导致模块内产生死循环
• 业务逻辑本身存在高并发场景，例如秒杀、短期促销活动、直播带货等
• 通过定时JOB循环拉取全量数据，但是循环的并发节奏控制不到位
• 缓存被击穿、业务代码发布后缓存失效等原因，导致大量请求直接打到了db

(3) 写法不规范

分页写法

最常见的分页写法就是使用limit，在分页查询时，我们会在 LIMIT 后面传两个参数，一个是偏移量(offset)，一个是获取的条数(limit)。当偏移量很小时，查询速度很快，但是随着 offset 变大时，查询速度会越来越慢。

MySQL Limit 语法格式：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

例如下列分页查询：

当limit只有0，10时，执行还是很快，但是随着offset增加，可以看到深度分页的情况下，分页越深，扫描的行数就越多，性能也就越来越差了。

explain select * from testlimittable order by id limit 1000, 10;
explain select * from testlimittable order by id limit 10000, 10;
explain select * from testlimittable order by id limit 20000, 10;
explain select * from testlimittable order by id limit 30000, 10;
explain select * from testlimittable order by id limit 40000, 10;
explain select * from testlimittable order by id limit 50000, 10;
explain select * from testlimittable order by id limit 60000, 10;

*：警惕通过分页写法来实现循环分批的逻辑，limit深分页实现不了将大量数据拆分成若干小份的效果

分批可以采用分段拉取减少扫描的行数，如果分段拉取不连续的话可以传入上一次拉取最大的值作为下一次的起始值：

最大最小值写法

由于where条件的字段数据分布问题，会导致max和min的查询非常慢：

explain select max(id) from hotel where hotelid=10000 and status='T';

由于hotelid=10000的数据分布比较多，可以看到扫描数很高：

1、添加联合索引

alter table hotel add index idx_hotelid_status(hotelid,status);

在索引覆盖下，extra提示Select tables optimized away，这意味着在查询执行期间不需要读取表，可以通过索引直接返回结果。

2、改写为order by的方式

explain select id from hotel where hotelid=10000 and status='T' order by id desc limit 1;

扫描数很少，虽然是type=index的索引扫描，但是由于MYSQL对limit的优化，实际上并不会全表扫描。

排序聚合写法

通常SQL在使用Group by及Order by后，会产生临时表和文件排序操作。若查询条件的数据量非常大，temporary和filesort都会产生额外的巨大开销。

a.  使用索引来满足排序聚合

alter table hotel add index idx_name_hotelid(name,hotelid);

此时MYSQL可以通过访问索引来避免执行filesort 及temporary操作

b.  取消隐形排序​

在某些情况下，Group by会默认实现隐形排序，通过添加ORDER BY NULL可以取消这种隐形排序。

*注意从MySQL 8.0开始，不会再有这种情况了，因此不需要ORDER BY NULL写法了

(4)资源

锁资源等待

在读写很热的表上，通常会发生锁资源争夺，从而导致慢查询的情况。

• 谨慎使用for update查询
• 增删改尽量保证使用到索引
• 降低并发，避免对同一条数据进行反复的修改

网络波动

往客户端发送数据时发生网络波动导致的慢查询

硬件配置

CPU利用率高，磁盘IO经常满载，导致慢查询

总结

慢查询治理是一个长期且漫长的过程，不应等SQL超时报错后才开始考虑优化，从一开始就要建立完善的日常化流程体系，才能有效的控制慢查询的增长。

但是经过长期优化后发现，仅仅从数据库层面优化，并不能实现慢查询完全“清零”，还有很多的痛点来自于业务逻辑和应用层面本身。这也需要研发工程师着重优化业务逻辑、应用策略，并加强数据库培训，在编写SQL时切勿过于随意，贪图省事，否则事后再优化会变得相当困难。