分布式SQL數(shù)據(jù)庫中部分索引的好處

在優(yōu)銳課的學(xué)習(xí)分享中，探討了分布式SQL數(shù)據(jù)庫中部分索引的優(yōu)勢，并探討了性能測試，結(jié)果等。實(shí)用干貨非常多，分享給大家參考學(xué)習(xí)。

如果使用局部索引而不是常規(guī)索引，則在可為空的列上（其中只有一小部分行的該列不具有空值），然后可以大大縮短插入，更新和刪除的響應(yīng)時(shí)間。 另外，單行選擇的響應(yīng)時(shí)間也縮短了一點(diǎn)。 這篇文章解釋了什么是部分索引，顯示了如何創(chuàng)建部分索引，描述了要求使用部分索引的規(guī)范用例，描述了一些簡單的性能測試，并顯示了結(jié)果證明有理由建議使用部分索引。 適當(dāng)?shù)挠美?/p>

**介紹**

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

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

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

這將是有益的，因?yàn)椴糠炙饕瘸Ｒ?guī)索引要小得多。最重要的是，在插入或刪除行時(shí)，在典型情況下將避免索引的維護(hù)成本。

**“創(chuàng)建索引”語法**

假設(shè)我們創(chuàng)建了一個(gè)表：

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

（我將在本文中以尾部分號顯示所有SQL語句，因?yàn)樗鼈儗⒁詙sqlsh命令形式顯示。）請注意，t.v列是可為空的。

創(chuàng)建常規(guī)二級索引：

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

并創(chuàng)建了部分二級索引：

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

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

性能測試

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

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：創(chuàng)建索引

首先，我首先在t.v上創(chuàng)建了一個(gè)常規(guī)索引，對``創(chuàng)建索引''操作進(jìn)行計(jì)時(shí)，然后運(yùn)行計(jì)時(shí)腳本。 （此腳本實(shí)現(xiàn)了測試＃2至＃4。）然后我刪除了索引并在t.v上創(chuàng)建了部分索引，也對該操作進(jìn)行了計(jì)時(shí)，并再次運(yùn)行了計(jì)時(shí)腳本.

測試2：單鍵選擇

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

過程select_not_null_rows（）通過運(yùn)行實(shí)現(xiàn)的“ for循環(huán)”來模擬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;

當(dāng)然，沒有人會(huì)寫像這樣的真實(shí)程序-尤其是因?yàn)樵跊]有唯一約束ont.v的情況下，select語句可能會(huì)返回多個(gè)行！ 但是這種幼稚的方法足以用于計(jì)時(shí)目的。 sparseness_step的值設(shè)置為10，反映了我的選擇，表示為：

case?a.v?%?10

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

測試3：單行插入

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

過程insert_rows（）通過運(yùn)行以下實(shí)現(xiàn)的“ for循環(huán)”來模擬OLTP單行插入語句：

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

沒有人會(huì)在實(shí)際程序中提交每一行。 但這是模擬單行OLTP的設(shè)備。 在這里，SQL執(zhí)行也遵循最佳的執(zhí)行準(zhǔn)備范式。

測試4：批量刪除

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

我在這里沒有使用prepare-execute范例，因?yàn)镾QL編譯和計(jì)劃所需的時(shí)間只是刪除十萬行所需時(shí)間的一小部分。

測試環(huán)境

我在兩個(gè)環(huán)境中記錄了時(shí)間。

·?首先，我使用本地MacBook運(yùn)行單節(jié)點(diǎn)YugabyteDB群集（版本2.0.1），復(fù)制因子（RF）為1。 ysqlsh客戶端在同一本地計(jì)算機(jī)上運(yùn)行。

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

結(jié)果

我記錄了在兩種測試條件（常規(guī)索引和部分索引）和兩種測試環(huán)境（“本地”和“云”）下上述測試的經(jīng)過時(shí)間。對于每個(gè)測量對，常規(guī)索引時(shí)間都大于部分索引時(shí)間?？梢灶A(yù)料，“云”環(huán)境中的時(shí)間絕對值大于“本地”環(huán)境中的時(shí)間絕對值。請注意，使用Raft共識算法和分布式事務(wù)，在RF = 3的三節(jié)點(diǎn)群集上的SQL操作預(yù)期比單個(gè)節(jié)點(diǎn)要慢，RF = 1且沒有節(jié)點(diǎn)間通信且代碼路徑較短。如此有彈性和可擴(kuò)展的系統(tǒng)有很多有據(jù)可查的好處，而這些好處可能會(huì)超過成本。

我通過將它們表示為速比來標(biāo)準(zhǔn)化結(jié)果。例如，如果使用常規(guī)索引進(jìn)行的測試花費(fèi)60秒，而使用部分索引進(jìn)行的測試花費(fèi)20秒，則部分索引速度是常規(guī)索引速度的3倍。在我的測量精度范圍內(nèi)，兩個(gè)測試環(huán)境中的速度比是相同的。

列出測試的部分索引與常規(guī)索引速度比

?

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

結(jié)論

這篇文章展示了如何在適當(dāng)?shù)挠美惺褂貌糠炙饕梢詼p少二級索引維護(hù)的成本，從而加快有關(guān)表的插入，更新和刪除操作。 通過使用部分索引來減少二級索引維護(hù)操作的數(shù)量在分布式SQL數(shù)據(jù)庫（例如YugabyteDB）中特別有利。 這是因?yàn)樵谒饕S護(hù)期間，看似僅插入，更新或刪除單個(gè)行的事務(wù)會(huì)自動(dòng)成為涉及主行和輔助索引行的分布式事務(wù)。

當(dāng)主行和輔助索引行存在于群集的潛在兩個(gè)不同節(jié)點(diǎn)上的兩個(gè)不同分片上時(shí)，這種分布式事務(wù)可以是多分片事務(wù)。 可以想象，多分片事務(wù)必然比單分片事務(wù)昂貴。

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

?