Mysql 面试

钝悟...大约 52 分钟

Mysql 面试

架构

一条 SQL 查询语句是如何执行的？

连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。
查询缓存：命中缓存，则直接返回结果。弊大于利，因为失效非常频繁——任何更新都会清空查询缓存。
分析器
- 词法分析：解析 SQL 关键字
- 语法分析：生成一颗对应的语法解析树
优化器
- 根据语法树生成多种执行计划
- 索引选择：根据策略选择最优方式
执行器
- 校验读写权限
- 根据执行计划，调用存储引擎的 API 来执行查询
存储引擎：存储数据，提供读写接口

一条 SQL 更新语句是如何执行的？

更新流程和查询的流程大致相同，不同之处在于：更新流程还涉及两个重要的日志模块：

redo log（重做日志）
- InnoDB 存储引擎独有的日志（物理日志）
- 采用循环写入
binlog（归档日志）
- Mysql Server 层通用日志（逻辑日志）
- 采用追加写入

为了保证 redo log 和 binlog 的数据一致性，所以采用两阶段提交方式更新日志。

为什么表数据删掉一半，表文件大小不变

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：

这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

如果删掉了一个数据页上的所有记录，则整个数据页就可以被复用了。

如果把整个表的数据删除，则所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。页分裂完成后，就可能产生空洞。另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。

也就是说，经过大量增删改的表，都是可能是存在空洞的。

为什么我的 MySQL 会“抖”一下？

利用 WAL 技术，数据库将随机写转换成了顺序写，大大提升了数据库的性能。

但是，由此也带来了内存脏页的问题。脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。

`SELECT` 语句内部的执行步骤是怎样的

一条完整的 SELECT 语句内部的执行顺序是这样的：

FROM 子句组装数据（包括通过 ON 进行连接）；
WHERE 子句进行条件筛选；
GROUP BY 分组；
使用聚集函数进行计算；
HAVING 筛选分组；
计算所有的表达式；
SELECT 的字段；
ORDER BY 排序；
LIMIT 筛选。

ORDER BY 是对分的组排序还是对分组中的记录排序呢？

ORDER BY 就是对记录进行排序。如果你在 ORDER BY 前面用到了 GROUP BY，实际上这是一种分组的聚合方式，已经把一组的数据聚合成为了一条记录，再进行排序的时候，相当于对分的组进行了排序。

`order by` 是怎么工作的？

用 explain 命令查看执行计划时，Extra 这个字段中的“Using filesort”表示的就是需要排序。

全字段排序

select city,name,age from t where city='杭州' order by name limit 1000;

这个语句执行流程如下所示：

初始化 sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 做快速排序；
按照排序结果取前 1000 行返回给客户端。

按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 N 份，每一份单独排序后存在这些临时文件中。然后把这 N 个有序文件再合并成一个有序的大文件。

rowid 排序

如果表的字段太多，导致单行太大，那么全字段排序的效率就不够好。

这种情况下，Mysql 可以采用 rowid 排序，相比于全字段排序，它的主要差异在于：

取行数据时，不取出整行，而只是取出 id 和用于排序的字段。当排序结束后，再根据 id 取出要查询的字段返回给客户端。

全字段排序 VS rowid 排序

如果内存足够大，Mysql 会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

如果内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

并不是所有的 order by 语句，都需要排序操作的。MySQL 之所以需要生成临时表，并且在临时表上做排序操作，**其原因是原来的数据都是无序的。**如果能保证排序字段命中索引，那么就无需再排序了。

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

存储引擎

Mysql 有哪些常见存储引擎？

InnoDB - Mysql 的默认存储引擎。支持事务、外键、表级锁和行级锁、自动崩溃恢复。索引采用 B+ 树聚簇索引。
MyISAM - Mysql 5.1 版本前的默认存储引擎。特性丰富，但不支持事务、外键、行级锁、自动崩溃恢复。索引采用 B+ 树非聚簇索引。
CSV - 可以将 CSV 文件作为 Mysql 的表来处理，但这种表不支持索引。
Memory - 适合快速访问数据，且数据不会被修改，重启丢失也没有关系。
NDB - 用于 Mysql 集群场景。

都说 InnoDB 好，那还要不要使用 Memory 引擎

InnoDB 和 Memory 引擎的数据组织方式是不同的：

InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。
而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。

内存表不支持行锁，只支持表锁。

数据放在内存中，是内存表的优势，但也是一个劣势。因为，数据库重启的时候，所有的内存表都会被清空。

InnoDB 和 MyISAM 有哪些差异？

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
锁	支持表级锁	支持表级锁、行级锁
索引	采用非聚簇索引	主键采用聚簇索引，以提高 IO 效率
表空间	小	大
关注点	性能	事务
计数器	维护了计数器，`SELECT COUNT(*)` 效率为 `O(1)`	没有维护计数器，需要全表扫描

索引

什么是索引？为什么要使用索引？

“索引”是数据库为了提高查找效率的一种数据结构。

日常生活中，我们可以通过检索目录，来快速定位书本中的内容。索引和数据表，就好比目录和书，想要高效查询数据表，索引至关重要。在数据量小且负载较低时，不恰当的索引对于性能的影响可能还不明显；但随着数据量逐渐增大，性能则会急剧下降。因此，设置合理的索引是数据库查询性能优化的最有效手段。

索引的优点和缺点是什么？

✔️️️️️️️ 索引的优点：

