1、Mysql 8.0架构详解

mysql由连接池，sql接口，解析器，优化器，缓存，存储引擎组成； mysql分为MySQL Server层，存储引擎层，文件系统层；

注：缓存模块8.0已经不使用了。

2、存储结构

数据库文件：/var/lib/mysql

• db.opt //用来存储当前数据库的默认字符集和字符校验规则
• t_order.frm //表数据结构
• t_order.ibd //表中数据和索引等信息
  • 表空间：段，区，页，行
    • 行：一行一个记录
    • 页：InnoDB的数据是按页为单位来读写的16KB（2^14=16384字节)
    • 区：数据量大时，索引分配空间按照区1m为单位分配。
    • 段：段一般为数据段，索引段和回滚段等
      • 索引段：存放B+树的非叶子结点的区集合；
      • 数据段：存放B+树的叶子结点的区集合；
      • 回滚段：存放回滚数据的区集合；MVCC使用回滚段实现多版本查询数据。

InnoDB 行格式

• Redundant：无人用
• Compact：紧凑型格式
• Dynamic：紧凑型格式在Compact基础上改进一点
• Compressed：紧凑型格式在Compact基础上改进一点
  • 记录额外信息
    • 变长字段列表：变长字段长度需要多少字节表示
      • 条件一：如果变长字段允许存储最大字节数>=255,就会用1字节表示；
      • 条件二：如果变长字段允许存储最大字节数>=255,就会用2字节表示；
    • NULL 值列表
      • 每一列对于一位，为1代表为null，为0代表不为null。
      • NULL 值列表必须是整数个字节（8bit），不足高位补零。
    • 记录头信息
      • delete_mask:为1，代表记录被删除
      • next_record:下一条记录的位置（左读记录头信息和右读真实数据的位置）
      • record_type:当前记录的类型
        • 0:普通记录
        • 1:B+树非叶子结点记录
        • 2:最小记录
        • 3:最大记录
  • 记录真实数据
    • row_id:如果没有指定主键，又没有唯一约束，那么row_id隐藏字段。非必需
    • trx_id:表示哪个事物生成的。必需的
    • roll_pointer:这条记录上一格版本的指针
  • varchar(n)：一行数据最大字节数65535
  • ascii：一个字符占一个字节，UTF-8占3个字节
  • 行溢出：一页不够存放当前记录时，溢出的数据会存放到【溢出页】中。
    • 行中存放着指向溢出页的指针
    • Compressed 和 Dynamic 这两个行格式和Compact类型，主要的区别在于Compressed 和 Dynamic 的记录中只存放指向溢出页的指针，Compact中存放了部分数据和指向溢出页的指针。

查询过程

• 连接器：对客户端的连接进行验证
• 查询缓存：对原始SQL进行缓存命中检测（8.0已经不使用）
• 解析器：对查询语句进行语法校验并解析，得到查询语句解析树
• 预处理器：检验表，字段，类型等对解析树进一步验证
• 优化器：通过物理优化和逻辑优化，通过cost计算将解析树转化为执行计划
• 执行器：通过API与底层的存储引擎进行交互，执行执行计划
• MYSQL：存储引擎层得到执行结果，返回给MySql Service层，返回客户端

查询缓存

• 对频繁变动的表，由于一旦变动就会清除该表的所有缓存，导致，命中率极低，每次sql还增加了查询缓存的额外工作，所以8.0以后就默认不使用缓存。
• 参与hash计算的客户端发来的原始sql，未经过解析器解析，需要完全一样的sql才能命中缓存
• 查询缓存实际上是缓存sql的hash值和sql的查询结果。省去大量sql查询过程

解析器 Parser

• 解析器
  • 语法解析：语法，括号，引号关键字是否正确
  • 词法解析：关键词，表名字段名，语句语法是否正确
  • 最后得到语法解析树
• 预处理器：进一步验证语法解析树
  • 库，表是否存在
  • 字段，类型是否正确
  • 是否使用禁止的关键字等
  • 调用函数，识别别名等

优化器 Optimizer

cost：根据解析树生成不同的执行计划，然后选择一种最优的执行计划，使用基于开销cost的优化器，最终采用开销cost最小的作为最终执行的方案。

