PostgreSQL 11数据库管理员指南第五章:数据表及数据

Coding Alan 4周前 (09-25) 226次浏览 0个评论

本书目录请见:PostgreSQL 11数据库管理员指南

本章包含一列有关数据表和使用其中所含数据的小节。很多小节包含通用的建议,但带有具体的PostgreSQL示例。

我遇到的有些系统管理员仅使用数据库服务端的外部功能。而数据库内部的则是由其他人复杂。

善待数据,数据才会善待你。保持数据整洁,你的查询就会运行的更快速并且产生更少的应用错误。还会在业务中获取很多的朋友。在深更半夜被叫起来处理数据问题就一点也不酷了。

本章中,我们将讲解如下小节:

  • 为数据库对象选择好名称
  • 处理带有引号名称的对象
  • 强制对列使用相同名称和定义
  • 识别并删除重复数据
  • 避免重复行
  • 为一组数据查找唯一键
  • 生成测试数据
  • 随机数据取样
  • 从数据表加载数据
  • 从普通文件加载数据
  • 使用服务端事务进程进行🈯️数据修改

为数据库对象选择好名称

帮助其他人理解一个数据库最简单的方式是确保所有对象的名称有意义。

怎样让名称有意义呢?

准备工作

花一点时间来反思你的数据库,以确保对其目标和主要用例有清晰的看法。这是因为本节中的所有项描述某种你需要根据具体状况下的看法来仔细考虑命名选择。

如何实现…

以下是一些在对数据库对象命名时应当考虑的点:

  • 命名遵循已有标准和实践。发明新标准并没有什么帮助,强化现有标准则会有帮助。
  • 命名清晰地描述角色或数据表内容。
  • 对于主要数据表,使用简短有力的名称。
  • 按照所关联的数据表对查询数据表命名,如account_status。
  • 对于连接或关联数据表,使用所关联所有主表的名称,如customer_account。
  • 确保命名对其它相似名称可进行明确的区分。
  • 使用一致的简写。
  • 使用下划线。默认不保留大写,因此像Java中那样使用驼峰命名,如customerAccount会让它们无法阅读。参见处理带有引号名称的对象一节。
  • 一致使用复数,或者一点都不使用。
  • 使用后缀来标识内容类型或对象的域。PostgreSQL已对自动生成的对象使用后缀。
  • 提前考虑。不要选择引用对象当前角色或地点的名称。因此不要因服务器在伦敦而使用名为London的数据表。这个服务器很有可能会迁移到洛杉矶。
  • 提前考虑。不要选择暗示仅表示某一类实例的名称,如名为TEST或BACKUP_DATA的数据表。另一方面,这种信息可以放到数据库名里,通常在数据库中不常使用。
  • 在出现长表名时避免使用缩略词。例如money_allocation_decision比MAD就好得多。在PostgreSQL将名称转换为小写时这尤其重要,这样缩略词会很不明确。
  • 表名常用作创建其它对象的根,因不要添加表格后缀或相似的内容。

扩展知识…

PostgreSQL中索引的标准名称如下:

这里的suffix是以下的一种:

  • pkey: 用于主键约束
  • key: 用于唯一约束
  • excl: 用于排它约束
  • idx: 用于任意其它类型的索引

所有序列的标准后缀是seq。

数据表可以有在每个事件启动的多个触发器。触发器按字母顺序执行,因此触发器名称应该有某种动作名来进行区别并用于指定顺序。在触发器名称前添加INSERT、UPDATE或DELETE看起来是不错的想法,但在触发器兼有UPDATE和DELETE作用时就会产生混淆,所有这些可能会导致混乱。

ℹ️触发器名称的字母排序总是遵循C语言的 locale 库,而不论实际的locale设置是什么。如果你的触发器名称使用非ASCII字符,那么实际排序可能会和所预期的不同。

以下示例显示在C的 locale 库中如何对字符è和é进行排序。可以修改locale及字符串列表来研究locale如何影响排序。

对于触发器一个有用的命令惯例如下:

如果你发现自己有奇怪或不规则的对象名称,使用RENAME子命令来保持名称整齐是一个不错的方法。示例如下:

处理带有引号名称的对象

如果我们将数据表名包裹在双引号中的话PostgreSQL对象名称可以包含空格和大小写混合的字符。这会带来一些困难,因此本节旨在当你遇到某些问题时帮助到你。

大小写敏感的问题通常对于习惯于其它数据库系统的人会是一个问题,如MySQL,或者对于那些面临想要从MySQL迁移出去的挑战的人。

准备工作

首先,我们来创建一个使用引号和混合大小写名称的数据表,如下:

如何实现…

如果我们尝试不使用正确的大小写访问这些表,会得到如下错误:

所以我们以正确的大小写进行书写:

但依然失败,事实报错相同。

如果想要访问通过引号名称创建的数据表,那么也必须使用引号名称来访问,如下:

