链接地址
刚看完此专辑，看到末节对前面所学的东西记忆模糊了；故二刷回顾、关键处做个笔记。

01|基础架构：一条sql查询语句是怎么执行的

01-30 - 图1
MySQL 可以分为 Server 层和存储引擎层**两部分。Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。
查询缓存，之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。查询缓存的失效非常频繁，命中率会非常低，MySQL 8.0 版本直接将查询缓存的整块功能删掉了；

02|日志系统：一条sql更新语句是如何执行的

只要我们写的是DML语句（insert,update,delete），那么我们在数据库服务端执行的时候就会涉及到 redo log(重做日志) 和 binlog(归档日志) 两个日志文件的变动。
其实DML（insert,update,delete）最简单的就是直接操作数据库磁盘，但由于涉及到随机IO性能差；所以我们会考虑写内存，空闲批量刷新的方式，但是这个方法又有问题，突然宕机了数据丢失，不可靠；那么一个折中办法，采用WAL技术，顺序写磁盘记录日志，内存也做维护，当遇到内存数据丢失的时候，依靠磁盘中的记录日志实现数据库数据的一致性保证数据不丢失，或者恢复内存数据（和HDFS中NN元数据管理方案相似，但NN的元数据管理依据FsImage和editor.log配合SNN相比会更复杂）；
上面说的WAL记录日志在MYSQL中就是redo log。redo log是一个固定大小的环形文件，顺序循环写，write pos是当前写的位置，check point是擦除的位置，当write pos追上 check point是进行一次”刷盘”（更新数据库）。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。特别强调下，redo log是InnoDB引擎独有的日志；
**

什么是crash-safe？

  1.  - 所有已经提交的事务的数据仍然存在。
  2.  - 所有没有提交的事务的数据自动回滚。

**

REDO的写盘时间会直接影响系统吞吐，显而易见，REDO的数据量要尽量少。其次，系统崩溃总是发生在始料未及的时候，当重启重放REDO时，系统并不知道哪些REDO对应的Page已经落盘，因此REDO的重放必须可重入，即REDO操作要保证幂等。最后，为了便于通过并发重放的方式加快重启恢复速度，REDO应该是基于Page的，即一个REDO只涉及一个Page的修改。 熟悉的读者会发现，数据量小是Logical Logging的优点，而幂等以及基于Page正是Physical Logging的优点，因此InnoDB采取了一种称为Physiological Logging的方式，来兼得二者的优势。所谓Physiological Logging，就是以Page为单位，但在Page内以逻辑的方式记录。举个例子，MLOG_REC_UPDATE_IN_PLACE类型的REDO中记录了对Page中一个Record的修改，方法如下： （Page ID，Record Offset，(Filed 1, Value 1) … (Filed i, Value i) … ) 其中，PageID指定要操作的Page页，Record Offset记录了Record在Page内的偏移位置，后面的Field数组，记录了需要修改的Field以及修改后的Value。

Q:停下来擦掉记录，不会造成请求阻塞吗？ 处理擦掉部分的时候还没有写入，如果这时crash难道不会出问题吗？ 如果光写log没写到库里，那不会导致数据实时性有问题吗？ 为什么要写到写不下才擦掉，队列处理错峰填谷不是更合理吗？

01-30 - 图2

update执行流程


mysql> update T set c=c+1 where ID=2;
01-30 - 图3
1.执行器查找innoDB引擎查ID=2这行数据，如果ID=2这一行所在的数据页本在内存中，直接返回给执行器；否则需要先从磁盘读入内存再返回
2.执行器拿到引擎给的数据，把c的值加1，得到新的一行数据，再调用引擎接口写入这行新数据
3.引擎将这行新数据更新到内存中，同时将更新操作写入redo log（物理日志），此时redo log处于prepare状态。然后返回告知执行器执行完成，随时可以提交事务
4.执行器生成这个操作的binlog，并把binlog写入到磁盘（逻辑日志）
5.执行器调度引擎接口的提交事务接口，引擎把刚才写入的redo log改成提交（commit）状态，更新完成

