实战！如何解决 MySQL 深分页问题

已于 2024-09-30 11:41:13 修改
阅读量739 收藏 11
点赞数 16
文章标签： mysql 数据库
于 2024-09-27 19:17:37 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Chat2DB/article/details/142600686
版权

前言

本文旨在深入分析MySQL深分页问题的原因、影响及解决方案，并详细分析底层原理。文章将分为以下几个部分：

  1. 深分页问题的背景和影响
  2. MySQL索引结构和查询执行流程
  3. 深分页性能下降的原因
  4. 优化策略及其底层原理
  5. 实战案例分析
  6. 总结与建议

第一部分：深分页问题的背景和影响

什么是深分页？

MySQL 作为最受欢迎的开源关系数据库之一，被广泛用于各种规模的应用程序中。随着数据量的不断增长，高效地处理大量数据成为数据库管理的重要挑战之一。

分页是一种常见的数据检索技术，它允许用户在大量数据中浏览和检索信息，而不必一次性加载所有数据。这对于提高用户体验和减少服务器负载至关重要。然而，当涉及到“深分页”时，即查询大量数据后的页面时，MySQL 的性能可能会显著下降。

深分页的影响

深分页问题对应用程序的性能和用户体验有以下几个方面的负面影响：

  • 响应时间增加：随着分页深度的增加，查询所需的时间也会增加，导致用户体验下降。
  • 服务器资源消耗：深分页查询会消耗更多的CPU和内存资源，可能导致服务器性能瓶颈。
  • 锁竞争和数据不一致：在并发环境下，长时间的查询可能导致锁竞争和数据不一致问题。

实际场景中的问题

在实际应用中，深分页问题可能出现在以下场景：

  • 大型电子商务网站：用户在浏览商品列表时，可能会跳转到较深的页面。
  • 社交媒体平台：用户查看时间线或评论时，可能会加载较旧的内容。
  • 数据分析报告：生成包含大量数据的报告时，可能需要处理深分页查询。

第二部分：MySQL 索引结构和查询执行流程

MySQL 索引概述

MySQL 使用多种类型的索引来提高查询性能，其中最常见的是 B+ 树索引。了解这些索引的结构对于理解深分页问题至关重要。

B+树索引的特点：

  • 节点存储：B+树是一种自平衡的树结构，其中每个节点可以有多个子节点。非叶子节点存储的是指向子节点的指针和分隔值，而叶子节点存储的是实际的数据记录或记录的指针。
  • 顺序访问：叶子节点中的数据是按照索引列的顺序存储的，这使得范围查询非常高效。
  • 聚簇索引和非聚簇索引：聚簇索引（主键索引）的叶子节点直接存储行数据，而非聚簇索引（二级索引）的叶子节点存储的是主键值。

查询执行流程

当一个查询被执行时，MySQL 的查询优化器会决定使用哪种索引，并生成一个查询执行计划。以下是典型的查询执行流程：

步骤 1：查询解析

  • MySQL 解析查询语句，确定要执行的操作和涉及的表。

步骤 2：查询优化

  • 查询优化器分析不同的执行计划，选择成本最低的计划。成本是基于估计的行数和索引的使用情况计算的。

步骤 3：索引扫描

  • 如果查询涉及索引，MySQL 会从索引的根节点开始向下扫描，直到找到满足条件的叶子节点。

步骤 4：回表操作

  • 对于非聚簇索引，找到叶子节点后，MySQL 需要使用主键值回到聚簇索引中检索完整的行数据。这个过程称为“回表”。

步骤 5：结果集构建

  • MySQL 根据查询条件构建结果集，如果使用了LIMIT语句，它会在构建结果集的过程中跳过不满足条件的行。

深分页查询的问题

在深分页查询中，LIMIT语句的offset值很大，这意味着MySQL需要扫描大量的索引节点和行数据，然后丢弃大部分结果。这个过程不仅效率低下，而且随着offset值的增加，性能下降会更加明显。原因如下：

  • 索引扫描开销：MySQL 需要扫描更多的索引节点来定位到offset对应的行。
  • 回表操作开销：对于非聚簇索引，每次找到满足条件的索引记录都需要执行一次回表操作，这在大offset值时尤其昂贵。
  • 结果集构建开销：即使已经找到了所需的数据，MySQL 仍然需要处理和丢弃之前的offset行。