索引大大减少了服务器需要扫描的数据量，从而加快检索速度。
索引可以帮助服务器避免排序和临时表。
索引可以将随机 I/O 变为顺序 I/O。
支持行级锁的数据库，如 InnoDB 会在访问行的时候加锁。使用索引可以减少访问的行数，从而减少锁的竞争，提高并发。
唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

❌ 索引的缺点：

创建和维护索引要耗费时间，这会随着数据量的增加而增加。
索引需要占用额外的物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立组合索引那么需要的空间就会更大。
写操作（INSERT/UPDATE/DELETE）时很可能需要更新索引，导致数据库的写操作性能降低。

基于以上，可以归纳出索引的基本使用规则：

索引不是越多越好，不要为所有列都创建索引
要尽量避免冗余和重复索引
要考虑删除未使用的索引
尽量的扩展索引，不要新建索引
频繁作为 WHERE 过滤条件的列应该考虑添加索引

何时适用索引？何时不适用索引？

✔️️️️ 什么情况适用索引？

字段的数值有唯一性的限制，如用户名。
频繁作为 WHERE 条件或 JOIN 条件的字段，尤其在数据表大的情况下。
频繁用于 GROUP BY 或 ORDER BY 的字段。将该字段作为索引，查询时就无需再排序了，因为 B+ 树本身就是按序存储的。
DISTINCT 字段需要创建索引。

❌ 什么情况不适用索引？

频繁写操作（ INSERT/UPDATE/DELETE ），也就意味着需要更新索引。
很少作为 WHERE 条件或 JOIN 条件的字段，也就意味着索引会经常无法命中，没有意义，还增加空间开销。
非常小的表，对于非常小的表，大部分情况下简单的全表扫描更高效。
特大型的表，建立和使用索引的代价将随之增长。可以考虑使用分区技术或 Nosql。

索引有哪些常见数据结构？

在 Mysql 中，索引是在存储引擎层而不是服务器层实现的，所以，并没有统一的索引标准。不同存储引擎的索引的数据结构也不相同。下面是 Mysql 常用存储引擎对一些主要索引数据结构的支持：

索引数据结构/存储引擎	InnoDB 引擎	MyISAM 引擎	Memory 引擎
B+ 树索引	✔️️️️️️️	✔️️️️️️️	✔️️️️️️️
Hash 索引	❌	❌	✔️️️️️️️
Full Text 索引	✔️️️️️️️	✔️️️️️️️	❌

Mysql 索引的常见数据结构：

哈希索引
- 因为索引数据结构紧凑，所以查询速度非常快。
- 只支持等值比较查询 - 包括 =、IN()、<=>；不支持任何范围查询，如 WHERE price > 100。
- 无法用于排序 - 因为哈希索引数据不是按照索引值顺序存储的。
- 不支持部分索引匹配查找 - 因为哈希索引时使用索引列的全部内容来进行哈希计算的。如，在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A，无法使用该索引。
- 不能用索引中的值来避免读取行 - 因为哈希索引只包含哈希值和行指针，不存储字段，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能影响不大。
- 哈希索引有可能出现哈希冲突
  - 出现哈希冲突时，必须遍历链表中所有的行指针，逐行比较，直到找到符合条件的行。
  - 如果哈希冲突多的话，维护索引的代价会很高。
B 树索引
- 适用于全键值查找、键值范围查找和键前缀查找，其中键前缀查找只适用于最左前缀查找。
- 所有的关键字（可以理解为数据）都存储在叶子节点，非叶子节点并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。
- 所有的叶子节点由指针连接。

什么是聚簇索引？什么是非聚簇索引？

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引又被称为**“聚簇索引（clustered index）”，其叶子节点存的是整行数据**。
- 聚簇表示数据行和相邻的键值紧凑地存储在一起，因为数据紧凑，所以访问快。
- 因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
- InnoDB 的聚簇索引实际是在同一个结构中保存了 B 树的索引和数据行。
非主键索引又被称为**“二级索引（secondary index）”，其叶子节点存的是主键的值**。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于 249 个。

聚簇索引和非聚簇索引的查询有什么区别

如果语句是 select * from T where ID=500，即聚簇索引查询方式，则只需要搜索主键索引树；
如果语句是 select * from T where k=5，即非聚簇索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非聚簇索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

显然，主键长度越小，非聚簇索引的叶子节点就越小，非聚簇索引占用的空间也就越小。

为什么 InnoDB 选择 B+ 树作为索引的数据结构

B+ 树 vs B 树
- B+ 树只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，所以 B+ 树的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。
- 另外，B+ 树叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。
B+树 vs 二叉树
- 对于有 N 个叶子节点的 B+ 树，其搜索复杂度为 O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。
- 在实际的应用当中， d 值是大于 100 的，这样就保证了，即使数据达到千万级别时，B+ 树的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。
- 而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+ 树高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
B+树 vs Hash
- Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。
- 但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+ 树索引要比 Hash 表索引有着更广泛的适用场景的原因。

索引有哪些优化策略？

索引基本原则

索引不是越多越好，不要为所有列都创建索引。要考虑到索引的维护代价、空间占用和查询时回表的代价。索引一定是按需创建的，并且要尽可能确保足够轻量。一旦创建了多字段的联合索引，我们要考虑尽可能利用索引本身完成数据查询，减少回表的成本。
要尽量避免冗余和重复索引。
要考虑删除未使用的索引。
尽量的扩展索引，不要新建索引。
频繁作为 WHERE 过滤条件的列应该考虑添加索引。

