MySQL45讲

1. 一条SQL查询语句怎么运行的

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

2. 一条SQL更新语句怎么运行

MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

redo log（粉板）

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（里面，并更新内存，这个时候更新就算完成了。在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。

大小固定，循环写
crash-safe

binlog

redo log 是innoDB引擎特有的，server 层的叫 binlog(归档日志)
redolog 是物理日志，记录“在某个数据页上做了什么修改”；binlog 是逻辑日志，是语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”
redolog循环写，binlog追加

对于语句 update T set c=c+1 where ID=2;

执行器先找引擎取 ID=2 这一行。ID 是主键，直接用树搜索找到。如果 ID=2 这一行所在数据页就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，再返回。
执行器拿到引擎给的行数据，把这个值加上 1，N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成

对于redo log 是有两阶段的：commit 和 prepare

如果不使用“两阶段提交”，数据库的状态就有可能和用它的日志恢复出来的库的状态不一致.

先r后b:binlog丢失，少了一次更新，恢复后仍是0。
先b后r:多了一次事务，恢复后是1.

undolog

Undo log的存在保证了事务的原子性，MVCC就是依赖它来实现，当对任何行做了修改的时候都会在undo log里面记录，大量的undo log构成行的历史版本记录，在需要的时候可以回退(rollback)到任何版本；

3.事务隔离

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

SQL标准隔离级别：

读未提交: 一个事务还没提交时，它做的变更就能被别的事务看到。
读提交: 一个事务提交之后，它做的变更才会被其他事务看到。
可重复读: 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化: 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

避免使用长事务，set autocommit=1, 通过显式语句的方式来启动事务。

information_schema 库的 innodb_trx 中可以查询长事务。

4-5. 索引

基于B+树。

主键索引的叶子节点存的是整行数据。 InnoDB 里，也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引。
非主键索引查询会回表。
自增id可以避免维护B+树时的分裂、合并问题。

索引维护。

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
更糟情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

覆盖索引

即：where 非主键查询，但只查询ID,ID在非主键索引树上了，不需要回表。

联合索引

最左前缀

索引下推

对于where 条件，如果索引中包含了该字段信息，会直接进行过滤，不会再回表比对。

6. 全局锁和表锁

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

全局锁的典型使用场景是，做全库逻辑备份

Flush tables with read lock (FTWRL)：其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
相较于readOnly,本命令在客户端异常后会自动释放锁。

表级锁（表锁和数据锁）
表锁的语法是 lock tables … read/write
另一类表级的锁是 MDL（metadata lock)。在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，自动加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
- 读写锁、写锁之间互斥

MDL会导致该表结构时阻塞，online DDL可以看下。

7.行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。MyISAM 不支持行锁。不支持行锁意味着并发控制只能使用表锁，同张表上只能有一个更新在执行，这就会影响到业务并发度。

两阶段锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁

这样就互相等待了。（1互斥、占有且等待、不可剥夺、循环等待）

死锁后：

等待，设置超时时间
死锁检测，主动回滚某个事务（推荐且默认）。
- 但并发过多时，死锁检测耗费CPU过多。
- 保证不出现，关闭检测。
- 控制并发度

8. 事务到底是不是隔离的

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。
在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。
数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。

        三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，每次需要时根据当前版本和 undo log 计算的。如需要 V2时，就通过 V4 依次执行 U3、U2 算出来。

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

    绿色可见，红色不可见。黄色中，如果在数组中，是未提交的事务生成的，不可见。否则可见。

    **InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。**

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）
对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
对于读提交，查询只承认在语句启动前就已经提交完成的数据；

9.普通索引和唯一索引

查询

对于普通索引查第一个记录后还要查下一个，直到不满足。唯一索引直接定位。但差距很小，innoDb按数据页读写，16KB在内存。

更新

更新一个数据页时，如果数据页在内存中就直接更新。不在，将更新操作缓存在 change buffer 中，不需从磁盘中读入了。在下次查询要访问这个数据页时，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。
- change buffer 在内存中有拷贝，也会被写入到磁盘上。
- 将 change buffer 中的操作应用到原数据页，得到最新结果的过程为 merge。除访问数据页会触发 merge ，后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会 merge 。
唯一索引需要判断唯一性约束，必须读入数据页，也就直接写了，不需要change buffer。
普通索引就可以先写入change buffer,避免io开销。

那么对于读少写多，change buffer就有用。反过来还是要多次io,效益降低。

10. mysql为什么会选错索引

索引信息统计不准确的，可以使用 analyze table x重新分析。
优化器误判的，可以 force index强制指定。
- 或者修改语句引导优化器，增加/删除索引绕过。

11. 怎么给字符串字段加索引

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
使用前缀索引可能就用不上覆盖索引对查询性能的优化了。
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

