数据库核心知识
2021-01-28 22:45:43 0 举报AI智能生成
数据库核心知识学习笔记
作者其他创作
大纲/内容
SQL语言
数据定义语言：简称【DDL】(Data Definition Language)，用来定义数据库对象：数据库，表，列
等。关键字：create，alter，drop等
等。关键字：create，alter，drop等
数据操作语言：简称【DML】(Data Manipulation Language)，用来对数据库中表的记录进行更
新。关键字：insert，delete，update等
新。关键字：insert，delete，update等
数据控制语言：简称【DCL】(Data Control Language)，用来定义数据库的访问权限和安全级别，
及创建用户；关键字：grant等
及创建用户；关键字：grant等
数据查询语言：简称【DQL】(Data Query Language)，用来查询数据库中表的记录。关键字：
select，from，where等
select，from，where等
MySQL
架构
连接器Connectors
系统管理和控制工具（Management Serveices & Utilities）
连接池（Connection Pool）
SQL接口（SQL Interface）
解析器（Parser）
查询优化器（Optimizer）
查询缓存（Cache和Buffer）
存储引擎（Pluggable Storage Engines）
MyISAM
高速引擎，拥有较高的插入，查询速度，但不支持事务、不支持行
锁、支持3种不同的存储格式。包括静态型、动态型和压缩型。
锁、支持3种不同的存储格式。包括静态型、动态型和压缩型。
InnoDB
5.5版本后MySQL的默认数据库，支持事务和行级锁定，事务处
理、回滚、崩溃修复能力和多版本并发控制的事务安全，比
MyISAM处理速度稍慢、支持外键（FOREIGN KEY）
理、回滚、崩溃修复能力和多版本并发控制的事务安全，比
MyISAM处理速度稍慢、支持外键（FOREIGN KEY）
Memory
内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和
数据量成正比的内存空间。只在内存上保存数据，意味着数据可能
会丢失
数据量成正比的内存空间。只在内存上保存数据，意味着数据可能
会丢失
物理结构
MySQL是通过文件系统对数据和索引进行存储的。
MySQL从物理结构上可以分为日志文件和数据索引文件
MySQL在Linux中的数据索引文件和日志文件都在/var/lib/mysql目录下
日志文件采用顺序IO方式存储、数据文件采用随机IO方式存储。
日志
慢查询日志（slow query log）
重做日志（redo log）
回滚日志（undo log）
中继日志（relay log）
索引
聚集(主键)索引
InnoDB
叶子节点存储数据
MyISAM
叶子节点存储地址
非聚集索引
InnoDB
叶子节点存储主键值
MyISAM
叶子节点存储地址
锁
乐观锁
程序实现
版本号
时间戳
悲观锁
表级锁
表锁(MySQL Layer) 手动加
read lock
读锁后可以加读锁,不能加写锁
write lock
写锁后不能加读锁,也不能加写锁
元数据锁(MySQL Layer) 自动加
CURD加读锁
DDL 加写锁
意向锁(InnoDB) 内部使用
意向共享锁IS
意向排他锁IX
行级锁(InnoDB)
按范围划分
记录锁
锁定索引中一条记录。 主键指定 where id=3
间隙锁
锁定记录前、记录中、记录后的行 RR隔离级 （可重复读）-- MySQL默认隔离级
邻键锁
记录锁 + 间隙锁
按功能划分
共享读锁S
手动加
select ... lock in share mode
排他写锁X
自动加
DML
手动加
select ... for update
基于索引实现,只有通过索引查询时才使用行级锁,否则使用表级锁
锁算法
Record Lock
Gap Lock
Next-Key Lock
在 InnoDB 存储引擎中，SELECT 操作的不可重复读问题通过 MVCC 得到了解决，而 UPDATE、DELETE 的不可重复读问题通过 Record Lock 解决，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。
锁问题
脏读
脏读指的是不同事务下，当前事务可以读取到另外事务未提交的数据。
不可重复读
不可重复读指的是同一事务内多次读取同一数据集合，读取到的数据是不一样的情况。
幻读
指在同一事务下，连续执行两次同样的 sql 语句可能返回不同的结果，第二次的 sql 语句可能会返回之前不存在的行。
丢失更新
一个事务的更新操作会被另一个事务的更新操作所覆盖。
事务
ACID
Atomicity（原子性）
构成事务的的所有操作必须是一个逻辑单元，要么全部执行，要么全部不
执行。
执行。
Consistency（一致性）
数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的。
Isolation（隔离性）
一个事务所做的修改在最终提交以前，对其他事务是不可见的。
未提交读
事务中的修改，即使没有提交，对其他事务也是可见的。
提交读
一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其他事务是不可见的。
可重复读
保证在同一个事务中多次读取同样数据的结果是一样的。
可串行化
强制事务串行执行。
Durability（持久性)
事务执行成功后必须全部写入磁盘。
MVCC
提交读
可重复读
版本号
系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
事务版本号：事务开始时的系统版本号。
隐藏的列
MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：
创建版本号：指示创建一个数据行的快照时的系统版本号；
删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。
Undo 日志
MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。
在 InnoDB 存储引擎中，SELECT 操作的不可重复读问题通过 MVCC 得到了解决，而 UPDATE、DELETE 的不可重复读问题通过 Record Lock 解决，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。
分库分表
水平切分
水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。
Sharding 策略
哈希取模：hash(key)%N
范围：可以是 ID 范围也可以是时间范围
映射表：使用单独的一个数据库来存储映射关系
Sharding 存在的问题
事务问题
使用分布式事务来解决，比如 XA 接口
连接
可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。
唯一性
使用全局唯一 ID （GUID）
为每个分片指定一个 ID 范围
分布式 ID 生成器（如 Twitter 的 Snowflake 算法）
垂直切分
垂直切分是将一张表按列分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。
复制
主从复制
binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程 ：负责从主服务器上读取- 二进制日志，并写入从服务器的中继日志（Relay log）。
SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。
读写分离
主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
关系型数据库的设计
函数依赖
记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。
如果 {A1，A2，... ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。
对于 A->B，如果能找到 A 的真子集 A'，使得 A'-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。
对于 A->B，B->C，则 A->C 是一个传递函数依赖
异常
学生课程关系的函数依赖为 {Sno, Cname} -> {Sname, Sdept, Mname, Grade}，键码为 {Sno, Cname}。也就是说，确定学生和课程之后，就能确定其它信息。
冗余数据：例如 学生-2 出现了两次。
修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。
范式
第一范式 (1NF)
属性不可分。
第二范式 (2NF)
每个非主属性完全函数依赖于键码。
可以通过分解来满足。
可以通过分解来满足。
0 条评论
下一页
