主持人 > 博客 > 管理 > 平衡数据库中的写入和读取

平衡数据库中的写入和读取

平衡数据库中的写入和读取
在以前 文章 我描述了基于函数而不是关系数据库中的表和字段构建的数据库的概念和实现。 它提供了许多示例，显示了这种方法相对于经典方法的优势。 许多人发现它们不够令人信服。

在这篇文章中，我将展示这个概念如何让您在不改变操作逻辑的情况下快速、方便地平衡数据库的写入和读取。 类似的功能已尝试在现代商业 DBMS（特别是 Oracle 和 Microsoft SQL Server）中实现。 在文章的最后，我将表明，温和地说，他们所做的事情并没有取得很好的效果。

使用说明

和以前一样，为了更好地理解，我将从示例开始描述。 假设我们需要实现一个逻辑，返回一个部门列表，其中包含员工人数及其总工资。

在功能数据库中，它看起来像这样：

CLASS Department ‘Отдел’;
name ‘Наименование’ = DATA STRING[100] (Department);

CLASS Employee ‘Сотрудник’;
department ‘Отдел’ = DATA Department (Employee);
salary ‘Зарплата’ =  DATA NUMERIC[10,2] (Employee);

countEmployees ‘Кол-во сотрудников’ (Department d) = 
    GROUP SUM 1 IF department(Employee e) = d;
salarySum ‘Суммарная зарплата’ (Department d) = 
    GROUP SUM salary(Employee e) IF department(e) = d;

SELECT name(Department d), countEmployees(d), salarySum(d);

在任何 DBMS 中执行此查询的复杂性将相当于 O(员工人数)因为这个计算需要扫描整个员工表，然后按部门进行分组。 根据所选计划，还会有一些小额（我们相信员工数量比部门多得多）补充 O（员工数量的对数） или O(部门数量) 用于分组等。

很明显，不同的 DBMS 中的执行开销可能不同，但复杂性不会有任何变化。

在建议的实现中，功能性 DBMS 将生成一个子查询，该子查询将计算部门所需的值，然后与部门表进行 JOIN 以获得名称。 然而，对于每个函数，在声明时，可以设置一个特殊的 MATERIALIZED 标记。 系统会自动为每个此类功能创建相应的字段。 当改变函数的值时，同一事务中字段的值也会改变。 当访问该函数时，将访问预先计算的字段。

特别是，如果您为函数设置 MATERIALIZED 计数员工 и 工资总额，然后两个字段将添加到部门列表表中，其中将存储员工人数及其总工资。 每当员工、其工资或部门隶属关系发生变化时，系统都会自动更改这些字段的值。 上面的查询将直接访问这些字段并在 O(部门数量).

有什么限制？ 只有一件事：这样的函数必须具有有限数量的输入值，并为其定义其值。 否则，将不可能构建一个存储所有值的表，因为不可能存在具有无限行数的表。

示例：

employeesCount ‘Количество сотрудников с зарплатой > N’ (Department d, NUMERIC[10,2] N) = 
    GROUP SUM salary(Employee e) IF department(e) = d AND salary(e) > N;

该函数是针对无限多个 N 值定义的（例如，任何负值都是合适的）。 因此，你不能将 MATERIALIZED 放在上面。 所以这是一个逻辑限制，而不是技术限制（也就是说，不是因为我们无法实现它）。 否则，没有任何限制。 您可以使用分组、排序、AND 和 OR、PARTITION、递归等。

例如，在上一篇文章的问题 2.2 中，您可以将 MATERIALIZED 放在两个函数上：

bought 'Купил' (Customer c, Product p, INTEGER y) = 
    GROUP SUM sum(Detail d) IF 
        customer(order(d)) = c AND 
        product(d) = p AND 
        extractYear(date(order(d))) = y MATERIALIZED;
rating 'Рейтинг' (Customer c, Product p, INTEGER y) = 
    PARTITION SUM 1 ORDER DESC bought(c, p, y), p BY c, y MATERIALIZED;
SELECT contactName(Customer c), name(Product p) WHERE rating(c, p, 1997) < 3;

系统本身将创建一张带有类型键的表 对客户的, 产品 и INTEGER，将向其中添加两个字段，并根据任何更改更新其中的字段值。 当进一步调用这些函数时，它们不会被计算，而是从相应的字段中读取值。

例如，使用此机制，您可以消除查询中的递归 (CTE)。 特别是，考虑使用子/父关系形成树的组（每个组都有到其父组的链接）：

parent = DATA Group (Group);

在函数式数据库中，递归逻辑可以指定如下：

