Java 八股文 - MySQL

MySQL

1. MySQL 有几种锁？

​ 三种锁的特点

• 表级锁：开销小，加锁快；不会出现死锁；锁定颗粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定颗粒度最小，发生所冲突的概率最低，并发度也最高
• 页面锁：开销和加锁时间介于表所和行锁之间；会出现死锁；锁定颗粒度介于表所和行锁之间，并发度一般

2. MySQL 中有哪些不同的表格？

共有 5 中类型的表格：

• MyISAM
  • MyISAM 是 MySQL 最常用的表格类型之一。它使用独立的数据文件（.MYD）和索引文件（.MYI）存储数据和索引，数据文件仅保持数据，索引文件仅保持索引。
• Heap
  • InnoDB 表格是 MySQL 中另一种常用的表格类型，支持事务处理，同时也具有行锁和外检约束等特性。InnoDB 表格通过聚簇索引（clustered index）来存储数据，而不是使用 MyISAM 的分离索引存储数据
• Merge
  • Memory 表格是存储在内存中的表格，也被称为 HEAP 表格。他被设计用于具有高执行速度和对短暂数据的快速访问场景，但是数据在 MySQL 关闭后会被清除
• INNODB
• ISAM

3. 简述 MySQL 数据库中 MyISAM 和 InnoDB 的区别

MyISAM

• 不支持事物，但是每次查询都是原子的
• 支持表级锁，即每次操作是对整个表加锁
• 存储表的总行数
• 一个 MYISAM 表有三个文件，索引文件、表结构文件、数据文件
• 采用非聚集索引，索引文件的数据域指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性

InnoDB：

• 支持 ACID 的事物，支持事物的四种隔离级别

• 支持行级锁及外检约束，因此可以支持写并发

• 不存储总行数

  • 一个 InnoDB 引擎存储在一个文件空间（共享表空间，表大小不受操作系统限制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空间，表大小收操作系统文件大小限制，一般为 2G），受操作系统文件大小的限制
  • 主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主索引，在访问辅索引；最好使用自增主键，防止插入数据时，为维持 B+ 树结构，文件的大调整

4. MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别

SQL 定义标准的四个隔离级别为：

1. read uncommited：读到未提交数据
2. read committed：脏读，不可重复读
3. repeatable read：可重读
4. serializable：串行事务

5. CHAR 和 VARCHAR 的区别？

5.1 固定长度 & 可变长度

• VARCHAR 类型用于存储可变长度的字符串（最大 65532 个字节（字符））

• CHAR 类型用于存储固定长度的字符串（最大 255 个字节（字符））

5.2 关于 CHAR 类型的创建细节

CHAR 列长度固定为创建表时声明的长度，长度范围值时 1 到 255，当 CHAR 指被存储时，它们被用空格填充到特定长度，检索 CHAR 值时需删除尾随空格。

5.3 在检索和存储方面会 CHAR 和 VARCHAR 会有所不同

6. 主键和候选键有什么区别？

• 表格的每行都由主键唯一标识，一个表只有一个主键

• 主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

二者关系：

主键是候选键的子集。也就是所有的候选键都由成为主键的资格，就看你选谁当主键。

7. myisamchk 是用来做什么的

它用来压缩 MyISAM 表，这减少了磁盘或内存的使用。

MyISAM Static 和 MyISAM Dynamic 有什么区别？

在 MyISAM Static 上的所有字段有固定宽度。动态 MyISAM 表将具有像 TEXT，BLOB 等字段，以适应不同长度的数据类型。

MyISAM Static 在受损的情况下更容易恢复。

8. 如果一个表有一列定义为 TIMESTAMP，将发生什么？

每当行被更改时，时间戳字段讲获取当前时间戳。

列设置为 AUTO INCREMENT 时，如果在表中达到最大值，会发生什么？

它会停止递增，任何进一步的插入都将产生错误，因为秘钥已被使用。

怎样才能找出最后一次插入时分配了那个自动增量？

LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值，并且不需要指定表名称。

9. 你怎么看到为表格定义的所有索引？

索引是用过以下方式为表格定义的：

SHOW INDEX FROM ;

10. LIKE 声明中的 % 和 _ 是什么意思

% 对应于 0 个或更多字符，_ 只是 LIKE 语句中的一个字符

如何在 Unix 和 MySQL 时间戳之间进行转换？

• UNIX_TIMESTAMP 是从 MySQL 时间戳转换为 Unix 时间戳的命令
• FROM_UNIXTIME 是从 Unix 时间戳转换为 MySQL 时间戳的命令

11. 列对比运算符是什么？

在 SELECT 语句的列比较中使用 =、<>、<=、<、>=、>、<<、>>、<=>、AND、OR 或 LIKE 运算符。

12. BLOB 和 TEXT 有什么区别？

BLOB 是一个二进制对象，可以容纳可变数量的数据。TEXT 是一个不区分大小写的 BOLB。

BOLB 和 TEXT 类型之间的唯一区别在于对 BOLE 值进行排序和比较时区分大小写，对 TEXT 值不区分大小写。

13. MySQL_fetch_array 和 MySQL_fetch_object 的区别是什么？

mysql_fetch_array 和 mysql_fetch_object 都是 MySQL 查询结果集中获取一行数据的函数，但它们的返回值和使用方式有所不同。

• MySQL_fetch_array - 将结果行作为关联数组或来自数据库的常规数组返回
• MySQL_fetch_object - 从数据库返回结果行作为对象

总的来说：mysql_fetch_array 返回的是数组，mysql_fetch_object 返回的是对象。

14. MyISAM 表格将在那里存储，并且还提供存储格式？

每个 MyISAM 表格以三种格式存储在磁盘上

• “.frm” 文件存储表定义
• 数据文件具有 “.MYD”(MYData) 扩展名
• 索引文件具有 “.MYI”(MYIndex) 扩展名

15. MySQL 如何优化 DISTINCT？

DISTINCT 在所有列上转换为 GROUP BY，并与 ORDER BY 子句结合使用

---

DISTINCT 的介绍与使用：https://blog.csdn.net/weixin_47498250/article/details/106861000

16. 如何显示前 50 行

在 MySQL 中，使用以下代码查询显示前 50 行：

select * from table_name limit 0, 50;

17. 可以使用多少列创建索引？

任何标准表最多可以创建 16 个索引列。

18. NOW() 和 CURRENT_DATE() 有什么区别？

NOW() 命令用于显示当前年份、月份、日期、小时、分钟和秒。

CURRENT_DATE() 仅显示当前年份，月份和日期。

19. 什么是非标准字符串类型？

1. TINYTEXT
2. TEXT
3. MEDIUMTEXT
4. LONGTEXT

20. 列的字符串类型可以是什么？

字符串类型是：

1. SET
2. BLOB
3. ENUM
4. CHAR
5. TEXT

21. 什么是通用 SQL 函数？

• CONCAT(A, B) - 链接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段
• FORMAT(X, D) - 格式化数字 X 到 D 有效数字
• CURRDATE(), CURRTIME() - 返回当前日期或时间
• NOW() - 将当前日期和时间作为一个值返回
• MONTH(), DAT(), YEAR(), WEEK(), WEEKDAY() - 从日期值中提取给定数据
• HOUR(), MINUTE(), SECOND() - 从时间值中提取给定数据
• DATEDIFF(A, B) - 确定两个日期之间的差异，通常用于计算年龄
• SUBTIMES(A, B) - 确定两次之间的差异
• FROMDAYS(INT) - 将整数天转换为日期值

22. MySQL 支持事务吗？

在缺省模式下，MySQL 是 autocommit 模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，MySQL 是不支持事务的。

