mysql优化limit分页性能

mysql默认的limit查询在偏移大数据后,性能急剧降低.
为了提高limit性能,一般使用书签查询优化和延迟关联优化.

书签查询优化limit

例子1

SELECT id FROM user LIMIT 10000, 10;
优化后
SELECT id FROM user WHERE id > 10000 LIMIT 10;

例子2

SELECT id FROM product LIMIT 866613, 20;
优化后
SELECT * FROM product WHERE ID >=(SELECT id FROM product LIMIT 866613, 1) limit 20 ;

延迟关联优化limit

例子1

SELECT id FROM product LIMIT 866613, 20;
优化后
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id ;

例子2

 SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type =\'0\' AND end_time >=\'2014-05-29\' ORDER BY id asc LIMIT 149420 ,20;
优化后
SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id ;

例子3(来自《高性能MySQL》)

select * from t_portal_user where create_time > '2012-10:10' and create_time<'2017:10:10' LIMIT 5000,10;
改进后：
SELECT * from t_portal_user INNER JOIN
(select id from t_portal_user where create_time > '2012-10:10' and create_time<'2017:10:10' LIMIT 5000,10)
as a USING(id) ;