我所理解的MySQL系列·第N篇·吐血整理的MySQL面试题

我所理解的 MySQL 系列最终篇，主要内容是前七篇文章中关于 MySQL 各种知识点的进阶面试题，请君采撷。

首先回顾一下前六篇：

mysql-N-封面

1. 基础架构

1.1 一条 select 查询语句在 MySQL 中的执行流程

客户端通过连接器与 MySQL 服务器建立连接，并获取了用户的读写权限，然后提交查询语句。
首先 MySQL 会在查询缓存中对提交的语句进行查询，如果命中且用户对表有操作权限，会直接返回查询缓存中查询结果作为本次查询的结果，查询到此结束。
如果查询缓存未命中，会来到分析器，分析器会解析语句并检查其合法性。如果语句不符合 MySQL 的语法规范，执行器会报错，查询到此结束。
若语句合法，会来到优化器，优化器会为 SQL 语句选择最优的执行计划。
最后来到执行器，如果用户对表有操作权限，执行器会调用存储引擎提供的接口来执行 SQL 语句，然后将查询结果返回给客户端，查询到此结束。

1.2 MySQL 常用的存储引擎有哪些？

InnoDB: 支持事务、MVCC、外键、行级锁和自增列
MyISAM: 支持全文索引、压缩、空间函数、表级锁，不支持事务
Memory: 存储于内存，速度更更更快，支持 Hash 索引，并发性能低

1.3 redo log 写满了该怎么办？

redo log 日志文件大小是固定的，当它被塞满之后会暂停写入，将一些日志（脏页）同步到磁盘中，就会有新的空闲 redo log 可以被写入了。

1.4 MySQL 如何保证数据一致性？

MySQL 通过两阶段提交（2PC，Two-Phase Commit）来保证事务的完整性，数据一致性。

在执行器调用 InnoDB 引擎的接口将写入更新数据时，存储引擎会将本次更新记录到 redo log 中，同时将 redo log 的状态标记为 prepare，表示可以提交事务。

随后执行器生成本次操作的 bin log 数据，写入 bin log 的日志文件中。

最后执行器调用 InnoDB 的提交事务接口，存储引擎把刚写入的 redo log 记录状态修改为 commit，本次更新结束。

1.5 其他更细节的问题

如果用户对表没有查询权限，是哪个部分报错？
- 若命中查询缓存，是查询缓存报错；若未命中查询缓存，是执行器报错。
MySQL 的查询缓存在何种情况下会失效？
- SQL 不一致（大小写敏感）
- 查询语句中存在不确定值，如uuid(), now(), rand()等
- 查询 mysql, information_schema, performance_schema 表不走缓存
- 通过函数、触发器或事件主体内执行查询语句
- 表发生改变，包括数据更新、表结构更新

2. 索引

2.1 MySQL 中常见的索引有哪些？

从索引字段特性来区分聚簇索引：索引顺序与数据存储顺序一致，其叶子节点存储的是数据行。：

普通索引：最基础的索引
唯一索引：索引列的值必须唯一
主键索引：特殊的唯一索引，作为主键它的值不能为空
联合索引：联合索引就是索引列为多个字段的普通索引，需要考虑最左前缀原则
前缀索引：对字符类型的前几个字符或二进制类型的前几个字节建立索引

从物理存储来区分：

聚簇索引：即主键索引，叶子节点存储的是数据行
非聚簇索引：非聚簇索引的叶子节点存储的是主键的值，同时它是基于聚簇索引创建的

2.2 常见实现索引的数据结构有哪些？

哈希索引：以 key-value 形式来存储数据的容器，仅适用于等值查询
有序数组：以 key 的顺序进行排列的数组，适合存储初始化过后就不再更新的数据
搜索树：最常用的索引类型
全文索引：通过建立倒排索引，解决了判断字段是否包含关键字的问题

2.3 什么是回表？如何避免回表？

