目录
目录
常见存储引擎
| 存储引擎 | 描述 |
| MyISAM | 非事务存储引擎 |
| InnoDB | 较高版本MySQL的默认引擎 |
| MEMORY | 数据只存在内存中，不持久化到磁盘上，多用于临时表 |
| BLACKHOLE | 丢弃写操作，读操作返回空，可用于MySQL集群【主库-分发主库-多备库】结构中的分发主库 |
| NDB | MySQL集群专用引擎 |
MyISAM与InnoDB对比
| 功能 | MyISAM | InnoDB |
| 事务 | 不支持 | 支持 |
| 聚簇索引 | 不支持 | 支持 |
| 锁粒度 | 表级 | 表级、行级 （记录锁、间隙锁、Next-Key锁等） |
| 外键 | 不支持 | 支持 |
| MVCC | 不支持 | 支持 |
| 表空间最大规模 | 256T | 64T （一页默认16K，一个表空间的页号为4个字节，即32位，2^32 * 16K = 64T） |
字符集编码
| 字符集 | 最大长度（字节） |
| ascill | 1 |
| gbk或gbk2312 | 2 |
| uft8 | 3 |
| utf8mb4 （MySQL8.0默认字符集） | 4 |
InnoDB行记录的存储格式
COMPACT：
| 变长列大小 | NULL值列 | 记录头 | row_id | trx_id | roll_potinter | 其他列值 |
记录头
| 预留位 | 预留位 | delete_flag | min_rec_flag | n_owned | heap_no | record_type | next_record |
REDUNDANT：
| 字段长度偏移列表 | 记录头 | 列值 |
记录头
| 预留位 | 预留位 | delete_flag | min_rec_flag | n_owned | heap_no | n_field | 1byte_offs_flag | next_record |
DYNAMIC与COMPRESSED：
和COMPACT基本一样，但这两者在处理溢出列时，都只存溢出列的存储地址，不会存储前768个字节。
COMPRESSED还能够使用压缩算法对【页面page】进行压缩。
页Page结构
| File Header |
| Page Header |
| Infimum + Supermum |
| User Records |
| Free Space |
| Page Diretory |
| File Trailer |
File Header
| FIL_PAGE_SPACE_OR_CHECKSUM | |
| FIL_PAGE_OFFSET | 4个字节，当前页号 |
| FIL_PAGE_PREV | 4个字节，前一个页号 |
| FIL_PAGE_NEXT | 4个字节，下一个页号 |
| FIL_PAGE_LSN | |
| FIL_PAGE_TYPE | 2个字节，当前页的类型 （主要包括UNDO_LOG、INODE、BLOB、INDEX） |
| FIL_PAGE_FILE_FLUSH_LSN | |
| FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID |
Page Header
| PAGE_N_DIR_SLOTS | 槽的数量 |
| PAGE_HEAP_TOP | 下一次插入记录的地址 |
| PAGE_N_HEAP | |
| PAGE_FREE | 垃圾链表的头节点地址 |
| PAGE_GARBAGE | 已删除记录的总字节数 |
| PAGE_LAST_INSERT | 最后插入的位置 |
| PAGE_DIRECTION | 插入方向 |
| PAGE_N_DIRECTION | 插入方向的插入数量 |
| PAGE_N_RECS | 用户记录总数，不包括最大、最小记录 |
| PAGE_MAX_TRX_ID | 当前页最大事务ID，只在二级索引中有 |
| PAGE_LEVEL | 当前页在B+树中的层级 |
| PAGE_INDEX_ID | 当前页所属的索引的ID |
| PAGE_BTR_SEG_LEAF | B+树叶子节点段头节点 |
| PAGE_BTR_SEG_TOP | B+树非叶子节点段头节点 |
单表访问方法
| const | 通过主键、唯一二级索引访问一条记录 |
| ref | 通过普通二级索引访问一个单点扫描区间 |
| ref_or_null | 通过普通二级索引的【一个单点扫描区间】或【NULL值】访问记录 |
| range | 通过【多个单点扫描区间】或【范围扫描区间】访问记录 |
| index | 查询的列全部都为二级索引的列（不需回表），且进行全表扫描 |
| all | 全表扫描 |
索引合并
| intersection索引合并 | 将两个索引的扫描的所有记录id取交集 两个所有的记录主键id必须是有序的， 对于联合索引，若只通过左侧部分索引列查，id是无序的，因为记录是按索引列来排序的 |
| union索引合并 | 将两个索引的扫描的所有记录id取并集 两个所有的记录主键id必须是有序的 对于联合索引，若只通过左侧部分索引列查，id是无序的，因为记录是按索引列来排序的 |
| sort-union索引合并 | 将两个索引的扫描的所有记录id先排序，再取交集 |
为什么没有sort-intersection？
因为sort-union是针对记录数少的情况下，先排序再并集后回表的代价较小，
但intersection是针对记录多的情况，如果先排序再交集，还不如全表扫描代价小
连接的本质
嵌套循环连接：驱动表，被驱动表（可使用索引），驱动表每扇出一条记录，就去被驱动表进行匹配，需要对被驱动表进行多次IO
基于join buffer的循环连接：将驱动表扇出的多条记录存储到join buffer中，一次性与被驱动表进行匹配，减少对被驱动表的IO次数
SQL成本计算
IO成本：默认系数1.0，对一个page进行读取的成本
CPU成本：默认系数0.2，对一条记录数进行条件判断的成本
连接的成本：单次访问驱动表成本 + 驱动表扇出值 * 单次访问被驱动表的成本
基于连接进行成本计算时，需要对驱动表扇出值进行粗略的估算，这个过程称作【条件过滤Condition Filtering】
子查询优化
符合【空值拒绝】的外连接转为内连接（好处：可以对不同的连接顺序评估成本）
不相关的IN子查询：
- 可使用【物化】的方式将子查询结果存入临时表，物化表可采用Memory引擎建立哈希索引，物化表超过系统变量【tmp_table_size】或【max_heap_table_size】时会使用磁盘进行持久化并建立B+树索引。使用物化表后，根据查询条件也可能再将SQL语句转为内连接来优化
- 甚至可以采用【半连接】的方式进行优化，半连接是指只关心查询表的一条记录在子查询的表中是否存在对应记录，而不关心到底对应子查询表的几条记录
- 转EXISTS
相关的IN子查询：
- 物化方式
- 转EXISTS
半连接的实现方式：
- Table pullout 表上拉：子查询的查询字段只包含主键或唯一索引列时，可直接将子查询提取到父查询，转为连接查询
- Duplicate Weedout 重复值消除：建立一张只包含父查询表主键的临时表，当父查询表中的记录需要添加到结果集中时，该记录主键必须不在临时表中存在才行
- Loose scan 松散扫描：子查询的查询条件和查询列都恰好为同一个索引列时，可以只取符合条件的第一条子查询记录与父查询表做匹配
- Semi-join Materialization 半连接物化：将子查询的结果进行物化，再与父表进行连接
- First Match 首次匹配：取父查询表的一条记录，到子查询表中进行匹配，能找到匹配的就放入结果集，再取父查询表的下一条记录
半连接适用条件：
- 子查询必须以IN的形式与外层查询的WHERE或ON组合
- 外层查询也可有其他查询条件，但必须是AND
- 子查询必须是单一查询，不能使用UNION
- 子查询不能使用group by、having或聚集函数
EXPLAIN常用列
id：每个SELECT语句对应一个id，id值相同的行，在前面的为驱动表，后面的为被驱动表
select_type：查询类型，常见的有SIMPLE、PRIMARY、SUBQUERY、MATERIALIZED等
table：涉及的表
type：访问表的方法，常见的有const、ref、ref_or_null、eq_ref、range、index、index_merge、all等
possible keys：可能使用到的索引列
key：通过成本计算后，决定使用的索引列
rows：扫描的行数，统计值，不是精确值
ref：type字段对应的 访问的 列值
Extra：额外说明信息，如 【Using index 使用覆盖索引】、Using join buffer、Using intersect/ union/sort_union 索引合并
Buffer Pool
用于缓存Page页的，内部数据结构主要为控制块、缓冲页
内部缓冲页细分为三种链表：
- free链：用来管理还未被使用的缓冲页
- flush链：用来管理被修改过的脏页，当一个Page被修改后，不会立即被刷回磁盘，而是在这个链表中等待刷盘时机，以减少IO次数，提高性能
- lru链：用于对缓冲页进行淘汰，细分为young区（热数据）、old区（冷数据），两个区比例默认为old占37%。当young区的缓冲页被访问，且其位于young区的后3/4时才会被移动到young区头部，降低链表刷新频率。对于old区的一个页，两次访问间隔小于【innodb_old_blocks_time 默认1秒】时，不会移动到young区
如何知道一个Page页是不是被Buffer Pool缓存了？
通过【表空间ID + 页号】与【控制块】进行哈希映射，快速查找一个Page是否被缓存了
数据库预读：
线性预读：顺序访问某个区的页面超过阈值【innodb_read_ahead_threshold 默认56】时，会将下一个区的全部页面异步预读至buffer pool中
随机预读：某个区的连续13个页面都被缓存值buffer pool中时，将会异步读取本区中的其他所有页
预读和全表扫描的存在就是lru链为什么要分young区和old区的原因，预读和全表扫描的页只会缓存在old区，提高了缓存页的整体命中率。
flush链表的刷盘时机：
后台有专门的线程负责每隔一段时间就从
- LRU链表old区的尾部向前扫描一些页
- flush链表刷新一些页
- 或者做checkpoint时（redo日志相关）
REDO日志
一条redo记录的通用格式
| type | space id | page no | data |
space id + page no 唯一定位一个page
根据对一条record写入的字节数可以划分为1，2，4，8，string + len
根据对记录的操作类型可以划分为CREAT、INSERT、DELETE、START_DELETE、END_DELETE
redo日志按照【组】进行写入，组的概念由MTR（Mini-Transaction）引出，MTR即对表进行一次操作时，会产生多个副作用操作，比如插入一条记录时，不仅仅需要更新对应page的record，也要更新索引、统计信息、page header、页分裂之类的，所以必须将这些所有的操作视作一个整体，这个整体就是组的概念。
所以在写入组redo时，在组的最后会插入一个MULTI_REC_END类型的redo日志，来标记它前面的redo日志是一个组。MULTI_REC_END只包含type字段。
但如果这个组只有一条redo日志，也插入MULTI_REC_END太浪费空间了，所以如果redo日志的type最高位为1，则代表改组只有1条redo日志，后面不再插入MULTI_REC_END。
redo日志页成为redo log block，大小为512K。分为block header、body、trailer。
header主要包含了DATA_LEN（该页已写入多少字节，默认12，即header的长度）、FIRST_REC_GROUP（该页中第一个组的第一个redo日志的偏移量）、CHECKPOINT_NO
body就是写入redo记录。
需要注意的是redo日志是先写入log buffer（默认16M）中，再刷盘。
MySQL内部提供buf free全局变量，来指示下一次写入redo记录的位置。
redo日志刷盘时机：
- 当 log buffer 使用率超过50%时，就进行刷盘
- 当事务提交时，就可能会进行刷盘（跟使用flush_log_at_trx_commit来控制是否同步刷盘， 0为交给后台线程1秒刷一次；1为同步刷盘；2为刷到操作系统缓冲区，可能会丢失）
- 后台线程每1秒刷一次
- 当服务器正常关闭时
- 进行checkpoint时
redo日志的硬盘文件内部也按512字节为单位进行划分block，每个文件的前四个block为保留block，用来存储一些该文件的信息。第一个为log file header，第二个为checkpoint1，第三个没使用，第四个为checkpoint2。
log file header主要存储了 LSN_START（本文件的起始LSN）
checkpoint主要存储了checkpoint_lsn、checkpoint_no、log_buf_size（执行checkpoint时log buffer的大小）
log sequence number（lsn）
全局redo log 的写入序号，buf_free的起始lsn值为8074
lsn越小，redo日志产生的越早。
flushed_to_disk_lsn：log buffer中已经刷入磁盘的redo 日志 lsn
flushed_to_disk_lsn <= buf_free
在buffer_pool的flush链表中，每个缓冲页的控制块都会保存oldest_modification（第一次修改该页的MTR开始时的lsn）、newest_modification（最近一次修改该页的MTR开始时的lsn）
flush链表的按照oldest_modification逆序排序，则尾节点是最早修改的缓冲页。
要注意一个页即使多次修改，也会在flush链表中存在一个。
checkpoint
redo日志空间是有限的，所以必须进行复用。那些flush链表中已经进行了刷盘的脏页对应的redo日志是可以被复用的。
那么就需要隔一段时间就check一下，哪些脏页被刷盘了，哪些redo占用的空间可以复用了。
所以在check的时候，只需要找到flush链表的尾节点的oldest_modification，所以小于这个lsn的redo就都可以被复用。
这时候就要记录一下做check的有关信息，checkpoint_lsn = flush尾节点oldest_modification，checkpoint_no += 1，以及checkpoint_lsn对应的offset。
当checkpoint_no为偶数就存到当前文件的checkpoint1中，否则写入checkpoint2中。
需要注意，不仅仅flush链表会刷盘，LRU链表的脏页也会刷盘。
奔溃恢复
当服务器奔溃时，就需要找到所有redo日志文件的log file header中checkpoint_no最大的值，以此为恢复起点，找到对应的checkpoint_lsn的offset的block。
恢复终点就是起始block向后扫描，第一个大小不等于512字节的block就是终点。
恢复时，采取Hash表（类似java HashMap）的形式，key为【space id + page no】，value为redo日志链表，同时跳过那些lsn小于checkpoint_lsn的日志。
采用hash表的好处就是恢复的时候，同一个页的所有redo日志是一起做的，减少了随机IO
UNDO日志
MVCC
通过 record记录的roll_pointer与undo日志的 roll_pointer组成的链表以及ReadView来实现【对单表进行普通查询时，不使用加锁的方式来并发读，实现不同的事务隔离级别】。
record记录和undo日志记录 都会包含trx_id事务id，这是用来判断当前事务可读记录的关键。
ReadView
m_ids：生成readview时还活跃的事务id列表
min_trx_id：生成readview时活跃事务id的最小值
max_trx_id：生成readview时下一个事务应被分配的id
creator_trx_id：生成该readview的事务的 id
判断一个记录是否对当前记录可读：
当前遍历的undo链表的记录的trx_id如果等于creator_trx_id，则该条记录可被当前事务读
当前遍历的trx_id小于min_trx_id，说明生成readview时该记录的事务已经提交了，可读
当前遍历的trx_id大于等于max_trx_id，说明生成readview时该记录的事务还未被创建，不可读
当前遍历的trx_id大于等于min_trx_id且小于max_trx_id，则需要判断当前的trx_id是否在m_ids列表中，不在说明事务已提交，可读；在的话不可读
Read Committed隔离级别：在当前事务中每次执行select都重新生成ReadView
Repeatable Read隔离级别：在当前事务中第一次执行select都重新生成ReadView
二级索引和MVCC：
只有聚簇索引的记录才有trx_id和roll_pointer，那读二级索引的时候：
- 判断当前索引页面page header中的MAX TRX ID与对应ReadView min_trx_id的关系，若MAX TRX ID 小于 min_trx_id则当前页面的记录对ReadView都可见
- 否则，需要进行回表判断
当一个事务条件时，（insert undo类型的日志可以直接释放），会将update undo类型的日志加入回滚段的history链表，以支持MVCC
如果当前系统中，最早生成的readview不在访问undo日志和delete mark的记录，则可以进行purge操作
锁
在【读已提交】、【可重复读】的隔离级别，不使用【lock in share mode】、【for update】加锁的【普通读 select】，都可以使用mvcc，这种方式称为一致性读
S锁——共享锁
X锁——排它锁
行级锁
表锁
IS锁——意向表级共享锁
IX锁——意向表级排它锁
意向锁是为了快速判断加表级锁时，表内的记录是否有S锁或X锁。
举个例子，当给记录加X锁时，会先给表加上IX锁，再给记录上X锁，这样后续有人想给表加上S锁或X锁时，看见表上有IX锁，则需要等待IX锁的释放才行，而不需要遍历表内所有的记录来看是否有记录被上了锁
意向锁之间是兼容的，也就是IX与IX兼容，IS与IS兼容，IX与IS也兼容
InnoDB的表级锁
表级S锁、X锁
表级IS锁、IX锁
表级AUTO-INC锁：为包含AUTO_INCREMENT列的表插入记录时，可能会加上这把锁；也可能使用轻量级锁，只在插入AUTO_INCREMENT列时生成一个轻量级锁，插入完后立刻释放
InnoDB的行级锁
Record Lock 记录锁
Gap Lock间隙锁——防止为两条记录之间插入记录（极大的降低幻读的概率）
Next-Key Lock——记录锁与间隙锁的合体
Insert Intention Lock插入意向锁——当前插入的间隙被上了Gap Lock，当我想插入，所以在该间隙生成插入意向锁，当Gap Lock释放时，通知我
隐式锁——延迟上锁，一个事务插入新的记录后，另一个事务通过该记录的trx_id来判断当前记录的事务是否活跃，若活跃则帮助他创建一个锁结构，自己也创建一个锁结构，自己等待。起到延迟生成锁的用处。
上面的锁在内存的结构中存放对应锁信息，如
- lock_type（LOCK_TABLE表级锁、LOCK_REC行级锁）
- lock_mode（LOCK_IS、LOCK_IX、LOCK_S、LOCK_X、LOCK_AUTO_INC）
- rec_lock_type行锁具体类型（LOCK_ORIDIARY即next-key锁、LOCK_GAP间隙锁、LOCK_REC_NOT_GAP记录锁、LOCK_INSERT_INTENTION插入意向锁）
488
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
