Skip to content

Latest commit

 

History

History
599 lines (582 loc) · 39.7 KB

mysql.md

File metadata and controls

599 lines (582 loc) · 39.7 KB
Raw

mysql

1.基本信息

  1. 连接管理
  2. 解析与优化
    1. 查询缓存-不推荐使用,mysql8.0删除
    2. 语法解析-检查语法
    3. 查询优化-优化sql,生成执行计划
  3. 存储引擎
  4. 字符集
    1. mysql中的utf8是阉割版的utf8mb3,使用1-3个字节表示字符
    2. utfmb4使用1-4个字节表示字符

引擎

InnoDB

  1. 以页为单位进行数据交换,InnoDB页一般为16kb

行格式

  1. Compact
    1. 变长字段长度列表+null值列表+记录头信息+记录的真实数据
    2. 变长字段长度列表
      1. 变长字段数据占用字节长度逆序排列
      2. 值为null的列字节长度不记录
      3. 没有变长字段则不用该结构
    3. null值列表
      1. 逆序排列
      2. 如果无存储null的列,则不用该结构
      3. 用整个字节表示,高位补0
    4. 记录头信息
      1. 固定5字节组成
      2. delete_mask
        1. 标识该记录是否被删除(0:未删除 1:已删除)
        2. 被删除的记录不会马上从磁盘移除(重新排列需要性能消耗)
        3. 页中有多个删除的行会形成一个垃圾链表,占用的空间为可重用空间
        4. 如果有新记录插入,会覆盖该行,进行复用
      3. min_rec_mask
        1. B+树的每层非叶子节点中的最小记录都会添加该标记
      4. n_owned
      5. 代表所在槽(Slot)的记录数目
      6. 如果不是所在组的最后一条则为0
      7. heap_no
        1. 表示该记录在本页的位置
        2. InnoDB会自动在每页插入2个条虚拟记录
        3. 最小记录:0 最大记录:1
      8. record_type
        1. 表示当前记录的类型
        2. 0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录
      9. next_record
        1. 当天记录到下一条记录的偏移量,按照主键从小到大形成一个单链
      10. 优点:向左读是记录头信息,向右读是真实数据.
      11. 而变长字段长度列表和null值列表都是逆序存放的,与对应的真实数据距离更近,提高缓存命中率
    5. 记录的真实数据
      1. 隐藏列
        1. 行ID-非必须(已指定主键或有Unique的字段)
        2. 事务ID-指向记录对应的undo日志的一个指针
        3. 回滚指针
      2. null值不会重复存储
      3. 使用非定长字符集(utf-8)时,char(m)即使只有一个字符也占有10个字节,避免空间碎片,同时会被记录到变长字段长度列表
  2. Redundant
    1. 字段长度偏移列表+记录头信息+记录的真实数据
    2. mysql5.0之前的格式
    3. 把所有列(包括隐藏列)的长度信息逆序存到字段长度偏移列表(第一个比特位存储null标识)
    4. 记录头中1byte_offs_flag表示偏移量是按照1字节还是2字节表示(因为偏移量第一位被用作展示null标识位)
    5. 存储null值的字段是定长类型(char(m)),null也占用空间.变长类型则不占用.
    6. char(m),占用的真实数据空间字符集大小*m.
  3. Dynamic
    1. mysql5.7默认行格式
    2. 行溢出直接存储溢出页空间地址
  4. Compressed
    1. 和Dynamic相同,但会压缩空间
  5. 行溢出
    1. 一个行中所有列(不包含隐藏列和记录头信息)占用的字节长度不可超过65535个字节(最大行长度).(需要考虑字符集大小以及null值标识)
    2. 一页大小16kb(16384字节),单页无法满足.
    3. Compact与Redundant(字符串前768字节+溢出页地址),Dynamic与Compressed(溢出页地址)

页结构

  1. 页有不同类型(表空间头部信息页,undo日志,存放记录的索引(index)页等)
  2. 文件头-页面头-最小最大记录-用户记录-空闲空间-页目录-文件尾
  3. File Header（文件头部）
    1. 固定38字节,页的通用信息
    2. FIL_PAGE_SPACE_OR_CHKSUM-当前页校验和,便于比较
    3. FIL_PAGE_OFFSET-页号
      1. 4个字节组成=32比特位,所以一个表空间最多有2^32个页
      2. 从0开始,一个表空间最多支持64TB的数据(16kb每页)
    4. FIL_PAGE_TYPE-页类型
    5. FIL_PAGE_PREV和FIL_PAGE_NEXT代表本页的上一个和下一个页的页号,形成双链表
  4. Page Header（页面头部）
    1. 固定56字节,存储各种状态信息
    2. PAGE_DIRECTION-记录插入方向
    3. PAGE_N_DIRECTION-一个方向连续插入的记录数量
    4. PAGE_N_DIR_SLOTS-在页目录中的槽数量
  5. Infimum + Supremum(最小记录和最大记录)
  6. User Records(用户记录,实际存储的行记录内容)
  7. Free Space(空闲空间)
  8. Page Directory(页目录)
    1. 由槽(Slot)组成
    2. 将所有记录(包括最大/最小记录,不包括删除)划分为几个组
      1. 最小记录的分组只有一条记录
      2. 最大记录的分组记录(1-8条)
      3. 剩下分组的记录数(4-8条)
    3. 每个组的最后一条记录的记录头中的地址偏移量单独存储到槽中
    4. 一个数据页中查找指定主键值的记录-二分法找到记录所在槽以及主键值最小的记录-根据记录的地址偏移量遍历所在组各个记录
  9. File Trailer(文件尾)
    1. 8个字节组成,页的通用信息
    2. 前4个字节和文件头的校验和对应
      1. 页数据从内存同步到磁盘,从文件头先同步
      2. 如果同步成功,页的收尾校验和应该一致
    3. 后4个字节代表页面被最后修改时对应的日志序列位置(LSN)-校验页完整性

