MySQL实战笔记

工程经验读书笔记数据库

读书笔记

发布日期: 2024-04-29

更新日期: 2024-06-29

文章字数: 35.7k

阅读时长: 128 分

阅读次数:

概述

数据库是系统的核心，如果它缓慢那么所有的业务都会受它影响，一个系统的性能很少能超过核心数据库的性能上限。

01 | 基础架构：一条SQL查询语句是如何执行的 ?

MySQL基本架构示意图：

MySQL可以分为Server层和存储引擎层两部分。

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

连接器

第一步，会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

长连接

数据库中长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

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

解决方法：

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
MySQL 5.7及以上，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。 key是查询的语句， value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。

查询缓存往往弊大于利，大多数时候建议不要使用。

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

分析器

如果没有命中查询缓存，就要开始真正执行语句了。分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句， MySQL需要识别出里面的字符串分别是什么，代表什么。做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

优化器

在开始执行之前，还要先经过优化器的处理。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

02 | 日志系统：一条SQL更新语句是如何执行的？

与查询流程不一样的是，更新流程还涉及两个重要的日志模块： redo log（重做日志）和 binlog（归档日志）。当有一条记录需要更新的时候， InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。然后Server层更新binlog,刷到磁盘。同时， InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面。

Redo log

文中举了《孔乙己》粉板和账本配合的故事，即对应MySQL中的WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

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

这里来介绍下redo log：

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这块“粉板”总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

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

补充

redo log其实是由两部分组成的，一部分是重做日志缓冲（redo log buffer），这部分是确实存在于内存中的；而另一部分则是重做日志文件（redo log file），这部分是存储在磁盘中的。当事务提交后，所有修改的信息会先存到内存中的重做日志缓冲，然后再被写入到磁盘的重做日志文件中。所以，我们不能简单地说redo log是在内存还是在磁盘，它其实是同时在内存和磁盘中都有存在的部分

Binlog

另外一个日志就是Binlog啦，先看看和redo log有啥差异。

思考：为啥要有这个binlog日志呢，我认为是用于备份用的，即主从备份，分布式raft那一套。而redo log是用于优化效率用的。

有以下三点不同

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

接下来，看看下面这条update SQL的执行流程

update T set c=c+1 where ID=2;

执行流程图

解释：

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

两阶段提交

这个两阶段提交在分布式也极为常见的概念，2阶段提交的2阶段分别是提交请求阶段和提交执行阶段。

怎样让数据库恢复到半个月内任意一秒的状态？

首先，找到最近的一次全量备份，从这个备份恢复到临时库。
然后，从备份的时间点开始，将备份的binlog依次取出来，重放到误删表之前的那个时刻。

为什么需要2阶段提交呢？

这里通过反证明法说明。

由于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来恢复临时库的话，恢复出来的这一行c的值就是0，与原库的值不同。

先写binlog后写redo log。 如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

简单说， redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

03 | 事务隔离：为什么你改了我还看不见？

简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中， 事务支持是在引擎层实现的。你现在知道， MySQL是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如MySQL原生的MyISAM引擎就不支持事务，这也是MyISAM被InnoDB取代的重要原因之一。

隔离性与隔离级别

提到事务，脑海里肯定有ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。这里重点说隔离性。

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirtyread）、不可重复读（non repeatable read）、幻读（ phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。SQL标准的事务隔离级别包括：读未提交（ read uncommitted）、读提交（read committed）、可重复读（ repeatable read）和串行化（ serializable ）。

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

假设数据表T中只有一列，其中一行的值为1，下面是按照时间顺序执行两个事务的行为。

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

若隔离级别是“读未提交”，则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被A看到了。因此， V2、 V3也都是2。
若隔离级别是“读提交”，则V1是1， V2的值是2。事务B的更新在提交后才能被A看到。所以，V3的值也是2。
若隔离级别是“可重复读”，则V1、 V2是1， V3是2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
若隔离级别是“串行化”，则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。所以从A的角度看， V1、 V2值是1， V3的值是2。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是，
“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念。
“串行化”隔离级别下直接用加锁的方式来避免并行访问。

Note：Oracle数据库的默认隔离级别是“读提交”，MySQL数据库的默认隔离级别是可重复读。因此，从Oracle迁移到MySQL的应用，为保证数据库隔离级别的一致，一定要将MySQL的隔离级别设置为“读提交”。

事务隔离的实现

可重复读：在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。假设一个值从1被按顺序改成了2、 3、 4，在回滚日志里面就会有类似下面的记录。

为什么建议你尽量不要使用长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
长事务还占用锁资源，也可能拖垮整个库。

事务的启动方式

显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback。
set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接。因此，建议总是使用set autocommit=1, 通过显式语句的方式来启动事务。

04 | 深入浅出索引（上）

MySQL索引经典15问！

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

索引的常见模型

哈希表、有序数组和搜索树。

哈希表

哈希表这种结构适用于只有等值查询的场景。

有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀。但是有序数组索引只适用于静态存储引擎。

搜索树

InnoDB的索引模型

InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的， InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。
假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。
这个表的建表语句是：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、 (200,2)、 (300,3)、 (500,5)和(600,6)，两棵树的示例示意图如下。

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

基于主键索引和普通索引的查询有什么区别？

如果语句是select * from Twhere ID=500，即主键查询方式，则只需要搜索ID这棵B+树；

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

索引维护

主要是由于页分裂和合并。

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。

自增主键的插入数据模式，符合递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

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

如何避免长事务对业务的影响 ？

首先，从应用开发端来看：

确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL的general_log开起来，然后随便跑一个业务逻辑，通过general_log的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成1。
确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用begin/commit框起来。我见过有些是业务并没有这个需要，但是也把好几个select语句放到了事务中。这种只读事务可以去掉。
业务连接数据库的时候，根据业务本身的预估，通过SETMAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后续的文章中会提到这类案例）

其次， 从数据库端来看

监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill；
Percona的pt-kill这个工具不错，推荐使用；
在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题；
如果使用的是MySQL 5.6或者更新版本，把innodb_undo_tablespaces设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

05 | 深入浅出索引（下）

执行

select * from Twhere k between 3 and 5

需要执行几次树的搜索操作，会扫描多少行？

表的初始化语句

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

我们一起来看看这条SQL查询语句的执行流程：

在k索引树上找到k=3的记录，取得 ID = 300；
再到ID索引树查到ID=300对应的R3；
在k索引树取下一个值k=5，取得ID=500；
再回到ID索引树查到ID=500对应的R4；
在k索引树取下一个值k=6，不满足条件，循环结束。

回到主键索引树搜索的过程，我们称为回表。

覆盖索引

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

最左前缀原则

如果为每一种查询都设计一个索引，索引是不是太多了。B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

索引下推

什么是索引下推？拿到索引之后根据where条件看看能不能再进行过滤。

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

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

区别是， InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。在我们的这个例子中，只需要对ID4、 ID5这两条记录回表取数据判断，就只需要回表2次。

06 | 全局锁和表锁：给表加个字段怎么有这么多阻碍？

数据库锁设计的初衷是处理并发问题。

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

全局锁

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

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

但是让整库都只读，听上去就很危险：
如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

这个备份结果里，用户A的数据状态是“账户余额没扣，但是用户课程表里面已经多了一门课”。如果后面用这个备份来恢复数据的话，用户A就发现，自己赚了。但是，如果备份表的顺序反过来，先备份用户课程表再备份账户余额表，又可能会出现什么结果【狗头】。

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock， MDL) 。

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

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

MDL读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

