当前位置：首页 > news >正文

mysql45讲记录

news 来源：原创 2024/5/3 5:47:46

mysql45讲

基础架构图
在这里插入图片描述

Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务
功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在
这一层实现，比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、 MyISAM、
Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了
默认存储引擎。

连接器

查看连接信息 show processlist
在这里插入图片描述

MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候
才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现
象看就是MySQL异常重启了。

查询缓存

MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成
DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语
句，可以用SQL_CACHE显式指定，像下面这个语句一样：
mysql> select SQL_CACHE * from T where ID=10；

分析器

分析词法和语法

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）
的时候，决定各个表的连接顺序。

执行器

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有
权限的错误。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引
擎提供的接口

比如我们这个例子中的表T中， ID字段没有索引，那么执行器的执行流程是这样的：

调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则
将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
至此，这个语句就执行完成了。
对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后
循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的

redo log innodb层的物理日志循环覆盖存储

在这里插入图片描述

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。
checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文
件。
write pos和checkpoint之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果write pos
追上checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把
checkpoint推进一下。

bin log server层的逻辑日志，追加存储

前面我们讲过， MySQL整体来看，其实就有两块：一块是Server层，它主要做的是MySQL功能
层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的粉板redo log是
InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog（归档日志）。

这两种日志有以下三点不同。

redo log是InnoDB引擎特有的； binlog是MySQL的Server层实现的，所有引擎都可以使用。
redo log是物理日志，记录的是“在某个数据页上做了什么修改”； binlog是逻辑日志，记录的
是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
redo log是循环写的，空间固定会用完； binlog是可以追加写入的。 “追加写”是指binlog文件
写到一定大小后会切换到下一个，并不会覆盖以前的日志

有了对这两个日志的概念性理解，我们再来看执行器和InnoDB引擎在执行这个简单的update语
句时的内部流程。
\1. 执行器先找引擎取ID=2这一行。 ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一
行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然
后再返回。
\2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行
数据，再调用引擎接口写入这行新数据。
\3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处
于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
\4. 执行器生成这个操作的binlog，并把binlog写入磁盘。
\5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更
新完成

在这里插入图片描述

你可能注意到了，最后三步看上去有点“绕”，将redo log的写入拆成了两个步骤： prepare和
commit，这就是"两阶段提交"。

为什么日志需要“两阶段提交”

由于redo log和binlog是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log再写
binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

仍然用前面的update语句来做例子。假设当前ID=2的行，字段c的值是0，再假设执行update语
句过程中在写完第一个日志后，第二个日志还没有写完期间发生了crash，会出现什么情况呢？

先写redo log后写binlog。假设在redo log写完， binlog还没有写完的时候， MySQL进程异
常重启。由于我们前面说过的， redo log写完之后，系统即使崩溃，仍然能够把数据恢复回
来，所以恢复后这一行c的值是1。
但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份
日志的时候，存起来的binlog里面就没有这条语句。
然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这
个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。
先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以
后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日
志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是
1，与原库的值不同。

数据库事务

隔离级别

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

索引

根据叶子节点的内容，索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered
index）。
非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引
（secondaryindex）。

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

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

索引维护

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

覆盖索引
在这里插入图片描述

索引下推

在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。
而MySQL 5.6 引入的索引下推优化（indexcondition pushdown)，可以在索引遍历过程中，对索
引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

为什么要重建索引。我们文章里面有提到，索引可能因为删除，或者页分
裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样
页面的利用率最高，也就是索引更紧凑、更省空间。

数据库锁

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

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。 MySQL提供了一个加全局读锁的方法，命令是
Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命
令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括
建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本。

表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock， MDL)。
表锁的语法是 lock tables …read/write。与FTWRL类似，可以用unlock tables主动释放锁，
也可以在客户端断开的时候自动释放。需要注意， lock tables语法除了会限制别的线程的读写
外，也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读
写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操
作。连写t1都不允许，自然也不能访问其他表。

另一类表级的锁是MDL（ metadata lock)。 MDL不需要显式使用，在访问一个表的时候会被
自动加上。 MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个
表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果
跟表结构对不上，肯定是不行的。

因此，在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当
要对表做结构变更操作的时候，加MDL写锁。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线
程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

我们可以看到session A先启动，这时候会对表t加一个MDL读锁。由于session B需要的也是
MDL读锁，因此可以正常执行。
之后session C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写
锁，因此只能被阻塞。
如果只有session C自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也
会被session C阻塞。前面我们说了，所有对表的增删改查操作都需要先申请MDL读锁，就都被
锁住，等于这个表现在完全不可读写了。
如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session
再请求的话，这个库的线程很快就会爆满。
你现在应该知道了，事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释
放，而会等到整个事务提交后再释放。
基于上面的分析，我们来讨论一个问题，如何安全地给小表加字段？
首先我们要解决长事务，事务不提交，就会一直占着MDL锁。在MySQL的information_schema
库的 innodb_trx表中，你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务
在执行，要考虑先暂停DDL，或者kill掉这个长事务。
但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频
繁，而你不得不加个字段，你该怎么做呢？
这时候kill可能未必管用，因为新的请求马上就来了。比较理想的机制是，在alter table语句里面
设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后
面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。
MariaDB已经合并了AliSQL的这个功能，所以这两个开源分支目前都支持DDL NOWAIT/WAITn
这个语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

小结
MySQL的全局锁和表级锁。
全局锁主要用在逻辑备份过程中。对于全部是InnoDB引擎的库，我建议你选择使用–singletransaction参数，对应用会更友好。
表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有lock
tables这样的语句，你需要追查一下，比较可能的情况是：
要么是你的系统现在还在用MyISAM这类不支持事务的引擎，那要安排升级换引擎；
要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把lock
tables 和 unlock tables 改成 begin 和 commit，问题就解决了。
MDL会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新。

行锁

顾名思义，行锁就是针对数据表中行记录的锁。这很好理解，比如事务A更新了一行，而这时候
事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新

在这里插入图片描述

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

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

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致
这几个线程都进入无限等待的状态，称为死锁。
在这里插入图片描述

这时候，事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和
事务B在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：
一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数
innodb_lock_wait_timeout来设置。在InnoDB中， innodb_lock_wait_timeout的默认值是50s。

另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事
务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

在MySQL里，有两个“视图”的概念：
一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。
创建视图的语法是create view …，而它的查询方法与表一样。
另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持
RC（Read Committed，读提交）和RR（ Repeatable Read，可重复读）隔离级别的实现。

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如
果当前的记录的行锁被其他事务占用的话，就需要进入锁等待

索引选择和实践
回到我们文章开头的问题，普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上
是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。
如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在
其他情况下， change buffer都能提升更新性能。
在实际使用中，你会发现，普通索引和change buffer的配合使用，对于数据量大的表的更新优
化还是很明显的。
特别地，在使用机械硬盘时， change buffer这个机制的收效是非常显著的。所以，当你有一个
类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索
引，尽量使用普通索引，然后把change buffer 尽量开大，以确保这个“历史数据”表的数据写入
速度

对于由于索引统计信息不准确导致的问题，你可以用analyze table来解决。
而对于其他优化器误判的情况，你可以在应用端用force index来强行指定索引，也可以通过修改
语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题

对于普通字符串创建索引时：

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

普通索引

前缀索引

倒序存储

hash

从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash字
段方法需要增加一个字段。当然，倒序存储方式使用4个字节的前缀长度应该是不够的，如
果再长一点，这个消耗跟额外这个hash字段也差不多抵消了。
在CPU消耗方面，倒序方式每次写和读的时候，都需要额外调用一次reverse函数，而hash
字段的方式需要额外调用一次crc32()函数。如果只从这两个函数的计算复杂度来看的
话， reverse函数额外消耗的CPU资源会更小些。
从查询效率上看，使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值虽
然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近1。而倒序存储
方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写
入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

在这里插入图片描述

这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力。这个值我建议你设
置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试