B+树索引

  1. 结构
    1. 下一个数据页中的用户记录的主键需要大于上一个页
    2. 页内的记录按照主键大小形成单向链表
    3. 各个数据页根据主键大小形成双向链表
    4. 数据页有不同层次,同一个层次为双向链表
    5. 根节点生成后,不会再移动
    6. 空间代价:每建立一个索引,需要占用许多页空间
    7. 时间代价:增删改会影响节点,每个索引需要维护
    8. 回表:利用索引完成查询后,根据主键去聚簇索引补齐数据
  2. 聚簇索引
    1. 叶子节点会存储完整的数据
    2. InnoDB存储引擎会自动创建聚簇索引
    3. 索引即数据,数据即索引
  3. 二级索引
    1. 使用非主键字段排序建立B+树
    2. 叶子节点除了索引字段还包括主键字段,是为了除了页号外有唯一字段来划分页
  4. 联合索引
    1. 以多个列排序建立索引(也是二级索引)
    2. 查询需要利用上前序索引
  5. 索引使用
    1. where语句查询条件顺序无影响,mysql的查询优化器会先优化
    2. 联合索引生效或范围查找,需要有索引中最左侧的列
    3. 排序列使用复杂函数会无法利用索引
    4. 回表的代价
      1. 顺序I/O,在二级索引内存储相连,速度较快
      2. 随机I/O,在聚簇索引中数据页较多
      3. 数据较少时,回表效率高.字段较多,可能会采用聚簇索引.mysql会进行分析
    5. 文件排序-filesort,查询结果集太大,无法在内存中完成排序,需要在磁盘中存放中间结果
    6. 覆盖索引-二级索引完成满足查询字段
    7. is null/ is not null / != 也可以用到索引,mysql定位null是最小值
    8. 如何使用索引
      1. 只针对搜索,排序或分组的字段建立索引
      2. 字段数据重复度高的不适合索引
      3. 索引字段的类型小
        1. 数据类型小,查询较快(cpu层面)
        2. 数据类型小,占用空间小.单个页存储记录多,减少I/O消耗
        3. 内存中可加载更多数据页
      4. 可只针对字符串前几个字符进行索引
      5. 比较表达式中索引字段不可使用函数方法
      6. 主键使用自增-B+数的叶子节点是按照主键顺序排列,减少页面分裂和排序
      7. 避免冗余和重复索引

MyISAM

  1. 索引是索引,数据是数据
  2. 按照记录的插入顺序单独存储在一个数据文件(不主动排序),不划分若干页,通过行号迅速定位
  3. 单独为表的主键创建一个索引,叶子节点为主键值+行号-通过主键找到行号,再通过行号找到对应记录
  4. 定长记录格式时,会在索引叶子节点存储记录在数据文件的地址偏移量,直接读取数据

二者区别

  1. InnoDB索引即数据,聚簇索引已经包含全部记录.MyISAM索引和记录分开存储
  2. InnoDB是默认的事务型存储引擎,MyISAM不是
  3. InnoDB支持表锁/行锁,MyISAM支持表锁
  4. InnoDB支持外键
  5. 清空整表,InnoDB一行行删除,MyISAM则会重新建表
  6. select count(*), InnoDB需要扫描整表,MyISAM直接读取行号
  7. MyISAM插入不频繁,查询频繁

