数据库（MySQL）笔记与总结

前言

你好，我是苏青羽，是一名默默无闻的计算机爱好者。

本笔记是我在学习MySQL时所总结的学习笔记，主要记录了MySQL的一些基础知识点，在这里分享给大家，希望通过该笔记能帮助更多的人去理解MySQL。

笔记中参考了网上的很多博客、公众号、网课、面经等等，这些均在笔记末尾的参考资料中有所展示，大家可自行查看，做更深入的了解。

本笔记主要用于在学习上的复盘，并不适合初学者学习。如果在笔记中遇到了不懂的知识点，一定要去自己看书或者上网查询相关知识点！

如果发现本文有较大的硬性错误、或者是某些内容存在侵权、以及有什么想补充的问题和内容，请点击“关于”，通过里面预留的联系方式同我联系！

本笔记正在实时更新中，若是想获取笔记的最新PDF版以及了解关于我的更多信息，可扫描下方二维码，或微信搜索公众号“苏青羽”关注我！

前提基础

学习本笔记前，请事先掌握以下基础知识：

• C++基础
• SQL基础
• 数据结构与算法
• 操作系统

数据库概述

1. 数据库是什么？

(1) 数据（Data）：是一种用于描述事物的符号记录，也是数据库中存储的基本对象。

(2) 数据库（DataBase，DB）：是长期储存在计算机内、有组织的、可共享的大量数据的集合。

(3) 数据库管理系统（DataBase Management System，DBMS）：用于数据的定义、组织、存储和管理，是一种维护和管理数据库的系统软件。常见的DBMS有：MySQL、Redis。

(4) 数据库系统（DataBase System, DBS）：一般由DB、DBMS、应用系统和数据库管理员构成。

2. 数据管理技术的发展（为什么要使用数据库）？

(1) 人工管理：数据不保存，不共享，不具有独立性。

(2) 文件系统：数据可以永久保存，由文件系统管理，数据共享性差，冗余度大，独立性差。

(3) 数据库系统：数据结构化，共享性高，冗余度低，独立性高，由DBMS统一管理和控制。

3. 文件系统、操作系统和数据库管理系统之间的联系？

(1) 文件系统：是一种专门负责管理文件的系统。它决定了数据是以什么样的方式储存到磁盘里，可以理解为是一种“格式”或是“标准”。

(2) 操作系统：是最接近硬件的一层软件。操作系统给用户与应用程序提供了文件的读写接口，因此必须与某个文件系统进行合作，按文件系统的标准去操作文件。

(3) 数据库管理系统：是一种面向用户和应用程序的底层系统软件。通常需要运行在操作系统之上，调用操作系统提供的各种接口以提供服务。

4. 什么是关系型和非关系型数据库？

(1) 关系型数据库：它是由统一格式的二维表及其之间的联系所组成的一个数据组织。它易于维护，使用方便。支持通用的SQL语句，用户学习成本低，而且还支持复杂查询（单表/多表查询），功能强大。

(2) 非关系型数据库：只要不是关系型数据库，它就是非关系型数据库，实际上它是一种数据结构化存储方法的集合。非关系数据库的存储格式可以是文档、键值对等等，格式灵活，应用场景广泛。同时还支持使用内存作为存储载体，读写性能高。但不支持SQL，学习成本较高，在复杂查询方面要弱于关系型数据库。

MySQL概述

1. 什么是MySQL？

MySQL是一个关系型数据库管理系统，是最流行的关系型数据库管理系统之一。MySQL是用C/C++编写的，采用了单进程多线程的程序架构，支持通用的SQL语句。

2. 什么是SQL？

结构化查询语言，又称SQL，是一种用户与关系型数据库进行交互的通用语言。用户可以通过SQL语句，来实现对数据库中的数据进行增删改查。他们之间的关系可表示为：用户输入SQL->关系型数据库管理系统接收SQL->执行SQL->操作数据库->进行数据的增删改查。

3. MySQL的内部构造？

(1) MySQL可以分为服务层和存储引擎层两部分。

(2) 服务层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，所有跨存储引擎的功能都在这一层实现，比如触发器、视图等。

(3) 存储引擎层：负责数据的存储和提取，其架构模式是插件式的。支持InnoDB、MyISAM、Memory等多个存储引擎，InnoDB从MySQL 5.5版本开始成为了默认的存储引擎。

4. MySQL内部是如何执行一条SQL语句？

(1) 客户端：与MySQL服务器建立连接，并发起SQL请求。（服务器与客户端的连接采用半双工通信，一端发送消息，另一端必须接受到完整的消息才能进行响应）

(2) 连接器：与客户端建立连接，验证用户身份，可设置最大连接数和数据包大小上限。

(3) 查询缓存：收到SQL请求后会先查询是否存在缓存。由于命中率低，于MySQL8.0移除。

(4) 分析器：如果不命中缓存，则会由分析器对SQL语句进行词法分析和语法分析。

(5) 优化器：对SQL操作进行优化处理，如选择最佳索引，优化多表查询连接顺序。

(6) 执行器：执行器会使用存储引擎层提供的接口执行具体的SQL操作。在执行前还会判断用户是否具有相应权限，如果没有则会返回错误。

5. MySQL有哪些常见的数据类型？

(1) 整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。

①　任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。

②　整数类型可以被指定长度，但它不会限制值的合法范围，只会影响显示字符的个数。长度限制一般与UNSIGNED ZEROFILL属性配合使用，可以自动为插入的数字补前置0。

(2) 实数类型：FLOAT（单精度浮点型）、DOUBLE（双精度浮点型）、DECIMAL（定点型）。DECIMAL只是一种存储格式，CPU不支持DECIMAL运算，实际运算时会转变为DOUBLE。

(3) 字符串类型：VARCHAR（变长字符串）、CHAR（定长字符串）、TEXT（长文本）、BLOB（二进制形式的长文本）。

(4) 时间类型： DATETIME（日期时间）、DATE（日期）和TIMESTAMP（时间戳）。

6. CHAR和VARCHAR的异同？

(1) CHAR 是定长字符串，而 VARCHAR 是变长字符串，会根据字符串长度来分配空间。

(2) CHAR 和 VARCHAR都需要设置字符串长度。对于CHAR来说，这个值是它的固定预分配空间，而对于VARCHAR来说，这个值是它字符串长度动态变化的最大值。

(3) 如果插入的字符串小于预定长度，CHAR会在原字符后面填补空格，VARCHAR则不会。

(4) 在对字符串检索时，CHAR字符串后面的空格会被忽略，如插入字符串“char ”，但最后只会显示“char”，这与它的存储方式有关（会填充空格）。VARCHAR则不会。

(5) CHAR和VARCHAR在面对想插入超出最大长度的字符串时均会报错。

7. 为什么VARCHAR需要手动指定字符串最大长度？

这并不是件坏事，设置字符串的最大长度相当于是一种对数据的约束，避免用户存储无效数据。另外数据库管理系统对数据了解的越多，对SQL语句的优化能力就越强，效率就越高。

8. CHAR、VARCHAR和TEXT的使用场景？

(1) 对于经常变更的数据来说，CHAR比VARCHAR更好。因为数据变更会导致VARCHAR频繁地重新分配内存空间，产生内存碎片。

(2) 对于非常短的数据，CHAR比VARCHAR更好。因为VARCHAR会在数据开头使用额外空间来存储字符串长度，在结尾也会占用1字节表示字符串结束，在空间效率上不如CHAR。

(3) TEXT专门用于存储长文本，支持比CHAR和VARCHAR更大的存储空间。

(4) 从效率上来讲，基本是 CHAR> VARCHAR > TEXT。

9. 数据库的键是什么？

数据库的键（Key）是关系模型（表）的一个重要组成成分，是一种逻辑上的划分，与实际的物理结构无关，共分为以下几种：

(1) 超键（Super Key）：能够唯一标识某个数据行的字段集合，可以包含多个字段。

(2) 候选键（Candidate Key）：是最小的超键，即只包含一个字段。

(3) 主键（Primary Key）：是用户选定的候选键。一个表只能有一个主键，唯一且非空。

(4) 外键（Foreign Key）：如果在表中保存了另一个表的主键，则称为该表的外键。

10. 数据库的字段约束有哪几种？

在使用MySQL定义表时，可以在某个字段上加入内置的约束，以确保输入数据的有效性。

(1) 非空约束（NOT NULL）: 字段值不能为空（NULL）。

(2) 唯一约束（UNIQUE）: 字段值不能重复。

(3) 主键约束（PRIMARY KEY）: 该字段会作为表的主键（仅一个），具有非空和唯一约束。

(4) 外键约束（FOREIGN KEY）: 该字段会指向另一个表中的主键，不能出现主键之外的值。

(5) 检查约束（CHECK）: 控制字段值的有效范围，一般用于数字类型。

11. MySQL中，一张表的主键可以由多个字段组成吗？

可以。虽然在概念上，一张表的主键应该只能包含一个字段（候选键），但MySQL在设置主键约束时，却可以同时选中多个字段，将他们设置为表的联合主键。但是不建议这么去做，因为这会破坏数据库的第二范式，降低数据库性能。

12. 什么是临时表？

(1) 临时表就是临时的表，不具有永久性。临时表的作用域仅限于当前与MySQL服务器连接的进程，当连接关闭或者服务器重启时，临时表会被自动清除。

