# MySQL 实战技巧

《MySQL 实战 45 讲》学习笔记与整理总结

### 原理篇

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

Server 端：所有存储引擎共享通用部分，相当于对各种存储引擎的统一管理，对外提供统一的接入接口和查询语言。

* 连接器：管理连接池，进行权限校验。
* 查询缓存：8.0 以后被移除。只要这个表发生了一次更新，就会全部清空所有缓存的结果，有了 Bufferpool 后，这个功能很鸡肋。
* 分析器：对 SQL 查询语法进行词法分析/语法分析。
* 优化器：基于统计信息和代价模型决策索引使用，以此生成执行计划。
* 执行器：调度存储引擎，执行具体的执行计划。

存储引擎端：

* MyISAM：不支持事务/不支持行锁，性能较低，已逐步被 Innodb 所替代。
* Innodb：支持事务/行锁等，是主流的高性能存储引擎。
* Memory：内存存储，可以用于创建内存临时表。

#### 2、存储引擎中的基础日志模块

* 最初 MySQL 只有一个日志就是 binlog，记录了所有的数据变更操作，如数据的更新/插入/删除等，这种操作流水被用来做主从之间的用户操作记录同步，或者用于备份与数据的恢复。在主从复制常见下，binlog 被后端的 IO 线程同步到从服务器上，作为 relaylog 中继日志，然后再被从服务器上的 SQL 线程执行，实现主从数据的同步，但这是最外层 server 端的日志，Innodb 之所以被如此的推崇，主要是内部使用了大量的缓存操作，大幅的减少随机磁盘 IO 读写所到来的性能耗时。
* Innodb 内部大量使用的缓存设计，为了加速读性能，设计了 BufferPool，通过预读机制将数据页批量加载到内存里。当下次请求命中了 BufferPool 的数据就直接返回，而避免了磁盘随机读 IO。同时针对不在缓存中的数据写入操作都会预先写到 ChangePool 中实现写操作的异步化，减少写操作时的磁盘随机读 IO 的次数。
* 在 BufferPool 的基础上，针对数据变更操作也会将变更的数据缓存到 BufferPool 中，这就带来了脏页的问题——即磁盘文件的数据内容和内存中的不一致。此时需要一种将内存数据同步到磁盘中的机制，因此 BufferPool 中新增了 LRU 链/Flush 链等用于持续后端刷新脏页到磁盘上。
* 这种数据刷新是异步的，若服务发生了故障重启则 BufferPool 中的数据就有清空丢失的风险。为了避免数据丢失，Innodb 基于 WAL 机制和高性能的磁盘顺序写机制为服务带来 crash-safe 的能力，将数据的变更后的值都先顺序写到一个 redolog 里，用于服务重启时恢复 BufferPool 未同步到磁盘中的变更数据。
* redolog 是顺序写，本身性能很高，但为了进一步的优化性能 Innodb 引入批量组写入，即未提交的变更先不直接写到磁盘上，而只是放在一个 redobuffer 的缓存里，当事务提交时，将 redobuffer 刷到磁盘 redolog 日志文件里。
* Innodb 还支持事务的能力，那么就需要一个回滚日志去记录历史版本数据，undolog 中记录了每行数据的历史版本记录，以便做回滚操作。因此在一次数据更新中，存储引擎从磁盘中加载旧数据，然后将旧数据写到 undolog 中，之后将变更后的数据缓存在 BufferPool 并将操作记录在 redolog 和 binlog，只有两个日志都写成功了，整个更新事务才算提交成功。

#### 3、事务隔离

事务隔离，本质是个多事务并发执行冲突问题，常见的有并发问题有脏写/脏读/不可重复读/幻读：

* 脏读：事务 A 对数据行做的操作还未提交，但已经被事务 B 读取了，但稍后事务 A 因执行失败而发生了回滚，但事务 B 未感知到，所使用的数据还是未提交的数据。
* 脏写：事务 A 对数据行 X 做了更新操作，事务 B 也对数据行 X 做了更新，但事务 A 发生了回滚，直接把事务 B 的更新覆盖了，产生了脏写。
* 不可重复读：事务 A 对同一行数据的读取结果不同时间值不一致。
* 幻读：特指范围或者新数据插入的问题，即范围查询时，同样的语句由于新插入了数据，导致读取到的数据集合不一致。

解决方案：

* RU 读未提交：可以读取到其他未提交事务的变更结果。
* RC 读提交：只能读取到其他提交事务的变更。
* RR 可重复读：不管有没有其他事务的提交，统一事务中的读取的内容都是一致的。
* Searalized：串行执行，无多事务并发问题。

运行时间比较长，长时间未提交的事务就可以称为长事务。长事务容易造成的影响：

* 并发情况下，数据库连接池容易被撑爆。
* 锁定太多的数据，造成大量的阻塞和锁超时。
* 执行时间长，容易造成主从延迟。

造成长事务的原因：

* 启动不当容易造成长事务：set autocommit=0 关掉了自动提交容易导致意外的长事务长链接，建议 set autocommit=1，以通过显示语句来启动事务。
* 操作的数据比较多。
* 事务中有其他非 DB 的耗时操作。

#### 4、Innodb 索引模型

常见索引：

* 哈希索引：无法进行范围查询。
* 二叉树索引：数据量大时树层数高，需要多次磁盘 I/O。
* 有序数组索引：数据变更时需要大规模移动数据。
* B+ 树索引：N 叉树 + 有序数组。

主键索引 vs 非主键索引：主键索引的叶子结点是整行数据，非主键索引（二级索引）的叶子节点是主键 ID。这两种数据的组织方式就对查询实现有影响。二级索引需要先查到主键 ID，然后回表去主键索引再做一次查询才能取到具体的数据。

为什么主键 ID 通常自增：如果新插入的数据不是有序的而是任意值，就可能插入到任意的一个数据页中。若数据页已满就会触发页分裂，这将大大降低插入的性能。因此一般要求主键索引的 ID 有序递增的，以避免页分裂问题。

查询执行计划优化：索引能够大幅提升查询的性能，所以在查询时命中索引的查询才是好的查询。一般使用 Explain 对 select 语句的执行计划进行解析，以避免出现全表扫描的问题，而所谓查询优化也只是针对当前的查询语句尽可能的让其走索引查询，以此提升查询性能。

覆盖索引：普通查询通过会有回表二次查询操作，而有些索引已经包含了查询所需要的所有字段例如主键 ID，不需要再进行二次的回表查询才能拿到数据。这类查询方式就被称为覆盖索引，覆盖索引也是我们查询优化的目标，是个常见的性能优化手段。

联合索引：将多个字段的值拼接在一起形成的索引，这种联合索引本质上还是按 key 有序排列的索引，不过 key 是多个字段按照指定的顺序拼接到一起的字符串。联合索引除了支持等值匹配规则外还支持最左部分匹配，即最左侧的列在 key 的最左侧部分。在进行有序行判断时，也可以只对最左侧部分匹配的值查询索引，缩小扫描数据行的范围。

索引下推：对索引根据条件过滤后的数据行不直接去做回表，而是利用联合索引包含的其他字段提前筛选以减少回表的次数。

比如 `select * from tuser where name like '张%' and age=10 and ismale=1;`

基于 name 字段的最做前缀法则，命中 name\_age 的联合索引，但由于是模糊查询所以只能使用 name 这一个字段做过滤，但索引中还有 age 字段数据，过滤条件同样有 age 参数，就可以基于该联合索引进行索引下推，减少回表次数。

几个常见的索引使用原则：

* 全值等值匹配规则
* 最左侧列匹配规则：查询条件中没有包含联合索引全部的字段列，只有最左侧的部分列，也是可以命中该索引的。
* 最左前缀匹配原则：如果使用 like 语法来查，且查询值最左前缀值是确定的，模糊匹配符% 在右面也是可以命中索引的，但模糊匹配符在最左侧是无法命中索引的。
* 等值匹配和范围匹配索引：只有第一个范围查询字段才能命中索引。

