MySQL学习笔记

MySQL 的基本架构

要想探究 MySQL 的执行原理，首先要了解 MySQL 的基础架构，从中清楚看到 SQL 语句在 MySQL 的各个模块中的执行过程。

Server层涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

连接器

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

mysql -h$ip -P$port -u$user -p

连接命令中的mysql是客户端工具，用来跟服务端建立连接。在完成经典的TCP握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

• 如果用户名或密码不对，你就会收到一个”Access denied for user”的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

因此，一个用户成功建立连接后，它的权限会维持在建立连接那一刻读到的权限。如果用管理员账号对这个用户的权限做修改，只要这个用户不断开连接，它的权限就不会发生变化。

那么，什么时候会断开连接呢？

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数wait_timeout控制的，默认值是8小时。

如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。这时候如果你要继续，就需要重连，然后再执行请求了。

长连接：指连接成功后，如果客户端持续有请求，则一直使用同一个连接。

短连接：指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

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

怎么解决这个问题呢？你可以考虑以下两种方案。

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

2. 如果你用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。（推荐第二种）

查询缓存

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

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

但是，一般不建议使用查询缓存，原因是查询缓存很容易失效。只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

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

mysql> select SQL_CACHE * from T where ID=10；

另外，MySQL 8.0版本已经把查询缓存的功能删掉了。

分析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。

首先，分析器会做“词法分析”。MySQL从你输入的”select”这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。

然后，分析器会做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒。

优化器

经过了分析器，MySQL就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的join：

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

• 既可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20。
• 也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

执行器

MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示(在工程实现上，如果命中查询缓存，会在查询缓存放回结果的时候，做权限验证。查询也会在优化器之前调用precheck验证权限)。

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

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

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

1. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。

问题：

如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？

答案：分析器阶段。

网友回答：

《高性能mysql》里提到解析器和预处理器。
解析器处理语法和解析查询, 生成一课对应的解析树。
预处理器进一步检查解析树的合法。比如: 数据表和数据列是否存在, 别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。

SQL执行过程

查询语句

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

• 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 SQL 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

• 通过分析器进行词法分析，提取 SQL 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id=’1’。然后判断这个 SQL 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

• 接下来就是优化器进行确定执行方案，上面的 SQL 语句，可以有两种执行方案：a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

• 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

更新语句

update tb_student A set A.age=19 where A.ID=50;

• 在一张表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句会把表上所有缓存结果清空
• 分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。
• 执行器先找引擎取ID=50这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=50这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
• 执行器拿到引擎给的行数据，把这个 age 值设为19，得到新的一行数据，再调用引擎接口写入这行新数据。
• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
• 执行器生成这个操作的binlog，并把binlog写入磁盘。
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

这里涉及到两个日志，redo log 和 binlog，还有一个概念，两阶段提交。

redo log

redo log 是在有一条记录需要更新时，InnoDB 引擎先将记录写到 redo log 里面，并更新内存。同时， InnoDB引擎会在适当的时候，把这个操作记录更新到磁盘里。

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos和checkpoint之间的是 redo log 剩余的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示 redo log 满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

redo log 有什么用处呢？

一个事务提交后，对Buffer Pool对应的页的修改还未持久到磁盘。如果Mysql突然宕机，这个事务的更改会不会直接消失？

答案是不会。Mysql InnoDB 引擎使用 redo log来保证事务的持久性。redo log 主要做的事情就是记录页的修改，redo log中的每一条记录包含了表空间号、数据页号、偏移量、具体修改的数据，可能会记录修改数据的长度（取决于redo log的类型）。 某个页面某个偏移量处修改了几个字节的值以及被修改的内容是什么，都由redo log记录。 在事务提交时，我们会将 redo log按照刷盘策略刷到磁盘上去，即使Mysql宕机，重启后也能恢复未写入磁盘的数据，从而保证了事务的持久性。 刷盘策略 innodb_flush_log_at_trx_commit 的默认值为 1，设置为 1 时不会丢失任何数据。 redo log 采用循环写的方式进行写入，大小固定，当写到结尾时，会回到开头循环写日志，会出现前面写的日志被覆盖的情况。

redo log 一般设置多大？

redo log太小的话，会导致很快就被写满，然后不得不强行刷redo log，这样WAL机制的能力就发挥不出来了。因此建议将redo log设置为4个文件、每个文件1GB。

redo log写入机制

事务在执行过程中，生成的redo log是要先写到redo log buffer的。

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

在一个事务的更新过程中，日志是要写多次的。插入数据过程中，需要保存日志，但是不能在还没 commit 的时候直接写到 redo log 文件里。所以，redo log buffer 是一块内存，用来先存 redo log 日志的。在执行第一个插入语句时，数据的内存被修改了， redo log buffer 也写入了日志。

真正把日志写到 redo log 文件里，是在执行 commit 语句的时候做的。

redo log可能存在的三种状态，这三种状态分别是：

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

为了控制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阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于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。

在什么时候会把线上生产库设置成“非双1”？

目前知道的场景，有以下这些：

1. 业务高峰期。一般如果有预知的高峰期，DBA会有预案，把主库设置成“非双1”。
2. 备库延迟，为了让备库尽快赶上主库。
3. 用备份恢复主库的副本，应用binlog的过程，这个跟上一种场景类似。
4. 批量导入数据的时候。

一般情况下，把生产库改成“非双1”配置，是设置innodb_flush_logs_at_trx_commit=2、sync_binlog=1000。

LSN

日志逻辑序列号（log sequence number，LSN）：LSN是单调递增的，用来对应redo log的一个个写入点。每次写入长度为length的redo log， LSN的值就会加上length。

LSN也会写到InnoDB的数据页中，来确保数据页不会被多次执行重复的redo log。

组提交

从MySQL看到的TPS是每秒两万的话，每秒就会写四万次磁盘。但是，用工具测试出来，磁盘能力也就两万左右，怎么能实现两万的TPS？

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

从图中可以看到，

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

所以，一次组提交里面，组员越多，节约磁盘IOPS的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。

在并发更新场景下，第一个事务写完redo log buffer以后，接下来这个fsync越晚调用，组员可能越多，节约IOPS的效果就越好。

两阶段提交里虽然把写binlog当成一个动作。但实际上，写binlog分为了两步。

1. 先把binlog从binlog cache中写到磁盘上的binlog文件；
2. 调用fsync持久化

MySQL为了让组提交的效果更好，把redo log做fsync的时间拖到了步骤1之后。也就是说，上面的图变成了这样：

这么一来，binlog也可以组提交了。在执行图5中第4步把binlog fsync到磁盘时，如果有多个事务的binlog已经写完了，也是一起持久化的，这样也可以减少IOPS的消耗。

不过通常情况下第3步执行得会很快，所以binlog的write和fsync间的间隔时间短，导致能集合到一起持久化的binlog比较少，因此binlog的组提交的效果通常不如redo log的效果那么好。