• 预估执行方式的成本，选择成本最小的执行方式，转化为执行计划
• 优化器维护一个执行计划缓存，当缓存命中时，直接使用上次的执行计划
• 执行方式的成本cost预估包含几个方面
  • io_cost：对IO操作的成本预估
  • cpu_cost：对CPU操作的成本预估
  • import_cost：对远程操作的成本预估
  • mem_cost：对内存消耗的成本预估

逻辑优化

使用关系代数对SQL做一些等价，使得SQL执行效率最高

• 对条件表达式进行等价谓词重写、条件简化、对视图重写
  • name like ‘Abc%’ 优化成 name >‘Abc’and name <= 'Abd'
  • age in (10,14,18) 优化成 age=10 or age=14 or age=18
  • having 并入 where，集中化解子条件
  • 去除冗余括号（a AND b) and (c AND d) 优化成 a AND b AND c AND d
  • 常量传递：col_1=col_2 and col_2=3 优化成 col_1=3 and col_2=3
  • 表达式计算：WHERE col_1 = 1+2 优化成 WHERE col_1=3
• 对子查询进行优化
• 对连接语义进行外连接消除，嵌套连接消除

物理优化

生成逻辑查询计划后，进一步对查询树进行物理查询优化

• 单表时：选择什么样的表单扫描方式最优
• 两个表时：哪种连接方式最优
• 多表连接时：连接顺序有多种组合，哪种连接顺序最优 基于规则的优化：基于一些预置的规则对查询进行优化 基于代价的优化：根据模型计算出可能执行计划的代价，然后选择最小的，动态的。（mysql默认）
• 索引扫描代价：加速数据的获取效率
• 单表扫描代价：
  • 全表扫描：获取全部数据，读取表对应的全部数据页
    • 顺序扫描：从物理存储上读取表的数据页
    • 索引扫描：根据索引键找到物理元组位置
    • 并行表扫描：同一个表并行地通过索引方式获取表的数据
  • 局部扫描：获取表的部分元组，读取指定位置对应的数据页
    • 根据数据量以及元组获取的条件，可能采用顺序读取或随机读取的方式，使用选择率来决定最优方案
    • 选择率的值很大，意味着采用顺序扫描的方式可能比局部扫描的随机读方式效率更高（如果数据库文件在磁盘没有碎片，那么对性能的改善更加明显）
    • 对于局部扫描通常会采用索引实现少量数据的读取优化，这是一种随机读取数据的方式。
    • 总来说，单表扫描的代价，由于单表扫描需要把数据从存储系统中加载到内存，所以需要考虑IO开销
• 扫描开销方式：
  • 顺序扫描 N_page * a_page_IO_time + N_tuple * a_tuple_CPU_time
  • 索引扫描 C_index+ N_page_index * a_page_IO_time
  • 参数说明如下：
    • a_page_IO_time：一个数据页加载IO耗时
    • N_page：数据页数量
    • N_tuple：元组数
    • a_tuple_CPU_time：一个元组从数据页中解析的CPU耗时
    • C_index：索引的IO耗时
    • N_page_index：索引页数量

执行计划 explain

• id：标识符
• select_type：表查询类型
  • simple：简单查询
  • primary：位于最外部查询
  • union：第二个或之后的查询
  • dependent union：第二个活之后的查询
  • union result：union查询的结果集
  • subquery：子查询中第一个select查询
  • dependent subquery：子查询当中第一个select查询取决于外部查询
  • derived：from子句的自查询
  • materialized：物理子查询
  • uncacheable subquery：结果集无法缓存的子查询，必需重新评估外部查询的每一行
  • uncacheable union：union中第二个或之后sleect属于无法缓存的子查询
• table：表名称
• partitions：涉及表哪个分区
• type：表查询连接类型
  • system
  • const
  • eq_ref
  • ref
  • fulltext
  • ref_or_null
  • index_merge
  • unique_subquery
  • index_subquery
  • range
  • index
  • ALL
