MySQL

典型回答 所谓的MySQL事务的2阶段提交，其实是在更新过程中，保证binlog和redolog一致性的一种手段。 上图中右侧部分即为2阶段提交。他的过程是： Prepare 阶段 这个阶段 SQL 已经成功执行并生成 redolog并写入磁盘，处于prepare阶段 BinLog持久化 binlog 提交，将 binlog 内存日志写入磁盘 Commit 在执行引擎内部执行事务操作，写入redolog，处于Commit阶段 write 和 fsync 是与文件系统和磁盘IO相关的两个不同的操作。 write 操作将数据写入文件的缓冲区，这意味着 write 操作完成后，并不一定立即将数据持久化到磁盘上，而是将数据暂时存储在内存中。 fsync 用于强制将文件的修改持久化到磁盘上。它通常与 write 配合使用，以确保文件的修改在 fsync 操作完成后被写入磁盘。 ✅write和fsync的区别是什么？ 那么，为什么这个过程需要用2阶段提交的方式呢？ 假设我们执行一条SQL语句，修改他的name为Hollis ： update user set name = 'hollis' where id = 10 。 假设先写入redo log 成功，但是没来得及写入bin log ，系统崩了。在MySQL重启后，可以根据redolog把记录更新成’hollis’，但是，binlog由于没写成功，所以他是没有记录下来这次变更的，那么也就意味着，主备同步的时候，是缺了一条SQL的，导致主备库之间数据不一致。 那么，如果换个顺序，先写入binlog成功，但是没来及的写入redolog，系统崩了。在MySQL重启之后，崩溃恢复的时候由于redo log还没写，所以什么都不用做，数据库记录还是旧值。但是因为binlog已经写入成功了，所以在做主备同步的时候，就会把新值同步到备库，就导致了主备库之间数据不一致。 如上面的例子，如果不引入二阶段提交的话，在bin log 和redo log没办法保证一致性的情况下，就会导致主备库之间的数据不一致。 而为了解决这个问题，那就引入了2阶段提交，来整体的控制redo log 和 bin log的一致性写入。 扩展知识 有了2阶段就不丢数据了吗？ ✅MySQL能保证数据100%不丢吗？ 2阶段如何保证一致性的？ 引入2阶段提交之后，事务的提交过程就可能有以下三种情况： 情况一：一阶段提交之后崩溃了，即写入 redo log，处于 prepare 状态 的时候崩溃了 ...

典型回答 在MySQL中，最左前缀匹配是指在查询中利用索引的最左边的一部分来进行匹配。指你执行查询时，如果查询条件涉及到了组合索引的前几个列，MySQL 就可以利用这个复合索引来进行匹配。 组合索引，指的就是有多个字段组成的一个联合索引，如 idx_col1_col2_col3 (col1,col2,col3)) 假如我们创建了一个组合索引 (col1, col2, col3)，如果你的查询条件是针对 col1 、 (col1, col2)或者(col1, col2, col3)，那么MySQL可以利用这个复合索引进行最左前缀匹配。 而且如果你用的是 (col1, col3)也是可以走索引的，只不过他用到的是 col1这个字段的索引。 但是，如果查询条件涉及到的列只有 col2 或者 只有col3或者只有col2和col3，总之就是如果不包含col1的话，那么是没有遵守最左前缀匹配，那么通常情况下（不考虑索引跳跃扫描等其他优化），就不能利用这个索引进行最左前缀匹配。 并且，需要注意的是，最左前缀匹配和查询条件的顺序没有关系，不管你写的是where col1 = "Holiis" and col2 = "666" 还是 where col2 = "666" and col1 = "Holiis" 对结果都没有影响，该命中还是会命中。 但是，需要大家注意的是，很多人会以为创建一个组合索引 (col1, col2, col3)的时候，数据库会创建出三个索引 (col1)、 (col1, col2)和(col1, col2, col3)，这么理解其实是不对的，他创建的只是一棵B+树，只不过在这颗树中，他是先按照col1排序，在col1相同时再按照col2排序的，col2相同再按照col3排序。 另外，如果不涉及到联合索引，单个字段的索引也需要遵守最左前缀，即有一个字段值为"abc"时，当我们使用like进行模糊匹配时，like "ab%"是可以走索引的，而"%bc"、"b%c"都是不行的，就是因为后者不遵守最左前缀匹配的原则了。 扩展知识 为什么要遵循最左前缀匹配 我们都知道，MySQL的Innodb引擎中，索引是通过B+树来实现的。不管是普通索引还是联合索引，都需要构造一个B+树的索引结构。 那么，我们都知道普通索引的存储结构是在B+树的每个非叶子节点上记录索引的值，而这棵B+树的叶子节点上记录的是索引的值和聚簇索引（主键索引）的值的。 如： （这样图是简化的，实际上还有双向链表，具体的可以参考索引介绍的文章） 那么，如果是联合索引的话，这棵B+树又是如何存储的呢？ 在联合索引中，联合索引(age,name)也是一个B+树，非叶子节点中记录的是name,age两个字段的值，叶子节点中记录的是name，age两个字段以及主键id的值。 在存储的过程中，如上图所示，当age不同时，按照age排序，当age相同时，则按照name排序。 所以，了解了索引的存储结构之后，我们就很容易理解最左前缀匹配了：因为索引底层是一个B+树，如果是联合索引的话，在构造B+树的时候，会先按照左边的key进行排序，左边的key相同时再依次按照右边的key排序。 所以，在通过索引查询的时候，也需要遵守最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。

