更多详情内容请访问:MySQL 系列文章导读

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

数据库的优化整个流程划分成了 观察(Show status) 和 行动(Action) 两个部分。数据库的优化可以总结为下图。字母 S 的部分代表观察(会使用相应的分析工具),字母 A 代表的部分是行动(对应分析可以采取的行动)。
在这里插入图片描述

可以从图中看到,在整个流程中需要用到很多分析工具:比如慢查询,EXPLAINSHOW PROFILING 等,这篇文章就会介绍这些数据库性能分析工具。

简单小结如下:
在这里插入图片描述

可以看到数据库调优的步骤中越往金字塔尖走,其成本越高,效果越差,因此我们在数据库调优的过程中,要重点把握金字塔底部的 sql 及索引调优数据库表结构调优系统配置参数调优等软件层面的调优。

2. 查看系统性能参数

可以使用 SHOW STATUS 语句查询一些数据库服务器的性能参数使用频率

其语法如下:

SHOW [GLOBAL][SESSION] STATUES 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 的上线时间

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 9     |
+---------------+-------+
1 row in set (0.01 sec)

看看存储引擎增删改查的行数。

mysql> show status like  'innodb_rows_%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 0     |
| Innodb_rows_read     | 8     |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
4 rows in set (0.00 sec)

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

先来造一下数据

CREATE DATABASE atguigudb1;

USE atguigudb1;

CREATE FUNCTION rand_string(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 //


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 //

# 存储过程1:创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE insert_course( max_num INT )
BEGIN 
    DECLARE i INT DEFAULT 0; 
    SET autocommit = 0;   #设置手动提交事务
    REPEAT  #循环
    SET i = i + 1;  #赋值
    INSERT INTO course (course_id, course_name ) VALUES
    (rand_num(10000,10100),rand_string(6)); 
    UNTIL i = max_num 
    END REPEAT; 
    COMMIT;  #提交事务
END //
DELIMITER ;

# 存储过程2:创建插入学生表存储过程
CREATE PROCEDURE insert_stu( max_num INT )
BEGIN 
    DECLARE i INT DEFAULT 0; 
    SET autocommit = 0;   #设置手动提交事务
    REPEAT  #循环
    SET i = i + 1;  #赋值
    INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES
(rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6)); 
    UNTIL i = max_num 
    END REPEAT; 
    COMMIT;  #提交事务
END //

# 插入课程数据
CALL insert_course(100);
#插入学生数据
CALL insert_stu(1000000);

执行查询操作并且查看 sql 执行成本,Value表示 I/O 加载的数据页的页数。

mysql> select * from student_info where id = 900001;
+--------+------------+--------+-----------+----------+---------------------+
| id     | student_id | name   | course_id | class_id | create_time         |
+--------+------------+--------+-----------+----------+---------------------+
| 900001 |     128284 | jbCKPX |     10080 |    10001 | 2022-05-31 11:01:54 |
+--------+------------+--------+-----------+----------+---------------------+
1 row in set (0.00 sec)

mysql> show status like 'last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

再来个大的。

mysql>  select * from student_info where id between 900001 and 900100;
+--------+------------+--------+-----------+----------+---------------------+
| id     | student_id | name   | course_id | class_id | create_time         |
+--------+------------+--------+-----------+----------+---------------------+
| 900001 |     128284 | jbCKPX |     10080 |    10001 | 2022-05-31 11:01:54 |
// ...
| 900099 |      45120 | MZOSay |     10081 |    10026 | 2022-05-31 11:01:54 |
| 900100 |      83397 | lQyTXg |     10034 |    10058 | 2022-05-31 11:01:54 |
+--------+------------+--------+-----------+----------+---------------------+
100 rows in set (0.00 sec)

mysql> show status like 'last_query_cost';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 41.136003 |
+-----------------+-----------+
1 row in set (0.00 sec)

不知道大家有没有发现,上面的查询页的数量是刚才的 41 倍,但是查询的效率并没有明显的变化,实际上这两个 SQL 查询的时间基本上一样,查询 last_query_cost 对于比较开销是非常有用的,特别是我们有好几种查询方式可选的时候。

🎈 SQL查询是一个动态的过程,从页加载的角度,我们可以得到以下两点结论:

  1. 位置决定效率:数据库缓冲池>内存>磁盘。
  2. 批量决定效率:顺序读取>大于随机读取,有时候批量顺序读取多个页甚至会比随机加载一个页更快。

在实际生产中,我们可以利用这个特点,把经常用于查询的数据尽量放在缓冲池中,其次我们可以充分利用磁盘的吞吐能力,批量读取数据。

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

慢查询日志用来记录相应时间超过阈值的语句,它可以帮助我们发现那些执行时间特别长的 sql 语句,以期进行针对性优化。一般 mysql 的慢查询日志默认关闭,非调优情况不建议开启,避免影响数据库的性能。

4.1 开启慢查询日志

4.1.1 开启 slow_query_log

查看

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------------------+
| Variable_name       | Value                                                |
+---------------------+------------------------------------------------------+
| slow_query_log      | OFF                                                  |
| slow_query_log_file | D:\mysql-5.7.26-winx64\data\DESKTOP-1PB99O1-slow.log |
+---------------------+------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

修改,注意这里要加 global,因为它是全局系统变量,否则会报错。

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.02 sec)

再查看

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------------------+
| Variable_name       | Value                                                |
+---------------------+------------------------------------------------------+
| slow_query_log      | ON                                                   |
| slow_query_log_file | D:\mysql-5.7.26-winx64\data\DESKTOP-1PB99O1-slow.log |
+---------------------+------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