(2) 外部临时表：外部临时表由用户手动创建。外部临时表的存在无法被查询，但可以查询到里面的数据，由当前客户端进程独享，其他客户端进程无法查询其数据。

(3) 内部临时表：MySQL内部在执行复杂SQL时，一旦涉及到分组、排序、去重等操作，会自动生成内部临时表，以辅助SQL的执行。内部临时表的存在及其数据无法被查询。

13. 什么是MySQL的视图？

(1) 视图（view）是一种虚拟的表，本质是一个逻辑表，仅保存了Select语句，并不存储任何表数据。查询视图所得到的数据均来自于定义视图时所引用的基本表，并且在每次查询时都会根据当前数据动态生成。

(2) 视图具有以下特点：视图本身的建立和删除不影响基本表。对视图或者基本表中的数据进行修改时，都会引起另一方的变化。视图的列可以来自于多个不同的基本表，但是此时视图将不允许添加和删除数据（视情况可以允许更新数据）。

(3) 视图的优点以及用途：隐藏SQL语句，使用者不必知道底层的SQL语句就可以查询表数据。重用SQL语句，减少繁琐的SQL语句编写次数。保护重要的表数据，只给用户展现部分公开数据。

(4) 视图的缺点：如果某个基本表的结构被修改了，那么就需要及时维护所有引用它的视图，增加了数据库的维护成本。对于底层查询语句极度复杂的视图来说，维护会变得很困难。

14. 什么是MySQL的存储过程？

(1) 存储过程就是一组SQL语句集，相当于是对多个SQL语句的封装，于MySQL5.0版本开始支持，可以实现一些比较复杂的逻辑功能。

(2) 存储过程具有类似于编程语言中函数的特性：模块化、封装、复用SQL语句，有输入输出参数，可以在里面声明变量，具有if/else/case/while等控制语句，提供了“游标”来定位到表数据结果集的每一行数据。

(3) 存储过程是预编译的。只有首次执行时需要经历服务层的分析和优化，后续被调用可以省去以上步骤，直接调用存储引擎执行操作，速度比单纯的SQL语句更快。

(4) 缺点：存储过程的调试比较困难。当数据库的某个表发生更改后，还需要及时维护所有引用该表的存储过程。

15. 什么是MySQL的函数？

(1) MySQL中提供了很多内置函数，包括数字函数、字符串函数、日期函数等等。

(2) MySQL还允许用户自定义函数，可以在里面封装多个SQL语句，并返回特定值。

16. MySQL的函数和存储过程的区别？

(1) 存储过程： 一般是用来完成特定的数据操作（如插入更新修改），可以返回零或多个返回值，需要作为独立的SQL语句来执行，不能作为其他SQL语句的一部分。

(2) 函数：一般是用来进行数据的计算，至少返回一个结果值，不能独立执行，必须作为其他SQL语句的一部分来进行调用。

(3) 他们都是预编译的，即只编译一次，后续的调用全都跳过服务层，直接使用存储引擎。

17. 什么是MySQL的触发器？

(1) 触发器是一种特殊的存储过程，它是由事件驱动的。例如，当我们使用了一个SQL语句时，触发了某个预定义事件，那么它就会自动执行相应的存储过程（SQL语句集）。

(2) 创建触发器的四要素：监视地点（某张表）、监视事件（在表上的某个操作）、触发时间（在操作完成之前还是之后）、触发事件（自定义的存储过程）。

(3) 触发器的作用：实现多个表的级联更改，更新某张表的数据会自动更新与之相关的其他表。实现数据更改前的安全性检查，比起表的内置约束更具灵活性。

(4) 触发器的缺点：多个触发器之间可能会产生冲突。触发器比较隐蔽，难以被发现。滥用触发器会导致维护困难。

18. SQL语句有几种类型？

(1) 数据定义语言（Data Ddefinition Language，DDL）：定义数据库中的内容，包括表、视图和索引，如CREATE，DROP，ALTER。

(2) 数据查询语言（Data Query Language，DQL）：查询表中的数据，对应SQL关键字为SELECT，可以完成各种简单查询，连接查询等。

(3) 数据操纵语言（Data Manipulation Language，DML）：操作表中的数据，包括插入更新删除，如INSERT，UPDATE，DELETE。

(4) 数据控制语言（Data Control Language，DCL）：控制数据库的属性和SQL执行流，如GRANT，REVOKE，COMMIT，ROLLBACK。

19. 多表之间的关联查询有多少种？

(1) 交叉连接（CROSS JOIN）：结果集是两个表的笛卡尔积，行数 = 两个表行数的乘积。

(2) 内连接（INNER JOIN）：只有相匹配的行记录才会显示在结果集中，分为等值连接、不等值连接、自连接

(3) 外连接（LEFT JOIN/RIGHT JOIN）：一个表为主表，另一个表为次表。主表的行记录会全部显示，次表只会显示相匹配的行记录，分为左外连接、右外连接。

(4) 联合查询分为两种（UNION与UNION ALL）：可以将多个结果集合并为一个。UNION会合并相同的行记录，而UNION ALL则不会，因此效率较高。

(5) 全连接（FULL JOIN）：无论是否匹配，结果集都会显示所有的行记录。MySQL没有内置的全连接，但可以通过联合使用左外连接和右外连接来实现全连接。

20. 数据查询语句（SELECT）的执行顺序？

(1) SELECT中的每个步骤都会生成一个虚表作为临时结果集，然后将该结果作为下个步骤的输入。经过以下的执行顺序，最终输出用户想要的结果。

(2) FROM获取所有表数据并生成笛卡尔积->ON过滤笛卡尔积->JOIN添加主表中匹配失败的数据行->WHERE过滤数据->GROUP BY进行分组->计算分组函数->HAVING对分组后的数据进行过滤->SELECT查询数据->DISTINCT对数据行去重->ORDER BY对结果集进行排序->LIMIT/OFFSET指定返回的数据行

(3) 以上步骤仅是逻辑上的一种概念，在底层MySQL会对每个步骤做出各种不同的优化，以提高数据查询的效率，所以不必纠结底层的实现原理。

21. DROP、DELETE与TRUNCATE的区别？

22. 在进行子查询时，IN和EXISTS的差别？

(1) 我们常常会进行子查询，即嵌套的SELECT语句。IN和EXISTS都能作为子查询的过滤关键字来使用，他们之间的区别如下：

(2) IN：先进行子查询生成临时表，然后与主表生成笛卡尔积，在笛卡尔积中再进行外部条件的过滤。随着子查询表数据量的增大，会导致笛卡尔积的行数量以倍数形式增长，检查次数暴增，适合子查询的表数据量较小的情况。

(3) EXISTS：先进行主表的查询和过滤，然后遍历结果集中的每一行，对每一行进行相应子查询表的EXISTS判断。检查次数仅与主表行数相关，适合子查询的表数据较大的情况。

索引

1. 什么是索引？

索引（index）是一种帮助MySQL高效获取数据的数据结构，其中的数据会以某种方式指向原表中的数据。我们可以在索引上利用某种高级的查找算法，来帮助用户快速查找数据。

2. 索引的优缺点？

(1) 优点：索引类似于书籍的目录，可以提高数据检索的效率并减少数据库IO的成本。另外也可以通过索引列对数据进行排序，降低数据排序的成本，减少CPU的消耗。

(2) 缺点：索引也是一张表，需要占用额外的物理空间。创建索引需要耗费时间，而当对表中的数据进行增删改时，索引也要动态的维护，会降低增删改的执行效率。

3. 索引有哪几种类型？

(1) 普通索引： 最基本的索引类型，索引值可以重复和为NULL，创建仅为了提高查询效率。

(2) 唯一索引：索引值不允许重复，但允许为NULL值，一个表可以有多个唯一索引。

(3) 主键索引： 特殊的唯一索引，索引值不允许为NULL，一个表只能有一个主键索引。

(4) 联合索引：将表中的多个字段进行组合而创建的索引，一个索引会对应多个数据列。

(5) 全文索引： 它是一种通过关键字匹配来完成查询的索引。与其他索引的数值比较不同，它是一种基于相似度的查询，类似于模糊匹配（like），但它的效率更高。

4. 说说索引的数据结构？

MySQL的索引有多种可选的数据结构，选择哪一种和具体存储引擎的实现有关。

(1) B树：B树又称多路平衡查找树。它的所有结点都会存储关键字和数据，且具有唯一性，任何一个键值对都会出现且只出现在一个结点中。利用B树，我们可以使查询效率逼近于二分查找。

(2) B+树：B+树是B树的变体，是MySQL默认的索引数据结构。在B+树中，所有的非叶子结点仅起到索引的作用，不保存数据，只保存关键字及其指向子结点的指针。B+树的叶子结点会包含全部的关键字（也包含非叶子结点的关键字）及其相应的数据，它会按照关键字大小顺序进行排列，并且相邻叶子结点之间会链接起来，形成有序链表。

(3) Hash表：利用哈希算法建立关键字和哈希值的一一映射关系，等值查询效率极高，但容易发生哈希碰撞。

5. MySQL为什么使用B+树作为索引的底层数据结构？

这是因为相比起其他数据结构来说，B+树有着它独特的优势所在。

(1) 相比起Hash表：Hash表的优点是等值查询效率极高，但是查询性能不稳定，无法预测。而且Hash表还不支持排序，不支持范围查询，使用范围较为局限。