session A先启动，对表t加一个MDL读锁。
由于session B需要的也是MDL读锁。
session C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞。
问题来了，之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。等于这个表现在完全不可读写了。

上面的例子如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session再请求的话，这个库的线程很快就会爆满。

事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

pro1:如何安全地给小表加字段？

solution1:首先我们要解决长事务，事务不提交，就会一直占着MDL锁。在MySQL的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。

pro2:你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？

这时候kill可能未必管用，因为新的请求马上就来了。比较理想的机制是，在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

07 | 行锁功过：怎么减少行锁对性能的影响？

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁。 InnoDB是支持行锁的。MyISAM引擎就不支持行锁。

从两阶段锁说起

实际上事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行。在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

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

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

死锁和死锁检测

这时候，事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。

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

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
- 在InnoDB中，innodb_lock_wait_timeout的默认值是50s。意味着当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。如果我们把这个时间设置成一个很小的值，比如1s，会伤害到普通的锁等待。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

背景：

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

那如果是我们上面说到的所有事务都要更新同一行的场景呢？

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的CPU资源。因此，你就会看到CPU利用率很高，但是每秒却执行不了几个事务。

根据上面的分析，我们来讨论一下，怎么解决由这种热点行更新导致的性能问题呢？

问题：

问题的关键在于死锁检测要耗费大量的CPU资源。

解决方法：

如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。
控制并发度。比如同一行同时最多只有10个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。

08 | 事务到底是隔离的还是不隔离的？

这部分主要是MVCC原理，可以到B站上学习下。

网上看到不错的作图，贴在这里啦~

好文推荐

看一遍就懂：MVCC原理详解

如果是可重复读隔离级别，事务T启动的时候会创建一个视图read-view，之后事务T执行期间，即使有其他事务修改了数据，事务T看到的仍然跟在启动时看到的一样。

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction withconsistent snapshot 这个命令。

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

“快照”在MVCC里是怎么工作的？

InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的row trx_id。

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

InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。 “活跃”指的就是，启动了但还没提交。

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（ current read）。

InnoDB的行数据有多个版本，每个数据版本有自己的row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据row trx_id和一致性视图确定数据版本的可见性。

￮对于可重复读，查询只承认在事务启动前就已经提交完成的数据；

￮对于读提交，查询只承认在语句启动前就已经提交完成的数据；

￮而当前读，总是读取已经提交完成的最新版本。

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

从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？

查询过程

假设，执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。

InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。

因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

更新过程

先介绍一下change buffer。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

pro:什么条件下可以使用change buffer呢？

ans：唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

介绍完change buffer,来说说更新过程，分为2个case。

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

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

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

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

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

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer使用场景

先来一个问题：普通索引的所有场景，使用change buffer都可以起到加速作用吗？

心中有个最初的原则：change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。

有好的场景当然也有不好的场景。

不好的场景就是假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。

索引选择和实践

普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议你尽量选择普通索引

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在其他情况下，change buffer都能提升更新性能。

change buffer 和 redo log

接下来其实主要就是区分redo log和change buffer。

现在，我们要在表上执行这个插入语句：

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

这里，我们假设当前k索引树的状态，查找到位置后，k1所在的数据页在内存(InnoDB buffer pool)中，k2所在的数据页不在内存中。如图2所示是带change buffer的更新状态图。

分析这条更新语句，你会发现它涉及了四个部分：内存、redo log（ib_log_fileX）、数据表空间（t.ibd）、系统表空间（ibdata1）。

这条更新语句做了如下的操作（按照图中的数字顺序）：

Page 1在内存中，直接更新内存；
Page 2没有在内存中，就在内存的change buffer区域，记录下“我要往Page 2插入一行”这个信息
将上述两个动作记入redo log中（图中3和4）。

做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间。

现在要执行 select * from t where k in (k1, k2)。

读Page 1的时候，直接从内存返回。有几位同学在前面文章的评论中问到，WAL之后如果读数据，是不是一定要读盘，是不是一定要从redo log里面把数据更新以后才可以返回？其实是不用的。你可以看一下图3的这个状态，虽然磁盘上还是之前的数据，但是这里直接从内存返回结果，结果是正确的。
要读Page 2的时候，需要把Page 2从磁盘读入内存中，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果。

可以看到，直到需要读Page 2的时候，这个数据页才会被读入内存。

所以，如果要简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

来个问题：如果某次写入使用了change buffer机制，之后主机异常重启，是否会丢失change buffer和数据。

这个问题的答案是不会丢失。虽然是只更新内存，但是在事务提交的时候，我们把change buffer的操作也记录到redo log里了，所以崩溃恢复的时候，change buffer也能找回来。

10 | MySQL为什么有时候会选错索引？

【优化器工作原理】

一种方法是，像我们第一个例子一样，采用force index强行选择一个索引。

第二种方法就是，我们可以考虑修改语句，引导MySQL使用我们期望的索引。比如，在这个例子里，显然把“order byb limit 1” 改成 “order byb,a limit 1” ，语义的逻辑是相同的。

第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

11 | 怎么给字符串字段加索引？

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

第一个语句创建的index1索引里面，包含了每个记录的整个字符串；而第二个语句创建的index2索引里面，对于每个记录都是只取前6个字节。

select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用的是index1（即email整个字符串的索引结构），执行顺序是这样的：

从index1索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得ID2的值；
到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；
取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=’zhangssxyz@xxx.com’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是index2（即email(6)索引结构），执行顺序是这样的：

从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；
到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；
重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

在这个过程中，要回主键索引取4次数据，也就是扫描了4行。

通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。

结论：使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

来个问题：当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？

实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

常用方法

mysql> select count(distinct email) as L from SUser;
-----
mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

前缀索引对覆盖索引的影响

先来看看这个SQL语句：

select id,email from SUser where email='zhangssxyz@xxx.com';

与前面例子中的SQL语句

select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用index1（即email整个字符串的索引结构）的话，可以利用覆盖索引，从index1查到结果后直接就返回了，不需要回到ID索引再去查一次。而如果使用index2（即email(6)索引结构）的话，就不得不回到ID索引再去判断email字段的值。

即使你将index2的定义修改为email(18)的前缀索引，这时候虽然index2已经包含了所有的信息，但InnoDB还是要回到id索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

其他方式

第一种方式是使用倒序存储。

第二种方式是使用hash字段。

首先，它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在[ID_X, ID_Y]的所有市民了。同样地， hash字段的方式也只能支持等值查询。

它们的区别，主要体现在以下三个方面：

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

总结

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

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

【数据库代码】

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

什么时候flush？

1：是InnoDB的redo log写满了。这时候系统会停止所有更新操作，把:checkpoint往前推进， redo log留出空间可以继续写。

checkpoint可不是随便往前修改一下位置就可以的。比如图中，把checkpoint位置从CP推进到CP’，就需要将两个点之间的日志（浅绿色部分），对应的所有脏页都flush到磁盘上。之后，图中从write pos到CP’之间就是可以再写入的redo log的区域。

2：系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

3：MySQL认为系统“空闲”的时候。

4：MySQL正常关闭的情况。这时候， MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

第一种是“redo log写满了，要flush脏页”，这种情况是InnoDB要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。

第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB用缓冲池（ buffer pool）管理内存，缓冲池中的内存页有三种状态：第一种是，还没有使用的；第二种是，使用了并且是干净页；第三种是，使用了并且是脏页。

InnoDB的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是redo log写盘速度。 InnoDB会根据这两个因素先单独算出两个数字。

InnoDB每次写入的日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值，我们假设为N。 InnoDB会根据这个N算出一个范围在0到100之间的数字，这个计算公式可以记为F2(N)。 F2(N)算法比较复杂，你只要知道N越大，算出来的值越大就好了。然后，根据上述算得的F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

