MySQL实战45讲

🅰 本文内容来源于极客时间专栏：《MySQL 实战45讲》

🅱 本文脚注的作用是对前一段描述内容的解释说明，并且放在当前章节的最后，而不是文末。更好的阅读体验推荐typora

1-基础架构：一条SQL是如何执行的

1	select * from T where ID=10;

1.1-MySQL 基础架构

如下图所示：

对比下hive的架构是：解析器-编译器-优化器-执行器

A-连接器

客户端连接连接器使用的协议是 TCP 协议
连接完成后，如果没有后续动作，这个连接就会处于sleep状态 ,也就是空闲状态（另外一个状态是 Query）
客户端如果 wait_timeout（默认是8h）时间内没的动静，连接就会断开

MySQL 长短连接：

长连接：连接成功后，一直持有这个连接，向 Server 发起请求
短连接：执行完几次查询之后就断开连接，下次查询的时候在重新建立一个

💁♂ 由于连接的过程是比较复杂的，开发中需要尽量减少连接动作，也就是使用长连接

MySQL 在执行的过程中，临时使用的内存是管理在连接对象里面的，这些资源在连接断开的时候才释放，所以有时MySQL的内存涨的很快，而内存占用过大可能会导致被 Kill掉，表现为MySQL异常重启。如何避免：

定期断开长连接
MySQL5.7 之后的版本，在执行一个比较大的操作后，执行mysql_reset_connection来初始化连接(该操作不需要重连和鉴权，就恢复到刚创建完的状态)

B-执行器

执行器是Server和存储引擎交互的部分，Server会调用存储引擎的接口，如对于

select * from T where ID=10; ID 字段没有索引

1⃣ 调用 InnoDB引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是，则将这行存在结果集中

2⃣ 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行(取满足条件的第一行和满足条件的下一行这个逻辑在存储引擎中已经实现)

3⃣ 执行器将上述遍历过程中所有满足条件的行，组成结果集返回给客户端

2-日志系统：一条SQL更新是如何执行的

MySQL中有2个重要的日志：

redo log 重做日志 引擎层日志(InnoDB引擎特有)
bin log 归档日志 server 层日志

redo log	bin log
引擎模块，InnoDB引擎特有	server模块，所有引擎都可用
循环写	追加写
物理日志（在某个页面做了什么修改）	逻辑日志（语句的更改逻辑，如给`id=2`这一行的c字段加1）

2.1-redolog

对于一个更新操作来说，如果每次更新都需要立刻写磁盘，则MySQL的存储引擎需要找到被更新的记录，然后更新。这个先定位再更新的机制必然有一定的成本（查找成本+IO成本¹）。MySQL尝试使用下面的思路来提升 更新效率

WAL : Write - Ahead Logging ：先写日志，后写磁盘

当涉及更新一条记录的时候，InnoDB引擎会将记录写到 redo log 里，并且更新内存。InnoDB引擎会在适当（系统比较空）的时候，将操作记录刷写到磁盘。在细节上：MySQL了会设置固定大小的 redo log，比如配置一组4个文件，每个文件1GB

有了redo log， InnoDB可以实现 crash-safe（保证在数据库发生异常重启之后，之前提交的记录不会丢失）。redo log的写入被拆分为2个步骤： prepare + commit 也就是两阶段提交：

两阶段提交实现了 bin log 和 redo log 的逻辑一致

对于 update T set c=c+1 where ID=2 ; id 是主键 , 更新的细节如下：

1⃣ 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回

2⃣ 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据

3⃣ 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务

4⃣ 执行器生成这个操作的 binlog，并把 binlog 写入磁盘

5⃣ 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成

可以通过反证法的方式来证明，无论是先写redolog再写binlog，还是先写binlog再写redolog都会导致崩溃恢复后的数据不一致。而两阶段提交可以保证数据一致性

2.2-bin log

bin log 是MySQL server层维护的一种二进制日志，记录所有的DML和DDL。作用有：

1⃣ 复制： MySQL Master端开启binlog，slave 可以获得数据备份，同样可以实现数据同步（如数据采集）

2⃣ 数据恢复：根据binlog来回放历史数据

binlog 包含两类文件

二进制日志索引文件(.index)：记录所有的二进制文件
二进制日志文件(.00000*)：记录所有 DDL 和 DML 语句事件

-- 查看binlog的状态：查看当前二进制日志文件的状态信息，显示正在写入的二进制文件，及当前position
show master status;

-- 查看二进制索引文件和二进制日志文件存储位置
show variables like '%log_bin%';

-- 查看binlog文件列表
show binary logs;


-- 解析binlog 日志文件
show binlog events in  'mysql-bin.000238';

show master status 命令结果如下：

Executed_Gtid_Set : 数据发生变化，当前值就会发生变化

binlog 日志格式

Statement 模式：基于 SQL 语句的复制(statement-based replication-SBR)，日志量小，会产生非确定性（比如使用不确定的函数now()）
Row 模式：基于行的复制(row-based replication-RBR)，日志量大，可以保证一致性，记录了记录修改前后的样子
Mixed 模式：混合模式复制(mixed-based replication-MBR)，根据SQL语句的类型自动选择Statement或Row格式

参考1

参考2

¹. IO成本就是寻址时间和上下文切换所需要的时间，最主要是用户态和内核态的上下文切换。我们知道用户态是无法直接访问磁盘等硬件上的数据的，只能通过操作系统去调内核态的接口，用内核态的线程去访问。这里的上下文切换指的是同进程的线程上下文切换，所谓上下文就是线程运行需要的环境信息。 1⃣ 首先，用户态线程需要一些中间计算结果保存CPU寄存器，保存CPU指令的地址到程序计数器（执行顺序保证），还要保存栈的信息等一些线程私有的信息。2⃣ 然后切换到内核态的线程执行，就需要把线程的私有信息从寄存器，程序计数器里读出来，然后执行读磁盘上的数据。读完后返回，又要把线程的信息写进寄存器和程序计数器。 3⃣ 切换到用户态后，用户态线程又要读之前保存的线程执行的环境信息出来，恢复执行。这个过程主要是消耗时间资源 ↩

3-事务隔离：为什么你改了，我还看不见

在 MySQL 中，事务支持是在引擎层实现的

3.1-隔离性和隔离级别

SQL 标准的事务隔离级别包括：

读未提交（read uncommitted）
读提交（read committed）
可重复读（repeatable read）(MySQL 默认的隔离级别)
串行化（serializable ）

结合下图，说说上面前3种隔离级别下，v1,v2,v3 的值

1⃣ 读未提交：V1=2,v2=2,v3=2

2⃣ 读已提交：V1=1,v2=2,v3=2

3⃣ 可重复读：V1=1,v2=1,v3=2

4⃣ 可串行化：事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看 V1=1,v2=1,v3=2

1
2
3

-- 查看 MySQL当前的隔离级别设置值：
show variables like 'tx_isolation' 
show variables like 'transaction_isolation' -- mysql5.7以上version

3.2-事务隔离的实现

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

1⃣ “读未提交”隔离级别下直接返回记录上的最新值，没有视图概念

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

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

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

拿可重复读来说：假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录

当前值是4，不同时刻启动的事务会有不同的 read-view（事务视图），对于是三个视图记录的值分别不同：

read-view A 1
read-view B 2
read-view C 4

MVCC（ Multi-Version Concurrency Control） : 同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制。其主要的思想是在读取数据时创建数据的一份快照，并对该快照进行读取，而不是直接在原始数据上进行操作。这样做可以实现非阻塞的读操作，从而提高并发性能

对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到

💁♂ 建议尽量不要使用长事务：

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

3.3-事务启动方式

事务启动的方式如下：

1⃣ : 显示启动事务语句，begin / start transaction 开启事务，commit / rollback 结束事务（提交/回滚）

2⃣ : set autocommit = 0 ,将线程的自动提交off ，意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接， 这会导致长事务。

💁♂ 建议：set autocommit = 1（开启自动提交），通过显式语句的方式来启动事务。commit work and chain，则是提交事务并自动启动下一个事务，这种方式可以省去了再次执行 begin 语句的开销

1 2	-- 你可以在 information_schema 库的 innodb_trx 这个表中查询长事务 select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

4-深入浅出索引 (上)

4.1-索引的常见模型

哈希表：只是适合等值查询的场景，对范围查询不友好，严重依赖内存（size 瓶颈）
有序数组：等值查询、范围查询都很快，对数据更新（插入/删除后数据）不友好
N叉树：在读写上有性能有点，适配磁盘的访问模式
跳表：可以说是有序的带层次的(在数据的基础上一层层加索引)链表

