MySQL -- 07 -- MySQL聚集索引和非聚集索引的区别

原文链接：MySQL – 07 – MySQL聚集索引和非聚集索引的区别

---

相关文章：

• MySQL – 01 – Linux上源码包安装MySQL（5.7）

• MySQL – 02 – Linux上源码包安装MySQL（8.0）

• MySQL – 03 – 配置MySQL开机自启

• MySQL – 04 – 解决Access denied for user root@localhost

• MySQL – 05 – 如何设计一个关系型数据库

• MySQL – 06 – MySQL索引优化

• MySQL – 07 – MySQL聚集索引和非聚集索引的区别

• MySQL – 08 – MySQL调优之慢查询日志（8.0）

• MySQL – 09 – MySQL调优之explain工具（8.0）

• MySQL – 10 – MySQL联合索引最左匹配原则

• MySQL – 11 – MySQL存储引擎MyISAM和InnoDB之间的区别（8.0）

• MySQL – 12 – MySQL存储引擎MyISAM和InnoDB在锁方面的区别（8.0）

• MySQL – 13 – MySQL事务的四个特性以及四个隔离级别

• MySQL – 14 – MySQL（InnoDB）当前读和快照读

---

官方文档：Clustered and Secondary Indexes

---

一、聚集索引

• 聚集索引，又称聚簇索引，该索引中键值的逻辑顺序与数据行的物理顺序相同，每个表 (InnoDB) 只能有一个聚集索引

• 聚集索引的选取规则如下

  • 如果存在主键，则选取该主键索引作为聚集索引

  • 如果不存在主键，则选取该表的第一个唯一非空索引作为聚集索引

  • 如果既不存在主键，也不存在合适的唯一键，则 InnoDB 会在内部生成一个隐藏的主键，并选取该主键索引作为聚集索引

• 在 InnoDB 中，聚集索引 B+ 树的叶子节点中除了存放主键信息，还存放了主键对应的行数据，因此我们可以直接在聚集索引中查找到想要的数据

• 聚集索引的优缺点

  • 查找时间较短，但索引占用的存储空间较大

---

二、非聚集索引

• 非聚集索引，又称辅助索引、非聚簇索引，该索引中键值的逻辑顺序与数据行的物理顺序不同，每个表 (InnoDB、MyISAM) 可以有多个非聚集索引

• 在 InnoDB 中，非聚集索引 B+ 树的叶子节点中存放了主键信息，当我们要查找数据时，需要先在非聚集索引中查找到对应的主键，然后再根据主键去聚集索引中查找到想要的数据

  • 如果使用了覆盖索引，则不需要回表，直接通过非聚集索引就可以查找到想要的数据

  • 覆盖索引：是指 select 查询的数据只需要在索引中就能取得，而不必去读取数据行，换句话说就是，查询列要被所建的索引覆盖

• 在 MyISAM 中，非聚集索引 B+ 树的叶子节点中存放了辅助键信息以及指向数据的地址，可以直接通过非聚集索引查找到想要的数据

• 非聚集索引的优缺点

  • 索引占用的存储空间较小，但查找时间较长

---

三、MySQL 的两种存储引擎

• InnoDB

  • 在 InnoDB 中，有且仅有一个聚集索引，其索引和数据 (.ibd 文件) 是一起存放的

  • InnoDB 索引示意图

    

    • InnoDB 聚集索引 B+ 树的叶子节点中除了存放了主键信息，还存放了主键对应的行数据

      • 在查询时，当加载叶子节点的组件进入内存的同时，也加载了对应的行数据

      • 如果使用 where id = 1 这样的条件来查询主键时，则按照 B+ 树的检索算法，即可查找到对应的叶子节点，并获得对应的行数据

    • 若对非聚集索引进行条件筛选，则需要经历两个步骤

      • 第一步，在非聚集索引的 B+ 树中查找到对应的主键

      • 第二步，在聚集索引的 B+ 树中再执行一遍查找操作，最终到达叶子节点，获取对应的行数据

• MyISAM

  • 在 MyISAM 中，只存在非聚集索引，其索引 (.MYI 文件) 和数据 (.MYD 文件) 是分开存储的

  • MyISAM 索引示意图

    

    • MyISAM 使用的均为非聚集索引，非聚集索引的两棵 B+ 树的节点结构完全一致，只是存放的内容不同

      • 主键索引 B+ 树的叶子节点中存放了主键信息以及指向数据的地址，可以直接通过主键索引查找到想要的数据

      • 辅助索引 B+ 树的叶子节点中存放了辅助键信息以及指向数据的地址，可以直接通过辅助索引查找到想要的数据，无须访问主键的索引树