(2) 相比起B树：B树的优点是具有较低的树高度，查询时仅需遍历几个结点即可，磁盘I/O次数少。但由于B树的结点同时保存了关键字和数据，每个结点能容纳的关键字并不多，空间效率还能进一步改进。B树的每个结点都可能命中，越靠近根结点的数据越容易命中，虽然在某些场景下这种特点比较高效，但也因此导致查询效率不稳定。而且B树在范围查询上效率低下，还不支持顺序遍历。

(3) 相比起红黑树：目前MySQL基本不会用到红黑树，因为红黑树的高度较大，需要比B树更多的磁盘I/O次数，性能较低。

(4) B+树的优势：B+树空间利用率高，每个非叶子结点只保存关键字不保存数据，因此可以容纳更多关键字，间接导致树的高度下降，磁盘I/O次数进一步降低。B+树的查询效率稳定，任何关键字都只能在叶子结点中命中，每次查询遍历的结点数和树高度都是一样的。B+树支持顺序遍历和范围查询，利用B+树的叶子结点所形成的有序链表，可以高效地遍历和查询所有关键字。

6. 索引如何使用？

(1) 在where子句中使用已添加索引的字段作为查询条件，可以提高查询效率。

(2) 在order by子句中使用已添加索引的字段作为排序条件，可以提高排序效率。

(3) 用户在写SQL语句时也可以强制使用或禁止某个索引。

7. 索引为什么能提高查询效率？

(1) 如果没有索引，查询数据时需要遍历整张表，在表数据较多时，效率十分低下。

(2) 索引是一种有序的数据结构，在上面进行数据查找效率极高。

(3) 索引中保存了原表中的关键字及其数据的映射关系，有了索引之后，就不用去遍历全表，直接在索引上进行数据查找即可。

8. 使用索引一定能提高查询的性能吗？

通常来说索引查询效率较高，但因为创建与维护索引需要一定的时空代价，因此在数据量比较小的表上建立索引反而可能使效率降低。

9. 索引为什么可以提高排序效率（在order by子句中使用已建立索引的字段）？

(1) 当我们使用order by进行排序时，MySQL会对表中的所有数据使用外部排序，这会涉及到多次磁盘I/O，还会占用CPU资源，影响性能。

(2) 如果我们在作为查询条件的字段上建立索引，由于索引本身是有序的，因此可以直接通过索引顺序取出数据，而不需要进行外部排序了。

(3) 特别地，当我们想要取出排序后的某个范围的数据，也无需进行排序，直接根据索引进行范围查询并取出数据即可，效率极高。

10. 什么是聚簇索引与非聚簇索引？

(1) 根据索引中关键字与数据的映射关系，我们可以将索引区分为聚簇索引和非聚簇索引。

(2) 聚簇索引（一级索引、聚集索引）：索引中存放了关键字和完整的数据，找到索引也就找到了数据。

(3) 非聚簇索引（二级索引，辅助索引）：索引中只存放关键字以及指向原表数据的指针，找到索引后还需要通过指针进行回表查询，才能找到数据，需要两次访存，效率较低。

(4) 聚簇索引与非聚簇索引和存储引擎的具体实现有关，在InnoDB中存在聚簇索引和非聚簇索引，而在MyISAM中只存在非聚簇索引。

11. 说说联合索引和它的最左前缀匹配原则？

(1) 联合索引是一种组合了多个字段的索引，一个联合索引会对应多个数据列。

(2) 索引都是有序的，联合索引也不例外。但联合索引会根据创建索引时所输入的字段顺序，从左到右依次排序。比如建立”A、B、C”三个字段的联合索引，在索引底层会先按照A排序，如果A相同，则按照B排序，以此类推。

(3) 如果想要在某字段上命中联合索引，那么查询条件至少要包含在联合索引中该字段前面的所有字段（查询顺序会由优化器调整），否则无法命中，即最左前缀匹配原则。

(4) 根据联合索引的特性，在建立联合索引的时候应该注意字段间的顺序。一般情况下，会将最常作为查询条件的字段放在前面。

12. 为什么联合索引遇到范围查询就会停止匹配？

(1) 索引能提高查询效率的根本原因在于它的有序性，但在联合索引中，除了第一个字段外，后续的字段都具有全局无序，局部相对有序的特性。也就是只有在前一个字段值相等的情况下，当前字段才会有序排布，但他们从全局来看是整体无序的。

(2) 如果在联合索引的某个字段上使用范围查询，可能会匹配多个不等的字段值。在这个范围中，后面的整体字段值又变成了无序状态，导致索引无法被命中。因此使用范围查询的字段可以命中联合索引，但是之后的字段会停止索引的匹配，转而将之前命中索引的所有记录一一取出，然后再判断是否满足后续的查询条件。

(3) 比如联合索引为（A，B），查询条件为A > 10 and B = 2。第一个条件可以利用索引快速匹配所有记录，但第二个条件就只能在这个范围上进行线性扫描与过滤，效率较低。

13. 索引覆盖是什么意思？

(1) 在进行数据查询时，如果想要查询的字段都建立过索引，我们就称之为索引覆盖。

(2) 索引覆盖是解决避免回表查询的一种方案，因为索引中已经包含了所有要查询的字段，因此直接在索引中返回数据即可，不需要再回表查询，提高了查询效率。

(3) 索引覆盖在单列索引和联合索引上均可使用。但它通常用于联合索引，因为单列索引很难覆盖要查询的所有列。比如先创建A和B两个字段的联合索引，以A为查询条件，查找字段为A和B，即可触发索引覆盖。

(4) 有了索引覆盖，我们应该尽可能在 Select 后只写必要的查询字段，以增加触发几率。

14. 非聚簇索引查找数据时一定会回表查询？

不一定。比如索引覆盖，由于索引中已经包含了所有要查询的字段，那么就不用回表查询。

15. 单列索引和联合索引的优劣？

(1) 单列索引只会对表中的一个字段建立索引，使用简单方便。但如果我们将多个单列索引作为查询条件，此时只会命中一个索引（由优化器选择最佳索引）。

(2) 联合索引会对表中的多个字段建立索引，它的创建与使用较为麻烦，需要注意字段间的顺序。但如果能正确使用联合索引，那么查询条件中的所有字段均可命中索引。

16. 什么是索引下推？

(1) 索引下推（index condition pushdown，ICP）在MySQL5.6的版本上推出，用于优化联合索引的查询效率。

(2) 联合索引在遇到范围查询时会停止匹配，如果联合索引是非聚簇索引，那么会导致MySQL频繁地进行回表查询，效率较低。而ICP不会直接回表查询，而是在联合索引的内部就先进行后续查询条件的过滤，以此减少不必要的回表查询次数。

(3) 比如联合索引为（A，B），查询条件为A > 10 and B = 2。不使用ICP时，利于索引可以匹配到所有A>10的记录，然后回表查询数据，在查询过程中再进行B=2条件的判断。使用ICP后，由于联合索引包含有B字段，可以直接在联合索引中查找B = 2的记录，最后再统一回表查询。

(4) ICP并不会提高联合索引的匹配效率，只是减少了回表查询的次数。即使在联合索引中进行后续查询条件的过滤，也只用到了普通的线性扫描和过滤，并没有利用到索引特性。

(5) ICP的触发条件是：不完全命中联合索引（中间停止匹配），联合索引是非聚簇索引（需要回表查询），联合索引中包含了后续查询条件的字段。

17. MySQL对全文索引的支持情况？

(1) MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引。

(2) MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引。

(3) 只可以在CHAR、VARCHAR、TEXT或其他字符串类型上建立全文索引。

事务与锁

1. 事务是什么？

事务是一个具有原子性的数据库操作序列，也是数据库并发控制的基本单位，事务里的操作要么都执行，要么都不执行。

2. 什么是事务的四大特性（ACID）？

(1) 原子性（A）： 事务是最小的执行单位，事务里的操作要么都执行，要么都不执行。

(2) 一致性（C）： 在事务执行的前后，数据库的完整性和数据的正确性不能被破坏。

(3) 隔离性（I）： 多个并发事务之间互不干扰，它们所操作的数据是相对独立的；

(4) 持久性（D）： 一个事务被提交之后，会永久改变数据库中的数据。

3. 说说事务的并发问题（脏读、不可重复读和幻读）？

(1) 脏读(Drity Read)：一个事务读取到另一个事务尚未提交的数据。比如事务 A 读取事务 B 更新的数据，然后 B 进行了回滚，那么 A 读取到的数据是脏数据。

(2) 不可重复读(Non-repeatable read)：一个事务在执行中读取两次同样的数据，但结果不一致。 这可能是在两次查询过程中，提交了另一个事务，对原有的数据进行了更新。

(3) 幻读(Phantom Read)：一个事务在执行中查询了两次同个表的数据记录条数，但结果不一致。 与不可重复读类似，这可能是在两次查询过程中，提交了另一个事务，对原有的数据进行了更新。

4. 不可重复读和幻读的区别？

(1) 不可重复读的重点是修改，数据的内容在事务查询前后发生了变化。

(2) 幻读的重点在于新增或者删除，数据的记录条数在事务查询前后发生了变化。

5. 说说事务的四大隔离级别？

(1) READ-UNCOMMITTED(未提交读)：最低的隔离级别，允许当前事务读取其他事务尚未提交的数据更新。可能会导致脏读、幻读或不可重复读。