一旦一个查询请求需要在执行过程中先flush掉一个脏页时，这个查询就可能要比平时慢了。而
MySQL中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁
边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还
可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放
到一起刷。
在InnoDB中， innodb_flush_neighbors 参数就是用来控制这个行为的，值为1的时候会有上述
的“连坐”机制，值为0时表示不找邻居，自己刷自己的。
找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机IO。机械硬盘的随机IOPS
一般只有几百，相同的逻辑操作减少随机IO就意味着系统性能的大幅度提升。
而如果使用的是SSD这类IOPS比较高的设备的话，我就建议你把innodb_flush_neighbors的值
设置成0。因为这时候IOPS往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操
作，减少SQL语句响应时间。
在MySQL 8.0中， innodb_flush_neighbors参数的默认值已经是0了。

问题

一个内存配置为128GB、 innodb_io_capacity设置为20000的大规格实例，正常会建议你将redo
log设置成4个1GB的文件。
但如果你在配置的时候不慎将redo log设置成了1个100M的文件，会发生什么情况呢？又为什么
会出现这样的情况呢？

每次事务提交都要写redo log，如果设置太小，很快就会被写满，也就是下面这个图的状态，这
个“环”将很快被写满， write pos一直追着CP。

这时候系统不得不停止所有更新，去推进checkpoint。
这时，你看到的现象就是磁盘压力很小，但是数据库出现间歇性的性能下跌。

一个InnoDB表包含两部
分，即：表结构定义和数据。在MySQL 8.0版本以前，表结构是存在以.frm为后缀的文件里。而
MySQL 8.0版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很
小，所以我们今天主要讨论的是表数据

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数
innodb_file_per_table控制的：
\1. 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一
起；
\2. 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。
从MySQL 5.6.6版本开始，它的默认值就是ON了。
我建议你不论使用MySQL的哪个版本，都将这个值设置为ON。因为，一个表单独存储为一个文
件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文
件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

重建表

用于修复数据页撕裂、空洞问题，优化表空间

optimize
table、 analyze table和alter table这三种方式重建表的区别

从MySQL 5.6版本开始， alter table t engine = InnoDB（也就是recreate）默认的就是上面图4
的流程了；
analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程
中加了MDL读锁；
optimize table t 等于recreate+analyze。

现在你已经知道了，如果要收缩一个表，只是delete掉表里面不用的数据的话，表文件的大小是
不会变的，你还要通过alter table命令重建表，才能达到表文件变小的目的。我跟你介绍了重建
表的两种实现方式， Online DDL的方式是可以考虑在业务低峰期使用的，而MySQL 5.5及之前的
版本，这个命令是会阻塞DML的，这个你需要特别小心

count(*)的实现方式

为什么InnoDB不跟MyISAM一样，也把数字存起来呢？
这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因， InnoDB
表“应该返回多少行”也是不确定的。
在这里插入图片描述

这和InnoDB的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发
控制，也就是MVCC来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于
count(*)请求来说， InnoDB只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基
于这个查询”的表的总行数

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

如果你用过show table status 命令的话，就会发现这个命令的输出结果里面也有一个
TABLE_ROWS用于显示这个表当前有多少行，这个命令执行挺快的，那这个TABLE_ROWS能
代替count(*)吗？

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

