一幅长文细学GaussDB（二）——数据库基础知识

2 数据库基础知识

2.1 数据库管理简介

数据库管理

数据库管理（Database Admin）：对数据库管理系统进行管理和维护的工作

数据库管理员（Database Administrator,DBA）：从事管理和维护数据库管理系统的相关人员的统称

核心目标：保证数据库管理系统的安全性、稳定性、数据一致性、系统的高性能

数据库管理工作范围

数据库对象管理：

• 物理设计工作
• 物理实现工作

数据库安全管理：

• 防止未授权访问，避免受保护的信息泄露
• 防止安全漏洞和不当的数据修改
• 确保数据只提供给授权用户使用

备份恢复管理：

• 指定合理的备份策略，实现数据定期备份功能
• 保证灾难发生时数据库系统能够做到最快恢复和最小损失

数据库性能管理：

• 对影响数据库性能的因素进行监控和优化
• 对数据库能使用的资源进行优化，从而增加系统吞吐量，并减少竞争，最大可能地处理工作负载。

数据库环境管理：

• 数据库的运行和维护管理，包括安装，配置，升级，迁移等；
• 确保数据库系统在内的IT基础设施的正常运作

对象管理

数据库对象：数据库里用来存储和指向数据的各种概念和结构的总称。

对象管理：使用对象定义语言或者工具创建、修改和删除各种数据库对象的管理过程。

常见的数据库对象：

制定数据库对象命名规范

• 统一名称大小写
• 书写格式为数据对象_数据对象名称，如student表我们可以写为t_student，也可以适当使用缩略词，如t_stu

备份和恢复

数据库备份：将数据库中的数据以及保证数据库系统正常运行的相关信息保存起来，以备系统出现故障后恢复数据库时使用。

备份对象：数据本身、和数据有关的数据库对象、用户及权限、数据库环境

数据库恢复：将数据库系统从故障或者瘫痪状态恢复到可正常运行，并能够将数据恢复到可接受状态的活动

灾难恢复

企业级容灾：对于企业和单位来说，数据库系统和其他应用系统构成更大的信息系统平台，所以数据库备份恢复并不是孤立的功能点，要和其他应用系统一并考虑，即考虑整个信息系统平台的容灾性能。

灾难备份：为了灾难备份而对数据、数据处理系统、网络系统、基础设施、专业技术能力和运行管理能力进行备份的过程。

恢复时间目标（RTO）：灾难发生后，信息系统或业务功能从停顿到必须恢复的时间要求。

恢复点目标（RPO）：灾难发生后，系统和数据必须恢复到的时间点要求

备份方式

根据备份的数据集合范围分类：全量备份、差异备份、增量备份。

• 全量备份：也叫完全备份；主要是对某个时间点的数据库里所有数据和对应的结构做一个完全备份。全量备份是差异备份和增量备份的基础，且备份期间会对系统性能产生一定影响，但是安全性和数据完备性最高。
• 差异备份：指上一次全量备份后，对发生变化的数据部分进行备份。
• 增量备份：指上一次备份后，对发生变化的数据部分进行备份。

根据是否停用数据库分类：热备、冷备、温备

• 热备：备份期间数据可读可写，数据库正常运行
• 温备：备份期间可读不可写，可用性弱于热备
• 冷备：不可读写，可理解为停止数据库，但备份出的数据可靠性最高

根据备份内容分类：物理备份、逻辑备份

• 物理备份：直接备份数据库所对应的数据文件甚至是整个磁盘
• 逻辑备份：把数据从数据库导出，并将导出的数据进行存档备份

数据库安装

数据库卸载

传统数据库：先对数据库进行一次全备，然后停止数据库服务，最后卸载数据库。

云数据库：先对数据库进行一次全备，然后从云平台删除数据库实例即可。

不同架构场景下：单机的卸载方式都是类似，需要在每个节点上执行相同的卸载操作；而分布式集群一般使用专有的卸载工具。

安全问题：对于一些客户，在数据库卸载时需要对存储介质上的数据再做销毁处理，保证数据不外泄。

