mysql 索引 （上）

本文主要是介绍mysql 索引 （上），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

innodb的索引模型

• • 索引维护

每一个索引在innodb中对应了一棵B+树

假设，我们有一个主键为id的表，表中有字段k，并且k上有索引。
建表语句如下：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中r1到r5的值分别为（100，1）、（200，2）、（300，3）、（400，4）、（500，5）和（600，6）两颗B+树的结构如下图：

可以发现，从叶子结点的内容可以将索引分为主键索引和非主键索引。
主键索引的叶子结点存的是整行数据，而非主键索引叶子结点存的是对应主键的值，主键索引也被称为聚簇索引，非主键索引也被称为二级索引。

根据上面的索引结构，我们来讨论一个问题：基于主键索引和普通索引的查询方式有什么区别？

• 如果语句是 select * from T where id=100，即主键查询的方式，只需要检索由主键构成的这棵索引树
• 如果语句是 select * from T where k=1，即二级索引查询的方式，则需要先检索k这棵索引树找到对应主键值为100，然后再检索主键索引树找到对应的数据，这个过程称为 回表

所以由此可知，基于非主键索引的查询需要多扫描一棵索引树，我们应该最大化的利用主键索引进行查询，避免多次扫描索引树。

索引维护

B+树为了维护索引的有序性，在插入新值的时候需要做必要的维护，以上图为例，如果插入的新的行id值为700，则只需要在r5的记录后面插入一个新的记录。如果新插入的id的值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

而更糟糕的情况是，如果r5所在的数据页已经满了，根据B+树的算法，这个时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程为页分裂，在这种情况下，性能自然会受到影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低50%。

基于索引的维护过程，我们来探讨一个案例：

你可能在一些建表规范里面见到过类似的描述，要求建表语句里一定要有自增主键，当然事无绝对，我们来分析一下哪些场景应该使用自增主键，而哪些场景不应该。

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的：NOT NULL PRIMARY KEY AUTO_INCREMENT。
插入新纪录的时候可以不指定id的值，系统会获取当前id的最大值加一座为下一条记录的id值。
所以，自增主键的插入数据的模式，正符合我们前面提到的递增插入的场景。每次插入一条新纪录，都是追加操作，不涉及挪动其他记录，也就没有触发叶子结点的分裂。
而有业务逻辑的字段做主键，往往不能保证有序的插入，这样会大大降低写入的性能。

处理考虑性能外，我们还可从存储空间的角度来看，假设你的表中确实有一个唯一的字段，比如字符串类型的身份证号码，那应该用身份证号做主键，还是用自增主键呢？

由于每个非主键叶子结点上都是主键的值，如果用身份证号做主键，则每个二级索引的叶子结点占用约20字节，而如果用整形做主键，则只需要4个字节，如果是长整型（bigint）则是8个字节。

显然，主键长度越小，普通索引的叶子结点就越小，普通索引占用的空间就越小。

所以，从性能和存储空间方面考虑，自增主键往往是更合理的选择。

那有没有什么场景适合业务字段直接做主键呢？还是有的，比如有些业务场景的需求是这样的：

1. 只有一个索引
2. 该索引必须是唯一索引

你肯定能发现，这就是典型的KV场景。

由于没有其他的索引，所以也就不用考虑其他索引叶子结点大小的问题了。
这时候我们就要优先考虑上一段提到的“尽量使用主键查询“的原则，直接将这个字段设置为主键，可以避免每次查询到要扫描两棵索引树。

这篇关于mysql 索引 （上）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！