扩展知识点1——数据页 操作系统一般一页4KB，但是InnoDB使用B+树存储数据时，一个树节点即一次磁盘IO到内存是16KB，4倍关系，即Mysql和磁盘的交互基本单位是页，16KB

扩展知识点2 ——二阶段提交 redo log落盘（prepare阶段） —— binlog 落盘 —— 修改redo log为提交状态（commit） 我再来说下自己的理解 。 1 prepare阶段 2 写binlog 3 commit 当在2之前崩溃时 重启恢复：后发现没有commit，回滚。备份恢复：没有binlog ，一致；当在3之前崩溃重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。 怎么检查binlog: 一个完整事物binlog结尾有固定的格式.

二阶段提交存在的意义是什么？
说下我个人理解：首先数据库集群数据同步是通过binlog来实现的，如果只有binlog，要么先更新数据（至内存）再写binlog或者先更新binlog再写成功，这两种中间发生宕机都会出现数据不一致的问题；另外为了保证crash-safe，redo log是必要的，那我们不要二阶段提交，比如先更新数据（至内存）再写redo log再写binlog，redo log和binlog分别在InnoDB和Server层，作为分布式系统都会涉及到一致性的问题，这样的方式不一致性风险远高于带prepare的二阶段方式。另外我猜测在数据恢复的时候，如果不是二阶段提交，那么每条redo log日志都必须去binlog中查找，是否真正成功了可以执行数据恢复提交commit。但如果是二阶段提交，redo log带状态的话，那么我们只需要对比redo log记录为prepare的即可，commit状态的记录直接提交进行数据恢复；
文章作者的理解：为了保证两份日志的逻辑一致。在redo log写入=数据恢复的前提条件成立下，先写redo log后写binlog，在数据库恢复的时候（通过节点备份+刷binlog日志恢复），会缺失crash时候，更新的数据；先写binlog再写redo log，在数据恢复的时候会恢复出一条crash时候没有执行成功的数据；

redolog和binlog具有关联行，在恢复数据时，redolog用于恢复主机故障时的未更新的物理数据，binlog用于备份操作。每个阶段的log操作都是记录在磁盘的，在恢复数据时，redolog 状态为commit则说明binlog也成功，直接恢复数据；如果redolog是prepare，则需要查询对应的binlog事务是否成功，决定是回滚还是执行。 再直白点，就是Mysql数据不丢失的能力主要体现在两个方面

  • 能够恢复到任何时间点的状态；—-> binlog（归档日志）
  • 能够保证MySQL在任何时间段突然奔溃，重启后之前提交的记录都不会丢失； —-> redo log（重做日志）和undo log（回滚日志）

binlog VS redo log

MySQL 保证数据不会丢的能力主要体现在两方面：

  1. 能够恢复到任何时间点的状态； ** **binlog可以保证
  2. 能够保证MySQL在任何时间段突然奔溃，重启后之前提交的记录都不会丢失；** **redo log和undo log保证

为什么binlog无法保证crash-safe？
其实上文已经说了，先写数据再写binlog或者先写binlog再写数据都无法保证数据一致性（和crash之前的数据逻辑不一致）；

binlog可以简化掉吗？这里需要分场景来看：

  • 如果是主从模式下，binlog是必须的，因为从库的数据同步依赖的就是binlog；
  • 如果是单机模式，并且不考虑数据库基于时间点的还原，binlog就不是必须，因为有redo log就可以保证crash-safe能力了；但如果万一需要回滚到某个时间点的状态，这时候就无能为力，所以建议binlog还是一直开启；

03 | 事务隔离：为什么你改了我还看不见？

  • 脏读（针对未提交数据）:读到其他事务未提交的数据；
  • 不可重复读（针对其他提交前后，读取数据本身的对比）：前后读取的记录内容不一致；
  • 幻读（针对其他提交前后，读取数据条数的对比） ：前后读取的记录数量不一致。

RC级别下，MVCC视图会在每一个语句前创建一个，所以在RC级别下，一个事务是可以看到另外一个事务已经提交的内容，因为它在每一次查询之前都会重新给予最新的数据创建一个新的MVCC视图。 RR级别下，MVCC视图实在开始事务的时候就创建好了，这个视图会一直使用，直到该事务结束。 这里要注意不同的隔离级别他们的一致性事务视图创建的时间点是不同的。

