MySQL 用 limit 分页为什么会影响性能？

优化方案(避免数据量大时扫描过多的记录):

解决：
   子查询的分页方式或者JOIN分页方式。
   JOIN分页和子查询分页的效率基本在一个等级上，消耗的时间也基本一致。注意排序字段需要加索引
  

-- 传统limit，文件扫描
SELECT * FROM tableName ORDER BY name LIMIT 500000,2;
受影响的行: 0

时间: 5.371s

-- 子查询方式，索引扫描
SELECT * FROM tableName
WHERE id >= (SELECT id FROM tableName ORDER BY name LIMIT 500000 , 1)
LIMIT 2;
受影响的行: 0
时间: 0.274s

-- JOIN分页方式
SELECT *
FROM tableName AS t1
JOIN (SELECT id FROM tableName ORDER BY name desc LIMIT 500000, 1) AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT 2;
受影响的行: 0
时间: 0.278s

        

select * from test where val=4 limit 300000,5 ( id为自增主键，val为非唯一索引 ), 当limit offset rows中的offset很大时，会出现效率问题   

 首先查询到非聚簇索引叶子节点数据(主键值id,val ), 根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。扫描满足条件的300005行，扔掉前面的300000行，返回最后的5行, MySQL耗费了大量随机I/O在查询聚簇索引的数据上

肯定会有人问：既然一开始是利用索引的，为什么不先沿着索引叶子节点查询到最后需要的5个节点，然后再去聚簇索引中查询实际数据。这样只需要5次随机I/O-- 没有这样做 为毛

类似于下面这张图：

Mysql 分页limit带来的性能问题 | 随意博客

MySQL 用 limit 分页为什么会影响性能？