《MySQL实战45讲》学习记录

1 | MySQL 中一条查询语句的执行过程

mysql> select * from T where ID=10；

MySQL 基本架构示意图（逻辑架构图）

大体来说可以分为 Server 层和存储引擎层
Server 层包括。。。，涵盖 MySQL 的大多数核心服务功能，以及内置函数，所有跨引擎的功能（比如存储过程、触发器、视图等）
存储引擎层负责数据的存储和提取。插件式，支持 Innodb、MyISAM、Memory 等存储引擎。最常见的是 Innodb，MySQL 5.5.5 后 Innodb 成为默认存储引擎。
可通过 create table 语句中使用 engine=memory 来指定使用的存储引擎。

连接器
客户端和服务端建立连接（tcp、验证用户名密码）、维持和管理连接、获取权限。
建立了连接后，如果太久没有操作，连接器会自动断开连接。
建立连接的过程耗费资源，可使用长连接（不自动断开）。弊端在于长连接太多，占用内存（断开连接时才释放改连接占用的内存）
查询缓存
之前执行过的查询的语句和结果可能会以 (k, v) 的形式保存在内存。
如果在内存，直接返回 val；如果不在内存，执行后续操作查询到结果后，将查询的语句和结果以 (k, v) 的形式保存在内存。
查询缓存大多数情况不建议使用，因为对表的一条写语句，都会清空表上的所有查询缓存。
MySQL 8.0之后，查询缓存整块功能被删除。
分析器
从现在开始，真正执行查询语句了。
首先对 SQL 语句进行解析（MySQL 要知道你要做什么）。
首先是词法分析。你的输入（一条 select 的 SQL 语句）本质上是一个字符串，由若干字符串和若干空格组成。MySQL 通过“select”关键字，识别出这是一个查询语句。通过“T”识别出表名为“T”。通过“ID”识别出列名“ID”。（在这里判断是否有表“T”，表“T”是否有列"ID"）
然后是语法分析。MySQL 判断你输入的 SQL 语句是否符合 SQL 语法规则。
优化器
到这里，MySQL 知道你要做什么了。但是还是要通过优化器，帮助 MySQL 决定用什么方式去做（基于 MySQL 认为的执行效率）。比如有多个索引的时候，具体选择那个索引；有多表关联（join）的时候，各个表的连接顺序。

比如 select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20; 这条查询语句
既可以取 t1 中 c=10 的行，通过 id 和 t2 连接，取其中 t2.d=20 的行
也可以取 t2 中 d=20 的行，通过 id 和 t1 连接，取其中 t1.c=10 的行
两种执行方案的逻辑结果相同，执行效率不同，优化器做的就是决定使用哪一个方案。

执行器
经过分析器，MySQL 知道了你要做什么。经过优化器，MySQL 知道了要怎么做。接着进入执行阶段。
首先要验证你有没有查询这个表的权限。
如果有，到具体使用的执行引擎取数据，并做计算。

比如 mysql> select * from T where ID=10 and name=“Tom”；
如果没有索引，要走全表查询。查询器会通过 Innodb 引擎接口取一行数据，判断是否满足 ID=10 and name=“Tom”。不是则跳过；是则保存在结果集。循环取下一行，直到表的最后一行。最后将结果集返回。
如果有索引，走索引。比如在 ID 列有索引。查询器会通过 Innodb 引擎接口取满足索引条件（ID=10）的第一行，判断是否满足 ID=10 and name=“Tom”。不是则跳过；是则保存在结果集。循环取满足索引条件（ID=10）的下一行，直到表的最后。最后将结果集返回。

2 | MySQL 中一条更新语句的执行过程

-- ID 列建了索引
mysql> update T set c=c+1 where ID=2;

连接器连接。
将该表的所有查询缓存清空。
分析器的词法分析、语法分析，得知是更新语句、要更新什么。
优化器决定走 ID 列的索引。
执行期负责找到这一行，然后更新。
与查询流程不同的是，更新流程还涉及两个重要的日志模块。redo log（重做日志）和binlog（归档日志），

重要的日志模块：redo log

《孔乙己》酒店掌柜的粉板，专门用来记录客人的赊账。还有个账本。
如果有人赊账、还账：为了效率，先记在粉板上，等空闲时或粉板记满的时候，再写一部分到账本，让粉板空出一些位置。
类似的，对于 MySQL，如果每次更新操作都要写入磁盘，磁盘还要找到那条记录，然后再更新。整个过程 IO 成本、查找成本都很高。

为了提升效率，MySQL 使用了 WAL（Write-Ahead Logging）技术。WAL 的关键点在于：先写日志，再写磁盘。
具体来说，当有一条记录需要更新时，Innodb 先把记录写在 redo log里，并更新内存，这个更新就算完成了。事后 MySQL 会在适当的时候（往往是系统比较空闲的时候），将这个操作记录写在磁盘。

如果赊账不多（粉板空间足够用），掌柜可以在打烊之后再整理。
如果赊账比较多，粉板写满了，这时候掌柜只能放下手中的活（停止正常营业），将粉板上的一部分赊账记录更新到账本上，并擦去粉板上这部分赊账记录，为记新赊账腾出空间。

与此类似，Innodb 的 redo log 是固定大小的，比如可以配置一组4各文件，每个文件大小1GB，一共4GB。从头开始写，写到末尾又从头开始写（直接覆盖写），像一个环形。

write pos 是当前记录的位置。check point 是当前要擦除的位置（也是上次写磁盘的末尾位置），擦出记录前把记录更新到磁盘的数据文件。write pos 和 check point 都是往后推移并且循环的（上图就是顺时针运动）。
write pos 和 check point 之间的部分（上图绿色区域）就是 redo log 中空白的部分，可以用来记录新的 redo log。当 write pos 追上 check point，就没有空间记录新的 redo log了，这时不能执行新的更新，得停下来擦去一些记录，把 check point 推进一些。

有了 redo log，Innodb 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失。这个能力称为 crash-save。即在 InnoDB 存储引擎中，事务提交过程中任何阶段，MySQL突然奔溃，重启后都能保证事务的完整性，已提交的数据不会丢失，未提交完整的数据会自动进行回滚。恢复内存状态

