笔记:

大厂必问的MySQL面试题](https://bbs.huaweicloud.com/blogs/323580)

数据库的索引,锁,事务

1.索引

参考资料:B+Tree原理及mysql的索引分析

  1. 索引的本质

    1
    2
    Mysql对索引的定义: 索引是帮助MySQL高效获取数据的数据结构。
    所以从这个定义来说,索引就是一个数据结构

    为什么要有索引:我的理解是,数据库查询是数据库的最主要功能之一,所以我们需要尽可能的快速的查询数据,所以数据库设计着从查询算法的角度进行优化。而有最基本的算法就是顺序查找(这种复杂度为O(n)的算法在数据量很大时显然是很糟糕。)不过除了这种算法,也有更优秀的算法,如二分查找,二叉树查找。

    但是,根据观察会发现,这些算法都是应用于一定的数据结构之上。

例如二分查找要求数据有序,二叉树查找只能应用于二叉查找树上。 但是数据本身的结构是不可能完全满足各种数据结构的。 所以数据之外,数据库还要维护着**==满足特定查找算法==**的数据结构,这种数据结构就是索引

索引的优缺点:

优点:

  • 加快数据查找的速度

  • 为用来排序或者分组的字段添加索引,可以加快分组和排序的速度

  • 加快表与表之间的连接

    缺点:

  • 建立索引需要占用物理空间

  • 会降低表的增删改的效率,因为每次对表记录进行增删改,需要进行动态维护索引,导致增删改时间变长

索引的作用:数据存储在磁盘上的,查询数据时,如果没有索引,会加载所有的数据到内存,依次进行检索,读取的磁盘次数较多。有了索引就不需要加载所有的数据,因为B+树的高度一般在2-4层最多读取2-4次磁盘,查询速度大大提升 2. b-tree(平衡多路查找树)

1
b-tree是为磁盘等外存储设备设计的一种平衡查找树。
  1. 什么时候需要建立索引?
    1. 经常用于查询的字段
    2. 经常用于连接的字段,建立索引,可加快连接的速度
    3. 经常需要排序的字段建立索引,可以加快排序的擦查询速度
  2. 什么情况下不建立索引?
    1. where条件中用不到的字段
    2. 区分度不高字段,如性别
    3. 参与列计算的列
    4. 表记录较少
    5. 经常需要增删改
  3. 索引的数据结构
    索引的数据结构主要有B+树和哈希表,对应的索引分别为B+树索引和哈希索引。InonoDB引擎的索引类型有B+树索引和哈希索引,默认索引类型为B+树索引。
  4. B+树索引
    B+树是基于B树和叶子节点顺序访问指针进行实现,它具有B树的平衡性,并且通过顺序访问指针来提高区间查询的性能
    在B+树中,节点的key从左到右递增排列,如果某个指针的左右邻居key分别是keyi和keyi+1,则该指针指向节点的所有key大于等于keyi且相遇等于keyi+1
    进行查找操作时,首先在根节点进行二分查找,找到==Key==所在的指针,然后递归地在指针所在节点进行查找。直到查找到叶子节点,然后在叶子节点进行能二分查找,找到Key所对应的数据项。
    MySQL数据 库使用最多的索引类型是BTREE索引,底层基于B+树索引结构来实现
  5. 哈希索引
    基于哈希表实现,对于每一行数据,存储引擎会对索引列进行哈希计算得到哈希码,并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的,将哈希码值作为哈希表的Key值,将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就说O(1),一般多用于精确查找

索引的分类

  1. **主键索引:**唯一非空索引,不允许有空值
  2. **唯一索引:**索引列中的值必须是唯一的,当允许为空值。唯一索引和主键索引的区别是:唯一约束可以为Null且可以有多个null值。用途:唯一标识数据库表中的每条记录,主要是用来防止数据库重复插入。创建额外一索引的SQL语句
  3. 组合索引:在表中的多个字段组合上创建索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用时需要遵循最左前缀原则
  4. **全文索引:**只有在MyISAM引擎上才能使用,只能在CAHR,VARCAHR和TEXT类型字段上使用全文索引

什么是最左匹配原则?

如果SQL语句中用到了

2.锁

类型

根据粒度

  1. 表锁,粒度最大的锁,加锁块,开销小,不会出现死锁,但是由于粒度大,造成锁的冲突几率大,并发的性能页低,(Mysql中 MyISAM 储存引擎支持表锁,表锁模式有表共享读锁``表独占写锁
  2. 页锁: 粒度基于表锁和行锁之间,因为页锁是在BDB中支持的一种锁机制,很少提及和使用
  3. 行锁: 粒度最小的锁,开销大,加锁慢,而且会出现死锁,当锁冲突几率低,并发性能高(行锁是InnoDB默认的支持的锁机制,MyISAM不支持行锁,这个也是InnoDB和MyISAM的区别之一)
    • 行锁可以划分为:共享读锁(s锁)排他写锁
    • 当一个事务对一条数据行加了S锁,当前事务不能修改该行数据只能执行读操作,其他事务只能对该行加S锁,不能加X锁
    • 当一个事务对一行数据加了X锁,该事务对刚行能进行读和写操作,其他事务对该行既不能读也不能写

根据使用方式

  1. 共享锁
  2. 排他锁

根据思想

悲观锁和乐观锁是在很多框架都存在的一种思想,不要狭义地认为它们是某一种框架的锁机制

  1. 乐观锁
  2. 悲观锁

3.事务

事务是作为一个单元的一组有序的数据库操作,如果组中所有的的操作都成功,那么认为事务成功。即使只有一个操作失败,事务也不成功。如果所有操作完成,事务就提交,其修改将作用与其他数据库进程。如果一个操作失败,则事务回滚,该事务的所有操作的影响都将取消。

特性:

  1. 原子性:要么全部执行,要么都不执行
  2. 一致性/可串性:
  3. 隔离性: 事务正确提交之前,不允许该事务对数据的任何改变提供给其他事务
  4. 持久性:事务正确提交后,其结果将永久保存在数据库中,即使在事务提交后有了其他故障,事务的处理结果也会得到保存

隔离级别:

几个概念:脏读,不可重复读,幻读

  • 脏读:一个事务中读取了一个未提交事务的数据
  • 不可重复读:一个事务的多次查询中返回了不同的结果(在查询间隔,有事务提交数据并修改了)
  • 幻读:事务读取某个范围内的记录时,另一个事务又在该范围插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行,就像产生了幻觉一样。

四个隔离级别

  • read uncommited:所有事务都可以看到其他未提交的执行结果
  • read committed:一个事务只能看到已经提交事务所做的改变,避免脏读
  • repeatable red:可重复读,Mysql的默认事务隔离级别,确保同一事务的多个实例在并发读取数据时,会看到重复的数据行,解决不可重复读问题
  • serializable:通过强制事务排序,使之不可能相互冲突,从而解决幻读