回表查询、覆盖索引

一、回表查询

如果索引列在select所需获得的列中或者根据一次索引查询就能获得记录就不需要回表，如果select所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就是回表。

二、InnoDB聚集索引

聚集索引是一种特殊的索引类型，它将数据行的物理存储顺序与索引键的逻辑顺序保存一致。在InnoDB中，表数据本身就是按照聚集索引组织的，这是与MyISAM等存储引擎的根本区别。数据行存储在索引的叶子节点中，表数据在磁盘上按照聚集索引键值的顺序存储，每个InnoDB表必须有且只有一个聚集索引。

• 聚集索引的确定规则【按以下优先选择】

1. 主键：显示定义的主键作为聚集索引
2. 第一个非空唯一索引：如果没有主键
3. 内部生成的隐藏列：如果以上都没有，InnoDB自动生成6字节的ROW_ID作为聚集索引

• 物理存储结构

聚集索引B+树结构：
┌─────────────────┐
│ 根节点（非叶子） │ ← 存储索引键和指针
├─────────────────┤
│ 中间节点（非叶子） │ ← 存储索引键和指针
├─────────────────┤
│ 叶子节点（存储数据） │ ← 存储完整的行数据
└─────────────────┘

三、二级索引（非聚类索引）的工作原理

在InnoDB中，所有非聚类索引都称为二级索引，其叶子节点不包含完整数据行，而是存储对应的主键值。

-- 查询语句 SELECT * FROM users WHERE name = 'Alice'; -- 执行步骤： -- 1. 在idx_name索引树中查找'Alice' → 获得主键id值 -- 2. 使用该id值到聚集索引中查找完整数据行 ← 这就是"回表查询"

四、覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

实现索引覆盖的方法常有：将被查询的字段，建立到联合索引里去。

create table user ( id int primary key, name varchar(20), sex varchar(5), index(name) )engine=innodb;

当我们使用如下代码时

select id,name from user where name='shenjian';

能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。