MySQL主键索引与辅助索引的区别

摘要：介绍MySQL主键索引与辅助索引的区别，介绍如何优化回表查询。

  索引是对数据库表中一列或多列值进行排序后的一种数据结构，能够实现快速查找，会影响where查询以及order by排序。它以索引文件的形式存储在磁盘上，包含对数据表中所有记录的引用指针。关于索引键值和行指针这两个非常重要的基本概念，具体来说定义如下：

  索引键值‌：直接存储创建了索引的列的值。
‌
  行指针‌：指向数据行的主键值。在InnoDB中，这通常是一个指向聚簇索引（聚集索引）中对应行的指针。这意味着即使使用非聚集索引查找数据，最终还是要通过这个行指针回到聚集索引中去获取完整的数据行。

  从索引键值类型可以划分MySQL索引为主键索引和辅助索引（二级索引）；从数据物理存储和索引键值逻辑关系可以划分MySQL索引为聚集索引（聚簇索引）和非聚集索引（非聚簇索引）。

  聚集索引：是一种数据存储方式，将数据行与索引（主键字段值）存储到同一棵B+树中，非叶子节点存储索引值和子节点地址，叶子节点存储主键字段值和数据行。找到索引也就找到了数据行，不需要根据主键进行回表查询。在 InnoDB 存储引擎中，主键通常自动成为聚集索引。这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。聚集索引中的key一定得是全局唯一的。

  非聚集索引：是指B+树叶子节点存储的并不是数据本身，而是索引键值和行指针，用于加速特定字段的查询，尤其是在需要频繁访问非主键列时。在 InnoDB 存储引擎中，普通索引、唯一索引和联合索引都是非聚集索引，它们都引用主键作为叶子节点的数据项。

  主键索引和辅助索引：B+Tree的叶子节点如果存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引。在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。
‌

  辅助索引查询过程：通过辅助索引查找数据时，需要先找到叶子节点的主键值，再通过主键值查找整行数据，此过程称为回表查询，它增加了磁盘 I/O 操作次数。

  对于一张InnoDB表，如果定义了主键，则主键索引就是聚簇索引；如果没有定义主键，则自动选择第一个可以唯一标识数据记录的列作为聚簇索引；否则，会创建一个隐藏的列作为聚簇索引，这个列长度为6个字节，类型为长整形。

  例如：

-- 创建辅助索引
CREATE INDEX idx_username ON users(username);
-- 查询username为'john'的用户，需要回表
SELECT username, age FROM users WHERE username = 'john';

  关于数据存储，索引idx_username的叶子节点存储的是username的值和对应行的id值。查询username所在行记录时，先通过idx_username找到id，再通过id在聚集索引中找到整行数据中的username和age。

  主键索引与辅助索引相同点是数据结构都是 B+ tree，区别如下：

名字	叶子节点数据结构	访问方式	唯一性	排序方式	插入、更新或删除记录的开销
主键索引	存放主键字段值和数据行	更快，因为数据已经排序，可以减少磁盘 I/O 操作	唯一	按照聚集索引排序，以加速范围查询	可能需要重新组织整张表，因为数据的逻辑存储顺序与聚集索引相关
辅助索引	存储非主键字段值和行指针，但不包含数据行	需要回表，效率低	允许多个记录具有相同的索引键值	数据不一定按照辅助索引的顺序排序	只更新索引，无需重新组织整张表的数据，较为高效

  简而言之，二者最主要区别是看B+树叶子节点存了什么数据——聚集索引的叶子节点存放主键字段值和所有真实数据，而辅助索引存储的是非主键字段值和行指针，但不包含数据行。温馨提示，许多数据库的文档和网上的博客都说聚集索引是按照顺序物理地存储数据。事实上，聚集索引的存储并不是物理上连续的，而是逻辑上连续的。

  总之，InnoDB引擎创建的主键索引包含主键id、数据库对应行数据和指针并将这些数据存储在B+Tree的叶子结点上。对于辅助索引，索引内容只包含索引键值和行指针，通过回表机制查询数据行。

  关于回表查询优化的题外话：使用覆盖索引（Covering Index）可以避免回表查询，覆盖索引是指索引包含了查询结果集所需的所有列，查询时可以直接从索引中获取数据，而无需回表。覆盖索引不应包含过多列，以免增加索引大小和维护开销。

-- 查询username为'john'的用户，需要回表
SELECT username, age FROM users WHERE username = 'john';

-- 使用覆盖索引优化查询
CREATE INDEX idx_username_age ON users(username, age);
EXPLAIN SELECT username, age FROM users WHERE username = 'john';
-- EXPLAIN结果中的Extra列会显示Using index，表明查询使用了覆盖索引。

  对于Wiener以上的话题，大家又有什么自己的独特见解呢？欢迎在下方评论区留言！