mysql是怎样运行的 -- 读书笔记(2023-02-12)
初识mysql
1.1 mysql客户端/服务端架构
1.客户端：和服务端建立连接，并将请求sql发送到服务端，服务端根据请求内容返回数据。 2.服务端：与多台客户端交互，并提供服务
1.2 客户端与服务器端连接的过程
服务端分为四部分：连接管理，解析与优化，存储引擎，以及文件系统。
graph TD
客户端1 --> 处理连接
客户端2 --> 处理连接
subgraph 服务端
subgraph 连接管理
处理连接
end
subgraph 解析与优化
处理连接-->查询缓存
查询缓存-->语法解析
语法解析-->查询优化
end
subgraph 存储引擎
查询优化-->InnoDB
end
InnoDB--> 文件系统
end
1.2.1 连接管理
采用连接池技术，缓存连接；防止连接频繁创建销毁降低性能。
1.2.2 解析与优化
- 查询缓存：相同的请求进来时，如果数据页未发生改变，则直接返回数据。
- 语法解析：在未命中缓存的情况下，将sql解析为一段可识别的命令。
- 查询优化：根据sql，判断索引等生成执行计划。
1.2.3 存储引擎
mysql自定义了存储格式，把数据的存储和读取封装到存储引擎中。分为多种存储引擎：innodb,myIsam 等。
2.innoDB记录存储结构
页简介： 硬盘和内存存在读取速率差距，因此将数据分为页，每页默认16Kb。 1.读取: 整页读取放到内存中 2.写入：更改内存中的页数据，并将页id插入到待刷新盘中，job定时刷新到硬盘中
2.1 InnoDB 行格式
设计了4种不同类型的行格式
2.1.1 COMPACT
其基本结构如下表格所示
| 记录的额外信息 | 记录的真实数据 | ||||
|---|---|---|---|---|---|
| 变长字段列表 | NULL值列表 | 记录头信息 | 列1的值 | 列2的值 | 列n的值 |
假设创建表pool_gateway：
CREATE TABLE `pool_gateway` (
`gateway_id` int NOT NULL COMMENT '通道号',
`gateway_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '通道名称',
`operator_type` int DEFAULT NULL COMMENT '运营商类型 1-移动，2-联通，6电信'
PRIMARY KEY (`gateway_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
- 变长字段列表： gateway_name为变长字段如varchar，将其长度的十六进制进行存储
- NULL值列表： 统计表中字段为null字段的个数，用二进制的0和1表示字段是否为空。如果可为空null字段5个，1和3为空；那么 10100，由于存储最少为一个字节，高位补0，变为00010100.
- 记录头信息： 固定5字节，40位，具有不同的意思。
| 名称 | 大小〈位〉 | 描述 |
|---|---|---|
| 预留位1 | l | 没有使用 |
| 预留位2 | 1 | 没有使用 |
| deleted_flag | 1 | 标记该记录是否被删除 |
| min_rec_fiag | 1 | B+树的每层非叶子节点 小的目 录项记录都会添加该标记 |
| n_owned | 4 | 页面被分为多个组，每组的master页记录所有条数，slave页记录0 |
| heap_no | 13 | 表示当前记录在页面堆的相对位置 |
| record_type | 3 | 记录类型；0：普通记录，1：b+树非叶子节点,2 infimum,3 supremum |
| next_record | 16 | 下一条记录的相对位置，如果记录在后面32字节，那么填32.如果在前面，那么填负数 |
- 记录的真实数据 除去自己创建的列，mysql会默认增加隐藏列，
| 列名 | 是否必需 | 占用空间 | 描述 |
|---|---|---|---|
| row id | 否 | 6字节 | 行ID，唯一标识一条记录；当建了自增主键后此字段不会创建 |
| trx_id | 是 | 6字节 | 事务 |
| roll_pointer | 是 | 7字节 | 回滚指针 |
2.1.2 REDUNDANT
mysql5.0之前采用的行格式
其基本结构如下表格所示
| 记录的额外信息 | 记录的真实数据 | |||
|---|---|---|---|---|
| 字段长度偏移列表 | 记录头信息 | 列1的值 | 列2的值 | 列n的值 |
2.1.3 DYNAMIC 行格式和COMPRSSED行格式
和COMPACT结构相似，对于溢出列处理不同。
2.2 对于溢出列
由于字段长度超过一页的长度，DYNAMIC 行格式和COMPRSSED行格式直接存储页的地址；COMPACT 改为 前768字节的数据+存储页的地址。如下图所示
graph LR
subgraph 页1
列1的值 --> 真实信息
列2的值 --> 页2
end
3.InnoDB数据页结构
页的基础结构
| 名称 | 中文名 | 占用空间大小 | 简单描述 |
|---|---|---|---|
| File Header | 文件头部 | 38字节 | 页的一些通用信息 |
| Page Header | 页面头部 | 56字节 | 数据页专有的一些信息 |
| Infimum+Supremum | 页面中的最小记录和最大记录 | 26字节 | 两个虚拟的记录 |
| User Records | 用户记录 | 不确定 | 用户存储的记录内容 |
| Free Space | 空闲空间 | 不确定 | 页中尚未使用的空间 |
| Page Directory | 页目录 | 不确定 | 页中某些记录的相对位置 |
| File Trailer | 文件尾部 | 8字节 | 校验页是否完整 |
3.1 数据操作
-
插入数据过程
- 判断是否有页里是否有空闲空间，如没有，则新建页
- 如果有，则再空闲空间中申请空间
- 插入过程，在列的头信息中，记录页的相对位置；从2开始（每个页初始化时，会生成两条默认记录，0-infimum(记录页面中最小的记录),1-supremum（记录页面中最大的记录），不存放在User Record是，存放在Infimum+Supremum）
-
删除过程
- 将数据列的头信息中is_deleted置为1.该举措为了防止在删除时，数据重新排列。
- 并加入到回收链表中
-
查找过程
- 方法1：由于存储结构是一个链表结构，可以通过从链表头开始查找，。
- 方法2：Page Directory(页目录)
-
将所有记录划分为几个组(分组规则，infimum 1条1组；supremum 1-8；其余4-8)
-
每个组最后一条相当于master,其头信息n_owned记录组内共有几条记录。
-
将master的地址偏移量单独提出来，存储到Page Directory，这些偏移量称为槽（Slot）,每个槽2个字节。
graph TB subgraph Infimum+Supremum Infimum记录 Supremum记录 end subgraph User Records Infimum记录-->第1条记录 第1条记录-->第2条记录 第2条记录-->第3条记录 第3条记录-->第4条记录 第4条记录-->Supremum记录 end subgraph Page Directory slot0:Infimum偏移量 slot1:Supremum偏移量 end- 利用Page Directory查找，二分法查找，由于有槽的存在，找到存储的槽，在顺序根据查找槽里的内容。
-
3.2 Page Header
pageHeader结构
3.3 File Header
3.4 File Trailer (文件尾部)
当fileHeader 和file Trailer相同时，说明刷新到磁盘成功；当不同时，说明在刷新磁盘时出现异常，需要回滚。
4. B+树索引
4.1 没有索引的情况，如何查找数据？
- 单页内: 根据链表一条条查或者根据pageDirectory二分查找
- 多页：顺序查询
4.2 索引
mysql中索引规则：
- 下一个数据页 中用户记录的主键值必须大于上一个页中用户记录的主键值.
- 给所有的页建立一个目录项
B+树结构图
graph LR
subgraph 索引页二级
索引页二级Infimum记录
索引页1地址&最小值
索引页2地址&最小值
索引页n地址&最小值
end
subgraph 索引页1
索引页1地址&最小值-->索引页1Infimum记录
索引页1Infimum记录
叶子页1地址&最小值
叶子页2地址&最小值
叶子页n地址&最小值
end
subgraph 索引页2
索引页2地址&最小值-->索引页21Infimum记录
索引页21Infimum记录
叶子页21地址&最小值
叶子页22地址&最小值
叶子页2n地址&最小值
索引页21Supremum记录
end
subgraph 索引页n
索引页n地址&最小值-->索引页n1Infimum记录
索引页n1Infimum记录
叶子页n1地址&最小值
叶子页n2地址&最小值
叶子页nn地址&最小值
索引页n1Supremum记录
end
subgraph 叶子页1
叶子页1地址&最小值-->页1Infimum记录
页1Infimum记录-->行1
行1-->Supremum记录
end
subgraph 叶子页2
叶子页2地址&最小值-->页2Infimum记录
页2Infimum记录-->页2行1
页2行1-->页2Supremum记录
end
subgraph 叶子页n
叶子页n地址&最小值-->页nInfimum记录
页nInfimum记录-->页n行1
页n行1-->页nSupremum记录
end
subgraph 叶子页21
叶子页21地址&最小值-->页21Infimum记录
页21Infimum记录--> 叶子页21行1
end
subgraph 叶子页22
叶子页22地址&最小值-->页22Infimum记录
页22Infimum记录-->页22行1
end
subgraph 叶子页2n
叶子页2n地址&最小值-->页2nInfimum记录
页2nInfimum记录-->页2n行1
end
subgraph 叶子页n1
叶子页n1地址&最小值-->页n1Infimum记录
页n1Infimum记录--> 叶子页n1行1
end
subgraph 叶子页n2
叶子页n2地址&最小值-->页n2Infimum记录
页n2Infimum记录-->页n2行1
end
subgraph 叶子页nn
叶子页nn地址&最小值-->页nnInfimum记录
页nnInfimum记录-->页nn行1
end
索引类型：
- 聚簇索引： 使用主键值的大小进行记录和页的排序。聚簇索引就是表的数据
- 二级索引： 根据用户创建的字段生成的索引，假设c2列的大小进行排序
- 叶子节点存储：主键id
- 页：按照顺序的排序
- 页内按照c2顺序进行排序，并形成双向队列
- 如果查询的字段是索引字段，则直接返回。如果是其他字段，查询出主键id值后，还需要做一次回表操作（根据聚簇索引再查一次）
- 联合索引: 由多列创建的索引，如c2,c3。和二级索引差别为排序用c2+c3组合排序。
4.3 应用B+树索引
4.3.1 扫描区间和边界条件
select * from t_table where id>=2 and id<=100 or (id in (1438,6328))
均可以转化为区间查询[2,1000],[1438,1438],[6328,6328],查找开始区间，查找结束区间。其实对于 B+ 索引来说，只要索引列和常数使用=、<=>、lN、 NOT 町、 IS NULL IS NOTNULL 〉、〈、
=、<=、 BETWEEN、! (也可以写成< >)或者 LIKE 操作符连接起来，就可以产生所谓的 扫描区间.
4.3.2 索引用于排序
- 排序字段为索引字段的情况，索引已经排好序；使用联合索引时，需要按照联合索引的顺序排序，否则无法走索引
- 排序非索引字段，将记录加载到内存中进行排序，或者内存不够的情况下，放入到磁盘进行排序。
- 不能索引排序的情况
- asc,desc混用
- 排序列包含非同一索引的列
- 排序列使用的索引字段不联系（Order by a,c ;索引：a,b,c）
- 扫描用的索引和排序的索引不是一个索引
- 排序列上用了函数
4.3.3 回表的代价
通过二级索引查询的时候步骤 1. 二级索引查询出主键id到内存中 2. 循环id查询内存中的页 3. 如果页不在内存中，则需要加载硬盘中的数据 如果二级索引查询出来的数据，在页中特别分散，可能不如全表扫描。 什么时候该用全表扫描，什么时候该用二级索引+回表，靠查询优化器进行判断。
4.3.4 创建索引
为了更好的使用，创建索引规则如下
- 只为用于搜索、排序或分组的列创建索引
- 考虑索引列中不重复值的个数
- 索引列的类型尽量小 （类型小，索引页存储多，树的层级更低）
- 覆盖索引 （查询只查索引列）
- 让索引列以列名的形式在搜索条件中单独出现
- 新插入记录时主键大小对效率的影响 （如果索引列插入到页数中间，会引起索引分裂成两页）。
- 定位并删除表中的冗余和重复索引.
4.4 InnoDB 的表空间
区：64个页一个区，如果默认16k一页，那么一个区1MB; 为什么要引入区的概念？ 因为页太小了，而且页与页之间是链表关系，在读取时，可能造成多次io。因此用区，申请连续的物理空间。 存放叶子节点和非叶子节点不在一起 区的分类：空闲的区，有剩余空闲页面的碎片区，没有空闲的区，附属于某个段的区 组：256个区为1组,一个组256MB
5.查询优化
建表如下
CREATE TABLE single_table (
id INT NOT NULL AUTO_INCREMENT,
keyl VARCHAR(100) ,
key2 INT,
key3 VARC8AR (lOO) ,
key-part1 RC8AR (lOO) ,
key-part2 V及RCHAR (1 00) ,
key-part3 VARCHAR(lOO) ,
common_field VARCHAR(lOO) ,
PRlMARY KEY (id) ,
KEY idx_keyl (keyl) ,
UNlQUE uk_key2 (key2)，
KEY idx_key3 (key3) ,
KEY idx_key_part(key-partl, key-part2, key-part3)
) Engine-InnoDB CHARSETz utf8i
5.1 单表访问方法
- const 单条索引记录查询 SELECT* from single_table where id=100; SELECT* from single_table where key2 =200;
- ref 普通的二级索引列与常数进行等值比较 SELECT * FROH single_table WHERE key1='abc' ;
- ref_or_null 某个二级索引列的值等于某个常数的记录，而且还想把该列中值为NULL 的记录也找出来;扫描区间[null,null],['abc','abc'] SELECT * FROH single_table WHERE key1='abc' or key1 is null ;
- range 区间范围 [50,100]
- index 扫描了所有区间的idx_key_part索引 SELECT key-part1. key-part2, keY-parrt3 from single_table WHERE key-part2 =' abc';
- all 全表扫描
- 索引合并
- Intersection索引合并
- union索引合并
- Sort-Union 索引合并
5.2 连接的原理
示例sql： select * from t1,t2 where t1.m1>1 and t1.m1=t2.m2 and t2.n2<'d'; 查询步骤：
- 确认驱动表
- 根据驱动表的查询结果流式查询被驱动表的数据，驱动表只访问一次，被驱动表根据驱动表查询结果访问多次
- 嵌套循环连续 驱动表查询数据出来一条，去被驱动表中查询出来数据
- 基于块的嵌套循环连续 join Buffer (连接缓冲区），查查一批数据之后，再 连接的方式：
- 左外连接 选取左侧的表为驱动表.
- 右外连接·选取右侧的表为驱动表.
- 内连接 若驱动表中的记录在被驱动表 找不到匹配的记录，则该记录不会加入到最后的结果集
- ON 子句中的过滤条件：外连接驱动表中的记录在被驱动表找不到匹配记录时是否应该把该驱动表记录加入结果集中
5.3 基于成本优化
- 成本的概念：读取一个页面花费的成本默认是1.0
- I/O成本
- CPU成本
- 基于成本优化的步骤
- 根据搜索条件，找出所有可能使用的索引.
- 计算全表扫描的代价。
- 计算使用不同索引执行查询的代价.
- 对比各种执行方案的代价，找出成本最低的那个方案.
- 全表扫描计算代价的基础：
每个表维护了字段的统计信息，根据统计信息判断统计
统计信息：
- rows: 表中的总条数
- data_length：表占用存储空间字节数，可反推页数
- 多表连接优化方向
- 尽量减少驱动表的扇出，
- 访问被驱动表的成本要尽量低.
- 统计数据的更新 InnoDB 表为单位来收集统计数据.这些统计数据可以是基于磁盘的永久性统计数据，也可以是基于内存的非永久性统计数据.可定时更新统计数据
6. 事务管理
6.1 事务的起源
ACID (atomicity,Isolation,consistency,durabilty) 原子性，隔离性，一致性，持久性 transaction 事务，直译为交易，交易就是买方付钱，卖方发货，收钱。
6.2 mysql事务的语法
begin
sql1;
sql2;
commit;
7. redo log
7.1 redo log 是什么，用于做什么。
在插入数据时，由于数据不会直接写入到磁盘中，而是在内存的页中，那么当出现断电的情况下，内存中的数据就丢失了。为了解决此问题，增加了redo log,在服务器重新启动后，根据redo log，将数据重新写回到数据库磁盘中。 为了解决断电的问题，我们应该怎样设计undo log呢？ 第一. 要足够的少的内容吗，列如更改一页数据的时候，只更改了很少的内容，但是刷新盘的时候，却需要将所有内容刷新到磁盘中。 因此 能够简单记录更改内容很重要。
- redo日志占用的空间非常小
- redo日志是顺序写入磁盘的
7.2 redo日志格式
graph LR
subgraph redo日志通用结构
type
space_id
page_number
data
end
type: 日志类型 space ID:表空间id page number: 页号 data： redo日志具体内容
服务器中维护一个全局row_id,当插入数据时，row_id自增，当row_id为256的倍数时，刷新row_id到磁盘。 如果服务器断电，取出的row_id+到256的倍数。
redo日志如何存储 redo log写入 redo buffer ,redo buffer 满了或者事务提交之后，刷到磁盘。
8. undo log
8.1 事务回滚的需求
事务在执行过程中，可能遇到各种错误，比如服务器的错误，或者事务执行了rollback。那么已经改了很多页的数据，如何回退回去呢？
8.1 事务id
- 在增删改的情况下分配一个事务id
- 服务器维护一个全局变量，当申请事务时，自增。当为256的倍数时，写入到磁盘中
- 在记录行格式中，请求头中存在trx_id，用于记录此条事务的id
9.事务隔离级别和MVCC
事务的隔离级别
9.1事务并发执行时遇到的一致性问题
- 脏写：如果一个事务修改了另一个未提交事务修改过的数据，意味着脏写。
- 脏读：如果一个事务读到另一个事务修改未提交的数据，那么就会出现脏读
- 如果能读到事务中修改的值，那么如果事务回滚了，读到的值就是一个虚拟的值。
- 如果能读到事务修改的值，根据此值做出的操作，反而比事务先完成。先后顺序发生了变化。
- 不可重复读： 如果一个事务a修改了另外一个未提交事务b读取的数据，意味着在b的过程中，两份数据是不一致的。
- 幻读： 如果一个事务先搜索了某些记录，在事务未提交时，另一个事务写入了一些符合的记录。
sql标准中的4种隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED 未提交读 | 可能 | 可能 | 可能 |
| READ COMMITTED 已提交读 | 不可能 | 可能 | 可能 |
| REPEATABLE READ 可重复读 | 不可能 | 不可能 | 可能 |
| SERIALIZABLE 可串行化 | 不可能 | 不可能 | 不可能 |
mysql 默认隔离级别
oracle只支持read committed 和serializable隔离级别 mysql支持四种
MVCC原理 行的规定里有两个隐藏列，trx_id，事务id,roll_pointer：每次对记录改动时，都会把旧版本写入到undo日志中。这个隐藏列相当于一个指针，可以找到记录修改前的信息 这样就会形成一个链表，叫做版本链 版本号155-->版本号144-->版本号143
利用这个版本链来控制事务能看到最新的版本还是小于当前的事务版本，这种机制称为多版本并发控制(Muti-Version concurrency Contorl) 未提交读 看到的是最新的版本 可重复读 看到小于当前的事务版本 已提交读 看到最新的已经提交的版本
为了解决已提交读能够看到已经提交的最新版本
通过生成readview来实现;readview的内容： m_ids 当前活跃的事务列表； min_trx_id最小的事务id； max_trx_id，下一个事务的id值； creator_trx_id 当前事务id
通过以下逻辑可以判断是否访问到已提交的事务 if 记录的trx_id = creator_trx_id =>可以访问 if trx_id < min_trx_id => 事务已提交 =>可以访问 if trx_id >= max_trx_id || trx_id in m_ids => 事务未提交 =>不可以访问 if min_trx_id <= trx_id < max_trx_id || trx_id not in m_ids => 事务已提交 =>可以访问
可重复读 第一次读和第二次读，使用的是一个readview. 已提交读 第一次读和第二次读，都生成一个readview
10. 锁的问题
解决并发事务带来问题的两种基本方式
-
写-写情况 锁结构：trx_id , is_waiting 表示当前事务是否在等待 step1: t1事务抢到锁，生成一个锁结构; trx_id:t1,is_waiting:false step2: t2事务未抢到，生成一个锁结构; trx_id:t2,is_waiting:true step3: t1释放锁，并检查此行有没有其他的锁结构，有的话，找到一条将is_waiting改为FALSE。
-
读-写情况 读写 方案1.mvcc，读写之间看不到 方案2. 读、写操作都采用加锁的方式
-
锁定读 1、共享锁（Shared Lock）,S锁，事务读取时，需要获取该记录的s锁 2、独占锁（exclusive lock）X锁
-
行锁，表锁 意向共享锁( In tention Shared Lock 简称 IS ，当事务准备在某条记录上加 锁时 需要先在表级别加 IS 锁. 意向独占锁(Intention Exclusive Lock) 简称 IX 锁，当事务准备在某条记录上加 时，需要先在表级别 一个 IX锁。