4.1.2 修改long_query_time阈值

查看

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.02 sec)

修改

mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

再查看

mysql> show global variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set, 1 warning (0.00 sec)

🔊记得要加 global,否则默认只在当前会话,不过,即使加 global 上面的修改还都只是临时的修改,当数据库服务器重启以后,以上修改就会失效。要想永久的生效,需要更改 my.cnf 文件,然后重启数据库服务器。

slow_query_log=ON
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE

4.2 案例演示

4.2.1 建表

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;

4.2.2 设置参数 log_bin_trust_function_creators

创建函数,假如报错

This function has none of DETERMINISTIC......

命令开启:允许创建函数设置:

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

4.2.3 创建函数

随机产生字符串:

DELIMITER //
CREATE FUNCTION rand_string(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 ;
#测试
SELECT rand_string(10);

产生随机数值:

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 ;
#测试:
SELECT rand_num(10,100);

4.2.4 创建存储过程

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 ;

4.2.5 调用存储过程

#调用刚刚写好的函数, 4000000条记录,从100001号开始
mysql> CALL insert_stu1(100001,4000000);
Query OK, 0 rows affected (10 min 47.03 sec)

注意,这个时间会比较长,请耐心等待几分钟哟。结束后可以查询下是不是插入成功了。

select count(*) from student;

接下来执行一下下面的查询操作,制造慢查询的场景。

mysql> set  long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM student WHERE stuno = 3455655;
+---------+---------+--------+------+---------+
| id      | stuno   | name   | age  | classId |
+---------+---------+--------+------+---------+
| 3355654 | 3455655 | QQFFkl |   57 |     904 |
+---------+---------+--------+------+---------+
1 row in set (3.47 sec)

mysql> select * from student where name = 'QQFFkl';
+---------+---------+--------+------+---------+
| id      | stuno   | name   | age  | classId |
+---------+---------+--------+------+---------+
|  143213 |  243214 | qQffkL |   95 |     543 |
|  225733 |  325734 | qQffkL |   10 |     861 |
|  280275 |  380276 | QqfFKL |   50 |     118 |
| 1355465 | 1455466 | QqfFKL |   52 |     195 |
| 1676763 | 1776764 | qQffkL |   11 |     906 |
| 1766208 | 1866209 | qqFfKl |   11 |     396 |
| 1870789 | 1970790 | qqFfKl |   97 |     182 |
| 2368740 | 2468741 | QQFFkl |   51 |     645 |
| 2386799 | 2486800 | qQffkL |   11 |     875 |
| 3170932 | 3270933 | QqfFKL |   50 |      92 |
| 3355654 | 3455655 | QQFFkl |   57 |     904 |
| 3966226 | 4066227 | qQffkL |   96 |     629 |
+---------+---------+--------+------+---------+

查看下慢查询的记录。

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 2     |
+---------------+-------+
1 row in set (0.00 sec)

🎯补充:在Mysql中,还有另外一个变量 min_examined_row_limit 用来控制慢查询日志,他的含义是,在查询时,查询时间超过 long_query_time 的日志,还要保证查询扫描过的记录数满足 min_examined_row_limit 才会被记录到慢查询日志。一般它默认是 0,我们也一般不会去修改它。

SHOW VARIABLES like 'min%'
OK
时间: 0.002s

4.3 慢查询日志分析工具:Mysqldumpslow

在生产环境中,如果要手工分析日志,查找、分析 SQL,显然是个体力活,MySQL 提供了日志分析工具 mysqldumpslow。

📑 注意:
1.该工具并不是 MySQL 内置的,不要在 MySQL 下执行,可以直接在根目录或者其他位置执行
2.该工具只有 Linux 下才是开箱可用的,实际上生产中mysql数据库一般也是部署在linux环境中的

通过 mysqldumpslow 可以查看慢查询日志帮助

mysqldumpslow --help

其结果如下图

在这里插入图片描述

现在来使用下,先找到慢查询日志的位置。(注:笔者实际上是 windows 的环境,在使用时时参考上面注意中的博客,后不再赘述)

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------------------+
| Variable_name       | Value                                                |
+---------------------+------------------------------------------------------+
| slow_query_log      | ON                                                   |
| slow_query_log_file | D:\mysql-5.7.26-winx64\data\DESKTOP-1PB99O1-slow.log |
+---------------------+------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

找到前10条记录。

D:\mysql-5.7.26-winx64\bin>mysqldumpslow -s c -t 10 D:\mysql-5.7.26-winx64\data\DESKTOP-1PB99O1-slow.log

Reading mysql slow query log from D:\mysql-5.7.26-winx64\data\DESKTOP-1PB99O1-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  MySQL, Version: N.N.N (MySQL Community Server (GPL)). started with:
  TCP Port: N, Named Pipe: MySQL
  # Time: N-N-02T00:N:N.885803Z
  # User@Host: root[root] @ localhost [::N]  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  use atguigudb1;
  SET timestamp=N;
  CALL insert_stu1(N,N)

Count: 1  Time=3.74s (3s)  Lock=0.00s (0s)  Rows=12.0 (12), root[root]@localhost
  select * from student where name = 'S'

Died at mysqldumpslow.pl line 161, <> chunk 2.

可以看到上面 sql 中具体的数值类都被N代替,字符串都被使用 S 代替,如果想要显示真实的数据,可以加上参数 -a

D:\mysql-5.7.26-winx64\bin> mysqldumpslow  -a -s c -t 10 D:\mysql-5.7.26-winx64\data\DESKTOP-1PB99O1-slow.log

Reading mysql slow query log from D:\mysql-5.7.26-winx64\data\DESKTOP-1PB99O1-slow.log
Count: 1  Time=3.74s (3s)  Lock=0.00s (0s)  Rows=12.0 (12), root[root]@localhost
  select * from student where name = 'QQFFkl'

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  MySQL, Version: 5.7.26 (MySQL Community Server (GPL)). started with:
  TCP Port: 3306, Named Pipe: MySQL
  # Time: 2022-06-02T00:27:36.885803Z
  # User@Host: root[root] @ localhost [::1]  Id:     9
  # Query_time: 647.031348  Lock_time: 0.000091 Rows_sent: 0  Rows_examined: 0
  use atguigudb1;
  SET timestamp=1654129656;
  CALL insert_stu1(100001,4000000)

最后罗列下工作中常用的一些查询。

#得到返回记录集最多的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 服务器停止慢查询日志功能有两种方法:

4.4.1 方式 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%';  #查询超时时长

4.4.2 方式 2:临时性方式

使用 SET 语句来设置。
(1)停止 MySQL 慢查询日志功能,具体 SQL 语句如下。

SET GLOBAL slow_query_log=off;

(2)使用 SHOW 语句查询慢查询日志功能信息,具体 SQL 语句如下

SHOW VARIABLES LIKE '%slow%';

结果如下

在这里插入图片描述

重启 MySQL 服务,执行如下 sql,会将 long_query_time 恢复至默认的 10s,不演试了。

SHOW VARIABLES LIKE '%long_query_time%';

4.5 删除与恢复慢查询日志

调优结束可以及时删除慢查询日志节省磁盘空间哟。当然手工删除也是可以的。

rm DESKTOP-1PB99O1-slow.log

如果误删了,而且还没有了备份或者回收站也没有了,可以使用下面的命令来重新恢复生成哟。

#先要打开慢查询日志
SET GLOBAL slow_query_log=ON;
#恢复慢查询日志
mysqladmin -u root -p flush-logs slow

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

查看是否开启

show variables like 'profiling';

在这里插入图片描述
如果没有开启,执行sql

mysql > set profiling = 'ON';

使用下

# 执行sql
SELECT * FROM student WHERE stuno=3453451;
SELECT * FROM student WHERE name=`JnoEfP`;
# 分析性能
SHOW PROFILES;

这里笔者在当前会话执行过许多 sql 了。效果如下

在这里插入图片描述
如果只需要查看最近一条sql的性能细节。

SHOW PROFILE;

结果如下:

在这里插入图片描述

可以查看指定 sql 的指定细节。

show profile cpu,block io for query 70;

如果发现一条 sql 慢的原因在于执行慢(executing字段耗时多),就可以接着用 Explain 进行分析具体的 sql 语句

🔊补充:

show profile的常用查询参数:

① ALL:显示所有的开销信息。
② BLOCK IO:显示块 IO 开销。
③ CONTEXT SWITCHES:上下文切换开销。
④ CPU:显示 CPU 开销信息。
⑤ IPC:显示发送和接收开销信息。
⑥ MEMORY:显示内存开销信息。
⑦ PAGE FAULTS:显示页面错误开销信息。
⑧ SOURCE:显示和 Source_function,Source_file,Source_line 相关的开销信息。
⑨ SWAPS:显示交换次数开销信息。

另外,在日常开发中, 如果在 show profile 的查询结果中,出现了以下任何一条。sql 语句需要优化。

🎨 sql 语句需要优化的场景:

Coverting Heap to MyISAM:查询结果太大,内存放不下,正在往磁盘中迁移
Creating tmp table:创建临时表,先拷贝数据到临时表,用完再删除临时表
Coping to tmp table on disk:把临时数据复制到磁盘上,警惕!
locked

最后,还需要注意:SHOW PROFILE 命令将被弃用,不过我们可以从 information_schema 中的 profiling 数据表进行查看。

6. 分析查询语句:EXPLAIN(重点)

6.1 EXPLAIN简介

6.1.1 作用

在定位了慢 sql 后,可以使用 Describe 或者 Explain 进行针对性的分析。

如果想知道 SQL 的执行计划,比如是全表扫描,还是索引扫描,可以通过 EXPLAIN 去完成。EXPLAIN 命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解 MySQL 的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行 SQL 语句时哪种策略预计会被优化器采用。

6.1.2 版本说明

(1)MySQL 5.6.3 以前只能 EXPLAIN SELECT;MYSQL 5.6.3 以后就可以 EXPLAIN SELECTEXPLAIN UPDATEEXPLAIN DELETE

注意,EXPLAIN 仅仅是查看执行计划,不会真实的执行 sql

EXPLAIN DELETE FROM student_info WHERE id = 2;

SELECT * FROM student_info LIMIT 10;

上面查询 sql 的结果如下,id 为 2 的数据还在的

在这里插入图片描述
(2)在 5.7 以前的版本中,想要显示分区参数 partitions 需要使用 explain partitions 命令;想要显示 filtered 需要使用 explain extended 命令。在 5.7 版本后,默认 explain 直接显示 partitionsfiltered中的信息(如下图):

在这里插入图片描述

6.2 基本语法

先看看其显示的参数列表。后面会逐一进行介绍。

在这里插入图片描述

6.3 数据准备

6.3.1 建表

建两个表方便联合查询

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;

6.3.2 创建存储函数

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 ;

创建函数,假如报错,需设置参数 log_bin_trust_function_creators,允许创建函数设置

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

6.3.3 创建存储过程

创建往 s1 表中插入数据的存储过程:

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 ;

创建往 s2 表中插入数据的存储过程:

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 ;

6.3.4 调用存储过程

s1 表数据的添加:加入 1 万条记录:

CALL insert_s1(10001,10000);

s2 表数据的添加:加入 1 万条记录:

CALL insert_s2(10001,10000);

6.4 EXPLAIN 各列作用

6.4.1 table

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

EXPLAIN SELECT * FROM s1 INNER JOIN s2;

如下图,一张表对应一个记录。注:临时表也会有对应的记录

在这里插入图片描述

6.4.2 id

一趟查询的标识。上面的查询结果,两个记录似乎 id 都是1,这是为什么呢?

在这里插入图片描述

实际上,一个 SELECT 关键字对应一个 id。下面 sql 有两个 select(子查询)

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

有两个不一样的 id 哟。其实就是一趟查询有一个 id 表示。

在这里插入图片描述
不过,这里有一个坑。看看下面语句。

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

两个记录的 id 都是 1,小小的眼睛是否充满了大大的疑惑?

在这里插入图片描述

这是因为优化器会对上面的 sql 语句进行优化,将其转换为多表连接,而不是子查询。因为子查询其实是一种嵌套查询的情况,其时间复杂度是 O(n^m),其中 m 是嵌套的层数,而多表查询的时间复杂度是 O(n*m)。而上面的语句两个查询并不需要存在依赖关系。

再看看 Union 联合查询的情况。

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

结果是这样:

在这里插入图片描述

这是因为 Union 是取表的并集,需要建临时表进行去重,因此会有三条记录。可以看到第三条记录的 Extra 就标识了它是一张临时表哦。临时表 id 是 Null。

再看看 Union ALL:

EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;

产生两条记录,因为它不会去重。

在这里插入图片描述

💌小结

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

6.4.3 select_type

一个 sql 语句中可能存在多个查询。每个 select 小查询都有一个 select_type,表示它在大查询中扮演什么角色。

先看一个简单的查询。

EXPLAIN SELECT * FROM s1;

select_type 是 simple

在这里插入图片描述

看下连接查询

EXPLAIN SELECT * FROM s1 INNER JOIN s2

还是simple

在这里插入图片描述

Union 联合查询。其左边的查询是 Primary,右边的查询类型是 Union,去重的临时表查询类型是 Union Result.

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

在这里插入图片描述

Union All:

EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;

不解释

在这里插入图片描述

子查询,如果不能被转换为多表连接的形式,也就是不会被优化器进行自动的优化。并且该子查询是不相关的子查询。

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

前面的查询,也就是外层查询是 Primary,内层查询是 SUBQUERY

在这里插入图片描述

子查询,如果不能被转换为多表连接的形式,并且该子查询是相关的子查询。比如下面的查询在内部子查询使用了外部的表。

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

外层查询是Primary,内层查询是DEPENDENT SUBQUERY

在这里插入图片描述

需要注意的是 DEPENDENT SUBQUERY 的查询语句可能会被执行多次,因为内存查询依赖于外层的查询,因此可能会是外层传一个值,内层就执行一次的模式。

在包含 Union或者 Union All 的子查询 sql 中,如果各个小查询都依赖于外查询,那么除了最左边的小查询外,各个小查询的类型都是 DEPENDENT UNION

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

外查询是 Primary,最左边的子查询是 DEPENDENT SUBQUERY,后面的子查询是 DEPENDENT UNION,临时去重表的类型是 Union Result。这里大家可能要困惑,第一个子查询中也没有看到依赖 s1 啊。这其实也是优化器会在执行时进行优化,将 IN 改成 Exist,并且把外部的表移到内部去。这里我们了解就行,以后会有文章给大家介绍优化器的。

在这里插入图片描述

还有,对于关于派生表的子查询。

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

其查询类型时 DERIVED.

在这里插入图片描述

当优化器在执行子查询时选择把子查询优化成为一张物化表,与外层查询进行连接查询时。

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

从下往上看,子查询的查询类型是 MATERIALIZED;物化过程是基于 id 为 2 的查询结果表进行的,其 table 是 subquery 2,查询类型是 SIMPLE,而外层也相当于是与固定的直接值进行查询,其类型也是 SIMPLE

在这里插入图片描述

上面的介绍都是一些基本的情况,还没有真正的介绍与索引相关的情况哦。觉得是不是晕晕的了,我们用一个表格进行下总结吧。

6.4.4 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
 );

