三天吃透 MySQL 八股文（2023 最新整理）

廖道健

本文已经收录到 Github 仓库，该仓库包含计算机基础、Java 基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等核心知识点，欢迎 star~
Github 地址：https://github.com/Tyson0314/Java-learning
事务的四大特性？

事务特性 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
  
• 一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比如 a 与 b 账户共有 1000 块，两人之间转账之后无论成功还是失败，它们的账户总和还是 1000。
  
• 隔离性。跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。
  
• 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
  

数据库的三大范式

第一范式 1NF

确保数据库表字段的原子性。

比如字段 userInfo: 广东省 10086' ，依照第一范式必须拆分成 userInfo: 广东省 userTel: 10086两个字段。

第二范式 2NF

首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。

举个例子。假定选课关系表为student_course(student_no, student_name, age, course_name, grade, credit)，主键为(student_no, course_name)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余（学生选 n 门课，姓名年龄有 n 条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记录）等问题。

应该拆分成三个表：学生：student(stuent_no, student_name, 年龄)；课程：course(course_name, credit)；选课关系：student_course_relation(student_no, course_name, grade)。

第三范式 3NF

首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

假定学生关系表为 Student(student_no, student_name, age, academy_id, academy_telephone)，主键为"学号"，其中学院 id 依赖于学号，而学院地点和学院电话依赖于学院 id，存在传递依赖，不符合第三范式。

可以把学生关系表分为如下两个表：学生：(student_no, student_name, age, academy_id)；学院：(academy_id, academy_telephone)。

2NF 和 3NF 的区别？

• 2NF 依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。
  
• 3NF 依据是非主键列是直接依赖于主键，还是直接依赖于非主键。
  

事务隔离级别有哪些？

先了解下几个概念：脏读、不可重复读、幻读。

• 脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
  
• 不可重复读是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。
  
• 幻读是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录。对幻读的正确理解是一个事务内的读取操作的结论不能支撑之后业务的执行。假设事务要新增一条记录，主键为 id，在新增之前执行了 select，没有发现 id 为 xxx 的记录，但插入时出现主键冲突，这就属于幻读，读取不到记录却发现主键冲突是因为记录实际上已经被其他的事务插入了，但当前事务不可见。
  

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

MySQL 数据库为我们提供的四种隔离级别：

• Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
  
• Repeatable read (可重复读)：MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。
  
• Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。
  
• Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。
  

查看隔离级别：


select @@transaction_isolation;





设置隔离级别：


set session transaction isolation level read uncommitted;





本文已经收录到 Github 仓库，该仓库包含计算机基础、Java 基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等核心知识点，欢迎 star~

Github 地址：https://github.com/Tyson0314/Java-learning

生产环境数据库一般用的什么隔离级别呢？

生产环境大多使用 RC。为什么不是 RR 呢？

可重复读(Repeatable Read)，简称为 RR 读已提交(Read Commited)，简称为 RC

缘由一：在 RR 隔离级别下，存在间隙锁，导致出现死锁的几率比 RC 大的多！缘由二：在 RR 隔离级别下，条件列未命中索引会锁表！而在 RC 隔离级别下，只锁行!

也就是说，RC 的并发性高于 RR。

并且大部分场景下，不可重复读问题是可以接受的。毕竟数据都已经提交了，读出来本身就没有太大问题！

互联网项目中mysql应该选什么事务隔离级别

编码和字符集的关系

我们平时可以在编辑器上输入各种中文英文字母，但这些都是给人读的，不是给计算机读的，其实计算机真正保存和传输数据都是以二进制 0101 的格式进行的。

那么就需要有一个规则，把中文和英文字母转化为二进制。其中 d 对应十六进制下的 64，它可以转换为 01 二进制的格式。于是字母和数字就这样一一对应起来了，这就是 ASCII 编码格式。

它用一个字节，也就是8位来标识字符，基础符号有 128 个，扩展符号也是 128 个。也就只能表示下英文字母和数字。

