写在前面

在上一篇笔记中说过了，除了要会写 SQL 语句，对于一些原理也要有一定的理解，比如 事务、锁、索引 等。
这次通过这本书，我们先大概的了解一下重点内容，先为面试做一下准备。
面试重点内容，用来对看的内容进行重点把握

第1章 MySQL体系结构和存储引擎

MySQL 单进程多线程。
先看一个总体图

MySQL体系结构

可以看到，MySQL由几部分组成：

• 连接池组件
• 管理服务和工具组件
• SQL 接口组件
• 优化器组件
• 缓冲组件
• 插件式存储引擎
• 物理文件

可以看出来，MySQL表的存储引擎是做成插件化的，可选择很多。

1. InnoDB 引擎

设计目标是面向在线事务处理（online transaction processing, OLTP）的应用。
支持 事务、行级锁、通过多版本控制支持高并发（MVCC）、提供一致性非锁定读。

2. MyISAM引擎

设计目标是面向 联机事务处理 （On-Line Analytical Processing，OLAP） 数据库应用。OLTP 和 OLAP 区别
不支持事务、不支持行级锁、表锁设计，支持全文索引

第2章 InnoDB 存储引擎

InnoDB 从 MySQL5.5开始就是默认的存储引擎。

InnoDB体系架构

从图中可以看出，InnoDB 体系分为3块：后台线程、内存池、文件。
InnoDB 存储引擎是多线程模型，后台有多个不同线程，用于处理不同的任务。

1. 线程模型

后台线程的主要作用：

1. 刷新内存池中的数据，保证缓冲池中的数据都是最新的数据
2. 将缓冲池中的已经修改的数据文件刷新到磁盘文件中
3. 异常发生时候，InnoDB 可以正常恢复

后台线程分为：

1. Master Thread：核心线程，将缓冲池中的数据异步刷新到磁盘
2. IO Thread：负责AIO请求的回调处理

这里的IO是异步的，有两个好处，其一，AIO可以异步调用，并行读取；其二，可以进行IO合并。
异步调用需要 call back，由 IO Thread 来完成。

3. Purge Thread：回收没有用的 UNDO 页（从 Master Thread 抽离）
4. Page Cleaner Thread：脏页刷新回磁盘（从 Master Thread 抽离）

3 和 4 都是为了减轻 Master Thread 的工作，提到InnoDB性能，从中抽离出来的。

2. 内存模型

2.1 缓冲池

InnoDB 是基于磁盘的存储系统，为了弥补 CPU 和磁盘性能的差距（因为内存操作是远远大于磁盘操作数据速度的），所以将从磁盘读出的数据保存在内存中，下次读取先从缓冲池中读取。有数据更新也先更新缓冲池的数据，通过 checkpoint 机制写回磁盘。
缓冲池中包括索引页、数据页、undo页、插入缓存、锁信息等。
那么究竟是怎样对缓冲池中的内存进行管理的呢？看下面这几种操作。

2.2 缓冲池管理（LRU List）

InnoDB使用LRU（Latest Recent Used）算法来管理缓冲池中的页，朴素的LRU会把最频繁使用的数据插到头部，最少使用的放在List末尾，当缓冲池不足以容纳新的数据时候，先从尾部释放数据页。但是InnoDB使用的LRU将新的数据插入到中间位置（5/8处），避免大量的一次性查询将频繁使用的数据页刷出缓冲池。

2.3 脏页管理（Flush List）

数据发生更新的时候，缓冲池中的数据首先被更新，修改之后的页称为脏页。脏页会保存在 Flush List 中，通过 checkpoint 机制，将脏页数据写回磁盘

脏页就是被修改的缓存数据（这时候缓存和磁盘中数据是不一致的）

2.4 页的读取和修改

数据库读取页的时候，会首先判断缓冲池中有没有该页，如果已经有，就是用缓冲池中的页；否则，从磁盘中读取该页
修改页的时候，也是修改缓冲池中的，再以一定的频率来刷新到磁盘进行持久化。
如果数据再刷新回磁盘之前，发生了异常，就会根据 redo log 进行恢复。所以 InnoDB会保证，在页修改时，redo log 已经持久化到磁盘了。

2.5 重做日志（redo log）缓冲

上面说到，redo log 是要先写到日志缓冲区中，然后以一定的频率刷新回磁盘。如下几种情况都会刷新回磁盘持久化：

1. Master Thread 没秒将 redo log 刷新
2. 事务提交
3. redo log buffer 空间少于一半

2.6 check point 技术

之前我们提到过，为了防止宕机导致事务未提交信息而丢失，在事务提交时，首先把数据保存到redo log 中，再修改页。这样保证了事务的持久性（D）。
每到 checkpoint，缓冲池中的脏页数据会马上刷回磁盘，而系统也就会把这个刷新的时间点记录到 redo log 的结尾，在进行数据恢复，checkpoint 之前的数据就不用恢复了。

checkpoint 机制的核心就是要减少实例恢复的时间

checkpoint触发时机：