查询 id 大于200(200>100,p1分区)的记录

DESC SELECT * FROM user_partitions WHERE id>200;

查看执行计划,partitions 是 p1,符合我们的分区规则
在这里插入图片描述

6.4.5 type ☆

type 标明了执行一条查询时对于 mysql 中一张表的访问方法。这是一个重要的指标,表示我们究竟是通过什么方式访问获取数据的。

完整的访问方法如下: system 、const、eq_ref 、ref 、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、 range、index、ALL。

下面将详细的讲解。

1️⃣system

当表中只有一条记录,并且该表中存储引擎统计数据是精确的,比如 MYISAM,Memory,那么其访问方法就是System。这种方式几乎是性能最高的,当然我们几乎用不上。

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

查询结果如下:

在这里插入图片描述
但凡我们再插入一条数据。

INSERT INTO t VALUES(2);
EXPLAIN SELECT * FROM t;

其访问方式就变成了性能最差的全表扫描 ALL

在这里插入图片描述

如果存储引擎是InnoDB,即使只有一条数据,其访问方式也是ALL,这是因为 InnnoDB 访问数据不是精确的。

2️⃣Const

当我们根据主键或者唯一的耳机索引,与常数进行等值匹配时,对单表的访问方法就是 const。这个访问方式的效率低于 system,但也是很高效的。