level (Group child, Group parent) = RECURSION 1l IF child IS Group AND parent == child
                                                             STEP 2l IF parent == parent($parent);
isParent (Group child, Group parent) = TRUE IF level(child, parent) MATERIALIZED;

由于对于函数 是父级 被标记为MATERIALIZED，那么将为它创建一个有两个键（组）的表，其中字段 是父级 仅当第一个键是第二个键的子键时才为真。 该表中的条目数将等于组数乘以树的平均深度。 例如，如果您需要计算某个群体的后代数量，您可以使用此函数：

childrenCount (Group g) = GROUP SUM 1 IF isParent(Group child, g);

SQL 查询中不会有 CTE。 相反，会有一个简单的 GROUP BY。

使用这种机制，如果需要，您还可以轻松地对数据库进行非规范化：

CLASS Order 'Заказ';
date 'Дата' = DATA DATE (Order);

CLASS OrderDetail 'Строка заказа';
order 'Заказ' = DATA Order (OrderDetail);
date 'Дата' (OrderDetail d) = date(order(d)) MATERIALIZED INDEXED;

调用函数时 日期 对于订单行，将从带有订单行的表中读取有索引的字段。 当订单日期发生变化时，系统本身将自动重新计算行中的非规范化日期。

优点

这整个机制是做什么用的？ 在经典的 DBMS 中，无需重写查询，开发人员或 DBA 只能更改索引、确定统计信息并告诉查询计划者如何执行它们（并且提示仅在商业 DBMS 中可用）。 无论他们如何努力，他们都无法完成文章中的第一个查询 O（部门数量） 无需更改查询或添加触发器。 在所提出的方案中，在开发阶段您不必考虑数据存储结构以及使用哪些聚合。 所有这些都可以直接在运行中轻松即时更改。

实际上它看起来像这样。 有些人直接根据手头的任务开发逻辑。 他们不了解算法及其复杂性，也不了解执行计划，也不了解连接类型，也不了解任何其他技术组件。 这些人更多的是业务分析师而不是开发人员。 然后，所有这些都进入测试或操作。 启用长时间运行的查询的日志记录。 当检测到长查询时，其他人（技术性更强 - 本质上是 DBA）决定在某些中间函数上启用 MATERIALIZED。 这会稍微减慢记录速度（因为它需要更新事务中的附加字段）。 但是，不仅此查询显着加快，而且使用此功能的所有其他查询也显着加快。 同时，决定实现哪个功能也相对容易。 两个主要参数：可能的输入值的数量（这是相应表中将有多少条记录），以及在其他函数中使用的频率。

类似物

现代商业 DBMS 具有类似的机制：具有快速刷新功能的物化视图 (Oracle) 和索引视图 (Microsoft SQL Server)。 在 PostgreSQL 中，MATERIALIZED VIEW 不能在事务中更新，而只能根据请求进行更新（甚至有非常严格的限制），因此我们不考虑它。 但它们有几个问题严重限制了它们的使用。

首先，如果您已经创建了常规视图，则只能启用具体化。 否则，您将必须重写其余请求来访问新创建的视图才能使用此具体化。 或者保持一切原样，但如果有某些已经预先计算好的数据，但许多查询并不总是使用它，而是重新计算它，那么它至少是无效的。

其次，它们有很多限制：

神谕

5.3.8.4 快速刷新的一般限制

物化视图的定义查询受到如下限制：

  • 物化视图不得包含对非重复表达式的引用，例如 SYSDATE 和 ROWNUM.
  • 物化视图不得包含对 RAW or LONG RAW 数据类型。
  • 它不能包含 SELECT 列出子查询。
  • 它不能包含分析函数（例如， RANK在） SELECT 条款。
  • 它不能引用一个表 XMLIndex 索引已定义。
  • 它不能包含 MODEL 条款。
  • 它不能包含 HAVING 带有子查询的子句。
  • 它不能包含具有以下功能的嵌套查询 ANY, ALL或 NOT EXISTS.
  • 它不能包含 [START WITH …] CONNECT BY 条款。
  • 它不能包含不同站点的多个明细表。
  • ON COMMIT 物化视图不能有远程详细信息表。
  • 嵌套物化视图必须具有联接或聚合。
  • 物化连接视图和物化聚合视图 GROUP BY 子句无法从索引组织表中进行选择。

5.3.8.5 仅连接的物化视图快速刷新的限制

