mysql如何优化数据库索引以支持高频分页查询_采用ID范围过滤法-mysql教程-PHP中文网

mysql深度分页慢因limit offset,size需扫描offset+size行并丢弃前offset行，而where id>？order by id limit n可直接索引定位，i/o从o(offset+size)降至o(size)，但要求id为主键或有序索引、查询严格匹配排序字段、应用层维护游标状态。

mysql如何优化数据库索引以支持高频分页查询_采用id范围过滤法

直接用 LIMIT offset, size 做分页，只要 offset 超过几万，查询就会明显变慢——这不是配置问题，是 MySQL 扫描机制决定的。ID 范围过滤法能绕过这个问题，但前提是索引设计和查询写法必须匹配。

为什么 `WHERE id > ? ORDER BY id LIMIT N` 快得多

MySQL 在执行 LIMIT 100000, 10 时，即使有主键索引，也得从头扫描 100010 行才能跳过前 100000 条；而 WHERE id > 100000 ORDER BY id LIMIT 10 可以直接定位到 B+ 树中 id = 100001 的叶子节点，往后取 10 条，I/O 次数从 O(offset + size) 降到 O(size)。

这要求：

id 字段必须是主键或有单独的升序/降序索引（ORDER BY id DESC 时需建 INDEX idx_id_desc (id DESC)）
查询必须严格按该字段排序，不能混用其他排序逻辑（如 ORDER BY create_time, id 就不适用）
应用层要能保存上一页最后一条的 id 值（游标），不能让用户随意输“第 892 页”

`id` 不连续或带条件筛选时怎么写

如果表里有删除、或分页时带 WHERE type = 8 这类条件，直接用 id > ? 会漏数据或跳行——因为满足条件的 id 并不连续。

正确做法是先用子查询锁定边界 id：

SELECT * FROM orders_history 
WHERE id >= (
  SELECT id FROM orders_history 
  WHERE type = 8 
  ORDER BY id 
  LIMIT 100000, 1
) AND type = 8 
ORDER BY id 
LIMIT 10;

注意两点：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

子查询里 WHERE 和 ORDER BY 必须一致，否则索引失效
外层查询的 WHERE type = 8 不能省，否则可能查出不满足条件的记录
这个子查询本身也要走索引，所以 (type, id) 复合索引比单列 id 索引更可靠

索引怎么建才真正生效

只建 PRIMARY KEY(id) 不够。当分页带筛选条件（比如 WHERE status = 'paid'）时，MySQL 很可能放弃主键索引，改走全表扫描。

应该根据实际查询模式建覆盖型复合索引：

如果常用 WHERE status = ? ORDER BY id：建 INDEX idx_status_id (status, id)
如果常用 WHERE created_at > ? ORDER BY id：建 INDEX idx_created_at_id (created_at, id)，注意顺序不能颠倒
避免在索引列上用函数或表达式，比如 WHERE DATE(created_at) = '2025-01-01' 会让索引完全失效

验证是否走索引：用 EXPLAIN 看 key 列是否非 NULL，rows 是否接近 LIMIT 值（而不是百万级）。