比如对主键与常数匹配,进行等值查询。

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

比如对Unique标识的唯一二级索引key2与常数匹配,进行等值查询。

EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

在这里插入图片描述

3️⃣eq_ref

再进行连接查询时,如果被驱动表是通过主键或者唯一二级索引等值匹配的方式进行查询的,那么被驱动表的访问方式是 eq_ref。这也是一种性能很不错的方式。

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

上面连接查询语句,对于驱动表来说,就是对s1全表进行扫描,找到符合条件的数据,因此其typeAll,对被驱动表来说,相于直接访问驱动表查询到的数据进行等值查询,因此其访问方式是eq_ref.

在这里插入图片描述

4️⃣ref

当使用普通的二级索引与常量进行等值匹配时,type 是 ref

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

结果如下:

在这里插入图片描述

下面考考你。以下 sql 的引用类型是什么呢?

EXPLAIN SELECT * FROM s1 WHERE key3 = 10066;

看看答案。你是不是猜错了。是 All。这是因为 key3 的字段 varchar 类型,但是我们这里常量值是整形,因此需要使用函数进行隐式的类型转换,一旦使用函数,索引就失效了,因此访问类型变成了全表扫描 All

在这里插入图片描述

我们常量使用对的类型。

EXPLAIN SELECT * FROM s1 WHERE key3 = '10066';