典型回答 这个问题其实是下面这个问题的同题，只是问法不一样，答案也是一样的，可以看下面这篇，也可以继续看后面的内容。 （这个知识点挺重要的，所以为了方便大家搜索所以单独写了一篇。） ✅MySQL索引一定遵循最左前缀匹配吗？ MySQL 8.0.13 版本中，对于range查询（什么是range后面会提到)，引入了**索引跳跃扫描（Index Skip Scan）**优化，支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描。 通过一个例子给大家解释一下，首先有下面这样一张表（参考了MySQL官网的例子，但是我做了些改动和优化）： 1 2 3 4 5 6 7 8 9 CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL); CREATE INDEX idx_t on t1(f1,f2); INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (2,1), (2,2), (2,3), (2,4), (2,5); INSERT INTO t1 SELECT f1, f2 + 5 FROM t1; INSERT INTO t1 SELECT f1, f2 + 10 FROM t1; INSERT INTO t1 SELECT f1, f2 + 20 FROM t1; INSERT INTO t1 SELECT f1, f2 + 40 FROM t1; 通过上面的SQL，先创建一张t1表，并把f1,f2两个字段设置为联合索引。之后再向其中插入一些记录。 ...

典型回答 聚簇索引（Clustered Index）和非聚簇索引（Non-clustered Index）是数据库中的两种索引类型，它们在组织和存储数据时有不同的方式。 聚簇索引，简单点理解就是将数据与索引放到了一起，找到索引也就找到了数据。也就是说，对于聚簇索引来说，他的非叶子节点上存储的是索引字段的值，而他的叶子节点上存储的是这条记录的整行数据。 在InnoDB中，聚簇索引（Clustered Index）指的是按照每张表的主键构建的一种索引方式，它是将表数据按照主键的顺序存储在磁盘上的一种方式。这种索引方式保证了行的物理存储顺序与主键的逻辑顺序相同，因此查找聚簇索引的速度非常快。 非聚簇索引，就是将数据与索引分开存储，叶子节点包含索引字段值及指向数据页数据行的逻辑指针。 在Innodb中，非聚簇索引（Non-clustered Index）是指根据非主键字段创建的索引，也就是通常所说的二级索引。它不影响表中数据的物理存储顺序，而是单独创建一张索引表，用于存储索引列和对应行的指针。 在InnoDB中，主键索引就是聚簇索引，而非主键索引，就是非聚簇索引，所以在InnoDB中： 对于聚簇索引来说，他的非叶子节点上存储的是索引值，而它的叶子节点上存储的是整行记录。 对于非聚簇索引来说，他的非叶子节点上存储的都是索引值，而它的叶子节点上存储的是主键的值+索引值。 所以，通过非聚簇索引的查询，需要进行一次回表，就是先查到主键ID，再通过ID查询所需字段。 ✅什么是回表，怎么减少回表的次数？ 没有创建主键怎么办？ 我们知道，Innodb中的聚簇索引是按照每张表的主键构造一个B+树，那么不知道大家有没有想过这个问题，如果我们在表结构中没有定义主键，那怎么办呢？ 其实，数据库中的每行记录中，除了保存了我们自己定义的一些字段以外，还有一些重要的 db_row_id字段，其实他就是一个数据库帮我添加的隐藏主键，如果我们没有给这个表创建主键，会选择一个不为空的唯一索引来作为聚簇索引，但是如果没有合适的唯一索引，那么会以这个隐藏主键来创建聚簇索引。

