Lance's questions

我是 SQL 的新手，并且大部分时间都在使用 ORM（好吧，SQL 构建器，而不是原始 SQL）。我在Vercel Postgres版本 15中有一个简单的字典应用程序，具有这种模式：

CREATE TABLE words (
  id serial PRIMARY KEY,
  transcription_count INTEGER NOT NULL,
  pronunciation_count INTEGER NOT NULL,
  verified BOOLEAN NOT NULL
);

CREATE TABLE transcriptions (
  id serial PRIMARY KEY,
  word_id INTEGER REFERENCES words,
  text TEXT NOT NULL,
  system INTEGER NOT NULL,
  length INTEGER NOT NULL
);

CREATE TABLE pronunciations (
  id serial PRIMARY KEY,
  word_id INTEGER REFERENCES words,
  text TEXT NOT NULL,
  syllable_count INTEGER NOT NULL
);

例如，我想找到转录文本长度 >= 5 的所有单词，并对文本进行排序，但返回单词。每个转录都属于一个系统，每个系统可以有 1 个或多个转录（例如，给定“拉丁”系统，单词“color/colour”有 2 个拼写，作为一个粗略的例子）。此外，每个单词可以有多个系统（例如，中文有拼音和汉字作为 2 个可能的系统，在中文系统中有繁体和简体作为系统内的 2 个转录）。

这就是我的想法。

SELECT id from words
INNER JOIN transcriptions t ON t.word_id = words.id
WHERE t.length >= 5
AND t.system = 1
ORDER BY t.text ASC

这是正确的查询类型吗？这会返回重复的单词吗，因为我正在按连接表排序，并且存在 1<>many 关系？我不清楚这将如何工作/会返回什么样的结果。另外，作为第二个查询，我怎么能另外指定“只为每个单词选择匹配请求的第一个转录”，所以它只按每个单词的 1 个转录排序？

我用一个非常有用的答案问了这个问题：
如何对跨多个表的结构进行唯一性约束？

Erwin Brandstetter 的回答表明了这一点：

WITH ins_string_properties AS (
  INSERT INTO string_properties (source_id, name, value)
  VALUES (gen_random_uuid(), 'slug', 'hello-world')
  ON CONFLICT DO NOTHING  -- to silence unique violation errors
  RETURNING source_id
  )
, ins_objects AS (
  INSERT INTO objects (id, type)
  SELECT o.id, o.type
  FROM   ins_string_properties isp  -- always 0 or 1 rows
  CROSS  JOIN LATERAL (
      VALUES 
        (isp.source_id    , 'baz')
      , (gen_random_uuid(), 'foo')
      , (gen_random_uuid(), 'bar')
      ) o(id, type)
  RETURNING id, type
  )
INSERT INTO object_properties (source_id, name, value_id)
SELECT io1.id, io2.type, io2.id
FROM   ins_objects io1
JOIN   ins_objects io2 ON io1.type = 'foo' AND io2.type = 'bar'
                       OR io1.type = 'bar' AND io2.type = 'baz'
;

我只是在学习 CTE，但答案是：

READ COMMITTED在具有默认事务隔离的并发写入负载下也是安全的。

我将在 CockroachDB 中使用它，他们似乎建议避免READ COMMITTED使用它SERIALIZABLE。

我可以将此查询与. 一起使用吗SERIALIZABLE，或者如果不可以，为什么不 / 必须修改什么以使其与SERIALIZABLE. 这些事务级别对我来说是新的，过去我主要使用 PostgreSQL 和 Ruby on Rails ORM，所以没有深入研究 SQL。只是尝试使用SERIALIZABLECockroachDB 推荐的默认事务隔离级别，并且不确定我是否/何时可以/不能使用它，并且不确定这种情况。

这就是我对READ COMMITTEDvs.的了解SERIALIZABLE。

假设我有一个类似这样的模式系统：

create table objects {
  uuid id;
  string type;
}

create table object_properties {
  uuid id;
  uuid source_id; // the object which has this property
  string name; // the property name
  uuid value_id; // the property value object
}

// ...and tables for each primitive data type
create table string_properties {
  uuid id;
  uuid source_id; // the object which has this property
  string name; // the property name
  string value; // the property value string
}