12.为什么我的Mysql会抖一下

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
- 平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

12.1 什么情况会引发数据库的 flush 过程呢？掌柜在什么情况下会把粉板上的赊账记录改到账本上？

粉板满了记不下。 InnoDB 的 redo log 写满。会停止所有更新，checkpoint往前推，redo log 留出空间继续写。

生意太好，要记的太多，快记不住了，赶紧找账本把这笔账先加进去。系统内存不足
生意不忙了空闲时。其实即使是“生意好”时，也要见缝插针地有机会就刷一点“脏页”。
年底关门清账。Mysql正常关闭。

这四种情况，三、四不需考虑，本来就是要空闲或关门的。

第一种InnoDb要尽量避免。出现这种情况时，整个系统就不能再接受更新了，所有更新都必须堵住。从监控上看，这时候更新数会跌为 0。
第二种则是常态，InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
- 第一种是，还没有使用的；
- 第二种是，使用了并且是干净页；
- 第三种是，使用了并且是脏页。

因读数据要读到内存页，干净页直接用，脏页就要先刷入磁盘，干净后用。那么以下两种情况就会明显影响性能。

个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

要避免的话，首先要合理地设置 innodb_io_capacity 的值，还要多关注脏页比例，不要让它经常接近 75%。

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

参数 innodb_file_per_table

表数据既可存在共享表空间里，也可是单独的文件。

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

从 MySQL 5.6.6 开始，默认值就是 ON 。设置为 ON，是推荐做法。

删除数据

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

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

这样就会造成空洞。

数据空洞

不止是删除数据会造成空洞，插入数据也会。

PageA满了，新增就会开辟PageB。

重建表

去除上述情况造成的空洞，可以使用alter table A engine=InnoDB来重建，但不是OnLine的，执行阶段不能更新。

MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。

对于过程中的更新，会将操作记录在一个日志文件（row log）中，临时文件生成后会重放。

alter 语句在启动的时候需要获取 MDL 写锁。但是这个写锁在真正拷贝数据之前就退化成读锁了。

因为要实现 Online，MDL 读锁不会阻塞增删改操作。不干脆直接解锁是为了保护自己，禁止其他线程对这个表同时做 DDL。

Online与inplace与copy table Online DDL

Innodb早期支持通过copy table跟inplace的方式来执行DDL语句.

inplace在copy table的基础上不需copy整个表格，只需在原来ibd文件上，新建所需要的索引页.节约极大IO资源占用. 且速度提高，减少了该表不提供写服务时长。但inplace仅支持索引的创建和删除，不支持其他的DDL操作。

DDL 过程如果是 Online 的，就一定是 inplace 的；

反过来未必，也就是说 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。

14. count(*)这么慢，我该怎么办

在不同的 MySQL 引擎中，count(*) 有不同的实现方式。

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

InnoDB 是索引组织表，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

对于 count(主键 id) 来说，InnoDB 引擎会遍历整表，把每一行 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

对于 count(字段) 来说：

如果这个“字段”是定义 not null ，一行行从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许 null，执行时，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。

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

**按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count() 。

16. “order by”是怎么工作的

sort_buffer_size

就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

全字段排序

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

rowid 排序

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

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

覆盖索引

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

17.如何正确的显示随机消息

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的；
tmp_table_size 这个配置限制了内存临时表的大小。不够就使用磁盘临时表。
不论如何，该语句都会扫描大量行数，且排序过程浪费大量资源。

随机排序算法

取得这个表的主键 id 的最大值 M 和最小值 N;
用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
取不小于 X 的第一个 ID 的行。

这样id有空洞的话，不同行概率不同。

取得整个表的行数，并记为 C。
取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
再用 limit Y,1 取得一行。

解决了数据空洞，但limit 开销较大。（也比order by rand()好）

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

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。而只能使用全索引扫描.
- 对于传入的值在B+树中无法检索。
隐式类型转换.类型转换不会使用索引。
隐式字符编码转换

以上实际都是在对索引字段做函数操作

19 .为什么我只查了一行，也这么慢

等MDL锁

show processlist命令查看时：Waiting for table metadata lock

某语句拿MDL写锁阻塞MDL读锁。sys.schema_table_lock_waits可查看造成阻塞的process id。

等flush

show processlist命令查看时：Waiting for table flus

flush很快，出现该状态可能是：有个 flush tables 命令被别的语句堵住，然后它又堵住 select 语句。

等行锁

某事物持有写锁未提交。sys.innodb_lock_waits可查询谁在占用写锁。

查询慢

当事物A开始事务，事务B开始执行大量更新。select是当前读，就需要依次执行undo log。找到事务B开始前的值。

20. 幻读是什么，幻读有什么问题

幻读：是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没看到的行。

