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

redo log 的写入拆成了两个步骤：prepare 和 commit，这就是**”两阶段提交”**。两阶段提交是为了让binlog 和 redo log两个日志一致：

3.事务隔离

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

事务有：原子性、隔离性、一致性、持久性。

数据库上有多个事务同时执行时，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读已提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

隔离级别实现方式：语句、事务级别的视图，以及串行化/行锁。

为什么MySQL要使用RR作为默认隔离级别？

事务隔离的实现通过MVCC实现。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

这也解释了为什么要尽量少用长事务。1.因为会缓存大量的回滚日志。2.除了回滚段，长事务还占用锁资源。

一个事务在执行到一半的时候实例崩溃了，在恢复的时候先恢复redo，再根据 redo构造 undo，从而回滚宕机前没有提交的事务。

4.深入浅出索引（上）

实现索引的方式却有很多种，这里介绍三种常见、也比较简单的数据结构。

它们分别是哈希表、有序数组和搜索树。

索引组织表：在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

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

B树与B+树：B树是每个节点都存了索引和数据，B+树是只在叶子节点存储数据，有点像跳表。

聚集索引与非聚集索引：Innobo引擎， data存的是数据本身。索引也是数据，数据和索引存在一个XX.IDB文件中，所以叫聚集索引。MyISAM引擎，data存的是数据地址。索引是索引、数据是数据。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚集索引。聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。

根据非主键查询：需要回表。

自增式ID的好处：都是追加插入。

用短ID的好处：每个二级索引的数据都是主键，用短主键可以节省大量存储空间。

重建索引，语句怎么写？1.索引可能因为删除，或者页分裂等原因，导致数据页有空洞。用这个语句代替： alter table T engine=InnoDB。

5.深入浅出索引（下）

本篇主题：索引优化。

覆盖索引（联合索引）：值已经在k索引树上，所以无需回表，索引 k 已经“覆盖了”我们的查询需求。
最左前缀原则：
- 对于联合索引，查询可以用最左一个索引快速定位。
- 对于单个索引，查询可以用最左几个字符快速定位。
MySQL 5.6 之后，引入索引下推优化。可以在索引遍历过程中，对索引中包含的字段先做判断。例如姓名、年龄联合索引，老版本是先筛选出姓名符合的，再一一回表判断年龄。新版本则直接一次性判断完姓名、年龄，再去查数据。

总结：

高频查询：可以建立联合索引来使用覆盖索引，不用回表。
非高频查询：再已有的联合索引基础上，使用最左前缀原则来快速查询。两种最左前缀原则的优化情况。
对于MySQL 5.6 引入索引下推，减少回表次数。

6.全局锁和表锁

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

全局锁：

全局锁的命令：Flush tables with read lock (FTWRL)：对整个数据库实例加锁。典型使用场景：做全库逻辑备份。此时整个库处于只读状态。
官方自带的逻辑备份工具： mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。
需求是全库只读，那么其实直接set global readonly=true也可以。

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

表级锁：

表锁：语法为lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。
元数据锁：无需显式使用，在访问一个表的时候会被自动加上。MDL 的作用是锁住表结构，保证读写的正确性。

使用元数据锁时，要注意两个坑：

修改表结构会进行全数据扫描：修改大表要特别注意。
修改表结构会同时申请读写锁：即使修改的是小表，如果该小表访问十分频繁，则会有问题。

如何安全的给热点小表加字段：

解决长事务
在 alter table 语句里面设定等待时间

7.行锁

MySQL的行锁在引擎层实现。

两阶段锁协议：在InnoDB事务中，行锁是需要时才会加上，并且要等事务结束才会一起释放。并不是用完就释放。

两阶段锁协议对我们的启示是：晚点拿锁。

行锁还有可能引起死锁的问题。比如事务A和事务B，在互相等待对方的行锁。

解决死锁的两个思路：

设置超时时间：死锁时直接进入等待，直到超时。超时时间可通过参数 innodb_lock_wait_timeout 来设置，默认50s。
开启死锁检测，主动回滚：发现死锁后，主动回滚死锁链条中的某一个事务。将参数 innodb_deadlock_detect 设置为 on来开启，默认on。但是它也是有额外负担。

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

