背??景2019-01-119:00-10:00一个MySQL数据库正在占满CPU。硬件配置：256G内存，48核分析进程接管这个问题的时候现场已经不在了，信息有限，还是从监控系统上看看当时的情况吧。从PMM监控来看，每天早上9点这个MySQL实例的CPU会增加到10%-20%。只有1月2日和1月11日，CPU达到100%，也就是今天的故障。疑似九点业务迫于压力释放，调查方向为慢查询。1、根据执行次数统计slowlog，找出最频繁的sql：mysqldumpslow-scslow.log>/tmp/slow_report.txtCount:3276Time=21.75s(71261s)Lock=0.00s(1s)Rows=0.9(2785),xxxSELECTT.TASK_ID,T.xx,T.xx,...FROMT_xx_TASKTWHEREN=NANDT.STATUSIN(N,N,N)ANDIFNULL(T.MAX_OPEN_TIMES,N)>IFNULL(T.OPEN_TIMES,N)AND(T.CLOSE_DATEISNULLORT.CLOSE_DATE>=SUBDATE(NOW(),INTERVAL'S'MINUTE))ANDT.REL_DEVTYPE=NANDT.REL_DEVID=NANDT.TASK_DATE>='S'ANDT.TASK_DATE<='S'ORDERBYTASK_IDDESCLIMITN,N2.在慢日志中找到这个查询记录扫描的行数：“Rows_examined:1161559”，好像是全表扫描，CPU上升一般是因为大量的慢sql，所以接下来分析这个sql3。因为T_xxx_TASK表在现场紧急清理了数据（从110万行到4万行），所以需要使用备份将表恢复到故障前。恢复备份后查看执行计划和执行时间：解释SELECTT.TASK_ID,T.xx,...FROMT_xxx_TASKTWHERE1=1ANDT.STATUSIN(1,2,3)ANDIFNULL(T.MAX_OPEN_TIMES,0)>IFNULL(T.OPEN_TIMES,0)AND(T.CLOSE_DATEISNULLORT.CLOSE_DATE>=SUBDATE(NOW(),INTERVAL'10'MINUTE))ANDT.REL_DEVTYPE=1ANDT.REL_DEVID=000000025xxxANDT.TASK_DATE>='2019-01-11'ANDT.TASK_DATE<='2019-01-11'ORDERBYTASK_IDDESCLIMIT0,20;执行时间10s+:1rowinset(10.37sec)tableindexinformation:showindexfromT_xxx_TASK;看到这里，基本可以确定是这条SQL引起的，因为执行一次需要10s+，而且在那个时间点，会并发大量的这条SQL。但是这里有几个坑：1.执行计划明明用了索引，为什么还是执行的这么慢？2、执行计划显示扫描行数为644，为什么慢日志显示超过100万行？A。我们先看执行计划，选择索引“INDX_BIOM_ELOCK_TASK3(TASK_ID)”。结合SQL，因为“ORDERBYTASK_IDDESC”子句，排序通常很慢??。如果使用文件排序，性能会更差。优化器选择这个索引来避免排序。那为什么不选择possible_keys:INDX_BIOM_ELOCK_TASK呢？原因也很简单。TASK_DATE字段的区分度太低。该索引需要扫描大量行，需要额外排序。优化器的综合判断成本比较昂贵，所以没有选择这个指标。但是，如果我们强行选择这个索引（使用forceindex语法），我们会看到SQL执行速度更快，不到10s，这是因为优化器基于成本的原则并不等同于执行速度；b.看执行计划中的type:index，“index”代表“fullindexscan”，类似于全表扫描，只是扫描不是按照行的顺序而是按照索引的顺序。主要优点是避免了排序，但是开销还是很高。Extra：使用where也意味着扫描索引后需要回表进行过滤。一般来说，type至少要保证达到range级别，最好能达到ref。第2点中提到的“slowlogrecordRows_examined:1161559，好像是全表扫描”，这里更正为“全索引扫描”，扫描的行数确实等于表中的行数;C。关于执行计划：“rows:644”，其实这只是一个估计值，并不准确。我们在分析慢SQL时，可以根据慢日志中的Rows_examined来判断准确的扫描行数。4、优化建议：增加一个复合索引IDX_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)优化过程：TASK_DATE字段上有一个索引，但是选择性很低，优化器不会使用这个索引。建议稍后删除此索引：selectcount(*),count(distinctTASK_DATE)fromT_BIOMA_ELOCK_TASK;+------------+--------------------------+|计数(*)|计数（不同的任务日期）|+------------+--------------------------+|1161559|223|+------------+------------------------+在这个sql中，REL_DEVIDfield在命名上选择度很高，通过下面的sql确实是这样：selectcount(*),count(distinctREL_DEVID)fromT_BIOMA_ELOCK_TASK;+----------+---------------------------+|计数(*)|计数（不同的REL_DEVID）|+------------+----------------------------+|1161559|62235|+---------+-------------------------+因为排序，task_id也必须是添加到新创建的索引中。REL_DEVID和task_id的组合具有100%的选择性：selectcount(*),count(distinctREL_DEVID,task_id)fromT_BIOMA_ELOCK_TASK;+----------+----------------------------------+|计数(*)|count(distinctREL_DEVID,task_id)|+----------+----------------------------------+|1161559|1161559|+--------+------------------------------------+测试环境添加REL_DEVID、TASK_ID组合索引，测试SQL性能：altertableT_BIOMA_ELOCK_TASKaddindexidx_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID);添加索引后的执行计划：这里还要注意“隐式转换”：REL_DEVID字段数据类型为varchar，需要在sql中加引号：ANDT.REL_DEVID=000000025xxx>>ANDT.REL_DEVID='000000025xxx'执行时间从10s+减少到毫秒级：1rowinset(0.00sec)结论一个典型的orderbyquery优化，添加更合适的索引可以避免性能问题：executeJustbecauseyouplantouseanindexdoesn'tmeanitwillperform快速地
