MySQL

真滴book理喻

已于 2024-04-03 11:34:30 修改

阅读量471

点赞数 1

分类专栏： # Java面试八股文文章标签： mysql 数据库 1024程序员节

于 2023-10-22 21:10:34 首次发布

本文链接：https://blog.csdn.net/m0_59749089/article/details/133976617

版权

Java面试八股文专栏收录该内容

15 篇文章 0 订阅

订阅专栏

一.MySQL基础

1.什么是关系型数据库？

关系型数据库就是一种建立在关系模型基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系，这种联系包括一对一、一对多、多对多。

有哪些常见的关系型数据库呢？

MySQL、Oracle、SQL Server、SQLite

2.什么是 SQL？

SQL 是一种用来和数据库打交道的结构化查询语言。例如，通过SQL可以对数据库中的数据进行增删改查等操作。

3.什么是 MySQL？

MySQL 是一种关系型数据库，主要用于持久化存储系统中的一些数据，MySQL 凭借其自身很多的优点被大量使用在各种系统中。

4.MySQL 有什么优点？

开源免费。
社区活跃，生态完善。
兼容性好，支持常见的操作系统和编程语言。
事务支持优秀， MySQL 中 InnoDB 存储引擎默认使用的 REPEATABLE-READ 隔离级别，基本不会有任何性能损失。
支持分库分表、读写分离。

二.MySQL 字段类型

MySQL 字段类型可以简单分为三大类：

数值类型：

整型：TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT
浮点型：FLOAT 和 DOUBLE
定点型：DECIMAL

字符串类型：

CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等。最常用的是 CHAR 和 VARCHAR。

日期时间类型：

YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

DECIMAL 是用于存储精确小数值的数据类型。相比于 FLOAT 和 DOUBLE 等浮点数类型，DECIMAL 能够确保小数点后的位数精确度不丢失，适用于金融、计算税费、精确度要求较高的场景。在 MySQL 中，DECIMAL 通常指定两个参数：第一个参数指定数字的最大长度，第二个参数指定小数点后的位数。

1.MySQL中的 UNSIGNED 修饰符有什么用？

MySQL 中可以使用 unsigned 修饰符修饰整数类型，被修饰的整数不允许存储负值。由于不需要存储负值，所以整数的存储上限会提高一倍。

下面是一个使用 unsigned 修饰符的例子：

CREATE TABLE mytable (
   id INT UNSIGNED,
   name VARCHAR(50)
);

2.CHAR 和 VARCHAR 的区别是什么？

CHAR 和 VARCHAR 都是MySQL中最常用到的字符串类型，两者的主要区别在于：

CHAR 是定长字符串，VARCHAR 是变长字符串。
CHAR 在存储字符串时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储字符串时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。
CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码等。VARCHAR 类型适合存储长度不确定或者长度差异较大的字符串，例如用户名、文章标题等。

Note：

Bcrypt 算法和 MD5 算法都是常见的密码哈希算法，用于对密码进行加密和存储。它们的主要目的是保护用户密码的安全性，使得即使在密码泄露的情况下，攻击者也很难还原出原始密码。

当char存储的字符串长度较短或者长度都差不多的情况下，才能最大程度的利用空间

3.VARCHAR(100) 和 VARCHAR(10) 的区别是什么？

VARCHAR(100) 表示最多存储 100 个字符和 VARCHAR(10) 表示最多存储 10 个字符。
VARCHAR(100) 和 VARCHAR(10) 二者存储相同的字符串，在磁盘上占用的存储空间是一样的。
VARCHAR(100) 在内存中操作时会消耗更多的内存。因为 VARCHAR 类型在内存中保存时，会根据定义的长度来分配相应的内存空间。

4.DECIMAL 和 FLOAT、DOUBLE 的区别是什么？

DECIMAL 是定点数，可以存储精确的小数值；FLOAT、DOUBLE 是浮点数，只能存储近似的小数值。
DECIMAL 用于存储具有精度要求的小数，例如和钱有关的数据。

Note：在 Java 中，MySQL 的 DECIMAL 类型对应的是 Java 类 java.math.BigDecimal。

5.DATETIME 和 TIMESTAMP 的区别是什么？

DATETIME 类型和时区无关，TIMESTAMP 类型和时区有关。
DATETIME 需要耗费 8 个字节的存储空间，TIMESTAMP 只需要使用 4 个字节的存储空间。
因为 DATETIME 需要耗费的存储空间更多，所以 DATETIME 类型比起 Timestamp 能够表示的时间范围更大。

Note：

1.DATETIME 和 TIMESTAMP 表示的时间范围如下

DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

2.关于 DATETIME 和 TIMESTAMP 的时区详解