案例分析

假设我们有一个用户表users，包含数百万条记录，我们需要查询第 100001 到第 100010 条记录。以下是一个简单的深分页查询：

SELECT * FROM users ORDER BY id LIMIT 100000, 10;

在这个查询中，MySQL 需要执行以下操作：

  • 扫描 users 表的索引（假设是聚簇索引）来找到 ID 为 100001 的记录。
  • 扫描并丢弃前 100000 条记录。
  • 返回第 100001 到第 100010 条记录。

这个过程在数据量大时非常低效，尤其是当索引不是聚簇索引时，每个匹配的索引记录都需要执行一次回表操作。

第三部分：深分页性能下降的原因

1. 索引扫描的局限性

在深分页查询中，性能下降的主要原因之一是索引扫描的局限性。以下是几个关键点：

全索引扫描

当LIMIT语句的offset值很大时，MySQL 可能需要执行全索引扫描来找到满足条件的记录。这意味着从索引的根节点开始，一直扫描到叶子节点，无论这些节点是否包含目标数据。

索引跳跃性

即使是索引扫描，MySQL 也无法直接跳转到特定的offset位置。它必须从索引的开始位置顺序扫描，直到达到所需的位置。这种顺序扫描的过程是耗时的。

回表开销

对于非聚簇索引，找到满足条件的索引记录后，MySQL 需要执行回表操作来获取完整的行数据。在深分页查询中，由于offset值大，这会导致大量的回表操作，从而增加 I/O 开销。

2. 数据访问模式

深分页查询通常涉及以下数据访问模式，这些模式会导致性能问题：

随机I/O

由于索引扫描通常涉及随机 I/O，这比顺序 I/O 要慢得多。尤其是在机械硬盘上，随机I/O的延迟会显著影响查询性能。

缓存效率低下

深分页查询往往不会受益于 MySQL 的查询缓存，因为查询缓存是基于查询字符串的精确匹配。此外，由于数据量较大，缓存的数据可能很快被淘汰。

3. 锁和事务的影响

在并发环境下，深分页查询可能会引起以下问题：

长事务和锁竞争

深分页查询可能需要较长的时间来执行，这会增加事务的持续时间。长时间的事务可能会导致锁竞争，影响其他并发操作的性能。

死锁风险

在复杂的查询操作中，深分页查询可能会增加死锁的风险，尤其是在涉及多个表和索引的情况下。

实例分析

以之前的用户表users为例，假设我们使用的是非聚簇索引来执行深分页查询。以下是一个具体的性能问题分析：

SELECT * FROM users WHERE username LIKE 'A%' ORDER BY id LIMIT 100000, 10;

在这个查询中，MySQL 首先会在username的索引上找到所有以 ’A’ 开头的记录，然后对这些记录进行排序，并执行回表操作来获取完整的用户信息。当offset值很大时，这个过程会变得非常低效，因为：

  • MySQL 需要扫描大量的索引记录。
  • 对于每个索引记录，MySQL 都需要执行一次回表操作。
  • 排序操作本身也会消耗大量的 CPU 资源。

小结

深分页性能下降的原因是多方面的，包括索引扫描的局限性、数据访问模式、锁和事务的影响等。这些因素共同作用，导致查询效率低下，尤其是在处理大量数据时。

第四部分：优化策略及其底层原理

1. 子查询优化策略

子查询优化策略的核心思想是减少回表操作。通过在子查询中找到满足条件的起始ID，然后在主查询中直接从该ID开始检索数据。

底层原理：

  • 子查询在二级索引上执行，快速定位到满足条件的起始点。
  • 主查询使用该起始点在主键索引上直接检索数据，避免了从二级索引到主键索引的多次回表。

示例：

SELECT * FROM users WHERE id = (SELECT id FROM users WHERE username LIKE 'A%' ORDER BY id LIMIT 100000, 1) LIMIT 10;

在这个例子中，子查询首先找到ID大于等于某个值的记录，主查询则从这个ID开始检索，减少了不必要的回表操作。

2. INNER JOIN 延迟关联策略

