优化MySQL查询：实用操作指南

一旦你查看了执行计划，慢速MySQL查询很少是神秘的。难点不在于知道索引很重要，而在于证明哪个查询慢、理解MySQL为何选择某个计划，以及在不使写入、存储或其他查询变得更糟的情况下修改查询或索引。

从证据开始。使用慢查询日志、Performance Schema、应用程序跟踪或像PMM这样的监控工具来找到真正影响用户的查询。然后使用EXPLAIN，并在安全的情况下使用EXPLAIN ANALYZE来查看MySQL正在做什么。

理解查询性能

常见原因包括：

缺失或无效的索引： 没有合适的索引，MySQL必须执行全表扫描，这对于大表来说非常低效。
编写不佳的SQL： 非sargable过滤器、不必要的SELECT *、意外的交叉连接以及低效的连接条件都会降低性能。
大数据集： 更多的数据意味着需要读取、排序、分组和缓存的页面更多。
硬件和配置： 次优的服务器配置或不足的硬件资源也可能起作用，尽管本指南侧重于查询级别的优化。

`EXPLAIN` 的强大功能

EXPLAIN 是当你想要了解MySQL如何计划一个查询时首先使用的工具。对于普通的EXPLAIN SELECT，MySQL显示优化器选择的计划，而不返回结果集。EXPLAIN ANALYZE 执行查询并报告实际时间，因此在生产系统上要谨慎使用。

如何使用 `EXPLAIN`

对于读取查询，在查询前加上 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：MySQL预期使用的索引部分的长度。较短并不自动更好；它取决于选择性和查询。
ref：与索引（key）比较的列或常量。
rows：MySQL预计要检查的行数的估计值。
filtered：通过表条件过滤的行百分比。
Extra：包含关于MySQL如何解析查询的附加信息。需要注意的关键值包括：
- Using where：表示MySQL在处理行时应用条件。这很常见，并不总是坏事。
- 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。你应该会看到更具选择性的访问类型，如 ref 或 range，并且如果日期过滤器具有选择性，估计的行数应该会下降。

场景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) 上的索引可能让MySQL按分组顺序扫描行。如果实际查询首先按日期、状态或租户过滤，那么复合索引可能更好，例如 (tenant_id, status, customer_id)。

CREATE INDEX idx_customer_id ON orders (customer_id);

场景3：不必要地使用 `SELECT *`

当你选择所有列（*）但只需要少数几列时，你会传输更多数据，并可能阻止覆盖索引发挥作用。这在具有JSON列、文本blob或多个可空字段的宽表上尤其明显。

-- 假设在 'status' 上有索引
SELECT * FROM tasks WHERE status = 'pending';

EXPLAIN 可能显示 Using where，但如果查询需要的列不在用于过滤的索引中，它仍然需要访问表数据。

解决方案： 只指定你需要的列：

SELECT task_id, description FROM tasks WHERE status = 'pending';

如果你经常查询这种确切形状，考虑一个包含过滤列和返回列的覆盖索引：

CREATE INDEX idx_tasks_status_id_description
  ON tasks (status, task_id, description);

不要为每个查询都创建覆盖索引。它们以存储和写入开销为代价加速读取。

重写慢查询

除了索引之外，你构建SQL的方式也会改变MySQL必须完成的工作量。

避免相关子查询

相关子查询可能为外部查询处理的每一行执行一次。MySQL可以优化其中一些，但如果 EXPLAIN 显示重复的依赖查找，那么连接或派生表通常更清晰、更快。

通常低效：

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'
);

通常作为连接更好：

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 子句中的前导通配符（%）通常会阻止普通的B树索引用于范围查找。

低效：

SELECT * FROM products WHERE product_name LIKE '%widget';

更好（如果可能）：

SELECT * FROM products WHERE product_name LIKE 'widget%';

如果你需要包含式匹配，考虑使用MySQL全文索引进行合适的文本搜索，针对特定语言使用n-gram方法，或者当相关性和灵活匹配很重要时使用搜索引擎。

尽可能使用 `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 手动更新。
服务器配置： 查询调优无法弥补过小的InnoDB缓冲池或过载的磁盘。在MySQL 8.0中，旧的查询缓存已被移除，因此不要围绕 query_cache_size 规划新的调优。
定期监控： 使用像MySQL Enterprise Monitor、Percona Monitoring and Management (PMM) 或内置的performance schema视图等工具来跟踪慢查询并识别趋势。

实用的调优工作流程

对于生产系统，从慢查询向外调优：

捕获确切的SQL、绑定值、行数和时间。
如果你的MySQL版本支持，运行 EXPLAIN FORMAT=TREE 或 EXPLAIN FORMAT=JSON。
检查所选索引是否与过滤和连接模式匹配。
在真实数据上测试查询重写或索引更改。
比较检查的行数、临时表、排序行为和实际延迟。

这可以防止你因为查询“看起来慢”而添加索引。索引是有成本的。每次插入、更新和删除都必须维护它们。一个包含十个重叠索引的表可能会整体变慢，即使一个读取查询得到了改善。

对于一个常见的多租户应用程序查询，索引顺序通常比索引列的数量更重要：

SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

一个有用的索引可能是：

CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

该索引以等式过滤器开始，然后支持日期范围和排序。如果你把 created_at 放在第一位，MySQL可能会在找到正确的租户之前扫描许多租户。如果你省略了 status，查询可能仍然有效，但会检查许多额外的行。

注意非Sargable过滤器

当MySQL可以使用索引搜索匹配行时，条件就是sargable的。将索引列包装在函数中通常会破坏这一点：

-- 更难使用 created_at 上的索引
SELECT * FROM orders
WHERE DATE(created_at) = '2025-01-15';

将其重写为范围：

SELECT *
FROM orders
WHERE created_at >= '2025-01-15'
  AND created_at <  '2025-01-16';

第二个版本让MySQL可以查找 created_at 上的索引。同样的想法适用于 LOWER(email)、数字列上的数学运算以及隐式类型转换。如果列已索引，尽可能保持比较的列侧干净。

小心分页

偏移分页在深页面上会变得昂贵：

SELECT id, title
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 200000;

MySQL仍然需要遍历前面的行才能返回你请求的页面。对于信息流、审计日志和管理表，键集分页通常更好：

SELECT id, title, published_at
FROM posts
WHERE status = 'published'
  AND (published_at, id) < ('2025-05-01 12:00:00', 987654)
ORDER BY published_at DESC, id DESC
LIMIT 20;

将其与诸如 (status, published_at, id) 之类的索引配对。这会稍微改变产品行为，因为用户通过游标移动而不是跳转到第10,000页，但它可以将痛苦的查询转变为可预测的查询。

用真实数据验证

小的临时数据库会撒谎。在20,000行上瞬间完成的查询在2亿行上可能很糟糕，尤其是当数据分布倾斜时。尽可能在类似生产环境的容量和基数下进行测试。如果你不能复制生产数据，至少生成具有相似租户大小、状态分布和日期范围的数据。

最后一个习惯很有帮助：将旧计划和新计划保留在工单中。未来的你会想知道为什么存在一个索引。

最好的MySQL调优习惯是让每个更改都赢得其位置。捕获慢查询，检查计划，更改一个查询或索引，然后比较延迟和检查的行数。一个干净的 EXPLAIN 计划很有用，但真正的胜利是在不产生新的写入压力或存储膨胀的情况下降低生产延迟。

优化MySQL查询：实用操作指南

理解查询性能

EXPLAIN 的强大功能

如何使用 EXPLAIN

解读 EXPLAIN 输出

使用 EXPLAIN 识别瓶颈