数据库
2020-05-09 19:06:31 0 举报AI智能生成
数据库
作者其他创作
大纲/内容
索引
概念
索引（Index）是帮助 MySQL 高效获取数据的数据结构。常见的查询算法,顺序查找,二分查找,二
叉排序树查找,哈希散列法,分块查找,平衡多路搜索树 B 树（B-tree）
叉排序树查找,哈希散列法,分块查找,平衡多路搜索树 B 树（B-tree）
索引的常见原则
见pdf
数据库三范式
第一范式(1st NF －列都是不可再分)
第二范式(2nd NF －每个表只描述一件事情)
第三范式(3rd NF － 不存在对非主键列的传递依赖)
数据库是事务， ACID 属性
原子性（ Atomicity ）
事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执
行。
行。
一致性（ Consistency ）
当事务完成时，数据必须处于一致状态。
隔离性（ Isolation ）
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方
式依赖于或影响其他事务
式依赖于或影响其他事务
永久性（ Durability ）
事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性
数据库锁
行级锁
行级锁是一种排他锁，防止其他事务修改此行；在使用以下语句时，Oracle 会自动应用行级锁：
1. INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
2. SELECT … FOR UPDATE 语句允许用户一次锁定多条记录进行更新
3. 使用 COMMIT 或 ROLLBACK 语句释放锁。
1. INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
2. SELECT … FOR UPDATE 语句允许用户一次锁定多条记录进行更新
3. 使用 COMMIT 或 ROLLBACK 语句释放锁。
表级锁
表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使
用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁
（排他锁）。
用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁
（排他锁）。
页级锁
页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级
冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB 支持页级锁
冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB 支持页级锁
分区分表
分库分表有垂直切分和水平切分两种
垂直切分 ( 按照功能模块
将表按照功能模块、关系密切程度划分出来，部署到不同的库上。例如，我们会建立定义数
据库 workDB、商品数据库 payDB、用户数据库 userDB、日志数据库 logDB 等，分别用于
存储项目数据定义表、商品定义表、用户数据表、日志数据表等。
据库 workDB、商品数据库 payDB、用户数据库 userDB、日志数据库 logDB 等，分别用于
存储项目数据定义表、商品定义表、用户数据表、日志数据表等。
水平切分 ( 按照规则划分存储
当一个表中的数据量过大时，我们可以把该表的数据按照某种规则，例如 userID 散列，进行
划分，然后存储到多个结构相同的表，和不同的库上。
划分，然后存储到多个结构相同的表，和不同的库上。
三阶段提交协议
概念
三 阶 段 提 交 （ Three-phase commit ） ， 也 叫 三 阶 段 提 交 协 议 （ Three-phase commit
protocol），是二阶段提交（2PC）的改进版本。
与两阶段提交不同的是，三阶段提交有两个改动点。
1、引入超时机制。同时在协调者和参与者中都引入超时机制。
2、在第一阶段和第二阶段中插入一个准备阶段。保证了在最后提交阶段之前各参与节点的状态是
一致的。也就是说，除了引入超时机制之外，3PC 把 2PC 的准备阶段再次一分为二，这样三阶段
提交就有 CanCommit、PreCommit、DoCommit 三个阶段。
protocol），是二阶段提交（2PC）的改进版本。
与两阶段提交不同的是，三阶段提交有两个改动点。
1、引入超时机制。同时在协调者和参与者中都引入超时机制。
2、在第一阶段和第二阶段中插入一个准备阶段。保证了在最后提交阶段之前各参与节点的状态是
一致的。也就是说，除了引入超时机制之外，3PC 把 2PC 的准备阶段再次一分为二，这样三阶段
提交就有 CanCommit、PreCommit、DoCommit 三个阶段。
CanCommit 阶段
协调者向参与者发送 commit 请求，参与者如果可以提交就返回 Yes 响应，否则返回 No 响应。
PreCommit 阶段
协调者根据参与者的反应情况来决定是否可以继续进行，有以下两种可能。假如协调者从所有的
参与者获得的反馈都是Yes响应，那么就会执行事务的预执行假如有任何一个参与者向协调者发送
了 No 响应，或者等待超时之后，协调者都没有接到参与者的响应，那么就执行事务的中断。
参与者获得的反馈都是Yes响应，那么就会执行事务的预执行假如有任何一个参与者向协调者发送
了 No 响应，或者等待超时之后，协调者都没有接到参与者的响应，那么就执行事务的中断。
doCommit 阶段
该阶段进行真正的事务提交，主要包含 1.协调这发送提交请求 2.参与者提交事务 3.参与者响应反
馈（ 事务提交完之后，向协调者发送 Ack 响应。）4.协调者确定完成事务。
馈（ 事务提交完之后，向协调者发送 Ack 响应。）4.协调者确定完成事务。
CAP
CAP 原则又称 CAP 定理，指的是在一个分布式系统中， Consistency（一致性）、 Availability
（可用性）、Partition tolerance（分区容错性），三者不可得兼。
（可用性）、Partition tolerance（分区容错性），三者不可得兼。
一致性（C ）
在分布式系统中的所有数据备份，在同一时刻是否同样的值。（等同于所有节点访问同一份
最新的数据副本）
最新的数据副本）
可用性（A
在集群中一部分节点故障后，集群整体是否还能响应客户端的读写请求。（对数据更新具备
高可用性）
高可用性）
分区容忍性（P ）
以实际效果而言，分区相当于对通信的时限要求。系统如果不能在时限内达成数据一致性，
就意味着发生了分区的情况，必须就当前操作在 C 和 A 之间做出选择。
就意味着发生了分区的情况，必须就当前操作在 C 和 A 之间做出选择。
存储引擎
存储引擎主要有： 1. MyIsam , 2. InnoDB, 3. Memory, 4. Archive, 5. Federated
InnoDB 底层存储结构为B+树
适用场景：
1）经常更新的表，适合处理多重并发的更新请求。
2）支持事务。
3）可以从灾难中恢复（通过 bin-log 日志等）。
4）外键约束。只有他支持外键。
5）支持自动增加列属性 auto_increment
1）经常更新的表，适合处理多重并发的更新请求。
2）支持事务。
3）可以从灾难中恢复（通过 bin-log 日志等）。
4）外键约束。只有他支持外键。
5）支持自动增加列属性 auto_increment
TokuDB （ Fractal Tree-节点带数据 ）
TokuDB 在线添加索引，不影响读写操作, 非常快的写入性能， Fractal-tree 在事务实现上有优
势。 他主要适用于访问频率不高的数据或历史数据归档。
势。 他主要适用于访问频率不高的数据或历史数据归档。
MyIASM，MySQL默认的引擎
ISAM 执行读取操作的速度很快
缺点是它不 支持事务处理。
不支持行级锁和外键，因此当 INSERT(插入)或 UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。
缺点是它不 支持事务处理。
不支持行级锁和外键，因此当 INSERT(插入)或 UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。
Memory，（也叫 HEAP）堆内存
MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用
HASH 索引。但是一旦服务关闭，表中的数据就会丢失掉。 Memory 同时支持散列索引和 B 树索
引，B树索引可以使用部分查询和通配查询，
HASH 索引。但是一旦服务关闭，表中的数据就会丢失掉。 Memory 同时支持散列索引和 B 树索
引，B树索引可以使用部分查询和通配查询，
存储过程( 特定功能的 SQL 语句集）
存储过程优化思路：
1. 尽量利用一些 sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
2. 中间结果存放于临时表，加索引。
3. 少使用游标。sql 是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如
对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次
读取。
4. 事务越短越好。sqlserver 支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成
并发操作的阻塞，死锁。导致查询极慢，cpu 占用率极地。
5. 使用 try-catch 处理错误异常。
6. 查找语句尽量不要放在循环内。
1. 尽量利用一些 sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
2. 中间结果存放于临时表，加索引。
3. 少使用游标。sql 是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如
对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次
读取。
4. 事务越短越好。sqlserver 支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成
并发操作的阻塞，死锁。导致查询极慢，cpu 占用率极地。
5. 使用 try-catch 处理错误异常。
6. 查找语句尽量不要放在循环内。
触发器(一段能自动执行的程序）
触发器是当对某一个表进行操作时触发。诸如：update、insert、delete 这些操作的时候，系统
会自动调用执行该表上对应的触发器。
会自动调用执行该表上对应的触发器。
数据库并发策略
乐观锁
乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据；悲观锁就刚好相反，觉得自
己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度；时间
戳就是不加锁，通过时间戳来控制并发出现的问题。
己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度；时间
戳就是不加锁，通过时间戳来控制并发出现的问题。
悲观锁
悲观锁就是在读取数据的时候，为了不让别人修改自己读取的数据，就会先对自己读取的数据加
锁，只有自己把数据读完了，才允许别人修改那部分数据，或者反过来说，就是自己修改某条数
据的时候，不允许别人读取该数据，只有等自己的整个事务提交了，才释放自己加上的锁，才允
许其他用户访问那部分数据。
锁，只有自己把数据读完了，才允许别人修改那部分数据，或者反过来说，就是自己修改某条数
据的时候，不允许别人读取该数据，只有等自己的整个事务提交了，才释放自己加上的锁，才允
许其他用户访问那部分数据。
时间戳
时间戳就是在数据库表中单独加一列时间戳，比如“TimeStamp”，每次读出来的时候，把该字
段也读出来，当写回去的时候，把该字段加1，提交之前 ，跟数据库的该字段比较一次，如果比数
据库的值大的话，就允许保存，否则不允许保存，这种处理方法虽然不使用数据库系统提供的锁
机制，但是这种方法可以大大提高数据库处理的并发量，
以上悲观锁所说的加“锁”，其实分为几种锁，分别是：排它锁（写锁）和共享锁（读锁）。
段也读出来，当写回去的时候，把该字段加1，提交之前 ，跟数据库的该字段比较一次，如果比数
据库的值大的话，就允许保存，否则不允许保存，这种处理方法虽然不使用数据库系统提供的锁
机制，但是这种方法可以大大提高数据库处理的并发量，
以上悲观锁所说的加“锁”，其实分为几种锁，分别是：排它锁（写锁）和共享锁（读锁）。
基于 Redis 分布式锁
1. 获取锁的时候，使用 setnx（SETNX key val：当且仅当 key 不存在时，set 一个 key
为 val 的字符串，返回 1；若 key 存在，则什么都不做，返回 0）加锁，锁的 value
值为一个随机生成的 UUID，在释放锁的时候进行判断。并使用 expire 命令为锁添
加一个超时时间，超过该时间则自动释放锁。
2. 获取锁的时候调用 setnx，如果返回 0，则该锁正在被别人使用，返回 1 则成功获取
锁。 还设置一个获取的超时时间，若超过这个时间则放弃获取锁。
3. 释放锁的时候，通过 UUID 判断是不是该锁，若是该锁，则执行 delete 进行锁释放
为 val 的字符串，返回 1；若 key 存在，则什么都不做，返回 0）加锁，锁的 value
值为一个随机生成的 UUID，在释放锁的时候进行判断。并使用 expire 命令为锁添
加一个超时时间，超过该时间则自动释放锁。
2. 获取锁的时候调用 setnx，如果返回 0，则该锁正在被别人使用，返回 1 则成功获取
锁。 还设置一个获取的超时时间，若超过这个时间则放弃获取锁。
3. 释放锁的时候，通过 UUID 判断是不是该锁，若是该锁，则执行 delete 进行锁释放
两阶段提交协议
概念
二阶段提交(Two-phaseCommit)是指，在计算机网络以及数据库领域内，为了使基于分布式系统
架构下的所有节点在进行事务提交时保持一致性而设计的一种算法(Algorithm)。通常，二阶段提
交也被称为是一种协议(Protocol))。在分布式系统中，每个节点虽然可以知晓自己的操作时成功
或者失败，却无法知道其他节点的操作的成功或失败。当一个事务跨越多个节点时，为了保持事
务的 ACID 特性，需要引入一个作为协调者的组件来统一掌控所有节点(称作参与者)的操作结果并
最终指示这些节点是否要把操作结果进行真正的提交(比如将更新后的数据写入磁盘等等)。因此，
二阶段提交的算法思路可以概括为：参与者将操作成败通知协调者，再由协调者根据所有参与者
的反馈情报决定各参与者是否要提交操作还是中止操作。
架构下的所有节点在进行事务提交时保持一致性而设计的一种算法(Algorithm)。通常，二阶段提
交也被称为是一种协议(Protocol))。在分布式系统中，每个节点虽然可以知晓自己的操作时成功
或者失败，却无法知道其他节点的操作的成功或失败。当一个事务跨越多个节点时，为了保持事
务的 ACID 特性，需要引入一个作为协调者的组件来统一掌控所有节点(称作参与者)的操作结果并
最终指示这些节点是否要把操作结果进行真正的提交(比如将更新后的数据写入磁盘等等)。因此，
二阶段提交的算法思路可以概括为：参与者将操作成败通知协调者，再由协调者根据所有参与者
的反馈情报决定各参与者是否要提交操作还是中止操作。
准备阶段
事务协调者(事务管理器)给每个参与者(资源管理器)发送 Prepare 消息，每个参与者要么直接返回
失败(如权限验证失败)，要么在本地执行事务，写本地的 redo 和 undo 日志，但不提交，到达一
种“万事俱备，只欠东风”的状态。
失败(如权限验证失败)，要么在本地执行事务，写本地的 redo 和 undo 日志，但不提交，到达一
种“万事俱备，只欠东风”的状态。
提交阶段
如果协调者收到了参与者的失败消息或者超时，直接给每个参与者发送回滚(Rollback)消息；否则，
发送提交(Commit)消息；参与者根据协调者的指令执行提交或者回滚操作，释放所有事务处理过
程中使用的锁资源。(注意:必须在最后阶段释放锁资源)
发送提交(Commit)消息；参与者根据协调者的指令执行提交或者回滚操作，释放所有事务处理过
程中使用的锁资源。(注意:必须在最后阶段释放锁资源)
缺点
同步阻塞问题
1、 执行过程中，所有参与节点都是事务阻塞型的。
单点故障
2、 由于协调者的重要性，一旦协调者发生故障。参与者会一直阻塞下去。
数据不一致 （脑裂问题）
3、 在二阶段提交的阶段二中，当协调者向参与者发送 commit 请求之后，发生了局部网络异
常或者在发送 commit 请求过程中协调者发生了故障，导致只有一部分参与者接受到了
commit 请求。于是整个分布式系统便出现了数据部一致性的现象(脑裂现象)。
二阶段无法解决的问题 （数据状态不确定）
4、 协调者再发出 commit 消息之后宕机，而唯一接收到这条消息的参与者同时也宕机了。那
么即使协调者通过选举协议产生了新的协调者，这条事务的状态也是不确定的，没人知道
事务是否被已经提交。
1、 执行过程中，所有参与节点都是事务阻塞型的。
单点故障
2、 由于协调者的重要性，一旦协调者发生故障。参与者会一直阻塞下去。
数据不一致 （脑裂问题）
3、 在二阶段提交的阶段二中，当协调者向参与者发送 commit 请求之后，发生了局部网络异
常或者在发送 commit 请求过程中协调者发生了故障，导致只有一部分参与者接受到了
commit 请求。于是整个分布式系统便出现了数据部一致性的现象(脑裂现象)。
二阶段无法解决的问题 （数据状态不确定）
4、 协调者再发出 commit 消息之后宕机，而唯一接收到这条消息的参与者同时也宕机了。那
么即使协调者通过选举协议产生了新的协调者，这条事务的状态也是不确定的，没人知道
事务是否被已经提交。
柔性事务
概念
在电商领域等互联网场景下，传统的事务在数据库性能和处理能力上都暴露出了瓶颈。在分布式
领域基于 CAP 理论以及 BASE 理论，有人就提出了 柔性事务 的概念。CAP（一致性、可用性、分
区容忍性）理论大家都理解很多次了，这里不再叙述。说一下 BASE 理论，它是在 CAP 理论的基
础之上的延伸。包括 基本可用（Basically Available）、柔性状态（Soft State）、最终一致性
（Eventual Consistency）。
通常所说的柔性事务分为：两阶段型、补偿型、异步确保型、最大努力通知型几种。
领域基于 CAP 理论以及 BASE 理论，有人就提出了 柔性事务 的概念。CAP（一致性、可用性、分
区容忍性）理论大家都理解很多次了，这里不再叙述。说一下 BASE 理论，它是在 CAP 理论的基
础之上的延伸。包括 基本可用（Basically Available）、柔性状态（Soft State）、最终一致性
（Eventual Consistency）。
通常所说的柔性事务分为：两阶段型、补偿型、异步确保型、最大努力通知型几种。
两阶段型
1、 就是分布式事务两阶段提交，对应技术上的 XA、JTA/JTS。这是分布式环境下事务处理的
典型模式。
典型模式。
补偿型
TCC 型事务（Try/Confirm/Cancel）可以归为补偿型。
WS-BusinessActivity 提供了一种基于补偿的 long-running 的事务处理模型。服务器 A 发起事务，
服务器 B 参与事务，服务器 A 的事务如果执行顺利，那么事务 A 就先行提交，如果事务 B 也执行
顺利，则事务 B 也提交，整个事务就算完成。但是如果事务 B 执行失败，事务 B 本身回滚，这时
事务 A 已经被提交，所以需要执行一个补偿操作，将已经提交的事务 A 执行的操作作反操作，恢
复到未执行前事务 A 的状态。这样的 SAGA 事务模型，是牺牲了一定的隔离性和一致性的，但是
提高了 long-running 事务的可用性。
WS-BusinessActivity 提供了一种基于补偿的 long-running 的事务处理模型。服务器 A 发起事务，
服务器 B 参与事务，服务器 A 的事务如果执行顺利，那么事务 A 就先行提交，如果事务 B 也执行
顺利，则事务 B 也提交，整个事务就算完成。但是如果事务 B 执行失败，事务 B 本身回滚，这时
事务 A 已经被提交，所以需要执行一个补偿操作，将已经提交的事务 A 执行的操作作反操作，恢
复到未执行前事务 A 的状态。这样的 SAGA 事务模型，是牺牲了一定的隔离性和一致性的，但是
提高了 long-running 事务的可用性。
异步确保型
通过将一系列同步的事务操作变为基于消息执行的异步操作, 避免了分布式事务中的同步
阻塞操作的影响。
阻塞操作的影响。
最大努力 通知 型
这是分布式事务中要求最低的一种, 也可以通过消息中间件实现, 与前面异步确保型操作不
同的一点是, 在消息由 MQ Server 投递到消费者之后, 允许在达到最大重试次数之后正常
结束事务。
同的一点是, 在消息由 MQ Server 投递到消费者之后, 允许在达到最大重试次数之后正常
结束事务。
0 条评论
下一页
