优化mysql分页查询

Bertha 。 2024-03-17 12:47 52阅读 0赞

##  ##

**目录**

一、分页查询的问题

二、MySQL的体系结构 编辑

三、Limit性能优化

--------------------

##  ##

## 一、分页查询的问题 ##

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

![b4bd0315319d46ce8768c329cc1a0740.jpeg][]

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

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

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

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

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

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

以此类推

我们看下，当增大偏移量查询的耗时 ![72f931c09267420d83ead83c772552f1.jpeg][]

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

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

## 二、MySQL的体系结构 ![b7c4aa85d75a413c90b10e6e936ae525.jpeg][] ##

> 各层功能

> 连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方 案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
> 
>   
> 服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析 和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
> 
>   
> 引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。 不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
> 
>   
> 存储层：主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

![3f590f430b5442e9b0449741c74b7c3e.jpeg][]

## 三、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

比如：

![b66addba56d54a4393bbe89aa4d44c46.jpeg][]

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

> SELECT id,app\_type,os\_version,keep\_type,promotion\_id FROM testing\_put\_user\_event\_type
> 
> where id>1563544 limit 0,100

![cb8c3a654e994031bd0b0e5ce11d9764.jpeg][]

那么第一页怎么查询呢？

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

> SELECT id,app\_type,os\_version,keep\_type,promotion\_id FROM testing\_put\_user\_event\_type
> 
> where id>0 limit 0,100

##  ##

[b4bd0315319d46ce8768c329cc1a0740.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/b2915da77c444e71b3693ea92edac230.png
[72f931c09267420d83ead83c772552f1.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/38b189f59f3e46a59f6211d83649025a.png
[b7c4aa85d75a413c90b10e6e936ae525.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/8dafb8556f4c499fa6c4e8e8e82bcac4.png
[3f590f430b5442e9b0449741c74b7c3e.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/dd516d96aa824ebc8337be0fc544c3c6.png
[b66addba56d54a4393bbe89aa4d44c46.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/47eaebd0ace2454db9e5863e8fc5a8f2.png
[cb8c3a654e994031bd0b0e5ce11d9764.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/6f275712a34c4954b164b122ee990643.png