也谈MySQL limit offset深翻页问题

深翻页问题

一页10条记录，翻第1页和第50000页的区别

-- ===== 步骤1：浅分页（第1页） → 很快 =====
EXPLAIN
SELECT * FROM orders
ORDER BY id
LIMIT 10 OFFSET 0;  -- 或 LIMIT 0, 10

id|select_type|table |partitions|type |possible_keys|key    |key_len|ref|rows|filtered|Extra|
--|-----------|------|----------|-----|-------------|-------|-------|---|----|--------|-----|
 1|SIMPLE     |orders|          |index|             |PRIMARY|8      |   |  10|   100.0|     |

-- 3ms
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 0;

-- ===== 步骤2：深分页（第5万页） → 非常慢！ =====
EXPLAIN
SELECT * FROM orders
ORDER BY id
LIMIT 10 OFFSET 500000;

id|select_type|table |partitions|type |possible_keys|key    |key_len|ref|rows  |filtered|Extra|
--|-----------|------|----------|-----|-------------|-------|-------|---|------|--------|-----|
 1|SIMPLE     |orders|          |index|             |PRIMARY|8      |   |500010|   100.0|     |

SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 500000;
-- 411ms 慢！但为什么慢？

存储引擎去聚集索引里边扫500000 + 10 行，然后逐行返给Server，Server按照LIMIT 10 OFFSET 500000判断不在区间的就丢弃，在则保留。最后只留10行。
在上面执行计划可以看到rows确实是500010 ，就这就是深翻页问题的原因。

优化方法

-- =====  优化方案 —— 延迟关联 =====
-- 先只查主键（覆盖索引），再用主键回表
-- 方案A：子查询法
EXPLAIN
SELECT * FROM orders
WHERE id >= (
    SELECT id FROM orders ORDER BY id LIMIT 1 OFFSET 500000
)
ORDER BY id LIMIT 10;

id|select_type|table |partitions|type |possible_keys|key    |key_len|ref|rows  |filtered|Extra      |
--|-----------|------|----------|-----|-------------|-------|-------|---|------|--------|-----------|
 1|PRIMARY    |orders|          |range|PRIMARY      |PRIMARY|8      |   |497542|   100.0|Using where|
 2|SUBQUERY   |orders|          |index|             |PRIMARY|8      |   |500001|   100.0|Using index|

-- 91ms
SELECT * FROM orders
WHERE id >= (
    SELECT id FROM orders ORDER BY id LIMIT 1 OFFSET 500000
)
ORDER BY id LIMIT 10;


-- 方案B：JOIN 法（更通用）
EXPLAIN
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 500000
) t ON o.id = t.id;

id|select_type|table     |partitions|type  |possible_keys|key    |key_len|ref |rows  |filtered|Extra      |
--|-----------|----------|----------|------|-------------|-------|-------|----|------|--------|-----------|
 1|PRIMARY    |<derived2>|          |ALL   |             |       |       |    |500010|   100.0|           |
 1|PRIMARY    |o         |          |eq_ref|PRIMARY      |PRIMARY|8      |t.id|     1|   100.0|           |
 2|DERIVED    |orders    |          |index |             |PRIMARY|8      |    |500010|   100.0|Using index|

-- 92ms
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 500000
) t ON o.id = t.id;

上面优化方法的思路都是属于先用LIMIT 10 OFFSET 500000定位主键，然后再用主键去回表。
从iops角度看，其实没有减少，都是扫了500010行，但是优化方法第一步只查id， 而未优化方法返回的是完整行。

原始深分页：每行解包完整记录（12 个字段、约 120 字节）→ 传完整行 → Server 丢弃
延迟关联：  每行只提取 8 字节主键                    → 传 8 字节  → Server 丢弃
            ↑
         同样的 50 万次"读页"，但每行的解包和传输工作量差一个量级

这就是 411ms 和 91ms 之间那 320ms 的构成——不是少读了页，是每行少干了解包完整记录的活。iops没省，但省了CPU的力气。