关于慢SQL和执行计划

如何定位慢SQL

注: 数据库的版本不同,操作可能不同(本文章使用5.7版本)
查看慢查询日志,确定已经执行完的慢查询

使用慢SQL日志:

1. 开启慢查询日志(一般默认是关闭状态)
2. 设置慢查询阈值(响应速度是多长时间被定为是慢查询)
3. 确定慢查询日志路径
4. 确定慢查询日志的文件,然后对文件内容进行分析

• 查看慢查询日志的打开状态

  show variables like ‘%slow_query_log%’;

• 如何开启慢查询日志

  set global slow_query_log=ON

• 查看默认慢查询阈值 (默认为10秒中)

  how variables like ‘%long_query_time%’;

• 如何设置慢查询的阈值
  MySQL5.7

  set long_query_time = 1;

• 如何知道慢查询日志路径

  show global variables like ‘datadir’;

• 如何知道慢查询日志的文件名

  show global variables like ‘slow_query_log_file’;

慢查询日志分析:

Time：慢查询发生的时间
User@Host：客户端用户和IP
Query_time：查询时间
Lock_time：等待表锁的时间
Rows_sent：语句返回的行数
Rows_examined：语句执行期间从存储引擎读取的行数

执行计划

执行计划是mysql优化器对SQL进行默认调优后，给出的一种执行方案，这个方案我们可以通过explain
这个指令进行查询。

分析执行计划的目的

• 检查关联查询的执行顺序
• 查询操作的具体类型
• 哪些索引可能会命中以及实际命中的索引有哪些
• 每张表可能有多少条记录参与到了查询中

执行计划中相关字段说明

• id

  select 的序列号，有几个 select 就有几个 id，id 的顺序是按 select 出现的顺序增长的。
  即：id 越大执行优先级越高，id相同则从上往下执行，id 为NULL最后执行。

• select_type

  1. SIMPLE ： 表示查询语句不包含子查询或 union
  2. PRIMARY：表示此查询是最外层的查询
  3. UNION：表示此查询是 union 的第二个或后续的查询
  4. UNION RESULT：union 的结果
  5. DEPENDENT UNION：子查询中的UNION操作，UNION后的所有select都是DEPENDENT UNION。
  6. SUBQUERY：SELECT 子查询语句
  7. DEPENDENT SUBQUERY：子查询中的第一个SELECT,SELECT 子查询语句依赖外层查询。
  8. DERIVED: from 子句后的相对比较复杂查询

• type
type是一个比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。
  常用属性值如下，从上至下效率依次增强

  1. ALL：表示全表扫描，性能最差。
  2. index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
  3. range：表示使用索引范围查询。使用 >、>=、<、<=、in 等等。
  4. index_merge: 表示查询中实用到了多个索引
  5. ref：表示使用非唯一索引进行单值查询。
  6. eq_ref：一般情况下出现在多表 join 查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
  7. const：表示使用主键或唯一索引做等值查询，常量查询。
  8. NULL：表示不用访问表，速度最快。

• Extra
  Extra 表示很多额外的信息，各种操作会在 Extra 提示相关信息，常见几种如下：

1. Using where 表示查询需要通过where条件查询数据(可能没有用到索引,也可能一部分用到了索引)
2. Using index 表示查询需要通过索引，索引就可以满足所需数据(不需要再回表查询，这里出现了索引覆盖)。
3. Using filesort 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有 Using filesort 建议优化。
4. Using temprorary 查询使用到了临时表，一般出现于去重、分组等操作(这里一般也需要优化)。
5. Using index condition 表示查询的记录，在索引中没有完全覆盖(可能要基于where或二级索引对应的主键再次查询)。