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);
- 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;
Explain 语句返回列的各列含义：
这些查询各人先留一个印象，后续会详细讲解。
4. Explain 返回列详解
接下来我们将展示 explain 中每个列的信息。
1. id 列：每个 select 都有一个对应的 id 号，并且是从 1 开始自增的。
2. select_type 列：表示查询语句实验的查询操作类型
2.1 simple：简单 select，不包罗 union 与子查询
- Explain select * from users;
毗连查询
- Explain select * from users inner join orders on users.id = orders.user_id;
2.2 primary：复杂查询中最外层查询，比如利用 union 或者 union all 时，id 为 1 的记录 select_type 通常是primary
- explain select id from users union select id from products;
2.3 subquery：指在 select 语句中出现的子查询语句，结果不依靠于外部查询（不在 from 语句中）
- explain select orders.*,(select name from products where id = 1) from orders;
- explain select orders.*,(select name from products where products.id = orders.user_id) from orders;
- 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'; #还原配置
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;
- 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;
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 = '张三' );
2.8 union result：假如两个查询中有相同的列，则会对这些列举行重复删除，只保留一个表中的列。
- explain
- select id from users
- union
- select id from products;
3. table 列：查询所涉及的表名。假如有多个表，将显示多行记录
4. partitions 列：表的分区环境。详细来说，它会显示出查询语句在哪些分区上实验，以及是否利用了分区裁剪等信息。假如没有分区，该项为 NULL。
5. type 列：查询所利用的访问类型
6. possible_keys 列：表示在查询中大概利用到某个索引或多个索引；假如没有选择索引，显示 NULL
7. key 列：表示在查询中现实利用的索引，假如没有利用索引，显示 NULL。
8. key_len 列：表示查询中现实利用的索引，假如实验查询时，该索引记录的最大长度（主要利用在团结索引）
团结索引可以通过这个值算出详细利用了索引中的哪些列。
利用单例索引：
- explain
- select * from users where id = 1;
利用团结索引：
- explain
- 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：利用非主键索引树就可以查询所需要的数据。一般是覆盖索引，即查询列都包含在辅助索引树叶子节点中，不需要回表查询。
- explain
- select user_id,id from orders where user_id = 1;
12.2 Using where：不通过索引查询所需的数据
- explain
- select * from orders where total_price = 100;
- explain
- select * from orders where user_id = 1 and total_price = 100;
12.3 Using index condition：表示查询不被索引覆盖，where 条件中是一个索引范围查找，过滤索引后回表找到全部符合条件的数据行
- explain
- select * from orders where user_id > 3;
12.4 Using temporary：需要创建一张临时表来处理查询
1.total_price列无索引，需要创建一张临时表举行去重
- explain
- select distinct total_price from orders;
2.name列有团结索引
- explain
- select distinct name from users;
12.5 Using filesort：当查询中包含 order by 操作而无法利用索引完成的排序操作，数据较少时从内存排序，假如数据较多需要在磁盘中排序
1.total_price 列无索引，无法通过索引举行排序。需要先保存 total_price 与对应的主键 id，然后在排序 total_price 查找数据。
- explain
- select total_price from orders order by total_price;
2.name 列有索引，因索引已经是排序好的所以直接读取就可以了。
- explain
- select name from users order by name;
12.6.Select tables optimized away：利用某些聚合函数（min,max）来访问某个索引值。
- explain
- select min(id) from users;
- explain
- select min(password) from users;
总结
精确公道利用 MySQL explain 可以帮助我们更好地明白查询实验计划，并确定怎样最好地优化查询 SQL，提升 SQL 性能，增加体系稳定性。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
