当前位置:   article > 正文

优化mysql分页查询_mysql分页查询优化

mysql分页查询优化

目录

一、分页查询的问题

二、MySQL的体系结构 ​编辑

三、Limit性能优化


一、分页查询的问题

在我们日常开发中,当使用limit实现分页查询时,当limit的偏移量越大时,sql语句的耗时也越大。

上图偏移量为0时,sql语句耗时在8.9毫秒。

顺便说下偏移量与页码、页大小的关系:

偏移量 = (页码  - 1)  x  页的大小

比如页的大小是每页100行记录, 那么:

第一页的偏移量就是  (1 - 1) x 100 = 0

第二页的偏移量就是  (2 - 1) x 100 = 100

以此类推

我们看下,当增大偏移量查询的耗时 

当偏移量加到1200000时,耗时也增大到796毫秒。

为什么偏移量会对性能有这么大影响呢? 

二、MySQL的体系结构 

各层功能 

连接层:最上层是一些客户端和链接服务,主要完成一些类似于连接处理、授权认证、及相关的安全方 案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。


服务层:第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析 和优化,部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如过程、函数等。


引擎层:存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信。 不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。


存储层:主要是将数据存储在文件系统之上,并完成与存储引擎的交互。 

三、Limit性能优化

上图是MySQL的系统结构图,客户端程序发送sql语句查询请求给服务层,服务层会解析、优化sql语句,之后交给存储引擎,也就是说,存储引擎是真正完成查询的(增加、删除、修改也是由存储引擎负责的)。 

SELECT * FROM testing_put_user_event_type  limit 1200000,100 

当存储引擎查询数据库文件后返回的不是一页的数据(100行), 而是从第1行 到 第 (1200000 + 100)行的数据一起返回给服务层。  服务层收到数据后会抛弃前面的1200000行,只留下最后的100行返回给客户端。

数据库表中行数据、索引都是以文件的形式存储到磁盘(硬盘)上的,而硬盘的速度相对来说要慢很多,存储引擎运行sql语句时,需要访问硬盘查询文件,然后返回数据给服务层。当返回的数据越多时,访问磁盘的次数就越多,就会越耗时。这就是为什么偏移量越大、返回的数据越多,越耗时的原因。

所以说,如果想优化上面的sql时,必须要减少返回的数据。

当表的主键是有序的,或者是自增的,可以使用id限定查询,查询过程是:

当已经查询了某页的数据后,记录下该页最后一行记录的主键id值(本例中是id为主键),查询下一页时就可以使用如下sql: 

 select * from testing_put_user_event_type

where 主键列名 > 当前页最后一行的主键值 limit 0, 100 

比如: 

当前页最后一行的主键值是1563544,查询下一页就可以使用: 

SELECT id,app_type,os_version,keep_type,promotion_id FROM testing_put_user_event_type

where id>1563544  limit 0,100

那么第一页怎么查询呢?

可以选择一个比所有主键值都小的值,比如0或者负数 :

SELECT id,app_type,os_version,keep_type,promotion_id FROM testing_put_user_event_type

where id>0  limit 0,100

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/秋刀鱼在做梦/article/detail/735470
推荐阅读
相关标签
  

闽ICP备14008679号