DATETIME 类型在存储数据时不会进行任何时区转换，也就是说，它存储的时间是你所输入的实际时间，不含有任何时区信息。无论什么时区的客户端访问数据库，返回的DATETIME 字段的值都是相同的。

在将数据保存到数据库时，TIMESTAMP字段会将你存储的本地时间转换成UTC时间进行存储；当你查询TIMESTAMP字段时，MySQL会自动地将其转换回客户端所在时区的时间。这样，在全球不同的时区使用数据库时，面向的都是各自时区的时间，这样在多时区的软件中非常有用。

6.NULL 和 '' 的区别是什么

NULL跟空字符串是两个完全不一样的值，区别如下：

''的长度是 0，不占用空间，而 NULL 是需要占用空间的。
NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。聚合函数 COUNT 的处理方式取决于参数的类型。如果参数是 COUNT(*)，则统计记录数包括 NULL 值；如果参数是某个字段名 COUNT(列名)，则会忽略 NULL 值。
查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULL 来判断，而不能使用 =、!=、 <、> 之类的比较运算符，而''是可以使用这些比较运算符的。

看了上面的介绍之后，相信你对另外一个高频面试题：“为什么 MySQL 不建议使用 NULL 作为列默认值？”也有了答案。

''是指空字符串；NULL代表一个不确定的值，就算是两个 NULL，它俩也不会相等

7.MySQL中如何表示 Boolean 类型？

MySQL 中没有专门的布尔类型，而是用 TINYINT(1) 类型来代表布尔类型。可以用 TINYINT(1) 类型来存储 0 或 1，分别对应 false 或 true。

三.MySQL基础架构

下图是 MySQL 的一个简要架构图，从下图可以很清晰的看到客户端的一条 SQL 语句在 MySQL 内部是如何执行的。

从上图可以看出， MySQL 主要由下面几部分构成：

连接器： 主要用来进行管理连接和权限验证。
查询缓存： 执行查询语句的时候，会把查询出的结果缓存起来，方便下次查询的时候，直接返回，在 MySQL 8.0 版本后被移除，因为这个功能不太实用。
分析器： 分析器会对SQL语句进行词法分析和语法分析。
优化器： 优化器会对SQL语句进行优化，并生成一个执行计划。
执行器： 通过调用存储引擎的接口执行SQL语句。
插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

1.SQL的查询语句是如何被执行的

其实我们的 SQL 可以分为两种，一种是查询，一种是更新：增加，修改，删除。我们先分析查询语句的执行过程。

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

首先客户端向服务端发起请求建立连接，连接器用来管理此连接并进行权限认证。
然后服务端会去查询缓存，如果命中直接返回缓存，如果未命中，会将SQL交给分析器。
分析器会对SQL进行词法分析、语法分析生成一颗语法树，并将语法树交给优化器。
优化器会对SQL进行优化，生成执行计划交给执行器。
执行器会调用存储引擎的接口执行SQL，存储引擎会根据执行计划的要求访问对应的表，并将结果查询出来返回给执行器。最后，执行器对结果进行进一步处理之后返回给客户端。

连接器的权限认证是在客户端输入的账户密码正确建立连接后，才去权限表中查询用户具有哪些权限。

词法分析就是分析SQL语句中的每个字符串分别代表什么。

语法分析会根据语法规则和词法分析的结果，判断输入的 SQL 语句是否满足 MySQL 的语法要求。如果不满足，就会返回给客户端 You have an error in your SQL syntax 的错误提醒。

优化器是在表里有多个索引的时候，决定使用哪个索引；或着在一个语句中有多表关联的时候，决定各个表的连接顺序。

执行器得到存储引擎返回的结果集之后，对结果集所做的处理包括SQL语句中的分组、排序、分页等操作。

存储引擎去执行SQL之前会判断当前用户对这个表有没有执行权限，如果没有，就会返回没有权限的错误，如果有才会去执行SQL。

2.SQL的更新语句是如何被执行的

update 语句执行过程和 select 语句相同，也需要经过连接器、分析器、优化器、执行器这些步骤。不同的是，在 update 执行过程中涉及到两个日志，一个是 redo log，一个是 binlog。

update tb_student A set A.age='19' where A.name=' 张三 ';

首先客户端发起请求到服务端建立连接，连接器用来管理此连接并进行权限认证。
接下来，服务端会去看一下查询缓存，对于SQL要更新的表，关于该表的所有查询缓存都会失效。
然后解析器会对SQL语句进行词法分析、语法分析生成一颗语法树，并将语法树交给优化器。
优化器会对SQL进行优化，生成执行计划交给执行器。
执行器首先会调用 InnoDB 引擎的接口查询需要的数据并更新，然后继续调用接口将更新后的数据传递给 InnoDB，InnoDB 引擎会把数据保存在内存中，同时记录到 redo log，最后 InnoDB 引擎告诉执行器，执行完成了，随时可以提交。
执行器收到通知后记录 binlog，然后调用 InnoDB 接口，修改 redo log 为提交状态，更新完成。

