1,mysql实现分页的方式
limit start,count
如果数据量比较小,10w以下,直接简单的使用上面这张方式就行了。
如果数据量比较大,特别是100w以上的数据量,用上面那种方式在最后的一些分页获取时特别慢,eg:limit 1200000,1000; LIMIT语句的偏移量就会越大,速度也会明显变慢。
改进方式:
每次获取主键id,并对结果集做一个id排序,where条件里加上id>上一次的最大id,limit可以写成固定的limit 0,2000
这样做的好处就是每次都会用到primary key,并且过滤掉之前已获取的数据,每次只需要获取最前面的2000条数据即可,效率大大提升,每次分页的耗时基本都是一样的。
SELECT id,product_id,merchant_id,chinese_name,english_name,
subtitle,is_deleted,is_available,create_time,CODE,
company_id,merchant_series_id,tax_no,TYPE,sale_type,
IF (datediff(NOW(), create_time) > 14,0,1) AS isNew
FROM merchant_product
WHERE is_deleted = 0
AND is_available = 1
AND is_vendible = 1
AND STATUS = 4
AND id > #maxId# order by id
LIMIT 0,2000;
如果后面要join其他表b的话,可以where a_id<=maxId and a_id>=minId
条件加上,然后b表的a_id字段有索引的话,查询速度就很快了。
2,oracle实现分页的方式
因为ROWNUM是对结果集加的一 个伪列,即先查到结果集之后再加上去的一个列 (强调:先要有结果集)。简单的说rownum是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1,而有其他大于1的值。
是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1,第二条为2,依次类推。如 果你用>,>=,=,between……and这些条件,因为从缓冲区或数据文件中得到的第一条记录的rownum为1,则被删除,接着取下 条,可是它的rownum还是1,又被删除,依次类推,便没有了数据。
另外:rowid与rownum虽都被称为伪列,但它们的存在方式是不一样的,rowid可以说是物理存在的,表示记录在表空间中的唯一位置ID,在 DB中唯一。只要记录没被搬动过,rowid是不变的。rowid相对于表来说又像表中的一般列,所以rowid为条件就不会有 rownum那些情况发生。
系统是按照记录插入时的顺序给记录排的号,rowid也是顺序分配的
select rownum ,id,name from student order by name
select rownum ,id,name from (select * from student order by name);
这两条语句得到的rownum是不一样的,第一条语句得到的rownum是记录插入是的顺序,第二条语句得到的rownum是1,2,3,4.。。。
了解了这些之后,你就知道为什么分页查询要用三层select, 第二层要只能要rowNum<了。
标准的写法
SELECT t2.*
FROM
(
SELECT
t1.*, rownum rn
FROM
(
SELECT *
FROM tb_courseinfo
) t1
WHERE rownum <= 150
) t2
WHERE rn > 100
性能最好的写法:(rownum <= 100 是固定的)
SELECT t1.*
FROM
(
SELECT *
FROM tb_courseinfo
WHERE id > #maxId#
ORDER BY id
) t1
WHERE rownum <= 100