覆盖索引

覆盖索引是指：索引上的信息足够满足查询请求，不需要回表查询数据。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀匹配原则

这里的最左前缀，可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

如果是联合索引，那么 key 也由多个列组成，同时，索引只能用于查找 key 是否存在（相等），遇到范围查询 (>、<、BETWEEN、LIKE) 就不能进一步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。

应该将选择性高的列或基数大的列优先排在多列索引最前列。“索引的选择性”是指不重复的索引值和记录总数的比值，选择性越高，查询效率越高。但有时，也需要考虑 WHERE 子句中的排序、分组和范围条件等因素，这些因素也会对查询性能造成较大影响。

前缀索引

“前缀索引”是指索引开始的部分字符。对于 BLOB/TEXT/VARCHAR 这种文本类型的列，必须使用前缀索引，因为数据库往往不允许索引这些列的完整长度。

前缀索引的优点是可以大大节约索引空间，从而提高索引效率。

前缀索引的缺点是会降低索引的区分度。此外，order by 无法使用前缀索引，无法把前缀索引用作覆盖索引。

独立索引

索引列不能是表达式的一部分，也不能是函数的参数
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

使用索引扫描来排序：ORDER BY 的字段作为索引，这样命中索引的查询结果，不需要额外排序

= 和 in 可以乱序：不需要考虑 =、IN 等的顺序，Mysql 会自动优化这些条件的顺序，以匹配尽可能多的索引列。

哪些情况下，索引会失效？

导致索引失效的情况有：

对索引使用左或者左右模糊匹配
对索引使用函数或表达式
对索引隐式类型转换
联合索引不遵循最左匹配原则
WHERE 子句中的 OR

普通索引和唯一索引，应该怎么选择？

普通索引和唯一索引的查询性能相差微乎其微。

为什么 MySQL 单表不要超过 2000W 行

事务

什么是事务，什么是 ACID

“事务”指的是满足 ACID 特性的一组操作。事务内的 SQL 语句，要么全执行成功，要么全执行失败。可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

ACID 是数据库事务正确执行的四个基本要素。

原子性（Atomicity）
- 事务被视为不可分割的最小单元，事务中的所有操作要么全部提交成功，要么全部失败回滚。
- 回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
一致性（Consistency）
- 数据库在事务执行前后都保持一致性状态。
- 在一致性状态下，所有事务对一个数据的读取结果都是相同的。
隔离性（Isolation）
- 一个事务所做的修改在最终提交以前，对其它事务是不可见的。
持久性（Durability）
- 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
- 可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性。

事务存在哪些并发修改问题

事务中存在的并发修改问题有：

丢失修改
脏读
不可重复读
幻读

“丢失修改”是指一个事务的更新操作被另外一个事务的更新操作替换。

如下图所示，T₁ 和 T₂ 两个事务对同一个数据进行修改，T₁ 先修改，T₂ 随后修改，T₂ 的修改覆盖了 T₁ 的修改。

“脏读（dirty read）”是指当前事务可以读取其他事务未提交的数据。

如下图所示，T₁ 修改一个数据，T₂ 随后读取这个数据。如果 T₁ 撤销了这次修改，那么 T₂ 读取的数据是脏数据。

“不可重复读（non-repeatable read）”是指一个事务内多次读取同一数据，过程中，该数据被其他事务所修改，导致当前事务多次读取的数据可能不一致。

如下图所示，T₂ 读取一个数据，T₁ 对该数据做了修改。如果 T₂ 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

“幻读（phantom read）”是指一个事务内多次读取同一范围的数据，过程中，其他事务在该数据范围新增了数据，导致当前事务未发现新增数据。

事务 T₁ 读取某个范围内的记录时，事务 T₂ 在该范围内插入了新的记录，T₁ 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

有哪些事务隔离级别，分别解决了什么问题

为了解决以上提到的并发修改问题，SQL 标准提出了四种“事务隔离级别”来应对这些问题。事务隔离级别等级越高，越能保证数据的一致性和完整性，但是执行效率也越低。因此，设置数据库的事务隔离级别时需要做一下权衡。

事务隔离级别从低到高分别是：

“读未提交（read uncommitted）” - 是指，事务中的修改，即使没有提交，对其它事务也是可见的。
**“读已提交（read committed）” ** - 是指，事务提交后，其他事务才能看到它的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
- 读已提交解决了脏读的问题。
- 读已提交是大多数数据库的默认事务隔离级别，如 Oracle。
“可重复读（repeatable read）” - 是指：保证在同一个事务中多次读取同样数据的结果是一样的。
- 可重复读解决了不可重复读问题。
- 可重复读是 InnoDB 存储引擎的默认事务隔离级别。
“串行化（serializable ）” - 是指，强制事务串行执行，对于同一行记录，加读写锁，一旦出现锁冲突，必须等前面的事务释放锁。
- 串行化解决了幻读问题。由于强制事务串行执行，自然避免了所有的并发问题。
- 串行化策略会在读取的每一行数据上都加锁，这可能导致大量的超时和锁竞争。这对于高并发应用基本上是不可接受的，所以一般不会采用这个级别。

事务隔离级别对并发修改问题的解决情况：

