MySQL

发表于 分类于
MySQL

还有 MySQL45讲学习笔记 （新的）

存储引擎

SHOW ENGINES 查看支持的所有存储引擎

InnoDB

  • MVCC 多版本并发控制
  • 四个隔离级别
  • next-key locking 避免幻读( phantom )
  • 插入缓存 insert buffer
  • 二次写 double write
  • 自适应哈希索引 adaptive hash index
  • 预读 read ahead
  • 重做日志 redo log
  • 聚集 clustered

MyISAM

[my-z[ei]m]

  • 不支持 事务、表锁设计
  • 支持 全文索引
  • 缓冲池只缓存索引文件，不缓存数据文件
  • 用 MyISAM 的表由 MYD 和 MYI 组成
    • MYD 用来存数据文件
    • MYI 用来存索引文件
  • 可用 myisampack 工具来解压缩数据文件，myisampack 使用赫夫曼编码来压缩数据
    • 压缩后的表是只读的
  • 5.0 开始，MyISAM 单表默认支持256TB的数据
  • 对于 MyISAM 存储引擎表，MySQL 数据库只缓存其索引文件，数据文件由操作系统来完成
  • 64 位支持大于 4 GB 的索引缓冲区

常用

  • user 表 看信息

user表

MySQL系统数据库

mysql

核心，它存储了 MySQL 的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等

information_schema

这个数据库保存着 MySQL 服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据

performance_schema

这个数据库里主要保存 MySQL 服务器运行过程中的一些状态信息，算是对 MySQL 服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息

sys

这个数据库主要是通过视图的形式把 information_schema 和 performance_schema 结合起来，让程序员可以更方便的了解 MySQL 服务器的一些性能信息

InnoDB(详)

  • InnoDB是多线程的

基本名词

  • dirty page 脏页：LRU 列表被修改的页( 因为和磁盘数据不一样了 )

存储结构

存储结构 = 表空间Tablespace( 段Segment( 区Extent( 页Page( 行Row ) ) ) )

存储结构

表空间

最高层

  • 启用 innodb_file_per_table 就能将每张表单独放到一个表空间
    • 如果启用，每张表的表空间只存放 数据、索引、插入缓冲 BITmap 页，其他的如回滚( undo )信息、插入缓冲索引页、系统事务信息、二次写缓冲( Double write buffer )等还是在原来的共享表空间( ibdata1 )里

段

表空间是由各个段组成的

有 数据段、索引段、回滚段 等

数据段 为 B+ 树的叶子节点

索引段为 B+ 树的非叶子节点( 非索引节点 )

区

由连续页组成

每个区大小都为 1 MB

每次从磁盘申请 4 ~ 5 个区

因为默认页 16 KB，所以一个区共 64 个连续页

对压缩页就另说了

页/块

页是 InnoDB 中磁盘和内存交互的基本单位，也是是管理存储空间的基本单位，一般是 16 KB

数据页 B-tree Node

数据页是存放记录的页

  • 记录按照主键值的大小串联成一个单向链表
  • 页之间组成双向链表
  • 页不是连续的，即不是页 1 页 2…
  • 一个页最少存储 2 条记录

image-20210105200559313

名称 中文名 占用空间大小 简单描述
File Header 文件头部 38 字节 页的一些通用信息
Page Header 页面头部 56 字节 数据页专有的一些信息
Infimum + Supremum 最小记录和最大记录 26 字节 两个虚拟的行记录
User Records 用户记录 不确定 实际存储的行记录内容
Free Space 空闲空间 不确定 页中尚未使用的空间
Page Directory 页面目录 不确定 页中的某些记录的相对位置
File Trailer 文件尾部 8 字节 校验页是否完整

存储的记录会按照我们指定的行格式存储到 User Records 部分

但是在一开始生成页的时候，其实并没有 User Records 这个部分，而是 Free Space

每当我们插入一条记录，都会从 Free Space 的那部分，申请一个记录大小的空间，然后划分到 User Records 部分

当 Free Space 部分的空间全部被 User Records 部分替代掉之后，也就意味着这个页使用完了

如果还有新的记录插入的话，就需要去 申请新的页

有两个伪记录 Infimum 和 Supremum，一个代表最小记录，一个代表最大记录

  • 它们并不存放在页的 User Records 部分，他们被单独放在一个称为 Infimum + Supremum 的部分
  • Infimum记录( 也就是最小记录 )的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录( 也就是最大记录 )

当删除第2条记录后：

  • 第 2 条记录并没有从存储空间中移除，而是把该条记录的 delete_mask 值设置为 1
  • 第 2 条记录的 next_record 值变为了 0，意味着该记录没有下一条记录了
  • 第 1 条记录的 next_record 指向了第 3 条记录
  • 最大记录 的 n_owned 值从 5 变成了 4
    • n_owned 是目录页

