01.深度分页

• 在 MySQL 中，深度分页通常会带来性能问题，尤其是当偏移量（OFFSET）很大时
• 这是因为 MySQL 在执行带有 LIMIT 和 OFFSET 的查询时，依然会从第 0 行开始逐条读取数据，直到达到指定的 OFFSET
• 这种情况下，即使你只需要后面几条记录，前面的记录仍然会被读取并丢弃，导致查询变慢

• 1）使用延迟关联（Deferred Join）

  • 当表有索引时，可以先通过索引查出主键 ID 的范围，再根据主键 ID 进行关联查询，避免使用大的偏移量

  • 这种方式减少了 MySQL 在大数据集上扫描的负担，尤其适用于带有索引的表

  • -- 第一步：只查出主键
    SELECT id FROM your_table ORDER BY id LIMIT 10000, 10;
    
    -- 第二步：根据主键获取完整数据
    SELECT * FROM your_table WHERE id IN (/*上一步得到的ID列表*/) ORDER BY id;
    

• 2）使用唯一索引替代 OFFSET

  • 如果表有唯一的自增 ID 或其他唯一字段，可以直接利用该索引来替代 OFFSET 进行分页

  • 可以通过 WHERE 子句过滤掉已经处理过的数据，从而避免 OFFSET 扫描

  • 这种方法通常比 LIMIT OFFSET 性能要好得多，特别是在进行深度分页时

  • SELECT * FROM your_table WHERE id > last_seen_id ORDER BY id ASC LIMIT 10;
    

• 3）合理使用覆盖索引

  • 覆盖索引是指 MySQL 可以仅通过索引查到所需数据，而无需回表（查询主表）

  • 通过确保所查询的列都在索引中，可以减少 I/O 操作，从而提高查询性能

  • SELECT id, name FROM your_table USE INDEX (idx_name) ORDER BY id LIMIT 10000, 10;