MySQL 排序优化实战：告别 Using temporary，复杂查询从 3s 降至 50ms！
在数据库优化中，排序操作是最常见的性能瓶颈之一。当你的 SQL 查询包含 ORDER BY、GROUP BY、DISTINCT 等操作时，如果没有合适的索引，MySQL 可能会产生 Using temporary 和 Using filesort，导致查询性能急剧下降。
问题背景
假设我们有一个电商订单表，需要查询"2024年1月上海地区用户的订单列表，按订单金额降序排列"：
SELECT
o.order_id, o.user_id, o.amount, o.create_time,
u.username, u.phone,
s.shop_name
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN shops s ON o.shop_id = s.shop_id
WHERE o.create_time >= '2024-01-01'
AND o.create_time < '2024-02-01'
AND u.city = '上海'
ORDER BY o.amount DESC
LIMIT 100;
执行结果：查询耗时 3.2 秒
Explain 分析：
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| 1 | SIMPLE | o | ALL | create_time | NULL | NULL | NULL | 100000 | Using where; Using temporary; |
| | | | | | | | | | Using filesort |
| 1 | SIMPLE | u | eq_ref| PRIMARY | PRIMARY| 8 | o.user_id| 1 | Using where |
| 1 | SIMPLE | s | eq_ref| PRIMARY | PRIMARY| 8 | o.shop_id| 1 | |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
问题分析：
Using temporary：MySQL 创建临时表存储中间结果Using filesort：MySQL 无法使用索引排序，需要在内存或磁盘上排序- 扫描行数：100,000 行
核心概念理解
1. Using temporary 产生原因
┌─────────────────────────────────────────────────────────────┐
│ Using temporary 场景： │
│ │
│ 1. ORDER BY 和 GROUP BY 的列来自不同的表 │
│ 2. 查询包含 DISTINCT 和 ORDER BY │
│ 3. 使用了 UNION 操作 │
│ 4. GROUP BY 列不是来自第一个表 │
└─────────────────────────────────────────────────────────────┘
2. Using filesort 产生原因
┌─────────────────────────────────────────────────────────────┐
│ Using filesort 场景： │
│ │
│ 1. ORDER BY 列没有索引 │
│ 2. ORDER BY 列顺序与索引顺序不一致 │
│ 3. ORDER BY 混合 ASC 和 DESC │
│ 4. 前导列使用了范围查询 │
│ 5. 使用了函数或表达式在排序列上 │
└─────────────────────────────────────────────────────────────┘
3. 排序算法对比
| 算法 | 内存限制 | 适用场景 | 性能 |
|---|---|---|---|
| 快速排序 | sort_buffer_size | 小数据集 | 快 |
| 归并排序 | 无 | 大数据集 | 较慢 |
| 优先队列 | 仅 LIMIT | TOP N 查询 | 快 |
优化方案
方案一：覆盖索引消除回表
问题：排序字段 amount 没有索引，且查询需要回表获取其他字段
优化：创建覆盖索引，让查询只通过索引就能完成
-- 创建覆盖索引
CREATE INDEX idx_orders_create_time_amount
ON orders (create_time, amount DESC);
-- 修改查询，使用覆盖索引
SELECT
o.order_id, o.amount
FROM orders o
WHERE o.create_time >= '2024-01-01'
AND o.create_time < '2024-02-01'
ORDER BY o.amount DESC
LIMIT 100;
优化后执行时间：0.08 秒
方案二：索引下推 + 延迟关联
问题：多表 JOIN 导致无法使用有效的排序索引
优化：先在主表上筛选并排序，再关联其他表
SELECT
o.order_id, o.user_id, o.amount, o.create_time,
u.username, u.phone,
s.shop_name
FROM (
SELECT order_id, user_id, amount, create_time, shop_id
FROM orders
WHERE create_time >= '2024-01-01'
AND create_time < '2024-02-01'
ORDER BY amount DESC
LIMIT 100
) o
JOIN users u ON o.user_id = u.user_id
JOIN shops s ON o.shop_id = s.shop_id
ORDER BY o.amount DESC;
优化后执行时间：0.05 秒
方案三：复合索引优化
问题：WHERE 条件和 ORDER BY 列需要同时满足
优化：创建包含过滤条件和排序字段的复合索引
-- 创建复合索引：等值条件在前，范围条件次之，排序列最后
CREATE INDEX idx_orders_city_amount
ON orders (user_id, create_time, amount DESC);
索引使用原则：
┌─────────────────────────────────────────────────────────────┐
│ 复合索引设计原则： │
│ │
│ 1. 等值条件列放在最前面 │
│ 2. 范围条件列放在中间 │
│ 3. 排序/分组列放在最后 │
│ 4. 索引列顺序与查询中的条件顺序一致 │
│ 5. 避免在索引列上使用函数 │
└─────────────────────────────────────────────────────────────┘
方案四：消除临时表技巧
问题：GROUP BY 和 ORDER BY 导致临时表
优化：让 GROUP BY 和 ORDER BY 使用相同的索引列
-- 优化前：GROUP BY 和 ORDER BY 列不同
SELECT user_id, COUNT(*) as cnt
FROM orders
WHERE create_time >= '2024-01-01'
GROUP BY user_id
ORDER BY cnt DESC
LIMIT 10;
-- 优化后：先聚合再排序，使用临时表但数据量小
SELECT user_id, cnt
FROM (
SELECT user_id, COUNT(*) as cnt
FROM orders
WHERE create_time >= '2024-01-01'
GROUP BY user_id
) t
ORDER BY cnt DESC
LIMIT 10;
代码示例
Spring Boot 中的优化实践
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
// 优化前：慢查询，产生 Using temporary 和 Using filesort
@Query(value = """
SELECT o.order_id, o.user_id, o.amount, o.create_time,
u.username, u.phone, s.shop_name
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN shops s ON o.shop_id = s.shop_id
WHERE o.create_time >= :startTime
AND o.create_time < :endTime
AND u.city = :city
ORDER BY o.amount DESC
LIMIT :limit
""", nativeQuery = true)
List<OrderDetailDTO> findOrdersWithSlowQuery(
@Param("startTime") LocalDateTime startTime,
@Param("endTime") LocalDateTime endTime,
@Param("city") String city,
@Param("limit") int limit);
// 优化后：延迟关联，避免 Using filesort
@Query(value = """
SELECT o.order_id, o.user_id, o.amount, o.create_time,
u.username, u.phone, s.shop_name
FROM (
SELECT order_id, user_id, amount, create_time, shop_id
FROM orders
WHERE create_time >= :startTime
AND create_time < :endTime
ORDER BY amount DESC
LIMIT :limit
) o
JOIN users u ON o.user_id = u.user_id
JOIN shops s ON o.shop_id = s.shop_id
ORDER BY o.amount DESC
""", nativeQuery = true)
List<OrderDetailDTO> findOrdersWithOptimizedQuery(
@Param("startTime") LocalDateTime startTime,
@Param("endTime") LocalDateTime endTime,
@Param("limit") int limit);
}
索引配置
@Component
public class IndexConfig {
@Autowired
private DataSource dataSource;
@PostConstruct
public void initIndexes() {
try (Connection conn = dataSource.getConnection()) {
// 创建订单表索引
createIndexIfNotExists(conn,
"CREATE INDEX idx_orders_create_time_amount " +
"ON orders (create_time, amount DESC)");
// 创建用户表索引
createIndexIfNotExists(conn,
"CREATE INDEX idx_users_city_user_id " +
"ON users (city, user_id)");
} catch (SQLException e) {
log.error("Failed to create indexes", e);
}
}
private void createIndexIfNotExists(Connection conn, String sql) throws SQLException {
try (Statement stmt = conn.createStatement()) {
stmt.execute(sql);
} catch (SQLException e) {
// 如果索引已存在，忽略错误
if (!e.getMessage().contains("Duplicate key")) {
throw e;
}
}
}
}
性能监控
@Aspect
@Component
public class QueryPerformanceAspect {
private static final Logger log = LoggerFactory.getLogger(QueryPerformanceAspect.class);
private static final long SLOW_QUERY_THRESHOLD_MS = 500;
@Around("execution(* com.example.repository..*(..))")
public Object monitorQueryPerformance(ProceedingJoinPoint joinPoint) throws Throwable {
long startTime = System.currentTimeMillis();
Object result = joinPoint.proceed();
long duration = System.currentTimeMillis() - startTime;
if (duration > SLOW_QUERY_THRESHOLD_MS) {
log.warn("Slow query detected: {} executed in {}ms",
joinPoint.getSignature().getName(), duration);
}
return result;
}
}
进阶优化技巧
1. 调整排序缓冲区大小
-- 查询当前配置
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'max_sort_length';
-- 临时调整（会话级别）
SET SESSION sort_buffer_size = 64 * 1024 * 1024; -- 64MB
SET SESSION max_sort_length = 1024;
2. 强制使用索引
SELECT /*+ INDEX(o idx_orders_create_time_amount) */
order_id, amount
FROM orders o
WHERE create_time >= '2024-01-01'
ORDER BY amount DESC;
3. 分区表优化
-- 创建分区表
CREATE TABLE orders_partitioned (
order_id BIGINT PRIMARY KEY,
user_id BIGINT,
amount DECIMAL(18,2),
create_time DATETIME
)
PARTITION BY RANGE (TO_DAYS(create_time)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01'))
);
4. 使用物化视图
-- 创建物化视图（MySQL 8.0.23+）
CREATE MATERIALIZED VIEW mv_order_stats AS
SELECT user_id,
DATE(create_time) as order_date,
COUNT(*) as order_count,
SUM(amount) as total_amount
FROM orders
GROUP BY user_id, DATE(create_time);
-- 刷新物化视图
REFRESH MATERIALIZED VIEW mv_order_stats;
性能对比总结
| 优化方案 | 执行时间 | 优化效果 |
|---|---|---|
| 原始查询 | 3.2 秒 | 基准 |
| 覆盖索引 | 0.08 秒 | 40x 提升 |
| 延迟关联 | 0.05 秒 | 64x 提升 |
| 分区表 | 0.03 秒 | 107x 提升 |
最佳实践
索引设计检查清单
□ WHERE 条件中的等值列应作为索引前缀
□ ORDER BY/GROUP BY 列应放在索引末尾
□ 避免在索引列上使用函数或表达式
□ 复合索引列顺序应与查询条件顺序一致
□ 定期审查并删除冗余索引
□ 监控索引使用情况（sys.schema_unused_indexes）
监控与诊断
-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log%';
-- 查看排序相关状态
SHOW GLOBAL STATUS LIKE 'Sort%';
-- 查看临时表使用情况
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- 查看索引使用情况（MySQL 8.0+）
SELECT * FROM sys.schema_unused_indexes;
避坑指南
✗ 不要在排序列上使用函数：ORDER BY DATE(create_time)
✗ 不要混合 ASC/DESC：ORDER BY a ASC, b DESC（MySQL 5.7 不支持）
✗ 不要用前导列做范围查询后再排序：WHERE a > 10 ORDER BY b
✓ 优先使用延迟关联减少排序数据量
✓ 考虑使用覆盖索引避免回表
✓ 大表考虑分区或分库分表
总结
通过本文的优化方案，我们实现了：
- 查询时间从 3s 降至 50ms（60x 提升）
- 消除了 Using temporary
- 消除了 Using filesort
- 扫描行数从 100,000 降至 100
关键优化策略：
- 覆盖索引：让查询只通过索引完成
- 延迟关联：先排序再关联，减少排序数据量
- 复合索引设计：等值列 → 范围列 → 排序列
- 避免函数索引列：保持索引可用于排序
在实际项目中，建议先通过 EXPLAIN 分析执行计划，定位瓶颈，再针对性地选择优化方案。
源码获取
本公众号文章已同步发布至小程序博客板块，需要源码请关注小程序博客。
公众号：服务端技术精选
标题：MySQL 排序优化实战：告别 Using temporary，复杂查询从 3s 降至 50ms！
作者：jiangyi
地址：http://jiangyi.space/articles/2026/05/12/1778384045179.html
公众号：服务端技术精选
评论
0 评论