综上：一般我们如果写 SQL 语句，都是用联合索引的最左侧的多个字段来进行等值匹配 + 范围搜索，或者是基于最左侧的部分字段来进行最左前缀模糊匹配，或者基于最左侧字段来进行范围搜索，才能使用上建立的联合索引。

#### 5、全局锁和表锁：给表加个字段怎么有这么多阻碍？

全局锁：给整个数据库实例进行加锁，即整个数据库都不能再执行任何更新操作，包括数据更新语句和表结构的更新语句，进入只读状态。

全局备份：如果不加锁，数据库还在动态变更中，容易出现数据不一致。加全局锁 `flush table with read lock（FTWRL）` 就成了一个保证一致性的解决方案，但这种方案风险太大。用主库的话整个业务除了读取其他操作都停摆了，用从库的话无法从主库同步最新 binlog 的操作，造成严重主从延迟。

优化方案：`mysqldump --single transaction` 在做备份时启动一个一致性视图来完成，背后机制是 Innodb 支持的 MVCC。

表级锁：

* 表锁：做数据更新操作对整个表实例加锁，锁力度太大基本不用，除了 MyISAM 这种不支持行锁的引擎需要。
* MDL 元数据锁：对表结构进行变更时需要加 MDL 写锁，如增加列/添加索引。默认情况下，数据的增删改查都会加 MDL 读锁，MDL 读锁和写锁互斥，所以当对表结构做变更加 MDL 写锁时需要注意对数据读写的影响。如对表加索引时，会触发全表扫描耗时较长，成为一个大事务。在此期间该表的读操作需要先加 MDL 读锁，但加不上就会一直阻塞直到超时。

如何给表安全的实现在线添加索引修改字段：

1. 创建 ghost 临时新表，表结构和目标表相同。
2. 新表直接执行用户提交的 alter 语句，完成表结构更新。
3. 分批次迁移原表的全量数据到新表中，同时解析 binlog 事件日志，将数据同步期间新增的数据同步到新表里。
4. 完成数据全量同步后，再通过 rename 语句替换老表。

#### 6、行锁功过：怎么减少行锁对性能的影响？

行锁：支持行数据粒度加锁，Innodb 支持但 MyISAM 不支持。对于不支持行锁的引擎来说，当对行数据做更新时需要加表级锁，业务数据更新并发度低。而 Innodb 支持行锁，业务并发度就比 MyISAM 高。这也是为什么 Innodb 能替代 MyISAM 的重要原因，因为性能更好。

两段锁协议：事务执行过程中加的行锁都必须要等到事务提交后才能释放。事务越长加锁时间也越长，其他事务的阻塞时间越长，那么加锁时就需要考虑如何尽可能缩短行的加锁时间。可以尽可能的将加锁操作放在事务的尾端，同时为了减少锁对其他事务的影响，应该将可能存在锁冲突影响锁的并发度的操作往后放。

两段锁协议下，大事务锁加的锁持有的时间长，容易导致锁冲突和死锁问题，这也是大事务的另一个弊端。

* 如何减少锁冲突：将热点数据拆分成多个片段，如一个国家的 GDP 总数拆分成多个省 GDP 之和，不同省的 GDP 只去对该省对应的行进行加锁，而不会都去竞争一个总 GDP 数据行锁。
* 如何解决死锁：设置锁超时时间/死锁检测。

#### 7、事务到底是隔离还是不隔离的？

可重复读的内核是 MVCC，而 MVCC 本质是 undolog 历史版本链 + ReadView（视图）。

MVCC 就是将 Readview + undolog 快照链相结合的方式保证事务不会读到并行执行事务更新值的机制。通过 MVCC 可以判定当前版本的数据是否对当前事务可见，不可见则沿着快照链的 roll\_pointer 回溯到上一版本，直至找到可见的版本返回。因此实现了在多事务并发下的可重复读的效果。

undolog 如何记录数据的版本链：

* 例如在缓存页中执行了一个 insert 语句，那么对应的这行 undolog 内容就是主键 + 对应的 delete 操作，以实现把这次的 insert 操作回退。
* Innodb 为每行数据引入两个隐藏字段：trx\_id + roll\_pointer。trx\_id 标记操作该行的事务 ID，roll\_pointer 则代表 undolog 的回滚记录。

undolog 日志什么时候删除：当不再需要了就会删除，什么时候不需要了呢？事务提交了，不需要这些回滚日志做回滚操作了，那么回滚日志就会被删除。

为什么建议尽量不要使用长事务：长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

Readview 机制：视图就是对当前事务对某行数据可见的版本集合，这里面有几个关键的版本号组成，首先是未提交事务的 ID 集合——记为 m\_ids。m\_ids 集合中的最小值为 m\_min\_id、最大值为 m\_max\_id，以及当前事务的 cur\_trx\_id。m\_ids 就定义了当前事务可见的版本集合。

* 小于 m\_min\_id 的，那肯定代表在创建当前事务时变更该行的事务已经提交了，因此可见。
* 大于 m\_max\_id 的，那肯定代表在创建当前事务时变更该行的事务还没创建，必然不可见。
* 在 m\_min\_id 和 m\_max\_id 之间的：
  * 先看是否是当前当前事务 ID，是的话代表是当前事务所做的变更，自然可见。
  * 若不是当前事务则判断是否在 m\_ids 集合中：
    * 在 m\_ids 集合中说明这个版本在创建前已创建但未提交，因此不可见。
    * 不在 m\_ids 中，代表已提交，可见。

可重复读和读提交各自的实现原理：

* 可重复读在事务开始时就创建了一致性读视图，且在允许过程中时读视图时不变的，所以事务只能看到在事务启动前完成提交的数据。
* 读提交是在每次语句启动前才创建读视图，所以只要在语句执行前完成提交的数据都能看到，即当前读。

#### 8、普通索引和唯一索引的区别概述？

索引是对列数据的有序组织结构， 普通索引和唯一索引在定义上来说只是列数据中是否允许重复值的存在：

* 唯一索引要求值的唯一，其包含的数据连续递增，同时在插入前需要去索引中判断值是否已存在。
* 普通索引允许重复值，在插入时直接找到位置后插入数据即可。

这一点小小的是否判重的区别对于读操作影响不大，Innodb 在读数据时会先从 BufferPool 中加载，如果没有命中再去磁盘里读取，且读取的最小单位是数据页，即使是普通索引，也是读取相同的数据页，然后放在 BufferPool 里。但是对插入操作影响较大，Innodb 中有提升写性能的 change buffer。对于普通索引，因为无需唯一性判断，插入的数据会直接写到 change buffer 中，然后写操作就算是完成并结束了。但对于唯一索引则不一样，唯一性的束缚要求先做唯一性判断，因此需要先加载索引数据页到内存里，这其中就涉及到耗时的磁盘随机读 IO 操作。

第一种情况是，**这个记录要更新的目标页在内存中**。这时，InnoDB 的处理流程如下：

* 对于唯一索引来说，找到插入的位置，判断到没有冲突，插入这个值，语句执行结束；
* 对于普通索引来说，找到插入的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

第二种情况是，**这个记录要更新的目标页不在内存中**。这时，InnoDB 的处理流程如下：

* 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
* 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

因此唯一索引和普通索引最大的区别在于唯一索引有值唯一性的检测，无法使用 change buffer 来起到加速的效果，所以写多读少场景应该优先使用普通索引。但在先写后读的场景中，这个 change buffer 反而会成为多余的，因为数据肯定会读磁盘，反而增加了 change buffer 的维护成本。

InnoDB 中对于性能的提升机制主要是设计了 BufferPool 和 change buffer 两大缓存机制，BufferPool 将磁盘读转换成了内存读，大幅提升了读操作的性能。change buffer 将随机磁盘写入转换成了内存异步写入，缓存会通过后台 IO 线程写到磁盘中。

**redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。**

### 性能篇

#### 1、MySQL 为什么有时候会选错索引？

要知道查询语句选择了那个索引可以使用 explain，它会输出一个具体的查询执行计划，包括查询是否走了索引、走的那个索引、使用到了那些查询条件、预估扫描了多少行等等信息。

优化查询执行计划就是做的这么一件事：让查询语句走该走的索引，只要走了索引性能一般不会太差，也能避免全表扫描等慢查询问题。所以新增的查询语句使用 explain 是个好习惯。

执行计划的分析流程有优化器负责，优化器中的分析逻辑是**统计信息 + 代价模型**

* 优化器的选择索引的目标是找到最优或者性能最好的索引。
* 那么问题就转换成了：影响一条查询语句的核心要素是什么？
  * 扫描行的数量：扫描行数越多，代表需要访问磁盘的次数越多，耗时自然越长
  * 是否有回表操作，回表的话其实扫描还需要 2 次查询。在同一数量级下，覆盖索引比回表查询性能更好。
  * 还需要考虑查询语句是否包含了排序、分组操作，有没有使用到了临时表等操作，来进行综合的判断。
* 扫描行的估算流程
  * 通过一个列中的数据的分布情况来估计列的区分度，用基数表示。基数越大代表区分度越强，相对应的就是某个值所存在的行数越少，基数越小区分度低，值的重复度高，那么包含一个值的行数就越多，扫描行自然越多。
  * 基数通过采样统计，即默认选择 N 个数据页，去统计值的分布，来代表整体统计信息。
* 基于统计信息的基础上还需要考虑代价模型，代价模型综合将排序、分组、临时表等因素都加入进来进行综合判断。

索引选错的情况：

* 采样出来的统计信息不准：如偏大导致估算扫描行时估多了，就可能选择错误的非最优索引。
  * 如何解决：
    * force index——强制索引
    * analyze table t——重新采样
* 加了 order by 语句会倾向评估排序的耗时代价要远比扫描行大，就倾向使用 order by 字段所在的索引，即使使用 order by 的索引要扫描的行数原比其他索引大。

#### 2、怎么给字符串字段加索引？

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

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

利用 WAL 技术，数据库将随机写转换成了顺序写，大大提升了数据库的性能。但是，由此也带来了内存脏页的问题。脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。

其中 flush 的效率是受到磁盘 IO 能力的限制的，通过 innodb\_io\_capacity 来控制。如果设置比实际 IO 小，那么就会导致刷脏页速度还不如产生脏页的速度。

* BufferPool 是缓存，不仅有查询缓存数据页，也有更新缓存变更数据页，这部分变更的数据页还没更新到磁盘文件中，与磁盘文件上的数据内容不一致，被称为脏页。但这部分数据肯定要写回到磁盘中。正常情况下是有异步 IO 线程持续写回。但有时候查询数据量过大或者写入操作过多导致 BufferPool 不够用了，那么就会触发大量 flush 操作刷盘。
* 什么情况会刷会脏页：
  * redolog 满了，需要强行将 redolog 做释放，并且将对应的 BufferPool 中的脏页 flush 回去。
  * BufferPool 满了：需要腾出新的空间来。
  * MySQL 关闭：全部刷回去。
  * MySQL 空闲：见缝插针刷一下

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

数据行的删除并不会释放已经占有的磁盘空间，而只是将这部分空间标记为可用，即可以用来插入新的数据。如果只是删除一行数据，复用度较低，只能在原数据行范围内的数据才能插入进来。但若是整个数据页的数据都被删除了，那么整个数据页就没有数据限制，可以被任意支配。也就意味着数据的存储并非是连续的，而是存在着大量存储空洞或者碎片。这种空洞，不仅会因为删除操作引入，插入数据中的页分裂也会带来这种空洞。当数据的 ID 是随机时，容易因为某一索引页放不下而触发页分裂。

如何压缩表空间的容量：

* 对表进行真正的删除，如设置每张表都是单独的文件，将 innodb\_file\_per\_table = on，那么每张表都对应一个单独的物理文件，删除表所有数据时，只需将对应的文件删除掉就能将该部分空间回收。
* 原表存在大量的空洞，数据页之间不连续，那么可以新建表然后将当前表的数据都迁移过去让数据更加紧凑。这种重建表也是一种全表备份，表备份分为 offline 和 online。所谓离线就是在备份期间不能再对数据进行更新，另外一种是在线，即允许表的更新，online 一般是通过临时表的替换来实现的。

#### 5、count(\*) 这么慢，我该怎么办？

不同存储引擎对 count 的实现是不同的，MyISAM 缓存了一个总数，直接将这个值返回（前提是没有过滤条件），而 InnoDB 是全表扫描累积计数。count 操作随着表的大小上涨，其耗时也会上涨。

InnoDB 不缓存总数的原因：InnoDB 支持事务，且 MySQL 默认隔离级别是可重复读，存在快照视图，那么不同的事务看到的数据集合就不一样。所以每次查询时都需要扫描累加。

InnoDB 针对 count 的一些优化：

* 使用最小的普通索引而非主键索引进行扫描，毕竟无论那个索引的计数结果都是一样的。
* 效率：count(字段)\<count(主键 id)\<count(1)≈count(\*)。
* 对于 count(字段)：返回的是不为 null 的行数。

如何在应用层实现更快速的 count：

* 加 Redis 缓存，每次新增或删除操作时都将修改总行数，这就带来两个问题：
  * 一致性问题，Redis 加了数但还没真正的写入到数据库中，或者写到了数据库但还没更新 Redis。
  * 不支持事务。
* 保存在 MySQL 中，通过事务来保证一致性问题。

#### 6、order by 是如何工作的？

order by 字段的排序其实是可以避免排序操作的，会优先走索引，就不需要再做排序操作了。

如果没有命中索引，即无法复用索引的有序性，而原数据字段列又是无序的，MySQL 就只能执行排序操作了，而排序是个耗时的重流程，所以这也是为什么我们有时候看到执行计划中的索引选择的与预设的不符合，预设的是扫描行数少的那个索引，但优化器却选择了 order by 中字段所命中的索引。

当通过 explain 查看执行计划时若出现了 Using filesort 就表明执行了排序操作。排序是个耗时的操作，当数据量过大时，内存无法放下全部数据，就得基于磁盘进行归并排序，先将每个磁盘子文件内部排好序，再归并在一起。

排序是在一块内存区域里进行的，叫做 sort\_buffer，即 MySQL 会把排序的数据都加载这块内存区域里，再执行排序算法，具体的数据加载有两种模式：

* 将用户需要的行字段都加载进去，这样子排序完后直接选取前 N 条数据返回，这种就是全字段排序。
* 另外一种是 rowid 加载方式，只需将待排序的字段值和数据行的唯一标识 rowid 加载到 sort\_buffer 中，这种加载方式能够减少排序内存空间的占用，但缺点就是排序后还得再根据 rowid 做回表查询。

但如果内存实在不够用了，毕竟分配的 sort\_buffer 的大小是有限的（由 sort\_buffer\_size 指定），那只好开启磁盘排序了，将放不下的部分数据放在磁盘里。

排序的具体流程：在 sort\_buffer 设置字段 => 将查询条件过滤后的行数据都写入到 sort\_buffer 中（全字段排序未必需要从主键索引中取完整数据行，若覆盖索引的查询方式，可以直接从索引中取出所有所需要的字段）=> 排序 => 截断返回

#### 7、如何正确地显示随机消息？

如何借助内存临时表来进行排序：

* explain 的 extra 输出的是 Using temporary & Using filesort 即通过临时表做排序操作。
* 临时表分为内存临时表和磁盘临时表，内存临时表用 memory 引擎来创建，磁盘临时表用 InnoDB 引擎创建。
* 加入临时表的排序逻辑和 order by 一样，只是在将数据放到 sort\_buffer 前会先创建一个临时表存放临时生成的中间数据值，例如基于随机数排序。数据都导入到临时表后，后续的排序流程就和之前一致了，如果使用的是内存临时表那么 rowid 排序会更有优势。