现在你知道了， InnoDB会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用IO资源并可能影响到了你的更新语句

而MySQL中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷

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

【B+树代码】

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

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

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

page A满了，再插入一个ID是550的数据时，就不得不再申请一个新的页面page B来保存数据了。页分裂完成后， page A的末尾就留下了空洞（注意：实际上，可能不止1个记录的位置是空洞）。

重建表

你可以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A里读出来再插入到表B中。由于表B是新建的表，所以表A主键索引上的空洞，在表B中就都不存在了。显然地，表B的主键索引更紧凑，数据页的利用率也更高。如果我们把表B作为临时表，数据从表A导入表B的操作完成后，用表B替换A，从效果上看，就起到了收缩表A空间的作用。

引入了Online DDL之后，重建表的流程：

建立一个临时文件，扫描表A主键的所有数据页；
用数据页中表A的记录生成B+树，存储到临时文件中；
生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中state2的状态；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中state3的状态；
用临时文件替换表A的数据文件。

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

【不同引擎工作以及常见count】

count(*)的实现方式

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

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

那为什么InnoDB不跟MyISAM一样，也把数字存起来呢？ 这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因， InnoDB表“应该返回多少行”也是不确定的。这里，我用一个算count(*)的例子来为你解释一下。

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

不同的count用法

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

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

对于count(字段)来说：

如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；
如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

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

结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count()，所以我建议你，尽量使用count()。

15 | 答疑文章（一）：日志和索引相关问题

这个部分好好看看平台文章。

日志相关问题

在两阶段提交的不同瞬间，MySQL如果发生异常重启，是怎么保证数据完整性的？

崩溃恢复时的判断规则。

如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；
如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整：
a. 如果是，则提交事务；
b. 否则，回滚事务。

MySQL怎么知道binlog是完整的?

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

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

另外，在MySQL 5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容的正确性。对于binlog日志由于磁盘原因，可能会在日志中间出错的情况，MySQL可以通过校验checksum的结果来发现。所以，MySQL还是有办法验证事务binlog的完整性的。

redo log 和 binlog是怎么关联起来的?

回答：它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log：
如果碰到既有prepare、又有commit的redo log，就直接提交；
如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务。

redo log一般设置多大？

redo log太小的话，会导致很快就被写满，然后不得不强行刷redo log，这样WAL机制的能力就发挥不出来了。所以，如果是现在常见的几个TB的磁盘的话，就不要太小气了，直接将redo log设置为4个文件、每个文件1GB吧。

redo log buffer是什么？是先修改内存，还是先写redo log文件？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin;
insert into t1 ...
insert into t2 ...
commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没commit的时候就直接写到redo log文件里。

所以，redo log buffer就是一块内存，用来先存redo日志的。也就是说，在执行第一个insert的时候，数据的内存被修改了，redo log buffer也写入了日志。

但是，真正把日志写到redo log文件（文件名是 ib_logfile+数字），是在执行commit语句的时候做的。

16 | “order by”是怎么工作的？

【流程及其优化】

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

全字段排序

为避免全表扫描，我们需要在city字段加上索引。在city字段上创建索引之后，我们用explain命令来看看这个语句的执行情况。

Extra这个字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。

通常情况下，这个语句执行流程如下所示：

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

这里，我们要熟悉OPTIMIZER_TRACE命，explain以及慢查询查看结果。

rowid排序

新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主键id。但这时，排序的结果就因为少了city和age字段的值，不能直接返回了，整个执行流程就变成如
下所示的样子：

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

全字段排序 VS rowid排序

使用索引优化从而优化排序。

17 | 如何正确地显示随机消息？

【学习SQL执行过程】

背景

这个英语学习App首页有一个随机显示单词的功能，也就是根据每个用户的级别有一个单词表，然后这个用户每次访问首页的时候，都会随机滚动显示三个单词。他们发现随着单词表变大，选单词这个逻辑变得越来越慢，甚至影响到了首页的打开速度。

现在对这个例子进行了简化：去掉每个级别的用户都有一个对应的单词表这个逻辑，直接就是从一个单词表中随机选出三个单词。

内存临时表

mysql> select word from words order by rand() limit 3;

这个语句的意思很直白，随机排序取前3个。使用explain命令来看看这个语句的执行情况。

对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。

这条语句的执行流程是这样的：

创建一个临时表。这个临时表使用的是memory引擎，表里有两个字段，第一个字段是double类型，为了后面描述方便，记为字段R，第二个字段是varchar(64)类型，记为字段W。并且，这个表没有建索引。
从words表中，按主键顺序取出所有的word值。对于每一个word值，调用rand()函数生成一个大于0小于1的随机小数，并把这个随机小数和word分别存入临时表的R和W字段中，到此，扫描行数是10000。
现在临时表有10000行数据了，接下来你要在这个没有索引的内存临时表上，按照字段R排序。
初始化 sort_buffer。sort_buffer中有两个字段，一个是double类型，另一个是整型。
从内存临时表中一行一行地取出R值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入sort_buffer中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加10000，变成了20000。
在sort_buffer中根据R的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出word值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了20003。

接下来，我们通过慢查询日志（slow log）来验证一下我们分析得到的扫描行数是否正确。

# Query_time: 0.900376  Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003
SET timestamp=1541402277;
select word from words order by rand() limit 3;

其中，Rows_examined：20003就表示这个语句执行过程中扫描了20003行，也就验证了我们分析得出的结论。

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

MySQL的表是用什么方法来定位“一行数据”的。如果你创建的表没有主键，或者把一个表的主键删掉了，那么InnoDB会自己生成一个长度为6字节的rowid来作为主键

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

【索引失效】

案例一：条件字段函数操作

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

案例二：隐式类型转换

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

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

案例三：隐式字符编码转换

其实是在说同一件事儿，即：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

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

【锁住了or长事务】

套路：分析问题，验证问题，解决问题。

有些情况下， “查一行”，也会执行得特别慢。

第一类： 查询长时间不返回

如图1所示，在表t执行下面的SQL语句：

mysql> select * from t where id=1;

查询结果长时间不返回。一般碰到这种情况的话，大概率是表t被锁住了。

接下来分析原因的时候，一般都是首先执行一下show processlist命令，看看当前语句处于什么状态。然后我们再针对每种状态，去分析它们产生的原因、如何复现，以及如何处理。

等MDL锁

使用show processlist命令查看Waiting for table metadata lock。

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

解决方法：就是找到谁持有MDL写锁，然后把它kill掉。

通过查询sys.schema_table_lock_waits这张表，我们就可以直接找出造成阻塞的process id，把这个连接用kill 命令断开即可。

等flush

mysql> select * from information_schema.processlist where id=1;

使用show processlist排查。

等行锁

现在，经过了表级锁的考验，我们的select 语句终于来到引擎里了。

mysql> select * from t where id=1 lock in share mode;

解决方法：

通过sys.innodb_lock_waits 表查出是谁占着这个写锁

mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

然后直接断开这个连接。

第二类： 查询慢

mysql> select * from t where id=1；

虽然扫描行数是1，但执行时间却长达800毫秒。

select * from t where id=1 lock in share mode

执行时扫描行数也是1行，执行时间是0.2毫秒。

先猜测下，再往下看。

session A先用start transaction with consistent snapshot命令启动了一个事务，之后session B才开始执行update 语句。

session B执行完100万次update语句后， id=1这一行处于什么状态呢？

session B更新完100万次，生成了100万个回滚日志(undo log)。

