• 周六. 3月 2nd, 2024

    MySQL面试题1

    root

    3月 20, 2022 #MySQL面试题

    一、 数据三大范式是什么?

    第一范式:每个列都不可以再拆分。

    第二范式:在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分。

    第三范式:在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键。

    二、MySQL有关权限的表都有哪几个?

    MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在MySQL数据库里,由mysql_install_db脚本初始化。

    这些权限表分别为user, db, table_priv, columns_priv和host。

    user: 记录允许连接到服务器的用户账号信息,里面的权限是全局级的。

    db: 记录各个账号在各个数据库上的操作权限。

    table_priv:记录数据表级的操作权限。

    columns_priv: 记录数据列级的操作权限。

    host:配合db权限表对给定主机上数据库级操作权限作更细致的控制。

    三. char和varchar的区别

    char是定长的,varchar是变长的。char插入的长度不足定义长度时,会使用空格填充,而varchar不会做填充。

    char(10)和varchar(10)存储”hello”时,char占用10个字符(5个实际,5个空格),而varchar只会占用5字符。

    char要比varchar的存取速度快,方便存储和查找,但是varchar的占用空间小。

    char适用于存储的数据长度基本一致。不需要空格,eg: 手机号, UUID, 密码加密后的密文。

    varchar适用于数据长度不一定,长度范围变化较大的场景。

    四. 有哪些事务隔离级别,MySQL的事务隔离级别是怎么实现的?

    SQL标准定义了四个隔离级别:

    READ-UNCOMMITTED(读未提交):最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读,幻读或不可重复读。

    READ-COMMITTED(读已提交):允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。

    REPEATABLE-READ(可重复读):对同一字段的多次读取结果是一致的,除非数据被本身事务自己所修改,可以阻止脏话读和不可重复读,但幻读仍有可能发生。

    SERIALIZABLE(可串行化):最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可防止脏读、不可重复读以及幻读。

    MySQL默认的隔离级别是REPEATABLE-READ(可重复读)

    MySQL事务隔离级别是怎么实现的?

    1.读未提交:它是性能最好,也可以说它是最野蛮的方式,因为它压根儿就不加锁,所以根本谈不上什么隔离效果,可以理解为没有隔离。

    2. 串行化: 读的时候加共享锁,也就是其他事务可以并发读,但是不能写。写的时候加排它锁,其他事务不能并发写也不能并发读。

    3.最后说读提交和可重复读。这两种隔离级别是比较复杂的,既要允许一定的并发,又想要兼顾的解决问题。

    4. 实现可重复读

    为了解决不可重复读,或者为了实现可重复读,MySQL采用了MVVC(多版本并发控制)的方式。

    我们在数据库表中看到的一行记录可能实际上有多个版本,每个版本的记录除了有数据本身外,还要有一个表示版本的字段,记为row trx_id, 而这个字段就是使其产生的事务的id,事务ID记为transaction id, 它在事务开始的时候向事务系统申请,按时间先后顺序递增

    按照上面这张图理解,一行记录现有3个版本,每一个版本都记录这使其产生的事务ID,比如事务A的transaction id是100, 那么版本1的row trix_id就是100,同理版本2和版本3。

    在上面介绍读提交和可重复读的时候都提到了一个词,叫做快照,学名叫做一致性视图,这也是可重复读和不可重复读的关键,可重复读是事务开始的时候生成一个当前事务全局性的快照,而读提交则是每次执行语句的时候都重新生成一次快照。

    对于一个快照来说,它能够读到哪些版本数据,要遵循以下规则:

    1. 当前事务内的更新,可以读到;
    2. 版本未提交,不能读到;
    3. 版本已提交,但是却在快照创建后提交的,不能读到;
    4. 版本已提交,但是快照创建前提交的,可以读到;

    利用上面的规则,再返回去套用到读提交和可重复读的两张图上就很清晰了。还是要强调,两者主要的区别就是在快照的创建上,可重复读仅在事务开始时创建一次,而读提交每次执行语句的时候都要重新创建一次。

    5. 并发写的问题

    存在这的情况,两个事务,对同一条数据做修改。最后结果应该是哪个事务的结果呢?肯定要是时间靠后的那个对不对。并且更新之前要先读数据,这里所说的读和上面说到的读不一样,更新之前的读叫做“当前读”,总是当前版本的数据, 也就是多版本中最新一次提交的那版。

    假设事务A执行Update操作,Upate的时候要对所修改的行加行锁,这个行锁会提交之后才释放。而在事务A提交之前,事务B也想update这行数据,于是申请行锁,但是由于已经被事务A占有,事务B是申请不到的,此时,事务B就会一直处于等待状态,直到事务A提交,事务B才能断续执行,如果事务A的时间太长,那么事务B很有可能出现超时异常。如下图所示:

    加锁的过程要分有索引和无索引两种情况,比如下面这条语句

    update user set age=11 where id=1

    id是这张表的主键,是有索引的情况,那么MySQL直接就在索引数中找到了这行,然后干净利落的加上行锁就可以了。

    而下面这条语句

    update user set age =11 where age=10

    表中没有为age字段设置索引,所以,MySQL无法直接定位到这行数据。那怎么呢, 当然也不是加表锁了。 MySQL会为这张表中所有行加行锁,没错,是所有行。但是呢,在加上行锁后,MySQL会进行一遍过滤,发现不满足的行就释放锁,最终只留下符合条件的行。虽然最科只为符合条件的行加了锁,但是这一锁一释放的过程对性能也是影响极大的。所以,如果是大表的话,建议合理设计索引,如果真的出现这种史,那很难保证并发度。

    6. 解决幻读

    上面介绍可重复读的时候,那张图里标示着出现幻读的地方实际上在MySQL中并不会出现,MySQL已经在可重复读隔离级别下解决了幻读的问题。

    前面刚说了并发写问题的解决方法就是行锁,而解决幻读用的也是锁,叫做间隙锁,MySQL把行锁和间隙锁合并在一起,解决了并发写和幻读的问题,这个锁叫做Next-Key锁。

    假设现在表中有两条记录,并且age字段已经添加了索引,两条记录age的值分别为10和30.

    此时,在数据库中会为索引维护一套B+树,用来快速定位行记录。B+索引树是有序的,所以会把这张表的索引分割成几个区间。

    如图所示,分成了3个区间,(负无穷,10], (10, 30], (30,正无穷], 在这3个区间是可以加间隙锁的。

    之后,我用下面的两个事务演示一下加锁过程。

    在事务A提交之前,事务B的插入操作只能等待,这就是间隙锁起得作用。当事务A执行update user set name=”风筝2号“ where age =10; 的时候,由于条件where age=10, 数据库不仅在age=10的行上添加行锁,而且在这条记录的两边,也就是(负无穷,10], (10, 30]这两个区间加了间隙锁,从而导致事务B插入操作无法完成,只能等待事务A提交。不仅插入age=10的记录需要等待事务A提交,age<10, 10<age<30的记录页无法完成,而大于等于30的记录则不受影响,这足以解决幻读的问题了。

    这是有索引的情况,如果age不是索引列,那么数据库会为整个表加上间隙锁。所以,如果是没有索引的话,不管age是否大于等于30, 都要等待事务A提交了才可以成功插入。

    root