MySQL高级篇之索引分类

索引分类

InnoDB中索引的分类：

1. 聚簇索引

• 聚集索引又叫聚簇索引，每个 InnoDB 表都必须有且只有一个聚簇索引 ，聚簇索引使用 B+Tree 构建，叶子节点存储的数据是整行记录。InnoDB 创建索引的具体规则如下：

  • 在表上定义主键 PRIMARY KEY，InnoDB 将主键索引用作聚簇索引。
  • 如果表没有定义主键，InnoDB 会选择第一个不为NULL的唯一索引（UNIQUE）列用作聚簇索引。
  • 如果主键索引和唯一索引都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID 字段构建聚簇索引。该 ROWID 字段会在插入新行时自动递增。

• 案例

  SELECT * FROM user_innodb WHERE id = 28;
  1

  • InnoDB 的数据和索引存储在一个文件 t_user_innodb.ibd 中。InnoDB 的数据组织方式是聚簇索引。

    1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（第一次磁盘IO）

    2. 将左子树节点加载到内存中，比较16<28<47，向下检索。（第二次磁盘IO）

    3. 检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将改记录返回给客户端。（第三次磁盘IO）

  

2. 非聚簇索引

• 非聚集索引又叫二级索引、辅助索引，除聚簇索引之外的所有索引都称为非聚集索引。在 InnoDB 中，辅助索引中的叶子节点存储的数据是该行的主键值。 在检索时，InnoDB 使用此主键值在聚簇索引中搜索行记录。

• 使用二级索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到聚集索引中检索获得整行记录。

• 案例

  SELECT * FROM t_user_innodb WHERE age=19;
  1

  • 根据在辅助索引树中获取的主键 id，到主键索引树检索数据的过程称为回表查询。磁盘IO数：辅助索引3次+获取记录回表3次