马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？立即注册
×
什么是深分页？
- 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;
- 扫描 users 表的索引（假设是聚簇索引）来找到 ID 为 100001 的纪录。
- 扫描并抛弃前 100000 条纪录。
- 返回第 100001 到第 100010 条纪录。
- 这个过程在数据量大时非常低效，尤其是当索引不是聚簇索引时，每个匹配的索引纪录都须要实行一次回表利用。
深分页性能降落的缘故因由
索引扫描的范围性
在深分页查询中，性能降落的告急缘故因由之一是索引扫描的范围性。以下是几个关键点：
- 全索引扫描
- 当LIMIT语句的offset值很大时，MySQL 大概须要实行全索引扫描来找到满足条件的纪录。这意味着从索引的根节点开始，不绝扫描到叶子节点，无论这些节点是否包罗目的数据。
- 索引跳跃性
- 纵然是索引扫描，MySQL 也无法直接跳转到特定的offset位置。它必须从索引的开始位置序次扫描，直到到达所需的位置。这种序次扫描的过程是耗时的。
- 回表开销
- 对于非聚簇索引，找到满足条件的索引纪录后，MySQL 须要实行回表利用来获取完备的行数据。在深分页查询中，由于offset值大，这会导致大量的回表利用，从而增长 I/O 开销。
锁和变乱的影响
在并发情况下，深分页查询大概会引起以下标题：
- 长变乱和锁竞争
- 深分页查询大概须要较长的时间来实行，这会增长变乱的连续时间。长时间的变乱大概会导致锁竞争，影响其他并发利用的性能。
- 死锁风险
- 在复杂的查询利用中，深分页查询大概会增长死锁的风险，尤其是在涉及多个表和索引的情况下。
实例分析
以之前的用户表users为例，假设我们使用的是非聚簇索引来实行深分页查询。以下是一个具体的性能标题分析：- SELECT * FROM users WHERE username LIKE 'A%' ORDER BY id LIMIT 100000, 10;
- MySQL 须要扫描大量的索引纪录。
- 对于每个索引纪录，MySQL 都须要实行一次回表利用。
- 排序利用自己也会斲丧大量的 CPU 资源。
小结 深分页性能降落的缘故因由是多方面的，包罗索引扫描的范围性、锁和变乱的影响等。这些因素共同作用，导致查询服从低下，尤其是在处置惩罚大量数据时。
优化计谋及其底层原理
子查询优化计谋 子查询优化计谋的焦点头脑是淘汰回表利用。通过在子查询中找到满足条件的起始ID，然后在主查询中直接从该ID开始检索数据。
底层原理：
- 子查询在二级索引上实行，快速定位到满足条件的起始点。
- 主查询使用该起始点在主键索引上直接检索数据，克制了从二级索引到主键索引的多次回表。
示例：- SELECT * FROM users WHERE id = (SELECT id FROM users WHERE username LIKE 'A%' ORDER BY id LIMIT 100000, 1) LIMIT 10;
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;
标签纪录法计谋
标签纪录法通过纪录上一次查询的末了一个 ID，下次查询从该 ID 开始。
底层原理：
- 使用有序索引的特性，从上一次查询的末了一个ID开始，克制重新扫描。
- 实用于有连续或可排序的字段，如自增主键或时间戳。
示例：
- SELECT * FROM users WHERE id > last_id ORDER BY id LIMIT 10;
缺点（这种方法不能随机翻页）
使用BETWEEN…AND…计谋
计谋形貌： 使用BETWEEN…AND…来取代LIMIT，直接指定查询的范围。
底层原理：
- BETWEEN…AND…答应 MySQL 直接定位到查询的起始和竣事点。
- 淘汰了扫描的行数，进步了查询服从。
示例：
- SELECT * FROM users WHERE id BETWEEN start_id AND end_id;
|
发表于 2025-10-14 00:31:41