关于作者|xuqi，携程高级数据库工程师，专注于MySQL和分布式数据库的优化、运维；潘大明，携程高级数据库工程师，专注于数据库性能优化和高可用；康楠，携程数据库专家，专注于数据库性能调优领域。1.后台慢查询是指在数据库中查询时间超过指定阈值的SQL。这类SQL通常伴随着执行时间长、服务器资源占用率高、业务响应慢等负面影响。随着携程酒店业务的不断扩大，加上大量SQLServer转MySQL项目的推进，慢查询数量快速增长，每天的告警数量也居高不下。因此，慢查询的管理和优化迫在眉睫。本文主要针对MySQL。2.慢查询治理实践2.1SQL上线流程优化之前流程发布比较快，但是随着越来越多的劣质SQL发布/迁移，告警和回滚的次数也越来越多。综合来看，数据库风险方面不容乐观，流程有待优化。相比老流程，新增SQLReview环节，提前筛选和优化潜在的慢查询，保证线上SQL质量。在这个流程的保证下，从上线到生产的所有SQL性能都可以在DBA的可控范围内进行评估，研发提交审核后，会收到审批事件表。携程目前有自动化点评点评平台，但由于酒店业务场景复杂，研发对SQL的理解参差不齐，平台给出的建议也不能全面，所以在过程中没有得到广泛应用。作为参考。2.2理解查询语句优化慢查询，首先要知道慢查询是如何产生的，执行计划是什么，最后才考虑如何优化查询。SQL流程和查询优化器一条sql的执行主要分为如图所示的几个步骤：SQL语法缓存查询（QC）语法分析（SQL写法、关键字语法等）生成执行计划执行查询输出结果通常是慢查询这一切都发生在“执行查询”这一步。了解查询计划可以有效帮助我们分析SQL性能不佳的原因。在SQL前面加EXPLAIN可以查看执行计划，计划以“表格”的形式展示：具体字段含义可以参考MySQL官方解释，这里不再赘述.2.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()方法来强制使用/忽略特定的索引。这种方式可能会导致执行计划选择不到最优的索引，导致计划出现偏差。IndexMergeofperformanceindex索引合并方法可以使用多个索引对同一张表进行条件扫描，检索多个范围扫描并将结果合并为一个。但是，当如图所示的两个索引字段分布很差时（status和bookable区分度很低），两个索引的结果集有大量数据需要合并，而性能会变得很差。(2)SQL频率业务代码的while和for循环的结束条件不正确，导致模块死循环。业务逻辑本身有高并发场景，比如秒杀、短线促销、直播带货等，全量通过定时JOB循环拉取数据，但是循环的并发节奏控制不好，缓存坏掉，业务代码释放后缓存失效等，导致大量请求直接打到db(3)写法不规范。查询的时候，我们会在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)来自hotelid=10000和status='T'的酒店；由于hotelid=10000的数据分布比较大，可以看出扫描次数非常多：1、添加联合索引altertablehoteladdindexidx_hotelid_status(hotelid,status);在索引覆盖下，额外提示Selecttablesoptimizedaway，这意味着查询执行时不需要读取表，直接通过索引返回结果。2.将其重写为orderbymethodexplainselectidfromhotelwherehotelid=10000andstatus='T'orderbyiddesclimit1;扫描次数很少，虽然是type=index的索引扫描，但是由于MYSQL对limit优化的限制，实际上并没有进行全表扫描。排序聚合的写法通常SQL会在使用Groupby和Orderby后生成临时表和文件排序操作。如果查询条件的数据量非常大，temporary和filesort都会产生额外的巨大开销。A。使用索引满足排序聚合altertablehoteladdindexidx_name_hotelid(name,hotelid);这时MYSQL可以通过访问索引b来避免执行filesort和临时操作。取消隐形排序在某些情况下，Groupby会默认实现隐形排序，可以通过添加ORDERBYNULL来取消。*注意，从MySQL8.0开始，这种情况将不再存在，所以不需要ORDERBYNULL。查询情况。谨慎使用update查询尽量增删改查，保证使用索引减少并发，避免重复修改同一条数据。向客户端发送数据时会出现网络波动。慢查询硬件配置CPU使用率高，磁盘IO经常满载，导致查询慢。总结慢速查询管理是一个长期而漫长的过程。不应该等到SQL超时报错再考虑优化。从一开始就必须建立完善的日常流程体系，有效控制慢查询的增长。但经过长期的优化，发现仅从数据库层面进行优化并不能彻底“清除”慢查询，很多痛点都来自于业务逻辑和应用层面本身。这也需要研发工程师重点优化业务逻辑、应用策略，加强数据库培训。写SQL的时候，不要太随意，尽量省事，不然事后优化起来会变得非常困难。
