平衡資料庫中的寫入和讀取

在上一個 文章 我描述了基於函數而不是關係資料庫中的表格和欄位建立的資料庫的概念和實作。 它提供了許多範例，顯示了這種方法相對於經典方法的優勢。 許多人發現它們不夠令人信服。

在這篇文章中，我將展示這個概念如何讓您在不改變操作邏輯的情況下快速、方便地平衡資料庫的寫入和讀取。 類似的功能已嘗試在現代商業 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）如以下範例所示：

  。 ”）； 建立物化視圖 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 查詢並嘗試變更其中的邏輯和資料。

在接下來的文章中，我還將討論如何設定功能限制、使用變更會話等等。

來源： www.habr.com