关键词：查询：一致性视图。更新：当前读。

查询和更新是有区别的。查询是一致性视图，更新是当前读。

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

可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽MVCC只在读已提交和可重复读两个隔离级别下工作。

MVCC和事务RR/RC隔离的运作机制：关键词：严格递增的事务ID -> 数据版本(事务ID+前一版本引用) -> undo -> 一致性视图 -> 事务ID数组 -> 高低水位

当前读：更新数据都是先读后写的，而这个读，只能读当前的已提交的值，称为“当前读”（current read）。即使不在当前视图里，读的也是最新值。其实当前读的时候，如果该值还在被其他事务使用并未提交，那么此时其实它是被其他事务加了写锁的，必须等到其他事务释放锁，当前事务才会继续执行。

如下所示，根据“两阶段协议”，C1事务会持有所有用到的数据的写锁，直到事务结束，才一并释放。所以事务B被阻塞，直到C1提交，释放锁：

RR/RC事务隔离怎么实现

实现原理如下：

事务可重复读RR：查询时，一致性读（consistent read，MVCC实现）。更新时，只能用当前读，数据被占用时则堵塞。
事务读已提交RC：实现与RR一样。不同的是，RR是在事务启动时(第一个语句执行，不是开始时)创建一致性视图，而RC是每一个语句执行前算出一个视图。

实践篇

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

业务上已经能确定一个字段是唯一的，此时该字段选普通索引还是唯一索引？

查询过程，两者性能相差不大。
更新过程：在使用普通索引时，change buffer 对更新有加速作用。唯一索引则无法使用change buffer。

读多写少和读少写多的场合，change buffer有何优劣？普通索引 + change buffer可提高效率

change buffer：当需要更新一个数据页时：如果该数据页不在内存中：在不影响数据一致性的前提下，1.将这些更新操作缓存在 change buffer 中，这样就无需再从磁盘中读入这个数据页。2.在下次查询需要访问该数据页时，将数据页读入内存，再执行 change buffer 中与这个页有关的操作。

change buffer是可持久化的数据：也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。会定期写到系统表空间。

merge的时机：1.访问该数据页时。2.系统后台线程定期merge。3.数据库正常关闭的过程中，也会执行merge。change buffer适用于写多读少的数据，因为merge是要消耗性能的。

只有普通索引可以用change buffer：对于唯一索引，所有更新操作都要先判断是否违反了唯一约束，所以必须要读磁盘加载数据页。所以，唯一索引的更新不能使用 change buffer，实际上也只有普通索引可以使用。

10.优化器如何决定走哪个索引？

优化器会根据扫描行数、是否回表、是否使用临时表、是否排序等因素进行综合判断：

扫描行数：

通过索引基数判断。根据统计信息来估算记录数。
MySQL使用采样统计得到索引基数。从N个数据页采样统计，更新超过1/M后重新统计。
拿到索引基数，就可以根据语句来判断会扫描多少行数。
修复索引统计不准确方法：analyze table t 命令重写统计索引信息。发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理。

偶尔碰到，原本可以执行很快的语句，就是执行的慢，怎么处理呢？

用 force index 强行选择。优点是简单暴力。
修改SQL语句诱导优化器使用期望的索引：例如把之前的“order by b limit 1” 改成 “order by b,a limit 1”。此时两个索引都要排序，所以又通过扫描行数来判断了。
直接新建或删除索引来改变优化器行为：某些场景下适用。

总之抓住关键点，优化器会根据扫描行数（可analyze table保证统计正确）、是否排序(可通过SQL诱导)、是否使用临时表等因素，来综合选择是否走索引，走哪个索引。扫描行数并不是唯一因素。

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

MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。alter table SUser add index index2(email(6));

