1。后台慢查询是指在数据库中查询时间超过指定阈值的SQL。这类SQL通常伴随着执行时间长、服务器资源占用率高、业务响应慢等负面影响。随着携程酒店业务的不断扩大，加上大量SQLServer转MySQL项目的推进，慢查询数量快速增长，每天的告警数量也居高不下。因此，慢查询的管理和优化迫在眉睫。本文主要针对MySQL。二、慢查询治理实践1、SQL线上流程优化之前的流程发布是比较快的，但是随着质量低劣的SQL发布/迁移越来越多，告警和回滚的次数也越来越多。综合来看，数据库风险方面不容乐观，流程有待优化。相比老流程，新增SQLReview环节，提前筛选和优化潜在的慢查询，保证线上SQL质量。在这个流程的保证下，从上线到生产的所有SQL性能都可以在DBA的可控范围内进行评估，研发提交审核后，会收到审批事件表。携程目前有自动化点评点评平台，但由于酒店业务场景复杂，研发对SQL的理解参差不齐，平台给出的建议也不能全面，所以在过程中没有得到广泛应用。作为参考。2、理解查询语句优化慢查询，首先要知道慢查询是如何产生的，执行计划是什么，最后才考虑如何优化查询。1）SQL流程和查询优化器一个sql的执行主要分为如图所示的几个步骤：SQL语法缓存查询（QC）语法分析（SQL写法、关键字语法等）生成执行计划执行查询输出结果通常慢查询都发生在“执行查询”这一步。了解查询计划可以有效帮助我们分析SQL性能不佳的原因。2）执行计划在SQL前面加上EXPLAIN可以查看执行计划。计划以“表格”的形式展示：具体字段的含义可以参考MySQL官方解释，这里不再赘述。3、优化慢查询问题可以通过执行计划来定位，通常可以分为这几种常见的原因。1）索引级别①缺少索引由于name字段缺少索引，该查询进行了全表扫描：select*fromhotelwherename='xc';添加索引后提示使用索引。②索引失效如图所示，索引失效的主要原因可以分为八类。在这些场景下，通过查看执行计划，会发现产生了type=ALL或者type=index的全表扫描。Like,or,non-operator,functionexplainselect*fromhotelwherenamelike'%hotel%';解释select*fromhotelwherenamelike'%hotel%'orBookable='T';解释select*fromhotelwherename<>'酒店';解释select*fromhotelwheresubstring(name,1,2)='hotel';parametertypedoesnotmatchcreatetablet1(col1varchar(3)primarykey)engine=innodbdefaultcharset=utf8mb4;t1table的col1是varchar类型，但是传入的参数是numeric类型，导致出现不可见转换，索引失效导致type=index的全表扫描。如果联合索引的WHERE条件不符合“最左匹配原则”，索引就会失效。altertablehotel添加索引idx_hotelid_name_isdel(hotelid,name,status);以下条件可以命中联合索引：explainselect*fromhotelwherehotelid=10000andname='ctrip'andstatus='T';说明select*fromhotelwherehotelid=10000andname='ctrip';解释select*fromhotelwherehotelid=10000;但以下情况不能使用联合索引：explainselect*fromhotelwherename='ctrip'andstatus='T';说明select*fromhotelwherename='ctrip';解释select*fromhotelwherestatus='T';数据分布和数据量索引字段的数据分布不均匀，当表数据量过小时，MYSQL查询优化器可能会认为返回的数据量本身很大，通过索引扫描。这时候选择全表扫描的权重会增加很多。③不带where条件查询不带where条件直接查询\修改全表是非常危险的操作。如果表的数据量足够大，可以尝试拆分成多个批量操作。优化过程中遇到的案例：某天发现某DB服务器IO异常，服务器链接开始堆积，导致大量应用报错。监控显示此时repldelay已经25分钟，集群几乎处于无高可用状态，非常危险。登录服务器进行排查，发现有一条删除整表的SQL贯穿JOB系统运行。表数据量大：-tarpresqls"deletefromXXXXXX"最后紧急杀掉这条SQL后恢复正常。在生产中直接删除整个表。非常危险的操作。④强制使用索引MySQL中有forceindex()和ignoreindex()方法来强制使用/忽略特定的索引。这种方式可能会导致执行计划选择不到最优的索引，导致计划出现偏差。⑤性能差的索引的IndexMergeIndexmerge方法可以使用多个索引对同一张表进行条件扫描，检索多个范围扫描，并将结果合并为一个。但是，当如图所示的两个索引字段分布很差时（status和bookable区分度很低），两个索引的结果集有大量数据需要合并，而性能会变得很差。2）SQL频率业务代码的while和for循环的结束条件不正确，导致模块死循环。业务逻辑本身有高并发场景，如秒杀、短期促销活动、直播带货等通过定时JOB循环拉取全量数据，但是循环的并发节奏不好控制，缓存被打断，业务代码释放后缓存失效等，导致大量请求直接打到db3）写法不规范①分页写法最常见的分页写法是使用limit，当分页查询，我们会在LIMIT后传递两个参数，一个是偏移量（offset），另一个是要获取的item个数（limit）。当偏移量小时，查询速度很快，但是随着偏移量变大，查询速度越来越慢。MySQLLimit语法格式：SELECT*FROMtableLIMIT[offset,]rows|rowsOFFSEToffset比如下面的分页查询：当limit只有0、10的时候，执行还是很快的，但是随着offset的增加，可以看到分页很深的情况下，分页越深，越多要扫描的行，性能越差。解释select*fromtestlimittableorderbyidlimit1000,10;解释select*fromtestlimittableorderbyidlimit10000,10;解释select*fromtestlimittableorderbyidlimit20000,10;解释select*fromtestlimittableorderbyidlimit0,310;解释select*fromtestlimittableorderbyidlimit40000,10;解释select*fromtestlimittableorderbyidlimit50000,10;解释select*fromtestlimittableorderbyidlimit60000,10;*:警惕分页循环batching,limit和deeppagination的逻辑无法达到将大量数据拆分成几个小部分的效果。批处理可以使用分段拉取来减少扫描的行数。如果分段拉不连续，可以传给上一个拉。最大值作为下一次的起始值：②最大值和最小值写入由于where条件的字段数据分布问题，max和min的查询会很慢：说明selectmax(id)fromhotel其中hotelid=10000和status='T'；由于hotelid=10000的数据分布比较大，可以看出扫描次数非常多：添加联合索引altertablehoteladdindexidx_hotelid_status(hotelid,status);indexcoverage下，额外提示Selecttablesoptimizedaway，意思是查询执行时不需要读表，直接通过索引返回结果。重写为orderbymethodexplainselectidfromhotelwherehotelid=10000andstatus='T'orderbyiddesclimit1;扫描次数很少，虽然是type=index的索引扫描，但是由于MYSQL对limit的优化，实际上不会进行全表扫描。③排序聚合的写法通常SQL会在使用Groupby和Orderby后生成临时表和文件排序操作。如果查询条件的数据量非常大，temporary和filesort都会产生额外的巨大开销。使用索引满足排序聚合altertablehoteladdindexidx_name_hotelid(name,hotelid);这时MYSQL可以通过访问索引取消不可见排序来避免执行文件排序和临时操作。在某些情况下，Groupby会默认实现不可见排序。添加ORDERBYNULL可以取消这种不可见的排序。*注意从MySQL8.0开始，不再存在这种情况，所以不用写ORDERBYNULL4）资源①锁资源在等待读写很热的表上，通常会发生锁资源争用，导致在慢查询的情况下。谨慎使用forupdate查询，尽量保证使用索引进行增删改查，减少并发，避免对同一块数据进行重复修改。2.向客户端发送数据时，网络波动导致查询慢。3.CPU利用率和磁盘IO高的硬件配置经常满载，导致查询慢3.总结慢查询管理是一个长期而冗长的过程。不应该等到SQL超时报错再考虑优化。从一开始就必须建立完整的日常流程体系，才能有效控制慢查询。查询的增长。但经过长期的优化，发现仅从数据库层面进行优化并不能彻底“清除”慢查询，很多痛点都来自于业务逻辑和应用层面本身。这也需要研发工程师重点优化业务逻辑、应用策略，加强数据库培训。写SQL的时候，不要太随意，尽量省事，不然事后优化起来会变得非常困难。
