MySQL中如何优化LIMIT分页

数据库 MySQL
MySQL中如何优化LIMIT分页?这个问题我们今天一起来聊一聊。

以下是一个示例,演示如何优化MySQL 中limit 分页查询的性能:

假设我们有一个名为 users 的表,其中存储了 1,000,000 条用户记录。我们想要每次查询 100 条记录,并从第 10,000 条记录开始查询。以下是一个查询语句的示例:

SELECT * FROM users ORDER BY id LIMIT 100 OFFSET 10000;

这个查询语句使用了 OFFSET 子句来跳过前面的 10,000 条记录,并使用了 LIMIT 子句来返回 100 条记录。

为了优化这个查询语句,我们可以使用以下技巧:

  • 使用索引:在 users 表上创建一个索引,以便在执行查询时使用。例如,我们可以在 id 字段上创建一个索引,以便在排序时使用。
ALTER TABLE users ADD INDEX idx_id (id);
  • 使用 EXPLAIN 分析查询:我们可以使用 EXPLAIN 命令来分析查询,以查看 MySQL 是否使用了索引和哪些索引被使用。
EXPLAIN SELECT * FROM users ORDER BY id LIMIT 100 OFFSET 10000;

如果索引被正确使用,则会在 EXPLAIN 的输出中看到 Using index,如下所示:

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | index | NULL | idx_id | 4 | NULL | 100 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  • 避免使用 OFFSET:我们可以使用“分页锚点”来避免使用 OFFSET。例如,我们可以在每个页面之间传递上一页的最后一个 id,并将其用作下一页查询的条件。这将消除 OFFSET 子句的需要,并且在每个页面之间具有更加一致的性能。
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 100;

这个查询语句将返回从第 100,001 条记录开始的 100 条记录。每次查询时,只需要改变 id 的值,即可获取下一页的结果。

  • 使用固定大小的分页:我们可以将每个页面的大小设置为固定值,例如 100 条记录,这将使查询更加可预测并且在每个页面之间具有更加一致的性能。
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 100;
  • 使用缓存:对于经常重复查询的结果,我们可以考虑将结果缓存起来,以避免每次查询时都重新执行查询


责任编辑:华轩 来源: 今日头条
相关推荐

2010-05-25 15:12:22

MySQL分页

2010-05-17 17:09:29

Mysql LIMIT

2019-09-11 10:40:49

MySQL大分页查询数据库

2010-11-25 10:12:02

MySQL查询优化

2022-09-07 07:37:06

LIMITOFFSET分页

2010-11-25 14:21:16

MySQL查询分页

2022-10-27 21:34:28

数据库机器学习架构

2010-10-13 16:31:18

优化MySQL查询

2011-03-09 13:06:29

LimitMySQL

2021-06-09 06:41:11

OFFSETLIMIT分页

2017-07-25 15:35:07

MysqlMysql优化LIMIT分页

2010-05-17 16:52:14

MySQL limit

2019-11-15 10:01:07

MySQL数据库数据

2010-12-07 09:20:44

MySQL limit

2022-07-12 12:02:08

分页互联网

2010-05-21 14:01:23

MySQL数据库

2018-03-21 15:02:23

数据库MySQL分页优化

2021-09-26 06:43:07

MySQL深分页优化

2021-09-27 13:33:03

MySQL深分页数据库

2023-11-07 07:50:55

LIMIT子句下推优化
点赞
收藏

51CTO技术栈公众号