这明显不够用。于是，为了标识中文，出现了 GB2312 的编码格式。为了标识希腊语，出现了 greek 编码格式，为了标识俄语，整了 cp866 编码格式。

为了统一它们，于是出现了 Unicode 编码格式，它用了 2~4 个字节来表示字符，这样理论上所有符号都能被收录进去，并且它还完全兼容 ASCII 的编码，也就是说，同样是字母 d，在 ASCII 用 64 表示，在 Unicode 里还是用 64 来表示。

但不同的地方是 ASCII 编码用 1 个字节来表示，而 Unicode 用则两个字节来表示。

同样都是字母 d，unicode 比 ascii 多使用了一个字节，如下：


D   ASCII:           01100100





可以看到，上面的 unicode 编码，前面的都是 0，其实用不上，但还占了个字节，有点浪费。如果我们能做到该隐藏时隐藏，这样就能省下不少空间，按这个思路，就是就有了 UTF-8 编码。

总结一下，按照一定规则把符号和二进制码对应起来，这就是编码。而把 n 多这种已经编码的字符聚在一起，就是我们常说的字符集。

比如 utf-8 字符集就是所有 utf-8 编码格式的字符的合集。

想看下 mysql 支持哪些字符集。可以执行 show charset;

utf8 和 utf8mb4 的区别

上面提到 utf-8 是在 unicode 的基础上做的优化，既然 unicode 有办法表示所有字符，那 utf-8 也一样可以表示所有字符，为了避免混淆，我在后面叫它大 utf8。

mysql 支持的字符集中有 utf8 和 utf8mb4。

先说 utf8mb4 编码，mb4 就是 most bytes 4 的意思，从上图最右边的Maxlen可以看到，它最大支持用 4 个字节来表示字符，它几乎可以用来表示目前已知的所有的字符。

再说 mysql 字符集里的 utf8，它是数据库的默认字符集。但注意，此 utf8 非彼 utf8，我们叫它小 utf8 字符集。为什么这么说，因为从 Maxlen 可以看出，它最多支持用 3 个字节去表示字符，按 utf8mb4 的命名方式，准确点应该叫它 utf8mb3。

utf8 就像是阉割版的 utf8mb4，只支持部分字符。比如emoji表情，它就不支持。

而 mysql 支持的字符集里，第三列，collation，它是指字符集的比较规则。

比如，"debug"和"Debug"是同一个单词，但它们大小写不同，该不该判为同一个单词呢。

这时候就需要用到 collation 了。

通过SHOW COLLATION WHERE Charset = 'utf8mb4';可以查看到utf8mb4下支持什么比较规则。



如果collation = utf8mb4_general_ci，是指使用 utf8mb4 字符集的前提下，挨个字符进行比较（general），并且不区分大小写（_ci，case insensitice）。

这种情况下，"debug"和"Debug"是同一个单词。

如果改成collation=utf8mb4_bin，就是指挨个比较二进制位大小。

于是"debug"和"Debug"就不是同一个单词。

那 utf8mb4 对比 utf8 有什么劣势吗？

我们知道数据库表里，字段类型如果是char(2)的话，里面的2是指字符个数，也就是说不管这张表用的是什么编码的字符集，都能放上 2 个字符。

而 char 又是固定长度，为了能放下 2 个 utf8mb4 的字符，char 会默认保留2*4（maxlen=4）= 8个字节的空间。

如果是 utf8mb3，则会默认保留 2 * 3 (maxlen=3) = 6个字节的空间。也就是说，在这种情况下，utf8mb4 会比 utf8mb3 多使用一些空间。
索引

什么是索引？

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。

索引一般存储在磁盘的文件中，它是占用物理空间的。
索引的优缺点？

优点：

• 加快数据查找的速度
  
• 为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度
  
• 加快表与表之间的连接
  

缺点：

• 建立索引需要占用物理空间
  
• 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长
  

索引的作用？

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为 B+树的高度一般在 2-4 层，最多只需要读取 2-4 次磁盘，查询速度大大提升。
什么情况下需要建索引？