输出如下:

使用规则是,如果使用引号名称创建数据表,那么需要使用引号名称来书写SQL语句。相应地,如果使用引号名称来编写SQL,那么也应当使用引号名称来创建数据表。

运行原理…

PostgreSQL对SQL中使用的所有名称转为小写。思考如下命令:

它与下面的命令完全相同:

也与下面的命令完全一致:

但是,与下面的命令则不是同一回事:

扩展知识…

如果通过用于创建对象名的数据表提取值,那么可能需要使用一个名为quote_ident()的便捷函数。该函数在需要用到对象名时把双引号放在值的两边,如下所示:

quote_ident()函数在通过基于PL/pgSQL函数中的变量名创建数据表时尤其有用,如下:

强制对列使用相同名称和定义

良好设计的数据库有平滑、易于理解的定义。这允许所有用户理解每张表中数据的含义。这也是解决数据质量问题的重要方法。

准备工作

如果你想要在本节中运行查询作为测试,那么使用下面的示例。或者可以直接在你自己的数据库中查看问题:

如何实现…

首先,我们将展示如何对目录执行查询来识别在不同表中以不同方法定义的列。我们使用如下的information_schema查询:

查询给出如下的输出:

对比两个给定的表更为复杂,因为两张表可能会很多相似的地方而仅有少量的不同。以下查询查找所有具有不同定义的同名数据表(因此使用不同的模式):

输出如下:

运行原理…

数据表的定义位于PostgreSQL中,并使用 Information Schema目录视图来进行访问。

定义不同可能有具体的原因。我们排除了PostgreSQL自己的内部表,因为在两个目录中有很类似的名称:PostgreSQL对SQL标准信息模式的实现以及PostgreSQL自己内部的pg_catalog模式。

这些查询非常的复杂。事实上,我们还可以对这些查询添加更多的复杂度来对比默认值或约束等内容。这一基本想法可朝着不同方向进行扩展。

扩展知识…

我们可以使用如下函数对比任意两张表的定义:

以下是对其使用的输出:

识别并删除重复数据

关系型数据库采用的想法是数据项可以独立标识。不管我们有多努力,总会在某处存在不良数据。本节展示如何进行诊断以及清理这一问题。

准备工作

我们先通过查看示例数据表cust。它在customerid中存在重复值:

在删除重复数据之前,记住有时不是数据错了,而是你对它的理解错了。在这种情况下,可能是你还没有恰当地规范数据库模型,需要包含其它表来处理数据的形式。可能还会发现重复行由之前你在数据加载过程的某处决定排除某列而造成的。反复查看,一次必删除。

如何实现…

首先,使用如下查询定位到重复内容:

我们在单独数据表中保存重复列表,因为在数据表很大的时候查询会很慢,因此不希望运行多次。

ℹ️一个UNLOGGED表可由更少的I/O创建,因为它不写入WAL(预写日志系统)。它比临时表要好,因为它不会在断开重连时消失。硬币的另一面是在崩溃时会丢失所有内容,但并不是很糟糕,因为你在使用一个无日志表,然后告诉PostgreSQL你能够在出现崩溃事件(机率不大)时重建数据表中的所有内容。

结果可用于手动识别不良数据,并且可通过执行如下步骤来解决问题:

  1. 在需要时合并这两行来提供数据的更好展现。可能会使用一行中的值来更新你决定保留的那一行的值,如下所示:
  2. 删除剩下不需要的行:

在某些情况下,数据行可能完全相同,比如在new_cust数据表中,像下面这样:

不同于前面示例,完全无法分离数据,因此我们不在没有任何手动操作的情况下删除行。SQL是基于数据集的语言,因此从数据集中仅挑出一行比大部人所希望的要困难得一些。

在这类情况下,我们应使用稍有不同的流程来监测重复内容。我们将使用一个名为ctid的隐藏列。它表示你所观察的行的物理位置,例如,重复行会有不同的ctid值。步骤如下:

  1. 首先开启一个事务:
  2. 然后我们锁定数据表来防止任何INSERT, UPDATE或DELETE操作,它们会改变重复列表以及/或改变它们的ctid值:
  3. 现在我们定位到所有的重复内容,记录最小ctid值,这样我们不会删除该值:
  4. 然后我们可以删除每个重复内容,而保留重复值中最小ctid值的那条内容:
  5. 我们执行事务,也会释放掉我们此前所添加的锁:
  6. 最后在进行所有删除后清理数据表:

运行原理…

第一条查询通过对唯一字段进行分组并对行计数来实现。大于一行的都是由重复值所产生。如果在查找一个字段以上(甚至是所有字段)的重复值,那么我们应使用以下形式的SQL:

这里,col1、col2等直到colN都是键的字段:

