企业官网建设流程全解析

在企业级项目中，深分页查询经常会成为性能瓶颈。本篇文章总结了我在实践中优化深分页 SQL 的经验，包括执行计划分析、索引优化、游标分页改写等内容。

一、问题场景

假设我们有一张订单表orders，包含字段：

id, user_id, status, total_amount, create_time

原始查询为：

SELECT id, user_id, status, total_amount, create_time FROM orders WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 990, 10;

• 业务背景：查询某用户最近的订单，且需要支持分页。

• 数据量假设：企业级通常几十万到几百万条订单。

二、原始 SQL 执行计划分析

使用 EXPLAIN查看原始 SQL：

分析：

1. type=ref→ 使用了user_id索引进行精确匹配。

2. key=user_id→ 索引选择正确。

3. Extra=Using filesort→ ORDER BY create_time DESC 未覆盖索引，需要额外排序。

4. 扫描行数→ MySQL 会扫描前 990 条行再丢弃（LIMIT 偏移量大），深分页效率低。

✅ 结论：单列索引只能加速 WHERE 条件，排序仍需额外操作。

三、复合索引优化

为了提升查询效率，我们创建复合索引：

ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time DESC);

• 作用：

  1. 覆盖WHERE user_id=…条件。

  2. 覆盖ORDER BY create_time DESC条件。

• 优化后 EXPLAIN：

分析：

1. type=ref → ref→ 使用索引范围扫描，避免全表扫描。

2. key_len=8→ 复合索引长度增加。

3. Extra=Using index condition→ ICP (Index Condition Pushdown) 优化回表行数。

✅ 结论：复合索引同时覆盖 WHERE + ORDER BY，大幅减少扫描行数和排序成本。

四、游标分页改写

深分页仍有偏移量大的问题，可以改写为游标分页：

SELECT id, user_id, status, total_amount, create_time FROM orders WHERE user_id = 12345 AND create_time < '2024-06-01 12:00:00' ORDER BY create_time DESC LIMIT 10;

• 优势：

  • 不用 OFFSET，避免扫描前面大量行。

  • 对大数据量分页性能稳定。

• EXPLAIN 输出：

✅ 结论：扫描行数恒定，排序在索引中完成，性能最佳。

五、实践总结

1. 深分页性能问题：

   • OFFSET 大时，MySQL 会扫描并丢弃大量行。

   • ORDER BY 未覆盖索引 →filesort。

2. 优化策略：

   • 复合索引覆盖 WHERE + ORDER BY。

   • 游标分页替代大 OFFSET 分页。

3. EXPLAIN 解析技巧：

   • type→ 尽量是ref、range或const，避免ALL。

   • key / key_len→ 关注索引是否被正确使用。

   • Extra→ 理解Using index condition和Using filesort。

4. 经验总结：

   • 使用 ICP 能减少回表行数。

   • 游标分页适合大数据量分页查询。

   • SQL 优化不仅是索引，改写查询逻辑同样重要。