回表是指从非聚簇索引查询到的主键 id 值，再在主键索引中查询其他所需要的字段的过程。

当使用非聚簇索引进行查询时，如果查询的是除了主键ID以及非聚簇索引列以外的字段，就会在非聚簇索引命中以后，根据主键ID的值再回到主键索引中进行查询，得到主键 id 对应的整个数据行，然后从中获取查询语句需要的字段后，才将这一行加入结果集，这个过程就是回表。

可以使用覆盖索引避免回表。

2.4 什么是覆盖索引？

覆盖索引就是指已经包含了查询语句查询的所有字段的非聚簇索引列，只需要在这一棵索引树上就可以得到查询语句所需要的所有字段，所以无需进行回表操作。

2.5 最左前缀原则及索引失效场景

对于联合索引，MySQL 会遵循最左前缀原则：查询条件与联合索引的最左列或最左连续多列一致，那么就可以使用该索引。

索引失效场景：

联合索引部分字段匹配时，按照最左前缀原则只使用到联合索引最左开始匹配的字段
某字段的范围查询（<,>,!=,<=,>=,between,like ‘**%’），最多只能使用到这个字段，联合索引中位于它后面的字段无法使用索引
等式不等式左侧的字段包含表达式或函数时，该字段是不会用到索引的
in 查询条件中值数据类型不一致，MySQL 会将所有值转化为与索引列一致的数据类型，从而无法使用索引
查询条件使用 <>(!=), not in, is not null
涉及字符集转换或隐式字段类型转换
查询影响行数大于全表的25%

联合索引在全字段匹配且为等值查询的情况下，查询条件的顺序不一致也能使用到联合索引。

索引失效并非指无法使用索引，而是说无法使用联合索引的全部字段。

2.6 什么是索引下推？

索引下推（Index Condition Pushdown, ICP）是指当联合索引在某些场景下失效时，在搜索索引树也能对查询条件中包含且索引也包含的字段进行判断，减少回表次数，它的目的是优化索引失效时的查询效率的。

举个例子，假设有如下表：

CREATE TABLE `user`  (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) NULL DEFAULT NULL,
  `age` int(12) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_age_name`(`age`,`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;

insert into user values (1,'达达',15),(2,'达达2',20),(3,'达达3',30);

要执行这条查询语句：

select * from user where age>10 and name='达达';

假设使用的是 idx_age_name 索引，若没有索引下推优化查询过程是这样：由于只能用到联合索引的第一个字段 age，所以在命中 age>10 的记录后，MySQL 会通过回表拿到这行数据的 name 字段值，进行是否等于达达的判断，最后再返回结果集

而如果通过索引下推优化之后，即便只能用到联合索引的第一个字段 age，在命中 age>10 的记录后，MySQL 也会对存在于查询条件和联合索引中且并未使用到的索引进行条件判断，将满足条件 name='达达' 的记录加入结果集。

3. 执行计划

3.1 如何进行 SQL 调优？

会看执行计划，一切都好说。主要可以从以下几个方面考虑：

是否使用索引
是否用对索引
是否有查询全部字段的必要
是否有减少回表次数的可能
是否可以使用覆盖索引进行优化
等等等等

3.2 limit 10000000,10000001 如何进行优化？

通过覆盖索引，减少回表次数进行优化

还是用上面 user 表来举例，假设我要取第10000000条数据：

select * from user limit 10000000,10000001;

可以通过这种方式来优化：

select * from user user1 join (select id from user limit 10000000,10000001) user2 on user1.id=user2.id;

3.3 执行计划中的 key_len 使用索引长度字节数是如何计算的？

索引长度 = 字段定义长度 * 字符集长度 + 是否默认NULL + 是否是变长字段

字段定义长度就是定义表结构时跟在字段类型后括号中的数字
字符长度是常数，utf8=3, gbk=2, latin1=1
是否默认NULL 也是常数，若字段默认值为 NULL，该值为1，因为 NULL 需要额外的一个字节来表示；否则该值为0
是否是变长字段也是常数，若该字段为变长字段，该值为2；否则该值为0