crash-save 相当于只要赊账记录记在了粉板上或者账本上，之后即使掌柜忘记了（比如停业了几天），开业时依然可以通过账本和粉板上的数据明确赊账账目。

innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘（redo log 先写 buffer，不一定马上写磁盘）。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失

重要的日志模块：binlog
redo log 是 Innodb 引擎特有的日志。binlog（归档日志） 是 Server 层自己的日志

binlog 归档日志：mysql 的 server 层自带。用于记录所有成功提交了事务的操作。写满一个文件写下一个文件。

binlog 没有 crash-save 能力，因此 Innodb 使用另一套日志系统——redo log来实现 crash-save。（如果不用 redo log，只用 binlog ，除非 binlog 上增加 check point，这样把 check point 之后的 binlog 都执行一遍，也能实现 crash-save）

binlog 和 redo log 的不同
1.binlog 是 Server 层实现的，所有引擎都可以使用；redo log 是 Innodb 特有的。
2.binlog 是逻辑日志，记录了语句的原始逻辑（类似于 SQL）；redo log 是物理日志，记录了哪个数据也做了什么修改
3.binlog 是“追加写”，写完一个文件写下一个文件，不覆盖历史文件；redo log 是"循环写"的，固定大小，写到末尾从头开始覆盖写

执行器和 Innodb 在执行 update 语句时的内部流程：(update T set c=c+1 where ID=2;)

1. 执行器找 Innodb 取 ID=2 这一行。ID 是主键，Innodb 通过主键索引树找到这一行。如果这一行所在的数据页本来就在内存中，直接返回给执行器；否则，需要从磁盘读入内存，然后再返回。
2. 执行器拿到这一行，将 c 列的原始值 +1 作为新值，让 Innodb 写入新的一行记录
3. Innodb 更新这行记录所在的数据页上的数据，同时将更新操作记录在 redo log 的 prepare 阶段，告知执行器随时可以提交事务
4. 执行器生成 binlog，将 binlog 写入磁盘。
5. 执行器通知 Innodb，将该记录的 redo log 置为 commit 状态，更新完成。

redolog 两阶段提交：保证 redolog 和 binlog 的逻辑一致

1. redolog 的 prepare 阶段
2. 写 binlog
3. redolog 的 commit 阶段

当在2之前崩溃：
重启恢复（最近一次全量备份+binlog）后：redolog 没有commit，回滚（删除 redolog 上该条 prepare阶段的记录）。备份恢复：binlog 没有该记录，一致
当在3之前崩溃：
重启恢复（最近一次全量备份+binlog）后：虽没有commit，但满足prepare和binlog完整，所以会将 redolog 上该条 prepare阶段的记录置为commit。备份：binlog 有该记录，一致

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

事务

事务
事务是一组数据库操作，要么全部成功，要么全部失败
事务实现了 ACID 中的 I 隔离性
Innodb 支持事务，MyISAM不支持事务

Innodb 的4大隔离级别

1. read uncommited 读未提交 事务A会读到事务B未提交的修改
   未解决脏读、未解决不可重复读、未解决幻读
2. read commited 读提交 事务A会读到事务B提交的修改
   解决了脏读、未解决不可重复读、未解决幻读
3. repeatable read 可重复读 事务A读到的同一条记录不变，即使事务B期间修改了该记录
   解决了脏读、解决了不可重复读、未解决幻读
4. serializable 串行化 事务A在事务期间读到的数据始终相同
   解决了脏读、解决了不可重复读、解决了幻读

记忆
读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。
串行：我的事务尚未提交，别人就别想改数据。
这4种隔离级别，并行性能依次降低，安全性依次提高。

实现上，数据库会创建一个视图，事务的读取结果以视图的逻辑结果未准。"读未提交"不创建视图，直接读表上的值。"读提交"在每个 sql 开始语句执行时创建视图。“可重复读”在事务启动时创建视图，且整个事务期间都用此视图。“串行化”直接通过加锁避免并行访问。

脏读、不可重复读、幻读

1. 脏读：事务A会读到事务B未提交的修改（体现在事务A在事务B修改前后读同一个数据，出现不一致）
2. 不可重复读：事务A会读到事务B提交了的修改（update）（体现在事务A在事务B提交前后读同一个数据，出现不一致）
3. 幻读：事务A会读到事务B提交了的插入（insert）（体现在事务A在事务B提交前后读同一组数据，出现记录数不一致）

事务隔离级别的实现：MVCC 多版本并发控制
每一条修改除了写 redolog，还要写 undolog。
通过 MVCC 实现了4个隔离级别用户读数据的问题。
比如一个数据修改从1改为2改为3改为4，undolog 中就会有类似下面的记录

同样是对该数据的查询，不同时刻启动、不同隔离级别的事务会有不同的 read-view（读视图），且不同事务的 review 互不影响。（读视图并不是类似表的存在，不是直接记录数据的，而是通过 undolog 将数据目前的值回滚为某个版本的值）

undolog 会定期删除，当系统判断没有事务再会用到这些 undolog 时，也就是当系统里没有比这个回滚日志更早的 read-view 的时候。

事务的启动

begin 或 start transaction;
--sql1;
--sql2;
--sql3;
commit;

rollback 回滚

索引

作用：提高查询效率
类似于书的目录，用空间换取时间

索引的常见模型
hash索引：适用于只有等值查询的场景
有序数组索引：适用于静态数据存储的场景，因为插入效率差
N叉树索引：由于读写上的性能优点，加上适配磁盘的访问效率，已经广泛引用于数据库引擎中了
跳表（redis、hbase）、LSM树（hbase日志合并树）等数据结构也应用于各种数据库产品中了

Innodb 就使用了N叉树索引，N为数据块大小/字段大小，差不多是1200，一棵高为4的树可以保存的记录为1200^3=17亿。而树根是常驻内存的（树的第二层也有很大概率在内存中），所以查找一个值只需2、3次磁盘IO。

MySQL 中，索引在存储引擎中实现。
Innodb 中，索引可分主键索引和非主键索引
主键索引的叶子结点存储的是整行数据（每个字段），因此 Innodb 的主键索引也是聚簇索引
非主键索引的叶子结点存储的是索引字段和主键，因此 Innodb 的非主键索引是稀疏索引/二级索引

