MySQL执行计划中Using filesort问题解决

一、什么是Using filesort

当你在MySQL执行计划中看到"Using filesort"这个提示时，就像看到汽车仪表盘上的警告灯一样，它在告诉你：当前的查询需要进行额外的排序操作。这个排序不是在内存中完成的，而是需要用到临时文件，所以性能上会有一定的影响。

简单来说，filesort是MySQL用来处理ORDER BY子句的一种算法。当MySQL不能使用索引来满足排序要求时，它就必须把数据取出来，在内存或者磁盘上进行排序。这个过程就像你把一堆杂乱的文件拿出来，重新整理排序一样，自然需要额外的时间和资源。

举个例子，假设我们有一个用户表：

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    register_time DATETIME NOT NULL,
    INDEX idx_username (username)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO users (username, age, register_time) VALUES
('张三', 25, '2022-01-01 10:00:00'),
('李四', 30, '2022-01-02 11:00:00'),
('王五', 28, '2022-01-03 12:00:00'),
('赵六', 35, '2022-01-04 13:00:00');

如果我们执行以下查询：

EXPLAIN SELECT * FROM users ORDER BY age;

执行计划中就会出现"Using filesort"，因为我们没有为age字段建立索引，MySQL只能把所有数据取出来进行排序。

二、为什么Using filesort会成为性能问题

filesort之所以会成为性能瓶颈，主要有以下几个原因：

内存消耗：如果排序的数据量很大，MySQL需要分配大量内存来进行排序，当内存不足时，还会使用磁盘临时文件，这会显著降低性能。
CPU消耗：排序操作本身是CPU密集型操作，特别是对于大数据集。
延迟返回结果：与使用索引直接按顺序读取数据不同，filesort需要先获取所有数据，排序完成后才能返回第一行结果。

来看一个更复杂的例子：

-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_order_date (order_date)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO orders (user_id, amount, order_date, status) VALUES
(1, 100.00, '2022-01-01', 'completed'),
(2, 200.00, '2022-01-02', 'completed'),
(3, 150.00, '2022-01-03', 'pending'),
(4, 300.00, '2022-01-04', 'completed'),
(1, 250.00, '2022-01-05', 'pending');

执行以下查询：

EXPLAIN SELECT * FROM orders WHERE status = 'completed' ORDER BY amount DESC;

这个查询会先过滤出状态为'completed'的订单，然后按金额降序排列。由于amount字段没有索引，MySQL只能使用filesort。随着订单表数据量的增长，这个查询会越来越慢。

三、如何避免和优化Using filesort

1. 为排序字段添加合适的索引

最直接的解决方案是为排序字段创建索引。MySQL可以使用索引来优化ORDER BY操作，避免filesort。

-- 为amount字段添加索引
ALTER TABLE orders ADD INDEX idx_amount (amount);

-- 再次执行查询
EXPLAIN SELECT * FROM orders WHERE status = 'completed' ORDER BY amount DESC;

现在执行计划中应该看不到"Using filesort"了，因为MySQL可以使用idx_amount索引直接按顺序读取数据。

2. 使用复合索引

当查询条件包含WHERE和ORDER BY时，复合索引可以发挥更大作用。索引的顺序很重要，应该把等值查询的列放在前面，范围查询和排序的列放在后面。

-- 创建复合索引
ALTER TABLE orders ADD INDEX idx_status_amount (status, amount);

-- 执行查询
EXPLAIN SELECT * FROM orders WHERE status = 'completed' ORDER BY amount DESC;

这个复合索引可以同时满足过滤和排序的需求，避免了filesort。

3. 优化现有索引

有时候，现有的索引可能没有被充分利用。可以通过调整查询语句来更好地利用索引。

-- 假设我们已经有idx_status_amount(status, amount)索引
-- 不优化的查询
EXPLAIN SELECT * FROM orders WHERE status IN ('completed', 'pending') ORDER BY amount DESC;

-- 优化后的查询
EXPLAIN SELECT * FROM orders WHERE status = 'completed' ORDER BY amount DESC
UNION ALL
SELECT * FROM orders WHERE status = 'pending' ORDER BY amount DESC;

IN条件有时会阻止索引的使用，拆分成UNION ALL可以让每个部分都能使用索引。

4. 限制结果集大小

如果只需要前几行结果，使用LIMIT可以减少排序的数据量。

-- 只需要金额最高的10个已完成订单
EXPLAIN SELECT * FROM orders WHERE status = 'completed' ORDER BY amount DESC LIMIT 10;

即使需要使用filesort，由于数据量小，性能影响也会大大降低。

5. 调整sort_buffer_size参数

如果确实无法避免filesort，可以通过调整MySQL参数来优化排序性能：

-- 查看当前sort_buffer_size设置
SHOW VARIABLES LIKE 'sort_buffer_size';

