MySql复习指东

MySql关系型数据库

1、红黑树,B+,B,二叉树

二叉树可能会退化成链表

红黑树是平衡二叉树的一种,避免退化成链表,但数据规模扩大时,树高会增大,查找效率下降

B树每个节点中有data域,会增加节点的大小,在单次IO吞吐量不变情况,会增加磁盘IO的次数,从而增加耗时;B+的data域存储在叶子结点中,非叶子节点存储key,节点小,IO次数少

B树不适合区间访问,B+树叶子节点之间通过双向指针连接,便于区间访问

image-20210304125100879

2、索引

聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据

非聚簇索引:存储其他索引,叶子节点存储主键,用于回表(回到聚簇索引的B+树寻找元组)

2q05hsflfa

聚簇索引的优势:

  1. 由于行数据和叶子节点存储在一起,同一页中会有多条行数据,访问同一数据页不同行记录时,已经把页加载到了Buffer中,再次访问的时候,会在内存中完成访问,不必访问磁盘。这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了。

3、优化

  • 降低单次查询范围,分多次查询,确定走索引再操作
  • 聚合索引采用最左匹配原则
  • 多个字段排序,可以在多个排序字段上添加联合索引来优化排序语句
  • 去掉不必要的返回字段,不然优化器认为全表扫描优于索引查找

4、ACID

事务是一组逻辑操作,要么都执行,要么都不执行

原子性:事务是不可分割的整体操作,要么都执行,要么都不执行

一致性:事务执行前后,数据保持一致,多个事务对同一个数据读取的结果是相同的

隔离性:各并发事务之间是相互独立的,一个事务不会被其他事务干扰

持久性:事务提交,对数据库的改变是持久的,即使数据库故障也不会影响

5、并发事务带来的影响

脏读:事务A读取到事务B未提交的数据

丢失修改:事务A和事务B同时获取到a=10,A:a=a-1——>B:a=a-2,得到的结果是a=8,丢失了事务A的修改结果

不可重复读:事务A多次读取同一个数据,在这个过程中,事务B对这个数据进行了修改,导致事务前后读取的数据不同

幻读:和不可重复读类似,区别在于幻读的重点是读取到新增加或删除的数据

6、隔离级别

READ-UNCOMMITTED:允许读取未提交的数据变更

READ-COMMITED:允许读取已提交的数据变更

REPEATABLE-READ:对同一字段的多次读取结果一致,除非数据被本身事务修改

SERIALIZABLE:所有事务逐个执行,事务之间完全不干扰

image-20210304150502864

默认采用REPEATABLE-READ隔离级别

为什么说MySql的默认隔离级别是REPEATABLE-READ却可以保证事务的隔离性?

MySql的REPEATABLE-READ已经可以解决脏读,不可重复读的问题,对于幻读采取Next-Key Lock锁解决

7、MVCC是什么?

(MVCC说到底是一种思想,各大数据库实现的方式也不尽相同,你如果不满意数据库的MVCC设计,你也可以在代码层面自己实现乐观锁从而实现多版本的并发控制等等。)

早期数据库都是采用锁机制,但锁会带来性能问题,于是人们尝试优化,MVCC就是对数据库读操作的优化

MVCC的意思用简单的话讲就是对数据库的任何修改的提交都不会直接覆盖之前的数据,而是产生一个新的版本与老版本共存,使得读取时可以完全不加锁。这样读某一个数据时,事务可以根据隔离级别选择要读取哪个版本的数据。过程中完全不需要加锁。

读已提交和可重复读的底层原理就是MVCC

数据库MVCC和隔离级别的关系是什么?

我以为我对Mysql事务很熟,直到我遇到了阿里面试官

8、MVCC的实现

undo log:用于事务回滚和实现多版本

redo log:MySql使用缓存,修改内存,不会立刻修改磁盘,事务中不断产生redo log,提交flush,保存到磁盘中。即使数据库在事务提交后故障,也会根据redo log恢复事务中数据修改

  • InnoDB行记录中除了rowid外,还有trx_id和db_roll_ptr。 trx_id表示最近修改的事务的id;db_roll_ptr指向undo segment中的undo log。在更新和删除操作完成后,新的数据行中的db_rool_ptr指向undo segment中的undo log,从而实现事务回滚和多版本。

ReadView(一致性视图):RC和RR的区别在于ReadView的策略不同

  • up_limit_id:当前已经提交的事务号 + 1,事务号 < up_limit_id ,对于当前Read View都是可见的。
  • low_limit_id:当前最大的事务号 + 1,事务号 >= low_limit_id,对于当前Read View都是不可见的。
  • trx_ids:为活跃事务id列表,即Read View初始化时当前未提交的事务列表。

