【转】MySQL 八股文

转自：
https://mp.weixin.qq.com/s/c-sy7tM0BmrqMUQFW7C65g

一、 基础篇

1、关系型和非关系型数据库的区别？

关系型数据库的优点：

• 容易理解，因为它采用了关系模型来组织数据
• 可以保持数据的一致性
• 数据更新的开销比较小
• 支持复杂查询（带 where 子句的查询）

非关系型数据库（NoSQL）的优点：

• 无需经过 SQL 层的解析，读写效率高
• 基于键值对，读写性能很高，易于扩展
• 可以支持多种类型数据的存储，如图片、文档等
• 可分为内存型数据库、文档型数据库，比如 Redis、MongoDB、HBase 等，适合数据量大、高可用的日志系统/地理位置存储系统

2、详细说一下一条 MySQL 语句执行的步骤

1. 客户端请求
2. 连接器（验证用户身份，给予权限）
3. 查询缓存（如果存在缓存则直接返回，不存在则继续执行后续的操作）
4. 分析器（对 SQL 进行词法分析和语法分析）
5. 优化器（对执行的 SQL 选择出最优的执行方案）
6. 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎所提供的接口）
7. 去引擎层获取数据返回（如果开启了查询缓存则会缓存查询的结果）

二、索引篇

1、MySQL 使用索引的原因？

• 索引的出现，就是为了提高数据查询的效率，就像书的目录一样
• 对于数据库的表而言，索引其实就是它的“目录”
• 创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 帮助引擎层避免排序和创建临时表
• 将随机 I/O 变为顺序 I/O，加速表和表之间的连接

2、索引的三种常见底层数据结构以及优缺点

三种常见的索引底层数据结构，分别是哈希表、有序数组和搜索树：

• 哈希表适用于等值查询的场景，比如 memcached 以及其它一些 NoSQL 引擎，但不适合范围查询
• 有序数组索引只适用于静态存储引擎，等值和范围查询性能好，但数据更新的成本高
• N 叉树由于其读写上的性能优点以及适配磁盘访问模式，已经被广泛应用在数据库引擎中

3、索引的常见类型以及它是如何发挥作用的？

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

• 主键索引的叶子节点存的是整行数据，在 InnoDB 里也被称为聚簇索引
• 非主键索引叶子节点存的是主键的值，在 InnoDB 里也被称为二级索引

4、MyISAM 和 InnoDB 实现 B+ 树索引方式的区别是什么？

• InnoDB 存储引擎：B+ 树索引的叶子节点保存的是数据本身，其数据文件本身就是索引文件
• MyISAM 存储引擎：B+ 树索引的叶子节点保存的是数据的物理地址，叶子节点的 data 域存放的是数据记录的地址，索引文件和数据文件是分离的

5、InnoDB 为什么设计 B+ 树索引？

两个考虑因素：

• InnoDB 执行的场景和功能需要在特定查询上拥有较强的性能
• CPU 将磁盘上的数据加载到内存中需要花费大量时间，磁盘 I/O 操作

为什么选择 B+ 树：

• 哈希索引虽然能提供 O(1) 的复杂度查询，但是对于范围查询和排序却无法很好地支持，最终会导致全表扫描
• B 树虽然能够在非叶子节点存储数据，但会导致在查询连续数据时可能带来更多的随机 I/O
• B+ 树的所有叶子节点都可以通过指针来相互连接，减少顺序遍历带来的随机 I/O

普通索引还是唯一索引：

• 由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发建议优先考虑非唯一索引。

6、什么是覆盖索引和索引下推？

覆盖索引：

• 在某个查询里，索引 k 已经“覆盖了”我们的查询需求，就称为覆盖索引
• 覆盖索引可以减少树的搜索次数，显著提升查询性能，使用覆盖索引是一个常用的性能优化手段

索引下推：

• MySQL 5.6 引入了索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

7、哪些操作会导致索引失效？

• 对索引使用左或者左右模糊匹配，也就是 “like %xx” 或者 “like %xx%” 这两种方式都会造成索引失效。原因在于查询的结果可能是多个，MySQL 不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询
• 对索引进行函数/表达式计算，因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引
• 对索引进行隐式转换，这相当于使用了新函数
• where 子句中的 or 语句，只要有条件列不是索引列，就会进行全表扫描

