MySQL -- 07 -- MySQL聚集索引和非聚集索引的区别
一、聚集索引
-
聚集索引,又称聚簇索引,该索引中键值的逻辑顺序与数据行的物理顺序相同,每个表 (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+ 树的叶子节点中存放了辅助键信息以及指向数据的地址,可以直接通过辅助索引查找到想要的数据,无须访问主键的索引树
-
-
-