一、什么是回表查询？
如果索引的列在 select 所需获得的
列中根据一次索引查询就能获得记录就不需要回表, 反之如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。
深入了解回表就需要了解聚簇索引和非聚簇索引
二、什么是聚集索引？
聚集索引是关系型数据库中一种非常核心的索引类型，它的核心特征是：表中数据的物理存储顺序与索引的逻辑顺序是完全一致的。
聚集索引类似汉语字典的拼音索引（A-Z）物理排列：索引的叶子节点直接包含了完整的行数据。找到索引，就找到了数据本身。
汉语词典也可以有部首目录，这就是非聚簇索引，因为物理存贮顺序和逻辑顺序不一致
InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，有且只有一个聚集索引：
聚集索引特点
- 一张表有且只有一个聚集索引
- 如果表定义了主键，则PK就是聚集索引；
- 如果表没有定义主键，则第一个非空唯一索引（not NULL unique）列是聚集索引；
- 否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
创建一张user表
create table user(
id int primary key,
sex int not null,
name varchar(16),
index (name)
)engine = InnoDB;
INSERT INTO user(id, k, name) VALUES(1, 2, '张三'),
(2, 1, '李四'),
(3, 1, '李四四');
现在我们要查询出 id 为 2 的数据。那么执行 select * from user where ID = 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。
但当我们使用 name 这个索引来查询 name = '张三' 的记录时就要用到回表。
select * from user where name = '张三';
原因是通过 name 这个普通索引查询方式，需要先搜索 name 索引树，然后得到主键 ID 的值为 1，再到 ID 索引树搜索一次。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。
基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。
总结：使用聚集索引（主键或第一个唯一索引）就不会回表，普通索引就会回表
三、什么是索引覆盖？
你查询语句中需要的所有字段，都已经包含在你所建立的索引中，无需回表，就是索引覆盖。
explain的输出结果Extra字段为Using index时，能够触发索引覆盖。
为了更好地理解，我们可以结合刚才提到的“聚集索引”和“非聚集索引”来看：
结合前面的概念：与“回表”的对比
-
没有索引覆盖时（需要回表）：上一步执行 SELECT sex FROM user WHERE name = '张三' 时，数据库先在索引树里找到了“张三”，但索引里只有 name，没有 sex。于是数据库必须拿着主键指针，再回一次原始数据表去查 age。这多出来的“回表”操作是非常消耗磁盘 I/O 的。
-
有索引覆盖时（无需回表）：如果你建立了一个包含 name 和 age 两个字段的联合索引 (name, age)。当你再执行同样的查询时，数据库在索引树里直接找到了 name='张三' 对应的 age 值。数据全在索引里，直接返回，省去了回表的开销。
通俗理解覆盖索引
上面说的就相当于现实中你去图书馆查一本书的摘要，
-
没有索引覆盖：你在图书馆的目录卡片（索引）上查到了某本书的编号，但卡片上没写你要的摘要。你必须根据编号走到书架前（回表），把书拿下来翻开才能看到摘要。
-
有索引覆盖：就像你在目录卡片上不仅查到了编号，卡片背面还直接印着你要的摘要。你站在原地看完卡片就得到了答案，根本不用去书架找书
如何实现索引覆盖
在 MySQL 中，你可以使用 EXPLAIN 关键字来分析 SQL 语句。如果查询计划（Extra 列）中出现了 Using index，就说明触发了索引覆盖。
还是那刚才的sql举例，
SELECT sex FROM user WHERE name = '张三';
没有sex索引会回表，那加上sex加上索引不就好了，那就too yang to simple 了。
在 InnoDB 引擎中，所有的非聚集索引（普通索引）的叶子节点，存储的都是“索引列的值 + 主键（id）”。
如果你给 sex 加上索引（index (sex)），当执行 SELECT sex FROM user WHERE name = '张三' 时，数据库的执行过程会是这样的：
-
先在 name 索引中找到 '张三'，拿到对应的主键 id。
-
因为你要查 sex，数据库拿着 id 去 sex 索引里找。
-
找到了 sex 索引的记录，但发现里面只有 sex 和 id。为了确认这条记录确实存在且完整，或者因为查询优化器的逻辑，它依然需要拿着 id 去聚集索引（主键表）里查一次完整数据。
结论：给 sex 加普通索引，不仅没有消除回表，反而让查询多走了一次 sex 索引树，性能反而变差了
真正完美的解决方案：联合索引（索引覆盖）
四、什么是联合索引
正如我们上一轮聊到的，想要彻底避免回表，你需要把 WHERE 条件和 SELECT 的字段放在同一个索引里。 正确的做法是建立一个联合索引：
ALTER TABLE user ADD INDEX idx_name_sex (name, sex);
为什么这样完美？
当你执行 SELECT sex FROM user WHERE name = '张三' 时：
-
数据库走到 idx_name_sex 联合索引树。
-
根据最左前缀原则，通过 name = '张三' 直接定位到索引的叶子节点。
-
因为 sex 也在这个联合索引里，数据库直接从叶子节点提取出 sex 的值返回。
-
全程没有碰主键表，0 回表！（EXPLAIN 会显示 Using index）
总结：尽量不要为多列上创建单列索引，因为这样的情况下最多只能使用一星索引，这样的话，不如去创建一个全覆盖索引，在多列上创建单列索引大部分情况下并不能提高 MySQL 的查询性能，MySQL 5.0 中引入了合并索引，在一定程度上可以表内多个单列索引来定位指定的结果，但是 5.0 以前的版本，如果 where 中的多个条件是基于多个单列索引，那么 MySQL 是无法使用这些索引的，这种情况下，还不如使用联合索引。
五、什么是索引下推
索引下推（Index Condition Pushdown，简称 ICP）是 MySQL 5.6 引入的一种查询优化机制。 简单来说，它的核心作用是：在遍历索引的过程中，直接对索引中包含的字段进行条件过滤，从而大幅减少“回表”的次数 。
刚才已经为user表新建了联合索引 index (name, sex)，现在执行下列sql
SELECT * FROM user WHERE name LIKE '张%' AND sex = 1;
这里 SELECT * 意味着必须回表，而 LIKE '张%' 是范围查询，会导致联合索引中的 sex 字段失效（不满足最左前缀原则）
- 在 MySQL 5.6 之前（没有索引下推）：
-
存储引擎通过 name LIKE '张%' 在索引树中扫描，找到所有姓“张”的记录（比如找到了 100 条）。
-
存储引擎拿着这 100 条记录的主键，全部回表去查完整数据。
-
返回给 Server 层。
-
Server 层再对这 100 条数据进行 AND sex = 1 的过滤，最后返回给客户端。
痛点：明明索引里已经有 sex 的值了，却还要盲目回表 100 次，做了大量无用功。
- 在 MySQL 5.6 之后（有了索引下推）：
-
存储引擎通过 name LIKE '张%' 扫描索引。
-
关键变化：存储引擎在遍历索引时，发现索引里刚好有 sex 字段，于是直接在索引层判断 sex = 1 是否成立。
-
只有当 name LIKE '张%' 且 sex = 1 都成立时，才去回表查完整数据。
-
返回给 Server 层。
优势：假设 100 个姓张的人里只有 2 个性别为 1，那么回表次数从 100 次骤降到了 2 次！
通俗理解索引下推
没有索引下推：你在图书馆按拼音（name）找书。只要拼音是“Zhang”开头的，你不管三七二十一，全部抱回阅览室（回表），然后让图书管理员（Server 层）一本本翻开检查是不是你要的特定版本（sex = 1）。
有索引下推P：你在书架前按拼音找书时，发现书脊上刚好印着版本号（sex）。你直接在书架前核对版本号，只有拼音和版本号都对上的书，你才抱回阅览室。
判断是否触发了索引下推
使用 EXPLAIN 分析 SQL 时，如果 Extra 列出现了 Using index condition，就说明触发了索引下推
索引下推适用条件
必须是存储引擎支持的：目前只有 InnoDB 和 MyISAM 支持。
必须是索引中包含的列：下推的条件只能是联合索引中，因为最左前缀失效而“多余”出来的那些列（就像上面例子中的 sex）。
不能用于子查询：ICP 只能用于当前表的 WHERE 条件
总结: 索引下推本质上是一种减少回表次数的优化手段。它把原本由 Server 层做的过滤工作，提前“下推”给了存储引擎层，让存储引擎在遍历索引时顺手把能过滤的条件过滤掉，极大地提升了范围查询时的性能
注意：本文归作者所有，未经作者允许，不得转载