典型回答 **InnoDB的数据页是InnoDB存储引擎中用于存储数据的基本单位。**它是磁盘上的一个连续区域，通常大小为16KB当然，也可以通过配置进行调整。16KB就意味着Innodb的每次读写都是以 16KB 为单位的，一次从磁盘到内存的读取的最小是16KB，一次从内存到磁盘的持久化也是最小16KB。 B+树的每个节点都对应着一个数据页，包括根节点、非叶子节点和叶子节点。B+树通过节点之间的指针连接了不同层级的数据页，从而构建了一个有序的索引结构。（文中都以聚簇索引为例。） 通过B+树的搜索过程，可以从根节点开始逐层遍历，最终到达叶子节点，找到所需的数据行。 所以，数据页是存储数据行的实际物理空间，以页为单位进行磁盘读写操作。B+树通过节点和指针的组织，构建了一个层次结构的索引，用于快速定位和访问数据行。 B+树的非叶子节点对应着数据页，其中存储着主键+指向子节点（即其他数据页）的指针。B+树的叶子节点包含实际的数据行，每个数据行存储在一个数据页中。 通过这种方式，InnoDB利用B+树和数据页的组合，实现了高效的数据存储和检索。B+树提供了快速的索引查找能力，而数据页提供了实际存储和管理数据行的机制。它们相互配合，使得InnoDB能够处理大规模数据的高效访问。 扩展知识 数据页的构成 一个数据页中包含了7个部分，分别是文件头、页头、最小和最大记录、用户记录、空闲空间、页目录以及文件尾。 关于数据页的结构，面试基本不怎么深入，如果感兴趣，可以看下下面这篇文档，讲的很好：https://smartkeyerror.oss-cn-shenzhen.aliyuncs.com/Psyduck/MySQL/InnoDB-Page.pdf

典型回答 Innodb中的锁在锁的级别上一般分为两种，一种是共享锁（S锁），一种是排他锁（X锁）。 共享锁&排他锁 共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。 排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的锁。获得排他锁的事务既能读数据，又能修改数据。 ✅什么是排他锁和共享锁？ 这就是我们经常会看到的X锁和S锁。即排他锁和共享锁。 除了S锁和X锁之外，Innodb还有两种锁，是IX锁和IS锁，这里的I是Intention 的意思，即意向锁。IX就是意向排他锁，IS就是意向共享锁。 意向锁 ✅什么是意向锁？ 当一个事务请求获取一个行级锁或表级锁时，MySQL会自动获取相应的表的意向锁。 IS锁： 表示事务打算在资源上设置共享锁（读锁）。这通常用于表示事务计划读取资源，并不希望在读取时有其他事务设置排它锁。 IX锁： 表示事务打算在资源上设置排它锁（写锁）。这表示事务计划修改资源，并不希望有其他事务同时设置共享或排它锁。 意向锁其实是一个表级锁！ 以下是MySQL官网上给出的这几种锁之间的冲突关系： 记录锁 **Record Lock，翻译成记录锁，是加在索引记录上的锁。**例如，SELECT c1 FROM t WHERE c1 = 10 For UPDATE;会对c1=10这条记录加锁，为了防止任何其他事务查询、插入、更新或删除c1值为10的行。 Record Lock是一个典型的行级锁，但是需要特别注意的是，Record锁的不是这行记录，而是锁索引记录。并且Record lock锁且只锁索引！ 如果没有索引怎么办？对于这种情况，InnoDB 会创建一个隐藏的聚簇索引，并使用这个索引进行记录锁定。 如果我们在一张表中没有定义主键，那么，MySQL会默认选择一个唯一的非空索引作为聚簇索引。如果没有适合的非空唯一索引，则会创建一个隐藏的主键（row_id）作为聚簇索引。 关于记录锁的加锁原则，以及Gap Lock和Next Key Lock，请看这篇： ✅MySQL的行级锁锁的到底是什么？ 插入意向锁 插入意向锁是一种由插入操作在行插入之前设置的间隙锁。这种锁表明了插入的意图，以这样一种方式，如果多个事务插入到同一索引间隙中但不在间隙内的相同位置插入，则它们不需要相互等待。 假设有索引记录的值为 4 和 7。分别尝试插入值为 5 和 6 的不同事务，在获取插入行的独占锁之前，各自用插入意向锁锁定 4 和 7 之间的间隙，但由于行不冲突，所以它们不会相互阻塞。但是如果他们的都要插入6，那么就会需要阻塞了。 ...

