MySQL Order By为什么没有用到索引？filesort排序的优化

当你的MySQL查询使用ORDER BY时，发现执行计划中出现了Using filesort，意味着数据库没有使用索引进行排序，而是将数据全部加载到内存或磁盘临时文件中进行排序，这会严重影响查询性能。本文将手把手教你分析ORDER BY不使用索引的原因，并提供具体的优化方案。

一、为什么ORDER BY没有用到索引？

MySQL的优化器在决定是否使用索引排序时，会综合考虑多种因素。以下是最常见的几种原因：

1. 排序字段不是索引的一部分
   如果你的ORDER BY子句中的列，没有出现在任何索引中，或者索引的顺序与排序要求不匹配，MySQL就无法利用索引排序。

2. 排序方向与索引不一致（混合排序）
   如果索引是(a ASC, b DESC)，但你查询的ORDER BY是(a DESC, b ASC)，MySQL无法直接使用该索引进行排序，因为索引的排序方向是固定的。

3. 排序字段是表达式或函数
   当你在ORDER BY子句中对列使用了函数或表达式，例如ORDER BY DATE(create_time)，即使create_time有索引，MySQL也无法直接利用该索引。

4. *SELECT 或选择了非索引列**
   如果你的查询SELECT了索引列之外的列，MySQL可能需要回表（从索引找到主键，再根据主键去数据行查找完整数据）来获取这些列。如果回表的成本高于排序成本，优化器可能会选择全表扫描后排序，而不是使用索引排序。

5. LIMIT 优化失效
   当LIMIT的值非常大时，即使有索引，MySQL也可能选择全表扫描再排序，因为排序少量数据（即使数据量很大）可能比回表取大量数据更快。

二、如何优化filesort排序？

针对上述原因，我们可以采取以下策略进行优化：

1. 创建合适的索引

这是最根本的优化方法。根据你的ORDER BY和WHERE条件，创建复合索引。

• 原则：将排序字段放在索引的最前面。
• 原则：将WHERE条件的筛选字段也加入索引，形成复合索引。

例如，如果你经常按create_time降序查询，就应该创建一个以create_time为前缀的索引。

-- 假设表名为 `orders`
CREATE INDEX idx_create_time ON orders(create_time DESC);

2. 使用覆盖索引

覆盖索引是指，SELECT查询的列都包含在索引中，这样MySQL可以直接从索引中获取所有需要的数据，无需回表，极大提升性能。

• 做法：修改SELECT语句，只查询索引中存在的列。

例如，如果你的索引是idx_user_amount(user_id, amount)，你可以这样查询：

-- 假设表名为 `orders`
SELECT user_id, amount FROM orders ORDER BY user_id ASC, amount DESC;

3. 避免在ORDER BY中使用函数或表达式

将函数或表达式移到应用层处理，或者在查询条件中直接使用字段。

• 错误示例：SELECT * FROM orders ORDER BY DATE(create_time);
• 正确示例：SELECT * FROM orders ORDER BY create_time;

然后在应用层处理create_time的格式化。

4. 使用FORCE INDEX（谨慎使用）

你可以强制MySQL使用某个索引。但这是一种“暴力”手段，通常不推荐，因为MySQL的优化器通常比人更懂如何选择最优执行计划。滥用可能导致更差的性能。

SELECT * FROM orders FORCE INDEX (idx_create_time) ORDER BY create_time DESC;

5. 优化LIMIT分页

当LIMIT的值很大时，考虑使用其他分页方式，例如基于WHERE条件的分页，以减少需要排序的数据量。

• 传统分页（可能很慢）：

  SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

• 优化分页（更快）：

  SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

三、实战案例：优化前后的对比

我们通过一个具体案例，来对比优化前后的效果。

假设我们有一个orders表：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10, 2),
    create_time DATETIME
);

-- 插入一些测试数据
INSERT INTO orders (user_id, amount, create_time) VALUES
(101, 150.50, '2023-01-01 10:00:00'),
(102, 200.00, '2023-01-02 11:00:00'),
(101, 120.75, '2023-01-03 12:00:00'),
(103, 300.25, '2023-01-01 09:00:00'),
(102, 180.90, '2023-01-02 10:30:00');

场景1：未优化的查询

我们按create_time降序查询所有数据。

EXPLAIN SELECT * FROM orders ORDER BY create_time DESC;

执行计划分析：
你可能会看到Extra列显示Using filesort，这表明MySQL没有使用索引，而是进行了文件排序。

优化1：创建索引

为create_time创建一个降序索引。

CREATE INDEX idx_create_time ON orders(create_time DESC);

场景2：优化后的查询

再次执行相同的查询。

EXPLAIN SELECT * FROM orders ORDER BY create_time DESC;

执行计划分析：
现在Extra列应该显示Using index，表明MySQL使用了我们创建的索引进行排序，避免了文件排序。

场景3：复杂排序与SELECT *

现在我们按user_id升序，amount降序查询，并选择所有列。

EXPLAIN SELECT * FROM orders ORDER BY user_id ASC, amount DESC;

执行计划分析：
你可能会看到Extra列显示Using filesort，因为虽然user_id和amount可能被索引覆盖，但SELECT *要求查询所有列，导致需要回表，优化器可能认为回表成本高于排序成本。

优化2：创建复合索引并使用覆盖索引

创建一个复合索引，并修改SELECT语句只查询索引中的列。

-- 创建复合索引
CREATE INDEX idx_user_amount ON orders(user_id ASC, amount DESC);

-- 使用覆盖索引查询
EXPLAIN SELECT user_id, amount FROM orders ORDER BY user_id ASC, amount DESC;

执行计划分析：
这次Extra列应该显示Using index，因为所有查询的列都在索引中，MySQL可以直接从索引获取数据，无需回表，也无需文件排序。

通过以上步骤，你可以系统地排查和解决ORDER BY不使用索引导致filesort的问题。记住，索引是提升排序性能的关键，而覆盖索引是优化的利器。