数据库迁移

说明：需要依据不同的迁移场景需求设计迁移方案。

考虑的要素：

• 迁移可用的时间窗口
• 迁移可以使用的工具
• 迁移过程中数据源系统是否停止写入操作
• 迁移过程的数据源系统和目标系统之间的网络情况
• 根据迁移的数据量估算备份/恢复事件
• 迁移后，源和目标数据库系统之间的数据一致性稽核

注：时间窗口是股票的术语，指的是当时间段运行到了一定的阶段时,会出现转折点。

数据库扩容

任何一个数据库系统的容量都是在某个时间点的基础上对未来一段时间内的数据量进行估算后确定的，容量不仅仅是数据存储量，还需要考虑以下几个方面：

• 计算能力不足（整个系统CPU日均繁忙程度>90%）
• 响应/并发能力不足（QPS，TPS显著下降，无法满足SLA）
• 数据容量不足（可用的数据空间低于15%）

扩容方案的选择：

• 垂直扩容：垂直扩容时增加数据库服务器硬件。如增加内存，增大存储，提升网络带宽等。这种方式相对简单，但是会遭遇单机硬件性能的瓶颈
• 水平扩容：横向增加服务器数量，通常指的是并行计算，利用集群中服务器数量的优势来增加整体系统的性能
• 停机扩容：操作简单，即停止服务器工作，任何增加服务器数量。这样走时间窗口有限，出现问题会导致扩容失败。如果时间过长，不容易被客户接受
• 平滑扩容：对数据库服务无影响，是一种在线水平扩容方式。但技术方案相对复杂。随着数据库服务器数量增多，扩容复杂程度就急剧上升。

例行维护工作

数据库故障处理：

• 配置数据库监控指标和告警阈值
• 针对故障事件的等级设置告警通知流程
• 接受告警信息后，根据日志进行故障定位
• 对于遇到的问题，应详细记录原始信息
• 严格遵守操作规程和行业安全规程
• 对于重大操作，在操作前要确认操作的可行性，做好相应的备份、应急和安全措施后，由有权限的操作人员执行。

2.2 数据库重要概念

数据库和数据库实例

数据库：物理操作系统文件或磁盘数据块的集合

数据库实例：指的是操作系统中一系列进程以及为这些进程所分配的内存块，如JDBC中连接数据库返回的对象

单实例：数据库实例是访问数据库的通道，通常一个数据库实例对应一个数据库

多实例：利用多实例操作，可以更充分的利用硬件资源，让服务器性能最大化

分布式集群：集群就是一组相互独立的服务器，通过高速网络组成一个计算机系统。在分布式集群中，每个服务器都可能有数据库的一份完整副本或者部分副本，所有服务器通过网络互相连接，共同组成一个完整的、全局的，逻辑上集中、物理上分布的大型数据库

数据库连接和会话

数据库连接（Connection）：指的是通过网络建立的客户端和专有服务器或调度器的一个网络连接，建立连接时需要指定参数，如服务器主机名或ip，端口号，连接用户名和口令等。

数据库会话（Session）：客户端和数据库之间通信的逻辑概念。指的是通信双方从开始通信到通信结束期间的一个上下文（Context）。这个上下文是一段位于服务器端的内存，记录了本次连接的客户端机器、对应的应用程序进程号、对应的用户登录等信息。

数据库连接池

建立数据库连接时有代价的，如果我们频繁的建立和关闭数据库连接，则此过程也会成为限制数据库性能的指标，从而降低数据库系统的性能。比较好的解决方式是采用数据库连接池。即一个用户用完的连接暂且不要马上断开，而是存于缓存中，等待一段时间看有无其他用户需要他。数据库连接池是数据库连接的复用，其负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个。

模式

在数据库中存在模式Schema，它是数据库对象的集合，其作用如下：

• 允许多个用户使用一个数据库而不干扰其他用户
• 把数据库对象组织成逻辑组，让他们更便于管理
• 形成命名空间，避免对象的名字冲突
• schema包含表及其他数据库对象，数据类型、函数、操作符等。