带lock in share mode的SQL语句，是当前读，因此会直接读到1000001这个结果，所以速度很快；而select * from t where id=1这个语句，是一致性读，因此需要从1000001开始，依次执行undo log，执行了100万次以后，才将1这个结果返回。

20 | 幻读是什么，幻读有什么问题？

【是什么？会出现什么问题？数据库又是怎么解决的？】

前提：可重复读隔离级别下的，间隙锁才会发生。

幻读是什么？

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

这个表除了主键id外，还有一个索引c，初始化语句在表中插入了6行数据。

现在，我们就来分析一下，如果只在id=5这一行加锁，而其他行的不加锁的话，会怎么样。

可以看到， session A里执行了三次查询，分别是Q1、 Q2和Q3。它们的SQL语句相同，都是select * from t where d=5 for update。查所有d=5的行，而且使用的是当前读，并且加上写锁。

Q1只返回id=5这一行；
在T2时刻，session B把id=0这一行的d值改成了5，因此T3时刻Q2查出来的是id=0和id=5这两行；
在T4时刻，session C又插入一行（1,1,5），因此T5时刻Q3查出来的是id=0、id=1和id=5的这三行。

其中， Q3读到id=1这一行的现象，被称为“幻读”。

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

说明

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

幻读有什么问题？

会导致数据不一致问题。

下面这个例子非常好。

分析一下上图

经过T1时刻，id=5这一行变成 (5,5,100)，当然这个结果最终是在T6时刻正式提交的;
经过T2时刻，id=0这一行变成(0,5,5);
经过T4时刻，表里面多了一行(1,5,5);
其他行跟这个执行序列无关，保持不变。

这些数据也没啥问题，但是我们来看看binlog里面的内容。

T2时刻，session B事务提交，写入了两条语句；
T4时刻，session C事务提交，写入了两条语句；
T6时刻，session A事务提交，写入了update t set d=100 where d=5 这条语句。

update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

update t set d=100 where d=5;/*所有d=5的行，d改成100*/

好，你应该看出问题了。这个语句序列，不论是拿到备库去执行，还是以后用binlog来克隆一个库，这三行的结果，都变成了 (0,5,100)、(1,5,100)和(5,5,100)。

也就是说，id=0和id=1这两行，发生了数据不一致。这个问题很严重，是不行的。

如何解决幻读？

行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题， InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。

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

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

间隙锁和行锁合称next-keylock，每个next-keylock是前开后闭区间。也就是说，我们的表t初始化以后，如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是 (-∞,0]、 (0,5]、 (5,10]、 (10,15]、 (15,20]、 (20, 25]、 (25, +supremum]。

但是可能会有死锁的情况发生。

你看到了，其实都不需要用到后面的update语句，就已经形成死锁了。我们按语句执行顺序来分析一下：

session A 执行select … for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10);
session B 执行select … for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
session B 试图插入一行(9,9,9)，被session A的间隙锁挡住了，只好进入等待；
session A试图插入一行(9,9,9)，被session B的间隙锁挡住了。

至此，两个session进入互相等待状态，形成死锁。当然，InnoDB的死锁检测马上就发现了这对死锁关系，让session A的insert语句报错返回了。

最后，这节分析的问题都是在可重复读隔离级别下的，间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把binlog格式设置为row。这也是现在不少公司使用的配置组合。

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

【重点】

以下默认是可重复读隔离级别。

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

该节有许多经典案例，回到平台文章细看，就不再这里一一列举。

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

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

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

慢查询性能问题

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

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

QPS突增问题

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

23 | MySQL是怎么保证数据不丢的？

【日志】

只要redo log和binlog保证持久化到磁盘，就能确保MySQL异常重启后，数据可以恢复。

binlog的写入机制

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

一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。

系统给binlog cache分配了一片内存，每个线程一个，参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把binlog cache里的完整事务写入到binlog中，并清空binlog cache。

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

图中的write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。

图中的fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的IOPS。

redo log的写入机制

事务在执行过程中，生成的redolog是要先写到redo log buffer的。 redo log buffer里面的内容，是不是每次生成后都要直接持久化到磁盘呢？答案是，不需要。如果事务执行期间MySQL发生异常重启，那这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有损失。事务还没提交的时候， redo log buffer中的部分日志有没有可能被持久化到磁盘呢？答案是，确实会有。

这三种状态分别是：

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

日志写到redo log buffer是很快的， wirte到page cache也差不多，但是持久化到磁盘的速度就慢多了。

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

设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中;
设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘；
设置为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写入到磁盘中。

一种是，redo log buffer占用的空间即将达到 innodb_log_buffer_size一半的时候，后台线程会主动写盘。
另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘。

如果把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。

其实到这里，我的理解是本质就是2次刷盘磁盘，但是加了buffer进行优化，提高效率。

组提交

三个并发事务(trx1, trx2, trx3)在prepare 阶段，都写完redo log buffer，持久化到磁盘的过程，对应的LSN分别是50、 120 和160。

从图中可以看到，

trx1是第一个到达的，会被选为这组的 leader；
等trx1要开始写盘的时候，这个组里面已经有了三个事务，这时候LSN也变成了160；
trx1去写盘的时候，带的就是LSN=160，因此等trx1返回时，所有LSN小于等于160的redo log，都已经被持久化到磁盘；
这时候trx2和trx3就可以直接返回了。

所以，一次组提交里面，组员越多，节约磁盘IOPS的效果越好。

WAL机制主要得益于两个方面：

redo log 和 binlog都是顺序写，磁盘的顺序写比随机写速度要快；
组提交机制，可以大幅度降低磁盘的IOPS消耗。【磁盘IOPS：磁盘IOPS是指一秒内磁盘进行多少次I/O读写；】

如果你的MySQL现在出现了性能瓶颈， 而且瓶颈在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对主备一致贡献非常的大。

MySQL主备的基本原理

节点A到B这条线的内部流程是什么样的。

主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。

备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。一个事务日志同步的完整过程是这样的：

在备库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读取中转日志，解析出日志里的命令，并执行。

binlog的三种格式

当binlog_format=statement时， binlog里面记录的就是SQL语句的原文。

把binlog的格式改为binlog_format=‘row’

row格式的binlog里没有了SQL语句的原文，而是替换成了两个event： Table_map和Delete_rows

为什么会有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格式。

循环复制问题

双M结构和M-S结构，其实区别只是多了一条线，即：节点A和B之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。

业务逻辑在节点A上更新了一条语句，然后再把生成的binlog 发给节点B，节点B执行完这条更新语句后也会生成binlog。如果节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然后节点A和B间，会不断地循环执行这个更新语句，也就是循环复制了。这个要怎么解决呢？

MySQL在binlog中记录了这个命令第一次执行时所在实例的serverid。因此，我们可以用下面的逻辑，来解决两个节点间的循环复制的问题：

规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系；
一个备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog；
每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

按照这个逻辑，如果我们设置了双M结构，日志的执行流就会变成这样：

从节点A更新的事务，binlog里面记的都是A的server id；
传到节点B执行一次以后，节点B生成的binlog 的server id也是A的server id；
再传回给节点A，A判断到这个server id与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

25 | MySQL是怎么保证高可用的？

【就是主备低延时】

正常情况下，只要主库执行更新生成的所有binlog，都可以传到备库并被正确地执行，备库就能达到跟主库一致的状态，这就是最终一致性。

主备延迟

主备切换可能是一个主动运维动作，比如软件升级、主库所在机器按计划下线等，也可能是被动操作，比如主库所在机器掉电。

与数据同步有关的时间点主要包括以下三个：