为什么不建议使用长事务？1. 长期占用锁资源，增加了锁冲突的几率 2. 会导致很多老的事务视图无法删除，回滚日志（undo log）占用大量存储空间；

04 | 深入浅出索引（上）

索引的常见模型

  1. 哈希表，数组+链表的行式，这种结构适合用于等值查询的场景
  2. 有序数组，单从查询效率上来说，有序数组是最好的数据结构，但是在数据更新的时候，就很麻烦，中间插入一条数据需要挪动后面所有记录，成本过高，所以有序数组只适合用于静态存储引擎
  3. 搜索树，平衡二叉树查询和更新的时间复杂度都是O(log(n))，但实际上数据库存储并不使用二叉树（InnoDB引擎使用的是B+树），原因是索引不止于在内存中，还要写到磁盘上，如果树过高查询要访问过多节点，造成过多的磁盘随机读，比如一颗100w节点的平衡二叉树，树高20，一次查询需要访问20个数据块，磁盘随机读一个数据快（节点）需要10ms的寻址时间，那么100w行的表使用二叉树存储单独访问一行需要20个10ms的时间；为了尽量减少读磁盘，必须尽量少的访问数据快，所以我们要使用N叉树，这里的N取决于数据快的大小；
  4. 数据库技术发展到今天，跳表、LSM 树等数据结构也被用于引擎设计中

InnoDB索引模型

在 InnoDB 中，每一张表其实就是多个 B+ 树，即一个主键索引树和多个非主键索引树。 执行查询的效率，一般使用主键索引 > 使用非主键索引 > 不使用索引。 如果不使用索引进行查询，则从主索引 B+ 树的叶子节点进行遍历。
存储例子，表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下
01-30 - 图4

索引维护——“页分裂”，如果插入id=400的数据，需要逻辑上挪动后面的数据空出一个位置，更糟糕的情况如果挪动的数据页已经满了，这时候需要申请一个新数据页，然后挪动部分数据过去，页分裂首先就是造成性能影响，其次是数据块的利用率下降；反之，数据删除会有页合并。由于页分裂的问题，所以我们往往推荐使用自增主键，避免数据插入的无须性造成页分裂。

05 | 深入浅出索引（下）

  1. 回表&覆盖索引
  2. 最左前缀原则，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符（即左字段组合或者左字段的一部分），所以建立联合索引时，要考虑到索引的复用能力。所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。
  3. 索引下推， MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

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

  1. 全局锁，锁整个库，让整个库只读，典型使用场景是做全库的逻辑备份。当然如果引擎支持可重复读隔离级别，我们可以拿到一致性视图（mysqldump使用-single-transaction的时候，导数据就会启动事务确保拿到一致性视图，而由于MVCC的支持，整个过程中数据是正常更新的）
  2. 表级锁
    1. 表锁
    2. 元数据锁（MDL），server层的锁，MDL读锁之间不互斥，读写锁和写锁之间互斥。在修改表结构的时候会加写锁。DDL加的是写锁，DML是读锁
  3. 行锁，引擎层锁，但并不是所有引擎都支持行锁

07 | 行锁功过：怎么减少行锁对性能的影响？

二阶段锁协议：在InnoDB事务中，行锁是在需要的时候才加上，但不能更不是不需要了就立刻释放，二十要等到事务结束时才释放；
死锁：资源循环依赖
死锁解决策略：1. 超时退出，innodb_lock_wait_timeout（默认50s）2. 死锁检测，发现死锁后主动回滚死锁链条中的某一个事务。innodb_deadlock_detect=on
死锁检测的时间复杂度：并发更新同一行的1000行，比如目前线程Tx更新行R1，作为死锁检测Tx会检测锁持有情况，1.查看自身持有的行锁 2.（行锁排队）遍历排在Tx前面的所有线程死锁检测（对某个线程进行死锁检测：新来的线程F，被锁了后就要检查锁住F的线程（假设为D）是否被锁，如果没有被锁，则没有死锁，如果被锁了，还要查看锁住线程D的是谁，如果是F，那么肯定死锁了，如果不是F（假设为B），那么就要继续判断锁住线程B的是谁，一直走知道发现线程没有被锁（无死锁）或者被F锁住（死锁）才会终止），死锁得时间复杂度依据循环依赖的平均递归层数m；1000行遍历次数为(1000+1)1000m/2，即此处时间复杂度应为O(n2)，此处和作者说的O(N)以及100w次有出入；

