第1讲-基础架构

现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。
也就是说，你执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是 InnoDB。
不同的存储引擎共用一个 Server 层。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置
函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以
key-value 对的形式，被直接缓存在内存中。
key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被
直接返回给客户端。但是，查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
因此建议你不要使用查询缓存！！MySQL 8.0 版本直接将查询缓存的整块功能删掉了。

在词法分析和语法分析之后，会进入优化器。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句
有多表关联（join）的时候，决定各个表的连接顺序。


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

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

第2讲-日志系统

这一讲要结合第15讲和23讲一起看。

文章中多处提到写日志时有两个阶段 write 和 fsync。主要是当我们调用操作系统提供的write接口时，进程的
写只是把数据放到page cache并标记dirty，而脏页面不会立即更新到硬盘中，而是由操作系统统一调度，如由
专门的flusher内核线程在满足一定条件时（如一定时间间隔、内存中的脏页达到一定比例）内将脏页面同步到
硬盘上（放入设备的IO请求队列）。
因为write调用不会等到硬盘IO完成之后才返回，因此如果OS在write调用之后、硬盘同步之前崩溃，则数据可能
丢失。虽然这样的时间窗口很小，但是对于需要保证事务的持久化（durability）和一致性（consistency）的
数据库程序来说，write()所提供的“松散的异步语义”是不够的，通常需要OS提供的同步IO（synchronized-IO）原语来保证：
int fsync(int fd);
fsync的功能是确保文件fd所有已修改的内容已经正确同步到硬盘上，该调用会阻塞等待直到设备报告IO完成。

InnoDB 有两块非常重要的日志，一个是undo log，另外一个是redo log，前者用来保证事务的原子性以及InnoDB的MVCC，后者用来保证事务的持久性。
undo log 维持原子性是指出错了rollback的时候是通过undo log恢复的，MVCC是指，从一个一致性视图跳转到之前的另一个版本的一致性视图也是通过undo log恢复的。第8讲对这一块讲的非常透彻！

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


注意：Redo log不是记录数据页“更新之后的状态”，而是记录这个页 “做了什么改动”。


问题1：正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？
回答：这里涉及到了，“redo log 里面到底是什么”的问题。实际上，redo log 并没有记录数据页的完整数据，所以它
并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。如果是正常运行的实
例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，
甚至与 redo log 毫无关系。
在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让
redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。


问题2：redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？
回答：这两个问题可以一起回答。在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：
begin;insert into t1 ...insert into t2 ...commit;
这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接
写到 redo log 文件里。所以，redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert
的时候，数据的内存被修改了，redo log buffer 也写入了日志。但是，真正把日志写到 redo log 文件
（文件名是 ib_logfile+ 数字），是在执行 commit 语句的时候做的。（这里说的是事务执行过程中不会“主动去刷盘”，
以减少不必要的 IO 消耗。但是可能会出现“被动写入磁盘”，比如内存不够、其他事务提交等情况。单独执行一个更新语句的
时候，InnoDB 会自己启动一个事务，在语句执行完成的时候提交。过程跟上面是一样的，只不过是“压缩”到了一个语句里面完成。

问题3：redo log 和 binlog 是怎么关联起来的?
回答：它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：
* 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
* 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

第8讲-事务隔离的实现原理【经典】

在 MySQL 里，有两个“视图”的概念：

一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。
我们并不需要拷贝出这 100G 的数据。
我们先来看看这个快照是怎么实现的。InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。
它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 
赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信
息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

详细的参考这一讲的具体讲解，非常清晰，值得多次回味！

第9讲-普通索引和唯一索引的选择【经典】

比如有个身份证号字段，业务需求是根据身份证号查找其他信息。首先身份证号不适合做主键，会影响
数据的存取效率。考虑要么创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。但一般情况下，普通索引效率更高。
查询的时候，唯一索引效率可能会略微高那么一点点，这还是建立在查询的数据可能跨页的情况，但概率很低。
写入的时候，普通索引的优势就非常大了，主要是会用到change buffer，避免了随机读！具体参考原文，非常
值得多次回味！

第10讲-有时候会选错索引

简单的说，选错索引的情况就是优化器综合分析了一条sql语句，以及结合相应的索引细化时，发现使用错误的索引可能总体成本更低。
比如总表为10W行，使用索引A，优化器预估需要扫描行数3W行，然后每一行还需要回表一次，它可能就认为效率可能比全表扫描更慢。
当然，优化器预估的扫描行数很可能非常不准，所以也就带来了很大的误差。因为预估行数是采样N页数据页，再乘以总的页数，随着数据的增删，采样的误差会越拉越大的。
有时候可以通过 force index 来强制使用某个索引。

第11讲，为字符串创建索引【经典】

比如说表中有一个字段是邮箱，长度会比较长，但又经常会有根据邮箱查询信息的业务需求，可以选的方法有：

直接创建完整索引，这样可能比较占用空间；
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；这里就会牵涉到区分度的问题和覆盖索引的问题，原文分析的非常好！！！
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
对于身份证号这种前缀在一个城市是一致的情况可以选择倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；总体来说，讲的非常实用！

第12讲-mysql偶尔会抖一下【经典】

平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。
那么，什么情况会引发数据库的 flush 过程呢？

InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。
系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
MySQL 认为系统“空闲”的时候。比如每隔一小段时间刷一次脏页。
MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

这几种情况中，前两种情况尤其注意：

“redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统
就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。

第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB 用缓冲池（buffer pool）管理内存，
缓冲池中的内存页有三种状态：
第一种是，还没有使用的；
第二种是，使用了并且是干净页；
第三种是，使用了并且是脏页。

InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。

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