延迟关联策略通过先获取满足条件的ID集合，然后与原表进行JOIN操作来获取完整数据。

底层原理：

  • 通过在二级索引上快速找到满足条件的ID集合。
  • 使用INNER JOIN在主键索引上检索这些ID对应的数据，减少了回表次数。

示例：

SELECT u.* FROM users u INNER JOIN (SELECT id FROM users WHERE username LIKE 'A%' ORDER BY id LIMIT 100000, 10) AS sub ON u.id = sub.id;

在这个例子中，子查询生成的临时表sub包含了需要检索的 ID 集合，然后通过 INNER JOIN 与users表连接，直接访问主键索引。

3. 标签记录法策略

标签记录法通过记录上一次查询的最后一个 ID，下次查询从该 ID 开始。

底层原理：

  • 利用有序索引的特性，从上一次查询的最后一个ID开始，避免从头扫描。
  • 适用于有连续或可排序的字段，如自增主键或时间戳。

示例：

SELECT * FROM users WHERE id > last_id ORDER BY id LIMIT 10;

这里的last_id是上一次查询的最后一个 ID，通过这种方式，可以直接跳过之前已经查询过的数据。

4. 使用BETWEEN…AND…策略

策略描述： 使用BETWEEN…AND…来代替LIMIT，直接指定查询的范围。

底层原理：

  • BETWEEN…AND…允许 MySQL 直接定位到查询的起始和结束点。
  • 减少了扫描的行数，提高了查询效率。

示例：

SELECT * FROM users WHERE id BETWEEN start_id AND end_id;

在这个例子中，start_id和end_id是预先计算好的ID范围，MySQL可以直接在这个范围内检索数据。

小结

这些优化策略的共同目标是减少不必要的索引扫描和回表操作，从而提高查询效率。每种策略都有其适用的场景和限制，因此在实际应用中，需要根据具体情况进行选择和调整。

第五部分：实战案例分析

假设我们有一个大型电子商务平台，其中有一个orders表，用于存储订单信息。这个表包含数百万条记录，并且随着业务的发展，数据量持续增长。我们经常需要查询特定时间范围内的订单，并进行分页显示。

原始查询问题

以下是一个常见的深分页查询，用于获取特定日期范围内的订单：

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' ORDER BY order_id LIMIT 100000, 10;

这个查询的问题在于，随着LIMIT的offset值增加，查询性能会显著下降。这是因为 MySQL 需要扫描大量的行来找到满足条件的记录。

优化策略应用

以下是针对上述查询的优化策略应用：

1. 子查询优化

