mysql
2020-07-18 23:01:25 46 举报AI智能生成
mysql 脑图
作者其他创作
大纲/内容
更新语句执行过程
更新数据 * n条
加载数据到缓存
写入undo log，便于回滚
更新内存数据
写redo log buffer
提交事务
提交事务时，redo log刷入磁盘
bin log 写入磁盘
往redo log写入commit标记
提交成功后，异步线程，刷脏页到磁盘，保持buffer pool和磁盘一致
异步刷新时，把一个数据页刷回去，而不是一行数据刷回去
几大组件
解析器
查询优化器
执行器
存储引擎
buffer pool
buffer pool
innodb_buffer_pool_size设置缓冲池大小
free-list：存放空闲的数据页
采用lru算法
普通lru存在的问题：预读（读第n页时，把第n+1页读到缓存）；全表扫描
预读存在的意义：计算机读写时，遵循局部性原理，提升性能
实际采用冷热数据分离的lru算法，将单链表分成了两个部分，由innodb_old_blocks_pct控制，默认为37
37%为冷数据，其余为热数据；一开始，数据直接放在冷数据部分的表头，在innodb_old_blocks_time后再次
被访问，则移动到热数据；如果热数据区域满了，则淘汰热数据区域尾部节点
37%为冷数据，其余为热数据；一开始，数据直接放在冷数据部分的表头，在innodb_old_blocks_time后再次
被访问，则移动到热数据；如果热数据区域满了，则淘汰热数据区域尾部节点
热数据区域，前1/4区域，假设10个元素，其中第5个元素被访问，也不会移动到头部；后3/4区域才会
链表中冷数据部门：全表扫描和预读后，innodb_old_blocks_time时间内没被访问的数据
业务系统中，可以借鉴，比如统计每天热门商品，然后预加载到缓存中
数据页缓存hash-table
key:表空间number + 页的number
value：对应的缓存数据页的地址
flush-list：存储脏页，即被修改过的页
每个buffer pool包含多个chunk，每个chunk的大小由innodb_buffer_pool_chunk_size参数控制的，默认值就是128MB
表空间
对应数据库磁盘中一张表
数据页
一页通常16kb，包含很多行；但是不可能包含全部行，所以一般有很多页
所属表空间
页的number，因为一个表一般有很多页
读取数据页
读取磁盘中一个数据页
从free-list获取一个空闲数据页
将磁盘中读取的数据页的数据，设置到空闲数据页中
从free-list中移除该空闲数据页
表数据
redo log
innodb_flush_log_at_trx_commit
0
事务提交时，不刷盘
1
事务提交时，必须刷盘；此时，redo log 就是完整的，
mysql崩溃也可以根据这个恢复数据
mysql崩溃也可以根据这个恢复数据
2
事务提交时，刷到os cache
bin log
sync_binlog
0
刷入os cache
1
刷盘
磁盘文件
undo log
redo log
表数据
数据页，和buffer pool中对应
行数据格式
varchar这种变长类型，在行的开头，有个变长字段的长度列表
一行数据有VARCHAR(10) VARCHAR(5) VARCHAR(20) CHAR(1) CHAR(1)，一共5个字段，假设数据是这样的：hello hi hao a a，则
实际存储：0x03 0x02 0x05 null值列表 头字段 hello hi hao a a
实际存储：0x03 0x02 0x05 null值列表 头字段 hello hi hao a a
针对可以为null的字段，每行都有一个null列表，每个字段占用1个bit，标识该行中，这个字段，是否为null
delete_mask：标识该行是否被删除
一行数据是“jack NULL m NULL xx_school”，真实存储如下所示：
0x09 0x04 00000101 0000000000000000000010000000000000011001 jack m xx_school
变长字段长度 null值列表 40bit的数据头 真实数据
按字符集编码后：
0x09 0x04 00000101 0000000000000000000010000000000000011001 616161 636320 6262626262
0x09 0x04 00000101 0000000000000000000010000000000000011001 jack m xx_school
变长字段长度 null值列表 40bit的数据头 真实数据
按字符集编码后：
0x09 0x04 00000101 0000000000000000000010000000000000011001 616161 636320 6262626262
隐藏字段
DB_ROW_ID：行的唯一标识，没设主键的话，会自动用这个当成主键
DB_TRX_ID：当前事务id
DB_ROLL_PTR：回滚指针
最终结构：
0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C（DB_ROW_ID）00000000032D（DB_TRX_ID） EA000010078E（DB_ROL_PTR） 616161 636320 6262626262
0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C（DB_ROW_ID）00000000032D（DB_TRX_ID） EA000010078E（DB_ROL_PTR） 616161 636320 6262626262
行字段有text、blog这种时，在一行里放不下，产生行溢出问题。大字段的内容，会存放在其他数据页中，这边只存储指针
一个数据页16k，一个区，包含64个数据页，大小共1m
每个组，256个数据区
每次读表数据时，就去找到对应的组，区，数据页，然后把这一页（一个16kb的数据）读到内存buffer pool；
更新时，改过的页，会加入到flush list，后台线程再去flush list，把更新后的这一页，写回去
更新时，改过的页，会加入到flush list，后台线程再去flush list，把更新后的这一页，写回去
数据页之间有prev/next指针相连，可以理解为双向链表；指针可能就是各个数据页在磁盘上的offset
读取机制
随机读取
读数据页
顺序读写
binlog、redolog
redo log
一条binlog的大致格式：日志类型（就是类似MLOG_1BYTE之类的），表空间ID，数据页号，数据页中的偏移量，修改数据长度，具体修改的数据
redo log也不是单条单条写入的，而是redo log block，
每个block 512字节，这个redo log block是内存结构
每个block 512字节，这个redo log block是内存结构
12byte header
4 byte 块编号
2 byte 已写入的字节长度，写满时，值为0x200,即512
2 byte 本block中第一条redo log的偏移量；因为不一定就是从第13个字节开始，因为上一个redo log的内容，可能在上一个block写不下，然后部分内容，写到这里面
checkpoint number
492 body
8 byte 尾部
redo log buffer，包含多个redo log block
默认16m，innodb_log_buffer_size控制
SHOW VARIABLES LIKE 'innodb_log_buffer_size'
redo log buffer刷入磁盘的时机
使用了超过一半
当事务提交时，该事务的redo log，假设存储在好几个redo log block，那么，全部刷盘
后台线程1s刷一次
mysql关闭时
相关参数：
redo log文件位置：innodb_log_group_home_dir
每组中多少个log file，默认为2：innodb_log_files_in_group
log file大小：innodb_log_file_size
undo log
insert，生成相反的delete
delete，生成相反的insert
update也是相反的update
innodb_undo_directory，undo log目录
undo 时，需要把修改了的内存里的数据页给还原
事务
多事务并发时，存在的问题
脏读
事务b读了事务a的数据，然后事务a回滚了，事务b此时就是脏读
不可重复读
事务a读了一条记录，然后事务b修改了该记录并提交；事务a再次查询，发现和第一次查询的对不上
幻读
事务a执行select，查到n条，事务b又插入了一条，提交；事务a再查，查到n+1条
sql标准中，规定的4种隔离级别
读未提交
读已提交
可重复读
串行
设置隔离级别
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
支持全局、会话级别
spring中，Transactional#isolation，默认值为default，意思是，mysql用啥我用啥
spring中，Transactional#isolation，如果设为READ_COMMITTED，那么，抓包会发现：
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
spring中，开启事务时，会设置set autocommit=0
row上undo log pointer
可以找到undo log；undo log上又可以找到更早的一条undo log
mvcc
开启查询时，生成一个readview视图，保证只能读到比本事务早的，已提交的数据。同时，会有一个集合，记录当前哪些事务是活跃的，未提交的，如果后续查看的数据，是由这些事务提交的，也选择无视；方式是：读最新数据，如果最新数据的版本，比当前版本高，则找上一个版本；以此类推
主要解决快照读，或者叫，非一致性读场景下的事务隔离级别；如果sql语句要进行当前读的话，就能读到其他事务的最新数据
简而言之，活跃事务视图中的，比当前最大事务id还大的，都不能看；只能看：比最小事务小的；或者不在活跃事务里的
读已提交：
每次select都生成read view，就可以保证，每次能读到其他事务已经提交的数据
可重复读：
首次select时，生成read view，后续的select，不再生成read view
mvcc和锁机制的解决问题的不同
mvcc：多个事务中，有的人读，有的人写
锁：多事务同时写同一行或n行数据
当事务a更新时，事务b要去查同一行数据，（快照读），直接走mvcc
当事务a更新时，事务b要去查同一行数据，（非快照读），被阻塞，需要等待事务a释放锁后，通知事务b
当事务a更新时，事务b要更新，事务b被阻塞
当事务a查询时，事务b要更新，不阻塞
当事务a，非快照读时，（加共享锁或者排它锁），事务b要更新，被阻塞
当事务a，非快照读时，事务b也要非快照读，如果都是共享锁，则不阻塞；否则会阻塞
锁机制
多事务更新同一行数据时，涉及到加锁；更新实际都是在内存里的数据页操作的，所以加锁也是在内存里操作
事务a，要改111这行数据，先加锁；事务b要改111，结果发现已经被人捷足先登了，于是阻塞；事务a，释放锁的时候，事务b会感知到。类似于java里的synchronized、或者wait/notify机制；但是是公平锁机制
一般不要主动加select .. for update/lock in share mode，使用分布式锁比较好
执行增删改时，阻塞alter table等ddl操作；ddl时，阻塞增删改
元数据锁的功能
表锁
LOCK TABLES xxx READ：这是加表级共享锁
LOCK TABLES xxx WRITE：这是加表级独占锁
意向表锁
意向独占锁
update表中语句时，行上加x锁，表上加意向x锁
意向独占锁
select时，表上加意向s锁
意义：update和select时，自动加的这些意向锁，主要是和上面的表锁互斥（lock table）
dml
查询
假设没有索引，连主键索引都没有
假设表有n个数据页，先加载第一页，然后查看该页内是否有需要的数据；如果没有，加载下一页
页内查找时
如果是按照主键查找
则可以查询page directory
非主键查找
遍历页内的每条记录
假想的简单的主键索引结构
将每个页中，最大最小的id找到，然后放在一个地方存起来：页号，最小di，最大id；假设这个结构，叫做页的元信息
然后后续根据主键查找，就很方便了，可以先遍历所有页的元信息，然后可以找到对应的页号，然后就可以定位到具体的页
插入
一开始，一直在一个页里，存不下了之后，分裂成2个页；且，第二页中的id小的，会挪到第一页中；第一页中，id大的，要挪到第二页
linux如何读取磁盘文件
VFS层，用户的读文件请求，先发给它
文件系统层，包括NFS/EXT3等，上层会根据要读写的文件，所属的文件系统，交给不同的文件系统去处理
Page Cache，基于操作系统内存
通用Block层
io 调度层
默认CFQ公平调度算法
可以优化为deadline算法
Block 设备驱动层，这里的block不是阻塞的意思，是块的意思，一般读写磁盘，是一次读取一块内容的，比如一个数据页16kb，会全部读出来
Block 设备层
RAID，磁盘冗余阵列
保证数据写入多块磁盘，提升可靠性
RAID，一般采用硬件实现，往RAID写时，默认也会写到其内部缓存，可以理解为内存；为了防止掉电时，丢失数据，内部胚布锂电池，断点后，使用锂电池的电量写磁盘
锂电池需要定期充放电，此期间，由WRITE_BACK切换为WRITE_THROUGH。RAID写磁盘时，不再写缓存，直接写磁盘，性能下降10倍以上
raid 充放电时期，如何避免性能抖动
方案1：锂电池换成电容，但是电容易老化，因此不常用
方案2：关闭自动充放电，脚本手动执行充放电，低峰期执行
方案3：充放电时，不write through，还是写缓存
软件实现的RAID，没有上述功能
raid 0
几块磁盘组成集群，每次写入时，可随机写一台；劣势是，数据可能丢失，因为数据只在某一个磁盘上存储
raid 1 为了解决raid 0的问题，raid 1是每次写入时，会吧相同的数据，写入多块磁盘，磁盘利用率减半（2块磁盘的话），33%（3块磁盘的话）
raid 5 硬盘设备的数据奇偶校验信息保存到其他硬盘设备中，数据还是单独只放在某一块磁盘上，当磁盘故障时，利用奇偶校验信息来修复
raid 10，直接按照redis cluster理解就行，分片+ 主从，raid 0 + raid 1的结合体
生产调优
使用多个buffer pool
buffer pool总大小=(chunk大小 * buffer pool数量)的倍数，chunk默认是128m
buffer pool使用机器内存的50-60%
SHOW ENGINE INNODB STATUS
关注的是free、lru、flush几个链表的数量的情况，lru链表的冷热数据转移的情况，缓存页的读写情况
执行计划
explain
const
主键查找，最快
ref
普通索引，先定位到主键，然后回表
range
利用索引，进行了范围查询
index
假设我们有一个表，联合索引是KEY(x1,x2,x3)，SQL语句是select x1,x2,x3 from table where x2=xxx，
会直接遍历KEY(x1,x2,x3)索引树的叶子节点的那些页，一个接一个的遍历，然后找到 x2=xxx 的那个数据，就把里面的x1，x2，x3三个字段的值直接提取出来就可以了！这个遍历二级索引的过程，要比遍历聚簇索引快多了，毕竟二级索引叶子节点就包含几个字段的值，比聚簇索引叶子节点小多了，所以速度也快！
简单来说，index：遍历整个普通索引来查询
all
全表扫描
生产环境配置
数据库
8核16g，每秒tps1000-2000
16核32g，2000-3000，甚至3000-4000
ssd推荐
压测数据库
qps:每秒处理的请求数，一个sql语句基本就是一个请求
tps:用在数据库更多一些，每秒处理的事务数，一个事务包含多个sql语句
io相关指标
iops：每秒可以处理的随机io数
吞吐量：每秒可以写入多少字节，一般顺序写速度可以达到200m/s
latency：往磁盘写入一条数据的延迟
cpu
网络
查看特定负载下，每分钟网卡的进出流量
io
压测工具：sysbench
压测时，关注机器状态
cpu：top命令
内存：top命令
磁盘：dstat -d/-r
网卡：dstat -n
生产监控
Prometheus
node_exporter
mysqld_exporter
Grafana
grafana-dashboards
java业务系统
2核4g或4核8g
单台每秒200-800并发，主要还是看请求处理耗时多少
线上问题
too many connections
数据库设置了my.cnf，max_connections=800，但是结果show variables like 'max_connections'
，发现为214；因为被操作系统的文件句柄数限制了
，发现为214；因为被操作系统的文件句柄数限制了
日志中有如下警告：
Could not increase number of max_open_files to more than mysqld (request: 65535)
Changed limits: max_connections: 214 (requested 2000)
Could not increase number of max_open_files to more than mysqld (request: 65535)
Changed limits: max_connections: 214 (requested 2000)
性能抖动
redo log 写满了，此时再要写redo log，就需要从第一个文件开始写，写之前，要把redo log对应的脏页先刷到磁盘数据页里面去
此时查看mysql监控，会有大量的磁盘io操作
如何处理：
提升刷盘的速度
采用固态硬盘
采用fio测试出固态硬盘的io值，然后修改innodb_io_capacity值为对应的固态硬盘io值
禁用innodb_flush_neighbors
0 条评论
下一页