可重复读隔离级别下，普通查询是快照读，不会看到别的事务插入的数据的。幻读在“当前读”下才会出现。
幻读仅专指“新插入的行”。修改原有数据导致的查询多了一条不算幻读。

20.1 幻读的问题

语义上：事务A的select for update的“我要把xxx的行锁住，不允许读写”。就被破坏了。

数据一致性：

执行完成后数据是：(0,5,5)、(1,5,5)、(5,5,100)。数据没问题。

但binlog有问题：

如果拿这个binlog去备库、克隆。得到的是 (0,5,100)、(1,5,100) 和 (5,5,100)。

即使把所有的记录都加上锁，还是阻止不了新插入的记录。新的未被锁。

20.2 如何解决幻读

产生幻读的原因是行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。所以加入间隙锁。

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。

20.3 间隙锁带来的问题

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

21. 为什么我只改一行的语句，锁这么多

加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

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

短连接风暴

使用短连接在业务高峰时期，可能出现连接数暴涨。

第一种方法：先处理掉那些占着连接但是不工作的线程。

第二种方法：减少连接过程的消耗。

慢查询性能问题

慢查询的第一种可能是，索引没有设计好。

慢查询的第二种可能是，语句没写好。

query_rewrite 存储过程临时转换语句

慢查询的第三种可能是，MySQL 选错了索引。

force index

QPS 突增问题

白名单去掉
限制语句
删除用户

23.Mysql如何保证数据不丢

binlog 的写入机制

事务执行中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

每个线程有自己 binlog cache，但是共用同一份 binlog 文件。

write(写入 page cache) 和 fsync 的时机，是由参数 sync_binlog 控制的：N次write后才会fsync

redo log 的写入机制

存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；
写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；
持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。

为控制 redo log 的写入策略，提供了 innodb_flush_log_at_trx_commit 参数，有三种可能取值：

0 时，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
1 时，表示每次事务提交时都将 redo log 直接持久化到磁盘；
2 时，表示每次事务提交时都只是把 redo log 写到 page cache。

除了后台线程每秒一次轮询操作，还有两种场景会让没有提交的事务的 redo log 写入到磁盘中。

redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。

IO性能瓶颈

设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。

24. MySQL是怎么保证主备一致的

基于binlog

在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
sql_thread 读取中转日志，解析出日志里的命令，并执行。

25. Mysql 如何保证高可用

MySQL 高可用系统的基础，就是主备切换逻辑。

主备延迟的来源

备库所在机器的性能要比主库所在的机器性能差
备库的压力大
即大事务

切换策略。

可靠性优先
可用性优先

实际的应用中，更建议使用可靠性优先的策略。毕竟保证数据准确，应该是数据库服务的底线。在这个基础上，通过减少主备延迟，提升系统的可用性。

26. 备库为什么延迟几个小时

备库并行复制能力。单线程复制能力全面低于多线程复制，对于更新压力较大的主库，备库是可能一直追不上主库的。

27. 主库出问题了，从库怎么办？

一主多从的切换正确性。

同步位点。

28. 读写分离有哪些坑？

过期读：在从库上会读到系统的一个过期状态

部分强制走主库方案；
sleep 方案；
判断主备无延迟方案；
配合 semi-sync 方案；
等主库位点方案；
等 GTID 方案。

29丨如何判断一个数据库是不是出问题了？

select 1 判断
查表判断
更新判断
内部统计

31丨误删数据后除了跑路，还能怎么办？

误删行

可以用 Flashback 工具通过闪回把数据恢复回来。

误删库 / 表

就需要使用全量备份，加增量日志的方式了

32丨为什么还有kill不掉的语句？

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

所以，如果发现一个线程处于 Killed 状态，可以做的事情就是，通过影响系统环境，让这个 Killed 状态尽快结束。

比如， InnoDB 并发度的问题，可以临时调大 innodb_thread_concurrency 的值，或停掉别的线程，让出位子给这个线程执行。

而如果是回滚逻辑由于受到 IO 资源限制执行得比较慢，就通过减少系统压力让它加速。

做完这些操作后，其实你已经没有办法再对它做什么了，只能等待流程自己完成。

33丨我查这么多数据，会不会把数据库内存打爆？

由于 MySQL 采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在 server 端保存完整的结果集。所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。

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

当然，我们前面文章有说过，全表扫描还是比较耗费 IO 资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的。

34丨到底可不可以使用join？

Join 的流程

select * from t1 straight_join t2 on (t1.a=t2.a);                                                               NLJ算法流程图

（如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，这样会影响我们分析 SQL 语句的执行过程。）

对驱动表 t1 做了全表扫描，这个过程需要扫描 100 行；
而对于每一行 R，根据 a 字段去表 t2 查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描 100 行；
所以，整个执行流程，总扫描行数是 200。