定义仅具有连接而不具有聚合的物化视图的查询对快速刷新有以下限制：

  • 所有限制来自 «快速刷新的一般限制«。
  • 他们不能有 GROUP BY 子句或聚合。
  • 中所有表的rowid FROM 列表必须出现在 SELECT 查询的列表。
  • 物化视图日志必须与所有基表的 rowid 一起存在 FROM 查询的列表。
  • 您无法使用在表中包含对象类型列的简单联接从多个表创建可快速刷新的物化视图。 SELECT 语句。

此外，如果出现以下情况，您选择的刷新方法将不会达到最佳效率：

  • 定义查询使用行为类似于内连接的外连接。 如果定义查询包含此类联接，请考虑重写定义查询以包含内部联接。
  • SELECT 物化视图的列表包含来自多个表的列的表达式。

5.3.8.6 对具有聚合的物化视图的快速刷新的限制

使用聚合或连接定义物化视图的查询对快速刷新有以下限制：

两者均支持快速刷新 ON COMMIT 和 ON DEMAND 物化视图，但是存在以下限制：

  • 物化视图中的所有表都必须有物化视图日志，并且物化视图日志必须：
    • 包含物化视图中引用的表中的所有列。
    • 指定为 ROWID 和 INCLUDING NEW VALUES.
    • 指定 SEQUENCE 如果表预计混合插入/直接加载、删除和更新。

  • 只有 SUM, COUNT, AVG, STDDEV, VARIANCE, MIN 和 MAX 支持快速刷新。
  • COUNT(*) 必须指定。
  • 聚合函数必须仅作为表达式的最外层部分出现。 也就是说，诸如 AVG(AVG(x)) or AVG(x)+ AVG(x) 不允许。
  • 对于每个聚合，例如 AVG(expr)， 相应的 COUNT(expr) 必须在场。 甲骨文建议 SUM(expr) 被指定。
  • If VARIANCE(expr) or STDDEV(expr) 被指定， COUNT(expr) 和 SUM(expr) 必须指定。 甲骨文建议 SUM(expr *expr) 被指定。
  • SELECT 定义查询中的列不能是包含来自多个基表的列的复杂表达式。 一个可能的解决方法是使用嵌套物化视图。
  • SELECT 列表必须包含所有 GROUP BY 列。
  • 物化视图不基于一个或多个远程表。
  • 如果使用 CHAR 物化视图日志过滤列的数据类型，主站和物化视图的字符集必须相同。
  • 如果物化视图具有以下之一，则仅在传统的 DML 插入和直接加载上支持快速刷新。
    • 物化视图 MIN or MAX 聚集
    • 物化视图具有 SUM(expr) 但不是 COUNT(expr)
    • 物化视图没有 COUNT(*)

    这种物化视图称为仅插入物化视图。

  • 物化视图 MAX or MIN 如果没有，则在删除或混合 DML 语句后可快速刷新 WHERE 条款。
    删除或混合 DML 后的最大/最小快速刷新与仅插入情况的行为不同。 它删除并重新计算受影响组的最大/最小值。 您需要了解其性能影响。
  • 具有命名视图或子查询的物化视图 FROM 如果视图可以完全合并，子句可以快速刷新。 有关将合并哪些视图的信息，请参阅 Oracle 数据库 SQL 语言参考.
  • 如果没有外连接，您可以在其中进行任意选择和连接 WHERE 条款。
  • 具有外连接的物化聚合视图在传统 DML 和直接加载之后可以快速刷新，前提是仅修改了外表。 此外，内连接表的连接列上必须存在唯一约束。 如果存在外连接，则所有连接必须通过以下方式连接 ANDs 并且必须使用等式 (=） 操作员。
  • 对于物化视图 CUBE, ROLLUP、分组集或它们的串联，适用以下限制：
    • SELECT 列表应包含分组区分符，可以是 GROUPING_ID 作用于所有 GROUP BY 表达式或 GROUPING 各有一个功能 GROUP BY 表达。 例如，如果 GROUP BY 物化视图的子句是“GROUP BY CUBE(a, b)”，那么 SELECT 列表应包含“GROUPING_ID(a, b)“ 要么 ”GROUPING(a) AND GROUPING(b)» 使物化视图能够快速刷新。
    • GROUP BY 不应导致任何重复的分组。 例如， ”GROUP BY a, ROLLUP(a, b)“刷新速度不快，因为它会导致重复分组”(a), (a, b), AND (a)«。

5.3.8.7 使用 UNION ALL 对物化视图快速刷新的限制

物化视图 UNION ALL 集合运算符支持 REFRESH FAST 如果满足以下条件，则可以选择：

  • 定义查询必须具有 UNION ALL 顶层操作员。

    UNION ALL 运算符不能嵌入子查询中，但有一个例外： UNION ALL 可以在子查询中 FROM 子句提供的定义查询的形式为 SELECT * FROM （视图或子查询 UNION ALL）如以下示例所示：

    CREATE VIEW view_with_unionall AS (SELECT c.rowid crid, c.cust_id, 2 umarker FROMcustomers c WHERE c.cust_last_name = 'Smith' UNION ALL SELECT c.rowid crid, c.cust_id, 3 umarker FROMcustomers c WHERE c.cust_last_name = “琼斯”）； 创建物化视图 unionall_inside_view_mv 按需快速刷新 AS SELECT * FROM view_with_unionall;

    请注意，该视图 view_with_unionall 满足快速刷新的要求。

  • 中的每个查询块 UNION ALL 查询必须满足具有聚合的快速可刷新物化视图或具有联接的快速可刷新物化视图的要求。

    必须根据相应类型的快速刷新物化视图的需要在表上创建适当的物化视图日志。
    请注意，Oracle 数据库还允许具有连接的单表物化视图的特殊情况，前提是 ROWID 列已包含在 SELECT 列表和物化视图日志中。 这显示在视图的定义查询中 view_with_unionall.

  • SELECT 每个查询的列表必须包含一个 UNION ALL 标记，以及 UNION ALL 每个列中必须有一个不同的常量数字或字符串值 UNION ALL 分支。 此外，标记列必须出现在相同的顺序位置 SELECT 每个查询块的列表。 看 ”UNION ALL 标记和查询重写» 有关更多信息 UNION ALL 标记。
  • 物化视图不支持某些功能，例如外连接、仅插入聚合物化视图查询和远程表 UNION ALL。 但请注意，复制中使用的物化视图不包含联接或聚合，可以在以下情况下快速刷新： UNION ALL 或使用远程表。
  • 兼容性初始化参数必须设置为 9.2.0 或更高版本才能创建快速可刷新的物化视图 UNION ALL.

我不想冒犯Oracle粉丝，但从他们的限制列表来看，这个机制似乎不是在一般情况下使用某种模型编写的，而是由成千上万的印度人编写的，每个人都有机会编写自己的分支，每个人都尽其所能，并且做到了。 将这种机制用于真正的逻辑就像走过雷区一样。 只要满足一项不明显的限制，你就可以随时获得地雷。 它如何工作也是一个单独的问题，但这超出了本文的范围。

微软SQL Server

其他要求

除了 SET 选项和确定性函数要求外，还必须满足以下要求：

  • 执行的用户 CREATE INDEX 必须是视图的所有者。
  • 当您创建索引时， IGNORE_DUP_KEY 选项必须设置为“关闭”（默认设置）。
  • 表必须由两部分名称引用， 架构.表名 在视图定义中。
  • 视图中引用的用户定义函数必须使用 WITH SCHEMABINDING 选项。
  • 视图中引用的任何用户定义函数必须由两部分名称引用， ..
  • 用户定义函数的数据访问属性必须是 NO SQL，并且外部访问属性必须是 NO.
  • 公共语言运行时 (CLR) 函数可以出现在视图的选择列表中，但不能成为聚集索引键定义的一部分。 CLR 函数不能出现在视图的 WHERE 子句或视图中 JOIN 操作的 ON 子句中。
  • 视图定义中使用的 CLR 用户定义类型的 CLR 函数和方法必须具有如下表所示的属性设置。

    以高
    备注

    确定性 = 正确
    必须显式声明为 Microsoft .NET Framework 方法的属性。

    精确=真实
    必须显式声明为 .NET Framework 方法的属性。

    数据访问 = 无 SQL
    通过将 DataAccess 属性设置为 DataAccessKind.None 并将 SystemDataAccess 属性设置为 SystemDataAccessKind.None 来确定。

    外部访问 = 否
    对于 CLR 例程，此属性默认为 NO。

  • 该视图必须使用以下命令创建 WITH SCHEMABINDING 选项。
  • 该视图必须仅引用与该视图位于同一数据库中的基表。 该视图不能引用其他视图。
  • 视图定义中的 SELECT 语句不得包含以下 Transact-SQL 元素：

    COUNT
    ROWSET 函数（OPENDATASOURCE, OPENQUERY, OPENROWSET， 和 OPENXML)
    OUTER 加入（LEFT, RIGHT或 FULL)

    派生表（通过指定 SELECT 中的陈述 FROM 条款）
    自加入
    通过使用指定列 SELECT * or SELECT <table_name>.*

    DISTINCT
    STDEV, STDEVP, VAR, VARP或 AVG
    公用表表达式 (CTE)

    浮动1, 文本, 文字, 图片, XML或 文件流 列
    子查询
    OVER 子句，其中包括排名或聚合窗口函数

    全文谓词 (CONTAINS, FREETEXT)
    SUM 引用可为空表达式的函数
    ORDER BY

    CLR用户定义的聚合函数
    TOP
    CUBE, ROLLUP或 GROUPING SETS 运营商

    MIN, MAX
    UNION, EXCEPT或 INTERSECT 运营商
    TABLESAMPLE

    表变量
    OUTER APPLY or CROSS APPLY
    PIVOT, UNPIVOT

    稀疏列集
    内联 (TVF) 或多语句表值函数 (MSTVF)
    OFFSET

    CHECKSUM_AGG

    1 索引视图可以包含 浮动 列; 但是，此类列不能包含在聚集索引键中。

  • If GROUP BY 存在，VIEW 定义必须包含 COUNT_BIG(*) 并且不得包含 HAVING。 这些 GROUP BY 限制仅适用于索引视图定义。 即使查询不满足这些条件，也可以在其执行计划中使用索引视图 GROUP BY 限制。
  • 如果视图定义包含 GROUP BY 子句中，唯一聚集索引的键只能引用子句中指定的列 GROUP BY 条款。