死锁检测注意点：

  1. 一致性读不会加锁，就不需要做死锁检测；
  2. 并不是每次死锁检测都都要扫所有事务；


innodb行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。（ 逐行加锁，事务提交的时候统一释放）———— 没有索引直接走主键索引树，逐行扫描加锁，如果确定了只会更新一条，建议加limit 1

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

  1. mysql> CREATE TABLE `t` (
  2.   `id` int(11) NOT NULL,
  3.   `k` int(11) DEFAULT NULL,
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB;
  6. insert into t(id, k) values(1,1),(2,2);

01-30 - 图5 （事务 B 查到的 k 的值是 3，而事务 A 查到的 k 的值是 1）
知识点：undo log、一致性视图、当前读、行锁

事务B由于更新得时候会执行当前读（如果是一致性读得话那么会把事务C的更新给丢失掉）(更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）)
lock in share mode 或 for update 也可以触发当前读

MVCC是怎么实现的？

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它(多版本数据并不是物理存在的，而是根据当前版本和undo log计算出来的)。
在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
01-30 - 图6

  • 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
  • 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
  • 如果落在黄色部分，那就包括两种情况
    • 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
    • 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。


09| 普通索引和唯一索引，应该怎么选择

查询过程，普通索引会在索引树多查一次，确认符合条件的下一条数据不满足为止，唯一索引由于是唯一的所以只查一次。但两者性能差距几乎可以忽略，由于InnoDB的数据是按数据页为单位来读写的，以页为单位（每页默认大小16KB），除非查询的下一条刚好在下一页（概率很低，一页可存上千个key）；
更新过程，唯一索引无法使用change buffer，从性能角度出发建议使用普通索引；

change buffer，以 mysql> insert into t(id,k) values(id1,k1),(id2,k2); 为例，假设id1的数据在内存页中，直接更新。假设id2的数据所在的page2不在内存中，则写入change buffer内存区域，记录”往page2插入一行id2信息”，将上述id1和id2的的两个操作写入redo log中。 为什么说唯一索引无法使用change buufer，change buffer的优势是当page不在内存中时，无需从磁盘加载数据到内存，节省了这一步骤的IO（同时如果更新的数据短时间后续不用的话，也能避免命中率下降）；而唯一索引在更新的时候，要确认数据唯一性，则必定会加载数据页到内存中，进行唯一性判断，既然数据页已经在内存中了，那么无须再借助change buffer了，反而多此一举； tip：另外change buffer并不一定能100%提升性能，如果更新的数据立刻就需要用，那么从磁盘加载数据页到内存，就会触发change buffer的merge（merge的时候change buffer内数据越少，提升的性能效果越低）。另外和从数据页加载到内存，直接更新内存页中数据相比，还多维护了一个change buffer，反而性能更低；

10 | Mysql为什么有时候会选错索引

原因1：rows采样估算行数偏差较大导致。以mysql> select * from t where a between 10000 and 20000;为例，假设有10w条数据，a索引上数据为1-10w，准确的rows应该是10000，但rows抽样计算出来可能是3w、5w甚至更多，导致mysql认为数据量太大，回表的代价大于全表扫描而不走a索引。

rows采样估算方法：InnoDB默认会选择N个数据页，统级这些数据页上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到这个索引的基数

原因2：sql中带order by 等可能”引导”mysql选错索引。以
mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1; 为例，从分析上来看，一定是a作为索引更好，但是order by b这句会导致mysql可能选择b作为索引。

解决方案1：使用force index()，显示矫正
解决方案2：修改sql，引导mysql使用我们期望的索引

11 | 怎么给字符串加索引

  1. 直接创建完整索引可能比较占空间
  2. 创建前缀索引要注意区分度，虽然能节省空间，但会增加扫描次数以及无法使用覆盖索引
  3. 对于身份证这类字段可以采用倒叙前缀或者加一列hash值存储索引

12 | 为什么我的Mysql会抖一下

现象：一条 SQL 语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短。
很可能是在刷”脏页”（内存数据和磁盘数据不一致称位”脏页”）。
什么情况下会刷脏页？

  1. redo log 写满了，这时候系统会停止所有更新操作
  2. 系统内存不足，需要新得内存页时候，发现内存不够用就要淘汰一些数据页，如果淘汰的是”脏页”，就要先将”脏页”写到磁盘。
  3. 空闲刷新
  4. 正常关闭前

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

数据删除和数据页的删除都是复用的，磁盘文件大小并不会缩小。
R1R2R4删除R2，只会把R2标记删除，当新增R3的时候，可能会直接复用这个位置。

删除数据和随机新增都会造成数据空洞，解决办法就是重建表，MySQL5.6开始引入Online DDL。

每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁

Online DDL重建表流程：

  1. 建立一个临时文件，扫描表 A 主键的所有数据页；
  2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
  3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
  4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
  5. 用临时文件替换表 A 的数据文件。

01-30 - 图7

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

按照效率排序的话，count(字段)可重复读隔离级别下，由于MVCC，每一行记录都要判断是否对该会话可见，InnoDB只好一行一行地读出一次判断。

count(id)：InnoDB引擎遍历表，把每一行的id取出来，返回给server层，server层拿到id，判非空，然后按行累加 count(1)：InnoDB引擎遍历表，但不取值，server层对于返回的每一行都放数字”1”进去，判非空，按行累加 count(字段)：如果这个字段允许为null，那么执行的时候，InnoDB要多判断一次非null count(1)比count(id)快，因为引擎返回id会涉及到解析数据行，以及拷贝字段值的操作

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

Q1：binlog写完，redo log还没commit前发生crash，那么崩溃恢复Mysql是如何处理的？
A1:

  1. 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
  2. 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：a. 如果是，则提交事务；b. 否则，回滚事务。

Q1-2：MySQL怎么知道binlog是完整的？
A1-2：
一个事务的 binlog 是有完整格式的：

  1. statement 格式的 binlog，最后会有 COMMIT；
  2. row 格式的 binlog，最后会有一个 XID event。

另外MySQL5.6.2版本之后引入binlog-checksum参数，可以通过checksum的结果来验证

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

Q1-4：为什么要二阶段提交？干脆先redo log写完再写binlog。崩溃恢复的时候，必须两个日志都完整才可以。
A1-4：
如果redo log写完了，事务就不能回滚了，这个时候binlog如果又写入失败，innoDB又无法回滚了，就会造成数据不一致。这是一个经典的分布式系统问题，二阶段提交就是和所有人确认ok的时候，才一起提交

Q1-5：不引入两个日志，至用binlog来支持崩溃恢复，又能支持归档，不可以吗？
A1-5：
binlog没有记录crash时候哪些是脏页，无法确定”数据重放”的时机点。除非每次写入binlog成功后，就立刻刷盘，刷完将该binlog改为commit状态，那么又变成了类似redo log了。

Q1-6：正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？
正常运行的实例，最终落盘就是把内存中的数据页写盘，这个过程和redo log毫无关系；在崩溃恢复场景，数据页读入内存，然后让redo log更新内存内容，更新完成后，内存中的数据页刷盘。

16 | order by是怎么工作的？

  1. 全字段排序，如果sort_buffer小于排序量，则不得不利用磁盘做归并排序

01-30 - 图8

  1. rowid排序，避免磁盘排序的方式，将需要排序的字段和主键id拿出来在sort_buffer中排序，然后回表

01-30 - 图9

  1. 为了避免排序，可以对order by 的字段加上索引，则筛选过滤出来的数据”天然有序”

01-30 - 图10

17 | 如何正确地显示随机消息？

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。
流程图如下，R为随机的Rand()值；根据Rand()值进行排序
01-30 - 图11

  1. // 优解1：获取区间最大、最小值，rand()因子得出区间内随机值X，获取第一个大于等于X的值
  2. // 不是绝对公平的，存在数据空洞的情况下 1245 4的概率更大
  3. // 效率很高 max(id)和min(id)以及id >= 的定位，可以认为只扫描了3行
  4. mysql> select max(id),min(id) into @M,@N from t ;
  5. set @X= floor((@M-@N+1)*rand() + @N);
  6. select * from t where id >= @X limit 1;
  1. // 优解2:严格随机, 取得整个表的行数，并记为C。取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。再用 limit Y,1 取得一行。
  2. mysql> select count(*) into @C from t;
  3. set @Y = floor(@C * rand());
  4. set @sql = concat("select * from t limit ", @Y, ",1");
  5. prepare stmt from @sql;
  6. execute stmt;
  7. DEALLOCATE prepare stmt;

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

  1. 条件字段函数

mysql> select count(*) from tradelog where month(t_modified)=7;

  1. 隐式类型转换

mysql判断条件”10”>9为true，则说明优先字符串转数字
所以如果存在 mysql> select * from tradelog where tradeid=110717; tradeid会转为数字，所以走了全表扫描；但 where tradeid='110717'; 则走索引

  1. 隐式数据转换

例如 t1.idStr 为utf-8，t2.idStr为utf8mb4字符集，则 t1.idStr = t2.idStr 时候是不走索引的，真正的执行逻辑是，CONVERT(``t1.idStr ``USING utf8mb4) = ``t2.idStr

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

  1. 表级
    1. 表被锁了
      1. 等MDL
    2. 等flush
  2. 行级
    1. 行锁，之前事务持有写锁，”当前读”被阻塞了
    2. 查询大表，条件字段没走索引，全表扫描造成慢
    3. “快照读”远慢于”当前读”
      1. 读id= 1，由于快照读要一次执行undo log才能获取到最原始的值，所以慢
      2. 01-30 - 图12

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

什么是幻读？
01-30 - 图13

幻读会导致数据库同步不一致的问题，如
01-30 - 图14

为了解决幻读，InnoDB引入了间隙锁（Gap Lock）。间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。间隙锁之间都不存在冲突关系，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁是在可重复读隔离级别下才会生效的。
间隙锁死锁场景
01-30 - 图15

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

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

  1. 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
  2. 原则 2：查找过程中访问到的对象才会加锁。
  3. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
  4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
  5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

该章节包含了各种场景分析，需要着重学习

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

  1. 短连接暴增，导致部分请求报错提示”Too many connections”
    1. 修改max_connections的限制，风险：调整过大，系统负载进一步加大，可能会适得其反
    2. 先处理掉占着线程不工作的线程
      1. 优先断开事务外空闲的线程（比如sleep连接），再考虑断开事务内空闲的线程
    3. 减少连接过程的消耗
      1. 跳过验证权限，–skip-grant-tables，重启服务器
  2. 慢查询
    1. 索引没设计好
    2. sql语句没写好
    3. 数据库选错索引
      1. 强制写force index语法

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

binlog写入机制
01-30 - 图16

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

redo log写入机制
01-30 - 图17

  1. innodb_flush_log_at_trx_commit设置为 0 的时候，表示每次事务提交时都只是把 redo log留在 redo log buffer 中 ;
  2. innodb_flush_log_at_trx_commit设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
  3. innodb_flush_log_at_trx_commit设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

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

组提交
**01-30 - 图18

  1. trx1 是第一个到达的，会被选为这组的 leader；
  2. 等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
  3. trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；
  4. 这时候 trx2 和 trx3 就可以直接返回了。


在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。 二阶段提交：redo log（prepare） —> binlog —> redo log commit 则优化为 redo log（prepare） write —> binlog write —> redo log fsync —> binlog fsync —> redo log commit write 注：write为写linux的page cache，fsync为写磁盘即真正算IOPS的操作

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

01-30 - 图19

  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 读取中转日志，解析出日志里的命令，并执行。

    建议备库设置成只读状态（readonly）

    1. 防止从库发生写操作
    2. 防止主备切换出现双写，数据不一致

双M结构，循环复制同步问题，两个库的service_id不相同。binlog 里面记录所属的service_id，相同的就不再执行。

binlog的三种格式：row、statement、mix

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

高可用障碍：主备延迟

  1. 主库A执行完一个事务，写入binlog，这个时刻记为——T1
  2. 从库同步主库binlog日志——T2
  3. 从库执行这个事务——T3

延迟时间即 T3-T1

你可以在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒。

主备延迟大的原因

  1. 备库性能差
  2. 备库压力过大，所有的读操作都在备库执行——建议一主多从解决
  3. 大事务（比如一次性删除大量数据、大表的DDL）

主备延迟产生不同主备切换策略

可靠性优先策略（双M结构下，主备切换）

  1. 判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
  2. 把主库 A 改成只读状态，即把 readonly 设置为 true；
  3. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
  4. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
  5. 把业务请求切到备库 B。

    步骤2、3会产生服务对外暂时不可用

可用性优先策略

  1. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
  2. 把业务请求切到备库 B。

    几乎没有不可用时间，但是会产生数据不一致问题

数据不一致情况1：可用性优先策略+binlog_format=mixed时的切换流程和数据结果

01-30 - 图20

数据不一致情况2：可用性优先策略+binlog_format=row时的切换流程和数据结果

01-30 - 图21

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

从库数据同步持续慢于主库写入

01-30 - 图22

Mysql5.6之前，sql_thread只支持单线程，所以主库写入（高负载并发写入）持续远快于从库同步线程速度，因此会出现严重的主备延迟。

从库并行复制策略

为了解决上述的问题，mysql引入的了多线程复制
01-30 - 图23coordinator 就是原来的 sql_thread, 不过现在它不再直接更新数据了，只负责读取中转日志和分发事务。真正更新日志的，变成了 worker 线程。而 work 线程的个数，就是由参数 slave_parallel_workers 决定的。

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

  1. 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。
  2. 同一个事务不能被拆开，必须放到同一个 worker 中。（否则破坏了事务逻辑的隔离性/原子性）

    Mysql 5.6的并行复制策略颗粒度很大，按库来区分的，如果库之间负载均衡，效果会很好，否则基本起不到并行的效果 我们也可以自己来实现，比如基于表的颗粒度，基于行的颗粒度。基于行的颗粒度逻辑就会比较麻烦了，检测、判断的时间复杂度会比较高，cpu消耗多，也不一定是最优解。

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

一主多从的主备切换过程

01-30 - 图24

基于位点的主备切换

当我们把节点 B 设置成节点 A’的从库的时候，需要执行一条 change master 命令：

  2. CHANGE MASTER TO 
  3. MASTER_HOST=$host_name 
  4. MASTER_PORT=$port 
  5. MASTER_USER=$user_name 
  6. MASTER_PASSWORD=$password 
  7. MASTER_LOG_FILE=$master_log_name    
  8. MASTER_LOG_POS=$master_log_pos    //备库A'的位点，无法精确获取

考虑到切换过程中不能丢数据，所以我们找位点的时候，总是要找一个“稍微往前”的，然后再通过判断跳过那些在从库 B 上已经执行过的事务。

基于GTID的主备切换

GTID（全局事务 ID）
MySQL 5.6 版本引入了 GTID，彻底解决了这个困难。
GTID 格式定义： GTID=source_id:transaction_id（这里的 source_id 就是 server_uuid，transaction_id即gno，而不是我们所说的transaction_id）

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

在 GTID 模式下，每个事务都会跟一个 GTID 一一对应。每个 MySQL 实例都维护了一个 GTID 集合，用来对应“这个实例执行过的所有事务”。

基于GTID的主备切换

  2. CHANGE MASTER TO 
  3. MASTER_HOST=$host_name 
  4. MASTER_PORT=$port 
  5. MASTER_USER=$user_name 
  6. MASTER_PASSWORD=$password 
  7. master_auto_position=1   //已经不需要指定位点了

我们把现在这个时刻，实例 A’的 GTID 集合记为 set_a，实例 B 的 GTID 集合记为 set_b。接下来，我们就看看现在的主备切换逻辑。
我们在实例 B 上执行 start slave 命令，取 binlog 的逻辑是这样的：

  1. 实例 B 指定主库 A’，基于主备协议建立连接。
  2. 实例 B 把 set_b 发给主库 A’。
  3. 实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在于 set_b 的 GTID 的集合，判断 A’本地是否包含了这个差集需要的所有 binlog 事务。
    1. 如果不包含，表示 A’已经把实例 B 需要的 binlog 给删掉了，直接返回错误；
    2. 如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给 B；
  4. 之后就从这个事务开始，往后读文件，按顺序取 binlog 发给 B 去执行。

基于位点的协议，是由备库决定的，备库指定哪个位点，主库就发哪个位点，不做日志的完整性判断。 基于GTID的协议，是由主库决定的。

28 | 读写分离有哪些坑？

一主多从架构的应用场景：读写分离。
01-30 - 图25带proxy的读写分离架构

主备延迟导致读从库数据出现”过期读”

  1. 强制走主库方案；
    1. 将查询做分类，必须要拿到最新结果的请求走主库，否则走从库
    2. 此方案用的比较普遍，但偏取巧，避难
  2. sleep 方案；
    1. 这个方案是假设主备延迟在1秒以内，做一个sleep(1)大概率能拿到最新数据
    2. 性能不高、部分读延迟超时时间长还是会”过期读”
  3. 判断主备无延迟方案；
    1. 判断seconds_behind_master是否等于0
  4. 配合 semi-sync 方案；
  5. 等主库位点方案；
    1. Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。

如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成

  1. 等 GTID 方案。
    1. Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。
    2. Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；
    3. Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。
    4. 如果这两个集合相同，也表示备库接收到的日志都已经同步完成。

GTID存在的问题是判断主备无延迟的逻辑是”备库收到的日志都执行完了”，但可能有部分日志是还没到备库的；如下所示：
01-30 - 图26trx3还没传给备库，此刻读也是”过期读”

为了解决这个问题，引入semi-sync，利用semi-sync配合GTID即解决上述的问题。

semi-sync（半同步复制）

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

延伸出来的问题：如果是一主多从的情况下怎么办？等所有从库都返回ACK吗？速度太慢了，可靠性也低。At Least方式？可靠性也低，如果客户端请求的从库刚好是没返回ACK的，那还是”过期读”

…. 后面还有一些，暂不整理

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

  1. select 1 判断
    1. select 1成功返回只能说明这个库进程还在，并不能说明没有问题
    2. 比如无法检测到系统并发查询线程过多的情况
  2. 查表判断
    1. 查表的方式无法检测到磁盘满的情况，因为此时读依旧可用，而写失败（更新事务要写 binlog，而一旦 binlog 所在磁盘的空间占用率达到 100%，那么所有的更新语句和事务提交的 commit 语句就都会被堵住。）
  3. 更新判断
    1. 双M结构下，主从都开启写做检测，更新同一行容易出现主从不一致，行冲突，导致同步停止
    2. 通过每台节点的server_id，更新自己的server_id行来解决
    3. 虽然这个方法比较常用，但更新判断无法解决检测SQL慢的问题，因为执行此检测SQL是随机性的
  4. 内部统计
    1. 打开redo log的时间监控， mysql> update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';
    2. 通过 MAX_TIMER 的值来判断数据库是否出问题了。比如，你可以设定阈值，单次 IO 请求时间超过 200 毫秒属于异常，然后使用类似下面这条语句作为检测逻辑。 mysql> select event_name,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;
    3. 开启存在性能损耗


并发连接和并发查询，并不是同一个概念。你在 show processlist 的结果里，看到的几千个连接，指的就是并发连接。而“当前正在执行”的语句，才是我们所说的并发查询。 并发连接数达到几千个影响并不大，就是多占一些内存而已。我们应该关注的是并发查询，因为并发查询太高才是 CPU 杀手。这也是为什么我们需要设置 innodb_thread_concurrency 参数的原因。 建议把 innodb_thread_concurrency 设置为 64~128 之间 在线程进入锁等待以后，并发线程的计数会减一，也就是说等行锁（也包括间隙锁）的线程是不算在 128 里面的。

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