四.MySQL存储引擎

1.MySQL 支持哪些存储引擎？

MySQL 支持多种存储引擎，包括 InnoDB、MyISAM、Memory 等存储引擎， 最常用的是 InnoDB 存储引擎。可以通过 Show Engines 命令来查看 MySQL 支持的所有存储引擎。

2.MySQL 存储引擎架构了解吗？

MySQL 存储引擎的架构采用的是 插件式架构，允许开发人员根据具体的需求为数据库表选择不同的存储引擎。需要注意的是，存储引擎是基于表的，而不是数据库。

可以根据 MySQL 定义的存储引擎实现标准接口来编写一个属于自己的存储引擎。这些非官方提供的存储引擎可以称为第三方存储引擎，区别于官方存储引擎。像目前最常用的 InnoDB 其实刚开始就是一个第三方存储引擎，后面由于过于优秀，其被 Oracle 直接收购了。

3.InnoDB 和 MyISAM 有什么区别？

1.是否支持行级锁

MyISAM 只有表级锁，而 InnoDB 支持行级锁和表级锁，默认为行级锁。也就是说，MyISAM 一锁就是锁住了整张表，这在并发写的情况下是多么滴憨憨啊！这也是为什么 InnoDB 在并发写的时候，性能更牛皮了！

2.是否支持事务

MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交和回滚事务的能力。

3.是否支持外键

MyISAM 不支持，而 InnoDB 支持。

外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！

4.是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log。

5.是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

讲真，这个对比有点废话，毕竟 MyISAM 连行级锁都不支持。MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能。

6.索引实现不一样。

虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。

7.性能有差别。

InnoDB 的性能比 MyISAM 更强大，不管是在读写混合模式下还是只读模式下，随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长。MyISAM 因为读写不能并发，它的处理能力跟核数没关系。

总结：

InnoDB 是把索引和数据存储在同一个文件里面；MyISAM 的索引和数据是分开存储的。
InnoDB 支持行级别的锁粒度，MyISAM 只支持表级别的锁粒度。
InnoDB 支持事务、外键、MVCC，MyISAM 都不支持。
InnoDB 支持数据库异常崩溃后的安全恢复，MyISAM 不支持。
InnoDB 的性能比 MyISAM 更强大

InnoDB 在磁盘上的文件有二个，分别是

.frm：存储表定义、

.ibd：存储数据文件和索引文件

MyISAM 在磁盘上的文件有三个，文件名都是表名，后缀名分别是

.frm：存储表定义、

.MYD：存储数据文件

.MYI：存储索引文件

4.InnoDB 和 MyISAM 如何选择？

说一下 InnoDB 相比于 MyISAM 的优点，然后说选择 InnoDB。

五.MySQL索引

1.什么是索引

索引是帮助数据库高效获取数据的数据结构，其作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

2.索引的优缺点

优点：

使用索引可以大大加快查询数据的速度。

缺点：

创建索引和维护索引需要耗费许多时间。在对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
索引使用物理文件存储，也会耗费一定空间。

3.索引的底层数据结构了解过吗？

索引底层数据结构存在很多种类型，常见的索引结构有: B 树、B+树、Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+ 树作为索引结构。

4.什么是 B 树、B+ 树

B 树也叫 B- 树，其结构类似于二叉搜索树。但是，B 树的每个节点可以有多个子节点。具体来说，B 树的每个节点可以存储多个关键字和对应的数据指针，同时也包含了指向其子节点的指针，这使得 B 树的高度相对较低，适合对数据进行增删改查。

B 树的每个节点通常有一个上限和下限，称为最小度数，记为 t。对于任意非根节点，它至少有 t-1个关键字和 t 个子节点，至多有 2t-1 个关键字和 2t 个子节点。对于根节点，它至少有一个关键字和两个子节点，至多有 2t-1 个关键字和 2t 个子节点。这些限制保证了 B 树的平衡性，使得所有叶子节点都在同一层级上，从而保证了查找、插入和删除等操作的效率。

B 树的时间复杂度为 O(log n)，其中 n 为B树中存储的关键字数量。

B+ 树是在 B 树的基础上做了一层优化，B+ 树只在叶子节点存储关键字和对应的数据，非叶子节点只存储关键字和指向其子节点的指针。并且，B+ 树的叶子节点形成了一个链表，便于范围查询和遍历。

