MySQL Limit性能优化及分页数据性能优化详解

今天小编就为大家分享一篇关于MySQL Limit性能优化及分页数据性能优化详解,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧

MySQL Limit可以分段查询数据库数据,主要应用在分页上。虽然现在写的网站数据都是千条级别,一些小的的优化起的作用不大,但是开发就要做到极致,追求完美性能。下面记录一些limit性能优化方法。

Limit语法:

?

1

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。

支持 limit # offset # 语法:

?

1

2

3

4

5

mysql> SELECT * FROM table LIMIT 5,10; //检索记录行6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为-1

mysql> SELECT * FROM table LIMIT 95,-1; //检索记录行96-last

//如果只给定一个参数,它表示返回最大的记录行数目,换句话说,LIMIT n 等价于 LIMIT 0,n

mysql> SELECT * FROM table LIMIT 5; //检索前5个记录行

limit n,m 表示从第n条记录开始选择m条记录。而大多数开发人员喜欢使用这类语句来解决Web中经典的分页问题。对于小规模的数据,这并不会有太大的问题。对于论坛这类可能具有非常大规模数据的应用来说,limit n,m 的效率是十分低的。因为每次都需要对数据进行选取。如果只是选取前5条记录,则非常轻松和容易;但是对100万条记录,选取从80万行记录开始的5条记录,则还需要扫描记录到这个位置。

也就是说limit 10000,20意味着扫描满足条件的10020行,扔掉前面的10000行,返回最后的20行;问题就在这里,如果是limit 100000,100,需要扫描100100行,在一个高并发的应用里,每次查询需要扫描超过10W行,性能肯定大打折扣。

不同数据量读取数据效率比较:

1.offset比较小的时候:

?

1

select * from table limit 5,10

多次运行,时间保持在0.0004-0.0005之间

?

1

2

3

Select * From table Where id >=(

Select id From table Order By id limit 10,1

) limit 10

多次运行,时间保持在0.0005-0.0006之间。所有,在offset较小的时候,直接使用limit效率会高点!

2.offset数据比较大的时候:

?

1

select * from table limit 10000,10

多次运行,时间保持在0.0187秒左右。

?

1

2

3

Select * From table Where id >=(

Select id From table Order By id limit 10000,1

) limit 10

多次运行,时间保持在0.061秒左右,是前者的1/3左右。所以,offset较大时,使用后者效率会搞!这是用了id做索引的结果。

如果用id作为数据表的主键:

?

1

select id from table limit 10000,10

查询花费时间大概在0.04秒,这是因为用id主键作为索引的结果。

Limit性能优化:

?

1

2

3

4

5

6

7

8

9

10

Select * From cyclopedia Where ID>=(

Select Max(ID) From (

Select ID From cyclopedia Order By ID limit 90001

) As tmp

) limit 100;

Select * From cyclopedia Where ID>=(

Select Max(ID) From (

Select ID From cyclopedia Order By ID limit 90000,1

) As tmp

) limit 100;

同样是取90000条后100条记录,第2句会快点。因为第1句是先取前90001条记录,取其中最大一个ID值作为起始标识,然后利用它快速定位下100条数据;而第2句是只取了最后一条记录,然后取ID值作为起始标识定位100条记录。第2句可简写成:

?

1

2

3

4

5

Select * From cyclopedia Where ID>=(

Select ID From (

Select ID From cyclopedia Order By ID limit 90000,1

) As tmp

) limit 100;

省去Max运算,一般ID都是递增。

分页数据性能优化:

1、对于数据量较大数据表,可以建立主键和索引字段建立索引表,通过索引表查询相应的主键,在通过主键查询数据量的数据表;

2、如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where 放第一位,limit用到的主键放第2位,而且只能select 主键!这样能提高读取速度

3、利用in:先通过where条件取得相应的主键值,然后利用主键值查询相应的字段值。

使用游标(cursor)分页:

为让mysql达到最佳查询性能,我将分页查询改为cursor查询方式:

?

1

select * from table where id > last_id limit 20 order by reply_id ASC;

上面的last_id为本页最后一条记录的id,这样就能实现“下一页”的查询了,同理也可以实现“上一页”的查询。

cursor方式分页只适合用于有顺序的数据且不支持跳页,我们可以建立自增ID或向数据表中增加有序字段:对于数据量大的项目,跳页的作用也不大,完全可以使用筛选条件达到查找的目的。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对钦钦技术栈的支持。如果你想了解更多相关内容请查看下面相关链接

原文链接:http://www.codetc.com/article-330-1.html

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/17154/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年8月31日 下午7:33
下一篇 2022年8月31日 下午7:34
软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!