不同的count用法
在前面文章的评论区，有同学留言问到：在select count(?) from t这样的查询语句里
面， count()、 count(主键id)、 count(字段)和count(1)等不同用法的性能，有哪些差别。今天谈
到了count()的性能问题，我就借此机会和你详细说明一下这几种用法的性能差别。
需要注意的是，下面的讨论还是基于InnoDB引擎的。
这里，首先你要弄清楚count()的语义。 count()是一个聚合函数，对于返回的结果集，一行行地
判断，如果count函数的参数不是NULL，累计值就加1，否则不加。最后返回累计值。
所以， count()、 count(主键id)和count(1) 都表示返回满足条件的结果集的总行数；而count(字
段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数。
至于分析性能差别的时候，你可以记住这么几个原则：
\1. server层要什么就给什么；
\2. InnoDB只给必要的值；
\3. 现在的优化器只优化了count()的语义为“取行数”，其他“显而易见”的优化并没有做。
这是什么意思呢？接下来，我们就一个个地来看看。
对于count(主键id)来说， InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server
层。 server层拿到id后，判断是不可能为空的，就按行累加。
对于count(1)来说， InnoDB引擎遍历整张表，但不取值。 server层对于返回的每一行，放一个
数字“1”进去，判断是不可能为空的，按行累加。
单看这两个用法的差别的话，你能对比出来， count(1)执行得要比count(主键id)快。因为从引擎
返回id会涉及到解析数据行，以及拷贝字段值的操作。
对于count(字段)来说：
\1. 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为
null，按行累加；
\2. 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再
判断一下，不是null才累加。
也就是前面的第一条原则， server层要什么字段， InnoDB就返回什么字段。
但是count()是例外，并不会把全部字段取出来，而是专门做了优化，不取值。 count()肯定不
是null，按行累加。
看到这里，你一定会说，优化器就不能自己判断一下吗，主键id肯定非空啊，为什么不能按照
count()来处理，多么简单的优化啊。
当然， MySQL专门针对这个语句进行优化，也不是不可以。但是这种需要专门优化的情况太多
了，而且MySQL已经优化过count()了，你直接使用这种用法就可以了。
所以结论是：按照效率排序的话， count(字段)<count(主键id)<count(1)≈count()，所以我建议
你，尽量使用count()。

全字段排序 VS rowid排序
我们来分析一下，从这两个执行流程里，还能得出什么结论。
如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过
程中一次可以排序更多行，但是需要再回到原表去取数据。
如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这
样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
这也就体现了MySQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问

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

隐式类型转换

数据类型转换的规则是什么？
为什么有数据类型转换，就需要走全索引扫描？

在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。

隐式字符编码转换

因为这两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询的时候用不上关联字段的索引

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
如何解决幻读？
现在你知道了，产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记
录之间的“间隙”。因此，为了解决幻读问题， InnoDB只好引入新的锁，也就是间隙锁(GapLock)。
顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表t，初始化插入了6个记录，这就产生了7个间隙；
在这里插入图片描述

这样，当你执行 select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记
录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。
也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁
在这里插入图片描述

间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，
就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把binlog格式设置为row。
在这里插入图片描述

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

lock in share mode只锁覆盖索引，但是如果是for update就不一样
了。执行 for update时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用lock in share mode
来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。

在删除数据的时候尽量加limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。
在这里插入图片描述

从服务端断开连接使用的是kill connection + id的命令，一个客户端处于sleep状态时，它的连接
被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会
收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”
在这里插入图片描述

慢查询性能问题
在MySQL中，会引发性能问题的慢查询，大体有以下三种可能：
\1. 索引没有设计好；
\2. SQL语句没写好；
\3. MySQL选错了索引

导致慢查询的第一种可能是，索引没有设计好。
这种场景一般就是通过紧急创建索引来解决。 MySQL 5.6版本以后，创建索引都支持Online DDL
了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行alter
table 语句。
比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库A、备库B，这个方案的
大致流程是这样的：
\1. 在备库B上执行 set sql_log_bin=off，也就是不写binlog，然后执行alter table 语句加上索引；
\2. 执行主备切换；
\3. 这时候主库是B，备库是A。在A上执行 set sql_log_bin=off，然后执行alter table 语句加上索引。

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

查询重写

导致慢查询的第三种可能，mysql选错了索引。

这时候，应急方案就是给这个语句加上force index。
同样地，使用查询重写功能，给原来的语句加上force index，也可以解决这个问题

上面我和你讨论的由慢查询导致性能问题的三种可能情况，实际上出现最多的是前两种，即：索
引没设计好和语句没写好。而这两种情况，恰恰是完全可以避免的。比如，通过下面这个过程，
我们就可以预先发现问题。
\1. 上线前，在测试环境，把慢查询日志（slow log）打开，并且把long_query_time设置成0，
确保每个语句都会被记录入慢查询日志；
\2. 在测试表里插入模拟线上的数据，做一遍回归测试；
\3. 观察慢查询日志里每类语句的输出，特别留意Rows_examined字段是否与预期一致。（我
们在前面文章中已经多次用到过Rows_examined方法了，相信你已经动手尝试过了。如果
还有不明白的，欢迎给我留言，我们一起讨论）。

你可以使用开源工具pt-query-digest(https://www.percona.com/doc/perconatoolkit/3.0/pt-query-digest.html)。

QPS突增问题

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

当然，这个操作的风险很高，需要你特别细致。它可能存在两个副作用：
\1. 如果别的功能里面也用到了这个SQL语句模板，会有误伤；
\2. 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以select 1的结
果返回的话，可能会导致后面的业务逻辑一起失败。
所以，方案3是用于止血的，跟前面提到的去掉权限验证一样，应该是你所有选项里优先级最低
的一个方案。
同时你会发现，其实方案1和2都要依赖于规范的运维体系：虚拟化、白名单机制、业务账号分
离。由此可见，更多的准备，往往意味着更稳定的系统。

MySQL写入binlog和redo log的流程

binlog的写入机制
其实， binlog的写入逻辑比较简单：事务执行过程中，先把日志写到binlog cache，事务提交的
时候，再把binlog cache写到binlog文件中。
一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到
了binlog cache的保存问题。
系统给binlog cache分配了一片内存，每个线程一个，参数 binlog_cache_size用于控制单个线程
内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
事务提交的时候，执行器把binlog cache里的完整事务写入到binlog中，并清空binlog cache。
在这里插入图片描述

可以看到，每个线程有自己binlog cache，但是共用同一份binlog文件。
图中的write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁
盘，所以速度比较快。
图中的fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的
IOPS。
write 和fsync的时机，是由参数sync_binlog控制的：
\1. sync_binlog=0的时候，表示每次提交事务都只write，不fsync；
\2. sync_binlog=1的时候，表示每次提交事务都会执行fsync；
\3. sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。
因此，在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际
的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其
设置为100~1000中的某个数值。
但是，将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志

redo log的写入机制
在这里插入图片描述

这三种状态分别是：
\1. 存在redo log buffer中，物理上是在MySQL进程内存中，就是图中的红色部分；
\2. 写到磁盘(write)，但是没有持久化（fsync)，物理上是在文件系统的page cache里面，也就
是图中的黄色部分；
\3. 持久化到磁盘，对应的是hard disk，也就是图中的绿色部分。
日志写到redo log buffer是很快的， wirte到page cache也差不多，但是持久化到磁盘的速度就慢
多了。
为了控制redo log的写入策略， InnoDB提供了innodb_flush_log_at_trx_commit参数，它有三种
可能取值：
\1. 设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中;
\2. 设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘；
\3. 设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。
InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的
page cache，然后调用fsync持久化到磁盘。
注意，事务执行中间过程的redo log也是直接写在redo log buffer中的，这些redo log也会被后台
线程一起持久化到磁盘。也就是说，一个没有提交的事务的redo log，也是可能已经持久化到磁
盘的。

实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的redo
log写入到磁盘中。
\1. 一种是， redo log buffer占用的空间即将达到 innodb_log_buffer_size一半的时候，
后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是write，而
没有调用fsync，也就是只留在了文件系统的page cache。
\2. 另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁
盘。假设一个事务A执行到一半，已经写了一些redo log到buffer中，这时候有另外一个线程
的事务B提交，如果innodb_flush_log_at_trx_commit设置的是1，那么按照这个参数的逻
辑，事务B要把redo log buffer里的日志全部持久化到磁盘。这时候，就会带上事务A在redo
log buffer里的日志一起持久化到磁盘。
这里需要说明的是，我们介绍两阶段提交的时候说过，时序上redo log先prepare，再写binlog，
最后再把redo log commit。

如果把innodb_flush_log_at_trx_commit设置成1，那么redo log在prepare阶段就要持久化一次，
因为有一个崩溃恢复逻辑是要依赖于prepare 的redo log，再加上binlog来恢复的。

每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑， InnoDB就认为redo log在commit的时候就
不需要fsync了，只会write到文件系统的page cache中就够了。
通常我们说MySQL的“双1”配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit都设
置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶
段），一次是binlog

这时候，你可能有一个疑问，这意味着我从MySQL看到的TPS是每秒两万的话，每秒就会写四
万次磁盘。但是，我用工具测试出来，磁盘能力也就两万左右，怎么能实现两万的TPS？
解释这个问题，就要用到组提交（group commit）机制了。
这里，我需要先和你介绍日志逻辑序列号（log sequence number， LSN）的概念。 LSN是单调
递增的，用来对应redo log的一个个写入点。每次写入长度为length的redo log， LSN的值就会加
上length。
LSN也会写到InnoDB的数据页中，来确保数据页不会被多次执行重复的redo log。关于LSN和
redo log、 checkpoint的关系，我会在后面的文章中详细展开

trx1是第一个到达的，会被选为这组的 leader；
\2. 等trx1要开始写盘的时候，这个组里面已经有了三个事务，这时候LSN也变成了160；
\3. trx1去写盘的时候，带的就是LSN=160，因此等trx1返回时，所有LSN小于等于160的redo
log，都已经被持久化到磁盘；
\4. 这时候trx2和trx3就可以直接返回了。
所以，一次组提交里面，组员越多，节约磁盘IOPS的效果越好。但如果只有单线程压测，那就
只能老老实实地一个事务对应一次持久化操作了。
在并发更新场景下，第一个事务写完redo log buffer以后，接下来这个fsync越晚调用，组员可能
越多，节约IOPS的效果就越好。
为了让一次fsync带的组员更多， MySQL有一个很有趣的优化：拖时间

想提升binlog组提交的效果，可以通过设置 binlog_group_commit_sync_delay和
binlog_group_commit_sync_no_delay_count来实现。
\1. binlog_group_commit_sync_delay参数，表示延迟多少微秒后才调用fsync;
\2. binlog_group_commit_sync_no_delay_count参数，表示累积多少次以后才调用fsync。
这两个条件是或的关系，也就是说只要有一个满足条件就会调用fsync。
所以，当binlog_group_commit_sync_delay设置为0的时
候， binlog_group_commit_sync_no_delay_count也无效了。

WAL机制主要得益于两个方面：
\1. redo log 和 binlog都是顺序写，磁盘的顺序写比随机写速度要快；
\2. 组提交机制，可以大幅度降低磁盘的IOPS消耗。

如果你的MySQL现在出现了性能瓶颈，而且瓶颈在IO
上，可以通过哪些方法来提升性能呢？
针对这个问题，可以考虑以下三种方法：
\1. 设置 binlog_group_commit_sync_delay和 binlog_group_commit_sync_no_delay_count参
数，减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加
语句的响应时间，但没有丢失数据的风险。
\2. 将sync_binlog 设置为大于1的值（比较常见是100~1000）。这样做的风险是，主机掉电时
会丢binlog日志。
\3. 将innodb_flush_log_at_trx_commit设置为2。这样做的风险是，主机掉电的时候会丢数据。

我不建议你把innodb_flush_log_at_trx_commit 设置成0。因为把这个参数设置成0，表示redo
log只保存在内存中，这样的话MySQL本身异常重启也会丢数据，风险太大。而redo log写到文
件系统的page cache的速度也是很快的，所以将这个参数设置成2跟设置成0其实性能差不多，
但这样做MySQL异常重启时就不会丢数据了，相比之下风险会更小

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

在这里插入图片描述

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

binlog的三种格式对比

一种是statement，一种是row，第三种格式，叫作mixed
在这里插入图片描述

为什么会有mixed格式的binlog？
基于上面的信息，我们来讨论一个问题：为什么会有mixed这种binlog格式的存在场景？推论
过程是这样的：
因为有些statement格式的binlog可能会导致主备不一致，所以要使用row格式。
但row格式的缺点是，很占空间。比如你用一个delete语句删掉10万行数据，用statement的
话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，
就要把这10万条记录都写到binlog中。这样做，不仅会占用更大的空间，同时写binlog也要耗
费IO资源，影响执行速度。
所以， MySQL就取了个折中方案，也就是有了mixed格式的binlog。 mixed格式的意思
是， MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，
否则就用statement格式。
也就是说， mixed格式可以利用statment格式的优点，同时又避免了数据不一致的风险。
因此，如果你的线上MySQL设置的binlog格式是statement的话，那基本上就可以认为这是一个
不合理的设置。你至少应该把binlog的格式设置为mixed。
比如我们这个例子，设置为mixed后，就会记录为row格式；而如果执行的语句去掉limit 1，就会
记录为statement格式。
当然我要说的是，现在越来越多的场景要求把MySQL的binlog格式设置成row。这么做的理由有
很多，我来给你举一个可以直接看出来的好处：恢复数据

接下来，我们就分别从delete、 insert和update这三种SQL语句的角度，来看看数据恢复的问
题。
通过图6你可以看出来，即使我执行的是delete语句， row格式的binlog也会把被删掉的行的整行
信息保存起来。所以，如果你在执行完一条delete语句以后，发现删错数据了，可以直接把
binlog中记录的delete语句转成insert，把被错删的数据插入回去就可以恢复了。
如果你是执行错了insert语句呢？那就更直接了。 row格式下， insert语句的binlog里会记录所有的
字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把insert语句转成
delete语句，删除掉这被误插入的一行数据就可以了。
如果执行的是update语句的话， binlog里面会记录修改前整行的数据和修改后的整行数据。所
以，如果你误执行了update语句的话，只需要把这个event前后的两行信息对调一下，再去数据
库里面执行，就能恢复这个更新操作了。
其实，由delete、 insert或者update语句导致的数据操作错误，需要恢复到操作之前状态的情
况，也时有发生。 MariaDB的Flashback工具就是基于上面介绍的原理来回滚数据的

循环复制问题
在这里插入图片描述

主备延迟

在介绍主动切换流程的详细步骤之前，我要先跟你说明一个概念，即“同步延迟”。与数据同步有
关的时间点主要包括以下三个：
\1. 主库A执行完成一个事务，写入binlog，我们把这个时刻记为T1;
\2. 之后传给备库B，我们把备库B接收完这个binlog的时刻记为T2;
\3. 备库B执行完成这个事务，我们把这个时刻记为T3。
所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是T3-T1

在备库上执行show slave status命令，它的返回结果里面会显示
seconds_behind_master，用于表示当前备库延迟了多少秒。
seconds_behind_master的计算方法是这样的：
\1. 每个事务的binlog 里面都有一个时间字段，用于记录主库上写入的时间；
\2. 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到
seconds_behind_master。
可以看到，其实seconds_behind_master这个参数计算的就是T3-T1。所以，我们可以用
seconds_behind_master来作为主备延迟的值，这个值的时间精度是秒

你可能会问，如果主备库机器的系统时间设置不一致，会不会导致主备延迟的值不准？
其实不会的。因为，备库连接到主库的时候，会通过执行SELECTUNIX_TIMESTAMP()函数来
获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行
seconds_behind_master计算的时候会自动扣掉这个差值

需要说明的是，在网络正常的时候，日志从主库传给备库所需的时间是很短的，即T2-T1的值是
非常小的。也就是说，网络正常情况下，主备延迟的主要来源是备库接收完binlog和执行完这个
事务之间的时间差。
所以说，主备延迟最直接的表现是，备库消费中转日志（ relaylog）的速度，比主库生产binlog
的速度要慢。接下来，我就和你一起分析下，这可能是由哪些原因导致的

主备延迟的来源
首先，有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。当备库主机上的多个备库都在争抢资源的
时候，就可能会导致主备延迟了

第二种常见的可能了，即备库的压力大。备库上的查询耗费了大量的CPU资源，影响了同步速度，造成主备延
迟

第三种可能了，即大事务。大事务这种情况很好理解。因为主库上必须等事务执行完成才会写入binlog，再传给备库。所以，如果一个主库上的语句执行10分钟，那这个事务很可能就会导致从库延迟10分钟

第四种：造成主备延迟还有一个大方向的原因，就是备库的并行复制能力

可靠性优先策略

在图1的双M结构下，从状态1到状态2切换的详细过程是这样的：
\1. 判断备库B现在的seconds_behind_master，如果小于某个值（比如5秒）继续下一步，否则
持续重试这一步；
\2. 把主库A改成只读状态，即把readonly设置为true；
\3. 判断备库B的seconds_behind_master的值，直到这个值变成0为止；
\4. 把备库B改成可读写状态，也就是把readonly设置为false；
\5. 把业务请求切到备库B。

可用性优先策略

\1. 使用row格式的binlog时，数据不一致的问题更容易被发现。而使用mixed或者statement格
式的binlog时，数据很可能悄悄地就不一致了。如果你过了很久才发现数据不一致的问题，
很可能这时的数据不一致已经不可查，或者连带造成了更多的数据逻辑不一致。
\2. 主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，我都建议你使用可靠
性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的
在这里插入图片描述

coordinator就是原来的sql_thread, 不过现在它不再直接更新数据了，只负责读取中转日
志和分发事务。真正更新日志的，变成了worker线程。而work线程的个数，就是由参数
slave_parallel_workers决定的。根据我的经验，把这个值设置为8~16之间最好（32核物理机的
情况），毕竟备库还有可能要提供读查询，不能把CPU都吃光了

思考一个问题：事务能不能按照轮询的方式分发给各个worker，也就是第一个
事务分给worker_1，第二个事务发给worker_2呢？
其实是不行的。因为，事务被分发给worker以后，不同的worker就独立执行了。但是，由于CPU
的调度策略，很可能第二个事务最终比第一个事务先执行。而如果这时候刚好这两个事务更新的
是同一行，也就意味着，同一行上的两个事务，在主库和备库上的执行顺序相反，会导致主备不
一致的问题

coordinator在分发的时候，需要满足以下这两个基本要求：
\1. 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker中。
\2. 同一个事务不能被拆开，必须放到同一个worker中。

表分发的方案，在多个表负载均匀的场景里应用效果很好。但是，如果碰到热点表，比如
所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个worker中，就变成单
线程复制了。

按行分发策略
要解决热点表的并行复制问题，就需要一个按行并行复制的方案。按行复制的核心思路是：如果
两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求binlog格式必须
是row。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7sKWesAt-1661839611044)(D:\工作\mysql相关总结\img\image-20220829142236334.png)]
在这里插入图片描述

