MySQL 查询优化之EXPLAIN

耶耶学后端

已于 2024-03-21 10:38:42 修改

阅读量370

点赞数 7

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-03-16 22:25:41 首次发布

本文链接：https://blog.csdn.net/2401_83616384/article/details/136771602

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

什么是EXPLAIN

EXPLAIN命令可以用于任何SELECT语句，展示MySQL如何使用索引来处理SELECT语句以及连接表。它显示了MySQL如何执行查询，包括如何表连接以及选择索引的情况。

开始之前

为了充分利用EXPLAIN的强大功能，我们先创建一个简单的表并插入一些数据作为我们的测试基础。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    department VARCHAR(100)
);

INSERT INTO employees (name, position, department) VALUES 
('John Doe', 'Software Engineer', 'Development'),
('Jane Smith', 'Project Manager', 'Marketing'),
('Emily Davis', 'Data Scientist', 'Data Analysis');

假设我们想要查询开发部门的所有员工，一个简单的查询如下：

SELECT * FROM employees WHERE department = 'Development';

使用EXPLAIN

要分析这个查询，我们在它前面加上EXPLAIN关键字：

EXPLAIN SELECT * FROM employees WHERE department = 'Development';

执行这个命令后，你会看到类似下面的表格（具体列和内容可能会根据MySQL的版本有所不同）：

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+

解读EXPLAIN输出

id: 查询的序列号。
select_type: 查询的类型，"SIMPLE"表示不含子查询或UNION。
table: 显示行从哪个表中检索。
type: 显示连接类型，"ALL"表示全表扫描。
possible_keys: 显示可能应用于此表的索引。
key: 实际使用的索引。
key_len: 使用的索引的长度。
ref: 显示索引的哪一列被使用。
rows: MySQL认为它必须检查的行数。
filtered: 表示返回结果的行占总行数的百分比。
Extra: 包含不适合其他列的额外信息。

实验案例

在我们的例子中，type列为ALL，意味着MySQL将进行全表扫描来查找匹配的行。对于大型数据库，这可能会非常低效。

添加索引

让我们对department列添加索引，并再次运行EXPLAIN来查看差异。

CREATE INDEX idx_department ON employees(department);

EXPLAIN SELECT * FROM employees WHERE department = 'Development';

添加索引后，再次运行EXPLAIN，你将看到type列改变了，不再是ALL，而是ref或range，具体取决于你的MySQL版本和表的大小。这表示MySQL能够使用索引来优化查询，减少必须检查的行数。

实践小结

通过实验案例，我们看到了EXPLAIN命令如何帮助我们理解MySQL如何执行特定的查询，以及如何通过添加索引来优化查询性能。EXPLAIN是优化数据库查询不可或缺的工具，理解它的输出可以帮助我们做出更有效的优化决策。不过，记得在生产环境中谨慎添加索引，因为它们虽然可以加速查询，但也会增加插入、更新和删除操作的开销。