如果你想提升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异常重启时就不会丢数据了，相比之下风险会更小。

为什么binlog cache是每个线程自己维护的，而redo log buffer是全局共用的？

回答：MySQL这么设计的主要原因是，binlog是不能“被打断的”。一个事务的binlog必须连续写，因此要整个事务完成后，再一起写到文件里。

而redo log并没有这个要求，中间有生成的日志可以写到redo log buffer中。redo log buffer中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

binglog

MySQL binlog(binary log 即二进制日志文件) **主要记录了 MySQL 数据库中数据的所有变化(数据库执行的所有 DDL 和 DML 语句)**。因此，我们根据主库的 MySQL binlog 日志就能够将主库的数据同步到从库中。

为什么要用两个日志模块，用一个日志模块不行吗?

这是因为最开始 MySQL 并没有 InnoDB 引擎（InnoDB 引擎是其他公司以插件形式插入 MySQL 的），MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。

binlog 和 redolog 的区别

• binlog 主要用于数据库还原，属于数据级别的数据恢复。主从复制是 binlog 最常见的一个应用场景。redolog 主要用于保证事务的持久性，属于事务级别的数据恢复。
• redolog 属于 InnoDB 引擎特有，binlog 属于所有引擎共有，因为 binlog 是 mysql 的 Server 层实现的。
• redolog 属于物理日志，主要记录的是某个页的修改。binlog 属于逻辑日志，主要记录的是数据库执行的所有 DDL 和 DML 语句。
• binlog 通过追加的方式进行写入，大小没有限制。redolog 采用循环写的方式写入，大小固定，当写到结尾时，会回到开头循环写日志。

binlog 写入机制

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

系统给binlog cache分配了一片内存，每个线程一个，参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

• 每个线程写入其对应的 binlog_cache_size

• 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（重做日志）让 InnoDB 存储引擎拥有了崩溃恢复能力。

binlog（归档日志）保证了 MySQL 集群架构的数据一致性。

虽然它们都属于持久化的保证，但是侧重点不同。

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做。

• 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
• 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binlog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。

原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

数据恢复

假设写入redo log 处于prepare阶段之后、写binlog之前，发生了崩溃（crash），由于此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog还没写，所以也不会传到备库。

那么问题来了，有没有一个极端的情况呢？假设binlog写完，redo log还没commit前发生crash， MySQL异常重启会出现什么现象？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

• 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 redo log 里的事务是否完整，如果完整就提交 redo log, 不完整就回滚事务。

如果 redo log 设置 commit 阶段发生异常，那会不会回滚事务呢？

• 它会判断对应的事务binlog是否存在并完整，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

  

这样就解决了数据一致性的问题。

MySQL怎么知道binlog是完整的?

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

• statement格式的binlog，最后会有COMMIT；

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

另外，在MySQL 5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容的正确性。校验checksum的结果可以处理关于binlog日志由于磁盘原因可能会在日志中间出错的情况。

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

回答：它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log：

• 如果碰到既有prepare、又有commit的redo log，就直接提交；

• 如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务。

生产建议

redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这个参数建议设置成1，这样可以保证MySQL异常重启之后数据不丢失。

sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数也建议设置成1，这样可以保证MySQL异常重启之后binlog不丢失。

定期全量备份的周期取决于系统重要性，有的是一天一备，有的是一周一备。那么在什么场景下，一天一备会比一周一备更有优势呢？或者说，它影响了这个数据库系统的哪个指标？

好处是“最长恢复时间”更短。

在一天一备的模式里，最坏情况下需要应用一天的binlog。

一周一备份，那就需要全备+周一到周日某个时间点的全部binlog用来恢复，时间相比前者需要增加很多；且需要确保整个一周的binlog都完好无损，否则将无法恢复。

系统的对应指标就是 RTO（恢复目标时间）。当然这个是有成本的，因为更频繁全量备份需要消耗更多存储空间，所以这个RTO是成本换来的，就需要根据业务重要性来评估了。

数据的存储

为什么一条 SQL 语句正常执行的时候特别快，但是有时候突然又变慢了？

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

平时执行很快的更新操作，其实就是在写内存和日志，而MySQL偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

那么，什么情况会引发数据库的flush过程呢？

• redo log 写满了，系统停止所有更新操作，把 checkpoint 往前推进， 在这之前需要将移动的位置之间的日志对应的所有脏页都 flush 到磁盘上。（要尽量避免）

• 系统内存不足，需要新的内存页，也就需要淘汰掉一些数据页。如果淘汰的是脏页，就要先将脏页写到磁盘。

  如果刷脏页一定会写盘，就保证了每个数据页有两种状态：

  • 一种是内存里存在，内存里就肯定是正确的结果，直接返回；
  • 另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。
    这样的效率最高。

• MySQL认为系统“空闲”的时候。有机会就刷一点“脏页”。

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

对于第二种情况是很常见的。

InnoDB 存储引擎是以页为单位来管理存储空间的，我们往 MySQL 插入的数据最终都是存在于页中，准确来说是数据页这种类型，为了减少磁盘 IO 开销，还有一个叫做 Buffer Pool(缓冲池)的区域，存在于内存中。当数据对应的页不存在于 Buffer Pool 中的话，mysql 会先将磁盘上的页缓存到 Buffer Pool 中，这样后面我们直接操作的就是 Buffer Pool 中的页，这样大大提高了读写性能。

缓冲池中的内存页有三种状态：

• 第一种是，还没有使用的；
• 第二种是，使用了并且是干净页；
• 第三种是，使用了并且是脏页。

这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

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

所以，InnoDB需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

测试磁盘随机读写的命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

InnoDB的刷盘速度参考因素：脏页比例，redo log写盘速度。

参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%（需多关注脏页比例，不要让其经常接近75%）。InnoDB会根据当前的脏页比例（假设为M），算出一个范围在0到100之间的数字，计算这个数字的伪代码类似这样：

F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

记(当前日志序号-checkpoint) = N，InnoDB 会根据这个 N 计算出一个范围在 0-100 的数字，计算公式为 F2(N)

取 F1(M) 和 F2(N) 的最大值 R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

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，如果设置太小，很快就会被写满，这时候系统不得不停止所有更新，去推进checkpoint。在这种情况下，连change buffer的优化也失效了。因为checkpoint一直要往前推，这个操作就会触发merge操作，然后又进一步地触发刷脏页操作。

数据库表的空间回收

一个InnoDB表包含两部分，即：表结构定义和数据。

表结构

在MySQL 8.0版本以前，表结构是存在以.frm为后缀的文件里。

而MySQL 8.0版本，则已经允许把表结构定义放在系统数据表中了。

表数据

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的：

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

从MySQL 5.6.6版本开始，它的默认值就是ON了。无论是哪个版本，建议设置为 ON。