3.4 其他更细节的问题

执行计划中的 rows 扫描行数是如何计算的？
- 扫描行数在执行计划中其实是一个估值，MySQL 会选择 N 个不同的索引数据页，计算平均值得到单页索引基数，然后再乘以索引页面数，就得到了扫描行数的估值。
Using index, Using index condition, Using where, Using temporary, Using filesort 各自代表什么？
- Using index：覆盖索引
- Using index condition：索引下推
- Using where：使用索引时代表where条件中非索引列；全表扫描时代表where中有查询条件
- Using temporary：临时表
- Using filesort：外部索引排序（文件排序）

4. 事务、隔离级别及MVCC

4.1 事务的 ACID 基本要素

A（Atomicity，原子性）：一个事务中的操作要么都成功，要么都失败
C（Consistency，一致性）：数据库总是从一个一致性状态转换到另一个一致性状态，若破坏约束，则不满足一致性条件
I（Isolation，隔离性）：在一次状态转换过程中不会受到其他状态转换的影响
D（Durability，持久性）：事务在提交以后，它所做的修改就会被永久保存到数据库

4.2 事务的隔离级别

读未提交（Read Uncommitted）：事务中的修改，即便没有提交，对其他事务也都是可见的
读已提交（Read Committed）:事务中的修改只有在提交之后，才会对其他事务可见
可重复读（Repeatable Read）：一个事务中多次查询相同的记录，结果总是一致的
可串行化（Serializable）：事务都是串行执行的，读会加读锁，写会加写锁

4.3 事务各个隔离级别中会出现的问题

| 隔离级别 | 脏读 | 不可重复读 | 幻读 | | ——– | —- | ———- | —- | | 读未提交 | √ | √ | √ | | 读已提交 | | √ | √ | | 可重复读 | | | √ | | 可串行化 | | | |

脏读（Dirty Read）：一个事务可以读取另一个未提交事务修改的数据
不可重复读（Non-Repeatable Read）：两次执行相同的查询可能会得到不一样的结果。
幻读（Phantom Read）：一个事务在读取某个范围内记录时，另外一个事务在该范围内插入一条新记录，当之前的事务再次读取这个范围的记录时，会读到这条新记录。

4.3 MySQL 在可重复读隔离级别中是如何解决幻读问题的？

通过间隙锁，锁住记录之间的空隙，防止被其他事务插入新记录

4.3 MVCC 的实现原理

通过 MVCC 我们常用于判断事务之间的可见性。事务在启动时会生成一个一致性视图，其中包含四个重要属性：

当前事务ID：表示生成读视图的事务的事务ID
事务ID列表：表示在生成读视图时，当前系统中活跃着的事务ID列表
最小事务ID：表示在生成读视图时，当前系统中活跃着的最小事务ID
下一个事务ID：表示在生成读视图时，系统应该分配给下一个事务的事务ID

我们通过将版本链与读视图两者结合起来，来进行并发事务间可见性的判断，判断规则如下（假设现在要判断事务A是否可以访问到事务B的修改记录）：

若事务B的当前事务ID小于事务A的最小事务ID的值，代表事务B是在事务A生成读视图之前就已经提交了的，所以事务B对于事务A来说是可见的。
若事务B的当前事务ID大于或等于事务A下一个事务ID的值，代表事务B是在事务A生成读视图之后才开启，所以事务B对于事务A来说是不可见的。
若事务B的当前事务ID在事务A的最小事务ID和下一个事务ID之间（左闭右开，[最小事务ID, 下一个事务ID)），需要分两种情况讨论：
- 若事务B的当前事务ID在事务A的事务ID列表中，代表创建事务A时事务B还是活跃的，未提交，所以事务B对于事务A来说是不可见的。
- 若事务B的当前事务ID不在事务A的事务ID列表中，代表创建事务A时事务B已经提交，所以事务B对于事务A来说是可见的。

