正在加载...

mysql分页limit 优化

[ 2011/06/27 15:29 | by selboo ]

mysql的分页比较简单,只需要limit offset,length就可以获取数据了,但是当offset和length比较大的时候,mysql明显性能下降

1.子查询优化法
先找出第一条数据,然后大于等于这条数据的id就是要获取的数据
缺点:数据必须是连续的,可以说不能有where条件,where条件会筛选数据,导致数据失去连续性

This is The SQL Code
mysql> SET profiling=1;
Query OK, 0 ROWS affected (0.00 sec)

mysql> SELECT COUNT(*) FROM Member;
+----------+
| COUNT(*) |
+----------+
|   169566 |
+----------+
1 ROW IN SET (0.00 sec)

mysql> pager grep <a name="entrymore"></a>!~-
PAGER SET TO 'grep !~-'

mysql> SELECT * FROM Member LIMIT 10, 100;
100 ROWS IN SET (0.00 sec)

mysql> SELECT * FROM Member WHERE MemberID >= (SELECT MemberID FROM Member LIMIT 10,1) LIMIT 100;
100 ROWS IN SET (0.00 sec)

mysql> SELECT * FROM Member LIMIT 1000, 100;
100 ROWS IN SET (0.01 sec)

mysql> SELECT * FROM Member WHERE MemberID >= (SELECT MemberID FROM Member LIMIT 1000,1) LIMIT 100;
100 ROWS IN SET (0.00 sec)

mysql> SELECT * FROM Member LIMIT 100000, 100;
100 ROWS IN SET (0.10 sec)

mysql> SELECT * FROM Member WHERE MemberID >= (SELECT MemberID FROM Member LIMIT 100000,1) LIMIT 100;
100 ROWS IN SET (0.02 sec)

mysql> nopager
PAGER SET TO stdout


mysql> SHOW profiles\G
*************************** 1. ROW ***************************
Query_ID: 1
Duration: 0.00003300
   Query: SELECT COUNT(*) FROM Member

*************************** 2. ROW ***************************
Query_ID: 2
Duration: 0.00167000
   Query: SELECT * FROM Member LIMIT 10, 100
*************************** 3. ROW ***************************
Query_ID: 3
Duration: 0.00112400
   Query: SELECT * FROM Member WHERE MemberID >= (SELECT MemberID FROM Member LIMIT 10,1) LIMIT 100

*************************** 4. ROW ***************************
Query_ID: 4
Duration: 0.00263200
   Query: SELECT * FROM Member LIMIT 1000, 100
*************************** 5. ROW ***************************
Query_ID: 5
Duration: 0.00134000
   Query: SELECT * FROM Member WHERE MemberID >= (SELECT MemberID FROM Member LIMIT 1000,1) LIMIT 100

*************************** 6. ROW ***************************
Query_ID: 6
Duration: 0.09956700
   Query: SELECT * FROM Member LIMIT 100000, 100
*************************** 7. ROW ***************************
Query_ID: 7
Duration: 0.02447700
   Query: SELECT * FROM Member WHERE MemberID >= (SELECT MemberID FROM Member LIMIT 100000,1) LIMIT 100
Parsed in 0.006 seconds at 369.14 KB/s


从结果中可以得知,当偏移1000以上使用子查询法可以有效的提高性能。

2.倒排表优化法
倒排表法类似建立索引,用一张表来维护页数,然后通过高效的连接得到数据

缺点:只适合数据数固定的情况,数据不能删除,维护页表困难

具体请看,http://blog.chinaunix.net/u/29134/showart_1333566.html

3.反向查找优化法
当偏移超过一半记录数的时候,先用排序,这样偏移就反转了

缺点:order by优化比较麻烦,要增加索引,索引影响数据的修改效率,并且要知道总记录数 ,偏移大于数据的一半

This is The SQL Code
LIMIT偏移算法:
正向查找: (当前页 - 1) * 页长度
反向查找: 总记录 - 当前页 * 页长度
Parsed in 0.002 seconds at 53.16 KB/s


做下实验,看看性能如何

总记录数:1,628,775
每页记录数: 40
总页数:1,628,775 / 40 = 40720
中间页数:40720 / 2 = 20360

第21000页
正向查找SQL:
This is The SQL Code
SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40  
Parsed in 0.002 seconds at 29.37 KB/s

时间:1.8696 秒

反向查找sql:
This is The SQL Code
SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40  
Parsed in 0.002 seconds at 40.31 KB/s

时间:1.8336 秒

第30000页
正向查找SQL:  
This is The SQL Code
SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40  
Parsed in 0.002 seconds at 28.70 KB/s

时间:2.6493 秒

反向查找sql:
This is The SQL Code
SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40  
Parsed in 0.002 seconds at 40.21 KB/s

时间:1.0035 秒

注意,反向查找的结果是是降序desc的,并且InputDate是记录的插入时间,也可以用主键联合索引,但是不方便。

4.limit限制优化法
把limit偏移量限制低于某个数。。超过这个数等于没数据,我记得alibaba的dba说过他们是这样做的

5.只查索引法
http://willko.iteye.com/blog/670120

总结:limit的优化限制都比较多,所以实际情况用或者不用只能具体情况具体分析了。页数那么后,基本很少人看的。。。


一般刚开始学SQL的时候,会这样写

This is The SQL Code
SELECT * FROM TABLE ORDER BY id LIMIT 1000, 10;
Parsed in 0.002 seconds at 23.84 KB/s

但在数据达到百万级的时候,这样写会慢死

This is The SQL Code
SELECT * FROM TABLE ORDER BY id LIMIT 1000000, 10;
Parsed in 0.002 seconds at 25.97 KB/s

也许耗费几十秒

网上很多优化的方法是这样的

This is The SQL Code
SELECT * FROM TABLE WHERE id >= (SELECT id FROM TABLE LIMIT 1000000, 1) LIMIT 10;
Parsed in 0.002 seconds at 40.65 KB/s

是的,速度提升到0.x秒了,看样子还行了
可是,还不是完美的!

以下这句才是完美的!

This is The SQL Code
SELECT * FROM TABLE WHERE id BETWEEN 1000000 AND 1000010;
Parsed in 0.002 seconds at 29.85 KB/s

比上面那句,还要再快5至10倍

另外,如果需要查询 id 不是连续的一段,最佳的方法就是先找出 id ,然后用 in 查询

This is The SQL Code
SELECT * FROM TABLE WHERE id IN(10000, 100000, 1000000...);
Parsed in 0.002 seconds at 28.69 KB/s

最后编辑: selboo 编辑于2012/08/14 15:20
Tags: , , ,
,
bedspreads
2011/10/09 18:30
做个牛人
分页: 1/1 第一页 1 最后页
发表评论
表情
打开HTML
打开UBB
打开表情
隐藏
记住我
昵称   密码   游客无需密码
网址   电邮   [注册]