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条记录丢弃，这无疑是一种巨大的性能浪费。

 

终于转入正题了，那我们应该怎样优化呢？

 

四、延迟关联优化

《高性能MySQL》书中其实也讨论这个情况：

 

 

 

延迟关联优化：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据。

 

select * from t_trade_order t

inner join (

     select id from t_trade_order

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

     limit 1000000, 10

) e

on t.id = e.id;

 

 

 

根据explain分析，查询时间仅为0.31，比普通的分页查询快了一个数量级。

 

想必大家会想知道，为什么延迟关联对比普通分页查询可以起到这样的优化效果呢？

这就涉及到上面所讲的覆盖索引和回表这两个重要概念了！

 

我们来看看这两条语句的执行流程就很清楚了。

优化前：

select * from t_trade_order

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

limit 1000000, 10;

（create_time建表时被设置为普通索引）

 

1.在create_time索引树上找到create_time=‘2019-10-17’的记录，取得其id。

2.再到主索引树查到对于id的记录

3.如数量小于10，更新时间，循环步骤1、2

4. 。。。

5.在create_time索引树取下一个值create_time='2019-10-25'，不满足条件，循环结束。

6.查询结果放弃前1000000行，返回10行

 

显然，普通的分页查询是逐一通过普通索引获得id然后回表查询，每次回表进行一次IO，造成相当大的性能浪费。

 

优化后

select * from t_trade_order t

inner join (

    select id from t_trade_order

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

    limit 1000000, 10

) e

on t.id = e.id;

 

1.使用覆盖索引，select id from t_trade_order where create_time between '2019-10-17' and '2019-10-25' limit 1000000, 10，查询结果放弃前1000000行，返回10行，查询出符合查询范围的id

2、回表关联，根据获得的id关联主索引表，批量匹配得到结果。（只需回到主索引表一次）

 

由此可知，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行，这可以减少MySQL回表的次数，也避免了MySQL直接在原表上扫描那些需要丢弃的行数（实则在普通索引树上扫描，速度快很多）。