• possible_keys：表可能使用到的索引
• key：表实际使用索引
• key_len：索引的长度
• ref：表哪些字段或常量用于连接查找索引上的值
• rows：查询预估返回表的行数
• filtered：表经过条件过滤之后与总数的百分比
• Extra：额外说明信息
  • Using index：覆盖索引
  • Using index condition：条件下推到存储引擎通过索引进行过滤
  • Using where：显示MySQL通过索引条件定位之后还需要返回表中获得所需要的数据
  • Impossible where：where 子句的条件永远都不可能为真
  • Using join buffer：表链接过程当中，将先前表的部分数据读取到join buffer 缓冲区中，然后从缓冲区中读取数据与当前表进行连接
  • Using MRR：读取数据采用多范围读的优化策略
  • Using temporary：MySQL需要创建临时表来存放查询结果集。通常Group by或order by子句中
  • Using filesort：MySQL需要对获取的数据进行额外的一次排序操作，无法通过索引的排序完成。通常发送在有order by 子句的语句当中。

语句执行顺序

• FROM ：对FROM的左边的表和右边的表计算笛卡尔积，产生虚拟表VT1
• ON ：对VT1进行ON筛选，只有那些符合条件的行才会被记录在虚拟表VT2中。
• JOIN ：指定外连接，匹配行作为外部行添加到VT2中，产生VT3。
• WHERE：对VT3进行条件过滤，通过插入到VT4中。
• GROUP BY：对VT4表进行分组操作，产生VT5.
• AVG，SUM：对VT6进行avg，sum操作，产生VT7中。
• HAVING：对VT7进行having过滤，符合插入到VT8中
• DISTINCT：对VT8进行去重操作，产生VT9.
• ORDER BY：对VT9进行排序操作，产生VT10.
• LIMIT：取出指定行记录，产生VT11，并返回结果。

执行器

执行器：获得执行计划，对存储引擎调用统一的数据操作接口。

• InnoDB：
  • 支持外键，事物，保证数据的完整性和一致性
  • 支持更细的锁力度，对锁的控制更好，读写效率更高
• MyISAM：
  • 不支持事物，只支持行锁，适合数据只读的场景
• Memory：
  • 基于内存的，效率高，受内存限制，不支持事务，不支持外键，支持B-Tree索引

InnoDB 存储引擎

Buffer Pool

Buffer Pool是专门用于更新数据使用的缓冲池，毕竟操作内存效率比操作磁盘快很多。

缓存预读机制

局部性原理：附近位置的数据可能很快被用到，所以缓存需要一个预读机制，一次性读取多一些数据到Buffer Pool中，在数据交互时，尽量减少磁盘IO次数，使用到一个空间换时间的思想。

• 线性预读：一次读取一个区Extent的相邻数据加载到Buffer Pool中
• 随机预读：当Page数量达到13时，提前把当前读取的Extent剩下的Page读取到Buffer Pool中

缓存空间

Buffer Pool默认128M，通过热数据区和冷数据区进行优化缓冲池

• Free Page：未使用的页，双向链表Free list表示
• Dirty Page：已修改的页，双向链表 Flush list 表示Change Buffer
  • 当内存的数据和磁盘的数据不一致时，称内存中的数据为脏数据；
  • 把内存中的数据写入到磁盘中，称为刷脏
  • 如何触发刷脏
    • 后台线程定时刷新
    • redo log 满了
    • Buffer Pool 内存不够时
    • Mysql正常关闭时
• Clean Page：已使用的页，LRU链表，有热数据区和冷数据区的双向链表，详细说
  • 设计此数据结构时，需要考虑如何尽可能将热点数据放到缓冲池中
  • 采用了两个LUR的方式进行设计，分别为热数据区和冷数据区，比例5:3
  • 热数据转移规则
    • 通过预读或普通读取，都是将数据页读取到缓冲池中
    • 使用头插法插入到冷数据区中，尾部淘汰
    • 如果冷数据区的数据被访问，存在时间超过1s，那么就移动到热数据区中，尾部淘汰

Log Buffer：Redo Log记录DML操作而不是数据页，所以空间足够的情况下可以容纳大事务中的多个DML操作，并把操作合并，从而提升性能，写入方式为顺序写。