但如果你的 MySQL 表类型是使用 InnoDB Tables 或 BDB tables 的话，你的 MySQL 就可以使用事务处理，使用 SET AUTOCOMMIT = 0 就可以使 MySQL 允许在非 autocommit 模式，在非 autocommit 模式下，你必须使用 COMMIT 来提交你的更改，或者用 ROLLBACK 来回滚你的更改。

23. MySQL 里记录货币用什么字段好？

NUMERIC 和 DECIMAL 类型被 MySQL 实现为同样的类型，这在 SQL92 标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱相关的数据。

例如：

salary DECIMAL(9, 2)

在这个例子中，9 代表将被用于存储值的总的小数位数，而 2 代表将被用于存储小数点后的位数。

因此，在这种情况下，能被存储在 salary 列中的值的范围从 -9999999.99 到 9999999.99

24. MySQL 有关权限的表都有哪几个？

MySQL 服务器通过权限表来控制用户对数据库的访问，权限表存放在 MySQL 数据库里，由 MySQL_install_db 脚本初始化。这些权限表分别user, db, table_priv, columns_priv 和 host。

25. MySQL 数据库作发布系统的存储，一天五万条以上的新增，预计运维三年，怎么优化？

1. 设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率。
2. 选择适合的表字段数据类型和存储引擎，适当的添加索引。
3. MySQL 库主从读写分离。
4. 找规律分表，减少单表中的数据量，提高查询速度。
5. 添加缓存机制，比如 memcached, apc 等。
6. 不经常改动的页面，生成静态页面。
7. 书写高效率的 SQL，比如 SELECT * FROM TABLE 改为 SELECT field_1, field_2, field_3 FROM TABLE.

26. 锁的优化策略

1. 读写分离
2. 分段加锁
3. 减少锁持有的时间
4. 多个线程尽量以相同的顺序去获取资源
5. 不能将锁的颗粒度过于细化，不然可能会出现县城的加锁和释放锁次数过多，反而不如一次加一把大锁。

27. 索引的底层实现原理和优化

B+ 树，经过优化的 B+ 树

主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引。

28. 什么情况下设置了索引但无法使用

1. 以 “%” 开头的 LIKE 语句，模糊匹配
2. OR 语句前后没有同时使用索引
3. 数据类型出现隐式转换（如 VARCHAR 不加单引号的话可能会自动转换为 INT 类型）

29. 实践中如何优化 MySQL

最好按照以下顺序优化：

1. SQL 语句及索引的优化
2. 数据库结构的优化
3. 系统配置的优化
4. 硬件的优化

详细可查看：https://mikechen.cc/3305.html

30. 优化数据库的方法

• 选取最合适的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL，例如“省份”、“性别”最好试用 ENUM
• 使用连接（JOIN）来代替子查询
• 事务处理
• 锁定、优化事务处理
• 使用外键，优化锁定表
• 建立索引
• 优化查询语句

31. 简单描述 MySQL 中，索引、主键、唯一索引、联合索引的区别，对数据库的性能有什么影响？

索引（介绍） 是一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针

• 普通索引（由关键字 KEY 或 INDEX 定义的索引）的唯一任务是加快对数据的访问速度
• 唯一索引：普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键词 UNIQUE 把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。
• 主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条数据，使用关键字 PRIMARY KEY 来创建
• 联合索引：索引可以覆盖多个数据列，如像：INDEX(columnA, columnB) 索引，这就是联合索引

利弊索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

32. 数据库中的事务是什么？

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务就认定为失败。

如果所有操作完成，事务则提交，其修改将作用于其他数据库进程。如果一个操作失败，则事务将回滚，该事物所有操作的影响都将取消。

事务特性：

• 原子性：即不可分割性，事务要么全部被执行，要么全部不被执行。
• 一致性或可串性：事务的执行使得数据库从一种正确状态转化成另一种正确的状态
• 隔离性：在事务正确提交之前，不允许把该事物对数据的任何改变提供给任何其它事务
• 持久性：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后，有了其它的故障，事务的处理结果也会得到保存

或者这样理解：