(2) READ-COMMITTED(提交读)：允许当前事务读取其他事务已经提交了的数据。可以阻止脏读，但是幻读或不可重复读仍有可能发生。

(3) REPEATABLE-READ(可重复读)：事务对同个数据的多次读取结果都是一致的，除非数据是被事务自己所修改。可以阻止脏读和不可重复读，但幻读仍有可能发生。

(4) SERIALIZABLE(串行化)：最高的隔离级别，所有的事务只能依次逐个执行。这样事务之间就完全不可能产生干扰，可以防止脏读、不可重复读以及幻读，完全满足事务的四大特性（ACID）。

(5) MySQL默认采用的隔离级别为REPEATABLE_READ（可重复读）。

6. MySQL的锁是什么？

当数据库有并发事务的时候，MySQL会使用锁机制来处理多个事务之间的互斥同步。注意，MySQL有着各种各样的锁，其实现与具体的存储引擎有关，并不是所有存储引擎都支持锁。

7. 从锁的粒度划分，MySQL的锁有多少种？

(1) 全局锁：是MySQL中锁定粒度最大的一种锁，它会针对整个数据库进行加锁。其他事务在锁被占用期间，无法对数据库中的所有表进行操作。全局锁一般用于全库备份或全库导出等全局一致性要求较高的场景下。

(2) 表级锁：是MySQL中锁定粒度较大的一种锁，会对事务当前操作的整张表加锁。表级锁开销小，加锁快，不会出现死锁（在多表操作中仍可能发生死锁），还被大部分MySQL存储引擎支持。但发生锁冲突的概率最高，事务并发度也最低。

(3) 行级锁：是MySQL中锁定粒度最细的一种锁，只针对事务当前操作的数据行进行加锁。行级锁开销大，加锁慢，会出现死锁。但发生锁冲突的概率最低，事务并发度最高。行级锁只能在事务的开始和提交之间才能锁定数据。若通过某个单独的SQL语句加上行级锁，那么它会在SQL语句结束的同时就释放掉。InnoDB存储引擎支持行级锁。

(4) 页级锁：是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁，表示对事务当前操作的数据行及其相邻的几个数据行进行加锁。页级锁的性能与事务并发度介于行级锁和表级锁之间，是一种折中的锁方案。BDB存储引擎支持页级锁。

8. 从锁的类型划分，MySQL的锁有多少种？

(1) 共享锁（Share Lock）：S锁，又称读锁，用于事务的只读操作。多个事务对于同一数据可以同时加多个读锁，都能访问到数据，但是只能读不能修改。读锁可以在事务读取数据结束后立即释放，无需等待事务结束。

(2) 排他锁（Exclusive Lock）：X 锁，又称写锁，用于事务的写操作。写锁仅允许一个事务对数据加锁，一旦锁被占用，其他事务就不能再获取该数据的其他锁，包括读锁和写锁。写锁必须等事务结束才能释放，其他事务也必须等写锁释放才能访问数据（读和写）。

9. 从锁的模式划分，MySQL的锁有多少种？

(1) 悲观锁（Pessimistic Lock）：每次加锁时都假定事务间会发生并发冲突，具有强烈的独占和排他特性。因此在整个数据处理过程中，悲观锁都会将数据设置为锁定状态。MySQL中传统的锁机制，如行锁和表锁、读锁和写锁都属于悲观锁。

(2) 乐观锁（Optimistic Lock）：每次加锁时都假定事务间会不发生并发冲突，采取了更加宽松的加锁机制。乐观锁在进行数据操作时不会检查和锁定数据，只在数据提交时检查是否发生了数据冲突。

10. 能详细说说乐观锁的实现机制吗？

(1) MySQL没有内置的乐观锁，大多是通过编程技巧来实现，常见的方式是版本号机制。

(2) 版本号机制会为数据增加一个版本标识，一般是给数据库的表增加一个 “version” 字段，以作为版本号。当事务读取表中的数据时，都会将版本号一同读出。

(3) 每次事务提交时，都会将之前读取的版本号和当前表的版本号进行比对。一旦发现当前的版本号要大于之前的版本号，就会判断事务中的数据已过期，立即停止事务提交并报错。而当事务成功提交时，表中的版本号则会自动加一。

11. 乐观锁和悲观锁的应用场景？

(1) 乐观锁适用于事务多读且少写的场景。在这样的场景下，事务间的并发冲突很少发生。使用乐观锁可以节省锁的开销，获得较高的并发性能。

(2) 悲观锁适用于事务多写的场景。该场景下一般会经常发生事务间的并发冲突，使用悲观锁较为安全。如果使用乐观锁，会导致事务不断报错，需要回滚重试，反而降低了效率。

12. 什么是记录锁、间隙锁、临键锁？

(1) MySQL的行锁根据其底层算法的不同，分为了记录锁、间隙锁和临键锁。

(2) 记录锁（Record Locks）：最简单的行锁，记录锁只会锁定表中的某一行记录。

(3) 间隙锁（Gap Locks）：又称区间锁，它每次都会锁定表中的一段区间。当加锁操作涉及某个范围时，会锁定该范围内的开区间，即使该区间内不存在数据。比如表中有三条数据“1、3、5”，当我们查询1~5之间的数据时，不仅会对符合条件的3进行加锁，还会对区间（1,5）进行加锁，其他事务无法在该区间内进行插入、删除等操作。间隙锁还支持对无穷范围进行加锁，比如查询大于5的数据，则会对区间（5，+∞）进行加锁，其他事务无法插入任何大于5的数据。

(4) 临键锁（Next-Key）：是MySQL默认使用的行锁，由记录锁与间隙锁组成。与间隙锁不同的是，无论是否涉及范围查询，临键锁都会自动锁住一段左开右闭的区间。临键锁实际就是在间隙锁的基础上，扩充区间范围至下条记录，并且连同下条记录一并锁住。比如表中有三条数据“1、3、5”，当查询条件为1时，会将（1,3]的区间给锁住。如果查询1~3之间的数据，则会将（1,5]的区间给锁住。

(5) 这三种行锁均由InnoDB提供，其实现依赖于索引，一旦某个加锁操作没有命中索引，那么该锁会退化为表锁。想要触发记录锁必须命中唯一索引，如主键ID。而间隙锁和临键锁只与非唯一索引相关，不会存在于唯一索引上。

(6) 间隙锁与临键锁都可用于解决幻读问题，确保数据的一致性和事务的隔离性。

(7) 间隙锁与临键锁虽然在某些场景下非常有用，但也有一些潜在的缺点，比如并发性能低，以及使用上的复杂性。

13. 什么是意向锁？

(1) 意向锁实际上是一种表锁，它的存在是为了协调行锁和表锁之间的关系。

(2) 当事务拥有行锁时，MySQL会自动为该表添加意向锁。其他事务如果想申请该表的表锁，那么不需要遍历每一行判断是否存在行锁，直接判断该表是否存在意向锁即可，提高了MySQL的性能。

14. 说说各种锁之间的关系？

(1) 全局锁、表锁、行锁、页锁他们都属于悲观锁，都拥有两种子类型，即读锁和写锁。

(2) 记录锁、间隙锁、临键锁属于行锁，而意向锁属于表锁。

(3) 一般来说，表锁与行锁不能共存。也就是说，同一张表不能被一个事务加上表锁，而又被另一个事务加上行锁。但是读锁没有此限制，表读锁与行读锁可以共存在一张表上。

15. 如何使用数据库的各种锁？

(1) 全局读锁：“FLUSH TABLES WITH READ LOCK”

(2) 表锁：“LOCK TABLES 表名 READ/WRITE”

(3) 行写锁：“SELECT…FOR UPDATE”

(4) 行读锁：“SELECT…LOCK IN SHARE MODE”

(5) 意向锁不用手动命令加上，只要加上行锁，MYSQL就会自动加上意向锁。

(6) 记录锁、间隙锁、临键锁都是行锁，使用方法与普通行锁没有什么不同，只取决于行锁的底层算法以及使用时的索引命中情况。

(7) 事实上，通过设置不同的隔离级别，在事务对数据进行操作时，数据库会自动给他们加上不同的锁，大部分情况下不需要手动加锁。

16. MySQL的锁保存在哪里？

锁是一种面向事务的机制，它的所有信息会跟随某个事务一同存放在内存，一旦事务结束，锁信息也会被自动清除。

17. 数据库的死锁是什么？

数据库中的死锁与操作系统中的死锁极为类似，多个并发事务会一直相互等待对方，导致所有事务均不能向前推进。只不过发生死锁的进程变成了数据库中的事务，需要访问的资源变成了数据库中的数据。

18. 如何判断数据库发生了死锁？

(1) 超时法：如果某个事务的等待时间超过了指定时限，则判定为出现死锁；

(2) 等待图法：InnoDB采用了一种叫作等待图（wait-for graph）的方法来自动检测死锁。

19. MySQL如何避免死锁？

(1) 查询时尽量命中索引，避免索引失效而使用粒度更大的表锁，使其他事务无法访问数据。

(2) 尽可能减少范围查询的范围区间，缩小间隙锁的锁定区间。

(3) 尽量控制事务的大小，减少事务锁定的数据量，缩短锁定的时间。

(4) 如果一条SQL语句涉及加锁操作，则尽量将其放在整个事务的最后执行。

(5) 尽可能使用低级别的事务隔离级别，隔离级别越低，数据库对锁的使用就越简单。

20.MySQL发生死锁后如何解决？