当数据页中存在多条被删除掉的记录时，这些记录的 next_record 属性将会把这些被删除掉的记录组成一个垃圾链表，以备之后直接重用这部分存储空间

初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组

之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的 n_owned 值加 1，表示本组内又添加了一条记录，直到该组中的记录数等于 8 个

在一个组中的记录数等于 8 个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中 4 条记录，另一个 5 条记录

这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量

File Trailer

InnoDB存储引擎会把数据存储到磁盘上，但是磁盘速度太慢，需要以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候中断电了咋办，这不是莫名尴尬么？为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），每个页的尾部都加了一个File Trailer部分，这个部分由8个字节组成，可以分成2个小部分：

  • 前4个字节代表页的校验和

    这个部分是和File Header中的校验和相对应的。每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为File Header在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了，那么在File Header中的校验和就代表着已经修改过的页，而在File Trialer中的校验和代表着原先的页，二者不同则意味着同步中间出了错。

  • 后4个字节代表页面被最后修改时对应的日志序列位置（LSN）

    这个部分也是为了校验页的完整性的，只不过我们目前还没说LSN是个什么意思，所以大家可以先不用管这个属性。

这个File Trailer与File Header类似，都是所有类型的页通用的。

image-20201220161710220
总结

  1. InnoDB为了不同的目的而设计了不同类型的页，我们把用于存放记录的页叫做数据页

  2. 一个数据页可以被大致划分为7个部分，分别是

    • File Header，表示页的一些通用信息，占固定的38字节
    • Page Header，表示数据页专有的一些信息，占固定的56个字节
    • Infimum + Supremum，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节
    • User Records：真实存储我们插入的记录的部分，大小不固定
    • Free Space：页中尚未使用的部分，大小不确定
    • Page Directory：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多
    • File Trailer：用于检验页是否完整的部分，占用固定的8个字节

  3. 每个记录的头信息中都有一个next_record属性，从而使页中的所有记录串联成一个单链表

  4. InnoDB会为把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page Directory中，所以在一个页中根据主键查找记录是非常快的，分为两步：

    • 通过二分法确定该记录所在的槽

    • 通过记录的 next_record 属性遍历该槽所在的组中的各个记录

  5. 每个数据页的File Header部分都有上一个和下一个页的编号，所以所有的数据页会组成一个双链表

  6. 为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的LSN值，如果首部和尾部的校验和和LSN值校验不成功的话，就说明同步过程出现了问题

undo 页 undo Log Page

系统页 System Page

Transaction system Page

Insert Buffer Bitmap

Insert Buffer Free List

Uncompressed BLOB Page

compressed BLOB Page

行

每个页最多允许存放 16 KB / 2 - 200 行的记录，即 7992 行

行格式

有 4 种不同类型的行格式，分别是 Compact、Redundant、Dynamic 和 Compressed

指定行格式：

1
2
3
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
    
ALTER TABLE 表名 ROW_FORMAT=行格式名称
Compact

image-20210105181541756

记录被分为 额外信息 和 真实数据

  • 额外信息是为了描述这条记录而不得不额外添加的一些信息，分为 3 类，分别是 变长字段长度列表、NULL 值列表 和 记录头信息
    • 变长字段长度列表
      • 所有变长字段( 如 VARCHAR(M)、VARBINARY(M)、各种 TEXT、BLOB 类型 )的真实数据占用的字节长度，形成一个变长字段长度列表，各变长字段数据占用的字节数按照 列的顺序 逆序 存放
        • 变长字段中存储多少字节的数据是不固定的，要把这些数据占用的字节数也存起来( 十六进制 )，才不至于把 MySQL 服务器搞懵
        • 变长字段长度列表中只存储值为 非 NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的
      • 对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表
      • 逆序存放！！！
    • NULL 值列表
      • 把这些值为 NULL 的列统一管理起来，存储到 NULL 值列表中，不然存储会很占地方
      • 如果表中没有允许存储 NULL 的列，那就不会有 NULL 值列表
        • 将每个允许存储 NULL 的列对应一个 二进制位，二进制位按照列的顺序 逆序 排列
          • 二进制位的值为 1 时，代表该列的值为 NULL
          • 二进制位的值为 0 时，代表该列的值不为 NULL
          • 逆序！！！
    • 记录头信息
      • 由固定的 5 个字节( 也就是 40 个二进制位 )组成
      • image-20210105183755207
  • 真实数据
    • 除了自己定义的列，还有隐藏列，如 DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR，分别表示
      • 行 ID，唯一标识一条记录、事务 ID、回滚指针
    • InnoDB 表主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为 DB_ROW_ID 的隐藏列作为主键
Redundant

老，略

image-20210105191613506

溢出

在 Compact 和 Reduntant 行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用 20 个字节存储指向这些页的地址( 这 20 个字节中还包括这些分散在其他页面中的数据的占用的字节数 )，从而可以找到剩余数据所在的页