数据目录

  1. 数据库
    1. 每个数据库在数据目录下创建同名的子目录
    2. 子目录下新建一个db.opt文件-数据库各种属性
  2. 表结构: 数据目录下新建一个专门描述表结构的文件-表名.frm,二进制存储
  3. 其他文件-服务器进程文件/服务器日志文件/证书和密钥文件
  4. 表中的数据
    1. InnoDB
      1. 表空间是抽象概念-对应文件系统中的多个文件
      2. 版本5.5.7-5.6.6 系统表空间
      3. 版本5.6.6-独立表空间-每个表建立一个独立空间
      4. 其他类型:通用表,undo表空间,临时表空间
    2. MyISAM
      1. 没有表空间概念,数据都存储在系统子目录下
    3. 视图
      1. 虚拟表,实际上查询语句的表名
      2. 存储视图不存储数据,存储结构就行了
      3. 系统子目录下会存储一个视图名.frm
  5. mysql系统数据库
    1. mysql:账号信息,日志信息,时区信息
    2. information_schema:元数据-表,识图,索引
    3. performance_schema:性能监控
    4. sys:识图(information_schema+performance_schema:性能监控)
  6. 区(extent)
    1. 为了便于管理表空间提出的一个概念
    2. 64个页组成一个区
      1. 物理上连续的64个页
      2. 在表中数据较多时,为索引分配空间会使用区做为单位,数据更大时会一次性分配多个区
      3. 这样一个索引的数据页是物理连续的,形成顺序I/O,避免大量的随机I/O
    3. 256个区组成一个组
    4. extent0区的前3个页类型是固定的
      1. FSP_HDR(登记整个表空间的一些整体属性以及本组所有的区),整个表空间只有一个FSP_HDR类型的页面
        1. File Space Header-表空间头部
          1. List Base Node for FREE List/List Base Node for FREE_FRAG List/List Base Node for FULL_FRAG List 区的3种状态链表
          2. FRAG_N_USED: 表明在FREE_FRAG链表中已经使用的页面数量，方便之后在链表中查找空闲的页面
          3. FREE Limit: 在该字段表示的页号之前的区都被初始化了，之后的区尚未被初始化.在表空间初始化时,并未把所有的区加入到FREE链表中
          4. Space Flags: 表空间的一些布尔类型的属性
          5. List Base Node for SEG_INODES_FULL List/List Base Node for SEG_INODES_FREE List 每个段对应的INODE Entry结构会集中存放到一个类型位INODE的页中，如果表空间中的段特别多，则会有多个INODE Entry结构，可能一个页放不下
      2. IBUF_BITMAP(存储本组所有的区的所有页面关于INSERT BUFFER的信息)
      3. INODE
        1. NODE类型的页就是为了存储INODE Entry结构而存在的
        2. List Node for INODE Page List: 当表空间的段超过85个,一个INODE类型的页无法满足存储,该字段用于执行下一个INODE类型的页
    5. 其余各组最开始区的前2个页类型是固定的
      1. XDES(和FSP_HDR类似,不过没有记录表空间整体属性(少了File Space Header))
        1. XDES Entry
          1. 一共256个XDES Entry(Extent Descriptor Entry)结构,,分别对应组内的每一个区
          2. Segment ID（8字节）:该区所在的段ID,若没分配,则没有
          3. List Node（12字节）:将若干个XDES Entry结构串联成一个链表.根据链表的类型不同,来有效快速分配区空间
            1. 页号和在指定页号中的页内偏移量
            2. 如果我们想定位表空间内的某一个位置的话,只需指定页号以及该位置在指定页号中的页内偏移量即可
          4. State（4字节）:表明区的状态
            1. FREE-空闲的区
            2. FREE_FRAG-有剩余空间的碎片区
            3. FULL_FRAG-没有剩余空间的碎片区
            4. FSEG-附属于某个段的区
          5. Page State Bitmap(16字节):代表一个区内的64个页的使用情况
        2. 为了分配数据更快高效,避免遍历所有区来判断空间,将不同状态的区使用List Node结构形成一个链表
          1. 直属于表空间,存储在表空间的第一个区的表空间头部中
          2. FREE链表/FREE_FRAG链表/FULL_FRAG链表
          3. 插入数据时,从FREE_FRAG链表的头结点去除空闲的页
          4. 当节点区空间用完,则修改状态,并移动到FULL_FRAG链表
      2. IBUF_BITMAP
    6. 区的状态
      1. FREE-空闲的区
      2. FREE_FRAG-有剩余空间的碎片区
      3. FULL_FRAG-没有剩余空间的碎片区
      4. FSEG-附属于某个段的区
  7. 段(segment)
    1. 为了再次提高效率,把叶子节点的页和非叶子节点的页分别存在在不同的区中
    2. 存放叶子节点的区的集合就算是一个段,存放非叶子节点的区的集合也算是一个段
    3. 也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段
      1. INDEX类型的数据页的页头中PAGE_BTR_SEG_LEAF和PAGE_BTR_SEG_TOP对应Segment Header结构存储着段的位置信息
        1. PAGE_BTR_SEG_LEAF B+树叶子段的头部信息，仅在B+树的根页定义
        2. PAGE_BTR_SEG_TOP B+树非叶子段的头部信息，仅在B+树的根页定义
      2. 为了便于管理段内的区,每一个段会有代表3个区空间信息的链表
      3. 段包含的区对应的XDES Entry中的List Node形成链表
      4. FREE链表/NOT_FULL链表/FULL链表
    4. 除了索引的叶子节点段和非叶子节点段之外，InnoDB中还有为存储一些特殊的数据而定义的段，比如回滚段
    5. 碎片区(fragment)
      1. 在表中刚插入数据时,按完整的区分配给段是十分浪费空间的,所以提出了碎片区（fragment）的概念
      2. 碎片区中的页可以用于不同的目的
      3. 比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于
      4. 碎片区直属于表空间，并不属于任何一个段
    6. 某个段分配存储空间的策略
      1. 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的
      2. 当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间
    7. 关于段更精确的描述应该是某些零散的页面以及一些完整的区的集合
    8. INODE Entry 段的存储结构
      1. Segment ID: 段号
      2. NOT_FULL_N_USED: 在NOT_FULL链表中已经使用了多少个页面 下次从NOT_FULL链表分配空闲页面时可以直接根据这个字段的值定位到
      3. 3个List Base Node(链表基节点) 表空间中固定位置加快定位 分别为段的FREE链表、NOT_FULL链表、FULL链表
        1. List Length(链表包含节点数)
        2. First Node Page Number和First Node Offset表明该链表的头节点在表空间中的位置
        3. Last Node Page Number和Last Node Offset表明该链表的尾节点在表空间中的位置
      4. Magic Number:表示这个段是否已初始化
      5. Fragment Array Entry:每个Fragment Array Entry结构都对应着一个零散的页面
  8. 系统表空间
    1. 整个MySQL进程只有一个系统表空间,额外记录一些有关整个系统信息的页面
    2. 表空间 ID（Space ID）是0
    3. 双写缓冲区: 系统表空间的extent 1和extent 2这两个区，也就是页号从64~191这128个页面被称为Doublewrite buffer
    4. InnoDB数据字典: 以B+树的形式保存在系统表空间的某些页面中
    5. Data Dictionary Header: 记录一些基本系统表的根页面位置 以及InnoDB存储引擎的一些全局信息
    6. 由于系统表空间不提供访问,提供了information_schema系统数据库