具体来说，B+ 树的每个节点通常有一个上限和下限，记为 m。对于任意非根节点，它至少有 m/2个关键字和 m/2 个子节点，至多有 m 个关键字和 m 个子节点。对于根节点，它至少有两个子节点，至多有 m 个关键字和 m 个子节点。其中，m 的取值通常与磁盘块的大小相关，B+ 树可以更好地利用磁盘块的大小，减少磁盘 I/O 操作的次数，提高查询效率。

B+ 树的时间复杂度为 O(log n)，其中 n 为 B+ 树中存储的关键字数量。

5.B 树和 B+ 树的区别

B 树的所有节点既存放键也存放数据，而 B+ 树只有叶子节点存放键和数据。
B 树的叶子节点都是独立的；B+ 树的叶子节点形成了一个链表，很适合范围查询和遍历。

6.MySQL中索引类型有哪些

主键索引：主键索引是数据库中针对主键列而建立的索引，能够确保主键是唯一且不能为 null 的。
普通索引：普通索引只是为了快速查询数据，允许字段值重复并且为NULL。
唯一索引：唯一索引就是在普通索引的基础上要求字段值唯一。
联合索引：联合索引是在多个列上建立的索引。
前缀索引：前缀索引只用于字符串类型。是对字符串的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

在 MySQL 中设置了 InnoDB 的表，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6 Byte 的自增主键。

7.什么是聚簇索引与非聚簇索引

聚簇索引是指在 B+ 树的叶子节点上保存的数据是整行记录，聚簇索引有且只有一个，一般情况下主键索引就是聚簇索引
非聚簇索引是指在 B+ 树的叶子节点上保存的数据是主键值，非聚簇索引可以有多个，一般我们自己定义的索引都是非聚簇索引

聚簇索引和非聚簇索引示意图：

这是 MySQL 的表的文件截图：

8.什么是二级索引

二级索引又称为非聚簇索引，二级索引在 B+ 树的叶子节点上保存的数据是主键值，非聚簇索引可以有多个，一般我们自己定义的索引都是非聚簇索引。唯一索引，普通索引，前缀索引等索引都属于二级索引。

9.知道什么是回表查询嘛?

回表查询的意思就是通过二级索引找到的主键值，然后再通过主键值去聚簇索引中找到对应的整行数据。

10.什么是覆盖索引

覆盖索引是指需要查询的字段正好是索引的字段，那么就可以直接根据索引查找到对应的数据，不需要回表查询了。

11.最左前缀匹配原则了解吗？

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，依此类推，直至联合索引中全部字段匹配完成，如果查询条件中不存在与联合索引中最左侧字段相匹配的字段，那么联合索引就不会生效。

当创建 (a,b,c) 复合索引时，索引生效或失效的情况如下所示，只能使用 a 和 ab、ac 和 abc 三种组合！

SELECT a,b,c FROM A WHERE a=1; //索引生效
SELECT a,b,c FROM A WHERE a=1 AND b=2; //索引生效
SELECT a,b,c FROM A WHERE a=1 AND c=3; //索引生效，实际上值使用了索引a
SELECT a,b,c FROM A WHERE b=2; //索引失效
SELECT a,b,c FROM A WHERE c=3; //索引失效
SELECT a,b,c FROM A WHERE b=2 AND c=3; //索引失效
SELECT a,b,c FROM A WHERE a=1 AND b>2 AND c=3; //索引失效
SELECT a,b,c FROM A WHERE a=1 AND b=2 AND c>3; //索引生效

索引失效，因为B+树的叶子节点是主键id，它也可以算作复合索引的字段，如果a=1 AND id>3，则代表b和c是
不确定的，索引就会失效
SELECT a,b,c FROM A WHERE a=1 AND id>3; 

索引生效，因为abc都固定住了
SELECT a,b,c FROM A WHERE a=1 AND b=2 AND c=3 AND id>3;

普通索引在某种程度上也算联合索引，比如为Student表上的name字段建立一个普通索引，则name字段和主键id也可以构成联合索引的效果，比如where子句是where name="zhangsan" and id>12就可以利用到索引下推。

如果在执行过程中遇到范围查询，如 > 和 < 会停止匹配。对于 >=、<=、BETWEEN、LIKE 前缀匹配的范围查询，并不会停止匹配。

12.什么情况下索引会失效?

通常情况下，想要判断出这条 sql 是否有索引失效的情况，可以使用 explain 执行计划来分析。下面列出的常见的一些让索引失效的情况，令索引失效的情况不止下面这些，遇到时可再补充进去。

查询语句中使用了 select *

select * from user where name='苏三';

在索引列上进行了运算操作

explain select * from user where id+1=2;

在索引列上进行了类型转换。例如，user表中的code字段，它是 varchar 字符类型的，而下方的SQL语句中的赋给code的字面量是整型的