基于位点的主备切换
这里，我们需要先来回顾一个知识点。
当我们把节点B设置成节点A’的从库的时候，需要执行一条change master命令：
CHANGE MASTER TO
MASTER_HOST= $host_name MASTER_PORT=$ port
MASTER_USER= $user_name MASTER_PASSWORD=$ password
MASTER_LOG_FILE= $master_log_name MASTER_LOG_POS=$ master_log_pos
这条命令有这么6个参数：
MASTER_HOST、 MASTER_PORT、 MASTER_USER和MASTER_PASSWORD四个参
数，分别代表了主库A’的IP、端口、用户名和密码。
最后两个参数MASTER_LOG_FILE和MASTER_LOG_POS表示，要从主库的
master_log_name文件的master_log_pos这个位置的日志继续同步。而这个位置就是我们所
说的同步位点，也就是主库对应的文件名和日志偏移量。
那么，这里就有一个问题了，节点B要设置成A’的从库，就要执行change master命令，就不可
避免地要设置位点的这两个参数，但是这两个参数到底应该怎么设置呢？
原来节点B是A的从库，本地记录的也是A的位点。但是相同的日志， A的位点和A’的位点是不同
的。因此，从库B要切换的时候，就需要先经过“找同步位点”这个逻辑

MySQL 5.6
版本引入了GTID，彻底解决了这个困难。

GTID的全称是Global Transaction Identifier，也就是全局事务ID，是一个事务在提交的时候生成
的，是这个事务的唯一标识。它由两部分组成，格式是：GTID=server_uuid:gno
其中：
server_uuid是一个实例第一次启动时自动生成的，是一个全局唯一的值；
gno是一个整数，初始值是1，每次提交事务的时候分配给这个事务，并加1。

GTID模式的启动也很简单，我们只需要在启动一个MySQL实例的时候，加上参数gtid_mode=on
和enforce_gtid_consistency=on就可以了。

在GTID模式下，每个事务都会跟一个GTID一一对应。这个GTID有两种生成方式，而使用哪种
方式取决于session变量gtid_next的值。
\1. 如果gtid_next=automatic，代表使用默认值。这时， MySQL就会把server_uuid:gno分配给
GTID=server_uuid:gno
GTID=source_id:transaction_id
这个事务。
a. 记录binlog的时候，先记录一行 SET@@SESSION.GTID_NEXT=‘server_uuid:gno’;
b. 把这个GTID加入本实例的GTID集合。
\2. 如果gtid_next是一个指定的GTID的值，比如通过set gtid_next='current_gtid’指定为
current_gtid，那么就有两种可能：
a. 如果current_gtid已经存在于实例的GTID集合中，接下来执行的这个事务会直接被系统忽
略；
b. 如果current_gtid没有存在于实例的GTID集合中，就将这个current_gtid分配给接下来要执
行的事务，也就是说系统不需要给这个事务生成新的GTID，因此gno也不用加1。
注意，一个current_gtid只能给一个事务使用。这个事务提交后，如果要执行下一个事务，就要
执行set 命令，把gtid_next设置成另外一个gtid或者automatic。

