MySQL_草稿图
2024-07-17 18:01:40 3 举报
登录查看完整内容
这张草稿图详细介绍了MySQL数据库的基本架构和一些核心概念。其中包括MySQL服务器的组件，如服务器、服务和存储引擎等。此外，还提到了MySQL的一些关键特性，如图形用户界面、命令行界面、二进制日志、复制等。最后，还概述了InnoDB引擎的特性，如事务处理、锁机制、MVCC等。
作者其他创作
大纲/内容
Join
方法
导出大小
导出时间
还原时间
SQL导出
727MB
102
600
符号分隔导出
669MB
86
301
主库和备库
写
一个全局节点和六个主-主结构节点的数据存储方式
00094416-6175
0016c91a-6175
002f218e-6177
是否有太多失效
用户全局数据
ValN.1
ValN.2
double write(1MB)
...
SET = 100(加锁)
存储的两个缓存结果集
Val1.m
0
是否多数查询都无法缓存
连接/线程处理
初始状态
数据库服务器
SQL线程
1
18
8
4700
13
3
93
Tbl1
mysql-bin.000001
主键索引
DB2
SQL解析器
文档1
负载均衡器
查询缓存
左边是之前MySQL串行处理单个SQL形态；中间的是目前MySQL版本提供的并行能力，InnoDB引擎并行扫描的形态；最右边的是未来MySQL要发展的形态。
name
age
id
张三
10
张猛
16
4
李四
12
2
王五
否
....
TID
DP
主库2
name like '张%' and age =10
使用不同索引策略的SELECT查询的QPS测试结果
MySQL服务器(主动)
是
查询是否完成预热？
客户端
ANSI SQL隔离级别
备库
合并完成
评论
AkroydKirsten1978-11-02
tbl1
事务2
etc...
B
100
Engine
00147564-6181
事务1
主库
第二步
复制
源卷
InnoDB二级索引叶子节点
主动-被动模式下的主-主复制
2.3
Server
RP
0.初始状态
分区键
一个典型的读密集型网站负载均衡架构
数据
Word1
拥有备库的环形结构
Tbl2
SQL优化器
2.事务1执行插入/查询
Binlog
AkroydChristian1958-12-07
AkroydDebbie1990-03-18
重放
2.1
2.2
2.4
BasingerViven1979-01-24
支持
是否有太多碎片导致的失效
用户文章评论
二进制日志
SET = 100(尝试加锁)
keyN
新闻
两个数据模型，一个易于分片，另一个则难以分片
OK
Keep-Alive连接
Apache工作进程
准备阶段:1.将事务状态设为TRX_PREPARED2.将redolog刷磁盘
共享表实例
blackhole存储引擎
99
设置了log_save_updates
write
(聚簇索引)
并行计划
EMPLOYEE
DEPARTMENT
HEAD
Jones
Accounting
Smith
Engineering
Brown
Gree
写事务日志
元数据管理
已存储数据
空闲空间
数据文件
MySQL主库
1.2
Page 2
Web服务器
查询1
查询2(已存储数据)
缓存块
存储引擎
文件系统
插入UUID：新的记录可能插入到之前记录的中间，导致需要强制移动之前的记录
文档2
AllenCuba1960-01-01
AstaireAngelina1980-03-04
客户端网络
中继日志
Word2
SQL和符号分隔导出所用的备份和恢复时间
redo logundo log重做日志
读
预处理器
快照
二级索引
BarrymoreJulia2000-05-16
是否碎片太多？
查询
QPS(每秒查询次数结果)
INNER JOIN
185QPS
EXISTS子查询
325QPS
文章
第一步
Val2.1
Val2.2
按功能划分
顺序插入到页:每条新纪录总是在前一条记录的后面插入
C
等待缓存预热完成
DB1
事务日志
列值
行号
表名
行数
时间(秒)
索引大小(MB)
userinfo
1 000 000
137
342
userinfo_uuid
180
544
3 000 000
1233
1036
4525
1707
主库1
是否有太多更新操作？
phone
address
12222
蛮逗村
Page 3
查询优化器
隔离级别
脏读可能性
不可重复读可能性
幻读可能性
加锁读
READ UNCOMMITED
Yes
No
READ COMMITTED
REPETABLE READ
SERIALIZABLE
1.事务2执行更新
写线程
源卷的同一逻辑副本
MyISAM非聚簇表分布
Master_Log_File=mysql-bin.000001Read_Master_Log_Pos=1582
第三步write (顺序写)
tbl3
1333
葫芦镇
1555
公平城
1666
深坑洞
col1
3.1
3.2
3.3
3.4
服务器B失效，替换A
当server1崩溃，server2已经追赶上，但server3的复制落后
Val1.1
Val1.2
指向下一个叶子页的链接
数据、索引、undo日志，等等
tbl2
客户端/服务器通信协议
结果存储
只有state_id
只有state_id_2
同时有state_id和state_id_2
MyISAM，Q1
114.96
25.40
112.19
MyISAM，Q2
9.97
16.34
16.37
InnoDB，Q1
108.55
100.33
107.97
InnoDB，Q2
12.12
28.04
28.06
Data
000e2f20-6180
SET = 100 (尝试加锁)
主动
a
b
<其他列>
WHERE子句
...数据...
不符合
符合
Page 1
结果
一主多备结构
3.事务1提交并释放锁
代理
11
21
作为中间价的负载均衡器
(加锁)99
MySQL
prepare
备库Server3
tbl4
主键列(col1)
TID:事务ID
RP:回滚指针
非主键列(col2)
double write buffer(2MB)
99 100
逻辑页，大小依赖于不同的存储引擎，对于InnoDB为16K
2.事务2执行并提交
Memory
mysql-bin.000009
日志线程
page
脏页
302
Log Buffer
存储引擎API
Doublewrite buffer
1.4
AllenMeryl1980-12-12
解析树
9999
(张三，10)
(张猛，16)
OS File System
被动
备库Server2
缩小query_cache_min_res_unit的值或者使用命令FLUSH QUERY CACHE整理碎片
9997
用户1
主库Server1
单实例
In Memory
1.事务1使用共享锁执行插入/查询
InnoDB缓冲池数据页
On Disk
回表-2
用户2
完成
Session
读查询
MySQL从库
增大query_cache_limit
......
9998
命中率是否可以接受？
20
56
3000
62
读线程
数据更改
叶子页:值<key1
age=10
key1<=值<key2
筛掉
当页被插满后，继续插入到新的页
MySQL不支持多主库复制
300
预留区域
80s
136s
72s
470s
数据集
原查询
优化后的查询
示例1
每秒5次查询
示例2
每秒7次查询
每秒53次查询
示例3
每秒2400次查询
每秒2000次查询
00277564-6178
binlog归档日志
InnoDB Buffer Pool
内部节点
A
文件系统层
在使用不同索引策略时插入100万行数据的速度
MyISAM
COMMIT
完成。该场景不适合查询缓存
执行器
使用中间件来在各数据中心间路由MySQL连接
key1
写时复制技术如何减少单个卷快照需要的大小
ValN.m
引擎层
3.事务1提交(稍后在备库上)
索引列(col2)
D
值>=keyN
1.1
1.3
完成。查询无法被缓存
注意:即使缺失该步骤，也不影响事务提交成功1.将commit标记写入表名事务提交完成 2.将事务设为TRX_NOT_STARTED状态
commit
Tablespace
优化器
服务器A
连接VARCHAR和ENUM列的速度
金字塔形复制拓扑
测试
QPS
VARCHAR 关联 VARCHAR
2.6
VARHCAR 关联 ENUM
1.7
ENUM关联VARCHAR
1.8
ENUM关联ENUM
3.5
一个主库、一个分发主库和多个备库
来自高层节点页的指针
增大query_cache_size
叶子节点根据col1列排序
Val2.m
服务器A失效，替换B
叶子节点根据col2列排序
主-主复制
有其他的错误配置
服务器B
Buffer Pool
InnoDB(聚簇)表分布
Example
Meaning
dinosaur
包含\"dinosaur\"的行rank值更高
~dinosaur
包含\"dinosaur\"的行rank值更低
+dinosaur
行记录必包含\"dinosaur\"
-dinosaur
行记录不可以包含\"dinosaur\"
dino*
包含以\"dino\"开头的单词的行rank值更高
2.事务2使用排他锁执行更新被阻塞
回表-1
Server层
循环连续写入
存储完成
AllenKim1930-07-12
4.事务2继续执行并提交
91
92
BasingerViven1976-12-08
recovery崩溃恢复第二步
InnoDB
MySQL服务器
Row
第四步 write(离散写)
分片数据存储
从单个实例到按功能划分的数据存储
连接层
查询执行引擎
写查询
解析器
并行扫描
日志文件
5
将复制事件传递到更多的多备库
一个门户网站以及专用于不同功能区域的节点
操作系统缓存
并行计算
查询2
NOT EXISTS子查询
360PQS
LEFT OUTER JOIN
425QPS
8035
UPDATE
8078
DELETE
8167
INSERT
SQL
InnoDB IO线程
用户3
应用对源卷做了更改
应用服务器
查询1(已存储数据)
(李四，12)
(王五，12)
InnoDB聚簇索引叶子节点
IO线程
MyISAM表layout_test的数据分布
recovery崩溃恢复第一步
被写满且已经刷到磁盘上的页可能会被重新读取
数据文件 .ibd
1.事务1无锁执行插入/查询
Key+PK cols
Master_Log_File=mysql-bin.000001Read_Master_Log_Pos=1493
大小:4KB
结果数据行
查询执行计划
指向数据的指针(依赖于不同存储引擎)
两个事务更新数据，但未使用共享锁串行化更新
环形复制拓扑
两个事务更新数据，使用共享锁串行化更新
采用blackhole存储引擎的分发主库
论坛
完成，查询从来不曾被处理过
内存实例
MySQL服务器(被动)
query_cache_limit设置是否够大？
redo log
代理可以使Apache不被长连接拖垮，产生更少的Apache工作进程
拥有备库的主-主结构
col2
InnoDB存储引擎层
1450
1493
1582
表
fsync
0 条评论
回复 删除
下一页
