MySQL 中索引底层实现与最佳左前缀法则
索引结构分类
MySQL 中索引有两种结构,分别为 B+Tree 结构和 HASH结构。
B+Tree 结构:
- 非叶子节点不存储数据,叶子节点存储数据,可以放更多的索引。
- 叶子节点包含所有索引字段。
- 叶子节点用指针连接,提高区间访问的性能。从左到右依次递增,且叶子节点间使用双向指针连接。即叶子节点会维护双向链表。
- B+Tree 中每个节点都是一“页”数据,16K。
HASH 结构:
对索引的 key 进行一次 hash 计算就可以定位出数据存储的位置。
仅能满足 ‘’=’’ 或 ‘‘IN’’ 操作, 不支持范围查询。
可能出现 hash 冲突问题。
BTree
我们知道平衡二叉树的结构,想要搜索一个节点的时间复杂度是 O(lgn),搜索的效率取决于树的高度。
BTree 也叫平衡多路查找树,结构如下。
多节点组合为一个树节点,根据节点上线来确定。
每个节点中存储数据,并且根据数据的大小来进行排序,搜索时在节点的数据区间来查找数据。
基本结构如下
B+Tree 结构
首先我们来分析为什么 MySQL 要使用默认 B+Tree 来存储索引。
B+Tree 是 特殊的 BTree,也是多个数据组成一个节点,根据数据区间来进行查找。
但 MySQL 使用的 B+Tree 与 BTree 有以下区别:
- BTree 叶子节点不会维护节点间的指针,而 B+Tree 会维护叶子节点间的双向指针。
- B+Tree 数据全在叶子节点中,非叶子节点只存储索引。会有索引冗余。非叶子节点不存储数据,非叶子节点中存储的索引数量就越多,层数就会越少。
- BTree 不会冗余索引,非叶子节点存储数据,层数会更多。
MySQL 中的索引实现
读取 B+Tree 结构的流程:
- 将根节点从磁盘中读取到内存。
- 根节点二分查找,查找到区间内的索引值,读取其磁盘空间加载到内存,一层一层查找。
- 将叶节点数据导入内存中,进行二分查找。
InnoDB 引擎普通索引与主键索引的叶子节点的区别:
InnoDB 引擎主键索引的索引叶子节点存储的是索引所在行的其他字段数据。
InnoDB 引擎普通索引的索引叶子节点存储的索引所在行的主键。
回表:从索引文件中查询得到数据的地址,根据地址去数据文件中获取数据。这就叫回表。
InnoDB 引擎中联合索引的数据存储:联合索引会逐个字段比较来进行排序。叶子节点存储主键值,根据主键值回表查询数据。根据此可以了解到最左前缀法则,如果不连续使用索引,缺少前驱索引,那么索引就会无效!
联合索引的使用:
select * 时, 非主键联合索引不能够使用范围查询(可以走索引,查到的数据全部都得回表,若回表的数据太多就不会走索引)。覆盖索引时,可以进行索引字段的范围查找。
select 联合索引字段,主键字段 where 联合索引第一个字段的范围查找。会走索引,因为联合索引中会存储主键字段。
最左前缀法则
最左前缀法则:如果使用联合索引不是按照索引顺序从左到右使用,联合索引就会失效(注:sql中可以将顺序打算,MySQL会进行优化,但不能出现缺少该索引字段的前驱索引字段的情况)。
最后几个小问题
为什么不使用最佳左缀就不能走索引呢?
因为联合索引的底层是根据一个一个字段来进行排序,没有第一个字段的排序结果,无法确定第二个字段的有序性。缺失联合索引中间字段也是同理。
为什么建议 InnoDB 表必须建主键?
InnoDB 的 B+Tree 结构就决定了一定要有主键来组织整张表的结构。若不主动创建主键,那么 InnoDB 会找寻数据不重复的字段设置为主键。若没有找到不重复的字段,InnoDB 会维护一个隐藏列作为主键来组织 B+Tree 结构。减少数据库自己的操作,增大性能。
为什么不建议使用 UUID,而推荐使用自增的整型数据?
有三点理由放弃 UUID。
第一点:UUID 为字符串。在数据比较大小时,要进行类型转换,耗费性能。
第二点:UUID 占用的空间较大。
第三点:UUID 是随机无序的,在插入索引树是,结构变动相对会更加频繁。
为什么推荐使用主键自增?
如果不是自增的数据,B+Tree 为保证顺序性,可能插入的节点不在末尾,树的结构会经历分裂、平衡等变化。
为什么非主键索引结构的叶子节点存储的是主键值?
数据一致性,并且最大成都减少了数据冗余,节省了空间。