索引的本质
帮助Mysql高效查询的一种数据结构,存储在磁盘中。
Mysql innodb存储引擎索引数据结构有哪些
[1].hash索引
[2].B + tree索引
[3].为什么innodb引擎不让用户手动去控制hash索引?
例如:select * from user where fname= ‘peter’
- 根据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的高度。
优点:
- 全表扫描使用B+tree。在叶子节点中有所有的数据。
- 叶子节点的数据是有序的。并且一个节点的末尾关键字会指向相邻节点的头关键字,并且是双向循环的。基于索引的扫库,扫表能力更加强。 排序能力更加强。
- 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个:
- 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没有使用