很明显，印度人没有参与其中，因为他们决定按照“我们会做得很少，但做得很好”的计划来做。 也就是说，他们场上的地雷更多，但位置更透明。 最令人失望的是这个限制：

该视图必须仅引用与该视图位于同一数据库中的基表。 该视图不能引用其他视图。

用我们的术语来说，这意味着一个函数无法访问另一个物化函数。 这将所有意识形态消灭在萌芽状态。
此外，这种限制（以及文本中的进一步限制）大大减少了用例：

视图定义中的 SELECT 语句不得包含以下 Transact-SQL 元素：

COUNT
ROWSET 函数（OPENDATASOURCE, OPENQUERY, OPENROWSET， 和 OPENXML)
OUTER 加入（LEFT, RIGHT或 FULL)

派生表（通过指定 SELECT 中的陈述 FROM 条款）
自加入
通过使用指定列 SELECT * or SELECT <table_name>.*

DISTINCT
STDEV, STDEVP, VAR, VARP或 AVG
公用表表达式 (CTE)

浮动1, 文本, 文字, 图片, XML或 文件流 列
子查询
OVER 子句，其中包括排名或聚合窗口函数

全文谓词 (CONTAINS, FREETEXT)
SUM 引用可为空表达式的函数
ORDER BY

CLR用户定义的聚合函数
TOP
CUBE, ROLLUP或 GROUPING SETS 运营商