然后我想创建这个对象：

{
  type: 'foo',
  bar: {
    type: 'bar',
    baz: {
      type: 'baz',
      slug: 'hello-world'
    }
  }
}

那是：

// objects
id  | type
123 | foo
234 | bar
345 | baz

// object_properties
source_id   | name  | value_id
123         | bar   | 234
234         | baz   | 345

// string_properties
source_id   | name  | value
345         | slug  | hello-world

slug: hello-world如果以结尾的树不存在，我只想创建这个“对象树” 。我怎样才能做到最好？我可以很容易地做到这一点，首先进行查询，检查对象是否存在，如果不存在则创建它。但这是一个查询，然后是一个插入。有可能两个进程同时进入，都进行查询，都成功，然后都进行插入。我怎样才能防止这种情况？请注意，我目前在事务中都发生了每个独立的查询+插入，因此每个事务都有查询后跟插入。

或者update第一笔交易的内部是否可以从第二笔交易的“外部”读取？我正在使用 PostgreSQL / CockroachDB，这是一种“未提交读取”的设置吗？

我正在研究一个精简的数据库模式（在 PostgreSQL 中），比如旧的FreeBase，只是没有那么多东西。到目前为止大约有 100 张桌子，但那是在我考虑为特殊情况添加几十张之前，感觉不太对劲。让我用一个简化的例子来解释这个问题，它以一种小的方式复制了这个问题，想象一下它会变得更加复杂，一个表有几十个可选的属性/关系/关联，并且有许多像这样的不同的互连表。

我知道并广泛使用过像 MongoDB 这样的 NoSQL 文档数据库，并且在像 Neo4j 这样的图形数据库中涉足过。由于这个副项目的复杂性，它们是我宁愿避免使用的工具，而且用于部署的工具和资源与当今世界的 PostgreSQL 之类的东西不一样。

因此，为了说明这个问题，想象一个“符号”表，其中包含所有unicode符号，以及 unicode 范围之外的数千个符号（征兵、玛雅脚本、其他非脚本符号等）。基表如下所示：

table symbols {
  id
  unicode (optional)
  preview_image_url (optional)
  title
  description
}

我们已经有了一些可选属性，因为有些符号没有 unicode，有些不需要预览图像（所有 unicode 都可以在浏览器中呈现，等等）。但是，让我们考虑一下我们想要存储有关...的结构化信息的其他一些“类型”符号。

首先，我们可以想到“脚本”符号，即用于书写系统的符号。很酷，我们可以在我们的表中添加可选的“script_name”属性，这还不错。但是不，什么样的脚本符号？有从右到左的文字、垂直文字、表意文字、字母文字、abjads 和 abugidas 等。一些字母文字如拉丁文字有镜像符号（如括号）或大写/小写对。一些脚本将字符与特定规则组合在一起，这些规则可以与哪些组合。有些符号纯粹是装饰性的，有些是几何的。因此，我们尝试考虑所有这些可选功能：

table symbols {
  id
  unicode (optional)
  preview_image_url (optional)
  title
  description
  is_logographic (optional)
  is_vertical (optional)
  is_rtl (optional)
  is_alphabet (optional)
  is_abjad (optional)
  is_abugida (optional)
  script_name (optional)
  mirror_image_symbol_id (optional)
  uppercase_symbol_id (optional)
  lowercase_symbol_id (optional)
  combining_class (optional)
}

不过，有些人可能会说拥有所有这些可选属性还不错，我不知道。

然后您可以继续并添加更多子子类型....

• 类似三角形的符号（在 unicode 中有一些）
• 阴影三角形符号
• 空三角形符号

想象一下你可以尝试在谷歌上搜索与符号相关的所有可能的东西。

• 看起来像“c”的符号
  • ©（版权符号）
  • ?（版权符号）
  • ℃（摄氏度的符号）
  • ¢（美元的美分符号）
  • ₡（哥斯达黎加和萨尔瓦多货币冒号的符号）
  • ₵（塞地符号，加纳货币）
  • ₢（克鲁塞罗的符号，巴西的历史货币）
  • ℄（实际上是“cl”
