首页 > 数据库 >深入优化MySQL深度分页：从第10000页出发，Java模拟高效分页技巧

深入优化MySQL深度分页：从第10000页出发，Java模拟高效分页技巧

时间：2024-10-20 13:20:26浏览次数：9  
标签：articles 10000 分页 created 查询 MySQL 主键 id

在深度分页（如 LIMIT 99990, 10）中，SQL 的优化方式主要是为了避免 MySQL 在执行时需要扫描大量的无用数据，从而提高查询效率。以下是几种常见的 SQL 层面的优化方法：

1. 使用覆盖索引优化

覆盖索引是一种索引优化技术，即查询只通过索引就可以获得所需的数据，而不需要访问实际的数据行。在分页查询中，特别是在深度分页的场景下，覆盖索引可以显著减少扫描的记录数。

优化方式：
假设我们有一张表 articles，其中有字段 id（主键）、title 和 created_at（创建时间），我们希望分页查询按 created_at 排序。

-- 创建索引
CREATE INDEX idx_created_at ON articles (created_at);

-- 使用覆盖索引进行分页
SELECT id, title FROM articles
WHERE created_at >= (SELECT created_at FROM articles ORDER BY created_at LIMIT 99990, 1)
ORDER BY created_at
LIMIT 10;

解释：

  • 首先通过子查询获取 LIMIT 99990, 1 对应的那条记录的 created_at 值。
  • 然后使用这个 created_at 值进行主查询，通过索引快速跳到目标位置，并从该位置开始返回后续的记录。

优势：

  • 子查询只需要查找一条记录，减少了扫描大量无用数据的过程。
  • 主查询使用索引直接定位到目标位置，不必扫描前面的数据。

2. 延迟关联（Deferred Join）

延迟关联是一种分步骤的查询优化技术，它可以通过先查找分页所需的主键或索引，然后再根据这些主键查找实际数据。适用于涉及大量关联查询时的分页优化。

示例：
假设我们有一个包含大量文章的表 articles，每篇文章有一个 id 和对应的其他字段。查询第10000页数据时，我们可以先通过索引查找 id，然后再关联查询其他数据。

-- 第一步：先查出分页需要的主键（id）
SELECT id FROM articles
ORDER BY created_at
LIMIT 99990, 10;

-- 第二步：根据这些主键再查完整的数据
SELECT id, title, content FROM articles
WHERE id IN (
  SELECT id FROM articles ORDER BY created_at LIMIT 99990, 10
);

解释：

  • 第一步只查找需要的 id，通过索引跳过大量无用记录。
  • 第二步根据这些 id 再去查询完整的记录。

优势：

  • 通过减少对大表的扫描，延迟关联方式可以在深度分页时减少查询的复杂度和成本。

3. 利用自增主键优化分页

如果你的表有自增主键（如 id），并且分页时可以通过主键来限制查询范围，那么可以通过自增主键进行分页优化。

-- 假设分页查询是按自增主键排序
SELECT id, title FROM articles
WHERE id > (SELECT id FROM articles ORDER BY id LIMIT 99990, 1)
ORDER BY id
LIMIT 10;

解释：

  • 通过子查询获取第 99990 条记录的 id，然后在主查询中直接通过 id > 进行过滤，从而减少前面的无效数据扫描。

优势：

  • 自增主键可以快速定位到需要的记录，而不需要扫描前面的所有数据行。

4. 基于条件的分页优化

如果表中有明显的分区或可以划分查询的字段（如 created_at 或某个分类字段），可以通过条件将查询限制在更小的数据集范围内。

示例：
假设表中按时间戳排序查询，并且 created_at 是一个连续增长的时间字段，那么可以通过时间范围条件来优化分页。

-- 使用时间戳来优化分页
SELECT id, title FROM articles
WHERE created_at >= '2024-01-01 00:00:00'
ORDER BY created_at
LIMIT 10;

解释：

  • 如果查询条件明确指定了某个时间范围，则 MySQL 不需要扫描全部记录，可以大幅缩小查询范围。

优势：

  • 通过限定查询范围，可以有效减少扫描的数据量，提升分页查询的性能。

5. 使用 ROW_NUMBER() 优化分页

在MySQL 8.0 及更新版本中，MySQL 支持 ROW_NUMBER() 函数，可以通过此函数优化分页。在使用 ROW_NUMBER() 时，可以避免 LIMIT 的性能问题，尤其在复杂查询时效果更好。

WITH numbered_articles AS (
  SELECT id, title, ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
  FROM articles
)
SELECT id, title
FROM numbered_articles
WHERE row_num BETWEEN 99991 AND 100000;

解释：

  • ROW_NUMBER() 函数为每一条记录分配一个行号。我们可以通过这个行号进行过滤，而不需要扫描整个数据集。

优势：

  • 对于深度分页，使用 ROW_NUMBER() 可以避免 LIMIT 的大范围扫描，性能相对较好。

6. 利用缓存加速深度分页

如果分页查询的结果是相对稳定的，可以将一些常用页（例如前几千页）的结果缓存起来，避免每次都从数据库进行深度分页查询。

缓存策略：

  • 缓存前几页的数据结果，例如将前100页的结果缓存在内存或 Redis 中。
  • 用户查询时，优先从缓存中获取数据，避免对数据库的频繁请求。