如果事务B对于事务A来说是不可见的，就需要顺着修改记录的版本链，从回滚指针开始往前遍历，直到找到第一个对于事务A来说是可见的事务ID，或者遍历完版本链也未找到（表示这条记录对事务A不可见）。

4.4 其他更细节的问题

快照读与当前读
- 快照读：读取记录时会根据一定规则读取事务可见版本的记录
- 当前读：读取记录的最新版本

5. 锁及加锁规则

5.1 说一下 MySQL 中锁的分类

全局锁（flush tables with read lock）：主要用于进行全库备份
表级锁：
- 表锁（lock table tableName read/write）
- 元数据锁（MDL, Meta Data Lock）：会在客户端访问表的时候自动加锁，在客户端提交事务时释放锁；避免了前后两次查询字段列数不一致的情况
- 意向锁（intention lock），它表示事务想要获取一张表中某几行的锁（共享锁或排它锁），避免了在表中已经存在行锁的情况下，另一个事务去申请表锁而扫描表中的每一行是否存在行锁的系统消耗。
- 自增锁：是一种特殊的表级锁，只存在于被设置为 AUTO_INCREMENT 自增列，在 insert 语句执行完成后立即释放
行级锁：
- 记录锁（Record Lock）：锁指定行
- 间隙锁（Gap Lock）：锁记录之间的间隙
- Next-Key Lock：锁记录与记录前面的间隙

5.2 行级锁的加锁规则

注意：这里所讨论的加锁规则都是指加排它锁。

我认为对于加锁规则需要记住最核心的内容是：记录可能出现在哪里，就锁住哪里。

唯一索引等值查询：若值存在，只锁唯一索引对应记录；若不存在，锁可能出现该值的间隙
唯一索引范围查询：
- 会给范围中的记录加上记录锁，间隙加上间隙锁
- 对于范围查询(大于/大于等于/小于/小于等于)是比较特殊的，它会将记录锁加到第一个边界之外的记录上，若其中有额外的间隙也会加上间隙锁（即会将 Next-key Lock 加到第一个边界之外的记录上）
普通索引等值查询：
- 给普通索引中可能出现该值的间隙全部加上锁，相等的记录加上记录锁
- 如果需要回表，满足条件的记录对应的主键也会被加上记录锁。
普通索引范围查询：
- 会给范围中的记录加上记录锁，间隙加上间隙锁
- 对于范围查询(大于/大于等于/小于/小于等于)是比较特殊的，它会将记录锁加到第一个边界之外的记录上，若其中有额外的间隙也会加上间隙锁（即会将 Next-key Lock 加到第一个边界之外的记录上）
- 如果需要回表，满足条件的记录对应的主键也会被加上记录锁。
limit 的加锁：
- 锁只会加到满足 limit 条件的记录的记录锁，之后的间隙锁不会加锁
非索引列：锁全表

5.3 加锁规则举例

CREATE TABLE `user`  (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) NULL DEFAULT NULL,
  `age` int(12) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;

insert into user values (5,'重塑',5),(10,'达达',10),(15,'刺猬',15),(16, '达达2.0', 10);

唯一索引等值查询：
- 值命中：
  - 加锁语句：select * from user where id=5 for update;
  - 加锁范围：锁主键索引 id=5 这一行
- 值未命中
  - 加锁语句：select * from user where id=6 for update;
  - 加锁范围：锁主键索引 (5,10)
唯一索引范围查询：
- 边界值未命中
  - 加锁语句：select * from user where id<10 for update;
  - 加锁语句：select * from user where id<=9 for update;
  - 加锁范围：锁主键索引 (-∞,10]
- - 边界值命中
    - 加锁语句：select * from user where id<=10 for update;
    - 加锁范围：锁主键索引 (-∞,11]