典型回答 MySQL的组提交（Group Commit）是一项优化技术，用于提高数据库系统的性能和事务处理的效率。它通过将多个事务的提交操作合并成一个批处理操作来减少磁盘IO和锁定开销，从而加速事务的处理。 我们的数据库需要不断地执行很多次数据变更，并且每次变更都需要把数据持久化下来，以方便进行崩溃恢复及主从同步、还有回滚等，这就涉及到binlog、redolog以及undolog的写入。 而频繁的文件写入需要会触发频繁磁盘写入操作，为了减少提交操作的开销，MySQL引入了组提交技术，就是**将多个事务的提交操作可以合并成一个批处理操作，以减少磁盘IO次数。**这个批处理操作将包含多个事务的修改，并一次性写入二进制日志。 通过以下命令可以查看关于组提交的配置： 1 2 3 4 5 6 7 8 mysql> show variables like '%group_commit%'; +-----------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------+-------+ | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | +-----------------------------------------+-------+ 2 rows in set (0.00 sec) binlog_group_commit_sync_delay 延迟多长时间再通过fsync进行刷盘，把数据持久化 binlog_group_commit_sync_no_delay_count 累积多少次操作再通过fsync进行刷盘，把数据持久化 注意，这两个条件是或的关系，只要满足一个，就会触发提交动作。 扩展知识 有了组提交后的2阶段 ✅什么是事务的2阶段提交？ 在有了组提交之后，2阶段就会有一些变化，因为日志的刷盘过程会因为组提交而需要等待，所以会变成这样： 这里面write 和 fsync 是与文件系统和磁盘IO相关的两个不同的操作。 write 操作将数据写入文件的缓冲区，这意味着 write 操作完成后，并不一定立即将数据持久化到磁盘上，而是将数据暂时存储在内存中。 fsync 用于强制将文件的修改持久化到磁盘上。它通常与 write 配合使用，以确保文件的修改在 fsync 操作完成后被写入磁盘。 所以，用于将缓冲区内容持久化到磁盘的fsync这一步，被延迟了。他会等一个组中多个事务都处于Prepare阶段后，然后进行一次组提交，即把日志持久化到磁盘中。 ...

典型回答 MVCC，是Multiversion Concurrency Control的缩写，翻译过来是多版本并发控制，和数据库锁一样，他也是一种并发控制的解决方案。 我们知道，在数据库中，对数据的操作主要有2种，分别是读和写，而在并发场景下，就可能出现以下三种情况： 读-读并发 读-写并发 写-写并发 我们都知道，在没有写的情况下读-读并发是不会出现问题的，而写-写并发这种情况比较常用的就是通过加锁的方式实现。那么，读-写并发则可以通过MVCC的机制解决。 扩展知识 快照读和当前读 ✅当前读和快照读有什么区别？ 要想搞清楚MVCC的机制，最重要的一个概念那就是快照读。 所谓快照读，就是读取的是快照数据，即快照生成的那一刻的数据，像我们常用的普通的SELECT语句在不加锁情况下就是快照读。如： 1 SELECT * FROM xx_table WHERE ... 和快照读相对应的另外一个概念叫做当前读，当前读就是读取最新数据，所以，加锁的 SELECT，或者对数据进行增删改都会进行当前读，比如： 1 2 3 4 5 6 7 8 9 SELECT * FROM xx_table LOCK IN SHARE MODE; SELECT * FROM xx_table FOR UPDATE; INSERT INTO xx_table ... DELETE FROM xx_table ... UPDATE xx_table ... 可以说快照读是MVCC实现的基础，而当前读是悲观锁实现的基础。 那么，快照读读到的快照是从哪里读到的呢？换句话说，快照是存在哪里的呢？ UndoLog undo log是Mysql中比较重要的事务日志之一，顾名思义，undo log是一种用于回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。 ...

