[https://www.bilibili.com/video/BV1iq4y1u7vj]
个人根据 尚硅谷宋红康Mysql高级视频总结的学习笔记
在数据库调优中，我们的目标就是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式。
1.数据库服务器的优化步骤
小结：
2.查看系统性能参数
在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。 SHOW STATUS语句语法如下：
SHOW [GLOBAL|SESSION] STATUS LIKE '参数';
一些常用的性能参数如下：
• Connections：连接MySQL服务器的次数。
• Uptime：MySQL服务器的上线时间。
• Slow_queries：慢查询的次数。
• Innodb_rows_read：Select查询返回的行数
• Innodb_rows_inserted：执行INSERT操作插入的行数
• Innodb_rows_updated：执行UPDATE操作更新的行数
• Innodb_rows_deleted：执行DELETE操作删除的行数
• Com_select：查询操作的次数。
• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
• Com_update：更新操作的次数。
• Com_delete：删除操作的次数。
若查询MySQL服务器的连接次数，则可以执行如下语句：
SHOW STATUS LIKE 'Connections';
若查询服务器工作时间，则可以执行如下语句：
SHOW STATUS LIKE 'UpLime';
3.统计SQL的查询成本：last_query_cost
一条SQL查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。
如果我们想要查看某条SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL语句所需要读取的页的数量。
student_info 表：
CREATE TABLE `student_info` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`student_id` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`course_id` INT NOT NULL ,
`class_id` INT(11) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：
SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id = 900001;
运行结果（1 条记录，运行时间为 0.042s ）
然后再看下查询上次执行sql的优化器的成本，实际上我们只需要检索一个页即可：
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？
SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id BETWEEN 900001 AND 900100;
运行结果（100 条记录，运行时间为 0.046s ）：
然后再看下查询上次执行sql的优化器的成本，这时我们大概需要进行 20 个页的查询。
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+
你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。
虽然 页数量（last_query_cost）增加了不少 ，但是通过缓冲池的机制，并 没有增加多少查询时间 。
使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。
SQL查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：
- 位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
- 批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的，而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。
如果是经常使用的数据要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。
4.定位执行慢的SQL：慢查询日志
MySQL的慢查询日志，用来记录再MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。
它的只要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题有很大帮助。比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集5秒的sql，结合explain进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件。
4.1开启慢查询日志参数
1.开启slow_query_log
mysql > set global slow_query_log='ON';
然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：
这时慢查询分析已经开启，同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件中。
2.修改long_query_time阈值
接下来我们来看下慢查询的时间阈值设置，使用如下命令：
mysql > show variables like '%long_query_time%';
这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：
#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
mysql > set global long_query_time = 1;
mysql> show global variables like '%long_query_time%';
mysql> set long_query_time=1;
mysql> show variables like '%long_query_time%';
补充：配置文件中一并设置参数
如下的方式相较于前面的命令行方式，可以看做是永久设置的方式。
修改my.cnf文件，[mysqld]下增加或修改参数long_query_time、slow_query_log和slow_query_log_file后，然后重启MySQL服务器。
[mysqld]
slow_quert_log = ON #开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/atguigu-slow.log #慢查询日志的目录和文件名信息
long_query_time = 3 #设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE
如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下。如果不指定文件名，默认文件名为hostname(主机名)-slow.log。
4.2查看慢查询数目
查询当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
除了上述的变量，控制慢查询日志的还有一个系统变量：min_examined_row_limit。这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time的值，那么，这个查询就被记录到慢查询日志中；反之，则不被记录到慢查询日志中。
这个值默认是0。与long_query_time=10合在一起，表示只要查询的执行时间超过10秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。可以根据需要，通过修改“my.ini”文件，来修改查询时长，或者利用set指令，用sql语句修改“min_examined_row_limit”的值。
4.3慢查询日志分析工具：mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
通过mysqldumpslow 可以查看慢查询日志
查看mysqldumpslow的帮助信息
mysqldumpslow --help
mysqldumpslow 命令的具体参数如下：
-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间 （默认方式）
ac:平均查询次数
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；
举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：
mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log
[root@bogon ~]# mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log
Reading mysql slow query log from /var/lib/mysql/atguigu01-slow.log
Count: 1 Time=2.39s (2s) Lock=0.00s (0s) Rows=13.0 (13), root[root]@localhost
SELECT * FROM student WHERE name = 'S'
Count: 1 Time=2.09s (2s) Lock=0.00s (0s) Rows=2.0 (2), root[root]@localhost
SELECT * FROM student WHERE stuno = N
工作常用参考：
#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more
4.4关闭慢查询日志
MySQL服务器停止慢查询日志功能有两种方法：
方式1：永久性方式
*#配置文件*
[mysqld]
slow_query_log=OFF
或者，把slow_query_log一项注释掉 或 删除
[mysqld] *#slow_query_log =OFF*
重启MySQL服务，执行如下语句查询慢日志功能。
SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长
方式2：临时性方式
使用SET语句来设置。
（1）停止MySQL慢查询日志功能，具体SQL语句如下。
SET GLOBAL slow_query_log=off;
（2）重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下
SHOW VARIABLES LIKE '%slow%';
#以及
SHOW VARIABLES LIKE '%long_query_time%';
4.5删除慢查询日志
使用SHOW语句显示慢查询日志信息，具体SQL语句如下。
SHOW VARIABLES LIKE 'slow_query_log%';
从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下，手动删除慢查询日志文件即可。使用命令mysqladmin flush-log来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件。
mysqladmin -uroot -p flush-logs slow
提示：慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须实现备份。
5.查看 SQL 执行成本：SHOW PROFILE
Show Profile是MySQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。
我们可以在会话级别启动这个功能
mysql > show variables like 'profiling';
通过设置 profiling='ON’ 来开启 show profile：
mysql > set profiling = 'ON';
然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：
show profiles;
你能看到当前会话一共有 2 个查询。如果我们想要查看最近一次查询的开销，可以使用：
show profile;
我们也可以查看指定的Query ID的开销，比如show profile for query 2查询结果是一样的。在SHOW PROFILE中我们可以查看不同部分的开销，比如cpu、block.io等：
mysql> show profile cpu,block io for query 2;
show profile的常用查询参数：
① ALL：显示所有的开销信息。
② BLOCK IO：显示块IO开销。
③ CONTEXT SWITCHES：上下文切换开销。
④ CPU：显示CPU开销信息。
⑤ IPC：显示发送和接收开销信息。
⑥ MEMORY：显示内存开销信息。
⑦ PAGE FAULTS：显示页面错误开销信息。
⑧ SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
⑨ SWAPS：显示交换次数开销信息。
日常开发需注意的结论：
①converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
③Copying to tmp table on disk：把内存中临时表复制到磁盘上。
④locked。
如果在show profile诊断结果中出现了以上5条结果中的任何一条，则sql语句需要优化。
注意：不过SHOW PROFILE命令将被弃用，我们可以用information_schema中的profiling数据表进行查看。
6.分析查询语句：EXPLAIN
定位了查询慢的SQL之后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。
MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划。我们可以根据EXPLAIN语句的各个输出项，有针对性的提升我们查询语句的性能。
6.1能做什么？
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
版本情况：
MySQL 5.6.3以前只能 EXPLAIN SELECT；MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE，DELETE
在5.7以前的版本中，想要显示 partitions 需要使用 explain partitions 命令；想要显示
filtered 需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。
基本语法
EXPLAIN 或 DESCRIBE语句的语法形式如下：
EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options
如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN，就像这样：
EXPLAIN SELECT 1;
EXPLAIN 语句输出的各个列的作用如下：
6.2EXPLAIN各列作用
①table
不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。
②id
在写的查询语句一般都以 SELECT 关键字开头，比较简单的查询语句里只有一个 SELECT 关键字，比如下边这个查询语句：
SELECT * FROM s1 WHERE key1 = 'a';
稍微复杂一点的连接查询中也只有一个 SELECT 关键字，比如：
SELECT * FROM s1 INNER JOIN s2
ON s1.key1 = s2.key1
WHERE s1.common_field = 'a';
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
查询优化器可能对涉及子查询的查询语句进行重写，转成了连接查询（上述语句由查询优化器重写为了多表连接查询，因此只有一个id）
mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
去重操作需要用到临时表空间。
这个语句的执行计划的第三条记录是什么？为何id值是NULL，而且table列也很奇怪？UNION！它会把多个查询的结果集合并起来并对查询结果集中的记录进行去重，怎么去重呢？MySQL使用的是内部的临时表。正如上边的查询计划中所示，UNION子句是为了把id为1的查询和id为2的查询的结果集合并起来并去重，所以在内部创建了一个名为<union1,2>的临时表(就是执行计划第三条记录的table列的名称)，id为NULL表明这个临时表是为了合并两个查询结果集而创建的。
跟UNION对比起来，UNION ALL就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含UNION ALL子句的查询的执行计划中，就没有那个id为NULL的记录。如下所示：
mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
小结:
- id如果相同，可以认为是一组，从上往下顺序执行
- 在所有组中，id值越大，优先级越高，越先执行
- 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好
③select_type
一条大的查询语句里边可以包含若干个SELECT关键字，每个Select关键字代表这一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询的)，每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，他们的id值是相同的。
MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。
#查询语句中不包含UNION或者子查询的查询都算是SIMPLE类型
EXPLAIN SELECT * FROM s1;
#连接查询也算是SIMPLE类型
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
#对于包含UNION或者UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的查询的select_type值就是PRIMARY
#对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION
#MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
#子查询：
#如果包含子查询的查询语句不能够转为对应的semi-join(多表连接)的形式，并且该子查询是不相关子查询。
#该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key FROM s2) or key3 = 'a';
#如果包含子查询的查询语句不能够转为对应的semi-join(多表连接)的形式，并且该子查询是相关子查询。
#该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY
EXPLAIN SELECT * FROM s1 #子查询中使用到了外查询中的表，因此是相关子查询
WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) or key3 = 'a';
#注意的是，select_type为DEPENDENT SUBQUERY的查询可能被执行多次。
#在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
#对于包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED
mysql> EXPLAIN SELECT * FROM (
SELECT key1, count(*) as c FROM s1 GROUP BY key1 #派生表
) AS derived_s1 where c > 1;
#当查询优化器在执行包含子查询的语句时,选择将子查询物化之后与外层查询进行连接查询时
#该子查询对应的select_type属性就是MATERIALIZED
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
④partitions (可略)
如果想详细了解，可以如下方式测试。创建分区表：
-- 创建分区表，
-- 按照id分区，id<100 p0分区，其他p1分区
CREATE TABLE user_partitions (id INT auto_increment,
NAME VARCHAR(12),PRIMARY KEY(id))
PARTITION BY RANGE(id)(
PARTITION p0 VALUES less than(100),
PARTITION p1 VALUES less than MAXVALUE
);
DESC SELECT * FROM user_partitions WHERE id>200;
查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则
⑤type
执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称“访问类型”，其中的type列就表明饿了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用erf访问方法来执行对s1表的查询。
完整的访问方法如下：
systen,const,eq_ref,ref_fulltext,ref_or_null,index_merge,unique_subquery,index_subquery,range,index,ALL。
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的。那么对该表的访问方法就是system。
system
mysql> CREATE TABLE t(i int) Engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t VALUES(1);
Query OK, 1 row affected (0.01 sec)
查询这个表的执行计划：
mysql> EXPLAIN SELECT * FROM t;
const
当我们根据主键或者唯一 二级索引列与常数进行等值匹配时，对单表的访问方法就是const
mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;
eq_ref
在连接查询时，如果被驱动表时通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动被访问方法就是eq_ref
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
从执行计划的结果中可以看出，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问方法是 eq_ref ，表明在访问s1表的时候可以 通过主键的等值匹配来进行访问。
- ref
当通过普通的二级索引与常见进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
-
ref_or_null
当对普通二级索引进行等值匹配查询，该所以咧的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
- index_merge
单表访问方法时在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
从执行计划的 type 列的值是 index_merge 就可以看出，MySQL 打算使用索引合并的方式来执行对 s1 表的查询。
- unique_subquery
unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_ubquery
mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';
- range
如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法。
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
或者：
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
- index
当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index。
mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
- ALL
全表扫描
mysql> EXPLAIN SELECT * FROM s1;
一般来说，这些访问方法中除了ALL这个访问方法外，其余的访问方法都能用到索引，除了index_merge访问方法外，其余的访问方法都最多只能用到一个索引。
小结：
结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL其中比较重要的几个提取出来。
SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）
⑥possible_keys和key
在EXPLAIN语句输出的执行计划中，possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询设计到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。如：
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
⑦key_len ☆
实际使用到的索引长度(即：字节数)
帮你检查是否充分的利用上了索引，值越大越好，主要针对联合索引，有一定的参考意义
mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;
mysql> EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
练习：
key_len的长度计算公式：
varchar(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)
⑧ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值。
当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的结构是什么，比如只是一个常数或者是某个列。
当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
比如只是一个常数或者某个列。
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
⑨rows ☆
预估的需要读取的记录条数，值越小越好
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
⑩filtered
某个表经过搜索条件过滤后剩余记录条数的百分比
如果使用的是索引执行的单表扫描，那么计算时需要顾及出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数(即rows * filtered)
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE
s1.common_field = 'a';
①①Extra
Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL道理将如何执行给定的查询语句。
- No tables used
当查询语句没有FROM子句时将会提示该额外信息。
mysql> EXPLAIN SELECT 1;
- Impossible WHERE
查询语句的WHERE子句永远为FALSE时将会提示该额外信息
mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
- Using where
当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，，在Extra列中会提示上述额外信息
mysql> EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
#当使用索引访问来执行对某个表的查询，并且该语句的WHERE子句中有除了该索引包含的列之外的其他搜索条件时，在Extra列中也会提示上述额外信息。
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
- No matching min/max row
当查询列表处有MIN或者MAX聚合函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息
mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg'; #表中没有key1='abcdefg'的数据
- Using index
当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在Extra列将会提示该额外信息。毕方说下边这个查询中只需要用到idx_key1而不需要回表操作。
mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
- Using index condition
有些搜索条件中虽然出现了索引列，但却不能使用到索引，索引下推
SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';
- Using join buffer (Block Nested Loop)
在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名为join buffer的内存来加快查询速度，也就是我们所讲的基于块的嵌套循环算法
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
- Not exists
当我们使用左(外)连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值，那么在该表的执行计划的Extra列就会提示Not exists额外信息
mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
- Using intersect(…) 、 Using union(…) 和 Using sort_union(…)
如果执行计划的Extra列出现了Using intersect(…)提示，说明准备使用Intersect索引合并的方式执行查询，括号中…表示需要进行索引合并的索引名称，
如果出现Using union(…)提示，说明准备使用Union索引合并的方式执行查询。
出现了Using sort_union(…)提示，说明准备使用Sort-Union索引合并的方式执行查询。
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
- Zero limit
当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息
mysql> EXPLAIN SELECT * FROM s1 LIMIT 0;
- Using filesort
很多那个框下排序操作无法使用到索引，只能在内存中(记录较少的时候)或者磁盘中(记录较多的时候)进行排序，MySql把这种在内存中或者磁盘上进行排序的方式统称为文件排序(filesort)。
如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示
mysql> EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
- Using temporary
在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示。
mysql> EXPLAIN SELECT DISTINCT common_field FROM s1;
再比如：
mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
mysql> EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
从 Extra的 Using index 的提示里我们可以看出，上述查询只需要扫描 idx_key1索引就可以搞定了，不再需要临时表了。
小结:
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况部分统计信息是估算的，并非精确值
扫一扫
555
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
