MySQL面试题

原创已于 2022-10-08 09:56:24 修改 · 683 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#面试 #职场和发展

于 2022-10-02 15:44:08 首次发布

一、mysql锁的类型有哪些？

可以从锁的属性、粒度和状态来进行分类。

属性：共享锁和排他锁。

粒度：记录锁、行锁、页锁、表锁、间隙锁和临建锁。

状态：意向共享锁和意向排他锁。

二、事务的基本特性和隔离级别？

基本特性：ACID。

A是原子性。指一个事务中的所有操作要么同时成功，要么同时失败。

C是一致性。指数据库总是从一个一致性的状态到另一个一致性的状态。

I是隔离性。指的是一个事务的修改在最终提交前，对其他的事务是不可见的。

D是持久性。指的是事务一旦提交，其修改会永久保存到数据库中。

隔离级别：读未提交、读已提交、可重复读、串行。

读未提交：可以读到其他事务未提交的数据，也叫做脏读。

读已提交：一个事务多次读取的结果可能不一致，也叫不可重复读。oracle默认的隔离级别。

可重复读：一个事务多次读取的结果一致，但是可能导致幻读。mysql默认的隔离级别。

串行：给每一行读取的数据都加锁，可能会导致大量超时和锁竞争的问题。

三、索引的设计原则？

查询更快，占用更少空间。

四、mysql的索引数据结构及特点？

两种索引数据结构：B+树索引和哈希索引。

B+树索引支持范围查询、每次查询所花费的时间基本一致、支持模糊查询。

哈希索引适用于等值查询，查询速度快。

五、怎么处理慢查询？

从三个方面分析原因：是否加载了多余的数据、查询条件没有命中索引、数据量太大。

如果是加载了多余的数据，那么需要重写sql，把多余的数据去掉。

如果是查询条件没有命中索引，那么需要查看语句的执行计划，分析使用索引的情况，修改语句或者索引，使查询尽可能命中索引。

如果是数据量过大，那么需要进行分库分表。单个表超过500w条数据或者超过2G大小就要分。

六、如何保证ACID？

mysql级别有个binlog日志。innoDB级别有undo log和redo log日志。

A由undo log保证。undo log记录了需要回滚的日志信息，事务回滚时撤销已经执行的sql。

C由其他三大特性以及业务逻辑代码保证。

I由MVCC保证。

D由内存和redo log保证。mysql修改数据的时候同时在内存和redo log中记录本次操作，宕机的时候可以从redo log中恢复。

简单提下 redo log 和 undo log。在修改数据的时候，会向 redo log 中记录修改的页内容（为了在数据库宕机重启后恢复对数据库的操作），也会向 undo log 记录数据原来的快照（用于回滚事务）。undo log有两个作用，除了用于回滚事务，还用于实现MVCC。

七、什么是MVCC？

数据库的并发有读读、读写和写写三种，读读并发无冲突，写写并发只能通过加锁解决数据竞争冲突问题，读写是最多的场景，所以使用MVCC（多版本并发控制）解决读写并发冲突，提高并发性能。

并发事务对数据的读操作不会产生并发问题，所以不用解决；
并发事务对数据的读+写，常规操作一般会对要操作的数据加锁来解决并发读+写可能产生的问题，MySQL的InnoDB实现了MVCC来更好地处理读写冲突，可以做到即使存在并发读写，也可以不用加锁，实现"非阻塞并发读"。
并发事务对数据的写操作，只能通过加锁(乐观锁/悲观锁)来解决。

MySQL实现的MVCC，主要是用于在并发读写的情况下，保证 “读” 数据时无需加锁也可以读取到数据的某一个版本的快照，好处是可以避免加锁，降低开销，解决了读写冲突，增大了数据库的并发性能。

当前读：读取的数据是最新版本，读取数据时还要保证其他并发事务不会修改当前的数据，当前读会对读取的记录加锁。比如：select …… lock in share mode（共享锁）、select …… for update | update | insert | delete（排他锁）
快照读：每一次修改数据，都会在 undo log 中存有快照记录，这里的快照，就是读取undo log中的某一版本的快照。这种方式的优点是可以不用加锁就可以读取到数据，缺点是读取到的数据可能不是最新的版本。一般的查询都是快照读，比如：select * from t_user where id=1; 在MVCC中的查询都是快照度。

MVCC是存储了同一条数据的历史版本链，不同的事务可以访问不同的历史版本。MVCC存储在undo log（回滚日志）中。MySQL中MVCC主要是通过行记录中的隐藏字段（隐藏主键 row_id、事务ID trx_id、回滚指针 roll_pointer）、undo log（版本链）、ReadView（一致性读视图）来实现的。