失误就是被绑定在一起作为一个逻辑工作单元的 SQL 语句分组，如果任何一个语句操作失败，那么整个操作就失败，以后操作就会回滚到开启事务操作前的状态，或者是上面的某个节点。为了确保要么执行，要么不执行，就可以使用事务。要讲组语句作为事务考虑，就需要通过 ACID 测试，即原子性、一致性、隔离性和持久性

33. SQL 注入漏洞产生的原因？如何防止？

• SQL 注入产生的原因：程序开发过程中不注意规范书写 SQL 语句和对特殊字符进行过滤，导致客户端可以通过全局变量 POST 和 GET 提交一些 SQL 语句正常执行。

防止 SQL 注入的方式：

• 开启配置文件中的 magic_quotes_gpc 和 magic_quotes_runtime 设置

• 执行 SQL 语句时使用 addslashes 进行 SQL 语句转换

• SQL 语句书写尽量不要省略双引号和单引号

• 过滤掉 SQL 语句中的一些关键字：UPDATE、INSERT、DELETE、SELECT、*

• 提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的

34. 为表中的字段选择合适的数据类型

字段类型优先级：整形 > date, time > enum, char > varchar > blob / text

优先考虑数据类型，其次是日期或二进制类型，最后是字符串类型，同级别的数据类型，应该优先选择占用空间小的数据类型

35. 存储时期

Datatime：以 YYYY-MM-DD HH:MM:SS 格式存储时期时间，精确到秒，占用 8 个字节的存储空间，datatime 类型与时区无关

Timestamp：以时间戳格式存储，占用 4 个字节，范围小 1970-1-1 到 2038-1-19，显示依赖于所指定的时区，默认在第一列行的数据修改时可以自动的修改

Date：（生日）占用的字节数比使用字符串 .datetime.int 存储要小，使用 date 只需要 3 个字节，存储时期月份，还可以利用日期时间函数进行日期间的计算

Time：存储时间部分的数据

注意：不要使用字符串类型来存储日期时间（通常比字符串占用的存储空间小，在进行查找过滤可以利用日期的函数）

使用 int 存储日期时间不如使用 timestamp 类型

36. 对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题：

1. 索引的目的是什么？
   • 快速访问数据表中的特定信息，提高检索速度
   • 创建唯一性索引，保证数据库表中每一行数据的唯一性、
   • 加速表和表之间的连接
   • 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
2. 索引对数据库系统的负面影响是什么？
   • 创建索引和维护索引需要消耗时间，这个时间随着数据量的增加而增加；
   • 索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
   • 当对表进行增、删、改、查的时候也要动态维护，这样就降低了数据的维护速度
3. 为数据表创建索引的原则有哪些？
   • 在最频繁使用的，用以缩小查询范围的字段上建立索引
   • 在频繁使用的、需要排序的字段上建立索引
4. 什么情况下不宜建立索引？
   • 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引
   • 对于一些特殊的数据类型，不宜建立索引，比如文本文件（text）等

37. 解释 MySQL 外连接、内连接和自连接的区别

交叉连接：交叉连接又叫笛卡尔积，它是值不适用任何条件，直接将一个表的所有记录和另一个表中的所有一一匹配

内连接：则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。

外连接：其结果集中不仅包含符合连接的行，而且还会包含左表、右表或两个表中的所有数据行，这三种情况依次称之为左外链接、右外连接和全连接

左外连接：也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的哪些字段值以 NULL 来填充。

右外连接：也称右连接，右表为左表，右表中的所有记录都会出现在结果集中。

左连接和右连接可以互换。MySQL 目前还不支持全外连接。

38. MySQL 中的事务回滚机制概述

事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销

举例说明：

• 要同时更改数据库中两个不同表时，如果他们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了一场而未能修改，此时就只能出现第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕

• 而当你把它们设定为一个事务的时候，当第一个表改完，第二个表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态

这就是所谓事务的回滚

