性能优化（执行计划 / 慢查询 / 常见陷阱） · SQL 改写优化

存在 WHERE 条件 + ORDER BY + LIMIT 的复合查询，怎么设计联合索引？

How to design a composite index for a query with WHERE, ORDER BY, and LIMIT clauses

答案语言

考察要点

这道题面试官想看你是否深刻理解 MySQL B+ 树联合索引的底层结构和工作原理。核心是考察你对 最左前缀原则 的应用能力，以及如何通过设计最优索引来同时满足查询的 过滤（WHERE）、排序（ORDER BY）和提前终止（LIMIT） 三个环节，从而避免代价高昂的 文件排序（filesort）。

核心概念

面试的核心是向面试官清晰地阐述，我们的目标是设计一个索引，使得 MySQL 可以严格按照索引的物理顺序读取数据，从而天然满足 ORDER BY 的要求，无需额外的排序步骤。

1. 最左前缀原则 (Leftmost Prefix Principle)

这是联合索引的基石。对于一个联合索引 (col1, col2, col3)，它在 B+ 树中的存储结构是先按 col1 排序，在 col1 相同的情况下再按 col2 排序，以此类推。

索引结构示意图 (ASCII B+ Tree)

text

 1                      [ Root Node ]
 2                           |
 3            +--------------+--------------+
 4            |                             |
 5      [a, jan, ...]                 [b, mar, ...]
 6            |                             |
 7+-----------+-----------+     +-----------+-----------+
 8|           |           |     |           |           |
 9[a,jan,1] [a,jan,5] [a,feb,2] [b,mar,3] [b,mar,8] [b,apr,4]  <- Leaf Nodes (Data is ordered)

上图索引为 (col1, col2, col3)，数据严格按照 col1 -> col2 -> col3 的顺序排列。

有效查询场景：
- WHERE col1 = 'a'
- WHERE col1 = 'a' AND col2 = 'jan'
- WHERE col1 = 'a' AND col2 = 'jan' AND col3 = 5
失效或部分有效场景：
- WHERE col2 = 'jan' (无法使用索引)
- WHERE col1 > 'a' (范围查询，col2 和 col3 的索引排序失效)

2. Filesort (文件排序)

当 ORDER BY 的字段无法利用索引的有序性时，MySQL 不得不将满足 WHERE 条件的记录加载到内存（或磁盘文件）中进行排序。这个过程称为文件排序。在 EXPLAIN 的 Extra 列中会显示 Using filesort，这是慢查询的常见根源，也是我们极力要避免的。

3. 索引设计黄金法则

对于 WHERE col_A = ? AND col_B > ? ORDER BY col_C, col_D LIMIT ? 这样的查询，索引设计应遵循以下顺序：

等值查询字段 (Equality Predicates)：WHERE 子句中用 = 连接的字段，如 col_A。这些字段应放在索引的最左侧。多个等值字段的顺序可以根据字段的**基数（Cardinality）**来调整，高基数的字段放前面，可以更快地过滤掉大部分数据。
范围查询字段 (Range Predicates)：WHERE 子句中用 >、<、BETWEEN、LIKE 等连接的字段，如 col_B。范围查询字段一旦出现，其后的索引字段就无法用于排序了。因此，它应该紧跟在等值查询字段之后。
排序字段 (ORDER BY Clause)：ORDER BY 子句中的字段，如 col_C, col_D。这些字段应放在范围查询字段之后。

核心推论：为了让 ORDER BY 能用上索引，必须保证 WHERE 子句中的所有字段（等值+范围）以及 ORDER BY 子句中的所有字段，整体上能构成索引的一个最左前缀，并且中间不能被范围查询“截断”。

示例分析

假设有查询：

sql

 1SELECT user_id, product_id, create_time
 2FROM `order`
 3WHERE `channel` = 'app' AND `status` = 'paid'
 4ORDER BY `create_time` DESC
 5LIMIT 10;

分析:
- 等值条件：channel, status
- 范围条件：无
- 排序条件：create_time

最优索引设计:

sql

 1-- 方案1：(channel, status, create_time)
 2ALTER TABLE `order` ADD INDEX `idx_channel_status_time` (`channel`, `status`, `create_time`);

工作流程:
1. MySQL 通过索引 idx_channel_status_time 定位到 channel = 'app' 的第一个节点。
2. 在这些节点中，继续定位到 status = 'paid' 的第一个节点。
3. 此时，剩下的记录在索引中已经天然按 create_time 排好序了。
4. MySQL 只需从这个位置开始，向后（或向前，取决于 DESC/ASC）扫描 10 条记录即可。
5. 整个过程没有 filesort，EXPLAIN 的 Extra 列不会出现 Using filesort。

常见低分回答

