超大表数据 开发中常见问题与最佳实践 sql 分页

https://developer.aliyun.com/learning/trainingcamp/mysql/1?spm=a2c6h.21072608.J_3548279640.1.707f59b5EHt8d7

背景：

 扫描大量无效数据后，返回请求数据 分页越来越慢。

 

 

 

开发中常见问题与最佳实践

SQL 执行顺序：

1.FROM, including JOINs

2. WHERE

3. GROUP BY

4. HAVING

5. WINDOW functions

6. SELECT

7. DISTINCT

8. UNION

9. ORDER BY

10 .LIMIT and OFFSET

 

 

开发中常见问题与最佳实践分页问题传统写法select * from sbtest1 order by id limit M,N问题点：扫描大量无效数据后，返回请求数据

 

 

 

 

 

 

 

 

开发中常见问题与最佳实践分页问题

➢select * from sbtest1 where id > #max_id# order by id limit n;

     •适用顺序翻页的场景，每次记录上一页#max_id#带入下一次查询中

➢select * from sbtest1 as ainner join (select id from sbtest1 order by id limit m, n) as b on a.id= b.idorder by a.id;

      •适用只按照id进行分页,无where条件

➢select * from sbtest1 as a inner join (select id from sbtest1where col=xxxx  order by id limit m, n) as bon a.id= b.idorder by a.id;

     •适用于带where条件，同时按照id顺序分页•此时，需要在where条件上创建二级索引