引言 本文整理了 MySQL 相关的知识，方便以后查阅。
基础架构 下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图。
连接器：身份认证和权限相关(登录 MySQL 的时候)。
查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器：按照 MySQL 认为最优的方案去执行。
执行器: 执行语句，然后从存储引擎返回数据。
简单来说 MySQL 主要分为 Server 层和存储引擎层：
Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。
基本组件 连接器连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。
主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。
查询缓存查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。
连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。
MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。
所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。
MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。
分析器MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：
第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。
完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。
优化器优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。
可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。
执行器当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。
存储引擎存储引擎就是真正保存数据，索引数据的容器。在 MySQL 中有两个主流的存储引擎，MyISAM 和 InnoDB。
区别MyISAM 是 MySQL 的默认数据库引擎（5.5 版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但 MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5 版本之后，MySQL 引入了 InnoDB（事务性数据库引擎），MySQL 5.5 版本后默认的存储引擎为 InnoDB。
大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃回复问题的话）。
是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。是否支持事务和崩溃后的安全恢复：MyISAM 强调的是性能，每次查询具有原子性,其执行速度比 InnoDB 类型更快，但是不提供事务支持。但是 InnoDB 提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。是否支持外键：MyISAM 不支持，而 InnoDB 支持。是否支持 MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC 比单纯的加锁更高效;MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC 可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中 MVCC 实现并不统一。
一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择 MyISAM 也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的。
索引 MySQL 索引使用的数据结构主要有 BTree 索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择 BTree 索引。
MySQL 的 BTree 索引使用的是 B 树中的 B+Tree，但对于主要的两种存储引擎的实现方式是不同的：
MyISAM: B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB: 其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
InnoDB 数据结构各个数据页可以组成一个双向链表 而每个数据页中的记录又可以组成一个单向链表
每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。
所以说，如果我们写 select * from user where id = '8'这样没有进行任何优化的 sql 语句，默认会这样做：
定位到记录所在的页，需要遍历双向链表，找到所在的页
从所在的页内中查找相应的记录，由于不是根据主键查询，只能遍历所在页的单链表了
innoDB-index-find-item.png哈希索引除了 B+树之外，还有一种常见的是哈希索引。
哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似 B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
本质上就是把键值换算成新的哈希值，根据这个哈希值来定位。看起来哈希索引很强，能快速找到对应的项，但其实哈希索引有好几个局限(根据他本质的原理可得)：
哈希索引也没办法利用索引完成排序
不支持最左匹配原则
在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题。
不支持范围查询
最左匹配原则最左匹配原则：
索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。
如果是联合索引，那么 key 也由多个列组成，同时，索引只能用于查找 key 是否存在（相等），遇到范围查询(>、<、between、like 左匹配)等就不能进一步匹配了，后续退化为线性查找。
因此，列的排列顺序决定了可命中索引的列数。
事务 事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账 1000 元，这个转账会涉及到两个关键操作就是：将小明的余额减少 1000 元，将小红的余额增加 1000 元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。
ACID原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
事务日志事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时，只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序 I/O，而不像随机 I/O 需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久化后，内存中被修改的数据可以在后台慢慢地刷回磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据要刷写两次磁盘。
如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。
并发问题在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。
脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。
不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
隔离级别READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
隔离级别脏读不可重复读幻影读READ-UNCOMMITTED√√√READ-COMMITTED×√√REPEATABLE-READ××√SERIALIZABLE×××MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看。
mysql> SELECT @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+MVCC很多应用的一个特点都是读多写少的场景，很多数据的读取次数远大于修改的次数，而读取数据间互相排斥显得不是很必要。所以就使用了一种读写锁的方法，读锁和读锁之间不互斥，而写锁和写锁、读锁都互斥。这样就很大提升了系统的并发能力。之后人们发现并发读还是不够，又提出了能不能让读写之间也不冲突的方法，就是读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务 session 会看到自己特定版本的数据。当然快照是一种概念模型，不同的数据库可能用不同的方式来实现这种功能。
InnoDB 中通过 UndoLog 实现了数据的多版本，而并发控制通过锁来实现。
Undo Log 除了实现 MVCC 外，还用于事务的回滚。
区分各种 logMySQL Innodb 中存在多种日志，除了错误日志、查询日志外，还有很多和数据持久性、一致性有关的日志。
binlog，是 mysql 服务层产生的日志，常用来进行数据恢复、数据库复制，常见的 mysql 主从架构，就是采用 slave 同步 master 的 binlog 实现的, 另外通过解析 binlog 能够实现 mysql 到其他数据源（如 ElasticSearch)的数据复制。
redo log 记录了数据操作在物理层面的修改，mysql 中使用了大量缓存，缓存存在于内存中，修改操作时会直接修改内存，而不是立刻修改磁盘，当内存和磁盘的数据不一致时，称内存中的数据为脏页(dirty page)。为了保证数据的安全性，事务进行中时会不断的产生 redo log，在事务提交时进行一次 flush 操作，保存到磁盘中, redo log 是按照顺序写入的，磁盘的顺序读写的速度远大于随机读写。当数据库或主机失效重启时，会根据 redo log 进行数据的恢复，如果 redo log 中有事务提交，则进行事务提交修改数据。这样实现了事务的原子性、一致性和持久性。
Undo Log: 除了记录 redo log 外，当进行数据修改时还会记录 undo log，undo log 用于数据的撤回操作，它记录了修改的反向操作，比如，插入对应删除，修改对应修改为原来的数据，通过 undo log 可以实现事务回滚，并且可以根据 undo log 回溯到某个特定的版本的数据，实现 MVCC。
redo log 和 binlog 的一致性，为了防止写完 binlog 但是 redo log 的事务还没提交导致的不一致，innodb 使用了两阶段提交
InnoDB prepare （持有prepare_commit_mutex，写redo log 和 undo log）write/sync Binlog （可以同步，也可以异步，只要redo log写入之后就可以放心提交了，因为即便丢失了bin log也可以通过redo log恢复数据）InnoDB commit (写入COMMIT标记后释放prepare_commit_mutex)实现InnoDB 行记录中除了刚才提到的 rowid 外，还有 trx_id 和 db_roll_ptr, trx_id 表示最近修改的事务的 id,db_roll_ptr 指向 undo segment 中的 undo log。
新增一个事务时事务 id 会增加，所以 trx_id 能够表示事务开始的先后顺序。
Undo log 分为 Insert 和 Update 两种，delete 可以看做是一种特殊的 update，即在记录上修改删除标记。update undo log 记录了数据之前的数据信息，通过这些信息可以还原到之前版本的状态。当进行插入操作时，生成的 Insert undo log 在事务提交后即可删除，因为其他事务不需要这个 undo log。进行删除修改操作时，会生成对应的 undo log，并将当前数据记录中的 db_roll_ptr 指向新的 undo log
可见性判断CREATE TABLE `testunique` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `uid` int(11) DEFAULT NULL, `ukey` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `id_uid` (`uid`), KEY `index_key` (`ukey`)) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8;隔离级别 REPEATABLE READ只有当 session2 commit 之后的查询才能查到 session1 插入的数据
事务可见性的处理过程:RR 级别下一个事务开始后第一个 snapshot read 的时候，会将当期活动的事务 id 记录下来，记录到 read view 中。RC 级别则是每次 snapshot read 都会创建一个新的 read view。
假设当前,read view 中最大的事务 id 为 tmax, 最小为 tmin。则判断一个数据是否可见以及对应的版本的方法为：
如果该行中的 trx_id, 赋值给 tid, 如果 tid 和当前事务 id 相等或小于 tmin，说明是事务内发生的或开启前的修改，则直接返回该版本数据;
如果 trx_id 大于 tmax, 则查看该版本的 db_roll_ptr 中的 trx_id，赋值给 tid 并从头开始判断。
如果 tid 小于 tmax 并且不在 read view 中，则返回，否则从回滚段中找出 undo log 的 trx_id，赋值给 tid 从头判断。
所以可见性是，只有当第一次读之前提交的修改和自己的修改可见，其他的均不可见。
Undo log 删除undo log 在没有活动事务依赖（用于 consistent read 或回滚)便可以清除，innodb 中存在后台 purge 线程进行后台轮询删除 undo log。
Current Read & Snapshot ReadREPEATABLE READ 隔离级别下普通的读操作即 select 都不加锁，使用 MVCC 进行一致性读取，这种读取又叫做 snapshot read。
在 innoDB 中，因为 MVCC 的实现，如果只进行普通读select * from t where a=1;那么是可以保证不可重复读和消除幻读，因为普通读操作会走 snapshot read，innoDB 会根据 tid 过滤掉事务开启前未提交的事务。
而 update, insert, delete, select … for update, select … lock in share mode 都会进行加锁，并且读取的是当前版本，也就是 READ COMMITTED 读的效果。
InnoDB 中加锁的方法是锁住对应的索引，一个操作进行前会选择一个索引进行扫描，扫描到一行后加上对应的锁然后返回给上层然后继续扫描。InnoDB 支持行级锁(record lock)，上述需要加锁的操作中，除了 select … lock in share mode 是加 shared lock（共享锁或读锁）外其他操作都加的是 exclusive lock(即排他锁或写锁)。在加行级锁前，会对表加一个 intention lock，即意向锁，意向锁是表级锁，不会和行级锁冲突，主要用途是表明一个要加行级锁或正在加锁的操作。
另外 InnoDB 中除了 record lock 外还有一种 gap lock，即锁住两个记录间的间隙，防止其他事务插入数据，用于防止幻读。当要锁的索引是主键索引或唯一索引并且是单数据加锁时，不需要加 gap lock，只通过行锁即可。当要锁索引不是唯一索引或者是范围加锁时，需要对索引数据和索引前的 gap 加锁，这种方式叫做 next-key locking。
另外在插入数据时，还需要提前在插入行的前面部分加上 insert intention lock, 即插入意向锁，插入意向锁之间不会冲突，会和 gap 锁冲突导致等待。当插入时遇到 duplicated key 错误时，会在要插入的行上加上 share lock。
因为 InnoDB 的 MVCC 机制：自己修改过的数据在快照中可见&update 操作是基于当前版本而不是快照版本，所以 InnoDB 的 RR 隔离级别存更新操作自动降级到了 RC 级别，即更新是在最新版本的数据上进行，故 RR 隔离级别下的非 READ-ONLY 事务，可能存在不可重复读和幻象的情况。
a事务先select，b事务update或insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作）a事务再select出来的结果在MVCC下还和第一次select一样，接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的）a事务再次select就会出现b事务中的修改或者新添加的行，并且这一行数据已经被update修改了从上面的例子中，我们看出 innoDB 的非 READ-ONLY RR 事务，退化成了 RC 级别。
为了保证 InnoDB 的 RR 隔离级别达到 ANSI-RR 级别，对于单纯的 SELECT 语句，需要显示 for update 进行 gap 锁，更新语句自动会上 gap 锁，防止其它事务的干扰，这样就能达到可重复读和消除幻读的目的。
锁 MyISAM 和 InnoDB 存储引擎使用的锁：
MyISAM 采用表级锁(table-level locking)。
InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁
表级锁和行级锁对比：
表级锁：MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁：MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
InnoDB 存储引擎的锁的算法有三种：
Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身
InnoDB 存储引擎总结 从 MySQL5.6 开始，InnoDB 是 MySQL 数据库的默认存储引擎。它支持事务，支持行锁和外键，通过 MVCC 来获得高并发。
MySQL5.6 的 InnoDB 存储引擎体系结构图如下:
InnoDB 缓冲池InnoDB 缓冲池是内存中用来缓存表数据和索引的一片区域。
当缓冲池大小达到 GB 级别时，通过设置多个缓冲池实例，可以提高并发处理能力，减少数据库内部资源竞争。对于存储到或从缓冲池中读取的每个页，都使用哈希函数随机分配到不同的缓冲池实例中。
innodb_buffer_pool_size: 设置缓冲池的大小(单位: Byte)，重启生效。
innodb_buffer_pool_instances: 设置缓冲池实例的个数，每个缓冲池管理自己的 free lists，flush lists,LRU list 和其他数据结构，并受到自己的互斥锁保护。此值默认为 1，最大为 64。只有当 innodb_buffer_pool_size 超过 1G 时，此参数才会起作用。重启生效。
缓冲池的 LRU 算法InnoDB 以列表的方式管理缓冲池，使用优化后的 LRU 算法。此算法可以最大限度地减少进入缓冲池并从未被再次访问的页的数量，这样可以确保热点页保持在缓冲池中。
当缓冲池的 free list 没有可用的空闲页时，InnoDB 会回收 LRU 列表中最近最少使用的页，并将新读取到的页添加到 LRU 列表的 midpoint 位置，称之为“midpoint insertion strategy”。它将 LRU 列表视为两个子列表，midpoint 之前的列表称为 new 子列表，包含最近经常访问的页；midpoint 之后的列表称为 old 子列表，包含最近不常访问的页。
最初，新添加到缓冲池的页位于 old 子列表的头部。当在缓冲池中第一次访问这些页时，会将它们移到 new 子列表的头部，此时发生的操作称为 page made young。随着数据库的运行，缓冲池中没有被访问到的页由于移到 LRU 列表的尾部而变老，最终会回收 LRU 列表尾部长时间未被访问的页。
可以通过 innodb_old_blocks_pct 参数设置 old 子列表在 LRU 列表中所占的比例。默认值为 37，对应 3/8 的位置。取值范围从 5 到 95。
为什么要将新读取到的页放在 midpoint 位置而不是 LRU 列表的头部？若直接将读取到的页插入到 LRU 列表的头部，当出现全表扫描或索引扫描的时候，需要将大量的新页读入到缓冲池中，导致热点页从缓冲池刷出，而这些新页可能仅在这次查询中用到，并不是热点数据，这样就会额外产生大量的磁盘 I/O 操作，影响效率。为了避免此问题，InnoDB 引擎引入了参数：innodb_old_blocks_time，此参数表示第一次读取 old 子列表中的页后，需要等待多少毫秒才会将此页移到 new 子列表。默认值为 1000。增加此值可以让更多的页更快的老化。
Change Bufferchange buffer 用来缓存不在缓冲池中的辅助索引页(非唯一索引)的变更。这些缓存的的变更，可能由 INSERT、UPDATE 或 DELETE 操作产生，当读操作将这些变更的页从磁盘载入缓冲池时，InnoDB 引擎会将 change buffer 中缓存的变更跟载入的辅助索引页合并。
不像聚簇索引，辅助索引通常不是唯一的，并且辅助索引的插入顺序是相对随机的。若不用 change buffer，那么每有一个页产生变更，都要进行 I/O 操作来合并变更。使用 change buffer 可以先将辅助索引页的变更缓存起来，当这些变更的页被其他操作载入缓冲池时再执行 merge 操作，这样可以减少大量的随机 I/O。change buffer 可能缓存了一个页内的多条记录的变更，这样可以将多次 I/O 操作减少至一次。
在内存中，change buffer 占据缓冲池的一部分。在磁盘上，change buffer 是系统表空间的一部分，以便数据库重启后缓存的索引变更可以继续被缓存。
innodb_change_buffering 参数可以配置将哪些操作缓存在 change buffer 中。可以通过此参数开启或禁用 insert 操作，delete 操作(当索引记录初始标记为删除时)和 purge 操作(当索引记录被物理删除时)。update 操作是 inset 和 delete 操作的组合。该参数的取值如下：
all: 默认值，包含 inserts、deletes 和 purges
none: 不缓存任何操作
inserts: 缓存 insert 操作
deletes: 缓存标记删除(delete-marking)操作
changes: 缓存 inserts 和 deletes
purges: 缓存后台进程发生的物理删除操作
自适应哈希索引自适应哈希索引是 InnoDB 表通过在内存中构造一个哈希索引来加速查询的优化技术，此优化只针对使用 '=' 和 'IN' 运算符的查询。MySQL 会监视 InnoDB 表的索引查找，若能通过构造哈希索引来提高效率，那么 InnoDB 会自动为经常访问的辅助索引页建立哈希索引。
这个哈希索引总是基于辅助索引(B+树结构)来构造。MySQL 通过索引键的任意长度的前缀和索引的访问模式来构造哈希索引。InnoDB 只为某些热点页构建哈希索引。
可通过 innodb_adaptive_hash_index 参数开启或禁用此功能，默认是开启状态。开启此功能后， InnoDB 会根据需要自动创建这个哈希索引，而不用人为干预创建，这就是叫自适应的原因。此功能并不是在所有情况下都适用，且 AHI 需要的内存都是从缓冲池申请的，所以此功能的开启或关闭需要通过测试来具体确定。可以通过 SHOW ENGINE INNODB STATUS 命令查看 AHI 的使用状况。
Redo Log重做日志用来实现事务的持久性。其由两部分组成：一是内存中的重做日志缓冲(redo log buffer)，其是易失的；二是磁盘上的重做日志文件(redo log file)，其是持久的。
在 MySQL 数据库宕机恢复期间会用到重做日志文件，用于更正不完整事务写入的数据。
InnoDB 通过 Force Log at Commit 机制实现事务的持久性，即在事务提交前，先将事务的重做日志刷新到到重做日志文件。
重做日志在磁盘上由一组文件组成，通常命名为 ib_logfile0 和 ib_logfile1。
MySQL 以循环方式将日志写入重做日志文件。假设现在有两个重做日志文件：ib_logfile0 和 ib_logfile1。重做日志先写入到 ib_logfile0，当 ib_logfile0 写满后再写入 ib_logfile1。当 ib_logfile1 也写满后，再往 ib_logfile0 中写，而之前的内容会被覆盖。
innodb_log_file_size 参数用来设置每个重做日志文件的大小(单位：Byte)。innodb_log_files_in_group 参数用来设置重做日志文件组中日志文件的个数。innodb_log_group_home_dir 参数设置重做日志文件所在的路径。从 MySQL5.6.3 开始，重做日志文件总大小的最大值从之前的 4GB 提升到了 512GB。
重做日志(redo log)跟二进制日志(binlog)的区别：
重做日志在 InnoDB 存储引擎层产生；而二进制日志在 MySQL 数据库上层产生，不仅仅针对 InnoDB 引擎，MySQL 中的任何存储引擎对于数据库的变更都会产生二进制日志。
两者记录的内容形式不同，二进制日志是一种逻辑日志，其记录的是对应的 SQL 语句；重做日志是物理格式日志，其记录的是每个页的变更。
两种日志记录写入磁盘的时间点不同，二进制日志只在事务提交完成后进行写入；而重做日志在事务进行中被不断的写入，也就是日志并不是随事务提交的顺序写入的。
重做日志缓冲是一块内存区域，用来缓存即将被写入到重做日志文件的数据。InnoDB 引擎首先将重做日志信息缓存到重做日志缓冲，然后定期将其刷新到磁盘上的重做日志文件。
如下三种情况会将重做日志缓冲中的数据刷新到磁盘的重做日志文件中：
Master Thread 会定期将重做日志缓冲刷新到重做日志文件，即使这个事务还没有提交。
事务提交时
当重做日志缓冲没有足够的空间时
InnoDB 通过 Force Log at Commit 机制实现事务的持久性，即在事务提交前，先将事务的重做日志刷新到到重做日志文件。
innodb_flush_method 定义用于将数据刷新到 InnoDB 数据文件和日志文件的方法，会影响 I/O 吞吐量。默认值为 NULL，可选项包含：fsync、O_DIRECT 和其他。若在 Unix-like 系统上此参数设置为 NULL，那么默认使用 fsync。
fsync: InnoDB 调用系统的 fsync()刷新数据文件和日志文件。
O_DIRECT: InnoDB 使用 O_DIRECT 方式打开数据文件，然后使用 fsync()刷新数据文件和日志文件。启用后将绕过操作系统缓存，直接写文件。
为确保每次重做日志缓冲都能写入到磁盘的重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB 引擎都需要调用一次 fsync 操作。由于默认情况下 innodb_flush_method 参数未设置为 O_DIRECT，因此重做日志缓冲先写入文件系统缓存。
innodb_log_buffer_size 参数可以设置重做日志缓冲的大小。
innodb_flush_log_at_trx_commit 参数用来控制重做日志缓冲刷新到磁盘的策略。取值范围如下：
1: 默认值。表示事务提交时必须调用一次 fsync 操作。
0: 表示事务提交时不进行刷新日志到磁盘。这个操作在 master thread 中完成，在 master thread 中每 1 秒会进行一次刷新日志到磁盘操作。
2: 表示事务提交时将重做日志缓冲写入重做日志文件，但仅写入文件系统的缓存中，不进行 fsync 操作。此情况下，若操作系统发生宕机，会丢失未从文件系统缓冲刷新到重做日志文件的那部分日志。
Undo Logundo log(也称为 rollback segment)用来存储被事务修改的记录的副本。
undo 日志有两个作用：一个是实现事务的原子性，即当事务由于意外情况未能成功运行时，可以使事务回滚，从而让数据恢复到事务开始时的状态；另一个作用是实现 MVCC 机制，当用户读取一行记录时，若该记录已经被其他事务占有，当前事务可以通过 undo 日志读取该记录之前的版本信息，以此实现一致性非锁定读。
每个回滚段(rollback segment)记录了 1024 个 undo 段(undo segment)，InnoDB 引擎在每个 undo 段中进行 undo 页的申请。
undo log 分为 insert undo log 和 update undo log。
insert undo log 指事务在 INSERT 操作中产生的 undo 日志。因为 INSERT 操作的记录仅对当前事务可见，所以该 undo 日志在事务提交后可以直接删除。
update undo log 通常保存的是对 DELETE 和 UPDATE 操作产生的 undo 日志。该 undo 日志可能需要提供 MVCC 机制，因此其不能在事务提交后立即删除。当事务提交后，它会放入回滚段的 history 链表的头部，等待 purge 线程进行最后的删除。
默认情况下，undo 日志位于系统表空间(system tablespace)中。从 MySQL5.6 起，可以通过 innodb_undo_tablespaces 和 innodb_undo_directory 参数将 undo 日志存放在独立表空间中。
系统表空间InnoDB 系统表空间包含 InnoDB 数据字典(InnoDB 相关对象的元数据)、双写缓冲(doublewrite buffer)、change buffer 和 undo logs。此外，还包含用户在系统表空间中创建的表数据和索引数据。由于多个表的数据可以在共同存放在系统表空间中，以此其也称为共享表空间。
系统表空间可由一个或多个文件组成。默认情况下，在 MySQL 数据目录中有一个命名为 ibdata1 的系统表空间文件。innodb_data_file_path 参数可以设置系统表空间文件的大小和数量。
可以通过 innodb_file_per_table 参数启用独立表空间。即每创建一个表就会产生一个单独的 .ibd 文件存放此表的记录和索引。若未启用此参数，那么 InnoDB 引擎创建的表就会存在于系统表空间中。
双写缓冲双写缓冲技术是为了解决 partial page write 问题而开发的。doublewrite buffer 是系统表空间上的连续的 128 个页(两个区)，大小为 2M。
当发生数据库宕机时，可能 InnoDB 存储引擎正在写入某个页到表中，而这个页只写了一部分，比如 16KB 的页（默认页大小），只写了前 4KB（因为大部分磁盘页大小是 4KB，它只能保证 4KB 的原子写入），之后就发生了宕机，这种情况被称为部分写失效（partial page write）。
doublewrite 的工作原理是：在将缓冲池中的页写入磁盘上对应位置之前，先将缓冲池中的页 copy 到内存中的 doublewrite buffer，之后顺序地将内存中 doublewrite buffer 中的页写入系统表空间中的 doublewrite 区域，然后立即调用系统 fsync 函数，同步数据到磁盘文件中，避免缓冲写带来的问题。在完成 doublewrite 页的写入之后，再将内存上 doublewrite buffer 中的页写入到自己的表空间文件。通过双写缓冲，数据文件和双写缓冲文件中，至少有一份数据是正确无误的，如果写磁盘过程发生了崩溃，那么 MySQL 重启时可以通过校验和来确认是否有错误数据，如果双写缓冲文件错误了，就从数据文件中拉取原始数据根据 redo log 得出正确的目标数据，而如果数据文件错误了，则将双写缓冲中的数据重新写入数据文件。
大表优化 当 MySQL 单表记录数过大时，数据库的 CRUD 性能会明显下降，一些常见的优化措施如下：
限定数据的范围
务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内
读/写分离
经典的数据库拆分方案，主库负责写，从库负责读
垂直分区
根据数据库里面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。
简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
垂直拆分的优点：可以使得列数据变小，在查询时减少读取的 Block 数，减少 I/O 次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起 Join 操作，可以通过在应用层进行 Join 来解决。此外，垂直分区会让事务变得更加复杂。
水平分区
保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。
水平拆分是指数据表行的拆分，表的行数超过 200 万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升 MySQL 并发能力没有什么意义，所以 水平拆分最好分库 。
水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点 Join 性能较差，逻辑复杂。尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络 I/O。
参考内容 [1]https://github.com/Snailclimb/JavaGuide/blob/master/docs/database/一条sql语句在mysql中如何执行的.md
[2]https://github.com/Snailclimb/JavaGuide/blob/master/docs/database/MySQL.md
[3]https://juejin.im/post/5b55b842f265da0f9e589e79
[4]https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/
[5]https://my.oschina.net/u/553773/blog/792144