image-20210304193206614

对于read_committed,每次读取都会生成一个新的ReadView

对于repeatable_read,会复用事务第一次生成的ReadView

9、介绍一下MySql的锁机制

==表锁,行锁;3种行锁;读写锁;意向锁;关于锁的sql语句==

MyISAM——表锁

InnoDB——表锁,行锁(默认)

表锁:粒度大,加锁快,实现简单,触发冲突的概率大,并发度小

行锁:粒度小,加锁慢,减少数据库操作冲突,并发度大

InnoDB的3种行锁算法:

  • record lock:单个行记录上锁
  • gap lock:间隙锁,区间上锁,不包括记录本身
  • next-key lock:record+gap(RR+next-key lock解决幻读问题)

读写锁:

  • S锁(共享锁,读锁),得到S锁的事务只能读,不能写,其他事务可以为数据对象加S锁,但不能加X锁
  • X锁(排他锁,写锁),得到S锁的事务可读写,其他事务不能加S锁,也不能加X锁,直到原始X锁释放

img

不同sql语句对加锁的影响:

  • SELECT ... 语句正常情况下为快照读,不加锁(默认);
  • SELECT ... LOCK IN SHARE MODE 语句为当前读,加 S 锁;
  • SELECT ... FOR UPDATE 语句为当前读,加 X 锁;
  • 常见的 DML 语句(如 INSERT、DELETE、UPDATE)为当前读,加 X 锁;
  • 常见的 DDL 语句(如 ALTER、CREATE 等)加表级锁,且这些语句为隐式提交,不能回滚。

10、RR配合锁机制如何解决幻读?

这里的锁机制指的是next-key lock

==next-key lock=record lock+gap lock==

在事务第一次select * from user where id>1 for update(使用X锁,强制当前读,id是主键)

这个时候根据在B+树上查询到的数据==区间加锁==,这里是(1,+inf),使得后续操作无法插入到这个区间中,从而解决幻读问题。

11、其他

1.InnoDB和MyISAM的区别

  • InnoDB支持行锁,MyISAM仅支持表锁
  • InnoDB支持外键,MyISAM不支持
  • InnoDB支持事务,MySIAM不支持
  • InnoDB支持MVCC,MySIAM不支持

该用谁,不用我多说了吧!

2.三大范式

  • 第一范式:原子性,每个数据表的字段都是不可分割的
  • 第二范式:第一范式基础上,避免部分依赖:主键(A,B,C);D字段只依赖A字段,这就是部分依赖
  • 第三范式:第二范式基础上,避免传递依赖:主键(A);B字段依赖A字段,C字段依赖B字段,即C==>B==>A,这就是传递依赖

12、索引在什么情况下会失效

  • 条件中有or,例如select * from table_name where a = 1 or b = 3
    • 必须所有的or条件都必须是独立索引,才会使用到索引
  • 在索引上进行计算会导致索引失效,例如select * from table_name where a + 1 = 2
  • 在索引的类型上进行数据类型的隐形转换,会导致索引失效,例如字符串一定要加引号,假设 select * from table_name where a = '1' 会使用到索引,如果写成select * from table_name where a = 1 则会导致索引失效。
  • 在索引中使用函数会导致索引失效,例如select * from table_name where abs(a) = 1
  • 在使用like查询时以%开头会导致索引失效
  • 索引上使用!=、<>进行判断时会导致索引失效,例如select * from table_name where a != 1
  • 索引字段上使用 is null/is not null判断时会导致索引失效,例如select * from table_name where a is null

13、面试题

  • Redis中的数据类型有哪些?

  • Redis中的String类型底层是如何实现的?

  • 使用Redis进行数据统计,在高并发的情况下会不会有问题?

  • 数据库的三大范式

  • 数据库事务的特性 ==(4)==

  • 事务是如何实现隔离性的? ==(6,7,10)==

  • 引入MVCC机制是为了实现什么? ==(7,8)==

  • B树和B+树的区别,能不能用二叉搜索树作为数据库的索引? ==(1)==

SQL题目

四个表,学生表(学号,姓名)、成绩表(学号,课程号,成绩)、课程表(课程号、课程名、教师号)、教师表(教师号、教师名)

查出所有平均成绩大于60的学生学号和平均成绩

14、思维导图

如果不清晰,可以下载查看。

MySql

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×