笔记:
大厂必问的MySQL面试题](https://bbs.huaweicloud.com/blogs/323580)
数据库的索引,锁,事务
1.索引
参考资料:B+Tree原理及mysql的索引分析
索引的本质
1
2Mysql对索引的定义: 索引是帮助MySQL高效获取数据的数据结构。
所以从这个定义来说,索引就是一个数据结构为什么要有索引:我的理解是,数据库查询是数据库的最主要功能之一,所以我们需要尽可能的快速的查询数据,所以数据库设计着从查询算法的角度进行优化。而有最基本的算法就是顺序查找(这种复杂度为O(n)的算法在数据量很大时显然是很糟糕。)不过除了这种算法,也有更优秀的算法,如二分查找,二叉树查找。
但是,根据观察会发现,这些算法都是应用于一定的数据结构之上。
例如二分查找要求数据有序,二叉树查找只能应用于二叉查找树上。 但是数据本身的结构是不可能完全满足各种数据结构的。 所以数据之外,数据库还要维护着**==满足特定查找算法
==**的数据结构
,这种数据结构就是索引
索引的优缺点:
优点:
加快数据查找的速度
为用来排序或者分组的字段添加索引,可以加快分组和排序的速度
加快表与表之间的连接
缺点:
建立索引需要占用物理空间
会降低表的增删改的效率,因为每次对表记录进行增删改,需要进行动态维护索引,导致增删改时间变长
索引的作用:数据存储在磁盘上的,查询数据时,如果没有索引,会加载所有的数据到内存,依次进行检索,读取的磁盘次数较多。有了索引就不需要加载所有的数据,因为B+树的高度一般在2-4层最多读取2-4次磁盘,查询速度大大提升 2. b-tree(平衡多路查找树)
1 | b-tree是为磁盘等外存储设备设计的一种平衡查找树。 |
- 什么时候需要建立索引?
- 经常用于查询的字段
- 经常用于连接的字段,建立索引,可加快连接的速度
- 经常需要排序的字段建立索引,可以加快排序的擦查询速度
- 什么情况下不建立索引?
- where条件中用不到的字段
- 区分度不高字段,如性别
- 参与列计算的列
- 表记录较少
- 经常需要增删改
- 索引的数据结构
索引的数据结构主要有B+树和哈希表,对应的索引分别为B+树索引和哈希索引。InonoDB引擎的索引类型有B+树索引和哈希索引,默认索引类型为B+树索引。 - B+树索引
B+树是基于B树和叶子节点顺序访问指针进行实现,它具有B树的平衡性,并且通过顺序访问指针来提高区间查询的性能
在B+树中,节点的key从左到右递增排列,如果某个指针的左右邻居key分别是keyi和keyi+1,则该指针指向节点的所有key大于等于keyi且相遇等于keyi+1
进行查找操作时,首先在根节点进行二分查找,找到==Key==所在的指针,然后递归地在指针所在节点进行查找。直到查找到叶子节点,然后在叶子节点进行能二分查找,找到Key所对应的数据项。
MySQL数据 库使用最多的索引类型是BTREE索引,底层基于B+树索引结构来实现 - 哈希索引
基于哈希表实现,对于每一行数据,存储引擎会对索引列进行哈希计算得到哈希码,并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的,将哈希码值作为哈希表的Key值,将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就说O(1),一般多用于精确查找
索引的分类
- **主键索引:**唯一非空索引,不允许有空值
- **唯一索引:**索引列中的值必须是唯一的,当允许为空值。唯一索引和主键索引的区别是:唯一约束可以为Null且可以有多个null值。用途:唯一标识数据库表中的每条记录,主要是用来防止数据库重复插入。创建额外一索引的SQL语句
- 组合索引:在表中的多个字段组合上创建索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用时需要遵循最左前缀原则
- **全文索引:**只有在MyISAM引擎上才能使用,只能在CAHR,VARCAHR和TEXT类型字段上使用全文索引
什么是最左匹配原则?
如果SQL语句中用到了
2.锁
类型
根据粒度
- 表锁,粒度最大的锁,加锁块,开销小,不会出现死锁,但是由于粒度大,造成锁的冲突几率大,并发的性能页低,(Mysql中 MyISAM 储存引擎支持表锁,表锁模式有
表共享读锁``表独占写锁
) - 页锁: 粒度基于表锁和行锁之间,因为页锁是在BDB中支持的一种锁机制,很少提及和使用
- 行锁: 粒度最小的锁,开销大,加锁慢,而且会出现死锁,当锁冲突几率低,并发性能高(行锁是InnoDB默认的支持的锁机制,MyISAM不支持行锁,这个也是InnoDB和MyISAM的区别之一)
- 行锁可以划分为:
共享读锁(s锁)
和排他写锁
- 当一个事务对一条数据行加了S锁,当前事务不能修改该行数据只能执行读操作,其他事务只能对该行加S锁,不能加X锁
- 当一个事务对一行数据加了X锁,该事务对刚行能进行读和写操作,其他事务对该行既不能读也不能写
- 行锁可以划分为:
根据使用方式
- 共享锁
- 排他锁
根据思想
悲观锁和乐观锁是在很多框架都存在的一种思想,不要狭义地认为它们是某一种框架的锁机制
- 乐观锁
- 悲观锁
3.事务
事务是作为一个单元的一组有序的数据库操作,如果组中所有的的操作都成功,那么认为事务成功。即使只有一个操作失败,事务也不成功。如果所有操作完成,事务就提交,其修改将作用与其他数据库进程。如果一个操作失败,则事务回滚,该事务的所有操作的影响都将取消。
特性:
- 原子性:要么全部执行,要么都不执行
- 一致性/可串性:
- 隔离性: 事务正确提交之前,不允许该事务对数据的任何改变提供给其他事务
- 持久性:事务正确提交后,其结果将永久保存在数据库中,即使在事务提交后有了其他故障,事务的处理结果也会得到保存
隔离级别:
几个概念:脏读,不可重复读,幻读
- 脏读:一个事务中读取了一个未提交事务的数据
- 不可重复读:一个事务的多次查询中返回了不同的结果(在查询间隔,有事务提交数据并修改了)
- 幻读:事务读取某个范围内的记录时,另一个事务又在该范围插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行,就像产生了幻觉一样。
四个隔离级别
- read uncommited:所有事务都可以看到其他未提交的执行结果
- read committed:一个事务只能看到已经提交事务所做的改变,避免脏读
- repeatable red:可重复读,Mysql的默认事务隔离级别,确保同一事务的多个实例在并发读取数据时,会看到重复的数据行,解决不可重复读问题
- serializable:通过强制事务排序,使之不可能相互冲突,从而解决幻读