当死锁发生后，我们可以利用事务回滚，或者关闭部分造成死锁的事务来解除死锁。

存储引擎

1. 存储引擎是什么？

存储引擎是数据库的底层软件组织，数据库管理系统（DBMS）通过存储引擎来进行数据的增删改查。不同的存储引擎在存储机制、索引结构、事务与锁等方面各不相同，而且有些存储引擎还具有特定的功能，可以应用在某些特殊场景下。MySQL支持多种存储引擎，包括InnoDB、MyISAM、MEMORY和BDB。其中InnoDB是MYSQL的默认存储引擎。

2. 说说InnoDB？

InnoDB是MySQL5.5版本后的默认存储引擎。它在可靠性和高性能之间做了平衡，有以下特点：支持外键、支持事务、具有ACID特性、支持表级锁与行级锁、索引类型为B+树的聚簇索引、数据与索引集中存储于一个文件中、不支持全文索引、底层数据文件为FRM文件（表结构文件）和IBD文件（表数据文件）。

3. 说说MyISAM？

MyISAM是MySQL早期版本的默认存储引擎，具有以下特点：拥有较高的数据插入与查询性能，不支持外键、不支持事务、不具有ACID特性、仅支持表级锁、索引类型为B+树的非聚簇索引、数据与索引分别存放于不同的文件中，支持全文索引，底层数据文件为FRM文件（表结构文件）和MYD文件（表数据文件）以及MYI文件（表索引文件）

4. 说说MEMORY？

MEMORY是一种特殊的存储引擎，它的表数据仅存在于内存中，具有以下特点：表数据易丢失、数据I/O效率高、支持Hash索引和B+树索引，在等值查询方面性能较高、不支持可变长数据类型（如VARCHAR）、不支持大容量数据类型（如TEXT和BLOB）、只支持表级锁、底层数据文件为FRM文件（表结构文件）、表结构可长期保存。

5. 存储引擎对各种粒度的锁的支持？

(1) 表级锁：InnoDB、MyISAM、MEMORY、BDB

(2) 行级锁：InnoDB

(3) 页级锁：BDB

6. 各种存储引擎的适用场景？

(1) 想要支持事务以及并发控制，选择InnoDB即可。由于在各个方面都比较均衡，它成为当前MySQL的默认存储引擎，没有特殊要求无需更换。

(2) 如果数据表主要用于插入和查询数据，则MyISAM可以提供更高的性能。

(3) 如果仅存放临时数据，数据量不大，安全性要求不高，则可以选用MEMORY，它可以提供更高的数据IO效率。

(4) 使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种需求，使用合适的存储引擎，会提高整个数据库的性能。

7. 说说InnoDB的存储结构？

(1) InnoDB对于数据库中的每张表都会生成两个对应的表文件：FRM文件和IBD文件。FRM文件用于存储表结构等信息，文件相对较小。IBD文件用于存储表中的数据以及索引，文件大小取决于表中的数据量，通常我们会称它为“表空间”。

(2) 在MySQL5.7版本后，InnoDB会为每个表生成一个IBD文件，称为“独立表空间”。在此之前，数据库中所有表的数据和索引只会存在一个IBD文件中，称为“系统表空间”或“共享表空间”，“独立表空间”具有可压缩、可传输等优势。

(3) 在MySQL8.0版本后，InnoDB去掉了FRM文件，表结构等信息都集成在IBD文件中。

(4) 除了“独立表空间”和“系统表空间”，还存在“Undo表空间”、“通用表空间”、“临时表空间”，他们代表了五种不同的表空间文件类型。

8. 说说InnoDB的IBD文件物理结构（表空间结构）？

(1) 页：是InnoDB中内存和磁盘进行数据交互的最小单位。每次进行内存——磁盘的数据交互时，都至少会读写一个页的内容。每个页固定大小为16KB，内部地址连续。

(2) 行：每个页中的主体信息会被划分成一个个的行，而每个行又会存放表中的某行实际数据或者一些其他的附加信息。行大小最大为8KB，且大小不固定。

(3) 区：每个区固定为1MB，存放64个地址连续的页。在频繁读取同个区的多个页时，会将一整个区读入内存中，减少磁盘I/O次数，避免磁盘的随机I/O。

(4) 组：每个组会包含并管理256个区，大小固定为256MB，通过组可以高效地管理区。

(5) 段：段并非是一种物理结构，而是表空间的一种逻辑划分，每个段在物理地址上不一定连续。段被分为叶子结点段和非叶子结点段，与B+树的聚簇索引相对应。叶子结点段管理实际数据，非叶子结点段存储和管理B+树。

9. 详细说说IBD文件（表空间）中的页？

页类型有多种，最常见的是数据页，也称索引页。因为InnoDB采取了聚簇索引，找到了索引也就找到了数据，因此数据页=索引页。它的结构如下：

(1) 页头/页尾：存储该页的控制信息，包括页号，页类型和页校验和。特别地，页头会存储上下页号，以页号为指针，多个页可以连接成一个双向链表，方便查询。

(2) 数据行：每个行用于存放数据表中的某行实际数据或者一些其他的附加信息，页里的所有行会通过行指针形成一个单向有序链表（按主键大小排序）。

(3) 页目录：页目录类似于B+树中的非聚簇索引，其有序存放了多个行地址，而每个行地址又是由多个行所组成的行分组中最大行的地址，这样可以提高行链表的查找效率。

(4) 数据页头：存放了数据页中独有控制信息，包括行链表和页目录的地址。

10. IBD文件的页与文件系统的磁盘块有什么关系？

IBD的文件物理结构是建立文件系统之上的二次划分。InnoDB的页大小一般为文件系统所定义的磁盘块的整数倍（保证磁盘块的完整），它充分利用了空间局部性原理，将某个数据及其附近的数据一同读入内存，可以提高数据在内存的命中率，减少磁盘I/O次数。通常每个磁盘块为4KB，每个页默认为16KB，会包含4个磁盘块，没有数据时也会占用一整个页。

11. 详细说说IBD文件（表空间）中的行？

(1) 行在数据页中的组织方式是链表。当新页创建时，默认会在页的固定位置创建最大行和最小行，它们不存放任何实际数据，仅作为行链表的头尾标识。想要插入行时，会在头尾行之间查找合适的位置来进行插入（保证行按主键有序）。

(2) 在数据页中还会存在第二个行链表，被称为垃圾链表。里面的行都是被删除的无效行，因为行被删除时数据不会立即清理，而是先移动到垃圾链表中，方便回滚和管理。

(3) 行也有多种格式，数据页中的行默认为DYNAMIC，其结构如下所示（从右往左）。

①　真实数据：从左到右依次是主键值、最后一次修改该数据行的事务ID、回滚指针（指向Undo Log版本链，后文会介绍）、表中除主键和NULL值外的其他列数据（排列顺序与表中一致）。

②　头信息：存放行的控制信息，包括下一行指针、行类型、行位置等等。

③　NULL值列表：为了节省空间，表中所有NULL值均不存放在真实数据区，而是存放于此。其本质是一个位图，标识了所有可为NULL值的列，根据位值可判断该列是否为NULL。

④　可变字段长度列表：保存了数据行里所有变长字段的实际长度，以便于在真实数据区进行空间的预分配。常见的变长字段有VARCHAR、TEXT、BLOB，以及使用了utf-8或gbk等变长字符集的CHAR类型。

12. 行指针指向的是下一个行中的哪个区域？

每个行指针指向的都是下一个行中的真实数据的起始地址。由于每个行的大小不固定，将行指针指向这个位置后，向右就是真实数据，向左就是头信息，无需去计算每个行的实际大小。

13. 表中的行数据全都存放在数据页的对应行中吗？

不一定。当我们在行中使用TEXT或BLOB等超大数据类型，一旦其中存储的数据过大，超过了一个页的可用空间，那么就会将这些数据转而存储在另一种特殊页——“溢出页”中。而在原先存储数据的位置处，就会用一个大小固定的指针来指向该页。

14. 通过InnoDB创建的表一定会有主键吗？

是的，因为InnoDB的表空间结构直接依赖于主键，因此表必须要有主键，有以下三种情况。

(1) 在表创建时，由用户手动指定了主键字段，是最简单的情况。

(2) 如果不指定主键字段，InnoDB会优先使用一个具有非空和唯一约束的字段列作为主键。

(3) 如果上述情况均不满足，则会创建一个名为“DB_ROW_ID”的6字节递增隐藏列，作为表的隐藏主键。

15. InnoDB的索引结构？

我们知道，InnoDB的索引采取了B+树的聚簇索引。当我们使用InnoDB创建新表时，默认会以主键索引创建B+树，并存放在IBD文件（表空间）中，B+树的结点对应表空间的页。非叶子结点页的行存储了主键值和下一页地址，叶子结点页的行存放了所有的真实行数据，行与页都会按主键形成有序链表。InnoDB的B+树聚簇索引（主键索引）不用自己创建，每个表都会自动创建，有且唯一。

16. InnoDB可以使用非聚簇索引吗？

可以。在InnoDB中，只要不是在主键上所建立的索引，均为非聚簇索引。它的叶子结点上仅存储聚簇索引的索引值，因此在查找到相应的叶子结点后，还需要通过聚簇索引进行二次查询，而这也被称为InnoDB的回表查询。

17. 说说什么是自增ID和UUID？

