mysql回表查询和索引覆盖

作为 JAVA 开发的必备知识，了解回表查询和索引覆盖可以大大提升数据库查询的速度，也是优化数据库查询的必备知识。

1. 什么是索引?

索引（在 MySQL 中也叫“键key”）是存储引擎快速找到记录的一种数据结构，通俗来说类似书本的目录，这个比方虽然被用的最多但是也是最恰如其当的，在查询书本中的某个知识点不借助目录的情况下，往往都找的够呛，那么索引相较于数据库的重要性也可见一斑。

回表查询和索引覆盖

InnoDB索引分为两大类，一类是聚集索引(Clustered Index)，一类是非聚集索引(Secondary Index)

聚集索引（聚簇索引）：叶子节点中存的是整行数据，找到索引也就找到了数据，索引即数据，表中行的物理顺序与键值的逻辑（索引）顺序相同，一个表只能包含一个聚集索引。因为索引（目录）只能按照一种方法进行排序。

非聚集索引（普通索引、非聚簇索引、二级索引）：非聚集索引的btree叶子节点中存储的是当行数据的PK（主键）。例如MYISAM通过key_buffer把索引先缓存到内存中，当需要访问数据时(通过索引访问数据)，在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

为什么非主键索引结构叶子结点存储的是主键值？

减少了出现行移动或者数据页分裂时二级索引的维护工作（当数据需要更新的时候，二级索引不需要修改，只需要修改聚簇索引，一个表只能有一个聚簇索引，其他的都是二级索引，这样只需要修改聚簇索引就可以了，不需要重新构建二级索引）

回表查询
回表查询顾名思义就是在数据查询过程中 MySQL 内部需要两次查询。即先定位查询数据所在表的主键值，再根据主键定位行记录。

要说回表查询，我们就要先从 InnoDB 的索引实现说起。InnoDB 索引分为两大类：聚集索引（ Clustered Index ）和普通索引（ Secondary Index ）。

InnoDB 聚集索引（ Clustered Index）
InnoDB 聚集索引的叶子节点存储行记录，因此 InnoDB 必须要有且只有一个聚集索引。

（1）如果表定义了 PK （Primary Key，主键），那么 PK 就是聚集索引；

（2）如果表没有定义 PK，则第一个 NOT NULL UNIQUE 的列就是聚集索引。

（3）否则 InnoDB 会另外创建一个隐藏的 ROWID 作为聚集索引。

由于这种机制是直接定位行记录，因此使得基于 PK 的查询速度非常快。

InnoDB 普通索引（ Secondary Index ）
InnoDB 普通索引的叶子节点存储主键值（MyISAM 则是存储的行记录头指针）。

回表查询
例如：创建一个表 user_t ：

CREATE TABLE `user_t` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `age` int DEFAULT '0',
  `sex` char(1) DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）',
  `count` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_t_name_IDX` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3;

​ 其中 id 为聚集索引，name 为普通索引。

为表中添加四条记录：

INSERT INTO user_t
(id, name, age, sex, count)
VALUES(1, 'zhangsan', 20, '0', 0);
INSERT INTO user_t
(id, name, age, sex, count)
VALUES(2, 'lisi', 21, '0', 0);
INSERT INTO user_t
(id, name, age, sex, count)
VALUES(3, 'xiaohong', 19, '1', 0);
INSERT INTO user_t
(id, name, age, sex, count)
VALUES(4, 'xiaohong', 18, '1', 0);

聚集索引的B+树索引（id是PK，叶子节点存储行记录）：

普通索引的B+树索引（name是KEY，叶子节点存储PK值，即id）：

​ 

普通索引因为无法直接定位行记录，其查询过程在通常情况下是需要扫描两遍索引树的。

select * from t where name = 'xiaohong';

explain查看执行过程是这样的：

查询的内容不再索引内，where条件为索引最左前缀，extra为null， type为ref，表明虽然用到了索引，但是没有索引覆盖，产生了回表

实际进行了2次索引扫描

 

粉红色的路径需要扫描两遍索引树，第一遍先通过普通索引定位到主键值id=5，然后第二遍再通过聚集索引定位到具体行记录。这就是所谓的回表查询，即先定位主键值，再根据主键值定位行记录，性能相对于只扫描一遍聚集索引树的性能要低一些。

怎么判断是否出现了回表
1，在 explain（执行计划）Extra 中看到了 Using Where，代表是按照 where 条件进行了过滤，和是否走索引、回表无必然的关系。
2，在 explain（执行计划）Extra 中是 Using Index & Using Where，表示 select 的数据在索引中能找到，但需要根据 where 条件过滤，这种情况也不会用到回表。
3，只有在使用了索引，且在 explain（执行计划）Extra 中看到了 Using Where 或者Extra是null的情况下，才代表回表查询数据。

索引覆盖

索引覆盖是一种避免回表查询的优化策略。具体做法就是将要查询的数据作为索引建立普哦，那个索引（可以是单列索引，也可以一个索引语句定义所有要查询的列，即联合索引），这样就可以直接返回索引中的数据，不需要再通过聚集索引去定位行记录，避免了回标的情况发生。

1.什么是覆盖索引？

1）只需要在一棵索引树上就可以获取sql所需所有的列数据，不需要回表，较之回表速度要更快。

2）explain输出结果extra字段为Using index时，触发了索引覆盖。

定义
如果一个索引覆盖（包含）了所有需要查询的字段的值，这个索引就是覆盖索引。

覆盖索引的定义与注意事项

如果一个索引覆盖（包含）了所有需要查询的字段的值，这个索引就是覆盖索引。因为索引中已经包含了要查询的字段的值，因此查询的时候直接返回索引中的字段值就可以了，不需要再到表中查询，避免了对主键索引的二次查询，也就提高了查询的效率。

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

怎么判断索引覆盖查询
当发起一个被索引覆盖的查询（索引覆盖查询）时，在 explain（执行计划）的 Extra 中可以看到 Using Index 的信息。

索引覆盖的优点

索引条目通常远小于数据行的大小，因为覆盖索引只需要读取索引，极大地减少了数据的访问量。
索引是按照列值顺序存储的，对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO小很多。
一些存储引擎比如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据的话需要一次系统调用，使用覆盖索引则避免了这一点。
由于InnoDB的聚簇索引，覆盖索引对InnoDB引擎下的数据库表特别有用。因为InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，就避免了对主键索引的二次查询。

哪些场景适合使用索引覆盖来优化SQL

当不需要查询整行记录时；

 全表count查询优化；

Limit分页查询；

哪些情况下不要建索引

表记录太少
经常增删改的表或者字段（如用户余额）
Where条件里用不到的字段不创建索引
过滤性不好的不适合建索引（如性别）