做者介绍
xuqi，携程资深数据库工程师，存眷MySQL、散布式数据库的优化、运维；
潘达鸣，携程资深数据库工程师，存眷数据库性能优化、高可用性范畴；
康男，携程数据库专家，存眷数据库性能调优范畴。
一、布景
慢查询指的是数据库中查询时间超越了指定的阈值的SQL，那类SQL凡是陪伴着施行时间长、办事器资本占用高、营业响应慢等负面影响。跟着携程酒店营业的不竭扩大，再加上大量的SQLServer转MySQL项目标推进，慢查询的数量正在飞速增长，每日的报警量也居高不下，因而慢查询的治理优化已经是刻不容缓，此文次要针对MySQL。
二、慢查询治理理论
1、SQL上线流程优化
之前的流程发布比力快速，但是跟着量量差的SQL发布\迁徙得越来越多，告警和回退数量也随之变多，综合下来，数据库风险方面不容乐不雅，该流程需要优化。
展开全文
和旧流程比拟，新增了一个SQLReview的环节，将潜在的慢查询提早挑选出来优化，确保上线的SQL量量，在此流程保障下，所有上线到消费的SQL性能都能在DBA评估后的可控范畴内，在研发提交审核后，会收到审批的事务单。
携程目前是存在主动化review审核的平台，但是因为酒店营业场景比力复杂，研发关于SQL的理解程度条理不齐，平台给出的定见其实不能做到八面玲珑，因而还没有被普遍利用于流程中，仅做为一个参考。
2、理解查询语句
要优化慢查询，起首要晓得慢查询是若何产生的，施行方案是怎么样的，最初考虑若何去优化查询。
1）SQL流程及查询优化器
一条sql的施行次要分红如图几个步调：
SQL语法的缓存查询（QC）
语法解析（SQL的编写、关键字的语法之类）
生成施行方案
施行查询
输出成果
凡是慢查询都发作在“施行查询”那步，读懂查询方案，能够有效地搀扶帮助我们阐发SQL性能差的原因。
2）施行方案
在SQL前面加上EXPLAIN，就能够查看施行方案，方案以“表”的形式展现：
详细字段含义能够参考MySQL官方的阐明，那里不多赘述。
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的数据散布比力多，能够看到扫描数很高：
添加结合索引
alter table hotel add index idx_hotelid_status(hotelid,status);
在索引笼盖下，extra提醒Select tables optimized away，那意味着在查询施行期间不需要读取表，能够通过索引间接返回成果。
改写为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城市产生额外的庞大开销。
利用索引来称心排序聚合
alter table hotel add index idx_name_hotelid(name,hotelid);
此时MYSQL能够通过拜候索引来制止施行filesort 及temporary操做
取缔隐形排序
在某些情状下，Group by会默认实现隐形排序，通过添加ORDER BY NULL能够取缔那种隐形排序。
*留意从MySQL 8.0起头，不会再有那种情状了，因而不需要ORDER BY NULL写法了
4）资本
①锁资本期待
在读写很热的表上，凡是会发作锁资本争夺，从而招致慢查询的情状。
隆重利用for update查询
增删改尽量包管利用到索引
降低并发，制止对统一条数据停止频频的修改
②收集颠簸
往客户端发送数据时发作收集颠簸招致的慢查询
③硬件设置装备摆设
CPU操纵率高，磁盘IO经常满载，招致慢查询
三、总结
慢查询治理是一个持久且漫长的过程，不该等SQL超时报错后才起头考虑优化，从一起头就要成立完美的日常化流程系统，才气有效的掌握慢查询的增长。
但是颠末持久优化后发现，仅仅从数据库层面优化，其实不能实现慢查询完全“清零”，还有良多的痛点来自于营业逻辑和应用层面自己。那也需要研发工程师着重优化营业逻辑、应用战略，并加强数据库培训，在编写SQL时切勿过于随意，妄想省事，不然过后再优化会变得相当困难。
做者丨xuqi 潘达鸣 康男
来源丨公家号：携程手艺（ID：ctriptech）
dbaplus社群欢送广阔手艺人员投稿，投稿邮箱：editor@dbaplus.cn
关于我们
dbaplus社群是围绕Database、BigData、AIOps的企业级专业社群。资深大咖、手艺干货，每天精品原创文章推送，每周线上手艺分享，每月线下手艺沙龙，每季度GdevopsDAMS行业大会。
存眷公家号【dbaplus社群】，获取更多原创手艺文章和精选东西下载