• 带有内置组合标记的符号，如 é。
• 1 字节的 Unicode 字形
• 2 字节 unicode 字形
• 4字节...
• ...

它开始变成这样：

table symbols {
  id
  unicode (optional)
  preview_image_url (optional)
  title
  description
  is_logographic (optional)
  is_vertical (optional)
  is_rtl (optional)
  is_alphabet (optional)
  is_abjad (optional)
  is_abugida (optional)
  script_name (optional)
  mirror_image_symbol_id (optional)
  uppercase_symbol_id (optional)
  lowercase_symbol_id (optional)
  combining_class (optional)
  is_triangle_like (optional)
  is_shaded_triangle_like (optional)
  is_empty_triangle_like (optional)
  looks_like_c (optional)
  looks_like_d (optional)
  looks_like_l (optional)
  ...
  has_built_in_diacritic (optional)
  is_1_byte (optional)
  is_2_bytes (optional)
  ...
}

很快我们就会得到 50 或 100 个可选字段。当您尝试对“生物体”及其所有独特而多样的特征进行建模时，您可以想象这会变得更加复杂！数以千计的可选功能，并且没有明确的 OO 类层次结构来创建子类，它更像是一个互连组合的图/网络。

所以我的想法开始转向让事情变得超级抽象/通用，并创建一个名为“facts”的表格，比如：

table facts {
  id
  object_type
  object_id
  property_name
  value_type
  value_id
}

这样你就可以创建一个像“symbol a”这样的对象，并在它上面有“facts”，比如“属性名称是script_name，值类型是一个字符串表，其中一个字符串映射到一个ID，作为对象符号类型的属性” . 或者另一个事实是：

// facts table
id: 123
object_type: 'symbol'
object_id: 12321
property_name: 'is_1_byte'
value_type: 'boolean'
value_id: 444

// boolean table
id: 444
value: true

// symbol table
id: 12321
unicode: 'a'

但是沿着这条路走下去，你最终只会得到几个表（基本上是“事实”表，可能还有 1 或 2 个其他元表），而不是 100 个。但是事情变得更加难以思考和可视化，并且查询变得更复杂一些。

但我看不到解决这个问题的方法。我倾向于让数据库成为这种抽象类型的“事实”表，但是在应用程序层中让它看起来更面向对象，就像在 JavaScript 中一样，它有属性或没有属性。我想稍微“强化”一下，并给每个组合/变体一个不同的类型名称，但这并不完全奏效，例如：

{
  type: 'alphabet_symbol',
  value: 'e'
},
{
  type: 'geometric_symbol',
  value: '▲'
}

然后构建一个类型树：

symbol
  alphabet_symbol
    mirror_image_alphabet_symbol
      mirror_image_alphabet_symbol_with_capital_lowercase
      capital_lowercase_alphabet_symbol
  abjad_symbol
  geometric_symbol
    triangle_geometric_symbol
      shaded_triangle_geometric_symbol

但这打破了：

symbol
  alphabet_symbol
    (cyrillic б)
  look_like_6_symbol
    (cyrillic б)

所以就像，也许只是向中心对象添加标签。

б
  id: 455

tags
  - name: 'is_alphabet_symbol'
    symbol_id: 455
  - name: 'looks_like_6_symbol'
    symbol_id: 455

但在这一点上，它归结为我最初在您开始尝试处理更多案件时分享的通用/抽象“事实”想法。

// facts table
id: 124
object_type: 'symbol'
object_id: 455
property_name: 'is_alphabet_symbol'
value_type: 'boolean'
value_id: 444

// boolean table
id: 444
value: true

// symbol table
id: 455
unicode: 'б'

所以想知道，这里简要概述的处理“类型”的动态性和变化的推荐方法是什么？您如何平衡捕获尽可能多的结构化数据的愿望而不制作一个大的可选填充平面表（这似乎在几十个可选列之后分解，更不用说在有机体建模的情况下为 100 或 1000） .

查看维基百科文章历史上的奥巴马差异，可能有数千个差异。单击特定的较旧差异大约需要 3-5 秒才能加载，而不是更活泼的 Wikipedia 页面。然后你可以看到两个版本之间的具体差异，旧的旧版本和当前版本。

