关于mysql:MySQL-常见面试题知识点总结2021-最新版-JavaGuide

相干浏览：2.7w 字！Java 根底面试题 / 知识点总结！（2021 最新版）

这篇文章之前发过，不过，我最近对其进行了重构欠缺并且修复了很多小问题。所以，在公号再同步一下！

内容很硬！强烈建议小伙伴们花 10 分钟左右浏览一遍！

MySQL 根底

关系型数据库介绍

顾名思义，关系型数据库就是一种建设在关系模型的根底上的数据库。关系模型表明了数据库中所存储的数据之间的分割（一对一、一对多、多对多）。

关系型数据库中，咱们的数据都被寄存在了各种表中（比方用户表），表中的每一行就寄存着一条数据（比方一个用户的信息）。

大部分关系型数据库都应用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都反对事务的四大个性(ACID)。

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

MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite）……。

MySQL 介绍

MySQL 是一种关系型数据库，次要用于长久化存储咱们的零碎中的一些数据比方用户信息。

因为 MySQL 是开源收费并且比拟成熟的数据库，因而，MySQL 被大量应用在各种零碎中。任何人都能够在 GPL(General Public License) 的许可下下载并依据个性化的须要对其进行批改。MySQL 的默认端口号是3306。

存储引擎

存储引擎相干的命令

查看 MySQL 提供的所有存储引擎

mysql> show engines;

从上图咱们能够查看出 MySQL 以后默认的存储引擎是 InnoDB，并且在 5.7 版本所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 反对事务。

查看 MySQL 以后默认的存储引擎

咱们也能够通过上面的命令查看默认的存储引擎。

mysql> show variables like '%storage_engine%';

查看表的存储引擎

show table status like "table_name" ;

MyISAM 和 InnoDB 的区别

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，堪称是风光一时。

尽管，MyISAM 的性能还行，各种个性也还不错（比方全文索引、压缩、空间函数等）。然而，MyISAM 不反对事务和行级锁，而且最大的缺点就是解体后无奈平安复原。

5.5 版本之后，MySQL 引入了 InnoDB（事务性数据库引擎），MySQL 5.5 版本后默认的存储引擎为 InnoDB。小伙子，肯定要记好这个 InnoDB，你每次应用 MySQL 数据库都是用的这个存储引擎吧？

言归正传！咱们上面还是来简略比照一下两者：

1. 是否反对行级锁

MyISAM 只有表级锁 (table-level locking)，而 InnoDB 反对行级锁(row-level locking) 和表级锁, 默认为行级锁。

也就说，MyISAM 一锁就是锁住了整张表，这在并发写的状况下是如许滴憨憨啊！这也是为什么 InnoDB 在并发写的时候，性能更牛皮了！

2. 是否反对事务

MyISAM 不提供事务反对。

InnoDB 提供事务反对，具备提交 (commit) 和回滚 (rollback) 事务的能力。

3. 是否反对外键

MyISAM 不反对，而 InnoDB 反对。

🌈 拓展一下：

个别咱们也是不倡议在数据库层面应用外键的，利用层面能够解决。不过，这样会对数据的一致性造成威逼。具体要不要应用外键还是要依据你的我的项目来决定。

4. 是否反对数据库异样解体后的平安复原

MyISAM 不反对，而 InnoDB 反对。

应用 InnoDB 的数据库在异样解体后，数据库重新启动的时候会保障数据库复原到解体前的状态。这个复原的过程依赖于 redo log。

🌈 拓展一下：

• MySQL InnoDB 引擎应用 redo log(重做日志) 保障事务的 持久性 ，应用 undo log(回滚日志) 来保障事务的 原子性。
• MySQL InnoDB 引擎通过 锁机制、MVCC 等伎俩来保障事务的隔离性（默认反对的隔离级别是 REPEATABLE-READ）。
• 保障了事务的持久性、原子性、隔离性之后，一致性能力失去保障。

5. 是否反对 MVCC

MyISAM 不反对，而 InnoDB 反对。

讲真，这个比照有点废话，毕竟 MyISAM 连行级锁都不反对。

MVCC 能够看作是行级锁的一个降级，能够无效缩小加锁操作，提供性能。

对于 MyISAM 和 InnoDB 的抉择问题

大多数时候咱们应用的都是 InnoDB 存储引擎，在某些读密集的状况下，应用 MyISAM 也是适合的。不过，前提是你的我的项目不介意 MyISAM 不反对事务、解体复原等毛病（可是~ 咱们个别都会介意啊！）。

《MySQL 高性能》下面有一句话这样写到:

不要轻易置信“MyISAM 比 InnoDB 快”之类的经验之谈，这个论断往往不是相对的。在很多咱们已知场景中，InnoDB 的速度都能够让 MyISAM 可望不可即，尤其是用到了聚簇索引，或者须要拜访的数据都能够放入内存的利用。