这样，每个MySQL实例都维护了一个GTID集合，用来对应“这个实例执行过的所有事务”

基于GTID的主备切换
现在，我们已经理解GTID的概念，再一起来看看基于GTID的主备复制的用法。
在GTID模式下，备库B要设置为新主库A’的从库的语法如下：
其中， master_auto_position=1就表示这个主备关系使用的是GTID协议。可以看到，前面让我
们头疼不已的MASTER_LOG_FILE和MASTER_LOG_POS参数，已经不需要指定了。

我们在实例B上执行start slave命令，取binlog的逻辑是这样的：
\1. 实例B指定主库A’，基于主备协议建立连接。
\2. 实例B把set_b发给主库A’。
\3. 实例A’算出set_a与set_b的差集，也就是所有存在于set_a，但是不存在于set_b的GITD的
集合，判断A’本地是否包含了这个差集需要的所有binlog事务。
a. 如果不包含，表示A’已经把实例B需要的binlog给删掉了，直接返回错误；
b. 如果确认全部包含， A’从自己的binlog文件里面，找出第一个不在set_b的事务，发给B；
\4. 之后就从这个事务开始，往后读文件，按顺序取binlog发给B去执行。

这跟基于位点的主备协议不同。基于位点的协议，是由备库决定的，备库指定哪个位点，主库就
发哪个位点，不做日志的完整性判断

读写分离有哪些坑？

客户端直连和带proxy的读写分离架构，各有哪些特点

客户端直连方案，因为少了一层proxy转发，所以查询性能稍微好一点儿，并且整体架构简
单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库
迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。
你可能会觉得这样客户端也太麻烦了，信息大量冗余，架构很丑。其实也未必，一般采用这
样的架构，一定会伴随一个负责管理后端的组件，比如Zookeeper，尽量让业务端只专注于
业务逻辑开发。
带proxy的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维
护等工作，都是由proxy完成的。但这样的话，对后端维护团队的要求会更高。而且， proxy
也需要有高可用架构。因此，带proxy架构的整体就相对比较复杂。

由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态。
这种“在从库上会读到系统的一个过期状态”的现象，在这篇文章里，我们暂且称之为“过期读” 。

解决方案：

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

强制走主库方案
强制走主库方案其实就是，将查询请求做分类。通常情况下，我们可以将查询请求分为这么两
类：

对于必须要拿到最新结果的请求，强制将其发到主库上。比如，在一个交易平台上，卖家发
布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结
果，就必须走主库。
对于可以读到旧数据的请求，才将其发到从库上。在这个交易平台上，买家来逛商铺页面，
就算晚几秒看到最新发布的商品，也是可以接受的。那么，这类请求就可以走从库。

Sleep 方案
主库更新后，读从库之前先sleep一下。具体的方案就是，类似于执行一条select sleep(1)命令

判断主备无延迟方案

要确保备库无延迟，通常有三种做法。

第一种确保主备无延迟的方法是，每次从库执行查询请求前，先判断
seconds_behind_master是否已经等于0。如果还不等于0 ，那就必须等到这个参数变为0才能执行查询请求

第二种方法，对比位点确保主备无延迟：
Master_Log_File和Read_Master_Log_Pos，表示的是读到的主库的最新位点；
Relay_Master_Log_File和Exec_Master_Log_Pos，表示的是备库执行的最新位点。
如果Master_Log_File和Relay_Master_Log_File、 Read_Master_Log_Pos和
Exec_Master_Log_Pos这两组值完全相同，就表示接收到的日志已经同步完成

第三种方法，对比GTID集合确保主备无延迟：
Auto_Position=1 ，表示这对主备关系使用了GTID协议。
Retrieved_Gtid_Set，是备库收到的所有日志的GTID集合；
Executed_Gtid_Set，是备库所有已经执行完成的GTID集合。
如果这两个集合相同，也表示备库接收到的日志都已经同步完成

配合semi-sync

半同步复制，也就是semi-sync replication。

semi-sync做了这样的设计：
\1. 事务提交的时候，主库把binlog发给从库；
\2. 从库收到binlog以后，发回给主库一个ack，表示收到了；
\3. 主库收到这个ack以后，才能给客户端返回“事务完成”的确认。
也就是说，如果启用了semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经
收到了这个日志。

semi-sync配合判断主备无延迟的方案，存在两个问题：
\1. 一主多从的时候，在某些从库执行查询请求会存在过期读的现象；
\2. 在持续延迟的情况下，可能出现过度等待的问题。

等主库位点方案

select master_pos_wait(file, pos[, timeout]);

这条命令的逻辑如下：
\1. 它是在从库执行的；
\2. 参数file和pos指的是主库上的文件名和位置；
\3. timeout可选，设置为正整数N表示这个函数最多等待N秒。
这个命令正常返回的结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置，执行了多少事务

GTID方案

select wait_for_executed_gtid_set(gtid_set, 1);

这条命令的逻辑是：
\1. 等待，直到这个库执行的事务中包含传入的gtid_set，返回0；
\2. 超时返回1

在前面等位点的方案中，我们执行完事务后，还要主动去主库执行show master status。而
MySQL 5.7.6版本开始，允许在执行完更新类事务后，把这个事务的GTID返回给客户端，这样
等GTID的方案就可以减少一次查询。
这时，等GTID的执行流程就变成了：
\1. trx1事务更新完成后，从返回包直接获取这个事务的GTID，记为gtid1；
\2. 选定一个从库执行查询语句；
\3. 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；
\4. 如果返回值是0，则在这个从库执行查询语句；
\5. 否则，到主库执行查询语句

怎么能够让MySQL在执行事务后，返回包中带上GTID呢？

你只需要将参数session_track_gtids设置为OWN_GTID，然后通过API接口mysql_session_track_get_first从返回包解析出GTID的值即可。

这里我再回答一下。其实， MySQL并没有提供这类接口的SQL用法，是提供给程序的
API(https://dev.mysql.com/doc/refman/5.7/en/c-api-functions.html)

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

在InnoDB中， innodb_thread_concurrency这个参数的默认值是0，表示不限制并发线程数量。
但是，不限制并发线程数肯定是不行的。因为，一个机器的CPU核数有限，线程全冲进来，上下
文切换的成本就会太高。
所以，通常情况下，我们建议把innodb_thread_concurrency设置为64~128之间的值

并发连接和并发查询，并不是同一个概念。你在show processlist的结果里，看到的几千个连
接，指的就是并发连接。而“当前正在执行”的语句，才是我们所说的并发查询。

实际上，在线程进入锁等待以后，并发线程的计数会减一，也就是说等行锁（也包括间隙
锁）的线程是不算在128里面的。

查表判断
为了能够检测InnoDB并发线程数过多导致的系统不可用情况，我们需要找一个访问InnoDB的场
景。一般的做法是，在系统库（mysql库）里创建一个表，比如命名为health_check，里面只放
一行数据，然后定期执行

更新判断
既然要更新，就要放个有意义的字段，常见做法是放一个timestamp字段，用来表示最后一次执
行检测的时间。

内部统计
针对磁盘利用率这个问题，如果MySQL可以告诉我们，内部每一次IO请求的时间，那我们判断
数据库是否出问题的方法就可靠得多了。
其实， MySQL 5.6版本以后提供的performance_schema库，就在file_summary_by_event_name
表里统计了每次IO请求的时间。

恢复数据比较安全的做法，是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执
行这些操作，然后再将确认过的临时库的数据，恢复回主库。

我们不止要说误删数据的事后处理办法，更重要是要做到事前预防。我有以下两个建
议：
\1. 把sql_safe_updates参数设置为on。这样一来，如果我们忘记在delete或者update语句中写
where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错。
\2. 代码上线前，必须经过SQL审计。

第一条建议是，账号分离。这样做的目的是，避免写错命令。比如：
我们只给业务开发同学DML权限，而不给truncate/drop权限。而如果业务开发人员有DDL需
求的话，也可以通过开发管理系统得到支持。
即使是DBA团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账
号。
第二条建议是，制定操作规范。这样做的目的，是避免写错要删除的表名。比如：
在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后
再删除这张表。
改表名的时候，要求给表名加固定的后缀（比如加_to_be_deleted)，然后删除表的动作必须
通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表

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