SELECT * FROM orders WHERE order_id = (SELECT order_id FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' ORDER BY order_id LIMIT 100000, 1) LIMIT 10;

在这个优化中，子查询首先找到起始的order_id，然后主查询从这个order_id开始检索，减少了回表操作。

2. INNER JOIN 延迟关联

SELECT o.* FROM orders o INNER JOIN (SELECT order_id FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' ORDER BY order_id LIMIT 100000, 10) AS sub ON o.order_id = sub.order_id;

这里，子查询创建了一个包含所需order_id的临时表，然后通过INNER JOIN与orders表连接，直接访问主键索引。

3. 标签记录法

假设我们已经知道上一次查询的最后一个order_id是200000，我们可以使用以下查询：

SELECT * FROM orders WHERE order_id > 200000 AND order_date BETWEEN '2023-01-01' AND '2023-01-31' ORDER BY order_id LIMIT 10;

这种方法允许我们直接从上一次查询的最后一个order_id开始，避免了从头扫描。

4. 使用BETWEEN…AND…

如果我们知道查询的 ID 范围，可以直接使用：

SELECT * FROM orders WHERE order_id BETWEEN 100001 AND 100010 AND order_date BETWEEN '2023-01-01' AND '2023-01-31' ORDER BY order_id;

这个查询直接指定了order_id的范围，减少了扫描的行数。

优化效果

通过应用上述优化策略，我们可以显著提高查询性能。以下是一些可能的优化效果：

  • 减少查询时间：通过减少回表操作和索引扫描，查询时间可以大幅减少。
  • 降低服务器负载：减少不必要的I/O操作和CPU计算，降低服务器负载。
  • 提升用户体验：快速响应用户的查询请求，提升用户体验。

小结

通过实战案例分析，我们可以看到深分页问题的优化不仅仅是技术上的调整，更是一个持续的过程，需要根据数据和业务的变化进行不断的优化和调整。

第六部分：总结与建议

最后，如果大家遇到类似的数据库问题，可以试试 Chat2DB。这是一个开源且免费的数据库客户端工具，你遇到任何数据库问题，都可以用自然语言向它提问，它会为你提供最佳的解决方案。同样的问题我们看看 Chat2DB 是如何解决的吧。

在这里插入图片描述

本文从深分页问题的背景和影响出发，深入分析了MySQL索引结构和查询执行流程，探讨了深分页性能下降的原因，并提出了几种优化策略。通过实战案例分析，我们展示了这些策略在实际应用中的效果。

通过本文的讨论，我们希望读者能够对MySQL深分页问题有一个全面的认识，并能够在实际工作中有效地进行优化。

Chat2DB 文档：https://docs.chat2db.ai/zh-CN/docs/start-guide/getting-started

Chat2DB 官网：https://chat2db.ai/zh-CN

Chat2DB GitHub：https://github.com/codePhiliaX/Chat2DB

Chat2DB
关注
〖Python 数据库开发实战 - MySQL篇⑭〗- 数据表的基本查询与数据分页
易编橙 · 终身成长社群，相遇已是上上签！
07-23 4万+
数据库为我们提供了 SQL 语言，该章节学习如何通过特定的条件在数据表简单的单表查询。之所以说是简单查询，是因为查询语句里面只包括 "SELECT" 和 "FROM" 语句，"FROM" 语句主要一般是确定数据的来源，从那个数据表查询数据。"SELECT" 语句是用来提取字段的数据，在结果集里就可以看到想要查询的字段内容了。这样的SQL查询功能比较简单，所以就被称作是 "简单查询"。...............
MySQL深分页，limit 100000,10 优化
qq_44700578的博客
06-05 1612
MySQL深分页，limit 100000,10 优化
实战！聊聊如何解决MySQL深分页问题
黑帽子技术的博客
09-26 588
前言 ...
MySQL深度分页问题深度解析与解决方案
最新发布
道长的博客
07-24 1545
在处理包含数百万条记录的大型数据表时，使用MySQL的`LIMIT`进行分页查询是一种常见的做法。然而，当请求的数据位于结果集的深层时，即所谓的“深度分页”，查询性能会急剧下降。本文将深入探讨深度分页问题的原因，并提供几种有效的解决方案。
mysql深度分页的问题
liuying1802028915的博客
06-01 733
今天在跟美团面试官面试的时候，面试官问了一个问题是 深度分页 什么是深度分页 假如有一张表有1千万条数据 id是主键
MySQL__深度分页问题
weixin_69252724的博客
04-27 725
不过，当查询的结果集占表的总行数的很大一部分时，可能就不会走索引了，自动转换为全表扫描。在我一次测试中, 100万条数据, 一次偶然的数据展示中, 发现响应数据特别慢, 从前端到后端的一个问题检查上, 最终我是将问题锁定在了一个SQL语句上面. 也就是导致。在复杂分页场景，往往需要通过 过滤条件，筛选到符合条件的 ID，此时的 ID 是离散且不连续的。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询（回表），减少了 IO 操作，提升了查询效率。
MySql深分页问题详解
靖节先生的博客
04-24 7094
MySql深分页问题详解1. 问题描述2. 问题分析3. 环境准备3.1 创建两个表3.2 创建两个函数3.3 编写存储过程3.4 编写存储过程3.5 创建索引3.6 验证测试4. 解决方案4.1 使用索引覆盖+子查询优化4.2 起始位置重定义4.3 降级策略5. 梳理总结 1. 问题描述 日常开发中经常会涉及到数据查询分页的问题，一般情况下都是根据前端传入页数与页码通过mysql的limit方式实现分页，对于数据量较小的情况下没有问题，但是如果数据量很大，深分页可能导致查询效率低下，接口超时的情况。 2.
【sql效率优化】优雅地解决mysql深分页问题？
m0_71777195的博客
07-12 790
日常需求开发过程中，相信大家对于limit一定不会陌生，但是使用limit时，当偏移量（offset）非常大时，会发现查询效率越来越慢。一开始limit 2000时，可能200ms,就能查询出需要的到数据，但是当limit 4000 offset 100000时，会发现它的查询效率已经需要1S左右，那要是更大的时候呢，只会越来越慢。本文将会讨论当mysql表大数据量的情况，如何优化深分页问题，并附上最近的优化慢sql问题的案例伪代码。先看看表结构（随便举了个例子，表结构不全，无用字段就不进行展示了） 假
如何解决MySQL深分页问题？
m0_49106528的博客
04-03 600
mysql深分页
MySQL 深度分页
We choose to go to the moon in this decade and do the other things, not because they are easy, but beacase they are hard.
01-18 3380
MySQL 深度分页
查询效率提升10倍！3种优化方案，帮你解决MySQL深分页问题
m0_71777195的博客
07-04 1054
开发经常遇到分页查询的需求，但是当翻页过多的时候，就会产生深分页，导致查询效率急剧下降。有没有什么办法，能解决深分页的问题呢？本文总结了三种优化方案，查询效率直接提升10倍，一起学习一下。先创建一张用户表，只在create_time字段上加索引： 然后往用户表中插入100万条测试数据，这里可以使用存储过程： 2. 验证深分页问题 每页10条，当我们查询第一页的时候，速度很快： 在不到0.01秒内直接返回了，所以没显示出执行时间。当我们翻到第10000页的时候，查询效率急剧下降： 执行时间变
MySQL深度分页
Hello World
05-08 3093
深度分页问题的本质是在 MySQL 数据库中，通过 LIMIT 和 OFFSET 关键字进行分页时，MySQL 需要在每次查询时扫描整张表，直到找到当前页的数据。这种查询方式需要进行大量的磁盘 I/O 和内存操作，导致查询效率非常低下。当我们每次查询的记录数很小，但是查询次数很多时，就会产生大量的 I/O 操作，严重影响查询效率。例如有个sql假设假如 LIMIT 和 OFFSET 关键字同时进行使用的话，limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。
mysql分页页码越大，速度越慢（深度分页问题）
weixin_42157627的博客
07-20 977
深度分页问题的本质是在 MySQL 数据库中，通过 LIMIT 和 OFFSET 关键字进行分页时，MySQL 需要在每次查询时扫描整张表，直到找到当前页的数据。当我们每次查询的记录数很小，但是查询次数很多时，就会产生大量的 I/O 操作，严重影响查询效率。转移到主键索引树查询的话，查询条件得改为主键id了，之前SQL的update_time这些条件就抽到子查询。因为以上的SQL，回表了100010次，实际上，我们只需要10条数据，也就是我们只需要10次回表其实就够了。这样就导致查询性能的下降。
MySQL 深度分页问题实际验证
Java搜索工程技术栈
02-26 187
一，前言 首先说明一下MySQL的版本： 表结构： id为自增主键，val为非唯一索引。 灌入大量数据，共500万： 我们知道，当limit offset rows中的offset很大时，会出现效率问题： 为了达到相同的目的，我们一般会改写成如下语句： 时间相差很明显。 为什么会出现上面的结果？我们看一下select * from test where val=4 limit 300000,5;的查询过程： 查询到索引叶子节点数据。 根据叶子节点上的主键值去聚簇索引...
深度分页问题解决（mysql）
qq_42072311的博客
06-27 4852
没有条件的分页查询 可以增加一个数字类型的字段，并加索引。使用这个字段的范围查询来替代分页查询 有条件的分页查询 可以通过嵌套查询，在里层实现分页功能，但是结果集只返回id。在外层再通过id来取出全量数据。 select a.* from ( select id from ca_credit_assign_log limit 1000000, 10) x, ca_credit_assign...
解决mysql深度分页问题
Good Luck
08-26 8270
数据：单表数据25万条。深度分页优化。
MySQL深分页问题原理与三种解决方案
JAVA前线技术专栏
05-03 1969
本文第一介绍深分页问题表现和原因。第二介绍深分页问题三种解决方法，方案一是从业务维度优化，方案二是使用覆盖索引进行优化，方案三是使用Id分页。第三展示MyBatis相关代码
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值