MySQL索引B+Tree

MySQL索引B+Tree

什么是索引：

	相当于一本书的目录，能让我们快速的找到对应的数据。
	索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。
	我们建立了索引后，比如id，我们能够将id与磁盘地址做一个映射，能够加速的找到磁盘地址对应的数据行，而不是全表扫描。

为什么要用索引：

1.索引能极大的减少存储引擎需要扫描的数据量。
2.索引，可以把随机io变成顺序io。
3.索引可以帮助我们在进行分组，排序等操作时，避免使用临时表。

二叉查找树：

右侧倾斜，相当于链表，数据量一大，查询速度会变慢。使用时间戳和主键自增字段做索引是最常见的，
所以数据量一大的时候并不能提高搜索效率，随着表的增长，树越来越倾斜，查询效率越来越低，
所以建议使用平衡二叉查找树或完全平衡二叉查找树。

平衡二叉树：

1.子节点和父节点的高度差不能超过1。
2.如果节点倾斜，会做一个左旋，右旋的操作，有一个平衡因子的概念。

这是就是关键字索引，不足：

	1.它太深了：
		数据的深度决定了它的io操作磁盘的次数，io又是非常耗时的。
	2.它太小了：
		每一个磁盘块（节点/页）保存的数据量太小了，操作系统与磁盘交互的单位是以页为单位，一次交换加载了4k的数据回来。
		没有很好的利用操作磁盘io的数据交换特性（4k，ssd硬盘：4k对齐），
		也没有利用好磁盘io的预读能力（空间局部性原理），
		从而带来的频繁的io操作。

备注： oracle从早年的面向记录后来转向面向数据块，
数据获得极大提升，后来又把备份以及恢复功能也改成了面向数据块，
从而实现了FlashBack以及后来其他的高级功能

多路平衡查找树，B-Tree(二三树)：

绝对平衡树

	解决：
		1.路数越多，高度越低
  		2.比如一个节点定义4kb，比如一个4个字节的int类型的索引，我们一次可以拿到1024个关键字拿回来，就可以解决了数据存储量小的问题。
  		（备注：mysql的一个页（节点）大小的定义是16kb，很好的利用了预读能力。mysql最小单位是页（磁盘块））

mysql B+Tree:

	1.B+树节点关键字搜索采用左闭合区间（<= 包含边界）	。
	2.B+树非叶子节点不保存数据相关信息，只保存关键字和叶子节点的引用。
	3.B+树关键字对应的数据保存在叶子节点中。
		优点：节省空间（节点中只保存关键字，不保存数据了）。
	4.B+树叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系。
		优点：做排序（线性指向下一个节点的头，形成一个链表。周围查找优势）

为什么选用B+Tree:

	1.B+树是B树的升级版（多路平衡查找树），它拥有B树的优势
	2.B+树的扫库扫表能力更强，（B树也相当于随机io,B+树叶子节点均为索引）
	3.B+树的磁盘读写能力更强。（内部节点只保存具体数据的指针，空间利用的更好，内部节点的空间更小）
	4.B+树的排序能力更强。
	5.B+树的查询效率更加稳定。

和B树不同于，B树把数据保存在节点上，B+树内部节点存储指针，B树io操作只需要找到就可以直接返回，
B+树采用闭合区间，需要根据顺序查找到指针找到对应的具体数据，
比如一亿条数据，B+树的查询效率是差不多的，因为可能需要查询几千上万层才能找到数据，
而B树不一样，运气好第二层就找到了，运气不好可能需要找到一万层，效率不稳定。

MySQL B+Tree索引体系形式-Myisam：

	生成三个文件：
	1.表定义文件.frm。
	2.数据文件.MYD。
	3.索引文件.MYI。

备注：MYI与MYD关联关系：
MYI B+Tree保存索引磁盘地址，相当于指针，根据这个指针找到MYD对应的数据行。

MySQL B+Tree索引体系形式-innodb:

	生成两个文件：
	1.表定义文件.frm。
	2.数据data文件.ibd。

它将索引和数据行绑定到一起。（聚集索引：数据库表行中数据的物理顺序与键（索引）的顺序相同）,没有索引的话它会自动创建一个6位int型隐藏的索引（在表空间里，是看不到的）

辅助索引：

	辅助索引最后保存一个主键索引的指针，扫完这个Tree再扫描主键索引的Tree。
	1.innodb认为最常用的是主键索引，它会放弃一些东西，宁愿扫描完成一次之后再扫描一次主键索引，这是	innodb设计的初衷。
	2.为了防止数据的变化。

干货：

	1.离散型越高，选择性越好。
	2.最左匹配原则：对索引中关键字进行计算（对比），一定是从左往右依次进行，且不可跳过。
		所有like语句不能使用第一个%，这样就违法了最左匹配原则。
	3.联合索引：单例索引是特殊的联合索引。
		1.经常用的列优先（最左匹配原则）
		2.选择性（离散度）高的列优先（离散度高原则）
		3.宽度小的列优先（最少空间原则）
		4.不能让索引重复。（比如有单列索引，再创建复合索引）
		5.覆盖索引：如果查询列可通过节点中的关键字直接返回，则该索引称之为覆盖索引。
		比如（创建了id索引：select id from user）
		优点：覆盖索引可减少数据库io的操作，将随机io变成顺序io，可提高查询的性能。

总结：

	1.索引列的数据长度能少就少。
		节省空间，能存储的关键字越多，路数越多，深度越短，io的操作就越少。
	2.索引一定不是越多越好，越全越好，一定是最合适的。
		影响插入，删除
	3.匹配列前缀
		like abc% 能用到索引。
		like %abc% ， like %abc 用不到索引。
	4.where条件中not in和<>操作无法使用索引。
	5.匹配范围值，order by group by 也可用到索引 。
		B+树的结构本来就是有序的
	6.多用指定列查询，只返回自己想得到的数据列，少用select *
	7.联合索引中如果不是按照索引最左列开始查询将无法用到索引（最左匹配原则）。
	8.联合索引中最左前列精确条件匹配，后面并范围匹配另外一列可以用到索引。
	9.联合索引中前面范围匹配，后面所有的列都无法使用索引。

亮点：

like abc% 有时候也不会使用索引，在离散性高的时候，
比如：我测试过，通过for循环添加了十万条数据 张三1，张三2，张三3，……
like 张三%是不会使用索引的，like 张三1% 也不会，导致全表扫描。（查看查询状态:explain）
like 张三11111%会使用，因为离散性高了，选择就好了，路数越多，深度越短，io的操作越少。

备注

	查询是否用到索引语句：
	explain select * from company_info where cname like '%小%'

补充：

MySQL另一种索引算法：哈希算法

	只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
	如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；
	当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，
	然后再根据链表往后扫描，直到找到相应的数据；

在范围查找的时候Hash算法就没有任何优势了，且在排序时也并没有什么优势，比如like关键字也属于范围查找。

哈希索引也不支持多列联合索引的最左匹配规则。（前面讲到）。

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

B+树是把值存储在叶子节点上的，形成了一个双向链表，周围查找能力更强，扫表能力就更不用说了。