• Master Thread checkpoint：每秒一次
• FLUSH_LRU_List_check point ：如果LRU List空闲页不足100个，清理队尾的页面，如果清理页中有脏页，出发 checkpoint 刷新脏页到磁盘
• Dirty Page Too much Checkpoint：脏页太多超过阈值，触发 checkpoint 强制刷新脏页到磁盘。
• Async/Sync Flush Checkpoint：redo log 不可用时

3. 关键特性

这里我们看一下 InnoDB 的关键特性，一共有三点：插入缓存、两次写、自适应 hash 索引优化、异步 IO

3.1 插入缓存

针对 非聚集索引，其插入方式是，先缓冲到内存中，之后在刷新到磁盘中。
原因：
InnoDB索引是用 B+树来存储的，非聚集索引在存储结构上不连续，离散的读取非聚集索引页，性能很差。

比如说现在有了聚集索引，然后又有一个列（比如说 name）来作为辅助索引。首先，这个辅助索引是一个非聚集索引（数据和索引分开嘛）。然后，当你要进行索引插入的时候，因为这个 name 列是一个无序的列，所以当一个新来的 name 索引，实际上市区要在叶子结点中离散的查找插入位置的。

比如，现在的索引叶子节点是 2 4 5 9 8
新来一个 6 ，就是要去离散查找插入位置的。
做了缓存之后，多个插入请求，被相对排序一下，就会减少这种离散插入的消耗。

总结，为了克服非聚集索引的插入离散性

3.2 两次写

1. 原因：
   数据库从内存向磁盘中 写一个 内存页，可能会由于宕机，从而导致这个页只写了部分数据，这就是部分写失效，它会导致数据丢失。这时是无法通过重做日志恢复的，因为重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。
2. double-write

• 首先，将数据写入 doubel-wirte-buffer
• 然后，将数据写入 共享磁盘空间
• 最后，再写入存储空间

相当于在共享磁盘空间做了一份复制。

3.3 自适应HASH索引

一个数据页频繁被访问的时候，会针对该页，根据缓存中的索引，建立 hash 索引，提高查询速度。

3.4 异步IO（AIO）

AIO，在一个IO之后，不会等待，直接下一个IO，所有IO请求发出，等待即可。

第3章 文件

这一章看一下就行了，不用记住什么

MySQL 数据库和 InnoDB 存储引擎都有多种类型的文件，作用不同。主要有 参数文件、 socket 文件、pid 文件、日志文件、表结构文件、存储引擎文件

3.1 日志文件

• 错误日志：记录启动运行、关闭时候遇到的错误信息
• 查询日志：记录所有的查询操作
• 二进制日志（binlog）：记录所有数据更改记录。用于数据恢复和复制。
• 慢查询日志：记录查询时间超过threshold的记录（用于后期优化之类的吧）

3.2 InnoDB存储文件

• 表空间文件：存储数据
• redo log 文件：存储事务日志。

第4章 表

这一章我们简单看一下，明白表的存储结构即可。
InnoDB 的数据结构都是用 B+Tree 来组织的。
粒度从左到右逐渐变细。
如下图中， tablespace-->segment-->extent-->page-->row，即为 “表段区页行”（表断了去夜航）
注意： 页是InnoDB管理磁盘的最小单位，InnoDB从磁盘中读数据都是按页读的（之前也说过什么页，脏页啥的）。

InnoDB逻辑存储结构

第5章 索引与算法

这一章是重中之重。整本书最核心的一章，面试必问的。
比较详细的讲解，都看懂就基本无敌

InnoDB 支持三种索引：B+Tree 索引、Hash索引、全文索引

5.1 什么是索引

MySQL 官方定义：索引（index）是帮助MySQL高效获取数据的数据结构。即，索引就是一种数据结构。
看一个例子，如下图中，我们通过建立一个二叉查找树来和左边真实的数据表，做一个映射。真正查找的时候只需，通过右边的二叉查找树来使用 二叉查找 在 内查找到。
这个二叉查找树就是一个索引。

一种可能的索引

5.2 B+树索引

对于B+Tree 的定义实际上还是比较复杂的。这里我们就不看了。

上图是书中举例的一颗 B+Tree，其有两个特点：

1. 所有数据都在叶子节点，非叶子节点不存储数据
2. 叶子节点采用双向链表连接

那么，为什么要用B+Tree来做索引呢，有什么优点呢：

1. B+数的磁盘读写代价低：非叶子节点也是页，每个页的大小有限，由于非叶子节点不存真实数据，所以可以放下非常多的索引，这样一来，每次IO可以查找的关键字越多，相对IO次数就下降。

所以说，不要用大的VARCHAR等，建立索引，就会破坏读写速度。

2. B+树的查询效率稳定：数据都是在叶子节点的，所以每次索引扫描，从根到叶子的路径长度都是一样的。所以效率稳定。
3. B+树方便范围查询：叶子节点使用双向链表，方便进行范围查询。

这里不论 MyISAM 还是 InnoDB 都是采用 B+树这样的索引

5.3 聚集索引

所谓聚集和非聚集，是指数据文件和索引文件是否分开。

