什么是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是优化数据库查询不可或缺的工具,理解它的输出可以帮助我们做出更有效的优化决策。不过,记得在生产环境中谨慎添加索引,因为它们虽然可以加速查询,但也会增加插入、更新和删除操作的开销。