通过主键索引树查找，到叶子结点即找到了整条记录。
通过非主键索引查找，到叶子结点只找到了该记录的主键值。需要在主键索引树通过该主键值查找整条记录。这个过程叫回表。

索引维护
B+ 树的分裂、合并、节点挪动，耗费资源。
当使用非空自增主键时，新插入的主键为当前表记录的最大主键值+1，是追加操作，不涉及挪动、分裂等。
主键的选择上，可以不是非空自增的，可以是某个字段，但是长度要小。因为每个非主键索引的叶子结点都要存储主键，主键长度决定了非主键索引占用的空间。

覆盖索引
非主键索引（索引字段和主键字段）覆盖了搜索条件（where和select中涉及的字段），这时不用回表。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
经常根据业务中的高频查询字段，建立联合索引。代价就是需要维护（插入、更新时修改索引）。

最左前缀原则
索引树的结点（非叶子、叶子）的索引项是按照索引定义时的字段顺序来排序的。
最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

如联合索引（a，b，c）
select id from t where a = 1 and b = 2
select id from t where a like 'Zhang%'

联合索引建立时，如何安排字段的顺序
（a，b，c）
存在单独查询的场景的字段，放在左边，查询时可用上联合索引。

select id from t where a = 1

此时可以不对 a字段 单独建立索引，可以少维护一个索引，因此这是第一原则。
其次考虑空间。比如索引（a，b），既存在单独查询 a，又存在单独查询 b，还存在 a、b 的联合查询，此时就不得不建立两个索引了。是建立联合索引（a，b）和单字段索引（a），还是建立联合索引（a，b）和单字段索引（b）呢？取决于 a 字段和 b 字段哪个占用空间少了。

索引下推
索引（a， b）

select id from t where a like 'Zhang%' and b = 100 and c = 1

MySQL 5.6之前，只能将 a 满足条件的所有记录一个一个回表查询，这其中当然包括不满足 b = 100 的条件的记录。
MySQL 5.6之后，引入的索引下推优化（index condition pushdown)，可以在回表之前对索引中包含的字段进行判断（如b），过滤掉不满足条件的记录（这个记录就不回表了，在非聚集索引树接着找下一个符合a like Zhang条件的记录），减少回表次数。

 select id
 from t
 where a = 1
 order by b (asc)
 limit 1

在order by (asc) limit x的字段使用联合索引（a，b）也是极好的（根据B+树的特点，满足a = 1的前x个记录即为 sql 结果）

浅析锁：全局锁、表级锁、行级锁

MySQL 数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁
全局锁就是对整个数据库实例加锁，命令是 Flush tables with read lock (FTWRL)。当需要让整个数据库处于只读状态时，可以开启全局锁。之后其他线程的数据更新语句（数据的增删改）、数据定义语句（建表语句、修改表结构的语句）、更新类事务的提交语句，都会被阻塞。
全局锁的经典使用场景：全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

表级锁
表级锁分两种：表锁和元数据锁（meta data lock，MDL）

表锁
表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

MDL
另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果就跟表结构对不上了。
因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

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

MDL锁 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

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

1. kill 与该小表相关的长事务
2. 若该小表为热点表，请求频繁：比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。MySQL 8.0已经支持相关语法（DDL NOWAIT/WAIT n）

行锁
MySQL的行锁是由各个引擎自己实现的，有的引擎支持行锁（如 Innodb）有的引擎不支持（如 MyISAM）。MyISAM没有行锁，意味着并发控制只能用表级锁，同一张表上任何时刻只能有一个更新在执行，影响业务并发度。而 Innodb 支持行锁，这也是 MyISAM 被 Innodb 取代的原因之一。

Innodb 的行锁：通过减少锁冲突来提高业务并发度
行锁就是针对数据表中行记录的锁。

两阶段锁协议：行锁的在需要的时候加上，但是并不是不需要了立即释放，而是在整个事务提交后才释放。比如事务 A 更新了一行（未提交事务），事务 B 要更新同一行，会被阻塞，直到事务 A 提交了才能继续执行更新。
两阶段锁协议的特点带来的启示：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

死锁和死锁检测
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待其他线程释放资源时，就会导致这几个线程都处于无限等待的状态，称为死锁
MySQL 对死锁有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。这种策略无法区分简单的锁等待和死锁，时间设置得太长，遇到死锁会等待很久；时间设置得太短，又会“误伤”简单的锁等待。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（一般是回滚代价最小的事物，即 undolog 最少的事务），让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。正常情况下采用这种策略。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的（搜索，可能是DFS？直到出现闭环）。

热点行更新导致的性能问题
当有n个线程要同时更新同一行时（抢同一个行锁），发生锁等待，要进行死锁检测。每个线程都要对其他 n-1个线程 看看 if（对方持有的锁 == 自己要申请的锁）&&（自己持有的锁 == 对方要申请的锁），死锁检测的代价为 O(n^2)。虽然实际上是没有死锁的（持有锁的线程在事务提交后释放锁，假如不作死锁检测，最后是可以正常执行完所有更新的），但是发生锁等待就会去做死锁检测，死锁检测是 O(n^2) 量级的，cpu容易挂。
如何解决？

1. 在可以保证这个业务一定不会发生死锁的前提下，临时关闭死锁检测（此时只剩超时机制）。不建议，可能会出现大量超时，业务受损。
2. 控制并发度

   1.在客户端做并发限制。限制单个客户端的并发上限。但是当客户端数目较多，最后服务端的并发数就是所有客户端的总和了。不建议
   2.在服务端做并发控制。通过中间件或者修改 MySQL 源码来实现。基本的思路是，对于相同行的更新，在进入引擎之前排队。
   3.在表设计上进行优化。比如将一行记录分为多行记录。比如原来一行记录表示影院的账户总额，改为用多行。每行记录相当于分账户，多行记录之和表示的账户总额。每次更新时，多行中随机挑一条进行更新，就大大降低了产生冲突的概率。但是这类方案要对实际业务逻辑进行详细设计，可能要做特殊处理。

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

事务中的更新需要当前读（更新之前，读取数据表中的数据值，作为更新操作的旧值）。

