mysql分页查询百万级别数据sql优化

-- 普通分页查询
SELECT * from table  limit 100000,10; -- 结果显示4.73s


-- 优化之后的分页查询 (前提是该ID为主键)
-- 第一种写法
SELECT a.* FROM table a INNER JOIN (SELECT id FROM table LIMIT 100000,10) b ON a.id = b.id; -- 1.127s

--第二种写法
SELECT a.* from table a where id >=(SELECT id from table limit 100000,1) LIMIT 10;

上面两个一个是子查询，一个是关联查询，那么这两种方案的查询性能哪一个更好呢？

• 子查询：把内层查询结果当作外层查询的比较条件，使用 IN( ) 函数、EXISTS 运算符等，示例如下：

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);
执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

• 连接查询（JOIN）连接查询不需要建立临时表，因此其速度比子查询快。另外注意：能过滤先过滤，过滤好了再链接

参考文章：mysql的limit分页，越往后为什么越慢，怎么解决 - 王小森# - 博客园