笙亿网络策划

MySQL分页查询的select语句怎么高效实现？

约 10 分钟读完 29 阅
文章导读
MySQL 分页查询高效实现的核心在于避免大偏移量带来的全表扫描或大量数据丢弃。对于小数据量可直接使用 LIMIT 语句，但对于百万级大表，推荐采用覆盖索引优化、延迟关联（Deferred Join）或基于游标（ID 大于上一页最大 ID）的方式。覆盖索引通过只查询索引列减少回表开销，延迟关联先查 ID 再关联主表，而游标分页则利用有序唯一值定位锚点，避免遍历历史数据，能将深分页查询时间从秒级降低
📋 目录
  1. Ⅰ MySQL 分页查询的 select 语句怎么高效实现？
  2. Ⅱ 面试题-Mysql 大表如何优化分页查询 - 腾讯云开发者社区 - 腾讯云
  3. Ⅲ mysql 大数据量分页查询优化总结 - 腾讯云开发者社区 - 腾讯云
  4. Ⅳ MySql 分页查询慢 | 这里告诉你答案
  5. Ⅴ MySQL 实战：基于游标与覆盖索引的高效分页解决分页查询卡顿
  6. Ⅵ MySQL 百万级数据量分页查询方法及其优化
  7. Ⅶ FAQ
A A

MySQL 分页查询的 select 语句怎么高效实现？

MySQL 分页查询高效实现的核心在于避免大偏移量带来的全表扫描或大量数据丢弃。对于小数据量可直接使用 LIMIT 语句，但对于百万级大表，推荐采用覆盖索引优化、延迟关联（Deferred Join）或基于游标（ID 大于上一页最大 ID）的方式。覆盖索引通过只查询索引列减少回表开销，延迟关联先查 ID 再关联主表，而游标分页则利用有序唯一值定位锚点，避免遍历历史数据，能将深分页查询时间从秒级降低至毫秒级，显著提升数据库性能。

面试题-Mysql 大表如何优化分页查询 - 腾讯云开发者社区 - 腾讯云

分页查询是一个常用的功能，当单表数据量达到百万级别，查询速度缓慢，那么如何优化呢？就上面的问题我们先来分析分页如何实现，实现原理，如何优化; 假设一页 20 个数据; 1.如何实现:(select * from table limit x,y) select * from table limit 0,20;(第一页) select * from table limit 20,20;(第二页) select * from table limit 40,20;(第三页) select * from table limit 60,20;(第四页) select * from table limit 80,20;(第五页) 2.实现原理：实际上 mysql 查询的时候，会取出 x+y 行数据，然后舍去前 x 行，取回 y 行数据，当 x 非常大的时候，查询效率很低. 3.如何优化：一，"id>="方式 比如说查询第 100000 页，先通过 select id from table limit 100000,1，取到起始 id，然后通过 select * from table where id>=(select id from table limit 100000,1) limit 20，查询时间几百毫秒; 二，关联 id 方式 select * from table a,(select id from table limit 100000,20) b on a.id=b.id，查询时间同样很短; 三，缓存查询结果 把查询结果缓存到 redis 中，这样直接读取内存，而不是查询硬盘数据.

mysql 大数据量分页查询优化总结 - 腾讯云开发者社区 - 腾讯云

传统分页查询:select c1,c2,cn… from table limit n,m mysql 的 limit 工作原理就是先读取前面 n 条记录，然后抛弃前 n 条，读后面 m 条想要的，所以 n 越大，偏移量越大，性能就越差. 1)limit 语句的查询时间与起始记录的位置成正比 2)mysql 的 limit 语句是很方便，但是对记录很多的表并不适合直接使用。推荐分页查询方法：1，尽量给出查询的大致范围 利用表的覆盖索引来加速分页查询 我们都知道，利用了索引查询的语句中如果只包含了那个索引列 ( 覆盖索引：索引的数据覆盖了需要查询的所有数据 ),那么这种情况会查询很快。因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外 mysql 中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。在我们的例子中，我们知道 id 字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何：之前，我们取最后一页记录的时间 代码语言：javascript ai 代码解释 select * from product limit 866613 , 20 37.44 秒 这次我们查询最后一页的数据 (利用覆盖索引，只包含 id 列),如下：代码语言：javascript ai 代码解释 select id from product limit 866613 , 20 0.2 秒 相对于查询了所有列的 37.44 秒，提升了大概 100 多倍的速度那么如果我们也要查询所有列，有两种方法，一种是 id>=的形式，另一种就是利用 join ,看下实际情况:

MySql 分页查询慢 | 这里告诉你答案

