最近偶有雅兴和时间,打算剖析一下mysql的索引机制,由于偶的根基薄弱,能力尚欠,在剖析中可能存在片面和错误,还请指出和谅解,谢谢
注:如果没有特殊说明,一下的索引都是主索引
在mysql中索引是大家关注最多的一个知识点,无论是面试和工作,我们都知道mysql最长用的引擎主要为:InnoDB和MyISAM,索引和数据库引擎是密切相关的。不同的引擎,使用的索引也不一样。InnoDB使用的是聚集索引,MyISAM使用的是非聚集索引,下面就这两种索引做一下介绍:
索引:
聚集索引,数据和索引的数据结构物理上是在一起,比如,如果索引结构是一个树,那么这棵树不仅包含键,也包含数据值,包含完整的数据。
非聚集索引,数据和索引的数据结构物理上不在一起,比如,如果索引结构是一个树,那么这棵树包含键,以及数据所在的地址
索引结构:
InnoDB使用B+树作为索引结构,叶子上存储了索引的数据。MyISAM使用的也是B+树的索引结构,不同的是,其叶子上存储了索引数据所在的物理地址。
其中InnoDB的辅索引,叶子上存储的是主索引的键数据。在用辅索引查找数据时,要遍历两次索引结构,一次为辅助索引的数据结构,另一次为主索引的数据结构。
我们都知道,无论是B+树还是B-树都是B树的变种,为什么不直接使用B树(平衡二叉树)呢?
主要取决于他们不同结构,B+或者B-树都是多路平衡二叉树,索引树的高度为h=log(d)n而B树是一棵平衡二叉树,其高度为h=log(2)n,所以B+或者B-树的树高小于B树,查找时间也就小于B树,比B树更快。并且逻辑上很近的数据,在B树或者红黑树上物理上相差比B+或者B-树远,所以不能很好的利用磁盘或者计算机IO局部性特性,但是B+树或者B-树可以利用此局部性原理,预读几页数据,达到减少IO磁盘的次数,提高性能。
为什么mysql索引使用B+树,而没有选择使用B-树呢?
因为B+树更适用于外存索引,这是由两者的结构决定的,B-树是在每个节点既存储键又储存数据,还存储下一个节点的地址。B+树只存储键和下一个节点的地址,不存数据,这样的话节点所占的空间就会小于B-树节点所占的空间,一般我们设计节点的时候,会把一个节点设计为一个固定大小的页,这样一个页所包含的键和地址的数量会大于B-树的结构。所以B+树的度会远远大于B-树的读,所以搜索速度比B-树要快很多。
联合索引是什么?
联合索引其实是一个有序元组,如同字典一样,举个例子,假如你有一个按姓氏和名字排序的电话簿,首先按姓氏排序,然后相同姓氏的按名字排序,如果你知道姓氏,对查找电话号码非常有用,如果你再知道名字,对查电话号码更有用。如果你只知道名字,对查电话号码几乎没有什么用
mysql索引优化:
1、不要使用过长的键作为主索引的键,因为每个辅助索引都会包含主索引的键,如果过长,那么每个辅助索引结构就会变得很大,占空间
2、主索引的键尽量单调,如果不单调,每次在建立索引的时候,每次插入会引起B+树的调整,性能下降
3、创建索引时,要考虑最左前缀原理
4、创建索引时,要考虑数据量,一般数据量小于2000时,没必要创建索引
5、创建索引时,要考虑索引的选择性,保证字段或者字段的前缀不重复值所占比例在90%以上
6、创建表时,最好指定一个业务无关的自增字段,有利于索引的创建
本文章主要介绍与mysql中InnoDB引擎相关的B+树索引,没有介绍hash索引,全文索引,如果以后有机会,一并奉上