主库A执行完成一个事务，写入binlog，我们把这个时刻记为T1;
之后传给备库B，我们把备库B接收完这个binlog的时刻记为T2;
备库B执行完成这个事务，我们把这个时刻记为T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是T3-T1。

你可以在备库上执行show slave status命令，它的返回结果里面会显示seconds_behind_master，用于表示当前备库延迟了多少秒。

seconds_behind_master的计算方法是这样的：

每个事务的binlog 里面都有一个时间字段，用于记录主库上写入的时间；
备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到seconds_behind_master。

可以看到，其实seconds_behind_master这个参数计算的就是T3-T1。所以，我们可以用seconds_behind_master来作为主备延迟的值，这个值的时间精度是秒。

主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产binlog的速度要慢。

下面来说说常见的例子。

主备延迟的来源

备库所在机器的性能要比主库所在的机器性能差。
备库的压力大：备库上的查询耗费了大量的CPU资源，影响了同步速度，造成主备延迟

一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。
通过binlog输出到外部系统，比如Hadoop这类系统，让外部系统提供统计类查询的能力。

大事务

可靠性优先策略

双M结构下，从状态1到状态2切换的详细过程是这样的：

判断备库B现在的seconds_behind_master，如果小于某个值（比如5秒）继续下一步，否则持续重试这一步；
把主库A改成只读状态，即把readonly设置为true；
判断备库B的seconds_behind_master的值，直到这个值变成0为止；
把备库B改成可读写状态，也就是把readonly 设置为false；
把业务请求切到备库B。

这个切换流程，一般是由专门的HA系统来完成的，我们暂时称之为可靠性优先流程。

可用性优先策略

强行把步骤4、 5调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库B，并且让备库B可以读写，那么系统几乎就没有不可用时间了。我们把这个切换流程，暂时称作可用性优先流程。这个切换流程的代价，就是可能出现数据不一致的情况。

可用性优先策略，且binlog_format=mixed时的切换流程和数据结果。

现在，我们一起分析下这个切换流程：

步骤2中，主库A执行完insert语句，插入了一行数据（4,4），之后开始进行主备切换。
步骤3中，由于主备之间有5秒的延迟，所以备库B还没来得及应用“插入c=4”这个中转日志，就开始接收客户端“插入 c=5”的命令。
步骤4中，备库B插入了一行数据（4,5），并且把这个binlog发给主库A。
步骤5中，备库B执行“插入c=4”这个中转日志，插入了一行数据（5,4）。而直接在备库B执行的“插入c=5”这个语句，传到主库A，就插入了一行新数据（5,5）。

最后的结果就是，主库A和备库B上出现了两行不一致的数据。可以看到，这个数据不一致，是由可用性优先流程导致的。

可用性优先策略，但设置binlog_format=row时的切换流程和数据结果。

因为row格式在记录binlog的时候，会记录新插入的行的所有字段值，所以最后只会有一行不一致。而且，两边的主备同步的应用线程会报错duplicate key error并停止。也就是说，这种情况下，备库B的(5,4)和主库A的(5,5)这两行数据，都不会被对方执行。

结论：

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

MySQL高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。

26 | 备库为什么会延迟好几个小时？

【中转日志消费不够快】

如果备库执行日志的速度持续低于主库生成日志的速度，那这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说，备库很可能永远都追不上主库的节奏。

coordinator在分发的时候，需要满足以下这两个基本要求：

不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker中。
同一个事务不能被拆开，必须放到同一个worker中。

MySQL 5.5版本的并行复制策略

按表分发策略

按表分发事务的基本思路是，如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证两个worker不会更新同一行。

按行分发策略

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

相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。

MySQL 5.6版本的并行复制策略

官方MySQL5.6版本，支持了并行复制，只是支持的粒度是按库并行。这个策略的并行效果，取决于压力模型。如果在主库上有多个DB，并且各个DB的压力均衡，使用这个策略的效果会很好。

MySQL 5.7的并行复制策略

MySQL 5.7并行复制策略的思想是：

同时处于prepare状态的事务，在备库执行时是可以并行的；
处于prepare状态的事务，与处于commit状态的事务之间，在备库执行时也是可以并行的。

为什么要有多线程复制呢？这是因为单线程复制的能力全面低于多线程复制，对于更新压力较大的主库，备库是可能一直追不上主库的。从现象上看就是，备库上seconds_behind_master的值越来越大。

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

【主备切换，日志从哪开始同步问题】

图中，虚线箭头表示的是主备关系，也就是A和A’互为主备，从库B、 C、 D指向的是主库A。一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其他的读请求则由从库分担。

相比于一主一备的切换流程，一主多从结构在切换完成后， A’会成为新的主库，从库B、 C、 D也要改接到A’。正是由于多了从库B、 C、 D重新指向的这个过程，所以主备切换的复杂性也相应增加了。

基于位点的主备切换

之所以需要这个，是因为从库要换连接的主库，我认为这个位点是之后备库A‘和从库开始同步的位置。

一种取同步位点的方法是这样的：

等待新主库A’把中转日志（relay log）全部同步完成；
在A’上执行show master status命令，得到当前A’上最新的File 和 Position；
取原主库A故障的时刻T；
用mysqlbinlog工具解析A’的File，得到T时刻的位点。

mysqlbinlog File --stop-datetime=T --start-datetime=T

当然这个值并不精确。为什么呢？

你可以设想有这么一种情况，假设在T这个时刻，主库A已经执行完成了一个insert 语句插入了一行数据R，并且已经将binlog传给了A’和B，然后在传完的瞬间主库A的主机就掉电了。

那么，这时候系统的状态是这样的：

在从库B上，由于同步了binlog， R这一行已经存在；
在新主库A’上， R这一行也已经存在，日志是写在123这个位置之后的；
我们在从库B上执行change master命令，指向A’的File文件的123位置，就会把插入R这一行数据的binlog又同步到从库B去执行。

这时候，从库B的同步线程就会报告 Duplicate entry ‘id_of_R’ for key ‘PRIMARY’ 错误，提示出现了主键冲突，然后停止同步。

因此，我们在切换任务的时候，要先主动跳过这些错误，有两种常用的方法。

一种做法是，主动跳过一个事务。跳过命令的写法是：

set global sql_slave_skip_counter=1;
start slave;

因为切换过程中，可能会不止重复执行一个事务，所以我们需要在从库B刚开始接到新主库A’时，持续观察，每次碰到这些错误就停下来，执行一次跳过命令，直到不再出现停下来的情况，以此来跳过可能涉及的所有事务。

另外一种方式是，通过设置slave_skip_errors参数，直接设置跳过指定的错误。

在执行主备切换时，有这么两类错误，是经常会遇到的：

1062错误是插入数据时唯一键冲突；
1032错误是删除数据时找不到行。

因此，我们可以把slave_skip_errors 设置为 “1032,1062”，这样中间碰到这两个错误时就直接跳过。

GTID

知道GTID的概念这部分就非常好理解了。

MySQL5.7 集群管理（主从复制、MHA、GTID、PXC）

全局事务标识∶ global transaction identifiers。
GTID与事务——对应，并且全局唯一ID。
一个GTID在一个服务器上只执行一次。
MySQL-5.6.5开始支持GTID。

组成：GTID = server_uuid : transaction_id

binlog和GTID的关系

28 | 读写分离有哪些坑？

【从库没有同步到位】

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

强制走主库方案

我们可以将查询请求分为这么两类：

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

Sleep 方案

主库更新后，读从库之前先sleep一下。

判断主备无延迟方案

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

第二种方法，对比位点确保主备无延迟。

