以下的文章主要是对MySQL limit查询优化的具体内容的介绍,我们大家都知道MySQL数据库的优化是相当重要的。其他最为常用也是最为需要优化的就是limit。MySQL的limit给分页带来了极大的方便,但数据量一大的时候,limit的性能就急剧下降。
同样是取10条数据
- select
* from yanxue8_visit limit 10000,10 - select
* from yanxue8_visit limit 0,10
就不是一个数量级别的。
网上也很多关于limit的五条优化准则,都是翻译自MySQL手册,虽然正确但不实用。今天发现一篇文章写了些关于limit优化的,很不错。
文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据,明显要好于直接使用limit。这里我具体使用数据分两种情况进行测试。(测试环境win2033+p4双核 (3GHZ) +4G内存MySQLlimit查询)
1、offset比较小的时候
- 1.select
* from yanxue8_visit limit 10,10
多次运行,时间保持在0.0004-0.0005之间
- Select
* From yanxue8_visit Where vid >=( - Select
vid From yanxue8_visit Order By vid limit 10,1 ) limit 10
多次运行,时间保持在0.0005-0.0006之间,主要是0.0006
结论:偏移offset较小的时候,直接使用limit较优。这个显然是子查询的原因。
2、offset大的时候
- select
* from yanxue8_visit limit 10000,10
多次运行,时间保持在0.0187左右
- Select
* From yanxue8_visit Where vid >=( - Select
vid From yanxue8_visit Order By vid limit 10000,1 ) limit 10
多次运行,时间保持在0.0061左右,只有前者的1/3。可以预计offset越大,后者越优。
实验下
- 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 !~- - 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
从结果中可以得知,当偏移1000以上使用子查询法可以有效的提高性能。
2.倒排表优化法
倒排表法类似建立索引,用一张表来维护页数,然后通过高效的连接得到数据
缺点:只适合数据数固定的情况,数据不能删除,维护页表困难
具体请看,http://blog.chinaunix.net/u/29134/showart_1333566.html
3.反向查找优化法
当偏移超过一半记录数的时候,先用排序,这样偏移就反转了
缺点:order by优化比较麻烦,要增加索引,索引影响数据的修改效率,并且要知道总记录数
,偏移大于数据的一半
正向查找: (当前页 - 1) * 页长度
反向查找: 总记录 - 当前页 * 页长度
做下实验,看看性能如何
总记录数:1,628,775
每页记录数: 40
总页数:1,628,775 / 40 = 40720
中间页数:40720 / 2 = 20360
第21000页
正向查找SQL:
时间:1.8696 秒
反向查找sql:
时间:1.8336 秒
第30000页
正向查找SQL:
时间:2.6493 秒
反向查找sql:
时间:1.0035 秒
注意,反向查找的结果是是降序desc的,并且InputDate是记录的插入时间,也可以用主键联合索引,但是不方便。
4.limit限制优化法
把limit偏移量限制低于某个数。。超过这个数等于没数据,我记得alibaba的dba说过他们是这样做的
5.只查索引法
http://willko.iteye.com/blog/670120
总结:limit的优化限制都比较多,所以实际情况用或者不用只能具体情况具体分析了。页数那么后,基本很少人看的。。。