单纯从存储上来说，LSM（log structure merge）树(日志结构合并树) 这种新的存储引擎也具备一定的优势，关于LSM这种结构可以参考《design data intensive application》这本书的第三章节，从追加日志，到SSTable ,到 LSM的过程

4.2-InnoDB的索引模型

InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。举个例子来说：

-- table schema
create table T
(
    id   int primary key,
    k    int not null,
    name varchar(16),
    index (k)
) engine = InnoDB;
-- 插入数据
insert into T (id,k)
values (100, 1),
       (200, 2),
       (300, 3),
       (500, 5),
       (600, 6)
;

对于上述的索引和数据，形成2颗索引树：

基于主键索引的查询和基于普通索引的查询的区别：

🅰 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树

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

InnoDB引擎中，主键索引默认就是聚簇索引（clustered index）

4.3-索引维护

这里我们讨论2种情况

1⃣ 插入新行 id = 700 ，只是需要在R5的后面插入新记录

2⃣ 插入新行 id = 400 ，这种情况下又分成2种情况：

R3~R5所在的数据页没有满，需要移动R4,R5的数据，给id=400空出位置
R3~R5所在的数据页满了，会发生页分裂¹⁰

页分裂会导致性能下降，同时空间利用率下降，需要申请新的数据页,将R4，R5移动到新的分页，同时如果相邻的2个页由于数据删除，数据页会进行合并

建表时维护自增主键的优势：

1⃣ 不会发生页分裂

2⃣ 自增主键一般占用的空间比较小，二级索引存储主键时，占用存储也比较小

💁♂ 无论是删除主键索引还是创建主键索引，都会导致表重建，而重建普通索引可以达到节省空间的目的，如果你有重建主键索引的需求：alter table T engine=InnoDB 会触发MySQL重建表，并进行碎片处理，达到节省空间的目的

5-深入浅出索引 (下)

对于上面的表插入如下数据：

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

-- 数据查询
select * from T where k between 3 and 5;

问执行上述的查询，需要执行几次树的搜索操作，会扫描多少行？SQL 的执行流程如下：

1⃣ 在 k 索引树上找到 k=3 的记录，取得 ID = 300；

2⃣再到 ID 索引树查到 ID=300 对应的 R3；

3⃣ 在 k 索引树取下一个值 k=5，取得 ID=500；

4⃣ 再回到 ID 索引树查到 ID=500 对应的 R4；

5⃣在 k 索引树取下一个值 k=6，不满足条件，循环结束。

上面的查询过程读了K索引的3条记录，（步骤1|3|5）回表了2次（步骤2|4）。回表的过程会影响数据响应时间，所以查询应该尽可能的避免回表，索引覆盖会解决这个问题

回表：从普通索引树搜索回到主键索引锁搜索的过程，就叫做回表

5.1-索引覆盖

select ID from T where k between 3 and 5 查询，只需要查询索引K就可以得到查询结果，不需要回表，这就是索引覆盖。其中

🅰 对引擎来说：在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，R3~R5（对应的索引 k 上的记录项）

🅱 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2

5.2-最左前缀原则

create table `tuser` (
  `id` int(11) not null,
  `id_card` varchar(32) default null,
  `name` varchar(32) default null,
  `age` int(11) default null,
  `ismale` tinyint(1) default null,
  primary key (`id`),
  key `id_card` (`id_card`),
  key `name_age` (`name`,`age`)
) engine=innodb

最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符

🅰 where name like '张%'

🅱 where name = '张三'

以上两种查询都可以用到 name_age 这个索引

5.3-索引下推

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

对于上面的查询查询过程如下：

1⃣ 使用普通索引(name_age)树,找到第一个满足条件的记录ID3

2⃣ 按照版本分

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

下图是二者的区别：左表回表4次，右表回表2次

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

数据库锁设计的初衷是为了处理并发（多个线程访问同一个资源）问题，按照锁的范围分：

全局锁
表锁
行锁

6.1-全局锁

MySQL通过FTWRL（Flush tables with read lock）,来加全局锁，加上全局锁之后，所有的数据更新语句和DML语句都会被阻塞。全局锁的典型使用场景是：做全库的逻辑备份。通过FTWRL的方式来添加全局锁，可以有效的突破的存储引擎带来的限制（MyISAM是不支持事务的，如果是InnoDB引擎，在RR的情况下，可以实现逻辑备份并且备份的时是支持更新的）

关于使用FTWRL 还是使用 set global readonly=true

6.2-表级锁

MySQL 的表级锁有2种

🅰 表锁

🅱 DML锁（Metadata Lock）

6.2.1-表锁

（假设是线程A）锁表的语法lock table t1 read,t2 write ,使用 unlock talbe 释放锁，对于该锁表语句：

除了A之外的线程对 t1 可读，对 t2 不可读写
线程A 在执行unlock tables之前，只能执行读 t1, 读写 t2, 不能在访问其他表（写t1都不行，更别他其他的表了）

6.2.2-DML锁

🅰 对一个表做增删改查的操作的时候（会申请读锁），加DML 读锁，读锁之间不互斥

🅱 对表做DML 变更的时候，加DML写锁，读写锁、写写锁之间互斥

如下例子中，给表T增加字段，表T变的完全不可读写

处理上述问题，我们首先要尽量避免长事务（session A位置及时 commit），下面的语句在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃，阻塞会引发后面的阻塞

1 2	alter table tbl_name nowait add column ... alter table tbl_name wait n add column ...

7-行锁功过：怎么减少行锁对性能的影响

MySQL 的行锁是在引擎层面实现的，InnoDB支持行锁，MyISAM不支持行锁

7.1-两阶段锁

两阶段锁协议（Two-Phase Locking Protocol），目的是为了保证事务的隔离性，避免数据的不一致性，在两阶段锁协议中，事务的执行过程被分为两个阶段：获取锁阶段（Growing Phase）和释放锁阶段（Shrinking Phase）

🅰 获取锁阶段：在这个阶段中，事务可以获取需要的锁，但不能释放任何锁

🅱 释放锁阶段：在这个阶段中，事务可以释放锁，但不能再获取新的锁（事务结束的时候释放锁）

右图事务B需等A释放锁才能获得id=1的锁

在InnoDB事务中，行锁满足两阶段锁协议。这个协议对我们的帮助就是： 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。 比如在一个简易的影票交易系统中，如顾客A要在影院B 购买电影票，步骤如下：

1⃣ 从顾客A账户余额中扣除电影票价 update

2⃣ 给影院B的账户余额增加这张电影票价 update（同时可能有另外一个顾客C在影院B购票，此处可能是最容易造成锁冲突的地方）

3⃣ 记录一条交易日志 insert

1,2,3应在一个事务中，根据两阶段锁协议，我们的事务应该将2 安排在最后。能够最大限度的减少事务之间的锁等待

两阶段锁协议确保了在事务处理过程中，其他事务不能访问正在处理的数据。然而，它也可能会导致死锁，因为可能会出现两个或更多的事务，互相等待对方释放锁的情况。因此，实际的数据库系统需要额外的机制来检测和解决死锁

7.2-死锁和死锁检测

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

出现死锁后，有2种策略：

1⃣ 进入等待状态，直到超时（MySQL innodb 超时时间由innodb_lock_wait_timeout 指定，默认50s）, 由于超时时间阈值设置过小可能会导致假阴性（误杀，将正常的等待判断为死锁），通常不会采用此方案

2⃣ 发起死锁检测（innodb_deadlock_detect 设置为 on） , 发现死锁后，主动回滚其中一个事务，让其他事务继续执行。死锁检测有额外的负担（死锁检测是$O(N^2)$的时间复杂度）

减少死锁的主要方向，就是控制访问相同资源的并发事务量，即降低$N$值

为什么死锁检测是$O(N^2)$的时间复杂度

并发更新同一行的1000个线程，整体耗费的死锁检测操作为$1000\times1000=100$万。为什么是个乘法——并发更新此行R1的某单个线程Tx，其所作的死锁检测工作为，Tx会有查看锁持有情况，耗费1000此操作——a.查看自身持有的行锁; b.遍历其他999个线程所持有的行锁，总共为$1 + 999=1000$次。为什么会遍历其他999个线程，而不是仅看当前持有R1行锁的这个线程就行了？—— 因为行锁排队。某线程Tm排队获取R1行锁，排在Tx前。如果Tx当前持有行锁R2，过会Tm先于Tx获持R1后，会变成——Tm持有R1，等待R2 && Tx持有R2，等待R1——Tm和Tx成环死锁。因此并发更新同一行的有N个线程，对应的死锁检测耗费代价为$O(N^2)$ ! 死锁检测不可避免，为防止死锁检测代价过高引起性能问题——想办法减少同时对同一行的更新的并发并发度。即降低N值