在MySQL中有两个kill命令：一个是kill query+线程id，表示终止这个线程中正在执行的语句；一
个是kill connection +线程id，这里connection可缺省，表示断开这个线程的连接，当然如果这个
线程有语句正在执行，也是要先停止正在执行的语句的

实现上，当用户执行kill query thread_id_B时， MySQL里处理kill命令的线程做了两件
事：
\1. 把session B的运行状态改成THD::KILL_QUERY(将变量killed赋值为THD::KILL_QUERY)；
\2. 给session B的执行线程发一个信号

\1. 一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态
是THD::KILL_QUERY，才开始进入语句终止逻辑；
\2. 如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处；
\3. 语句从开始进入终止逻辑，到终止逻辑完全完成，是有一个过程的。

小结
在今天这篇文章中，我首先和你介绍了MySQL中，有些语句和连接“kill不掉”的情况。
这些“kill不掉”的情况，其实是因为发送kill命令的客户端，并没有强行停止目标线程的执行，而只
是设置了个状态，并唤醒对应的线程。而被kill的线程，需要执行到判断状态的“埋点”，才会开始
进入终止逻辑阶段。并且，终止逻辑本身也是需要耗费时间的。
所以，如果你发现一个线程处于Killed状态，你可以做的事情就是，通过影响系统环境，让这个
Killed状态尽快结束。
比如，如果是第一个例子里InnoDB并发度的问题，你就可以临时调大
innodb_thread_concurrency的值，或者停掉别的线程，让出位子给这个线程执行。
而如果是回滚逻辑由于受到IO资源限制执行得比较慢，就通过减少系统压力让它加速。
做完这些操作后，其实你已经没有办法再对它做什么了，只能等待流程自己完成。

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

全表扫描对server层的影响

实际上，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：
\1. 获取一行，写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是
16k。
\2. 重复获取行，直到net_buffer写满，调用网络接口发出去。
\3. 如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。
\4. 如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send
buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
这个过程对应的流程图如下所示。
在这里插入图片描述

MySQL是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致MySQL服务端由于结果发不出去，这个事务的执行时间变长

对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，我都建议你使用mysql_store_result这个接口，直接把查询结果保存到本地内存。

仅当一个线程处于“等待客户端接收结果”的状态，才会显示"Sending to client"；而如
果显示成“Sending data”，它的意思只是“正在执行”。

由于有WAL机制，当事务提交的时候，磁盘上的数据页是旧的，那如果这时候马上有一个查询要来读这个数据页，是不是要马上把redo log应用到数据页呢？
答案是不需要。因为这时候内存数据页的结果是最新的，直接读内存页就可以了。你看，这时候查询根本不需要读磁盘，直接从内存拿结果，速度是很快的。所以说， Buffer Pool还有加速查询的作用

InnoDB Buffer Pool的大小是由参数 innodb_buffer_pool_size确定的，一般建议设置成可用物理内存的60%~80%。
在这里插入图片描述

在InnoDB实现上，按照5:3的比例把整个LRU链表分成了young区域和old区域。图中LRU_old指
向的就是old区域的第一个位置，是整个链表的5/8处。也就是说，靠近链表头部的5/8是young区
域，靠近链表尾部的3/8是old区域。

改进后的LRU算法执行流程变成了下面这样。
\1. 图7中状态1，要访问数据页P3，由于P3在young区域，因此和优化前的LRU算法一样，将
其移到链表头部，变成状态2。
\2. 之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页Pm，但是新
插入的数据页Px，是放在LRU_old处。
\3. 处于old区域的数据页，每次被访问的时候都要做下面这个判断：
若这个数据页在LRU链表中存在的时间超过了1秒，就把它移动到链表头部；
如果这个数据页在LRU链表中存在的时间短于1秒，位置保持不变。 1秒这个时间，是由
参数innodb_old_blocks_time控制的。其默认值是1000，单位毫秒。
这个策略，就是为了处理类似全表扫描的操作量身定制的。还是以刚刚的扫描200G的历史数据
表为例，我们看看改进后的LRU算法的操作逻辑：
\1. 扫描过程中，需要新插入的数据页，都被放到old区域;
\2. 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页
第一次被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域；
\3. 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到
链表头部（也就是young区域），很快就会被淘汰出去。
可以看到，这个策略最大的收益，就是在扫描这个大表的过程中，虽然也用到了Buffer Pool，但
是对young区域完全没有影响，从而保证了Buffer Pool响应正常业务的查询命中率。

到底可不可以使用join？

我们来看一下这个语句：

select * from t1 straight_join t2 on (t1.a=t2.a);

如果直接使用join语句， MySQL优化器可能会选择表t1或t2作为驱动表，这样会影响我们分析SQL语句的执行过程。所以，为了便于分析执行过程中的性能问题，我改用straight_join让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。在这个语句里， t1 是驱动表， t2是被驱动表
在这里插入图片描述

第一个问题：能不能使用join语句？
\1. 如果可以使用IndexNested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是
没问题的；
\2. 如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这
样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。
所以你在判断要不要使用join语句时，就是看explain结果里面， Extra字段里面有没有出现“Block
Nested Loop”字样。
第二个问题是：如果要使用join，应该选择大表做驱动表还是选择小表做驱动表？
\1. 如果是IndexNested-Loop Join算法，应该选择小表做驱动表；
\2. 如果是Block Nested-Loop Join算法：
在join_buffer_size足够大的时候，是一样的；
在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。
所以，这个问题的结论就是，总是应该使用小表做驱动表。

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

小结
今天，我和你介绍了MySQL执行join语句的两种可能算法，这两种算法是由能否使用被驱动表的
索引决定的。而能否用上被驱动表的索引，对join语句的性能影响很大。
通过对IndexNested-Loop Join和Block Nested-Loop Join两个算法执行过程的分析，我们也得到
了文章开头两个问题的答案：
\1. 如果可以使用被驱动表的索引， join语句还是有其优势的；
\2. 不能使用被驱动表的索引，只能使用Block Nested-Loop Join算法，这样的语句就尽量不要使用；
\3. 在使用join的时候，应该让小表做驱动表

join语句怎么优化？

Multi-Range Read优化

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

Batched Key Access

那么，这个BKA算法到底要怎么启用呢？
如果要使用BKA优化算法的话，你需要在执行SQL语句之前，先设置

set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’;

其中，前两个参数的作用是要启用MRR。这么做的原因是， BKA算法的优化要依赖于MRR。

BNL算法的性能问题

大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也就结束了。但是，
对Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。
为了减少这种影响，你可以考虑增大join_buffer_size的值，减少对被驱动表的扫描次数。
也就是说， BNL算法对系统的影响主要包括三个方面：
\1. 可能会多次扫描被驱动表，占用磁盘IO资源；
\2. 判断join条件需要执行M*N次对比（M、 N分别是两张表的行数），如果是大表就会占用非常
多的CPU资源；
\3. 可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。

我们执行语句之前，需要通过理论分析和查看explain结果的方式，确认是否要使用BNL算法。如
果确认优化器会使用BNL算法，就需要做优化。优化的常见做法是，给被驱动表的join字段加上
索引，把BNL算法转成BKA算法

在表t2的字段b上创建索引会浪费资源，但是不创建索引的话这个语句的等值条件要判断10亿
次，想想也是浪费。那么，有没有两全其美的办法呢？
这时候，我们可以考虑使用临时表。使用临时表的大致思路是：
\1. 把表t2中满足条件的数据放在临时表tmp_t中；
\2. 为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；
\3. 让表t1和tmp_t做join操作。

总体来看，不论是在原表上加索引，还是用有索引的临时表，我们的思路都是让join语句能够用上被驱动表上的索引，来触发BKA算法，提升查询性能

