两种日志系统
- binlog（归档日志）（Server层）
- redo log（重做日志）（InnoDB引擎）
  - 作用：
    - 高性能：MySQL先写日志，再写磁盘。因为每次更新，直接写磁盘IO和查找成本太高。
    - 高可用：crash-safe能力。
  - 特点：固定大小，循环写入，不持久保存。
  - 示意图：
两种日志的不同点
- 共有与特有：redo log 是 InnoDB引擎特有；binlog 是 MySQL 的 Server 层实现，所有引擎均可使用。
- 物理与逻辑记录：redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这
  一行的 c 字段加 1 ”。binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。
- 循环写与追加写：redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会
  覆盖以前的日志。
redo log 两阶段提交：
- redo log 的写入拆成了两个步骤：prepare 和 commit，这就是**”两阶段提交”**。两阶段提交是为了让binlog 和 redo log两个日志一致。
- 示意图：

3.事务隔离

本篇主题：事务隔离级别。

在 MySQL 中，事务支持是在引擎层实现的。如 InnoDB。
事务ACID特性：
- 原子性
- 隔离性
- 一致性
- 持久性
多个事务同时执行的问题：
- 脏读（dirty read）
- 不可重复读（non-repeatable read）
- 幻读（phantom read），就有了“隔离级别”的概念。
SQL标准事务隔离级别：为了解决上述问题
- 读未提交（read uncommitted）
- 读已提交（read committed）
- 可重复读（repeatable read）
- 串行化（serializable ）
隔离级别实现方式：
- 语句、事务级别的视图
- 串行化/行锁
为什么MySQL要使用RR作为默认隔离级别？历史问题。statement格式的日志，不用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.索引可能因为删除，或者页分裂等原因，导致数据页有空洞。2.用这个语句重建索引：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也可以。
- 表级锁：表级别锁有两种：表锁、元数据锁。
  - 表锁：语法为lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。
  - 元数据锁：（meta data lock，MDL)。无需显式使用，在访问一个表的时候会被自动加上。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实现）。更新时，只能用当前读，数据被占用时则堵塞。RR是在事务启动时(第一个语句执行，不是开始时)创建一致性视图。
- 事务读已提交RC：实现与RR一样。不同的是，RC是每一个语句执行前算出一个视图。

实践篇

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

本章主题：change buffer。

如果业务上已经能确定一个字段是唯一的，那么该字段选普通索引还是唯一索引？
- 查询过程：两者性能相差不大。
- 更新过程：可考虑使用普通索引，这样可以利用到 change buffer 来提升更新性能。唯一索引无法使用change buffer
change buffer
- 解决问题：提高数据库写性能。即更新操作性能。
- 原理：当需要更新一个数据页时：如果该数据页不在内存中：在不影响数据一致性的前提下：
  - 1.将这些更新操作缓存在 change buffer 中，这样就无需再从磁盘中读入这个数据页。
  - 2.在下次查询需要访问该数据页时，将数据页读入内存，再执行 change buffer 中与这个页有关的操作。
- change buffer是可持久化的数据：也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。会定期写到系统表空间。
- change buffer 持久化时merge的时机：
  - 1.访问该数据页时。
  - 2.系统后台线程定期merge。
  - 3.数据库正常关闭的过程中，也会执行merge。
change buffer适用场合
- change buffer适用于写多读少的数据，因为merge是要消耗性能的。
- 只有普通索引可以用 change buffer：读多写少时，普通索引字段 + change buffer可提高效率
- 唯一索引则不能使用 change buffer：唯一索引，必须要先读磁盘再加载数据页，因为唯一索引字段更新时需要先判断是否违反了唯一性约束。

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

本篇主题：优化器索引失效。

优化器索引优化判断依据：
- 扫描行数：
  - 如何计算扫描行数：通过索引基数判断
    - 索引基数可以使用采样统计得到。从N个数据页采样统计，更新数量超过1/M时重新统计。
    - 拿到索引基数，就可以根据语句来判断会扫描多少行数。
  - 优化失效原因：索引统计有时会不准确：
    - 现象：发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理。
    - 修复方法：用analyze table t 命令重写统计索引信息即可。