(1) 自增ID和UUID是主键的特殊类型，在插入新的数据行时会自动生成相应的主键值。

(2) 自增ID在插入数据时会获取表中最大主键值并自动加一，以作为新数据行的主键值。

(3) UUID（通用唯一标识码，Universally Unique Identifier）在插入数据时，会根据一系列算法生成具有唯一性的字符串，以作为新数据行的主键。

18. 说说自增ID和UUID的特点和应用场景？

(1) 自增ID：有序，生成速度快，占用空间小，数据插入效率高（配合InnoDB的B+树聚簇索引，每次插入仅需向后添加叶子结点，无需频繁的跨页读写），安全性不高（主键ID容易被猜测从而暴露数据规模），高负载下性能差（多事务插入时会频繁导致最大叶子结点的锁竞争），不利于数据的迁移与扩展（自增ID仅有局部唯一性，使用分布式数据库可能会导致新插入的数据ID重复）

(2) UUID：无序且随机，全局唯一，生成速度慢，占用空间大，数据插入效率低（随机插入叶子结点，随机I/O性能差），安全性高，高负载下性能高，方便数据迁移与扩展。

(3) 根据自增ID和UUID的特点，我们可以在不同的场景下使用它们。比如需要数据迁移或对数据安全性要求较高时，UUID是更好的选择，其他情况使用自增ID会更加方便。

19. 了解InnoDB的Undo Log吗？

(1) Undo Log，又名回滚日志，是一种为了实现事务的回滚操作而设置的一种机制（日志）。

(2) 当每个事务进行数据的读写操作时，都会在磁盘中创建与之对应的Undo Log记录。当事务异常时，就会根据Undo Log记录逐一进行撤销操作，保证事务的原子性。

(3) Undo log是一种逻辑日志，记录了事务在表上的逻辑修改，比如新增、删除和更新。

20. 说说Undo Log是底层实现原理（如何实现回滚）？

(1) Undo Log存在于InnoDB的ibu文件（Undo表空间）中，Undo Log记录组成了Undo Log页，而Undo Log页又组成了Undo Log段。Undo Log记录也是一种行，在Undo Log页中会组织成单向链表，而多个Undo Log页又会组成双向链表。这样在进行事务回滚时，就可以通过Undo Log链表来顺序遍历这些Undo Log记录，进行数据的恢复。

(2) 对于插入操作来说，需要在Undo Log记录中存储新数据行的主键信息。在回滚时，可以根据该主键信息回表查询，然后删除对应数据行即可。

(3) 对于删除操作来说，需要在Undo Log记录中存储被删数据行的所有真实数据。在回滚时，可以根据这些数据来进行恢复。

(4) 对于更新操作来说，若不更新主键，需要在Undo Log记录中存储数据行未修改前的所有真实数据，在回滚时，可以根据这些旧数据来进行恢复。若是更新主键，则需要添加两条Undo Log记录，一条代表原数据行的删除，一条代表新数据行的插入。

21. Undo Log在事务提交后会被删除吗？

(1) Undo Log页分为两种：一种只记录插入操作，另一类只记录删除和更新操作。它们会组成不同的Undo Log链表，即Insert Undo链表和Update Undo链表。

(2) Insert Undo链表在事务提交之后就可以立即删除。Update Undo链表在事务提交之后不能立即删除，需要服务于MVCC机制（后文提及），由专门的线程来负责定时删除。

22. Undo Log如何体现在数据行中（数据行的回滚指针）？

(1) 在数据页中，每个数据行都保存了一个回滚指针，它指向一个Undo Log记录，而多个Undo Log记录通过自身的回滚指针串连起来，形成该数据行的Undo Log版本链。

(2) 在插入新数据行时，数据行的回滚指针会指向新产生的Insert Undo Log记录。该指针在事务提交后就置NULL，因为其Undo Log记录被删除了（对应于Insert Undo链表）。

(3) 在修改数据行时，数据行的回滚指针会指向新产生的Update Undo Log记录。该指针在事务提交后不置NULL，因为其Undo Log记录还保存在磁盘中（对应Update Undo链表）。

23. Undo Log链和Undo Log版本链的不同？

(1) Undo Log链服务于事务回滚，它们按照某个事务操作的顺序自动形成了链表，链表指针（行指针）保存在Undo Log记录的头信息中，实现了事务的逆序回滚。

(2) Undo Log版本链服务于MVCC，他们按照某个数据行的修改顺序自动形成了链表，链表指针（回滚指针）保存在Undo Log记录的真实数据区，实现了数据行的并发读取。

(3) 两者是独立存在的，一条Undo Log可以同时作为这两个链表的公有结点。

24. 了解InnoDB的Redo Log吗？

(1) Redo Log，又名重做日志，是一种保证事务持久性的机制（日志）。

(2) 在事务操作数据之前，会先将数据的修改信息写入磁盘的Redo Log中。这样，即使因故障而导致事务提交的数据没能更新到磁盘中，也可以根据Redo Log中的信息来进行数据恢复，保证了事务的持久性。当事务成功将数据更新到磁盘中时，旧的Redo Log就可以删除，释放被占用的空间。

(3) Redo Log是一种物理日志，记录了表空间数据页的修改信息，而不去记录导致数据修改的特定操作，如插入、删除和更新。

25. 说说Redo Log的刷盘时机（在何时写入磁盘中）？

(1) Redo Log分为两部分：在内存中的Redo Log缓冲区和在磁盘上的Redo Log文件。在写入Redo Log时，首先会将Redo Log写入到缓冲区中，之后根据不同的刷盘策略将Redo Log更新到磁盘中的文件。刷盘策略有三种，均发生在事务提交前：

①　策略1：默认策略，缓冲区每多一条Redo Log记录，就立即将其刷新到磁盘中。

②　策略0：每隔1秒将Redo Log记录从缓冲区写入到磁盘中。

③　策略2：缓冲区每多一条Redo Log记录，就将它同时写入操作系统负责的缓冲区中，之后每隔1秒由操作系统负责进行刷盘。

(2) 除了预定义的刷盘策略之外，在缓冲区数据过大、事务被提交、MySQL被关闭等情况下，也会立即进行Redo Log的刷盘操作。因此Redo Log不一定是事务提交时才写入到磁盘文件中，而是在事务的执行过程中逐步写入，这能减少事务的提交等待时间。

26. 什么是Buffer Pool？

(1) Buffer Pool是InnoDB在内存中的缓冲区。由于内存的速度远快于硬盘，因此InnoDB每次对数据的操作都会先作用于该缓冲区，然后再间接作用于磁盘的数据库文件，InnoDB会通过多个I/O线程来进行磁盘文件与内存缓冲区的数据同步。

(2) Buffer Pool与磁盘进行数据交互的单位是页，它们在内存中以链表的形式存在，比如Flush链表（需要刷新回磁盘的页）和LRU链表（空间不足时优先被淘汰的页）。

(3) Buffer Pool包含InnoDB的所有内存缓冲区，如Undo Log缓冲区和Redo Log缓冲区。

27. 什么是InnoDB的四大特性？

插入缓冲（写缓冲）、二次写、自适应哈希、预读。

28. 什么是InnoDB的插入缓冲（Insert Buffer）或写缓冲（Change Buffer）？

(1) 在对非聚簇索引的插入或更新时，会先判断该非聚簇索引页是否在Buffer Pool中。如果在则直接插入，如果不在则先放入内存的Insert Buffer中，后续再一次性更新到磁盘。

(2) Insert Buffer只作用于非聚簇索引上。如果插入数据需要同时更新聚簇索引和非聚簇索引，那么在聚簇索引上的插入往往是有序的（自增ID），而在非聚簇索引上就成了随机插入，导致读写性能下降。利用Insert Buffer可以将多次读写转为一次，优化性能。

(3) Insert Buffer只作用于非唯一索引上。如果是唯一索引，在插入数据时需要校验索引值的唯一性，此时仍需要回盘读写相应的索引页，又增加了读写开销。

(4) 在MYSQL5.5之前，Insert Buffer只针对insert做了优化。而现在对delete和update等操作也有效，因此也称为写缓冲（Change Buffer）。

29. 什么是InnoDB的二次写（Double Write）？

(1) 在将Buffer Pool中的页写回磁盘前，会预先写入到内存中的Double Write Buffer并更新到磁盘的系统表空间，最后再将这些页写回到原来的各个独立表空间。

(2) 其目的是为了解决InnoDB的部分写失效。一个页会占据多个磁盘块，而操作系统每次都是以磁盘块为单位进行读写，有可能在页仅写入到磁盘一部分时，就发生了故障，导致磁盘中产生了残缺页。利用二次写，预先将页进行写入，即使发生故障也可以恢复。

(3) 部分写失效无法根据Redo Log恢复。Redo Log作用的单位是事务，在恢复数据时需要完整页的部分数据配合，而残缺页则无法完成该功能，因此必须使用二次写来解决。

30. 什么是InnoDB的自适应哈希索引(AHI)？

InnoDB会监控数据库的表查询情况，根据一些规则为经常访问的页建立哈希索引，以提高这些“热点页”的查询速度，这个过程无需用户干预。

31. 什么是InnoDB的预读(Read Ahead)？

预读机制会根据某种算法，预测用户后续可能用到的某些页，并提前读取到内存中，以减少磁盘的I/O次数。一般来说当用户频繁读取一个区中的多个页时，就会将整个区一同读出。

32. 说说InnoDB是如何处理数据读写的？