比如 a = 1
事务 A 开启
事务 A 查 a = 1
其他事务更新了 a = a + 1
事务 A 查 a = 1（事务 A 的 read-view 上 a = 1，实际数据表中 a = 2）
事务 A 更新了 a = a + 1（更新时需要当前读，读得 a = 2，更新后a = 3，更新 read-view 中 a 的值）
事务 A 查 a = 3（事务 A 的 read-view 上 a = 3，实际数据表中 a = 3）

MySQL 里有两种视图

• 一个是 view，它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样
• 另一个是 Innodb 在实现 MVCC 时用到的一致性读视图（consistent read view），用于实现 RC（Read Committed）和 RR（Repeatable Read）两种隔离级别的实现。它没有物理结构，用于定义该事务在事务执行期间能看到的数据的版本。

一致性读视图在 MVCC 中的实现
Innodb 的 RR 可重复读隔离级别下，事务已开启的时候都会产生一个全库的"快照"——read-view。考虑到数据量可能很大，read-view 不可能直接拷贝全库所有数据状态，而是有其他的实现方式。
InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

事务高低水位

而且每行数据也是有多个版本的。不是指每行数据的多个版本直接存储在数据表上，数据表上只保存每行数据最新的一个版本，而历史版本会保存在 undolog 中。每个数据版本的产生一定是由于数据的更新，数据版本的row trx_id会记录产生数据更新的事务的 transaction id。

事务执行select的时候，先从数据表中找到该数据（数值是最新版本），通过比对版本的row trx_id和当前事务的 transaction id，如果是当前事务不能看的数据版本，就根据 undolog 去找上一个数据版本，知道找到第一个能看的数据版本。“能不能看”体现在 row trx_id 的事务提交早于 当前事务的开启。

update包括读和写两个动作。先读数据值，再将更新后的结果写入数据表。事务执行update的时候，读的是数据表上的值，也就是数据的最新版本，而不是 read-view 上该数据的版本，更新也是基于数据的最新版本的更新，否则会丢失事务开启后 其他事务的提交 对该数据的更新。

总结：
对于 RC 读提交，查询 只承认 在语句启动前 就已经提交完成 的数据；
对于 RR 可重复读，查询 只承认 在事务启动前 就已经提交完成 的数据；

普通索引和唯一索引的异同

查询过程：
对于

-- k上建了索引
select id from T where k=5;

对于唯一索引：找到了第一条符合 k = 5 的节点后，流程结束。
对于普通索引：找到了第一条符合 k = 5 的节点后，接着找下一条，直到下一条不符合 k = 5，流程才结束。
性能差别微乎其微：Innodb 将一整个数据页（默认16kb）读到内存，除非普通索引的结点刚好是数据页的最后一个结点，读下一个结点时需要将下一个数据页整个读入内存，这个会麻烦点，但是概率极小。
只有非唯一性索引（普通索引）可以使用 change buffer。因为对于唯一性索引（主键索引、唯一索引），插入或更新时要先判断新值是否是唯一的（数据表中是否已经存在索引值为新值的记录），这个操作要将数据页读到内存才能判断，既然都把数据也读到内存了，就没必要使用 change buffer 了。而对于非唯一性索引（普通索引），不需要读取数据页并判断唯一性，因此直接将更新的内容保存在 change buffer 。空间代价上更小，读一个数据页占用 buffer pool（Innodb所占用的内存的表现形式） 的16kb，而保存在 change buffer 虽然也占用 buffer pool 的空间，但是只需要顺序写。

更新过程：
change buffer：当需要更新二级索引树的数据页时，会先将更新保存在 buffer change，等下次查询到这个数据时，读取二级索引树的数据页，加上 change buffer 中的 change，就是真正的数据，修改数据页并返回结果（数据页定期在后台刷写磁盘）。change buffer也会定期持久化到磁盘（merge）。

例如插入一条新纪录（4， 400）。
第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：
对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。但，这不是我们关注的重点。
第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：
对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

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

change buffer 适用场景
前边说过适用于普通索引的更新/插入场景。
又由于 change buffer 是在做 merge 的时候将内容写入磁盘，而在更新/查询之后的第一次 select 时候会做merge。因此将多次更新/插入一次性 merge 入磁盘，可以最大程度地优化。
因此，change buffer 适用于更新之后不会马上查询、或者说多次更新一次查询的场景。
而对于每次更新都马上查询的场景，即使触发条件先写 change buffer，但是由于马上查询会触发 merge，不但不会减少随机磁盘IO，还会增加 change buffer 的维护成本。这种情况需要关闭这个普通索引的 change buffer。

redolog 和 change buffer
先不看 redolog 和 change buffer，对于数据修改（insert、update、delete）来说，无非是三个步骤：

1. 将数据行所在的数据页读入内存
2. 相应的修改内存中的数据页
3. 将修改后的数据页写入磁盘

其中1、3涉及随机IO，1是随机读IO，2是随机写IO。
change buffer 节省的是步骤1的IO消耗。且只有满足特定条件（普通索引、修改时数据页不再内存中、修改后不马上查询的场景）的时候，才能优化。
redolog 节省的是步骤3的IO消耗。redolog 本身是顺序读写的。redolog 通过两阶段提交保证了 crash-safe。

总结：
普通索引和唯一索引在查询性能上没差别，都是普通索引在更新性能上比较好。在业务能保证唯一性的前提下，建议使用普通索引。

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

写 SQL 时，如果不主动指定使用哪个索引，使用哪个索引是由 MySQL 来确定的。
优化器优化时会考虑的因素： 1.扫描行数 2.是否使用临时表 3. 是否需要排序
对扫描行数的预估靠“抽样统计法”
普通索引需要回表，这个代价优化器也会算进去。

对于由于索引统计信息不准确（比如扫描行数的预估）导致的问题，你可以用 analyze table 来解决。
而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

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

innodb支持前缀索引，可指定索引为字符串前n个字节（1个字符占1个字节）。
前缀索引可以减少索引占的存储空间大小，但是可能会增加额外的记录扫描次数。因此设置合适的前缀索引大小很重要，保证该长度下的前缀的区分度足够好的同时，长度尽可能小。