隔离级别	丢失修改	脏读	不可重复读	幻读
读未提交	✔️️️	❌	❌	❌
读已提交	✔️️️	✔️️️	❌	❌
可重复读	✔️️️	✔️️️	✔️️️	❌
可串行化	✔️️️	✔️️️	✔️️️	✔️️️

各事务隔离级别是如何实现的

Mysql 中的事务功能是在存储引擎层实现的，并非所有存储引擎都支持事务功能。InnoDB 是 Mysql 的首先事务存储引擎。

四种隔离级别具体是如何实现的呢？

以 InnoDB 的事务实现来说明：

对于“读未提交”隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
对于“串行化”隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
对于“读提交”和“可重复读”隔离级别的事务来说，它们都是通过 ReadView 来实现的，区别仅在于创建 ReadView 的时机不同。ReadView 可以理解为一个数据快照。
- “读提交”隔离级别是在“每个语句执行前”都会重新生成一个 ReadView
- “可重复读”隔离级别是在“启动事务时”生成一个 ReadView，然后整个事务期间都在用这个 ReadView。

什么是 MVCC

MVCC 是 Multi Version Concurrency Control 的缩写，即“多版本并发控制”。MVCC 的设计目标是提高数据库的并发性，采用非阻塞的方式去处理读/写并发冲突，可以将其看成一种乐观锁。

不仅是 Mysql，包括 Oracle、PostgreSQL 等其他关系型数据库都实现了各自的 MVCC，实现机制没有统一标准。MVCC 是 InnoDB 存储引擎实现事务隔离级别的一种具体方式。其主要用于实现读已提交和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

MVCC 的实现原理是什么

MVCC 的实现原理，主要基于隐式字段、UndoLog、ReadView 来实现。

隐式字段

InnoDB 存储引擎中，数据表的每行记录，除了用户显示定义的字段以外，还有几个数据库隐式定义的字段：

row_id - 隐藏的自增 ID。如果数据表没有指定主键，InnoDB 会自动基于 row_id 产生一个聚簇索引。
trx_id - 最近修改的事务 ID。事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
roll_pointer - 回滚指针，指向这条记录的上一个版本。

UndoLog

MVCC 的多版本指的是多个版本的快照，快照存储在 UndoLog 中。该日志通过回滚指针 roll_pointer 把一个数据行的所有快照链接起来，构成一个版本链。

ReadView

ReadView 就是事务进行快照读时产生的读视图（快照）。

ReadView 有四个重要的字段：

m_ids - 指的是在创建 ReadView 时，当前数据库中“活跃事务”的事务 ID 列表。注意：这是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
min_trx_id - 指的是在创建 ReadView 时，当前数据库中“活跃事务”中事务 id 最小的事务，也就是 m_ids 的最小值。
max_trx_id - 这个并不是 m_ids 的最大值，而是指创建 ReadView 时当前数据库中应该给下一个事务分配的 ID 值，也就是全局事务中最大的事务 ID 值 + 1；
creator_trx_id - 指的是创建该 ReadView 的事务的事务 ID。

在创建 ReadView 后，我们可以将记录中的 trx_id 划分为三种情况：

已提交事务
已启动但未提交的事务
未启动的事务

ReadView 如何判断版本链中哪个版本可见？

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

trx_id == creator_trx_id - 表示 trx_id 版本记录由 ReadView 所代表的当前事务产生，当然可以访问。
trx_id < min_trx_id - 表示 trx_id 版本记录是在创建 ReadView 之前已提交的事务生成的，当前事务可以访问。
trx_id >= max_trx_id - 表示 trx_id 版本记录是在创建 ReadView 之后才启动的事务生成的，当前事务不可以访问。
min_trx_id <= trx_id < max_trx_id - 需要判断 trx_id 是否在 m_ids 列表中
- 如果 trx_id 在 m_ids 列表中，表示生成 trx_id 版本记录的事务依然活跃（未提交事务），当前事务不可以访问。
- 如果 trx_id 不在 m_ids 列表中，表示生成 trx_id 版本记录的事务已提交，当前事务可以访问。

这种通过“版本链”来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

MVCC 实现了哪些隔离级别，如何实现的

对于“读已提交”和“可重复读”隔离级别的事务来说，它们都是通过 MVCC 的 ReadView 机制来实现的，区别仅在于创建 ReadView 的时机不同。ReadView 可以理解为一个数据快照。

可重复读隔离级别只有在启动事务时才会创建 ReadView，然后整个事务期间都使用这个 ReadView。这样就保证了在事务期间读到的数据都是事务启动前的记录。
读已提交隔离级别每次读取数据时都会创建一个 ReadView。这意味着，事务期间的多次读取同一条数据，前后读取的数据可能会出现不一致——因为，这期间可能有另外一个事务修改了该记录，并提交了事务。

这两个隔离级别的实现是通过“事务的 ReadView 里的字段”和“记录中的两个隐藏列”的比对，来控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

MySQL InnoDB 引擎的默认隔离级别虽然是“可重复读”，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

针对快照读（普通 SELECT 语句），通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（SELECT ... FOR UPDATE 等语句），通过 Next-Key Lock（记录锁+间隙锁）方式解决了幻读，因为当执行 SELECT ... FOR UPDATE 语句的时候，会加上 Next-Key Lock，如果有其他事务在 Next-Key Lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题。

锁

Mysql 中有哪些锁

为了解决并发访问题，Mysql 支持了很多种锁来实现不同程度的隔离性，以保证数据的安全性。

