MySQL 分页查询优化——延迟关联优化

最新推荐文章于 2025-05-22 08:41:38 发布

weixin_43831204

最新推荐文章于 2025-05-22 08:41:38 发布

阅读量570

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.csdn.net/weixin_43831204/article/details/103537733

本文探讨了MySQL分页查询的优化策略，重点介绍了如何利用覆盖索引和延迟关联技术减少回表次数，显著提升查询效率，适用于大量数据的分页场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL 分页查询优化——延迟关联优化

1. InnoDB表的索引的几个概念

2. 覆盖索引和回表

3. 分页查询

4. 延迟关联优化

写在前面

下面的介绍均是在选用MySQL数据库和Innodb引擎的基础开展。我们先来学习索引的几个概念，帮助我们理解延迟关联优化的加快分页查询速度的原因。

一、Innodb表的索引的几个概念

InnoDB表是基于聚簇索引建立的。

索引一般分为主键索引和普通索引（辅助索引），聚簇索引并不是主键索引这样的单独的索引类型，而是一种数据存储方式。通俗的来说，单独的索引是存储了索引信息的B+Tree，而聚簇索引是在同一个结构中保存了B+Tree和数据行，即通过主键索引B+Tree的结构建立数据文件（网上的说法是索引和数据存储在同一个文件中），因此聚簇索引是一种数据存储方式。

如果对于索引的概念不是很熟悉，建议去查阅相关资料学习，索引是一个很庞大的知识结构。

Innodb表以主键索引建立后缀名为.MYD表存储文件，普通索引亦以B+Tree实现，并保存在后缀名为.MYI的文件中，具体结构图如下所示：

在查询时选用主键索引和普通索引有什么不一样呢？

以下面语句为例：

· select * from table where id = 5;（id为主键索引）检索过程如上图绿色箭头所示，直接在主索引树上根据主键检索

· select * from table where name = "Gates"; （name为普通索引）检索过程如上图红色箭头所示，先根据普通索引检索普通索引树得到id为5，然后再拿得到的id到主索引树检索得到结果。在这个过程中，回到主键索引树搜索的过程，我们称为回表。

从这里可以看出，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

解释完普通索引和主键索引的检索过程，让我们来看看什么是覆盖索引。

二、覆盖索引和回表。

什么是“覆盖索引”？

查询的列被所建的辅助索引所覆盖，无需回表。用大白话解释就是，要查的数据直接可以从索引树上就能取得，无需回表查找。

注意：不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引

结合上图的例子：

· select id from table where name = "Gates"; 即为一个覆盖索引。

三、分页查询使用场景

需求：查询最近 7 天的订单，并做分页。订单表数据量：3000W。

未经优化的SQL：

select * from t_trade_order

where create_time between '2019-10-17' and '2019-10-25'

limit 1000000, 10;

根据explain输出的结果可知，这是一条慢查询，在业务环境中不允许出现这样的慢查询。

我们都知道在做分页时会用到Limit关键字去筛选所需数据，limit接受1个或者2个参数，接受两个参数时第一个参数表示偏移量，即从哪一行开始取数据，第二个参数表示要取的行数。如果只有一个参数，相当于偏移量为0。当偏移量很大时，如limit 100000,10 取第100001-100010条记录，mysql会取出100010条记录然后将前100000条记录丢弃，这无疑是一种巨大的性能浪费。