数据库基础02 - MySQL, 事务

我就闷着冲冲冲

已于 2022-08-27 14:22:16 修改

阅读量505

点赞数

文章标签：数据库 mysql java

于 2022-08-09 17:30:32 首次发布

本文链接：https://blog.csdn.net/fengbaobaola/article/details/126247702

版权

1. 什么是事务？

事务是逻辑上的一组操作，要么都执行，要么都不执行，是不可分割的。

2. 事务的四大特性（ACID）

（1）原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么全部不起作用。

（2）一致性（Consistency）：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。

（3）隔离性（Isolation）：并发访问数据库时，一个用户的事务不会被其他事务所干扰，个并发事务之间数据库是独立的。

（4）持久性（Durability）：一个事务被提交后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

3. 并发事务会带来哪些问题？

数据库并发会带来脏读、幻读、丢弃更改、不可重复读这四个常见问题，其中，

（1）脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

（2）幻读：它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据。在之后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样。

（3）丢弃更改：如，事务1读取某表中的数据A=20，事务2也读取 A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

（4）不可重复读：与幻读类似。事务T2 读取一个数据，然后事务T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同，即出现了一个事务内两次读取的数据是不一样的情况。

幻读和不可重复读的区别：不可重复读的重点是修改，比如多次读取一条记录发现其中某些列的值被修改；幻读的重点在于新增或删除，比如多次读取一条记录发现记录增多或减少了。

4. 事务的隔离级别有哪些？MySQL默认的隔离级别是？

SQL标准定义了4个隔离级别，

（1）读取未提交：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

（2）读取已提交：允许读取并发事务已提交的数据，可以阻止脏读，但幻读或不可重复读仍有可能发生。

（3）可重复读：对同一字段读取多次的结果都是一样的，可以阻止脏读或不可重复读，但幻读仍有可能发生。

（4）可串行化：最高的隔离级别。和串行化读取的结果一致，所有的事务依次逐个执行，这样事务之间完全不可能产生干扰，可以防止脏读、幻读以及不可重复读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重复读）。

这里需要注意的是：与 SQL 标准不同的地方在于InnoDB 存储引擎在可重复读事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是可重复读已经可以完全保证事务的隔离性要求，即达到了 SQL标准的可串行化隔离级别。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是读取已提交，但是你要知道的是InnoDB 存储引擎默认使用可重复读并不会有任何性能损失。

InnoDB 存储引擎在分布式事务的情况下一般会用到可串行化隔离级别。

5. 锁机制和InnoDB算法

（1）MyISAM和InnoDB存储引擎使用的锁

01 MyISAM：采用表级锁。

02 InnoDB：支持行级锁和表级锁，默认采用行级锁。

（2）表级锁和行级锁对比

01 表级锁：MySQL各存储引擎中锁定粒度最大的一种锁。对当前操作的整张表加锁，实现简单，消耗资源小，加锁快，不会产生死锁。

02 行级锁：MySQL各存储引擎中锁定粒度最小的一种锁。只针对当前操作的行进行加锁，大大减少了数据库操作的冲突，其加锁粒度小，并发度高，但加锁的开销大，枷锁满，会出现死锁。

（3）InnoDB存储引擎的锁的算法

InnoDB随时都可以加锁，但并非随时都可以解锁。具体来说，InnoDB 采用的是两阶段锁定协议，即在事务执行过程中，随时都可以执行加锁操作，但是只有在事务执行 COMMIT 或者 ROLLBACK 的时候才会释放锁，并且所有的锁是在同一时刻被释放。并且，行级锁只在存储引擎层实现，而对于 InnoDB 存储引擎来说，行级锁又分三种，或者说有三种行级锁算法：

01 Record lock：为某行记录加锁。

02 Gap lock：间隙锁，锁定一个范围，不包括记录本身。

03 Next-key lock：结合了 record 和 gap 的加锁算法，其主要目的是为了解决幻读问题。

6. 数据库乐观锁和悲观锁的原理和应用场景

（1）悲观锁

先获取锁，再进行业务操作。一般就是利用类似 SELECT … FOR UPDATE 这样的语句对数据加锁，避免其他事务意外修改数据。当数据库执行 SELECT … FOR UPDATE 时会获取被select中的数据行的行锁，select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

（2）乐观锁

先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过。Java 并发包中的 AtomicFieldUpdater 类似，也是利用 CAS 机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号，来实现乐观锁需要的版本判断。

7. 数据库为什么要进行分库和分表？都放在同一个库或同一张表中不可以吗？

分库和分表的目的在于减小数据库的单库单表负担，提高查询性能，缩短查询时间。