扩展-hash join

看到这里你可能发现了，其实上面计算10亿次那个操作，看上去有点儿傻。如果join_buffer里面
维护的不是一个无序数组，而是一个哈希表的话，那么就不是10亿次判断，而是100万次hash查
找。这样的话，整条语句的执行速度就快多了吧？
确实如此。
这，也正是MySQL的优化器和执行器一直被诟病的一个原因：不支持哈希join。并且， MySQL官
方的roadmap，也是迟迟没有把这个优化排上议程。
实际上，这个优化思路，我们可以自己实现在业务端。实现流程大致如下：
\1. select * from t1;取得表t1的全部1000行数据，在业务端存入一个hash结构，比如C++里的
set、 PHP的dict这样的数据结构。
\2. select * from t2 where b>=1 and b<=2000; 获取表t2中满足条件的2000行数据。
\3. 把这2000行数据，一行一行地取到业务端，到hash结构的数据表中寻找匹配的数据。满足
匹配的条件的这行数据，就作为结果集的一行。
理论上，这个过程会比临时表方案的执行速度还要快一些

小结
今天，我和你分享了IndexNested-Loop Join（NLJ）和Block Nested-Loop Join（BNL）的优化
方法。
在这些优化方法中：
\1. BKA优化是MySQL已经内置支持的，建议你默认使用；
\2. BNL算法效率低，建议你都尽量转成BKA算法。优化的方向就是给被驱动表的关联字段加上
索引；
\3. 基于临时表的改进方案，对于能够提前过滤出小数据的join语句来说，效果还是很好的；
\4. MySQL目前的版本还不支持hash join，但你可以配合应用端自己模拟出来，理论上效果要好
于临时表的方案

为什么临时表可以重名？

-- 使用临时表
create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

内存表和临时表区别

内存表，指的是使用Memory引擎的表，建表语法是create table …engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。
而临时表，可以使用各种引擎类型。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用Memory引擎
在这里插入图片描述
可以看到，临时表在使用上有以下几个特点：
\1. 建表语法是create temporarytable …。
\2. 一个临时表只能被创建它的session访问，对其他线程不可见。所以，图中session A创建的
临时表t，对于session B就是不可见的。
\3. 临时表可以与普通表同名。
\4. session A内有同名的临时表和普通表的时候， show create语句，以及增删改查语句访问的
是临时表。
\5. show tables命令不显示临时表。
由于临时表只能被创建它的session访问，所以在这个session结束的时候，会自动删除临时表。

也正是由于这个特性，临时表就特别适合我们文章开头的join优化这种场景。为什么呢？
原因主要包括以下两个方面：
\1. 不同session的临时表是可以重名的，如果有多个session同时执行join优化，不需要担心表名重复导致建表失败的问题。
\2. 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作

在实际应用中，临时表一般用于处理比较复杂的计算逻辑。由于临时表是每个线程自己可见的，
所以不需要考虑多个线程执行同一个处理逻辑时，临时表的重名问题。在线程退出的时候，临时
表也能自动删除，省去了收尾和异常处理的工作。
在binlog_format='row’的时候，临时表的操作不记录到binlog中，也省去了不少麻烦，这也可以
成为你选择binlog_format时的一个考虑因素。

为什么临时表可以重名

MySQL要给这个InnoDB表创建一个frm文件保存表结构定义，还要有地方保
存表数据。
这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程id}{线程id}
序列号”。你可以使用select @@tmpdir命令，来显示实例的临时文件目录。
而关于表中数据的存放方式，在不同的MySQL版本中有着不同的处理方式：
在5.6以及之前的版本里， MySQL会在临时文件目录下创建一个相同前缀、以.ibd为后缀的文
件，用来存放数据文件；
而从 5.7版本开始， MySQL引入了一个临时文件表空间，专门用来存放临时文件的数据。因
此，我们就不需要再创建ibd文件了

MySQL维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都
对应一个table_def_key。
一个普通表的table_def_key的值是由“库名+表名”得到的，所以如果你要在同一个库下创建两
个同名的普通表，创建第二个表的过程中就会发现table_def_key已经存在了。
而对于临时表， table_def_key在“库名+表名”基础上，又加入了“server_id+thread_id”。

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

union
在这里插入图片描述
group by 执行流程

group by 优化方法 --索引

group by优化方法 --直接排序

那么，我们就会想了， MySQL有没有让我们直接走磁盘临时表的方法呢？
答案是，有的。
在group by语句中加入SQL_BIG_RESULT这个提示（hint），就可以告诉优化器：这个语句涉
及的数据量很大，请直接用磁盘临时表。
MySQL的优化器一看，磁盘临时表是B+树存储，存储效率不如数组来得高。所以，既然你告诉
我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。
因此，下面这个语句
的执行流程就是这样的：
\1. 初始化sort_buffer，确定放入一个整型字段，记为m；
\2. 扫描表t1的索引a，依次取出里面的id值, 将 id%100的值存入sort_buffer中；
\3. 扫描完成后，对sort_buffer的字段m做排序（如果sort_buffer内存不够用，就会利用磁盘临
时文件辅助排序）；
select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

MySQL什么时候会使用内部临时表？
\1. 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要
额外的内存，来保存中间结果；
\2. join_buffer是无序数组， sort_buffer是有序数组，临时表是二维表结构；
\3. 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中， union
需要用到唯一索引约束， group by还需要用到另外一个字段来存累积计数。

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

内存表的数据组织结构

与InnoDB引擎不同， Memory引擎的数据和索引是分开的。
在这里插入图片描述

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

从中我们可以看出，这两个引擎的一些典型不同：
\1. InnoDB表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
\2. 当数据文件有空洞的时候， InnoDB表在插入新数据的时候，为了保证数据有序性，只能在
固定的位置写入新值，而内存表找到空位就可以插入新值；
\3. 数据位置发生变化的时候， InnoDB表只需要修改主键索引，而内存表需要修改所有索引；
\4. InnoDB表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索
引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
\5. InnoDB支持变长数据类型，不同记录的长度可能不同；内存表不支持Blob 和 Text字段，并
且即使定义了varchar(N)，实际也当作char(N)，也就是固定长度字符串来存储，因此内存表
的每行数据长度相同。

其实，一般在我们的印象中，内存表的优势是速度快，其中的一个原因就是Memory引擎支持
hash索引。当然，更重要的原因是，内存表的所有数据都保存在内存，而内存的读写速度总是
比磁盘快。
但是，接下来我要跟你说明，为什么我不建议你在生产环境上使用内存表。这里的原因主要包括
两个方面：
\1. 锁粒度问题；
\2. 数据持久化问题。

内存表的锁

内存表不支持行锁，只支持表锁。因此，一张表只要有更新，就会堵住其他所有在这个表上的读写操作

数据持久性问题

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

在备库重启的时候，备库binlog里的delete语句就会传到主库，然后把主库内存表的内容删除。
这样你在使用的时候就会发现，主库的内存表数据突然被清空了。
基于上面的分析，你可以看到，内存表并不适合在生产环境上作为普通数据表使用。
有同学会说，但是内存表执行速度快呀。这个问题，其实你可以这么分析：
\1. 如果你的表更新量大，那么并发度是一个很重要的参考指标， InnoDB支持行锁，并发度比
内存表好；
\2. 能放到内存表的数据量都不大。如果你考虑的是读的性能，一个读QPS很高并且数据量不大
的表，即使是使用InnoDB，数据也是都会缓存在InnoDB Buffer Pool里的。因此，使用
InnoDB表的读性能也不会差。
所以，我建议你把普通内存表都用InnoDB表来代替

数据量可控，不会耗费过多内存的情
况下，你可以考虑使用内存表。
内存临时表刚好可以无视内存表的两个不足，主要是下面的三个原因：
\1. 临时表不会被其他线程访问，没有并发性的问题；
\2. 临时表重启后也是需要删除的，清空数据这个问题不存在；
\3. 备库的临时表也不会影响主库的用户线程。

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

什么情况下自增主键会出现 “空洞”？