典型回答 在介绍执行计划的时候，我们重点介绍了key、type、extra等字段，没有展开介绍filtered这个字段。因为他其实不怎么重要。但是也有的面试官就爱问（太八股了，明明都不怎么关注，还要问。。。），到底filtered是越大越好还是越小越好？ filtered的意义 针对使用索引的查询来说，**filtered**** 字段表示查询过程中通过索引扫描的行数中（其实就是执行计划中的rows字段），经过****WHERE**** 条件过滤后，预计会保留下来的行的百分比（是个预估值）。这个值介于0.00-100.00之间。** 比如我们有一张表定义如下： 1 2 3 4 5 6 7 8 9 10 CREATE TABLE `t2` ( `id` INT(11), `a` int(11) NOT NULL, `b` varchar(64) NOT NULL, `c` varchar(64) NOT NULL, `d` varchar(64) NOT NULL, `f` varchar(64) DEFAULT NULL, PRIMARY KEY(id), KEY `idx_abc` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 那么，我们在使用条件where a > 32 and b = ‘hollis’ 查询的时候： ...

典型回答 MySQL中是可以存储emoji表情的，但是要使用UTF8MB4的字符编码才可以。如果是UTF8MB3的话，存储这些扩展字符的话会无法解析导致报错。 扩展知识 MySQL对Unicode的支持 Unicode字符集，他现在已经是计算机科学领域里的一项业界标准，它对世界上大部分的文字系统进行了整理、编码，使得计算机可以用更为简单的方式来呈现和处理文字。 为了适应不同的数据存储和传递需求，人们提出了 Unicode Transformation Format（UTF）系列编码。这其中包含UTF-8、UTF-16、UTF-32等。 通过查阅MySQL官方文档（https://dev.mysql.com/doc/refman/8.0/en/charset-unicode.html ），我们可以知道，在MySQL中，主要支持以下字符集：utf8、ucs2、utf8mb3、utf8mb4、utf16、utf16le和utf32 不同的字符集的区别在于包含的字符情况以及存储需要的空间。 字符集 支持的字符 每个字符所需存储空间 utf8mb3, utf8 BMP 1-3 字节 ucs2 BMP 2 字节 utf8mb4 BMP和补充字符 1-4 字节 utf16 BMP和补充字符 2或4 字节 utf16le BMP和补充字符 2或4 字节 utf32 BMP和补充字符 4 字节 在MySQL官方文档中，介绍了支持的编码方式之后，还有一段醒目的提醒： 翻译过来是：utf8mb3字符集已被弃用，它在未来的MySQL版本中将会被删除，请使用utf8mb4代替。在目前的8.0版本中，utf8指的就是utf8mb3，虽然未来可能改成utf8mb4，但是为了避免产生歧义，可以考虑为字符集引用显式指定utf8mb4，而不是utf8。 也就是说，当我们在MySQL 8.0 中指定字符编码方式为UTF-8的时候，其实使用的是utf8mb3这种编码方式。 那么，我们先来说说utf8mb3。 utf8mb3 utf8mb3字符集是MySQL早期就支持的字符集，他具有以下特征: 1、仅支持BMP字符(不支持补充字符) 2、每个多字节字符最多需要三个字节 注意，仅支持BMP字符，那么什么是BMP字符呢？ BMP是Basic Multilingual Plane的缩写，即码位在0到65535之间(或者U+0000和U+FFFF)的字符。 BMP中并不包含补充字符，即码位在U+10000和U+10FFFF之间的字符。补充字符有哪些呢，如一些生僻的汉字，或者Emoji 表情等都是补充字符。 也就是说，如果在建表的时候，指定的编码方式是utf8mb3（utf-8），那么对于一些生僻字或者emoji表情都无法表示。 utf8mb4 早期的时候，Unicode 只用到了 0~0xFFFF 范围的数字编码，这就是 BMP 字符集。所以，最初MySQL在设计之初，也就只涉及了包含BMP 字符集的utfmb3(utf-8)，但是随着文字越来越多，3个字节肯定无法全部表示，于是Unicode支持的字符就更多了。 所以，早期的utfmb3在有些场景中就不能满足需求了，于是，MySQL在5.5.3之后增加了utf8mb4的编码。 utfmb4字符集具有以下特征: 1、支持BMP和补充字符。 2、每个多字节字符最多需要4个字节。 ...