如何确定前缀索引长度：先算出这个列上有多少个不同的值，然后一次查询不同的前缀区分度即可。select count(distinct email) , count(distinct left(email,4)） from SUser

前缀索引会导致无法使用覆盖索引.

字段区分度不高时，可以考虑使用倒序存储，或者再加一个hash字段。

12.刷脏页导致MySQL突然卡顿

从现象看，就是数据库“抖”了一下。原因：刷脏页。

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

我们知道，MySQL是WAL的，即InnoDB 在处理更新语句的时候，只做了更新内存，然后写日志（redo log）这一个磁盘操作。数据真正写到磁盘的表，是之后在后台执行的事情，也就是刷脏页。

要更新磁盘，无非就是两种情况：内存不够用了、日志空间不够用了。

什么时候会flush？主要涉及4种场景：

redo log 写满了
系统内存不足：内存不够用的时候，就要淘汰一些内存数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
系统空闲时
MySQL正常关闭时

InnoDB 刷脏页的控制策略及参数：

13.数据库表的空间回收

删除某条记录产生的空洞可以复用，空间不会立即回收。所以经过大量增删改的表，都是可能是存在空洞的。

表重建：把这些空洞去掉，从而达到收缩表空间的目的。

alter table A engine=InnoDB：使用该命令重建表即可。

DDL 不是 Online 的，会阻塞增删改。MySQL 5.6 支持 Online DDL，写锁退化为读锁，原表A此时允许增删改，增删改的操作保存到单独的日志文件（row log），拷贝完成后，有一个短暂的停顿，把row log的变更更新到新的表中，

Online的概念：就是单纯的表示，copy的时候不阻塞增删改而已。

14.count(*)操作慢怎么办

两种引擎的实现方式：

MyISAM：引擎把一个表的总行数存在了磁盘上。因此执行 count(*) 的时候会直接返回，效率很高。不过加了过滤条件的计数，也是一行一行计数。
InnoDB ：把数据一行一行地从引擎里面读出来，然后累积计数。比较麻烦。

InnoDB也做了优化：count(*) 操作，会选取普通索引树，而不是主键索引树。能够减少扫描的数据量。

show table status不能提供准确数据：是从索引统计值统计时的采样估算的，根据官方文档说误差可能达到 40% 到 50%。

如果现在有一个页面经常要显示交易系统的操作记录总数，到底应该怎么办呢？答案是，只能自己计数，放到DB或Redis都可。

不同参数区别如下：

count(主键 id)：InnoDB遍历全表，取出值ID，返回给server层。server层每一行取到ID，判断ID是不可能为空的，直接按行累加。
count(1)：InnoDB遍历全表，但不取值。server层每一行直接用“1”填充，判断“1”是不可能为空的，直接按行累加。所以count(1)要比count(主键ID)快。返回 id 会涉及到解析数据行，以及拷贝字段值。
*count(字段)*：
- 字段定义不允许null：逐行地从记录中读出该字段，判断不能为 null，直接按行累加；
- 字段定义允许null：逐行地从记录中读出该字段，判断可能为 null，还要把值取出来再判断一下，不是null才按行累加；
count(*)：是个例外，专门做了优化，不取值。因为count(*) 肯定不是 null，直接按行累加。

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

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

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

其他问题也很有意思，参见原文。

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

MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

1.全字段排序：

全字段排序执行流程：其实就是把所有满足条件的记录查出来，放到sort_buffer，然后排序，最后拿出符合条件的前N条记录即可。

如果sort_buffer内存不够，就需要使用外部排序，即利用磁盘临时文件辅助排序。

2.rowid 排序

rowid排序执行流程：简单的理解，就是不缓存全字段，而是只缓存id和参与排序的字段进行排序，拿到符合条件的id集后，再多一次回表操作拿到所有记录，最后返回结果。

排序的成本是比较高的。但并不是所有的order by都会用到排序，如果数据本来就是有序的，那压根用不到排序操作。

3.索引排序

建立联合索引，此时数据加了索引，本来就是有序的，直接取，用不到排序操作。

17.快速取随机行

需求：在一张单词表里，访问时要随机选出3个单词，且访问的频率很高。

1.内存临时表(memory引擎)排序

即*order by rand()*，随机排序，取前三个：select word from words order by rand() limit 3;

什么情况下会产生临时表Using temporary？查询需要临时表的时候，比如我们这个例子里，需要临时表来放rand()结果。

2.磁盘临时表（默认InnoDB引擎）

不是所有临时表都是内存临时表。内存不够时，使用磁盘临时表。

tmp_table_size 参数：内存临时表大小的参数。默认16M，超过这个大小则转换为磁盘临时表。

internal_tmp_disk_storage_engine参数：配置磁盘临时表的引擎，默认 InnoDB。

3.随机算法

算法简述：

方案1：ID范围映射到0-1，随机取一个。
方案2：统计记录总行数，然后映射到0-1，随机取第N行。

教训：临时表排序代价很高，使用到临时表时，要注意能不能避开。另外可以调高sort_buffer参数。

18.有可能导致索引失效的情况

案例一：条件字段函数操作。
案例二：隐式类型转换：类型是varchar，语句里却用int。
案例三：隐式字符集转换

19.为什么查询慢（锁问题）

本篇主要涉及表锁、行锁和一致性读的概念。隔离级别：可重复读。

有时候数据库CPU和IO都不高，但是只查一行也会执行得很慢。有可能是哪些原因呢？

show processlist 命令：分析原因，我们一般直接执行 show processlist 命令，看看当前语句处于什么状态。

慢的原因1：有锁：

表锁：等MDL锁： show processlist 命令：看到了**状态Waiting for table metadata lock**。直接找到谁持有MDL写锁，然后把它kill掉。
表锁：等 flush：show processlist 命令：看到了**状态Waiting for table flush**。和前面一样，查询ID，然后直接kill掉就行。
行锁：访问 id=1 这个记录时要加读锁，如果已经有其他事务在上面持有写锁，那么我们访问时，就会被行锁锁住。可以通过 sys.innodb_lock_waits 表查到是谁占着写锁，直接干掉线程即可，以4号为例： KILL QUERY 4 或 KILL 4。

慢的原因1：查询慢：

没有索引：加索引。
一致性读，回滚巨量undolog。

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

幻读的概念：指一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行（所以插入的行才算是幻读，更新的行不算，因为前面已经是可以看到的）。

也就是说，即使把所有筛选到的记录都加上锁，还是阻止不了新插入的记录，这也是为什么“幻读”会被单独拿出来解决的原因。

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

引入间隙锁带来的死锁问题：大家一起都加间隙锁，然后大家一起插数据，然后就都等着对方释放间隙锁。

为了解决幻读问题，引入间隙锁，但是也带来了死锁的问题。如果无需解决幻读，那么直接用读已提交，不过要解决日志不一致问题：读已提交 + binlog_format=row。

21.加锁规则

本篇主要讲加锁规则，间隙锁和行锁的加锁规则。

间隙锁在可重复读隔离级别下才有效，本篇文章基于可重复读隔离级别。

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

原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：唯一索引、等值查询，所有的 next-key lock 都退化为行锁、不加间隙锁。
优化 2：普通索引、等值查询，向右遍历若最后一个值不等时，该 next-key lock 退化为间隙锁。
1个 bug：唯一索引、范围查询，加锁时会一直访问到不满足条件的第一个值为止。

22.线上临时提升MySQL性能（Server层）

业务场景：生产环境，业务高峰期，需要临时提升MySQL性能。

方案肯定都不是无损的。

1.业务场景1：短连接风暴

业务场景：业务高峰期，连接数可能会暴涨。

方案1：调高 max_connections 的值：不推荐。更多的权限验证等操作，已经连接的线程反而拿不到 CPU 资源去执行业务的 SQL 请求。
方案2：断开空闲链接。即在 show processlist 的结果里，踢掉显示为 sleep 的线程，可能是有损的。事务具体的状态，查询 information_schema 库的 nnodb_trx 表。
方案3：临时取消权限验证。怎么跳过权限验证：重启数据库，并使用–skip-grant-tables 参数启动。风险太高，不建议使用。

2.业务场景2：慢查询

方案1：索引问题，则紧急创建索引。应该先在备库执行。备库先关掉binlog，然后修改索引即可。之后主备切换。然后在原主库上再修改。
方案2：语句没写好。线上改写 SQL 语句即可：MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式。
方案3：MySQL选错索引。修改SQL语句，给这个语句加上 force index 即可。使用上述方法临时修改语句。

3.上线前就排查

预先发现问题：

慢查询调整为记录所有查询信息：上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；
做回归测试：在测试表里插入模拟线上的数据，做一遍回归测试；
通过慢查询分析语句是否有问题：观察慢查询日志里每类语句的输出，特别留意慢查询日志的 Rows_examined 字段是否与预期一致。即扫描行数。（我们在前面文章中已经多次用到过 Rows_examined 方法了，相信你已经动手尝试过了。如果还有不明白的，欢迎给我留言，我们一起讨论）。

4.QPS 突增问题

业务场景：突然出现业务高峰，或者Bug导致某个语句QPS疯涨。

解决方法：直接把业务下掉。如何在DB端干掉一个功能呢：

全新的业务，根据白名单加入：直接把该业务的白名单干掉。
新功能使用的是单独的数据库用户：直接把这个用户删掉，然后断开现有连接

23.线上临时提升MySQL性能（InnoDB层）

在专栏的第 2 篇和第 15 篇文章中，介绍的是，如果 redo log 和 binlog 是完整的，MySQL 是如何保证 crash-safe 的。今天这篇文章，我着重和你介绍的是 MySQL 是“怎么保证 redo log 和 binlog 是完整的”。

binlog写入：data -> binlog cache -> page cache -> 磁盘。事务提交时一次性从binlog cache 写入。

redo log 的写入：data -> redo log buffer -> page cache -> 磁盘。事务还没提交的时候，redo log buffer 中的部分日志也可能会被持久化到磁盘。

最佳实践：MySQL 的“双1”配置：sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。

针对组提交机制，MySQL进行了一个有意思的优化，就是“拖时间”。每次多等几个事务到prepare，再fsync。

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

这篇文章主要介绍主备的基本原理。

建议把备库设置为只读模式：防止误操作，出现双写。

一个 update 语句在节点 A 执行，然后同步到节点 B 的完整流程：

binlog有两/三种格式：statement、row、mixed(前面两种的混合)。row格式：生产中用的比较普遍，但占用空间大。

所以MySQL提供了mixed格式：MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

循环复制问题：双主结构的循环赋值问题，赋值对方binlog。如何解决循环赋值问题，简单说就是给binlog加一个server id来识别过滤。

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

MySQL提供了主库和从库的最终一致性。

主备延迟问题：

在备库执行show slave status 命令，返回的结果里面会显示 seconds_behind_master，就是用来表示当前备库延迟了多少秒。
主从时间不一致没问题，从库会自动扣除差值。
其实延迟问题没法解决：要不就全同步。要不就强制走主数据库。

主备延迟原因：

备库太差。
备库读的压力太大。大事务。

主备切换：

保证可靠性：先只写主库。从库追上后，从库改主库。
保证可用性：直接切。可能会出现主备数据不一致。

也可以用可靠性优先，DB不可用时，把日志暂时存放到其他文件或临时表，最后切换完成后再统一恢复即可。

26.备库延迟几小时怎么办

对于备库：关键点就在于上述图中的sql_thread，5.6之前是单线程执行的，此时主库太快，备库就跟不上。最新版已改为多线程执行。

MySQL5.6 版本，支持了并行复制。不过是按库并行。即以库名为key进行分发。

官方 MySQL5.7 版本新增的备库并行策略，修改了 binlog 的内容，也就是说 binlog 协议和之前的版本不兼容，在主备切换、版本升级的时候要注意。

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

前面24、25、26讲的都是一主一备。

读多写少的情况下，我们会用到一主多从。这也是接下来27、28两篇的重点。

本篇的主题是，一主多从的切换正确性。下一篇的主题是，一主多从的查询逻辑正确性的方法。

本篇要讨论的是，在一主多从架构下，主库故障如何进行主备切换。

一主多从一般用于读写分离，主库负责所有的写入和一部分读，其他库负责读。

同步位点（也就是binlog的位置），主备切换时使用。

GTID ：全称 Global Transaction Identifier，即全局事务 ID。是一个事务在提交的时候生成的，是这个事务的唯一标识。

跳过事务和忽略错误，很容易出错。MySQL 5.6 版本引入了 GTID。

每个 MySQL 实例都维护了一个 GTID 集合，用来对应“这个实例执行过的所有事务”。所以有重复事务时会直接跳过，不会报错。

过期读：就是主从模式，主库刚更新完数据，从库不能读到最新的数据。读到的是“过期的”数据。

如何解决呢？主要有以下几种方案：

强制走主库方案；
sleep 方案；主库更新后，读从库之前先 sleep 一下。
判断主备无延迟方案；从库seconds_behind_master为0才查询
配合 semi-sync 方案；
等主库位点方案；
等 GTID 方案。

semi-sync 方案：引入半同步复制，也就是 semi-sync replication。

其设计如下，其实就是引入了一个确认机制：一主多从时可能会有问题。A从库返回确认，查的确是B。

事务提交的时候，主库把 binlog 发给从库；
从库收到 binlog 以后，发回给主库一个 ack，表示收到了；
主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。

等主库位点方案：

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

MySQL 5.7.6 版本开始，允许在执行完更新类事务后，把这个事务的 GTID 会返回给客户端。

执行流程变为：

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

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

select 1 判断
查表判断：select 1 不能判断 InnoDB 并发查询耗尽的阻塞情况。
更新判断

推荐优先考虑 update 系统表，然后再配合增加检测 performance_schema 的信息。

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

本篇主题：误删数据。怎样减少误删数据的风险，怎样挽救由误删数据带来的损失。

第24篇讲到，使用 delete 语句误删了数据行，可以用 Flashback 工具通过闪回来恢复数据。

Flashback 恢复数据的原理：修改 binlog 的内容，拿回原库重放。前提是，需要确保 binlog_format=row 和 binlog_row_image=FULL。

恢复操作不建议直接在主库上执行。

如何预防：

把 sql_safe_updates 参数设置为 on。这样一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错。
代码上线前，必须经过 SQL 审计。
小表的记录，加id>=0即可。delete删除
账号分离。
通过改名修改。

drop以后，只能使用“全量备份 + 增量日志”恢复。

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

MySQL中有两个kill命令：

kill query + 线程 id：终止这个线程中正在执行的语句；
kill connection + 线程 id：断开这个线程的连接（如果该线程有语句在执行，则先停止语句执行）。connection可以省略。

kill不掉的情况：使用了 kill 命令，却没能断开这个连接。再执行 show processlist 命令，看到这条语句的 Command 列显示的是 Killed。按理说不应该是直接看不到了吗？停止是通过感知埋点来实现的。停止是有过程的。如果执行线程处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处；

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

如果主机内存只有 100G，现在要对一个 200G 的大表做全表扫描，会不会把数据库主机的内存用光？

对大表做全表扫描，流程到底是怎么样的呢？

结果集存在哪呢？

实际上，没有保存整个完整结果。MySQL是边读边发的，取发数据流程如下：

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

net_buffer是每个线程一块。

2.InnoDB 的内存管理算法

用的是最近最少使用算法，即LRU算法（Least Recently Used）。

（1）LRU算法

InnoDB 管理 Buffer Pool 的 LRU 算法，用链表来实现：

最近访问数据移到链表表头：图中表头 P1 是最近刚刚被访问过的数据页；假设内存里只能放下这么多数据页；
访问已有的记录更新到表头：这时候有一个读请求访问 P3，因此变成状态 2，P3 被移到最前面；
访问没有的记录，删除队尾老记录，新记录从磁盘加载后插到队头：状态 3 表示，这次访问的数据页是不存在于链表中的，所以需要在 Buffer Pool 中新申请一个数据页 Px，加到链表头部。但是由于内存已经满了，不能申请新的内存。于是，会清空链表末尾 Pm 这个数据页的内存，存入 Px 的内容，然后放到链表头部。
从效果上看，就是最久没有被访问的数据页 Pm，被淘汰了。

InnoDB的实现改进：

按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域，执行流程变为：

访问young区时：逻辑和基础LRU算法一样。
访问不存在的数据页时：也是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。
访问old区时，做如下判断：
- 该数据页在链表存在时间超过1秒：移到链表头部。
- 该数据页在链表存在时间短于1秒：位置保持不变。

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

被驱动表能用上索引的情况的前提下，我们得到了两个结论：

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

join_buffer。

什么是“小表”？

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

35.join语句怎么优化？

ndex Nested-Loop Join（NLJ）和 Block Nested-Loop Join（BNL）的优化方法总结：

NLJ算法优化：BKA 优化是 MySQL 已经内置支持，建议默认使用；
BNL算法优化：尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；
BNL算法优化：基于临时表的改进方案。对于能够提前过滤出小数据的 join 语句来说，该方案效果很好；
BNL算法优化：应用端模拟hash join。MySQL 目前的版本还不支持 hash join，但可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。
手动模拟Hash join

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

本篇主题：临时表。

临时表的好处：

线程自己可见。
线程退出时自动删除。
在 binlog_format=’row’的时候，临时表的操作不记录到 binlog 中。

本篇说的是用户临时表，是用户自己创建的。另一种是内部临时表，前面17篇已经讲过。

37.什么时候会使用内部临时表？Union和GroupBy

用来辅助SQL存放中间数据的数据结构目前有三个：（好像还有一个update相关的buffer？）

sort buffer：排序时候使用。
join buffer：join语句时使用。
内部临时表：需要额外的内存来保存中间结果时，如果执行逻辑需要用到二维表特性，就优先考虑使用临时表。

union执行流程：

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

group by 的语义：根据分组key统计不同的key出现的个数，group by 本质上就是统计。它的意思是，根据某个字段来分组，然后分组到一起的数据用聚合函数来处理，比如可以计数（COUNT），求和（SUM），求平均数（AVG）等。然后加Having则可以根据聚合函数的结果做进一步过滤。group by也会用到临时表。

优化思路：直接新增一个有索引的列，列的值就是 group by 的 key。然后根据该列group by，结果就是有序的，逐行统计处理即可。如果 group by 后时根据分组的key顺序排列的，那么不用再建立临时表.

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

两种引擎数据组织方式比较：

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

建议，不要在生产环境上使用内存表。原因有两个：

锁粒度问题：只支持表锁，不支持行锁，对事务并发度支持不高。
数据持久化问题：数据库重启时，所有内存表都会被清空。

生产环境唯一建议使用Memory引擎的场景，用户手动建立临时表且数据少且等值查询时：

内存表不需要写磁盘，速度快。
等值查询时：索引 b 使用 hash 索引，等值查找速度比 B-Tree 索引快；
数据少时：临时表数据少，占用的内存有限。

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

如果主键用的是自增ID，不应该把业务依赖到ID的自增性。因为自增ID不保证绝对是自增的。

自增值不同引擎不同，InnoDB8.0后保存在redo log。

自增值在语句执行完成前就会先修改。

自增锁主要是考虑批量插入语句的问题。

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

在MySQL中，普通的insert是一个比较轻量的操作。

但是有些insert的情况比较特殊，需要加锁等。本篇就来讨论这些情况，比如前面的insert…select批量插入语句。

1.insert … select 语句

加锁场景：insert…select语句，在可重复读隔离级别下，binlog_format=statement 时，执行时需要对表 t 的所有行和间隙加锁。

加锁原因：为了保持statement格式的binlog日志和数据的一致性。根本原因还是statement的描述性差的问题。

insert … select ：很常见的在两个表之间拷贝数据的方法。在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。
insert 和 select 是同一个表：有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。
insert 时如果出现唯一键冲突：会*在冲突的唯一值上加共享的 next-key lock(S 锁)*。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

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

如何在两张表中拷贝数据？

数据量和加锁范围小：直接 insert…select
无锁方案：现将数据写到外部数据文件，再写回目标表。

无锁方案：

对比一下这三种方法的优缺点。

物理拷贝方式：速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：
- 必须是全表拷贝，不能只拷贝部分数据；
- 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；
- 由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。
用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。
用 select … into outfile生成csv 的方法：是最灵活的，支持所有的 SQL 写法。缺点之一是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

后两种方式都是逻辑备份方式，是可以跨引擎使用的。

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

grant 语句是用来给用户赋权的。一些操作文档会要求，grant 之后要马上跟着执行一个 flush privileges 命令，才能使赋权语句生效。

grant 之后真的需要执行 flush privileges 才能生效吗？本篇进行解答。先说结论，不用。grant命令会同时更改表数据和内存数据。只有不规范的操作时需要使用，比如直接修改表数据，此时需要用flush privileges更新内存数据，即删掉内存数据，拉最新的表数据填入。

grant 之后真的需要执行 flush privileges 才能生效吗？是立即生效的吗？不用。会同时更新表和内存的。全局级别缓存到会话线程里，db级别在每次use db时重新缓存到会话，表或列级别grant后立即更新权限。

43.要不要使用分区表？

分区表有什么问题，为什么公司规范不让使用分区表呢？本篇来聊下分区表的使用行为。

场景都是单机分区。集群用不了分区（NDB引擎好像支持cluster模式）。

两种分区策略：

通用分区策略（generic partitioning）：每次访问分区都由 server 层控制。MySQL一开始支持分区表时就使用的策略，在文件管理、表管理的实现上很粗糙，性能也很差。MyISAM引擎使用的分区策略。
本地分区策略（native partitioning）：InnoDB引擎引入的，在 InnoDB 内部自己管理打开分区的行为。

上面讲的是范围分区，MySQL还支持hash 分区、list 分区等分区方法。

分区表的缺点主要是：

第一次访问的时候需要访问所有分区。
共用 MDL 锁。

因此，如果决定使用分区表，分区不要创建太多太细。

此外，上面讲的是单服务器的分区。集群分区可以看NDB引擎。

用分区表还是用中间件，根据团队自己决定。

比如，阿里云的DRDS就是分库分表的中间件典型代表。自己实现了一个层Server访问层在这一层进行分库分表（对透明），然后MySQL只是相当于存储层。一些Join、负载Order by/Group by都在DRDS中间件这层完成，简单的逻辑插叙计算完对应的分库分表后下推给MySQL

45.自增id用完怎么办？

本篇分析MySQL 里面的几种自增 id，它们的值达到上限以后，会出现什么情况。

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

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

当然，在 MySQL 里还有别的自增 id，比如 table_id、binlog 文件序号等

监控：

使用show processlist命令查看连接状态，即查看当前语句是什么状态。

数据库的慢查询日志中有一个 rows_examined 字段，表示这个语句执行过程中扫描了多少行。

可以用 show variables 来查看当前的配置参数：show variables like ‘transaction_isolation’;

如何查询长事务：可以在 information_schema 库的 innodb_trx 这个表中查询长事务，如下为查找持续时间超过 60s 的事务：select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60。监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；

事务具体的状态，查询 information_schema 库的 nnodb_trx 表。

空闲链接。即在 show processlist 的结果里，踢掉显示为 sleep 的线程，可能是有损的。

怎么干掉一个连接：用 kill connection + id 的命令去干掉sleep的连接即可。优先干掉trx_mysql_thread_id=4的。

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

InnoDB 提供了 *innodb_flush_log_at_trx_commit 参数来规定 redo log 的写入策略*

通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

索引优化：

经常一起查的索引，可考虑使用联合索引（覆盖索引）
注意使用最左前缀原则
唯一性的字段，可使用普通索引，而不是唯一索引，以激活change buffer。
使用唯一索引时，next-key-lock可退化为行锁，以减少死锁。
字符串索引可考虑前缀索引来节省空间。
排序较多的，可建立联合索引，此时数据加了索引，本来就是有序的，直接取，用不到排序操作。甚至是覆盖索引。

有可能导致索引失效的情况

案例一：条件字段函数操作。
案例二：隐式类型转换：类型是varchar，语句里却用int。
案例三：隐式字符集转换

转载请注明来源