select * from user where code=101;

在模糊查询时，% 号在最左边

select * from user where code like '%1';

联合索引在使用的时候没有遵循最左前缀匹配原则
使用 or 关键字的时候，前面和后面的字段一个建立了索引，一个没建立索引。

select * from user where id=1 or height='175';

13.索引下推了解吗？

索引下推是 MySQL 5.6 及以上版本推出的一项索引优化功能。索引下推是指把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断。在存储引擎层会先对索引中包含的字段进行判断，把不符合条件的数据过滤掉，从而减少回表次数。

没有使用索引下推的情况

使用了索引下推的情况

可以看出，没有索引下推的联合索引只把索引中最左侧的字段传给了存储引擎；有索引下推的联合索引把索引中的所有字段都传给了存储引擎。

索引下推不仅可用于联合索引，普通索引也可以利用到，因为普通索引一定程度上也算联合索引。

14.索引优化的方式有哪些？

此问题等同于问：使用索引的时候，可以如何优化呢？

对于频繁更新的字段建立索引的时候要慎重
对查询频率高的字段创建索引。
索引的数目不要太多。首先每创建一个索引都会占用相应的物理空间，其次过多的索引会导致insert、update、delete语句的执行效率降低；
对于长期不使用的索引建议删除
尽量使用覆盖索引，避免回表查询。
在创建联合索引时，要根据业务需求，把where子句中使用最频繁字段放在联合索引的最左边。
要避免索引失效：如何避免索引失效的情况见问题12。
如果字符串类型的字段内容较长，可以使用前缀索引。如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

六.MySQL日志

1.MySQL的常见日志有哪些？

针对 InnoDB 存储引擎，MySQL 中常见的日志类型主要有下面几类，其中比较重要的是 binlog、redo log 和 undo log，需要我们重点关注。

错误日志（error log）：对 MySQL 的启动、运行、关闭过程进行了记录。
二进制日志（binary log，binlog）：主要记录的是更改数据库数据的 SQL 语句。
一般查询日志（general query log）：已建立连接的客户端发送给 MySQL 服务器的所有 SQL 记录，因为 SQL 的量比较大，默认是不开启的，也不建议开启。
慢查询日志（slow query log）：执行时间超过系统变量 long_query_time 的查询，解决 SQL 慢查询问题时会用到。
事务日志(redo log 和 undo log) ：redo log 是重做日志，undo log 是回滚日志。
中继日志(relay log) ：relay log 针对的是主从复制中的从库，relay log 是复制过程中产生的日志，很多方面都跟 binary log 差不多。
DDL 日志(metadata log) ：DDL 语句执行的元数据操作。

2.慢查询日志（slow query log）

慢查询日志记录了执行时间超过系统变量 long_query_time 的所有查询语句，long_query_time 默认是 10s，通常设置为 1s。在解决慢查询问题时，可以通过慢查询日志找到慢 SQL，再用 EXPLAIN 命令获取慢 SQL 的执行计划进行分析。

3.二进制日志（binlog）

binlog 是什么

binlog 是 MySQL 架构中 Server 层的日志，记录了对 MySQL 数据库执行了更改的所有操作，主要用于数据备份和主从复制。binlog 是一个二进制文件，通过追加写的方式写入磁盘。并且，binlog 有不同的模式，包括 statement、row、mixed，而不同的模式，会对数据库的性能产生不同的影响，实际开发中可以根据具体的场景将 binlog 配置为不同的模式。

binlog 有三种日志记录方式，分别是：

Statement 模式：每一条会修改数据的sql都会被记录在 binlog 中，如 insert、update、delete。
Row 模式: 记录的是每一行的具体变更事件。
Mixed 模式：Statement 模式和 Row 模式的混合。默认使用 Statement 模式，少数特殊具体场景自动切换到 Row 模式。

binlog 主要用来作什么

binlog 最主要的应用场景是主从复制，在主从复制中：

首先，主库会将更改了数据库的所有操作写入到 binlog。
从库在连接到主库之后，会创建一个 I/O 线程向主库请求更新的 binlog。
主库在接收到请求之后，会创建一个 binlog dump 线程来不断发送 binlog。
从库的 I/O 线程会一直监听并接收 binlog，并将接收的 binlog 写入到 relay log 中。
从库的 SQL 线程会重新执行 relay log 中的日志，以同步数据到本地。

4.redo log

什么是 redo log

redolog 是 InnoDB 存储引擎生成的事务日志，记录了事务对数据库的修改操作，主要用在数据异常崩溃后的安全恢复，以保证事务的持久性。