1. 隐藏字段

DB_TRX_ID：修改或创建该数据的事务ID；
DB_ROLL_PTR：回滚指针，指向数据的上一个版本。

2. undo log回滚日志

undo log的主要作用是实现回滚和MVCC。MVCC所需要的版本链就存储在undo log中。

3. Read View

Read View 就是事务进行快照读时，会产生的一个“读视图”，记录并存储了当前活跃事务的id。其主要解决的问题是，事务在读取数据的时候，应该读哪个版本的数据（最新数据还是某个版本的数据）。Read View维护了几个变量：

rw_trx_ids：生成Read View时，当前活跃的事务id数组，从小到大排序。
min_trx_id：rw_trx_ids中最小事务的id。
max_trx_id：生成Read View时，要分配给下一个事务的id。
curr_trx_id：创建Read View的当前事务id。

原理就是将当前事务的id与最新数据的事务id和活跃事务id进行比较，确定读的版本。

MVCC主要是用来解决RU隔离级别下的脏读和RC隔离级别下的不可重复读的问题，所以MVCC只在RC（解决脏读）和RR（解决不可重复读）隔离级别下生效，也就是MySQL只会在RC和RR隔离级别下的快照读时才会生成ReadView。区别就是，在RC隔离级别下，每一次快照读都会生成一个最新的ReadView；在RR隔离级别下，只有事务中第一次快照读会生成ReadView，之后的快照读都使用第一次生成的ReadView。

八、mysql主从同步的原理？

主节点：dump 线程。

从节点：I/O 线程和 sql 线程。

主节点：每当 binlog 日志文件发生改变，dump 线程都会将变更的内容发送给从节点。

从节点：I/O 线程接受主节点发来的binlog日志并将其写到 relay log 中，sql 线程从 relay log 中读取内容修改数据。

这里有个问题，如果主库写入后，没来得及同步给从库，突然宕机了，会导致数据的丢失。所以就有了以下两种机制解决这个问题。

全同步复制：主节点写入binlog日志后，将binlog文件发送给从库，等全部的从库都回复确认后才返回给客户端。（性能较差）

半同步复制：主节点写入binlog日志后，将binlog文件发送给从库，只要有一个从库回复确认就返回给客户端。

十、InnoDB和MyISAM的区别？

事务：InnoDB支持事务，支持事务的四种隔离级别；MyISAM不支持事务，但是每次查询操作都是原子的。

锁：InnoDB支持行锁； MyISAM支持表锁。

行数统计：InnoDB不存储数据的总行数，所以每次统计总行数都需要全表扫描； MyISAM存储了数据的总行数。

存储方式：InnoDB为聚簇索引； MyISAM为非聚簇索引（索引文件的数据域存储了指向数据文件的指针）。

文件系统：InnoDB表可以存储为一个文件空间（共享表空间，表大小不受操作系统限制，一个表可能有多个表文件）或多个文件文件空间（受操作系统限制，每个文件最大2G）； MyISAM表有索引文件，表结构文件和数据文件3个文件。

十一、Mysql索引类型及对数据库性能的影响？

普通索引：允许被索引的数据列有重复值。
唯一索引：不允许被索引的数据列有重复值，保证唯一性。
主键索引：一个表只能有一个主键索引，如果是聚簇索引，那么数据和主键存放在一起。
联合索引：在多个列上建立索引，如INDEX（colA, colB）索引。
全文索引：通过倒排索引判断字段是否包含的问题。（ES做得更好）。

优点：索引提高查询数据的速度；索引可以使用优化隐藏器，提高性能。

缺点：索引会占用额外的空间；在增删改的时候，需要操作索引文件，会降低速度。

十二、Mysql数据库中，什么情况下设置了索引，但无法使用？

没有符合最左前缀原则；
字段进行了隐式数据类型转换；
走索引没有全表扫描效率高。

十三、InnoDB是怎么实现事务的？

InnoDB通过Buffer pool、LogBuffer、undo log和redo log实现事务，先内存再磁盘。以一个update语句为例：

InnoDB会找到要修改的数据页，然后将其放到Buffer pool（内存）中，并修改Buffer pool中的值；
针对update语句生成一个redo log对象，并将其放入LogBuffer中；
针对update语句生成undo log日志，用于回滚；
如果事务提交，将LogBuffer中的redo log对象进行持久化；后续会有其他机制将Buffer pool中的数据持久化；如果回滚，则利用undo log日志进行回滚。