8、如何给字符串加索引？

• 直接创建完整索引，这样可能会比较占用空间
• 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题
• 创建 hash 字段索引，查询性能稳定，但有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描

三、日志篇

1、MySQL 的 change buffer 是什么？

当需要更新一个数据页时，如果数据页在内存中就直接更新。而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中。

这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，再将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要注意的是，唯一索引的更新就不能使用 change buffer，事实上也只有普通索引才可以使用。

唯一索引更新不能使用 change buffer 的原因是唯一索引的特性决定了它的更新操作必须要立即生效，而 change buffer 是一种延迟写技术，它将更新操作暂时存储在内存中，延迟写入磁盘，以提高性能。由于唯一索引的更新操作必须立即生效，不能延迟，所以无法使用 change buffer 来优化唯一索引的更新。

需要注意的是，即使唯一索引无法使用 change buffer，数据库管理系统仍然会采取其他优化措施来提高唯一索引的更新性能，例如使用日志写入技术，确保数据的一致性和持久性。这样可以在一定程度上弥补唯一索引无法使用 change buffer 的性能影响。

适用场景

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见于账单类/日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer 中，但由于之后马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 I/O 的次数不仅不会减少，反而还会增加 change buffer 的维护代价。

2、MySQL 是如何判断扫描数的？

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条。只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。

3、MySQL 的 redo log 和 binlog 的区别？

4、为什么需要 redo log？

• redo log 主要用于 MySQL 异常重启后的数据恢复，确保了数据的一致性
• 其实是为了配合 MySQL 的 WAL 机制。因为 MySQL 在进行更新操作时，为了能够快速响应，所以采用了异步写回磁盘的技术，在写入内存后就返回。但是这样会存在 crash 后内存数据丢失的隐患，而 redo log 具备 crash safe 的能力

5、为什么 redo log 具有 crash safe 的能力，是 binlog 无法替代的？

第一点：redo log 可以确保 InnoDB 判断出哪些数据已经刷盘，哪些数据还没有：

• redo log 和 binlog 有一个很大的区别，一个是循环写，一个是追加写。也就是说 redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。而 binlog 是追加日志，保存的是全量的日志
• 当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但是没有一个标志可以让 InnoDB 判断出哪些数据已经刷盘，哪些数据还没有
• 但 redo log 不一样，只要是刷入磁盘的数据，都会从 redo log 中抹掉，因为是循环写！数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了

第二点：如果 redo log 写入失败，说明此次操作失败，事务也不可能提交：

• redo log 在每次更新操作完成后，就一定会写入日志，如果写入失败，说明此次操作失败，事务也不可能提交
• redo log 内部结构是基于页的，记录了这个页的字段值变化，只要 crash 后读取 redo log 进行重放，就可以恢复数据

6、当数据库 crash 后，如何恢复未刷盘的数据到内存中？

根据 redo log 和 binlog 的两阶段提交，未持久化的数据分为以下几种情况：

• change buffer 写入，redo log 虽然做了 fsync 但未 commit，binlog 未 fsync 到磁盘，这部分数据丢失
• change buffer 写入，redo log 已经 fsync 但未 commit，binlog 已经 fsync 到磁盘，先从 binlog 恢复 redo log，再从 redo log 恢复 change buffer
• change buffer 写入，redo log 和 binlog 都已经 fsync，直接从 redo log 里恢复

7、redo log 的写入方式？

redo log 包括两部分内容，分别是内存中的日志缓冲（redo log buffer）和磁盘上的日志文件（redo log file）。

MySQL 每执行一条 DML 语句，会先把记录写入 redo log buffer（用户空间），再保存到内核空间的缓冲区 OS buffer 中，在后续某个时间点再一次性将多个操作记录写到 redo log file（刷盘）。这种先写日志，再写磁盘的技术，就是 WAL。

可以发现，redo log buffer 写入到 redo log file，是经过 OS buffer 中转的。可以通过参数 innodb_flush_log_at_trx_commit 进行配置，参数值含义如下：

• 0：延迟写，事务提交时不会将 redo log buffer 中的日志写入到 OS buffer，而是每秒写入 OS buffer 并调用写入到 redo log file 中
• 1：实时写，实时刷，事务每次提交都会将 redo log buffer 中的日志写入到 OS buffer 并保存到 redo log file 中
• 2：实时写，延迟刷，每次事务提交都写入到 OS buffer，然后每秒将日志写入到 redo log file

