数据库索引

一，InnoDB的索引

InnoDB的索引有两类索引，聚集索引(Clustered Index)与普通索引(Secondary Index)。

 

InnoDB的每一个表都会有聚集索引：

(1)如果表定义了PK，则PK就是聚集索引；

(2)如果表没有定义PK，则第一个非空unique列是聚集索引；

(3)否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

 

索引的结构是B+树，这里不展开B+树的细节，说几个结论：

(1)在索引结构中，非叶子节点存储key，叶子节点存储value；

(2)聚集索引，叶子节点存储行记录(row)；

画外音：所以，InnoDB索引和记录是存储在一起的，而MyISAM的索引和记录是分开存储的。

(3)普通索引，叶子节点存储了PK的值；

画外音：

所以，InnoDB的普通索引，实际上会扫描两遍：

第一遍，由普通索引找到PK；

第二遍，由PK找到行记录；

索引结构，InnoDB/MyISAM的索引结构，如果大家感兴趣，未来撰文详述。

 

 

五、总结

(1)InnoDB的索引与行记录存储在一起，这一点和MyISAM不一样；

(2)InnoDB的聚集索引存储行记录，普通索引存储PK，所以普通索引要查询两次；

(3)记录锁锁定索引记录；

(4)间隙锁锁定间隔，防止间隔中被其他事务插入；

(5)临键锁锁定索引记录+间隔，防止幻读；

 

二、hash和Btree

Hash索引

 

所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO，但是innodb和myisam之所以没有采用它，是因为它存在着好多缺点：

1、因为Hash索引比较的是经过Hash计算的值，Hash索引仅仅能满足“=”,“IN”,“<=>”查询，不能使用范围查询

2、每次都要全表扫描

（由于不同索引键存在相同Hash值，所以即使满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果）

3、由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作

(Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算)

4、对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的联合索引的最优前缀，也就是联合索引的前面一个或几个索引键进行查询时，Hash索引无法被利用。

（因为组合索引在计算哈希值的时候是一起计算的）

5、当哈希值大量重复且数据量非常大时，其检索效率并没有Btree索引高的。

 

Btree索引

至于Btree索引，它是以B+树为存储结构实现的。

但是Btree索引的存储结构在Innodb和MyISAM中有很大区别。

在MyISAM中，我们如果要对某张表的某列建立Btree索引的话，如图：

 

 

所以我们经常会说MyISAM中数据文件和索引文件是分开的。

因此MyISAM的索引方式也称为非聚集，Innodb的索引方式称为聚集索引。

因此当我们根据Btree索引去搜索的时候，若key存在，在data域找到其地址，然后根据地址去表中查找数据记录。

 

至于Innodb它跟上面又有很大不同，它的叶子节点存储的并不是表的地址，而是数据

 

 

 

 

我们可以看到这里并没有将地址放入叶子节点，而是直接放入了对应的数据，这也就是我们平常说到的，Innodb的索引文件就是数据文件，

 

那么对于Innodb的辅助索引结构跟主索引也相差很多，如图：

 

 

 

我们可以发现，这里叶子节点存储的是主键的信息，所以我们在利用辅助索引的时候，检索到主键信息，然后再通过主键去主索引中定位表中的数据，这就可以说明Innodb中主键之所以不宜用过长的字段，由于所有的辅助索引都包含主索引，所以很容易让辅助索引变得庞大。

 

 

我们还可以发现：在Innodb中尽量使用自增的主键，这样每次增加数据时只需要在后面添加即可，非单调的主键在插入时会需要维持B+tree特性而进行分裂调整，十分低效。

Btree索引中的最左匹配原则：

Btree是按照从左到右的顺序来建立搜索树的。比如索引是(name,age,sex)，会先检查name字段，如果name字段相同再去检查后两个字段。

所以当传进来的是后两个字段的数据（age，sex），因为建立搜索树的时候是按照第一个字段建立的，所以必须根据name字段才能知道下一个字段去哪里查询。

所以传进来的是（name，sex）时，首先会根据name指定搜索方向，但是第二个字段缺失，所以将name字段正确的都找到后，然后才会去匹配sex的数据。

 

MyISAM和InnoDB都使用B+树来实现索引：

• MyISAM的索引与数据分开存储

• MyISAM的索引叶子存储指针，主键索引与普通索引无太大区别

• InnoDB的聚集索引和数据行统一存储

• InnoDB的聚集索引存储数据行本身，普通索引存储主键

• InnoDB一定有且只有一个聚集索引

• InnoDB建议使用趋势递增整数作为PK，而不宜使用较长的列作为PK

参考

https://blog.csdn.net/u014307117/article/details/47325091#commentBox