访问类型

  1. const: 常数类型
  2. ref: 使用索引进行等值比较
  3. ref_or_null: 除了等值记录,null也要
  4. range: 使用索引进行范围匹配
  5. index: 使用索引即可完成查询,不需要回表
  6. index merge: 索引合并
    1. Intersection合并(交集)
    2. Union合并(并集)
    3. Sort-Union合并
    4. 都是需要查询优化处理后才能确定方案
  7. all: 全表

连接

  1. 驱动表/被驱动表-两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次
  2. 内连接和外连接
    1. 内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时 不会把该记录加入到最后的结果集
    2. 内连接
      1. 内连接中的WHERE子句和ON子句是等价的
      2. 对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果
      3. SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2
    3. 外连接
      1. 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。
      2. 左外连接:选取左侧的表为驱动表
      3. 右外连接:选取右侧的表为驱动表
  3. 连接的原理
    1. 嵌套循环连接（Nested-Loop Join）-驱动表只访问一次,被驱动表访问多次
    2. 使用索引加快连接速度-eq_ref-被驱动表使用主键或者唯一二级索引
    3. 基于块的嵌套循环连接（Block Nested-Loop Join）
      1. 每次被驱动表查询需要从磁盘获取结果,效率太低
      2. 提出join buffer的空间,将驱动集的结果放在其中,减少I/O
      3. 不要select *,节省join buffer空间

成本

  1. 执行成本 I/O成本 CPU成本
  2. 步骤
    1. 根据搜索条件，找出所有可能使用的索引
    2. 计算全表扫描的代价
    3. 计算使用不同索引执行查询的代价
    4. 对比各种执行方案的代价，找出成本最低的那一个

InnoDB 统计数据

  1. InnoDB提供了两种存储统计数据的方式
    1. 永久性的统计数据-磁盘 MySQL 5.6.6之后默认
    2. 非永久性的统计数据-内存
  2. 以表为单位来收集和存储统计数据的
  3. 都是估计值,而不是准确数值
  4. 可以定期更新统计数据
    1. 开启innodb_stats_auto_recalc,当表中改动数据超过10%时,更新统计数据-异步
    2. 手动更新统计数据

查询重写/查询优化

  1. 查询重写
    1. 移除不必要的括号
    2. 常量传递
    3. 等值传递
    4. 移除没用的条件
    5. 表达式计算
    6. HAVING子句和WHERE子句的合并
    7. 常量表检测
    8. 外连接消除:右连接->左连接->补充is not null->内连接
  2. 查询优化
    1. in子查询优化-将子查询结果集写入到临时表
      1. 写入临时表的记录会被去重
      2. 建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引
      3. 子查询的结果集非常大,则改为使用B+树存储索引
      4. 将子查询结果集中的记录保存到临时表的过程称之为物化（英文名：Materialize）

Explain

optimizer trace 表

