MySQL回表查询

Myth丶恋晨 2022-12-23 15:24 191阅读 0赞

在读这篇文章的开始先布置个作业，欢迎在评论区互怼！

> SQL优化  
> 问：假设有一张订单表order，主要包含了主键order\_no，订单状态status，提交事件create\_time等列，并且创建了status列索引和create\_time列索引。此时通过create\_time降序获取状态为1的订单编码。以下是具体实现代码；
> 
> ###### select order\_no from order where status=1 order by create\_time desc ######

针对这条sql代码，还有哪些优化空间？

![在这里插入图片描述][20201125173244259.gif_pic_center]

##     ##

各位大佬们有什么思路嘛，还是说一脸懵？？哈哈哈，话不多说，往下看。

你可能从来都没有听说过回表一词，但是你在实际工作中肯定用过回表。  
如果还没有听过回表，那我相信不管你看多少 SQL 优化的知识，都还只是停留在表面。（说完感觉得罪了一个亿的程序员…）

![在这里插入图片描述][20201125174516104.jpg_pic_center]

##### 一条SQL语句的查询过程 #####

我们先来看看什么是回表？

通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的**非索引列**，索引就需要到表中找到相应的列的信息，这就叫回表。

根据这个概念，当你使用 Explain 执行查询计划时，当结果中 Extra 出现了 using index、using where、using index condition 等你就认为使用了过滤条件，使用了索引，SQL 优化的还不错。这其实是一种错误的认识。  
因为，使用了索引并不代表查询就最优。从 using index condition、using index & using where 等可以看出，这条 SQL 语句其实是进行了回表的。

还有些时候，你查看 Explain 执行计划后，发现明明走了索引，为什么还是慢？这里面可能就是存在回表了。下面我们通过一个例子来说明什么是回表。先创建一张表，sql 语句如下：

create table order(
        id int primary key, 
        order_no varchar(15) DEFAULT NULL COMMENT '订单号', 
        status tinyint(2) DEFAULT '0' COMMENT '提交状态',
        create_time timestamp NULL DEFAULT NULL COMMENT '创建时间'
        index (status),
        index (create_time)
    )engine = InnoDB;

然后，我们再执行下面的 SQL 语句，插入几条测试数据。

INSERT INTO ORDER
        ( id, order_no, status, create_time )
    VALUES
    	( 1, 111, 1, '2020-11-20 07:28:31' ),
    	( 2, 222, 0, '2020-11-20 07:28:31' ),
    	( 3, 333, 0, '2020-11-20 07:28:31' );

假设，现在我们要查询出 id 为 2 的数据。那么执行 select \* from order where id = 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索id 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。

但当我们使用 status 这个索引来查询 status = 1 的记录时的订单号order\_no就要用到回表。select order\_no from order where status = 1; 原因是通过 status 这个普通索引查询方式，则需要先搜索 status 索引树，然后得到主键 id 的值为 1，再到 id 索引树搜索一次才能获取到这条记录从而得到order\_no。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

我这里表里的数据量比较少，如果数据量大的话，你能很明显的看出两次查询所用的时间，很明显使用主键查询效率更高。

> 看到这里应该对回表有了点理解吧。那么文章开头的问题有头绪了嘛？？？？  
> 来评论区互怼吧  
> ![在这里插入图片描述][20201125173423359.gif_pic_center]

[20201125173244259.gif_pic_center]: /images/20221120/4260d3abea724e808dcce50d15781c54.png
[20201125174516104.jpg_pic_center]: /images/20221120/a3ea3e03199a4eee8d0d563cb7aba527.png
[20201125173423359.gif_pic_center]: /images/20221120/d299efb73d014f7fb1f3d8bf230a7ec4.png