Postrge-SQL技术社区mysql Explain解读 - Powered by Discuz! Archiver

立山发表于 7 天前

mysql Explain解读

https://i-blog.csdnimg.cn/direct/13365e1b286f44bdbf8767a1716a1535.png
1. Explain 含义

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

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

数据预备
drop table orders;
drop table products;
drop table users;
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL,
ENGINE=InnoDB DEFAULT CHARSET=utf8
);
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
price FLOAT NOT NULL,
ENGINE=InnoDB DEFAULT CHARSET=utf8
);
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
order_date DATETIME NOT NULL,
total_price FLOAT NOT NULL,
product_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id),
ENGINE=InnoDB DEFAULT CHARSET=utf8
);
alter table users add index index_name_email (name,email);
INSERT INTO users (name, email, password)
VALUES ('张三', 'zhangsan@example.com', 'password123'),
   ('李四', 'lisi@example.com', 'password123'),
   ('王五', 'wangwu@example.com', 'password123'),
   ('赵六', 'zhaoliu@example.com', 'password123'),
   ('钱七', 'qianqi@example.com', 'password123');
INSERT INTO products (name, price)
VALUES ('产品1', 10.00),
   ('产品2', 15.00),
   ('产品3', 20.00),
   ('产品4', 12.00),
   ('产品5', 18.00);
INSERT INTO orders (user_id, order_date, total_price, product_id)
VALUES (1, '2023-02-18 10:00:00', 100.00, 1),
   (2, '2023-02-18 11:00:00', 50.00, 2),
   (3, '2023-02-18 12:00:00', 20.00, 3),
   (4, '2023-02-18 13:00:00', 15.00, 4),
   (5, '2023-02-18 14:00:00', 25.00, 5); MySQL 5.7 版本之前，利用 Explain Extended 在 Explain 的基础上额外多返回 filtered 列与 extra 列：
Explain Extended select * from users;
MySQL 5.7 版本之前，利用 Explain Partitions 在 Explain 的基础上额外多返回 partitions 列：
Explain Partitions select * from users;
MySQL 5.7 版本引入了这两个特性，直接利用 Explain 关键字可以将 partitions 列、filtered 列、extra 列直接查询出来。
Explain select * from users;
https://i-blog.csdnimg.cn/direct/8f359a71653c433aa0a88724c0142538.png
Explain 语句返回列的各列含义：
https://i-blog.csdnimg.cn/direct/2cdac2e1cd8645d298945d52f8a31bb7.png
这些查询各人先留一个印象，后续会详细讲解。
4. Explain 返回列详解

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

Explain select * from users;
https://i-blog.csdnimg.cn/direct/a615f46a1f3942a68cd887f870511a0e.png
毗连查询
Explain select * from users inner join orders on users.id = orders.user_id;
https://i-blog.csdnimg.cn/direct/64e383e86aa84e99a66b2970ac149962.png
2.2 primary：复杂查询中最外层查询，比如利用 union 或者 union all 时，id 为 1 的记录 select_type 通常是primary
explain select id from users union select id from products;
https://i-blog.csdnimg.cn/direct/7f977a7ebb6347bb98970780a496c8bd.png
2.3 subquery：指在 select 语句中出现的子查询语句，结果不依靠于外部查询（不在 from 语句中）

explain select orders.*,(select name from products where id = 1) from orders; https://i-blog.csdnimg.cn/direct/a5dc1ff8c3f4451198cc8e42a102eef0.png2.4 dependent subquery：指在 select 语句中出现的查询语句，结果依靠于外部查询

explain select orders.*,(select name from products where products.id = orders.user_id) from orders; https://i-blog.csdnimg.cn/direct/a673a87203ac41919be2eb14ce00cc03.png2.5 derived：派生表，在 FROM 子句的查询语句，表示从外部数据源中推导出来的，而不是从 SELECT 语句中的其他列中选择出来的。

set session optimizer_switch='derived_merge=off'; #关闭 MySQL5.7 对衍生表合并优化

explain select * from (select user_id from orders where id = 1) as temp;

set session optimizer_switch='derived_merge=on'; #还原配置
https://i-blog.csdnimg.cn/direct/925fc560d5df48a3b367b5746d93ed59.png
2.6 union：分 union 与 union all 两种，若第二个 select 出如今 union 之后，则被标记为 union；假如 union 被 from 子句的子查询包含，那么第一个 select 会被标记为 derived；union 会针对相同的结果集举行去重，union all 不会举行去重处理。