InnoDB 的 Buffer Pool(缓冲池)

  1. InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时,就会把完整的页的数据全部加载到内存中
  2. 即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中
  3. Buffer Pool结构: 控制卡-碎片-缓存页
  4. 为了管理Buffer Pool的使用情况->free链表: 单独申请一个内存空间作为基节点 当需要使用缓存时,则从链表中取出一个并移除
  5. 如何快速确定某个表空间下某个页是否依旧被缓存
    1. 用表空间号 + 页号作为key，缓存页作为value创建一个哈希表
  6. 修改了某个缓存页的数据-->脏页(dirty page),为了管理脏页-->flush链表
    1. 后台线程定时从LRU链表的冷数据中刷新一部分脏页到磁盘
    2. 后台线程定时从flush链表中刷新一部分页面到磁盘
    3. 从磁盘读取数据页时,Buffer Pool已满则从LRU链表的冷数据中刷新一部分脏页到磁盘
  7. LRU链表(Least Recently Used)
    1. Buffer Pool空间有限,为了提高利用率,避免无用数据一直占着空间
    2. 全表扫描与预读机制会影响LRU效率
    3. 分为2部分,热数据(young区域)/冷数据(old区域)
    4. 预读-->某个页面初次加载,对应的控制块放在old区域的头部,这样就不影响young区域
    5. 全表-->old区域的缓存页被使用时记录下访问时间,如果在一定时间间隔内再次访问,则还保持在old区域
      1. 因为全表扫描读取的非常快
    6. 为了避免频繁移动节点,只有被访问的缓存页位于young区域的1/4的后边,才会被移动到LRU链表头部
  8. 在Buffer Pool特别大而且多线程并发访问特别高的情况下,会申请多个Buffer Pool
  9. MySQL 5.7.5之后支持在系统运行期间调整Buffer Pool大小,以chunk为单位申请,也就是说一个Buffer Pool实例其实是由若干个chunk组成的

事务

  1. ACID
    1. 原子性（Atomicity）: 要么完成,要么未操作,不存在中间状态
    2. 隔离性（Isolation）: 保证其它的状态转换不会影响到本次状态转换
    3. 一致性（Consistency）: NOT NULL来拒绝NULL值的插入
    4. 持久性（Durability）: 转换对应的数据库操作所修改的数据都应该在磁盘上保留下来

redo日志

  1. 持久性要求保证对于一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失
  2. 如果在事务提交完成之前把所修改的所有页面都刷新到磁盘
    1. 改动数据很少,但需要把整个页面刷回磁盘太浪费性能
    2. 涉及多个页面,随机I/O效率低
  3. 可以只记录事务中改动的内容本身,然后存储到磁盘-->redo日志
    1. redo日志占用的空间非常小
    2. redo日志是顺序写入磁盘的
  4. redo日志有许多类型用于针对不同的记录修改case(非紧凑行格式/紧凑行格式/创建/删除等)
  5. 物理层面: redo日志指明了对哪个表空间的哪个页进行了修改
  6. 逻辑层面: 系统奔溃重启时,不能直接依赖redo日志恢复,而是系统调用底层函数,而redo日志的数据会作为入参
  7. Mini-Transaction-mtr
    1. 一个事务内会有多条redo日志,同时为了保证原子性,会将这些redo作为一个组,其中最后一个redo日志类似是MLOG_MULTI_REC_END
    2. mtr生成的redo日志存在磁盘的redo log block页中
  8. redo日志缓冲区-redo log buffer
    1. 写入redo日志时也不能直接直接写到磁盘上
    2. 连续的redo log block
    3. mtr结束时会被复制到log buffer中
    4. buf_free的全局变量，该变量指明后续写入的redo日志应该写入到log buffer中的哪个位置
    5. 不同事务的mtr会交叉存入
  9. redo日志刷盘时机
    1. log buffer空间不足时
    2. 事务提交时->保证持久性
    3. 脏页刷新到磁盘前,会检查是否存在对应的redo日志
    4. 后台线程刷新
    5. 关闭服务器时
    6. 做checkpoint时
  10. 磁盘上的redo日志文件
    1. 磁盘上的redo日志文件不只一个，而是以一个日志文件组的形式出现的
    2. log buffer中的redo日志刷新到磁盘的本质就是把block的镜像写入日志文件中
    3. 日志文件其实也是由若干个512字节大小的block组成
    4. redo日志文件组中的每个文件大小都一样，格式也一样
      1. 前2048个字节，也就是前4个block是用来存储一些管理信息的
      2. 从第2048字节往后是用来存储log buffer中的block镜像的
  11. lsn-Log Sequence Number-日志序列号 在log buffer中
  12. 用于标记redo日志的生成顺序先后,lsn值越小，说明redo日志产生的越早
  13. 每一组由mtr生成的redo日志都有一个唯一的LSN值与其对应
  14. flushed_to_disk_lsn-表示刷新到磁盘中的redo日志量位置的全局变量
  15. mtr结束时，会把一组redo日志写入到log buffer中,同时也要更新buffer ppol中的flush链表(脏页)
    1. flush链表中的脏页按照修改时间倒序,被多次更新的页面不会重复插入,而是更新最后更新时间(实际是记录redo日志的lsn值)
  16. checkpoint
  17. 磁盘上的redo日志文件组大小有限
  18. checkpoint用于判断某些redo日志占用的磁盘空间是否可以覆盖
  19. redo日志可以被覆盖，意味着它对应的脏页被刷到了磁盘-->检查flush链表最后一个控制块的最后更新时间(lsn值)-->磁盘中lsn小于这个值的都可以覆盖
  20. 定义一个全局变量checkpoint_lsn并将赋值上一步的lsn记录,到redo日志文件的头部信息
  21. 有另外一个全部变量checkpoint_no,每发生一次checkpoint则+1,当checkpoint_no的值是偶数时，就写到checkpoint1中， 是奇数时，就写到checkpoint2中
  22. 用户线程也可以主动批量从flush链表中刷出脏页
  23. 崩溃恢复
    1. 确定起点-redo日志文件头中有记录checkpoint1和checkpoint2,哪个更大则就是起点
    2. 确定终点-block的头部信息中有一个字段用于标识该block是否已经占满
    3. 加快恢复
      1. 使用表空间ID和页号计算出散列值,形成哈希表.这样可以将同一个表空间和页号的redo日志排序,提高顺序I/O使用率
      2. 2.跳过已经刷新到磁盘的页面.页面头信息有一个字段存储最后一次修改的lsn值,如果redo日志的值小于这个值,则无需刷新