通过 drop table 命令，系统会直接删除这个文件；如果放在共享空间，即使表删掉了，空间也不会被回收。
而 delete 命令，分为记录的复用和数据页的复用。
删除某条记录，InnoDB 只会把记录标记为删除，如果要插入在这个记录删除区间的记录，可能会复用这个位置。但是磁盘文件大小不会被缩小。
删除一个数据页上的所有记录，整个数据页就都可以被复用了。发生页合并时，另一个空出来的页也能被复用了。

使用 delete 删除不能回收表空间，而没有被使用的空间，看起来像空洞。实际上插入数据使得页分裂也会造成空洞。

重建表

使用alter table A engine=InnoDB命令来重建表。

在MySQL 5.5版本之前，

• MySQL会自动新建一个与表A结构相同的表B
• 按照主键ID递增的顺序，把数据一行一行地从表A里读出来再插入到表B中。
• 表B的主键索引更紧凑，数据页的利用率也更高。但是InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。
• 把表B作为临时表，数据从表A导入表B的操作完成后，用表B替换A，收缩了表A空间。

显然，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表A的话，就会造成数据丢失。因此，在整个DDL过程中，表A中不能有更新。也就是说，这个DDL不是Online的。

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

这个方案在重建表的过程中，允许对表A做增删改操作。重建表的流程：

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

DDL之前是要拿MDL写锁的，这样还能叫Online DDL吗？

alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。为了实现Online，MDL读锁不会阻塞增删改操作，同时 MDL 读锁可以保护这张表，禁止其他线程对这个表同时做 DDL。

上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗IO和CPU资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，我推荐你使用GitHub开源的gh-ost来做。

Online 和 inplace

根据表A重建出来的数据是放在“tmp_file”里的，这个临时文件是InnoDB在内部创建出来的。整个DDL过程都在InnoDB内部完成。对于server层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

如果你有一个1TB的表，现在磁盘间是1.2TB，能不能做一个inplace的DDL呢？

不能。因为，tmp_file也是要占用临时空间的。

重建表的这个语句alter table t engine=InnoDB，其实隐含的意思是：

alter table t engine=innodb,ALGORITHM=inplace;

跟inplace对应的就是拷贝表的方式了，用法是：

alter table t engine=innodb,ALGORITHM=copy;

Online 和 inplace 不是一个概念。如果我要给InnoDB表的一个字段加全文索引，写法是：

alter table t add FULLTEXT(field_name);

这个过程是inplace的，但会阻塞增删改操作，是非Online的。

如果说这两个逻辑之间的关系是什么的话，可以概括为：

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

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

• analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；
• optimize table t 等于alter table + analyze。

MySQL 事务

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

事务的特性

ACID，原子性、一致性、隔离性、持久性

原子性：事务是原子操作，事务内任何一个 SQL 执行失败，所有已执行成功的 SQL 语句必须撤销。通过 undo log 实现。

隔离性：事务操作按照一定粒度隔离，通过 读写锁 + MVCC 来实现。

一致性：事务开始之前和结束之后，数据库的完整性约束没有破坏。

持久性：一个事务一旦被提交，它对数据库中数据的改变就是永久性的。

隔离性与隔离级别

启动事务，查询得到值1	启动事务
事务A	事务B
	查询得到值1
	讲1改成2
查询得到值 V1
	提交事务B
查询得到值 V2
提交事务A
查询得到值 V3

我们来看看在不同的隔离级别下，事务A会有哪些不同的返回结果，也就是图里面V1、V2、V3的返回值分别是什么。

• 若隔离级别是“读未提交”， 则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。

配置方式：将启动参数transaction-isolation的值设置成READ-COMMITTED。你可以用show variables来查看当前的值。

mysql> show variables like 'transaction_isolation';

+-----------------------+----------------+

| Variable_name | Value |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

+-----------------------+----------------+

事务隔离的实现

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

在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。

在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。

这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；

而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

这里提到了“视图”的概念，在MySQL里，有两个视图概念：

• 一个是view。它是一个用查询语句定义的虚拟表。语法：create view….
• 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持读提交和可重复读的隔离级别的实现。

在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从1被按顺序改成了2、3、4，在回滚日志里面就会有类似下面的记录。（事务隔离级别为“可重复读”）

当前值是4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的read-view。如图中看到的，在视图A、B、C里面，这一个记录的值分别是1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于read-view A，要得到1，就必须将当前值依次执行图中所有的回滚操作得到。

当系统判断没有事务再需要用到这些回滚日志时，即当系统内没有比这个回滚日志更早的 read-view 的时候，回滚日志会被删除

关于MVCC的理解

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

可见性规则：

1. InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。

2. 每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。而对于当前版本之前的版本并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。

InnoDB 的每一行都会有三个默认隐式字段 row_id, tx_id, roll_point
1.row_id：若表中没有主键，会使用系统的一个默认递增 row_id 作为主键
2.tx_id：事务 id，记录当前操作此记录的事务
3.roll_point：上一个版本对应的 undo log 的指针

在内部实现中，InnoDB 通过数据行的 row_id 和 Read View 来判断数据的可见 性，如不可见，则通过数据行的 roll_point 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前提交的修改和对事务本身做的修改。

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

4. 视图数组和高水位，就组成了当前事务的一致性视图

5. 数据版本的可见性规则，就是基于数据的行事务id（ row trx_id） 和一致性视图的对比。对比的结论 就是：一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

• 版本未提交，不可见
• 版本已提交，但是是在视图创建后提交的，不可见
• 版本已提交，而且是在视图创建前提交的，可见

基于上边提到的可见性规则，在可重复读的隔离级别下，看一个例子。

