Greenplum 数据管理

2019独角兽企业重金招聘Python工程师标准>>>

5. 数据管理

本章讲述关于数据库数据的管理，涉及常规数据库中的增删改查等内容，还涉及 GPDB 中的并发控制，事务与空间的回收。

5.1 GPDB 的并发控制

与事务型数据库系统通过锁机制来控制并发访问的机制不同， GPDB(与PostgreSQL一样)使用多版本控制(Multiversion Concurrency Control/MVCC)保证数据一致性。 这意味着在查询数据库时，每个事务看到的只是数据的快照，其确保当前的事务不会看到其他事务在相同记录上的修改。据此为数据库的每个事务提供事务隔离。

MVCC以避免给数据库事务显式锁定的方式，最大化减少锁争用以确保多用户环境下的性能。在并发控制方面，使用MVCC而不是使用锁机制的最大优势是， MVCC对查询(读)的锁与写的锁不存在冲突，并且读与写之间从不互相阻塞。

GPDB提供了各种锁机制来控制对表数据的并发访问。 大多数GPDB的SQL命令可以自动获取适当模式的锁以确保在命令执行时相关的表不会被删除或者修改。 对于不能适应MVCC锁的应用来说，可以使用LOCK命令来获得适当的锁。然而，恰当的使用MVCC比使用LOCK有更好的性能表现。

5.2 插入记录（insert）

常用的插入案例：

-- 全记录插入
insert into products values(1, 'cheese', 9.99);

-- 指定顺序插入
insert into products(name, price, product_no) values('cheese', 9.99, 1);

-- 使用一个命令插入多条记录
insert into products(product_no, name, price) values
(1, 'cheese', 9.99),
(2, 'bread', 1.99);

在同时插入大量数据时，应该考虑使用外部表(CREATE EXTERNAL TABLE)或者COPY命令。在装载大量记录时，这些装载机制比使用INSERT更高效。

AO表为批量装载做了优化。不建议在AO表上使用单条的INSERT语句。

5.3 更新记录（update）

UPDATE意味着对数据库中现有的数据进行修改。可以修改表中单独的记录、全部的记录、全部记录的一部分。每个列都可以被单独的更新，但不会影响其他列。

要执行更新，需要如下3方面的信息：

1. 要被更新的表和列
2. 列的新值
3. 需要被更新的列必须匹配的条件

例子，更新 product_no 为 2 的商品的价格为 6.11：

update products set price = 6.11 where product_no = 2;

在 GPDB 中的 UPDATE 的限制：

• GPDB 中的 DK(分布键) 不可以被 UPDATE。
• GPDB 中不支持 RETURNING 子句。

5.4 删除记录（delete, truncate）

使用DELETE命令从指定的表中删除符合WHERE条件的记录。如果没有使用WHERE子句，将会删除该表的所有记录。在 GPDB 中不支持 RETURNING 子句。

例子，删除 products 中价格为 6.11 的记录：

delete from products where price = 10;

例子，删除表中所有记录：

delete from products;

希望快速删除所有记录，应该使用 TRUNCATE 命令。例如：

truncate products;

该命令一次清空表中的全部记录。值得注意的是， TRUNCATE不扫描表， 其继承者表不会被执行该操作，只是被TRUNCATE的表受到影响。分区表视作一个整体，在父级表上执行TRUNCATE操作会清空所有相关子表的数据。

5.5 使用事务

事务允许将多个SQL语句放在一起当作一个整体操作，所有SQL一起成功或失败。

• 在GPDB中用以执行事务的SQL命令为：

• 使用BEGIN或START TRANSACTION开始一个事务块

• 使用END或COMMIT提交事务块

• 使用ROLLBACK回滚事务而不提交任何修改

• 使用SAVEPOINT选择性的保存事务点，之后可以使用ROLLBACK TO SAVEPOINT回滚到之前保存的事务点，还可以使用RELEASE SAVEPOINT来释放之前保存的事务点。

• 事务隔离级别

SQL标准定义了4个事务隔离级别。在GPDB中可以使用4个事务隔离级别的任何一个。但在内部，实际上只存在两个不同的隔离级别 – 已提交读(readcommited)和序列化(serializable)：