image-20210105195626851

Dynamic 与 Compressed

它们不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把 所有的字节 都存储到其他页面中，只在记录的真实数据处存储其他页面的地址

image-20210105195944180

与 Dynamic 不同的是，Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间

架构

innodb架构图

InnoDB有多个内存块，可以认为这些内存块组成了一个大的内存池，作用：

  • 维护线程
  • 缓存

后台线程

  • Master Thread
  • IO Thread
  • Purge Thread
  • Page Cleaner Thread

内存

内存组成：

  • innodb_buffer_pool 缓冲池
  • redo log_buffer 重做日志缓冲
  • innodb_additional_men_pool_size 额外内存池

缓冲池

作用是提高性能：

  • 读取：硬盘读到的数据 → 缓冲池，下次读相同的页，看缓冲池有没有，没有再去硬盘找
  • 修改：改缓冲池 → 以一定频率刷新到硬盘
    • Checkpoint 机制

缓存的数据页类型有：

  • data page 数据页
  • index page 索引页
  • [insert buffer 插入缓存](#Insert Buffer)
  • [adaptive hash index 自适应哈希索引](#Adaptive Hash Index)
  • lock info 锁信息
  • data dictionary 数据字典

缓冲池的组成：

组成

( 碎片：分配空间后，剩余的不够一个页( 16 KB ( 未压缩 ) )，就成了碎片 )

允许多个缓冲池实例，每个页根据 hash 值平均分配到不同缓冲池实例

管理缓冲池

主要使用的 List：

  • LRU List：左边是热区( new )，右边是 old，当缓冲区满了，就释放末尾的页( new 和 old 是相对 midpoint 来说的 )
    • 防止全表扫描污染缓冲池，有：
      • midpoint
        • 新读取到的页不是加入 LRU 首部，而是放入 midpoint 位置( 默认在 LRU 5/8 处 )
        • 可以通过 innodb_old_blocks_pct 改
      • innodb_old_blocks_time
        • 表示页读取到 mid 位置后还需要多长时间才加入 LRU 热端
  • Free List：记录空闲的页
    • 有维护一个控制信息，记录了头/尾地址，以及数量
    • Buffer Pool 初始化的时候已经分配好了控制块和缓存页，只是没有数据。此时 LRU List 为空，所有页都在 Free List
    • 每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free List 中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的 Free List 节点从链表中移除，放入 LRU List，表示该页已经被使用了
    • 如果 Free List 没空闲的，就要移除 LRU List 末尾的页
  • Flush List
    • 结构与 Free List 相似
    • Flush List 存储的是 dirty page 脏页( 是指针，指向 LRU List 的脏页 )
    • 脏页既存在于 LRU List，也在 Flush List
    • 这里的脏页修改指的此页被加载进 Buffer Pool 后第一次被修改，只有第一次被修改时才需要加入 Flush List ( 代码中是根据 Page 头部的 oldest_modification == 0 来判断是否是第一次修改 )，如果这个页被再次修改就不会再放到 Flush List 了，因为已经存在
    • 并且在 Flush List 中的脏页是根据 oldest_lsn ( 这个值表示这个页第一次被更改时的 lsn 号，对应值 oldest_modification，每个页头部记录 )进行排序刷新到磁盘的，值越小表示要最先被刷新，避免数据不一致
    • Checkpoint 机制

image-20201214171050793

重做日志缓冲

  • InnoDB 首先将重做日志信息放入 redo log buffer，然后按一定频率将其刷新到 重做日志文件

  • 一般情况，每一秒会将重做日志缓冲刷新到日志文件

缓冲大小由 innodb_log_buffer_size 控制，默认的 8 MB 已经基本满足每一秒发生的事务量

刷入磁盘的时机：

  • Master Thread 每秒都会刷新
  • 每个事务提交时会刷新
  • 当 重做日志缓冲 空间小于 1/2 时，会刷新

Change Buffer

参考

注意：唯一索引普通索引选择难题

对于一个字段，比如身份证，是选择唯一索引，还是普通索引？

  • 对于查询来说，差距微乎其微，即使是普通索引，相同的值也在页的同一个位置（再不济也在隔壁吧~），而唯一索引查到了就停止了

  • 对于更新来说，就会影响了

    • change buffer：当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

      需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上

      将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

    • 对于唯一索引来说，更新时必须验证整个表都没有这个值，必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了

    • change buffer 实际上也只有普通索引可以使用

    • 将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升

Checkpoint

主要解决：

  • 缩短数据库恢复时间
    • 重做日志中记录了 Checkpoint 的位置，这个点之前的页已经刷新回磁盘，只需要对 Checkpoint之后的重做日志进行恢复。这样就大大缩短了恢复时间
  • 缓冲池不够用时，将脏页刷新到磁盘
    • 缓冲池不够用时，根据 LRU 算法，溢出最近最少使用的页，如果页为脏页，强制执行 Checkpoint，将脏页刷新回磁盘
  • 重做日志不可用时，刷新脏页
    • 重做日志写满了，但脏页还在 Flush List 里面，没有刷到磁盘。要强制产生 Checkpoint，将缓冲池中的页至少刷新到当前位置

引入 LSN ( Log Sequence Number )

  • 就是字面意思
  • 每个页都有 LSN，重做日志也有，Checkpoint 也有

实现：

有三种 Checkpoint：

  • Sharp Checkpoint：在数据库关闭时使用，将所有的脏页都刷新回磁盘

  • Fuzzy Checkpoint：在数据库运行时使用，只刷新一部分脏页

    • MasterThread Checkpoint

      • 每秒/十秒从 Flush List 刷新一定比例脏页到磁盘

    • FLUSH_LRU_LIST Checkpoint

      • 解决上面的 “缓冲池不够用时，将脏页刷新到磁盘“

    • Async/Sync Flush Checkpoint ( 在 Page Cleaner Threader 中操作 )

      • 解决上面的 “重做日志不可用”

      • 强制把一些页刷回磁盘( 从 Flush List 获取 )

      • 把已经写入重做日志的 LSN 称为 redo_lsn，已经刷回磁盘的最新的 LSN 称为 checkpoint_lsn，则有：

        1
        checkpoint_age = redo_lsn - checkpoint_lsn
        1
        async_water_mark = 75% * total_redo_log_file_size
        1
        sync_water_mark = 90% * total_redo_log_file_size

      • 如果 checkpoint_age < async_water_mark：不需要刷新

      • 如果 sync_water_mark > checkpoint_age > async_water_mark：从 Flush List 刷新足够脏页到磁盘。使得 checkpoint_age < async_water_mark

      • 如果 checkpoint_age > sync_water_mark，很少发生。和上面一样

  • Dirty Page too much

    • 脏页太多，强制 Checkpoint

总的来说还是为了保证缓冲池有足够的页

关键特性5个

Insert Buffer

插入缓存

增强插入性能

使用的条件

  • 索引是辅助索引( secondary index )
  • 索引不是唯一的( unique )

对于非聚集索引的插入和更新操作，不是每一次直接插入到索引页中，而是先判断插入非聚集索引页是否在缓冲池中，若存在，则直接插入，不存在，则先放入一个 Insert Buffer 对象中。数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge( 合并 )操作，这时通常能将多个插入合并到一个操作中( 因为在一个索引页中 )，这就大大提高了对于非聚集索引插入的性能

Double Write

两次写

https://www.cnblogs.com/geaozhang/p/7241744.html#gongzuoliucheng

《内幕》P53

增强可靠性

重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力

在应用重做日志前，需要一个页的副本，当写入失败时，先用副本来还原这个页，在进行重做

关键词：内存、共享表空间、各个表空间、副本在共享表空间、顺序写（写进共享表空间）、随机写（内存到各个表空间）、2M、1M

Adaptive Hash Index

AHI 自适应哈希索引

AHI 是通过缓冲池的 B+ 树页构造出来的，不需要对整张表构建

Async IO

AIO、异步 IO

Flush Neighbor Page

刷新邻接页

当刷新一个脏页时，会检测该页所在区( extent )的所有页，如果是脏页，也一并刷新

视图

虚表

某个查询语句的一个别名

在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了

索引

B+ 树索引

一个 B+ 树索引的根节点自诞生之日起，便不会再移动

最下边一层是叶子节点，其余的是内节点

B+ 树找到的是 被查找数据行 所在的 页，然后把页读到内存，在内存找

一个页最少存储 2 条记录

一般情况下，我们用到的 B+ 树都不会超过 4 层，通过主键值去查找某条记录最多只需要做 4 个页面内的查找( 查找 3 个目录项页和一个用户记录页 )

在 InnoDB 中，索引即数据，数据即索引

  • MyISAM 将索引和数据分开存储，对数据/用户记录没有按主键大小排序，而是有个行号，索引叶子节点不存数据，而是存主键 + 行号，即先通过索引找行号，再根据行号找完整的数据( 对其他列建立的索引也是这样 )
  • 也就是说，MyISAM 中建立的索引相当于全部都是 二级索引

使用二分法查找

大目录嵌套小目录，小目录表示的才是数据

即数据都在树的叶子节点上( 完整的用户记录 )

对于一条记录( record )，有分为 用户记录( 0 )、目录项记录( 1 )、最小记录( 2 )、最大记录( 3 )，由 记录头信息 里的 record_type 决定

  • 目录项中的两个列是 主键 和 页号
    • 主键 key：页的用户记录中 最小 的主键值
    • 页号 page_no
  • 用户记录包含完整的数据，存储了所有列的值
聚簇索引

而 B+ 树索引叫做 聚簇( chu4 )索引

聚簇索引的两个特性：

  1. 使用 记录主键值的大小 进行记录和页的排序，包括

    • 页内的 记录 是按照主键的大小顺序排成一个 单向链表

    • 各个存放用户记录的 页 也是根据页中用户记录的主键大小顺序排成一个 双向链表

    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表

  2. B+树的叶子节点存储的是 完整的用户记录

    • 所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）

InnoDB 会自动的为我们创建聚簇索引

聚簇索引只能在搜索条件是主键值时才能发挥作用

二级索引与联合索引( 注意回表 )

也叫非聚簇索引

针对某一列叫二级索引，多个列叫联合索引，联合索引 本质上也是 二级索引

对二级索引来说

  • 用户记录里只有 b 列的数据( b 列数据 + 主键 )，不包含其他列
  • 目录项记录是 b 列最小值 + 页号 + 对应主键
    • 因为 b 列不唯一，主键值是为了在有 b 列值相同的时候，防止插入时懵逼，不知道插入哪个页，即先比 b 列，如果一样，再比主键
  • 所以在二级索引中，要通过某一列的值查用户数据，需要查到二级索引的叶子节点的用户记录，拿到用户记录里的主键值，再去 聚簇索引 里找完整的用户记录
    • 这个过程称为 回表 ( 通过某列的值查找完整的用户记录需要使用到 2 棵 B+ 树 )，所以叫做二级索引
      • 找第一次的时候，因为记录都是连在一起的，叫 顺序 IO，而找第二次时，主键可能是不连续的，这就导致了 随机 IO
      • 需要回表的记录越多，使用二级索引的性能就越低
        • 如果第一次返回的记录占比很大，那么第二次回表随机 IO 花费的也就越多，还不如直接聚簇索引( 全表扫描 )
        • 而决定什么时候用全表扫描，什么时候是 二级索引 + 回表，就是 查询优化器 的工作了
        • 为避免回表，应该尽量 索引覆盖

对联合索引来说

  • 假如针对 b、c 列建立联合索引，就先按 b 列进行排序，再按 c 列进行排序
  • 目录项记录是 b最小值 + c最小值 + 页号 + 对应主键
  • 用户记录是 b + c + 对应主键
基本操作

InnoDB 和 MyISAM 都会自动为主键或者声明为 UNIQUE 的列去建立 B+ 树索引

在创建表的时候指定需要建立索引的单个列或者建立联合索引的多个列：

1
2
3
4
CREATE TALBE 表名 (
    各种列的信息 ··· , 
    [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)

其中的 KEY 和 INDEX 是同义词，任意选用一个就可以。我们也可以在修改表结构的时候添加索引：

1
ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);

也可以在修改表结构的时候删除索引：

1
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

比方说我们想在创建 index_demo 表的时候就为 c2 和 c3 列添加一个 联合索引，可以这么写建表语句：

1
2
3
4
5
6
7
CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1),
    INDEX idx_c2_c3 (c2, c3)
);

