Mysql中的分页处理

先来说一下Mysql中limit的语法：

--语法：
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

--举例：

select * from table limit 5; --返回前5行

select * from table limit 0,5; --同上，返回前5行

select * from table limit 5,10; --返回6-15行

 

Mysql中分页主要利用limit能够根据偏移量返回结果的特性：

select ... from ... where ... order by ... desc limit pagesize * (page - 1), pagesize;

--举例：取t_u_coach表中按coachId降序排列后第5页的教练列表，其中每页10个教练
select * from t_u_coach where id > 10000 order by coachId desc limit 40, 10;

在中小数据量的情况下，唯一需要注意的是coachId和id最好已经建立了聚合索引。

 

但是当数据量很大的时候，limit m,n 的性能随着m的增加而急剧下降。比如：

--举例：偏移值过大的时候性能下降
select * from t_u_coach where id > 10000 order by coachId desc limit 40000, 10;

 

此时，通常有两种方法来进行优化：

一，使用子查询的方式来进行分页

select * from t_u_coach where coachId >= (select coachId from t_u_coach where id > 10000 order by coachId desc  limit 40000， 1) limit 10;

 

二，使用join的方式来进行分页

 

select * from t_u_coach as t1 join (select coachId from t_u_coach where id > 10000 order by coachId desc limit 40000, 1) as t2 where t1.coachId >= t2.coachId order by t1.coachId desc limit 10;

 

使用子查询来进行分页优化的时候，主要是因为能在子查询中使用索引。