分页查询还在用create_time去做降序？

本笔记记录的这个案例非常经典，也在展示一种优化原则：有时，巧妙地利用现有资源，比盲目增加索引更能体现技术深度。

📌 问题定位：一个典型的排序慢SQL

在巡检中发现一条慢查询，其目的是获取签约信息表中最新创建的10条记录：

SELECT id, batch_no, order_no, ... -- 多个字段
FROM contract_sign_info
ORDER BY create_time DESC  -- 按创建时间降序排序
LIMIT 0, 10

其中contract_sign_info表DDL如下：

CREATE TABLE `contract_sign_info` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `batch_no` varchar(128) DEFAULT NULL COMMENT '合同批次号',
  `order_no` varchar(128) DEFAULT NULL COMMENT '合同订单号',
  `contract_no` varchar(32) DEFAULT NULL COMMENT '合同编号,APLXXXXX',
  `contract_name` varchar(200) NOT NULL COMMENT '合同名称',
  `contract_tmpid` varchar(64) NOT NULL COMMENT '合同模版ID',
  `contract_status` int NOT NULL DEFAULT '0' COMMENT '合同状态 0:未签约 1:签约中 2:拒签 3:已签约',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `business_type` varchar(32) NOT NULL DEFAULT '1' COMMENT '业务名称 1:优付 2:设计云',
  `channel_type` varchar(32) NOT NULL DEFAULT '1' COMMENT '调用渠道 1:易保全',
  `contract_type` varchar(12) DEFAULT NULL COMMENT 'B2C,B2B,B2B2B',
  `total_num` int DEFAULT NULL COMMENT '批量签约总份数',
  `sequence_order` int DEFAULT NULL COMMENT '合同业务顺序号',
  `contract_url` varchar(128) DEFAULT NULL COMMENT '合同地址',
  `signatories_json` varchar(500) DEFAULT NULL COMMENT '签约方信息（记录签约方名称方便查询，签约方A,签约方B）',
  `return_msg` varchar(500) DEFAULT NULL COMMENT '签约返回信息',
  `template_param` text COMMENT '协议参数JSON',
  PRIMARY KEY (`id`),
  UNIQUE KEY `pk_cs_order_no` (`order_no`) USING BTREE,
  KEY `index_contract_no` (`contract_no`),
  KEY `idx_signatories_json` (`signatories_json`(400)),
  KEY `idx_batch_no` (`batch_no`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb3 COMMENT = '合同签约信息表'

执行情况：该表约有368W 条数据，此查询耗时超过4秒。

下面表格展示了此次慢查询的关键性能指标。

执行开始时间	数据库名	用户名	执行总次数	执行总时长	执行时长(95%)	执行总时长(秒)	锁定总时长(秒)	解析总行数	返回总行数
2025/12/25 15:30	auth	axxxer'@'10.0..'	2	4.275785	2.137892	7346843	20	3673421	10

瓶颈分析：问题的关键在于 ORDER BY create_time DESC。由于 create_time 字段上没有建立索引，MySQL 只能进行全表扫描，并对所有 368W 行数据在磁盘上执行昂贵的 Filesort 操作，这是最耗时的部分 。

💡 巧妙优化：利用主键索引的“四两拨千斤”

最直接的想法是为 create_time 字段创建索引。但这会带来额外的存储和维护开销。这里洞察到了一个更巧妙的解决方案：

对于这种流水表，自增id与create_time是强正相关的。

这意味着，后插入的数据其 id 越大，通常 create_time 也越晚。因此，ORDER BY create_time DESC 与 ORDER BY id DESC 的排序结果几乎是完全一致的。

基于此，可以将SQL改写为：

SELECT id, batch_no, order_no, ... -- 原字段
FROM contract_sign_info
ORDER BY id DESC  -- 改为按主键ID降序
LIMIT 10

优化效果：查询时间从 4秒+ 降至 20毫秒以内。

核心原理：id 字段是主键，自带高效的聚簇索引。MySQL 可以直接利用这棵B+树，从右向左（降序）快速定位到最小的10个 id（即最新的10条记录），几乎无需扫描无关数据 。这个方案没有增加任何额外的索引开销，直接利用了最高效的现有资源。

🚀 技术拓展：深分页场景的优化方案

当分页的偏移量（OFFSET）非常大时，例如 LIMIT 100000, 10，即使有索引，性能也会急剧下降，因为数据库仍需遍历并跳过大量的记录。这就是“深分页”问题。以下是几种主流的优化方案：

1. 游标分页（Cursor-based Pagination）【推荐】

这是解决深分页最有效的方案，尤其适合“无限加载”或“上一页/下一页”的场景。

• 做法：不记录页码，而是记录上一页最后一条记录的标识（如 id 或 create_time）。
• 示例：假设上一页最后一条记录的 id 是 100000，查询下一页的SQL为：

  SELECT * FROM contract_sign_info 
  WHERE id < 100000  -- 关键：直接定位到开始位置
  ORDER BY id DESC 
  LIMIT 10
  

• 优点：性能恒定，与页码深度无关。
• 缺点：不支持随机跳转到指定页码 。

2. 延迟关联（Deferred Join）

此方案将获取记录和回表查询分开，减少大量数据的回表操作。

• 做法：先通过覆盖索引快速定位到目标页的主键ID，再通过关联查询回表获取完整数据。
• 示例：

  SELECT o.* FROM contract_sign_info o
  INNER JOIN (
      SELECT id FROM contract_sign_info
      ORDER BY id DESC
      LIMIT 3000000, 10  -- 在子查询中先分页，只扫描索引
  ) AS tmp ON o.id = tmp.id  -- 再通过主键快速关联出完整数据
  

• 适用场景：排序字段有索引，且需要回表查询所有列的情况 。

3. 覆盖索引（Covering Index）优化

• 做法：创建一个包含查询中所有字段的索引，使查询仅通过索引就能完成，避免回表。
• 缺点：索引会占用较大存储空间，且如果查询字段经常变化，则难以设计 。

💎 总结与启示

优化策略	核心思想	适用场景
利用主键排序	利用自增ID与时间的强相关性，避免新建索引。	按时间顺序获取最新或最旧记录。
游标分页	记录位置而非页码，跳过 OFFSET。	无限滚动、只能顺序翻页的场景。
延迟关联	先通过索引定位主键，再回表，减少无效IO。	深分页且排序字段有索引的场景。

这次优化案例给我们的核心启示是：数据库优化乃至程序优化，转换思路有时比一味增加资源更有效。在面对性能问题时，先深入理解业务逻辑（如数据生成规律）和数据库原理（如索引机制），往往能找到更优雅、更高效的解决方案。