RedoLog：因为change Buf的数据持久化是异步线程完成的，存在数据库崩溃无法持久化的情况，数据看启动时会读取Redo log之前未保存的操作，重新执行一遍，从而实现数据恢复这就是事务持久化的保障机制。

log buf 刷盘机制：

• 0:每次提交事务时，写入LogBuf中，后台线程1s一次刷盘(mysql服务器崩溃会存在1s的数据丢失)
• 1:每次提交事务时，同时刷盘到磁盘中；（默认策略）
• 2:每次提交事务时，同时更新操作系统缓存，后台线程1s刷新到磁盘(操作系统崩溃会导致1s数据丢失)

BinLog：mysql自己维护的一个二进制日志，以事件形式顺序写和追加写记录ddl和dml语句

• 格式：
  • row：记录数据被修改成什么样子
  • statement：记录执行了的sql语句
  • mixed：以上两种格式混合使用
• 重要的功能
  • 数据恢复：记录了DDL和DML语句通过工具来恢复数据
  • 主从同步：bgsave生成rdb文件进行更新数据

RedoLog 和 BinLog 一致性

• 为什么需要双写保证
  • RedoLog保证了主库数据的恢复，BinLog保证了从数据库数据同步的恢复，所以需要保证
  • 2PC提交，遵循XA协议
    • prepare阶段：写入RedoLog
    • commit阶段：写入Binlog

Double Write Buffer

• 为什么需要双写
  • InnoDB pageSize 16KB，将数据写入到磁盘以Page为单位操作的。计算机硬件和操作系统的写文件以4KB为单位，不能保证Mysql数据页面16KB的一次性原子写。
  • 16KB的数据只有部分写到磁盘就崩溃了，这种现象称为partial page writes
• 为什么不能使用redoLog解决 partial page writes
  • redoLog是物理逻辑日志，不是完全幂等的，对insert产生的日志不是幂等的。
• 两次写的工作流程
  • 1、将buffer pool的Dirty page 写入redo-log-buf，并写入redo log中
  • 2、将buffer pool的Dirty page 顺序写入 double write buffer，并写入 double wirete 文件中
  • 3、调用 fsync异步刷新到实际存储物理位置
  • 4、故障恢复时，检测double write buffer与数据页存储位置的内容，若处于页断裂状态则简单的丢弃，若数据页不一致，则从double write 页还原

索引

索引原理：Mysql 中的数据保存在磁盘上，引入索引的目的是提高访问磁盘的效率。

• 索引优点：
  • 减少服务器需要扫描的数据量
  • 帮助服务器避免排序和建立临时表
  • 将 Order by 等随机IO转为顺序IO
• 索引缺点：
  • 创建索引和维护索引耗费时间
  • 索引需要占物理空间
  • 对于非常小的表，大部分情况下贱的的全表扫描更高效
• 索引类型：
  • Hash表：散列算法，把key通过哈希函数计算key物理位置，通过这个地址来获取具体数据。
  • 自适应Hash索引 AHI
    • 自适应：自InnoDB引擎根据查询统计，发现满足Hash索引的数据结构特点，就会建立Hash索引
    • Hash索引适合在内存中使用，所以AHI一般存在于InnoDB的缓存中
    • AHI缺点：
      • AHI会占用 Buffer Pool空间
      • AHI只适合搜索等值的查询，范围查询不使用
  • B树索引
    • 时间复杂度 O(h*logN)
    • 尽可能少的磁盘IO，加快检索速度
    • 可以支持范围查找
  • B+树索引
    • B树的叶子结点存的数据，而B+树存的索引
    • B+树叶子结点存放所有数据，叶子结点之间用一个链表串联起来，便于范围查找
    • B+树层级更少：非叶子结点不存数据，只存索引16K
    • B+树查询速度更加稳定
    • B+树默认排序功能：叶子结点构成一个有序链表
    • B+树全结点便利更快：叶子结点有序链表，有利于全表扫描
• MyISAM引擎（非聚簇索引）
  • .frm（创建表语句）.MYD（数据文件） .MYI（索引文件）
  • 叶子结点记录对应数据的物理地址
• InnoDB引擎（聚簇索引）
  • .frm（创建表语句）.idb（数据+索引文件）
  • 叶子结点直接存储数据记录（需要回表）