InnoDB 存储引擎是以页为单位来管理存储空间的，我们往 MySQL 插入的数据最终都是存在于页中的，准确点来说是数据页这种类型。为了减少磁盘 IO 开销，还有一个叫做 Buffer Pool(缓冲池) 的区域，存在于内存中。当我们的数据对应的页不存在于 Buffer Pool 中的话， MySQL 会先将磁盘上的页缓存到 Buffer Pool 中，这样后面我们直接操作的就是 Buffer Pool 中的页，这样大大提高了读写性能。
在MySQL的InnoDB存储引擎中，事务的修改操作是先在缓冲池中修改页，然后几乎同步地将对应的重做日志记录到日志缓冲区。（SQL更新语句的执行流程那有一张图）
redo log 中的每一条记录包含了表空间号、数据页号、偏移量、具体修改的数据。

redo log 如何保证事务的持久性

当事务执行了修改操作，对应的修改会被写入到 log buffer 中，InnoDB 存储引擎会在多种情况下将 log buffer 中的重做日志持久化到磁盘上的 redo log 文件。这样，即使 MySQL 宕机了，重启之后也能恢复未能写入磁盘的数据，从而保证事务的持久性。InnoDB 将 redo log 刷到磁盘上有下面几种情况：

事务提交：当事务提交时，log buffer 里的 redo log 会被刷新到磁盘。可以通过innodb_flush_log_at_trx_commit 参数控制，后文会提到。
后台刷新线程：InnoDB 启动了一个后台线程，每隔一段时间（默认 1 秒）将脏页刷新到磁盘，并将相关的重做日志一同刷新。也就是说，一个没有提交事务的 redo log 记录，也可能会被刷盘。
log buffer 空间不足时：log buffer 中缓存的 redo log 已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
Checkpoint（检查点）：InnoDB 定期会执行检查点操作，将内存中的脏数据（已修改但尚未写入磁盘的数据）刷新到磁盘，并且会将相应的重做日志一同刷新，以确保数据的一致性。
正常关闭服务器：MySQL 关闭的时候，redo log 都会刷入到磁盘里去。

innodb_flush_log_at_trx_commit 的值有 3 种，也就是共有 3 种刷盘策略：

0：设置为 0 的时候，表示每次事务提交时不进行刷盘操作。这种方式性能最高，但是也最不安全，因为如果 MySQL 挂了或宕机了，可能会丢失最近 1 秒内的事务。
1：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作。这种方式性能最低，但是也最安全，因为只要事务提交成功，redo log 记录就一定在磁盘里，不会有任何数据丢失。
2：设置为 2 的时候，表示每次事务提交时都只把 log buffer 里的 redo log 内容写入 page cache。page cache 是专门用来缓存文件的，这里被缓存的文件就是 redo log 文件。这种方式的性能和安全性都介于前两者中间。

page cache 是文件系统缓存

已修改但尚未写入磁盘的数据页

什么情况下还是会出现数据丢失？

page cache是操作系统的缓存

redo log 写入 log buffer 但还未写入 page cache，此时数据库崩溃，就会出现数据丢失。刷盘策略innodb_flush_log_at_trx_commit 的值为 0 时可能会出现这种数据丢失。
redo log 已经写入 page cache 但还未写入磁盘，操作系统奔溃，也可能出现数据丢失。刷盘策略 innodb_flush_log_at_trx_commit 的值为 2 时可能会出现这种数据丢失。

5.undo log

什么是 undo log

undo log 是 InnoDB 存储引擎生成的事务日志，记录了事务对数据库的修改操作，主要用在事务回滚，以保证事务的原子性。除此之外，undo log 还用于 MVCC。

undolog 如何保证事务的原子性

每一个对事务的修改操作都会被记录到 undo log，当执行事务过程中出现错误或者需要回滚操作的话，MySQL 就可以利用 undo log 将数据恢复到事务开始之前的状态。

undo log 属于逻辑日志，记录的是 SQL 语句，比如说事务执行一条 DELETE 语句，那 undo log 就会记录一条相对应的 INSERT 语句。

6.binlog、redo log 和 undo log 有什么区别？

binlog 是 Server 层的日志，是所有存储引擎共有的，主要用于数据备份和主从复制。
redolog 和 InnoDB 存储引擎生成的事务日志，记录了事务对数据库执行了修改的操作，主要用于数据库系统崩溃等故障恢复，以保证事务的持久性。
undo log 也是 InnoDB 引擎生成的事务日志，记录了事务对数据库执行了修改的操作，主要用在事务回滚，以保证事务的原子性。除此之外，undo log 还用于 MVCC。

七.MySQL事务

1.什么是事务

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

2.事务的ACID特性