• 已提交读(readcommited)

当事务使用该隔离级别， SELECT查询只能看到查询开始前的数据，其永远读不到SELECT查询期间其他并发事务未提交或已提交的修改。不过， SELECT语句可以看到当前事务之前所做的改变，虽然这些改变尚未提交。实际上SELECT语句看到的是该查询开始时数据库的一个镜像。 需要注意的是，如果在一个事务中有两个SELECT语句，且在第一个SELECT语句期间有其他的事务提交了对数据的改变，两个SELECT语句可以看到不同的数据。

UPDATE和DELETE命令与SELECT在数据目标上有同样的行为，他们也只会找到命令开始前已经提交的数据。不过，在获取这些目标数据之前其可能已经被其他的事务更新(或者删除或者锁定)。 已提交读事务隔离级别对于多数的应用已经足够，且该级别高效而易用。 然而对于复杂的查询更新应用来说，获取比已提交读隔离级别更高的数据一致性保护还是很有必要的。

• 序列化(serializable)，串行化

这是最严格的事务隔离级别。该级别要求事务被串行执行，就是说事务必须一个接着一个的执行而不能并发执行。

应用程序在使用该级别时需要做好由于序列化失败而需要的重试操作。当一个序列化事务在执行时， SELECT查询只能看到该事务开始前提交的数据，其永远读不到该事务期间其他并发事务未提交或已提交的修改。

不过， SELECT语句可以看到当前事务之前提交的改变，虽然这些改变尚未提交。同一事务中连续的SELECT命令始终看到相同的数据。

UPDATE和DELETE命令与SELECT在数据目标上有同样的行为，他们也只会找到事务开始前已经提交的数据。不过，在获取这些目标数据之前其可能已经被其他的事务更新(或者删除或者锁定)。在这种情况下， 系列化事务将会等待前面更新事务的提交或者回滚(若该事务仍在执行)。

如果前面的更新事务回滚了， 其影响被丢弃，序列化事务将处理更新之前的数据。而如果之前的更新事务提交了(发生了真实的数据更新删除而非仅仅的获取锁)，序列化事务将会被回滚。

• 未提交读

在 GPDB 中与已提交读等同。

• 可重复读

在 GPDB中与序列化等同。

• GPDB 缺省事务隔离级别

在GPDB中缺省的事务隔离级别是已提交读。要使用不同的事务隔离级别，可以在BEGIN事务时声明隔离界别，或者在事务开始之后使用SET TRANSACTION命令设置隔离级别。

5.6 回收空间

由于MVCC事务并发模型的原因，已经删除或者更新的记录仍然占据着磁盘空间，虽然其对于新的事务来说已经不可见。如果数据库有大量的更新和删除操作，其将会产生大量的过期记录。

需要定期运行 VACUUM 命令删除过期记录，例如：

vacuum products;

VACUUM命令还会收集表级别的统计信息，如记录数、占用磁盘页面数，所以在装载数据之后对全表执行VACUUM是有必要的，这同样适用AO表。

• 配置子空间映射

过期的记录会被存在叫做自由空间映射的地方。自由空间映射的大小必须足够容纳数据库中的所有过期记录。如果尺寸不够大，超出自由映射空间的过期记录占用的空间将无法被VACUUM命令回收。

VACUUM FULL命令将回收所有过期记录，但这是一个很昂贵的操作并且其可能会花费无法接受的时间长度在一张大表上来完成操作。 如果自由映射空间已经溢出，最好的做法是及时的使用CREATE TABLE AS命令来重建数据表并删除旧的表。在GPDB中不建议使用VACUUM FULL命令。

• 只有映射空间的设置

修改的配置文件为 postgresql.conf，由于此配置文件在各个 segment 节点上都存在，可能需要 完全停止整个 GPDB，然后对每个 segment 实例进行设置后再启动 GPDB。

修改的 参数名 为 max_fsm_pages 和 max_fsm_relations。

转载于:https://my.oschina.net/crooner/blog/917581