为了避免死锁检测带来的性能问题，我们可以也可以从业务逻辑的角度出发去解决这个问题，比如在购买影票的例子中，我们可以将一行改为，逻辑上的多行来减少锁冲突:

可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗,如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理

8-事务到底是隔离的还是不隔离的？

当前讨论的隔离级别：==RR==，id = 1 的原始值为1，如下图顺序进行查询：

🅰 A事务查到的 k = 1

🅱 B事务查到的 k = 3

8.1-MVCC是如何工作的？

transaction id ：每个事务的唯一 id号（对象是某一个动作）。InnoDB中每个事务都有一个唯一的事务ID，记为 transaction id ,该 id 是事务开启的时候向InnoDB的事务系统申请的，该 id 按照申请顺序严格递增

row trx_id：记录当前数据是被哪个transaction_id 改为当前值的（对象是某一个数据）。每次事务更新数据时，会生成新的数据版本，row trx_id 记录当前的数据版本是被哪个事务改为当前值的

如下图：一个记录被多个事务连续更新后的状态：

关于undo log : 语句更新会产生 undo log (回滚日志)，作用是可以用于回滚，同时可以提供多版本并发控制下的读（MVCC）²⁵

==从可读性上来说==：一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

1⃣ 版本未提交，不可见

2⃣ 版本已提交，但是

在视图创建后提交的，不可见

是在视图创建前提交的，可见

按照上面的规则来判断事务A，事务A的一致性视图是在事务A启动的时候生成的，此时：

事务B的 (id=1,k=3) 还没有提交，不可见，属于情况 1
事务C的(id=1,k=2) 已经提交，但是是在A的一致性视图创建后提交的，不可见，属于情况2.1
(id=1,k=1) 已经提交，而且是在A的一致性视图创建前提交的，可见，属于情况2.2

真实的物理实现，涉及到高水位（视图数组最大值+1）、低水位（视图数组的最小值）、视图数组（已经开始，但没有提交的事务id构成的数组）

参考逻辑

8.2-更新逻辑

我们再来分析一下事务B的 update 逻辑，按照上述的一致性读逻辑，事务B，是不能读到事务C的(1,2)的，因为事务C的是在事务B的一致性视图生成后提交的，按理说不可见；视图B在更新前去查询一次数据，返回的K=1，但是当更新的时候，必须拿到最新的值（1,2），否则事务C的更新就丢失了，因此更新数据涉及到一条规则：

当前读：更新数据是先读后写的，读只能读当前已提交的最新值，这个读称之为当前读

因此，事务B查询结果是 (id=1,k=3)

此外，除了update 语句外，select 语句如果加锁，也是当前读，如我们将事务A的查询改为如下语句，也可以得到k=3的结果

1
2
3

select k from t where id=1 lock in share mode; -- 读锁（S 锁，共享锁）
select k from t where id=1 for update; -- 写锁（X 锁，排他锁)
-- update 的加锁语义和 select …for update 是一致的

进一步假设事务C还没有提交，事务B就更新，事务B的更新语句会如何处理呢？

info about the picture

由于2阶段锁协议（2PL）的存在，事务C’ 没有提交，即 (1,2) 这个版本的写锁还没有释放，而事务B是当前读，必须得到当前版本(1,2)，而且必须加锁，因此阻塞了，必须等事务C’释放这个锁，才能继续它的当前读，如下图：

对于开头的3个事务，下面更改隔离级别为：==RC==：

事务A ： k = 2
事务B ： k = 3

²⁵. mvcc : https://cloud.tencent.com/developer/article/1801920 ↩

²⁹. https://time.geekbang.org/column/article/70562 ↩

9-普通索引和唯一索引，应该怎么选？

对于表T 查询，讨论在 k 上建立唯一索引还是普通索引

9.1-查询过程

对于select id from T where k=5查询，该查询在索引树上的查询过程，先是从 B+ 树的 root 开始，按层搜索到叶子节点(右下角数据页，可以认为数据页内部通过二分法来定位记录)

1⃣ : 对于普通索引来说，查找满足条件的第一个记录(5,500)后 ,需要查找下一个记录，直到碰到第一个不满足k=5条件的记录

2⃣: 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就停止检索

但是两者的性能差异微乎其微的，因为InnoDB引擎是以数据页为单位来进行数据的读写的，当找到 k = 5 的记录的时候，它所在的数据页已经在内存中了，普通索引需要多做一次的 ‘查找和判断下一条记录’ 只是一次指针寻址和一次计算，如果k=5记录数据页的最后一个记录，这种情况的概率很低，计算平均性能的差异对现在的CPU来说可以忽略不计

以数据页为单位进行数据读写：当需要读一条记录的时候，并不是将目标记录本身从磁盘中读出来，而是以页为单位，将目标记录所在的页整体读入内存，每个数据页大小默认为16KB

9.2-更新过程

当需要更新一个数据页时，MySQL会进行判断当前修改的数据页是否在内存中：

1⃣ : 在内存中，直接对数据进行更新

2⃣ : InnoDB将更新缓存在change buffer（既存储在内存中，也存储磁盘中：可以缓存更新逻辑）中，在下次访问这个数据页的时候，将数据读入内存，执行change buffer 中和这个数据页有关的更新操作

访问数据页时会触发merge（将change buffer的操作应用到数据页，得到最终的结果的过程称之为 merge），系统后台线程会定期merge，在数据库正常关闭过程中，也会执行merge。无疑，使用change buffer 能够有效的提升语句的执行效率，而且由于数据页不需要读入内存占用buffer pool³³,还可以提高内存利用率

为什么唯一索引不能使用change buffer？是因为唯一索引需要检查当前的插入是否违反了唯一约束，这个检查需要将数据页读取到内存中，因此不能使用change buffer。只有普通索引可以使用change buffer，对于一张表需要插入新记录 (5,500)，InnoDB的处理流程如下：

1⃣ 这个记录在内存中

如果是唯一索引：找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束
如果是普通索引：找到3和5之间的位置，插入这个值，语句执行结束

相比之下只是相差一个CPU时间

2⃣ 这个记录不在内存中：

唯一索引：将数据页读入到内存，判断到没有冲突，插入这个值，语句执行结束
普通索引：将更新记录在 change buffer，语句执行就结束了

相比下，普通索引省掉了磁盘读入内存涉及随机IO的访问³⁴，使用change buffer，对于写多读少的系统很合适，如账单日志类目这种页面写完之后被访问的概率很低的系统

💁♂ 关于唯一索引和普通索引选取的建议：

2类索引在查询上对性能没有什么影响，优先尽量使用普通索引；如果所有的更新后，都立马伴随着这个记录的查询，应该关闭change buffer

对比redo log WAL 和 Change buffer

在性能上³⁵：redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

³³. changer buffer 使用的是buffer pool 的内存，默认占用50%，可以通过innodb_change_buffer_max_size来指定 ↩

³⁴. 这是数据库里成本最高的 ↩

³⁵. https://time.geekbang.org/column/article/70848 ↩

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

双1设置可以=> 数据安全参考link

show variables  like 'innodb_flush_log_at_trx_commit'; 
-- innodb_flush_log_at_trx_commit = 1 每次事务提交时MySQL都会把 redo log buffer的数据写入log file，并且flush(刷到磁盘)中去;
show variables  like 'sync_binlog'  
-- sync_binlog = 1  sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

对于某个表t

create table `t` (
  `id` int(11) not null auto_increment,
  `a` int(11) default null,
  `b` int(11) default null,
  primary key (`id`),
  key `a` (`a`),
  key `b` (`b`)
) engine=innodb;

在数据库中，影响执行效率的因素：

扫描行数是影响执行代价的因素之一，扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少
否使用了临时表
是否排序等因素
扫描普通索引会考虑到回表的代价

索引基数（索引上不同值的个数）越大，索引的区分度越大，通过 show index from table_name 查看索引基数。该命令得到的是一个采样估算值³⁷

优化器存在选错索引的可能性。对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。而对于其他优化器误判的情况

你可以在应用端用 force index 来强行指定索引
也可以通过修改语句来引导优化器
还可以通过增加或者删除索引来绕过这个问题

³⁷. InnoDB选取N个页，计算平均不同的值，然后乘以总页数 ↩

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

11.1-前缀索引

在原字段建立索引和建立前缀索引：

1 2	alter table SUser add index index1(email); -- 在email上建立索引 alter table SUser add index index2(email(6)); -- 在 email的前6个字符建立索引