39. SQL 语言包括哪几部分？每部分都有哪些操作关键字？

SQL 语言包括数据定义（DDL），数据操作（DML）、数据控制（DCL）和数据查询（DQL）

数据类型：Create Table, Alter Table, Drop Table, Create / Drop Index 等

数据操作：Select, Insert, Update, Delete

数据控制：Grant, Revoke

数据查询：Select

40. 完整性约束包括哪些？

数据完整性（Data Integrity）是指数据的精确（Accuracy）和可靠性（Reliability）

分为以下四类：

• 实体完整性：规定表的每一行在表是唯一的实体
• 域完整性：是指表中的列必须满则某种特性的数据类型约束，其中约束又包括取值范围、精度等规定
• 参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散
• 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特定的约束条件。用户定义的完整性即是针对某个特定数据库的约束条件，它反映某一具体必须满足的语义要求。

与表有关的约束：包括列约束（NOT NULL（为空约束）和表约束（PRIMARY KEY、FOREIGN KEY、CHECK、UNIQUE））

41. 什么是锁？

需求：

• 数据库是一个多用户使用的公共资源。当多个用户并发地存储数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
  作用
• 加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事物释放锁之前，其它的事务不能对此数据对象进行更新操作。

42. 什么叫视图？游标是什么？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增、删、改、查的操作。视图通常是一个表或者多个表的行或列的子集。对视图的修改不影响基本表。相比多表查询，它使得我们获取数据更容易。

游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不适用游标，但是需要逐条处理数据的时候，游标显得十分重要。

43. 什么是存储过程？用什么来调用？

• 存储过程是一个预编译的 SQL，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。

• 如果其它操作需要执行多次 SQL，使用存储过程比单纯 SQL 语句执行要快。可以用一个命令对象来调用存储过程。

44. 如何通俗地理解三个范式？

三大范式的概念：

• 第一范式(1NF)：每个列都不可以再拆分。
• 第二范式(2NF)：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
• 第三范式(3NF)：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

对三大范式的理解：

• 第一范式：1NF 是对属性的原子性约束，要求属性具有原子性，不可再分解
• 第二范式：2NF 是对记录的唯一性约束，要求记录有唯一标识，即实体的唯一性
• 第三范式：3NF 是对字段冗余的约束，即任何字段不能由其它字段派生出来。

范式化设计的优缺点：

• 优点：可以尽量的减少数据冗余，使得更新快，体积小
• 缺点：对于查询需要多个表进行关联，减少写的效率增加读的效率，更难进行索引优化

反范式化：

• 优点：可以减少表的关联，可以更好的进行索引优化
• 缺点：数据冗余以及数据异常，数据的修改需要更多的成本

45. 什么是基本表？什么是视图？

• 基本表是本身独立存在的表，在 SQL 中一个关系就对应一个表。
• 视图是从一个或多个基本表导出的表。
• 视图本身不独立存储在数据库中，是一个虚表。

46. 试述视图的优点？

• 视图能够简化用户的操作
• 视图使用户能以多种角度看待同一个数据
• 视图为数据库提供了一定程度的逻辑独立性
• 视图能够对机密数据提供安全保护

47. NULL 是什么意思？

• NULL 这个值表示 UNKNOWN（未知），他不表示 “”（空字符串）

• 对 NULL 这个值的任何比较都会产生一个 NULL 值，你不能把任何一个值与一个 NULL 值进行比较，并在逻辑上希望获得一个答案

48. 主键、外键和索引的区别？

定义：

• 主键 - 唯一表示一条记录，不能有重复的，不允许为空
• 外键 - 表的外键是另一个表的主键，外键可以有重复的，可以是空值
• 索引 - 该字段没有重复值，但可以有一个空值

定义：

• 主键 - 用来保证数据的完整性
• 外键 - 用来和其它表建立联系用
• 索引 - 是提高查询排序的速度】

个数：

• 主键 - 主键只能有一个
• 外键 - 一个表的外键可以有多个
• 索引 - 一个表的索引可以有多个