explain
select * from (
select id from products where price = 10
union
select id from orders where user_id in (1,2)
union
select id from users where name = '张三' ) as temp;
https://i-blog.csdnimg.cn/direct/504e2e2ea0a247af89282fdc47ea552a.png
explain
select * from (
select id from products where price = 10
union all
select id from orders where user_id in (1,2)
union all
select id from users where name = '张三' ) as temp;
https://i-blog.csdnimg.cn/direct/7555fdb891ae40f9ba7204dd20fdf127.png
2.7 dependent union：当 union 作为子查询时，其中第一个 union 为 dependent subquery，第二个 union 为 dependent union。
explain
select * from orders where id in (
select id from products where price = 10
union
select id from orders where user_id = 2
union
select id from users where name = '张三' );
https://i-blog.csdnimg.cn/direct/dccf07eeb0424c86942f47070950b0a3.png
2.8 union result：假如两个查询中有相同的列，则会对这些列举行重复删除，只保留一个表中的列。
explain
select id from users
union
select id from products;
https://i-blog.csdnimg.cn/direct/9108d3fc37e54c13900bb7cdcebd6144.png
https://i-blog.csdnimg.cn/direct/33b0d67cae5e4d4e8811f22ac58270d9.png
3. table 列：查询所涉及的表名。假如有多个表，将显示多行记录
4. partitions 列：表的分区环境。详细来说，它会显示出查询语句在哪些分区上实验，以及是否利用了分区裁剪等信息。假如没有分区，该项为 NULL。
5. type 列：查询所利用的访问类型
6. possible_keys 列：表示在查询中大概利用到某个索引或多个索引；假如没有选择索引，显示 NULL
7. key 列：表示在查询中现实利用的索引，假如没有利用索引，显示 NULL。
8. key_len 列：表示查询中现实利用的索引，假如实验查询时，该索引记录的最大长度（主要利用在团结索引）
团结索引可以通过这个值算出详细利用了索引中的哪些列。
利用单例索引：
explain
select * from users where id = 1;
https://i-blog.csdnimg.cn/direct/8cd1db092c5840b99a96a1c898faf5ae.png利用团结索引：
explain
select * from users where name = '张三' and email = 'zhangsan@example.com';
https://i-blog.csdnimg.cn/direct/a94faf4991174f0c8e1568ffb250f8b5.png 盘算规则：
        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：利用非主键索引树就可以查询所需要的数据。一般是覆盖索引，即查询列都包含在辅助索引树叶子节点中，不需要回表查询。
explain
select user_id,id from orders where user_id = 1;
https://i-blog.csdnimg.cn/direct/25bf89c129d14207aab4f64bb8a0dddd.png
12.2 Using where：不通过索引查询所需的数据
explain
select * from orders where total_price = 100;

explain
select * from orders where user_id = 1 and total_price = 100; https://i-blog.csdnimg.cn/direct/74dc17796a3045acb71bab812d55c33e.png
12.3 Using index condition：表示查询不被索引覆盖，where 条件中是一个索引范围查找，过滤索引后回表找到全部符合条件的数据行

explain
select * from orders where user_id > 3; https://i-blog.csdnimg.cn/direct/b837897a5fc849dd9710db9009eae04f.png
12.4 Using temporary：需要创建一张临时表来处理查询

1.total_price列无索引，需要创建一张临时表举行去重
explain
select distinct total_price from orders; https://i-blog.csdnimg.cn/direct/5ceabd4bbcd541f4830f7aefd60b8a87.png
2.name列有团结索引
explain
select distinct name from users;
https://i-blog.csdnimg.cn/direct/3e5228b61d324340afe101912d594a37.png
12.5 Using filesort：当查询中包含 order by 操作而无法利用索引完成的排序操作，数据较少时从内存排序，假如数据较多需要在磁盘中排序

1.total_price 列无索引，无法通过索引举行排序。需要先保存 total_price 与对应的主键 id，然后在排序 total_price 查找数据。
explain
select total_price from orders order by total_price;
https://i-blog.csdnimg.cn/direct/1a4e9788df9a40338f00d2e44000ee06.png
2.name 列有索引，因索引已经是排序好的所以直接读取就可以了。
explain
select name from users order by name;
https://i-blog.csdnimg.cn/direct/80943a0fc98d4d988827c2272d1f7612.png
12.6.Select tables optimized away：利用某些聚合函数（min,max）来访问某个索引值。
explain
select min(id) from users;
explain
select min(password) from users;
https://i-blog.csdnimg.cn/direct/445d7fd56cd947b2b6ce234abbcfb3a8.png
总结
精确公道利用 MySQL explain 可以帮助我们更好地明白查询实验计划，并确定怎样最好地优化查询 SQL，提升 SQL 性能，增加体系稳定性。

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

页: [1]

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

mysql Explain解读