• InnoDB索引和MyISAM索引对比
  • MyISAM查询性能更好，而InnoDB需要回表的动作

索引分类

• 按数据结构分类：
  • B+树索引
  • Hash索引
  • R树索引：一般不使用（geomery类型）范围查询
  • 全文索引
• 按物理存储方式分类
  • 聚簇索引：索引结构和数据一起存放的索引（主键索引）
    • 叶子结点存放数据，叶子结点有一个链表串联起来
  • 非聚簇索引：索引结构和数据分开存放的索引（二级索引）
    • 叶子结点不存放数据，存放的引用地址，需要回表查询数据
• 按应用维度分类
  • 主键索引
  • 普通索引
  • 唯一索引
  • 覆盖索引
  • 联合索引
  • 全文索引

索引失效

• 字段隐式转换
• 条件包含 or
• like %%没有最左匹配
• 联合索引没有最左匹配
• 索引列使用函数或计算
• 使用 not in、!=、 <>
• 使用is null，is not null
• 使用了 order by 没有索引

Mysql 事务

事务是一种操作序列，这些操作要么都执行，要么都不执行，是一个不可分割的工作单位。

ACID特点

• 原子性（Atomicity）事务时一个不可分割的工作单位，要么都发生，要么都回滚。
• 一致性（Durable）事务开始之前和结束之后，数据的完整性约束没有被破坏。
• 持久性（Consistent）一旦事务完成提交，其结果就是永久性，即使系统故障也能恢复。
• 隔离性（Isolation）指在并发环境中，不同事物同时操作相同的数据，每个事物都有各自完整的数据空间。

事务隔离级别

• 未提交读RU
• 已提交读RC：可解决脏读问题或者使用MVCC解决
  • 脏读：T2读取到T1未提交的数据（回滚）
• 可重复读RR：可解决不可重复读问题或者使用MVCC
  • 不可重复读：多次读取同一条数据，两次的结果不一致
• 串行化（会锁表）：可解决幻读问题或者使用MVCC+锁
  • 幻读：多次范围读取数据，读取的数据不一致

MVCC 多版本并发控制

• 隐藏字段：
  • trx_id：事务版本号：每次启动事务都会有一个自增事务ID，判断先后顺序
  • roll_pointer：回滚指针
  • row_id：如果没有主键那么会有第三个隐藏列
• 版本链：多个事务并行操作某一行数据时，会产生多个版本，通过回滚指针连成一个链表（版本链）
• Read View：事务执行时会产生读视图，可见性判断，判断当前事务可见哪个版本的数据（一句话总结，事务开启时，可以读到的最大事务ID，如果后面还有更大的事务ID生成则看不到）
  • m_ids:当前系统活跃的读写事务ID
  • min_limit_id：当前系统活跃的读写事务最小ID
  • max_limit_id：当前系统活跃的读写事务最大ID
  • creator_trx_id：创建当前ReadView的事务ID
• 当前读：读取的记录数据最新的版本，显式加锁的都是当前读
• 快照读：读取的记录数据可见版本（旧版本）不加锁，普通的select都是快照读（Undo log）
• 执行流程
  • 事务A开启，得到事务100
  • 事务B开启，得到事务101
  • 事务A生成一个ReadView
  • 事务B进行修改操作，把名字张三改为李四
    • 原数据拷贝到undo log中，然后对数据进行修改，标记事务ID和上一个数据版本在undolog的地址
  • 提交事务B
  • 事务A再次执行查询操作，会新生成一个ReadView，从版本链查看可见记录
  • 总结：事务A查看ReadView时，事务A比事务B早，所以查不到事务B的更新数据。固事务A开启之后查询几次都是读取同一个ReadView版本（name=“张三”）

Mysql 锁

• 模式分类
  • 乐观锁
  • 悲观锁
• 粒度分类
  • 全局锁
  • 表锁
  • 页级锁
  • 行级锁
• 属性分类
  • 共享锁（S）
  • 排他锁（X）
• 状态分类
  • 意向共享锁
  • 意向排他锁
• 算法分类
  • 间隙锁
  • 临键锁
  • 记录锁