商品表分页 explain

1.以下最基本分页sql，并分析分页指标

 第一页： explain select * from prod_main order by prod_id desc limit 0,20 

 第二页： explain select * from prod_main order by prod_id desc limit 1,20 

 ...... 随着页码越来越大，查询效率越来越低下。

按照主键ID，倒排序，type级别为index

2.随着页码增多，效率对比

a.1000页

b.5000页

c.100000页

 

3.优化sql

 a.利用子查询，首先查出分页页码中，最大数的id,

 b.然后进行范围查询。

sql:

#未优化sql 查询第50000页的数据
select * from prod_main order by prod_id desc limit 49999,20;

#优化的sql
select * from prod_main where prod_id <=
   (select prod_id from prod_main order by prod_id desc limit 49999,1)
order by prod_id desc limit 20;

结果：

 a.未优化

b.优化

4.

BEGIN
	#Routine body goes here...

   if startrow = 0 
       select * from prod_main order by prod_id desc limit startrow,pagesize;
   else 
       select * from prod_main where prod_id <= (select prod_id where prod_main order by prod_id desc limit startrow,1)
          order by prod_id desc limit pagesize ;
   end if;

END;

入参：IN startrow int,IN pagesize int