MySQL常见面试题总结

事务特性

• 原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

• 一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

• 隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这么一种效果：对于任意两个并发的事务T 1和T 2，在事务T 1看来，T 2要么在T 1开始之前就已经结束，要么在T 1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

• 持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

隔离级别

• READ_UNCOMMITTED脏读: 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。任何操作都不加锁。
• READ_COMMITTED读/写提交: 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。
• REPEATABLE_READ可重复读（默认该级别）: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE序列化: 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。读加共享锁，写加排他锁，读写互斥。

为什么选用可重复读当作默认隔离级别？
主从复制。主从复制，是基于bin log复制的。bin log有三种格式：

• statement: 记录的是修改SQL语句
• row: 记录的是每行实际数据的变更
• mixed: statement和row模式的混合

Mysql在5.0版本以前，只支持STATEMENT格式。而这种格式在RC下主从复制是有bug的，因此Mysql将可重复读(Repeatable Read)作为默认的隔离级别。

如下图所示，在主(master)上执行如下事务:

此时在主库中查询：

select * from t;

输出结果：

+---+---+
| c1 |c2
+---+---+
| 2 | 2
+---+---+
1 row in set

而从库中查询，输出结果：

Empty set

这里出现了主从不一致性的问题。原因其实很简单，就是在master上执行的顺序为先删后插，而Binlog中语句的顺序以commit为序。从(slave)同步的是bin log，因此从机执行的顺序和主机不一致。

脏读、幻读、不可重复读

脏读
一个事务读到另一个事务未提交的更新数据。比如银行取钱，事务A开启事务，此时切换到事务B，事务B开启事务–>取走100元，此时切换回事务A，事务A读取的是取走100之后的金额，但此时事务B未提交，若B提交失败，发生余额错误。

幻读
是指当事务不是独立执行时发生的一种现象。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

可重复读
同一个事务中多次执行同一个select, 读取到的数据没有发生改变(一般使用MVCC实现)；RR各级级别要求达到可重复读的标准。

不可重复读
指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。 那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

不可重复度和幻读区别：
不可重复读的重点是修改，幻读的重点在于新增或者删除。

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

多版本并发控制MVCC

上文说的，是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE等成熟的数据库，出于提升并发性能的考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

多版本控制指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，InnoDB是在undo log中实现的，通过undo log可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。

MVCC的实现原理

主要是依赖记录中的：3个隐式字段，undo日志 ，Read View来实现的。

隐式字段

InnoDB存储引擎在数据库每行数据的后面添加了三个字段：

• 6字节的事务ID( DB_TRX_ID )字段: 用来标识最近一次对本行记录做修改(insert|update)的事务的标识符, 即最后一次修改(insert|update)本行记录的事务id。至于delete操作，在innodb看来也不过是一次update操作，更新行中的一个特殊位将行表示为deleted, 并非真正删除。
• 7字节的回滚指针( DB_ROLL_PTR )字段: 指写入回滚段(rollback segment)的 undo log。如果一行记录被更新, 则 undo log包含 重建该行记录被更新之前内容所必须的信息，即指向这条记录的上一个版本（存储于rollback segment里）。
• 6字节的 DB_ROW_ID 字段: 包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。 InnoDB 便是通过这个指针找到之前版本的数据。该行记录上所有旧版本，在 undo 中都通过链表的形式组织。

如果我们的表中没有主键或合适的唯一索引, 也就是无法生成聚集索引的时候, InnoDB会帮我们自动生成聚集索引，但聚集索引会使用DB_ROW_ID的值来作为主键；如果我们有自己的主键或者合适的唯一索引, 那么聚集索引中也就不会包含 DB_ROW_ID 了 。

undo日志

• insert undo log：代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃。
• update undo log：事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除。

为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。

• purge：为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的节点可能是会purge线程清除掉，在事务提交之后可能就被删除丢失）。

Read View(读视图)

是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)。
Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新的老版本。

举例

事务 A 的操作过程为：假设事务 A 对值 x 进行更新之后，该行即产生一个新版本和旧版本。
1、对 DB_ROW_ID = 1 的这行记录加排他锁
2、把该行原本的值拷贝到 undo log中， DB_TRX_ID 和 DB_ROLL_PTR 都不动
3、修改该行的值，更新 DATA_TRX_ID 为修改记录的事务 ID ，将 DATA_ROLL_PTR 指向刚刚拷贝到 undo log 链中的旧版本记录，这样就能通过 DB_ROLL_PTR 找到这条记录的历史版本。如果对同一行记录执行连续的 UPDATE ， Undo Log 会组成一个链表，遍历这个链表可以看到这条记录的变迁。
4、记录 redo log ，包括 undo log 中的修改

又来了个事务2修改person表的同一个记录，将age修改为30岁：
1、在事务2修改该行数据时，数据库也先为该行加锁
2、然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面
3、修改该行age为30岁，并且修改隐藏字段的事务ID为当前事务2的ID, 那就是2，回滚指针指向刚刚拷贝到undo log的副本记录
4、事务提交，释放锁

在innodb中，创建一个新事务的时候，innodb会将当前系统中的活跃事务列表创建一个副本（read view），副本中保存的是系统当前不应该被本事务看到的其他事务id列表。当用户在这个事务中要读取该行记录的时候，innodb会将该行当前的版本号与该read view进行比较。

具体的算法是（可重复读级别）：

假设当前数据行事务ID为 T0 ，read view 中保存的最老的事务id T_min ,最新的事务id 为 T_max,当前进行的事务id 为 T_new。