前缀索引对覆盖索引的影响
使用前缀索引，就用不上覆盖索引对查询性能的优化了。

实战
比如对于邮箱这样的字符串字段，长度为6的前缀索引的效果可能就相当好了。
但是对于身份证号码这样的字符串字段来说，字符串的前缀是居民的区域信息，直接使用前缀索引区分度不够好。
对于身份证号码来说，可以采用以下两种方法加索引。

1. 倒序存储。在数据库中保存原身份证号码的倒序。身份证号码的后几位是比较随机的，对身份证号码的倒序建立前缀索引，有比较好的区分度。
2. 使用hash字段。数据库表增加一个整数字段（4个字节），每次新插入一条记录时，将身份证号码用crc32()函数获得一个校验码，并存储该校验码。在这个校验码字段上建立索引（4位）。由hash的特点知，校验码不同，原身份证号码一定不同；校验码相同，原身份证号码有可能相同（hash冲突）。因此查询时需要指定身份证号码。

   SELECT field_1, field_2 
   FROM t
   WHERE hash = crc32('441581xxx')
   	AND id_card = '441581xxx'
   

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

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

1. 相同点：都不支持范围查询
2. 从额外占用的存储空间来看，倒序存储在原字段上建立前缀索引，表本身并没有额外的存储消耗；使用hash字段则需要在表上增加一个整数字段来存储原字段的hash结果
3. 从CPU消耗来看，倒序存储需要在插入和查询时都需要调用一次reverse()函数；使用hash字段在插入和查询时，也都需要调用一次crc32()函数
4. 从查询效率来看，使用hash字段的查询性能更加的稳定。即使 crc32 可能存在 hash 冲突，但是概率非常小，可以说使用hash字段的查询时间复杂度（平均扫描行数）趋近于1；而倒序存储始终还是用的前缀索引的方式，还是会增加行数（区分度不可能为100%，且较多的小于100%），而且还需要回表。

字符串字段创建索引可以使用的方式有：

1. 直接创建完整索引，比较消耗存储空间
2. 正常的前缀索引（正序），支持范围查询，空间消耗低，无法使用覆盖索引，很可能需要回表（除非是SELECT id FROM t WHERE 索引字段 BETWEEN ab AND ac），可能增加需要扫描记录的行数
3. 倒序索引，除了不支持范围索引、多了一次reverse()函数调用，其他同前缀索引。用于原字符串的前缀区分度低、后缀区分度高的情况。
4. 创建hash字段索引，查询效率高，需要创建额外的表字段，多了计算消耗（比如crc32()函数调用），不支持范围查询

MySQL “抖”一下

正常执行速度很快的 SQL，有时变得特别慢。随机，且持续时间很短。
这是由于 MySQL 在 flush 刷写磁盘。

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

flush 刷脏页的场景：

1. 一种情况是 redolog 写满了（write pos 赶上了 check point），必须写一部分到磁盘，从而让 redolog 腾出点空间（check point 前移）。这个过程停止处理写请求。
2. 第二种情况是内存空间不足了，要淘汰一些内存中已有的数据页（可能是干净页，也可能是脏页），腾出空间给其他数据页使用。当是脏页时，就要先将脏页刷写到磁盘。
3. 第三种情况是 MySQL 认为系统“空闲”的时候，“闲着也是闲着”，刷一些脏页。这个空闲的时候可能是大片空闲的时间，也可能是系统繁忙时段的“见缝插针”。
4. 第四种情况是 MySQL 正常关闭的时候，会将内存中的脏页全部刷写到磁盘上。这样下次 MySQL 启动时，直接从磁盘读数据就行了。

刷脏页的四种场景对性能的影响
第三种是在系统“空闲”时刷写，第四种是 MySQL 即将关闭，不用关注性能问题。
第一种是 Innodb 需要尽量避免的，因为 redolog 满了刷写磁盘的过程，系统停止处理更新请求。
第二种是“内存不够用了，要淘汰一部分数据页，其中的脏页要写到磁盘”。这种场景时常态。
Innodb 用缓冲池 buffer pool 来管理内存。缓冲池中的内存页有3种状态：

1. 第一种是，还没有使用的
2. 第二种是，使用了并且是干净页的
3. 第三种是，使用了并且是脏页的

Innodb 的策略是尽量使用内存，因此属于第一种的很少。
而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。当内存满了的时候只能淘汰一部分，这时候只能把最久不使用的（LRU）数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

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

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

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

Innodb 刷脏页的控制策略

1. 合理设置 innodb_io_capacity 这个参数，它会告诉 InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS。InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。脏页刷太慢会导致脏页积累，甚至刷脏页速度小于产生脏页的速度。
2. 平时要多关注脏页比例，不要让它经常接近 75%

脏页“连坐”。如果要刷写的一个脏页的旁边的数据页刚好是脏页，一并刷写，并且具有传递性。
这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。
而如果使用的是 SSD 这类 IOPS 比较高的设备的话，我就建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。
在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。

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

直接删除表 drop table
参数 innodb_file_per_table
表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：
这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。

我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

删除行
删除行，在存储底层将 B+ 树的该行所在数据页上的该行数据清空，并置为“可复用”（空洞）。当下次有符合 B+ 树搜索条件的记录插入时，进行复用，用该行存储新记录。类似的，将一整个数据页的数据都删除，整个数据页的所有行数都置为“可复用”。但是并不会回收空间，也就是磁盘上的文件不会减小。
有可能造成空洞：1. 插入 2. 更新 3. 删除
如果能把空洞去掉，就能达到收缩表空间的目的。
重建表，就也可以达到这样的目的。

重建表
MySQL 5.5 之前

alter table A engine=InnoDB
-- == alter table t engine=innodb,ALGORITHM=inplace;原地

整个 DDL 过程，表中不能有增删改（非 online）。

MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化
简单说就是在重建表是生成临时表的过程中发生的增删改，保存在一个日志文件中（row log）。临时文件生成后，再将 row log 中的操作应用到临时文件。
最后用临时表替代原表。

重建表的时候，不会吧数据页全部塞满，每个数据页会留 1/16 给后续的更新用。

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

Online 和 inplace （原地）

count(*)

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

