MySQL高阶
2021-09-14 22:39:14 32 举报AI智能生成
MySQL高阶
作者其他创作
大纲/内容
MySQL 架构
架构图
分层结构
网络连接层
Connectors
该层一般基于 C/S 架构组件，对外提供交互的组件
SQL 处理层
Management Service & Utilities
管理服务组件和工具组件，提供对MySQL 集成管理，备份、恢复、安全管理等
SQL Interface
SQL 接口组件，接收用户 SQL 命令，如 DML，DDL 和存储过程等，并将最终结果返回给用户
Parser
查询分析器组件，首先分析 SQL 命令语法的合法性（是否符合 SQL 92 标准），并尝试将 SQL 命令分解成数据结构，若分解失败，则提示 SQL 语句不合理
Limit 属于 MySQL 自己的语法，不属于 SQL 92 标准
Optimizer
优化器组件，对 SQL 命令按照标准流程进行优化分析
针对索引，选择最优索引。
多表关联，将数据较小的表放在左边（嵌套循环，外层循环小提高性能）。
通过执行计划 Explain 执行 Where 从左到右寻找过滤力度最大的先执行（如有主键，先找主键）。
Caches & Buffers
缓存和缓冲组件
Map 结构： Key 存储 SQL 语句 hash 值，Value 存储 SQL 返回的结果。当该数据更新时，缓存层会删除。
MySQL 8.0 版本已经不再使用该功能
插件式存储引擎层
MyISAM
高速引擎，查询和插入顺序较高。5.6 版本支持了事务（与 InnoDB 事务不同），但不能同时与带有事务引擎使用（GTID 全局事务ID，可了解），但是仍然不支持行锁。
B+Tree 索引
InnoDB
5.5 版本以后默认引擎，支持事务处理、回滚、修复及 MVCC（多版本并发控制）和行锁及外键。
B+Tree 索引
Memory
内存存储引擎，无需磁盘 I/O。重启表结构会保留，数据会丢失。
Hash 索引
CSV
可以将 Excel 等彪哥数据存储为 CSV 文件。
不支持索引
NDB
集群存储引擎，类似 Oracle 的 RAC 集群，不同的是结构采用 share noting 集群架构。
NDB 数据全部放在内存中，但是 JOIN 查询速度慢。
只支持 READ COMMITTED 隔离级别
没有 MVCC
Archive
只支持INSERT/SELECT操作，MySQL5.1之后支持索引。
支持数据压缩，行锁
Federated
本身不保存任何数据，提供远程数据库表的指向
Maria
可以看作MyISAM的后续版本
支持缓存数据、索引外键、支持行锁、提供MVCC功能、支持事务和非事务安全选项、更有BLOB类型处理性能
SQL 执行流程
视图
可更新视图
物化示图
分区
RANGE分区
LIST分区
HASH分区
KEY分区
InnoDB 基本点
历史
5.5.8 由 MyISAM 转为 InnoDB 为默认存储引擎
与 MyISAM 区别
MyISAM 索引
非聚簇索引
二级索引
InnoDB 索引
聚簇索引
二级索引
对比
聚簇索引优点
聚簇索引缺点
相同点
不同点
索引
数据结构
数据结构示例网站
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B-Tree 概念
B+Tree
与 B-Tree 区别
B-Tree 结构图
B+Tree 结构图
存储方式区别
B-Tree 高度
节点顺序
指针指向
使用 B+Tree 而不采用 B-Tree 原因
索引类型
Cluster Index（聚簇索引）
Secondary Index（二级索引 / 辅助索引）
从索引结构，加之 B+Tree 特性，可以确定主键不建议使用 UUID 类型，因为插入、更新、删除需要对树进行一个分裂、合并、旋转等操作来维护平衡性。
InnoDB 架构
架构图
InnoDB 关键特性
Buffer Pool
Change Buffer Page（Insert Buffer Page）
Double Write
Adaptive Hash Index
Memory（内存）
相关知识
Tablespace
System tablespace
General tablespace
file-per-table tablespace
advantages
disdvantages
Page（页）
标准页
压缩页
Buffer Chunks（缓冲块）
Buffer Pool 内存常用三大逻辑链表
Free List
LRU List
LRU 实现原理
特性
缓存了所有读入内存的数据页
热点数据、新数据、旧数据处理
FLU List（Flush List）
Buffer Pool 数据
Buffer Pool 预热
MySQL重启与数据加载
提高预热效率
Buffer Pool 预读
I/O过程
读取过程
读请求合并
InnoDB 使用两种预读算法来提高 I/O 性能
Linear Read-Ahead（线性预读）
RandomRead-Ahead（随机预读）
Buffer Pool 工作机制
加速读
加速写
Buffer Pool（缓冲池）
Data Page
数据页，架构图上未标识
Index Page（索引页）
Change Buffer Page
Adaptive Hash Index(自适应哈希)
Lock Info(锁信息)
Data Dictionary(数据字典)
write Ahead Log
Redo Log Buffer（重做日志缓冲）
构成结构
innodb_log_group_home_dir
innodb_log_group
Redo Log File
写入过程
Redo Log Info
Redo Log Buffer
Redo Log File
触发落盘几种场景
Redo Log Buffer 空间不足
后台线程
建立 CheckPoint
实例 ShutDown
Bin Log 切换
事务提交
设置为 0
设置为 1
设置为 2
示意图
Doublewrite Buffer（双写缓冲）
时序图
产生原因
Partial Page Write
工作原理
DoubleWrite Buffer
share table space
innodb-file-per-tablespace
崩溃恢复
副作用
写负载
监控负载
示例
采用Double Write Buffer的原因
写入连续性
Buffer溢出
Additional Memory Pool
Disk（硬盘）
Tablespace（表空间）
Tablespace
System tablespace
General tablespace
temp tablespace
file-per-table tablespace
advantages
disdvantages
undo tablespace
逻辑存储结构
Tablespace（表空间）
Segment（段）
数据段
索引段
回滚段
Extent（区）
Page（页）
Page、System、Disk 关系
常见页类型
B-Tree Node
undo log Page
System page
Transaction System Page
Insert Buffer Bitmap
Insert Buffer Tree List
Uncompressed BLOB Page
compressed BLOB Page
页数据
File Header
FILE_PAGE_TYPE
Page Header
虚拟行数据
Infimum
Supremum
User Record
Free Space
Page Dictionary
File Trailer
Row（行）
格式为 Compact，事务 Id、回滚指针（指向 undo log record）、表定义的字段
CHAR
行溢出数据
存放位置
何时溢出
Antelope文件格式
Compact 行记录格式
分支主题
变长字段列表
Null 值字段标志位
record header（记录头）
隐藏列
DB_ROW_ID
DB_TRX_ID
DB_ROLL_PTR
列数据...
Redundant行记录格式
分支主题
字段长度偏移列表
记录头信息
隐藏列
DB_ROW_ID
DB_TRX_ID
DB_ROLL_PTR
Barracuda文件格式
Compressed行记录格式
Dynamic行记录格式
Redo log
主要线程
Master Thread
Loop
每秒钟操作
1. 处理redo log
2. 合并insert buffer
3. 刷新 dirty page
4. 如果当前没有活动用户，切换到 background loop。
每 10 秒操作
每秒操作的前三步骤
4. 回收 undo 页
5. 刷新 innodb_io_capacity 的 100% 或 10% 到磁盘，每 10 秒都会执行。InnoDB 存储引擎会检查
background loop
flush loop
suspend loop
IO Thread
使用AIO
read IO Thread
write IO Thread
insert buffer IO Thread
log IO Thread
Purge Thread
Page Cleaner Thread
流程图
日志
Error Log
Slow Query Log
General Query Log
Slave Relay Log
Binary Log
恢复
审计
InnoDB存储引擎层
相关知识
CheckPoint
Sharp Checkpoint
Fuzzy Checkpoint
master thread checkpoint
flush_lru_list checkpoint
async/sync flush checkpoint
dirty page too much checkpoint
LSN
利用LSN可获取到的信息
数据页版本信息
写入日志总量，通过LSN开始与结束号码计算
checkpoint位置
判断数据版本
LSN类型
lsn
flush_lsn
written_to_some_lsn
wirtten_to_all_lsn
last_checkpoint_lsn
next_checkpoint_lsn
archived_lsn
next_archived_lsn
LSN解析
时序图
标注说明
data_in_buffer_lsn
data_page_in_buffer_lsn
redo_log_in_buffer_lsn
redo_log_on_disk_lsn
checkpoint_lsn
时序步骤
MTR
遵循三个协议
The FIX Rules
Write Ahead Log
Force Log at Commit
Log Block
Log Buffer
log writer线程
recent_written buffer
link_buf
flush list连续性
主要lsn
write_lsn
buf_ready_for_write_lsn
flushed_to_disk_lsn
Redo Log
重做日志文件组
重要参数
恢复行为
格式
和 Binary Log 区别
位置
顺序
逻辑
写入
Undo Log
作用
提供回滚进而保障了事务的原子性
MVCC（多版本控制）
Update操作
Delete操作
和 Redo Log 区别
日志类型
写入时机
记录方式
Redo Log和Undo Log简化过程
Lock
相关知识点
MVCC
Current Read
SQL 语句
select .. for update
select ... lock in share mode
insert
update
delete
实现方式
Next-Key 临键锁
Gap间隙锁
行记录锁
Snapshot Read
SQL语句
基本的 select ... 语句
快照生成
Read Commited
Read Repeatable
实现方式
行数据隐藏列
示意图
表锁
加表锁
读锁
允许多个会话同时持有读锁
持有读锁的会话可以读表，但不能写表
其他会话就算没有给表加读锁，也是可以读表的，但是不能写表
其他会话申请该表写锁时会阻塞，直到锁释放。
写锁
持有写锁的会话既可以读表，也可以写表
只有持有写锁的会话才可以访问该表，其他会话访问该表会被阻塞，直到锁释放
其他会话无论申请该表的读锁或写锁，都会阻塞，直到锁释放
释放表锁
使用 UNLOCK TABLES 语句可以显示释放表锁
如果会话在持有表锁的情况下执行 LOCK TABLES 语句，将会释放该会话之前持有的锁
如果会话在持有表锁的情况下执行 START TRANSACTION 或 BEGIN 开启一个事务，将会释放该会话之前持有的锁
如果会话连接断开，将会释放该会话所有的锁
示例
行锁
读锁（S锁，共享锁）
写锁（X 锁，排他锁）
常见的 INSERT、UPDATE、DELETE 会自动对操作的数据行加写锁
实现
innoDB索引
简单加锁流程
加锁语句
具体过程
复杂加锁流程
加锁语句
具体过程
对比
总结
开销
粒度
发生死锁
Lock概念
lock_mode
表级锁
意向锁
LOCK_IS
IS(读意向锁)
LOCK_IX
IX(写意向锁)
LOCK_AUTO_INC
innodb_autoinc_lock_mode
0
1
2
AUTO_INC互斥
解锁
自增中断
行级锁
读锁
写锁
兼容性
意向锁之间互不冲突
S 锁只和 S/IS 锁兼容，和其他锁都冲突
X 锁和其他所有锁都冲突
AI 锁只和意向锁兼容
lock_type
Record Lock
示例
注意事项
模拟过程
Precise Modes
LOCK_ORDINARY
Next-Key Lock
改善幻读问题
示例
模拟
LOCK_GAP
Gap Lock
示例
模拟
innodb_locks_unsafe_for_binlog
LOCK_INSERT_INTENSION
LOCK_REC_NOT_GAP
兼容矩阵
在已有任意锁时，可以再申请插入意向锁
已有插入意向锁时，不兼容间隙锁与临键锁
移除插入意向锁
间隙锁兼容除插入意向锁以外的任意锁
记录锁与临键锁互斥，且与自身互斥
悲观锁与乐观锁
Optimistic Lock
实现方式
版本号
CAS
1. 读取内存位置 V
2. 进行比较原预期值 A
3. 拟写入新值 B
ABA问题
解决方式
自旋问题
只能保证一个共享变量的原子操作
Pessimistic Lock
实现方式
Java 的 synchronized 和 ReentrantLock
MySQL 的锁
事务
事务隔离特性
Atomic
Consistency
Isolution
Durability
事务隔离级别标准
Read Uncommitted
Read Committed
Repeatable Read
Serialized
加锁协议
一级封锁协议
二级封锁协议
三级封锁协议
四级封锁协议
隔离级别配置
查询
修改
隔离级别加锁示例
表名 students，id 为主键，no 为二级唯一索引，name 和 age 为二级非唯一索引，score 无索引。
分支主题
聚簇索引
二级唯一索引
二级非唯一索引
无索引
范围查询
聚簇索引
二级非唯一索引
使用事务隔离级别带来的问题
性能
数据
公共条件
分支主题
Dirty Read
Unrepeatable Read
Phantom Read
Lost Update
提交覆盖(一类丢失更新)
回滚覆盖(二类丢失更新)
隔离级别关系
分支主题
一致性非锁定读
性能优化
索引层优化
类型
普通索引
唯一索引
全文索引
组合索引
索引条件图示
index key
Last Key
First Key
index filter
table filter
ICP过程
相关博客
EXPALIN命令
id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
extra
语法细节优化
SELECT
LIKE
JOIN ON
ORDER BY
WHERE
OR
COUNT
慢查询日志
配置方法
分析工具
MySQL 自带分析工具 mysqldumpslow
mysqlsla
Query Profiler
Performance Schema
MySQL 5.7 四个基本的库
服务器层优化
Buffer Pool
提高Buffer容量
手动内存预热
自动内存预热
降低磁盘IO
设置 Redo Log 大小
innodb_log_file_size 设置成 innodb_buffer_pool_size * 0.25
关闭部分与数据不相关的日志
更改 Force Log at Commit 机制
提高磁盘读写能力
增加磁盘
使用SSD
参数优化
内存参数
线程独享
sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size
线程共享
innodb_buffer_pool_size
key_buffer_size
tmp_table_size
max_head_table_size
事务日志
innodb_log_file_size
innodb_log_files_in_group
innodb_log_buffer_size
innodb_flush_log_at_trx_commit
IO
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_doublewrite = 1
delay_key_write
innodb_read_io_threads
innodb_io_capacity
innodb_flush_neighbors
sync_binlog
其他参数
expire_logs_days
max_allowed_packet
skip_name_resolve
read_only
skip_slave_start
sql_mode
max_connections
高可用
分布式理论
Consistency
线性一致性
顺序一致性
ZooKeeper实现的一致性
弱一致性
最终一致性
因果一致性
读己所写一致性
会话一致性
单调读一致性
单调写一致性
Consenus
分布式协议
分布一致性
分布式事务
推荐框架
JTA
seata
RocketMQ
X/Open Specification(XA规范)
本地事务
全局事务
二阶段提交协议
图示
正常状态
失败状态
Fail-stop Failures模型
角色
Coordinator（协调者）
Participant（参与者，也叫 Voter 投票者）
阶段
1. Commit request phase
2. Commit phase
细节描述
1. 提交请求阶段
2. 提交阶段
提交事务
中断事务
Crash模型
协调者Crash，参与者正常
协调者正常，参与者Crash
参与者无法恢复
参与者可恢复
协调者Crash，参与者Crash
协调者与参与者在第一阶段Crash
协调者在第二阶段Crash
参与者在agree阶段Crash
参与者在commit阶段Crash
Fail-stop Failures模型
三阶段提交协议
图片示例
正常状态
异常状态
细节描述
CanCommit
PreCommit
所有参与者反馈agree
有参与者响应超时，或者反馈aborted
DoCommit
所有参与者反馈agree
有参与者超时，或者反馈aborted
Network Partition（网络分区情况）
TCC提交协议
Try
完成业务检查、预留业务资源
Confirm
使用预留的资源执行业务操作（需要保证幂等性）
Cancle
取消执行业务操作，释放预留的资源（需要保证幂等性）
Paxos算法
假设A、B、C三个节点
1. Prepare 阶段
2. Accept 阶段
ACID
CAP
Consistence（一致性）
Availability（可用性）
Partition Tolerance（分区容错性）
CAP 伪二分法
原因
重CA轻P
重CP轻A
重AP轻C
ACID与CAP一致性区别
ACID
内部一致性注重于事务前后数据的完整性
节点服务器的数据完整性
本质区别
CAP
外部一致性则注重于读写数据或主从数据的一致性
分布式多服务器之间复制数据以取得这些服务器拥有同样的数据，这是一种分布式领域的一致性概念
本质区别
可线性化
BASE
Basically Available（基本可用）
Soft State（软状态）
Eventually Consistent（最终一致性）
分库分表
分库分表
水平分割
垂直分割
第三方插件
Shareding JDBC
MyCat
节点复制
服务器间外部复制
主从模式
Asynchronous Replication
逻辑原理
技术实现
问题
Fully synchronous Replication
逻辑实现
技术实现
存在问题
Semisynchronous Replication
逻辑实现
技术实现
半同步退化机制
存在问题
Lossless Replication
逻辑实现
技术实现
存在问题
半同步复制与无损复制区别
半同步复制
无损复制
MGR模式
特性
数据一致性
事务并发冲突处理
节点故障自动检测
组成员自动管理
容错能力
基于Paxos多主更新
示例
分支主题
Certify
冲突解决方式
单主模式
多主模式
MGR使用
前置条件
仅支持 InnoDB 引擎
仅支持IPv4
必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set
每张表必须有主键，用于做write set冲突检测
限制条件
一个MGR集群最多支持9个节点
官方建议 READ COMMITTED 隔离级别
SELECT ... FOR UPDATE 可能造成死锁
不支持外键：多主不支持，单主模式不存在此问题
不支持外键于save point特性，无法做全局间的约束检测与部分部分回滚
二进制日志不支持 binlog event checksum
不支持 SERIALIZABLE 事务隔离级别
COMMIT可能会失败，类似于快照事务隔离级别的失败场景
DDL 无法作为事务处理，存在一些问题
不支持 Replication event checksums，需要在 my.cnf 里面配置
Single-Primary Mode
示图
Multi-Primary Mode
示图
服务器间内部复制
基于Binlog复制
statement-based replication
优势
binlog文件较小
主从数据库版本可以不一样
缺点
不定函数或者功能会让复制出问题
复制过程可能会有全表扫描
有AUTO_INCREMENT字段，会有阻塞
复杂语句如果执行出错会消耗更多资源
row-based replication
优点
对函数、存储过程、触发器等机制兼容
更小锁粒度
支持多线程复制
缺点
binlog日志量大
对大事务执行效率不高
UDF产生的大BLOB值影响复制效率
不能直接查看binlog日志
mixed-based replication
基于GTID复制
GTID
source_id
transaction_id
示例
工作原理
GTID在binlog中的结构
优点
更简单的实现 failover 做到主从切换自动化
更简单的搭建主从复制。
数据比传统的复制更加安全。
GTID 是连续的没有空洞的，保证数据的一致性，零丢失。
缺点
只支持 InnoDB 引擎
必须全 GTID 才可以复制
故障处理比较复杂，需要注入空事务
不支持 CREATE TABLE ... SELECT 语句，因为该语句会被拆成 CREATE TABLE 和 INSERT 两个事务，并且两个事务被分配同一个 GTID，导致 INSERT 操作直接 Skip
不支持CREATE TEMPORARY TABLE、DROP TEMPORARY TABLE 临时表操作
Errant transaction 问题：即从库不能进行任何事物型操作，会引入新的 GTID，当 binlog 被清除后，再进行主从切换，会导致其他从库找不到此 GTID，从而挂载不上
GTID配合SBR、RBR、MBR三者之一混合使用，保证数据一致性
Master配置
Slave配置
高可用选型
基于 MySQL 原生异步或半同步复制协议
MHA
架构
分支主题
MHA Manager
Master
Slave(M)
Slave(N)
工作流程
Failover过程
无法保证数据不会丢失
优点
可以进行故障的自动检测和转移
可扩展性较好，可以根据需要扩展MySQL的节点数量和结构
相比于双节点的 MySQL 复制，三节点/多节点的 MySQL 发生不可用的概率更低
支持基于日志点、GTID的复制方式
缺点
至少需要三节点，相对于双节点需要更多的资源
逻辑较为复杂，发生故障后排查问题，定位问题更加困难
数据一致性仍然靠原生半同步复制保证，仍然存在数据不一致的风险
可能因为网络分区发生脑裂现象
需要基于 SSH 免认证配置，存在一定的安全隐患
只监控 master，未监控 slave 状态
MMM
优点
缺点
由于架构里只有一个写入点，所以扩展性是有限的，但是对一般中型企业够用了。解决方案：对于大应用可以采取垂直拆分到多个 MMM 架构的方式，使用 MMM Cluster 来管理
对于读写分离和读负载均衡还是要程序来开发或者使用其他工具完成。
数据不能保证抢一致性，但是保证了高可用
可能因为网络分区发生脑裂现象
存在单点故障
不支持基于 GTID 复制
Zookeeper + Proxy
优点
较好保证了整个系统的高可用
扩展性好，可以扩展为大规模集群
缺点
依然依赖于原生的半同步复制
引入 zk，使系统变得更加复杂
基于分布一致性协议
MGR
MySQL Innodb Cluster(基于MGR方案)
优点
支持多主写入
无脑裂问题
自动故障转移，自动添加、剔除节点，不依赖任何第三方工具
缺点
同 MGR
MySQL NDB Cluster
优点
全部使用官方组件，不依赖于第三方
当 NDB Cluster 关闭时，NDB Cluster 数据节点在 Memory 中保存的数据将写入磁盘，并在 Cluster 启动的下一个 time 重新加载到 Memory。
同步复制，在 NDB Cluster 所有数据节点都保持同步，可实现数据强一致性
缺点
基于内存，数据库集群规模受内存大小限制
配置复杂，必须使用 NDB 存储引擎，与常规存储引擎存在差异，只支持 READ COMMITTED 隔离级别，没有 MVCC
联表 JOIN 性能很差
MariaDB Galera Cluster
功能
同步复制
真正的 multi-master，即所有节点可以同时读写数据库
自动的节点成员控制，失效节点自动被清除
新节点加入数据自动复制
真正的并行复制，基于行级
用户可以直接连接集群，使用感受上与 MySQL 完全一致
优点
因为是多主，所以不存在 Slavelag（延迟）
不存在丢失事务的情况
同时具有读和写的扩展能力
节点间数据是同步的,而 Master/Slave 模式是异步的，不同 Slave 上的 binlog 可能是不同的
缺点
需要打 wsrep 补丁
只支持 InnoDB 存储引擎
不同 Slave 的 binlog 可能不同
0 条评论
下一页