前缀索引的优势：占用空间更小；前缀索引的劣势：

无法使用覆盖索引
由于长度定义的不好(前缀索引要选择合适的长度)，导致索引的基数变小，最终导致回表扫描变多。如何选择前缀索引的长度：

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;

11.2-倒序存储 & hash 字段

2种方式否不支持范围查询

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

一条 SQL 语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短。表现出来，就好像MySQL抖了一下

12.1-SQL为什么变慢？

脏页：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页“

干净页：内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”

flush : 将内存数据刷写到磁盘上使得，内存数据和磁盘的数据一致

什么情况下会触发数据库的 Flash？

1⃣ redo log 写满，需要推进 checkpoint时

2⃣ 内存不足，需要淘汰脏页时

3⃣ MySQL空闲时，刷脏页

4⃣ MySQL正常关闭的时候，刷写脏页

其中情况1出现的时候，MySQL会阻塞所有更新，从监控上看，更新数变为0 ；情况2出现的时候，MySQL使用buffer pool来管理内存，buffer pool 中的内存页存在三种状态：

还没有使用的
使用了而且是干净页
使用了而且是脏页

当读入的数据页没有在内存中时，必须从 buffer pool中申请新页，淘汰则会淘汰最久不使用的数据页，如果是干净页直接释放出来使用，如果是脏页，必须将脏页刷写到磁盘变成干净页才能使用，MySQL刷脏页是一个常态，但是一个查询要淘汰的脏页个数太多时，会导致查询时间加长。从而导致MySQL抖动了一下

12.2-`InnoDB`刷脏页的控制策略

InnoDB使用innodb_io_capacity³⁸，告知InnoDB所在的主机的IO能力，这样InnoDB才能最大发挥磁盘IOPS的能力。当前的这个参数是最大的写磁盘的能力，还需要$innodb_io_capacity \times R \%$ ³⁹InnoDB的刷盘速度实际参考2个因素：

脏页比例
redo log 写盘速度

³⁸. 建议设置为磁盘的IOPS ↩

³⁹. R% 如何计算，参考：https://time.geekbang.org/column/article/71806 ↩

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

MySQL在进行数据删除的时候，会将数据页标记为可复用，实际上并不会进行删除，会形成空洞；如果主键不是依次递增的，插入数据会导致页分裂，会导致页空洞，更新数据可以认为是先删除在插入，也会导致页分裂。如果把这些空洞去掉，就可以达到收缩表空间的目的。而重建表，就可以达到这样的目的

左图：MySQL5.5 | 右图： MySQL5.6

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

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

InnoDB 表直接count(*) 会遍历全表，虽然结果准确，但会导致性能问题(全表扫描会导致所导致的性能问题)。可以考虑使用外部存储的方式来存储count(*),比如redis。不能够保证计数和 MySQL 表里的数据精确一致的原因，是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图

14.1-count(?) 的用法

至于分析性能差别的时候，你可以记住这么几个原则：

server 层要什么就给什么；
InnoDB 只给必要的值；
现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做

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

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

3⃣ 对于 count(字段) 来说：

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

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

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

⁴⁰. 主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值 ↩

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

redo log buffer 就是一块内存，用来先存 redo 日志的。可以认为WAL和redo log 是一个东西

16-`order by`是怎么工作的?

对于下面的查询，MySQL是如何执行的呢？

select city
            ,name
            ,age 
from t 
where city='杭州' 
order by name limit 1000  ; -- id是主键， city上有索引

16.1-全字段排序

1⃣ 初始化 sort buffer⁴¹, 确定放入 name,city,age 3个字段

2⃣ 根据 city 的索引树，找到第一个满足 city = '杭州' 的主键id

3⃣ 回表取出整行，取name,city , age 3个字段的值，存入 sort buffer

4⃣ 从索引city 取下一个记录的主键id

5⃣ 重复3,4 直到不满足查询条件为止

6⃣ 对 sort buffer 中的数据按照name 做快排。 sort_buffer_size（当前参数的大小决定排序是基于内存的快排，还是开辟磁盘空间，使用归并排序）

7⃣ 取前1000行返回给客户端

16.2-row_id 排序

1⃣ 初始化 sort_buffer⁴¹，确定放入两个字段，即 name 和 id

2⃣ 从索引 city 找到第一个满足 city='杭州’ 条件的主键 id，也就是图中的 ID_X（city索引数的图）

3⃣ 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中

4⃣ 从索引 city 取下一个记录的主键 id

5⃣ 重复步骤 3、4 直到不满足 city=’杭州’条件为止，也就是图中的 ID_Y（city索引数的图）

6⃣ 对 sort_buffer 中的数据按照字段 name 进行排序 , max_length_for_sort_data（设置该参数，对于太长的字段，可以实现只是取排序字段和主键排序，select的字段通过回表的方式获取）

7⃣ 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端

16.3-联合索引

执行如下语句：alter table t add index city_user(city, name);

1⃣ 从索引 (city,name) 找到第一个满足 city=’杭州’条件的主键 id

2⃣ 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回

3⃣ 从索引 (city,name) 取下一个记录主键 id

4⃣ 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city=’杭州’条件时循环结束

当然我们可以进一步在 city name age 3个字段上建立索引，然后当前的查询就会索引覆盖，更快

⁴¹. sort_buffer 是MySQL用于排序的一个缓冲 ↩

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

对于需求：从单词表中随机选出3个单词，select word from words order by rand() limit 3; 测试插入10W条记录，本章使用了

内存临时表 -> 按照row id 的方式进行排序
磁盘临时表 -> 优先队列排序算法，生成一个大根堆，完成排序

然后介绍了一种随机1排序的方法：

1⃣ 取得这个表的主键 id 的最大值 M 和最小值 N

2⃣ 用随机函数生成一个最大值到最小值之间的数 $X = (M-N)\times rand() + N$

3⃣ 取不小于 X 的第一个 ID 的行

最后又介绍了一种随机2排序方法：

1⃣ 取得整个表的行数，并记为 C

2⃣ 取得 $Y = floor(C * rand())$。 floor 函数在这里的作用，就是取整数部分

3⃣ 再用 limit Y,1 取得一行

18-为什么我的这些SQL语句逻辑相同，性能确差异巨大？

18.1-条件字段函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。比如，你在 t_modified 、id上建立索引，却产生了下面的SQL写法：where month(t_modified) = 7 或者 where id - 1 = 100 。

18.2-隐式类型转换

首先，在MySQL、PostgreSQL、 Hive 中，字符串类型和数字类型比较的时候，都是将字符串转为数字比较，验证方法如下：

1
2
3

select '10' > 9;
-- 如果是字符类型转为数字类型，返回结果应该为1或者是true
-- 如果是数字类型转为字符类型，返回的结果应该是0或者是false

所以对于表 tradelog（tradeid 类型为字符类型），执行如下查询将无法使用索引：

select * from tradelog where tradeid=110717; 
-- 因为实际的SQL是：

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

18.3-隐式字符编码转换

表trade_detail 字符集使用utf8; tradelog 表使用 utf8mb4⁴⁴字符集，2个表在做关联Join的时候

select d.*
from tradelog l -- utf8mb4
   , trade_detail d -- utf8
where d.tradeid = l.tradeid -- l表的tradeid 有索引
  and l.id = 2;

⁴⁴. utf8mb4是支持emoji的，而utf8不支持。utf8mb4是utf8的超集 ↩

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

如果MySQL数据库本身就有很大的压力，导致数据库的CPU利用率很高或者是 ioutil(IO 利用率) 很高，这种情况下所有的语句执行都可能变慢。除了这个原因之外，还有另外2种情况：

19.1-查询长时间不返回

这种情况下，有2种原因导致，遇到这种情况，详细分析可以参考链接

1⃣ 等 DML锁，通过 sys.schema_table_lock_waits⁴⁵表可以定位到哪个 process id 造成了阻塞

2⃣ 等flush， flush table 是很快的，出现 waiting for table flush 状态的情况可能是,有一个flush tables 命令被别的语句阻塞了，然后flush table 又阻塞了我们的select 语句。

1 2	flush tables t with read lock; -- 只关闭表t flush tables with read lock; -- 关闭 MySQL里所有打开的表

show variables like '%long_query_time%'; -- 查看慢查询阈值
set long_query_time = 1; -- 设置慢查询阈值
select *
from mysql.slow_log; -- 记录慢查询可以记录到表中，也可以记录到文件中，如果记录到表中，可以按照当前的方式查询

19.2-查询慢

1⃣ 全表扫描，从SQL上来说，我们应该避免全表扫描