表空间

表空间概念：

• 表空间是由一个或者多个数据文件组成的
• 通过表空间定义数据库对象文件的存放位置
• 数据库中所有对象在逻辑上都存放在表空间中
• 在物理上储存在表空间所属的数据文件中

表空间作用：

• 根据数据库对象使用模式安排数据物理存放位置，提高性能。可以将频繁使用的索引放置在性能稳定且运算速度快的磁盘上。而对于归档好的这类使用频率低的数据，我们则放于速度慢的磁盘上
• 通过表空间指定数据占用的物理磁盘空间，限制其物理空间使用上限，避免磁盘空间被耗尽

表

表：在关系数据库中，数据库表就是加了第一范式的二维表

记录：表中的每一行成为一个记录，也可以叫元组，每个记录由若干个字段组成，字段也可以叫做属性。

字段：也称为属性，字段的取值我们称为域，定义字段时都需指明其两个要素：字段名和数据类型。

CREATE TABLE t_author(
	author_id integer,
	author_name char(60),
	author_age integer,
	author_address varchar(255)
);
1
2
3
4
5
6

数据类型

说明：数据库表中可定义的数据类型如下

字段设计建议

• 尽量使用短字段数据类型，使用一致的数据类型，使用执行效率比较高的数据类型
• 多个表存在逻辑关系时，表示同一含义的字段应该使用相同的数据类型
• 对于字符串数据，建议使用变长字符串数据类型，并指定最大长度

临时表

• GaussDB(for MySQL)支持创建临时表。临时表用于保存一个会话中需要的数据。当会话退出时，临时表的数据自动清空。
• 临时表无法通过show tables命令来显示

CREATE TEMPORARY TABLE staff_history_session(
	startdate DATE,
	enddate DATE
)
1
2
3
4

表的存储方式

表存分类：表可以分为行存表和列存表。

行存表：关系数据库中的二维表，每条元组是一行一行的。

列存表：HBase数据库中的表，每条元组是一列一列的。

列存适用场景：

• 统计分析类查询
• 适合OLAP，数据挖掘等大量查询的应用查询

行存适合场景：

• 点查询（返回记录少，基于索引的简单查询）
• 适合OLTP这种轻量级事务，大量写操作，数据增删改比较多的场景

分区

分区：将大表的数据范围许多小的数据子集，这些子集称为分区。分区的类别：根据不同的依据可以分为不同的分区

• 范围分区表
• 列表分区表
• 哈希分区表
• 间隔分区表

分区表的收益：改善查询性能、增强可用性、方便维护、均衡I/O

分区剪枝：对于分区对象的查询可以仅搜索自己关心的分区，提高检索效率

数据分布

数据分布：GuassDB(DWS)分布式数据库的数据表是分散在所有数据节点(DataNode,DN)上的，所以创建表的时候需要指定分布列。

分布方式：

数据策略选择

复制：适合记录集较小的表

Hash/List/Range:适合数据量较大的表

分布列选择原则

• 尽量选择离散值比较多的列，保证数据均匀分布。分布均匀是为了避免木桶效应，各个DN对等执行。
• 在满足第一条原则的情况下，不要选择存在常量过滤的列。
• 满足前两条原则的情况下，尽量选择关联字段或聚合字段作为分布列，这种方式是为了避免数据节点之间数据重发布，降低IO的开销从而提升关联操作和聚合操作的性能。

视图

视图本质：通过逻辑映射将经常使用的数据映射为一张临时表。视图和基表不同，不是物理上实际存在的，是一个虚表。

视图作用：

• 简化操作
• 提高安全性，对于不同的用户来说，规定的用户只能看到自己需要的数据部分
• 逻辑独立性

视图的限制性：

• 性能问题：查询可能很简单，但是封装的视图语句很复杂
• 修改限制：对于复杂视图，用户不能通过视图修改基表数据