独享锁和共享锁

InnoDB 实现标准行级锁定，根据是否独占资源，可以把锁分为两类：

独享锁（Exclusive），简写为 X 锁，又称写锁。
- 独占锁允许持有该锁的事务更新或删除行。
- 使用方式：SELECT ... FOR UPDATE;
共享锁（Shared），简写为 S 锁，又称读锁。
- 共享锁允许持有该锁的事务读取一行。
- 使用方式：SELECT ... LOCK IN SHARE MODE;

为什么要引入读写锁机制？

实际上，读写锁是一种通用的锁机制，并非 Mysql 的专利。在很多软件领域，都存在读写锁机制。

因为读操作本身是线程安全的，而一般业务往往又是读多写少的情况。因此，如果对读操作进行互斥，是不必要的，并且会大大降低并发访问效率。正式为了应对这种问题，产生了读写锁机制。

读写锁的特点是：读读不互斥、读写互斥、写写互斥。简言之：只要存在写锁，其他事务就不能做任何操作。

注：InnoDB 下的行锁、间隙锁、next-key 锁统统属于独享锁。

悲观锁和乐观锁

基于加锁方式分类，Mysql 可以分为悲观锁和乐观锁。

悲观锁 - 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
- 在查询完数据的时候就把事务锁起来，直到提交事务（COMMIT）
- 实现方式：使用数据库中的锁机制。
乐观锁 - 假设最好的情况——每次访问数据时，都假设数据不会被其他线程修改，不必加锁。只在更新的时候，判断一下在此期间是否有其他线程更新该数据。
- 实现方式：更新数据时，先使用版本号机制或 CAS 算法检查数据是否被修改。

为什么要引入乐观锁？

乐观锁也是一种通用的锁机制，在很多软件领域，都存在乐观锁机制。

锁，意味着互斥，意味着阻塞。在高并发场景下，锁越多，阻塞越多，势必会拉低并发性能。那么，为了提高并发度，能不能尽量不加锁呢？

乐观锁，顾名思义，就是假设最好的情况——每次访问数据时，都假设数据不会被其他线程修改，不必加锁。虽然不加锁，但不意味着什么都不做，而是在更新的时候，判断一下在此期间是否有其他线程更新该数据。乐观锁最常见的实现方式，是使用版本号机制或 CAS 算法（Compare And Swap）去实现。

乐观锁的优点是：减少锁竞争，提高并发度。

乐观锁的缺点是：

存在 ABA 问题。所谓的 ABA 问题是指在并发编程中，如果一个变量初次读取的时候是 A 值，它的值被改成了 B，然后又其他线程把 B 值改成了 A，而另一个早期线程在对比值时会误以为此值没有发生改变，但其实已经发生变化了
如果乐观锁所检查的数据存在大量锁竞争，会由于不断循环重试，产生大量的 CPU 开销。

全局锁、表级锁、行级锁

前文提到了，锁，意味着互斥，意味着阻塞。在高并发场景下，锁越多，阻塞越多，势必会拉低并发性能。在不得不加锁的情况下，显然，加锁的范围越小，锁竞争的发生频率就越小，系统的并发程度就越高。但是，加锁也需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销，锁粒度越小，系统的锁操作开销就越大。因此，在选择锁粒度时，也需要在锁开销和并发程度之间做一个权衡。

根据加锁的范围，MySQL 的锁大致可以分为：

全局锁 - “全局锁”会锁定整个数据库。
表级锁（table lock） - “表级锁”锁定整张表。用户对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他用户才能获得读锁，读锁之间不会相互阻塞。表级锁有：
- 表锁
- 元数据锁（MDL）
- 意向锁（Intention Lock）
- 自增锁（AUTO-INC）
行级锁（row lock） - “行级锁”锁定指定的行记录。这样其它线程还是可以对同一个表中的其它行记录进行操作。行级锁有：
- 记录锁（Record Lock）
- 间隙锁（Gap Lock）
- 临键锁（Next-Key Lock）
- 插入意向锁

以上各种加锁粒度，在不同存储引擎中的支持情况并不相同。如：InnoDB 支持全局锁、表级锁、行级锁；而 MyISAM 只支持全局锁、表级锁。

死锁是如何产生的

“死锁”是指两个或多个事务竞争同一资源，并请求锁定对方占用的资源，从而导致恶性循环的现象。

产生死锁的场景：

当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。
多个事务同时锁定同一个资源时，也会产生死锁。

如何避免死锁

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。由此可知，要想避免死锁，就要从这几个必要条件上去着手：

更新表时，尽量使用主键更新，减少冲突；
避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
设置合理的锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。
在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；
在允许幻读和不可重复读的情况下，尽量使用读已提交事务隔离级别，可以避免 Gap Lock 导致的死锁问题；
还可以使用其它的方式来代替数据库实现幂等性校验。例如，使用 Redis 以及 ZooKeeper 来实现，运行效率比数据库更佳。

如何解决死锁

当出现死锁以后，有两种策略：

设置事务等待锁的超时时间。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
开启死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是，我直接把这个时间设置成一个很小的值，比如 1s，也是不可取的。当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。为了解决死锁问题，不同数据库实现了各自的死锁检测和超时机制。InnoDB 的处理策略是：将持有最少行级排它锁的事务进行回滚。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。因此，死锁检测可能会耗费大量的 CPU。

HA

优化

哪种 COUNT 性能最好