就是期望的ref访问类型了。

在这里插入图片描述

5️⃣ref_or_null

当使用普通的二级索引进行等值匹配时,当索引值可以是 Null 时,type 是 ref_or_null

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

结果如下:

在这里插入图片描述

6️⃣index_merge

当进行单表访问时,如果多个查询字段分别建立了单列索引,使用 OR 连接,其访问类型是 index_merge

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

其结果如下。同时还可以看到 key 这一字段,是使用了两个索引。

在这里插入图片描述

猜猜下面 sql 的引用类型

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

猜对了吗?答案是 ref,这是因为用 AND 连接两个查询时,实际上只使用了 key1 的索引。

在这里插入图片描述

7️⃣unique_subquery

针对一些包含 IN 的 subcase,如果优化器决定将 IN 子查询优化为 EXIST 子查询,而且子查询可以使用主键进行等值匹配的话,子查询的执行计划的 type 就是 unique_subquery

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

结果如下:

在这里插入图片描述

8️⃣range

范围查找的访问计划类型是 range

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

在这里插入图片描述

9️⃣index

当我们可以使用索引覆盖,但是需要扫描的全部的索引记录时,该表的访问方式就是 index。索引覆盖后面文章介绍优化器时会详细介绍,为了便于大家理解,先简单介绍如下。比如下面 sql 语句中,key_part2 ,key_part2 都属于联合索引 INDEX idx_key_part(key_part1, key_part2, key_part3) 的一部分,在查找数据时可以用上这个联合索引,而不用进行回表操作,这种情况即使索引覆盖。

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

结果如下:

在这里插入图片描述

1️⃣0️⃣ALL

EXPLAIN SELECT * FROM s1;

结果
在这里插入图片描述

❤温馨提示:这里很多小伙伴会觉得记不住,其实您可以收藏这篇博客,执行 EXPLAIN 时对应结果,反向查找博文对应内容,毕竟咱们只需要能够读懂性能分析的结果。

最后进行下小结吧。

结果值从最好到最坏依次是: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来(见加粗部分)。

SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,最好是 consts 级别。(阿里巴巴开发手册要求)

6.4.6 possible_keys 和 key

分别表示可能被使用的索引与实际使用的索引。

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

在这里插入图片描述

对应优化器来说,可以选择的 possible_keys 越少越好,因为选项越多,进行过滤花的时间也就对应更多。另外,优化器会对各个索引进行查询的效率进行评估,以此来选择实际使用的 key。而且由于优化器会对 sql 进行优化,完全可能会出现 possible_keys 是 null,但是 key 不为 null 的情况。

6.4.7 key_len ☆

实际使用的索引的长度,单位是字节。可以帮助你检查是否充分利用了索引,主要针对联合索引具有一定的参考,对同一索引来说,key_len 值越大越好(与自己比较,后面将解释)。

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;

其结果如下,是 4,这个结果怎么算出来的呢?

在这里插入图片描述

这是因为使用的是主键 id 作为索引,其类型是 int,占 4 个字节。

再来。猜猜下面的 key_len 是多少。

 EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

什么?你猜的是 4,而答案是 5。

在这里插入图片描述

这是因为虽然 key2 也是 int 类型,但是它被 unique 修饰,并没有标识非空(而主键都是非空的),因此加上空值标记,一共是5字节

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

答案是 303,因为类型是 varchar(100),100 个字符,utf-8 每个字符占 3 个字节,共 300 个字节,加上变长列表 2 个字节与一个空值标识占一个字节,共 303 字节。

在这里插入图片描述

看看联合索引的情况。

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

其key_len还是303,不需要解释了吧。

在这里插入图片描述

再看看下面这个联合索引。

 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

其结果是 606

在这里插入图片描述

这个查询的 key-len 比上面的查询大,性能就比上面的好,怎么理解呢?其实只要你看过我之前介绍B+树的文章就很容易理解了。因为在目录页我除了考虑 key_part1 ,还会考虑 key_part2,定位到的数据就更加精准,范围更小,需要加载 I/O 的数据页数量就会更少,这样是不是性能就比较好啊。
image-20220430153658627

猜猜下面的 sql 执行后 key_len 是多少

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

是空哦,因为我们都不会使用到索引,这就是我们一直在提的最左前缀原则,后面会详细介绍的。

