MySQL分页查询踩坑记：从物理存储到结果排序的思考

CREATE TABLE tb_foo_bar (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    subject_type INT NOT NULL,
    subject_id INT NOT NULL,
    INDEX idx_company_id (company_id,subject_type,subject_id)
);

SELECT *
FROM tb_foo_bar
WHERE company_id = 4
	AND subject_type = 1
LIMIT 10 OFFSET 40;

[数据页结构]
Page Header
│
├── 记录1 (主键=1)
├── 记录2 (主键=2)
├── 记录3 (主键=3)
└── ...

[页间关系]
Page 1 ──► Page 2 ──► Page 3 ──► ...
(1-100)    (101-200)   (201-300)

Buffer Pool
├── LRU List
│   ├── Young Page List
│   └── Old Page List
└── Free List

-- 案例分析
EXPLAIN FORMAT=TREE
SELECT * 
FROM tb_foo_bar 
WHERE company_id = 4 
  AND subject_type = 1 
LIMIT 10 OFFSET 1000;

/*
可能的执行计划1（小偏移）：
└── Index Scan (idx_company_subject)
    └── Filter: company_id=4 AND subject_type=1
        └── Limit/Offset

可能的执行计划2（大偏移）：
└── Table Scan
    └── Filter: company_id=4 AND subject_type=1
        └── Limit/Offset
*/

路径1（使用索引）：
└── 索引扫描
    ├── 1. 定位索引起始位置 (company_id=4 AND subject_type=1)
    ├── 2. 顺序扫描索引记录
    ├── 3. 通过主键回表获取完整记录
    └── 4. 收集直到满足 OFFSET+LIMIT 条记录

路径2（全表扫描）：
└── 表扫描
    ├── 1. 从第一个数据页开始顺序扫描
    ├── 2. 对每条记录应用 WHERE 条件过滤
    ├── 3. 符合条件的记录进入结果集
    └── 4. 直到满足 OFFSET+LIMIT 条记录

1. 数据获取阶段
   ├── 索引扫描路径
   │   ├── 按索引顺序读取（company_id, subject_type, subject_id 顺序）
   │   ├── 回表获取完整记录
   │   └── 结果顺序受索引结构影响
   │
   └── 全表扫描路径
       ├── 按数据页顺序读取
       ├── 应用过滤条件
       └── 结果顺序受数据页存储顺序影响

2. 结果集处理阶段
   ├── 内存临时表处理（sort_buffer_size 范围内）
   │   ├── 存储中间结果
   │   └── 可能发生内存中排序
   │
   └── 磁盘临时表处理（超出 sort_buffer_size）
       ├── 创建磁盘临时表
       ├── 分批处理数据
       └── 可能发生文件排序

3. LIMIT/OFFSET 处理阶段
   ├── 跳过 OFFSET 条记录
   ├── 保留 LIMIT 条记录
   └── 返回最终结果集