存储引擎
InnoDB
- 支持事务
- 面向在线事务处理(OLTP)
- 多版本并发机制MVCC获得高性能
-
MyISAM
不支持事务、表锁设计，支持全文索引
-
NDB
集群存储引擎
-
Memory
表中数据放到内存里面
-
Archive
只支持Insert和Select操作
- 压缩存储，适合存储归档数据
-
Federated
不存放数据
-
Maria
目标是为了替代MyISAM
-
其他CSV等
InnoDB存储引擎
体系结构
后台线程
- 作用
- 刷新内存池中的数据，保证缓冲池缓冲的是最新数据
- 已修改的数据刷新到磁盘文件
- 发生异常的时候可以恢复正常运行状态
- Master Thread
- 缓冲池数据异步刷新到磁盘，保证数据一致性
- IO Thread
- 负责AIO(Async IO)请求的回调问题
- Purge Thread
- 事务提交后，回收已经使用并分配的undo页
- Page Cleaner Thread
- 将执勤版本中的脏数据刷新独立出来，减轻Master Thread的负担
- 作用
内存池
目的
- 缩短数据库的恢复时间
- 缓冲池不够时，将脏页刷新到磁盘
- 重做日志不可用的时候，刷新脏页
种类
插入缓冲(Insert Buffer)
- Insert Buffer
- 非聚集索引的插入和更新，对应的索引页不再缓冲池中，先放入插入缓冲，然后以一定的频率进行插入缓冲和辅助索引页字节点的Merge
- 索引时辅助索引、索引不是唯一的
- B+树实现
- Change Buffer
- 适用于非唯一的辅助索引
- Merge Insert Buffer
- 辅助索引页被读取到缓冲池时
- Insert Buffer Bitmap 页追踪到该辅助索引页已无页可用空间时
- Master Thread
- Insert Buffer
- 两次写(Double Write)
- 确保数据页的可靠性
- 当用户需要一个页的副本，写入失效发生，先通过页的副本来还原该页，在进行重做
- 自适应哈希索引(Adaptive Hash Index)
- InnoDB监控对应表上各种索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立Hash索引
- 异步IO(Async IO)
- 异步IO处理磁盘操作
刷新临近页(Flush Neighbor Page)
可以把数据库参数看成一个key-value
- 动态参数：运行时可变
-
日志文件
错误日志
- 二进制日志
- 记录所有对MySQL执行更改的所有操作
- 用途
- 恢复
- 复制
- 审计
- 记录所有对MySQL执行更改的所有操作
- 满查询日志
- 可以帮助定位满查询SQL语句
查询日志
-
pid文件
-
表结构定义文件
-
InnoDB存储引擎文件
表空间文件
重做日志文件
主键
表空间
- 默认情况下又一个共享表空间ibdata1，所有数据都存放在这个表空间内
- 段
- 数据段
- B+树的叶子节点
- 索引段
- B+树的非索引节点
- 回滚段
- 数据段
- 区
- 连续页组成的空间，大小为1MB，页为16KB
- 页
- 磁盘管理的最小单位
- 默认大小为16KB
- 数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页
行
Compact行记录格式
- 变长字段长度列表
- 列的长度小于255字节用1字节表示
- 长度大学255字节用2字节表示
- NULL标志位
- 该行数据含有NULL值用1表示
- 占1个字节
- 记录头信息
- 固定5个字节
- 数据项……
- 变长字段长度列表
- Redundant行记录格式
- 字段长度偏移列表
- 列的长度小于255 用1表示
- 列的长度大于255 用2表示
- 记录头信息
- 6个字节
- 列数据……
- 字段长度偏移列表
- 行溢出数据
- 官网65535是指所有varchar列的长度总和
- Compressed和Dynamic行记录格式
- 对于BLOB中的数据采用了完全的行溢出的方式
CHAR的行结构存储
File Header 文件头
- 页的头信息，38个字节
- Page Header 页头
- 数据页的状态信息
- Infimun和Supremum Records
- 每个数据页都会存在两个虚拟的行记录
- Infimun记录是比该页任何主键值都要小的值
- Supremun比任何可能大的值都大的值
- User Records 用户记录即行记录
- 实际存储行记录的内容
- Free Space 空闲空间
- Page Directory 页目录
- 页相对位置，不是偏移量
File Trailer 文件结尾信息
-
约束
数据完整性
- 途径
- 选择合适的数据类型来确保一个数据值满足特定条件
- 外键约束
- 编写触发器
- DEFAULT约束作为强制域完整性的一个方面
- InnoDB约束
- Primary Key
- Unique Key
- Foreign Key
- Default
- Not Null
- 途径
- 约束的创建和查找
- 表创建时就进行约束定义
- 利用Alter Table 命令来创建约束
- 约束和索引区别
- 约束更像时逻辑概念，保证数据的完整性
- 索引是一种数据结构，既有逻辑上的概念，也代表着物理存储方式
- ENUM和Set约束
- 触发器与约束
- 在执行Select、Delete、update之前或者之后自动调用SQL命令或存储过程
外键约束
命名的虚拟表，没有实际的物理存储
物化视图，Oracle存在，MySQL没有
将一个表或者索引分解为多个更小、更可管理的部分
- MySQL支持水平分区
- 局部分区索引：一个分区中即存放了数据又存放了索引
- 分区类型
- RANGE分区
- 行数据基于属于一个给定连续区间的数值被放入分区
- 可以加快查询速度
- 行数据基于属于一个给定连续区间的数值被放入分区
- LIST分区
- 对比与RANGE分区，LIST面向的是离散的值
- 分区的值时离散的
- 对比与RANGE分区，LIST面向的是离散的值
- HASH分区
- 根据用户自定表达式的值来进行分区，该表达式不能返回负值
- 数据均匀分布到预先定义的各个分区
- 根据用户自定表达式的值来进行分区，该表达式不能返回负值
- KEY分区
- 利用MySQL提供的哈希函数进行分区
- COLUMNS分区
- 可以直接使用非整形的数据进行分区
- 如果表中存在主键或者唯一索引时，分区列必须时唯一索引的一个组成部分
- RANGE分区
- 子分区
- 在分区的基础上再进行分区也称复合分区
- 每个子分区的数量必须一致
- 要在任何分区上使用SUBPARTITION定义子分区必须定义所有的子分区
- 每个SUBPARTITION子句必须包括子分区的一个名字
- 子分区的名字必须时唯一的
分区中的NULL值
概述
- 平衡查找树，所有记录节点都是按键值的大小顺序存放在同一层叶子节点上，由各叶子节点的指针进行连接
操作
聚集索引
- 按主键构造树，叶子节点中存放的即为整张表的行记录数据
- 一张表只有一个聚集索引
- 数据页(叶子节点)存放的完整的数据，而非叶子节点存的是键值和指向数据页的偏移量
- 对于主键的排序和查找速度以及范围查询速度非常快
- 辅助索引
- 也称非聚集索引
- 叶子节点包括键值和书签(bookmark)，书签表示哪里可以找到索引相对应的行数据
- 先通过辅助索引找到叶子节点的指针也就是主键索引的主键，然后再去主键索引来找到完整的行记录
- 索引分裂
索引管理
概念
- 索引中不重复记录数据的预估值
- 在访问高选择性属性字段并从表中取出很少一部分数据的时候，对这个字段添加B+树索引很有必要
统计
联合索引
- 表上的多个列进行索引
- 覆盖索引
- 从辅助索引中就可以查询到的记录，而不需要查询聚集索引中的记录
- 优化器不实用索引的情况
- 多发生于范围查找、JOIN连接操作等
- 索引提示
- 优化器错误的选择了某个索引
- 语句可以选择的索引非常多
- Multi-Range Read优化
- 优点
- 数据访问变得较为顺序
- 减少缓冲池中页被替换的次数
- 批量处理对键值的查询操作
- 工作方式
- 将查询得到的辅助索引存放于一个缓存中
- 将缓存中的键值根据ROWID进行排序
- 根据RowId的排序顺序来访问实际的数据文件
- 优点
Index Condition PushDown ICP优化
哈希表
- 哈希冲突
- 哈希函数
- InnoDB
- 哈希算法来进行字典的查找，冲突机制为链表的方式
-
全文检索
概述
- 将存储数据库中的整本书或者整篇文章中的任意内容信息查找出来的技术
- 倒排索引
- invert file index {单词，单词所在的文档ID}
- full inverted index {单词，(单词所在文档ID，在具体文档中的位置)}
- InnoDB全文检索
- 限制
- 每张表只能有一个全文检索的索引
- 多列组成的全文检索的索引必须相同的字符集和排序规则
- 不支持没有单词界定符的语言
- 限制
全文检索
管理对共享资源的并发访问
lock与latch
行级锁
- 共享锁 S Lock 允许事务读一行数据
- 排他锁 X Lock 允许事务删除或更新一行数据
- 意向锁即表级别锁
- 意向共享锁 IS Lock 事务想要获得一张表中某几行共享锁
- 意向排他锁 IX Lock 事务想要获得一张表中某几行的排他锁
- 意向锁不会阻塞除全表扫描以外的任何请求
- 一致性的非锁定读
- 一致性锁定读
- 自增长与锁
外键与锁
行锁
- Record Lock 单行记录的锁
- Gap Lock 间隙锁 锁一个范围，但不包含记录本身
- Next-Key Lock 两者结合，锁一个范围且包含记录本身
幻想问题Phantom Problem
脏读
- 脏页与脏数据的区别：脏页在缓冲池别修改但没刷新到磁盘，脏数据指事务对缓冲池中行修改还没有提交
- 未提交数据，如果读到了脏数据，即一个事务可以读到另一个事务中未提交的数据
- 违反事务的隔离性
- 不可重复读
- 一个事务内多次读取同一数据集合，但是本事务未结束，另一个事务也访问同一数据集合，并做了DML操作
- 脏读是未提交的数据，不可重复读是已经提交的数据
- 违反事务的一致性
丢失更新
-
死锁
概念
- 争夺锁资源造成的一种互相等待的现象
- 处理办法：超时、wait-for graph等待图
- 概率
- 系统中事务的数量n，数量越多发生死锁的概率越大
- 每个事务操作的数量r 越多概率越大
- 操作数据的集合R，越小概率越大
示例
指将当前锁的粒度降低
-
事务
基本概念
概念
- A原子性
- C一致性 事务开始前和结束后完整性约束没有被破坏
- I隔离性 事务提交前对其他事务不可见
- D持久性 一旦提交结果就是永久的
- 子主题 5
分类
基本概念
- redo log 重做日志，保证原子性和持久性，恢复事务修改的页操作，物理日志，记录页的物理修改操作
- undo log 保证一致性，回滚行记录到某个特定版本，逻辑日志，根据每行记录就行记录
- redo
- 概念
- 组成
- 内存中的重做日志缓冲国
- 重做日志文件
- 组成
- 事务提交时，必须将所有日志写入重做日志文件中进行持久化
- 基本是按照顺序写的
- 区别binlog
- 重做日志是引擎层产生的，Binlog是数据库上上层产生的
- 记录内容不一样，二进制逻辑日志，对应的是SQL语句，重做日志是物理格式的，记录每个页的修改
- 写入磁盘的时间点不一致，binlog在事务提交完成后进行一次写入，重做日志在事务中不断写入
- log block
- 重做日志以512字节进行存储，以块的形式保存
- log group
- 重做日志组
- 格式
- redo_log_type 重做日志类型
- space 表空间的id
- page_no 页的偏移量
- LSN
- Long Sequence Number 日志序列号
- 含义
- 重做日志的写入总量
- checkpoint的日志位置
- 页的版本
- Long Sequence Number 日志序列号
- 恢复
- 基于页的物理日志，恢复速度快
- 概念
undo
start transaction ｜ begin 显示开启
- Commit
- RollBack
- SavePoint indentifier
- Release SavePoint indentiifer
- RollBack to SavePoint
-
事务操作统计
TPS
- 每秒事务处理能力=(com_commit+com_rollback)/time
QPS
Read Uncommitted
- Read Committed
- Repeatable Read
- Serializable
InnodDB默认是Repeatable Read 使用Next-Key Lock锁来避免幻读问题
分布式事务
隔离级别必须是Serializable
- 组成
- 多个资源管理器
- 提供访问事务资源的方法，通常一个数据库就是一个资源管理器
- 一个事务管理器
- 协调参与全局事务中的各个事务
- 一个应用程序
- 定义事务的边界，指定全局事务中的操作
- 多个资源管理器
两段式提交
在循环中提交
- 使用自动提交
-
长事务
执行时间较长的事务
-
备份与恢复
概述
按备份的方法分
- 热备Hot Backup
- 冷备Cold Backup
- 温备 Warn Backup
- 按备份后文件内容
- 逻辑备份
- 裸文件备份
按备份内容
备份frm文件，共享表空间文件，独立表空间文件，重做日志文件
- 优点
- 备份简单，只需要复制相关文件就行
- 易于在不同操作系统、版本上恢复
- 恢复简单，把文件恢复到指定位置就行
- 恢复速度快，不需要执行任何SQL语句
缺点
mysqldump
- 备份出表结构和数据，都是SQL语句的方式
- select … into outfile
- 导出表中的数据
- 逻辑备份的恢复 mysqldump执行对应的sql文件就可以
- load data infile
-
二进制日志的备份与恢复
热备
ibbackup
- 高性能热备
- 在线备份，不阻塞SQL语句
- 实质复制数据库文件和重做日志
- 支持压缩备份
- 跨平台支持
- 恢复表空间文件
- 应用重做日志
-
复制
概述
- 原理
- 主服务器把数据更改记录记到binlog中
- 从服务器把主服务器的binlog复制到自己的relay log中
- 从服务器重做中继日志中的文件，把更改应用到自己的数据库上，保持数据一致性
- 异步实时
- 从服务两个线程
- 一个I/O，负责读取主服务器的二进制日志
- SQL线程，复制执行中继日志
- 原理
- 快照+复制的备份架构
- 复制功能
- 数据分布
- 读取的负载平衡
- 数据库备份
- 高可用性和故障转移
- 复制功能
若有收获，就点个赞吧
0 人点赞
