Geekly articles weekly

数据库读写平衡

图片

在上一篇文章中，我描述了基于功能而不是关系数据库中的表和字段构建的数据库的概念和实现。 它提供了许多示例，显示了此方法相对于经典方法的优势。 许多人发现他们没有足够的说服力。

在本文中，我将展示该概念如何使您能够快速方便地平衡对数据库的读写，而无需改变工作逻辑。 他们试图在现代商业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用作函数countEmployees和SalarySum ，则在带有部门列表的表中，将添加两个字段，其中将存储雇员人数及其总薪水。 如果员工，他们的工资或与部门有附属关系发生任何变化，系统将自动更改这些字段的值。 上面的查询将开始直接访问这些字段，并将对O（部门数）执行 。

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

一个例子：
employeesCount ' > N' (Department d, NUMERIC [ 10 , 2 ] N) =
GROUP SUM salary(Employee e) IF department(e) = d AND salary(e) > N;
此函数是为N个数字的无限个值定义的（例如，任何负值都适用）。 因此，不能将其材料化。 因此，这是逻辑上的限制，而不是技术上的限制（也就是说，不是因为我们无法实现此限制）。 否则，没有任何限制。 您可以使用分组，排序，与与或，分区，递归等。

例如，在上一篇文章的任务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 ;
系统本身将创建一个具有类型为Customer ， Product和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 ;
由于为isParent函数附加了MATERIALIZED，因此将为其创建带有两个键（组）的表，其中只有第一个键是第二个键的后代时， isParent字段才为true。 该表中的条目数将等于组数乘以树的平均深度。 例如，如果有必要计算特定组的后代数量，则可以访问此功能：
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中提供HINT）。 无论他们如何努力，如果不更改请求和添加触发器，他们将无法满足文章中对O（部门数）的第一个请求。 在拟议的方案中，在开发阶段，您不必考虑数据存储的结构以及要使用的聚合。 所有这些都可以直接在运行中轻松更改。

实际上，如下。 有些人直接根据任务开发逻辑。 他们不精通算法及其复杂性，也不精通执行计划，不精通join'ov的类型，也不精通任何其他技术组件。 这些人比开发人员更多的是业务分析师。 然后，它们全部进入测试或运行状态。 启用长查询日志记录。 当检测到长请求时，其他人（实际上是DBA）则决定将MATERIALIZED包含在某些中间功能上。 因此，记录会稍慢一些（因为需要更新事务中的其他字段）。 但是，不仅此请求显着加速，而且所有其他使用此功能的请求也得到了加速。 同时，决定要实现的特定功能相对简单。 两个主要参数：可能的输入值的数量（恰好在对应表中有多少条记录），以及在其他函数中使用该记录的频率。

类似物


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

首先，仅当您已经创建了常规VIEW时，才能启用实现。 否则，您将必须重写其余访问新创建视图的请求，才能使用此实现。 或保留所有内容，但是如果已经计算出某些数据，它将至少无效，但是许多查询并不总是使用它们，而是重新计算。

其次，它们有很多限制：

甲骨文
5.3.8.4快速刷新的一般限制

物化视图的定义查询受到如下限制：
  • 实例化视图不得包含对非重复表达式（如SYSDATE和ROWNUM引用。
  • 实例化视图不得包含对RAW或LONG RAW数据类型的引用。
  • 它不能包含SELECT列表子查询。
  • 它不能在SELECT子句中包含分析函数（例如RANK ）。
  • 它不能引用在其上定义了XMLIndex索引的表。
  • 它不能包含MODEL子句。
  • 它不能包含带有子查询的HAVING子句。
  • 它不能包含具有ANY ， ALL或NOT EXISTS嵌套查询。
  • 它不能包含[START WITH …] CONNECT BY子句。
  • 它不能在不同站点包含多个明细表。
  • COMMIT化视图不能具有远程明细表。
  • 嵌套的物化视图必须具有联接或聚集。
  • 带有GROUP BY子句的GROUP BY视图和实例化聚合视图不能从索引组织的表中选择。