2⃣ 时间消耗在unlog回滚上：

对于select * from t where id = 1; -- 10w记录，id是主键，从查询返回来看返回结果很慢

当前读可以很快的读取到数据；而一致性读需要相当的响应时间

⁴⁵. select blocking_pid from sys.schema_table_lock_waits ↩

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

20.1-幻读是什么？

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

info about the picture

幻读说明：当前读+新插入的数据才会出现

1⃣ 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读（读到所有已提交记录的最新值）”下才会出现

2⃣ 上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”

20.2-幻读有什么问题？

幻读会导致：

🅰 破坏语义。如上例中 T1语义就是对所有d=5的行都加上锁，SessionB的 T2语句更新id=0后，还可以修改id=0的字段值，破坏了SessionA中 T1定义的对所有d=5的行加锁的逻辑

🅱 破坏数据一致性⁴⁶

info about the picture

如上的分析，都是建立在假设 select * from t where d=5 for update 这条语句只给 d=5 这一行，也就是 id=5 的这一行加锁”导致的，我们尝试把扫描过程中碰到的行，也都加上写锁，看看会发生什么？

info about the picture

如上，即使把所有的记录都加上锁，还是阻止不了新插入的记录，在 T3 时刻，我们给所有行加锁的时候，id=1 这一行还不存在，不存在也就加不上锁

行锁只能锁住行，但是新插入记录的动作要更新的是记录之间的“间隙” ，为了解决幻读问题，MySQL引入了 间隙锁（Gap lock）

20.3-间隙锁

info about the picture

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

如果这里 begin ; select * from where c = 5 lock in share mode; 此时 session B是会被阻塞的

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

$(-\infty,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20,25],(25,+\infty)$

下图是一个间隙锁导致的死锁例子：

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 的间隙锁挡住了

可以发现间隙锁的引入，可能会导致同样的语句锁住更大的范围，解决掉了幻读，但是影响了并发度的。只有在可重复读的隔离级别下，才会有间隙锁。读提交的隔离级别下不会有间隙锁。要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row，这也是现在不少公司使用的配置组合

⁴⁶. 锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性 ↩

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

隔离级别：==RR==；间隙锁和 next-key lock（行锁和间隙锁统一称为 next-key lock）的加锁规则：

1⃣ 原则 1：加锁的基本单位是 next-key lock，next-key lock 是前开后闭区间

2⃣ 原则 2：查找过程中访问到的对象才会加锁

3⃣ 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁

4⃣优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁

5⃣一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

具体案例移步

锁退化的问题，

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

💡 下面这些优化的方式都是有损的

22.1-短链接风暴

连接过程涉及TCP+鉴权等会占用CPU资源

1⃣ 第一种方法：先处理掉那些占着连接但是不工作的线程。kill connection + id的命令⁴⁸，一个客户端处于 sleep 状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道

2⃣ 第二种方法：减少连接过程的消耗。重启数据库，并使用–skip-grant-tables 参数启动。风险很高

22.2-慢查询性能问题

引发性能的情况有三种：

索引没有设计好；可以Online DDL
SQL 语句没写好；参见章节 18 为什么这些SQL语句逻辑相同，性能差异确巨大
MySQL 选错了索引。应急方案 force index

如何避免情况1和情况2？

1⃣ 上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志

2⃣ 在测试表里插入模拟线上的数据，做一遍回归测试

3⃣ 观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致

不要吝啬这段花在上线前的“额外”时间，因为这会帮你省下很多故障排除的时间

查询重写：

1
2
3

insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules();

22.3-QPS 突增问题

有时由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。详情移步

⁴⁸. select * from information_schema.innodb_trx; 查找处于事务中的id ↩

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

关于数据可靠性,也即持久性的

23.1-binlog 写入机制

只要redo log 和binlog 写入了磁盘，就能确保MySQL异常重启后，数据可以恢复

1⃣ 事务执行过程中，先把日志写入到binlog cache ，事务提交的时候，再把binlog cache写入到binlog 文件中，并清空binlog cache

2⃣ 系统给 binlog cache 分配了一片内存，每个线程会按照参数binlog_cache_size 的大小获取自己享有的cache大小，如果不够用就暂存到磁盘

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync⁴⁹

将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，比较常见的是将其设置为 100~1000 中的某个数值

23.2-redo log 的写入机制

redo log 3种存储状态

InnoDB 提供了 innodb_flush_log_at_trx_commit 参数来控制 redo log 的写入策略：

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 持久化到磁盘⁵⁰。此外还有2种情况也会写盘：

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

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

组提交

todo : 部分内容没有做总结

⁴⁹. 对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志 ↩

⁵⁰. 一个正在执行的事务产生的redo log 也是直接写到 redo log buffer 的，即一个未被提交的事务也有可能持久化到磁盘 ↩

⁵¹. log sequence number，LSN ↩

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

主备库的区别：备库和从库的概念是不同的，虽然二者都是只读的，但是从库对外提供服务，而备库只是为主库提供备份

24.1-MySQL 主备的基本原理

左图为主备切换流程(M-S架构)，右图为节点 A 到 B 这条线的内部流程是什么样的

一个事务日志同步的完整过程是这样的：

1⃣ 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量

2⃣ 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接

3⃣ 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B

4⃣ 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）

5⃣ sql_thread 读取中转日志，解析出日志里的命令，并执行

24.2-binlog 的三种格式对比

statement
row
mixed

row 格式使用 mysqlbinlog工具解析出来的结果：

越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。其中一个直接看出来的好处：恢复数据

24.3-M-M架构

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

25.1-主备延迟

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

1⃣ 主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1

2⃣ 之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2

3⃣ 备库 B 执行完成这个事务，我们把这个时刻记为 T3

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

25.2-造成主备延迟的原因

1⃣ 备库所在机器的性能要比主库所在的机器性能差

2⃣ 备库的压力大

3⃣ 大事务⁵³

4⃣ 备库的并行复制能力

25.3-可靠性优先策略

推荐使用该策略

25.4-可用性优先策略

介绍了异常切换的情况

⁵³. 一次性地用 delete 语句删除太多数据。其实，这就是一个典型的大事务场景,另一种典型的大事务场景，就是大表 DDL ↩

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

主要介绍：备库并行复制能力。MySQL5.6之前备库的复制时单线程的。为什么要有多线程复制呢？这是因为单线程复制的能力全面低于多线程复制，对于更新压力较大的主库，备库是可能一直追不上主库的。从而导致备库上 seconds_behind_master 的值越来越大

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

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

todo:待更

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

大多数的互联网应用场景都是读多写少，在发展过程中很可能先会遇到读性能的问题。而在数据库层解决读性能问题，就要涉及到接下来两篇文章要讨论的架构，一主多从。（前3章是一主一备）

1⃣ 基于位点的主备切换(需要注意的是这个位点并不准确)

