面试官听到我对 explain 使用的回答竖起了大拇指

面试官听到我对 explain 使用的回答竖起了大拇指引言 在 Java 数据库操作中 理解 SQL 语句的执行计划是优化查询性能的关键之一 Explain 是一个重要的 SQL 语句 它能够显示数据库如何执行某个查询 帮助开发者识别性能瓶颈和优化查询

大家好,欢迎来到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 的性能。可能的值从最好到最差包括:systemconsteq_refrefrangeindexALL。其中 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.分析执行计划

从执行计划中看出,typeref,表示使用了 department_id 索引,这是个非唯一索引。keydepartment_id 这个索引,而且 rows 为 5000,表示扫描了 5000 行匹配的 department_id = 5 的条件。从 Extra 看出在应用 WHERE 条件后,还需要进行文件排序来满足 ORDER BY 子句。

4.找出问题

尽管查询使用了索引,但由于索引不完全覆盖查询的条件和排序,查询需要进行额外的文件排序。这可能会导致性能瓶颈,特别是在结果集较大时。

5.优化解决它!

创建复合索引

创建一个包含 department_idsalary 的复合索引,这样可以覆盖查询的 WHEREORDER 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

(0)

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

关注微信