在 InnoDB引擎中，索引文件 就是 数据文件，因为叶子节点中存的就是整行数据。
InnoDB 要求表必须要有主键，如果没有指定，那么系统会自动选择一列可以唯一表示的列做主键，如果没有这样的列，那么就自动生成一个 long int 的隐含字段作为主键。
所以一般来说，InnoDB 索引要求使用一个自增主键，后面5.7会讲。

聚集索引

5.4 非聚集索引

在 MyISAM 引擎中，索引文件和数据文件是分开的。B+树索引的子节点中，存的是数据的行地址。

非聚集索引

辅助索引

聚集索引和非聚集索引一般都是由 主键作为主索引，同时还可以有 其他列作为 辅助索引。
对于聚集索引来说，辅助索引的叶子节点存的就是主键值，所以如果从辅助索引来查找，那么需要 2次查找，先通过辅助索引找到主键，再通过主键和主索引找到需要的数据。
而对于非聚集索引来说，辅助索引的叶子节点也存的是行地址，所以只需要一次查找辅助索引即可。
下面这个图可以说明问题。

聚集索引和非聚集索引在辅助查找时候的区别

5.5 联合索引 及其 最左原则

联合索引

联合索引是对表中的多个列进行索引。之前的单列索引可以看成是联合索引列数为1的特殊情况。
联合索引也是一个 B+Tree，不同的是索引键值的数量>=2。

最左原则

联合索引使用遵循从左到右匹配。如果有一个 (a,b,c) 的索引，那么就相当于有了 (a) (a,b) (a,b,c) 这三种索引

(a,b,c) 的联合索引
查询条件：
（A， ，）-- 会使用索引
（A，B，）-- 会使用索引
（A，B，C）-- 会使用索引
（， B，）-- 不会使用索引
（， B，C）-- 不会使用索引

5.6 覆盖索引

覆盖索引是指，能从辅助索引中查到的信息，就不再去查聚集索引了。
因为毕竟辅助索引不存整行信息，大小远远小于聚集索引，可以减少很多IO操作。

5.7 InnoDB主键选择与插入优化

1. 使用自增主键

使用 InnoDB 存储引擎时候，使用一个与业务无关的自增字段作为主键。
实际上，如果没有指明主键，数据库也会自动添加一个主键，long char 型。
那么为什么需要这么做呢？
InnoDB 使用聚集索引，数据记录被存在子节点上。这就要求同一个子节点内（大小为一个内存页或者磁盘页），各条数据记录按照主键顺序存放，新的数据插入后，MySQL会根据其主键选择合适的插入位置，页达到装在因子（InnoDB默认15/16），则开辟一个新页（节点）。
这时候，使用自增主键，那么插入新的记录，就会顺序写在当前索引节点的后续位置，本页满了，就写下一个页面。如下图。
这样就是一个紧凑的索引结构，近似于顺序填满。而且每次插入基本不会移动数据，效率非常高。

使用自增主键

2. 使用非自增主键

当我们使用非自增主键（例如学号），每次插入时候，主键的值是一个近似于随机的，因此每次更新记录都要插入到现有索引页中间的某个位置。

使用非自增主键

因此，会不紧凑，大量碎片。同时，频繁移动数据，效率低下。

5.8 MRR（Multi-Range Read）技术

MySQL 5.5.6之后支持的技术，目的是为了减少随机访问，将随机访问转换成为较为顺序的数据访问。
工作方式为：

• 将查询得到的非聚集索引键值存储到一个缓存中
• 缓存中的键值进行排序，再根据排序的顺序进行数据访问
  这样如果多个键值在同一个页里面，就可以通过一个IO操作把数据都拿出来。

5.9 Hash 索引

Hash 索引就是采用 hash 算法，把键值映射成为 hash值，检索是直接用一次 hash 算法进行检索即可，是无序的。
优势：
等值查询时候，非常快。 where name="XDX" 就是一个等值查询，对应的概念是模糊查询。
限制：

• 无法用于排序与分组
• 只支持精确查找，无法
• 数据多发生哈希碰撞时候，效率非常低下。

前面我们提到过 InnoDB引擎一个特性“自适应hash索引”

• 当某个索引值使用非常频繁时候，会在 B+Tree 索引的基础上 在创建一个hash索引
• 这样就让 B+Tree 索引有了 Hash 索引快速查找的优点

5.10 全文 索引

MyISAM 和 InnoDB 都支持全文索引。用于 查找文本中的关键字词，而不是比较直接相等。
全文索引一般使用 倒排索引 实现，其记录着关键词到其所在文档的映射。

倒排索引： word1: 文档1， 文档2 ， 文档3
word2: 文档2， 文档3， 文档4
倒排索引

5.11 索引使用场景

对于非常小的表，使用全表扫描比建立索引更加高效
对于中到大型表，建立索引非常有效
对于超大表，建立和维护索引的代价比较大。这时候，需要使用一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录的匹配，例如使用 分区技术

什么列适合建立索引

• 主键和外键
• 经常被 where 的列
• 范围查找的列
• 经常用作连接两个表的列