在这里插入图片描述

📚练习: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)

6.4.8 ref

当索引列进行等值查询时,与索引列匹配的对象信息。

与常量等值匹配。

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

refconst

在这里插入图片描述

再来。连接查询的情况:

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

对被驱动表s2执行的查询引用了atguigudb1.s1.id字段进行等值查询。

在这里插入图片描述

最后看看使用函数的情况。其 ref 就是 func

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

6.4.9 rows ☆

预估的需要读取的记录条目数。条目数越小越好。这是因为值越小,加载I/O的页数就越少。

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

在这里插入图片描述

6.4.10 filtered

经过搜索条件后过滤剩下的记录所占的百分比。百分比越高越好,比如同样 rows 是 40,如果 filter 是 100,则是从 40 条记录里进行查找,如果 filter 是 10,则是从 400 条记录里进行查找,相比较而言当然是前者的效率更高哦。

如果执行的是单表扫描,那么计算时需要估计除了对应搜索条件外的其他搜索条件满足的记录有多少条。晕了就看看下面的例子。

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

结果是 10,表示有 398 条记录满足 key1 > 'z' 的条件,这 398 条记录的 10% 满足 common_field = 'a' 条件。

在这里插入图片描述

实际上,对于单表查询,这个字段没有太大的意义,我们更加关注连接查询时的 filtered 值,它决定了被驱动表要执行的次数。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE
s1.common_field = 'a';

结果如下。在标明驱动表 s1 提供给被驱动表的记录数是 9895 条,其中 989.5 条满足过滤条件s1.key1 = s2.key1,那么被驱动表需要执行 990 次查询。

在这里插入图片描述

6.4.11 Extra ☆

提供一些额外信息,可以更精确的知道 MySQL 到底如何执行给定的查询语句。

No tables used,不解释。

EXPLAIN SELECT 1;

在这里插入图片描述

Impossible WHERE,当查询条件永远不可能满足,查不到数据时会出现该信息。

 EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

在这里插入图片描述

Using where,没有使用索引,普通的 where 查询

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

在这里插入图片描述

使用索引查询,则默默使用索引,什么额外信息也没有。

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

在这里插入图片描述

索引加普通 where,那还是 using where

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

在这里插入图片描述

No matching min/max row。当查询语句中有 MIN、MAX 等聚合函数,但是并没有符合 where 条件的搜索记录时,会提供额外信息 No matching min/max row(表中根本没有满足 where 条件的字句,找 min、max 没有意义)

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

在这里插入图片描述

Select tables optimized away。当查询语句中有 MIN、MAX 等聚合函数,有符合 where 条件的搜索记录时。

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

在这里插入图片描述

Using index。在使用覆盖索引的情况提示。所谓覆盖索引,就是索引中覆盖了需要查询的所有字段,不需要再使用聚簇索引进行回表查找,比如下面的例子,使用 key1 作为查找条件,该字段建立了索引,B+ 树可以查找到 key1 字段和主键,因此下面只查找 key1 字段就不用进行回表操作,这是非常棒的情况。

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

在这里插入图片描述

Using index condition:搜索列中虽然出现了索引列,但是不能够使用索引,这是很坑的。

比如下面的查询虽然出现了索引列作为查询条件,但是还是需要进行回表查找,回表操作是一个随机 I/O,比较耗时。

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

在这里插入图片描述

上面这种情况可以使用索引下推(可以通过配置项进行配置),使我们使用 WHERE key1 > 'z' 得到的结果先进行模糊匹配 key1 LIKE '%a',然后再去回表,就可以减少回表的次数了。

Using join buffer (Block Nested Loop):在连接查询中,当被驱动表不能够有效利用索引实现提升速度,数据库就使用缓存来尽可能提升一些性能。

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

在这里插入图片描述

Not exists:使用左外连接时,当被驱动表的搜索条件要求某个字段为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(…):索引合并。

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

在这里插入图片描述

Zero limit

EXPLAIN SELECT * FROM s1 LIMIT 0;

在这里插入图片描述

Using filesort:排序时无法使用到索引,只能在内存(记录较少)或者磁盘中(记录数较多)进行排序,这种情况时比较悲壮的。

EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

在这里插入图片描述

Using temporary:普通字段去重、分组,无法使用索引,使用临时表,这也是需要被优化的。

EXPLAIN SELECT DISTINCT common_field FROM s1;

在这里插入图片描述

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

7. EXPLAIN 的进一步使用

7.1 EXPLAIN 四种输出格式

这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式: 传统格式JSON格式TREE格式 以及 可视化输出。用户可以根据需要选择适用于自己的格式。

7.1.1 传统格式

传统格式简单明了,输出是一个表格形式,概要说明查询计划。

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

在这里插入图片描述

7.1.2 JSON 格式

在EXPLAIN单词和真正的查询语句中间加上 FORMAT=JSON 。
传统格式与json格式的各个字段存在如下表所示的对应关系(mysql5.7官方文档)。
在这里插入图片描述

demo如下:

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