第三种方法，对比GTID集合确保主备无延迟。

等主库位点方案

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

这条命令的逻辑如下：

它是在从库执行的；
参数file和pos指的是主库上的文件名和位置；
timeout可选，设置为正整数N表示这个函数最多等待N秒。

这个命令正常返回的结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置，执行了多少事务。

使用逻辑：

trx1事务更新完成后，马上执行show master status得到当前主库执行到的File和Position；
选定一个从库执行查询语句；
在从库上执行select master_pos_wait(File, Position, 1)；
如果返回值是>=0的正整数，则在这个从库执行查询语句；
否则，到主库执行查询语句。

GTID方案

select wait_for_executed_gtid_set(gtid_set, 1);

这条命令的逻辑是：

等待，直到这个库执行的事务中包含传入的gtid_set，返回0；
超时返回1。

等GTID的执行流程就变成了：

trx1事务更新完成后，从返回包直接获取这个事务的GTID，记为gtid1；
选定一个从库执行查询语句；
在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；
如果返回值是0，则在这个从库执行查询语句；
否则，到主库执行查询语句。

跟等主库位点的方案一样，等待超时后是否直接到主库查询，需要业务开发同学来做限流考虑。

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

主备切换有两种场景，一种是主动切换，一种是被动切换。而其中被动切换，往往是因为主库出问题了，由HA系统发起的。

select 1判断

实际上，select 1成功返回，只能说明这个库的进程还在，并不能说明主库没问题。

查表判断

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

mysql> select * from mysql.health_check;

可能出现读没有问题，但是写存在问题，还是排查不够彻底。

更新判断

既然要更新，就要放个有意义的字段，常见做法是放一个timestamp字段，用来表示最后一次执行检测的时间。这条更新语句类似于：

mysql> update mysql.health_check set t_modified=now();

节点可用性的检测都应该包含主库和备库。如果用更新来检测主库的话，那么备库也要进行更新检测。

但是这都是外部提供，我们可以通过使用数据库告诉我们内部的真实情况。

内部统计

MySQL 5.6版本以后提供的performance_schema库，就在file_summary_by_event_name表里统计了每次IO请求的时间。

30 | 答疑文章（二）：用动态的观点看加锁

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

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

先对和MySQL相关的误删数据，做下分类：

使用delete语句误删数据行；
使用drop table或者truncate table语句误删数据表；
使用drop database语句误删数据库；
使用rm命令误删整个MySQL实例。

其实主要就是靠数据库记录的日志恢复。

误删行

具体恢复数据时，对单个事务做如下处理：

对于insert语句，对应的binlog event类型是Write_rows event，把它改成Delete_rows event即可；
同理，对于delete语句，也是将Delete_rows event改为Write_rows event；
而如果是Update_rows的话，binlog里面记录了数据行修改前和修改后的值，对调这两行的位置即可。

误删库/表

这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份binlog。在这两个条件都具备的情况下，假如有人中午12点误删了一个库，恢复数据的流程如下：

取最近一次全量备份，假设这个库是一天一备，上次备份是当天0点；
用备份恢复出一个临时库；
从日志备份里面，取出凌晨0点之后的日志；
把这些日志，除了误删除数据的语句外，全部应用到临时库。

延迟复制备库

我们可以考虑搭建延迟复制的备库。这个功能是MySQL 5.6版本引入的。

一般的主备复制结构存在的问题是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也都一起被误删了。

延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N命令，可以指定这个备库持续保持跟主库有N秒的延迟。

比如你把N设置为3600，这就代表了如果主库上有数据被误删了，并且在1小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

这样的话，你就随时可以得到一个，只需要最多再追1小时，就可以恢复出数据的临时实例，也就缩短了整个数据恢复需要的时间。

预防误删库/表的方法

第一条建议是，账号分离。第二条建议是，制定操作规范。

rm删除数据

只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话， HA系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。这时，你要做的就是在这个节点上把数据恢复回来，再接入整个集群

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

在MySQL中有两个kill命令：一个是kill query+线程id，表示终止这个线程中正在执行的语句；一个是kill connection +线程id，这里connection可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。不知道你在使用MySQL的时候，有没有遇到过这样的现象：使用了kill命令，却没能断开这个连接。再执行show processlist命令，看到这条语句的Command列显示的是Killed。

kill并不是马上停止的意思，而是告诉执行线程说，这条语句已经不需要继续执行了，可以开始“执行停止的逻辑了”。

MySQL客户端发送请求后，接收服务端返回结果的方式有两种：

一种是本地缓存，也就是在本地开一片内存，先把结果存起来。如果你用API开发，对应的就是mysql_store_result 方法。
另一种是不缓存，读一个处理一个。如果你用API开发，对应的就是mysql_use_result方法。

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

其实这篇文章主要说的是MySQL是边读边发的，但是我认为和DBMS的buffer pool也有关系。

全表扫描对server层的影响

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

实际上，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：

获取一行，写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是16k。
重复获取行，直到net_buffer写满，调用网络接口发出去。
如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。
如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

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

在show processlist命令中的State列的值一直处于“Sending to client”，就表示服务器端的网络栈写满了。

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

Sending data：它的意思只是“正在执行”。

全表扫描对InnoDB的影响

InnoDB内存管理用的是最近最少使用 (Least RecentlyUsed, LRU)算法，这个算法的核心就是淘汰最久未使用的数据。

InnoDB不能直接使用这个LRU算法。实际上， InnoDB对LRU算法做了改进。在InnoDB实现上，按照5:3的比例把整个LRU链表分成了young区域和old区域。图中LRU_old指向的就是old区域的第一个位置，是整个链表的5/8处。也就是说，靠近链表头部的5/8是young区域，靠近链表尾部的3/8是old区域。

改进后的LRU算法执行流程变成了下面这样。

图7中状态1，要访问数据页P3，由于P3在young区域，因此和优化前的LRU算法一样，将其移到链表头部，变成状态2。
之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页Pm，但是新插入的数据页Px，是放在LRU_old处。
处于old区域的数据页，每次被访问的时候都要做下面这个判断：若这个数据页在LRU链表中存在的时间超过了1秒，就把它移动到链表头部；如果这个数据页在LRU链表中存在的时间短于1秒，位置保持不变。 1秒这个时间，是由参数innodb_old_blocks_time控制的。其默认值是1000，单位毫秒。

我们看看改进后的LRU算法的操作逻辑：

扫描过程中，需要新插入的数据页，都被放到old区域;
一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域；
再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是young区域），很快就会被淘汰出去。

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

在实际生产中，关于join语句使用的问题，一般会集中在以下两类：

我们DBA不让使用join，使用join有什么问题呢？
如果有两个大小不同的表做join，应该用哪个表做驱动表呢？

Index Nested-Loop Join

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

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

可以看到，在这条语句里，被驱动表t2的字段a上有索引， join过程用上了这个索引，因此这个语句的执行流程是这样的：

从表t1中读入一行数据 R；
从数据行R中，取出a字段到表t2里去查找；
取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
重复执行步骤1到3，直到表t1的末尾循环结束。

对比用单表查询怎么实现。

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

可以看到，在这个查询过程，也是扫描了200行，但是总共执行了101条语句，比直接join多了100次交互。除此之外，客户端还要自己拼接SQL语句和结果。

结论：

使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；
如果使用join语句的话，需要让小表做驱动表。

Simple Nested-Loop Join

由于表的字段b上没有索引，因此再用图的执行流程时，每次到t2去匹配的时候，就要做一次全表扫描。

Block Nested-Loop Join

这时候，被驱动表上没有可用的索引，算法的流程是这样的：