MIN, MAX
UNION, EXCEPT或 INTERSECT 运营商
TABLESAMPLE

表变量
OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

稀疏列集
内联 (TVF) 或多语句表值函数 (MSTVF)
OFFSET

CHECKSUM_AGG

禁止使用 OUTER JOINS、UNION、ORDER BY 等。 指定可以使用什么而不是不能使用什么可能更容易。 这个列表可能会短得多。

总结一下：LGPL 技术中的每个（让我们注意商业）DBMS 都存在大量限制，而没有（逻辑上的，非技术的除外）。 然而，应该注意的是，在关系逻辑中实现这种机制比在所描述的功能逻辑中实现要困难一些。

履行

怎么运行的？ PostgreSQL 被用作“虚拟机”。 内部有一个复杂的算法来构建查询。 这里 源代码。 而且不只是一大堆带有一堆“如果”的启发式方法。 所以，如果你有几个月的时间来学习，你可以尝试理解建筑。

它有效吗？ 相当有效。 不幸的是，这很难证明。 我只能说，如果考虑大型应用程序中存在的数千个查询，那么平均而言它们比优秀开发人员的查询效率更高。 一个优秀的 SQL 程序员可以更有效地编写任何查询，但是如果有一千个查询，他根本没有动力或时间去做这件事。 我现在唯一可以引用作为有效性证明的是，有几个项目正在基于此 DBMS 构建的平台上运行 企业资源计划系统，它具有数千种不同的物化功能，拥有数千个用户和 TB 级数据库，在常规双处理器服务器上运行着数亿条记录。 但是，任何人都可以通过下载来检查/反驳其有效性 一个平台 和 PostgreSQL， 打开 记录 SQL 查询并尝试更改其中的逻辑和数据。

在接下来的文章中，我还将讨论如何设置功能限制、使用更改会话等等。

来源： habr.com

添加评论