• 经常用于查询的字段
  
• 经常用于连接的字段建立索引，可以加快连接的速度
  
• 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度
  

什么情况下不建索引？

• where条件中用不到的字段不适合建立索引
  
• 表记录较少。比如只有几百条数据，没必要加索引。
  
• 需要经常增删改。需要评估是否适合加索引
  
• 参与列计算的列不适合建索引
  
• 区分度不高的字段不适合建立索引，如性别，只有男/女/未知三个值。加了索引，查询效率也不会提高。
  

索引的数据结构

索引的数据结构主要有 B+树和哈希表，对应的索引分别为 B+树索引和哈希索引。InnoDB 引擎的索引类型有 B+树索引和哈希索引，默认的索引类型为 B+树索引。

B+树索引

B+ 树是基于 B 树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 分别是 key<sub>i</sub> 和 key<sub>i+1</sub>，则该指针指向节点的所有 key 大于等于 key<sub>i</sub> 且小于等于 key<sub>i+1</sub>。



进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。

MySQL 数据库使用最多的索引类型是BTREE索引，底层基于 B+树数据结构来实现。


mysql> show index from blog\G;





哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的 key 值，将指向数据行的指针作为哈希表的 value 值。这样查找一个数据的时间复杂度就是 O(1)，一般多用于精确查找。
Hash 索引和 B+树索引的区别？

• 哈希索引不支持排序，因为哈希表是无序的。
  
• 哈希索引不支持范围查找。
  
• 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
  
• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而 B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。
  

为什么 B+树比 B 树更适合实现数据库索引？

• 由于 B+树的数据都存储在叶子结点中，叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是 B 树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以 B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁的，所以通常 B+树用于数据库索引。
  
• B+树的节点只存储索引 key 值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的 I/O 支出。
  
• B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
  

索引有什么分类？

1、主键索引：名为 primary 的唯一非空索引，不允许有空值。

2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一索引字段可以为 null 且可以存在多个 null 值，而主键索引字段不可以为 null。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。创建唯一索引的 SQL 语句如下：


ALTER TABLE table_name





3、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。

4、全文索引：只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

5、普通索引：普通索引是最基本的索引，它没有任何限制，值可以为空。
什么是最左匹配原则？

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。

对(a,b,c,d)建立索引，查询条件为a = 1 and b = 2 and c > 3 and d = 4，那么 a、b 和 c 三个字段能用到索引，而 d 无法使用索引。因为遇到了范围查询。

如下图，对(a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序（当 a 相等时，会根据 b 进行排序）。直接执行b = 2这种查询条件无法使用索引。



当 a 的值确定的时候，b 是有序的。例如a = 1时，b 值为 1，2 是有序的状态。当a = 2时候，b 的值为 1，4 也是有序状态。 当执行a = 1 and b = 2时 a 和 b 字段能用到索引。而执行a > 1 and b = 2时，a 字段能用到索引，b 字段用不到索引。因为 a 的值此时是一个范围，不是固定的，在这个范围内 b 值不是有序的，因此 b 字段无法使用索引。
什么是聚集索引？

InnoDB 使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为 6 个字节，它的值会随着数据的插入自增。
什么是覆盖索引？

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以 MySQL 使用 b+树索引做覆盖索引。

对于使用了覆盖索引的查询，在查询前面使用explain，输出的 extra 列会显示为using index。

比如user_like 用户点赞表，组合索引为(user_id, blog_id)，user_id和blog_id都不为null。


explain select blog_id from user_like where user_id = 13;





explain结果的Extra列为Using index，查询的列被索引覆盖，并且 where 筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。


explain select user_id from user_like where blog_id = 1;





explain结果的Extra列为Using where; Using index， 查询的列被索引覆盖，where 筛选条件不符合最左前缀原则，无法通过索引查找找到符合条件的数据，但可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。

本文已经收录到 Github 仓库，该仓库包含计算机基础、Java 基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等核心知识点，欢迎 star~
Github 地址：https://github.com/Tyson0314/Java-learning