1. 数据库服务器的优化步骤

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。

整个流程划分成了 观察(Show status) 和 行动(Action) 两个部分。字母 S 的部分代表观察（会使用相应的分析工具）， A 代表行动（对应分析可以采取的行动）。对越底层调整成本越低，但效果却更好

2. 查看系统性能参数

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：删除操作的次数。

3. 统计SQL的查询成本：last_query_cost

使用第8章的 student_info 表为例：

如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：

SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id = 900001;

SHOW STATUS LIKE 'last_query_cost';

我们只需要检索一个页即可:

如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？

SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id BETWEEN 900001 AND 900100;

SHOW STATUS LIKE 'last_query_cost';

大概需要进行 20 个页的查询:

页的数量是刚才的20倍，但是查询的效率并没有明显的变化，因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量(last_query_cost)增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。

使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

SQL 查询是一个动态的过程，从页加载的角度可以得到以下结论：

位置决定效率。如果页就在缓冲池中则效率最高，否则还需从内存或磁盘中读取

批量决定效率。从磁盘中对一页进行随机读，效率极低。而顺序对页进行批量读取，平均一页的效率就提升很多，甚至要快过单页面在内存中的随机读取

常用的数据尽量放到缓冲池，充分利用磁盘吞吐能力，批量读取数据

4. 定位执行慢的 SQL：慢查询日志

慢查询日志用来记录MySQL中响应时间超过阈值的语句， long_query_time的默认值为 10，即运行时间超过10s的语句会被记录到慢查询日志中。

主要作用是帮助我们发现那些执行时间特别长的SQL查询，并进行针对性优化，从而提高系统整体效率。

默认情况没有开启，不是调优需要，不建议启动该参数，会造成性能影响。

4.1 开启慢查询日志参数

开启slow_query_log

set global slow_query_log='ON';

然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：

SHOW VARIABLES LIKE '%slow_query_log%';

2. 修改long_query_time阈值

查看慢查询的时间阈值设置：

show variables like '%long_query_time%';  # 10

这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

#测试发现：设置global的方式只对新连接的客户端有效。所以可以一并执行下述语句 
set global long_query_time = 1; 
show global variables like '%long_query_time%'; 

set long_query_time=1; 
show variables like '%long_query_time%';

或者直接在配置文件中永久设置 my.cnf，记得重启服务器

[mysqld]
slow_query_log=ON # 开启慢查询日志
slow_query_log_file=/var/mysql/slow_query.log # 慢查询日志存储位置
long_query_time=1 # 设置阈值
log_output=FILE

不指定路径，则会默认存储到MySQL数据文件夹下的hostname-slow.log中

4.2 查看慢查询数目

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

补充说明：

除上述变量外，控制慢查询日志的另一个系统变量min_examined_row_limit，即查询扫描过的最少记录数。默认为0。
都按默认情况的话，一条查询称为慢条件：查询时间>10s 并且查询记录数 >= 0

4.3 案例演示

建表：

CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

设置参数 log_bin_trust_function_creators 允许创建函数：

set global log_bin_trust_function_creators=1;

创建函数：随机产生字符串和随机生成数值，与上一节相同，不多赘述。

创建存储过程

DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0; #设置手动提交事务
    REPEAT #循环
    SET i = i + 1; #赋值
    INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
    ((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
    UNTIL i = max_num
    END REPEAT;
    COMMIT; #提交事务
END //
DELIMITER ;

调用存储过程

#调用刚刚写好的函数, 4000000条记录,从100001号开始
CALL insert_stu1(100001,4000000);

4.4 测试及分析

测试：都超过1s了

SELECT * FROM student WHERE stuno = 3455655;
SELECT * FROM student WHERE name = 'IMJmfO';

分析

show status like 'slow_queries';

4.5 慢查询日志分析工具：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/slow_query.log

工作常用参考：

#得到返回记录集最多的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.6 关闭慢查询日志

如果没有调优的话，就关了吧。

4.7 删除慢查询日志

查询日志存在哪了，到该目录下手动删除即可

SHOW VARIABLES LIKE 'slow_query_log%';

重新生成查询日志文件

mysqladmin -uroot -p flush-logs slow

慢查询日志使用该命令删除重建，如果需要旧的日志一定要记得备份！！！

5. 查看 SQL 执行成本：SHOW PROFILE

show variables like 'profiling';
set profiling = 'ON';

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诊断中出现任意一条，SQL语句该优化了。

该命令即将弃用，可以从 information_schema中的profiling表进行查看

看下当前会话都有哪些 profiles，使用下面这条命令：

show profiles;

查看最近一次查询的开销

show profile;

看看自己刚刚那贼慢的查询语句是什么耗时特别多：

show profile cpu,block io for query 2; # 填自己的query数，从profiles里看

原来是执行的慢啊。那我们去看看到底为什么吧！

6. 分析查询语句：EXPLAIN

6.1 基本语法

EXPLAIN SELECT select_options
# 或
DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个`唯一的id`
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

6.2 数据准备

# 建两张表，用来联合索引
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;

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

# 创建函数
DELIMITER //
CREATE FUNCTION rand_string1(n INT)
    RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
        SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

# 创建存储过程 向s1 s2插入数据
DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s1 VALUES(
    (min_num + i),
    rand_string1(6),
    (min_num + 30 * i + 5),
    rand_string1(6),
    rand_string1(10),
    rand_string1(5),
    rand_string1(10),
    rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s2 VALUES(
        (min_num + i),
        rand_string1(6),
        (min_num + 30 * i + 5),
        rand_string1(6),
        rand_string1(10),
        rand_string1(5),
        rand_string1(10),
        rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

CALL insert_s1(10001,10000);
CALL insert_s2(10001,10000);

6.3 EXPLAIN各列作用

1. table：查询每一条记录对应一个单表

不论我们的查询语句有多复杂，包含了多少个表，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

# s1：驱动表 s2：被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

2. id：对应一个SELECT关键字

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
关注点：id号每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);

查询优化器可能对涉及子查询的语句进行重写，将其转变成了多表查询的操作：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

Union去重：在临时表中进行

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

3. select_type

SIMPLE：不包含 UNION 或子查询

包含UNION或子查询的查询是由几个小查询组成的，其中最左边的小查询类型为PRIMARY
若用了UNION，则除最左边的小查询外，其余小查询类型都为UNION
MySQL使用临时表进行UNION的去重，该表的类型为UNION RESULT 若包含子查询的语句不能转为多表查询的形式，并且该子查询是不相关/相关子查询
该子查询的第一个SELECT 代表的查询的类型为SUBQUERY/DEPENDENT SUBQUERY

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

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

包含UNION类的大查询中，若每个小查询都依赖外层查询的话，除了最左边的小查询外，其余小查询类型为DEPENDENT UNION

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

查询优化器执行包含子查询的语句时，选择将子查询物化后与外层查询进行连接，该子查询对应的类型为MATERIALIZED

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
-- 子查询的结果转换成了物化表，相当于只包含key1的记录构成的表
-- subquery2：子查询是根据 id=2 的表查询出来的

对于包含派生表的查询，该派生表对应的子查询类型为DERIVED

EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;

-- 外部查询用的表是内部查询派生出来的(AS derived_s1)

4. partitions(略)

5. type（重点）

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

当表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如MyISAM,MEMORY，那么对该表的访问方式为system

CREATE TABLE t(i int) Engine=MyISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;

再次插入一条记录，type就变为ALL。可以自己试试InnoDB

当我们根据主键或唯一索引列与常数进行等值匹配时，对单表的访问方法为const

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

在连接查询中，如果被驱动表通过主键或唯一索引列等值匹配的方式进行访问的(若主键或唯一索引是联合索引，所有索引列都必须等值比较)，则对该驱动表的访问方式为eq_ref

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

普通的索引和常量进行匹配，对该表的访问方式为ref

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

普通的索引和常量进行匹配，该索引列的值可以为NULL,对该表的访问方式为ref_or_null

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

单表访问情况下使用Intersection、Union、Sort-Union三种索引合并的方式来执行查询，则访问方式为index_merge

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

两个索引都用上了 unique_subquery针对一些包含IN子查询的语句，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用主键进行等值匹配。

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

如果使用索引获取某些范围区间的记录，则为range访问方法

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

当我们可以使用索引覆盖，但需要扫描所有索引记录时，访问方式为index
即我们发现 key_part3有一个联合索引，但他在最后边，而我们要找的所有列(这里只有key_part2)正好也是联合索引的一部分，寻思了一下，不然直接用该索引得了。
索引覆盖：不用回表了，联合索引虽然是非聚簇索引，但要查找的也在联合索引里

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

全表扫描：ALL

EXPLAIN SELECT * FROM s1;

SQL性能优化的目标：至少要达到 range级别，要求是ref级别，最好是const级别。（阿里巴巴开发手册要求）

6. possible_keys和key：可能用到的索引和实际用到的索引

7. key_len（重点）

实际使用到的索引长度(字节)
帮你检查是否充分利用上索引，值越大越好，主要针对联合索引，有一定参考意义

id为主键：索引长度为4字节(INT的长度)

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

key2为唯一索引，索引长度为5字节(INT的长度+可能为NULL 空占一字节)

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

key1是普通索引，VARCHAR(100)。索引长度为303字节(utf8一个字符占3字节。3*100 + NULL可能 1字节+ 变长类型需要2字节记录具体长度)

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

联合索引，索引长度为303字节，只用了一列

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

联合索引，索引长度为606字节，用了两列，比上面的好

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)

8. ref

使用索引列等值查询时，与索引列进行等值匹配的对象信息。比如只是一个常数或者是某个列

与常量比较：

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

与某表的某一列比较：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

与函数比较：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

9. rows（重点）

预估的需要读取的记录条数，越小越好

10. filtered

通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比。

预估记录条数经过搜索条件过滤后真正还能剩多少的百分比，百分百最好
例：查询出来是40条记录，预估的也是40条， filtered为100%，而预估若是400条则filtered为10%

举例：查询key1 > 'z' 并且 common_field = 'a'的记录：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';

首先使用索引(这里的type是range)，即用idx_key1 扫描表a，估计出413条记录，接下来使用额外的查询条件(common_field = 'a')进行二次过滤，最后只剩 41条左右了(10%)

因此一个比较低filtered值表示需要有一个更好的索引，假如type=all，表示以全表扫描的方式得到1000条记录，且filtered=0.1%，表示只有1条记录是符合搜索条件的。此时如果加一个索引可以直接搜出来1条数据，那么filtered就可以提升到100%。

11. Extra

用来说明一些额外信息。可以通过它们更精确的理解MySQL到底如何执行给定的查询语句

no tables used:没用到FROM

EXPLAIN SELECT 1;

Impossible WHERE：WHERE子句永远为FALSE

EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

Using where：(1)使用全表扫描(没有任何索引)，并且该语句WHERE子句有针对该表的搜索条件

EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

(2)当使用索引访问来执行查询，并且该语句的WHERE子句中有除了该索引包含的列外的其他条件

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

No matching min/max row：当查询列表处有MIN/MAX聚合函数，但没有符合WHERE子句中搜索条件的记录

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

Using index：查询列表和搜索条件只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下

EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';

我猜你忘了什么是索引覆盖了，因为写到这时我也忘了

复习一遍什么是索引覆盖：key1索引是二级索引，正常来说需要回表来获取查询列表的值，但是查询列表的值刚好包含在使用的索引中，所以在二级索引的B+树上就能拿到所需的信息了

Using index condition：搜索条件中出现了索引列但不能使用索引

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';

正常情况下通过索引找到满足key > 'z'的主键，通过主键回表查找记录再和 key1 LIKE '%b'对比

由于回表操作是一个随机IO，若是key1 LIKE '%b'筛选掉的条件特别多，我们无用的回表就会特别多。

所以MySQL把上面的步骤改了一下：根据key > 'z'定位到二级索引对应的记录，先检测 key1 LIKE '%b'再回表。

这样大大减少了回表时间。MySQL把这个改进称之为索引条件下推

Using join buffer (Block Nested Loop)：连接查询中，当驱动表不能有效利用索引加快访问速度，MySQL会为其分配一块join buffer的内存块加快查询速度。也就是所谓的基于块的嵌套循环算法

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

Not exists：当我们使用左连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，但那个列又是NOT NULL的

EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

Using intersect(...) 、 Using union(...) 和 Using sort_union(...):准备使用intersect索引合并的方式执行查询， ...表示需要进行索引合并的索引名称准备使用Union索引合并方式查询

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

Zero limit：limit 0

 EXPLAIN SELECT * FROM s1 LIMIT 0;

Using filesort: 有些情况下对结果集进行排序可以用到索引(key1的索引)

EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

但很多情况下排序操作无法使用索引，只能在内存/磁盘中进行排序。而这种方式被称为filesort

EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

Using temporary：许多查询中可能会借助临时表进行去重、排序之类的操作。比如执行DISTINCT,GROUP BY,UNION等子句的查询过程中，如果不能有效利用索引完成查询，MySQL很有可能寻求建立内部临时表来执行查询。不过可能付出巨大成本进行维护

EXPLAIN SELECT DISTINCT common_field FROM s1;

小结：

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

7. EXPLAIN的进一步使用

7.1 EXPLAIN四种输出格式

1. 传统格式：即之前输出的形式

2. JSON格式

传统格式缺少了一个衡量执行计划好坏的重要属性--成本，而JSON是四种格式中信息最详尽的格式

EXPLAIN FORMAT=JSON SELECT ....

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G

先看 s1 表的 cost_info 部分：

"cost_info": {
    "read_cost": "1840.84",
    "eval_cost": "193.76",
    "prefix_cost": "2034.60",
    "data_read_per_join": "1M"
}

read_cost = IO成本 + 检测 rows × (1 - filter) 条记录的CPU成本
eval_cost = 检测 rows × filter 条记录的成本
prefix_cost 就是单独查询 s1 表的成本，也就是： read_cost + eval_cost
data_read_per_join 表示在此次查询中需要读取的数据量。

对于 s2 表的 "cost_info" 部分是这样的：

"cost_info": {
    "read_cost": "968.80",
    "eval_cost": "193.76",
    "prefix_cost": "3197.16",
    "data_read_per_join": "1M"
}

由于 s2 表是被驱动表，所以可能被读取多次，这里的 read_cost 和 eval_cost 是访问多次s2表后累加起来的值，大家主要关注里边儿的 prefix_cost 的值代表的是整个连接查询预计的成本，也就是单次查询 s1 表和多次查询 s2 表后的成本的和，也就是：

968.80 + 193.76 + 2034.60 = 3197.16

3. TREE格式

8.0.16后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询

4. 可视化输出:略

7.2 SHOW WARNINGS的使用

查看与这个查询的执行计划有关的扩展信息

EXPLAIN SELECT s1.key1, s2.key1
FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

我们拿s1左连接s2 但执行结果却是s2为驱动表：

SHOW WARNINGS;

可以看到优化后把左外连接改为内连接：

8. 分析优化器执行计划：trace

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

查询 information_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的：

select * from student where id < 10;
select * from information_schema.optimizer_trace\G

自己看吧，太长了

9. MySQL监控分析视图-sys schema

将performance_schema和information_schema中的数据以更容易理解的方式总结归纳为视图，目的是为了降低查询performance_schema的复杂度,让DBA快速定位问题。

索引情况

#1. 查询冗余索引
select * from sys.schema_redundant_indexes;

#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;

#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted 
from sys.schema_index_statistics where table_schema='dbname' ;

表相关

# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;

# 2. 查询占用bufferpool较多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;

# 3. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;

#2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;

#3. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;

IO相关

#1. 查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

InnoDB相关

#1. 行锁阻塞情况
select * from sys.innodb_lock_waits;

风险提示：

通过sys库查询时，MySQL会消耗大量资源去收集信息，可能造成业务请求被阻塞。生产上建议不要频繁查询。

进阶篇(9) 性能分析工具的使用