MySQL数据库调优之 explain的学习

性能分析工具的使用

在数据库调优中，目标就是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮助快速找到调优的思路与方式。
1.数据库服务器的优化步骤
整个流程分为观察(Show status)和行动(Action) 两个部分。字母S的部分代表观察，字母A代表的是行动。
在这里插入图片描述
如果发现执行SQL时存在不规则延迟或者卡顿的情况，就可以采用分析工具定位有问题的SQL。三种分析工具可以理解为SQL调优的三个步骤:慢查询、EXPLAIN和SHOW PFOFILING

2. 查看系统性能参数
在Mysql中，可以使用SHOW STATUS语句查询一些Mysql数据库服务器的性能参数、执行频率。

SHOW [GLOBAL |SESSION] STATUS LIKE ‘参数’；
在这里插入图片描述
查询连接次数

查询慢查询的次数

数据页

页的数量可能是刚才的20倍，但是查询的效率并没有明显的变化，实际上这两个SQL查询的时间基本上是一致的，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量(last_query_cost)增加了不少,但是通过缓冲池的机制，并没有增加多少的查询时间。
使用场景： 对于比较开销是非常有用的，特别是当有好几种查询方式可以选择的时候。
SQL 查询是一个动态的过程，从页加载的角度来看，可以得出以下两个结论：
1.位置决定效率。如果页就在数据库缓冲池中，那么效率是很高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取的效率高很多。
2.批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的，而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读。
所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。首先要考虑数据存放的位置，如果是经常使用的数据就尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率就会得到了提升。
定位执行慢的SQL：慢查询日志
Mysql的慢查询日志，用来记录Mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询的日志中。long_query_time的默认值是10，意思是运行10秒以上（不含10秒）的语句，认为是超过了我们的最大忍耐时间值。
它的主要作用是，帮助我们发现哪些执行时间特别长的SQL查询，并且针对性的进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞，运行变慢的时候，检查一下慢查询日志，找到哪些慢查询，对解决问题很有帮助。比如1条SQL执行超过5秒钟，我们就算慢SQL,希望能收集超过5秒的SQL，结合explain进行全面的分析。
默认情况下，Mysql数据库没有开启慢查询日志，需要手动设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少的带来一定的性能的影响。

在这里插入图片描述
SET GLOBAL slow_query_log=‘on’ 开启慢查询日志，改变一下时间 SET long_query_time=1
除了上述变量，控制慢查询日志的还有一个系统变量：‘min_examined_row_limit’。这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time的值，那么，这个查询就被记录到慢查询的日志中；反之，则不被记录到慢查询的日志中。
在这里插入图片描述
这个值默认是0，与long_query_time=10 结合在一起，表示只要查询的执行时间超过10秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询的日志中。
慢查询日志的分析工具：mysqldumpslow

关闭慢查询日志 set global slow_query_log=off

查看SQL 执行成本： SHOW PROFILE
show profile 是Mysql提供的可以用来分析当前会话中SQL都做了什么，执行的资源消耗情况的工具，可用于SQL调优的测量**。默认情况下处于关闭状态**，并保存最近15次的运行结果。
在这里插入图片描述
执行成本

针对于指定的某条查询 for query XX

show profile 的常用的查询参数

2. 分析查询语句：EXPLAIN

概述
定位了查询慢的SQL后，就可以使用explain或describe工具做针对性的分析查询语句。describe语句的使用方法与explain语句是一样的，并且分析结果也是一样的。
Mysql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算饭呢西系统中收集到的统计信息，为客户端请求的Qurey 提供它认为最优的执行计划（它认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最消耗时间）。
这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么？对于每个表采用什么方法来具体执行查询等。mysql 提供了EXPLAIN 语句可以查看某个查询语句的具体执行计划。
能做些什么？
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

 其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE 语句等都可以加上EXPLAIN，用来查看这些语句的执行计划。

注意：执行EXPLAIN 时并没有真正的执行后面的语句，因此可以安全的查看执行计划
EXPLAIN 语句输出的各个列的作用
在这里插入图片描述
数据脚本准备

