MySQL -- 09 -- MySQL调优之explain工具（8.0）

最新推荐文章于 2025-04-24 14:20:29 发布

Gene Xu

最新推荐文章于 2025-04-24 14:20:29 发布

阅读量434

点赞数

分类专栏： MySQL 文章标签： MySQL explain

本文链接：https://blog.csdn.net/Goodbye_Youth/article/details/108121607

版权

本文深入探讨MySQL的explain工具，解析SQL执行计划，涵盖查询类型、连接类型、索引使用及性能瓶颈分析，助您优化数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原文链接：MySQL – 09 – MySQL调优之explain工具（8.0）

使用 explain 可以模拟优化器执行 SQL 语句，从而知道 MySQL 是如何处理 SQL 语句、如何分析查询语句、如何分析表结构的性能瓶颈

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行数据被优化器查询

此外，除了 select 语句，其他如：insert、update、delete、replace 等语句也可以使用 explain 来查询执行计划

官方文档：EXPLAIN Output Format

一、创建测试数据

生成思路
- 利用 MySQL 内存表插入快的特点，可以先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中
- 这里我们来生成一张具有 1000 条数据的学生信息表

创建普通表和内存表

普通表

DROP TABLE IF EXISTS tbl_student;
CREATE TABLE tbl_student (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid VARCHAR(40) NOT NULL COMMENT '学生唯一标识',
  name VARCHAR(20) NOT NULL COMMENT '学生姓名',
  age INT(3) UNSIGNED NOT NULL COMMENT '学生年龄',
  sex TINYINT(1) UNSIGNED NOT NULL COMMENT '学生性别: 1=男，0=女',
  PRIMARY KEY (id),
  UNIQUE KEY uk_uuid (uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表';

内存表

DROP TABLE IF EXISTS tbl_student_memory;
CREATE TABLE tbl_student_memory (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid VARCHAR(40) NOT NULL COMMENT '学生唯一标识',
    name VARCHAR(20) NOT NULL COMMENT '学生姓名',
    age INT(3) UNSIGNED NOT NULL COMMENT '学生年龄',
    sex TINYINT(1) UNSIGNED NOT NULL COMMENT '学生性别: 1=男，0=女',
    PRIMARY KEY (id),
    UNIQUE KEY uk_uuid (uuid)
) ENGINE=Memory DEFAULT CHARSET=utf8 COMMENT='学生信息表';

创建随机函数

生成随机姓名

DROP FUNCTION IF EXISTS generate_student_name;
CREATE FUNCTION generate_student_name(n INT) RETURNS VARCHAR(20)
BEGIN 
    DECLARE source_name VARCHAR(60) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
    DECLARE return_name VARCHAR(20) DEFAULT '' ;
    DECLARE i INT DEFAULT 0; 
    WHILE i < n DO 
        SET return_name = CONCAT(return_name, SUBSTRING(source_name , FLOOR(1 + RAND() * 62 ), 1)); 
        SET i = i +1; 
    END WHILE; 
    RETURN return_name; 
END;

生成随机年龄

DROP FUNCTION IF EXISTS generate_student_age;
CREATE FUNCTION generate_student_age() RETURNS INT(2)
BEGIN 
    DECLARE return_age INT(2) DEFAULT 20;
    SET return_age = return_age + RAND() * 10; 
    RETURN return_age; 
END;

生成随机性别

DROP FUNCTION IF EXISTS generate_student_sex;
CREATE FUNCTION generate_student_sex() RETURNS TINYINT(1)
BEGIN 
    DECLARE return_sex TINYINT(1) DEFAULT 1 ;
    SET return_sex = (CASE WHEN RAND() >= 0.5 THEN 1 ELSE 0 END); 
    RETURN return_sex; 
END;

创建存储过程

DROP PROCEDURE IF EXISTS add_student_record;
CREATE PROCEDURE add_student_record(IN n int)
BEGIN  
    DECLARE i INT DEFAULT 1;
    WHILE (i <= n) DO
      INSERT INTO tbl_student_memory (uuid, name, age, sex ) 
      VALUES (UUID(), generate_student_name(20), generate_student_age(), generate_student_sex());
      SET i = i + 1;
    END WHILE;
END;

调用存储过程
```
CALL add_student_record(1000);
```

插入普通表中

INSERT INTO tbl_student SELECT * FROM tbl_student_memory;

统计学生表

mysql> select count(*) from tbl_student;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

PS：如果要生成大量数据，需要提前在 MySQL 配置文件中设置两个参数，然后重启 MySQL
```
tmp_table_size=1024M
max_heap_table_size=1024M
```
- tmp_table_size
  - 内存临时表的最大值，超过该限制后会记录到磁盘当中，存储在指定的 tmpdir 目录下
```
mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)
```
- max_heap_table_size
  - 用户可以创建的内存表大小，该值用于计算内存表的最大行数值

二、调用 explain

mysql> explain select name from tbl_student t order by name desc;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

三、参数说明

查询序号，表示执行 select 语句的执行顺序

当 id 相同时，从上到下顺序执行；当 id 不同时，id 越大的越先执行

mysql> explain select t1.uuid, t2.name from tbl_student t1, tbl_student t2 where t2.name = t1.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select name from tbl_student t1 union select name from tbl_student t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
|  2 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

select_type

查询类型，主要用于区别普通查询、联合查询、子查询等复杂查询，此处只展示了常见的分类

SIMPLE

最简单的 select 语句，查询中不包括子查询和 union

mysql> explain select name from tbl_student t;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

PRIMARY

最外层的 select 语句，在使用 union 的查询语句中是最前面的 select 语句

mysql> explain select name from tbl_student t1 where id = (select id from tbl_student t2 where id = 1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys

最低0.47元/天解锁文章