萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL LIMIT 分页完全指南:语法、优化与大数据量实战

基本语法

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
  • 单参数:LIMIT 5 等价 LIMIT 0,5,取前 5 行。
  • 双参数:LIMIT 5,10 跳过前 5 行,取第 6–15 行(offset 从 0 开始)。
  • 兼容写法:LIMIT 10 OFFSET 5(PostgreSQL 风格)。
  • 取尾部数据:LIMIT 95, -1 取第 96 行至末尾。

性能陷阱:大偏移量扫描全表

当 offset 极大(如 LIMIT 100000, 10),MySQL 需扫描 100010 行再丢弃前 10 万行,导致 IO 与 CPU 飙升。

优化方案:延迟关联 / 子查询定位

核心思路:先仅扫描主键索引定位目标行 ID,再回表取数据。

方案一:子查询锚定 ID

SELECT * FROM customers
WHERE customer_id >= (
    SELECT customer_id FROM customers ORDER BY customer_id LIMIT 10000, 1
)
LIMIT 10;

方案二:记录上次最大 ID(游标分页)

-- 首页
SELECT id, name, content FROM users ORDER BY id ASC LIMIT 20;

-- 后续页(假设上页最大 id=100073)
SELECT id, name, content FROM users WHERE id > 100073 ORDER BY id ASC LIMIT 20;

适用于 ID 单调递增、无删除或删除不影响业务的场景,性能极佳。

方案三:覆盖索引 + 子查询(含 WHERE 条件)

SELECT * FROM (
    SELECT id FROM wl_tagindex WHERE byname='f' ORDER BY id LIMIT 300000, 10
) a
LEFT JOIN wl_tagindex b ON a.id = b.id;
  • 子查询仅扫描联合索引 (byname, id),极快。
  • 外层回表仅 10 行,总耗时从 3.21s 降至 0.11s。
  • 前提:WHERE 列与 ORDER BY 列需建立联合索引,且子查询不破坏连续性。

其他优化策略

策略适用场景缺点
反向查找偏移量 > 总行数 50%需知总行数,ORDER BY 优化复杂
限制最大偏移业务允许截断深翻页用户体验受损
只查索引列仅需索引覆盖的列不支持 SELECT *

实测对比(169 万行表)

-- 直接 LIMIT
SELECT * FROM Member LIMIT 100000, 100;        -- 0.099s

-- 子查询优化
SELECT * FROM Member WHERE MemberID >= (
    SELECT MemberID FROM Member LIMIT 100000, 1
) LIMIT 100;                                    -- 0.024s

偏移量越大,优化收益越明显。

最佳实践清单

  1. 主键使用 INT AUTO_INCREMENT,便于游标分页。
  2. 联合索引遵循“最左前缀”,覆盖 WHERE + ORDER BY 列。
  3. 深分页优先用“上次最大 ID”游标法;必须用 OFFSET 时,套用子查询定位主键。
  4. 避免在大表对非索引列做大偏移 LIMIT。
copyright © 萬盛學電腦網 all rights reserved