CREATE VIEW stu_class(id,name,class) AS select student.s_id,student.name,stu_info.class from student,stu_info where student.s_id = stu_info.s_id;
1

索引

说明：索引提供指向存储在表的指定列中的数据值的指针，如同图书的目录，能够加快表的查询速度，但同时也增加了插入、更新和删除操作的处理时间。

创建索引的建议：

• 在经常需要搜索查询的列上创建索引，可以加快搜索的速度。
• 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序加快排序查询事件
• 在经常使用WHERE子句的列上创建索引，加快条件的判断速度
• 为经常出现在关键字ORDER BY、GROUP BY、DISTINCT后面的字段建立索引

有效索引：创建后的索引并非一定会被使用。如果系统判定使用索引的时间比顺序扫描还久，就不会使用索引。故我们要定期删除无用索引。我们还可以通过explain语句查看执行计划来判断是否使用索引。

索引的类别

约束

完整性约束：数据的完整性是指数据的正确性和一致性，在定义表时我们可以定义完整性约束。

• 完整性约束是一种规则，本身不占用数据库空间
• 完整性约束和表结构定义一起保存在数据字典中

常见的约束类型：

• 唯一性和主键约束UNIQUE/PRIMARY KEY
• 外键约束FOREIGN KEY
• 检查约束CHECK
• 非空约束NOT NULL
• 默认约束DEFAULT

约束的设计

• 如果能够从业务层面补全字段值，就不建议使用DEFAULT约束，避免数据加载时产生不符合预期的结果
• 给明确不存在NULL值得字段加上NOT NULL约束，优化器能够对表结构进行优化
• 给可以显式命名的约束显式命名。除了NOT NULL和DEFAULT约束外，其他约束都能显式命名

数据库对象间的关系

事务

事务概念：事务（Transaction）是用户定义的一系列操作，这些操作作为一个工作单元来执行，要么一个操作都不做，要么把操作全做完。

事务的四大特性：

• Atomicity原子性：事务是数据库的逻辑工作单位，事务中的一系列操作要么都做，要么都不做
• Consistency一致性：事务的执行结果是使数据库从一个一致性状态转到另一个一致性昨天
• Isolation隔离性：数据库中一个事务的执行不能被其他事务干扰。即一个事务的内部操作及使用的数据对其他事务是隔离的，并发执行的各个事务不能相互干扰
• Durability持久性：事务一旦提交，对数据库中数据的改变是永久的。提交后的操作或者故障不会对事务的操作结果产生任何影响

事务结束的标记：

• COMMIT提交事务后事务正常结束
• 事务异常，通过RollBack回滚事务

事务处理模型：

• 隐式事务：每一条以分号结束的操作语句都是一个事务，GaussDB(for MySQL)默认是隐式提交。
• 显式事务：事务有显式的开始和结束标记，用START TRANSACTION开启事务，以COMMIT或者ROLLBACK结束事务

START TRANSACTION
DELETE FROM dbbank.orders;
SELECT COUNT(*) FROM dbbank.orders;
ROLLBACK;
1
2
3
4

数据不一致情况

脏读：一个事务读取了其他事务中还没有提交的数据

不可重复读：一个事务获得的数据可以被其他事务修改。在处理过程中多次读取同一个数据，这个数据可能发生变化

幻(影)读：是不可重复读的一种特殊情况。当重复读后，一个事务发现莫名其妙多了一些数据或者少了一些数据，这些数据就被成为幻影数据。

事务隔离级别

ANSI SQL标准定义了四种事务隔离级别来避免三种数据不一致问题。

• 序列化：系统的所有事务串行执行，能够避免所有数据不一致情况。但是开启该级别会导致系统的并发度大幅下降。
• 可重复读：一个事务一旦开始，事务过程中读取的所有数据不允许被其他事务修改，但这样依然会出现幻读情况。
• 已提交读：一个事务能够读取到提交过的数据。但是这么做会出现不可重复读。
• 未提交读：一个事务可以读取被其他事务修改过，但还没有提交的数据。但是这么做会出现脏读。