随机数排序用到了排序 + 临时表操作，耗时重，所以要避免。另外一个设计原则是可能将业务逻辑放在业务系统中做，而非数据库系统。

截断后的数量非常少怎么办：针对只需要前三的情况，使用只包含 3 个元素的最小堆（优先队列），因为不需要将全部队列都排好序后在得到前三名，只需要前三名。

若 SQL 语句是 limit 1000，如果使用优先队列算法的话，需要维护的堆的大小就是 1000 行，很容易超过了设置 sort\_buffer\_size 的大小，所以只能使用归并排序算法。

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

**索引字段加了函数操作后，索引的有序性被破坏，优化器会放弃走树搜索功能。**

例 1：

* 为了找到所有 7 月的记录，给时间字段索引加了个 month 函数解析出月份，这会触发全表扫描操作。

```sql
select count(*) from t where month(create_time)=7;
```

* 原因：B+ 树索引字段默认是有序的，但函数操作后的有序性无法保证，如 MD5 等这些哈希函数输出的值完全是随机的，此时无法基于索引的有序性做二叉搜索，从而失去了快速定位的能力，只能进行全表扫描。
* 优化：用范围、值转移等方式代替函数操作，
  * 将解析月份的操作改成范围过滤，如 `month(create_time)=7 => (create_time >= '2016-7-1' and create_time<'2016-8-1')...`。

例 2：

* 隐式类型转换，其主要特点是字段值的类型与字段的类型不一致。
* `select * from tradelog where jobId=110717`，但 jobId 字段类型是 varchar。MySQL 有个潜在规则是字符串和数字做比较的话，会将字符串转换成数字。意味着这句 sql 查询语句，实际执行的是 `select * from CAST(jobId as singed int) where jobId=110717`。这个转换实际就是个函数操作，那么就转换成了在过滤字段上添加了函数操作，触发了全表扫描的问题。

例 3：

* 隐式字符编码转换，MySQL 支持多种字符类型，其中 utf8mb4 是 utf8 的超集，当字段类型和值的编码类型不一致时就会发生编码类型转换，转换方向是从小集向超集，那么如果字段类型是小集值的编码类型是超集，就会触发在字段上的编码函数转换操作，导致全表扫描。
* `select * from t1 where t1.tradeid=t2.tradeid` 等价于 `select * from t1 where convert (t1.tradeid using utf8mb4) = t2.tradeid`。需要修改 t2 的 tradeid 字段为 utf8 或者将 t1 的 tradeid 字段设置成 utf8mb4。也可以在 SQL 中将值转换成 utf8。

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

本文分析影响 SQL 查询的可能因素，包括等待执行时间长、查询执行时间长。在排查时主要用到 show processlist 去查看当前 SQL 查询线程的状态。

* 等待时间长：这部分强调查询发起后到实际执行中的间隔时间，可能影响因素有等待 MDL 锁、等待行锁、等待 flush 等。
  * 等 MDL 锁：默认情况下 select 语句在执行前会先给表添加 MDL 读锁，避免在查询执行过程中表结构发生变更。表的字段或索引变更前执行器会先添加写锁，如果此时表已经加上了 MDL 写锁，那么后续的 select 查询添加 MDL 读锁时就会被锁住，需要等到释放 MDL 写锁才能继续执行查询语句。
    * 如何优化：
      * 针对加了 MDL 写锁的操作，通过 show processlist 来查看对应的执行线程，然后 kill 掉。
      * 针对字段变更或者加索引操作放在低峰期，或通过 gh-ost 工具创建。
  * 等待行锁：如 select ... lock in share mode，本质是通过加锁实现当前读的效果，那么如果待加锁的语句被其他事务加了写锁，那么需要等待其他事务释放掉。如果恰好是个大事务，那么就会出现长时间等待行锁导致查询过慢。
    * 如何处理：如果大事务确认不该存在，如大面积归档数据的删除操作，可以先将该大事务关闭掉，比如 kill 线程 ID（非 kill query ID）。kill 线程 ID 背后的默认逻辑是断开对应的操作连接，连接被断开的时候会自动回滚该事务，并且释放掉对应的锁。
* 执行时间长：
  * 深度分页：如 limit 1 offset 500000，虽然返回只有一行数据，但要扫描 500000 行后才能拿到。平时要注意避免深度分页，比如限制页码的跳转，限制数据的查询范围等。如果实现必须要支持深度分页，由于页码通常是连续点击触发的，可以尽可能记住上次分页的末尾 ID，将深度分页条件加上 ID 过滤条件。
  * 回滚读：在 RR 隔离级别下，如果一个事务执行期间一行数据做了多次变更，那么在这个大事务提交前所有变更的历史记录都会在 undolog 的版本链条中，以方便实现 MVCC 的视图读。但该事务中仅能看到最老的版本（RR 可重复读），那么针对所更改行的读都需要进行回溯版本链，找到自己可见的版本返回该值，在这种多次变更版本链条较长的场景下回溯时间会很长，导致查询时间很长。

#### 10、幻读是什么，幻读有什么问题？

* 幻读：多事务并发情况下由于并发写入行导致同一个事务中读取的数据集合内容出现的不一致情况。具体来说，同样的查询语句，但得到的结果集不一样，如上次获取行数是 50，下次总行数是 100 这类多了一些别的事务新增的行的现象。
  * 触发条件：在可重复读的隔离级别下，当前事务是看不到新增的行的，但如果 select 语句改成了 for update 当前读操作，虽然当前访问到的所有行都加了行锁，当每次读的时候都是当前读，会出现结果集新增了新的数据行，即出现幻读问题。幻读特指“新插入的行”。
  * 影响：
    * 破坏锁的语义上一致性：在语义上，for update 会在执行当前读的时候对所有访问到的行都加锁，但实际上只是对**当前**数据库中的满足条件的行都加锁，而满足这些条件的行集合会时刻发生变化，就会导致同一个事务中同一查询条件不同时刻结果不一样，那么实际加锁并未实现“对所有的符合条件的数据行都加上了锁”。
    * 破坏数据上的一致性：事务中锁都是两段锁协议，即在事务中任意位置加的锁，都只能在事务提交时才会被释放。binlog 中记录事务的先后顺序由提交时间决定。事务 A 虽然发起的时间早，但事务 A 最后晚于事务 B 提交，就导致记录在 binlog 中事务 A 的语句排在事务 B 后面，其实就是一种指令或执行语句重排的问题。binlog 中的事务先后顺序会决定数据回放或者从库数据同步中事务的执行顺序，最后基于 binlog 回放出来的数据与主库上会不一致（前提是 binlog 是 statement 格式）。
* 间隙锁：顾名思义是对行锁的补充，既然加锁所有的行都没法阻挡新插入的数据，那么就把数据值之间的缝隙也加上锁，若发现在这些间隙中有插入操作就锁住。间隙锁虽然解决了幻读问题，因为加的锁范围更大了，会导致并发度下降、增大死锁的概率，如并发插入的事务，先加锁后插入，就会因为相互需要同一个间隙锁，导致死锁问题。

#### 11、为什么我只改一行的语句，锁这么多？

加锁规则： 两个原则 + 两个优化

* 两个原则：
  * 可重复读隔离级别下加锁的基本单位是 next-key lock，即同时加上间隙锁 + 行锁，next-key lock 是个前开后闭的区间。
  * 查找过程中访问的对象才会加锁，如果扫描的是二级索引，那么锁就加到二级索引上，如果加的主键索引，那么锁就加到主键索引上。
* 两个优化：
  * 等值查询：唯一索引中，会退化为行锁。
  * 等值查询：向右遍历到第一个不满足条件的值时，next\_key lock 就会退化为间隙锁，所以整体加的锁是: `(](](](]()`

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