在这个建表语句中我们创建的索引名是 idx_c2_c3，这个名称可以随便起，不过我们还是建议以 idx_ 为前缀，后边跟着需要建立索引的列名，多个列名之间用下划线 _ 分隔开。

如果我们想删除这个索引，可以这么写：

1
ALTER TABLE index_demo DROP INDEX idx_c2_c3;

WHERE 子句中的几个搜索条件的顺序对查询结果没有影响，有 查询优化器，会分析这些搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件

适用条件

全值匹配、匹配左边连续列、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另外一列、用于排序、用于分组

a、全值匹配

…就是全值匹配…

b、匹配左边连续列

搜索条件中的各个列必须是联合索引中从最左边连续的列

c、匹配列前缀

'As%' 可以，'%As%' 不行

d、匹配范围值

如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到 B+ 树索引

e、精确匹配某一列并范围匹配另外一列

…就是精确匹配某一列并范围匹配另外一列

f、用于排序

一般情况下，把记录都加载到内存中，再用一些排序算法，在内存中对这些记录进行排序，有的时候结果集太大以至于不能在内存中进行排序，就得暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端

在 MySQL 中，把在内存中或者磁盘上进行排序的方式统称为文件排序( filesort )

如果 ORDER BY 子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤

ORDER BY 的子句后边的列的 顺序 也必须按照索引列的顺序给出

  • 不能使用索引进行排序的情况
    • ASC、DESC 混用
    • WHERE 子句出现非排序使用到的索引列
    • 排序列包含非同一个索引的列
      • 有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序
    • 排序列使用了复杂的表达式
      • 要想使用索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式，如使用一些 函数
