大家好,欢迎来到IT知识分享网。
引言:在 Java 数据库操作中,理解 SQL 语句的执行计划是优化查询性能的关键之一。Explain 是一个重要的 SQL 语句,它能够显示数据库如何执行某个查询,帮助开发者识别性能瓶颈和优化查询。在面试中,被问到如何使用 Explain 以及如何分析其输出结果,是对开发者理解数据库优化的一个重要考核点。本文将带你深入了解 Explain 语句的使用方法和分析技巧,帮助你在面试和实际开发中,能够自信且清晰地阐述查询优化的工作原理和应用场景。
题目
面试官听到我对 explain 使用的回答竖起了大拇指
推荐解析
今天碰见一个面试官问我:用过 explain 吗?说说怎么分析的?
我:哈哈哈撞我枪口上了,让我来震惊你一下!
我:你好面试官,我当然用过 explain,我平时都会那它去查看 SQL 语句是否还能优化。接下来我从主要属性跟实际例子来讲解:
主要的属性
id
查询中每个 SELECT 子句的标识符。简单查询的 id 通常为 1,复杂查询(如包含子查询或 UNION)的 id 会有多个。
select_type
描述查询的类型。比如:简单查询显示为 SIMPLE,子查询显示为 SUBQUERY,UNION 中的第二个和后续查询显示为 UNION。
table
表名称这个就不用再详细解释了吧哈哈。
partitions
表示查询涉及到的分区。如果你有使用分区表的话才需要关注此字段。
type
表示访问的类型,这里也可以看出你的 SQL 的性能。可能的值从最好到最差包括:system、const、eq_ref、ref、range、index、ALL。其中 ALL 表示全表扫描,效率最低。
- system:
- 表示查询的表只有一行(系统表)。这是一个特殊的情况,不常见。
- const:
- 表示查询的表最多只有一行匹配结果。这通常发生在查询条件是主键或唯一索引,并且是常量比较,以下是一个使用主键查找的例子:
- EXPLAIN SELECT * FROM employees WHERE employee_id = 12345;
- eq_ref:
- 表示对于每个来自前一张表的行,MySQL 仅访问一次这个表。这通常发生在连接查询中使用主键或唯一索引的情况下,例子如下:
- EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id;
- ref:
- MySQL 使用非唯一索引扫描来查找行。查询条件使用的索引是非唯一的(如普通索引),例子如下使用了非唯一索引进行查找:
- EXPLAIN SELECT * FROM employees WHERE department_id = 5;
- range:表示 MySQL 会扫描表的一部分,而不是全部行。范围扫描通常出现在使用索引的范围查询中(如 BETWEEN、>, <, >=, <=)。下面是范围查询:
- EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 50000 AND ;
- index:表示 MySQL 扫描索引中的所有行,而不是表中的所有行。即使索引列的值覆盖查询,也需要扫描整个索引。以下是使用索引扫描例子:
- EXPLAIN SELECT name FROM employees;
- all(性能最差):表示 MySQL 需要扫描表中的所有行,即全表扫描。这通常出现在没有索引的查询条件中。以下是全表扫描例子:
- EXPLAIN SELECT * FROM employees;
possible_keys
表示查询可能使用的索引列表。
key
实际使用索引的长度。如果没有使用索引,该字段显示为 NULL。
key_len
这个字段表示使用的索引的长度。该值是根据索引的定义和查询条件计算的。
rows
MySQL 会估计为了找到所需的行,需要读取的行数。该值是一个估计值,不是精确值。
filtered
显示查询条件过滤掉的行的百分比。一个高百分比表示查询条件的选择性好。
Extra
额外信息,如 Using index(表示使用覆盖索引)、Using where(表示使用 WHERE 条件进行过滤)、Using temporary(表示使用临时表)、Using filesort(表示需要额外的排序步骤)。
实际例子
1.创建 employees 表
CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department_id INT, salary DECIMAL(10, 2), hire_date DATE, INDEX (department_id) );
我们要执行以下查询来查找部门 ID 为 5 且薪水在 50000 到 之间的员工,并按薪水降序排序:
SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 5 AND salary BETWEEN 50000 AND ORDER BY salary DESC;
2.我们先使用 explain 分析计划进行分析:
EXPLAIN SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 5 AND salary BETWEEN 50000 AND ORDER BY salary DESC;
输出结果如下:
id |
select_type |
table |
partitions |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
1 |
SIMPLE |
employees |
NULL |
ref |
department_id |
department_id |
4 |
const |
5000 |
20.00 |
Using where; Using filesort |
3.分析执行计划
从执行计划中看出,type 为 ref,表示使用了 department_id 索引,这是个非唯一索引。key 为 department_id 这个索引,而且 rows 为 5000,表示扫描了 5000 行匹配的 department_id = 5 的条件。从 Extra 看出在应用 WHERE 条件后,还需要进行文件排序来满足 ORDER BY 子句。
4.找出问题
尽管查询使用了索引,但由于索引不完全覆盖查询的条件和排序,查询需要进行额外的文件排序。这可能会导致性能瓶颈,特别是在结果集较大时。
5.优化解决它!
创建复合索引
创建一个包含 department_id 和 salary 的复合索引,这样可以覆盖查询的 WHERE 和 ORDER BY 条件:
CREATE INDEX idx_department_salary ON employees (department_id, salary);
复合索引可以使查询在扫描 department_id 列时,同时按 salary 列排序,避免额外的文件排序。
再次执行计划分析
优化后的 EXPLAIN 输出如下:
id |
select_type |
table |
partitions |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
1 |
SIMPLE |
employees |
NULL |
range |
idx_department_salary |
idx_department_salary |
5 |
NULL |
500 |
100.00 |
Using where |
6.分析优化后的结果
从新的 EXPLAIN 输出中可以看出:
- type: range,表示使用范围扫描,这是个相对高效的访问类型。
- key: idx_department_salary,表示实际使用了复合索引。
- rows: 500,估计读取的行数减少了,因为索引更精确地覆盖了查询条件。
- Extra: 仅显示 Using where,不再需要文件排序,因为索引已经覆盖了排序需求。
是不是分析起来很简单咧,完结撒花!!!!,除了新增联合索引的方式,你们还知道什么优化策略吗?
推荐文章和书籍
文章:https://zhuanlan.zhihu.com/p/
书籍:《 Java 核心技术卷 I 》
欢迎交流
当谈到Explain 时,我们可以探讨以下几个问题:
1)如何判断查询是否使用了索引?
2)Explain 输出中的 type 列代表什么?哪些类型表示查询性能较好?
3)如何通过 Explain 输出中的 Explain 列优化查询?
这些问题将帮助我们深入了解 Explain 执行计划的概念、应用和原理,从而更好用来解决实际问题。
免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://yundeesoft.com/96452.html