我们在开发的过程中使用分页是不可避免的，通常情况下我们的做法是使用 limit 加偏移量：select * from table where column=xxx order by xxx limit 1,20。当数据量比较小时 (100 万以内),无论你翻到哪一页，性能都是很快的。如果查询慢，只要在 where 条件和 order by 的列上加上索引就可以解决。但是，当数据量大的时候 (小编遇到的情况 是 500 万数据),如果翻到最后几页，即使加了索引，查询也是非常慢的，这是什么原因导致的呢？我们该如何解决呢？limit 分页原理 当我们翻到最后几页时，查询的 sql 通常是:select * from table where column=xxx order by xxx limit 1000000,20。查询非常慢。但是我们查看前几页的时候，速度并不慢。这是因为 limit 的偏移量太大导致的。MySql 使用 limit 时的原理是 (用上面的例子举例): MySql 将查询出 1000020 条记录。然后舍掉前面的 1000000 条记录。返回剩下的 20 条记录。上述的过程是在《高性能 MySql》书中确认的。解决方案 解决的方法就是尽量使用索引覆盖扫描，就是我们 select 后面检出的是索引列，而不是 所有的列，而且这个索引的列最好是 id。然后再做一次关联查询返回所有的列。上述的 sql 可以写成：代码语言：txt AI 代码解释 SELECT * FROM table t INNER JOIN ( SELECT id FROM table WHERE xxx_id = 143381 LIMIT 800000,20 ) t1 ON t.id = t1.id 我们在 mysql 中做的真实的实验：image1 上图是没有优化过的 sql，执行时间为 2s 多。经过优化后如下：image2 执行时间为 0.3s，性能有了大幅度的提升。虽然做了优化，但是随着偏移量的增加，性能也会随着下降，MySql 官方虽然也给出了

MySQL 实战：基于游标与覆盖索引的高效分页解决分页查询卡顿

1.1 问题复现 典型的慢查询示例：代码语言：javascript AI 代码解释 SELECT*FROMorder_historyWHEREuser_id=100ORDERBYcreate_timeDESCLIMIT100000,10; 执行计划分析：type=ALL(全表扫描) rows=100010(实际扫描行数) Extra=Using filesort(文件排序) 1.2 性能损耗原理 三级性能瓶颈：IO 成本：扫描全部索引树或数据页 CPU 成本：排序丢弃前 N 条数据 网络成本：传输冗余数据 2.1 核心原理 利用有序唯一值作为定位锚点，避免遍历历史数据：代码语言：javascript AI 代码解释--下一页 SELECT*FROMorder_historyWHEREuser_id=100ANDid> 上一页最后一条 IDORDERBYidASCLIMIT10;--上一页 SELECT*FROMorder_historyWHEREuser_id=100ANDid< 当前页第一条 IDORDERBYidDESCLIMIT10; 2.2 实战案例：电商订单分页优化 原始表结构：代码语言：javascript AI 代码解释 CREATETABLEorders(idBIGINTAUTO_INCREMENTPRIMARYKEY,user_idINTNOTNULL,amountDECIMAL(10,2),create_timeDATETIME,INDEXidx_user_create(user_id,create_time)); 优化后查询：代码语言：javascript AI 代码解释--第一页 SELECT*FROMordersWHEREuser_id=100ORDERBYcreate_timeDESC,idDESCLIMIT10;--下一页 (假设上一页最后一条 create_time='2023-08-20 15:30:00',id=9527)SELECT*FROMordersWHEREuser_id=100AND(create_time<'2023-08-20 15:30:00'OR(create_time='2023-08-20 15:30:00'ANDid<9527))ORDERBYcreate_timeDESC,idDESCLIMIT10; 优化效果：执行时间从**1200ms**降至**8ms** 扫描行数从**100010 行**变为**10 行** 通过索引覆盖避免回表查询，结合延迟关联 (Deferred Join) 技术：代码语言：javascript

MySQL 百万级数据量分页查询方法及其优化

方法 1: 直接使用数据库提供的 SQL 语句 语句样式：MySQL 中，可用如下方法：SELECT * FROM 表名称 LIMIT M,N 适应场景：适用于数据量较少的情况 (元组百/千级) 原因/缺点：全表扫描，速度会很慢 且 有的数据库结果集返回不稳定 (如某次返回 1,2,3，另外的一次返回 2,1,3). Limit 限制的是从结果集的 M 位置处取出 N 条输出，其余抛弃。方法 2: 建立主键或唯一索引，利用索引 (假设每页 10 条) 语句样式:MySQL 中，可用如下方法：SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) LIMIT M 适应场景：适用于数据量多的情况 (元组数上万) 原因：索引扫描，速度会很快。有朋友提出：因为数据查询出来并不是按照 pk_id 排序的，所以会有漏掉数据的情况，只能方法 3 方法 3: 基于索引再排序 语句样式:MySQL 中，可用如下方法：SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M 适应场景：适用于数据量多的情况 (元组数上万). 最好 ORDER BY 后的列对象是主键或唯一所以，使得 ORDERBY 操作能利用索引被消除但结果集是稳定的 (稳定的含义，参见方法 1) 原因：索引扫描，速度会很快。但 MySQL 的排序操作，只有 ASC 没有 DESC(DESC 是假的，未来会做真正的 DESC，期待). 方法 4: 基于索引使用 prepare 第一个问号表示 pageNum，第二个？表示每页元组数 语句样式：MySQL 中，可用如下方法：PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (?* ?) ORDER BY id_pk ASC LIMIT M 适应场景：大数据量 原因：索引扫描，速度会很快。prepare 语句又比一般的查询语句快一点。

FAQ

MySQL 分页查询为什么深分页会慢？

MySQL分页查询的select语句怎么高效实现？

因为 limit 偏移量过大时，数据库需要扫描并丢弃大量前序数据，导致 IO 和 CPU 成本增加。

如何优化百万级数据量的分页查询？

可以使用覆盖索引配合延迟关联，或者利用主键 ID 大于上一页最大 ID 的方式进行游标分页。

覆盖索引在分页优化中起什么作用？

覆盖索引允许只查询索引列而不回表，大幅减少数据读取量，再通过 join 回表获取完整数据。