数据库分页

mysql

limit

SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15  即 6,7,8,9,10,11,12,13,14,15 共10条记录
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
SELECT * FROM table LIMIT 5; //检索前 5 个记录行

offset

SELECT * FROM table LIMIT 5,10; 检索记录行 6-15
SELECT * FROM table LIMIT 10 offset 5 即表示检索记录行 6-15 与上面的互换位置

pageIndex,pageSize

(pageIndex-1)*pageSize
pageSize

String originSql,int pageIndex ,int pageSize

StringBuilder sb = new StringBuilder();
sb.append(originSql);
sb.append(" limit ");
sb.append(pageSize);
sb.append(" offset ");
sb.append( (pageIndex-1)*pageSize   );
sb.toString();

分页优化主键优先原则

• 查询100001条id limit 100000,1
• 查询100001条id后的第100条id
  id>=(select id from xxx limit 1000,1) limit 100
• 例子主键子查询

select * from order where id>=(
   select id from orders limit 10000,1
) limit 100

SELECT * FROM articles WHERE  id >=   
(SELECT id FROM articles  WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10

• 例子主键方式

select * from order where id>=1000001 limit 100;

oracle

String originSql,int pageIndex ,int pageSize

StringBuilder sb= new StringBuilder();
sb.append("select * from (select tmp_tb.*,ROWNUM row_id from (");
sb.append(originSql);
sb.append(") tmp_tb where ROWNUM<=");
sb.append(pageIndex*pageSize);
sb.append(") where row_id>");
sb.append((pageIndex-1)*pageSize);
sb.toString();

db2

String originSql,int pageIndex ,int pageSize

StringBuilder sb= new StringBuilder();
sb.append("SELECT * FROM (SELECT TEMP_TAB.*,ROWNUMBER() OVER() AS IDX FROM (");
sb.append(originalSql);
sb.append(" ) AS TEMP_TAB) AS TEMP_TAB_WITH_IDX WHERE TEMP_TAB_WITH_IDX.IDX> ");
sb.append((pageIndex-1)*pageSize);
sb.append(" AND TEMP_TAB_WITH_IDX.IDX<=");
sb.append(pageIndex*pageSize);
sb.toString();