8、redo log 的执行流程？

假设执行的 SQL 如下：

update T set a = 1 where id = 666;

1. MySQL 客户端将请求语句发往 MySQL Server 层
2. MySQL Server 层接收到 SQL 请求后，对其进行分析、优化、执行等处理工作，将生成的 SQL 执行计划发到 InnoDB 存储引擎层执行
3. InnoDB 存储引擎层将"a 修改为 1"的这个操作记录到内存中
4. 记录到内存以后会修改 redo log 的记录，会再添加一行记录，其内容是“需要在哪个数据页上做什么修改”
5. 此后，将事务的状态设置为 prepare ，说明已经准备好提交事务了
6. 等到 MySQL Server 层处理完事务以后，会将事务的状态设置为 commit，也就是提交该事务
7. 在收到事务提交的请求以后，redo log 会把刚才写入内存中的操作记录再写入到磁盘中，从而完成整个日志的记录过程

9、binlog 的是什么？起到什么作用？可以保证 crash safe 吗？

• binlog 是归档日志，属于 MySQL Server 层的日志。可以实现主从复制和数据恢复
• 当需要恢复数据时，可以取出某个时间范围内的 binlog 进行重放恢复
• binlog 不可以做 crash safe，因为在 crash 之前，binlog 可能没有写入完全 MySQL 就挂了，所以需要配合 redo log 才可以进行 crash safe

10、什么是两阶段提交？

MySQL 将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入 binlog，这就是“两阶段提交”。

两阶段提交就是为了让这两个状态保持逻辑上的一致。

redo log 用于恢复主机故障时未更新的物理数据，而 binlog 用于备份操作。两者本身就是独立的个体，要想保持一致，就必须使用分布式事务的解决方案来处理。

如果不用两阶段提交的话，可能会出现以下几种情况：

• 先写 redo log，crash 后，binlog 备份恢复时少了一次更新，与当前数据不一致
• 先写 binlog，crash 后，由于 redo log 没写入，事务无效，后续 binlog 备份恢复时，数据不一致

两阶段提交就是为了保证 redo log 和 binlog 数据的完全一致性。只有这两个日志文件在逻辑上高度一致了才能放心使用。

在恢复数据时，redo log 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redo log 是 prepare，则需要查询对应的 binlog 事务是否成功，再决定是回滚还是执行。

11、MySQL 怎么知道 binlog 是完整的？

一个事务的 binlog 是有完整格式的：

• statement 格式的 binlog，最后会有 COMMIT
• row 格式的 binlog，最后会有一个 XID event

12、什么是 WAL 技术，有什么优点？

WAL，英文全称是 Write-Ahead Logging，它的关键点就是日志先写内存，再写磁盘。MySQL 执行更新操作后，在真正把数据写入到磁盘前，先记录日志。

好处就是不用每一次操作都实时把数据写盘，就算 crash 后也可以通过 redo log 恢复，所以能够实现快速响应 SQL 语句。

13、binlog 日志的三种格式

• Statement：基于 SQL 语句的复制（statement-based replication, SBR）
• Row：基于行的复制（row-based replication, RBR）
• Mixed：混合模式复制（mixed-based replication, MBR）

Statement 格式

每一条会修改数据的 SQL 都会记录在 binlog 中。

• 优点：不需要记录每一行的变化，减少了 binlog 的日志量，节约了 I/O，提高性能
• 缺点：由于记录的只是执行语句，为了这些语句能在备库上正确地运行，所以还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库上得到和在主库端执行时相同的结果

Row 格式

不记录 SQL 语句的上下文相关信息，仅保存哪条记录被修改。

• 优点：binlog 中可以不记录执行的 SQL 语句的上下文相关的信息，仅需要记录哪一条记录被修改成什么了。所以 row level 的日志内容会非常清楚地记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程或者 function 或者 trigger 的调用和触发无法被正确复制的问题
• 缺点：可能会产生大量的日志内容

Mixed 格式

实际上就是 Statement 与 Row 的结合。

一般的语句修改使用 Statment 格式保存 binlog；Statement 无法完成主从复制的操作，如一些函数，则采用 Row 格式保存 binlog，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式。