不同的引擎对于自增值的保存策略不同。
MyISAM引擎的自增值保存在数据文件中。
InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值
持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的
值”，具体情况是：
在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一
次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的
自增值。
举例来说，如果一个表当前数据行里最大的id是10， AUTO_INCREMENT=11。这时候，
我们删除id=10的行， AUTO_INCREMENT还是11。但如果马上重启实例，重启后这个表
的AUTO_INCREMENT就会变成10。
也就是说， MySQL重启可能会修改一个表的AUTO_INCREMENT的值。
在MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复
重启之前的值。

自增值修改机制
在MySQL里面，如果字段id被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的
行为如下：
\1. 如果插入数据时id字段指定为0、 null 或未指定值，那么就把这个表当前的
AUTO_INCREMENT值填到自增字段；
\2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。
根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入
的值是X，当前的自增值是Y。
\1. 如果X<Y，那么这个表的自增值不变；
\2. 如果X≥Y，就需要把当前自增值修改为新的自增值。
新的自增值生成算法是：从auto_increment_offset开始，以auto_increment_increment为步
长，持续叠加，直到找到第一个大于X的值，作为新的自增值。
其中， auto_increment_offset 和 auto_increment_increment是两个系统参数，分别用来表示自
增的初始值和步长，默认值都是1。
当auto_increment_offset和auto_increment_increment都是1的时候，新的自增值生成逻辑很简
单，就是：
\1. 如果准备插入的值>=当前自增值，新的自增值就是“准备插入的值+1”；
\2. 否则，自增值不变。

自增值的修改时机

唯一键冲突是导致自增主键id不连续的第一种原因。

事务回滚也会产生类似的现象，这就是第二种原因

自增值为什么不能回退

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增id，肯
定要加锁，然后顺序申请。
\1. 假设事务A申请到了id=2，事务B申请到id=3，那么这时候表t的自增值是4，之后继续执
行。
\2. 事务B正确提交了，但事务A出现了唯一键冲突。
\3. 如果允许事务A把自增id回退，也就是把表t的当前自增值改回2，那么就会出现这样的情
况：表里面已经有id=3的行，而当前的自增id值是2。
\4. 接下来，继续执行的其他事务就会申请到id=2，然后再申请到id=3。这时，就会出现插入语
句报错“主键冲突”。

而为了解决这个主键冲突，有两种方法：
\1. 每次申请id之前，先判断表里面是否已经存在这个id。如果存在，就跳过这个id。但是，这
个方法的成本很高。因为，本来申请id是一个很快的操作，现在还要再去主键索引树上判断
id是否存在。
\2. 把自增id的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增
id。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降。

可见，这两个方法都会导致性能问题。造成这些麻烦的罪魁祸首，就是我们假设的这个“允许自
增id回退”的前提导致的。
因此， InnoDB放弃了这个设计，语句执行失败也不回退自增id。也正是因为这样，所以才只保
证了自增id是递增的，但不保证是连续的

自增锁的优化

可以看到，自增id锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请。其实，在MySQL 5.1版本之前，并不是这样的。

在MySQL 5.0版本的时候，自增锁的范围是语句级别。也就是说，如果一个语句申请了一个表自
增锁，这个锁会等语句执行结束以后才释放。显然，这样设计会影响并发度。
MySQL 5.1.22版本引入了一个新策略，新增参数innodb_autoinc_lock_mode，默认值是1。
\1. 这个参数的值被设置为0时，表示采用之前MySQL 5.0版本的策略，即语句执行结束后才释
放锁；
\2. 这个参数的值被设置为1时：
普通insert语句，自增锁在申请之后就马上释放；
类似insert …select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
\3. 这个参数的值被设置为2时，所有的申请自增主键的动作都是申请后就释放锁

因此，对于批量插入数据的语句， MySQL有一个批量申请自增id的策略：
\1. 语句执行过程中，第一次申请自增id，会分配1个；
\2. 1个用完以后，这个语句第二次申请自增id，会分配2个；
\3. 2个用完以后，还是这个语句，第三次申请自增id，会分配4个；
\4. 依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍

批量插入数据时，申请的id，会是前一次的2倍，也可能导致id不连续

举个例子，我们一起看看下面的这个语句序列：

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

insert…select，实际上往表t2中插入了4行数据。但是，这四行数据是分三次申请的自增id，第一
次申请到了id=1，第二次被分配了id=2和id=3，第三次被分配到id=4到id=7。
由于这条语句实际只用上了4个id，所以id=5到id=7就被浪费掉了。之后，再执行insert into t2
values(null, 5,5)，实际上插入的数据就是（8,5,5)。
这是主键id出现自增id不连续的第三种原因。

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

insert … select 语句

正常情况下， grant命令之后，没有必要跟着执行flush privileges命令。

flush privileges使用场景

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

要不要使用分区表？

可以看到，这个表包含了一个.frm文件和4个.ibd文件，每个分区对应一个.ibd文件。也就是说：
对于引擎层来说，这是4个表；
对于Server层来说，这是1个表

分区表的引擎层行为

分区表和手工分表，一个是由server层来决定使用哪个分区，一个是由应用层代码来决定使用哪
个分表。因此，从引擎层看，这两种方式也是没有差别的

分区策略
每当第一次访问一个分区表的时候， MySQL需要把所有的分区都访问一遍。一个典型的报错情
况是这样的：如果一个分区表的分区很多，比如超过了1000个，而MySQL启动的时
候， open_files_limit参数使用的是默认值1024，那么就会在访问这个表的时候，由于需要打开所
有的文件，导致打开表文件的个数超过了上限而报错

MyISAM分区表使用的分区策略，我们称为通用分区策略（generic partitioning），每次访问分
区都由server层控制。通用分区策略，是MySQL一开始支持分区表的时候就存在的代码，在文件
管理、表管理的实现上很粗糙，因此有比较严重的性能问题

从MySQL 5.7.9开始， InnoDB引擎引入了本地分区策略（native partitioning）。这个策略是在
InnoDB内部自己管理打开分区的行为。
MySQL从5.7.17开始，将MyISAM分区表标记为即将弃用(deprecated)，意思是“从这个版本开始
不建议这么使用，请使用替代方案。在将来的版本中会废弃这个功能”。
从MySQL 8.0版本开始，就不允许创建MyISAM分区表了，只允许创建已经实现了本地分区策略
的引擎。目前来看，只有InnoDB和NDB这两个引擎支持了本地分区策略

到这里我们小结一下：
\1. MySQL在第一次打开分区表的时候，需要访问所有的分区；
\2. 在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；
\3. 在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必
要的分区

分区表的应用场景
分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简
洁。还有，分区表可以很方便的清理历史数据。

如果一项业务跑的时间足够长，往往就会有根据时间删除历史数据的需求。这时候，按照时间分
区的分区表，就可以直接通过alter table t drop partition …这个语法删掉分区，从而删掉过期的历
史数据。
这个alter table t drop partition …操作是直接删除分区文件，效果跟drop普通表类似。与使用
delete语句删除数据相比，优势是速度快、对系统影响小

InnoDB系统自增row_id
如果你创建的InnoDB表没有指定主键，那么InnoDB会给你创建一个不可见的，长度为6个字节
的row_id。 InnoDB维护了一个全局的dict_sys.row_id值，所有无主键的InnoDB表，每插入一行
数据，都将当前的dict_sys.row_id值作为要插入数据的row_id，然后把dict_sys.row_id的值加1。

MySQL内部维护了一个全局变量global_query_id，每次执行语句的时候将它赋值给Query_id，
然后给这个变量加1。如果当前语句是这个事务执行的第一条语句，那么MySQL还会同时把
Query_id赋值给这个事务的Xid。
而global_query_id是一个纯内存变量，重启之后就清零了。所以你就知道了，在同一个数据库实
例中，不同事务的Xid也是有可能相同的。
但是MySQL重启之后会重新生成新的binlog文件，这就保证了，同一个binlog文件里， Xid一定是
惟一的。
虽然MySQL重启不会导致同一个binlog里面出现两个相同的Xid，但是如果global_query_id达到
上限后，就会继续从0开始计数。从理论上讲，还是就会出现同一个binlog里面出现相同Xid的场
景

Innodb trx_id
Xid和InnoDB的trx_id是两个容易混淆的概念。
Xid是由server层维护的。 InnoDB内部使用Xid，就是为了能够在InnoDB事务和server之间做关
联。但是， InnoDB自己的trx_id，是另外维护的。

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