IT评测·应用市场-qidao123.com技术社区

标题: mysql Explain解读 [打印本页]
作者: 立山    时间: 2025-4-19 00:27
标题: mysql Explain解读

  1. Explain 含义

我们发现查询瓶颈，优化查询性能。 Explain 是 SQL 分析工具中非常紧张的一个功能，它可以模拟优化器实验查询语句，帮助我们明白查询是怎样实验的；分析查询实验计划可以帮助
2. Explain 作用

表的读取顺序 - SQL 实验时查询操作类型 - 可以利用哪些索引 - 每张表有多少行记录被扫描 - SQL 语句性能分析
3. Explain 用法

数据预备
  1. drop table orders;
  2. drop table products;
  3. drop table users;
  4. CREATE TABLE users (
  5.     id INT PRIMARY KEY AUTO_INCREMENT,
  6.     name VARCHAR(50) NOT NULL,
  7.     email VARCHAR(100) NOT NULL,
  8.     password VARCHAR(100) NOT NULL,
  9.     ENGINE=InnoDB DEFAULT CHARSET=utf8
  10. );
  11. CREATE TABLE products (
  12.     id INT PRIMARY KEY AUTO_INCREMENT,
  13.     name VARCHAR(50) NOT NULL,
  14.     price FLOAT NOT NULL,
  15.     ENGINE=InnoDB DEFAULT CHARSET=utf8
  16. );
  17. CREATE TABLE orders (
  18.     id INT PRIMARY KEY AUTO_INCREMENT,
  19.     user_id INT NOT NULL,
  20.     order_date DATETIME NOT NULL,
  21.     total_price FLOAT NOT NULL,
  22.     product_id INT NOT NULL,
  23.     FOREIGN KEY (user_id) REFERENCES users(id),
  24.     FOREIGN KEY (product_id) REFERENCES products(id),
  25.     ENGINE=InnoDB DEFAULT CHARSET=utf8
  26. );
  27. alter table users add index index_name_email (name,email);
  28. INSERT INTO users (name, email, password)
  29. VALUES ('张三', 'zhangsan@example.com', 'password123'),
  30.        ('李四', 'lisi@example.com', 'password123'),
  31.        ('王五', 'wangwu@example.com', 'password123'),
  32.        ('赵六', 'zhaoliu@example.com', 'password123'),
  33.        ('钱七', 'qianqi@example.com', 'password123');
  34. INSERT INTO products (name, price)
  35. VALUES ('产品1', 10.00),
  36.        ('产品2', 15.00),
  37.        ('产品3', 20.00),
  38.        ('产品4', 12.00),
  39.        ('产品5', 18.00);
  40. INSERT INTO orders (user_id, order_date, total_price, product_id)
  41. VALUES (1, '2023-02-18 10:00:00', 100.00, 1),
  42.        (2, '2023-02-18 11:00:00', 50.00, 2),
  43.        (3, '2023-02-18 12:00:00', 20.00, 3),
  44.        (4, '2023-02-18 13:00:00', 15.00, 4),
  45.        (5, '2023-02-18 14:00:00', 25.00, 5);
复制代码
MySQL 5.7 版本之前，利用 Explain Extended 在 Explain 的基础上额外多返回 filtered 列与 extra 列：
  1. Explain Extended select * from users;
复制代码

MySQL 5.7 版本之前，利用 Explain Partitions 在 Explain 的基础上额外多返回 partitions 列：
  1. Explain Partitions select * from users;
复制代码

MySQL 5.7 版本引入了这两个特性，直接利用 Explain 关键字可以将 partitions 列、filtered 列、extra 列直接查询出来。
  1. Explain select * from users;
复制代码


Explain 语句返回列的各列含义：

这些查询各人先留一个印象，后续会详细讲解。
4. Explain 返回列详解

接下来我们将展示 explain 中每个列的信息。
1. id 列：每个 select 都有一个对应的 id 号，并且是从 1 开始自增的。
2. select_type 列：表示查询语句实验的查询操作类型
2.1 simple：简单 select，不包罗 union 与子查询
 
  1. Explain select * from users;
复制代码


毗连查询
  1. Explain select * from users inner join orders on users.id = orders.user_id;
复制代码


2.2 primary：复杂查询中最外层查询，比如利用 union 或者 union all 时，id 为 1 的记录 select_type 通常是primary
  1. explain select id from users union select id from products;
复制代码


2.3 subquery：指在 select 语句中出现的子查询语句，结果不依靠于外部查询（不在 from 语句中）
 
  1. explain select orders.*,(select name from products where id = 1) from orders;
复制代码
2.4 dependent subquery：指在 select 语句中出现的查询语句，结果依靠于外部查询
 
  1. explain select orders.*,(select name from products where products.id = orders.user_id) from orders;
复制代码
2.5 derived：派生表，在 FROM 子句的查询语句，表示从外部数据源中推导出来的，而不是从 SELECT 语句中的其他列中选择出来的。
 
  1. set session optimizer_switch='derived_merge=off'; #关闭 MySQL5.7 对衍生表合并优化
  3. explain select * from (select user_id from orders where id = 1) as temp;
  5. set session optimizer_switch='derived_merge=on'; #还原配置
