MySQL 查询优化：实用操作指南
缓慢的数据库查询可能成为任何应用程序的重大瓶颈，导致用户体验差和基础设施成本增加。幸运的是，MySQL 提供了强大的工具来诊断和解决这些性能问题。本指南将引导您完成优化 MySQL 查询的关键技术，重点关注实际应用和清晰理解。
我们将介绍如何使用 EXPLAIN 语句来理解查询执行计划，识别常见的性能陷阱，并提供重写低效查询的策略。通过掌握这些技术，您可以显著提高数据库的响应能力和整体应用程序性能。
理解查询性能
在深入优化之前，了解查询为何会缓慢至关重要。常见的原因包括：
- 缺失或无效的索引： 没有适当的索引，MySQL 必须执行全表扫描，这对于大型表来说效率非常低。
- 编写不佳的 SQL： 复杂的子查询、
SELECT *和低效的 JOIN 条件都会降低性能。 - 大量数据集： 仅仅处理大量数据本身就会减慢操作速度。
- 硬件和配置： 不理想的服务器配置或不足的硬件资源也可能起作用，尽管本指南侧重于查询级别的优化。
EXPLAIN 的强大功能
EXPLAIN 语句是您了解 MySQL 如何执行查询的主要工具。它提供了对执行计划的深入了解，展示了表是如何连接的、使用了哪些索引以及如何扫描行。它实际上并不执行查询，因此可以在生产系统上安全使用。
如何使用 EXPLAIN
只需在您的 SELECT、INSERT、DELETE、UPDATE 或 REPLACE 语句前加上 EXPLAIN：
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';
解释 EXPLAIN 输出
EXPLAIN 的输出是一个包含几个重要列的表：
id：查询中 SELECT 的序号。数字越大，通常越先执行。select_type：SELECT 的类型（例如，SIMPLE、PRIMARY、SUBQUERY、DERIVED）。table：正在访问的表。partitions：使用的分区（如果启用了分区）。type：连接类型。这是最关键的列之一。目标是const、eq_ref、ref、range。避免index，尤其要避免ALL（全表扫描）。possible_keys：显示 MySQL 可能 使用的索引。key：MySQL 实际 选择使用的索引。key_len：所选键的长度。通常越短越好。ref：与索引（key）进行比较的列或常量。rows：MySQL 必须检查以执行查询的行数估计值。filtered：表条件过滤的行百分比。Extra：包含有关 MySQL 如何解析查询的附加信息。需要关注的关键值包括：Using where：表示在获取行后使用WHERE子句进行过滤。Using index：表示查询被索引覆盖（所有必需的列都在索引中），这是好的。Using temporary：MySQL 需要创建临时表，通常用于GROUP BY或ORDER BY操作。这可能会很慢。Using filesort：MySQL 必须进行外部排序（不使用索引进行排序）。这通常是低效ORDER BY子句的标志。
使用 EXPLAIN 识别瓶颈
让我们看一些常见场景以及 EXPLAIN 如何帮助识别问题：
场景 1：全表扫描
考虑一个查询，如：
SELECT * FROM orders WHERE order_date = '2023-10-26';
如果 order_date 列没有索引，EXPLAIN 可能会显示：
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 1000000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
问题： type: ALL 表示全表扫描。rows: 1000000 显示 MySQL 必须检查 orders 表中的每一行。key: NULL 表示没有使用索引。
解决方案： 在 order_date 列上添加索引：
CREATE INDEX idx_order_date ON orders (order_date);
添加索引后，重新运行 EXPLAIN。您现在应该看到更高效的 type（如 ref 或 range）和显著降低的 rows 计数。
场景 2：低效的 ORDER BY 或 GROUP BY
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id ORDER BY customer_id;
如果 customer_id 没有索引，或者索引不支持排序，EXPLAIN 可能会显示：
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
| 1 | SIMPLE | orders | index | NULL | NULL | NULL | NULL | 100000 | Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
问题： Using temporary 和 Using filesort 表明 MySQL 正在执行耗时的操作来排序和分组数据。这通常是因为没有索引能够有效地同时满足分组和排序要求。
解决方案： 根据查询的不同，创建一个包含分组和排序列的索引可能会有帮助。对于这个特定的查询，(customer_id) 上的索引可能就足够了。如果查询更复杂，可能需要一个复合索引。
CREATE INDEX idx_customer_id ON orders (customer_id);
场景 3：不必要地使用 SELECT *
当您选择所有列（*）但只需要少数几列时，您可能会阻止 MySQL 使用现有索引来覆盖查询，即使索引存在于 WHERE 子句的列上。这会导致额外的表查找。
-- 假设有一个 'status' 上的索引
SELECT * FROM tasks WHERE status = 'pending';
EXPLAIN 可能会显示 Using where，但如果查询需要的列不在用于过滤的索引中，它仍然需要访问表数据。
解决方案： 只指定您需要的列：
SELECT task_id, description FROM tasks WHERE status = 'pending';
如果您经常查询特定的列以及其他列，请考虑创建一个包含查询所需所有列的覆盖索引。
重写慢查询
除了索引之外，您构建 SQL 的方式也会对性能产生巨大影响。
避免相关子查询
相关子查询在外部查询处理的每一行都会执行一次。它们通常效率低下。
低效：
SELECT o.order_id, o.order_date
FROM orders o
WHERE o.customer_id IN (
SELECT c.customer_id
FROM customers c
WHERE c.country = 'USA'
);
高效（使用 JOIN）：
SELECT o.order_id, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA';
对两个版本使用 EXPLAIN 会突出显示性能差异。
优化 LIKE 子句
LIKE 子句中的前导通配符（%）会阻止索引的使用。
低效：
SELECT * FROM products WHERE product_name LIKE '%widget';
更好（如果可能）：
SELECT * FROM products WHERE product_name LIKE 'widget%';
如果您确实需要前导通配符，请考虑全文索引或其他搜索解决方案。
在可能的情况下使用 UNION ALL 而不是 UNION
UNION 会删除重复的行，这需要额外的排序和去重步骤。如果您知道没有重复项或不需要删除它们，UNION ALL 会更快。
慢：
SELECT name FROM table1
UNION
SELECT name FROM table2;
快：
SELECT name FROM table1
UNION ALL
SELECT name FROM table2;
其他优化技巧
- 保持统计信息更新： 确保表统计信息是最新的，以便查询优化器能够做出明智的决策。这通常是自动处理的，但可以使用
ANALYZE TABLE手动更新。 - 服务器配置： 虽然本指南侧重于查询，但审查 MySQL 配置变量，如
innodb_buffer_pool_size、query_cache_size（在 MySQL 8.0 中已弃用）和sort_buffer_size，对于整体性能至关重要。 - 定期监控： 使用 MySQL Enterprise Monitor、Percona Monitoring and Management (PMM) 或内置的 performance schema 视图等工具来跟踪慢查询并识别趋势。
结论
优化 MySQL 查询是一个迭代过程，它结合了对数据的理解、使用 EXPLAIN 等诊断工具以及应用编写 SQL 的最佳实践。通过关注索引、避免全表扫描和高效地构建查询，您可以极大地提高应用程序的性能和可伸缩性。请记住，始终测试您的更改并衡量其影响。
祝您优化愉快！