• MyISAM 把一张表的总行数保存在磁盘上，执行 count(*) 直接返回这个数，效率很高。
• Innodb 执行 count(*) 的时候需要把数据一行行从引擎中读出来，累积计数。（由于 MVCC 多版本并发控制的存在，只有该事务可见的行能计入 count(*)，因此只能一行一行读）
  
  上图：同一时刻，同一查询，不同结果
  实际上 Innodb 对执行 count(*) 已经做过优化了。
  任何一棵索引树做遍历求 count(*) 的结果都是一样的。主键索引的叶子结点保存的是所有字段，每个数据页能存储的数据行最少（在所有索引中），索引树最大（数据页/叶子节点多）。所以 Innodb 会选择最小的索引树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。
  show table status 命令显示的 TABLE_ROWS 行数也不能直接使用，这个值是通过“采用统计法”估算的，官方文档说误差可能达到 40% 到 50%。

小结：

• MyISAM 表虽然 count(*) 很快，但是不支持事务；
• show table status 命令虽然返回很快，但是不准确；
• InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题。

因此想要 count(*) 计数，只能采用别的方法。

1. 用缓存系统保存计数。计数值在逻辑上是不准确的。因为“更新 MySQL”和“更新缓存”是两个操作。无论这两个操作的先后，当“从缓存中获取 count(*) ”发生在这两个操作之间的时候，数值和实际值是不一致的。缓存系统还容易丢失更新。（不支持分布式事务，无法拿到精确一致的视图）
2. 在数据库保存计数。在数据库用单独的一张计数表保存 count(*) 的值。更新/删除的时候，需要在一个事务中操作数据表和计数表，这样其他事务做计数表的查询，查询结果和实际结果是逻辑一致的。（把计数值也放在 MySQL 中，就解决了一致性视图的问题）

不同 count() 的用法
count(id)，Innodb 遍历整张表，把每一行的 id 取出来，返回给 Server 层。Server 层遍历所有的 id，判断不为 NULL 的计数累加。
count(1)，Innodb 遍历整张表，但不取值的。Server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。单单看这个差别，count(1) 比 count(*) 快，因为不需要拷贝字段值。

实际上 count(*) 专门做了优化，不取值。
所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。

15 ｜ 答疑文章（1）

日志相关
再谈事务提交时，redolog 和 binlog 扮演的角色

redolog prepare 状态 ----时间点 A—> 写 binlog ----时间点 B—> redolog commit 状态

当发生 crash 崩溃时，MySQL 可能是时间 A 崩溃的，也可能是时间 B 崩溃的。
当在时间点 A 崩溃，MySQL 认为事务没有成功提交（体现在 redolog 只有记录 prepare 状态，没有 commit 状态，也没有写 binlog），crash-save 崩溃恢复时对该事务做回滚处理。
当在时间点 B 崩溃，虽然没有写 redolog 的 commit 状态，但是因为写了 binlog，MySQL任务事务已经成功提交，crash-save 崩溃恢复时承认该事务。

crash-save：数据库发生异常重启，内存中的数据页没有刷到磁盘，可以通过 redolog 恢复

crash- save 时只用看是否写了 binlog 不就可以了吗？redolog 的作用是？
redolog 的主要作用是作为写磁盘的一种缓存。这里的两阶段提交机制和 binlog 相互配合， 保证了主从复制时的主备一致。在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页

redolog buffer
redolog 先写在内存上的 redolog buffer，之后再刷盘到 redolog file

16 ｜ "Order By"的工作机制

全字段排序

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

为了避免全表查询，在 city 字段加上索引。
MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

排序过程：

1. 初始化 sort_buffer，放入 city、name、age 三个字段（可以理解为临时表）
2. 从city 的索引树找到第一个满足 city=‘杭州’ 条件的主键 id
3. 表的主键索引树中找到主键 id对应的整行，取出 city、name、age 三个字段的值，存入 sort_buffer中
4. 从city 的索引树找下一个主键 id
5. 重复3、4，直到4中找的主键 id 不满足 city=‘杭州’ 的条件
6. 对 sort_buffer 中的数据对 name 字段做快排
7. 返回前1000行

步骤6中的排序，可能是内存排序，可能是外排序，这取决于排序所需的内存和参数 sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

如果排序的单行长度太大（最后要返回的字段太多）？
采用 rowid 排序
sort_buffer 中只放入主键 id、排序字段（这里是 order by 的 name）。
排序后获取前1000行的主键 id。
通过主键 id 回主键索引表找到1000个主键 id 的行的完整字段，并返回。

全字段排序 vs rowid 排序
根据 MySQL 的“优先使用内存，减少磁盘IO”的原则，由于 rowid 排序的磁盘读更多（回表），因此优先选择全字段排序。

如果索引是（city，name）？
此时 city 的索引树的同一 city 的 name 是从左到右升序的，只需从左到右取出前1000个（或city = “杭州”的不足1000个，提前结束）id，回表找完整的3个字段即可，无需排序。

进一步优化？
覆盖索引覆盖所有字段（select、where、order），甚至可以完全不需要回表。
如果索引是（city，name，age），通过最左前缀原则知 city 和 name 起到（city，name）的效果，索引树又记录了 age 字段，直接返回即可，无需回表，无需排序。

17 ｜ 返回随机行

1. orber by rand

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

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法
临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表
MySQL 5.6 版本引入的一个新的排序算法，即：优先队列排序算法（堆排序）。如“limit 3”就只用排序前3行，不用排序其他的9997行。
当”limit n“的行数n * 每行占用的空间超过了设置的 sort_buffer_size 大小，就只能使用归并排序算法。
总之，不论是使用哪种类型的临时表，order by rand() 这种写法都会让计算过程非常复杂，需要大量的扫描行数，因此排序过程的资源消耗也会很大。

2. 随机排序方法

我们先把问题简化一下，如果只随机选择 1 个 word 值，可以怎么做呢？思路上是这样的：

1. 取得这个表的主键 id 的最大值 M 和最小值 N;
2. 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
3. 取不小于 X 的第一个 ID 的行。我们把这个算法，暂时称作随机算法 1。

这里，我直接给你贴一下执行语句的序列:

select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;