把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

可以看到，在这个过程中，对表t1和t2都做了一次全表扫描，因此总的扫描行数是1100。由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是：100*1000=10万次。

假设小表的行数是N，大表的行数是M，那么在这个算法里：

两个表都做一次全表扫描，所以总的扫描行数是M+N；
内存中的判断次数是M*N。

可以看到，调换这两个算式中的M和N没差别，因此这时候选择大表还是小表做驱动表，执行耗时是一样的。

分段放：join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。 如果放不下表t1的所有数据话，策略很简单，就是分段放。我把join_buffer_size改成1200，再执行：

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

执行过程就变成了：

扫描表t1，顺序读取数据行放入join_buffer中，放完第88行join_buffer满了，继续第2步；
扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回；
清空join_buffer；
继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。

我们再来看下，在这种情况下驱动表的选择问题。

假设，驱动表的数据行数是N，需要分K段才能完成算法流程，被驱动表的数据行数是M。注意，这里的K不是常数， N越大K就会越大，因此把K表示为λ * N，显然λ的取值范围是(0,1)。所以，在这个算法的执行过程中：

扫描行数是 N+λ * N * M；
内存判断 N * M次。
显然，内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数，在M和N大小确定的情况下， N小一些，整个算式的结果会更小。所以结论是，应该让小表当驱动表。当然，你会发现，在N+λNM这个式子里， λ才是影响扫描行数的关键因素，这个值越小越好。

回答文章开头的两个问题。

第一个问题：能不能使用join语句？

如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。

所以你在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”字样。

第二个问题是：如果要使用join，应该选择大表做驱动表还是选择小表做驱动表？

如果是Index Nested-Loop Join算法，应该选择小表做驱动表；
如果是Block Nested-Loop Join算法：
- 在join_buffer_size足够大的时候，是一样的；
- 在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。

所以，这个问题的结论就是，总是应该使用小表做驱动表。

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

总结

通过对Index Nested-Loop Join和Block Nested-Loop Join两个算法执行过程的分析，我们也得到了文章开头两个问题的答案：

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

35 | join语句怎么优化？

Multi-Range Read优化

【主要是根据局部一致性原理进行优化的】

Multi-Range Read优化 (MRR)。这个优化的主要目的是尽量使用顺序读盘。

主键索引是一棵B+树，在这棵树上，每次只能根据一个主键id查到一行数据。因此，回表肯定是一行行搜索主键索引的

如果随着a的值递增顺序查询的话， id的值就变成随机的，那么就会出现随机访问，性能相对较差。虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

这就是MRR优化的设计思路。此时，语句的执行流程变成了这样：

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

Batched Key Access

NLJ算法执行的逻辑是：从驱动表t1，一行行地取出a的值，再到被驱动表t2去做join。也就是说，对于表t2来说，每次都是匹配一个值。这时， MRR的优势就用不上了。

BNL算法的性能问题

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

也就是说， BNL算法对系统的影响主要包括三个方面：

可能会多次扫描被驱动表，占用磁盘IO资源；
判断join条件需要执行M*N次对比（M、 N分别是两张表的行数），如果是大表就会占用非常多的CPU资源；
可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。

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

内存表和临时表的概念

内存表，指的是使用Memory引擎的表，建表语法是create table engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。
临时表，可以使用各种引擎类型。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用Memory引擎。

临时表的特性

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

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

临时表的应用

为什么临时表可以重名？

SQL create temporary table temp_t(id int primary key)engine=innodb;

MySQL要给这个InnoDB表创建一个frm文件保存表结构定义，还要有地方保存表数据。这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程id}_{线程id}_序列号”。你可以使用select @@tmpdir命令，来显示实例的临时文件目录。

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

union 执行流程

SQL (select 1000 as f) union (select id from t1 order by id desc limit 2);

创建一个内存临时表，这个临时表只有一个整型字段f，并且f是主键字段。
执行第一个子查询，得到1000这个值，并存入临时表中。
执行第二个子查询：
i. 拿到第一行id=1000，试图插入临时表中。但由于1000这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；
Sii. 取到第二行id=999，插入临时表成功。
从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是1000和999。

可以看到，这里的内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键id的唯一性约束，实现了union的语义。

group by 执行流程

SQL select id%10 as m, count(*) as c from t1 group by m;

在Extra字段里面，我们可以看到三个信息：

Using index，表示这个语句使用了覆盖索引，选择了索引a，不需要回表；
Using temporary，表示使用了临时表；
Using filesort，表示需要排序。

这个语句的执行流程是这样的：

创建内存临时表，表里有两个字段m和c，主键是m；
扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；

如果临时表中没有主键为x的行，就插入一个记录(x,1);
如果表中有主键为x的行，就将x这一行的c值加1；

遍历完成后，再根据字段m做排序，得到结果集返回给客户端。

指导原则

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

数据库中已经设置buffer，为什么还要有临时表这个东西，除了存储在磁盘上还有其他的好处吗?

缓冲区（Buffer）：缓冲区是用来存储数据页的内存区域，它的主要作用是提高数据库的性能。当数据库需要读取或写入数据时，首先将数据页加载到内存的缓冲区中，这样数据库可以直接在内存中进行操作，而不必每次都访问磁盘。这样做的好处是减少了磁盘I/O操作的次数，加快了数据的访问速度，提高了数据库的性能。
临时表（Temporary Table）：临时表是一种特殊类型的表，它在数据库中的生命周期通常比普通表短暂，它的数据通常不会持久保存，而是在会话结束或者事务提交后被删除。临时表通常用于临时存储数据、中间计算结果等，它们可以帮助简化复杂的查询或者操作，提高查询的可读性和性能。

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

内存表的数据组织结构

假设有以下的两张表t1 和 t2，其中表t1使用Memory引擎，表t2使用InnoDB引擎。

可以看到，内存表t1的返回结果里面0在最后一行，而InnoDB表t2的返回结果里0在第一行。表t2用的是InnoDB引擎，它的主键索引id的组织方式，你已经很熟悉了： InnoDB表的数据就放在主键索引树上，主键索引是B+树。所以表t2的数据组织方式如下图所示：

与InnoDB引擎不同， Memory引擎的数据和索引是分开的。

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

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

从中我们可以看出，这两个引擎的一些典型不同

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

hash索引和B-Tree索引

存表也是支B-Tree索引的。在id列上创建一个B-Tree索引， SQL语句可以这么写：

SQL alter table t1 add index a_btree_index using btree (id);

内存表的锁

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

数据持久性问题

数据放在内存中，是内存表的优势，但也是一个劣势。因为，数据库重启的时候，所有的内存表都会被清空。内存表并不适合在生产环境上作为普通数据表使用。

如果你的表更新量大，那么并发度是一个很重要的参考指标， InnoDB支持行锁，并发度比内存表好；
能放到内存表的数据量都不大。如果你考虑的是读的性能，一个读QPS很高并且数据量不大的表，即使是使用InnoDB，数据也是都会缓存在InnoDB Buffer Pool里的。因此，使用InnoDB表的读性能也不会差。

建议你把普通内存表都用InnoDB表来代替

内存临时表刚好可以无视内存表的两个不足，主要是下面的三个原因：

临时表不会被其他线程访问，没有并发性的问题；
临时表重启后也是需要删除的，清空数据这个问题不存在；
备库的临时表也不会影响主库的用户线程。

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

自增值保存在哪儿？

可以看到，表定义里面出现了一个AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成id=2。其实，这个输出结果容易引起这样的误解：自增值是保存在表结构定义里的。实际上，表的结构定义存放在后缀名为.frm的文件中，但是并不会保存自增值。

不同的引擎对于自增值的保存策略不同。MyISAM引擎的自增值保存在数据文件中。InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”。

自增值修改机制

在MySQL里面，如果字段id被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT值填到自增字段；
如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。

自增值的修改时机

唯一键冲突导致自增主键id不连续

假设，表t里面已经有了(1,1,1)这条记录，这时我再执行一条插入数据命令：

insert into t values(null, 1, 1);

这个语句的执行流程就是：

执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1);
InnoDB发现用户没有指定自增id的值，获取表t当前的自增值2；
将传入的行的值改成(2,1,1);
将表的自增值改成3；
继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate key error，语句返回。

可以看到，这个表的自增值改成3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键c冲突，所以id=2这一行并没有插入成功，但也没有将自增值再改回去。

事务回滚导致自增主键id不连续

同样道理。

思考：为什么自增值为什么不能回退？

【简单来说，在多事务执行下回退会出现重复】

下面举例子说明。

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增id，肯定要加锁，然后顺序申请。

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

而为了解决这个主键冲突，有两种方法：

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

可见，这两个方法都会导致性能问题。

MySQL内部机制

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

语句执行过程中，第一次申请自增id，会分配1个；
1个用完以后，这个语句第二次申请自增id，会分配2个；
2个用完以后，还是这个语句，第三次申请自增id，会分配4个；
依此类推，同一个语句去申请自增id，每次申请到的自增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)。

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

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

而如果insert和select的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。
insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的next-keylock(S锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

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

mysqldump方法

SQL mysqldump -hℎ𝑜𝑠𝑡−𝑃port -u$user –add-locks=0 –no-create-info –single-transaction –set-gtid-purged=OFF db1

这条命令中，主要参数含义如下：

–single-transaction的作用是，在导出数据的时候不需要对表db1.t加表锁，而是使用STARTTRANSACTION WITH CONSISTENTSNAPSHOT的方法；
–add-locks设置为0，表示在输出的文件结果里，不增加” LOCKTABLES t WRITE;”；
–no-create-info的意思是，不需要导出表结构；
–set-gtid-purged=off表示的是，不输出跟GTID相关的信息；
–result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的。
通过这条mysqldump命令生成的t.sql文件中就包含了如图1所示的INSERT语句。

导出CSV文件

另一种方法是直接将结果导出成.csv文件。 MySQL提供了下面的语法，用来将查询结果导出到服务端本地目录：

SQL select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

物理拷贝

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

grant之后真的需要执行flush privileges吗？

如果没有执行这个flush命令的话，赋权语句真的不能生效吗？grant语句会同时修改数据表和内存，判断权限的时候使用的是内存数据。因此，规范地使用grant和revoke语句，是不需要随后加上flush privileges语句的。flush privileges语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用DML语句操作系统权限表导致的，所以我们尽量不要使用这类语句。

43 | 要不要使用分区表？

CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);

在表t中初始化插入了两行记录，按照定义的分区规则，这两行记录分别落在p_2018和p_2019这两个分区上。可以看到，这个表包含了一个.frm文件和4个.ibd文件，每个分区对应一个.ibd文件。也就是说：对于引擎层来说，这是4个表；对于Server层来说，这是1个表。

分区策略

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这两个引擎支持了本地分区策略。

分区表的server层行为

如果从server层看的话，一个分区表就只是一个表。

可以看到，虽然session B只需要操作p_2107这个分区，但是由于session A持有整个表t的MDL锁，就导致了session B的alter语句被堵住。

MySQL在第一次打开分区表的时候，需要访问所有的分区；
在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；
在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区。

有两个问题需要注意：

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

至于分区表的其他问题，比如查询需要跨多个分区取数据，查询性能就会比较慢，基本上就不是分区表本身的问题，而是数据量的问题或者说是使用方式的问题了。

45 | 自增id用完怎么办？

MySQL里面的几种自增id，一起分析一下它们的值达到上限以后，会出现什么情况。2^32 -1（4294967295）不是一个特别大的数，对于一个频繁插入删除数据的表来说，是可能会被用完的。因此在建表的时候你需要考察你的表是否有可能达到这个上限，如果有可能，就应该创建成8个字节的bigint unsigned。

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。

每种自增id有各自的应用场景，在达到上限后的表现也不同：

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

面试高频知识

搬运自知乎

1、一条 sql 语句是如何执行的？也就是说，从客户端执行了一条 sql 命令，服务端会进行哪些处理？（例如验证身份，是否启用缓存啥的）

2、索引相关：索引是如何实现的？多种引擎的实现区别？聚族索引，非聚族索引，二级索引，唯一索引、最左匹配原则等等（非常重要）

3、事务相关：例如事务的隔离是如何实现的？事务是如何保证原子性？不同的事务看到的数据怎么就不一样了？难道每个事务都拷贝一份视图？MVCC 的实现原理（重要）等等。

4、各种锁相关：例如表锁，行锁，间隙锁，共享锁，排他锁。这些锁的出现主要是用来解决哪些问题？（重要）

5、日志相关：redolog，binlog，undolog，这些日志的实现原理，为了解决怎么问题？日志也是非常重要的吧，面试也问的挺多。

6、数据库的主从备份、如何保证数据不丢失、如何保证高可用等等。

7、一些故障排查的命令，例如慢查询，sql 的执行计划，索引统计的刷新等等。

JoyTsing

https://joytsing.github.io/posts/44885/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 JoyTsing !

工程经验读书笔记数据库

MySQL技术内幕-InnoDB存储引擎(二)

MySQL文件和日志

2024-05-03 数据库

读书笔记数据库

在Linux下为g++链接三方库

想像原生一样使用

2024-04-27 Cpp

Cpp 工程经验 Linux

MySQL实战笔记

概述

01 | 基础架构： 一条SQL查询语句是如何执行的 ?

02 | 日志系统： 一条SQL更新语句是如何执行的？

03 | 事务隔离： 为什么你改了我还看不见？

04 | 深入浅出索引（上）

05 | 深入浅出索引（下）

06 | 全局锁和表锁 ： 给表加个字段怎么有这么多阻碍？

07 | 行锁功过： 怎么减少行锁对性能的影响？

08 | 事务到底是隔离的还是不隔离的？

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

10 | MySQL为什么有时候会选错索引？

11 | 怎么给字符串字段加索引？

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

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

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

15 | 答疑文章（一） ： 日志和索引相关问题

16 | “order by”是怎么工作的？

17 | 如何正确地显示随机消息？

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

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

20 | 幻读是什么， 幻读有什么问题？

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

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

23 | MySQL是怎么保证数据不丢的？

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

25 | MySQL是怎么保证高可用的？

26 | 备库为什么会延迟好几个小时？

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

28 | 读写分离有哪些坑？

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

30 | 答疑文章（二） ： 用动态的观点看加锁

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

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

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

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

35 | join语句怎么优化？

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

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

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

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

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

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

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

43 | 要不要使用分区表？

45 | 自增id用完怎么办？

面试高频知识

01 | 基础架构：一条SQL查询语句是如何执行的 ?

02 | 日志系统：一条SQL更新语句是如何执行的？

03 | 事务隔离：为什么你改了我还看不见？

06 | 全局锁和表锁：给表加个字段怎么有这么多阻碍？

07 | 行锁功过：怎么减少行锁对性能的影响？

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

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

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

15 | 答疑文章（一）：日志和索引相关问题

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

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

20 | 幻读是什么，幻读有什么问题？

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

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

30 | 答疑文章（二）：用动态的观点看加锁

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

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

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