个别状况下咱们抉择 InnoDB 都是没有问题的，然而某些状况下你并不在乎可扩大能力和并发能力，也不须要事务反对，也不在乎解体后的平安复原问题的话，抉择 MyISAM 也是一个不错的抉择。然而个别状况下，咱们都是须要思考到这些问题的。

因而，对于咱们日常开发的业务零碎来说，你简直找不到什么理由再应用 MyISAM 作为本人的 MySQL 数据库的存储引擎。

锁机制与 InnoDB 锁算法

MyISAM 和 InnoDB 存储引擎应用的锁：

• MyISAM 采纳表级锁(table-level locking)。
• InnoDB 反对行级锁 (row-level locking) 和表级锁, 默认为行级锁

表级锁和行级锁比照：

• 表级锁： MySQL 中锁定 粒度最大 的一种锁，对以后操作的整张表加锁，实现简略，资源耗费也比拟少，加锁快，不会呈现死锁。其锁定粒度最大，触发锁抵触的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都反对表级锁。
• 行级锁： MySQL 中锁定 粒度最小 的一种锁，只针对以后操作的行进行加锁。行级锁能大大减少数据库操作的抵触。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会呈现死锁。

InnoDB 存储引擎的锁的算法有三种：

• Record lock：记录锁，单个行记录上的锁
• Gap lock：间隙锁，锁定一个范畴，不包含记录自身
• Next-key lock：record+gap 临键锁，锁定一个范畴，蕴含记录自身

查问缓存

执行查问语句的时候，会先查问缓存。不过，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;

如上，开启查问缓存后在同样的查问条件以及数据状况下，会间接在缓存中返回后果。这里的查问条件包含查问自身、以后要查问的数据库、客户端协定版本号等一些可能影响后果的信息。因而任何两个查问在任何字符上的不同都会导致缓存不命中。此外，如果查问中蕴含任何用户自定义函数、存储函数、用户变量、长期表、MySQL 库中的零碎表，其查问后果也不会被缓存。

缓存建设之后，MySQL 的查问缓存零碎会跟踪查问中波及的每张表，如果这些表（数据或构造）发生变化，那么和这张表相干的所有缓存数据都将生效。

缓存尽管可能晋升数据库的查问性能，然而缓存同时也带来了额定的开销，每次查问后都要做一次缓存操作，生效后还要销毁。 因而，开启查问缓存要审慎，尤其对于写密集的利用来说更是如此。如果开启，要留神正当管制缓存空间大小，一般来说其大小设置为几十 MB 比拟适合。此外，还能够通过 sql_cache 和 sql_no_cache 来管制某个查问语句是否须要缓存：

select sql_no_cache count(*) from usr;

事务

何为事务？

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

能够简略举一个例子不？

事务最经典也常常被拿出来说例子就是转账了。如果小明要给小红转账 1000 元，这个转账会波及到两个要害操作就是：

1. 将小明的余额缩小 1000 元
2. 将小红的余额减少 1000 元。

事务会把这两个操作就能够看成逻辑上的一个整体，这个整体蕴含的操作要么都胜利，要么都要失败。

这样就不会呈现小明余额缩小而小红的余额却并没有减少的状况。

何为数据库事务？

数据库事务在咱们日常开发中接触的最多了。如果你的我的项目属于单体架构的话，你接触到的往往就是数据库事务了。

平时，咱们在议论事务的时候，如果没有特指 分布式事务 ，往往指的就是 数据库事务。

那数据库事务有什么作用呢？

简略来说：数据库事务能够保障多个对数据库的操作（也就是 SQL 语句）形成一个逻辑上的整体。形成这个逻辑上的整体的这些数据库操作遵循：要么全副执行胜利, 要么全副不执行。

# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
## 提交事务
COMMIT;

另外，关系型数据库（例如：MySQL、SQL Server、Oracle 等）事务都有 ACID 个性：

何为 ACID 个性呢？

1. 原子性（Atomicity）：事务是最小的执行单位，不容许宰割。事务的原子性确保动作要么全副实现，要么齐全不起作用；
2. 一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否胜利，转账者和收款人的总额应该是不变的；
3. 隔离性（Isolation）：并发拜访数据库时，一个用户的事务不被其余事务所烦扰，各并发事务之间数据库是独立的；
4. 持久性（Durabilily）：一个事务被提交之后。它对数据库中数据的扭转是长久的，即便数据库产生故障也不应该对其有任何影响。

数据事务的实现原理呢？

咱们这里以 MySQL 的 InnoDB 引擎为例来简略说一下。

MySQL InnoDB 引擎应用 redo log(重做日志) 保障事务的 持久性 ，应用 undo log(回滚日志) 来保障事务的 原子性。

MySQL InnoDB 引擎通过 锁机制、MVCC 等伎俩来保障事务的隔离性（默认反对的隔离级别是 REPEATABLE-READ）。

保障了事务的持久性、原子性、隔离性之后，一致性能力失去保障。

并发事务带来哪些问题?