示例：

  • 电商平台的商品展示，前几页往往被频繁访问，缓存这些数据可以显著减少数据库的压力。

7. 避免过深的分页

在实际业务场景中，如果发现用户正在进行非常深的分页查询（例如第1000页或第10000页），可以考虑提供一种替代方案：

  • 限制分页深度：通过限制用户只能翻看前几百页，减少极深分页的性能问题。
  • 给用户提示：例如在搜索结果页，提示用户修改查询条件，而不是继续深度翻页。

八、深度分页优化的使用场景

  • 电商平台：商品展示通常会有数百万条数据，但用户只需要查看一小部分。在这种场景中，可以通过深度分页优化来提升页面加载速度。
  • 日志查询系统：在日志系统中，数据量极大，但用户通常只会关注特定时间段或最近的记录。通过索引覆盖、条件查询可以加速日志的分页查询。
  • 搜索引擎：用户在搜索时可能会翻看多页结果，对于热门的搜索词，结果集可能非常庞大，使用延迟关联和索引优化可以减少查询时间。

九、总结

深度分页在数据量大的场景下容易引发性能问题，MySQL 的传统 LIMIT 实现会导致不必要的记录扫描。通过覆盖索引、延迟关联、自增主键优化等技术手段，可以大大减少扫描的记录数，提升分页查询的效率。在实际的业务场景中，可以根据具体数据结构和查询场景选择合适的优化方式，以满足高效分页查询的需求。

标签：articles,10000,分页,created,查询,MySQL,主键,id
From： https://blog.csdn.net/qq_41520636/article/details/143091491

相关文章

  • Mysql高级-day01
    Mysql高级-day01MySQL高级课程简介序号Day01Day02Day03Day041Linux系统安装MySQL体系结构应用优化MySQL常用工具2索引存储引擎查询缓存优化MySQL日志3视图优化SQL步骤内存管理及优化MySQL主从复制4存储过程和函数索引使用MySQL锁问题综......
  • 一文彻底弄懂MySQL的MVCC多版本控制器
    InnoDB的MVCC（Multi-VersionConcurrencyControl，多版本并发控制）是MySQL实现高并发事务处理的一种机制。通过MVCC，InnoDB可以在高并发环境下支持事务隔离，并提供非阻塞的读操作，从而避免锁定所有读操作带来的性能瓶颈。MVCC允许事务在不加锁的情况下读取数据，保证了性能和一......
  • mysql8.0安装和配置教程
    1.下载地址：https://dev.mysql.com/downloads/mysql/进入界面是最新的版本，这里下载。1.安装MySQL2.下载3.选择自定义安装4.安装结束5.配置mysql环境变量我使用系统Windows11安装mysql选择自定义安装路径是：D:\ProgramFiles\MySQLServer8.0右键......
  • MySQL数据库备份与恢复
    MySQL数据库的备份与恢复是数据库管理的重要部分，它可以帮助我们保护数据不受硬件故障、数据损坏、人为错误等因素的影响。以下是MySQL数据备份与恢复的详细步骤和代码示例。数据备份1.使用 mysqldump 工具mysqldump是MySQL自带的一个强大的命令行工具，用于导出数据库......
  • 使用MySQL之用通配符进行过滤
    1.LIKE操作符通配符（wildcard）：用来匹配值的一部分的特殊字符。搜索模式（searchpattern）：由字面值、通配符或两者组合构成的搜索条件。通配符本身实际是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符。为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL后跟的......
  • MySQL数据库中存储图片和读取图片的操作
    文章目录方法一：将图片以BLOB类型存储在数据库中MySQL语句实现Python实现方法二：将图片存储在文件系统中，并在数据库中存储路径MySQL语句实现Python实现总结在MySQL数据库中存储图片通常有两种主要方式：将图片以二进制数据（BLOB类型）直接存储在数据库中，或者将图......
  • 科普文：软件架构数据库系列之【MySQL死锁案例分析：间隙锁“Gap Lock”导致的死锁及解决
    概叙科普文：软件架构数据库系列之【详解MySQL死锁】-CSDN博客科普文：软件架构数据库系列之【MySQL死锁案例分析：index_merge导致的死锁及解决方案ERROR1213(40001):Deadlock】-CSDN博客科普文：软件架构数据库系列之【MySQL死锁案例分析：加锁顺序“循环等待”导致的死锁及解......
  • 2024/10/19日 日志--》关于MySQL中 JDBC的API 详解的整理简述
    今天进一步学习了JDBC中的API，已经可以初步连接数据库了，接下来继续进行学习。点击查看代码--JDBCAPI详解--DirverManager--DriverManager(驱动管理类)作用:1.注册驱动2.获取数据库连接--1.注册驱动--Class.forName("com.mysql.jdbc.Driver");--·需要注意的是:My......
  • 一文搞懂MySQL索引
    索引概述介绍索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。优缺点优势劣势提高数据检索的效......
  • Mysql高级-day04
    Mysql高级-day041.MySql中常用工具1.1mysql该mysql不是指mysql服务，而是指mysql的客户端工具。语法：mysql[options][database]1.1.1连接选项参数： -u,--user=name 指定用户名 -p,--password[=name] 指定密码 -h,--host=name 指定服务器IP或域名 -P,--por......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99