---

问题是，如果他们不使用 git（我会假设），他们如何有效地存储这些差异？

我正在寻找如何在我正在使用的小型玩具应用程序中实现文本差异跟踪。可能会有一百万页，每页的记录可能有 0 到 1000 次编辑。编辑将比维基百科的文章小得多，有时是一个单词的字符差异，有时是几个句子。

到目前为止我看到的推荐方法是存储记录文本的当前全文形式，然后存储“反向差异”以获取前一个，以及从那个等前一个。然后水合旧的文章，您将获取所有旧的差异（可能像维基百科差异中的 3000 个差异），然后从当前文本倒退以获取旧版本。不过，这似乎效率极低（而且我不确定如何计算“反向”差异，例如使用jsdiff）。这是最好的方法吗？我想您不想在每次更改时都存储全文的副本，或者维基百科会这样做吗？我想象的内容规模会爆炸式增长，尤其是在维基百科上。

基本上想知道如何通过以某种方式将内容存储在 SQL 数据库中来实现类似于维基百科的差异。我的案例没有维基百科那么大的内容，但如果知道如何针对这种“更糟糕的情况”正确解决它会很好。

所以我认为我有一个相当复杂的系统，它正在开始出现。写出所有表格太复杂了，但这里是一个简短的描述。

基本上我正在为帖子创建一个像 StackOverflow 这样的徽章系统。你有这些类型的表：

• 用户
• 帖子
• 事件（保存到数据库，以便您知道每个重要事件何时发生）
• user_statistics（徽章计数汇总等）
• post_statistics（帖子上的操作计数汇总，“已编辑 20 次”）
• user_badges（授予用户徽章）
• 徽章类型

然后假设您“更新帖子”。这是发生的事情：

• 帖子记录已更新
• 接下来会创建事件记录，上面写着“更新操作”，只有在它不存在时才会创建，否则它会重用相同的更新事件（以免防止向系统发送垃圾邮件）。它与帖子 ID 和用户 ID 相关联。
• 更新用户统计信息以计算新事件（如果已创建）。可能有多个统计信息需要更新，因为这些统计信息可能仅限于某些类别（例如特定语言的所有帖子）
• 如果统计数据达到阈值，则检查我们是否需要创建一个徽章或潜在的多个徽章，然后创建徽章。
• 可能会创建通知记录。
• 可能还有其他一些事情，例如提升站点上的权限，因为他们有更多的声誉，等等。
• 所有这一切都需要成功，所以没有什么可以做的（所有计数都是正确的，并且徽章被正确授予）。

您如何在 PostgreSQL 中适当地完成此任务？在我的具体情况下，似乎有大约 10 个表被查询，至少 5 个表被修改（创建或更新记录）。所有这些在理论上应该是原子的，在一个事务中，但是打包到一个事务中似乎很多，特别是如果你有这些“事件”在一秒钟内多次出现。

我能想到的唯一可能解决这个问题的初始方法是 使用队列和后台作业。上述每个步骤都将在事务之外按顺序完成，步骤之间可能存在时间间隔。所以会有一个中间状态，事情是不一致的。但最终（理论上似乎），队列将运行并重试直到成功，并进入正确的状态。这应该怎么做？

如果不是，是否可以在每个事件上进行单一交易的复杂性？我不知道，我不认为实施徽章和这些计数器的解决方案会变得如此复杂，但每个事件都有很多需要考虑和做的事情。基于您构建可扩展数据库系统的专业知识，我正在寻找任何指向正确方向的指针。

假设这个系统一定是这么复杂，因为我在理论上真的是在问如何处理复杂的事务需求。也就是说，如果您知道一种对徽章系统进行建模的理想方法，那会很高兴知道，但不会真正解决问题的主要部分。感谢您的帮助！

目前，就我的目的而言，一切都可以被认为适合单台机器，而不是分布在多个数据库中。

我不熟悉数据库方面的思考。我已经了解了 B 树如何存储在磁盘上，但我还没有看到实际的实现或了解它如何用于 B 树。

想知道如何在磁盘上存储跳过列表。有很多关于在内存中用指针存储它的描述，但我还没有看到关于磁盘存储的任何内容。想知道它在高层次上会是什么样子（如果它只是一个大文件或许多小文件，是否有不同类型的文件等）。