（1）通过分表

可以减少数据库的单表负担，将压力分散到不同的表上，同时因为不同的表上的数据量少了，可以起到提高查询性能、缩短查询时间的作用，此外，可以很大的缓解表锁的问题。分表策略可以归纳为垂直拆分和水平拆分。

（2）库内分表

仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并不能减轻 MySQL 服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括 CPU、内存、磁盘 IO、网络带宽等。

8. 大表优化手段 / 数据库结构优化手段

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，常见的优化措施有，

（1）垂直拆分 / 垂直分区

指数据表列的拆分。例如，⽤户表中既有⽤户的登录信息⼜有⽤户的基本信息，可以将⽤户表拆分成两个单独的表。

优点：可以使得列数据变⼩，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

缺点：主键会出现冗余，会让事务变得更加复杂。

（2）水平拆分 / 水平分区

保持数据表结构不变，通过某种策略存储数据分片，即指数据表行的拆分。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。⽔平拆分可以支撑非常大的数据量。举个例子：可以将用户信息表拆分成多个⽤户信息表。

（3）限定数据的范围

务必禁⽌不带任何限制数据范围条件的查询语句。比如，尽量避免使用 select * 。

（4）读写分离

经典的数据库拆分⽅案，主库负责写，从库负责读。

9. MySQL中有哪些索引？有什么特点？

普通索引：仅加速查询 + 一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入。
唯一索引：加速查询 + 一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。
主键索引：加速查询 + 一张表只能有一个主键索引，不允许重复、不允许为 NULL。
组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即将最常用（频率）作限制条件的列放在最左边，依次递减。
全文索引：查找的是文本中的关键词，主要用于全文检索。
非聚簇索引：数据和索引是分开的，B+ 树叶子节点的 data 域存放的是数据记录的地址。
聚簇索引：数据文件本身就是索引文件，树的叶子节点 data 域保存了完整的数据记录。

10. 既然索引有那么多优点，为什么不对表中的每一列都创建一个索引呢？

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立簇索引，那么需要的空间就会更大。
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

10.1 创建索引的准则

（1）应该创建索引的列

01 在经常需要搜索的列上，可以加快搜索的速度；

02 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

03 在经常需要根据范围查询进行搜索的列上；

04 在经常需要排序的列上；

05 在经常使用 where 子句的列上。

（2）不应该创建索引的列

01 很少使用或参考的列；

02 只有很少数据值或重复值多的列；

03 当某一列的修改性能要求远远高于检索性能时。

11. 索引是如何提高查询速度的？

将无序的数据变成相对有序的数据。

11.1 MySQL中为什么要有事务回滚机制？

在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。当事务已经被提交之后，就无法再次回滚了。

回滚日志作用： 1）能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息； 2) 在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。

12. 数据库如何保持一致性？

分为两个层面来说。

（1）从数据库层面

数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，一致性是目的，原子性、隔离性、持久性是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。

（2）从应用层面

通过代码判断数据库数据是否有效，然后决定回滚还是提交数据。

13. 数据库如何保持原子性？

问题：一个事务在执行过程中，在还没有提交事务之前，如果MySQL 发生了崩溃，要怎么回滚到事务之前的数据呢？

主要是利用 Innodb 的 undo log。 undo log 名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的 SQL语句，他需要记录你要回滚的相应日志信息。例如，

当你 delete 一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert 这条旧数据。

undo log 记录了这些回滚需要的信息，当事务执行失败或调用了 rollback，导致事务需要回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。

回滚日志是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC（多版本的并发控制机制）。

13.1 为什么需要Buffer Pool？

MySQL 的数据都是存在磁盘中的，那么我们要更新一条记录的时候，得先要从磁盘读取该记录，然后在内存中修改这条记录。那修改完这条记录是选择直接写回到磁盘，还是选择缓存起来呢？

当然是缓存起来好，这样下次有查询语句命中了这条记录，直接读取缓存中的记录，就不需要从磁盘获取数据了。

为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

14. 数据库如何保持持久性？

Buffer Pool 是提高了读写效率没错，但是问题来了，Buffer Pool 是基于内存的，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存。

主要是利用Innodb的redo log 重写日志，正如之前说的，MySQL是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再写回到磁盘上。如果此时突然宕机，内存中的数据就会丢失。怎么解决这个问题？简单啊，事务提交前直接把数据写入磁盘就行啊。这么做有什么问题？

只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

重写日志是 Innodb 存储引擎层生成的日志，实现了事务的持久性，主要用于断电故障恢复。

采用redo log的好处？

其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下：