在典型的应用程序中，多个事务并发运行，常常会操作雷同的数据来实现各自的工作（多个用户对同一数据进行操作）。并发尽管是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）: 当一个事务正在拜访数据并且对数据进行了批改，而这种批改还没有提交到数据库中，这时另外一个事务也拜访了这个数据，而后应用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，根据“脏数据”所做的操作可能是不正确的。
• 失落批改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也拜访了该数据，那么在第一个事务中批改了这个数据后，第二个事务也批改了这个数据。这样第一个事务内的批改后果就被失落，因而称为失落批改。例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 批改 A=A-1，事务 2 也批改 A=A-1，最终后果 A=19，事务 1 的批改被失落。
• 不可反复读（Unrepeatable read）: 指在一个事务内屡次读同一数据。在这个事务还没有完结时，另一个事务也拜访该数据。那么，在第一个事务中的两次读数据之间，因为第二个事务的批改导致第一个事务两次读取的数据可能不太一样。这就产生了在一个事务内两次读到的数据是不一样的状况，因而称为不可反复读。
• 幻读（Phantom read）: 幻读与不可反复读相似。它产生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查问中，第一个事务（T1）就会发现多了一些本来不存在的记录，就如同产生了幻觉一样，所以称为幻读。

不可反复读和幻读区别：

不可反复读的重点是批改比方屡次读取一条记录发现其中某些列的值被批改，幻读的重点在于新增或者删除比方屡次读取一条记录发现记录增多或缩小了。

事务隔离级别有哪些?

SQL 规范定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，容许读取尚未提交的数据变更，可能会导致脏读、幻读或不可反复读。
• READ-COMMITTED(读取已提交)： 容许读取并发事务曾经提交的数据，能够阻止脏读，然而幻读或不可反复读仍有可能产生。
• REPEATABLE-READ(可反复读)： 对同一字段的屡次读取后果都是统一的，除非数据是被自身事务本人所批改，能够阻止脏读和不可反复读，但幻读仍有可能产生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，齐全遵从 ACID 的隔离级别。所有的事务顺次一一执行，这样事务之间就齐全不可能产生烦扰，也就是说，该级别能够避免脏读、不可反复读以及幻读。

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

MySQL InnoDB 存储引擎的默认反对的隔离级别是 REPEATABLE-READ（可重读）。咱们能够通过 SELECT @@tx_isolation; 命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

这里须要留神的是：与 SQL 规范不同的中央在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下应用的是 Next-Key Lock 锁算法，因而能够防止幻读的产生，这与其余数据库系统 (如 SQL Server) 是不同的。所以说 InnoDB 存储引擎的默认反对的隔离级别是 REPEATABLE-READ（可重读） 曾经能够齐全保障事务的隔离性要求，即达到了 SQL 规范的 SERIALIZABLE(可串行化) 隔离级别。

🐛 问题更正：MySQL InnoDB 的 REPEATABLE-READ（可重读）并不保障防止幻读，须要利用应用加锁读来保障。而这个加锁度应用到的机制就是 Next-Key Locks。

因为隔离级别越低，事务申请的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容)，然而你要晓得的是 InnoDB 存储引擎默认应用 REPEATABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的状况下个别会用到 SERIALIZABLE(可串行化) 隔离级别。

🌈 拓展一下(以下内容摘自《MySQL 技术底细：InnoDB 存储引擎(第 2 版)》7.7 章)：

InnoDB 存储引擎提供了对 XA 事务的反对，并通过 XA 事务来反对分布式事务的实现。分布式事务指的是容许多个独立的事务资源（transactional resources）参加到一个全局的事务中。事务资源通常是关系型数据库系统，但也能够是其余类型的资源。全局事务要求在其中的所有参加的事务要么都提交，要么都回滚，这对于事务原有的 ACID 要求又有了进步。另外，在应用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。

后记

最初再举荐一个十分不错的 Java 教程类开源我的项目：JavaGuide。我在大三开始筹备秋招面试的时候，创立了 JavaGuide 这个我的项目。目前这个我的项目曾经有 100k+ 的 star，相干浏览：《1049 天，100K! 简略复盘！》。

对于你学习 Java 以及筹备 Java 方向的面试都很有帮忙！正如作者说的那样，这是一份：涵盖大部分 Java 程序员所须要把握的外围常识的 Java 学习 + 面试指南！

相干举荐：

• 图解计算机根底！
• 阿里 ACM 大佬开源的学习笔记！TQL!
• 计算机优质书籍收罗 + 学习路线举荐！

我是 Guide 哥，拥抱开源，喜爱烹饪。开源我的项目 JavaGuide 作者，Github：Snailclimb – Overview。将来几年，心愿继续欠缺 JavaGuide，争取可能帮忙更多学习 Java 的小伙伴！共勉！凎！点击查看我的 2020 年工作汇报！

参考

• 《高性能 MySQL》
• https://www.omnisci.com/techn…