原子性：事务是最小的执行单位，事务中的操作要么都执行，要么都不执行。
一致性：事务执行前后，数据要保持一致，例如转账业务中，无论事务是否成功，转账的人和收款的人的总额应该是不变的。
隔离性：并发访问数据库时，一个用户的事务不应该被其他事务所干扰。
持久性：一个事务提交之后，它对数据库中数据的改变是永久的，即使数据库发生故障也不应该对其有任何影响。

Note：只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

3.并发事务带来了哪些问题?

在典型的应用程序中，经常会有多个用户对同一数据进行操作，导致多个事务并发运行。也就是说，这多个事务会操作相同的数据来完成各自的任务。并发虽然是必须的，但可能会导致以下的问题。

脏读：脏读指的是一个事务读到另外一个事务还没有提交的数据。

不可重复读：不可重复读指的是一个事务先后读取相同的数据，但第二次读取的数据不同或者没有读取到。

幻读：幻读是指一个事务查询某个数据时没有查到，但是在插入这个数据的时候，又发现数据已经存在。

4.不可重复读和幻读有什么区别？

不可重复读的重点是内容修改或者记录减少。比如多次读取同一条记录发现其中某些字段的值被修改了。
幻读的重点在于记录新增。比如多次执行同一条查询语句时，发现查到的记录增加了。

Note：幻读其实可以看作是不可重复读的一种特殊情况，单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。

5.并发事务的控制方式有哪些？

MySQL 中并发事务的控制方式有两种：分别是锁和 MVCC。锁可以看作是悲观控制的模式，MVCC 可以看作是乐观控制的模式。

MySQL 中的锁按照操作方式可以分为共享锁和排他锁

共享锁（Share Lock，S 锁）：又叫读锁，事务在读取数据的时候可以获取读锁，一个事务获取了数据的读锁之后，其他事务只能获取该数据的读锁，无法获取写锁。
排他锁（Exclusive Lock，X 锁）：又称写锁、事务在可以修改数据的时候获取写锁，一个事务获取了某个数据的写锁之后，其它事务无法再获取这个数据的任何锁。

MVCC 参见问题 6。

6.什么是 MVCC

事务的隔离性是由锁和 MVCC 实现的。

其中 MVCC 的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作之间没有冲突，它的底层实现主要分为了三个部分，分别是是隐藏字段、undo log 日志、readView 读视图。

跟 MVCC 相关的隐藏字段有二个，第一个字段是插入和修改记录的事务 id；第二个字段是回滚指针（roll_pointer），指向这条记录的上一个版本地址。

undo log 当中不仅记录了回滚日志，还记录了老版本数据，这些老版本数据在内部会通过回滚指针形成一个版本链。在多个事务并行操作某一行记录时，这个版本链中就记录了不同事务修改的数据版本。

readView 解决的是事务查询数据时选择哪个版本的问题，readView 会根据匹配规则和当前的一些事务 id 判断该访问版本链中的哪个记录。并且，不同的隔离级别产生 readview 的时机是不一样的，如果是 RC 隔离级别，每一次执行快照读时生成 ReadView，如果是 RR 隔离级别仅在事务中第一次执行快照读时生成 ReadView，后续复用。

7.SQL 标准定义了哪些事务隔离级别?

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

READ-UNCOMMITTED（读未提交) ：读未提交指的是允许一个事务读取另一个事务未提交的数据，可能会导致脏读、幻读、不可重复读。
READ-COMMITTED（读已提交）：读已提交指的是一个事物提交之后，它做的变更才会被其他事务看到，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ（可重复读）：可重复读指的是对相同数据的多次读取，结果都是一致的，除非数据是事务本身所修改的，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE（可串行化）：所有的事务依次逐个执行，这样事务之间就不可能产生干扰，该级别可以防止并发事务产生的所有问题。

8.MySQL 的隔离级别是基于锁实现的吗？

不同隔离级别的实现方式不同。其中，READ-COMMITTED 和 REPEATABLE-READ 隔离级别主要是基于 MVCC 实现的。但是，也可能用到锁，比如在更新数据时，这二个隔离级别会加排它锁。SERIALIZABLE 隔离级别是通过锁来实现的。

9.MySQL 的默认隔离级别是什么?

MySQL 默认支持的隔离级别是可重复读（REPEATABLE-READ）。我们可以通过以下命令来查看：

SELECT @@tx_isolation;

MySQL 8.0 该命令改为：

SELECT @@transaction_isolation;

八.MySQL 锁

1.表级锁和行级锁了解吗？有什么区别？

表级锁会锁住整张表，行级锁仅对一行或多行记录上锁，所以在并发写操作时，行级锁的性能要高于表级锁。InnoDB 既支持行级锁也支持表级锁，默认是行级锁；MyISAM仅支持表级锁。行级锁和表级锁的区别如下：