索引字段顺序错误：为上述查询创建 (create_time, channel, status) 索引。
- 错误原因：最左前缀是 create_time，但 WHERE 条件没有 create_time 的等值查询，无法有效利用此索引进行过滤，会导致大范围扫描甚至全表扫描。
将范围查询字段放在排序字段之前：对于查询 WHERE user_id = 100 AND create_time > '2023-01-01' ORDER BY score DESC，创建索引 (user_id, create_time, score)。
- 错误原因：索引在 create_time 上进行了范围扫描后，score 字段的有序性就无法保证了。MySQL 找到 user_id = 100 且 create_time > '2023-01-01' 的所有记录后，仍然需要对这些记录按 score 进行文件排序。
ORDER BY 方向与索引不一致：查询为 ORDER BY col_a ASC, col_b DESC，但索引为 (col_a, col_b)（默认均为 ASC）。
- 错误原因：在 MySQL 8.0 之前，索引所有列的排序方向必须一致。这种混合排序无法利用索引，会导致 filesort。正确的做法是在 MySQL 8.0+ 中创建混合排序索引 INDEX(col_a ASC, col_b DESC)。
滥用 SELECT * 导致无法覆盖索引：即使索引 (channel, status, create_time) 设计正确，如果查询是 SELECT * ...，MySQL 仍然需要在通过索引找到主键后，进行回表查询其他列，增加了 I/O 开销。如果查询是 SELECT channel, status, create_time ...，则可触发覆盖索引（Covering Index），EXPLAIN 的 Extra 会显示 Using index，性能最佳。
认为多个单列索引可以被合并使用：创建 INDEX(channel), INDEX(status), INDEX(create_time) 三个单列索引。
- 错误原因：MySQL 的优化器通常只会选择其中一个最有效的索引（通常是选择性最高的）来过滤数据，然后对结果集进行文件排序。它不会神奇地将多个索引组合起来以避免排序。

工程实践与优化

使用 EXPLAIN 分析执行计划：这是诊断索引是否生效的唯一标准。
- 理想状态：type 是 ref 或 range，key 是你设计的联合索引，Extra 中没有 Using filesort，最好是 Using index（覆盖索引）。
- 糟糕状态：type 是 index 或 ALL，Extra 中出现 Using filesort。
sql
```
 1-- 好的执行计划示例
 2EXPLAIN SELECT user_id, product_id, create_time
 3FROM `order`
 4WHERE `channel` = 'app' AND `status` = 'paid'
 5ORDER BY `create_time` DESC
 6LIMIT 10;
```
type key Extra
ref idx_channel_status_time Using index
索引基数（Cardinality）考量：在组合等值查询字段时，将高基数（区分度高）的字段放在前面。例如，user_id 的基数远高于 status，索引 (user_id, status) 通常优于 (status, user_id)。
覆盖索引（Covering Index）：尽量让索引包含查询所需的所有列（SELECT、WHERE、ORDER BY 中的列）。这可以避免回表，极大提升性能，EXPLAIN 的 Extra 会显示 Using index。
索引维护成本：索引不是越多越好。每个额外的索引都会增加写操作（INSERT, UPDATE, DELETE）的成本，并占用磁盘空间。应为最核心、最频繁的查询设计索引。

type	key	Extra
ref	idx_channel_status_time	Using index

常见追问与延伸题目

追问：如果数据量增长 100 倍，这条 SQL 性能怎样？怎么优化？
- 回答要点 1 (索引正确)：如果已经设计了最优的联合索引 (channel, status, create_time)，性能下降是对数级的（ $O(\log N)$ 定位 + $O(M)$ 扫描，M为LIMIT值），而不是线性的。即使数据量增长100倍，查询响应时间可能只会从 1ms 增加到几毫秒，仍然非常快。这是因为 B+ 树的层级增长非常缓慢。
- 回答要点 2 (索引错误)：如果存在 filesort，性能会急剧恶化。排序的数据量会随数据总量线性增长，当内存中的 sort_buffer_size 不足以容纳排序数据时，MySQL 会使用多路归并排序，在磁盘上创建临时文件，导致大量的磁盘 I/O，性能可能下降成百上千倍。优化方案就是回到原点，按照我们讨论的黄金法则，添加正确的联合索引。
追问：如果 ORDER BY 的排序列方向不一致，比如 ORDER BY col_a ASC, col_b DESC，该如何处理？
- 回答要点 1 (MySQL 8.0+ )：MySQL 8.0 版本开始支持降序索引（Descending Indexes）。可以直接创建与查询完全匹配的索引：CREATE INDEX idx_mixed_order ON t (col_a ASC, col_b DESC); 这样就可以完美利用索引避免 filesort。
- 回答要点 2 (MySQL 5.7 及更早版本)：这些版本不支持混合方向索引。无法通过一个索引同时满足 ASC 和 DESC 的排序。此时有两种妥协方案：
  - 业务侧改造：如果可能，将所有排序方向统一为 ASC 或 DESC。例如，可以新增一个 reversed_col_b 字段，其值与 col_b 相反，然后查询 ORDER BY col_a ASC, reversed_col_b ASC，索引建在 (col_a, reversed_col_b) 上。
  - 接受 filesort：如果无法改造，只能接受 filesort。此时索引应至少包含 WHERE 条件和 ORDER BY 的第一个字段，即 (where_col, col_a)，以尽量减少参与排序的数据量。
追问：如果 WHERE 条件中包含 IN 查询，比如 WHERE status IN ('paid', 'shipped') ORDER BY create_time DESC，索引应该怎么建？
- 回答要点 1 (现代 MySQL 版本)：对于 IN 查询，现代 MySQL 优化器（如 5.6+）足够智能。它可能将 IN 视为多个等值查询的组合。对于索引 (status, create_time)，优化器会分别查找 status = 'paid' 的有序 create_time 列表和 status = 'shipped' 的有序 create_time 列表，然后将这两个已排序的结果集合并（merge），这个合并过程的代价远小于全量 filesort。因此，索引 (status, create_time) 依然是最佳选择。
- 回答要点 2 (旧版本或复杂场景)：在某些旧版本或 IN 列表非常大的情况下，优化器可能退化为范围查询处理，导致 create_time 的排序失效。此时 EXPLAIN 会显示 Using filesort。如果性能不可接受，可以考虑将 IN 查询拆分为多个 UNION ALL 查询，每个子查询对应 IN 中的一个值，并使用 LIMIT，最后在外部再做一次排序和 LIMIT。但这会增加查询的复杂性。