复制代码

2.6 union：分 union 与 union all 两种，若第二个 select 出如今 union 之后，则被标记为 union；假如 union 被 from 子句的子查询包含，那么第一个 select 会被标记为 derived；union 会针对相同的结果集举行去重，union all 不会举行去重处理。
 
  1. explain
  2. select * from (
  3. select id from products where price = 10
  4. union
  5. select id from orders where user_id in (1,2)
  6. union
  7. select id from users where name = '张三' ) as temp;
复制代码


  1. explain
  2. select * from (
  3. select id from products where price = 10
  4. union all
  5. select id from orders where user_id in (1,2)
  6. union all
  7. select id from users where name = '张三' ) as temp;
复制代码


2.7 dependent union：当 union 作为子查询时，其中第一个 union 为 dependent subquery，第二个 union 为 dependent union。
  1. explain
  2. select * from orders where id in (
  3. select id from products where price = 10
  4. union
  5. select id from orders where user_id = 2
  6. union
  7. select id from users where name = '张三' );
复制代码


2.8 union result：假如两个查询中有相同的列，则会对这些列举行重复删除，只保留一个表中的列。
  1. explain
  2. select id from users
  3. union
  4. select id from products;
复制代码



3. table 列：查询所涉及的表名。假如有多个表，将显示多行记录
4. partitions 列：表的分区环境。详细来说，它会显示出查询语句在哪些分区上实验，以及是否利用了分区裁剪等信息。假如没有分区，该项为 NULL。
5. type 列：查询所利用的访问类型
6. possible_keys 列：表示在查询中大概利用到某个索引或多个索引；假如没有选择索引，显示 NULL
7. key 列：表示在查询中现实利用的索引，假如没有利用索引，显示 NULL。
8. key_len 列：表示查询中现实利用的索引，假如实验查询时，该索引记录的最大长度（主要利用在团结索引）
团结索引可以通过这个值算出详细利用了索引中的哪些列。
利用单例索引：
  1. explain  
  2. select * from users where id = 1;
复制代码

利用团结索引：
  1. explain
  2. select * from users where name = '张三' and email = 'zhangsan@example.com';
复制代码

盘算规则：
        char(n)：n 个字节
        varchar(n)：假如是 utf - 8：3 * n + 2 字节，加的 2 个字节存储字符串长度，假如是 utf8mb4：4 * n + 2 字节
        tinyint：1个字节
        smallint：2 字节
        int：4 字节
        bigint：8 字节
        date：3 字节
        datetime：8 字节 字段假如为 NULL，需要半个字节记录是否为 NULL
        timestamp：4 字节
9. ref 列：表示将哪个字段或常量与 key 列所利用的索引举行比较。
10. rows 列：表示查询需要扫描的大概行数。可以利用 rows * filtered/100 盘算出与 explain 前一个表举行毗连的行数。
11. filtered 列：表示符合查询条件的数据行百分比。可以利用 rows * filtered/100 盘算出与 explain 前一个表举行毗连的行数。
12. Extra 列：SQL 实验查询的一些额外信息
12.1.Using Index：利用非主键索引树就可以查询所需要的数据。一般是覆盖索引，即查询列都包含在辅助索引树叶子节点中，不需要回表查询。
  1. explain
  2. select user_id,id from orders where user_id = 1;
复制代码


12.2 Using where：不通过索引查询所需的数据
  1. explain
  2. select * from orders where total_price = 100;
  4. explain
  5. select * from orders where user_id = 1 and total_price = 100;
复制代码

12.3 Using index condition：表示查询不被索引覆盖，where 条件中是一个索引范围查找，过滤索引后回表找到全部符合条件的数据行
 
  1. explain
  2. select * from orders where user_id > 3;
复制代码

12.4 Using temporary：需要创建一张临时表来处理查询

1.total_price列无索引，需要创建一张临时表举行去重
  1. explain
  2. select distinct total_price from orders;
复制代码

2.name列有团结索引
  1. explain
  2. select distinct name from users;
复制代码


12.5 Using filesort：当查询中包含 order by 操作而无法利用索引完成的排序操作，数据较少时从内存排序，假如数据较多需要在磁盘中排序

1.total_price 列无索引，无法通过索引举行排序。需要先保存 total_price 与对应的主键 id，然后在排序 total_price 查找数据。
  1. explain
  2. select total_price from orders order by total_price;
复制代码

2.name 列有索引，因索引已经是排序好的所以直接读取就可以了。
  1. explain
  2. select name from users order by name;
复制代码

12.6.Select tables optimized away：利用某些聚合函数（min,max）来访问某个索引值。
  1. explain
  2. select min(id) from users;
  3. explain
  4. select min(password) from users;
复制代码

总结
精确公道利用 MySQL explain 可以帮助我们更好地明白查询实验计划，并确定怎样最好地优化查询 SQL，提升 SQL 性能，增加体系稳定性。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/) Powered by Discuz! X3.4