MYSQLg advanced ------ return table

You need to check the return form before 先对 聚簇索引和非聚簇索引 innoDB和MyISAM有一定的了解; Interested in seeing it for yourself ：地址

一、什么是回表？

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

聚集索引(clustered index)

普通索引(secondary index)

The primary key index contains all the data for that row,A normal index contains only the index sumid

Actually a non-clustered index The process is the so-called back table;

通俗的来讲就是：如果select There are non-indexed columns in the required columns,A single index query cannot obtain all the information,You need to find the corresponding column in the tableID; 在根据ID去去查询
Corresponds to the data of the specific column in the table,这个过程就是回表;The information of all columns can be obtained according to one index query,就不需要回表;（也就是聚集索引）

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);

Execute the following without returning to the table,Because of the query method based on the primary key,只需要查询ID这颗B+树即可;The primary key is unique according to this unique index,MYSQLYou can be sure to search for this record;
id为主键索引,主键索引就是聚集索引
The leaf nodes of a clustered index contain records for entire rows,Get all the information in one query,Therefore, no return form is required

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

下面这个sqlIt just needs to be returned：因为name 是普通的索引,the way of his query,需要先查询name的索引树,然后得到id的主键为3,再到idThe index tree performs a query.即先定位主键值,Relocation record,Although the index is used in this process,But in fact, the bottom layer performs two index queries,This process is the return form;

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

详细的 请查看 博客

Hope it can help you understand better;不足之处,请大家批评指正;