5.3.8.5仅具有联接的物化视图快速刷新的限制

定义仅具有联接且没有聚合的物化视图查询对快速刷新具有以下限制：
  • “ 快速刷新的一般限制 ”中的所有限制。
  • 它们不能具有GROUP BY子句或聚合。
  • FROM列表中所有表的Rowid必须出现在查询的SELECT列表中。
  • 在查询的FROM列表中，物化视图日志必须存在所有基表的行FROM 。
  • 您不能从具有简单连接的多个表创建快速刷新的物化视图，这些简单连接在SELECT语句中包含对象类型列。

同样，在以下情况下，您选择的刷新方法也不会达到最佳效率：
  • 定义查询使用行为类似于内部联接的外部联接。 如果定义查询包含此类联接，请考虑重写定义查询以包含内部联接。
  • SELECT化视图的SELECT列表包含来自多个表的列上的表达式。

5.3.8.6带有集合的物化视图快速刷新的限制

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

ON COMMIT和ON DEMAND化视图都支持快速刷新，但是存在以下限制：
  • 实例化视图中的所有表都必须具有实例化视图日志，并且实例化视图日志必须：
    • 包含实例化视图中引用的表中的所有列。
    • 使用ROWID指定并包含NEW VALUES 。
    • 如果期望该表包含插入/直接加载，删除和更新的混合，则指定SEQUENCE子句。

  • 仅支持SUM ， COUNT ， AVG ， VARIANCE ， VARIANCE ， MIN和MAX进行快速刷新。
  • 必须指定COUNT(*) 。
  • 聚合函数只能出现在表达式的最外层。 即，不允许使用AVG(AVG(x))或AVG(x) + AVG(x)类的AVG(x) 。
  • 对于每个聚合，例如AVG(expr) ，必须存在相应的COUNT(expr) 。 Oracle建议指定SUM(expr) 。
  • 如果指定了VARIANCE(expr)或STDDEV(expr ），则必须指定COUNT(expr)和SUM(expr) 。 Oracle建议指定SUM(expr *expr) 。
  • 定义查询中的SELECT列不能是包含多个基本表中的列的复杂表达式。 一个可能的解决方法是使用嵌套的物化视图。
  • SELECT列表必须包含所有GROUP BY列。
  • 物化视图不是基于一个或多个远程表。
  • 如果在CHAR化视图日志的过滤器列中使用CHAR数据类型，则主站点和实例化视图的字符集必须相同。
  • 如果实例化视图具有以下内容之一，则仅在常规DML插入和直接载荷上支持快速刷新。
    • 具有MIN或MAX聚合的MIN视图
    • 具有SUM(expr)但没有COUNT(expr) SUM(expr)视图
    • 没有COUNT(*)视图

    这种实例化视图称为仅插入的实例化视图。
  • 如果删除或混合DML语句不具有WHERE子句，则具有MAX或MIN的实例化视图可在其后快速刷新。
    删除或混合DML后的max / min快速刷新与仅插入情况下的行为不同。 它删除并重新计算受影响组的最大值/最小值。 您需要了解其性能影响。
  • 只要可以完全合并视图，可以快速刷新在FROM子句中具有命名视图或子查询的FROM视图。 有关将合并哪些视图的信息，请参见《 Oracle数据库SQL语言参考》 。
  • 如果没有外部联接，则可以在WHERE子句中进行任意选择和联接。
  • 如果仅修改了外部表，则在常规DML和直接加载之后，具有外部联接的实例化聚合视图可以快速刷新。 此外，内部联接表的联接列上必须存在唯一约束。 如果存在外部联接，则所有联接必须通过AND进行连接AND并且必须使用等于（ = ）运算符。
  • 对于具有CUBE ， ROLLUP ，分组集或它们的串联的ROLLUP化视图，适用以下限制：
    • SELECT列表应包含分组区分GROUPING_ID ，它可以是所有GROUP BY表达式上的GROUPING_ID函数，也可以是每个GROUP BY表达式上的GROUPING函数。 例如，如果实例化视图的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视图或子查询），如下所示：例如：
     创建视图view_with_unionall AS
 （选择c.rowid crid，c.cust_id，2个umarker
 来自客户c在哪里c.cust_last_name ='Smith'
 全联盟
  SELECT c.rowid crid，c.cust_id，3个umarker
 来自客户c的位置c.cust_last_name ='Jones'）;

创建材料视图unionall_inside_view_mv
按需快速刷新
 SELECT * FROM view_with_unionall;
    请注意，视图view_with_unionall满足快速刷新的要求。
  • UNION ALL查询中的每个查询块必须满足具有聚合的可快速刷新的实例化视图或具有联接的可快速刷新的实例化视图的要求。

    必须根据相应类型的快速可刷新实例化视图的要求，在表上创建适当的实例化视图日志。
    请注意，只有在SELECT列表和实例化视图日志中包含ROWID列的情况下，Oracle数据库还允许具有连接的单个表实例化视图的特殊情况。 这在视图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爱好者，但是从他们的限制列表来看，似乎这种机制不是在一般情况下使用某种模型编写的，而是数千名印度人编写的，每个人都可以编写自己的线程，并且每个人都可以并且做到了。 使用这种机制实现真正的逻辑就像在雷区中漫步一样。 在任何时候，您都可以找到一个地雷，击中不明显的限制之一。 这是如何工作的也是一个单独的问题，但这不在本文的讨论范围之内。

Microsoft SQL服务器

附加要求


除了SET选项和确定性功能要求外，还必须满足以下要求：
  • 执行CREATE INDEX的用户必须是视图的所有者。
  • 创建索引时，必须将IGNORE_DUP_KEY选项设置为OFF（默认设置）。
  • 表必须由两部分名称schema引用。 视图定义中的表名 。
  • 必须使用WITH SCHEMABINDING选项创建视图中引用的用户定义函数。
  • 视图中引用的任何用户定义函数都必须由两部分名称<schema>引用。 <功能> 。
  • 用户定义函数的数据访问属性必须为NO SQL ，外部访问属性必须为NO 。
  • 公共语言运行时（CLR）函数可以出现在视图的选择列表中，但不能成为聚集索引键的定义的一部分。 CLR函数不能出现在视图的WHERE子句或视图中的JOIN操作的ON子句中。
  • 视图定义中使用的CLR用户定义类型的CLR函数和方法必须具有如下表所示的属性集。
    物业资料注意事项
    确定性= TRUE必须明确声明为Microsoft .NET Framework方法的属性。
    精确=真必须明确声明为.NET Framework方法的属性。
    数据访问=无SQL通过将DataAccess属性设置为DataAccessKind.None和将SystemDataAccess属性设置为SystemDataAccessKind.None来确定。
    外部访问=否对于CLR例程，此属性默认为NO。
  • 必须使用WITH SCHEMABINDING选项创建WITH SCHEMABINDING 。
  • 该视图必须仅引用与该视图位于同一数据库中的基表。 该视图无法引用其他视图。
  • 视图定义中的SELECT语句不得包含以下Transact-SQL元素：
    COUNTROWSET函数（ OPENDATASOURCE ， OPENQUERY ， OPENROWSET和OPENXML ）OUTER联接（ LEFT ， RIGHT或FULL ）
    派生表（通过在FROM子句中指定SELECT语句定义）自我加入通过使用SELECT *或SELECT <table_name>.*指定列SELECT <table_name>.*
    DISTINCTSTDEV ， STDEVP ， VAR ， VARP或AVG公用表表达式（CTE）
    float 1 ， text ， ntext ， image ， XML或filestream列子查询OVER子句，其中包括排名或汇总窗口函数
    全文谓词（ CONTAINS ， FREETEXT ）引用可为空的表达式的SUM函数ORDER BY
    CLR用户定义的聚合函数TOPCUBE ， ROLLUP或GROUPING SETS运算符
    MIN ， MAXUNION ， EXCEPT或INTERSECT运算符TABLESAMPLE
    表变量OUTER APPLY或CROSS APPLYUNPIVOT
    稀疏列集内联（TVF）或多语句表值函数（MSTVF）OFFSET
    CHECKSUM_AGG

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

在这里，您可以看到印第安人并没有受到吸引，因为他们决定按照该计划这样做：“我们只会做点小事，但是会做得很好。” 也就是说，他们在野外有更多的地雷，但是它们的位置更加透明。 最令人困扰的是这个限制：
该视图必须仅引用与该视图位于同一数据库中的基表。 该视图无法引用其他视图。

用我们的术语来说，这意味着一个函数不能访问另一个物化函数。 它削减了整个意识形态。
同样，此限制（以及本文中的进一步限制）大大减少了用例：
视图定义中的SELECT语句不得包含以下Transact-SQL元素：
COUNTROWSET函数（ OPENDATASOURCE ， OPENQUERY ， OPENROWSET和OPENXML ）OUTER联接（ LEFT ， RIGHT或FULL ）
派生表（通过在FROM子句中指定SELECT语句定义）自我加入通过使用SELECT *或SELECT <table_name>.*指定列SELECT <table_name>.*
DISTINCTSTDEV ， STDEVP ， VAR ， VARP或AVG公用表表达式（CTE）
float 1 ， text ， ntext ， image ， XML或filestream列子查询OVER子句，其中包括排名或汇总窗口函数
全文谓词（ CONTAINS ， FREETEXT ）引用可为空的表达式的SUM函数ORDER BY
CLR用户定义的聚合函数TOPCUBE ， ROLLUP或GROUPING SETS运算符
MIN ， MAXUNION ， EXCEPT或INTERSECT运算符TABLESAMPLE
表变量OUTER APPLY或CROSS APPLYUNPIVOT
稀疏列集内联（TVF）或多语句表值函数（MSTVF）OFFSET
CHECKSUM_AGG

禁止使用外部联接，UNION，ORDER BY等。 也许指出可以使用的总比未使用的要容易。 该列表可能会小得多。

总结一下：在LGPL技术中，每个DBMS（我注意到商业版）中都有大量的限制，而没有限制（只有一个逻辑而不是技术上的限制）。 但是，应该注意的是，在关系逻辑中实现此机制比在所描述的功能中要复杂得多。

实作


如何运作？ PostgreSQL被用作“虚拟机”。 里面有一个构建查询的复杂算法。 这是源代码 。 而且，不仅存在大量带有ifs的启发式方法。 因此，如果您有几个月的学习时间，则可以尝试了解该体系结构。

它有效吗？ 足够有效。 不幸的是，很难证明这一点。 我只能说，如果考虑大型应用程序中的数千个请求，那么平均而言，它们比一个好的开发人员更有效。 优秀的SQL程序员可以更有效地编写任何查询，但是只要执行一千个查询，他就根本没有动力也没有时间去做。 现在，我唯一能证明其有效性的是，在基于此DBMS的平台的基础上，开展了多个ERP系统项目，其中有成千上万种不同的MATERIALIZED功能，具有成千上万个用户和terrabyte数据库，并具有数亿条记录在常规双处理器服务器上。 但是，任何人都可以通过下载平台和PostgreSQL， 启用 SQL查询日志记录并尝试更改其中的逻辑和数据来测试/反驳有效性。

在以下文章中，我还将讨论如何在函数上挂起限制，如何使用更改会话等等。

Source: https://habr.com/ru/post/zh-CN459066/

More articles:


All Articles