undo日志

  1. 为了回滚而记录的日志
  2. 事务Id-trx_id
    1. 在行记录的隐藏列
    2. 服务器会在内存中维护一个全局变量,每次分配事务后,会自增加1
    3. 每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间的页号为5的页面中一个称之为Max Trx ID的属性处，这个属性占用8个字节的存储空间
    4. 当系统下一次重新启动时，会将上边提到的Max Trx ID属性加载到内存中，将该值加上256之后赋值给我们前边提到的全局变量
  3. undo日志对应类型为FIL_PAGE_UNDO_LOG的页格式
  4. 修改数据的动作实际会向聚簇索引和二级索引都修改,但由于其他索引中包含主键id,实际只需针对聚簇索引的动作即可
  5. DELETE操作对应的undo日志
    1. 将记录的delete_mask标识位设置为1,修改记录的trx_id、roll_pointer这些隐藏列的值
    2. 当该删除语句所在的事务提交之后，会有专门的线程后来真正的把记录删除掉.把该记录从正常记录链表中移除，并且加入到垃圾链表中
    3. 页面的Page Header部分有一个PAGE_GARBAGE属性用于记录页面内的可重用存储空间的字节数
      1. 每当有已删除记录被加入到垃圾链表后,都会把这个PAGE_GARBAGE属性的值加上该已删除记录占用的存储空间大小
      2. 新记录插入会检查可重用空间是否足够容纳,如果不够,则申请新的空间
      3. 如果足够,则利用可重用空间-->产生空间碎片
      4. 当空间碎片不足以容纳新的记录,则会开辟一个新的临时页面空间依次存储记录,再复制回原页面,来消灭碎片空间(耗费性能)
  6. UPDATE操作对应的undo日志
    1. 不更新主键
      1. 就地更新（in-place update）- 如果更新后的列和更新前的列占用的存储空间都一样大
      2. 先删除掉旧记录，再插入新记录-此处删除并不是delete mark操作,而是直接加入垃圾链表
    2. 更新主键
      1. 将旧记录进行delete mark操作(因为别的事务同时也可能访问这条记录,所有不能直接删除)
      2. 根据更新后各列的值创建一条新记录，并将其插入到聚簇索引中（需重新定位插入的位置）
  7. 存储结构
    1. Undo Page Header
      1. TRX_UNDO_PAGE_TYPE: 页面存储的undo日志类型(TRX_UNDO_INSERT大类/TRX_UNDO_UPDATE大类)-->并不是按照语义理解类型
        1. 不同大类的undo日志不能混着存储,除了INSERT类型的undo日志在事务提交后可以删除,其他类型的undo日志还需要为MVCC服务
      2. TRX_UNDO_PAGE_START : 代表页面从哪个位置开始存储undo日志
      3. TRX_UNDO_PAGE_FREE : 代表页面最后一个undo日志的在页面中的偏移量
      4. TRX_UNDO_PAGE_NODE : 代表一个List Node结构
    2. Undo页面链表
      1. 一个事务可能包含多个语句,产生很多undo日志,单个页面无法存储,此时多个页面形成一个链表
      2. 第一个Undo页面会额外存储一些管理信息-->段信息
      3. INSERT大类和UPDATE大类的undo日志会单独存储-->2条链表
      4. 普通表和临时表的undo日志单独存储-->多个链表
      5. 每个事务是独立的-->又是多个链表
    3. Undo Log Segment Header
      1. 在链表的头页面中
      2. 每一个Undo页面链表都对应着一个段
      3. undo页面链表的基节点存储在这
    4. Undo Log Header: undo日志一些信息,涉及到多事务
    5. 重用undo日志
      1. 链表中只包含一个Undo页面
      2. Undo页面已经使用的空间小于整个页面空间的3/4
    6. 回滚段-Rollback Segment Header
      1. 存储了不同undo页面链表的frist undo page的页号,对所有的undo页面进行分配和管理
      2. 系统表空间第5号页面存储了128个回滚段的页面地址,每个Rollback Segment Header就相当于一个回滚段
      3. 在Rollback Segment Header页面中，又包含1024个undo slot，每个undo slot都对应一个Undo页面链表
      4. 针对普通表和临时表划分不同种类的回滚段的原因
        1. 在修改针对普通表的回滚段中的Undo页面时，需要记录对应的redo日志
        2. 而修改针对临时表的回滚段中的Undo页面时，不需要记录对应的redo日志