(1) 读（Read）：

①　通过Buffer Pool的自适应哈希索引（AHI）进行数据的查找并返回。

②　如果AHI未命中，则会读取所需的磁盘页到Buffer Pool，并返回数据。

③　读取磁盘页时会触发预读（Read Ahead），将多个页一同读取。

④　InnoDB会以多个链表的方式来管理Buffer Pool中的页，如Flush链表和LRU链表。

(2) 写（Write）：

①　将写操作形成的Undo Log记录写到Undo Log缓冲区和磁盘中的Undo表空间。

②　将写操作形成的Redo Log记录写到Redo Log缓冲区和磁盘中的Redo Log文件。

③　将真实数据写入到Buffer Pool中的对应页。

④　根据是否会影响非唯一非聚簇索引，判断数据是否要写入插入缓冲（Insert Buffer）。

⑤　使用二次写（Double Write）提前将页写回磁盘的系统表空间，避免部分写失效。

⑥　InnoDB的I/O线程会将Buffer Pool的脏数据在合适的时机写回磁盘的独立表空间。

33. 说说InnoDB的MVCC机制？

(1) 多版本并发控制（Multiversion Concurrency Control，MVCC）是一种类似于乐观锁的机制，它保存了每个数据行的历史版本，在事务查询数据时可以通过比较版本号（事务ID）来决定是否显示数据，实现了不需要加锁就可以保证事务隔离性的功能。

(2) 实现MVCC需要Undo Log版本链。每个数据行都有一个回滚指针，它指向一个Undo Log版本链，其中每个Undo Log记录都保存了上一版本数据行的旧数据。

(3) 实现MVCC需要读视图（Read View）。它是一种数据结构，在事务执行过程中创建，其中包含了活跃事务ID集合（创建读视图时未提交的事务），活跃事务的最小ID和最大ID，创建读视图的当前事务ID。（事务ID有自增性，每创建一个事务都会自动加一）

(4) MVCC在事务查询数据时触发，其规则如下（已存在Undo Log版本链和读视图）：

①　判断数据行的事务ID是否与当前事务ID相同。若是，则意味着读取自己修改的数据，允许读取。若不是，则进入下一步。

②　判断数据行的事务ID是否小于活跃事务最小ID。若是，则意味着在该数据行版本在生成读视图前已经提交，允许读取。若不是，则进入下一步。

③　判断数据行的事务ID是否大于活跃事务最大ID。若是，则意味着在生成读视图后，该数据行又被新开启的其他事务修改，不允许读取。若不是，则进入下一步。

④　判断数据行的事务ID是否在活跃事务ID集合中。若是，则该数据行的修改还未提交，不允许读取。若不是，则该数据行版本在生成读视图时刚好被提交，允许读取。

⑤　在判断数据行或Undo Log记录中的数据不允许读取后，会自动遍历Undo Log版本链的下条记录，直到找到满足上述规则的数据。

34. InnoDB的MVCC可以解决什么问题？

(1) 通过 MVCC 可以让读写互相不阻塞，读不互相阻塞，写不阻塞读，提高事务并发度。

(2) MVCC 采用了类似乐观锁的方式，读取数据时不加锁，降低了死锁的概率。

(3) 解决了事务的脏读问题。在事务每次查询时都建立新的读视图（Read View），保证每次都可以读取数据库中其他事务最新提交的数据，同时又不会读取未提交的数据。

(4) 解决了事务的不可重复读问题。在事务启动后，首次查询会建立读视图（也可以设置在事务启动时就会立即建立读视图）,之后的所有查询都会基于该读视图读取数据。这样事务只能看到某个时间点之前其他事务的提交，而不能看到之后其他事务的提交。

35. 什么是快照读和当前读？

(1) 快照读：在读取数据时，会使用MVCC机制来寻找符合要求的数据版本，并进行读取。

(2) 当前读：不使用MVCC机制，直接读取数据库中的最新数据。

36. InnoDB是如何实现事务隔离的？

(1) InnoDB使用了MVCC+锁的联合处理来实现事务的四个隔离级别，在每个隔离级别上对数据库的读操作（select）和写操作（delete、update）都设置了不同的行为。

(2) 如果仅用锁实现事务隔离，会容易导致事务并发度下降，读写性能降低，死锁等情况，而MVCC可以很好地解决这些问题，两者通常搭配适用。

37. InnoDB是怎么实现事务的四个隔离级别？

(1) READ-UNCOMMITTED(未提交读)：读取数据时使用当前读，不使用MVCC和锁。更新数据时加上行读锁，在事务结束后释放，其他事务在当前事务结束前不能修改该数据，但可以读取该数据。

(2) READ-COMMITTED(提交读)：读取数据时使用快照读，每次查询时会构造新的读视图，以解决脏读问题。更新数据时加上行写锁，在事务结束后释放，其他事务在当前事务结束前不能读写该数据。

(3) REPEATABLE-READ(可重复读)：读取数据时使用快照读，事务执行中只会构造一次读视图，以解决脏读和不可重复读。更新数据时加上临键锁（Next-key），在事务结束后释放，其他事务在当前事务结束前不能读写、插入和删除某个范围内的数据，可以解决部分幻读问题（不能完全解决）。

(4) SERIALIZABLE(可串行化)：读取数据时对一整张表加上表读锁，事务结束后释放，使用当前读，读取最新版本的数据。更新数据时对一整张表加上表写锁，事务结束后释放，完全串行操作，解决了所有事务并发问题，但效率极低。

38. InnoDB如何实现事务的四大特性？

(1) 原子性：通过Undo Log实现。

(2) 隔离性：通过隔离级别实现。

(3) 持久性：通过Redo Log和Double Write实现。

(4) 一致性：数据库必须要先实现上述三大特性，才能实现一致性。

数据库优化

1. 数据库三大范式是什么？

范式是构建表时需要遵循的某种规则。利用范式，可以降低数据冗余，提高数据库性能。

(1) 第一范式（1NF）：每个字段都是表中的最小单元列，互不重复且不可分割。

(2) 第二范式（2NF）：在第一范式的基础上设置了单一主键。即主键只能包含一个字段，其他字段只能依赖于整个主键，而不能依赖于主键中的部分字段（部分依赖）。

(3) 第三范式（3NF）：在第二范式的基础上，除主键外的其他字段都只能依赖于主键，而不会互相依赖（传递依赖），比如C依赖B，B又依赖主键，这就导致了传递依赖。

(4) 除了三大范式外，还有BC范式和第四范式，但规则过于严苛，很难用上。

2. 表可以不设置主键吗？

抛开具体的存储引擎不谈，MySQL并没有规定一定要为表设置主键。但主键是数据行在整张表中的唯一性保障，它可以加快数据库的操作效率，确保输入数据的有效性。即使业务表里没有明确的主键，也建议添加一个额外的辅助字段作为主键。

3. 字段定义为非空约束有什么好处？

(1) 安全。空值经常被用户所忽略，导致没有做非空判断，从而出现不可预估的错误。

(2) 效率。空值为了节省空间，采用了位图的方式来保存，而不是具体的数值或字符串，这也导致了空值无法利用索引，效率低下。

4. 什么情况下索引会失效，从而使查询转为全表扫描？

(1) 在建立索引的字段上使用函数或数学运算。

(2) 在查询时使用“!=”、“not in”或 “<>”等操作符。

(3) 在建立索引的字段上使用空值（NULL）判断。

(4) 在使用联合索引时，查询条件不包含索引中的第一个字段。

(5) 在使用联合索引时，进行了范围查询，会导致后续的所有字段均索引失效。

(6) 如果在使用like语句时，正则表达式以“%”开头，则索引失效。

5. 索引的设计原则？

(1) 尽可能创建选择唯一索引，由于值唯一，查询效率更高。

(2) 常作为查询条件的字段更适合创建索引，有利于提高整个表的查询速度。

(3) 常作为排序条件的字段更适合创建索引，有利于提高排序效率。

(4) 数据更新频繁的字段不适合创建索引，因为此时还需要频繁进行索引的动态维护。

(5) 尽量在数据长度较小的字段上建立索引，数据长度太大会影响查询效率。

(6) 尽量限制索引的数量，减少额外的索引维护成本。

(7) 小表不适合建立索引，优化效果不明显，有可能使用索引反而还降低了查询效率。

6. 我们能从EXPLAIN的Type列了解到什么？

EXPLAIN是一种查询SQL语句执行情况（执行计划）的命令，通过它可以了解SQL语句的某些信息，然后进行适当的优化。在显示的结果中，最重要的是TYPE字段，它显示了SQL语句的索引命中情况，按效率由高到低可能会出现以下字段值。

(1) Const/System：对主键或唯一索引的等值查询，速度很快。System是特殊的Const，即表中仅有一行数据，速度最快。由于InnoDB无法预判，因此System只出现在MyISAM。

(2) Eq_ref：在等值连接的多表查询中，连接条件包含了某张表的主键或唯一索引。

(3) Ref：非聚簇索引的等值查询，并满足联合索引的最左原则。

(4) Ref_or_null：在Ref的基础上加上了空值的判断。

(5) Index_merge：任意索引的等值查询，需要回表查询数据，然后合并多个结果集。

(6) Range：任意索引的范围查询。

(7) Index：全表扫描，但只需要遍历索引树，获取索引值即可。

(8) All：全表扫描，需要遍历所有行中的其他数据列。

