MySQL Explain 执行计划 慢查询

相关参数

slow-query-log=on
慢查询开关

long_query_time=2
慢查询阈值，超过2秒记入慢查询日志

slow_query_log_file =/data/mysql3306/log/mysql-slow.log

log_quries_not_using_indexes=OFF|ON
如果运行的语句没有使用索引，是否也被当做慢查询记录到慢查询日志中，ON开启，OFF关闭

log_throttle_queries_not_using_indexes=整数类型
生产环境中可能会有很多没有利用索引的查询，如果都记入慢查询日志，数量会相当庞大，这个参数可以限制每分钟写入慢日志的没用索引的查询，默认0（表示无上限），单位为个（或者条）

慢查询语句查看

慢查询的日志中给出了账号，主句，运行时间，锁定时间，返回行等信息，然后根据这些信息来分析此SQL语句出了什么问题。

[root@xxxxx] tail -n 6 ???????/mysql-slow.log 
# Time: 2020-10-13T01:00:15.314956+08:00
# User@Host: root[root] @ localhost []  Id: 47245134
# Query_time: 2.137869  Lock_time: 0.000031 Rows_sent: 359402  Rows_examined: 359402
use 某个库;
SET timestamp=1602522015;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `TABLE_NAME`;

use 某个库;
SET timestamp=1602522015;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `TABLE_NAME`;

mysqldumpslow 工具

随着慢查询日志文件越来越大，直接使用vi，cat命令查看很不方便，这时可以使用内置的mysqldumpslow命令进行分析。先来看看参数

mysqldumpslow --help

--verbose    verbose
--debug      debug
--help       write this text to standard output
-v           verbose
-d           debug
-s ORDER     排序规则（al, at, ar, c, l, r, t),默认是at
             al: average lock time
             ar: average rows sent
             at: average query time
              c: 次数
              l: 锁定时间
              r: 执行后的返回行数
              t: 查询时间  
  -r         逆序
-t NUM       只显示多少条数据，类似于limit
-g PATTERN   与shell中的grep作用相同，查询PATTERN，大小写不敏感

-a           don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           don't subtract lock time from total time

查询出现次数最多的第一个SQL

[root@xxxxx] mysqldumpslow -s r -t 1 慢查询文件
Reading mysql slow query log from /dbdata/mysql3306/log/mysql-slow.log
Count: 771  Time=2.57s (1978s)  Lock=0.00s (0s)  Rows=307384.7 (236993596), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `TABLE_NAME`

平均访问次数最多的20个SQL

mysqldumpslow -s ar -t 20 慢查询文件

平均访问次数最多,并且里面含有left join字符的20条SQL，可以用于查询左连接的SQL

mysqldumpslow -s ar -t  20  -g  "left join" sqldlow.log

如果出现如下错误,说明你要分析的sql日志太大了，请拆分后再分析

Died at /usr/bin/mysqldumpslow line 161, <> chunk 405659

pt-query-digest 工具

mysqldumpslow是mysql自带的，percona-toolkit中的pt-query-digest相比它更加强大，可以捕获线上的SQL语句，对其进行分析，生成慢查询日志的分析报告。

待补充

explain 分析语句

学习引用
https://www.cnblogs.com/danhuangpai/p/8475458.html
https://blog.csdn.net/mu_wind/article/details/107644632
https://www.cnblogs.com/wy123/p/7366486.html
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

可以在SQL前加explain关键词，MySQL会在此查询上设置一个标记，执行时返回其执行计划中的每一步的信息，而非真正执行它。explain返回的结果会有12个字段，分别是

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra

id

反应的时表的读取顺序或查询中执行select子句的顺序

• id相同，可以认为是一组的，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先被执行
• id不同，如果是子查询，id序号会递增，id值越大优先级越高，越先被执行

select_type

表示select的类型，主要用于区别普通查询、联合查询、子查询等复杂查询（相关链接：
- simpe：简单的select查询，查询中不包含子查询或者union
- primary：查询中若包含任何复杂的子部分，最外层查询标记为primary
- subquery：select或where列表中的子查询中的首个select（如果有多个子查询存在）
- derived（衍生）：在from列表中包含的子查询，MySQL会递归执行这些子查询，把结果放到临时表里。
- union：出现在union后的select查询，则被标记为union；若union包含在from子句的子查询中，外层的select将被标记为derived
- union result：union后的结果集，union操作的结果

table