2⃣ GTID：GTID](全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识格式： GTID=server_uuid:gno

server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值
gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1⁵⁴

⁵⁴. 在 MySQL 里面我们说 transaction_id 就是指事务 id，事务 id 是在事务执行过程中分配的，如果这个事务回滚了，事务 id 也会递增，而 gno 是在事务提交的时候才会分配 ↩

28-读写分离有哪些坑？

自定义的过期读。涉及到的处理过期读的方案如下：

2⃣ sleep 方案

3⃣ 判断主备无延迟方案

4⃣ 配合 semi-sync 方案

5⃣ 等主库位点方案

6⃣ 等 GTID 方案

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

主备切换有下面2种场景：

🅰 主动切换

🅱 被动切换；即由HA 系统确认主库出了问题，然后HA系统发起切换

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

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

误删数据的事后处理办法，更重要是要做到事前预防误删数据，下面是2个建议：

sql_safe_updates 参数设置为 on。这样一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错
代码上线前，必须经过 SQL 审计

防止误删表，误删库的建议：

第一条建议是，账号分离
第二条建议是：制定操作规范。这样做的目的，是避免写错要删除的表名(比如：在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。改表名的时候，要求给表名加固定的后缀比如加 _to_be_deleted ，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表)

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

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

全表扫描对 server层的影响

假如，现在有一个对 200G的InnoDB表 db1.t 进行全表扫描，并将结果集保存在客户端的需求，你可能会有下面命令：

1	mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

那么server端取数据和发数据的流程是下面这样的：

1⃣ 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k

2⃣ 重复获取行，直到 net_buffer 写满，调用网络接口发出去

3⃣ 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer

4⃣ 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待，直到网络栈重新可写，再继续发送

即：边读边发，整个流程如下：

执行 show processlist state 处于 Sending to client 状态的，就表示服务器端的网络线程写满了。对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，建议使用 mysql_store_result 这个接口，直接把查询结果保存到本地内存

全表扫描对`InnoDB`的影响

内存利用率： buffer pool (实际是 buffer pool 中的 change buffer) 可以起到加速更新的作用，同时也具备加速查询的作用，事务提交的时候，磁盘的数据页是旧的，如果马上有一个查询来读数据页，MySQL并不需要将redo log 应用到数据页，也是直接读内存页就可以了。Buffer pool 对查询的加速效果，依赖内存命中率。show engine innodb status 搜索hit 即可定位到。如何设置buffer pool的大小？

InnoDB 内存管理使用的 LRU 这个缓存淘汰算法，并且是基于链表实现的缓存淘汰算法

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

1⃣ 图 7 中状态 1，要访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成状态

2⃣之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处

3⃣ 处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：

若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部
如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒

这个策略，就是为了处理类似全表扫描的操作量身定制的，在扫描这个大表的过程中，虽然也用到了 Buffer Pool，但是对 young 区域完全没有影响，从而保证了 Buffer Pool 响应正常业务的查询命中率

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

🔥 首先，对于我们的查询语句，在上线之前，explain一下是很有必要的，那么对于explain的几种类型需要了解：

1⃣ const: 根据主键等值匹配，唯一索引(is null除外) 等值匹配

2⃣ index : 索引覆盖，无须回表

3⃣ ref ：普通索引等值匹配，普通索引 is null ，唯一索引 is null

4⃣ range: 主键索引或者唯一索引范围查询，或者普通索引范围查询，若范围在总范围中占比大，会变为 ALL

其中，各个类型的优劣如下：

const> ref > index > range > all

t1 和 t2的表结构如下：其中t1中100条记录，t2中1000条记录

create table `t2` (`id` int(11) not null,
                   `a`  int(11) default null,
                   `b`  int(11) default null,
                   primary key (`id`),
                   key `a` (`a`)
) engine = innodb

34.1-Index Nested-Loop Join

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

当前语句的执行流程：

1⃣ 从表 t1 中读入一行数据 R

2⃣ 从数据行 R 中，取出 a 字段到表 t2 里去查找

3⃣ 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分

4⃣ 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束

如果t1 = N 行，t2 M 行，近似复杂度如下：

$O(N)= N + N \times2\times\log2 M$

可以发现，N 越小，整个复杂度越低。

34.2-Simple Nested-Loop Join

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

由于t2的字段b上没有索引，每次去t2匹配的时候，就需要做一个全表扫描。共需要扫描:$100,000 = 100\times1000$

MySQL并没有使用这个Simple Nested-Loop Join 算法，而是 Block Nested-Loop Join

34.3-Block Nested-Loop Join

对于查询，select * from t1 straight_join t2 on (t1.a=t2.b); 流程如下：

1⃣ 把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；

2⃣ 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

这个过程对 t1和 t2都做了一次全表扫描，总的扫描行数是1100，对于t2的每一行都需要在内存中做判断，共需要 100,000 次比较。但是这个比较是基于内存操作，比Simple Nested-Loop Join 快很多。

其中 join_buffer⁵⁵ 如果放不下驱动表，就需要分块（block）放置，过程如下：

1⃣ 扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步；

2⃣ 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；

3⃣ 清空 join_buffer；

4⃣ 继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。

扫描行数 : N+λ N M （λ 取值(0,1)）
内存判断 : N * M

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

⁵⁵. join_buffer 是可以通过 join_buffer_size 进行设定的,默认256k) ↩

35-join语句怎么优化？

1
2
3

create table t1(id int primary key, a int, b int, index(a)); 
-- t1 插入1000行数据，每行的a = 1001-id，即表t1中的字段a是逆序的
create table t2 like t1;  -- 在表t2中插入 100w 数据

35.1-Multi-Range Read 优化

1	select * from t1 where a>=1 and a<=100;

如上查询中，会涉及到回表的过程，回表过程是一行行查询数据，还是批量的查询数据呢？主键索引是一颗B+树，每次只能根据一个主键id 查到一行数据，因此回表一定是一行行搜索主键索引的。在上面的例子中，如果随着a的值递增顺序查询的话，id的值就变成随机的了，那么就会出现随机访问磁盘，性能相对较差。

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

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

1⃣ 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ; (如果read_rnd_buffer 放置满了，就会先执行 2,3步骤)

2⃣ 将 read_rnd_buffer 中的 id 进行递增排序；

3⃣ 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

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

35.2-Batched Key Access

Batched Key Accesss（BKA）算法，其实是对 NLJ(Index Nested Loop Join) 算法的优化，NLJ的逻辑是，从驱动表t1，一行行取出a的值，再到被驱动表t2做join，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了，而BKA的逻辑是，将表t1的数据取出来一部分，放置到join_buffer中，然后一起传给表t2。

info about the picture

Post Script : BKA 算法的优化要依赖于 MRR，使用BKA的前提是开启了 MRR

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

说说临时表和内存表的区别？

内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在

临时表，可以使用各种引擎类型。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎

临时表有哪些特性？

info about the picture

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

由于临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表。也正是由于这个特性，临时表就特别适合我们文章开头的 join 优化这种场景。为什么呢？原因主要包括以下两个方面：

不同 session 的临时表是可以重名的，如果有多个 session 同时执行 join 优化，不需要担心表名重复导致建表失败的问题
不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作

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

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

show processlist 命令的作用：可以帮助确定哪些查询可能会导致性能问题，常用语调试MySQL性能或锁问题，其输出列的含义如下：

Id：连接的唯一标识符
User：建立此连接的MySQL用户
Host：用户连接到MySQL服务器的机器或IP地址
db：当前连接使用的数据库。可能为空，如果没有选择数据库
Command：执行的命令，如 Query（查询）, Sleep（休眠）, etc.
Time：命令已经运行的秒数
State：连接的当前状态，如Waiting for table lock
Info：具体执行的查询或命令。对于非查询命令，此列可能为空

-- t1表使用内存表，Memory存储引擎
create table t1(id int primary key, c int) engine=Memory;
-- t2表使用innodb存储引擎
create table t2(id int primary key, c int) engine=innodb;
-- 向 t1和t2插入数据
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
insert into t2 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);

38.1-内存表的数据组织方式

info about the picture

🅰 InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）

🅱 而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）

2种引擎比对的差异如下：

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

-- 最开始插入数据
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
-- 查询结果
select * from t1;
-- 删除id=5的数据
delete from t1 where id=5;
-- id=10填充原本id=5的位置，由于内存表中每个数据行被删除以后，空出的这个位置都可以被接下来要插入的数据复用
insert into t1 values(10,10);
-- 查询结果
select * from t1;

38.2-内存表的哈希索引和B-Tree索引

t1 的这个主键索引是哈希索引，执行范围查询时，实际走的是全表扫描，当然我们可以使得内存表支持B-Tree索引

1 2	-- 在 id 列上创建一个 B-Tree 索引 alter table t1 add index a_btree_index using btree (id);

info about the picture

内存表的优势是速度快，速度快的2点：

🅰 Memory 引擎支持 hash 索引

🅱 内存表的所有数据都保存在内存，而内存的读写速度总是比磁盘快

38.3-内存表的锁

1 2	# 让返回的数据睡一会 select sleep(2) as sleep_interval , 3

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

info about the picture

38.4-内存表的持久性

info about the picture

基于以上的分析，我们得出结论：内存表并不适合在生产环境上作为普通数据表使用

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

自增主键是递增的，但是不一定连续。主键的递增特性避免了页分裂

create table `t` (
  `id` int(11) not null auto_increment,
  `c` int(11) default null,
  `d` int(11) default null,
  primary key (`id`),
  unique key `c` (`c`)
) engine=innodb;

表的结构定义存放在后缀名为.frm 的文件后面，在这个空表 t 里面执行 insert into t values(null, 1, 1); 插入一行数据，再执行 show create table 命令，就可以看到如下图所示的结果

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

-- AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成 id=2

InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是：

在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。�举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。
也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值
在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值

39.1-自增值修改机制

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

如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段
如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值，可能会更新 auto_increment_increment 的值，假设某次要插入的值是 X，当前的自增值是 Y
1. 如果 X< Y，那么这个表的自增值不变
2. 如果 X≥ Y，就需要把当前自增值修改为新的自增值（从 auto_increment_offset 开始，以 auto_increment_increment为步长(默认设置为1)，持续叠加，直到找到第一个大于 X 的值，作为新的自增值）

尝试执行以下代码，进行测试

# 第一次执行插入
insert into t values(null, 1, 1);
# 观察 AUTO_INCREMENT 的值
show create table t;

# 第二次执行插入
insert into t values(100, 100, 100);
# 观察 AUTO_INCREMENT 的值
show create table t;

# 第三次执行插入
insert into t values(99, 99, 99);
# 观察 AUTO_INCREMENT 的值
show create table t;

-- 会观察到 id 不在连续了

当 auto_increment_offset 和 auto_increment_increment 都是 1 的时候，新的自增值生成逻辑很简单，就是：

如果准备插入的值 >= 当前自增值，新的自增值就是“准备插入的值 +1”
否则，自增值不变

39.2-自增值的修改时机

目前，假设表 t 里面已经有了 (1,1,1) 这条记录，这时我再执行一条插入数据命令insert into t values(null, 1, 1);

info about the picture

事务回滚也会导致逐渐不连续

insert into t values(null,1,1);
begin;
insert into t values(null,2,2);
rollback;
insert into t values(null,2,2);
//插入的行是(3,2,2)

自增值为什么不能回退?

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增 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。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降

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

39.3-自增锁的优化

自增 id 锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请

MySQL 5.1.22 版本引入了新增参数 innodb_autoinc_lock_mode，默认值是 1。

这个参数的值被设置为 0 时，语句执行结束后才释放锁
这个参数的值被设置为 1 时：普通 insert 语句，自增锁在申请之后就马上释放；类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放
这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁

info about the picture

如果我们现在的 binlog_format=statement，binlog 会怎么记录呢？由于两个 session 是同时执行插入数据命令的，所以 binlog 里面对表 t2 的更新日志只有两种情况：要么先记 session A 的，要么先记 session B 的。但不论是哪一种，这个 binlog 拿去从库执行，或者用来恢复临时实例，备库和临时实例里面，session B 这个语句执行出来，生成的结果里面，id 都是连续的。这时，从库和主库就发生了数据不一致

那么如何解决这个问题呢？

一种思路是，让原库的批量插入数据语句，固定生成连续的 id 值。所以，自增锁直到语句执行结束才释放，就是为了达到这个目的
另一种思路是，在 binlog 里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖于自增主键去生成。这种情况，其实就是 innodb_autoinc_lock_mode 设置为 2，同时 binlog_format 设置为 row。

在生产上，尤其是有 insert … select 和 replace … select 和load data 这种批量插入数据的场景，推荐第2种方式，这样做，既能提升并发性，又不会出现数据一致性问题

批量插入语句，自增ID的策略

对于批量插入数据的语句，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;

-- 第一次申请了id=1，第二次申请了 id in (2,3)，第三次申请了 id in (4,5,6,7) 但是只用了id=4
insert into t2(c,d) select c,d from t; 
insert into t2 values(null, 5,5);  -- 实际插入的值为（8,5,5）

总结一下，ID不连续的原因：

人为的指定了id，跳过了一些ID
唯一键冲突
事务回滚
批量插入数据的时候，批量申请但是没有用完

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

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

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

数据库设置的隔离级别为RR；binlog_format=statement

40.1-insert … select 语句

info about the picture

执行 insert … select 的时候，对目标表也不是锁全表，而是只锁住需要访问的资源

40.2-insert循环写入

40.3-insert 唯一键冲突

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

如果可以控制对源表的扫描行数和加锁范围很小的话，我们简单地使用 insert … select 语句即可实现，在2个表中拷贝数据。当然，为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。这时，有两种常用的方法

-- 创建一个表 `db1.t`
create database db1;
use db1;

create table t(id int primary key, a int, b int, index(a))engine=innodb;

-- 往 `db1.t` 中 插入 1000 行数据
delimiter ;;
  create procedure idata()
  begin
    declare i int;
    set i=1;
    while(i<=1000)do
      insert into t values(i,i,i);
      set i=i+1;
    end while;
  end;;
delimiter ;
call idata();


-- 创建一个和 `db1.t` 相同结构的表 db2.t
create database db2;

-- 把 `db1.t` 里面 a>900 的数据行导出来，插入到 db2.t 中
create table db2.t like db1.t

41.1-mysqldump 方法

1
2
3

mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

-- –skip-extended-insert 生成单条的 insert 语句

通过 mysqldump 命令生成的 t.sql 文件，大概是这样的： insert into t values(901,901,901),(902,902,902),(903,903,903)..... 。得到该文件后，，可以通过以下的命令，将 insert语句放到db2中执行

mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"

source 并不是一条 SQL 语句，而是一个客户端命令。mysql 客户端执行这个命令的流程是这样的：

打开文件，默认以分号为结尾读取一条条的 SQL 语句
将 SQL 语句发送到服务端执行

41.2-导出 CSV 文件

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

这条语句会将结果保存在服务端
这条命令不会帮你覆盖文件，因此你需要确保 /server_tmp/t.csv这个文件不存在

得到.csv 导出文件后，你就可以用load data 命令将数据导入到目标表 db2.t 中

1	load data infile '/server_tmp/t.csv' into table db2.t;

该语句的执行步骤如下：

打开文件 /server_tmp/t.csv，以制表符 (\t) 作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；
启动事务。判断每一行的字段数与表 db2.t 是否相同：
1. 若不相同，则直接报错，事务回滚；
2. 若相同，则构造成一行，调用 InnoDB 引擎接口，写入到表中。
重复步骤 3，直到 /server_tmp/t.csv 整个文件读入完成，提交事务

如果 binlog_format=statement，由于 /server_tmp/t.csv 文件只保存在主库所在的主机上，如果只是把这条语句原文写到 binlog 中，在备库执行的时候，备库的本地机器上并没有这个文件，就会导致主备同步停止。所以，这条语句执行的完整流程，如下

info about the picture

load data 命令有两种用法：

不加“local”，是读取服务端的文件，这个文件必须在 secure_file_priv 指定的目录或子目录下
加上“local”，读取的是客户端的文件，只要 mysql 客户端有访问这个文件的权限即可。这时候，MySQL 客户端会先把本地文件传给服务端，然后执行上述的 load data 流程

42.3-物理拷贝方法

dump和导出CSV方式，都是逻辑导数的方式，即：将数据从表 db1.t 中读出来，生成文本，然后再写入目标表 db2.t 中。有没有一种方式：直接把 db1.t 表的.frm 文件和.ibd 文件拷贝到 db2 目录下呢？

一个 InnoDB 表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有 db2.t 这个表，系统是不会识别和接受它们的，所以直接拷贝的方式不可行

MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能，流程如下：现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r

info about the picture

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

42.1-全局权限

mysql 如何创建一个用户？

-- 创建一个用户
create user 'ua'@'%' identified by 'pa'; -- 创建1个用户为 'ua'@'%' 的用户 ， 密码是 pa

-- 查询某个用户
select * from mysql.user where User = 'ua';

-- 删除一个用户
delete from mysql.user where User = 'ua';

在MySQL中，用户名 (user)+ 地址 (host) 才表示一个用户，因此 ua@ip1 和 ua@ip2 代表的是两个不同的用户

如何给MySQL中某个用户赋予最高权限？

1	grant all privileges on . to 'ua'@'%' with grant option;

该命令执行了2个动作：

磁盘上，将 mysql.user 表里，用户’ua’@’%'这一行的所有表示权限的字段的值都修改为‘Y’；
内存里，从数组 acl_users 中找到这个用户对应的对象，将 access 值（权限位）修改为二进制的“全 1”

在 grant 命令执行完了之后，如果新的客户端使用 ua用户登录，MySQL 会为新连接维护一个线程对象，然后从 acl_users 数组里查到这个用户的权限，并将权限值拷贝到这个线程对象中。之后在这个连接中所有关于全局权限的判断，都直接使用线程对象内部保存的权限位。

对于一个已经存在的连接，它的全局权限不受 grant 命令的影响

mysql 如何回收权限？

如果一个用户有所有权限，一般就不应该设置为所有 IP 地址都可以访问，应该是限制性的IP可以访问

1	revoke all privileges on . from 'ua'@'%';

该命令对应2个动作：

磁盘上，将 mysql.user 表里，用户’ua’@’%'这一行的所有表示权限的字段的值都修改为“N”
内存里，从数组 acl_users 中找到这个用户对应的对象，将 access 的值修改为 0

42.2-DB权限

1	grant all privileges on db1.* to 'ua'@'%' with grant option;

这条 grant 命令做了如下两个动作：

磁盘上，往 mysql.db 表中插入了一行记录，所有权限位字段设置为“Y”
内存里，增加一个对象到数组 acl_dbs 中，这个对象的权限位为“全 1”

