分布式SQL數據庫中部分索引的好處

在優銳課的學習分享中，探討了分布式SQL數據庫中部分索引的優勢，并探討了性能測試，結果等。實用干貨非常多，分享給大家參考學習。

如果使用局部索引而不是常規索引，則在可為空的列上（其中只有一小部分行的該列不具有空值），然后可以大大縮短插入，更新和刪除的響應時間。 另外，單行選擇的響應時間也縮短了一點。 這篇文章解釋了什么是部分索引，顯示了如何創建部分索引，描述了要求使用部分索引的規范用例，描述了一些簡單的性能測試，并顯示了結果證明有理由建議使用部分索引。 適當的用例。

**介紹**

有時，業務分析會確定實體類型具有可選屬性，該屬性在典型情況下仍未設置，需要標識將可選屬性設置為某個值的實體出現，并且從不要求 識別未設置的事件。 現在出現了一個典型的例子，一些辦公設施在一個很小的地下室里提供有限的自行車存放空間，并為很少有人利用這一點提供了編號的鑰匙。 當看門人交錯了一把鑰匙時，確實需要找出“誰被分配了42號鑰匙？”，但是不需要識別不使用自行車存放處的人員。

這樣的實體類型將被實現為具有可為空的列以表示可選屬性的表。并且該列將需要輔助索引來支持快速執行查詢，該查詢通過此列的非非空值來標識行。

如果你有一個同時包含“開票”和“未開票”訂單的表，則會出現另一種情況（與可選屬性的情況稍有不同），其中“未開票”的訂單只占總表的一小部分，但通常在OLTP場景中唯一要選擇的對象-正是這樣以便可以對其進行處理并將其設置為“開票”。使用限制定義了部分索引，以便僅對表的某些行進行索引。在當前情況下，在第一種情況下，限制將指定僅對該列具有不為空值的行建立索引。或者，在第二種情況下，將僅索引“未開票”的行。

這將是有益的，因為部分索引比常規索引要小得多。最重要的是，在插入或刪除行時，在典型情況下將避免索引的維護成本。

**“創建索引”語法**

假設我們創建了一個表：

create?table?t(k?int?primary?key,?v?int);

（我將在本文中以尾部分號顯示所有SQL語句，因為它們將以ysqlsh命令形式顯示。）請注意，t.v列是可為空的。

創建常規二級索引：

create?index?t_v?on?t(v);

并創建了部分二級索引：

create?index?t_v?on?t(v)?where?v?is?not?null;

where子句限制可以是任何僅提及要索引表中的列的單行表達式。 例如，``其中v不為null且v！= 0''。 就這么簡單！

性能測試

我用一百萬行填充了表t，其中只有百分之十的行的t.v不為空。 這是用PostgreSQL語法編寫的SQL，其中％運算符用于表示取模函數。

insert?into?t(k,?v)
select
a.v,
case?a.v?%?10
??when?0?then?a.v
??else????????null
end
from
(select?generate_series(1,?1000000)?as?v)?as?a;

測試1：創建索引

首先，我首先在t.v上創建了一個常規索引，對``創建索引''操作進行計時，然后運行計時腳本。 （此腳本實現了測試＃2至＃4。）然后我刪除了索引并在t.v上創建了部分索引，也對該操作進行了計時，并再次運行了計時腳本.

測試2：單鍵選擇

\timing?on
call?select_not_null_rows(100000);
\timing?off

過程select_not_null_rows（）通過運行實現的“ for循環”來模擬OLTP單行選擇語句：

for?j?in?1..num_rows?loop
??select?t.k?into?the_k?from?t?where?t.v?=?the_v;
??the_v?:=?the_v?+?sparseness_step;
end?loop;

當然，沒有人會寫像這樣的真實程序-尤其是因為在沒有唯一約束ont.v的情況下，select語句可能會返回多個行！ 但是這種幼稚的方法足以用于計時目的。 sparseness_step的值設置為10，反映了我的選擇，表示為：

case?a.v?%?10

在上面顯示的插入語句中，我曾用來填充表t。在程序運行時首次遇到該問題時，該程序反復發出的選擇語句是在幕后準備的，因此SQL執行確實遵循最佳的準備- 執行范例

測試3：單行插入

\timing?on
call?insert_rows(100000);
\timing?off

過程insert_rows（）通過運行以下實現的“ for循環”來模擬OLTP單行插入語句：

for?j?in?1..num_rows?loop
??insert?into?t(k,?v)?values(1000000?+?j,?null);
??commit;
end?loop;

沒有人會在實際程序中提交每一行。 但這是模擬單行OLTP的設備。 在這里，SQL執行也遵循最佳的執行準備范式。

測試4：批量刪除

\timing?on
delete?from?t?where?k?>?1000000;
\timing?off

我在這里沒有使用prepare-execute范例，因為SQL編譯和計劃所需的時間只是刪除十萬行所需時間的一小部分。

測試環境

我在兩個環境中記錄了時間。

·?首先，我使用本地MacBook運行單節點YugabyteDB群集（版本2.0.1），復制因子（RF）為1。 ysqlsh客戶端在同一本地計算機上運行。

·?然后，我使用委托在AWS上的，RF = 3的實際三節點集群來運行它們。 我將所有三個節點部署在相同的``us-west-2''（俄勒岡）可用性區域中。 節點的類型為“ c5.large”，每個節點都有足夠的存儲空間來滿足我的測試要求。 我在同一可用區中的另一個節點上運行了ysqlsh客戶端。

結果

我記錄了在兩種測試條件（常規索引和部分索引）和兩種測試環境（“本地”和“云”）下上述測試的經過時間。對于每個測量對，常規索引時間都大于部分索引時間。可以預料，“云”環境中的時間絕對值大于“本地”環境中的時間絕對值。請注意，使用Raft共識算法和分布式事務，在RF = 3的三節點群集上的SQL操作預期比單個節點要慢，RF = 1且沒有節點間通信且代碼路徑較短。如此有彈性和可擴展的系統有很多有據可查的好處，而這些好處可能會超過成本。

我通過將它們表示為速比來標準化結果。例如，如果使用常規索引進行的測試花費60秒，而使用部分索引進行的測試花費20秒，則部分索引速度是常規索引速度的3倍。在我的測量精度范圍內，兩個測試環境中的速度比是相同的。

列出測試的部分索引與常規索引速度比

?

當然，你的結果可能會有所不同。 索引列中的非零值稀疏密度將使速度比更大，而稀疏密度較小將使它們變得更小。

結論

這篇文章展示了如何在適當的用例中使用部分索引，可以減少二級索引維護的成本，從而加快有關表的插入，更新和刪除操作。 通過使用部分索引來減少二級索引維護操作的數量在分布式SQL數據庫（例如YugabyteDB）中特別有利。 這是因為在索引維護期間，看似僅插入，更新或刪除單個行的事務會自動成為涉及主行和輔助索引行的分布式事務。

當主行和輔助索引行存在于群集的潛在兩個不同節點上的兩個不同分片上時，這種分布式事務可以是多分片事務。 可以想象，多分片事務必然比單分片事務昂貴。

文章寫到這里，如有不足之處，歡迎補充評論！

?