• 如果 T0 < T_min ,那么该行数据可见。
  因为 T0 在 T_new 事务开始前 已经提交。
• 如果 T0 > T_max ,数据行不可见。根据 DB_ROLL_PTR 指针 找到下一个数据版本，再次进行数据可见性判断。
  因为 T0事务 在 T_new 开始前并不存在，也就是说T0 在T_new 开始后创建。
• 如果 T_min <= T0 <= T_max ，判断T0 是否在read_view 中，如果 不在该行数据可见。如果不可见根据 DB_ROLL_PTR 指针找到下一个 数据版本，再次进行数据可见性判断。

在RR级别，事务在begin/start transaction之后的第一条select读操作后，会创建一个快照(read view)，将当前系统中活跃的其他事务记录记录起来；
在RC级别，事务中每条select语句都会创建一个快照(read view)；
正是因为Read Commited和 Repeatable read的read view 生成方式和时机不同，导致在不同隔离级别下,read committed 总是读最新一份快照数据，而repeatable read 读事务开始时的行数据版本。

MVCC在可重复读级别下解决读的幻读问题：
保证取出的数据不会有后启动的事务中创建的数据。
但不能解决写的幻读问题
在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

当执行select操作时innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了。快照的生成当在第一次执行select的时候，也就是说假设当A开启了事务，然后没有执行任何操作，这时候Binsert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系，因为快照已经生成了，后面的select都是根据快照来的。

当前读

对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁，即使是别的事务提交的数据也可以查询到。假设要update一条记录，但是在另一个事务中已经delete掉这条数据并且commit了，如果update就会产生冲突，所以在update的时候需要知道最新的数据。

快照读

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。

MVCC能解决什么问题？带来的好处是？

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以MVCC可以为数据库解决以下问题：

• 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能；
• 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题。

如何解决幻读

很明显可重复读的隔离级别没有办法彻底的解决幻读的问题，如果我们的项目中需要解决幻读的话也有两个办法：

• 使用串行化读的隔离级别
• MVCC+next-key locks：next-key locks由record locks(索引加锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)

Next-Key锁是行锁和GAP（间隙锁）的合并，行锁可以防止不同事务版本的数据修改提交时造成数据冲突的情况。GAP锁防止别的事务新增（在区间内加gap锁），行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。

MVCC版本号举例

事务1插入数据

begin;-- 获取到全局事务ID
insert into `test_zq` (`id`, `test_id`) values('5','68');
insert into `test_zq` (`id`, `test_id`) values('6','78');
commit;

当执行完以上SQL语句之后，表格中的内容会变成：

对上述表格做删除逻辑，执行以下SQL语句（假设获取到的事务逻辑ID为 3）

begin；--获得全局事务ID = 3
delete test_zq where id = 6;
commit;

执行完上述SQL之后数据并没有被真正删除，而是对删除版本号做改变，如下所示：

修改逻辑和删除逻辑有点相似，修改数据的时候会先复制一条当前记录行数据，同时标记这条数据的数据行版本号为当前是事务版本号，最后把原来的数据行的删除版本号标记为当前是事务。

执行以下SQL语句：

begin;-- 获取全局系统事务ID 假设为 10
update test_zq set test_id = 22 where id = 5;
commit;

执行后表格实际数据应该是：

此时，数据查询规则如下：

• 查找数据行版本号早于当前事务版本号的数据行记录。

也就是说，数据行的版本号要小于或等于当前是事务的系统版本号，这样也就确保了读取到的数据是当前事务开始前已经存在的数据，或者是自身事务改变过的数据

• 查找删除版本号要么为NULL，要么大于当前事务版本号的记录

这样确保查询出来的数据行记录在事务开启之前没有被删除。
根据上述规则，我们继续以上张表格为例，对此做查询操作：

begin;-- 假设拿到的系统事务ID为 12
select * from test_zq;
commit;

执行结果应该是：

这样，同一个事务中，就实现了可重复读。

Mysql主从同步的实现原理

主从同步：当master(主)库的数据发生变化的时候，变化会实时的同步到slave(从)库。

优点

• 主库写，从库读，降低服务器压力；
• 在从服务器进行备份，避免备份期间影响主服务器服务，保证数据安全；
• 当主服务器出现问题时，可以切换到从服务器，提高性能

主从库如何同步

Master主服务器将对数据的操作记录到二进制日志中（Binary log），MySQL将事务串行（交叉执行）的写入二进制日志；
Slave从服务器将二进制日志（Binary log）copy到中继日志当中（Relay log）。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接；
开始binlog 转储过程；如果binlog dump process已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志；
SQL slave thread（SQL从线程）从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。

为什么需要中继日志（Relay log）？

由于网络等原因，Binary log不能全部存到 I/O thread中，所以Relay log（存储在从服务器slave缓存中，开销较小）用来缓存Binary log的事件。

主从同步延时（同一个接口插入后查询的延时）怎么解决？

• 可以通过pt-query-digest工具分析备库上的慢查询日志，找出是查询导致的问题还是写入负载太大导致的问题。如果是查询导致的问题可以看是否可以通过拆分查询等方式来加快速度；如果是负载过大，可以通过预读的方式提高实际重放时的速度。
• 业务持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。
• 服务的基础架构在业务和mysql之间加入memcache或者Redis的cache层。降低mysql的读压力；
• 使用比主库更好的硬件设备作为slave；
• slave主要是读，对数据安全要求没有master高，可以将 sync_binlog 设置为0或者关闭bin log，innodb_flushlog也可以设置为0来提高sql的执行效率。