结果如下。可以看到 json 格式的信息量会更加丰富。尤其是成本信息,是用于衡量一个执行计划的好坏的重要指标。

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "12766.44"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "s2",
          "access_type": "ALL",
          "possible_keys": [
            "idx_key1"
          ],
          "rows_examined_per_scan": 9898,
          "rows_produced_per_join": 8908,
          "filtered": "90.00",
          "cost_info": {
            "read_cost": "294.96",
            "eval_cost": "1781.64",
            "prefix_cost": "2076.60",
            "data_read_per_join": "15M"
          },
          "used_columns": [
            "key1",
            "common_field"
          ],
          "attached_condition": "((`atguigudb1`.`s2`.`common_field` is not null) and (`atguigudb1`.`s2`.`key1` is not null))"
        }
      },
      {
        "table": {
          "table_name": "s1",
          "access_type": "ref",
          "possible_keys": [
            "idx_key1"
          ],
          "key": "idx_key1",
          "used_key_parts": [
            "key1"
          ],
          "key_length": "303",
          "ref": [
            "atguigudb1.s2.key1"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 8908,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "8908.20",
            "eval_cost": "1781.64",
            "prefix_cost": "12766.44",
            "data_read_per_join": "15M"
          },
          "used_columns": [
            "key1"
          ]
        }
      }
    ]
  }
}

大家可能有疑问 “cost_info” 里边的成本看着怪怪的,它们是怎么计算出来的?

先看 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 成本

💚 rows和filter都是我们前边介绍执行计划的输出列,在JSON格式的执行计划中,rows相当于rows_examined_per_scan,filtered名称不变

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

7.1.3 TREE 格式

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

 EXPLAIN FORMAT=tree SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE
s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=1360.08 rows=990)
 -> Filter: ((s1.common_field = 'a') and (s1.key1 is not null)) (cost=1013.75
rows=990)
   -> Table scan on s1 (cost=1013.75 rows=9895)
 -> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index
condition: (cast(s1.key1 as double) = cast(s2.key2 as double)) (cost=0.25 rows=1)
1 row in set, 1 warning (0.00 sec)

7.1.4 可视化输出

可视化输出,可以通过 MySQL Workbench 可视化查看 MySQL 的执行计划。通过点击 Workbench 的放大镜图标,即可生成可视化的查询计划。

在这里插入图片描述

上图按从左到右的连接顺序显示表。红色框表示 全表扫描 ,而绿色框表示使用索引查找对于每个表,显示使用的索引。还要注意的是,每个表格的框上方是每个表访问所发现的行数的估计值以及访问该表的成本。

7.2 SHOW WARNINGS 的使用

可以显示数据库真正执行的 sql

先使用 Explain,我们写的 sql 按道理是使用 s1 作为驱动表

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

执行结果把 s2 作为了驱动表,s1 作为了被驱动表
在这里插入图片描述

紧接着使用SHOW WARNINGS

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
 Level: Note
 Code: 1003
Message: /* select#1 */ select `atguigu`.`s1`.`key1` AS `key1`,`atguigu`.`s2`.`key1`
AS `key1` from `atguigu`.`s1` join `atguigu`.`s2` where ((`atguigu`.`s1`.`key1` =
`atguigu`.`s2`.`key1`) and (`atguigu`.`s2`.`common_field` is not null))
1 row in set (0.00 sec)

上面 message 中显示的是数据库优化、重写后‘真正’执行的查询语句。果然它帮我们做了优化。

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

OPTIMIZE_TRACE 是 mysql 5.6 中引入的一个跟踪工具,它可以跟踪优化器做出的各种决策,比如访问表的方法,各种开销计算,各种转换,结果会被记录到 information_schema.optimizer_trace中。

开启。

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

开启后,可分析如下语句:

  • SELECT
  • INSERT
  • REPLACE
  • UPDATE
  • DELETE
  • EXPLAIN
  • SET
  • DECLARE
  • CASE
  • IF
  • RETURN
  • CALL

测试:执行如下 SQL 语句

select * from student where id < 10;

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

select * from information_schema.optimizer_trace\G

结果如下

