MYSQLg高级------回表

MYSQLg高级------回表

博主 默语带您 Go to New World. ✍ 个人主页—— 默语 的博客👦🏻 《java 面试题大全》 🍩惟余辈才疏学浅，临摹之作或有不妥之处，还请读者海涵指正。☕🍭 《MYSQL从入门到精通》数据库是开发者必会基础之一~ 🪁 吾期望此文有资助于尔，即使粗浅难及深广，亦备添少许微薄之助。苟未尽善尽美，敬请批评指正，以资改进。！💻⌨

查看回表之前大家需要 先对 聚簇索引和非聚簇索引 innoDB和MyISAM有一定的了解； 有兴趣看自行查看 ：地址

回表（或称为二次查询）是MySQL数据库中一个重要的概念，通常涉及到使用非唯一索引来查询数据后，再通过该数据的唯一主键索引去获取更多的详细信息。这是因为非唯一索引只包含索引字段和对应的主键值，而不包含其他需要的数据。以下是关于回表的简要概述：

回表的概念：

回表是指在通过非唯一索引（或称为辅助索引）进行查询时，MySQL数据库首先使用索引找到匹配行的主键值，然后再使用主键索引去获取这些行的详细数据。这是为了避免在非唯一索引中保存大量冗余数据，同时提供了快速查找匹配行的能力。

回表的过程：

1. 使用非唯一索引： 查询语句首先使用非唯一索引来查找满足条件的行。
2. 获取主键值： 通过非唯一索引找到的行中包含了对应的主键值。
3. 使用主键索引： 使用主键索引查找并返回与主键值对应的完整行数据。

回表的影响：

虽然回表可以提供更详细的数据，但它也可能引起性能问题。因为回表涉及到了多次磁盘访问，可能会增加查询的IO开销，尤其是在大数据量的情况下。这可能导致查询性能下降。

回表的优化：

为了优化回表带来的性能问题，可以考虑以下方法：

• 使用覆盖索引（Covering Index）： 创建一个包含需要查询的字段的索引，这样查询就可以直接从索引中获取数据，而不需要回表。
• 调整查询语句： 考虑使用JOIN操作或子查询来一次性获取所需的数据，减少回表的次数。
• 适当考虑索引策略： 根据查询的具体情况，选择合适的索引，以减少回表操作。

回表是数据库查询性能优化中需要关注的一个方面，了解何时会发生回表以及如何优化回表操作，可以帮助您更好地设计数据库结构和查询语句，以获得更好的性能。

一、什么是回表？

这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

聚集索引(clustered index)

普通索引(secondary index)

主键索引包含该行所有数据，普通索引包含的只有该索引和id

其实非聚集索引 的过程就是所谓的回表；

通俗的来讲就是：如果select 所需要获得列中有非索引列，一次索引查询不能获取所有的信息，需要到表中找到相应列的ID； 在根据ID去去查询 对应表中具体的列的数据，这个过程就是回表；而根据一次索引查询就能获取所有列的信息，就不需要回表；（也就是聚集索引）

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) ,
KEY `fk_dept_id`(`deptId`)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

INSERT INTO `atguigudb`.`user` (`id`, `name`, `deptId`) VALUES (1, '小闫', 10);
INSERT INTO `atguigudb`.`user` (`id`, `name`, `deptId`) VALUES (2, '老闫', 10);
INSERT INTO `atguigudb`.`user` (`id`, `name`, `deptId`) VALUES (3, '小闫01', 10);
INSERT INTO `atguigudb`.`user` (`id`, `name`, `deptId`) VALUES (4, '小闫02', 10);
INSERT INTO `atguigudb`.`user` (`id`, `name`, `deptId`) VALUES (5, '小闫03', 10);

执行下面的就不需要回表，因为根据主键查询方式，只需要查询ID这颗B+树即可；主键是唯一的根据这个唯一索引，MYSQL就能确定搜索到这条记录； id为主键索引，主键索引就是聚集索引 聚集索引的叶子节点包含整给行的记录，一次查询就能获取所有的信息，故不需要回表

# 直接访问id 找到对应的值
select id,name,deptId FROM USER WHERE name='3';

下面这个sql就是需要回表的：因为name 是普通的索引，他的查询的方式，需要先查询name的索引树，然后得到id的主键为3，再到id索引树进行一次查询。即先定位主键值，再定位记录，再这个过程中虽然使用了索引，但实际上底层进行了两次索引的查询，这给过程就是回表；

#非聚簇索引 根据值找到id 根据id找到对应的值
select id,name,deptId FROM USER WHERE name="小闫01";
select id,name,deptId FROM USER WHERE name='3';

详细的 请查看 博客

希望可以帮助大家更好的理解吧；不足之处，请大家批评指正；