浅入浅出MySQL事务等特性

把数据库生产账号放在新人文档上，谁之过？

7 年前 Reddit 论坛出现了这么一篇帖子，大学毕业后第一天上班的新手程序员，运行了一份数据库测试脚本，直接把整个生产数据库删掉，CTO 让他有多远滚多远。

我是南哥，相信对你通关面试、拿下Offer有所帮助。

敲黑板：本文总结了MySQL事务、主从复制等常见的面试题！

1. MySQL事务

1.1 事务的特性

MySQL事务有四大特性。

1. 原子性(atomicity)：一个事务必须是一个不可分割的最小工作单元，整个事务所有的操作，要么成功提交，要么都失败回滚。
2. 一致性(consistency)：事务总是从一个一致性状态转换为另一个一致性状态。
3. 隔离性(isolation)：一个事务所作出的修改在还没有提交之前，对其他事务来说是不可见的。
4. 持久性(durability)：如果事务进行提交后，其所做的修改必须是永久性的，不会因为系统崩溃而丢失修改。

1.2 事务隔离级别

SQL标准定义了四种隔离级别，较低级别的隔离通常来说系统开销更低些。

1. READ UNCOMMITTED（未提交读）：事务的修改，即使没有提交，对其他事务来说也是可见的。这是最低级别的事务隔离，企业生产中很少使用到。
2. READ COMMITTED（提交读）：事务在未提交前，所做的修改对其他事务是不可见的。这个隔离级别也称为不可重复读，主要是因为两次重复的数据读取，可能会产生两种完全不同的结果。
3. REPEATABLE READ（可重复读）：这个事务隔离级别保证了一个事务多次读取都是同样的结果，能够解决前面两个隔离级别可能产生的不可重复读问题。另外可重复读是MySQL默认的事务隔离级别。
4. SERIALIZABLE（可串行化）：该隔离级别会强制事务串行执行，同时对读取的每一行数据都加上锁，来。通过这种方式可以解决幻读的事务问题，不过可能导致锁竞争问题和大量的SQL超时。

1.3 幻读

并发事务带来的问题主要有四种，可以用上面我们谈到的事务隔离级别来处理。

1. 脏读：一个事务读取到另一个事务未提交的数据。
2. 不可重复读：一个事务多次读取同一数据，另一个事务修改了该数据，导致第一个事务第二次读取数据发现和第一次读取的数据不一致。
3. 幻读：一个事务多次读取同一数据，另一个事务给这些数据插入删除了某些内容，导致第一个事务数据的数量发生改变。
4. 丢失修改：一个事务修改了某个数据，另一个事务与其读取同一数据且原始值都相同，另一个事务修改数据后提交，导致第一个事务的修改操作丢失。

1.4 处理幻读问题

幻读可以采用我提到的SERIALIZABLE（可串行化）隔离级别来解决幻读，事务按顺序执行，也就不会有幻读问题。

MySQL也提供了其他方法来处理幻读问题。

1. 设置间隙锁，在两个索引值之间的数据进行加锁，可以杜绝其他事务在这个范围内对数据数量的影响。
2. next-key锁就是间隙锁和行锁的组合，通过间隙锁锁住区间值、行锁锁住行本身。

1.5 死锁问题

死锁是因为多个事务互相占用对方请求的资源导致的现象，要打破这个问题需要回滚其中一个事务，这样另一个事务就能获得请求资源了，而回滚的事务只需要重新执行即可。

InnoDB引擎目前处理死锁的方法是通过持有行级排他锁的数量来判断，持有最少行级排他锁的事务会进行回滚。

1.6 隔离级别相关命令

MySQL默认隔离级别是可重复读，企业生产一般也是用的这个隔离级别。

查看隔离级别的指令：

select @@tx_isolation

设置隔离级别为可重复读的指令：

set session transaction isolation level repeatable read

2. MySQL主从复制

2.1 主从复制概念

面试官：MySQL主从复制了解吧？

回答这个问题前，大家先思考下MySQL主从复制起到了什么作用。知道技术诞生的缘由，技术原理和步骤的整个逻辑推导就很清晰。

MySQL主从复制把数据库数据同步到多台服务器上，同理就可以把读操作分布到多台服务器上，这对于那些读密集型的系统性能提升是很大的。

数据有了多台服务器的备份，就不怕单点故障。我们只需要快速切换到另一台MySQL服务器即可，减少了数据库宕机的时间。

MySQL主从复制主要是利用了主库的Binary Log二进制文件来进行数据复制。

复制的步骤可以分为三步。

1. 主库根据数据库事务提交的顺序，把数据更改记录到二进制文件Binary Log中。
2. 备库建立TCP/IP连接后通过IO线程获取Binary Log，同时将Binary Log复制到中继日志Relay Log。
3. 备库再读取中继日志Realy Log中的事件，重放到备库的数据里。

如果你现在有两台MySQL，一台版本是03年的MySQL5.0，另一台是18年的MySQL8.0.11。新版本可以作为老版本的从服务器，但反过来是不可行的。MySQL的复制具有向后兼容性，老版本可能无法解析新版本的新特性，甚至复制的文件格式都差异太大。

2.2 二进制文件的日志格式

面试官：那Binary Log日志格式知道有哪些？

MySQL提供了三种二进制日志格式用于主从复制。