g、用于分组

如果按索引来分组 GROUP BY，就能用到索引

总的来说

一切按照 B+ 树节点的存放方式来决定

索引的代价
  • 空间
    • 每建立一个索引都要为它建立一棵 B+ 树，每一棵 B+ 树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的 B+ 树由许多数据页组成
  • 时间
    • 每次对表中的数据进行增、删、改操作时，都需要去修改各个 B+ 树索引
查询优化器

查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用 二级索引 + 回表 的方式

一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择使用 二级索引 + 回表 的方式进行查询，因为回表的记录越少，性能提升就越高

覆盖索引

为了避免 回表 操作带来的性能损耗，最好在 查询列表里只包含索引列

对这种查询/排序条件只用到索引的方式称为 索引覆盖

索引的挑选
只为用于搜索、排序或分组的列创建索引
考虑列的基数
  • 列的基数 指的是某一列中 不重复数据 的个数
  • 在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中
  • 最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好
索引列的类型尽量小
  • 数据类型越小，在查询时进行的比较操作越快( CPU 的层次 )
  • 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率
索引字符串值的前缀

只对字符串的前几个字符进行索引

1
2
3
4
5
6
7
CREATE TABLE person_info(
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

name(10) 就表示在建立的 B+ 树索引中只保留记录的前 10 个字符的编码，这种只索引字符串值的前缀的策略是非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候

  • 但是这种就用不了索引排序了，因为只对前几个字符进行索引，后面的字符都是无序的
让索引列在比较表达式中单独出现

有一个整数列 my_col，我们为其建立了索引。有下边的两个WHERE子句

  1. WHERE my_col * 2 < 4

  2. WHERE my_col < 4/2

第1个 my_col 并不是以单独列的形式出现的，存储引擎会依次遍历所有的记录，计算这个表达式的值是不是小于 4，这种情况下是使用不到 B+ 树索引的

第2个 my_col 是以单独列的形式出现的，这样的情况可以直接使用 B+树索引

主键插入顺序

让主键具有 AUTO_INCREMENT，让存储引擎自己为表生成主键

  • 因为如果一个主键 1 ~ 20 的数据页满了，这时候插进去一个主键是 5 的，就得分成两个页，造成性能损耗
避免冗余和重复索引

全文索引

通常使用倒排索引( inverted index )

innodb 使用 full inverted index

哈希索引

就是上面的 [自适应哈希索引](#Adaptive Hash Index)

Innodb 会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升

要求：对这个页的连续访问模式都是一样的，访问模式即查询条件

特点

　　1、无序，没有树高

　　2、降低对二级索引树的频繁访问资源

　　3、自适应

缺陷

　　1、hash 自适应索引会占用 innodb buffer pool

　　2、自适应 hash 索引只适合搜索等值的查询，如 select * from table where index_col=’xxx’，而对于其他查找类型，如范围查找，是不能使用的

　　3、极端情况下，自适应 hash 索引才有比较大的意义，可以降低逻辑读

事务

ACID

  • 原子性（Atomicity）[ˌætəˈmisiti:]
    • 转账不能转一半
  • 一致性（Consistency）
  • 隔离性（Isolation）[ˌaisəˈleiʃən]
    • 执行顺序有一定规律
  • 持久性（Durability）[ˌdjʊərəˈbɪlɪtɪ/]

事务的状态

image-20210324135118187

提交

autocommit：默认ON，每一条语句都算是一个独立的事务

隐式提交：即使 autocommit off，也会隐式提交（结构变化）

保存点

SAVEPOINT 保存点名称;

ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;

RELEASE SAVEPOINT 保存点名称;

redo

事务提交后还在内存，但是如果故障，内存数据都没了

我们只是想让已经提交了的事务对数据库中数据所做的修改永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复出来

只需要把修改了哪些东西记录一下就好

是顺序写的

redo 有 redo log buffer（重做日志缓冲，易丢失） 和 redo log file（重做日志文件，持久的）

事务提交时，必须先把日志写到重做日志文件（这里指 redo log 和 undo log）进行持久化

把一条记录插入到一个页面时需要更改的地方非常多，所以有很多类型的 redo log

  • MLOG_REC_INSERT（对应的十进制数字为 9）：表示插入一条使用非紧凑行格式的记录时的redo日志类型
  • MLOG_COMP_REC_INSERT（对应的十进制数字为 38）：表示插入一条使用紧凑行格式的记录时的redo日志类型
  • MLOG_COMP_PAGE_CREATE（type 字段对应的十进制数字为 58）：表示创建一个存储紧凑行格式记录的页面的redo日志类型
  • MLOG_COMP_REC_DELETE（type 字段对应的十进制数字为 42）：表示删除一条使用紧凑行格式记录的redo日志类型
  • …

这些类型的 redo log 既包含物理层面的意思，也包含逻辑层面的意思，具体指：

  • 物理层面看，这些日志都指明了对哪个表空间的哪个页进行了修改

  • 逻辑层面看，在系统奔溃重启时，并不能直接根据这些日志里的记载，将页面内的某个偏移量处恢复成某个数据，而是需要调用一些事先准备好的函数，执行完这些函数后才可以将页面恢复成系统奔溃前的样子

对 redo 日志中的某些数据还可能进行压缩处理

底层页面中的一次原子访问的过程称之为一个 Mini-Transaction，简称 mtr

对于 redo log 组，因为一条 redo 可能导致很多操作，如页分裂啥的，需要保证原子性，所以要在该组中的最后一条 redo 日志后边加上一条特殊类型的 redo 日志，该类型名称为MLOG_MULTI_REC_END（他只有一个type字段）

image-20210324143558673

对于 Mini-Transaction

image-20210324143757095

通过mtr生成的redo日志都放在了大小为512字节的页中，叫 redo log block

image-20210324144128844

真正的 redo 日志都是存储到占用 496 字节大小的 log block body 中

redo log buffer（redo日志缓冲区）

image-20210324144412280

每个 mtr 运行过程中产生的日志先暂时存到一个地方，当该 mtr 结束的时候，将过程中产生的一组 redo 日志再全部复制到 log buffer 中，不同事务的 mtr 可能是交替写入 log buffer 的

redo 日志刷盘时机是

  • log buffer 空间不足时（超过一半）
  • 事务提交时
  • 后台线程（每秒）
  • 正常关闭服务器时
  • checkpoint
  • …
  • 需要调用一下操作系统提供的fsync函数才可以

恢复

checkpoint_lsn

undo

undo log 撤销日志，用来帮助事务回滚和 MVCC

隐藏列：roll_pointer

  • 改动时，都会把旧的版本写入到 undo 日志中，这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

是随机读写的

事务 id

  • 只有在事务对表中的记录做改动时才会为这个事务分配一个唯一的事务 id

事务隔离

事务并发会遇到的问题

  • 脏写：一个事务修改了另一个未提交事务修改过的数据
  • 脏读：一个事务读到了另一个未提交事务修改过的数据
  • 不可重复读：一个事务读到另一个事务修改过的数据，并且其他事务每对该数据进行一次修改后，该事务查询到的是不一样的值
  • 幻读：一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来
    • 幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录，如果后读取时没有读到，那就是不可重复读

不论是哪种隔离级别，都不允许脏写的情况发生

严重性：脏写 > 脏读 > 不可重复读 > 幻读

不可重复读和幻读有什么区别？

(1) 不可重复读是读取了其他事务更改的数据，针对 update 操作

(2) 幻读是读取了其他事务新增的数据，针对 insert 和 delete 操作

既想保持事务的隔离性（按顺序来），又想让服务器在处理访问同一数据的多个事务时性能尽量高些，所以有隔离级别这东西

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED 读未提交 Possible Possible Possible
READ COMMITTED 读已提交 Not Possible Possible Possible
REPEATABLE READ 可重复读 Not Possible Not Possible Possible
SERIALIZABLE 可串行化 Not Possible Not Possible Not Possible

Oracle 只支持 READ COMMITTED 和 SERIALIZABLE 隔离级别

MySQL 在 REPEATABLE READ 隔离级别下，是可以防止幻读问题的发生

MySQL的默认隔离级别为 REPEATABLE READ

设置隔离级别：SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

  • level
    • REPEATABLE READ
    • READ COMMITTED
    • READ UNCOMMITTED
    • SERIALIZABLE
  • GLOBAL、SESSION或者什么都不放

MVCC

  • 版本链
    • 每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表。版本链的头节点就是当前记录最新的值
  • ReadView
    • 需要判断一下版本链中的哪个版本是当前事务隔离级别可见的
    • m_ids、min_trx_id、max_trx_id、creator_trx_id
    • 在访问某条记录时
      • 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问
      • 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问
      • 如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
      • 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问
    • READ COMMITTD 在每一次进行普通 SELECT 操作前都会生成一个 ReadView，而 REPEATABLE READ 只在第一次进行普通 SELECT 操作前生成一个 ReadView，之后的查询操作都重复使用这个 ReadView 就好了

purge

在确定系统中包含最早产生的那个 ReadView 的事务不会再访问某些 update undo 日志以及被打了删除标记的记录后，有一个后台运行的 purge 线程会把它们真正的删除掉

锁

锁结构主要属性

  • trx信息：代表这个锁结构是哪个事务生成的

  • is_waiting：代表当前事务是否在等待

当某事务改动了这条记录后，就生成了一个锁结构与该记录关联，因为之前没有别的事务为这条记录加锁，所以 is_waiting 属性就是false，我们把这个场景就称之为获取锁成功，或者加锁成功，然后就可以继续执行操作了

另一个事务也想对该记录做改动，那么先去看看有没有锁结构与这条记录关联，发现有一个锁结构与之关联后，然后也生成了一个锁结构与这条记录关联，不过锁结构的 is_waiting 为 true，表示当前事务需要等待，我们把这个场景就称之为获取锁失败，或者加锁失败，或者没有成功的获取到锁

在持有锁事务提交之后，就会把该事务生成的锁结构释放掉，然后看看还有没有别的事务在等待获取锁，发现了另一个事务还在等待获取锁，所以把那个事务对应的锁结构的 is_waiting 属性设置为 false，然后把该事务对应的线程唤醒，让它继续执行，此时那个事务就算获取到锁了

锁类别

  • 共享锁（S锁）
  • 独占锁（X锁）
  • 意向锁（是表级锁）
    • 意向共享锁（IS锁）：当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁
    • 意向独占锁（IX锁）：当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁

InnoDB的表级锁

InnoDB的行级锁（记录锁）

  • Record Locks
  • Gap Locks（gap锁）
    • gap锁的提出仅仅是为了防止插入幻影记录而提出的
  • Next-Key Locks（next-key锁
    • 既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录
  • Insert Intention Locks（插入意向锁
  • 隐式锁

备份与恢复

调优

https://coolshell.cn/articles/1846.html

Explain

  • id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id

    • 在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表
    • 在包含子查询的查询语句的执行计划中，每个SELECT关键字都会对应一个唯一的id值
      • 查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询
      • 两个表的 UNION 是有三个 id，因为还有一个临时表，id 为 NULL 表明这个临时表是为了合并两个查询的结果集去重而创建的
      • UNION ALL 就只有两个，不需要去重

  • select_type SELECT关键字对应的那个查询的类型

    • PRIMARY：对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY
    • UNION：对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION
    • UNION RESULT：UNION 临时表
    • SUBQUERY：如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY，由于select_type为SUBQUERY的子查询由于会被物化，所以只需要执行一遍
    • DEPENDENT SUBQUERY：子查询是相关子查询，DEPENDENT SUBQUERY的查询可能会被执行多次
    • DEPENDENT UNION
    • DERIVED
    • MATERIALIZED

  • table 表名

  • partitions 匹配的分区信息

  • type 针对单表的访问方法

    • system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory
    • const：根据主键或者唯一二级索引列与常数进行等值匹配
    • eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的
    • ref：通过普通的二级索引列与常量进行等值匹配时来查询某个表，可能是ref
    • ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值，可能
    • index_merge：索引合并
    • range：范围区间
    • index：覆盖索引，但是没有匹配所有索引列
    • ALL：全表

  • possible_keys 可能用到的索引

  • key 实际上使用的索引

  • key_len 实际使用到的索引长度

  • ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息

  • rows 预估的需要读取的记录条数

  • filtered 某个表经过搜索条件过滤后剩余记录条数的百分比

  • Extra 一些额外的信息

    • Using index：可以使用索引覆盖

    • Using index condition：搜索条件中虽然出现了索引列，但却不能使用到索引

      • SELECT * FROM s1 WHERE key1 > ‘z’ AND key1 LIKE ‘%a’;
        其中的key1 > ‘z’可以使用到索引，但是key1 LIKE ‘%a’却无法使用到索引，在以前版本的MySQL中，是按照下边步骤来执行这个查询的：

        先根据key1 > ‘z’这个条件，从二级索引idx_key1中获取到对应的二级索引记录。

        根据上一步骤得到的二级索引记录中的主键值进行回表，找到完整的用户记录再检测该记录是否符合key1 LIKE ‘%a’这个条件，将符合条件的记录加入到最后的结果集。

        但是虽然key1 LIKE ‘%a’不能组成范围区间参与range访问方法的执行，但这个条件毕竟只涉及到了key1列，所以新版本MySQL把上边的步骤改进了一下：

        先根据key1 > ‘z’这个条件，定位到二级索引idx_key1中对应的二级索引记录。

        对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足key1 LIKE ‘%a’这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。

        对于满足key1 LIKE ‘%a’这个条件的二级索引记录执行回表操作。

        我们说回表操作其实是一个随机IO，比较耗时，所以上述修改虽然只改进了一点点，但是可以省去好多回表操作的成本。设计MySQL的大叔们把他们的这个改进称之为索引条件下推（英文名：Index Condition Pushdown）。

    • Using where：使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件

EXPLAIN FORMAT=JSON + 语句

  • json格式的执行计划

SHOW WARNINGS

  • 使用EXPLAIN语句查看了某个查询的执行计划后，紧接着还可以使用SHOW WARNINGS语句查看与这个查询的执行计划有关的一些扩展信息

高可用

主从复制

参考

分表

参考

中间件：sharding-sphere、sharding-jdbc

1、垂直拆分（分字段）在实际业务中用的不多，麻烦

2、水平拆分

3、MySQL 分区表

  • HASH 分区

分库

按业务拆库和按表分库

按表分库：垂直分库和水平分库

乱七八糟

数据库三大范式

  • 第一范式：每个列都不可以再拆分
  • 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分
  • 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键