-- 基于 MySQL 5.7.37
-- 创建表 t、初始化数据
CREATE TABLE `t` (
`id` INT ( 11 ) NOT NULL,
`k` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` )) ENGINE = INNODB;
INSERT INTO t ( id, k ) VALUES (1, 1),(2, 2);

时间	事务A	事务B	事务C
1	start transaction with consistent snapshot;
2		start transaction with consistent snapshot;
3			update t set k=k+1 where id = 1;
4		update t set k=k+1 where id = 1;
5		select k from t where id = 1;
6	select k from t where id = 1;
7	commit;
8		commit;

假设事务 A,B,C 的事务 id 分别为 100，101，102，三个事务开始之前，id=1这行数据的 tx_id = 90

时间	事务A(100)	事务B(101)	事务C(102)	数据版本
1	start transaction with consistent snapshot; 视图数组[100]，高水位100			数据版本90（1，1）
2		start transaction with consistent snapshot; 视图数组[100，101]，高水位101
3			update t set k=k+1 where id = 1; 视图数组[100，101，102]，高水位102	数据版本102（1，2）
4		update t set k=k+1 where id = 1;		数据版本101（1，3）
5		select k from t where id = 1;
6	select k from t where id = 1;
7	commit;
8		commit;

根据可见性规则判断事务A的查询结果：

• (1,3)还没提交，属于情况1，不可见；
• (1,2)虽然提交了，但是是在视图数组创建之后提交的，属于情况2，不可见；
• (1,1)是在视图数组创建之前提交的，可见。

但是关于事务B的update语句，如果按照一致性读，好像不太对？

事务B的视图是先生成的，之后事务C才提交，不是应该看不见(1,2)吗？它又是怎么算出(1,3)的？

答案和更新逻辑有关。

更新逻辑

事务B 在更新之前会先查询一次数据，这个查询返回 k 值为 1。

但是更新数据时，不能再在历史版本上更新了，不然事务 C 会丢失它的更新。因此，事务B更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

“当前读” 实际上是通过加锁实现的。在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是等到事务结束时才释放，这就是两阶段锁协议。

所以假设事务C没有马上提交，事务B会阻塞直到事务C提交后释放锁，事务B再拿到锁继续执行。

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

其实，除了 update 语句外，select 语句加上锁，也是当前读。

select k from t where id=1 lock in share mode; # 共享锁 
select k from t where id=1 for update; # 排他锁

MVCC 的好处

我觉得可以从数据库的三种并发场景来说，
第一种是读和读的并发，就是两个线程 A 和 B，同时进行读操作，这时不会产生任何并发问题；
第二种是读和写的并发，就是两个线程 A 和 B 分别进行读写操作，这时会对数据库的数据造成以下几个问题：1. 事务隔离性问题 2. 会出现脏读、幻读、不可重复读的问题
第三种是写和写的并发，就是两个线程 A 和 B，同时进行写操作，这种情况下可能会出现数据更新丢失问题，而 MVCC 就是为了解决事务操作中并发安全问题的多版本并发控制技术。它是通过数据库记录中的隐式字段、undo 日志和 Read View 实现的。
MVCC 主要解决三个问题：

1. 读写并发阻塞问题，从而提高数据的并发处理能力
2. MVCC 采用乐观锁的方式实现，降低了死锁的概率
3. 解决了一致性读的问题，也就是事务启动时根据某个条件读取到的数据，直到事务结束时再去执行相同的条件，还是读到同一份数据，不会发生变化

我们在开发过程中主要是根据业务场景使用乐观锁或悲观锁，这两个组合中，MVCC 用来解决读写冲突，乐观锁或悲观锁用来解决写和写的冲突，从而最大程度地提高数据库的并发性能

事务的启动

MySQL的事务启动方式有以下几种：

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

事务启动时机

begin/start transaction命令不是事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句（第一个快照语句），事务才真正启动。

如果要实现马上启动一个事务，要使用 start transaction with consistent snapshot 命令。

读已提交隔离级别下，等同于普通的 start transaction

生产建议

尽量不要使用长事务。

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

在MySQL 5.5及以前的版本，回滚日志是跟数据字典一起放在ibdata文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有20GB，而回滚段有200GB的库。最终只好为了清理回滚段，重建整个库。

方法一：

有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。

因此，建议使用set autocommit=1, 通过显式语句的方式来启动事务。

方法二：

而对于一个需要频繁使用事务的业务，第二种方式每个事务在开始时都不需要主动执行一次 “begin”，减少了语句的交互次数。如果想要使用第二种方式并避免长事务的误用，建议使用commit work and chain语法。

如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行begin语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

你可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过60s的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

你现在知道了系统里面应该避免长事务，如果你是业务开发负责人同时也是数据库负责人，你会有什么方案来避免出现或者处理这种情况呢？

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

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

其次，从数据库端来看：

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

MySQL 索引

B树：为磁盘而生

B树也称B-树，是为磁盘等辅存储设备设计的多路平衡查找树，与二叉树相比，B树的每个非叶节点可以有很多个子树。因此，当总节点数量相同时，B树的高度远远小于AVL树和红黑树，磁盘IO大大减少。

B树的优势除了树高小，还有对访问局部性原理的利用。局部性原理是指，当一个数据被使用时，其附近的数据有较大概率在短时间内被使用。B树将键相近的数据存储在同一个节点，当访问其中某个数据时，数据库会将整个节点读到缓存中；当它临近的数据紧接着被访问时，可以直接在缓存中读取，无需进行磁盘IO；换句话说，B树的缓存命中率更高。

B+树

B树和B+树的区别

• B树中每个节点（包括叶节点和非叶节点）都存储真实的数据，B+树只有叶子节点存储真实的数据，非叶节点只存储键。这里说的真实数据，可能是行的全部数据（如InnoDB的聚簇索引），也可能只是行的主键（如InnoDB的辅助索引），或者是行所在的地址（如MyIsam的非聚簇索引）。
• B树中一条记录只会出现一次，不会重复出现，而B+树的键则可能重复出现——一定会在叶节点出现，也可能在非叶节点重复出现。
• B+树的叶节点之间通过双向链表链接。
• B树中的非叶节点，记录数比子节点个数少1；而B+树中记录数与子节点个数相同

B+树优势

• 更少的IO次数：B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比B数多很多（即阶m更大），因此B+树的高度更低，访问时所需要的IO次数更少。此外，由于每个节点存储的记录数更多，所以对局部性原理的利用更好，缓存命中率更高。
• 更适用于范围查询：在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限。而B+树的范围查询，只需要对链表进行遍历即可。
• 更稳定的查询效率：B树的查询时间复杂度在1到树高之间（分别对应记录在根节点和叶节点），而B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点。

B+树效率估算

对于InnoDB的B+索引来说，树的高度一般在2-4层。（下面是具体的估算，最好理解后记下来）

树的高度是由阶数决定的，阶数越大树越矮；而阶数大小又取决于每个节点可以存储多少条记录。InnoDB中每个节点使用一个页，页的大小为16KB，其中元数据只占128字节左右（包括文件管理头信息，页面头信息等等），大多数空间都用来存储数据。

• 对于非叶节点，记录只包含索引的键和指向下一层节点的指针。假设每个非叶节点页面存储1000条记录，则每条记录大约占用16字节；当索引列是整型或较短的字符串时，这个假设合理。经常有建议说索引列长度不应过大，原因在于：索引列太长，每个节点包含的记录数太少，会导致树太高，索引的效果会大打折扣，而且索引会浪费更多空间。
• 对于叶节点，记录包含了索引的键和值（值可能是行的主键、一行完整数据等），数据量更大。这里假设每个叶节点页面存储100条记录（实际上，当索引为聚簇索引时，这个数字可能不足100；当索引为辅助索引时，可能远大于100）。

对于一棵3层B+树，第一层（根节点）有1个页面，可以存储1000条记录；第二层有1000个页面，可以存储1000×1000条记录；第三层有1000×1000个页面，每个页面存储100条记录，因此可以存储千万条数据。

不适用其他类型索引原因

• 哈希表：链表里存储的value不是有序的，区间查询速度慢；

• 二叉查找树：解决了排序的基本问题，但是无法保证平衡，可能退化成链表；

• 平衡二叉树：通过旋转解决了平衡问题，但是旋转操作效率太低；

• 红黑树：通过舍弃严格平衡和引入红黑节点，解决了AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多；

存储引擎的索引类型

MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引（非聚集索引）”。

InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（聚集索引）”，而其余的索引都作为 辅助索引 ，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

联合索引的使用

我们在进行数据库表设计的时候，往往不会使用物理删除，通常会通过一个 is_deleted 字段实现逻辑删除。假设字段 a 需要设置唯一索引，若使用逻辑删除，就不能单独给这个字段a 设置唯一索引了，而应该设置 uq_index(a,is_deleted)。

假设 is_deleted 为 0 为 未删除，当我们要删除其中一条数据时，将该行数据的 is_deleted 设置为1，这样会出现什么问题呢？该怎么解决这个问题？

如果只将 is_deleted 设置为1，那么此时我们再插入一条数据，该数据标识为未删除。再过一会，我们再将这行数据删除，此时就会违反唯一性约束了。

针对这个问题，有两种解决方案：

• 当需要删除时，将 is_deleted 设置为 null，null 是不走索引的，且 MySQL 中的每个 null 值都是不相同的，因此也就不会受到唯一性约束。
• 当需要删除该行数据时，将 is_deleted 设置为该行数据的 id，每行数据的 id 值都是一定的，因此也就不会违反唯一性约束了。

普通索引与唯一索引的选择

查询过程

假设在 k 上建一个索引：

select id from T where k=5

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

但是这个查找性能差距微乎其微。

普通索引要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

极端情况下，如果 k = 5这条记录刚好是这个数据页最后一个记录，这时可能会需要读取下一个数据页来取下一个记录，这个操作略微复杂。

但是对于整型字段，一个数据页可以放近千个key，出现这种情况概率较低。

更新过程

change buffer

需要更新一个在内存中的数据页时可以直接更新，但是该数据页没在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在 change buffer 中，避免从磁盘读入该数据页。而下次查询需要访问这个数据页时，将数据页读入内存，执行 change buffer 中的相关操作。

change buffer 是可持久化的数据，在内存中有拷贝，也会写入到磁盘。其使用的内存是 buffer pool 里的内存，因此其不能无限增大。

change buffer 的大小可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一性约束更新使用不了 change buffer，只有普通索引可以使用。

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

change buffer 使用场景

• 写多读少的业务，如账单类、日志类。change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

• 普通索引和change buffer的配合使用，对于数据量大的表的更新优化效果显著。

• 当有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那应该特别关注这些表里的索引，尽量使用普通索引，然后把change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

change buffer一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致change buffer丢失呢？change buffer丢失可不是小事儿，再从磁盘读入数据可就没有了merge过程，就等于是数据丢失了。会不会出现这种情况呢？

1.change buffer 有一部分在内存有一部分在ibdata（系统表空间）
做 purge 操作,应该就会把 change buffer 里相应的数据持久化到 ibdata
2.redo log 里记录了数据页的修改以及 change buffer 新写入的信息
如果掉电,持久化的 change buffer 数据已经 purge , 不用恢复。主要分析没有持久化的数据
情况又分为以下几种:
(1) change buffer 写入, redo log 虽然做了 fsync 但未 commit ,binlog 未 fsync 到磁盘,这部分数据丢失
(2) change buffer 写入, redo log 写入但没有 commit, binlog 以及fsync到磁盘,先从 binlog 恢复 redo log,再从redo log恢复change buffer
(3)change buffer写入,redo log和binlog都已经fsync.那么直接从redo log里恢复。

merge的过程是否会把数据直接写回磁盘？

merge的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版本的数据页）；
2. 从change buffer里找出这个数据页的change buffer 记录(可能有多个），依次应用，得到新版数据页；
3. 写redo log。这个redo log包含了数据的变更和change buffer的变更。

到这里merge过程就结束了。这时候，数据页和内存中change buffer对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。

隐式转换

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

隐式类型转换

假设你现在维护了一个交易系统，其中交易记录表tradelog包含交易流水号（tradeid）、交易员id（operator）、交易时间（t_modified）等字段。为了便于描述，我们先忽略其他字段。这个表的建表语句如下：

mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

trade_id在数据库中是varchar类型的，看这个全表扫描的语句：

mysql> select * from tradelog where tradeid=110717;

对于优化器来说，这个语句相当于：

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

另一个问题是，id的类型是int，如果执行下面这个语句，是否会导致全表扫描呢？

select * from tradelog where id="83126";

答案是不会，这个语句相当于：

mysql> select * from tradelog where id = CAST("83126" AS signed int);

可以看到这里只是对参数进行了隐式转换，还是会走id这个索引。

隐式字符编码转换

假设系统有表trade_detail，用于记录交易的操作细节。为了便于量化分析和复现，我往交易日志表tradelog和交易详情表trade_detail这两个表里插入一些数据。

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

这时候，如果要查询id=2的交易的所有操作步骤信息，SQL语句可以这么写：

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/

1. 第一行显示优化器会先在交易记录表tradelog上查到id=2的行，这个步骤用上了主键索引，rows=1表示只扫描一行；
2. 第二行key=NULL，表示没有用上交易详情表trade_detail上的tradeid索引，进行了全表扫描。

这个语句的执行流程如下：

• 第1步，是根据id在tradelog表里找到L2这一行；
• 第2步，是从L2中取出tradeid字段的值；
• 第3步，是根据tradeid值到trade_detail表中查找条件匹配的行。explain的结果里面第二行的key=NULL表示的就是，这个过程是通过遍历主键索引的方式，一个一个地判断tradeid的值是否匹配

这里问题出现在第3步，这两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询的时候用不上关联字段的索引。

如果单独把第3步改成 SQL 语句的话，那就是：

mysql> select * from trade_detail where tradeid=$L2.tradeid.value; 

其中，$L2.tradeid.value的字符集是utf8mb4。

字符集utf8mb4是utf8的超集，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。

实际上这个语句等同于下面这个写法：

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 

对索引字段做了函数操作，优化器就放弃了走搜索树功能。

怎么优化这个查询语句呢？

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

• 比较常见的优化方法是，把trade_detail表上的tradeid字段的字符集也改成utf8mb4，这样就没有字符集转换的问题了。

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

• 如果能够修改字段的字符集的话，是最好不过了。但如果数据量比较大， 或者业务上暂时不能做这个DDL的话，那就只能采用修改SQL语句的方法了。主动把 l.tradeid转成utf8，就避免了被驱动表上的字符编码转换。

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

索引维护

B+树为了维护索引有序性，在插入新值时需要做必要的维护。

• 假如此时插入的新值是中间值，需要逻辑上挪动后面的数据，空出位置。
• 可能会出现页分裂：如果当前插入所在的数据页满了，根据B+树的算法，需要申请一个数据页，挪动部分数据过去，影响性能；原本放到一个页的数据，现在分到两个页种，整体空间利用率降低约50%。
• 页合并：当相邻两个页删除了数据，利用率很低后，会将数据页合并。

索引下推

如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。那么，SQL语句是这么写的：

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

执行流程：

首先这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录ID3。

在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。

而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。也就是说，MySQL 5.6 以后，会过滤掉 age 不为 10 且 ismale 不为 1 的数据。

如果要重建索引 k，你的两个SQL语句可以这么写：

alter table T drop index k;
alter table T add index(k);

如果要重建主键索引，也可以这么写：

alter table T drop primary key;
alter table T add primary key(id);

我的问题是，对于上面这两个重建索引的作法，说出你的理解。如果有不合适的，为什么，更好的方法是什么？

重建索引k的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

为什么要重建索引呢？什么情况下需要重建索引？

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

优化器是怎么选择索引的？

我们先建一个简单的表，表里有a、b两个字段，并分别建上索引：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB；

然后，我们往表t中插入10万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到(100000,100000,100000)。

我是用存储过程来插入数据的，这里我贴出来方便你复现：

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

接下来，我们分析一条SQL语句：

mysql> select * from t where a between 10000 and 20000;

使用 explain 来分析这条语句，发现确实是走了 a 索引。

再做如下操作：

session A	session B
start transaction with consistent snapshot;
	delete from t; call idata();
	explain select * from t where a between 10000 and 20000;
commit;

session B把数据都删除后，又调用了 idata这个存储过程，插入了10万行数据。这时候 session B 的查询语句就不会选择索引 a 了。

使用慢查询日志查看具体的执行情况。

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

• 第一句，是将慢查询日志的阈值设置为0，表示这个线程接下来的语句都会被记录入慢查询日志中；
• 第二句，Q1是session B原来的查询；
• 第三句，Q2是加了force index(a)来和session B原来的查询语句执行情况对比。

发现我们没有使用 foece index 的时候，MySQL用错了索引，导致了更长的执行时间。

优化器逻辑

优化器会结合扫描行数、是否使用临时表、是否排序等因素综合判断。扫描行数越少，访问磁盘数据次数越少，消耗CPU资源越少。

使用 explain 分析出 rows 这个字段值下的扫描行数是多少，这个数据是优化器预估的，而非真实的数据。

优化器不仅会考虑扫描行数，还会考虑回表次数及代价。这个例子中，优化器可能认为直接扫描主键索引更快，但是从执行时间上看，这个选择不是最优的。

analyze table t #可以用来重新统计索引信息。

重新统计完后，就能成功走索引 a 了。

依然是基于这个表t，我们看看另外一个语句：

mysql> select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;

使用索引a 进行查询，就是扫描1000行；而使用索引b 进行查询，就需要扫描50001行。但使用 explain 分析，这次优化器选择了索引b，而 rows 字段显示为 50198。

原因是，优化器任务使用索引b 可以避免排序。

# 优化方法
# 1. force index 
mysql> select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;

# 2. order by b,a
mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;

# 3.limit 100
mysql> select * from  (select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 100)alias limit 1;

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

如何给字符串字段添加索引？

前缀索引

前缀索引可用在给可变字符串定义前缀长度，即节省空间，又不用额外增加太多查询成本。

如何确定应该使用多长的前缀呢？

你可以使用下面这个语句，算出这个列上有多少个不同的值：

mysql> select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用这个语句：

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;

当然，使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如5%。然后，在返回的L4~L7中，找出不小于 L * 95%的值，假设这里L6、L7都满足，你就可以选择前缀长度为6。

其他方式

如果遇到前缀的区分度不够好时，应该怎么办呢？

比如我们国家的身份证号，一共18位，其中前6位是地址码，所以同个县的人的身份证号前6位一般是相同的。但是如果要维护一个市的身份证号，那这个长度就到前12位才能满足区分度要求了。

• 倒序存储

mysql> select field_list from t where id_card = reverse('input_id_card_string');

身份证的后6位更容易区分，倒序存储后，再创建长度为6的前缀索引，解决。

• hash 字段。在表上创建一个整数字段，保存身份证的校验码，给这个字段创建索引。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

由于校验码可能存在冲突，也就是说两个不同的身份证号通过crc32()函数得到的结果可能是相同的，所以你的查询语句where部分要判断id_card的值是否精确相同。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样，索引的长度变成了4个字节，比原来小了很多。

使用倒序存储和使用hash字段这两种方法的异同点：

相同点：不支持范围查询。

不同点：

1. 倒序存储方式在主键索引上，不会消耗额外的存储空间。hash字段需要增加一个字段。但是倒序存储很大可能会使用大于4个字节的前缀长度，这个消耗和hash字段差不多抵消。
2. 倒序方式每次写和读都需要额外调用一次 reverse 函数，而 hash 字段需要额外调用 crc32() 函数。reverse函数额外消耗的 CPU 资源小一点。
3. hash字段的查询性能更稳定。虽然 crc32 算出来的值有冲突概率，但是概率较小，每次查询的平均扫描行数接近 1。倒序存储其实还是使用前缀索引方式，还是会增加扫描行数。

如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号@gmail.com”, 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。

系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登录验证这个行为的话，你会怎么设计这个登录名的索引呢？

从优化索引空间角度看，

由于这个学号的规则，无论是正向还是反向的前缀索引，重复度都比较高。因为维护的只是一个学校的，因此前面6位（其中，前三位是所在城市编号、第四到第六位是学校编号）其实是固定的，邮箱后缀都是@gamil.com，因此可以只存入学年份加顺序编号，它们的长度是9位。

而其实在此基础上，可以用数字类型来存这9位数字。比如201100001，这样只需要占4个字节。其实这个就是一种hash，只是它用了最简单的转换规则：字符串转数字的规则，而刚好我们设定的这个背景，可以保证这个转换后结果的唯一性。

从业务量预估优化和收益看，

一个学校每年预估2万新生，50年才100万记录，能节省多少空间，直接全字段索引。省去了开发转换及局限性风险，碰到超大量迫不得已再用后两种办法。

MySQL 锁

全局锁

全局锁是对整个数据库实例加锁。

使用场景：

做全库逻辑备份，把整库每个表都 select 出来存成文本。

加全局读锁命令是：Flush tables with read lock (FTWRL)

FTWRL 执行的时候要刷脏页的数据到磁盘，因为要保持数据的一致性 ，理解的执行FTWRL时候是 所有事务 都提交完毕的时候

在备份过程中整个库完全处于只读状态。

• 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
• 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

为什么备份过程中要加锁呢？不加锁会怎样？

不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

当存储引擎支持可重复读的隔离级别且当前数据库中的所有表使用事务引擎，我们可以利用一致性视图进行备份，官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

既然要全库只读，为什么不使用set global readonly=true的方式呢？

1. 在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global变量的方式影响面更大，我不建议你使用。

2. 全库只读 readonly = true 还有个情况在 slave 上 如果用户有超级权限的话 readonly 是失效的

3. 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

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

表锁

使用方法：lock tables … read/write，使用 unlock tables 主动释放锁，或者在客户端断开时自动释放。

但 lock tables 除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

MDL

不需要显式使用，在访问一个表时会自动加上。

在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

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

MySQL 5.6 支持 online ddl,对表字段的修改不会阻塞读写（事实上是近似的online，仍有极少时间阻塞）。online ddl的过程：

• 获取 MDL 写锁(短暂阻塞，这里获取写锁再降级是为了防止并发表结构修改)
• 降级成 MDL 读锁
• 真正做 DDL（可以理解为prepare状态的）
• 升级成 MDL 写锁（短暂阻塞，将DDL操作提交）
• 释放 MDL 锁

注意，大多数的 ddl 都支持 online，但也有不支持的，如：添加全文索引，空间索引

给一个小表加个字段，导致整个库挂了，这是出了什么问题？

申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。

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

# MariaDB 和 AliSQL 支持
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

备份一般都会在备库上执行，你在用–single-transaction方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

假设这个DDL是针对表t1的， 这里我把备份过程中几个关键的语句列出来：

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

在备份开始的时候，为了确保RR（可重复读）隔离级别，再设置一次RR隔离级别(Q1);

启动事务，这里用 WITH CONSISTENT SNAPSHOT确保这个语句执行完就可以得到一个一致性视图（Q2)；

设置一个保存点，这个很重要（Q3）；

show create 是为了拿到表结构(Q4)，然后正式导数据 （Q5），回滚到SAVEPOINT sp，在这里的作用是释放 t1的MDL锁 （Q6。当然这部分属于“超纲”，上文正文里面都没提到。

DDL从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。

参考答案如下：

1. 如果在Q4语句执行之前到达，现象：没有影响，备份拿到的是DDL后的表结构。
2. 如果在“时刻 2”到达，则表结构被改过，Q5执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump终止；
3. 如果在“时刻2”和“时刻3”之间到达，mysqldump占着t1的MDL读锁，binlog被阻塞，现象：主从延迟，直到Q6执行完成。
4. 从“时刻4”开始，mysqldump释放了MDL读锁，现象：没有影响，备份拿到的是DDL前的表结构。

行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。

但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。

两阶段锁协议

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

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

死锁和死锁检测

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

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

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

第一种方法的默认等待时间为50s，这个时间对于某些在线服务是无法接受的，但是又不能将其设置为很小的值，如果不是死锁而是正常的锁等待的话，会出现误伤。

正常情况下，我们使用第二组策略。

当所有事务都要更新同一行，每个被堵住的线程都要判断一次是否由于自己的加入导致了死锁。假设有1000个并发线程需要同时更新同一行，那么死锁检测的操作是100万这个量级的，这期间需要耗费大量的CPU资源。

如何解决由这种热点行更新导致的性能问题？

1. 确保这个业务不出现死锁，可以临时把死锁检测关掉。（有风险，不采用）
2. 控制并发度。利用中间件控制流量，对于相同行的更新，在进入数据库前排队。
3. 将热点行数据拆分成逻辑上的多行来减少锁冲突，但业务逻辑复杂。

如果你要删除一个表里面的前10000行数据，有以下三种方法可以做到：

• 第一种，直接执行delete from T limit 10000;
• 第二种，在一个连接中循环执行20次 delete from T limit 500;
• 第三种，在20个连接中同时执行delete from T limit 500

哪一种方法最好？说说理由。

第二种方式是相对较好的。

第一种方式（即：直接执行delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。

第三种方式（即：在20个连接中同时执行delete from T limit 500），会人为造成锁冲突。

Next-key Lock

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

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

备注：这篇文章中，如果没有特别说明，我们把间隙锁记为开区间，把next-key lock记为前开后闭区间。

这个suprenum是InnoDB给每个索引加了一个不存在的最大值suprenum，为了保证next-key lock的前开后闭区间。

加锁规则

此规则适用于5.x系列<=5.7.24，8.0系列 <=8.0.13

1. 原则1：加锁基本单位是 next-key lock。前开后闭区间。（但是具体执行时，会分成间隙锁和行锁两段来执行）
2. 原则2：查找过程中访问到的对象才会加锁
3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

来看一个例子理解以上规则。

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);

案例一：等值查询间隙锁

sessionA	sessionB	sessionC
begin; update t set d = d+1 where id = 7;
	insert into t values(8,8,8); (blocked)
		update t set d = d+1 where id = 10; (Query OK)

由于表t中没有id=7的记录，所以用我们上面提到的加锁规则判断一下的话：

1. 加锁单位是next-key lock，session A加锁范围就是(5,10]；
2. 这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是(5,10)。

案例二：非唯一索引等值锁

sessionA	sessionB	sessionC
begin; select id from t where c = 5 lock in share mode;
	update t set d = d+1 where id = 5; (Query OK)
		insert into t values(7,7,7); (blocked)

这里session A要给索引c上c=5的这一行加上读锁。

1. 根据原则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock。
2. 要注意c是普通索引，因此仅访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock。
3. 但是同时这个符合优化2：等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁(5,10)。
4. 根据原则2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么session B的update语句可以执行完成。

但session C要插入一个(7,7,7)的记录，就会被session A的间隙锁(5,10)锁住。

lock in share mode只锁覆盖索引， for update会给主键索引上满足条件的行加上行锁。锁是加在索引上的，如果你要用lock in share mode来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将session A的查询语句改成select d from t where c=5 lock in share mode。

begin;
select id from t where c in(5,20,10) lock in share mode;

在查找c=5的时候，先锁住了(0,5]。但是因为c不是唯一索引，为了确认还有没有别的记录c=5，就要向右遍历，找到c=10才确认没有了，这个过程满足优化2，所以加了间隙锁(5,10)。

同样的，执行c=10这个逻辑的时候，加锁的范围是(5,10] 和 (10,15)；执行c=20这个逻辑的时候，加锁的范围是(15,20] 和 (20,25)。

通过这个分析，我们可以知道，这条语句在索引c上加的三个记录锁的顺序是：先加c=5的记录锁，再加c=10的记录锁，最后加c=20的记录锁。

这些锁是“在执行过程中一个一个加的”，而不是一次性加上去的。

案例三：主键索引范围锁

对于我们这个表t，下面这两条查询语句，加锁范围相同吗？

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

语句2：

1. 开始执行的时候，要找到第一个id=10的行，因此本该是next-key lock(5,10]。 根据优化1， 主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁。
2. 范围查找就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]

分析一下这条查询语句的加锁范围：

begin;
select * from t where id>9 and id<12 order by id desc for update;

利用上面的加锁规则，我们知道这个语句的加锁范围是主键索引上的 (0,5]、(5,10]和(10, 15)。

1. 首先这个查询语句的语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个id<12的值”。
2. 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到id=12的这个值，只是最终没找到，但找到了(10,15)这个间隙。
3. 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5这一行，所以会加一个next-key lock (0,5]。

也就是说，在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方法。

案例四：非唯一索引范围锁

sessionA	sessionB	sessionC
begin; select * from t where c>=10 and c<11 for update;
	insert into t values(8,8,8); (blocked)
		update t set d = d+1 where c = 15; (blocked)

在第一次用c=10定位记录的时候，索引c上加了(5,10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终sesion A加的锁是，索引c上的(5,10] 和(10,15] 这两个next-key lock。

这里需要扫描到c=15才停止扫描，是合理的，因为InnoDB要扫到c=15，才知道不需要继续往后找了。

案例五：唯一索引范围锁bug

sessionA	sessionB	sessionC
begin; select * from t where id>10 and id<=15 for update;
	update t set d = d+1 where id = 20; (blocked)
		insert into t values(16,16,16); (blocked)

session A是一个范围查询，按照原则1的话，应该是索引id上只加(10,15]这个next-key lock，并且因为id是唯一键，所以循环判断到id=15这一行就应该停止了。

但是实现上，InnoDB会往前扫描到第一个不满足条件的行为止，也就是id=20。而且由于这是个范围扫描，因此索引id上的(15,20]这个next-key lock也会被锁上。

案例六·：limit语句加锁

limit 语句可以判断满足条件的语句有n条，然后直接返回，不会走到判断最后一个值不满足等值条件。

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

<=到底是间隙锁还是行锁？

其实，这个问题，你要跟“执行过程”配合起来分析。在InnoDB要去找“第一个值”的时候，是按照等值去找的，用的是等值判断的规则；找到第一个值以后，要在索引内找“下一个值”，对应于我们规则中说的范围查找。

所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。

那么，一个空表有间隙吗？这个间隙是由谁定义的？你怎么验证这个结论呢？

一个空表就只有一个间隙。比如，在空表上执行：

begin;
select * from t where id>1 for update;

这个查询语句加锁的范围就是next-key lock (-∞, supremum]。

MVCC➕Next-key-Lock 防止幻读

InnoDB 存储引擎在 RR 级别下通过 MVCC 和 Next-key Lock 来解决幻读问题：

1、执行普通 select，此时会以 MVCC 快照读的方式读取数据

在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读”

2、执行 select…for update/lock in share mode、insert、update、delete 等当前读

在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 Next-key Lock 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读

解决幻读的方法

解决幻读的方式有很多，但是它们的核心思想就是一个事务在操作某张表数据的时候，另外一个事务不允许新增或者删除这张表中的数据了。解决幻读的方式主要有以下几种：

1. 将事务隔离级别调整为 SERIALIZABLE 。
2. 在可重复读的事务级别下，给事务操作的这张表添加表锁。
3. 在可重复读的事务级别下，给事务操作的这张表添加 Next-key Lock（Record Lock+Gap Lock）。

业务设计问题

业务上有这样的需求，A、B两个用户，如果互相关注，则成为好友。设计上是有两张表，一个是like表，一个是friend表，like表有user_id、liker_id两个字段，我设置为复合唯一索引即uk_user_id_liker_id。语句执行逻辑是这样的：

以A关注B为例：
第一步，先查询对方有没有关注自己（B有没有关注A）
select * from like where user_id = B and liker_id = A;

如果有，则成为好友
insert into friend;

没有，则只是单向关注关系
insert into like;

但是如果A、B同时关注对方，会出现不会成为好友的情况。因为上面第1步，双方都没关注对方。第1步即使使用了排他锁也不行，因为记录不存在，行锁无法生效。请问这种情况，在MySQL锁层面有没有办法处理？

先根据上边的场景建两张表。

CREATE TABLE `like` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `liker_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id_liker_id` (`user_id`,`liker_id`)
) ENGINE=InnoDB;

CREATE TABLE `friend` (
  id` int(11) NOT NULL AUTO_INCREMENT,
  `friend_1_id` int(11) NOT NULL,
  `firned_2_id` int(11) NOT NULL,
  UNIQUE KEY `uk_friend` (`friend_1_id`,`firned_2_id`)
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

session 1(A关注B)	session 2(B关注A)
begin; select * from like where user_id = B and liker_id = A;（返回空）
	begin; select * from like where user_id = A and liker_id = B;（返回空）
	insert into like(user_id,liker_id) values(B,A);
insert into like(user_id,liker_id) values(A,B);
commit;
	commit;

也就是说，在并发情况下，A和B在查询对方有没有关注自己的时候 select 语句查出来的结果为空，因此，session 1的逻辑就是“既然B没有关注A，那就只插入一个单向关注关系”。session 2也同样是这个逻辑。

解决方法如下：

首先，要给“like”表增加一个字段，比如叫作 relation_ship，并设为整型，取值1、2、3。

值是1的时候，表示user_id 关注 liker_id;
值是2的时候，表示liker_id 关注 user_id;
值是3的时候，表示互相关注。

然后，当 A关注B的时候，逻辑改成如下所示的样子：

应用代码里面，比较A和B的大小，如果A<B，就执行下面的逻辑

mysql> begin; /*启动事务*/
insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
select relation_ship from `like` where user_id=A and liker_id=B;
/*代码中判断返回的 relation_ship，
  如果是1，事务结束，执行 commit
  如果是3，则执行下面这两个语句：
  */
insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
commit;

如果A>B，则执行下面的逻辑

mysql> begin; /*启动事务*/
insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
select relation_ship from `like` where user_id=B and liker_id=A;
/*代码中判断返回的 relation_ship，
  如果是2，事务结束，执行 commit
  如果是3，则执行下面这两个语句：
*/
insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
commit;

这个设计里，让“like”表里的数据保证user_id < liker_id，这样不论是A关注B，还是B关注A，在操作“like”表的时候，如果反向的关系已经存在，就会出现行锁冲突。

然后，insert … on duplicate语句，确保了在事务内部，执行了这个SQL语句后，就强行占住了这个行锁，之后的select 判断relation_ship这个逻辑时就确保了是在行锁保护下的读操作。

操作符 “|” 是按位或，连同最后一句insert语句里的ignore，是为了保证重复调用时的幂等性。

这样，即使在双方“同时”执行关注操作，最终数据库里的结果，也是like表里面有一条关于A和B的记录，而且relation_ship的值是3， 并且friend表里面也有了A和B的这条记录。

这里提到了insert … on duplicate语句，但是这个语句可能会造成 death lock 问题。

如果有两个事务并发的执行同样的语句，那么就会产生death lock，如：

时间	session 1	session 2
T1	begin; insert on duplicate key
T2	key已存在，获取该记录的S锁	begin; insert on duplicate key
T3	对读取的记录进行修改	key已存在，获取该记录的S锁
T4		对读取的记录进行修改
T5	把修改写入存储引擎，给该记录加上 X 锁，session2存在 S 锁，等待 session2 释放 S 锁
T6		把修改写进存储引擎，给该记录加上 X 锁，session1 存在 S 锁，等待 session1 释放 S 锁
T7		死锁

那么，再来看看为什么上面的方法不会存在死锁问题呢？

原因是加入了 relation_ship这个字段，在并发情况下 insert 的时候，实际上 insert 的是不同行。