使用二进制排序索引

目录结构

注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：

1、参考书籍：《PostgreSQL数据库内核分析》
2、参考书籍：《数据库事务处理的艺术：事务管理与并发控制》
3、PostgreSQL数据库仓库链接，点击前往
4、日本著名PostgreSQL数据库专家 铃木启修 网站主页，点击前往
5、参考书籍：《PostgreSQL中文手册》
6、参考书籍：《PostgreSQL指南：内幕探索》，点击前往
7、参考书籍：《事务处理 概念与技术》
8、Using binary-sorted indexes，点击前往
9、Waiting for Postgres 17: The new built-in C.UTF-8 locale，点击前往

1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
2、本文目的：开源共享 抛砖引玉 一起学习
3、本文不提供任何资源 不存在任何交易 与任何组织和机构无关
4、大家可以根据需要自行 复制粘贴以及作为其他个人用途，但是不允许转载 不允许商用 （写作不易，还请见谅 💖）
5、本文内容基于PostgreSQL master源码开发而成

@

目录

文章快速说明索引

学习目标：

做数据库内核开发久了就会有一种 少年得志，年少轻狂 的错觉，然鹅细细一品觉得自己其实不算特别优秀 远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在，每每想到于此，皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉，即日起 暂缓其他基于PostgreSQL数据库的兼容功能开发，近段时间 将着重于学习分享Postgres的基础知识和实践内幕。

学习内容：（详见目录）

1、深入理解PostgreSQL数据库之使用二进制排序索引

学习时间：

2024-03-25 16:59:00 星期一

学习产出：

1、PostgreSQL数据库基础知识回顾 1个
2、CSDN 技术博客 1篇
3、PostgreSQL数据库内核深入学习

注：下面我们所有的学习环境是Centos8+PostgreSQL master+Oracle19C+MySQL8.0

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
(1 row)

postgres=#

#-----------------------------------------------------------------------------#

SQL> select * from v$version;          

BANNER        Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production	
BANNER_FULL	  Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0	
BANNER_LEGACY Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production	
CON_ID 0


#-----------------------------------------------------------------------------#

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.06 sec)

mysql>

使用二进制排序索引

在上一篇文章中，我提到 Postgres 数据库通常具有按语言排序而不是按字节排序的文本索引，这就是为什么它们需要在 libc 或 ICU 升级时重新索引。在这篇文章中，我们将讨论如何使用字节排序，以及这样做的优点和缺点是什么。

对二进制字符串进行排序意味着比较字符串内的字节，而不关心它们代表什么字符。例如，在 UTF-8 数据库中，考虑字符串 Beta 和 alpha 时：

  • 按字节比较表明 'Beta' < 'alpha'，因为大写字母 B 的代码点是 0x42，小写字母 a 的代码点是 0x61
  • 语言比较表明'alpha' < 'Beta'，因为它知道即使大小写混合，字母 a 也会出现在 B 之前。更普遍的语言校对具有有关重音、标点符号、符号以及潜在的区域定制的排序规则

这些类型的简单优缺点比较如下：

Linguistic order Binary order
Ease of use ✅ better ❌ worse
Human readability ✅ better ❌ worse
Range search (*) ✅ better ❌ worse
Performance ❌ worse ✅ better
Portability ❌ worse ✅ 100%
Real immutability ❌ No ✅ Yes
LIKE prefix search ❌ No ✅ Yes

(*) 在两个边界之间定位字符串，例如输出分页结果

如何在索引中包含二进制顺序的文本？

第一种情况是文本列或表达式具有显式排序规则，其 collcollate 属性设置为以下区域设置之一：

  • C，在 Postgres 中始终可用
  • POSIX（C 的别名）
  • C.utf8（或 C.UTF-8）适用于某些操作系统（Linux、FreeBSD...）例如
CREATE TABLE product(
 product_id uuid,
 product_code text COLLATE "C" UNIQUE
);

为支持 product_code 的唯一性约束而创建的索引将按二进制顺序排序。它还意味着 ORDER BY Product_code 如果出现在查询中，将使用二进制排序顺序。要以其他方式排序，需要将显式 COLLATE 子句添加到查询中。

第二种情况是创建列时未指定 COLLATE 子句，但数据库的默认区域设置是这些二进制排序区域设置之一。当使用以下命令创建数据库时会发生这种情况：