49. 你可以用什么确保表格里的字段只接受特定范围里的值？

使用 CHECK 和 触发器实现实例：

https://blog.csdn.net/pan_junbiao/article/details/86241390

• Check 限制，它在数据库表个里被定义，用来限制输入该列的值。

• 触发器也可以被用来限制数据库表里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能

50. 说说对 SQL 语句优化有哪些方法？

• Where 字句中：where 表之间的连接必须写在其它 where 条件之前，那些可以过滤掉最大数量记录的条件必须写在 where 字句的末尾 .HAVING 最后

• 用 EXISTS 代替 IN、用 NOT EXISTS 代替 NOT IN

• 避免在索引列上使用计算

• 避免在索引列上使用 IS NULL 和 IS NOT NULL

• 对查询进行优化，应尽量避免全表扫描，首先应该考虑在 where 以及 order by 涉及的列上建立索引

• 应尽量避免在 where 字句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

补充1：MySQL 四种事务的区别

https://blog.csdn.net/zc520yzy/article/details/103042945

关于事务

1. 什么是事务

事务是应用程序中一些列严密的操作，所有操作必须成功完成，否则在每个操作所做的所有更改都会被撤销。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

• 事务的结束有两种，当事务中的所有步骤全部成功执行时，事务提交

• 如果其中一个步骤失败，讲发生回滚操作，撤销之前到事务开始时的所有操作。

2. 事务的 ACID

事务具有四个特征：原子性、一致性、隔离性和持久性。这个四个特征简称为 ACID 特性。

1. 原子性：事务是数据库的逻辑工作单位，事务中包含的操作要么是都做，要么都不做
2. 一致性：事务执行的结果必须是使数据库从一个一致性的状态变到另一个一致性的状态。因此当数据库发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
3. 隔离性：一个事物的执行不能其它事务干扰，即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持续性：也成永久性，指一个事物一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

3 MySQL 的四种隔离级别

SQL 的标准定义了 4 类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

3.1 Read Uncommitted（读取未提交内容）

在该隔离级别，所有事物都可以看到其他未提交事物的执行结果。本隔离级别很少用于实际应用，因为它的性能不比其它级别好多少。读取未提交的数据，也被称为脏读（Dirty Read）

3.2 Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。它满足了隔离的简单定义：一个事物只能看见已经提交事物所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事物的其它实例在该实例处理期间可能会有新的 Commit，所以同一 Select 可能返回不同的结果。

3.3 Repeatable Read（可重读）

这是 MySQL 的默认事物的隔离界别，它确保同一事物的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事物又在该范围内插入了新行，当用户再读取该范围的数据行，会发现有新的“幻影”行，InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

3.4 Serializable（可串行化）

这是最高的隔离级别，他通过强制事物排序，使之不可能互相冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

补充2：对 MySQL 三种锁（行级锁、表级锁、页级锁）与间隙锁的介绍，悲观锁与乐观锁的理解与抉择方案

行级锁、表级锁、页级锁、间隙锁

1. 行级锁

   • 描述：行级锁是 MySQL 中颗粒度最小的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁颗粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

   • 其实行级锁和页级锁之间还有其他颗粒度的锁，也就是间隙锁和临键锁。

     InnoDB 有三种行锁的算法：

     • Record Lock（记录锁）：单个行记录上的锁，这个也是我们日常认为的行锁。
     • Gap Lock（间隙锁）：间隙锁，锁定一个范围，但不包括记录本身（只不过它的颗粒度比记录锁的整行更大了，它是锁定了某个范围内的多个行，包括根本不存在的数据）。GAP 锁的目的，是为了防止同一事物的两次当前读，出现幻读的情况。该锁只会在隔离级别是 RR 或者以上的级别内存在。间隙锁的目的是为了让其它事务无法在间隙中新增数据。
     • Next-Key Lock（临键锁）：它是记录锁和间隙锁的结合，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。next-key 锁是 InnoDB 默认的锁。

     上面三种锁都是排它锁（X 锁）

   • next-key lock 的效果相当于一个记录锁加一个间隙锁。当 next-key lock 加在某索引上，则该记录和它前面的区间都被锁定