1. 基于语句的二进制文件，保存了在MySQL主库所有执行过的数据变更语句，相当于从库需要把主库执行过的SQL都执行一遍。
2. 基于行的二进制文件，会把每条被改变的行记录都作为事件写入到二进制文件中。缺点也很明显，行记录的事件是很多的，可能会导致二进制文件大小过大。这种复制模式通常来说让带宽压力更大些。
3. 混合模式。MySQL能够在以上两种复制模式之间动态切换，默认会使用基于语句的复制方式，如果发现无法被正确复制，就切换成基于行的复制方式。

2.3 二进制文件选择

面试官：知道哪种二进制格式比较好吗？

基于语句的二进制文件，有可能会出现数据不一致的问题。例如某条删除语句SQL要删除10000条数据中的1000条，这条删除语句没有采用order by进行排序。如果主、从服务器存储数据的顺序不一样，就会导致每次执行删除的数据都是不同的。

# 没有排序的删除语句
delete from test where name = 'JavaGetOffer' limit 1000;

# 有排序的删除语句
delete from test where name = 'JavaGetOffer' order by id asc limit 1000;

混合模式的话不确定因素太多，两种复制模式的不断切换可能回导致二进制日志出现不可预测的事件。如果从服务器复制该二进制文件后的数据库状态是混乱无序的，那整个复制的过程就没有意义了。

一般来说选择行的复制会更加稳妥，也更加安全。虽然二进制文件过大会带来带宽压力大和复制较慢的问题，但比起数据安全性来说，显然后者更加重要。

2.4 主从模式的优点

面试官：那MySQL主从模式有什么好处？

大家如果有细看第一个面试官问题就知道上文已经有答案了，我这里再总结下。

1. 对于读密集的应用程序，可以利用MySQL主从模式将读操作负载均衡到多个从服务器上，提高系统的抗压能力。
2. MySQL主从还可以避免单点问题，有效减少数据库宕机的时间。同时多个数据源支持查询保证了数据库的可用性。
3. 另外如果需要对MySQL进行版本升级，可以先对备库进步版本升级，保证查询可用的同时，再慢慢升级其他全部MySQL实例。

2.5 全局事务标识符

面试官：如果把二进制文件丢给从库，从库是不是复制整个文件？

能设计出MySQL的聪明人肯定不会这么傻。如果二进制文件包含了已存在的数据，就会造成数据重复了。

MySQL从库只会复制它本身缺失的最新数据，利用二进制文件里的全局事务标识符(GTID)就可以找到对应的二进制文件具体位置。

主库的每一次事务提交都会被分配一个唯一的全局事务标识符，这个标识由server_uuid和一个递增的事务编号组成。

MySQL从库是根据本身当前全局事务标识符找到二进制文件对应位置才进行复制而不是复制全部。

3. MySQL高级特性

3.1 分区表

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的区域，这样的话就有很多好处。

1. 在执行查询的时候，优化器会根据分区定义过滤不需要查询的分区，这样的话就不需要扫描所有数据
2. 可以把数据分布在不同的物理设备上，高效利用多个硬件设备
3. 在表非常大且业务热点数据是最新表数据的情况下，根据时间进行分区可以快速过滤掉大量无关的历史数据

3.2 分区表的缺点

1. 分区表是根据列进行分区的话，查询那些和分区列无关的数据，需要扫描所有分区表
2. 分区列和SQL的索引列不匹配，也需要扫描所有分区表
3. 当对分区表增删改查时，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销

# 创建表时同时设置分区
CREATE TABLE sales (
    order_date DATETIME NOT NULL,
    -- Other columns omitted
) ENGINE=InnODB PARTITION BY RANGE(YEAR(order_date)) (
    PARTITION P2010 VALUES LESS THAN(2010),
    PARTITION P2011 VALUES LESS THAN(2011),
    PARTITION P2012 VALUES LESS THAN(2012),
    PARTITION pCatchall VALUES LESS THAN MAXVALUE);

3.3 视图

MySQL视图本身是一个虚拟表，不存放任何数据，其实就相当于保存了一条Select语句，把这条Select语句封装成视图。

我举个例子吧。在业务开发中，如果不得不改变MySQL表名，而不想改动代码的表名。可以用视图查询新表名的内容，然后把视图命名为旧表名，这样查询视图也能查询出数据。

CREATE VIEW 新表名 AS
	SELECT * FROM 旧表名

3.4 其他高级特性

MySQL高级特性还包括了存储过程、触发器和事件。

1. 存储过程其实就是在MySQL里写方法函数

例如可以让MySQL执行函数来插入1万条数据

1. 触发器可以让你在SQL语句操作表数据的时候，在SQL语句执行前、执行后触发一些特定操作

例如可以编写触发器，在插入A表数据时，给日志记录B表插入一条日志

1. 事件类似于Linux的定时任务，可以是在某个时候、每隔一个时间间隔执行一段SQL代码。

例如可以创建一个事件每隔一段时间调用下我们定义的一个存储过程

3.5 全文索引

MySQL全文索引类似于ElasticSearch的全文索引。

主要是针对文本内容这种格式的数据，MySQL全文索引会对字段进行分词处理，返回匹配相关的文本内容。

创作不易，不妨点赞、收藏、关注支持一下，各位的支持就是我创作的最大动力❤️