-
第09章 性能分析工具的使用【2.索引及调优篇】【MySQL高级】
第09章 性能分析工具的使用
前言
2022/8/3 15:23
暑假学习ing
推荐
【MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！-哔哩哔哩】
第09章 性能分析工具的使用
在数据库调优中，我们的目标就是
响应时间更快，哈吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式1. 数据库服务器的优化步骤
当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。
整个流程划分成了
观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）
我们可以通过观察了解数据库整体的运行状态，通过性能分析工具可以让我们了解执行慢的SQL都有哪些，查看具体的SQL 执行计划，甚至是SQL执行中的每一步的成本代价，这样才能定位问题所在，找到了问题，再采取相应的行动
详细解释一下这张图:
首先在S1部分，需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动，有可能是周期性节点的原因，比如双十一、促销活动等。这样的话，可以通过A1这一步骤解决，也就是加缓存，或者更改缓存失效策略。如果缓存策略没有解决，或者不是周期性波动的原因，就需要进一步分析查询延迟和卡顿的原因。接下来进入S2这一步，需要开启慢查询。慢查询可以帮我们定位执行慢的SQL语句。可以通过设置long_query_time参数定义“慢""的阈值，如果SQL执行时间超过了long query_time，则会认为是慢查询。当收集上来这些慢查询之后，就可以通过分析工具对慢查询日志进行分析。
在S3这一步骤中，我们就知道了执行慢的SQL，这样就可以针对性地用EXPLAIN查看对应SQL语句的执行计划，或者使用show profile查看SQL中每一个步骤的时间成本。这样就可以了解SQL查询慢是因为执行时间长，还是等待时间长。
如果是SQL等待时间长，就进入A2步骤。在这一步骤中，可以调优服务器的参数，比如适当增加数据库缓冲池等。如果是SQL执行时间长，就进入A3步骤，这一步中需要考虑是索引设计的问题?还是查询关联的数据表过多?还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。
如果A2和A3都不能解决问题，需要考虑数据库自身的SQL查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤。如果已经达到了性能瓶颈，进入A4阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库、垂直分表和水平分表等。以上就是数据库调忧的流程思路。如果发现执行SQL时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的SQL，这三种分析工具可以理解是SQL调优的三个步骤:慢查询、EXPLAIN和SHOEW PROFILING
小结：
2.查看系统性能参数
在MySQL中，可以使用
SHOW STATUS语句查询一些MySQL数据库服务器的性能参数、执行频率。
SHOW STATUS语句语法如下：SHOW [GLOBAL|SESSION] STATUS LIKE '参数';- 1
- 2
一些常用的性能参数如下：
- 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'; /* +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 44 | +---------------+-------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 若查询服务器工作时间，则可以执行如下语句:
show status like 'uptime'; /* +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Uptime | 460878| +---------------+--------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 若查询MySQL服务器的慢查询次数，则可以执行如下语句:
SHOW STATUS LIKE 'slow_queries' ; /* +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 0 | +---------------+-------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。再比如，如下的指令可以查看相关的指令情况:
SHOW STATUS LIKE 'Innodb_rows_%'; /* +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Innodb_rows_deleted | 0 | | Innodb_rows_inserted | 224 | | Innodb_rows_read | 214 | | Innodb_rows_updated | 0 | +----------------------+-------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
3. 统计SQL的查询成本：last_query_cost
一条SQL查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。
如果想要查看某条SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL语句所需要读取的页的数量
我们依然使用第8章的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;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
如果想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：
SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id = 900001; #运行结果（1 条记录，运行时间为 0.042s ）- 1
- 2
- 3
- 4
然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：
SHOW STATUS LIKE 'last_query_cost'; /* +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | Last_query_cost | 1.000000 | +-----------------+----------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
如果要查询 id 在 900001 到 9000100 之间的学生记录呢？
SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id BETWEEN 900001 AND 900100;- 1
- 2
运行结果（100 条记录，运行时间为
0.046s）然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询
SHOW STATUS LIKE 'last_query_cost'; /* +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | Last_query_cost | 21.134453 | +-----------------+-----------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然
页数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。使用场景：它对于比较开销是非常有用的，特别是有好几种查询方式可选的时候
SQL查询是一个动态的过程，从页加载的角度来看，可以得到以下两点结论:
位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。批量决定效率。如果从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms)，而采用顺序读取的方式，批主对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。
所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到
缓冲池中，其次可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。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
在使用前，需要先看下慢查询是否已经开启，使用下面这条命令即可:show variables like 'slow_query_log'; /* +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | OFF | +----------------+-------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
我们能看到slow_query_log=OFF，可以把慢查询日志打开，注意设置变量值的时候需要使用global，否则会报错:
set global slow_query_log='ON';- 1
再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：
show variables like '%slow_query_log%'; +---------------------+-----------------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/centos7-mysql-1-slow.log | +---------------------+-----------------------------------------+ 2 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
能看到这时慢查询分析已经开启，同时文件保存在/var/lib/mysql/LSLNO1-slow.log 文件中
2. 修改long_query_time阈值
接下来看下慢查询的时间阈值设置，使用如下命令：
show variables like '%long_query_time%'; /* +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
这里如果想把时间缩短，比如设置为 1 秒，可以这样设置：
#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句 set global long_query_time = 1; show global variables like '%long_query_time%'; set long_query_time=1; show variables like '%long_query_time%'; /* +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
补充:配置文件中一并设置参数
如下的方式相较于前面的命令行方式，可以看作是永久设置的方式
修改my.cnf文件，[mysqld]下增加或修改参数long_query_time、slow_query_log和slow_query_log_file后，然后重启MySQL服务器[mysqld] slow_query_log=ON#开启慢查询日志的开关 slow_query_log_file=/var/lib/mysql/atguigu-slow.log#慢查询日志的目录和文件名信息 long_query_time=3 #设置慢查询的阈值为3秒。超出此设定值的SQL即被记录到慢查询日志 log_output=FTLE- 1
- 2
- 3
- 4
- 5
如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下。如果不指定文件名，默认文件名为hostname-slow.log
4.2 查看慢查询数目
查询当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%'; /* +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 0 | +---------------+-------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
4.3 案例演示
步骤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;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
步骤2：设置参数log_bin_trust_function_creators
创建函数，假如报错：This function has none of DETERMINISTIC…命令开启：允许创建函数设置
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。- 1
步骤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); /* +------------------+ | rand_num(10,100) | +------------------+ | 23 | +------------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
步骤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 ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
步骤5：调用存储过程
#调用刚刚写好的函数, 4000000条记录,从100001号开始 CALL insert_stu1(100001,4000000);- 1
- 2
- 3
mysql> SELECT COUNT(*) FROM student; +----------+ | COUNT(*) | +----------+ | 4000000 | +----------+ 1 row in set (0.70 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
4.4 测试及分析
1. 测试
SELECT * FROM student WHERE stuno = 3455655; /* +---------+---------+--------+------+---------+ | id | stuno | name | age | classId | +---------+---------+--------+------+---------+ | 3355654 | 3455655 | IHnqPe | 14 | 943 | +---------+---------+--------+------+---------+ 1 row in set (1.33 sec) */ SELECT * FROM student WHERE name = 'IHnqPe '; /* +---------+---------+--------+------+---------+ | id | stuno | name | age | classId | +---------+---------+--------+------+---------+ | 2264898 | 2364899 | IHnqPe | 14 | 968 | | 2802437 | 2902438 | IHnqPe | 13 | 933 | | 2947412 | 3047413 | IHnqPe | 13 | 929 | | 3355654 | 3455655 | IHnqPe | 14 | 943 | | 3432555 | 3532556 | IhNqpE | 15 | 507 | +---------+---------+--------+------+---------+ 5 rows in set (1.43 sec) */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
从上面的结果可以看出来，查询学生编号为“3455655”的学生信息花费时间为1.33 秒。查询学生姓名为“IHnqPe”的学生信息花费时间为1.43 秒。已经达到了秒的数量级，说明目前查询效率是比较低的，下面我们分析一下原因:
2. 分析
show status like 'slow_queries'; /* +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 2 | +---------------+-------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
补充说明:
除了上述变量，控制慢查询日志的还有一个系统变量: min_examined_row_limit。这个变量的意思是，查询扫描过的最少记录数。这个变晕和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time的值，那么，这个查询就被记录到慢查询日志中;反之，则不被记录到慢查询日志中。
mysql> show variables like ‘min%’;
/*
±-----------------------±------+
| Variable_name | Value |
±-----------------------±------+
| min_examined_row_limit | 0 |
±-----------------------±------+
1 row in set (0.02 sec)
*/
这个值默认是0。与long_query_time=10合在一起，表示只要查询的执行时间超过10秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。也可以根据需要，通过修“my.ini"文件，来修改查询时长，或者通过SET指令，用SQL语句修改“min_examined_row_limit"的值。4.5 慢查询日志分析工具：mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具
mysqldumpslow查看mysqldumpslow的帮助信息
#注意：下面语句不是在mysql>下执行，而要在根目录下执行 [root@centos7-mysql-1 ~]# mysqldumpslow --help /* Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -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 */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
mysqldumpslow 命令的具体参数如下:
-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：- c: 访问次数
- l: 锁定时间
- r: 返回记录
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间 （默认方式）
- ac:平均查询次数
-t :即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的下面演示如何得到慢查询：
首先找到慢查询文件在哪个目录下：#首先找到慢查询文件在哪个目录下： [root@centos7-mysql-1 ~]# cd /var/lib/mysql [root@centos7-mysql-1 mysql]# ll #运行结构可知目录为LSLNO1-slow.log /* 总用量 902900 drwxr-x---. 2 mysql mysql 67 8月 3 18:23 atguigudb1 -rw-r-----. 1 mysql mysql 56 7月 25 19:53 auto.cnf -rw-r-----. 1 mysql mysql 498 7月 25 20:34 binlog.000001 -rw-r-----. 1 mysql mysql 1400 7月 27 18:19 binlog.000002 -rw-r-----. 1 mysql mysql 7004 7月 28 20:02 binlog.000003 -rw-r-----. 1 mysql mysql 647264629 8月 3 18:33 binlog.000004 -rw-r-----. 1 mysql mysql 64 7月 29 10:00 binlog.index -rw-------. 1 mysql mysql 1680 7月 25 19:53 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 7月 25 19:53 ca.pem -rw-r-----. 1 mysql mysql 901 8月 3 18:43 centos7-mysql-1-slow.log -rw-r--r--. 1 mysql mysql 1112 7月 25 19:53 client-cert.pem -rw-------. 1 mysql mysql 1676 7月 25 19:53 client-key.pem drwxr-x---. 2 mysql mysql 4096 7月 31 11:15 dbtest1 drwxr-x---. 2 mysql mysql 242 8月 3 13:21 dbtest2 -rw-r-----. 1 mysql mysql 196608 8月 3 18:33 #ib_16384_0.dblwr -rw-r-----. 1 mysql mysql 8585216 8月 3 14:18 #ib_16384_1.dblwr -rw-r-----. 1 mysql mysql 3795 7月 28 20:02 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 8月 3 18:32 ibdata1 -rw-r-----. 1 mysql mysql 50331648 8月 3 18:33 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 8月 3 18:33 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 7月 29 10:00 ibtmp1 drwxr-x---. 2 mysql mysql 187 7月 29 10:00 #innodb_temp drwxr-x---. 2 mysql mysql 143 7月 25 19:53 mysql -rw-r-----. 1 mysql mysql 25165824 8月 3 18:33 mysql.ibd srwxrwxrwx. 1 mysql mysql 0 7月 29 10:00 mysql.sock -rw-------. 1 mysql mysql 5 7月 29 10:00 mysql.sock.lock drwxr-x---. 2 mysql mysql 8192 7月 25 19:53 performance_schema -rw-------. 1 mysql mysql 1680 7月 25 19:53 private_key.pem -rw-r--r--. 1 mysql mysql 452 7月 25 19:53 public_key.pem -rw-r--r--. 1 mysql mysql 1112 7月 25 19:53 server-cert.pem -rw-------. 1 mysql mysql 1676 7月 25 19:53 server-key.pem drwxr-x---. 2 mysql mysql 28 7月 25 19:53 sys -rw-r-----. 1 mysql mysql 83886080 8月 3 18:33 undo_001 -rw-r-----. 1 mysql mysql 33554432 8月 3 18:32 undo_002 */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：
#查看前五条 SQL 语句 [root@centos7-mysql-1 mysql]# mysqldumpslow -s t -t 5 /var/lib/mysql/centos7-mysql-1-slow.log /*查询结果 Count: 1 Time=251.55s (251s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[192.168.253.134] CALL insert_stu1(N,N) Count: 1 Time=1.43s (1s) Lock=0.00s (0s) Rows=5.0 (5), root[root]@localhost SELECT * FROM student WHERE name = 'S' Count: 1 Time=1.29s (1s) Lock=0.03s (0s) Rows=1.0 (1), root[root]@localhost SELECT * FROM student WHERE stuno = N Died at /usr/bin/mysqldumpslow line 162, <> chunk 3. */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
不想替换S、N 用
-a花费时间降序排列[root@centos7-mysql-1 mysql]# mysqldumpslow -a -s t -t 5 /var/lib/mysql/centos7-mysql-1-slow.log Reading mysql slow query log from /var/lib/mysql/centos7-mysql-1-slow.log Count: 1 Time=251.55s (251s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[192.168.253.134] CALL insert_stu1(100001,4000000) Count: 1 Time=1.43s (1s) Lock=0.00s (0s) Rows=5.0 (5), root[root]@localhost SELECT * FROM student WHERE name = 'IHnqPe ' Count: 1 Time=1.29s (1s) Lock=0.03s (0s) Rows=1.0 (1), root[root]@localhost SELECT * FROM student WHERE stuno = 3455655 Died at /usr/bin/mysqldumpslow line 162, <> chunk 3.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
工作常用参考：
#得到返回记录集最多的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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
4.6 关闭慢查询日志
MySQL服务器停止慢查询日志功能有两种方法：
方式1：永久性方式
[mysqld] slow_query_log=OFF- 1
- 2
或者，把slow_query_log一项注释掉 或 删除
[mysqld] #slow_query_log =OFF- 1
- 2
重启MySQL服务，执行如下语句查询慢日志功能。
SHOW VARIABLES LIKE ‘%slow%’; #查询慢查询日志所在目录 SHOW VARIABLES LIKE ‘%long_query_time%’; #查询超时时长- 1
- 2
可以看到,MySQL系统中的慢查询日志是关闭的。
方式2：临时性方式
使用SET语句来设置。
（1）停止MySQL慢查询日志功能，具体SQL语句如下SET GLOBAL slow_query_log=off; SHOW VARIABLES LIKE '%slow%'; /* +---------------------------+--------------------------------+ | Variable_name | Value | +---------------------------+--------------------------------+ | log_slow_admin_statements | OFF | | log_slow_extra | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/LSLNO1-slow.log | +---------------------------+--------------------------------+ */ #以及 SHOW VARIABLES LIKE '%long_query_time%'; /* +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
（2）重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下
#在根目录下重启服务 [root@centos7-mysql-1 mysql]# systemctl restart mysqld mysql> SHOW VARIABLES LIKE '%long_query_time%'; /* 重启后阈值恢复默认值10 +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
4.7 删除慢查询日志
查询并调优完后该日志没有用了，可以将其删除
使用SHOW语句显示慢查询日志信息，具体SQL语句如下show variables like 'slow_query_log%'; /* +---------------------+-----------------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/centos7-mysql-1-slow.log | +---------------------+-----------------------------------------+ 2 rows in set (0.00 sec) */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日志文件即可
[root@LSLNO1 mysql]# rm centos7-mysql-1-slow.log rm：是否删除普通文件 "LSLNO1-slow.log"？y- 1
- 2
使用命令
mysqladmin flush-logs来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件mysqladmin -uroot -p flush-logs slow- 1
# 在打开的条件下，执行上面的语句，才可以看见重置的日志文件 SET GLOBAL slow_query_log=on;- 1
- 2
提示:
慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须享先备份。5. 查看 SQL 执行成本：SHOW PROFILE
show profile在《逻辑架构》章节中讲过，这里作为复习。
Show Profile是MySQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。
可以在会话级别开启这个功能show variables like 'profiling'; /* +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
通过设profiling='ON’来开启show profile :
set profiling = 'ON';- 1
show profile使用演示：
use atguigudb; select * from student where stuno =343455; -- select * from student where name = 'vyituS'; -- show profiles; /* +----------+------------+---------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------------+ | 1 | 0.00174700 | show variables like 'profiling' | | 2 | 1.52700950 | select * from student where stuno =343455 | | 3 | 1.20279475 | select * from student where name = 'vyituS' | +----------+------------+---------------------------------------------+ 3 rows in set, 1 warning (0.00 sec) */ show profile; /* +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000083 | | Executing hook on transaction | 0.000004 | | starting | 0.000009 | | checking permissions | 0.000006 | | Opening tables | 0.000044 | | init | 0.000004 | | System lock | 0.000008 | | optimizing | 0.000008 | | statistics | 0.000019 | | preparing | 0.000018 | | executing | 1.202507 | | end | 0.000024 | | query end | 0.000005 | | waiting for handler commit | 0.000010 | | closing tables | 0.000012 | | freeing items | 0.000023 | | cleaning up | 0.000013 | +--------------------------------+----------+ 17 rows in set, 1 warning (0.00 sec) */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
我们也可以查看指定的Query lD的开销，比如
show profile for query 2查询结果是一样的。在SHOWPROFILE中我们可以查看不同部分的开销，比如cpu、block.io等:mysql> show profile cpu, block io for query 2; +--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000111 | 0.000066 | 0.000039 | 0 | 0 | | Executing hook on transaction | 0.000006 | 0.000002 | 0.000001 | 0 | 0 | | starting | 0.000009 | 0.000005 | 0.000003 | 0 | 0 | | checking permissions | 0.000006 | 0.000004 | 0.000003 | 0 | 0 | | Opening tables | 0.000044 | 0.000028 | 0.000016 | 0 | 0 | | init | 0.000004 | 0.000002 | 0.000002 | 0 | 0 | | System lock | 0.000008 | 0.000005 | 0.000002 | 0 | 0 | | optimizing | 0.000009 | 0.000006 | 0.000004 | 0 | 0 | | statistics | 0.000057 | 0.000036 | 0.000022 | 0 | 0 | | preparing | 0.000023 | 0.000013 | 0.000008 | 0 | 0 | | executing | 1.526632 | 1.208456 | 0.519889 | 361248 | 0 | | end | 0.000021 | 0.000008 | 0.000005 | 0 | 0 | | query end | 0.000005 | 0.000003 | 0.000001 | 0 | 0 | | waiting for handler commit | 0.000012 | 0.000008 | 0.000005 | 0 | 0 | | closing tables | 0.000013 | 0.000008 | 0.000004 | 0 | 0 | | freeing items | 0.000037 | 0.000024 | 0.000014 | 0 | 0 | | cleaning up | 0.000016 | 0.000009 | 0.000006 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+ 17 rows in set, 1 warning (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
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诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。
注意:
不过 SHOW PROFILE命令将被弃用，可以从 information_schema中的profiling 数据表进行查看6. 分析查询语句：EXPLAIN
6.1概述
定位了查询慢的SQL之后，就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。
MySQL中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的
执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。
MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，大家看懂EXPLAIN语句的各个输出项，可以有针对性的提升查询语句的性能。1.能做什么?
- 表的读取顺序
- 数据读取操作的操作类型。
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
2.官网介绍
https://dev.mysql.com/doc/refman/5.7/en/explain-output.htmlhttps://dev.mysql.com/doc/refman/8.0/en/explain-output.html
3.版本情况
- 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 SELECT * FROM student_info; SELECT * FROM student_info LIMIT 10; DESCRIBE DELETE FROM student_info WHERE id=2;- 1
- 2
- 3
- 4
- 5
6.2 基本语法
EXPLAIN 或 DESCRIBE语句的语法形式如下：
EXPLAIN SELECT select_options #或者 DESCRIBE SELECT select_options- 1
- 2
- 3
如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：
EXPLAIN SELECT 1; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
输出的上述信息就是所谓的
执行计划。在这个执行计划的辅助下，我们需要知道应该怎样改进自己的查询语句以使查询执行起来更高效。其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE语句等都可以加上EXPLAIN，用来查看这些语句的执行计划，只是平时我们对SELECT语句更感兴趣注意: 执行EXPLAIN时并没有真正的执行该后面的语句，因此可以安全的查看执行计划。
EXPLAIN语句输出的各个列的作用如下：列名 描述 id在一个大的查询语句中每个SELECT关键字都对应一个 唯一的idselect_typeSELECT关键字对应的那个查询的类型 table表名 partitions匹配的分区信息 type针对单表的访问方法 possible_keys可能用到的索引 key实际上使用的索引 key_len实际使用到的索引长度(单位：字节) ref当使用索引列等值查询时，与索引列进行等值匹配的对象信息 rows预估的需要读取的记录条数 filtered某个表经过搜索条件过滤后剩余记录条数的百分比 Extra一些额外的信息 在这里把它们都列出来只是为了描述—个轮廓，让大家有一个大致的印象。
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;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
#表二 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;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
2. 设置参数log_bin_trust_function_creators
创建函数，假如报错，需开启如下命令：允许创建函数设置：set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。- 1
3. 创建存储函数
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 ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
4. 创建存储过程
创建往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 ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
创建往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 ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
5. 调用存储过程
s1表数据的添加：加入1万条记录：CALL insert_s1(10001,10000);- 1
s2表数据的添加：加入1万条记录：
CALL insert_s2(10001,10000);- 1
SELECT COUNT(*) FROM s1;#10000 SELECT COUNT(*) FROM s2;#10000- 1
- 2
- 3
6.4 EXPLAIN各列作用
为了有比较好的体验，调整了下
EXPLAIN输出列的顺序。1.table
不论查询语句有多复杂，里边
包含了多少个表，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。演示
#1. table：表名 #查询的每一行记录都对应着一个单表 EXPLAIN SELECT * FROM s1; /*单表查询返回一行 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ */ #s1:驱动表 s2:被驱动表 EXPLAIN SELECT * FROM s1 INNER JOIN s2; /*查询的每一行记录都对应着一个单表: 多表查询返回多行 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
2.id
演示
#2. id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id #-->通常出现了几个select关键字就对应几个id SELECT * FROM s1 WHERE key1 = 'a';#id值1 SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';#id值1 SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2);#id值1、2 SELECT * FROM s1 UNION SELECT * FROM s2; EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; /*id值1 +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 INNER JOIN s2;#多表查询 /*id值1、1 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ */ EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; /*id值1、2 +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ */ ######查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作######## EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a'); /*两个select却只出现一个id-->查询优化器将该语句转成了多表查询 +----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+------+----------+------------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 1 | SIMPLE | s2 | NULL | eq_ref | idx_key2 | idx_key2 | 5 | atguigudb.s1.key1 | 1 | 10.00 | Using index condition; Using where | +----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+------+----------+------------------------------------+ */ #Union去重-->UNION取并集并去重时产生一个临时表- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
小结:
- id如果相同，可以认为是一组，从上往下顺序执行
- 在所有组中。id值越大。优先级越高。越先执行
- 关注点: id号每个号码。表示一趟独立的查询,一个sql的查询趟数越少越好
3.select_type
一条大的查询语句里边可以包含若干个SELECT关键字，
每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表（这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为
select_type的属性，意思是只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，我们看一下select_type都能取哪些值，请看官方文档:名称 描述 SIMPLE Simple SELECT (not using UNION or subqueries) PRIMARY Outermost SELECT UNION Second or later SELECT statement in a UNION UNION RESULT Result of a UNION SUBQUERY First SELECT in subquery DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query DERIVED Derived table MATERIALIZED Materialized subquery UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated foreach row of the outer query UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) 演示
#3. select_type：SELECT关键字对应的那个查询的类型,确定小查询在整个大查询中扮演了一个什么角色 # 查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型 EXPLAIN SELECT * FROM s1; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ */ #连接查询也算是`SIMPLE`类型 EXPLAIN SELECT * FROM s1 INNER JOIN s2; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ */ #对于包含`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; /*临时表的select_type-->UNION RESULT +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | NULL | UNION RESULT || NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | DERIVED | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ */ #当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时， #该子查询对应的`select_type`属性就是`MATERIALIZED` EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); #子查询被转为了物化表 /* +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 1 | SIMPLE || NULL | eq_ref | | | 303 | atguigudb.s1.key1 | 1 | 100.00 | NULL | | 2 | MATERIALIZED | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------+------+----------+-------------+ */ - 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
4. partitions (可略) :匹配的分区信息
- 代表分区表中的命中情况，非分区表，该项为
NULL。一般情况下的查询语句的执行计划的partitions列的值都是NULL - https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html
- 如果想详细了解，可以如下方式测试。创建分区表：
-- 创建分区表， -- 按照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 );- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则
DESC SELECT * FROM user_partitions WHERE id>200; /* +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user_partitions | p1 | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where | +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
5. type ☆
执行计划的一条记录就代表着MySQL对某个表的
执行查询时的访问方法，又称"访问类型"，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。完整的访问方法如下：
system，const，eq_ref，ref，fulltext，ref_or_null，
index_merge，unique_subquery，index_subquery，range，index，ALL。我们详细解释一下：
system：
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。比方说我们新建一个NyISA表，并为其后入—条记录:演示
# 5. type：针对单表的访问方法 #当表中`只有一条记录`并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory， #那么对该表的访问方法就是`system`。 CREATE TABLE t(i INT) ENGINE=MYISAM; INSERT INTO t VALUES(1); #system-->性能最高的场景 EXPLAIN SELECT * FROM t; /* +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ */ #再添加 INSERT INTO t VALUES(1); #EXPLAIN SELECT * FROM t; 就会变成all #换成InnoDB CREATE TABLE tt(i INT) ENGINE=INNODB; INSERT INTO tt VALUES(1); EXPLAIN SELECT * FROM tt; #all /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | tt | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ */ #当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是`const` EXPLAIN SELECT * FROM s1 WHERE id = 10005; /*select_type为`const`-->访问效率为常数级 +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key2 = 10066; /* +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | idx_key2 | idx_key2 | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ */ #all EXPLAIN SELECT * FROM s1 WHERE key3 = 10066; #在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的 #（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则 # 驱动表 all 对该被驱动表的访问方法就是`eq_ref` EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; /* `eq_ref` +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | atguigudb.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ */ #当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref` EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ */ #为什么 EXPLAIN SELECT * FROM s1 WHERE key3 = 10066; 是all 因为key3是varchar类型 10066需要函数转换，涉及函数是不会用到索引的 #加上''就是 ref EXPLAIN SELECT * FROM s1 WHERE key3 = '10066'; #当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法 #就可能是`ref_or_null` EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL; /* +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | ref_or_null | idx_key1 | idx_key1 | 303 | const | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ */ #单表访问方法时在某些场景下可以使用`Intersection`、`Union`、 #`Sort-Union`这三种索引合并的方式来执行查询 OR的并集关系使两个索引都将用到 `index_merge` EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; /* +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL | 2 | 100.00 | Using union(idx_key1,idx_key3); Using where | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ */ #`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'; /* +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | unique_subquery | PRIMARY,idx_key1 | PRIMARY | 4 | func | 1 | 10.00 | Using where | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ */ #如果使用索引获取某些`范围区间`的记录，那么就可能使用到`range`访问方法 EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c'); /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 3 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ */ #同上 EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b'; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 367 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ */ #当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index` # 索引覆盖 用到了联合索引idx_key_part EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; /* +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | s1 | NULL | index | idx_key_part | idx_key_part | 909 | NULL | 9895 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ */ #最熟悉的全表扫描 `all` EXPLAIN SELECT * FROM s1; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
小结：
结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL其中比较重要的几个提取出来（见上图中的蓝色）。
SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 const级别。（阿里巴巴开发手册要求）6. possible_keys和key
在EXPLAIN语句输出的执行计划中, p
ossible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引
比方说下边这个查询:EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a'; /* +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1,idx_key3 | idx_key3 | 303 | const | 1 | 5.00 | Using where | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
上述执行计划的
possible_keys列的值是idx_key1 , idx_key3，表示该查询可能使用到idx_key1, idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定使用哪一个来执行语句。演示
#6. possible_keys和key：可能用到的索引 和 实际上使用的索引 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a'; /* +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1,idx_key3 | idx_key3 | 303 | const | 1 | 5.00 | Using where | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
7. key_len
演示
#7. key_len：实际使用到的索引长度(即：字节数) # 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。 EXPLAIN SELECT * FROM s1 WHERE id = 10005; # int 主键 4个字节 /* +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key2 = 10126; # int 唯一索引可能为null 4+1=5 /* +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | idx_key2 | idx_key2 | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; # varchar 100 utf8 100*3 +1(null) +2(实践长度） /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a'; #303 /* +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b'; #606 /* +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 606 | const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'; #909 EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a'; #没有使用索引，null #练习： #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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
8. ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref ,ref_or_null、unique_subquery、 index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的结构是什么，比如只是一个常数或者是某个列。大家看下边这个查询:EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数，当然有时候更复杂一点:
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; /* +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | atguigudb.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
演示
# 8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。 #比如只是一个常数或者是某个列。 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; /* +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | atguigudb.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1); /* +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | func | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
9. rows ☆
演示
# 9. rows：预估的需要读取的记录条数 # `值越小越好` EXPLAIN SELECT * FROM s1 WHERE key1 > 'z'; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 374 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------- */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
10.filtered
越高越好，说明查的数据都是有效的
演示# 10. filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比 #如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用 #到对应索引的搜索条件外的其他搜索条件的记录有多少条。 #越高越好，说明查的数据都是有效的 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a'; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 374 | 10.00 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ */ #对于单表查询来说，这个filtered列的值没什么意义，我们`更关注在连接查询 #中驱动表对应的执行计划记录的filtered值`，它决定了被驱动表要执行的次数(即：rows * filtered) EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 10.00 | Using where | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | atguigudb.s1.key1 | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
11.Extra☆
顾名思义，
Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。可以通过这些额外信息来更准确的理辑MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑比较重要的额外信息介绍给大家。No tables used
当直询语句的没有
FROM子句时将会提示该额外信息，比如:# 11.Extra:一些额外的信息 # 更准确的理解MySQL到底将如何执行给定的查询语句 #当查询语句的没有`FROM`子句时将会提示该额外信息 No tables used EXPLAIN SELECT 1; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
Impossible WHERE
#查询语句的`WHERE`子句永远为`FALSE`时将会提示该额外信息 Impossible WHERE EXPLAIN SELECT * FROM s1 WHERE 1 != 1; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
Using where
#当使用全表扫描来执行对某个表的查询，并且该语句的`WHERE` #子句中有针对该表的搜索条件时，在`Extra`列中会提示上述额外信息。 Using where EXPLAIN SELECT * FROM s1 WHERE common_field = 'a'; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ */ #当使用索引访问来执行对某个表的查询，并且该语句的`WHERE`子句中 #有除了该索引包含的列之外的其他搜索条件时，在`Extra`列中也会提示上述额外信息。 USING WHERE EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
No matching min/max row
#当查询列表处有`MIN`或者`MAX`聚合函数，但是并没有符合`WHERE`子句中 #的搜索条件的记录时，将会提示该额外信息 No matching min/max row EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg'; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ */ # SELECT * FROM s1 LIMIT 10; EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'znOhuD'; #znOhuD 是 s1表中key1字段真实存在的数据 Select tables optimized away /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
Using index
#当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以 #使用覆盖索引的情况下，在`Extra`列将会提示该额外信息 Using index 。比方说下边这个查询中只 #需要用到`idx_key1`而不需要回表操作： #覆盖索引 不需要回表操作 直接可以通过二级索引查到数据 # EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'; EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
Using index condition
#Using index condition：查找使用了索引,但是需要回表查询数据 #有些搜索条件中虽然出现了索引列，但却不能使用到索引 #看课件理解索引条件下推 Using index condition #先找key1 > 'z' 再找key1 LIKE '%a'，最后回表 *， 优化 key1 > 'z' 再回表 * 最后 key1 LIKE '%a' EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a'; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 374 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
using where; using index的组合:
查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using join buffer (Block Nested Loop)
#在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为 #其分配一块名叫`join buffer`的内存块来加快查询速度，也就是我们所讲的`基于块的嵌套循环算法` #见课件说明 Using where; Using join buffer (hash join) EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 10.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
Not exists
#当我们使用左（外）连接时，如果`WHERE`子句中包含要求被驱动表的某个列等于`NULL`值的搜索条件， #而且那个列又是不允许存储`NULL`值的，那么在该表的执行计划的Extra列就会提示`Not exists`额外信息 Using where; Not exists EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | atguigudb.s1.key1 | 1 | 10.00 | Using where; Not exists | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
Using intersect(…) 、 Using union(…) 和 Using sort_union(…)
#如果执行计划的`Extra`列出现了`Using intersect(...)`提示，说明准备使用`Intersect`索引 #合并的方式执行查询，括号中的`...`表示需要进行索引合并的索引名称； #如果出现了`Using union(...)`提示，说明准备使用`Union`索引合并的方式执行查询； #出现了`Using sort_union(...)`提示，说明准备使用`Sort-Union`索引合并的方式执行查询。 Using union(idx_key1,idx_key3); Using where EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; /* +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL | 2 | 100.00 | Using union(idx_key1,idx_key3); Using where | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
Zero limit
#当我们的`LIMIT`子句的参数为`0`时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息 Zero limit EXPLAIN SELECT * FROM s1 LIMIT 0; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Zero limit | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
Using filesort
有一些情况下对结果集中的记录进行排序是可以使用到索引的，比如下边这个查询:
#有一些情况下对结果集中的记录进行排序是可以使用到索引的。 NULL #比如： EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | index | NULL | idx_key1 | 303 | NULL | 10 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
这个查询语句可以利用idx_key1索引直接取出key1列的10条记录，然后再进行回表操作就好了，但是很多情况下排序操作无法使用到索引，只能在内存中(记录较少的时候）或名磁盘中，(记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序〔英文名∶ filesort )。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示，比如这样:
#很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候） #进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：`filesort`）。 #如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的`Extra`列中显示`Using filesort`提示 EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
Using temporary
#在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们 #在执行许多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中，如果不能有效利用索引来完成 #查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行 #计划的`Extra`列将会显示`Using temporary`提示 EXPLAIN SELECT DISTINCT common_field FROM s1; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ */ #EXPLAIN SELECT DISTINCT key1 FROM s1; #同上。 EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ */ #执行计划中出现`Using temporary`并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以 #我们`最好能使用索引来替代掉使用临时表`。比如：扫描指定的索引idx_key1即可 Using index EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
12.小结
- EXPLAIN不考各种Cache
- EXPLAIN不能显示MySQL在执行查询时所作的优化工作
- EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
- 部分统计信息是估算的，并非精确值
7. EXPLAIN的进一步使用
7.1 EXPLAIN四种输出格式
这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式：
传统格式，JSON格式，TREE格式以及可视化输出。用户可以根据需要选择适用于自己的格式1. 传统格式
传统格式简单明了，输出是一个表格形式，概要说明查询计划
EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | s2 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 90.00 | Using where | | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | atguigudb.s2.key1 | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
2. JSON格式
第1种格式中介绍的
EXPLAIN语句输出中缺少了一个衡量执行计划好坏的重要属性——成本。而JSON格式是四种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。- JSON格式：在EXPLAIN单词和真正的查询语句中间加上 FORMAT=JSON
EXPLAIN FORMAT=JSON SELECT ...- 1
- EXPLAIN的column与JSON的对应关系:〔来源于MySQL 5.7文档)
这样就可以得到一个json格式的执行计划，里面包含该计划花费的成本，比如这样:#json格式的explain EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'; /* +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, #整个查询语句只有1个SELECT关键字，该关键字对应的id号为1 "cost_info": { "query_cost": "1360.07" #整个查询的执行成本预计为3197.16 }, "nested_loop": [#几个表之间采用版套循环连按算法执行 #以下是参与嵌套循环连接算法的各个表的信息 { "table": { "table_name": "s1", #s1是驱动表 "access_type": "ALL",#访问方法为ALL，意味着使用全表扫描访问 "possible_keys": [ #可能使用的索引 "idx_key1" ], "rows_examined_per_scan": 9688, #查询一次s1表大致需要9895条记录 "rows_produced_per_join": 968, #驱动表s1的扇出是968 "filtered": "10.00", #condition filtering代表的百分比 "cost_info": { "read_cost": "914.80", #稍后解释 "eval_cost": "98.95", #稍后解释 "prefix_cost": "1013.75", #单次查询s1表总共的成本 "data_read_per_join": "1M" #读取的数据量 }, "used_columns": [ #执行查询中涉及的列 "id", "key1", "key2", "key3", "key_part1", "key_part2", "key_part3", "common_field" ], #对s1表访问时针对单表查询的条件 "attached_condition": "((`atguigudb`.`s1`.`common_field` = 'a') and (`atguigudb`.`s1`.`key1` is not null))" } }, { "table": { "table_name": "s2", #s2是被驱动表 "access_type": "ref",#访问方法为ref，意味着使用索引等值匹配的方式访问 "possible_keys": [ #可能使用的索引 "idx_key2" ], "key": "idx_key2", #实际使用的索引 "used_key_parts": [ #实际使用的索引列 "key2" ], "key_length": "5", #key_len "ref": [ #与key2列等值匹配的对象 "atguigudb.s1.key1" ], "rows_examined_per_scan": 1, #查询一次s1表大致需要扫描1条记录 "rows_produced_per_join": 968, #被驱动表s1的扇出是968（由于后面没有多余的表进行连接，所以这个值也没啥用） "filtered": "100.00", #condition filtering代表的百分比 #对s1表访问时针对单表查询的条件 "index_condition": "(cast(`atguigudb`.`s1`.`key1` as double) = cast(`atguigudb`.`s2`.`key2` as double))", "cost_info": { "read_cost": "247.38", #稍后解释 "eval_cost": "98.95", #稍后解释 "prefix_cost": "1360.08", #单次查询s2表总共的成本 "data_read_per_join": "1M" #读取的数据量 }, "used_columns": [ #执行查询中涉及的列 "id", "key1", "key2", "key3", "key_part1", "key_part2", "key_part3", "common_field" ] } } ] } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
我们使用
#后边跟随注释的形式为大家解释了EXPLAIN FORMAT=JSON语句的输出内容，但是大家可能有疑问 “cost_info” 里边的成本看着怪怪的，它们是怎么计算出来的？先看s1表的 “cost_info” 部分："cost_info": { "read_cost": "914.80", "eval_cost": "98.95", "prefix_cost": "1013.75", "data_read_per_join": "1M" }- 1
- 2
- 3
- 4
- 5
- 6
-
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_costdata_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” }- 1
- 2
- 3
- 4
- 5
- 6
由于
s2表是被驱动表，所以可能被读取多次，这里的read_cost和eval_cost是访问多次 s2 表后累加起来的值，大家主要关注里边儿的prefix_cost的值代表的是整个连接查询预计的成本，也就是单次查询s1表和多次查询s2表后的成本的和，也就是：968.80 + 193.76 + 2034.60 = 3197.16- 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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
4. 可视化输出
可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标，即可生成可视化的查询计划。
上图按从左到右的连接顺序显示表。红色框表示
全表扫描，而绿色框表示使用索引查找。对于每个表，显示使用的索引。还要注意的是，每个表格的框上方是每个表访问所发现的行数的估计值以及访问该表的成本7.2 SHOW WARNINGS的使用
在使用
EXPLAIN语句查看了某个查询的执行计划后，紧接着还可以使用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; SHOW WARNINGS\G #Message后的“语句”可以近似看作真正执行的情况 #通常通过SHOW WARNINGS可以看到优化器对查询语句的重写和优化 /* *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `atguigudb`.`s1`.`key1` AS `key1`,`atguigudb`.`s2`.`key1` AS `key1` from `atguigudb`.`s1` join `atguigudb`.`s2` where ((`atguigudb`.`s1`.`key1` = `atguigudb`.`s2`.`key1`) and (`atguigudb`.`s2`.`common_field` is not null)) */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
可以看到
SHOW WARNINGS展示出来的信息有三个字段，分别是Level、Code、Message。最常见的就是code为1003的信息，当Code信为1003时,Message字段展示的信息类似于查询优化器将查询语句重写后的语句。比如上边的查询本来是一个左(外）连接查询，但是有一个s2.common_field IS NOT NULL的条件，这就会导致查询优化器把左（外〉连接查询优化为内连接查询，从SHOW WARNINGS的Message字段也可以看出来，原本的LEFTJOIN已经变成了JOIN。小结：
通过SHOW WARNINGS可以看到优化器对查询语句的重写和优化8. 分析优化器执行计划：trace
OPTIMIZER_TRACE是MySQL 5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策〈比如访问表的方法、各种开销计算、各种转换等)，并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。此功能默认关闭。开启trace，并设置格式为JSON，同时设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示
#开启trace SET optimizer_trace="enabled=on",end_markers_in_json=on; #设置trace最大能够使用的内存大小： set optimizer_trace_max_mem_size=1000000;- 1
- 2
- 3
- 4
- 5
开启后，可分析如下语句：
- SELECT
- INSERT
- REPLACE
- UPDATE
- DELETE
- EXPLAIN
- SET
- DECLARE
- CASE
- IF
- RETURN
- CALL
测试：执行如下SQL语句
select * from student where id < 10; /* +----+--------+--------+------+---------+ | id | stuno | name | age | classId | +----+--------+--------+------+---------+ | 1 | 100002 | tolqVJ | 47 | 158 | | 2 | 100003 | Ahispv | 29 | 811 | | 3 | 100004 | uEKNLo | 32 | 397 | | 4 | 100005 | lXcxdY | 68 | 310 | | 5 | 100006 | FeFNbQ | 15 | 829 | | 6 | 100007 | XoAMhC | 38 | 927 | | 7 | 100008 | IEUKTn | 71 | 593 | | 8 | 100009 | VQtuTi | 27 | 494 | | 9 | 100010 | SQBkvx | 13 | 825 | +----+--------+--------+------+---------+ 9 rows in set (0.00 sec) */- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
最后， 查询 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 ] /* 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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
9. MySQL监控分析视图-sys schema
关于MySQL的性能监控和问题诊断，一般都从performance_schema中去获取想要的数据，在MySQL5.7.7版本中新增sys schema，它将performance_schema和information_schema中的数据以更容易理解的方式总结归纳为"视图"，其目的就是为了
降低查询performance_schema的复杂度，让DBA能够快遇的定位问题。下面看看这些库中都有哪些监控表和视图，学握了这些，在开发和运维的过程中就起到了事半功倍的效果。9.1 Sys 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
- 2
- 3
- 4
- 5
- 6
- 7
表相关
# 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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
语句相关
#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;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
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;- 1
- 2
- 3
Innodb 相关
#1. 行锁阻塞情况 select * from sys.innodb_lock_waits- 1
- 2
风险提示:
通过sys库去查询时，MySQL会消耗大量资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。建议生产上不要频繁的去查询sys或者performance_schema、information_schema来完成监控、巡检等工作。10.小结
查询是数据库中最频繁的操作，提高查询速度可以有效地提高MysQL数据库的性能。通过对查询语句的分析可以了解查询语句的执行能力。找出查询语句的瓶颈，从而优化查询语句。
最后
2022/8/4 15:47
p134~p140
Markdown 91583 字数 2760 行数
HTML 89405 字数 2017 段落
-
相关阅读:
猿创征文 | 开箱即用 yyg-cli：快速创建 vue3 组件库和vue3 全家桶项目
安卓离线点击第一条通知跳转正常，第二条失败或者通过URL Scheme唤起App指定页面第一次成功，第二次失败
笔记59：序列到序列学习Seq2seq
捷码赋能案例：湖南天辰产研实力迅速提升！实战玩转智慧楼宇/工地等项目
【ESP-S3-BOX-Lite花屏问题】：Github下载源码（出厂源码factory_demo）编译调试到ESP-S3-BOX-Lite中出现花屏现象
Go 中的数据竞争模型
【牛客编程题】GO语言入门46题
每日一练 | 华为认证真题练习Day118
虚拟筛选、高通量实验筛选化合物库
unity shader全局雾效
- 原文地址：https://blog.csdn.net/qq_51625007/article/details/126141216