2. 表级锁

   • 描述：表级锁是 MySQL 中的锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的 MyISAM 与 InnoDB 都支持表级锁定。表级锁定分别为表共享锁（共享锁）与表独占写锁（排他锁）
   • LOCK TABLE my_table_name READ; 用读锁锁表，会阻塞其他事物修改表数据
   • LOCK TABLE my_table_name WRITE; 用写锁锁表，会阻塞其他事物读和写

   MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

   但是在 InnoDB 中如果需要表锁就需要显式地声明了

3. 页级锁

   • 描述：页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级锁冲突少，但速度慢。因此，采取了这种的页级锁，一次锁定相邻的一组记录，BDB 支持页级锁。

按照锁的共享策略来分：共享锁、排他锁、意向共享锁、意向排他锁

共享锁和排他锁在 MySQL 中具体体现就是读锁和写锁：

• 读锁（共享锁）：Shared Locks（S 锁），针对同一份数据，多个读操作可以同时进行而不会互相影响
• 写锁（排他锁）：Exclusive Locks（X 锁），当前写操作没有完成前，它会阻塞其他写锁和读锁
• IS 锁：意向共享锁、Intention Shared Lock。当食物准备在某条记录上加 S 锁时，需要先在表级别加上一个 IS 锁。
• IX 锁：意向排他锁、Intention Exclusive Lock。当事务准备在某记录上加 X 锁时，需要先在表级别加上一个 IX 锁。

​ IS、IX 锁是表级锁，它们的提出仅仅为了在之后加表级别的 S 锁和 X 锁时可以快速判断表中的记录是否被加上锁，以避免用遍历的方式来查看表中有没有上锁的记录。就是说当对一个行加锁之后，如果有打算给行所在的表加一个表锁，必须先看看该表的行有没有被加锁，否则就会出现冲突。IS 和 IX 锁就避免了判断表中行有没有加锁对每一行的遍历。直接查看表有没有意向锁就可以知道表中有没有行锁

从加锁策略上分：乐观锁 / 悲观锁

• 乐观并发控制 / 乐观锁（OCC - Optimistic Concurrency Control / Optimistic Locking）

• 观并发控制 / 悲观锁（PCC - Pessimistic Concurrency Control / Pessimistic Lock）

悲观锁

• 悲观锁理解：

  • 在关系数据库管理系统中，悲观并发控制（悲观锁）是一种并发控制的方法；悲观锁指的是采用一种持悲观消极的态度，默认数据被外界访问时，必然会产生冲突，所以在数据处理的整个过程中都采用加锁的状态，保证在同一时间、只有一个线程可以访问到数据，实现数据的排他性；通常，数据库的悲观锁是利用数据库本身提供的锁机制去实现的

• 悲观锁的实现：

  • 外界要访问某条数据，那它就要首先向数据库申请改数据的锁（某种锁）

  • 如果获取成功，那它就可以操作改数据，在它操作期间，其它客户端就无法操作该数据了

  • 如果获取失败，则代表通一时间已有其它客户端获取了该锁，那就必须等待其他用户释放锁‘

• 优缺点

  • 优点：

    • 适合在写多读少的并发环境中使用，虽然无法维持非常多的性能，但是在乐观锁无法提更好的性能前提下，可以做到数据的安全性

  • 缺点：

    • 加锁会增加系统开销，虽然能保证数据的安全，但是数据的吞吐量低，不适合在读多写少的场景下使用

乐观锁

• 乐观锁的理解
  • 乐观锁是假设认为即使在并发环境中，外界对数据的操作一般是不会造成冲突，所以并不会去加锁（所以乐观锁并不是一把锁），而是在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回冲突信息，让用户决定如何去做下一步，比如说重试，知道成功为止；数据库的乐观锁，并不是利用数据库本身的锁去实现的，可能是利用某种实现逻辑去实现做到乐观锁的思想。