* 慢查询性能问题的紧急处理方案
  * 索引设计的不好：gh-ost 在线上紧急创建索引。
  * SQL 语句没写好：使用 MySQL 的语句改写规则。
  * MySQL 选错了索引：使用 force index 强制走指定的索引。

### 可靠性

#### 1、MySQL 是怎么保证数据不丢的？

MySQL 通过 BufferPool 缓存加速性能后通过 WAL 机制保证数据不丢，只要保证数据变更语句写入到 redolog 和 binlog 并且持久化到硬盘后就能实现故障恢复的能力。

* redolog 是伴随 BufferPool 而生的 WAL 顺序写性能加速技术的实现，将「数据页磁盘随机写」=>「内存 + redolog 日志顺序写 + 数据页后端异步磁盘随机写」。
* 在 WAL 机制下，事务提交不会等待所有数据的变更都持久化到磁盘上，而只是写到内存和 redolog、binlog 日志中就算是完成了。
* binlog 日志和 redolog 日志的视角有个核心的不同，即 binlog 日志和 redolog 日志都记录的是已完成的事务，但这两个日志认为的完成阶段是不一样的。
  * binlog 认为这些事务的变更数据也全都持久化到磁盘上了，包含那些只写到内存 + redolog 中的事务操作，但实际上这部分数据页还没持久化到磁盘上，如果发生了单机故障如重启，那么内存中未刷新到磁盘中的变更数据页就丢失了，但在 binlog 日志的视角又认为这些日志都已经完成了，也不会再关注这些已经提交的事务，所以基于 binlog 不会对这部分数据进行恢复。binlog 用途主要在于主从复制 + 数据恢复。
* redolog 也记录的那些已经提交的事务，但 redolog 知道这些事务所变更的数据页都只是写到了 BufferPool 上，还没有写到磁盘上。所以 redolog 记录的是具体的数据页的变化，当宕机后就基于 redolog 判断对应的变更是否刷到磁盘上了，若没有则恢复该部分数据页的内容。因为 redolog 保存的都是那些还没有刷到磁盘上的事务，所以基于 redolog 会引入一种性能问题：如果 redolog 满了，就需要 redolog 中对应的 BufferPool 脏页都刷盘为 redolog 腾出空间，但这种随机磁盘写是一种重耗时操作。

**总结来说，binlog 中记录的是一种逻辑上完成的事务，而 redolog 记录的是逻辑上完成在物理上未完成的事务。**

redolog 和 binlog 通过两阶段提交机制来保证事务提交的持久性，即先持久化到 redolog 中，再持久化到 binlog 中，最后在 redolog 上 commit 完成两阶段提交。同一个事务在 redolog 和 binlog 上使用 XID 作为关联。

binlog 写入机制：

* 事务是原子的，不管事务中包含了多少条 sql 语句，都是不能拆开的，必须一次性完整记录，所以 binlog 在记录事务时，会先将执行中的事务目前已经执行的语句先记录到 binlogcache 中，每个线程都有自己的 binlogcache，只有当事务提交时才将整个事务一股脑写到磁盘 binlog 日志文件上。
* 但是文件系统为了进行加速写入性能，本身也有个缓存，叫页缓存（os cache），需要写到磁盘上的数据都会先写到页缓存上，然后由页缓存的刷磁盘策略写到文件上。所以这里写入磁盘就分成了两个步骤：flush = write + fsync，write 就特指只是将数据写到缓存上，fsync 指将数据从页缓存持久化到磁盘上。
* sync\_binlog 的值决定了如何调用 fsync，所以 fsync 调用越少性能越高，该策略值影响写入性能。但 sync\_binlog 设置为 0 和 n 都存在宕机重启引发的事务丢失风险。

redolog 的写入机制：

* redolog buffer 是用来缓存每个事务的正在执行的语句。当事务提交后，事务既可以刷新到磁盘的 redolog 文件上也可以继续保留在 redolog buffer 中，其写入流程与 binlog 类似，先写到 buffer 上，然后 write 到 oscache 上，最后 fsync 到磁盘文件上：
* MySQL 的「双 1」配置：binlog 的 sync\_binlog = 1 & redolog 的 innodb\_flush\_log\_at\_trx\_commit = 1。代表每次 binlog 都直接 fsync 到磁盘上，redolog 也直接 fsync 到磁盘上。
* 两阶段提交：先写 redolog 处于 prepare，然后写 binlog，最后 redolog commit。

事务组提交机制：在每次 fsync redolog 时会尽可能的一起 fsync 多个事务，即按组来 fsync 事务。

* 为了每次 fsync 时多带上几个事务，MySQL 采取了拖时间的策略。正常的两阶段提交：写入 redolog => prepare => 写入 binlog => commit。但写入 redolog 的步骤实际是两步——先 write 后 fsync，写入 binlog 也是如此，那么 MySQL 就把写入 redolog 简化为 write，然后就处于 prepare 阶段，紧接着在 binlog 的 write 之后再去进行 redolog 的 fsync。此时 redolog 的 write 和 fsync 之间存在一个时间差，就有可能也有其他的并行事务也完成了 redolog 的 write，那么在 redolog 的 fsync 的时候就也能把其他事务的内容一起持久化到磁盘上，实现有限的 IOPS 却支撑更多的事务。
* 从上文中可以看出 write 和 fsync 之间的间隔会影响每次组提交时包含的事务数量，间隔越大那么每次批量 fsync 的事务越多，性能更好。

WAL 机制是如何提升 MySQL 写入性能的呢？

* 「数据磁页盘随机写」=>「数据页内存写 + 日志顺序写」。
* 组提交机制，大幅降低磁盘的 IOPS，以有限的 IOPS 支持更多的事务。

什么场景下可以设置 MySQL 日志的「非双 1」？通常都是对写入速度要求较高，同时可靠性要求低的场景：

* 从库延迟，加快同步主库的速度，尽量缩短与主库之间的差距。
* 业务高峰期，QPS 压力过大。
* 数据恢复或批量导入数据。

这种场景下就可以设置 sync\_binlog=1000 & binlog\_flush\_log\_at\_trx\_commit = 2 ，减少 fsync 的次数并提升数据的写入性能。

#### 2、MySQL 是怎么保证主备一致的？

binlog 除了对数据进行归档外还有更重要的作用 —— 主从复制，这是 MySQL 高可用架构的基础。

主从复制的流程：主从节点建立链接后，会有后端 dump 线程持续不断的将主库的 binlog 日志同步到从节点，从节点的 io 线程收到日志数据后会记录在 relaylog 日志中，再交给专门的 sql 线程对日志中的操作进行回放，实现数据的恢复。

binlog 侧重于是逻辑上的日志，有三种格式：statement、row、mixed

* statement：记录的 sql 语句原文，但同样的 sql 语句在回放后的结果并不一定可能和主库一致，比如前面的幻读引入的数据不一致问题、比如执行计划或选择索引与主库不一样，这是日志格式上影响数据不一致的主要原因，所以线上通常不推荐 statement 格式。
* row：记录的对应数据行变更后的值，推荐线上使用，因为可以准确无误的恢复数据，但有可能日志量较大，因为要记录每一行的变更。
* mixed：是为了优化 row 格式下日志量过大的问题，将 row 和 statement 的格式混在一起，当判断不会出现不一致问题时用 statement，其他情况用 row。不常用。

如何解决双 M 的 binlog 的循环复制？通过判断 serverId 的方式中断循环

* 每行 binlog 日志上都会记录执行主库的 serverId，在同步 binlog 进行数据恢复前会先判断 serverId 是否是本机的 serverId。如果相同就不会再进行处理。当 B 收到 A 的 binlog 时新生成的 binlog 中 serverId 为 A 的 serverId。

#### 3、MySQL 如何保证高可用的？