14、redo log 的日志格式

redo log buffer （内存中）是由首尾相连的四个文件组成的，它们分别是：ib_logfile_0、ib_logfile_1、ib_logfile_2、ib_logfile_3。

• write pos 是当前记录的位置，一边写一边往后移，写到第 3 号文件末尾后就回到 0 号文件开头
• checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要先把记录更新到数据文件
• write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作
• 如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下
• 有了 redo log，当数据库发生宕机重启后，可以通过 redo log 将未落盘的数据（check point 之后的数据）恢复，保证已经提交的事务记录不会丢失，这种能力称为 crash safe

15、原本可以执行得很快的 SQL 语句，执行速度却比预期慢很多，原因是什么？如何解决？

从大到小可分为四种情况：

• MySQL 数据库本身被堵住了，比如系统或者网络资源不够
• SQL 语句被堵住了，比如表锁、行锁等，导致存储引擎不执行对应的 SQL 语句
• 索引使用不当，没有走索引
• 表中数据的特点导致的，走了索引，但回表次数庞大

解决方法：

• 考虑采用 force index 强行选择一个索引
• 考虑修改语句，引导 MySQL 使用我们期望的索引。比如把"order by b limit 1"改成"order by b, a limit 1"，语义的逻辑是相同的
• 在有些场景下，可以新建一个更合适的索引来提供给优化器做选择，或者删掉无用的索引

16、InnoDB 的数据页结构

一个数据页大致划分为七个部分：

• File Header：表示页的一些通用信息，占固定的 38 字节
• page Header：表示数据页专有信息，占固定的 56 字节
• inimum + Supermum：两个虚拟的伪记录，分别表示页中的最小记录和最大记录，占固定的 26 字节
• User Records：真正存储我们插入的数据，大小不固定
• Free Space：页中尚未使用的部分，大小不固定
• Page Directory：页中某些记录的相对位置，也就是各个槽对应的记录在页中的地址偏移量
• File Trailer：用于检验页是否完整，占固定大小 8 字节

四、数据篇

1、MySQL 是如何保证数据不丢失的？

• 只要 redo log 和 binlog 保证持久化，磁盘就能确保 MySQL 在异常重启后能恢复数据
• 在恢复数据时，redo log 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redo log 是 prepare，则需要查询对应的 binlog 事务是否成功，再决定是回滚还是执行

2、drop、truncate 和 delete 的区别？

• delete 语句执行删除的过程是每次从表中删除一行，同时将该行的删除操作作为事务记录在日志中保存以便进行回滚操作
• tuncate 则是一次性地从表中删除所有的数据并且不把删除操作记入日志保存，删除行是不能恢复的，并且在删除的过程中不会激活与表有关的删除触发器，执行速度快
• drop 语句将表所占用的空间全释放掉
• 在速度上，一般来说，drop > truncate > delete
• 如果想删除部分数据用 delete，注意带上 where 子句，回滚段要足够大
• 如果想删除表，当然用 drop，但如果想保留表并将所有数据删除，或者如果和事务无关，用 truncate 即可
• 如果和事务有关，或者想触发 trigger，还是用 delete
• 如果是整理表内部的碎片，可以用 truncate 跟上 reuse stroage，再重新导入/插入数据

3、MySQL 的两个 kill 命令

• kill query + 线程 id，表示终止这个线程中正在执行的语句
• kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接

kill 失败的原因？

• kill 命令被堵了，还没到位
• kill 命令到位了，但是没被立刻触发
• kill 命令被触发了，但是执行完也需要时间

4、如何理解 MySQL 的边读边发？

• 如果客户端接收慢，会导致服务端由于结果发不出去，这个事务的执行时间会很长
• 服务端并不需要保存一个完整的结果集，取数据和发数据的流程都是通过一个 next_buffer 来操作的
• 内存的数据页都是在 Buffer_Pool 中操作的
• InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法，使用链表实现，在实现上按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域

5、MySQL 的大表查询为什么不会爆内存？

• 由于 MySQL 是边读变发，因此对于数据量很大的查询结果来说，不会在 Server 端保存完整的结果集，所以如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆
• InnoDB 引擎内部，由于有淘汰策略，InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法，使用链表实现，在实现上按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。即使对冷数据进行全扫描，影响也能做到可控制

6、MySQL 临时表的用法和特性

