摘自:
https://juejin.cn/post/6844903856388718606#heading-4
https://my.oschina.net/xiaoyoung/blog/3046779
仅做个人备份,浏览请看原文
目录
假设我们有一张这样的表,表中有如图2-0
的数据
CREATE TABLE `user` (
`ID` bigint(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(20),
PRIMARY KEY (`ID`),
KEY `idx_name` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码
聚集索引
每个InnoDB表都有一个称为聚集索引的特殊索引,该索引是按照表的主键构造的一棵B+树。
根据示例数据构建如图2-1所示聚集索引:
知识点
- 叶子节点存放了整张表的所有行数据。
- 非叶子节点并不存储行数据,是为了能存储更多索引键,从而降低B+树的高度,进而减少IO次数。
- 聚集索引的存储在物理上并不是连续的,每个数据页在不同的磁盘块,通过一个双向链表来进行连接。
查找:假设要查找数据项6
- 把根节点由磁盘块0加载到内存,发生一次IO,在内存中用二分查找确定6在3和9之间;
- 通过指针P2的磁盘地址,将磁盘2加载到内存,发生第二次IO,再在内存中进行二分查找找到6,结束。
这里只进行了两次IO,实际上,每个磁盘块大小为4K,3层的B+树可以表示上百万的数据,也就是每次查找只需要3次IO,所以索引对性能的提高将是巨大的。
怎样选择聚集索引
每张InnoDB表有且只有一个聚集索引,那它是怎么选择索引的呢?
- 一般情况,用
PRIMARY KEY
来作为聚集索引。 - 如果没有定义
PRIMARY KEY
,将会用第一个UNIQUE
且NOT NULL
的列来作为聚集索引。 - 如果表没有合适的
UNIQUE
索引,会内部根据行ID值生成一个隐藏的聚簇索引GEN_CLUST_INDEX
。
所以在建表的时候,如果没有逻辑唯一且非空列时,可以添加一个auto_increment的列,方便建立一个聚集索引。
非聚集索引
非聚集索引又叫辅助索引,叶子节点并不包含行记录数据,而是存储了聚集索引键。
根据示例数据(idx_name
索引)构建如图2-2所示辅助索引:
知识点
- 每个表可以有多个辅助索引
- 通过辅助索引查数据时,先查找辅助索引获得聚集索引的主键,然后通过主键索引来查找完整的行记录。
- 通过非主键索引比主键索引查找速度要慢一倍。
查找:获取NAME=Jake
的数据
第一阶段:通过辅助索引查到主键索引的主键
- 把idx_name索引的根节点由磁盘块0加载到内存,发生一次IO,查找到在P2指针中
- 根据P2指针的磁盘地址,加载磁盘块2到内存,发生第二次IO,查找到Jake节点以及它的主键索引9
第二阶段:通过主键索引找到完整的行记录
- 把根节点由磁盘块0加载到内存,发生一次IO,在内存中用二分查找确定9在P3指针中
- 通过指针P3的磁盘地址,将磁盘3加载到内存,发生第二次IO,再在内存中进行二分查找找到9,以及它的行记录