数据库索引
参考: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)的索引,那么只需要修改原来的索引即可。