• 只对当前 Session 可见
• 可以与普通表重名
• 增删改查用的是临时表
• show tables 不显示临时表
• 在实际应用中，临时表一般用于处理比较复杂的计算逻辑
• 由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理时临时表的重名问题，在线程退出的时候，临时表会自动删除

7、MySQL 存储引擎介绍（InnoDB、MyISAM、Memory）

• InnoDB 是事务型数据库的首选引擎，支持事务安全（ACID），支持行锁和外键。MySQL 5.5.5 之后，InnoDB 作为默认存储引擎
• MyISAM 是基于 ISAM 的存储引擎，并对其进行了扩展。它是在 Web、数据存储和其他应用环境下最常用的存储引擎之一。MyISAM 拥有较高的插入和查询速度，但不支持事务。在 MySQL 5.5.5 之前的版本中，MyISAM 是默认存储引擎
• Memory 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问

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

内存表就是使用 Memory 引擎创建的表。

不建议在生产环境上使用内存表。原因主要包括两个方面：锁粒度问题、数据持久化问题。

• 由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止
• 如果主库跟这个备库是双 M 架构，还可能导致主库的内存表数据被删掉

9、如果数据库误操作，如何执行数据恢复？

数据库在某个时候误操作，可以找到距离误操作最近的时间节点的 binlog，重放到临时数据库里，然后选择误删的数据节点，恢复到线上数据库。

五、主从备份篇

1、MySQL 是如何保证主备同步的？

主备关系的建立：

1. 一开始创建主备关系的时候，是由备库指定的，比如基于位点的主备关系，备库说“我要从 binlog 文件 A 的位置 P ”开始同步，主库就从这个指定的位置开始往后发
2. 主备关系搭建之后，是主库决定要发数据给备库的，所以主库有新的日志也会发给备库

MySQL 主备切换流程：

1. 客户端读写都是直接访问节点 A，而节点 B 是备库，只要将 A 的更新都同步过来，到本地执行就可以保证数据是相同的
2. 当需要切换的时候就把节点换一下，A 是 B 的备库

一个事务完整的同步过程：

1. 备库 B 和主库 A 建立了长连接，主库 A 内部有专门的线程用于维护这个长连接
2. 在备库 B 上通过 changemaster 命令设置主库 A 的 IP 端口和用户名密码，以及从哪个位置开始请求 binlog（包括文件名和日志偏移量）
3. 在备库 B 上执行 start slave 命令，备库会启动两个线程：io_thread 和 sql_thread，分别负责建立连接和读取中继日志进行解析执行
4. 备库读取主库传过来的 binlog 文件，备库收到文件后写到本地成为中继日志
5. 后来由于多线程复制方案的引入，sql_thread 演化成了多个线程

2、什么是主备延迟？

主库和备库在执行同一个事务的时候出现时间差的问题，主要原因有：

• 有些部署条件下，备库所在机器的性能要比主库的性能差
• 备库的压力较大
• 大事务，一个语句在主库上执行 10 分钟，那么这个事务可能会导致从库延迟 10 分钟

3、为什么要有多线程复制策略？

• 因为单线程复制的能力全面低于多线程复制，对于更新压力较大的主库，备库可能是一直追不上的，带来的现象就是备库上seconds_behind_master 值会越来越大
• 在实际应用中，建议使用可靠性优先策略，减少主备延迟，提升系统可用性，尽量减少大事务操作，把大事务拆分小事务

4、MySQL 的并行策略有哪些？

• 按表分发策略：如果两个事务更新不同的表，那么它们就可以并行。因为数据是存储在表里的，所以按表分发可以保证两个 worker 不会更新同一行。缺点是如果碰到热点表，比如所有的更新事务都涉及到某一个表的时候，所有的事务都会被分配到同一个 worker 中，就变成单线程复制了
• 按行分发策略：如果两个事务没有更新相同的行，那么它们在备库上可以并行。显然这个模式要求 binlog 格式必须是 row。缺点是相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源

5、MySQL 的一主一备和一主多从有什么区别？

在一主一备的双 M 架构里，主备切换只需要把客户端流量切到备库；而在一主多从的架构里，主备切换除了要把客户端流量切到备库外，还需要把从库接到新主库上。

6、MySQL 的并发连接和并发查询有什么区别？

