mysql优化limit查询语句的5个方法

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

1.子查询优化法

先找出第一条数据,然后大于等于这条数据的id就是要获取的数据
缺点:数据必须是连续的,可以说不能有where条件,where条件会筛选数据,导致数据失去连续性,具体方法请看下面的查询实例:

代码如下:

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.倒排表优化法

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

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

倒排表介绍:(而倒排索引具称是搜索引擎的算法基石)

倒排表是指存放在内存中的能够追加倒排记录的倒排索引。倒排表是迷你的倒排索引。

临时倒排文件是指存放在磁盘中,以文件的形式存储的不能够追加倒排记录的倒排索引。临时倒排文件是中等规模的倒排索引。

最终倒排文件是指由存放在磁盘中,以文件的形式存储的临时倒排文件归并得到的倒排索引。最终倒排文件是较大规模的倒排索引。

倒排索引作为抽象概念,而倒排表、临时倒排文件、最终倒排文件是倒排索引的三种不同的表现形式。

3.反向查找优化法

当偏移超过一半记录数的时候,先用排序,这样偏移就反转了

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

limit偏移算法:
正向查找: (当前页 - 1) * 页长度
反向查找: 总记录 - 当前页 * 页长度

做下实验,看看性能如何

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

第21000页
正向查找SQL:

代码如下:

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40

时间:1.8696 秒

反向查找sql:

代码如下:

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40

时间:1.8336 秒

第30000页
正向查找SQL:

代码如下:

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40

时间:2.6493 秒

反向查找sql:

代码如下:

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40

时间:1.0035 秒

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

4.limit限制优化法

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

5.只查索引法

MySQL的limit工作原理就是先读取n条记录,然后抛弃前n条,读m条想要的,所以n越大,性能会越差。
优化前SQL:

代码如下:

SELECT * FROM member ORDER BY last_active LIMIT 50,5

优化后SQL:

代码如下:

SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)

区别在于,优化前的SQL需要更多I/O浪费,因为先读索引,再读数据,然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了,然后通过member_id读取需要的列。

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

(0)

相关推荐

  • MySQL 大数据量快速插入方法和语句优化分享

    锁定也将降低多连接测试的整体时间,尽管因为它们等候锁定最大等待时间将上升.例如: 复制代码 代码如下: Connection 1 does 1000 inserts Connections 2, 3, and 4 do 1 insert Connection 5 does 1000 inserts 如果不使用锁定,2.3和4将在1和5前完成.如果使用锁定,2.3和4将可能不在1或5前完成,但是整体时间应该快大约40%. INSERT.UPDATE和DELETE操作在MySQL中是很快的,通过为在

  • mysql数据库优化需要遵守的原则

    这是我在网上看到的一篇不错的mysql数据库优化文章,内容说的很全! 1.使用索引来更快地遍历表 缺省情况下建立的索引是非群集索引,但有时它并不是最佳的.在非群集索引下,数据在物理上随机存放在数据页上.合理的索引设计要建立在对各种查询的分析和预测上.一般来说: a.有大量重复值.且经常有范围查询( > ,< ,> =,< =)和order by.group by发生的列,可考虑建立群集索引; b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引; c.组合索引要尽量使关键查询

  • MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍

    不知道有没有人碰到过这样恶心的问题:两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低. 情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id. 下面给出建表语句: 复制代码 代码如下: create table t_team ( id int primary key, tname varchar(100) ); create table t_people (

  • mysql数据库sql优化原则(经验总结)

    一.前提 这里的原则只是针对MySQL数据库,其他的数据库某些是殊途同归,某些还是存在差异.我总结的也是MySQL普遍的规则,对于某些特殊情况得特殊对待.在构造SQL语句的时候要养成良好的习惯. 二.原则总结 原则1.仅列出需要查询的字段,这对速度不会明显的影响,主要是考虑节省应用程序服务器的内存. 原来语句: select * from admin 优化为: select admin_id,admin_name,admin_password from admin 原则2.尽量避免在列上做运算,

  • MySQL Order by 语句用法与优化详解

    MySQL Order By keyword是用来给记录中的数据进行分类的.MySQL Order By Keyword根据关键词分类ORDER BY keyword是用来给记录中的数据进行分类的. 复制代码 代码如下: SELECT column_name(s) FROM table_name ORDER BY column_name 例子 SQL创建代码: 复制代码 代码如下: CREATE TABLE IF NOT EXISTS mysql_order_by_test (  uid int

  • Mysql中基本语句优化的十个原则小结

    前言 在数据库的应用中,程序员们通过不断的实践总结了很多经验,这些经验是一些普遍的适用规则,每一个程序员都应该了解并记住它们,在构造sql时,养成良好的习惯,下面话不多说,来看看详细的介绍: mysql基本语句优化原则 一.尽量避免在列上运算,这样会导致索引失效 select * from t where YEAR(d) >= 2011; 优化为 select * from t where d >='2011-0101' 二.使用 JOIN 时,应该用小结果集驱动大结果集,同时把复杂的 JOI

  • MySQL中索引优化distinct语句及distinct的多字段操作

    MySQL通常使用GROUPBY(本质上是排序动作)完成DISTINCT操作,如果DISTINCT操作和ORDERBY操作组合使用,通常会用到临时表.这样会影响性能. 在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个不能利用索引完成DISTINCT操作的实例. 实例1 使用索引优化DISTINCT操作 create table m11 (a int, b int, c int, d int, primary key(a)) engine=INNODB;

  • MySQL SQL语句优化的10条建议

    1.将经常要用到的字段(比如经常要用这些字段来排序,或者用来做搜索),则最好将这些字段设为索引.2.字段的种类尽可能用int 或者tinyint类型.另外字段尽可能用NOT NULL.3.当然无可避免某些字段会用到text ,varchar等字符类型,最好将text字段的单独出另外一个表出来(用主键关联好)4.字段的类型,以及长度,是一个很考究开发者优化功力的一个方面.如果表数据有一定的量了,不妨用PROCEDURE ANALYSE()命令来取得字段的优化建议!(在phpmyadmin里可以在查

  • MYSQL WHERE语句优化

    但请注意,下面的优化并不是完全的.MYSQL实施了许多优化,但我没时间全部测试. MySQL的一些优化列在下面: 删除不必要的括号: ((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d) 常数调入: (a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5 删除常数条件: (B>=5 AND B=5) OR (B=

  • 浅谈MySQL中优化sql语句查询常用的30种方法

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描. 3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from

随机推荐

其他