主从数据的一致性准确来说指的是最终一致性，即从库可以准确的恢复出主库的数据，但不能保证是任意时刻都和主库是同步的，存在主从延迟的问题。主从延迟在分布式架构的场景下会影响 MySQL 的高可用性，存在从库数据读的延迟、主从切换下的数据丢失等问题。

为什么有主从延迟？主从复制是有时间成本的， 这个过程包含三步：

1. 主库事务完成并写入 binlog 的时刻 T1；
2. 主库将 binlog 日志内容传输到从库上，耗时主要受到网络的影响，所以网络正常的时候这部分耗时很小，记作 T2；
3. 从库消费 relaylog 进行 sql 回放，不同数据操作的执行时间是不同的，在某些大事务场景下该步骤耗时会非常大，自然导致从库数据版本落后主库较多，该步骤的耗时也是导致主从延迟主要原因，记作 T3；

主从延迟的时间差 用 second\_behind\_master = T3-T1 = （T3-T2）+（T2-T1）表示，其中（T3-T2）就是从库消费 relaylog 的速度， 当小于主库时，一般就会出现了主从延迟问题。

为什么从库回放事务速度会小于主库？影响的因素：&#x20;

* 从库机器配置较差不如主库，当从库读 QPS 较高资源紧张情况下，会对 relaylog 的回放数据写入有影响。
  * 解决方案：做对称部署，主从机器规格相同。
* 从库压力过大：即使主从机器配置相同，但基于读写分离的策略，从库往往承担较大的读请求处理，包括离线分析任务、全量备份，这些导致从库负载过高消耗了大量的 CPU 资源，影响同步速度导致主从延迟。
  * 解决方案：
    * 一主多从部署架构：用多个从库分摊读请求的压力。
    * 通过 binlog 接入外界系统如 ES、Hadoop 等，将复杂查询或者离线统计分析功能从 MySQL 从库剥离出来，减少从库的负载。
* 大事务的影响：比如一个事务执行了 10 分钟，那么从库回放也需要 10 分钟，那么主从数据版本就会延迟 10 分钟。
* 解决方案：分类处理，如 DML 和 DDL 不同类型操作的大事务其处理方式也不同
  * DML 大事务拆成小事务：如将一次性 delete 大量数据拆成多次 detele 少量数据，分批多次提交。
  * DDL 大事务：如大表创建索引等，这类问题的解决方案就是低峰期 + gh-ost online DDL 操作。

主从延迟会对主从切换策略有明显的影响， 针对主从延迟问题有两大处理方向：

* 可靠性优先：优先保主从数据的一致性，即主从延迟不能太大，在一定合理范围内才能发生切换，在切换期间，主库存在短暂不可用的情况，因为需要等从库同步完主库的数据，把数据版本和主库保持一致。
* 可用性优先：就是不管主从是否有延迟，不管是否主从完全同步，先切到从库再说，优先保证线上系统的可用性，这种策略可能会导致切换后数据丢失的问题。
* 怎么发现数据不一致的问题：设置 binlog 为 row，可以很快因为 duplicate key error 等系统报错来快速发现，而其他格式如 statement 可能发生了不一致的问题但不会有任何报错发生。

正常情况下可靠性大于可用性，但若主库都崩了那需要考虑可用性优先了，事后再恢复收据。但不管怎样都会给业务带来有损影响， 所以从这个角度来说，主从延迟的大小对可用性有着主要影响，延迟越大系统数据恢复时间约长，可用性相对来说越低。

#### 4、备库为什么会延迟好几个小时？

影响主从延迟的各类因素中存在偶发的如读高峰期压力过大、大事务、网络抖动这种因素，但这种延迟问题从库之后会很快追上来。还有类是持续性的延迟，即从库消费速度持续落后与主库，如配置不行、或者从库并发能力低，这种持续落后与主库就会导致与主库的延迟越来越高。配置不行可以提高配置，从库并发度低就考虑提升从库的并发复制能力，即从库的单线程的 sql\_thread 优化为多线程并行消费 relaylog，从而提升消费速度。

如何提升从库的并发复制能力？

1、主库是如何提升并发度的？

* 行锁，并发度较高

2、从库的并行复制需要考虑哪些问题？

* 并行执行的事务之间互不影响相互独立，避免同时修改同一行。但由于 binlog 日志中记录的事务与实际的执行发起时间不一致（事务需要在执行完后才会记录到 binlog 上，有可能操作同一行的事务先执行，但回放时变成了后执行，执行顺序与主库相反，这种发生指令重排后从库若再将这些事务并行执行，就可能发生从库回放出来的数据与主库不一致的问题。注：特指无间隙锁的幻读问题）
* 同一个事务多个更新只能由一个 sql\_thread 执行不能拆分，即从库并行的最小粒度也是事务。

3、并行复制策略：

* 按表分发：每个 sql\_thread 线程都监听一个绑定了指定表的事务队列，当 coordinator 发现事务 A 更新的表只涉及到表 A，就将其分配表 A 所绑定的事务队列上交由对应 sql\_thread 执行。若出现冲突，如一个事务需要操作多个表，而这些表中还存在未执行完成的队列那么就需要等待，直到这些表只剩下一个 worker 再操作。按表分发策略适合多表均衡的场景，但如果热点表下，其并行复制能力会再回退到单线程模型。
* MariaDB 的基于 redolog 组提交的并行复制策略：模拟主库的并行执行模式
  * 因为事务中的两段锁协议，即锁一旦加上后，只有在事务提交时才会释放，所以如果在主库可以并行执行，从库也能并行执行。
  * 在 redolog 的组中一起提交的事务，存在不会修改同一行的特性。（行锁）
  * 具体实现：
    * 针对 redolog 中同一组提交的事务都有相同的 commitid，记在 binlog 中。
    * 从库每次从 binlog 取出同一 commitid 的多个事务并分发到不同的 sql\_thread 上，并行执行。
    * 同一事务组执行完成后再去取下一事务组。
  * 缺点：
    * 一个事务组提交后才能取下一个事务组，容易被同一事务组内的大事务影响。
* 5.7 后的并行策略：对 MariaDB 的并行复制策略的优化
  * MariaDB 策略的核心是认为同一组的事务会同时处在 commit 阶段，因为这些同时 commit 的事务之间没有锁冲突，自然也不会操作同一行，满足行并行的要求。但其实同时处在 prepare 阶段的事务也同样满足该要求，因为这些表示事务中的多个更新语句都通过了锁冲突的校验，不存在行锁冲突问题，可以并行执行。那么就可以将同时 处在 prepare 的事务也进行并行，提升并发度。
  * 前面的 binlog\_group\_commit\_sync\_delay 控制着可以延迟多久才触发 fsync，增加同时 commit 的事务数量，减少写盘次数。同样类似的，该值也会「制造」同时处在 prepare 的事务数量，增加从库的并发度。

#### 5、主库出问题了，从库怎么办？

一主多从有个典型问题 —— 如何进行一主多从的主备切换流程？

* 传统基于点位的主备切换：
  * 点位简单来说就是定位事务位置的坐标，由「所属的日志文件」和「日志文件中的位置」组成，通过匹配点位来确定从库需要同步的事务，当出现主库 A 故障后，会评选出新的主库 A’，然后新主库 A’ 会以故障时间作为点位开始位置，继续同步 binlog。点位同步的问题主要在于点位难以定位，可能同步给从库的事务已经在从库中，而且从库无法识别继续执行该事务，导致出现重复 key 等异常，这类错误在发生主备切换后可以设置忽略但复杂且易出错。