表级锁：表级锁会锁住整张表。
行级锁：行级锁只针对相关记录进行加锁。

区别：行级锁相比于表级锁的优点是锁定粒度小，触发锁冲突的概率低；缺点是开销大、加锁慢、可能会出现死锁。

表级锁开销小、加锁快、不会出现死锁。但是，锁定粒度大，发生锁冲突的概率高。
InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 等语句时，如果 WHERE 条件中字段没有命中索引或者索引失效的话，行级锁会对表中的所有行记录加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！不过，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因。

2.InnoDB 有哪几类行锁？

InnoDB 的行锁是针对索引字段进行加锁的，InnoDB 支持三种行锁：

记录锁（Record Lock）：锁住的是记录。
间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
临键锁（Next-Key Lock）：临键锁是记录锁和间隙锁的结合，锁定一个范围，包含记录本身。主要目的是为了解决幻读问题。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

3.共享锁和排他锁了解吗？

不论是表级锁还是行级锁，按照操作方式都可以分为共享锁和排他锁这两类：

共享锁（Share Lock，S 锁）：又叫读锁，事务在读取数据的时候可以获取读锁，一个事务获取了数据的读锁之后，其他事务只能获取该数据的读锁，无法获取写锁。
排他锁（Exclusive Lock，X 锁）：又称写锁、事务在可以修改数据的时候获取写锁，一个事务获取了某个数据的写锁之后，其它事务无法再获取这个数据的任何锁。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过，你可以通过以下语句显式加共享锁或排他锁。

# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
# 排他锁
SELECT ... FOR UPDATE;

4.意向锁有了解吗？

为了避免加表锁时与行锁冲突，在 InnoDB 中引入了意向锁。意向锁是用来快速判断是否可以对某个表加表锁。意向锁是表级锁，共有两种：

意向共享锁（Intention Shared Lock，IS 锁）：事务在对表中的某些记录加共享锁之前，必须先取得这个表的意向共享锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务在对表中的某些记录加排他锁之前，必须先取得该表的意向排他锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享锁、排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的。

意向锁和表级锁中的共享锁和排它锁大部分互斥

5.当前读和快照读有什么区别？

当前读是指读取数据库中的最新记录，读取时会通过加共享锁或排它锁，来保证其他并发事务不能修改当前记录。当前读的一些常见 SQL 语句类型如下：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对读的记录加一个S锁
SELECT...FOR SHARE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

快照读是指如果读取的记录被加了排它锁，读取操作不会去等待记录上排它锁的释放，而是去读取记录的一个快照。快照是记录的历史版本，每行记录可能存在多个历史版本。

只有在事务隔离级别 RC 和 RR 下，InnoDB 才会使用快照读，且快照读读取的快照也可能不同：

在读已提交的级别下，快照读读取的是记录的最新快照。
在可重复读的级别下，快照读读取的是本事务开始时的快照。

快照读不包括下面这两类对查询结果加锁的 SELECT 语句：

SELECT ... FOR UPDATE
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;

快照读读取的记录被加上了排它锁可能是因为记录正在被执行update或者delete操作

九.其它问题

1.能用 MySQL 直接存储文件吗？

虽然可以将图片、音频、视频等文件转为对应的二进制数据存储。但是，还是建议不要在数据库中存储文件，会严重影响数据库性能，消耗过多存储空间。可以选择使用云服务厂商提供的开箱即用的文件存储服务，成熟稳定，价格也比较低。数据库只存储文件地址信息，文件由文件存储服务负责存储。

也可以选择自建文件存储服务，实现起来也不难，基于 FastDFS、MinIO（推荐）等开源项目就可以实现分布式文件服务。

2.MySQL 如何存储 IP 地址？

可以将 IP 地址转换成整形数据存储，性能更好，占用空间也更小。

MySQL 提供了两个方法来处理 ip 地址

INET_ATON()：把 IP 转为无符号整型
INET_NTON()：把整型的 IP 转为地址

插入数据前，先用 INET_ATON 方法把 IP 地址转为整型，显示数据时，使用 INET_NTON 方法把整型的 IP 地址转为地址显示即可。

3.有哪些常见的 SQL 优化手段？

下面是常见的SQL优化手段，更具体的可以参考Java面试指北

避免使用 SELECT *
尽量避免多表做 join
最好不要使用外键和级联
选择合适的字段类型
尽量用 UNION ALL 代替 UNION
优化慢 SQL
正确使用索引

4.如何分析 SQL 的性能？

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 适用于SELECT、DELETE、INSERT、REPLACE、UPDATE语句，一般分析 SELECT 查询较多。

我们这里简单来演示一下 EXPLAIN 的使用。

EXPLAIN 的输出格式如下：

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

各个字段的含义如下：