数据库索引

参考:https://www.cnblogs.com/yelongsan/p/9405914.html

参考带图:https://www.wengbi.com/thread_94416_1.html

技术内幕四维图:https://blog.csdn.net/tanliqing2010/article/details/81509539

看起来好像啥都知道,但让你说的时候可能就GG了:

Mysql支持Hash索引和B+树索引两种,具体说说看?

使用索引为什么可以加快数据库的检索速度啊?

为什么说索引会降低插入、删除、修改等维护任务的速度。

因为要保持平衡树

索引的最左匹配原则指的是什么?

Hash索引和B+树索引有什么区别?主流的使用哪一个比较多?InnoDB存储都支持吗?

聚集索引和非聚集索引有什么区别?

1.mysql基本存储是页,页和页是双链表,页中每条记录之间是单链表。

https://mp.weixin.qq.com/s?__biz=MzIxNTQ3NDMzMw==&mid=2247483701&idx=1&sn=bd229dd584f51ef4fe545d44ad8cdbf9&chksm=979688c7a0e101d1b5c752094013b78f5bd50ab905257ba82149d85d35ea07aba1a15b0e52b4&mpshare=1&scene=1&srcid=0409Tn66UYWSWvqEVlOpwGtR&key=6cd553e86912686a47d76f2d900b1b5b388c90b29708f016db3a6e1bcebe032220ba63626095c4298f32cda7d0d7bd11bded2365f05c32e522584dd149b98db0bb8549ef144cdca694665d31d35cfeef&ascene=0&uin=MzAzMjU4NDM3Nw%3D%3D&devicetype=iMac+MacBookPro12%2C1+OSX+OSX+10.12.4+build(16E195)&version=12020810&nettype=WIFI&lang=zh_CN&fontScale=100&pass_ticket=YHEmqDDX8hHkj5FiSVpQvjYqIMBDHHDS2po4mfJe%2BqIXlqwJI%2Bg7aJUZq0%2BDwGJ0

没有用索引我们是需要遍历双向链表来定位对应的页。

建立索引实际上就是建立一颗B+树。

1)B+树是一颗平衡树,如果我们对这颗树增删改的话,那肯定会破坏它的原有结构。

2)要维持平衡树,就必须做额外的工作。正因为这些额外的工作开销,导致索引会降低增删改的速度

2.b+树理解

所有记录节点都是按键值的大小顺序存放在同一层的叶节点中,各叶节点指针进行连接。

https://www.cnblogs.com/wade-luffy/p/6292784.html

插入:涉及到主页和叶子页拆分合旋转

LeafPage满了,一次拆分

LeafPage和IndexPage都满了,两次拆分

旋转发生在LeafPage已经满了、但是其左右兄弟节点没有满的情况下

删除:

B+树使用填充因子(fillfactor)来控制树的删除变化,50%是填充因子可设的最小值。删除根据填充因子的变化来衡量。

填充因子小于50%,这时需要做合并操作。

填充因子,左右兄弟的节点占有整个空间的比例。填充因子:1-空余空间比例。

3.哈希索引理解

在HEAP表中,如果存储的数据重复度很低(也就是说基数很大),对该列数据以等值查询为主,没有范围查询、没有排序的时候,特别适合采用哈希索引

优点:

本质上就是把键值换算成新的哈希值,根据这个哈希值来定位。

如果是等值查询,那么哈希索引明显有绝对优势。

缺点:

哈希索引也没办法利用索引完成排序

不支持最左匹配原则

在有大量重复键值情况下,哈希索引的效率也是极低的---->哈希碰撞问题。

不支持范围查询

InnoDB支持哈希索引嘛:

mysqlInnoDB存储引擎是支持hash索引的,不过我们必须启用,hash索引的创建由InnoDB存储引擎引擎自动优化创建(自适应的),我们干预不了。

4.聚集索引和非聚集索引

1)聚集索引在叶子节点存储的是表中的数据

2)非聚集索引在叶子节点存储的是主键和索引列

3)使用非聚集索引查询出数据时,拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)

4)覆盖索引:非聚合组合索引的一种形式,把要查询出的列和索引是对应的,不做回表操作(要查询出的列在叶子节点都存在!所以,就不用回表了,比如创建了索引(username,age),在查询数据的时候:selectusername,agefromuserwhereusername='Java3y'andage=20。)

5.最左匹配原则

联合索引中,遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了,后续退化为线性查找。

例子:

如有索引(a,b,c,d),查询条件a=1andb=2andc>3andd=4,则会在每个节点依次命中a、b、c,无法命中d。

6.意向锁(表的意向锁):

innodb中有行锁和表锁。

正常情况下,加了行锁,表锁需要查询每一行是否加了行锁,才进行加表锁成功。

这里,innodb有了意向锁的含义,在加行锁的同时加了表的意向锁,此时表锁只需要查询有没有意向锁就好。

https://www.zhihu.com/question/51513268

7.mvcc

数据快照

https://572327713.iteye.com/blog/2438247

可以从回答read_commmit,repeatableread实现原理得到答案:

read_commmit语句级快照,每次读取最新版本的快照;

repeatableread事务级别的快照,表里每个数据行都隐式有版本和过期时间的字段,一个事务来处理,会获取小于等于当前版本号的数据(数据快照)

8.gap锁间隙锁

比如操作5,表中有1和10,会锁住5到1和5到10的区间行锁,防止幻读。

在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。gaplock的机制主要是解决可重复读模式下的幻读问题

总结:

1)尽量选择区分度高的列作为索引,区分度的公式是COUNT(DISTINCTcol)/COUNT(*)。表示字段不重复的比率,比率越大我们扫描的记录数就越少。

2)尽可能的扩展索引,不要新建立索引。比如表中已经有了a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

相关推荐