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

官方文档:Clustered and Secondary Indexes


一、聚集索引

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

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

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

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

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

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

  • 聚集索引的优缺点

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

二、非聚集索引

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

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

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

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

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

  • 非聚集索引的优缺点

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

三、MySQL 的两种存储引擎

  • InnoDB

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

    • InnoDB 索引示意图

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

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

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

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

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

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

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

  • MyISAM

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

    • MyISAM 索引示意图

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

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

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

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