* 基于 GTID 的主备切换：
  * GTID 本质也是定位事务的一个点位
    * GTID：GTID 已经完成提交事务的唯一 ID，因为事务已经提交所以与事务 ID 不同，GTID 是连续的，由 {serverID:transactionId} 组成，每个 MySQL 的节点上都维护着已经提交的事务 GTID 集合。
    * 对于 GTID 可以有两种赋值方式：1、automatic 2、指定值。如果指定的值在 MySQL 节点的 GTID 集合中已经存在则忽略跳过该事务，不再执行。
    * 通过 GTID 就清晰的记录了那些事务已经提交了，无需再执行。
  * GTID 在主从切换中的应用：
    * 当发生主从切换后，新主库 A’ 会和从库的 GTID 集合做差值，找到那些主库上有的但从库还未同步的事务，然后将这些事务中最早的那个 GTID 作为开始点位，逐一同步给从库，如果从库发现 GTID 本机已经存在，就直接跳过不会报错。

#### 6、读写分离有哪些坑？

一主多从优势：

* 更高的可用性和可靠性。
* 读写分离，通常业务场景都是读多写少，将读请求分摊到多个从库上，能够有效分摊主库的压力。

读写分离的实现方案：

* 客户端直连方案：少一层中间层，客户端直连从库，进行读写，查询性能好些，
* 添加一层 proxy：中间专门加一层 proxy 层，进行多个从节点的负载均衡，客户端无需感知复杂的主从架构，维护更简单。

主从延迟过期读问题：

* force master 强制走主库：将读请求强制转发到主库上，这种方案其实就失去了读写分离的均衡优势。
* sleep 延迟等待方案：针对先写后读的情况，在读前停顿一下，如停顿 1s，减少读到过期读的概率。
* 判断主从无延迟的方案：主从无延迟的时候再去读数据。若 second\_behind\_master=0 则认为主从无延迟，但误差为秒级。也可以使用主从的 GTID 集合的差集或基于主从的 Master\_log\_file & Read\_Master\_Log\_Pos 值的差来判断，精确度更高。
* semi-sync 方案：当主库的 binlog 事务提交时必须将日志同步到从节点才算整个过程完成，这里同步只是将 binlog 日志从主库网络传输到从库上，但无需等到从库回放完成，从库收到日志后，会给主库一个 ack 消息，而主库只有在收到从库的 ack 后才会给客户端返回「事务完成」的确认。semi-sync 方案还能有效提升主从架构的数据可靠性，因为与普通的异步复制不同，semi-sync 需要将日志同步到从库上才算完成，即从库有着完整的 binlog 日志记录，可以慢慢进行回放。而普通异步复制，可能存在事务还未及时同步给主库，而主库发生故障了，那这部分数据实际上就丢失了。
* 等主库的点位同步 & GTID 点位方案：在查询时我们不需要等待所有的事务都完整同步到位，只需要部分我们需要的指定的事务同步到从库就行了。对于点位同步方案就是等到指定的点位，对于 GTID 就是等到指定的 GTID，在查询的时候把 GTID 加到请求参数中，若从库一直未收到指定的 GTID 事务，有两种处理方案：超时重试或者转发主库。

#### 7、如何判断一个数据库是不是出问题了？

**外部检测方法：这些方法只适合小规模或者个人 demo，线上肯定不会等到 MySQL 都无法处理请求才执行主备切换或其他干预**

* select 1 判断只能说明 MySQL 这个进程还在，但主库未必没有问题。例如使用 innodb\_thread\_concurrency 控制线程并发数，当达到上限后，接收到新请求就会进入等待状态。但其满了仍然是可以执行 select 1 的，但此时执行新的表查询是会被 block 的。
* 并发查询 vs 并发连接：
  * 并发连接只是有多个客户端，数量可以有很多，比如几千个，但这些链接未必全部都在执行，有可能已经进入阻塞状态，此时就不会再占用线程了，即不会再占用 inno\_thread\_concurrency 的线程资源了。
  * 并发查询是指活跃的 SQL 线程数，其值是对 CPU 资源能力的限制。当达到上限时，新到的请求或者处理就只能等待了。
* 查表判断：为数据库的健康检测专门新建一个表，用来做表的健康检测。这种查询可以保证查询没问题但无法侦测出更新问题，如磁盘满了或者 binlog 满了可以查询但无法更新。
* 更新判断：找个无关紧要的字段，如时间戳执行更新操作，如果能执行代表更新没问题。

**内部监控方法：这才是靠谱的方法**

* 对数据库的各种系统指标：磁盘 + CPU + 内存 + IO 都进行监控上报，有问题 P0 报警及时处理。

#### 8、误删数据怎么办？

怎么删数据：

* delete 语句删除数据行，只是释放对应的数据页可被复用，但无法减少空间。delete 全表是很慢的，要写入 undo log、binlog 等，不如直接 drop table 或者 truncate table。
* drop table、truncate table 语句删除数据表。
  * 无论是删库还是删表，恢复方案都是全量备份 + binlog 日志回放。在回放时通过设置 GTID 来跳过误删的事务语句，避免重蹈覆辙。
  * 正常基于 mysqlbinlog 应用来对 binlog 做数据恢复是单线程的，如何加速呢？
    * mysqlbinlog 可以指定 database，限定只恢复指定的数据库，缩小数据范围。
    * 将待恢复的数据库临时实例伪装成一个从库，然后通过并行复制来加速恢复的过程。
    * 搭建延迟复制备库，正常来说主从延迟是问题，但巧妙利用的话也可以起到意向不到的效果，如作为一种特殊的备库，故意设置比主库延迟半天，如果主库出现问题，就可以基于延迟备库快速恢复完整的数据。
* drop database 删除整个数据库。
* rm 删除整个数据库实例。

最佳的方式：做好权限卡控，没有删表的机会。

#### 9、为什么还有 kill 不掉的语句？

两种 kill 方式：

* kill query + thread Id：终止正在执行的语句。
* kill connection + thread Id：connection 可省略，断开这个线程的连接。

kill 发出后并不会达到线程马上停止的效果，而只是唤醒线程告诉它不用再继续干活了，需要终止线程了。开始「执行停止的逻辑」，接收到通知的线程就开始收尾工作，如发现自己持有 MDL 锁或者行锁就先把锁给释放了，如果发现自己事务还未提交，那就回滚下。

专业点来说：线程不是说停就停的，

* MySQL 对 kill 指定的线程会先设置其状态为 killed。
* 再给事务 session 发送终止信号，注意只是个信号。
* session 被唤醒后，继续执行，直到埋点处。
* 线程在执行过程中有很多「埋点」，到了这些埋点后才会对线程的状态进行判断，是否已经被 kill 了，如果发现线程状态被 kill，就开始启动终止退出逻辑。
* 这也就是为什么，kill 的第一步是先唤醒线程，因为不唤醒线程，就无法继续执行到埋点处。
* 语句进入线程终止逻辑，直至终止逻辑完成，整个过程完成后才会真正的退出。

如果 kill 耗时较长，可能原因有

* 终止逻辑较长，如大事务被终止，那么该事务所做的所有更新都需要回滚。
* 服务负载压力过大，导致线程长时间未执行到埋点处。

发送 kill 命令的客户端，并没有强行停止目标线程的执行，而只是设置了个状态，并唤醒对应的线程。而被 kill 的线程，需要执行到判断状态的「埋点」才会开始进入终止逻辑阶段。并且终止逻辑本身也是需要耗费时间的。

#### 10、我查这么多数据，会不会把数据库内存打爆？

如果一张大表数据很多，把这张表全都加载到内存里，内存放不下，那么对该表进行全表扫描，会不会出现内存溢出的问题？

* 不会，因为 MySQL 是「边读边发」，就是服务端不会一口气读取出来全都发给客户端，实际上服务端采取的是分批发送策略，每次都不需要保存完整的结果集，每次只是查询一部分数据，放在 net\_buffer 缓存里，当缓存满了就通过网络接口发出去。紧接着继续加载下一部分数据，如此循环直至完成全表扫描。net\_buffer 是由 net\_buffer\_length 来指定的，一般 16k。
* 这种边读边发也存在问题，即如果数据接收方消费较慢，就会导致 MySQL 服务端缓存中的数据发不出去服务端执行时间较长，此时服务端的线程很多都处于 "Sending to client" 的阻塞。出现这种情况时需要查看为什么数据发不出去，是客户端太慢还是服务端的 net\_buffer 设置的太小。