而如果不用 join

执行select * from t1，查出表 t1 的所有数据，这里有 100 行；
循环遍历这 100 行数据：
- 从每一行 R 取出字段 a 的值 $R.a；
- 执行select * from t2 where a=$R.a；
- 把返回的结果和 R 构成结果集的一行。

也是扫描了 200 行，但是总共执行了 101 条语句，比直接 join 多了 100 次交互。

驱动表

驱动表是走全表扫描，而被驱动表是走树搜索。

如果是 Index Nested-Loop Join （被驱动表有索引）算法，应该选择小表做驱动表；
如果是 Block Nested-Loop Join 算法：（没索引，将驱动表放入线程内存 join_buffer ，被驱动表一行一行取对比）
- 在 join_buffer_size 足够大的时候，是一样的；
- 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

35 | join语句怎么优化？

Multi-Range Read 优化

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

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

想稳定地使用 MRR 的话，要设置set optimizer_switch="mrr_cost_based=off"。（更倾向于不使用 MRR）.MRR 能够提升性能的核心在于，查询语句在索引 a 上做的是一个范围查询，得到足够多的主键 id。排序后，再去主键索引查数据，才能体现出“顺序性”的优势。

Batched Key Access

BKA 算法的优化要依赖于 MRR，是对 NLJ 算法的优化。把表 t1 的数据取出一部分，放到一个临时内存 join_buffer。

大表 join 操作虽然对 IO 有影响，但是在语句执行结束后，对 IO 的影响也就结束了。但是，对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

BNL 转 BKA

BNL太耗资源，但如果被驱动表是个大表，但其实实际参与组合的数据很少，建索引的话开销大，不建的话又慢，就可以在查询时创建临时表，把被驱动表的匹配数据放进去再参与join.

hash join

临时表实际中有点扯，这种的话，如果非要不加索引。还是在代码里处理分两次查做映射。

36 | 为什么临时表可以重名？

用途

处理 35 节中的join,分库分表时非分批键查询，可以多表全查询后统一放到某库某实例上，做一个临时的统一表，在进行limit等操作

重名

创建临时表时创建了一个 frm 文件保存表结构定义，还要有地方保存表数据。

这个 frm 文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程 id}{线程 id} 序列号”。维护数据表，除了物理上有文件外，内存也有套机制区别不同的表，每个表都对应一个 table_def_key。不同session的线程不同所以其实是不重复的。

主从同步

在 binlog_format='row’的时候，临时表的操作不记录到 binlog 中，也省去了不少麻烦，这也可以成为你选择 binlog_format 时的一个考虑因素。

37 | 什么时候会使用内部临时表？

不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。如果表的数据量比较大，上面这个 group by 语句执行起来就会很慢，

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

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

数据组织结构

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

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

hash 索引和 B-Tree 索引

Memory也可以支持BTree

锁

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

持久性

重启丢数据，在内存中。

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

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

MyISAM 引擎的自增值保存在数据文件中。
InnoDB 自增值，保存内存里，MySQL 8.0 后，才有了“自增值持久化”的能力。之前重启后会去找max(id) + 1，但这是删一个最后的，取到的其实就是重复的了。

新插入

没有传id就用自增。传了如果大于等于，就更新为传的。

导致不连续

其他的唯一键冲突，未然未插入，但自增值修改是在插入前，即使插入失败也已经更新了。
回滚道理差不多。
批量插入

对于批量插入数据的语句，MySQL 有一个批量申请自增 id 的策略：

语句执行过程中，第一次申请自增 id，会分配 1 个；
1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；
依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。

最后的申请可能就会造成浪费且不连续。

40. insert语句的锁为什么这么多？

insert … select 是很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。

而如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。

insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

41. 怎么最快地复制一张表？

mysqldump 方法

导出 CSV 文件

物理拷贝

MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可通过导出 + 导入表空间的方式，实现物理拷贝表的功能。

假设现在目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：

执行 create table r like t，创建一个相同表结构的空表；
执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；
执行 unlock tables，这时候 t.cfg 文件会被删除；
执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

有以下几个注意点：

在第 3 步执行完 flsuh table 命令之后，db1.t 整个表处于只读状态，直到执行 unlock tables 命令后才释放读锁；
在执行 import tablespace 的时候，为了让文件里的表空间 id 和数据字典中的一致，会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此，如果是一个很大的文件（比如 TB 级别），每个数据页都需要修改，所以你会看到这个 import 语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import 语句的耗时是非常短的。

42.grant之后要跟着flush privileges吗？

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

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

43.要不要使用分区表？

略过

45. 自增id用完了咋办

表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。
Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。
InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的 bug，好在留给我们的时间还很充裕。
thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。