普通索引等值查询：
- 加锁语句：select * from user where age=10 for update;
- 加锁范围：普通索引age的(5, 15)以及主键索引 id=10 和 id=11 这两行
普通索引范围查询：
- 加锁语句：select * from user where age>8 and age<=12 for update;
- 加锁范围：普通索引age的(5, 15]以及主键索引 id=10,id=11,id=15 这三行
普通索引 limit 的加锁：
- 加锁语句：select * from user where age=10 limit 1 for update;
- 加锁范围：普通索引age的(5, 10]以及主键索引 id=10 这一行
非索引列：锁全表
- 加锁语句：select * from user where name='达达' for update;
- 加锁范围：锁全表

6. 分库分表与主从同步

6.1 分库分表的方式

水平切分：
- 水平分表：将同一张表中的数据按照一定的规则拆分到多张表中进行存储，多张表放在一个库中
- 水平分库：将同一张表中的数据按照一定的规则拆分到多张表中进行存储，多张表放在多个库中
垂直切分：
- 垂直分表：将一张表按照字段拆分成多表，每个表存储其中一部分字段
- 垂直分库：专库专用，将表分隔到多个库中

6.2 水平切分策略

哈希：将ID进行哈希，这样的好处是使得ID充分散列，数据分布更均匀
ID取模：跟哈希是相似的原理
范围：ID范围或时间范围
映射表：映射表的方式就是自定义切分策略，更具主观性，但由于需要通过映射表找到切分后的数据所在区域，所以会影响查询效率

6.3 分库分表后带来的问题及解决方案

分布式事务：多节点的事务问题
- 全局事务（如两阶段提交 Two-Phase Commit, 2PC）
- 补偿事务（Try-Confirm-Cancel, TCC）
- 本地消息表
- MQ事务消息
- 开源框架（如 Seata）
跨库查询：
- 通过冗余表或者冗余字段来实现跨库关联查询
- 中间件
全局主键的唯一性：
- UUID
- 基于分布式锁实现自增ID
- 分布式自增ID生成器，如雪花算法（Snowflake）

6.4 主从同步的原理

当主库中有数据更新时，主库会按照配置的 binlog_format 的值来将日志写入 bin_log 中，之后主库会通过 Log Dump Thread 线程来通知从库有数据需要同步。
从库中的 I/O Thread 线程就是负责与主库建立连接的，它接收到主库的新数据通知后，将向 Log Dump Thread 线程发起读取日志数据的请求，然后将读取到的日志数据写入从库的中转日志 Relay log 中。
最后，从库中的 SQL Thread 线程检测到中转日志 Relay log 有更新后，会将这些发生在主库的日志数据解析为 SQL 语句全部执行一遍，以此来完成主从同步。

6.5 binlog_format 三种模式

statement : 客户端提交的原始 SQL
row : 经过优化后，精确到行的 SQL（不会导致主从不一致，但日志空间也更大）
mixd : 混合模式，易引起主从不一致的语句使用 row 模式，其他语句使用 statement 模式

7. 结束语

我所理解的MySQL系列到这里就暂时告一段落了，前六篇只是将我认为比较重要的部分做了整理，还有很多没有涉及的地方，日后也会不定期继续更新，同时还会有其他系列（比如分布式、Dubbo、Netty等）会跟大家见面。

江湖再见。

告辞。

最后，本文收录于个人语雀知识库: 我所理解的后端技术，欢迎来访。

文档信息

本文作者：Planeswalker23
本文链接：https://planeswalker23.github.io/2020/11/16/%E6%88%91%E6%89%80%E7%90%86%E8%A7%A3%E7%9A%84MySQL%E7%B3%BB%E5%88%97-%E7%AC%ACN%E7%AF%87-%E5%90%90%E8%A1%80%E6%95%B4%E7%90%86%E7%9A%84MySQL%E9%9D%A2%E8%AF%95%E9%A2%98/
版权声明：本作品系原创，作者保留所有权利，未经作者允许，禁止转载和演绎。