• 在执行 show processlist 的结果里，看到了几千个连接，这里指的是并发连接。而“当前正在执行”的语句，才是并发查询
• 并发连接数多影响的是内存，并发查询太高对 CPU 不利
• 一个机器的 CPU 核数有限，线程全冲进来，上下文切换的成本就会太高。所以需要设置参数 innodb_thread_concurrency 用来限制线程数，当线程数达到该参数值时，InnoDB 就会认为线程数用完了，会阻止其他语句进入引擎执行

六、性能篇

1、短时间提高 MySQL 性能的方法

• kill connection + id：先处理掉那些占着连接但是不工作的线程，或者再考虑断开事务内空闲太久的连接
• 减少连接过程的消耗

在 MySQL 中会引发性能问题的慢查询，大体有以下三种可能：

• 索引没有设计好
• SQL 语句没写好
• MySQL 选错了索引（force index）

2、为什么 MySQL 自增主键 id 会出现不连续？

• 唯一键冲突
• 事务回滚
• 自增主键的批量申请
• 深层次原因是：MySQL 不判断自增主键是否存在，从而减少加锁的时间范围和粒度，这样能保持更高的性能，确保自增主键不能回退，所以才有自增主键不连续

自增主键怎么做到唯一性？

自增值加 1，通过自增锁控制并发。

3、InnoDB 为什么要用自增 id 作为主键？

自增主键的插入模式，符合递增插入，每次都是追加操作，不涉及挪动记录，也不会触发叶子节点的分裂。每次插入新的记录就会顺序添加到当前索引节点的后续位置，当一页写满时，就会自动开辟一个新的页。

而用业务逻辑的字段做主键，不容易保证有序插入，每次插入主键的值近似于随机。因此每次新纪录都会被插到现有索引页的中间某个位置， 频繁地移动、分页操作会造成大量的碎片，得到不够紧凑的索引结构，写数据成本较高。

4、如何最快的复制一张表？

为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表：

• 使用 mysqldump 命令将数据导出成一组 insert 语句
• 直接将结果导出成 .csv 文件。MySQL 提供语法，用来将查询结果导出到服务端本地目录select * from db1.t where a > 900 into outfile '/server_tmp/t.csv'，得到 .csv 导出文件后，就可以用 load data 命令将数据导入到目标表 db2.t 中load data infile '/server_tmp/t.csv' into table db2.t
• 物理拷贝：在 MySQL 5.6 版本引入了可传输表空间（transportable tablespace）的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能

5、grant 和 flush privileges 语句

• grant 语句会同时修改数据表和内存，判断权限的时候使用的是内存数据，因此规范使用是不需要加上 flush privileges 语句
• flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用

6、要不要使用分区表？

• 分区并不是越细越好。实际上，单表或者单分区的数据在一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了
• 分区也不要提前预留太多，在使用之前预先创建即可。比如如果是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区，要及时 drop 掉

7、join 的用法

• 使用 left join，左边的表不一定是驱动表
• 如果需要 left join 的语义，就不能把被驱动表的字段放在 where 条件里做等值判断或不等值判断，必须都写在 on 里面
• 标准的 group by 语句，是需要在 select 部分加一个聚合函数，比如select a, count(*) from t group by a order by null

8、MySQL 有哪些自增 id？各自的场景是什么？

• 当表的自增 id 达到上限之后，再申请，值不会变化，进而导致连续插入数据的时候会报主键冲突错误
• row_id 达到上限之后，归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据
• Xid 只需要不在同一个 binlog 文件中出现重复值即可，理论上会出现重复值，但概率极小，可忽略不计
• InnoDB 的 max_trx_id 递增值在每次 MySQL 重启时会保存起来
• Xid 是由 Server 层维护的。InnoDB 内部使用 Xid，是为了能够在 InnoDB 事务和 Server 之间做关联。但是 InnoDB 自己的 trx_id 是另外维护的
• thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑，使用了 insert_unique 算法

七、锁篇

1、说一下 MySQL 的锁

MySQL 在 Server 层和存储引擎层都运用了大量的锁。

MySQL Server 层需要讲两种锁：一种是 MDL（metadata lock）元数据锁，另一种是 Table Lock 表锁。

MDL 又名元数据锁，任何描述数据库的内容就是元数据，比如表结构、库结构等都是元数据。

