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
:只使用索引树中的信息,而不需要进一步搜索读取实际的行来检索表中的列信息WHERE
条件列上创建有索引,且是索引前导列- 查询列要与条件列在同一棵索引树上,有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 warnings\G;
可以查看优化器改写后的,真正执行的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_TRACE\G;