显示这一步所访问数据库的表名称，有时不是真实的表名字，可能是第几步执行的结果的简称。

partitions

type

对表的访问方式，又称“访问类型”。常见的访问类型有ALL,index,range,ref,eq_ref,const,system,NULL(从左到右，性能从查到好)

• ALL：Full Table Scan，为了找到匹配的行，将全表扫描遍历。

• index：Full Index Scan，index与ALL的区别为index只遍历索引树。

• range：索引范围查找，常见于where条件中出现between、<、>等查询。这种索引范围扫描比全表扫描要好，因为它只开始于索引的某一点，而结束于某一点，不用扫描全部索引

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以应该属于查找和扫描的混合体

• eq_ref：类似于ref，区别在于使用的索引是唯一索引，对于每个索引建，表中只有一条记录与之匹配，常见于主键或唯一键索引扫描，简单来说，就是在多表连接中使用primary key或者unique key作为关联条件

• const、system：如果单表中最多只有一个匹配行，查询起来将非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量来处理。例如，根据主键或者唯一索引进行查询。如果表中只有一行记录，那么就是system

• index_merge：where条件中使用了多个索引列进行查询（where name =xxx or age =xxx)

• index_merge：对普通二级索引进行等值查询

• ref_or_null：不用访问表或者索引就能直接得到结果，例如从上一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys：

查询时可能使用的索引

key：

实际使用的索引，如果没有选择索引，那么此列将显示为NULL。若想强制使用或者不使用索引可以在查询中添加FORCE INDEX或者IGNORE INDEX，查询中若使用了覆盖索引（只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度快），则该索引仅出现key列表中

key_len：

索引中使用的字节数，使用的索引字段的最大长度

ref：

表示上述表的连接匹配条件，即哪些列或常量(const)被用于查找索引列上的值（如字段名student.id）

rows：

• 根据表统计信息及索引选用情况，为了找到所需的记录所需要读取的行数（估算的，不是精确值）。
• 注意这个不是结果集里的行数。
• 如果查询优化器使用全表扫描查询，rows 列代表预计的需要扫码的行数。
• 如果查询优化器使用索引执行查询，rows 列代表预计扫描的索引记录行数。
• 使用explain语句查看执行过程时，rows 列表示MySQL根据n-1步的结果集，预估在n步需要扫描多少记录行（比如下方的图中，第一步预估需要扫描P表100W行，但第二步基于第一步的结果集，可能只需要扫描C表1行）
  

filtered：

结果的行占需要读到的行(rows列的值)的百分比,主要用于连接查询中。是一个百分比的值，对于连接查询来说，主要看驱动表的 filtered的值 ，通过 rows * filtered/100 计算可以估算出被驱动表还需要执行的查询次数。

Extra：

非常重要的额外信息

• Using Index：只使用索引树中的信息，而不需要进一步搜索读取实际的行来检索表中的列信息

  1. WHERE条件列上创建有索引，且是索引前导列
  2. 查询列要与条件列在同一棵索引树上，有3种情况：一是查询列即是条件列本身，二是查询列与条件列建立了联合索引，三是查询列是被聚集索引覆盖的列。

• Using where：感觉网上都乱七八糟的，我现在理解就是，查完之后，返回数据之前，根据where条件进行过滤(如果有索引的话早就用了，没索引可用，所有在最后再过滤，一般都是回表全表扫描？)

• Using Index Condition：

• using index & using where：

• using temporary：表示需要使用临时表来存储结果集，常见于order by,group by

• using filesort：当查询中包含order by操作而且无法利用索引完成的排序操作称为“文件排序”，出现using filesort一般是因为order by后的条件导致索引失效

查看优化器改写后的SQL

执行完explain语句后，执行show warningsG;可以查看优化器改写后的,真正执行的SQL

Performance Schema 查看慢查询

show profiles 将来会被performance_schema替代，本来想学习show profiles，还是转为学习。。。。略

通过trace分析优化器如何选择执行计划

首先要打开trace，并设置格式为JSON，设置trace最大能够使用的内存大小，避免使用过程中因为默认内存过小而不能完整显示。（注意都设置为会话级别）

mysql > set optimizer_trace='enabled=on';
mysql > set end_markers_in_json=on;
mysql > set optimizer_trace_max_mem_size=1000000;

执行一次相查询的SQL

mysql > 你需要查的SQL

检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就可以知道MySQL是如何执行SQL的

mysql > SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACEG;