Mysql索引数据结构

索引的本质

帮助Mysql高效查询的一种数据结构,存储在磁盘中。

Mysql innodb存储引擎索引数据结构有哪些

[1].hash索引
[2].B + tree索引
[3].为什么innodb引擎不让用户手动去控制hash索引？
例如：select * from user where fname= ‘peter’

1. 根据fname=peter计算相应的hash值，找到9527这一行的记录指针。
   2）需要比对值是不是peter。（因为会有hash碰撞,如果不同的value通过一个hash计算达到了相同的hash值，所以需要比较值。）
   3）hash索引，蒋数据记录保存再内存中。内存空间有多少？磁盘有多少？表中所有的数据都加载到内存中，用户不好保证内存是否溢出，并且数据量很大放在内存中也不合适。
   4）由于维护难度大，所以innodb自适应hash索引，如果系统检测到查询可以加速，就会自动适应hash索引，但是不会让用户去控制hash索引。

为什么Mysql的innodb引擎要用B+Tree

1.二叉搜索树

特点： 大的放右边，小的放左边。每个节点下有至多两个节点。
缺点：特殊的一种二叉树，链表数，插叙任意一个节点，需要便利所有的数据。即使最好的打算，也需要便利几乎一半的树。并且树的深度很高。

2.平衡二叉搜索树AVL

演示地址：https://www.cs.usfca.edu/~galles/visualization/AVLtree.html
定义：左子树和右子树都是一个平衡二叉树，并且左子树和右子树之间的高度差不会超过1.（每路节点只有两个节点）。为了保证定义，右左旋和右旋。
缺点：
1）树很深,高度太高，查询一个节点，树的高度决定查询数据的次数
2）每次查询只会获取节点中一个关键字的内容。但是mysql innodb page=16KB， 每次查询获取一个int关键字4byte。经过了大量的io，获取很少的数据，效率很低。

3.B-Tree多路搜索树，多叉平衡树

特点：
1）每个节点可以分出多路节点。
2）每个节点由一个关键字内容，变成了多个关键字内容
3）是一个绝对平衡的树。
4）degree度，等于树的路数。可以定义当前节点最大的关键子数量=路数-1
5）每个节点中的数据包括： 关键字，数据区，子节点的引用
6）区间划分： (负无穷，5) (5,20) (20,正无穷)
优点：
1）通过把一个节点中的关键字数量变多，把AVL瘦高的树，变矮，减少高度。
2）每个节点中的关键字数量变多，每次io能加载的目标数据变多。

4.B+Tree

特点：
1)每个节点上没有了数据区，所有的数据区都放在叶子节点
2)区间划分：[负无穷,1) [1,28) [28,66) [66,正无穷)
3)例如查询1这个节点。
第一层：1,28,66根据区间[1,28) 到第二层，
第二层：1,10,20根据[1,10)到第三层，
第三层：1,8，9获取 数据
io的次数就是整个B+tree的高度。
优点：

1. 全表扫描使用B+tree。在叶子节点中有所有的数据。
2. 叶子节点的数据是有序的。并且一个节点的末尾关键字会指向相邻节点的头关键字，并且是双向循环的。基于索引的扫库，扫表能力更加强。 排序能力更加强。
3. 16kb的page，除以（指针6byte+4int)*3=30 每次io获取的数量比B-Tree数量多.因为B-Tree： （指针6byte+4byte+数据区）*3>30,每次io的数量要少些。
   

B+Tree索引落地到磁盘的过程

myisam存储引擎和innodb简介

MYISAM存储引擎不产生引擎事务，数据插入速度极快，为方便快速插入测试数据，等我们插完数据，再把存储类型修改为InnoDB。

myisam存储引擎

myiasm落地到磁盘的文件有2个：

1. user.myi 假设创建了id主键索引。叶子节点挂在的是数据区的引用
   2)user.myd 根据user.myi获取到数据区的引用，在这里获取数据。
   3)无论是主键索引还是非主键索引都是一样的。

innodb存储引擎

只有一个idb文件。记录了索引的结构，也记录了数据。
1)innodb存储引擎，只有主键索引，对应的叶子节点挂的是行记录数据。
2)其他的辅助索引，叶子节点挂的是当前索引的值和主键的值，然后根据获取到的主键的值，再去根据主键索引的查询获取数据。例如：如果在一颗高度为3的辅助索引中查找数据，首先从辅助索引中获得主键值（3次IO），接着从高度为3的聚集索引（主键索引）中查找以获得整行数据（3次IO），总共需6次IO。一个表上可以存在多个辅助索引。
3)innodb引擎中，只有主键是聚集索引（主键的叶子节点挂在的是行记录），其他索引是非聚集索引（辅助索引的叶子节点挂在的是辅助索引的值和主键的值）。

什么是回表操作

回表操作，通过先去查询了辅助索引获取到了主键的值，再通过主键去查询整个行记录的操作叫做回表。

为什么不建议使用select * from xxx where name=‘xxx’

假设name和id建立了索引。
如果你查询的只是： select id,name from user where name=‘xxx’.可以不进行回表操作。因为辅助索引查询的是当前索引的值name和主键的值id。

什么是索引最左匹配原则

最左匹配原则：从左往右依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用.
示例： 建了一个(a,b,c)的组合索引
1）select * from tb where a=1 and b=2 and c=3;
– abc三个索引都在where条件里面用到了，而且都发挥了作用
2) select * from tb where c=3 and b=2 and a=1;
– where里面的条件顺序在查询之前会被mysql的优化器优化，效果跟上一句一样
3)select * from tb where a=1 and b>2 and c=3;
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
4)select * from tb where a>1 and b=2 and c=3;
– a用到了，b没有使用，c没有使用