事务隔离级别和MVCC

  1. 事务并发时会遇到的问题
    1. 脏写: 一个事务修改了另一个未提交事务修改过的数据-->通过锁解决
    2. 脏读: 一个事务读到了另一个未提交事务修改过的数据
    3. 不可重复读: 一个事务只能读到另一个已经提交的事务修改过的数据,并且其他事务每对该数据进行一次修改并提交后,该事务都能查询得到最新值
    4. 幻读: 一个事务先根据某些条件查询出一些记录,之后另一个事务又向表中插入了符合这些条件的记录,原先的事务再次按照该条件查询时,能把另一个事务插入的记录也读出来
  2. SQL标准中的四种隔离级别
    1. READ UNCOMMITTED：未提交读-->可能发生脏读、不可重复读和幻读问题
    2. READ COMMITTED：已提交读-->可能发生不可重复读和幻读问题
    3. REPEATABLE READ：可重复读-->可能发生幻读问题-->mysql默认,可依赖MVCC机制解决幻读问题
    4. SERIALIZABLE：可串行化-->各种问题都不可以发生
  3. MVCC-Multi-Version Concurrency Control,多版本并发控制
    1. 版本链-不同事务对于同一条记录的修改生成的undo日志会使用隐藏字段roll_pointer连接起来,同时对应的事务id-trx_id也记录下来
    2. ReadView
      1. m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
      2. min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
      3. max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
      4. creator_trx_id：表示生成该ReadView的事务的事务id。
      5. 根据事务id进行比较规则是否可读到
    3. READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同
    4. 执行DELETE语句或者更新主键的UPDATE语句并不会立即把对应的记录完全从页面中删除，而是执行一个所谓的delete mark操作，相当于只是对记录打上了一个删除标志位，这主要就是为MVCC服务的
    5. insert undo在事务提交之后就可以被释放掉了，而update undo由于还需要支持MVCC，不能立即删除掉

锁

  1. 怎么解决脏读、不可重复读、幻读
    1. 方案一：读操作利用多版本并发控制（MVCC），写操作进行加锁
    2. 方案二：读、写操作都采用加锁的方式
  2. 一致性读（Consistent Reads）
    1. 事务利用MVCC进行的读取操作称之为一致性读，或者一致性无锁读，有的地方也称之为快照读
    2. 并不会对表中的任何记录做加锁操作，其他事务可以自由的对表中的记录做改动
  3. 共享锁，英文名：Shared Locks-->S锁
  4. 独占锁，也常称排他锁，英文名：Exclusive Locks-->X锁
  5. 表级锁也有共享锁和独占锁
  6. 为了在加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁,会有一个意向锁的概念
  7. 存储引擎中的锁
    1. 对于MyISAM、MEMORY、MERGE这些存储引擎来说,只支持表级锁,而且这些引擎并不支持事务,所以使用这些存储引擎的锁一般都是针对当前会话来说的
    2. InnoDB的表级锁
      1. 对某个表执行SELECT、INSERT、DELETE、UPDATE语句时,并不会有表锁
      2. 表级别的意向锁-IS锁、IX锁-对行记录加锁前,会对表加一个意向锁,用于后面加表锁时需要遍历才知道有没有行记录加锁
      3. 执行DDL语句时,并发事务会阻塞
      4. 表级别的AUTO-INC锁(自动递增)
        1. 采用AUTO-INC锁，也就是在执行插入语句时就在表级别加一个AUTO-INC锁
        2. 采用一个轻量级锁,在为插入语句生成AUTO_INCREMENT修饰的列的值时获取一下这个轻量级锁-可能导致不同事务的插入语句的id数值交叉
    3. InnoDB的行级锁
      1. Record Locks-LOCK_REC_NOT_GAP-正经记录锁-有S锁和X锁之分
      2. Gap Locks-间隙锁-仅仅是使用加锁方案解决幻读时防止插入幻影记录而提出的
      3. Next-Key Locks-LOCK_ORDINARY-本质就是一个正经记录锁和一个gap锁的合体
      4. Insert Intention Locks-插入意向锁-插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁,表明有事务想在某个间隙中插入新记录，但是现在在等待
      5. 隐式锁-一个事务对新插入的记录可以不显式的加锁（生成一个锁结构),而是利用事务Id,别的事务在对这条记录加S锁或者X锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构,然后自己再生成一个锁结构后进入等待状态
    4. 存储
      1. 符合一定条件的锁信息是放在同一个锁结构中的
      2. 在同一个事务中进行加锁操作
      3. 被加锁的记录在同一个页面中
      4. 加锁的类型是一样的
      5. 等待状态是一样的