这个方法效率很高，因为取 max(id) 和 min(id) 都是不需要扫描索引的，而第三步的 select 也可以用索引快速定位，可以认为就只扫描了 3 行。但实际上，这个算法本身并不严格满足题目的随机要求，因为ID 中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。

所以，为了得到严格随机的结果，你可以用下面这个流程:

1. 取得整个表的行数，并记为 C。
2. 取得 Y = floor(C * rand()). floor 函数在这里的作用，就是取整数部分。
3. 再用 limit Y,1（丢掉前 Y 条记录，取之后的1行）取得一行。

我们把这个算法，称为随机算法 2。下面这段代码，就是上面流程的执行语句的序列。

select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

由于 limit 后面的参数不能直接跟变量，所以我在上面的代码中使用了 prepare+execute 的方法。你也可以把拼接 SQL 语句的方法写在应用程序中，会更简单些。这个随机算法 2，解决了算法 1 里面明显的概率不均匀问题。

limit n 取 n 个数，就取 n 个 Y 就好了。

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行
select * from t limit @Y2，1；
select * from t limit @Y3，1；

18 | SQL语句逻辑相同、性能差异

案例一：条件字段函数操作
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。（比如日期字段，”where month(date) = 7“，是用不上日期索引的）

案例二：隐式类型转换

select * from tradelog where tradeid=110717;

tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。
在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。

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

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

select * from tradelog where id="83126";

以上的 SQL 可以利用索引，不会走全表扫描。因为字符串转换成数字，相当于

select * from tradelog where id=83126;

案例三：隐式字符编码转换
表之间字符集不同导致关联表的时候，没法用上索引

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

先获得 log 表中 id=2 的行的 tradeid，再去 detail 表中 找 tradeid 相等的行。索引 log 表是驱动表，detail 表是被驱动表
两个表的字符集不同，一个是 utf8，一个是 utf8mb4
SQL 语句相当于

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

所以字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，优化器会放弃走树搜索功能。

select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

上面的 SQL 语句中，detail 变成了驱动表，log 变成了被驱动表

select l.operator from tradelog l , trade_detail d where d.traideid =CONVERT(l.tradeid.value USING utf8mb4) and d.id=4;

这里的 CONVERT 函数是加在输入参数上的，这样就可以用上被驱动表的 traideid 索引.

总结：
优化语句

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

有两种方法：

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

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

2. 如果能够修改字段的字符集的话，是最好不过了。但如果数据量比较大， 或者业务上暂时不能做这个 DDL 的话，那就只能采用修改 SQL 语句的方法了。

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

这里，我主动把 l.tradeid 转成 utf8，就避免了被驱动表上的字符编码转换。

小结
三个案例其实在讲一件事：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
第二个例子是隐式类型转换，第三个例子是隐式字符编码转换，它们都跟第一个例子一样，因为要求在索引字段上做函数操作而导致了全索引扫描。

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

有些情况下，“查一行”，也会执行得特别慢。
需要说明的是，如果 MySQL 数据库本身就有很大的压力，导致数据库服务器 CPU 占用率很高或 ioutil（IO 利用率）很高，这种情况下所有语句的执行都有可能变慢，不属于我们今天的讨论范围。
第一类：查询长时间不返回：
一般碰到这种情况的话，大概率是表被锁住了。
等 MDL 锁
有其他线程正在表 t 上请求或者持有 MDL 写锁，把当前线程的 select 语句堵住了。
通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。（MySQL 启动时需要设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失)）
等 flush
正在全表/全库 flush，或者 flush 被别的语句阻塞了，而当前的查询又被 flush 阻塞了。
排查：show processlist;
出现"Waiting for table flush"

等行锁
上边两种相当于都是"表级锁"，现在，select 语句终于来到引擎里了。

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

读的时候上共享锁（读锁），读的是最新值（当前读）
由于访问 id=1 这个记录时要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，我们的 select 语句就会被堵住。

第二类：查询慢：
扫描行数多，查询时间慢：
比如查询没走索引，而是全表查询，扫描行数为表的行数，当表行数很多的时候，查询时间慢。
undolog 很长
花了很长时间遍历找当前事务能看的那个数据版本

20 ｜ 幻读

RR（可重复读，MySQL默认的隔离级别）下，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行（或后一次看不到前一次看到的行）。

幻读专指 update 增加新行，或 delete 删除行的。
RR 下，普通的查询（直接 select from where;）是“快照读”，后面加上 for update/lock in share mode是当前读。幻读在“当前读”下才会出现，在“快照读”下不会出现。
（for update，lock in share mode，update，delete，insert 都是关于当前读）

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

Innodb 解决幻读：间隙锁。select 时扫描过的若干行，行与行之间加上间隙锁，确保这些间隙无法再插入新纪录。
待完成

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

（间隙锁的加锁规则）

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

以业务高峰期的性能问题为背景，介绍了一些紧急处理的手段

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

WAL 机制：只要 redo log 和 binlog 保证持久化到磁盘，就能保证 MySQL 异常重启后，数据可以恢复。这一节介绍了 MySQL 写入 binlog 和 redo log 的流程。

binlog 写入机制
事务执行过程中，先写 binlog cache，事务提交的时候，再将 binlog cache 写到 binlog 文件中。
一个事务的 binlog 是不能被拆开的，无论这个事务多大，也要确保一次性写入。一个事务的 binlog 太多怎么办？
系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
事务提交时，将当前线程的 binlog cache 中的完整事务写入到 binlog 中，并清空 binlog cache。

可以看到，每个线程有自己的 binlog cache，但是共用一份 binlog files。
图中的 write，是把日志写入文件系统的 page cache，并没有将数据持久化到磁盘，所以速度比较快。
图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。
write 和 fsync 的时机，是由参数 sync_binlog 控制的：
sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。
在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。
但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

redolog 写入机制
事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的
redo log buffer 就是一块内存，线程共享。

事务还没提交的时候，redo log buffer 中的部分日志有没有可能被持久化到磁盘呢？答案是， 确实会有。
redo log 三种状态