CREATE DATABASE dbname LOCALE=binary-sorting-locale TEMPLATE=template0;
-- or
CREATE DATABASE dbname LC_COLLATE=binary-sorting-locale TEMPLATE=template0;

这意味着数据库中的所有文本排序都将以二进制形式进行，除非使用具有不同排序规则的显式 COLLATE 子句。

第三种情况是数据库不是使用这些选项创建的，但 template1 数据库是使用二进制排序语言环境初始化的。例如，在现代 Linux 系统（glibc 2.35 或更高版本）上，C.utf8 现在是内置语言环境，initdb --locale=C.utf8 将在所有数据库中默认使用以二进制排序的文本来初始化 Postgres 实例。

请注意，ICU 排序规则不能用于此目的，因为 ICU 库不会公开任何以二进制方式排序的语言环境。

为什么大多数安装不使用任何二进制排序？

在大多数 Postgres 安装中，initdb 不是使用 --locale 参数调用的，因此它是从环境中获取的（通常来自 $LANG，请参阅 GLIBC 中的区域设置 或 POSIX 规范的区域设置章节了解更多信息）。

正如文档所说：

默认情况下，initdb 将使用其执行环境的区域设置来初始化数据库集群
因此，如果您的系统已设置为使用数据库集群中所需的区域设置，则无需执行任何其他操作

通常，$LANG 会在安装操作系统时设置语言和区域，例如 en_US.utf8。然后区域设置的选择进入 template0 和 template1。然后用户创建的数据库继承它，然后在这些数据库中文本列也继承它。最后，基于这些列的索引按照列的排序规则进行排序，因此它们也继承自该区域设置。

简而言之，通过在创建所有这些对象时遵循默认设置，我们得到了这个事件链，其中所有内容都设置为使用操作系统的默认区域设置。

$LANG -> initdb -> template dbs -> user-created dbs -> columns -> indexes

但开发人员甚至 DBA 不一定决定或期望 $LANG 在此链的开头有任何特定值。当生成 SQL 脚本来创建数据库、模式、表时，开发人员通常不会规定区域设置，并假设默认安装的区域设置即可。不关心创建脚本和查询中的排序规则和区域设置也更简单。

然而，当必须升级实现 en_US.utf8 语言环境或任何其他语言排序语言环境的库时，在重新联机之前必须重新创建依赖于该语言环境的任何索引，这是一个数据库/DBA 问题。

因此，为了减少出现该问题的可能性，我们可能会在创建时提前询问每个索引列：该列是否真的需要语言排序顺序？很多时候，答案是否定的，按字节就可以了。甚至有时不仅很好，而且实际上更好。

或者考虑数据库本身，我们可能会问：在此数据库中运行的哪些查询需要语言排序顺序？如果答案是否定的，那么为什么不默认使用数据库的二进制排序规则呢？

但是，由于binary不是我们在索引上设置的属性，而是使用碰巧以二进制排序的排序规则的结果，因此在切换到二进制排序规则之前需要了解一些问题。让我们在这篇文章的其余部分讨论这些。

C/POSIX 的弱点

在 Postgres 中，排序规则的选择不仅驱动排序顺序，还驱动字符分类和大小写转换。具体来说，是 upper()、lower()、initcap() 等函数的结果，以及模式匹配中关于哪些字符是字母、数字和标点符号的决策。

由于 libc 允许两个独立的语言环境进行排序和分类，Postgres libc 排序规则也是如此：用于排序的语言环境位于 pg_collation.collcollate 中，用于字符分类和大小写转换的语言环境位于 pg_collation.collctype 中，分别对应于 CREATE COLLATION 的 LC_COLLATE 和 LC_CTYPE 选项。

在数据库级别，默认的 libc 排序规则也作为双重设置：pg_database.datcollate 和 pg_database.datctype，分别对应于 CREATE DATABASE 命令的 LC_COLLATE 和 LC_CTYPE 选项。

使用 C 或 POSIX 可以对 Postgres 支持的所有字符集进行排序（collate部分），包括utf-8，但是当涉及到字符分类和大小写转换（ctype部分）时，它只知道US-ASCII字符集。所以基本上它给出了英语的正确结果，但仅此而已。例子：