-- 临时设置为更大的值(例如4MB)
SET SESSION sort_buffer_size = 4 * 1024 * 1024;

增大sort_buffer_size可以让更多排序在内存中完成，减少磁盘I/O。

四、实际案例分析

让我们看一个更复杂的实际案例，分析如何一步步优化filesort问题。

假设我们有一个电商系统的商品表：

-- 创建商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    sales INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category_id (category_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- 插入测试数据(这里省略具体数据，假设表中有足够多的数据)

业务需求：获取某个分类下价格在100-500元之间，按销量降序排列的商品列表，分页显示。

初始查询：

EXPLAIN SELECT * FROM products 
WHERE category_id = 5 AND price BETWEEN 100 AND 500 
ORDER BY sales DESC 
LIMIT 0, 20;

这个查询可能会有以下问题：

使用了filesort，因为sales字段没有索引
即使为sales创建单独索引，由于有范围查询price，索引可能不会被充分利用

优化方案：

创建复合索引：

ALTER TABLE products ADD INDEX idx_category_price_sales (category_id, price, sales);

修改查询语句：

EXPLAIN SELECT * FROM products 
WHERE category_id = 5 AND price >= 100 AND price <= 500 
ORDER BY sales DESC 
LIMIT 0, 20;

如果数据量非常大，可以考虑使用延迟关联：

EXPLAIN SELECT p.* FROM products p
INNER JOIN (
    SELECT id FROM products
    WHERE category_id = 5 AND price >= 100 AND price <= 500
    ORDER BY sales DESC
    LIMIT 0, 20
) AS tmp ON p.id = tmp.id
ORDER BY sales DESC;

这种优化方式先利用覆盖索引获取ID，再通过JOIN获取完整数据，可以显著减少排序的数据量。

五、特殊情况处理

有些情况下，即使有索引，MySQL仍然会使用filesort：

排序方向不一致：

-- 复合索引是(category_id, price, sales)
EXPLAIN SELECT * FROM products 
WHERE category_id = 5 
ORDER BY price ASC, sales DESC;

如果排序方向不一致(一个ASC一个DESC)，MySQL可能无法使用索引排序。解决方案是使用相同的排序方向或创建特殊索引：

ALTER TABLE products ADD INDEX idx_category_price_sales_desc (category_id, price ASC, sales DESC);

使用了不同的排序表达式：

-- 使用函数或表达式
EXPLAIN SELECT * FROM products 
WHERE category_id = 5 
ORDER BY ABS(price - 300);

这种情况无法使用索引排序，应该尽量避免在ORDER BY中使用函数。

多表JOIN查询：

EXPLAIN SELECT p.*, c.name AS category_name 
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price > 100
ORDER BY p.sales DESC;

在多表查询中，排序字段可能来自驱动表或被驱动表，索引使用会更复杂。需要确保ORDER BY的字段来自驱动表，或者考虑使用STRAIGHT_JOIN控制JOIN顺序。

六、监控与诊断

为了有效发现和解决filesort问题，我们需要一些监控和诊断工具：

慢查询日志：

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 记录执行超过1秒的查询
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 记录未使用索引的查询

性能分析：

-- 使用EXPLAIN ANALYZE(MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM products ORDER BY sales DESC LIMIT 100;

-- 使用SHOW PROFILE(较老版本)
SET profiling = 1;
SELECT * FROM products ORDER BY sales DESC LIMIT 100;
SHOW PROFILE;

使用Performance Schema监控排序操作：

-- 查看排序相关的统计
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%ORDER BY%'
ORDER BY SUM_SORT_SCAN DESC LIMIT 10;

七、总结与最佳实践

通过以上分析和示例，我们可以总结出以下最佳实践：

优先考虑索引优化：为常用的排序字段创建适当的索引，特别是复合索引。
理解索引使用规则：MySQL使用索引进行排序需要满足一定条件，如排序字段顺序与索引一致、排序方向相同等。
避免复杂的排序表达式：尽量不要在ORDER BY中使用函数或复杂表达式。
合理使用LIMIT：当只需要部分结果时，使用LIMIT减少排序数据量。
监控和诊断：定期检查慢查询日志和执行计划，及时发现filesort问题。
权衡利弊：不是所有的filesort都需要消除，对于小数据量或低频查询，优化的收益可能不明显。
考虑替代方案：对于特别复杂的排序需求，可以考虑使用应用程序内存排序或专门的搜索工具如Elasticsearch。

记住，数据库优化是一个系统工程，需要结合具体业务场景、数据特点和访问模式来制定最合适的方案。filesort只是众多性能问题中的一个，理解其原理和解决方案有助于我们构建更高效的数据库应用。

敲码拾光专注于编程技术，涵盖编程语言、代码实战案例、软件开发技巧、IT前沿技术、编程开发工具，是您提升技术能力的优质网络平台。