*************************** 1. row ***************************
 //第1部分:查询语句
 QUERY: select * from student where id < 10
 //第2部分:QUERY字段对应语句的跟踪信息
 TRACE: {
 "steps": [
 {
   "join_preparation": {  //预备工作
    "select#": 1,
    "steps": [
    {
      "expanded_query": "/* select#1 */ select `student`.`id` AS
`id`,`student`.`stuno` AS `stuno`,`student`.`name` AS `name`,`student`.`age` AS
`age`,`student`.`classId` AS `classId` from `student` where (`student`.`id` < 10)"
    }
   ] /* steps */
  } /* join_preparation */
 },
 {
   "join_optimization": {  //进行优化
    "select#": 1,
    "steps": [
    {
      "condition_processing": {  //条件处理
       "condition": "WHERE",
       "original_condition": "(`student`.`id` < 10)",
       "steps": [
       {
         "transformation": "equality_propagation",
         "resulting_condition": "(`student`.`id` < 10)"
       },
       {
         "transformation": "constant_propagation",
         "resulting_condition": "(`student`.`id` < 10)"
       },
       {
         "transformation": "trivial_condition_removal",
         "resulting_condition": "(`student`.`id` < 10)"
       }
] /* steps */
     } /* condition_processing */
    },
    {
      "substitute_generated_columns": {  //替换生成的列
     } /* substitute_generated_columns */
    },
    {
      "table_dependencies": [   //表的依赖关系
      {
        "table": "`student`",
        "row_may_be_null": false,
        "map_bit": 0,
        "depends_on_map_bits": [
       ] /* depends_on_map_bits */
      }
     ] /* table_dependencies */
    },
    {
      "ref_optimizer_key_uses": [   //使用键
     ] /* ref_optimizer_key_uses */
    },
    {
      "rows_estimation": [   //行判断
      {
        "table": "`student`",
        "range_analysis": {
         "table_scan": {
          "rows": 3973767,
          "cost": 408558
        } /* table_scan */,   //扫描表
         "potential_range_indexes": [   //潜在的范围索引
         {
           "index": "PRIMARY",
           "usable": true,
           "key_parts": [
            "id"
          ] /* key_parts */
         }
        ] /* potential_range_indexes */,
         "setup_range_conditions": [   //设置范围条件
        ] /* setup_range_conditions */,
         "group_index_range": {
          "chosen": false,
          "cause": "not_group_by_or_distinct"
        } /* group_index_range */,
         "skip_scan_range": {
          "potential_skip_scan_indexes": [
          {
            "index": "PRIMARY",
            "usable": false,
            "cause": "query_references_nonkey_column"
          }
         ] /* potential_skip_scan_indexes */
        } /* skip_scan_range */,
         "analyzing_range_alternatives": {  //分析范围选项
          "range_scan_alternatives": [
          {
"index": "PRIMARY",
            "ranges": [
             "id < 10"
           ] /* ranges */,
            "index_dives_for_eq_ranges": true,
            "rowid_ordered": true,
            "using_mrr": false,
            "index_only": false,
            "rows": 9,
            "cost": 1.91986,
            "chosen": true
          }
         ] /* range_scan_alternatives */,
          "analyzing_roworder_intersect": {
           "usable": false,
           "cause": "too_few_roworder_scans"
         } /* analyzing_roworder_intersect */
        } /* analyzing_range_alternatives */,
         "chosen_range_access_summary": {   //选择范围访问摘要
          "range_access_plan": {
           "type": "range_scan",
           "index": "PRIMARY",
           "rows": 9,
           "ranges": [
            "id < 10"
          ] /* ranges */
         } /* range_access_plan */,
          "rows_for_plan": 9,
          "cost_for_plan": 1.91986,
          "chosen": true
        } /* chosen_range_access_summary */
       } /* range_analysis */
      }
     ] /* rows_estimation */
    },
    {
      "considered_execution_plans": [  //考虑执行计划
      {
        "plan_prefix": [
       ] /* plan_prefix */,
        "table": "`student`",
        "best_access_path": {  //最佳访问路径
         "considered_access_paths": [
         {
           "rows_to_scan": 9,
           "access_type": "range",
           "range_details": {
            "used_index": "PRIMARY"
          } /* range_details */,
           "resulting_rows": 9,
           "cost": 2.81986,
           "chosen": true
         }
        ] /* considered_access_paths */
       } /* best_access_path */,
        "condition_filtering_pct": 100,  //行过滤百分比
        "rows_for_plan": 9,
        "cost_for_plan": 2.81986,
        "chosen": true
      }
     ] /* considered_execution_plans */
    },
    {
      "attaching_conditions_to_tables": {  //将条件附加到表上
       "original_condition": "(`student`.`id` < 10)",
       "attached_conditions_computation": [
      ] /* attached_conditions_computation */,
       "attached_conditions_summary": [  //附加条件概要
       {
         "table": "`student`",
         "attached": "(`student`.`id` < 10)"
       }
      ] /* attached_conditions_summary */
     } /* attaching_conditions_to_tables */
    },
    {
      "finalizing_table_conditions": [
      {
        "table": "`student`",
        "original_table_condition": "(`student`.`id` < 10)",
        "final_table_condition  ": "(`student`.`id` < 10)"
      }
     ] /* finalizing_table_conditions */
    },
    {
      "refine_plan": [  //精简计划
      {
        "table": "`student`"
      }
     ] /* refine_plan */
    }
   ] /* steps */
  } /* join_optimization */
 },
 {
   "join_execution": {   //执行
    "select#": 1,
    "steps": [
   ] /* steps */
  } /* join_execution */
 }
] /* steps */
}
//第3部分:跟踪信息过长时,被截断的跟踪信息的字节数。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0  //丢失的超出最大容量的字节
//第4部分:执行跟踪语句的用户是否有查看对象的权限。当不具有权限时,该列信息为1且TRACE字段为空,一般在
调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下,会出现此问题。
INSUFFICIENT_PRIVILEGES: 0  //缺失权限
1 row in set (0.00 sec)

9. MySQL 监控分析视图 sys schema

9.1 Sys schema视图摘要

performace-schemainformation-schema 可以用来分析数据库性能,mysql 5.7 还设计了 sys schema 整合了上面两个 schema,还让它们以视图方式显示,更易于理解

  1. 主机相关:以 host_summary 开头,主要汇总了 IO 延迟的信息。
  2. Innodb 相关:以 innodb 开头,汇总了 innodb buffer 信息和事务等待 innodb 锁的信息。
  3. I/O相关:以 IO 开头,汇总了等待 I/O、I/O 使用量情况。
  4. 内存使用情况:以 memory 开头,从主机、线程、事件等角度展示内存的使用情况
  5. 连接与会话信息:processlist 和 session 相关视图,总结了会话相关信息。
  6. 表相关:以 schema_table 开头的视图,展示了表的统计信息。
  7. 索引信息:统计了索引的使用情况,包含冗余索引和未使用的索引情况。
  8. 语句相关:以 statement 开头,包含执行全表扫描、使用临时表、排序等的语句信息。
  9. 用户相关:以 user 开头的视图,统计了用户使用的文件 I/O、执行语句统计信息。
  10. 等待事件相关信息:以 wait 开头,展示等待事件的延迟情况。

9.2 Sys schema视图使用场景

索引情况

#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相关

#查看消耗磁盘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 相关

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

END

本文作者:
文章标题:第18章-性能分析工具的使用
本文地址:https://www.pendulumye.com/mysql/325.html
版权说明:若无注明,本文皆个人学习记录原创,转载请保留文章出处。
最后修改:2022 年 07 月 07 日
千山万水总是情,给个一毛行不行💋