- 是否回表
- 是否使用临时表
- 是否排序
- 其他因素进行综合判断
优化器优化失效处理：
- 现象：偶尔会碰到，原本可以执行很快的语句，就是执行的慢
- 处理方式：
  - 重写索引统计信息：适用于由于索引统计不准确导致扫描行数计算不准的情况。
  - 用 force index 强行选择：优点是简单暴力。
  - 修改SQL诱导：修改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突然卡顿

本篇主题：刷脏页导致数据库“抖”了一下。

脏页：内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。
InnoDB 在处理更新语句时，只做更新内存的操作。而写日志（redo log）到磁盘的操作，则丢给后台执行，即刷脏页。
什么时候会刷脏页：主要涉及4种场景：
- 日志空间不足：redo log写满了
- 系统内存不足：此时需要淘汰一些内存数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
- 系统空闲时
- MySQL正常关闭时
InnoDB 刷脏页的控制策略及参数：待补充。

13.数据库表的空间回收

本篇主题：重建表以回收空洞。

经过大量增删改的表，都是可能是存在空洞的：因为删除某条记录产生的空洞可以复用，空间不会立即回收。
表重建：把这些空洞去掉，从而达到收缩表空间的目的。alter table A engine=InnoDB：使用该命令重建表即可。
表重建涉及的锁问题：
- MySQL 5.6前，DDL 不是 Online 的，DDL的锁是写锁，会阻塞增删改。
- MySQL 5.6后，支持 Online DDL。DDL的锁写锁退化为读锁。原表A此时允许增删改。
- 增删改的操作保存到单独的日志文件（row log），拷贝完成后，有一个短暂的停顿，把row log的变更更新到新的表中。
- Online的概念：就是单纯的表示，copy的时候不阻塞增删改而已。

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

本篇主题：count(*)实现。

两种引擎的count()实现方式*：
- MyISAM：
  - 实现方式：引擎把一个表的总行数存在了磁盘上。因此执行 count(*) 的时候会直接返回，效率很高。
  - 不过加了过滤条件的计数，也是一行一行计数。
- InnoDB ：
  - 实现方式：把数据一行一行地从引擎里面读出来，然后累积计数。比较麻烦。
  - 优化：count(*) 操作，会选取普通索引树，而不是主键索引树。能够减少扫描的数据量。
count()函数不同参数区别：
- 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，直接按行累加。
如何精确统计数据：
- show table status不能提供准确数据：是从索引统计值统计时的采样估算的，根据官方文档说误差可能达到 40% 到 50%。
- 只能自己手动计数，放到DB或Redis都可。

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

redo log 和 binlog 是怎么关联起来的?
- 它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描 redo log。

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

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

本篇主题：“order by”工作原理

MySQL 会给每个线程分配一块内存用于排序，称为sort_buffer。
排序类型：
- 全字段排序：
  - 执行流程：其实就是把所有满足条件的记录查出来，放到sort_buffer，然后排序，最后拿出符合条件的前N条记录即可。
  - 如果sort_buffer内存不够时，需要使用外部排序，即利用磁盘临时文件辅助排序。
- rowid 排序
  - 执行流程：简单的理解，就是不缓存全字段，而是只缓存id和参与排序的字段进行排序，拿到符合条件的id集后，再多一次回表操作拿到所有记录，最后返回结果。
- 索引排序
  - 执行流程：建立联合索引，此时由于数据加了索引，本来就是有序的，直接取，用不到排序操作。
  - 不是所有的order by都会用到排序：排序的成本是比较高的。如果数据本来就是有序的，那压根用不到排序操作。

17.快速取随机行

本篇主题：临时表

需求：在一张单词表里，访问时要随机选出3个单词，且访问的频率很高。
思路：
- 1.内存临时表(memory引擎)排序：即*order by rand()，随机排序，取前三个：select word from words order by rand() limit 3;
- 2.磁盘临时表（默认InnoDB引擎）：不是所有临时表都是内存临时表。内存不够时，使用磁盘临时表。
- 3.随机算法（即免临时表方案）
  - 方案1：ID范围映射到0-1，随机取一个。
  - 方案2：统计记录总行数，然后映射到0-1，随机取第N行。