• 乐观锁的实现：
  • 通常乐观锁的实现有两种，但他们内在都是 CAS 思想的设计：
    1. 方式一：使用数据版本（version）实现
       1. 这是乐观锁最常用的一种实现方式。什么事数据版本呢？就是在表中增加一个字段作为该记录的版本标识，比如叫 version，每次对该记录的写操作都会让 version + 1。
       2. 所以当我们读取了数据（包括 version），做出更新，要提交的时候，就会拿取得的 version 取跟数据库中的 version 比较是否一致，如果一致则代表这个时间段，并没有其它的县城也对这个数据进行修改、更新，同时 version + 1；如果不一致，则代表这个时间段，该记录已经被其它线程修改过了，认为是过期数据，返回冲突信息，比如重试（重新读取最新数据，再过更新）
       3. update table set num = num + 1, version = version + 1 where version = #{version} and id = #{id}
    2. 方式二：使用时间戳（timestamp）实现
       1. 表中增加一个字段，名称无所谓，比如叫 update_time，字段类型使用时间戳（timestamp）
       2. 原理和方式一致，也是在更新提交时，检查当前数据库中数据的时间戳和自己更新前取得的时间戳是否一致，如果一致则代表此刻没有冲突，可以提交更新，同时时间戳更新为当前时间，否则就是该时间段有其它线程也更新提交过，返回冲突信息，等待用户下一步动作。
       3. update table set num = num + 1, update_time = unix_timestamp(now()) where id = #{id} and update_time = #{updateTime}
  • 注意：实现乐观锁的时候，我们必须保证 CAS 多个操作的原子性，即获取数据库数据的版本，拿数据库版本与之前拿到的版本的比较，以及更新数据等这几个操作的执行必须是连贯执行，具有符合操作的原子性；所以如果是数据库的 SQL，那么我们就要保证多个 SQL 操作处于同一个事务中
• 优缺点
  • 优点
    • 在读多写少的并发场景下，可以避免数据库加锁的开销，提高 DAO 层的响应性能
    • 其实很多情况下，我们 ORM 工具都带有乐观锁的实现，所以这些方法不一定需要我们认为去实现
  • 缺点
    • 在写多读少的并发场景下，即在写操作的竞争激烈的情况下，会导致 CAS 多次重试，冲突评率过高，导致开销比悲观锁更高

乐观锁和悲观锁的抉择

对乐观锁和悲观锁的抉择主要体现在写-写

在乐观锁的抉择中，我们可以从下面三个因素来考虑：

• 响应速度：如果 DAO 层需要非常高的响应速度，尤其是读多写少的场景下，那我们就可以采用乐观锁方案，降低数据库锁的开销，提供并发量
• 冲突频率：如果冲突频率非常高，那么我们就可以采用悲观锁，保证成功率；毕竟如果冲突频率大，乐观锁会需要多次重试才能重试成功，代价可能会大大增加
• 重试代价：如果重试代价大，比如说重试过程的代码执行非常耗时，那么此时就不建议使用乐观锁了，还不如直接上悲观锁爽快

所以我们知道：

• 在读多写少，CAS 竞争没那么激烈的时候，我们可以采用乐观锁策略，降低数据库加锁的开销，提高数据库并发响应
• 在读多写少的情景下，因为会产生大量的 CAS 竞争，且重试成本比较高的情况下，我们就不建议再采用乐观锁策略了，还是直接使用悲观锁的数据库加锁吧

文中参考资料链接：
https://blog.csdn.net/weixin_47498250/article/details/106861000

https://mikechen.cc/3305.html

https://blog.csdn.net/pan_junbiao/article/details/86241390

https://blog.csdn.net/zc520yzy/article/details/103042945

https://blog.csdn.net/cy973071263/article/details/105188519