先说结论：按照效率排序的话，COUNT(字段) < COUNT(主键 id) < COUNT(1) ≈ COUNT(*)。推荐采用 COUNT(*) 。

对于 COUNT(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
对于 COUNT(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
单看这两个用法的差别的话，你能对比出来，COUNT(1) 执行得要比 COUNT(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
对于 COUNT(字段) 来说：
- 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
- 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
- 也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。

但是 COUNT(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。COUNT(*) 肯定不是 null，按行累加。

不同的 MySQL 引擎中，COUNT(*) 有不同的实现方式：

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 COUNT(*) 的时候会直接返回这个数，效率很高；
而 InnoDB 引擎就麻烦了，它执行 COUNT(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

为什么 InnoDB 不跟 MyISAM 一样，也维护一个计数器？

因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 COUNT(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

MyISAM 表虽然 COUNT(*) 很快，但是不支持事务；
show table status 命令虽然返回很快，但是不准确；
InnoDB 表直接 COUNT(*) 会遍历全表，虽然结果准确，但会导致性能问题。

如何优化查询计数？

可以使用 Redis 保存计数，但存在丢失更新一集数据不一致问题。

可以使用数据库其他表保存计数，但要用事务进行控制，增/删数据时，同步改变计数。

EXISTS 和 IN 有什么区别

EXISTS - 先对外表进行循环查询，再将查询结果放入 EXISTS 的子查询中进行条件比较，确定外层查询数据是否保留；
IN - 先查询内表，将内表的查询结果作为条件，提供给外表查询语句进行比较；

索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表。在这种方式下效率是最高的。

比如下面这样：

 SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
 SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)

当 A 小于 B 时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于：

 for i in A
     for j in B
         if j.cc == i.cc then ...

当 B 小于 A 时用 IN，因为实现的逻辑类似于：

 for i in B
     for j in A
         if j.cc == i.cc then ...

哪个表小就用哪个表来驱动，A 表小就用 EXISTS，B 表小就用 IN；如果两个表大小相当，则使用 EXISTS 和 IN 的区别不大。

UNION 和 UNION ALL 有什么区别

UNION 和 UNION ALL 都是将两个结果集合并为一个，两个要联合的 SQL 语句字段个数必须一样，而且字段类型要“相容”（一致）。

UNION 需要进行去重扫描，因此消息较低；而 UNION ALL 不会进行去重。
UNION 会按照字段的顺序进行排序；而 UNION ALL 只是简单的将两个结果合并就返回。

JOIN 有哪些类型

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

JOIN 可以分为两大类：内连接和外连接。

内连接（INNER JOIN）

内连接又称等值连接，使用 INNER JOIN 关键字。在没有条件语句的情况下返回笛卡尔积。

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

自连接（=）

自连接可以看成内连接的一种，只是连接的表是自身而已。自然连接是把同名列通过 = 连接起来的，同名列可以有多个。

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

自然连接（NATURAL JOIN）

内连接提供连接的列，而自然连接自动连接所有同名列。自然连接使用 NATURAL JOIN 关键字。

SELECT *
FROM Products
NATURAL JOIN Customers;

外连接（`OUTER JOIN`）

外连接返回一个表中的所有行，并且仅返回来自此表中满足连接条件的那些行，即两个表中的列是相等的。外连接分为左外连接、右外连接、全外连接（Mysql 不支持）。

左连接（LEFT JOIN）

左外连接就是保留左表没有关联的行。

SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;

右连接（RIGHT JOIN）

右外连接就是保留右表没有关联的行。

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON customers.cust_id = orders.cust_id;

为什么这些 SQL 语句逻辑相同，性能却差异巨大？

函数操作会破坏索引有序性

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

示例：

CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

select COUNT(*) from tradelog where month(t_modified)=7;

由于在 t_modified 字段加了 month() 函数操作，导致了全索引扫描。为了能够用上索引的快速定位能力，我们就要把 SQL 语句改成基于字段本身的范围查询。

select COUNT(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

隐式转换

下面两个 SQL 的执行流程相同：

select * from tradelog where tradeid=110717;
select * from tradelog where CAST(tradid AS signed int) = 110717;

交易编号 tradeid 这个字段上，本来就有索引，但是 explain 的结果却显示，这条语句需要走全表扫描。这是由于这条语句隐式增加了转换函数，而对索引字段做函数操作，优化器会放弃走树搜索功能。

隐式字符编码转换

示例：

CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /* 操作步骤 */
  `step_info` varchar(32) DEFAULT NULL, /* 步骤信息 */
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

SELECT d.*
FROM tradelog l, trade_detail d
WHERE d.tradeid = l.tradeid AND l.id = 2;
# 等价于
select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

# 不需要做字符编码转换
EXPLAIN
SELECT l.operator
FROM tradelog l, trade_detail d
WHERE d.tradeid = l.tradeid AND d.id = 2;

字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。

为什么我只查一行的语句，也执行这么慢？

查询长时间不返回

查询结果长时间不返回。

一般碰到这种情况的话，大概率是表被锁住了。接下来分析原因的时候，一般都是首先执行一下 show processlist 命令，看看当前语句处于什么状态。

使用 show processlist 命令查看 Waiting for table metadata lock 的示意图

出现这个状态表示的是，现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。

MySQL 有哪些“饮鸩止渴”提高性能的方法？

短连接风暴

短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。

MySQL 建立连接的成本很高。
- 除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。
短连接模型存在一个风险：一旦数据库处理速度很慢，连接数就会暴涨。
max_connections 控制一个 MySQL 实例同时存在的连接数的上限。
- 超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

解决方法 1：先处理掉那些占着连接但是不工作的线程

show processlist 查看 sleep 的线程，然后干掉空闲的连接。注意：可能会误杀事务。
应该优先断开事务外空闲的连接。
- 通过查 information_schema 库的 innodb_trx 表判断是否处于事务中。
再考虑断开事务内空闲太久的连接。

解决方法 2：减少连接过程的消耗

如果想短时间创建大量数据库连接，有一种做法是跳过权限验证。

跳过权限验证的方法是：重启数据库，并使用 –skip-grant-tables 参数启动。

注意：此方法风险极高，不建议使用。

慢查询性能问题

一般有三种可能：

索引没有设计好；
SQL 语句没写好；
MySQL 选错了索引。
- 可以通过 force index 强制使用某个索引

QPS 突增问题

有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。

应对方法：

一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。
如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。
- 这个方法是用于止血的，但风险很高，不建议使用。

个人观点：以上方法都是基于 DBA 视角的处理方式。实际环境中，应该做好数据库 QPS、CPU 监控，如果发现请求量激增，快要达到瓶颈，可以先紧急弹性扩容，保障业务不损失。然后排查原因，是否是新业务设计不当导致、是否是大数据在也业务高峰期进行数据分析导致，等等。

join 语句如何优化

大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

MRR

MRR 优化后的语句执行流程：

根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
将 read_rnd_buffer 中的 id 进行递增排序；
排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

这里，read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。临时表在使用上有以下几个特点：

建表语法是 create temporary table …。
一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。
临时表可以与普通表同名。
session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
show tables 命令不显示临时表。

到底可不可以使用 join

如果可以使用被驱动表的索引，join 语句还是有其优势的；
不能使用被驱动表的索引，只能使用 Block Nested-Loop Join 算法，这样的语句就尽量不要使用；
在使用 join 的时候，应该让小表做驱动表。

我查了这么多数据会不会把数据库内存打爆

全表扫描对 server 层的影响

MySQL 是“边读边发的”

InnoDB 的数据是保存在主键索引上的，所以全表扫描实际上是直接扫描表 t 的主键索引。这条查询语句由于没有其他的判断条件，所以查到的每一行都可以直接放到结果集里面，然后返回给客户端。

查询的结果是分段发给客户端的，因此扫描全表，查询返回大量的数据，并不会把内存打爆。

全表扫描对 InnoDB 的影响

对于 InnoDB 引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于 InnoDB 对 LRU 算法做了改进，冷数据的全表扫描，对 Buffer Pool 的影响也能做到可控。

其他

为什么临时表可以重名

临时表在使用上有以下几个特点：

建表语法是 create temporary table …。
一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。
临时表可以与普通表同名。
session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
show tables 命令不显示临时表。

临时表特别适合 join 优化这种场景，原因是：

不同 session 的临时表是可以重名的，如果有多个 session 同时执行 join 优化，不需要担心表名重复导致建表失败的问题。
不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。

临时表的应用

由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。其中，分库分表系统的跨库查询就是一个典型的使用场景。

分库分表两种实现思路：

**第一种思路是，**在 proxy 层的进程代码中实现排序。

这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算。不过，这个方案的缺点也比较明显：

需要的开发工作量比较大。我们举例的这条语句还算是比较简单的，如果涉及到复杂的操作，比如 group by，甚至 join 这样的操作，对中间层的开发能力要求比较高；
对 proxy 端的压力比较大，尤其是很容易出现内存不够用和 CPU 瓶颈的问题。

**另一种思路就是，**把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。

比如上面这条语句，执行流程可以类似这样：

在汇总库上创建一个临时表 temp_ht，表里包含三个字段 v、k、t_modified；
在各个分库上执行

select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

把分库执行的结果插入到 temp_ht 表中；
执行

select v from temp_ht order by t_modified desc limit 100;

得到结果。

在实践中，我们往往会发现每个分库的计算量都不饱和，所以会直接把临时表 temp_ht 放到 32 个分库中的某一个上。

什么时候会使用内部临时表

如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

自增主键为什么不是连续的

表的结构定义存放在后缀名为.frm 的文件中，但是并不会保存自增值。

在 MyISAM 引擎里面，自增值是被写在数据文件上的。而在 InnoDB 中，自增值是被记录在内存的。

InnoDB 中，只保证了自增 id 是递增的，但不保证是连续的。这么做是处于性能考虑：语句执行失败也不回退自增 id。

自增 ID 不够用了怎么办？

grant 之后为什么要跟着 flush privilege

grant 语句会同时修改数据表和内存，判断权限的时候使用的是内存数据。因此，规范地使用 grant 和 revoke 语句，是不需要随后加上 flush privileges 语句的。

flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用 DML 语句操作系统权限表导致的，所以我们尽量不要使用这类语句。

参考资料

昵称

邮箱

网址

按正序
按倒序
按热度

Mysql 面试

# Mysql 面试

# 架构

# 一条 SQL 查询语句是如何执行的？

# 一条 SQL 更新语句是如何执行的？

# 为什么表数据删掉一半，表文件大小不变

# 为什么我的 MySQL 会“抖”一下？

# SELECT 语句内部的执行步骤是怎样的

# ORDER BY 是对分的组排序还是对分组中的记录排序呢？

# order by 是怎么工作的？

# 全字段排序

# rowid 排序

# 全字段排序 VS rowid 排序

# 存储引擎

# Mysql 有哪些常见存储引擎？

# 都说 InnoDB 好，那还要不要使用 Memory 引擎

# InnoDB 和 MyISAM 有哪些差异？

# 索引

# 什么是索引？为什么要使用索引？

# 索引的优点和缺点是什么？

# 何时适用索引？何时不适用索引？

# 索引有哪些常见数据结构？

# 什么是聚簇索引？什么是非聚簇索引？

# 聚簇索引和非聚簇索引的查询有什么区别

# 为什么 InnoDB 选择 B+ 树作为索引的数据结构

# 索引有哪些优化策略？

# 索引基本原则

# 覆盖索引

# 最左前缀匹配原则

# 前缀索引

# 独立索引

# 哪些情况下，索引会失效？

# 普通索引和唯一索引，应该怎么选择？

# 为什么 MySQL 单表不要超过 2000W 行

# 事务

# 什么是事务，什么是 ACID

# 事务存在哪些并发修改问题

# 有哪些事务隔离级别，分别解决了什么问题

# 各事务隔离级别是如何实现的

# 什么是 MVCC

# MVCC 的实现原理是什么

# 隐式字段

# UndoLog

# ReadView

# MVCC 实现了哪些隔离级别，如何实现的

# 锁

# Mysql 中有哪些锁

# 独享锁和共享锁

# 悲观锁和乐观锁

# 全局锁、表级锁、行级锁

# 死锁是如何产生的

# 如何避免死锁

# 如何解决死锁

# HA

# 优化

# 哪种 COUNT 性能最好

# EXISTS 和 IN 有什么区别

# UNION 和 UNION ALL 有什么区别

# JOIN 有哪些类型

# 内连接（INNER JOIN）

# 外连接（OUTER JOIN）

# 为什么这些 SQL 语句逻辑相同，性能却差异巨大？

# 函数操作会破坏索引有序性

# 隐式转换

# 隐式字符编码转换

# 为什么我只查一行的语句，也执行这么慢？

# 查询长时间不返回

# MySQL 有哪些“饮鸩止渴”提高性能的方法？

# 短连接风暴

# 解决方法 1：先处理掉那些占着连接但是不工作的线程

# 解决方法 2：减少连接过程的消耗

# 慢查询性能问题

# QPS 突增问题

# join 语句如何优化

# MRR

# 到底可不可以使用 join

# 我查了这么多数据会不会把数据库内存打爆

# 全表扫描对 server 层的影响

# 全表扫描对 InnoDB 的影响

# 其他

Mysql 面试

架构

一条 SQL 查询语句是如何执行的？

一条 SQL 更新语句是如何执行的？

为什么表数据删掉一半，表文件大小不变

为什么我的 MySQL 会“抖”一下？

`SELECT` 语句内部的执行步骤是怎样的

ORDER BY 是对分的组排序还是对分组中的记录排序呢？

`order by` 是怎么工作的？

全字段排序

rowid 排序

全字段排序 VS rowid 排序

存储引擎

Mysql 有哪些常见存储引擎？

都说 InnoDB 好，那还要不要使用 Memory 引擎

InnoDB 和 MyISAM 有哪些差异？

索引

什么是索引？为什么要使用索引？

索引的优点和缺点是什么？

何时适用索引？何时不适用索引？

索引有哪些常见数据结构？

什么是聚簇索引？什么是非聚簇索引？

聚簇索引和非聚簇索引的查询有什么区别

为什么 InnoDB 选择 B+ 树作为索引的数据结构

索引有哪些优化策略？

索引基本原则

覆盖索引

最左前缀匹配原则

前缀索引

独立索引

哪些情况下，索引会失效？

普通索引和唯一索引，应该怎么选择？

为什么 MySQL 单表不要超过 2000W 行

事务

什么是事务，什么是 ACID

事务存在哪些并发修改问题

有哪些事务隔离级别，分别解决了什么问题

各事务隔离级别是如何实现的

什么是 MVCC

MVCC 的实现原理是什么

隐式字段

UndoLog

ReadView

MVCC 实现了哪些隔离级别，如何实现的

锁

Mysql 中有哪些锁

独享锁和共享锁

悲观锁和乐观锁

全局锁、表级锁、行级锁

死锁是如何产生的

如何避免死锁

如何解决死锁

HA

优化

哪种 COUNT 性能最好

EXISTS 和 IN 有什么区别

UNION 和 UNION ALL 有什么区别

JOIN 有哪些类型

内连接（INNER JOIN）

外连接（`OUTER JOIN`）

为什么这些 SQL 语句逻辑相同，性能却差异巨大？

函数操作会破坏索引有序性

隐式转换

隐式字符编码转换

为什么我只查一行的语句，也执行这么慢？

查询长时间不返回

MySQL 有哪些“饮鸩止渴”提高性能的方法？

短连接风暴

解决方法 1：先处理掉那些占着连接但是不工作的线程

解决方法 2：减少连接过程的消耗

慢查询性能问题

QPS 突增问题

join 语句如何优化

MRR

到底可不可以使用 join

我查了这么多数据会不会把数据库内存打爆

全表扫描对 server 层的影响

全表扫描对 InnoDB 的影响

其他

为什么临时表可以重名