同步操作将从 SnailClimb/JavaGuide 强制同步,此操作会覆盖自 Fork 仓库以来所做的任何修改,且无法恢复!!!
确定后同步将在后台操作,完成时将刷新页面,请耐心等待。
title | category | tag | head | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
MySQL常见面试题总结 |
数据库 |
|
|
顾名思义,关系型数据库(RDB,Relational Database)就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系(一对一、一对多、多对多)。
关系型数据库中,我们的数据都被存放在了各种表中(比如用户表),表中的每一行就存放着一条数据(比如一个用户的信息)。
大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且,大部分关系型数据库都支持事务的四大特性(ACID)。
有哪些常见的关系型数据库呢?
MySQL、PostgreSQL、Oracle、SQL Server、SQLite(微信本地的聊天记录的存储就是用的 SQLite) ......。
SQL 是一种结构化查询语言(Structured Query Language),专门用来与数据库打交道,目的是提供一种从数据库中读写数据的简单有效的方法。
几乎所有的主流关系数据库都支持 SQL ,适用性非常强。并且,一些非关系型数据库也兼容 SQL 或者使用的是类似于 SQL 的查询语言。
SQL 可以帮助我们:
MySQL 是一种关系型数据库,主要用于持久化存储我们的系统中的一些数据比如用户信息。
由于 MySQL 是开源免费并且比较成熟的数据库,因此,MySQL 被大量使用在各种系统中。任何人都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进行修改。MySQL 的默认端口号是3306。
这个问题本质上是在问 MySQL 如此流行的原因。
MySQL 主要具有下面这些优点:
MySQL 字段类型可以简单分为三大类:
下面这张图不是我画的,忘记是从哪里保存下来的了,总结的还蛮不错的。
MySQL 字段类型比较多,我这里会挑选一些日常开发使用很频繁且面试常问的字段类型,以面试问题的形式来详细介绍。如无特殊说明,针对的都是 InnoDB 存储引擎。
另外,推荐阅读一下《高性能 MySQL(第三版)》的第四章,有详细介绍 MySQL 字段类型优化。
MySQL 中的整数类型可以使用可选的 UNSIGNED 属性来表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限提高一倍,因为它不需要存储负数值。
例如, TINYINT UNSIGNED 类型的取值范围是 0 ~ 255,而普通的 TINYINT 类型的值范围是 -128 ~ 127。INT UNSIGNED 类型的取值范围是 0 ~ 4,294,967,295,而普通的 TINYINT 类型的值范围是 2,147,483,648 ~ 2,147,483,647。
对于从 0 开始递增的 ID 列,使用 UNSIGNED 属性可以非常适合,因为不允许负值并且可以拥有更大的上限范围,提供了更多的 ID 值可用。
CHAR 和 VARCHAR 是最常用到的字符串类型,两者的主要区别在于:CHAR 是定长字符串,VARCHAR 是变长字符串。
CHAR 在存储时会在右边填充空格以达到指定的长度,检索时会去掉空格;VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度,检索时不需要处理。
CHAR 更适合存储长度较短或者长度都差不多的字符串,例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串,例如用户昵称、文章标题等。
CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值,无论是字母、数字还是中文,每个都只占用一个字符。
VARCHAR(100)和 VARCHAR(10)都是变长类型,表示能存储最多 100 个字符和 10 个字符。因此,VARCHAR (100) 可以满足更大范围的字符存储需求,有更好的业务拓展性。而 VARCHAR(10)存储超过 10 个字符时,就需要修改表结构才可以。
虽说 VARCHAR(100)和 VARCHAR(10)能存储的字符范围不同,但二者存储相同的字符串,所占用磁盘的存储空间其实是一样的,这也是很多人容易误解的一点。
不过,VARCHAR(100) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时,通常会分配固定大小的内存块来保存值,即使用字符类型中定义的长度。例如在进行排序的时候,VARCHAR(100)是按照 100 这个长度来进行的,也就会消耗更多内存。
DECIMAL 和 FLOAT 的区别是:DECIMAL 是定点数,FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值,FLOAT/DOUBLE 只能存储近似的小数值。
DECIMAL 用于存储具有精度要求的小数,例如与货币相关的数据,可以避免浮点数带来的精度损失。
在 Java 中,MySQL 的 DECIMAL 类型对应的是 Java 类 java.math.BigDecimal
。
TEXT 类型类似于 CHAR(0-255 字节)和 VARCHAR(0-65,535 字节),但可以存储更长的字符串,即长文本数据,例如博客内容。
类型 | 可存储大小 | 用途 |
---|---|---|
TINYTEXT | 0-255 字节 | 一般文本字符串 |
TEXT | 0-65,535 字节 | 长文本字符串 |
MEDIUMTEXT | 0-16,772,150 字节 | 较大文本数据 |
LONGTEXT | 0-4,294,967,295 字节 | 极大文本数据 |
BLOB 类型主要用于存储二进制大对象,例如图片、音视频等文件。
类型 | 可存储大小 | 用途 |
---|---|---|
TINYBLOB | 0-255 字节 | 短文本二进制字符串 |
BLOB | 0-65KB | 二进制字符串 |
MEDIUMBLOB | 0-16MB | 二进制形式的长文本数据 |
LONGBLOB | 0-4GB | 二进制形式的极大文本数据 |
在日常开发中,很少使用 TEXT 类型,但偶尔会用到,而 BLOB 类型则基本不常用。如果预期长度范围可以通过 VARCHAR 来满足,建议避免使用 TEXT。
数据库规范通常不推荐使用 BLOB 和 TEXT 类型,这两种类型具有一些缺点和限制,例如:
DATETIME 类型没有时区信息,TIMESTAMP 和时区有关。
TIMESTAMP 只需要使用 4 个字节的存储空间,但是 DATETIME 需要耗费 8 个字节的存储空间。但是,这样同样造成了一个问题,Timestamp 表示的时间范围更小。
关于两者的详细对比,请参考我写的MySQL 时间类型数据存储建议。
NULL
跟 ''
(空字符串)是两个完全不一样的值,区别如下:
NULL
代表一个不确定的值,就算是两个 NULL
,它俩也不一定相等。例如,SELECT NULL=NULL
的结果为 false,但是在我们使用DISTINCT
,GROUP BY
,ORDER BY
时,NULL
又被认为是相等的。''
的长度是 0,是不占用空间的,而NULL
是需要占用空间的。NULL
会影响聚合函数的结果。例如,SUM
、AVG
、MIN
、MAX
等聚合函数会忽略 NULL
值。 COUNT
的处理方式取决于参数的类型。如果参数是 *
(COUNT(*)
),则会统计所有的记录数,包括 NULL
值;如果参数是某个字段名(COUNT(列名)
),则会忽略 NULL
值,只统计非空值的个数。NULL
值时,必须使用 IS NULL
或 IS NOT NULLl
来判断,而不能使用 =、!=、 <、> 之类的比较运算符。而''
是可以使用这些比较运算符的。看了上面的介绍之后,相信你对另外一个高频面试题:“为什么 MySQL 不建议使用 NULL
作为列默认值?”也有了答案。
MySQL 中没有专门的布尔类型,而是用 TINYINT(1) 类型来表示布尔值。TINYINT(1) 类型可以存储 0 或 1,分别对应 false 或 true。
建议配合 SQL 语句在 MySQL 中的执行过程 这篇文章来理解 MySQL 基础架构。另外,“一个 SQL 语句在 MySQL 中的执行流程”也是面试中比较常问的一个问题。
下图是 MySQL 的一个简要架构图,从下图你可以很清晰的看到客户端的一条 SQL 语句在 MySQL 内部是如何执行的。
从上图可以看出, MySQL 主要由下面几部分构成:
MySQL 核心在于存储引擎,想要深入学习 MySQL,必定要深入研究 MySQL 存储引擎。
MySQL 支持多种存储引擎,你可以通过 SHOW ENGINES
命令来查看 MySQL 支持的所有存储引擎。
从上图我们可以查看出, MySQL 当前默认的存储引擎是 InnoDB。并且,所有的存储引擎中只有 InnoDB 是事务性存储引擎,也就是说只有 InnoDB 支持事务。
我这里使用的 MySQL 版本是 8.x,不同的 MySQL 版本之间可能会有差别。
MySQL 5.5.5 之前,MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后,InnoDB 是 MySQL 的默认存储引擎。
你可以通过 SELECT VERSION()
命令查看你的 MySQL 版本。
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.00 sec)
你也可以通过 SHOW VARIABLES LIKE '%storage_engine%'
命令直接查看 MySQL 当前默认的存储引擎。
mysql> SHOW VARIABLES LIKE '%storage_engine%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.00 sec)
如果你想要深入了解每个存储引擎以及它们之间的区别,推荐你去阅读以下 MySQL 官方文档对应的介绍(面试不会问这么细,了解即可):
MySQL 存储引擎采用的是 插件式架构 ,支持多种存储引擎,我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的,而不是数据库。
并且,你还可以根据 MySQL 定义的存储引擎实现标准接口来编写一个属于自己的存储引擎。这些非官方提供的存储引擎可以称为第三方存储引擎,区别于官方存储引擎。像目前最常用的 InnoDB 其实刚开始就是一个第三方存储引擎,后面由于过于优秀,其被 Oracle 直接收购了。
MySQL 官方文档也有介绍到如何编写一个自定义存储引擎,地址:https://dev.mysql.com/doc/internals/en/custom-engine.html 。
MySQL 5.5 之前,MyISAM 引擎是 MySQL 的默认存储引擎,可谓是风光一时。
虽然,MyISAM 的性能还行,各种特性也还不错(比如全文索引、压缩、空间函数等)。但是,MyISAM 不支持事务和行级锁,而且最大的缺陷就是崩溃后无法安全恢复。
MySQL 5.5 版本之后,InnoDB 是 MySQL 的默认存储引擎。
言归正传!咱们下面还是来简单对比一下两者:
1.是否支持行级锁
MyISAM 只有表级锁(table-level locking),而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
也就说,MyISAM 一锁就是锁住了整张表,这在并发写的情况下是多么滴憨憨啊!这也是为什么 InnoDB 在并发写的时候,性能更牛皮了!
2.是否支持事务
MyISAM 不提供事务支持。
InnoDB 提供事务支持,实现了 SQL 标准定义了四个隔离级别,具有提交(commit)和回滚(rollback)事务的能力。并且,InnoDB 默认使用的 REPEATABLE-READ(可重读)隔离级别是可以解决幻读问题发生的(基于 MVCC 和 Next-Key Lock)。
关于 MySQL 事务的详细介绍,可以看看我写的这篇文章:MySQL 事务隔离级别详解。
3.是否支持外键
MyISAM 不支持,而 InnoDB 支持。
外键对于维护数据一致性非常有帮助,但是对性能有一定的损耗。因此,通常情况下,我们是不建议在实际生产项目中使用外键的,在业务代码中进行约束即可!
阿里的《Java 开发手册》也是明确规定禁止使用外键的。
不过,在代码中进行约束的话,对程序员的能力要求更高,具体是否要采用外键还是要根据你的项目实际情况而定。
总结:一般我们也是不建议在数据库层面使用外键的,应用层面可以解决。不过,这样会对数据的一致性造成威胁。具体要不要使用外键还是要根据你的项目来决定。
4.是否支持数据库异常崩溃后的安全恢复
MyISAM 不支持,而 InnoDB 支持。
使用 InnoDB 的数据库在异常崩溃后,数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log
。
5.是否支持 MVCC
MyISAM 不支持,而 InnoDB 支持。
讲真,这个对比有点废话,毕竟 MyISAM 连行级锁都不支持。MVCC 可以看作是行级锁的一个升级,可以有效减少加锁操作,提高性能。
6.索引实现不一样。
虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构,但是两者的实现方式不太一样。
InnoDB 引擎中,其数据文件本身就是索引文件。相比 MyISAM,索引文件和数据文件是分离的,其表数据文件本身就是按 B+Tree 组织的一个索引结构,树的叶节点 data 域保存了完整的数据记录。
详细区别,推荐你看看我写的这篇文章:MySQL 索引详解。
7.性能有差别。
InnoDB 的性能比 MyISAM 更强大,不管是在读写混合模式下还是只读模式下,随着 CPU 核数的增加,InnoDB 的读写能力呈线性增长。MyISAM 因为读写不能并发,它的处理能力跟核数没关系。
总结:
最后,再分享一张图片给你,这张图片详细对比了常见的几种 MySQL 存储引擎。
大多数时候我们使用的都是 InnoDB 存储引擎,在某些读密集的情况下,使用 MyISAM 也是合适的。不过,前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点(可是~我们一般都会介意啊!)。
《MySQL 高性能》上面有一句话这样写到:
不要轻易相信“MyISAM 比 InnoDB 快”之类的经验之谈,这个结论往往不是绝对的。在很多我们已知场景中,InnoDB 的速度都可以让 MyISAM 望尘莫及,尤其是用到了聚簇索引,或者需要访问的数据都可以放入内存的应用。
一般情况下我们选择 InnoDB 都是没有问题的,但是某些情况下你并不在乎可扩展能力和并发能力,也不需要事务支持,也不在乎崩溃后的安全恢复问题的话,选择 MyISAM 也是一个不错的选择。但是一般情况下,我们都是需要考虑到这些问题的。
因此,对于咱们日常开发的业务系统来说,你几乎找不到什么理由再使用 MyISAM 作为自己的 MySQL 数据库的存储引擎。
MySQL 索引相关的问题比较多,对于面试和工作都比较重要,于是,我单独抽了一篇文章专门来总结 MySQL 索引相关的知识点和问题:MySQL 索引详解 。
执行查询语句的时候,会先查询缓存。不过,MySQL 8.0 版本后移除,因为这个功能不太实用
my.cnf
加入以下配置,重启 MySQL 开启查询缓存
query_cache_type=1
query_cache_size=600000
MySQL 执行以下命令也可以开启查询缓存
set global query_cache_type=1;
set global query_cache_size=600000;
如上,开启查询缓存后在同样的查询条件以及数据情况下,会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。
查询缓存不命中的情况:
缓存虽然能够提升数据库的查询性能,但是缓存同时也带来了额外的开销,每次查询后都要做一次缓存操作,失效后还要销毁。 因此,开启查询缓存要谨慎,尤其对于写密集的应用来说更是如此。如果开启,要注意合理控制缓存空间大小,一般来说其大小设置为几十 MB 比较合适。此外,还可以通过 sql_cache
和 sql_no_cache
来控制某个查询语句是否需要缓存:
SELECT sql_no_cache COUNT(*) FROM usr;
MySQL 日志常见的面试题有:
上诉问题的答案可以在《Java 面试指北》(付费) 的 「技术面试题篇」 中找到。
我们设想一个场景,这个场景中我们需要插入多条相关联的数据到数据库,不幸的是,这个过程可能会遇到下面这些问题:
上面的任何一个问题都可能会导致数据的不一致性。为了保证数据的一致性,系统必须能够处理这些问题。事务就是我们抽象出来简化这些问题的首选机制。事务的概念起源于数据库,目前,已经成为一个比较广泛的概念。
何为事务? 一言蔽之,事务是逻辑上的一组操作,要么都执行,要么都不执行。
事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账 1000 元,这个转账会涉及到两个关键操作,这两个操作必须都成功或者都失败。
事务会把这两个操作就可以看成逻辑上的一个整体,这个整体包含的操作要么都成功,要么都要失败。这样就不会出现小明余额减少而小红的余额却并没有增加的情况。
大多数情况下,我们在谈论事务的时候,如果没有特指分布式事务,往往指的就是数据库事务。
数据库事务在我们日常开发中接触的最多了。如果你的项目属于单体架构的话,你接触到的往往就是数据库事务了。
那数据库事务有什么作用呢?
简单来说,数据库事务可以保证多个对数据库的操作(也就是 SQL 语句)构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循:要么全部执行成功,要么全部不执行 。
# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
## 提交事务
COMMIT;
另外,关系型数据库(例如:MySQL
、SQL Server
、Oracle
等)事务都有 ACID 特性:
Atomicity
):事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;Consistency
):执行事务前后,数据保持一致,例如转账业务中,无论事务是否成功,转账者和收款人的总额应该是不变的;Isolation
):并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的;Durability
):一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。🌈 这里要额外补充一点:只有保证了事务的持久性、原子性、隔离性之后,一致性才能得到保障。也就是说 A、I、D 是手段,C 是目的! 想必大家也和我一样,被 ACID 这个概念被误导了很久! 我也是看周志明老师的公开课《周志明的软件架构课》才搞清楚的(多看好书!!!)。
另外,DDIA 也就是 《Designing Data-Intensive Application(数据密集型应用系统设计)》 的作者在他的这本书中如是说:
Atomicity, isolation, and durability are properties of the database, whereas consis‐ tency (in the ACID sense) is a property of the application. The application may rely on the database’s atomicity and isolation properties in order to achieve consistency, but it’s not up to the database alone.
翻译过来的意思是:原子性,隔离性和持久性是数据库的属性,而一致性(在 ACID 意义上)是应用程序的属性。应用可能依赖数据库的原子性和隔离属性来实现一致性,但这并不仅取决于数据库。因此,字母 C 不属于 ACID 。
《Designing Data-Intensive Application(数据密集型应用系统设计)》这本书强推一波,值得读很多遍!豆瓣有接近 90% 的人看了这本书之后给了五星好评。另外,中文翻译版本已经在 GitHub 开源,地址:https://github.com/Vonng/ddia 。
在典型的应用程序中,多个事务并发运行,经常会操作相同的数据来完成各自的任务(多个用户对同一数据进行操作)。并发虽然是必须的,但可能会导致以下的问题。
一个事务读取数据并且对数据进行了修改,这个修改对其他事务来说是可见的,即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据,但第一个事务突然回滚,导致数据并没有被提交到数据库,那第二个事务读取到的就是脏数据,这也就是脏读的由来。
例如:事务 1 读取某表中的数据 A=20,事务 1 修改 A=A-1,事务 2 读取到 A = 19,事务 1 回滚导致对 A 的修改并未提交到数据库, A 的值还是 20。
在一个事务读取一个数据时,另外一个事务也访问了该数据,那么在第一个事务中修改了这个数据后,第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失,因此称为丢失修改。
例如:事务 1 读取某表中的数据 A=20,事务 2 也读取 A=20,事务 1 先修改 A=A-1,事务 2 后来也修改 A=A-1,最终结果 A=19,事务 1 的修改被丢失。
指在一个事务内多次读同一数据。在这个事务还没有结束时,另一个事务也访问该数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况,因此称为不可重复读。
例如:事务 1 读取某表中的数据 A=20,事务 2 也读取 A=20,事务 1 修改 A=A-1,事务 2 再次读取 A =19,此时读取的结果和第一次读取的结果不同。
幻读与不可重复读类似。它发生在一个事务读取了几行数据,接着另一个并发事务插入了一些数据时。在随后的查询中,第一个事务就会发现多了一些原本不存在的记录,就好像发生了幻觉一样,所以称为幻读。
例如:事务 2 读取某个范围的数据,事务 1 在这个范围插入了新的数据,事务 2 再次读取这个范围的数据发现相比于第一次读取的结果多了新的数据。
幻读其实可以看作是不可重复读的一种特殊情况,单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。
举个例子:执行 delete
和 update
操作的时候,可以直接对记录加锁,保证事务安全。而执行 insert
操作的时候,由于记录锁(Record Lock)只能锁住已经存在的记录,为了避免插入新记录,需要依赖间隙锁(Gap Lock)。也就是说执行 insert
操作的时候需要依赖 Next-Key Lock(Record Lock+Gap Lock) 进行加锁来保证不出现幻读。
MySQL 中并发事务的控制方式无非就两种:锁 和 MVCC。锁可以看作是悲观控制的模式,多版本并发控制(MVCC,Multiversion concurrency control)可以看作是乐观控制的模式。
锁 控制方式下会通过锁来显示控制共享资源而不是通过调度手段,MySQL 中主要是通过 读写锁 来实现并发控制。
读写锁可以做到读读并行,但是无法做到写读、写写并行。另外,根据根据锁粒度的不同,又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。InnoDB 不光支持表级锁,还支持行级锁,默认为行级锁。行级锁的粒度更小,仅对相关的记录上锁即可(对一行或者多行记录加锁),所以对于并发写入操作来说, InnoDB 的性能更高。不论是表级锁还是行级锁,都存在共享锁(Share Lock,S 锁)和排他锁(Exclusive Lock,X 锁)这两类。
MVCC 是多版本并发控制方法,即对一份数据会存储多个版本,通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号,版本号是唯一的。
MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。
关于 InnoDB 对 MVCC 的具体实现可以看这篇文章:InnoDB 存储引擎对 MVCC 的实现 。
SQL 标准定义了四个隔离级别:
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
READ-UNCOMMITTED | √ | √ | √ |
READ-COMMITTED | × | √ | √ |
REPEATABLE-READ | × | × | √ |
SERIALIZABLE | × | × | × |
MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。
SERIALIZABLE 隔离级别是通过锁来实现的,READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过, SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制,就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。
MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读)。我们可以通过SELECT @@tx_isolation;
命令来查看,MySQL 8.0 该命令改为SELECT @@transaction_isolation;
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
关于 MySQL 事务隔离级别的详细介绍,可以看看我写的这篇文章:MySQL 事务隔离级别详解。
锁是一种常见的并发事务的控制方式。
MyISAM 仅仅支持表级锁(table-level locking),一锁就锁整张表,这在并发写的情况下性非常差。InnoDB 不光支持表级锁(table-level locking),还支持行级锁(row-level locking),默认为行级锁。
行级锁的粒度更小,仅对相关的记录上锁即可(对一行或者多行记录加锁),所以对于并发写入操作来说, InnoDB 的性能更高。
表级锁和行级锁对比:
InnoDB 的行锁是针对索引字段加的锁,表级锁是针对非索引字段加的锁。当我们执行 UPDATE
、DELETE
语句时,如果 WHERE
条件中字段没有命中唯一索引或者索引失效的话,就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到,一定要多多注意!!!
不过,很多时候即使用了索引也有可能会走全表扫描,这是因为 MySQL 优化器的原因。
InnoDB 行锁是通过对索引数据页上的记录加锁实现的,MySQL InnoDB 支持三种行锁定方式:
在 InnoDB 默认的隔离级别 REPEATABLE-READ 下,行锁默认使用的是 Next-Key Lock。但是,如果操作的索引是唯一索引或主键,InnoDB 会对 Next-Key Lock 进行优化,将其降级为 Record Lock,即仅锁住索引本身,而不是范围。
一些大厂面试中可能会问到 Next-Key Lock 的加锁范围,这里推荐一篇文章:MySQL next-key lock 加锁范围是什么? - 程序员小航 - 2021 。
不论是表级锁还是行级锁,都存在共享锁(Share Lock,S 锁)和排他锁(Exclusive Lock,X 锁)这两类:
排他锁与任何的锁都不兼容,共享锁仅和共享锁兼容。
S 锁 | X 锁 | |
---|---|---|
S 锁 | 不冲突 | 冲突 |
X 锁 | 冲突 | 冲突 |
由于 MVCC 的存在,对于一般的 SELECT
语句,InnoDB 不会加任何锁。不过, 你可以通过以下语句显式加共享锁或排他锁。
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
# 排他锁
SELECT ... FOR UPDATE;
如果需要用到表锁的话,如何判断表中的记录没有行锁呢,一行一行遍历肯定是不行,性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。
意向锁是表级锁,共有两种:
意向锁是由数据引擎自己维护的,用户无法手动操作意向锁,在为数据行加共享/排他锁之前,InooDB 会先获取该数据行所在在数据表的对应意向锁。
意向锁之间是互相兼容的。
IS 锁 | IX 锁 | |
---|---|---|
IS 锁 | 兼容 | 兼容 |
IX 锁 | 兼容 | 兼容 |
意向锁和共享锁和排它锁互斥(这里指的是表级别的共享锁和排他锁,意向锁不会与行级的共享锁和排他锁互斥)。
IS 锁 | IX 锁 | |
---|---|---|
S 锁 | 兼容 | 互斥 |
X 锁 | 互斥 | 互斥 |
《MySQL 技术内幕 InnoDB 存储引擎》这本书对应的描述应该是笔误了。
快照读(一致性非锁定读)就是单纯的 SELECT
语句,但不包括下面这两类 SELECT
语句:
SELECT ... FOR UPDATE
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
快照即记录的历史版本,每行记录可能存在多个历史版本(多版本技术)。
快照读的情况下,如果读取的记录正在执行 UPDATE/DELETE 操作,读取操作不会因此去等待记录上 X 锁的释放,而是会去读取行的一个快照。
只有在事务隔离级别 RC(读取已提交) 和 RR(可重读)下,InnoDB 才会使用一致性非锁定读:
快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。
当前读 (一致性锁定读)就是给行记录加 X 锁或 S 锁。
当前读的一些常见 SQL 语句类型如下:
# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对读的记录加一个S锁
SELECT...FOR SHARE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...
不太重要的一个知识点,简单了解即可。
关系型数据库设计表的时候,通常会有一列作为自增主键。InnoDB 中的自增主键会涉及一种比较特殊的表级锁— 自增锁(AUTO-INC Locks) 。
CREATE TABLE `sequence_id` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`stub` CHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
更准确点来说,不仅仅是自增主键,AUTO_INCREMENT
的列都会涉及到自增锁,毕竟非主键也可以设置自增长。
如果一个事务正在插入数据到有自增列的表时,会先获取自增锁,拿不到就可能会被阻塞住。这里的阻塞行为只是自增锁行为的其中一种,可以理解为自增锁就是一个接口,其具体的实现有多种。具体的配置项为 innodb_autoinc_lock_mode
(MySQL 5.1.22 引入),可以选择的值如下:
innodb_autoinc_lock_mode | 介绍 |
---|---|
0 | 传统模式 |
1 | 连续模式(MySQL 8.0 之前默认) |
2 | 交错模式(MySQL 8.0 之后默认) |
交错模式下,所有的“INSERT-LIKE”语句(所有的插入语句,包括:INSERT
、REPLACE
、INSERT…SELECT
、REPLACE…SELECT
、LOAD DATA
等)都不使用表级锁,使用的是轻量级互斥锁实现,多条插入语句可以并发执行,速度更快,扩展性也更好。
不过,如果你的 MySQL 数据库有主从同步需求并且 Binlog 存储格式为 Statement 的话,不要将 InnoDB 自增锁模式设置为交叉模式,不然会有数据不一致性问题。这是因为并发情况下插入语句的执行顺序就无法得到保障。
如果 MySQL 采用的格式为 Statement ,那么 MySQL 的主从同步实际上同步的就是一条一条的 SQL 语句。
最后,再推荐一篇文章:为什么 MySQL 的自增主键不单调也不连续 。
关于 MySQL 性能优化的建议总结,请看这篇文章:MySQL 高性能优化规范建议总结 。
可以是可以,直接存储文件对应的二进制数据即可。不过,还是建议不要在数据库中存储文件,会严重影响数据库性能,消耗过多存储空间。
可以选择使用云服务厂商提供的开箱即用的文件存储服务,成熟稳定,价格也比较低。
也可以选择自建文件存储服务,实现起来也不难,基于 FastDFS、MinIO(推荐) 等开源项目就可以实现分布式文件服务。
数据库只存储文件地址信息,文件由文件存储服务负责存储。
相关阅读:Spring Boot 整合 MinIO 实现分布式文件服务 。
可以将 IP 地址转换成整形数据存储,性能更好,占用空间也更小。
MySQL 提供了两个方法来处理 ip 地址
INET_ATON()
:把 ip 转为无符号整型 (4-8 位)INET_NTOA()
:把整型的 ip 转为地址插入数据前,先用 INET_ATON()
把 ip 地址转为整型,显示数据时,使用 INET_NTOA()
把整型的 ip 地址转为地址显示即可。
《Java 面试指北》(付费) 的 「技术面试题篇」 有一篇文章详细介绍了常见的 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)
各个字段的含义如下:
列名 | 含义 |
---|---|
id | SELECT 查询的序列标识符 |
select_type | SELECT 关键字对应的查询类型 |
table | 用到的表名 |
partitions | 匹配的分区,对于未分区的表,值为 NULL |
type | 表的访问方法 |
possible_keys | 可能用到的索引 |
key | 实际用到的索引 |
key_len | 所选索引的长度 |
ref | 当使用索引等值查询时,与索引作比较的列或常量 |
rows | 预计要读取的行数 |
filtered | 按表条件过滤后,留存的记录数的百分比 |
Extra | 附加信息 |
篇幅问题,我这里只是简单介绍了一下 MySQL 执行计划,详细介绍请看:SQL 的执行计划这篇文章。
读写分离和分库分表相关的问题比较多,于是,我单独写了一篇文章来介绍:读写分离和分库分表详解。
书籍推荐 。
文章推荐 :
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。