为什么需要 MDL 呢？

主要是为了解决事务隔离问题和数据复制问题。

InnoDB 有 5 种表级锁：IS（意向读锁）、IX（意向写锁）、S（读）、X（写）、AUTO-INC。

• 在对表进行 select、insert、delete、update 语句时不会加表级锁
• IS 和 IX 的作用是为了判断表中是否有已经被加锁的记录
• 自增主键的保障就是 AUTO-INC 锁，它是语句级别的，为表的某个列添加 AUTO_INCREMENT 属性，之后在插⼊记录时，可以不指定该列的值，系统会⾃动为它赋上单调递增的值

意向锁

• 对 InnoDB 表中某些记录加上共享锁之前，需要先在表级别加上一个意向共享锁
• 对 InnoDB 表中某些记录加上独占锁之前，需要先在表级别加上一个意向独占锁
• 简单来说，当执行插入、更新、删除操作时，需要先对表加上意向独占锁，然后再对该记录加独占锁

普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读的，是无锁的。

不过，select 也可以对记录加共享锁和独占锁的。

// 先在表上加上意向共享锁，然后再对读取的记录加共享锁
select ... lock in share mode;
// 先在表上加上意向独占锁，然后再对读取的记录加独占锁
select ... for update;

意向共享锁和意向独占锁都是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有意向锁，那么加独占表锁时，就需要遍历表里的所有记录，查看是否有记录存在独占锁，这样效率会很慢。

有了意向锁，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加独占表锁时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录了。

所以，意向锁的目的是为了能快速判断表里是否有记录被加锁。

InnoDB 有 4 种行级锁。

• Record Lock，记录锁，也就是仅仅把一条记录锁上
• Gap Lock，间隙锁，解决幻读，锁定一个范围，但是不包含记录本身
• Next Key Lock，Record Lock + Gap Lock 的组合，锁住某条记录的同时，阻止其他事务在该记录前面的间隙插入新纪录
• InsertIntention Lock，插入意向锁，如果插入到同一行间隙中的多个事务未插入到间隙内的同一位置时，则无须等待

2、什么是幻读？

指在同一个事务中，存在前后两次查询同一个范围内的数据，第二次看到了第一次没有查询到的数据。

幻读出现的场景？

事务的隔离级别是可重复读，并且是当前读。

幻读带来的问题？

• 对行锁语义的破坏
• 破坏了数据一致性

解决方法？

加间隙锁，锁住行与行之间的间隙，阻塞新插入的操作。带来的问题：降低并发度，可能导致死锁。

八、其它篇

1、为什么 MySQL 会“抖一下”？

Buffer Pool 中的脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush。在刷脏页的过程中，由于会占用资源，可能会让更新和查询语句的响应时间长一些。

2、为什么删除了表，表文件的大小还是没变？

数据项删除之后，InnoDB 的某个页 page A 会被标记为可复用。delete 命令把整个表的数据删除，结果就是所有的数据页都会被标记为可复用，但是在磁盘上的文件不会变小。

经过大量增删改的表，都是可能存在空洞的。这些空洞也占空间，所以如果能够把这些空洞去掉，就能达到收缩表空间的目的。重建表，就可以达到这样的目的。可以使用alter table A engine = InnoDB命令来重建表。

3、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 的才累加
• 但是 count (*) 是例外，它并不会把全部的字段都取出来，而是专门做了优化，不取值。count(*)判断肯定不是 null 的就按行累加
• 按照效率排序，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以建议尽量使用 count(*)

4、order by 排序的内部原理

MySQL 会为每个线程分配一个内存（sort buffer）用于排序，该内存大小为 sort_buffer_size。如果排序的数据量小于 sort_buffer_size，排序就会在内存中完成。

内部排序分为两种：

• 全字段排序：到索引树上找到满足条件的主键 id，根据主键 id 取出数据放到 sort buffer 中，然后再进行快速排序
• row_id 排序：通过控制排序的行数据的长度，让 sort buffer 中尽可能多的存放数据

如果数据量很大，内存中又无法存下这么多，这时就会使用磁盘临时文件来辅助排序，也称为外部排序：MySQL 会分为好几份单独的临时文件来存放排序后的数据，一般是在磁盘文件中进行归并，然后将这些文件合并成一个大文件。