1.存在 redo log buffer 中，物理上是在 MySQL 进程内存中。图中的红色部分。
2.写在磁盘（write），但是还没持久化（fsync），物理上是在文件系统的 page cache 里面。图中的黄色部分。
3.持久化到磁盘。图中的绿色部分。
控制 redo log 的写入策略的 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：
设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。
InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
注意，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。
实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。
一种是，redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘.
如果把 innodb_flush_log_at_trx_commit 设置成 1，那么 redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的。
每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了，只会 write 到文件系统的 page cache 中就够了。
ps：只需要知道 redo log 是否是 prepare 状态和有没有 binlog，就能判断事务是否提交了。（1.redo log prepare && bin log commit 事务提交，没影响。
2.redo log prepare && bin log uncommit 事务回滚，没影响）
redo log 的 commit 状态只需要 write 写磁盘，没必要 fsync 持久化。

通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

这时候，你可能有一个疑问，这意味着我从 MySQL 看到的 TPS 是每秒两万的话，每秒就会写四万次磁盘。但是，我用工具测试出来，磁盘能力也就两万左右，怎么能实现两万的 TPS？
答：组提交（group commit）机制
日志逻辑序列号（log sequence number，LSN）的概念。LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。
LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log。
并发事务时，redo log buffer 会一次性持久化多个事务的 redo log 记录，节约 IOPS 。
binlog 也有组提交，效果没 redo log 这么好。

因此，WAL 机制主要得益于两个方面：
redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？
针对这个问题，可以考虑以下三种方法：
设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
将 innodb_flush_log_at_trx_commit 设置为 2（每次事务提交时都只是把 redo log 写到 page cache）。这样做的风险是，主机掉电的时候会丢数据。

我不建议你把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小。
系统断电了就不一样，磁盘缓冲也丢失了 应用程序自身异常重启，磁盘缓冲还是会刷新道物理磁盘的。

24 ｜ MySQL如何保证主备一致？

31 ｜ 误删数据后怎么办？

误删数据分以下几种情况：
1.使用 delete 语句误删数据行
2.使用 drop table 或 truncate table（仅清空表数据，表仍然存在） 语句误删数据表
3.使用 drop database 语句误删数据库
4.使用 rm 命令误删整个 MySQL 实例

误删行
可以用 FlashBack 工具通过闪回将数据恢复。
FlashBack 闪回的原理，修改事务对应的 binlog 的内容（insert 改为 delete，delete 改为 insert，update 的新旧值交换），拿回原库重放
如果误操作涉及多个事务，顺序 ABC，重放时顺序 CBA。

误删库/表
取全量备份+增量日志（binlog）

33 ｜ 大表做全表扫描的流程

大表（比如表的大小超过内存的大小）做全表扫描的流程：
1.获取一行，写到 net_buffer（大小是由参数 net_buffer_length 定义的，默认是 16k）
2.循环1，直到 net_buffer 写满，调用网络接口发出去
3.如果2中发送成功，就清空 net_buffer，继续12
4.如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
也就是说，MySQL 是边读边发的，如果客户端接收得慢，会导致服务端由于结果发不出去，这个事务的执行时间变长。
对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，我都建议你使用 mysql_store_result 这个接口，直接把查询结果保存到本地内存（将所有结果一次性返回）
结论：查询的结果是分段发给客户端的，因此扫描全表，查询返回大量的数据，并不会把内存打爆。

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

Index Nested-Loop Join(NLJ)

-- a 上有索引
select * from t1 straight_join t2 on (t1.a=t2.a);

straight_join 等同于 inner join，但是指定了左表为驱动表，右表为被驱动表。
这条语句执行流程：
1.取左表的一行
2.将该行的 a 值，去右表中找 a 字段匹配的行，与左表的行组成一行。
3.循环12，知道左表的最后一行
“遍历左表”、“用上了右表的索引”。这与“嵌套循环”相似，于是称之为“Index Nested-Loop Join”，简称 NLJ
NLJ 的特点：
驱动表走全表扫描，被驱动表走树搜索。
所以小表当驱动表，大表当被驱动表。

使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；

以上结论的前提：被驱动表用得上索引。

如果被驱动表用不上索引：Block Nested-Loop Join（BNL）

-- b 上无索引
select * from t1 straight_join t2 on (t1.b=t2.b);

该算法的流程：
1.把 t1 的数据读入线程内存 join_buffer 中，由于此处是“select *”，于是将整个 t1 放入了内存
2.扫描 t2，将 t2 的每一行取出来，跟 join_buffer 中的做对比，满足 join 条件的作为结果集的一部分。
这个方法的扫描行数是 t1 的行数 + t2 的行数，判断次数是行数相乘。
这个判断次数和暴力全表 join 的扫描行数相等，但是由于判断是内存操作，效率会快很多。
这种方法，大表小表谁做驱动表的执行耗时是一样的。但是由于 join_buffer 如果放不下，就要分段（做完一段，清空 join_buffer，做下一段），每增加一段，t2 就增加一次全表扫描，因此小表作为驱动表比较好（当然，减少段数的另一个角度是增加每个段能容纳的大小，即 join_buffer 的大小 join_buffer_size）。

“小表”的定义。参与 join 的总数据量（行数*字段）

35 ｜ join语句的优化

回表：在普通索引树找到主键值，回到主键索引通过主键 Id 找到整条记录。

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

此时，在普通索引树上每找到一个满足 a 条件的主键值，就会回一次表，回表的过程是一次次的磁盘随机访问。

MRR(Multi-Range Read) 优化
如果将所有满足 a 条件的主键值，进行排序，将排序后的主键值一次次回表，就会是一个类似于顺序读盘的操作。（即使不是真正的连续的顺序读盘，也比随机访问效率高）。这就是 MRR 优化。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR。可通过相关参数设置）

BKA(Batched Key Access)
BKA 是对 NLJ 的优化
NLJ 是将驱动表的每个值，去索引树中搜索，也就是每次都是匹配一个值。这时，MRR 的优势就用不上了。
于是可以将 BNL 中的 join_buffer 应用在 NLJ，一次性从 t1 中多拿一些行出来，一起传给 t2。
具体方法：在原表上加索引，或者用有索引的临时表，我们的思路都是让 join 语句能够用上被驱动表上的索引，来触发 BKA 算法，提升查询性能。

38 ｜ Memory引擎