高性能MySQL实战学习笔记~1

一 MySQL体系结构

1. Client Connectors

• 处理客户端的连接请求, MySQL支持所有的连接类型

2. MySQL Server

• 连接处理: 响应客户端连接请求, 线程池资源管理
• 用户鉴权: 用户登录身份鉴权
• 安全管理: 用户执行操作权限校验

3.Service & utilities

• 管理服务&工具集: 备份恢复, 安全管理, 集群管理服务&工具

4.SQL Interface

• SQL接口: 接收用户的SQL命令并处理

5.Parser

• SQL解析器: 解析查询语句, 生成语法树, 解析器会查询缓存

6.Optimizer

• 查询优化器: 对查询语句进行优化, 选择合适索引

7.Caches

• 缓存: 全局缓存和session缓存

8.Pluggable Storage Engines

• 存储引擎层: 一种文件访问机制, 与文件交互, 插件式存储引擎

9.File System

• 物理文件存储层: 物理文件的存储, 含redolog, undolog, binlog, error_log, query_log, slow_log, ibd, frm等

二 SQL的执行流程

1. select

2. insert

待完善…

三 存储引擎及其分类

1. 存储引擎

• MySQL中具体与文件打交道的子系统, 根据MySQL公司提供的文件访问层抽象接口定制的一种文件访问机制

2. 分类

• MyISAM
• InnoDB
• Memory
• NDB
• Archive
• Blackhole
• Merge
• CSV
• TokuDB
• RocksDB
• MyRocks
• ArkDB
• Federated
• Example
• PERFORMANCE_SCHEMA
• MRG_MYISAM

四 InnoDB存储引擎体系结构

1. 实例层

1-1 master thread(优先级最高)

• 负责调度其他各线程, 内部包含主循环(1s和10s), 后台循环, 刷新循环
• 主循环的两种操作
  • 1s
    • 第一步: 日志缓冲刷新到磁盘(会总是操作, 即使事务还没有提交)
    • 第二步: 最多可能刷100个新脏页到磁盘
    • 第三步: 执行和并改变缓冲的操作
    • 第四步: 若当前没有用户活动可能切换到后台循环等
  • 10s
    • 第一步: 刷新可能最多100个脏页到磁盘
    • 第二步: 合并至多5个改变缓冲(总是)
    • 第三步: 日志缓冲刷新到磁盘(总是)
    • 第四步: 删除无用的undo页(总是)
    • 第五步: 刷新100个或者10个脏页到磁盘(总是)产生一个检查点(总是)等

1-2 buf dump thread

• 负责把 buffer pool 中的内容 dump 到物理文件中, 以便快速加热数据

1-3 page cleaner thread

• 负责把buffer pool中的脏页刷新到磁盘, 5.7版本之后才出现

1-4 purge thread

• 负责将不再使用的undo日志进行回收

1-5 read thread

• 处理用户的读请求, 并负责将数据页从磁盘上读出来

1-6 write thread

• 负责将数据页从缓冲区写出到磁盘

1-7 redo log thread

• 负责把日志缓冲中的内容刷新到 redo log 文件中

1-8 insert buffer thread

• 负责把nsert buffer中的内容刷新到磁盘

1-9 Buffer Pool

buffer pool文件决定了一个SQL执行的快慢

单机单实例参考配置: 物理内存的 60% - 80%

多机多实例: 不用超过物理内存的 80%

看 show global status like '%buffer_pool_wait%'的值

(5.7以后版本buffer pool大小可以动态修改, 无需重启数据库实例)

• undo page(undo页)
• insert buffer page(insert buffer页)
• adaptive hash index(自适应hash索引页)
• index page(索引页)
• lock info(锁信息)
• data dictionary(数据字典页)

1-10 redo log buffer

• 是一个循环复用的文件集件
• 负责记录InnoDB中所有对Buffer Pool的物理修改日志
• redo log大小直接影响了我们的数据库的处理能力

1-11 double write buffer

• 主要解决由于宕机引起物理写入操作中断, 数据页不完整的情况

2. 物理层

2-1 系统表空间

• 5.6
  • ibdata文件
    • insert buffer
    • double write
    • 回滚
    • 索引
    • 数据字典
    • undo信息
  • undo文件
• 5.7
  • undo信息(5.7之后从ibdata分离出来)
  • 新增temporary(临时表空间)
• 8.0
  • 数据字典(8.0之后从ibdata分离出来)
  • temporary可以配置多个物理文件

2-2 用户表空间

• .ibd后缀的文件
  • insert buffer的bitma页
  • 叶子页
  • 非叶子页

2-3 redo日志

• 多个redo文件

五 几个主流存储引擎特性

Feature	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes	Yes	Yes	Yes	No	No
Backup/point-in-time recovery (note 1)	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Clustered indexes	No	No	Yes	No	No
Compressed data	N	No	Yes	Yes	No
Data caches	No	N/A	Yes	No	Yes
Encrypted data	Yes(note 3)	Yes(note 3)	Yes(note 4)	Yes(note 3)	Yes(note 3)
Foreign key support	No	No	Yes	No	Yes(note 5)
Full-text search indexes	Yes	No	Yes(note 6)	No	No
Geospatial data type support	Yes	No	Yes	Yes	Yes
Geospatial indexing support	Yes	No	Yes(note 7)	No	No
Hash indexes	No	Yes	No(note 8)	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Replication support(note 1)	Yes	Limited(note 9)	Yes	Yes	Yes
Storage limits	256TB	RAM	64TB	None	384EB
T-tree indexes	No	No	No	No	Yes
Transactions	No	No	Yes	No	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes

六 InnoDB和MyISAM比较

功能	InnoDB	MyISAM
ACID事务	是	否
配置ACID属性	是	否
崩溃恢复	是	否
外键支持	是	否
行级锁力度	是	否
MVCC	是	否

七 InnoDB部分特性

功能	支持	功能	支持
存储限制	64TB	索引高速缓存	是
MVCC	是	数据高速缓存	是
B树索引	是	自适应散列索引	是
集群索引	是	复制	是
压缩数据	是	更改数据字典	是
加密数据	是	地理空间数据类型	是
查询高速缓存	是	地理空间索引	否
事务	是	全文搜索索引	是
锁定粒度	行	集群数据库	否
外键	是	备份和恢复	是
文件格式管理	是	快速索引创建	是
多个缓冲区池	是	PERFORMANCE_SCHEMA	是
更改缓冲	是	自动故障恢复	是

八 InnoDB核心要点

• 表空间
• ARIES
• 数据页
• 内存
• 线程
• 状态查询
• 锁
  • MVCC
  • 锁分类
  • 锁算法
• 事务
  • 事务
  • 事务特性ACID
  • 事务隔离级别