注意这种类型的查询可能需要对所有键字段进行完整表格排序。这要求排序空间等于表格的大小,因此在对超大表进行SQL运行时最好先进行考量。对于这各路查询需要有一个比较大的work_mem配置,可能要128 MB或更多。

我们所展示的DELETE FROM … USING查询,仅在PostgreSQL中起作用,因为它使用了ctid值,这是数据表中每行的标识符。如果想要本章前面那样对多个字段运行该查询,需要将第3步中的查询扩展如下:

然后,扩展第4步中的查询如下:

前面的查询通过对相似值的所有行进行分组,然后查找ctid值最低的行。最低值与更接近于表的起始处,因此会从数据表的后部删除重复项。在运行VACUUM时,可能会发现数据表变小,因为我们从最远端删除了行。

BEGIN和COMMIT命令将LOCK和DELETE命令封装为单个事务,这是所要求的。否则会在获取到锁后会立即释放。

使用单事务的另一个原因是在出错时我们总是要进行回滚,在从线上表中删除数据时这总是有益的。

扩展知识…

在删除重复行时长期进行修改的锁表不大可能。这会给大表带有很大的问题。此时,我们需要稍作调整:

  1. 标识出要删除的行,将它们保存到一个副表中。
  2. 对请表构建索引在加速对行的访问(可以使用CONCURRENTLY关键字,在第九章:常规维护中的维护索引一节会进行讲解)。
  3. 编写循环程序从副表中读取各行,执行一系列更小的事务。
  4. 开启新的事务。
  5. 从副表中读取一组匹配的行。
  6. 从主表中选取这些行来进行更新,依赖于索引来让这些访问快速发生。
  7. 删除相应的行。
  8. 提交执行然后再次循环。

前述的程序不能写成一个数据库函数,因为在同一个函数中不能有多个事务。我们需要有多个事务来确保对每行的锁仅保持一个非常短的时间。

避免重复行

避免重复行是保证任意数据库质量最重要的一个方面。PostgreSQL在这个领域比大多数关系型数据所多进行了扩展来提供一些有用的功能。

准备工作

选取一组你希望变成唯的字段。这是适用于所有行还是其中的一段?

我们先使用示例表:

如何实现…

要避免重复行,我们需要创建一个唯一索引让数据库服务器用于强制具体字段集合的唯一性。可以对基本数据类型采取如下3种相似的方式:

  1. 对一组字段创建主键约束。每张表只允许有一个主键。数据行的值不得为NULL,因为我们要对该字段作NOT NULL的限制。
  2. 这会新建一个名为new_cust_pkey的索引。
  3. 对一组字段创建一个唯一约束。我们可以使用它们代替(或直接使用)主键。每张表中对此的数量没有限制。在这些字段中允许有NULL值:
  4. 这会新建一个名为new_cust_customerid_key的索引。
  5. 对这组字段创建一个唯一索引:
  6. 这会新建一个名为new_cust_customerid_idx的索引。

所有这些技巧通过一些稍有不同的语法排除了重复值。它们共同创建一个索引,但只有前两者会创建正式的约束。每种技巧在我们有主键或使用多字段的唯一约束时可以进行使用。

最后一个方法很重要,因为它允许对索引指定一个WHERE语句。这在某种情况下知道字段值是唯一会有益。所产生的索引称为部分索引。

假设我们的数据像这样:

它会产生如下的输出:

然后我们可以对数据表添加部分索引来仅对status = ‘OPEN’强制customerid的唯一性,如下:

如果你的唯一性约束需要对更多复杂的数据类型来进行施加,那么可能需要使用更为高级的语法。这里有一些有助的示例。

我们先使用一个最简单的示例:创建一个盒子的表格并在其中放置示例数据。它可能是你第一次看到PostgreSQL的数据类型语法:

我们可以看到根据它们的 x 和 y 坐标既不接触也不重叠的两个盒子。

要在这里强制唯一性,我们希望创建一个对添加与已有盒子位置重叠的约束。对 box 数据类型重叠运算符定义为&&,,因此我们可以使用下面的语法来添加约束:

这新建一个名为boxes_position_excl的索引:

我们甚至可以对基本数据类型使用相同的语法。因此第一个示例的每4种执行方式如下:

这会新建一个名为new_cust_customerid_excl的索引并且会排除掉重复项:

运行原理…

 

扩展知识…

重复索引

无索引唯一性

真实案例 – IP 地址段分配

真实案例 – 时间段

真实案例 – 前缀范围

为一组数据查找唯一键

准备工作

如何实现…

运行原理…

生成测试数据

如何实现…

运行原理…

扩展知识…

其它内容

随机数据取样

如何实现…

运行原理…

从数据表加载数据

准备工作

如何实现…

运行原理…

扩展知识…

从普通文件加载数据

准备工作

如何实现…

运行原理…

扩展知识…

使用服务端事务进程进行🈯️数据修改

如何实现…

扩展知识…

更新中…

喜欢 (0)
[]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址