临时表参数配置：
- 什么情况下会产生临时表Using temporary*？查询需要临时表的时候，比如上述例子里，需要临时表来放rand()结果。
- tmp_table_size 参数：内存临时表大小的参数。默认16M，超过这个大小则转换为磁盘临时表。
- internal_tmp_disk_storage_engine参数：配置磁盘临时表的引擎，默认 InnoDB。
- 教训：临时表排序代价很高，使用到临时表时，要注意能不能避开。另外可以调高sort_buffer参数。

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

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

19.为什么查询慢

本篇住址：锁和慢查询导致查询慢。

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

分析思路：
- 硬件问题：直接Top、free、netstat，查看硬件使用情况。
- 软件问题：用*show processlist 命令*，看看当前语句处于什么状态，分析原因，
查询慢原因：
- 系统本身负载就高：可以通过连接数、结合硬件状态来体现负载。
- 有锁：
  - 表锁：等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。
- 查询本身就慢：
  - 没有索引：加索引。
  - 一致性读，回滚巨量undolog。

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

本篇主题：幻读。

幻读
- 幻读的概念：指一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
- （所以插入的行才算是幻读，更新的行不算，因为前面已经是可以看到的）。
- 也就是说，即使把所有筛选到的记录都加上锁，还是阻止不了新插入的记录，这也是为什么“幻读”会被单独拿出来解决的原因。
间隙锁（gap-key）：
- 引入间隙锁以解决当前读的幻读问题：隔离级别下的可重复读只能解决快照读的幻读问题只解决。而当前读的幻读问题，需要使用间隙锁来解决。间隙锁只在RR级别生效
- 跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
- 引入间隙锁带来的死锁问题：
  - 原因：大家一起都加间隙锁，然后大家一起插数据，然后就都等着对方释放间隙锁。
  - 为了解决幻读问题，引入间隙锁，但是也带来了死锁的问题。如果无需解决幻读，那么可以直接用读已提交，不过要解决日志不一致问题：读已提交 + binlog_format=row。
Next-Key Lock ：
- “行锁（Record Lock）+ 间隙锁”，合称 next-key lock，每个 next-key lock 是前开后闭区间。
- 对于INSERT的操作，其会检查插入记录的下一条记录是否被锁定，若已经被锁定，则不允许查询。
- Next-Key Lock隔离级别：
  - InnoDB存储引擎默认的事务隔离级别是RR，在该隔离级别下，Innobb其采用Next-Key Locking的方式来加锁,解决幻读问题。
  - 如果把隔离级别设置为RC的话，就没有间隙锁了。但同时，要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。
可重复读解决了幻读问题吗？见上文。幻读问题分为快照读和当前读两种情况。前者MVCC解决（RR/RC均生效），后者间隙锁解决（仅RR生效）。

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(前面两种的混合)。MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则用 statement 格式。
双主循环复制问题：
- 双主结构的循环赋值问题：互相赋值对方binlog。*
- 如何解决循环赋值问题*：简单说就是给binlog加一个server id来识别过滤。

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

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

主备延迟问题：
- 在备库执行show slave status 命令，返回的结果里面会显示seconds_behind_master，就是用来表示当前备库延迟了多少秒。
- 主从时间不一致没问题，从库会自动扣除差值。
- 其实延迟问题没法解决：要不就全同步。要不就强制走主数据库。
主备延迟原因：
- 备库太差。
- 备库读的压力太大。大事务。
主备切换：
- 保证可靠性：先只写主库。从库追上后，从库改主库。
- 保证可用性：直接切。可能会出现主备数据不一致。
也可以用可靠性优先，DB不可用时，把日志暂时存放到其他文件或临时表，最后切换完成后再统一恢复即可。

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

对于备库：关键点就在于上述图中的sql_thread。
- MySQL5.6 之前：单线程执行的，此时主库太快，备库就跟不上。MySQL最新版已改为多线程执行。
- 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。
案例三：隐式字符集转换

转载请注明来源