Mysql SQL优化之 limit offset 很大时性能降

Mysql SQL优化之 limit offset 很大时性能降低(InnoDB引擎)

现象

在 mysql 中,如果select时 offset 很大, 即使相关索引建的挺好, 也会造成慢查询.
如:

    select <一些主键>, 其他字段 
    from a join b on a.id=b.id
    where <筛选条件>
    order by <一些字段>
    limit 1 offset 1000000

先给操作结论

1. 如果可以,优先用 id 比较 last_id 替代offset. 比如列表本来就是按id顺序进行翻页的.
2. 否则,参考下面的形式修改原有带 offset 的语句. 原则是: 先 select 需要的 id(主键), 然后取出需要的其他字段

select 需要的字段 
from
(
    select <a.id 或 b.id> as id
    from a join b on a.id=b.id
    where <筛选条件>
    order by <一些字段>
    limit <limit_param> offset <offset_param>
) c join a on  c.id=a.id
    join b on c.id=b.id
    order by <一些字段>

分析

索引只能找到主键,要取其他字段,还要用主键逐个查表,判断条件并排序,然后抛弃 offset 个, 留下 offset+1 到 offset+limit 个. 时间被花在了用主键逐个查表取别的字段那里了.
如果能在找主键的时候就抛弃不需要的行,然后需要其他字段的时候再按主键取,就能避免逐条按主键取其他字段的 IO 时间, 从而提高性能.

更详细的分析,请参考

https://www.jb51.net/article/141933.htm