-- show the inability of "C" to uppercase accented characters
test=> select initcap('élysée' collate "C");
 initcap 
---------
 éLyséE

C.utf8 排序规则

C.utf8 旨在像 C 一样对二进制文本进行排序，但为具有所有 Unicode 字符的类 ctype 函数提供正确的结果（至少达到您的 libc 支持的 Unicode 版本）。例子：

-- show the ability of "C.utf8" to uppercase accented characters
test=> select initcap('élysée' collate "C.utf8");
 initcap 
---------
 Élysée

C.utf8 的主要问题是它不是 POSIX 标准的一部分，并非在所有地方都受支持，对于 Linux 系统上通常使用的 GNU libc 来说，自 2022 年 2 月发布的 GNU libc 2.35 起才得到完全支持。Debian 或 Redhat 等主要发行版在此之前已经提供了实现此语言环境的补丁，但它们碰巧没有对二进制中的所有代码点进行排序，因此这些 2.35 之前的版本不可信。

另一个更微妙的问题是，Postgres 并不假设或知道 C.utf8 以二进制排序，即使它确实如此，因此它不会使用与 C 排序规则一起使用的一些优化，例如使用索引来匹配左锚定left-anchored的 LIKE 模式。

在没有适当的 C.utf8 区域设置的系统上，或者如果想要克服这种缺乏优化的问题，Postgres 排序规则可以通过 lc_collate=C 和 lc_ctype=some-locale 进行，您可以根据您的操作系统找到适合的 some-locale。

例如，要获取默认为二进制排序且对 ctype 函数具有正确 UTF-8 支持的数据库，假设操作系统具有 en_US.utf8，则可以执行以下操作：

CREATE DATABASE dbname
   TEMPLATE='template0'
   ENCODING='UTF8'
   LC_COLLATE='C'
   LC_CTYPE='en_US.utf8';

未来内置的C.utf8排序规则

C.utf8 的概念很好，但事实上它并不普遍可用，而且它的某些行为仍然依赖于操作系统，这是有问题的。目前正在研究的解决方案是独立于操作系统的 Postgres 二进制排序规则，它非常类似于 C.utf8，但在 Postgres 中内化。在当前的 CommitFest 中，这被称为"C"和"C.UTF-8"的内置排序规则提供程序。

希望这项工作最终会出现在下一个 PostgreSQL 版本中，并为未来的二进制排序规则提供最简单的选择。但目前，更多的是“自己动手”。

"unicode"或"und-x-icu"排序规则

现在假设我们创建了一个数据库，其默认排序规则以二进制排序。有时，我们可能希望输出以“人类方式”排序的文本，以便"alpha"位于"Beta"之前，更不用说更复杂的语言规则了。

如上所述，在这种情况下需要显式 COLLATE 子句。但是用什么排序规则呢？对于 libc 排序规则，除了 C/POSIX 之外，SQL 脚本和查询不能总是知道操作系统中定义的 libc 语言环境，因此也无法知道 pg_collation 中定义的 libc 语言环境。此外，COLLATE 子句需要一个标识符作为排序规则的名称，而不是参数，这迫使我们对该名称进行硬编码。

支持 ICU 的 Postgres 安装碰巧有硬编码的与语言无关的排序规则，并且保证存在。 从 Postgres 16 开始，名称unicode被分配给根 unicode 排序规则，因此基本上任何查询都可以执行以下操作：

SELECT ... FROM ... ORDER BY colname COLLATE "unicode";

并获得语言感知的顺序。

在Postgres 16之前，我们可以使用更奇怪的命名und-x-icu排序规则，它也保证存在。

结论

正如 Unicode 字符库在每个 Unicode 版本中不断增长（大约每年一次）一样，UTF-8 排序规则的版本也随之变化，并且 Postgres 的排序规则升级问题永远不会消失。

虽然 Postgres 拥有对语言排序规则的良好支持是必要的，但这并不意味着它们必须用于我们数据库中的每一段文本，并且我们需要支付随之而来的重新索引的成本。

由于语言排序往往是默认的，因此使用二进制排序规则或二进制排序规则和语言排序规则的混合会涉及更多一些，但可能值得考虑。

posted @ 2024-03-26 11:35  孤傲小二~阿沐  阅读(54)  评论(0)    收藏  举报