# 两张表
CREATE TABLE s1(id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY(id),INDEX idx_key1(key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3(key3),INDEX idx_key_part(key_part1,key_part2,key_part3)) ENGINE=INNODB  CHARSET=utf8;# 两张表  CREATE TABLE s2(id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY(id),INDEX idx_key1(key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3(key3),INDEX idx_key_part(key_part1,key_part2,key_part3)) ENGINE=INNODB  CHARSET=utf8;#函数
#创建随机产生字符串的函数DELIMITER //	
CREATE FUNCTION rand_string(n INT)RETURNS VARCHAR(255) #该函数返回一个字符串
BEGINDECLARE chars_str VARCHAR(100) DEFAULT 'adafqfabfabcafqifqfyhafnkfavbabvavhabdadhabcacakcacavhafqoqnacncabcbabaadaa';DECLARE return_str VARCHAR(255) DEFAULT '';		DECLARE i INT DEFAULT 0;WHILE i < n DOSET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));SET i=i+1;
END WHILE;
RETURN return_str;
END //
DELIMITER;       SELECT @@log_bin_trust_function_creatorsSET  GLOBAL log_bin_trust_function_creators=1#创建随机产生数字的函数
DELIMITER //
CREATE FUNCTION rand_num(from_num INT,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=FLOOR(from_num+RAND()*(to_num-from_num+1));
RETURN i;
END //
DELIMITER;#创建存储过程DELIMITER //CREATE PROCEDURE insert_s1(IN min_num INT(10),IN max_num INT(10))BEGINDECLARE i INT DEFAULT 0;SET autocommit=0;REPEATSET i=i+1;INSERT INTO s1 VALUES((min_num+i),rand_string(6),(min_num+30*i+5),rand_string(6),rand_string(10),rand_string(5),rand_string(10),rand_string(10));UNTIL i=max_numEND REPEAT;COMMIT;
END //
DELIMITER ;DELIMITER //CREATE PROCEDURE insert_s2(IN min_num INT(10),IN max_num INT(10))BEGINDECLARE i INT DEFAULT 0;SET autocommit=0;REPEATSET i=i+1;INSERT INTO s2 VALUES((min_num+i),rand_string(6),(min_num+30*i+5),rand_string(6),rand_string(10),rand_string(5),rand_string(10),rand_string(10));UNTIL i=max_numEND REPEAT;COMMIT;
END //
DELIMITER ;#调用存储过程CALL insert_s1(10001,10000);CALL insert_s2(10001,10000);

EXPLAIN 各列的作用
1.table
不论我们的查询语句有多复杂，里边包含了多少个表，到最后也是需要对每个表进行单表访问的，所以Mysql规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名，可能是简称）
在这里插入图片描述
这个查询语句只涉及到对于s1的单表查询，所以EXPLAIN 输出中只有一条记录，其中的table列的值为s1.用来说明着条记录是对s1表的单表访问方法的。
shang
多表关联查询，上面的驱动表，下面的是被驱动表
2.id
在一个大的查询语句中每个SELECT 关键字都对应一个唯一的id
在这里插入图片描述
查询优化器可能对涉及子查询的查询语句进行重写

id都为1，mysql查询优化器会把嵌套子查询转变为多表查询的操作

union去重
在这里插入图片描述
不需要去重的union all

在这里插入图片描述
id小结:
id 如果相同，可以认为是一组，从上往下的顺序执行
在所有的组中，id值越大，优先级越高，越先执行(子查询)
关注点：id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好

3.select_type
一个大的查询语句里面可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，他们的id是相同的。
mysql为每一个SELECT关键字代表的小查询都定义了一称为select_type的属性，只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色
查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型
连接查询也算是SIMPLE类型
对于包含UNION或者UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的查询的select_type值就是PRIMARY
对于包含UNION或者UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询外，其余的小查询的SELECT_TYPE值就是UNION
子查询：如果包含子查询的查询语句不能够转化为对应的semi-join的形式，并且该子查询是不相关子查询。该子查询的第一个SELECT关键字代表的那个查询的select_type 就是SUBQUERY。

如果包含子查询的查询语句不能够转化为对应的semi-join的形式，并且该子查询是不相关子查询。 则该子查询的第一个SELECT关键字代表的那个查询的SELECT_TYPE 就是DEPENDENT SUBQUERY
在这里插入图片描述
注意的是，select_type为DEPENDENT SUBQUERY的查询可能会被执行多次

在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询外，其余的小查询的SELECT_TYPE 就是DEPENDENT SUBQUERY

EXPLAIN SELECT * FROM s1
WHERE key1 IN (SELECT key1 FROM s2 WHERE key1='a' UNION  SELECT key1 FROM s1 WHERE key1='b');

在这里插入图片描述
其中的in会被SQL优化器转化为exists语句
对于包含派生表的查询，该派生表对应的子查询的select_type 就是DERIVED

EXPLAIN SELECT *
FROM (SELECT key1,COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c>1

在这里插入图片描述
当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type 属性就是MATERIALIZED

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);##子查询被转化为了物化表

在这里插入图片描述
4.partition(略)：匹配的分区信息

5.type 针对单表的访问方法（重要）
执行计划的一条记录就代表着mysql对某个表的执行查询时的访问方法，又称为访问类型。其中的type 列就表明了这个访问方法是啥，是较为重要的一个指标·。比如，type列的值为ref,表明mysql 即将使用ref访问方法来执行对s1表的查询。
当表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory,那么对该表的访问方法就是system
当根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const
在这里插入图片描述
在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是“eq_ref”

在这里插入图片描述
当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就是ref

当对普通二级索引进行等值匹配查询，该索引列的值也可以是“NULL”值时，那么对该表的访问方法就可能是ref_or_null

在这里插入图片描述
单表访问方法时在某些场景下可以使用Intersection\Union\Sort-Union这三种索引合并的方式来执行查询

unique_subquery 是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转化为EXISTS子查询，而且子查询可以使用到主键进行等值匹配比较的话，那么该子查询执行计划的type列的值就是unique_subquery

EXPLAIN SELECT * FROM s1
WHERE key2 IN(SELECT id FROM s2 WHERE s1.key1=s2.key1) OR key3='a'

在这里插入图片描述
如果使用索引获取某些“范围区间”的记录，那么就可能使用到range访问方法

在这里插入图片描述
当可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index

最熟悉的全表扫描

6.possbile_key 和 key 重要
在explain语句的输出执行计划中，possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引被列出，但不一定被查询使用。key 列表表示实际用到的索引有哪些，如果为NULL，则没有使用索引。在这里插入图片描述
key1是个范围，key3是个精确值 把and改成or就是一个并集，属于一个范围，索引就会使用了
EXPLAIN SELECT * FROM s1 WHERE key1>‘z’ OR key3 =‘a’;

7.key_len 实际使用到的索引长度值越大越好
帮你检查是否充分利用上了索引，值越大越好，主要针对于联合索引，有一定的意义
在这里插入图片描述

8.ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
比如只是一个常数或者是某个列
在这里插入图片描述
加一个函数upper后

9 rows 预估的需要读取的记录总数值越小越好

10.filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比
如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条 rowsfiltered的百分比的数据条数
EXPLAIN SELECT * FROM s1 WHERE key1 >‘a’ AND common_field=‘a’;

对于单表查询来说，这个filtered列的值没什么意义。**更需要关注在连接查询中驱动表对应的执行计划的filtered的值，它决定了被驱动表要执行的次数（即：rowsfiltered）**
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1=s2.key1 WHERE s1.common_field=‘a’;

在这里插入图片描述
11 Extra 比较重要 一些额外的信息
更准确的理解Mysql到底将如何执行给定的查询语句
当查询语句没有from 子句时将会提示该额外信息

查询语句的where子句的条件永远为fasle

使用全表扫描来执行对某个表的查询，并且该语句的where子句有针对该表的搜索条件时，在extra列中会提示额外信息
在这里插入图片描述
在使用索引访问来执行对某个表的查询，并且该语句的where子句中有除了该索引包含的列之外的其他搜索条件时，在extra列中也会提示额外的信息
EXPLAIN SELECT * FROM s1 WHERE key1=‘a’

EXPLAIN SELECT * FROM s1 WHERE key1=‘a’ AND common_field=‘a’

在这里插入图片描述
当查询列表处有MIN或MAX的聚合函数的时候，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示额外信息
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1=‘adadaf’;

当查询列表以及搜索条件中包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在Extra列将会提示额外的信息。比如下边的查询中只需要使用到idx_key1而不需要回表的操作
EXPLAIN SELECT key1 FROM s1 WHERE key1=‘a’
在这里插入图片描述
有些搜索条件中虽然出现了索引列，但却不能使用到索引（索引下推）
EXPLAIN SELECT * FROM s1 WHERE key1 >‘z’ AND key1 LIKE ‘%a’;

在连接查询执行过程中，当被驱动表不能有效的利用索引来加快访问速度，mysql一般会为其分配一块叫join buffer的内存块来加快查询速度，也就是基于块的嵌套循环算法
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field=s2.common_field

在这里插入图片描述
当使用左外连接时，如果where子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not Exists的额外信息
EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1=s2.key1 WHERE s2.id IS NULL
在这里插入图片描述

EXPLAIN SELECT * FROM s1 WHERE key1=‘a’ OR key3=‘a’

EXPLAIN SELECT * FROM s1 LIMIT 0

在这里插入图片描述
有一些情况下对结果集中的记录进行排序是可以使用到索引的
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

文件排序 filesort
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

在这里插入图片描述
在许多查询的执行过程中，Mysql可能会借助临时表来完成一些功能。比如去重、排序一类的。比如在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，**如果不能有效利用索引来完成查询，Mysql有可能寻求通过建立内部的临时表来执行查询。**如果查询中使用到了内部的临时表，在执行计划的extra列会显示Using temporary的提示
在这里插入图片描述
本身是有序的状态

小结
Explain 不考虑各种Cache
explain 不能显示Mysql在执行查询过程时所作的优化操作
explain 不会告诉你关于触发器、存储过程的信息或者用户自定义函数对查询的影响情况
部分统计信息是估算的，并非精确值