所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：
* 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
* 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。所以，InnoDB 需要有控制
脏页比例的机制，来尽量避免上面的这两种情况。

具体的InnoDB 刷脏页的控制策略 参考正文。

第13讲-表数据删除但表文件大小不变

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

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。
也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

不止是删除数据会造成空洞，插入数据也会。如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。
也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。
而重建表，就可以达到这样的目的。

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

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

从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）默认的就是上面图 4 的流程了【见原文】；
analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
optimize table t 等于 recreate+analyze。

问题：什么时候使用 alter table t engine=InnoDB 会让一个表占用的空间反而变大。
解答：在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。
假如是这么一个过程：
1. 将表 t 重建一次；
2. 插入一部分数据，但是插入的这些数据，用掉了一部分的预留空间；
3. 这种情况下，再重建一次表 t，就可能会出现问题中的现象。

第16讲-order by的原理

假设我们要执行这样一个需求：

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

为避免全表扫描，在 city 字段加上索引。通常情况下，这个语句执行流程如下所示：

初始化 sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 做快速排序；
按照排序结果取前 1000 行返回给客户端。

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

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。
但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，
这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

如果 MySQL 认为排序的单行长度太大会怎么做呢？新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。
但这时，排序的结果就因为少了 city 和 age 字段的值，不能直接返回了，整个执行流程就变成如下所示的样子：

初始化 sort_buffer，确定放入两个字段，即 name 和 id；
从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到不满足 city='杭州’条件为止；
对 sort_buffer 中的数据按照字段 name 进行排序；
遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

这个执行流程称为 rowid 排序。

总结：

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，
但是需要再回到原表去取数据。如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 
中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。这也就体现了 MySQL 的一个设计思想：
如果内存够，就要多利用内存，尽量减少磁盘访问。

但并不是所有的 order by 语句，都需要排序操作的。具体的可以是联合索引或者覆盖索引，详细参考原文！！

第18讲-相同逻辑的sql语句性能却差别很大

总结起来就是：

对索引字段做函数操作，可能会破坏索引值的有序性，导致走全表扫描。
隐式类型转换可能导致索引失效，比如

select * from tradelog where tradeid=110717; // tradeid 为 varchar类型

在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。对于优化器来说，这个语句相当于：

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

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

像这种sql就没问题：

select * from tradelog where id="83126";

这是因为隐式转换并不会应用到索引字段上，而是应用到索引字段对应的参数上，不影响索引本身的有序。
select 'a' = 0 ; 的结果是1，说明无法转换成数字的字符串都被转换成0来处理了。

第19讲-只查一行数据却执行的很慢

有其他线程正在表 t 上请求或者持有 MDL （MetaData Lock）写锁，把 select 语句堵住了。
等flush，有其他线程正要对表 t 做 flush 操作。flush操作执行起来是很快的，除非它们也被别的线程堵住了。所以，出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。
以上是表锁范围，现在进入行锁范围。如果一个事务在这行记录上持有一个写锁，我们的 select 语句就会被堵住。

第20讲-解释什么是幻读，以及通过间隙锁解决幻读【经典】

结合第21讲对间隙锁和next-key lock的案例分析来学习效果更好，这两张理解起来有一点点复杂，要多读多思考。

幻读定义：

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。传送门：当前读，快照读

这篇讲义的内容需要结合实例来分析理解，因此这里不做笔录，直接参考原文

第24讲-mysql是如何保证主备一致的

具体主备之间的连接，binlong的传输参见原文，这里看下三种binlog格式：

statement格式，会将执行的sql语句原封不动的保存到binlog中，但是会有主从不一致的风险，比如一条sql语句用了两个索引，有可能主用了索引a，而从用了索引b。
row格式，row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows。Table_map event，用于说明接下来要操作的表是 test 库的表 t;Delete_rows event，用于定义删除的行为。当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id=4 的行，不会有主备删除不同行的问题。
mixed 格式。因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。因此，如果你的线上 MySQL 设置的 binlog 格式是 statement 的话，那基本上就可以认为这是一个不合理的设置。你至少应该把 binlog 的格式设置为 mixed。

现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。这么做的理由有很多，我来给你举一个可以直接看出来的好处：恢复数据。具体怎么恢复数据，参见原文，很详细。

第28讲-如果保证读写分离从库与主库一致

主从复制一块，这一章节重点看

第39和45讲自增ID为啥是不连续的，自增ID用完怎么办

在 MySQL 里面，如果字段 id 被定义为 AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：
如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是 X，当前的自增值是 Y。

如果 X<Y，那么这个表的自增值不变；
如果 X≥Y，就需要把当前自增值修改为新的自增值。

新的自增值生成算法是：

从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。其中，auto_increment_offset 和 auto_increment_increment 是两个系统参数，分别用来表示自增的初始值和步长，默认值都是 1。

备注：在一些场景下，使用的就不全是默认值。比如，双 M 的主备结构里要求双写的时候，我们就可能会设置成 auto_increment_increment=2，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突。当 auto_increment_offset 和 auto_increment_increment 都是 1 的时候，新的自增值生成逻辑很简单，就是：如果准备插入的值 >= 当前自增值，新的自增值就是“准备插入的值 +1”；否则，自增值不变。

这就引入了我们文章开头提到的问题，在这两个参数都设置为 1 的时候，自增主键 id 却不能保证是连续的，这是什么原因呢？

唯一键冲突是导致自增主键 id 不连续的第一种原因。
同样地，事务回滚也会产生类似的现象，这就是第二种原因。这两种方案都是为了最大化提升并发度和性能。原因参考原文，通过举例来说明。

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

表自身定义的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
系统提供的row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。
Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。
InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来。

MySQL实战45讲笔记