其他

  1. select count
    1. COUNT函数的参数可以是任意表达式，该函数用于统计在符合搜索条件的记录中，指定的表达式不为NULL的行数有多少
    2. COUNT函数的参数是*，MySQL会将*当作常数0处理
    3. COUNT(*)、COUNT(常数)、COUNT(主键)形式,执行过程是一样的,代价也一样,判断过程略有不同
    4. COUNT(非主键列),server层面的优化器会根据成本来选择
    5. 是在server层面完成计数,引擎层面一次查询一条
  2. limit
    1. MySQL中是在实际向客户端发送记录前才会去判断LIMIT子句是否符合要求,比如limit 5000,1,需要浪费前面5000次查询才能拿到结果
    2. 可以先在二级索引中命中主键id,再统一在聚簇索引中查询
  3. WHERE t1.id = t2.id = t3.id -> (t1.id = t2.id) = t3.id -> true = t3.id
  4. 两条一样的insert语句导致死锁
    1. t1事务插入一条数据,根据自带的trx_id生成隐式锁
    2. t2事务插入同样的数据,唯一主键数据也相同,T2需要获取S型next-key锁,
    3. 但是T1并未提交，T1插入的记录上的隐式锁相当于一个X型正经记录锁（RC隔离级别）
    4. 所以T2向获取S型next-key锁时会遇到锁冲突，T2进入阻塞状态，并且将T1的隐式锁转换为显式锁（就是帮助T1生成一个正经记录锁的锁结构）
    5. t1事务想再在第一次的数据前插入数据时,由于已经被t2事务加了一个间隙锁,不管t2事务是否拿到锁或者在阻塞,当前的insert都会被阻塞
    6. 死锁产生的原因
      1. T1在等待T2释放name值为'g关羽'的二级索引记录上的gap锁。
      2. T2在等待T1释放name值为'g关羽'的二级索引记录上的X型正经记录锁。
    7. 解决办法
      1. 方案一：一个事务中只插入一条记录。
      2. 方案二：先插入name值为'd邓艾'的记录，再插入name值为'g关羽'的记录
  5. binlog
    1. 是binary log的缩写，即二进制日志
    2. binlog中记载了数据库发生的变化，比方说新建了一个数据库或者表、表结构发生改变、表中的数据发生了变化时都会记录相应的binlog日志
    3. 用途一： 用于主从复制-多副本数据库用于同步数据
    4. 用途二： 用于恢复,执行binlog日志从备份节点开始按顺序恢复数据
    5. binlog日志文件格式
      1. 固定4字节-固定魔数
      2. 事件1(格式描述文件)
      3. 事件2
        1. event header: 事件是什么类型、什么时候生成的、由哪个服务器生成的等信息。
        2. event data: 描述了该事件所特有的一些信息
    6. 基于语句的binlog->部分情况会导致主从数据不一致
    7. 基于行的binlog
    8. 混合语句和行的binlog,通常采用语句
  6. 先把undo日志写到Undo页面中以及记录相应的redo日志
    1. 先将undo日志写入Undo页面，然后再记录修改该页面对应的redo日志(指的是undo日志本身)
    2. 先将这个过程产生的redo日志写入到redo log buffer
    3. 再将这个过程修改的页面加入到buffer pool的flush链表中
  7. 修改聚簇索引记录
    1. 首先更新系统字段trx_id以及roll_pointer
    2. 真正的修改记录内容
    3. 记录更新的redo日志
    4. 在该MTR提交时，也是先将MTR中的redo日志复制到redo log buffer，然后再将修改的页面加入到flush链表-可以认为在这个过程中，先记录修改页面的redo日志，然后再真正的修改页面。
  8. 记录binlog
  9. 提交事务的时候-在事务提交时，binlog才会被真正刷新到binlog日志文件中，redo日志也会被刷新到redo日志文件中
  10. 分布式事务
    1. 事务具体是在存储引擎层实现的
    2. XA规范-两阶段提交
      1. Prepare阶段: 事务协调器确认各个事务管理器是否已经就绪(把在事务执行过程中所产生的redo日志都刷新到硬盘).如果有个没就绪,则通知回滚
      2. Commit阶段: 各个事务管理器都就绪,则事务协调器通知提交事务
    3. MySQL的外部XA
      1. MySQL服务器充当小弟，而连接服务器的客户端程序充当大哥
      2. 普遍使用场景是数据库中间件
    4. MySQL的内部XA
      1. 如果存储引擎提交了事务，server层的binlog日志必须也被写入到硬盘上
      2. 如果存储引擎回滚了事务，server层的binlog日志必须不能被写入到硬盘上
      3. 有binlog参与的内部XA事务
        1. Prepare阶段: 存储引擎将该事务执行过程中产生的redo日志刷盘，并且将本事务的状态设置为PREPARE(undo日志头中)
        2. Commit阶段: 先将事务执行过程中产生的binlog刷新到硬盘，再执行存储引擎的提交工作,更新Undo页面链表的状态
        3. 奔溃恢复:
          1. 按照已经刷新到磁盘的redo日志修改页面，把系统恢复到崩溃前的状态
          2. 判断Undo页面链表对应的事务状态,如果事务还没提交,则可忽略
          3. 如果是准备阶段,则要看对应的binlog是否已经刷新到硬盘,如果是的话,说明对应的都已经完成,直接提交事务即可.否则要回滚