一.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(推荐) 等开源项目就可以实现分布式文件服务。
相关阅读:Spring Boot 整合 MinIO 实现分布式文件服务open in new window 。
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)
各个字段的含义如下:
5.读写分离和分库分表了解吗?
太多了,有余力再去看JavaGuide吧!