我看过这些论文：

• 涡轮图
• 图池
• BiShard 并行处理器
• G-商店
• NX图形
• 网格图

...和其他一些人。我是数据库的新手（除了在不了解其内部结构的情况下将它们用于 Web 应用程序之外），因此我对如何使用磁盘进行存储没有太多基础。

我想大致了解这些论文在文件系统上存储图形的目的。在简要浏览它们时，他们提到将相关子图从磁盘加载到内存中以进行高效更新/查询。其中一些将边存储在一组文件中（称为“碎片”），将顶点存储在另一组文件中（称为“间隔”）。有些有多个不同的ID，比如TurboGraph中的“记录ID（RID）”和“顶点ID” （下图1）。

但是，我还没有看到所有部分如何组合在一起的完整概述。想知道是否有人可以解释这一点。

具体来说：

1. 如何将数据结构化为图形数据库的文件（高级别）。
2. 在高层次上将内容加载到内存中进行查询/更新时必须发生什么。

到目前为止我还不清楚什么需要加载到内存中，以及这些 ID 的具体用途。我不确定每个页面（通常>= 1MB）是否被加载到内存中并以某种方式解析，或者逐行扫描，或类似的东西（基本上不确定文件是如何解析/扫描的，如果它是解析成某种内存数据结构，或者如果您可以直接在文件字节上进行图形遍历）。而且我不确定 ID 的用途。在 RDBMS 中，ID 有时是每个表的递增整数，没有其他含义。在这些论文中，ID 似乎更多地与页面中顶点的位置加上某种偏移量等有关。此外，一些论文为一个顶点及其所有边（邻接列表）存储一条大单线（看起来），但我想知道如果每个顶点有数千或数百万条边该怎么办。如果可以指出要寻找的相关特征，那么进行进一步研究将有助于使这一点更加清晰。

非常感谢您抽出宝贵时间，我希望这是有道理的。

---

图1。

我想知道如何在物理磁盘上存储树，也许是像XML 数据库这样的面向树的数据库。

维基百科关于 XML 数据库的陈述：

...自定义优化的数据结构用于存储和查询。这通常会提高只读查询和更新方面的性能。XML 节点和文档是（逻辑）存储的基本单元，就像关系数据库具有字段和行一样。

但没有解释将树存储在磁盘上的实际数据结构是什么。想知道是否有人可以解释它是如何工作的。似乎您可以将每个“文档”存储为自己的文件，例如作为文件：

<a>
  <b>
    <c>
      <d>Hello</d>
    </c>
    <c>
      <x>World</x>
    </c>
    <e>
      <f ref="b:0/c:0/d"></f>
    </e>
    <g>
      <f ref="b:0/c:0/x"></f>
    </g>
  </b>
  <b>
    <c>
      <d>Hello2</d>
    </c>
    <c>
      <x>World2</x>
    </c>
    <e>
      <f ref="b:1/c:0/d"></f>
    </e>
    <g>
      <f ref="b:1/c:0/x"></f>
    </g>
  </b>
</a>

然后你会有一个查询“给我所有 b/c/x，其中 b/c/d 匹配 Hello”，或者只是“给我所有 c 下面的节点”。是指向特定节点的ref="b:0/c:0/d"指针，类似的东西。

基本上我想知道，为了完成这些事情，用于在磁盘上存储 XML 或任何树结构的数据结构。

也许不是将整个 XML 文档存储在一个文件中，而是最终存储如下内容：

a/b/c/d Hello
a/b/c/d World
a/b/e/f ref=b:0/c:0/d
a/b/g/f ref=b:0/c:1/d
...

这正是我想知道的，数据在物理层面上在磁盘文件中的样子。不太确定要搜索什么。XML 数据库数据结构等不会返回太多。

This SO answer概述了MySQL如何将其数据库组织成文件，但没有描述文件本身的结构。

我想知道，在物理层面上，关系数据库管理系统如何将它们的记录存储在文件中。文件的结构大致是什么，列/值在哪里（如果它只是一个 CSV），以及它是如何遍历的。