全表扫描对 BufferPool 内存的影响，正常来说查询请求会先去 BufferPool 看是否有需要的数据，若有直接返回，若没有则从磁盘加载，然后缓存到 BufferPool 中，缓存命中率越高性能越好。那么全表扫描时会不会出现表中的数据都把 BufferPool 都占了的情况，而这些全表扫描的数据又是非热点数据，在内存中白白浪费内存空间？

* 不会，InnoDB 采取的是冷热分离思想，将数据链按照 7:3 的比例划分为热数据区和冷数据区，只有冷数据区的数据满足了一定的访问要求如时隔 1s 后还会被访问，才开始升级到热数据区，冷数据区主要是存放那些新加载到内存中的数据，如全表扫描的数据最先加载到内存里，然后由于之后不会再被访问，在冷数据就被访问了，而热数据区的不受影响。
* 冷热数据分离很好的解决了全表扫描的热点数据被淘汰的问题，保证在全表扫描的情况下，热点数据不受影响，仍然保持很高的命中率。

### 应用篇

#### 1、到底可不可以使用 join？

join 的实现有三种方式：Index Nested - Loop Join、Simple Nested Loop Join、Block Nested Loop Join。

无论哪种方式 join 双方都是驱动表 T1 和被驱动表 T2，其本质实现都是 for 循环遍历驱动表 T1，拿驱动表的每一行数据去被驱动表 T2 上进行匹配，从 T2 中找到满足条件的行和 T1 的行组成输出的结果行，作为结果集的一部分。

**通常来说，驱动表是小表，被驱动表是大表，原因是驱动表对扫描行数或者成本的影响更大。**

* 针对驱动表的每一行数据都去被驱动表中进行匹配，在匹配过程中恰好命中了被驱动表的索引，就成为 Index Nested-Loop Join 走树搜索。
* 如果没有命中被驱动表的索引，继续用上面的算法，就是 Simple Nest Loop Join。
* 使用 join\_buffer 在内存中进行查找，就是 Block Nested Loop Join。

影响 join 的性能有那些因素：被驱动表没有索引或 join\_buffer 太小。join\_buffer 越大，每次 T1 和 T2 加载到内存中的数据越多。如果 T1 没办法一次性加载完就只能分段加载，对 T2 的扫描次数增加 & 分的块越多，全表扫描 T2 的次数越多，性能越慢。

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

#### 2、为什么临时表可以重名？

内存表 vs 临时表

* 内存表：整个表都在内存中，没有持久化到磁盘上的表，如 Memory 引擎创建的表就是内存表。
* 临时表：临时创建的表，数据既可以放在内存中也可以持久化到磁盘上，但临时表只能由创建它的 session 访问，session 结束临时表也就被删掉了，临时表会自动被回收。
  * 临时表在不同线程间不存在重名的问题，即使名称与普通表相同，但其真实的表名称中会包含线程 ID 等信息，线程间相互隔离，所以不需要考虑线程间并发的问题。
  * 临时表是自动回收的，当 session 结束的时候，临时表就被回收了，省去了人工需要的收尾和异常处理的工作。
  * 临时表也有普通表的特性，如创建索引、添加唯一键约束，定义字段的类型。
* 从以上三点特性来说，临时表特别和 Java 中的 ThreadLocal 在应用上有相似的特点：临时存在、线程内可见、线程间相互独立。

**临时表的数据只由 session 可见且自动销毁，而且是一个单独存在的表，适合用于临时存放的数据如 sort\_buffer/join\_buffer、分库分表的结果聚合等批量表数据的处理，因此临时表通常会作为复查查询的常用优化手段。**

比如，在分库分表的查询场景下，引擎需要先从各个子分区中过滤出局部数据集，然后再统一收集到一个节点上做汇总操作，如排序、分组等，这些批量临时数据行的管理就适合使用临时表，从而避免客户端手动的聚合。如果数据可以全部放到内存中那么就使用内存临时表，如果内存放不下了，那就放在磁盘里，使用 InnoDB 作为临时磁盘表的引擎。

临时表既可以系统自动创建，如在复杂查询场景下，也可以用户手动创建。

临时表是如何管理的？

* 有专门的临时表空间来存放临时表，
* 每个 session 中会维护自己的临时表列表，在 session 结束退出时，就会将列表中的每个临时表都执行销毁和回收工作。

临时表的执行被记录到日志里吗？

* 取决于 binlog 的日志格式，若是 row 则不需要记录，因为 row 下 binlog 已经存放了所有的临时表处理之后的结果逻辑，无需中间过程的回放和记录；但 statement 和 mixed 则需要记录，因此时 binlog 是记录的执行逻辑信息，即需要逐步回放执行流程，所以记录中间临时表相关的语句。
* binlog 中记录了临时表，那么在主备复制的架构下，该临时表就会被传给从库进行执行，为了避免从库单线程模型下的同名表问题，主库在写入到 binlog 中会将用户指定的名称变更为实际加入 serverId 的 table\_def\_key 这种唯一标识，这样从库即使单个线程，也不会出现重名冲突的问题。

另外需要注意的是：临时表和 threadLocal 具有同样的问题，即如果是在连接池或者线程池这种场景下，因为线程需要复用，即使执行完成后也不会退出，就不会触发临时表的回收工作，有可能这些临时表会一直存放在内存里，所以需要及时的**手动删除用户临时表**。

#### 3、什么时候会使用内部临时表？

临时表可以用于 Innodb 对复杂查询的优化，即在复杂查询中，引擎会自动创建内部临时表（系统创建的临时表叫内部临时表）。

两个典型场景：

* 排序时候的 sort\_buffer 和 join、union 场景的 join\_buffer。
* 子查询场景。
* union 和 join 的区别：union 是行加起来，取行的并集，有去重。union all 类似 union，但没去重。join 是列加起来，做列属性填充。

groupby 为什么需要临时表，因为数据列是无序的，所以 MySQL 也不知道同样的值有多少行，所以只能先全部取出来，然后再统计，但能不能知道有多少行呢？可以的，如果将同样行的数据都放在一起不就行了。索引是有序的，不仅值从小到大排列，而且相同的值都聚在一起，直接遍历索引就一样可以进行聚合操作，逐个从前向后遍历，遇到新值后就先缓存累加，直到下一个新值，并且该值的聚合结果可以直接发送给客户端，无需临时表也无需额外的排序。

**这就是为什么在 groupby 的时候一定要走索引的原因，无需临时表、无需排序，性能自然高。**

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

Innodb 与 Memory 的区别：

* 索引类型不同：
  * Innodb 是把数据和主键索引放在一起的，是索引组织型的表。Memory 引擎是把索引和数据分开放，是堆组织类型的表。
  * 当数据位置发生改变时，Innodb 只需要修改主键索引，而 Memory 需要修改所有的索引。
  * Innodb 主键索引只需要一次查询，而 Memory 所有索引都需要两次查询。
  * Innodb 的索引是 B+ 树索引，支持范围查询。Memory 是 Hash 索引不支持范围查询。
* 锁类型不同，锁的力度不同：
  * Memory 不支持行锁，不支持变长的字符串，即使定义了 varchar 也是固定长度的 char。
  * Innodb 是磁盘表，Memory 是内存表。虽然 Memory 的读写操作全是内存操作，但是其性能未必比 Innodb 好，因为 Memory 只支持表锁，并发度太低。
  * Innodb 有 BufferPool + WAL 技术，可以基本做到和内存操作性能差不多。
* 数据持久化问题
  * Innodb 支持持久化，Memory 异常重启后数据就清空了。

Memory 内存引擎适合作为临时表：Hash 索引读更快 + 内存操作不需要读写磁盘。


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://gists.lanlance.cn/cssys/mysql-45.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.