MySQL 慢SQL的排查分析及无法匹配索引的情况

一、慢SQL的排查方法

1.1 AWR报告-TOP SQL

可以关注 扫描数据量大的SQL，通常伴随着CPU的升高。具体可以关注以下指标：

指标	说明
SQL ordered by Rows Examined Average	扫描记录平均数，常用于定位单条占用CPU资源较多、存在明显性能问题的SQL
SQL ordered by Rows Examined	扫描记录数（总和），常用于定位总体占用CPU资源较多的SQL
SQL ordered by Elapsed Time Average	执行时间（平均），单条执行时间长的SQL，但不一定有性能问题，可能是受害者
SQL ordered by Elapsed Time	执行时间（总和）
SQL ordered by Executions	执行次数
SQL ordered by Rows Affected	影响记录数（总和），插入、更新、删除的记录数
SQL ordered by Rows Sent	查询返回记录数（总和）
SQL ordered by Errors Produced	执行失败次数（总和）
SQL ordered by Warnings Produced	执行报警测试（总和）

1、SQL ordered by Rows Examined Average平均扫描记录数高，但平均返回（Rows Send avg）少说明SQL效率存在问题
2、SQL ordered by Rows Examined SQL扫描记录数高，会占据大量CPU
3、SQL ordered by Elapsed Time Average SQL执行时间超过1秒的都需要关注

1.2 慢日志($MYSQL_LOGDIR/slow_queries.log)

1. 记录执行结束时间、用户及ip、耗时、返回记录数、扫描记录数、SQL原文
2. 优点：a.记录ip、用户、时间点、SQL原文，
   b.不受JDBC参数useCursorFetch或useServerPrepStmus的影响，记录所有的SQL
3. 缺点：只记录执行时间超过n秒的SQL，n=1

1.3 show processlist/AWR-Sessions、Session History连接运行情况

事中：show [full] processlist
事后：AWR-Sessions
Session History:当时每个连接最近执行的10条SQL

二、慢SQL的分析

2.1 explain 分析SQL执行计划

explain执行结果关注重点
type:

1. system 该表只有一行，例如系统表，也是const的一种情况
2. const该表最多只匹配一行，通常在主键、唯一索引比较时使用
3. eq_ref该表每次与前一个表进行关联时，只匹配一行，通常连接的字段是主键、唯一索引，所有索引字段都使用到，运算符是=
4. ref 普通索引或联合索引的前缀匹配
5. ALL 使用全表扫，需要优化
6. index 全索引扫描效率也比较低
   rows:预估扫描记录数，数字越大，效率越低

2.2 常见无法匹配索引的情况

1.查询条件在字段上进行了数学或函数运算（包括隐式数据转换）或者是表连接的字段类型不一致
2.没有匹配复合索引的第一个字段
复合索引是从第一个索引列开始，从左到右依次匹配，一旦某个字段不能匹配，便停止匹配，后边的索引字段不会再匹配，即使覆盖了其他索引列也无法利用索引
3.涉及表连接的表字符集（character set）、排序规则（COLLATION)不同
4.查询条件最左以通配符%开始，例如 where var like ‘%123’
5.查询条件使用负向查询条件:NOT!=<> not like 等
6.使用子查询update、delete语句