7. 我们能从EXPLAIN的Extra列了解到什么？

Extra是Explain所显示的字段之一，表示SQL语句执行时的额外信息，以下是常见字段值。

(1) Using Index：触发索引覆盖，无需回表查询。

(2) Using Index Condition：触发索引下推，减少回表查询次数。

(3) Using Where：无法触发索引下推，需要较多的回表查询次数。

(4) Using MRR：对非聚簇索引的范围查询缓存并排序，然后再回表查询，优化为顺序I/O。

(5) Using Temporary：在SQL执行过程中用到了临时表，效率较低。

(6) Using Filesort：在SQL执行过程中需要额外排序，效率较低。

8. 如何优化SQL语句，提高数据库的查询效率？

(1) 为表创建合适的索引，尽量在SQL语句中去使用和命中索引，避免全表扫描。

(2) 在查询时尽可能触发索引的底层优化机制，如索引覆盖，索引下推等。

(3) 在查询过程中尽可能避免使用临时表和外部排序。

(4) 使用Explain命令来分析SQL语句，寻找更多可能的优化方向。

9. 什么是垂直分表和水平分表？

分表是数据库的一种优化策略，可以减少数据库的单表负担，提高单表查询性能，缓解表锁的竞争问题。 分表策略可以归纳为垂直分表和水平分表。

(1) 垂直分表：拆分表的数据列，将多个列单独作为一张表，且每张分表都带有原表的主键。它简化了单表的复杂结构，缩减了每一个行记录的数据量，减少了磁盘I/O次数。但主键会在多个表出现冗余，需要额外的维护成本。

(2) 水平分表：保持表结构不变，把多个行记录单独作为一张表。它可以避免单表的数据量过大，但在查询时通常需要连接多个表，增加了查询语句的复杂性。

10. 了解MySQL的BinLog吗？

(1) BinLog，又称二进制日志，它提供了数据库变更历史查询、数据库备份和恢复、主从复制等功能。它是一种跨存储引擎的逻辑日志，所有存储引擎均可使用。它存在于服务层，可以被用户查询和使用。

(2) BinLog存储了数据的变更记录，有三种写入格式：

①　statement：记录了每条导致数据变更的SQL语句。在进行数据恢复时仅需按序执行SQL语句即可，日志量小，性能较高。但是需要保存SQL语句及表相关的上下文信息，部分特殊的SQL语句还无法被保存，存在局限性。

②　row：记录了所有数据行记录的更改信息。可以保存和恢复所有数据，适用于各种场景，但是保存的日志量大，性能低。

③　mixed：一种折中的方案，一般使用statement模式，无法使用statement时再使用row。

11. 说说MySQL的主从复制和读写分离？

主从复制和读写分离是指将数据库存放在多个服务器上，主库负责写，从库负责读。通过该机制，可以降低单个服务器数据库的压力，同时在多个服务器中保存有相应的数据库备份，避免单点故障造成的严重影响。

12. MySQL是如何将数据库同步到多个服务器上？

BinLog是实现这一机制的关键，它可以实现不同服务器的数据库同步。主要流程如下：

(1) 主库把数据的变更记录保存到二进制日志（BinLog）中，当有从库连接时，创建推送线程将BinLog推送至从库。

(2) 从库创建I/O线程，连接主库并将主库的二进制日志里的更新记录拷贝了到本地的中继日志（RelayLog）中。

(3) 从库创建SQL线程读取中继日志，执行其中的数据更新，完成同步。

13. 主从复制有多少种复制方式？

(1) 异步复制：MySQL 默认为异步复制，无需等待从库，效率较高，但不够安全，有可能从库复制失败，主库就返回数据更新已成功。

(2) 同步复制：每次数据更新都需要等待所有从库的响应，效率极低，故没有相应实现。

(3) 半同步复制：在数据更新时，只要至少有一个从库复制成功，就立即返回数据更新已成功。如果从库未响应，则主库会等待一段时间后重新推送，如果仍失败则立即切换异步复制以保证效率，直到从库恢复再切回半同步复制。由于它的复制成功仅指BinLog，而不管从库是否从RelayLog中恢复了数据，因此会存在一定的延迟，也不够安全。

14. 主从复制有多少种架构？

(1) 一主多从：一台主库服务器，多台从库服务器。读取数据时通过负载均衡分配到多个从库上读取，更新数据时写入主库，并同步更新到多个从库中。由于同步操作需要额外的时间开销，因此适合对实时性要求不高的场景。

(2) 双主从（主主复制）：两个服务器互为主从，两台服务器既可以读取数据也可以更新数据。性能高，速度快，故障时可立即切换服务器，无需将从库转变为主库的额外操作。

(3) 多主一从：多台主库服务器，一台从库服务器，在MySQL5.7以上版本支持。所有数据可集中在一台从库服务器读取与备份，不需要读取与合并其他从库，节省成本。

(4) 联级复制：在一主多从的基础上，在主库和各个从库之间增加了一个二级主库。二级主库仅负责将一级主库推送的BinLog推送给各个从库，以此减轻一级主库的推送压力。

15. 数据库怎么优化，有什么方案？

(1) 根据业务需求，选择合适的存储引擎。

(2) 范式优化，尽可能遵循三大范式，减少冗余。

(3) 反范式优化，使用合适的分表策略，增加冗余，是一种用空间换时间的方案。

(4) 主从复制和读写分离。

(5) 增加缓冲机制。

(6) 集群（多个相同功能的服务器）和分布式（多个不同子功能的服务器协同完成任务）

16. 什么是数据库的冷备份和热备份？

(1) 冷备份：在数据库已经正常关闭的情况下进行备份，需要备份数据库的所有文件。简单快速，安全性高，但数据库必须处于关闭状态，灵活性差。

(2) 热备份：在数据库正常运行的情况下进行备份，仅需备份部分文件，如主从复制。不用事先关闭数据库，灵活度高，但安全性低，难以维护。

17. MySQL有关权限的表都有哪几个？

MySQL服务器通过权限表来控制用户对数据库的访问，按控制粒度分为用户表（User），库级表（DB），表级表（Table_priv），列级表（Columns_priv）。

更新记录

更新日期	更新详情
（2022年05月22日）	内容汇总，主要吸收了拓跋阿秀校招笔记的内容，以及部分面经
（2023年07月25日）	改名为关系型数据库（MySQL）笔记，删减了原笔记中大量过于繁杂的内容，对笔记进行重新排版。汇总了关于MySQL的各种重要知识点，包括索引、事务和锁、存储引擎，以及数据库优化等方面，从原理出发去更详细地解释MySQL。
(2023年08月31日)	将各个大板块重新编号，现在各个版块都有独立的题目编号，互不干扰。将更新记录中的版本号替换为年份日期，取消版本号机制，方便记录更新。简化笔记名称，去掉“关系型数据库”字样。
（2023年09月17日）	修改首页文字布局，统一化布局。修改前言。添加前提基础模块。更改正文和标题字体。更新目录。修改参考资料。所有的更新日期都添加前置0，统一长度。修复页码错误。将笔记改名为“数据库（MySQL）”。将“MySQL”模块改名为“MySQL概述”。

参考资料

《MySQL》：https://interviewguide.cn/notes/03-hunting_job/02-interview/04-01-01-MySQL.html

《MySQL数据库面试题（2020最新版）》：https://thinkwon.blog.csdn.net/article/details/104778621

《MySQL数据库面试题总结（2022最新版）》：https://blog.csdn.net/adminpd/article/details/122910606

《合集·MySql》：https://space.bilibili.com/37659343/channel/collectiondetail?sid=855091

《数据库系统与文件系统的区别》：https://blog.csdn.net/zxnsirius/article/details/50946779

《数据库学习笔记…》：https://blog.csdn.net/L__HH/article/details/120435859

《Mysql——内部结构了解》：https://blog.csdn.net/qq_41510985/article/details/120353714

《MySQL 数据类型》：https://www.runoob.com/mysql/mysql-data-types.html

《为什么VARCHAR需要长度规格？》：https://www.orcode.com/question/748927_kcf627.html

《数据库索引是什么？为什么要使用索引？》：https://blog.csdn.net/Python_BT/article/details/124129166

《MySQL 之全文索引》：https://blog.csdn.net/mrzhouxiaofei/article/details/79940958

《Mysql性能优化：什么是索引下推？》：https://zhuanlan.zhihu.com/p/121084592

《MySQL各种锁详解..》：https://www.bilibili.com/video/BV1po4y1M7k5

《如何解决MySQL中的死锁问题？》：https://blog.csdn.net/wypblog/article/details/124311117

《MySQL常用存储引擎之Memory》：https://blog.csdn.net/Leon_Jinhai_Sun/article/details/96132127

《深入理解InnoDB – 架构篇》：https://zhuanlan.zhihu.com/p/158978012

《MVCC 原理》：https://zhuanlan.zhihu.com/p/147372839

《MySQL临时表详细解释》：https://blog.csdn.net/Bb15070047748/article/details/106550803

《SQL优化之SQL查询语句的执行顺序解析》：https://blog.csdn.net/seoyundu/article/details/100661828

《数据库冷备和热备》：https://blog.csdn.net/czh500/article/details/90274060

《B树和B+树》：https://blog.csdn.net/u014453898/article/details/112469113

《uuid与自增id的区别与使用》：https://blog.csdn.net/Loney_Island/article/details/120689448