MySQL深度分页问题

深度分页问题解析

select
    *
from
    test
ORDER BY
    createdTime
LIMIT 300000,10

• 表结构: ID (主键), name (名字), createdTime (创建时间, 索引)
• 查询示例: LIMIT从第30万行开始返回10条记录
• 执行效率低: 1000万表中执行时间68364毫秒, 全表扫描而非利用索引
• 索引结构
  • 聚集索引: 非叶子节点为ID值, 叶子节点为完整数据
  • 非聚集索引: 非叶子节点为time值, 叶子节点为主键ID值
• 回表操作: 非聚集索引到聚集索引查找数据, 需执行30万次

解决方案

• 优化查询语句
  • 子查询优化: 使用子查询找到第30万行的时间戳
  • 条件优化: 使用查找到的时间戳作为后续查询的起点
  • 执行计划改进: 子查询在非聚集索引上查找, 提升效率至1879毫秒

select
    *
from
    test
WHERE
  createdTime>=(
    select
            createdTime
        from
            test
        ORDER BY
            createdTime
        limit 30000,1
  )
ORDER BY
    createdTime
LIMIT 10

• 减少回表次数
  • 索引覆盖: 先仅查询”time”字段, 使用”LIMIT”取第30万行的第一条记录
  • 条件优化: 使用查找到的”time”值作为后续查询的起点, 减少回表操作
  • 执行计划改进: 子查询在非聚集索引上查找, 提升效率至1879毫秒
• 进一步优化

  • 页数翻页策略: 基于上一页最后一条记录的”time”值作为下一页查询起点

  • 细节处理: 相同”time”值下的数据分割, 避免重复页

    • 分割处理: 在相同”time”基础上, 根据ID或其他字段进行数据分割

  • 页数统计问题: 避免使用慢的”COUNT”函数, 通过限制页数解决

    • 实例: 某东每页60条数据, 提供100页, 总计6000条数据, 满足用户需求

  • 技术难题可通过业务角度简化解决, 如限制页数和数据量, 提升用户体验与系统性能

执行计划分析

• 原始查询执行计划

EXPLAIN
select
    *
from
    test
ORDER BY
    createdTime
LIMIT 300000,10

• 执行计划分析

  • 全表扫描：type=ALL
  • 未使用索引：key=NULL
  • 扫描行数：rows=10000000

• 优化后查询执行计划

EXPLAIN
select
    *
from
    test
WHERE
  createdTime>=(
    select
            createdTime
        from
            test
        ORDER BY
            createdTime
        limit 30000,1
  )
ORDER BY
    createdTime
LIMIT 10

• 执行计划分析
  • 使用索引：type=range, key=createdTime
  • 扫描行数：rows=300010
  • 子查询优化：Using index

链接

视频链接