基于库的权限记录保存在 mysql.db 表中，在内存里则保存在数组 acl_dbs，查询某个用户的库权限方式如下：

1	select * from mysql.db where user = 'ua'

每次需要判断一个用户对一个数据库读写权限的时候，都需要遍历一次 acl_dbs 数组，根据 user、host 和 db 找到匹配的对象，然后根据对象的权限位来判断（并没有拷贝到连接对象的线程对象中）。grant 修改 db 权限的时候，是同时对磁盘和内存生效的

info about the picture

42.3-表列权限

表权限定义存放在表 mysql.tables_priv 中，列权限定义存放在表 mysql.columns_priv 中。这两类权限，组合起来存放在内存的 hash 结构 column_priv_hash 中

create table db1.t1(id int, a int);

-- 将`db1.t1` 表的所有权限授权给 ua 用户
grant all privileges on db1.t1 to 'ua'@'%' with grant option; 

-- 将mydb.mytbl的查询 id列权限，和 (id,a) 的插入权限 授权给 ua 用户
GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;

这两个权限每次 grant 的时候都会修改数据表，也会同步修改内存中的 hash 结构。因此，对这两类权限的操作，也会马上影响到已经存在的连接

42.4- `flush privileges` 的作用

flush privileges 命令会清空 acl_users 数组，然后从 mysql.user 表中读取数据重新加载，重新构造一个 acl_users 数组。也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍。同样地，对于 db 权限、表权限和列权限，MySQL 也做了这样的处理

那么 flush privileges 的使用场景就是：当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges 语句可以用来重建内存数据，达到一致状态

info about the picture

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

该表的创建会形成以下文件

t.frm 即对于 Server 层来说，这是 1 个表
t#P#p_2017.ibd， t#P#p_2018.ibd，t#P#p_2019.ibd，t#P#p_others.ibd，即对于引擎层来说，这是 4 个表

43.1-分区表的引擎层行为

对于InnoDB 来说，这是 4 个表

info about the picture

使用分区表的一个重要原因就是单表过大。那么，如果不使用分区表的话，我们就是要使用手动分表的方式，分区表和手工分表，分区表由 server 层来决定使用哪个分区，手工分表是由应用层代码来决定使用哪个分表

43.2-分区策略

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

1	Too many open files

MyISAM 分区表使用的分区策略，我们称为通用分区策略（generic partitioning），每次访问分区都由 server 层控制。通用分区策略，是 MySQL 一开始支持分区表的时候就存在的代码，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题。从 MySQL 5.7.9 开始，InnoDB 引擎引入了本地分区策略（native partitioning），在InnoDB引擎打开文件超过 innodb_open_files 这个值的时候，就会关掉一些之前打开的文件

43.3-分区表的Server层行为

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

info about the picture

虽然 session B 只需要操作 p_2017 这个分区，但是由于 session A 持有整个表 t 的 MDL 锁，就导致了 session B 的 alter 语句被堵住。分区表，在做 DDL 的时候，影响会更大

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

44-答疑文章（三）：说一说这些好问题

45-自增id用完怎么办？

45.1- 表定义自增ID

表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。因此，当自增主键id 用完时，仍然会得到最后一个ID，再有插入就会报错主键冲突，测试如下：

create table t
(
    id int unsigned auto_increment primary key
) auto_increment = 4294967295; -- 2^32 - 1 = 4294967295

select * from t;

insert into t values (null);
# 成功插入一行 4294967295
show create table t;
/* CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/

select * from t; -- 4294967295
insert into t values (null);
# Duplicate entry '4294967295' for key 'PRIMARY'

$2^{32}-1 = 4294967295 $ 不是一个特别大的数，对于一个频繁插入删除数据的表来说，是可能会被用完的。因此如果你的表是否有可能达到这个上限，就应该创建成 8 个字节的 bigint unsigned

45.2- InnoDB 系统自增 row_id

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

写入表的 row_id 是从 0 开始到 $2^{48}-1$。达到上限后，下一个值就是 0，然后继续循，如果突破这个现实，新写入的行就会覆盖原有的行

45.3- Xid

Xid 在 MySQL 内部是怎么生成的呢？

MySQL 内部维护了一个全局变量 global_query_id，每次执行语句的时候将它赋值给 Query_id，然后自身加 1。如果当前语句是某个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid

global_query_id 是一个纯内存变量，重启之后就清零了。所以，在同一个数据库实例中，不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件，这就保证了，同一个 binlog 文件里，Xid 一定是惟一的

MySQL 重启不会导致同一个 binlog 里面出现两个相同的 Xid，但是如果 global_query_id 达到上限后，就会继续从 0 开始计数。从理论上讲，还是就会出现同一个 binlog 里面出现相同 Xid 的场景，这种情况非常极端，无需考虑

45.4- Innodb trx_id

Xid 在 trx_id 的区别在于什么？

Xid是由server层维护的。 trx_id（transaction id）是InnoDB（引擎层）另外维护的，InnoDB 内部使用Xid，就是为了能够在 InnoDB 事务和 server 之间做关联

Todo: 待更新

45.5- thread_id

线程 id 是MySQL 中最常见的一种自增 id，show processlist 里面的第一列，就是 thread_id，系统保存了一个全局变量 thread_id_counter，每新建一个连接，就将 thread_id_counter 赋值给这个新连接的线程变量，并且保证该ID在所有线程数组中是唯一的。当连接关闭时，会调用函数 release_thread_id，从thread_ids移除当前 id号

thread_id_counter 定义的大小是 4 个字节，因此达到 $2^{32}-1$ 后，它就会重置为 0，然后继续增加

1
2
3

select  * from information_schema.innodb_trx; -- 查看当前的事务

show processlist;  -- 查看当前的连接

MySQL 的 unique key 约束：允许多个null

create table if not exists `unique_key_test`
(
    id bigint,
    id_card varchar(18) ,
    name varchar(30),
    primary key (id),
    unique key (id_card)
);

insert into unique_key_test values (1,'411524199710094032','z3');
insert into unique_key_test values (2,null,'l4');
insert into unique_key_test values (3,null,'w5'); -- 插入成功

1-基础架构：一条SQL是如何执行的

1.1-MySQL 基础架构

A-连接器

MySQL 长短连接：

B-执行器

2-日志系统：一条SQL更新是如何执行的

2.1-redolog

2.2-bin log

binlog 日志格式

3-事务隔离： 为什么你改了，我还看不见

3.1-隔离性和隔离级别

3.2-事务隔离的实现

3.3-事务启动方式

4-深入浅出 索引 (上)

4.1-索引的常见模型

4.2-InnoDB的索引模型

4.3-索引维护

5-深入浅出 索引 (下)

5.1-索引覆盖

5.2-最左前缀原则

5.3-索引下推

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

6.1-全局锁

6.2-表级锁

6.2.1-表锁

6.2.2-DML锁

7-行锁功过：怎么减少行锁对性能的影响

7.1-两阶段锁

7.2-死锁和死锁检测

8-事务到底是隔离的还是不隔离的？

8.1-MVCC是如何工作的？

8.2-更新逻辑

9-普通索引和唯一索引，应该怎么选？

9.1-查询过程

9.2-更新过程

对比redo log WAL 和 Change buffer

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

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

11.1-前缀索引

11.2-倒序存储 & hash 字段

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

12.1-SQL为什么变慢？

12.2-InnoDB刷脏页的控制策略

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

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

14.1-count(?) 的用法

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

16-order by是怎么工作的?

16.1-全字段排序

16.2-row_id 排序

16.3-联合索引

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

18-为什么我的这些SQL语句逻辑相同，性能确差异巨大？

18.1-条件字段函数操作

18.2-隐式类型转换

18.3-隐式字符编码转换

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

19.1-查询长时间不返回

19.2-查询慢

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

20.1-幻读是什么？

20.2-幻读有什么问题？

20.3-间隙锁

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

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

22.1-短链接风暴

22.2-慢查询性能问题

22.3-QPS 突增问题

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

23.1-binlog 写入机制

23.2-redo log 的写入机制

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

24.1-MySQL 主备的基本原理

24.2-binlog 的三种格式对比

24.3-M-M架构

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

25.1-主备延迟

25.2-造成主备延迟的原因

25.3-可靠性优先策略

25.4-可用性优先策略

3-事务隔离：为什么你改了，我还看不见

4-深入浅出索引 (上)

5-深入浅出索引 (下)

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

12.2-`InnoDB`刷脏页的控制策略

16-`order by`是怎么工作的?

全表扫描对`InnoDB`的影响

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

42.4- `flush privileges` 的作用