本文介紹索引創(chuàng)建時需遵循一些準則，以達到最佳的查詢性能和數(shù)據(jù)維護效率，這些準則也是PawSQL索引推薦引擎的內(nèi)部工作邏輯。本文基于最常用的B+樹索引來舉例，其他索引的使用有一定的限制條件，具體請參考??《數(shù)據(jù)庫索引的類型》??。
準則1：基于您的工作負載創(chuàng)建索引
創(chuàng)建高效的索引最重要的原則是，基于您的工作負載（workload）創(chuàng)建索引，而不是基于您的表結(jié)構(gòu)。針對數(shù)據(jù)庫執(zhí)行的所有 SQL 語句構(gòu)成了該數(shù)據(jù)庫的工作負載。索引的目的是為了提升數(shù)據(jù)庫中操作的效率，因此任何其他不以工作負載出發(fā)的索引創(chuàng)建方法都是錯誤的。
在針對一個工作負載構(gòu)建一組索引時，需要考慮工作負載的以下屬性：
- SQL類型，在用戶執(zhí)行頻繁插入新數(shù)據(jù)和修改現(xiàn)有數(shù)據(jù)的OLTP場景，多個索引可能會對性能產(chǎn)生負面影響，并對系統(tǒng)資源造成壓力，建議創(chuàng)建最少數(shù)量的索引來滿足您的索引要求。而在以查詢?yōu)橹鞯腛LAP場景，您可以添加更多索引，每個索引具有多個鍵列，甚至可以添加函數(shù)索引和條件索引。
- SQL頻率，為最頻繁使用的查詢創(chuàng)建索引，通過為這些查詢創(chuàng)建最好的索引，能夠最大限度的提升系統(tǒng)的整體性能。
- SQL的重要性，查詢越重要，您可能越想通過創(chuàng)建索引來進行性能優(yōu)化。
- SQL的本身的結(jié)構(gòu)，詳見下一章節(jié)。
準則2：基于單個SQL的結(jié)構(gòu)創(chuàng)建索引
索引的作用如下：
- 快速定位數(shù)據(jù)
- 避免排序
- 避免回表
- 實現(xiàn)行級鎖(MySQL，另文討論)
- 實現(xiàn)唯一性約束
本章節(jié)我們將通過分析SQL的結(jié)構(gòu)來實現(xiàn)以上的功能，創(chuàng)建高效的索引。
為快速定位創(chuàng)建索引
索引可以通過匹配查詢的條件快速的定位數(shù)據(jù)，查詢的條件可能在WHERE子句、HAVING子句、ON子句中。索引和條件的匹配原則遵循最左前綴匹配原則.
最左前綴原則
最左前綴原則指的是，如果查詢的時候等值的查詢條件精確匹配索引的左邊連續(xù)一列或幾列，則此列就可以被用到，同時遇到范圍查詢(>、<、between、like)就會停止匹配，包括范圍條件。
對于聯(lián)合索引lineitem(l_shipdate,l_quantity),下面的SQL中前兩個符合最左前綴原則，可以使用該索引。最后一個不滿足最左前綴原則，無法使用該索引。
select * from lineitem where l_shipdate = date '2021-12-01' and l_quantity = 100; -- 可以使用索引
select * from lineitem where l_shipdate = date '2021-12-01'; -- 可以使用索引
select * from lineitem where l_quantity = 100; -- 不滿足最左前綴原則，無法使用該索引
這三個SQL對于的執(zhí)行計劃如下：
-> Index lookup on lineitem using lidx (L_QUANTITY=100.00, L_SHIPDATE=DATE'2021-12-01') (cost=0.35 rows=1)
-> Index lookup on lineitem using lidx ( L_SHIPDATE=DATE'2021-12-01') (cost=0.35 rows=1)
-> Filter: (lineitem.L_QUANTITY = 100.00) (cost=15208.05 rows=49486)
-> Table scan on lineitem (cost=15208.05 rows=148473)
由于最左前綴原則，在創(chuàng)建聯(lián)合索引時，索引字段的順序需要考慮字段值去重之后的個數(shù)(Cardinality)，Cardinality較大的放前面。
等值條件（Index lookup）
- 關(guān)聯(lián)等值條件, 在一個表作為被驅(qū)動表時的等值關(guān)聯(lián)條件也可以被認為時等值條件被索引匹配使用。
- T1.COL = T2.COL
select * from orders, lineitem where o_orderkey = l_orderkey;
-> Nested loop inner join (cost=484815.77 rows=1326500)
-> Table scan on orders (cost=20540.71 rows=200128)
-> Index lookup on lineitem using lineitem_idx(L_ORDERKEY=orders.O_ORDERKEY) (cost=1.66 rows=7)
范圍條件(Index range scan)
- 范圍操作符（>,>=,<,<=,BETWEEN）
- IN ('A','B')
- IS NOT NULL
- IS NULL
- LIKE 'ABC%'
- COL = 'A' OR COL = 'B'
范圍條件也可以用來快速定位數(shù)據(jù)。
create index lshipdate_idx on lineitem(l_shipdate);
explain format = tree select * from lineitem where l_shipdate >= date '2021-12-01';
-> Index range scan on lineitem using lshipdate_idx over ('2021-12-01' <= L_SHIPDATE), with index condition: (lineitem.L_SHIPDATE >= DATE'2021-12-01') (cost=11855.06 rows=26344)由于最左匹配原則，位于范圍條件后面的索引列無法利用該索引。
為避免排序創(chuàng)建索引
對于B+樹索引，由于其是按照索引鍵排序的，因此可以通過索引來避免在SQL執(zhí)行中進行排序。涉及的SQL結(jié)構(gòu)主要包括：
- GROUP BY
- ORDER BY
- DISTINCT
- PARTITION BY... ORDER BY...
create index lshipdate_idx on lineitem(l_shipdate);
可以看到下面的SQL的執(zhí)行計劃通過訪問lshipdate_idx索引避免了排序。
select * from lineitem order by l_shipdate limit 10;
-> Limit: 10 row(s) (cost=0.02 rows=10)
-> Index scan on lineitem using lshipdate_idx (cost=0.02 rows=10)
select l_shipdate, sum(l_quantity) as sum_qty from lineitem group by l_shipdate;
-> Group aggregate: sum(lineitem.L_QUANTITY) (cost=30055.35 rows=148473)
-> Index scan on lineitem using lshipdate_idx (cost=15208.05 rows=148473)
select DISTINCT l_shipdate from lineitem;
-> Covering index skip scan for deduplication on lineitem using lshipdate_idx (cost=4954.90 rows=15973)
- SQL4(PARTITION BY... ORDER BY...)
select rank() over (partition by L_SHIPDATE order by L_ORDERKEY) from lineitem;
WindowAgg (cost=0.29..545.28 rows=10000 width=28)
-> Index Only Scan using lshipdate_idx on lineitem (cost=0.29..370.28 rows=10000 width=20)
需要注意：
- 對于分組和去重，順序不一致是沒有關(guān)系的。
- 對于排序，排序字段的順序需要和索引字段的順序一致，否則沒有辦法利用索引來避免排序。
- 對于同時有分組和排序的，需要把排序的索引列放在前面。
譬如對于下面的SQL。
select l_shipdate, l_orderkey, sum(l_quantity) as sum_qty from lineitem group by l_shipdate,l_orderkey order by l_orderkey;
- 情形1，建索引(l_shipdate, l_orderkey)，索引訪問，需排序，代價為486.526。
-> Sort: lineitem.L_ORDERKEY (actual time=479.465..486.526 rows=149413 loops=1)
-> Stream results (cost=30055.35 rows=148473) (actual time=0.175..423.447 rows=149413 loops=1)
-> Group aggregate: sum(lineitem.L_QUANTITY) (cost=30055.35 rows=148473) (actual time=0.170..394.978 rows=149413 loops=1)
-> Index scan on lineitem using lshipdate_idx2 (cost=15208.05 rows=148473) (actual time=0.145..359.567 rows=149814 loops=1)
- 情形2，建索引(l_orderkey,l_shipdate)，索引訪問，避免排序，代價228.401，性能提升120%。
-> Group aggregate: sum(lineitem.L_QUANTITY) (cost=30055.35 rows=148473) (actual time=0.067..228.401 rows=149413 loops=1)
-> Index scan on lineitem using lshipdate_idx3 (cost=15208.05 rows=148473) (actual time=0.052..194.479 rows=149814 loops=1)
為避免回表創(chuàng)建索引（Covering index scan）
當(dāng)查詢中的列都在索引列中時，數(shù)據(jù)庫只需要訪問索引即可獲取所需的數(shù)據(jù)，避免了回表操作。在某些場景下，可以大幅的提升查詢效率。
對于如下的SQL語句。
select l_shipdate, l_orderkey, sum(l_quantity) as sum_qty from lineitem group by l_orderkey,l_shipdate;
- 索引（l_orderkey,l_shipdate）中沒有包含?
?l_quantity??，需要回表，執(zhí)行計劃如下，代價194.875。
-> Group aggregate: sum(lineitem.L_QUANTITY) (cost=30055.35 rows=148473) (actual time=0.044..194.875 rows=149413 loops=1)
-> Index scan on lineitem using lshipdate_idx3 (cost=15208.05 rows=148473) (actual time=0.034..159.863 rows=149814 loops=1)
- 索引（l_orderkey,l_shipdate，l_quantity ）中包含l_quantity，不需要回表，執(zhí)行計劃如下，代價113.433，性能提升約71.8%。
-> Group aggregate: sum(lineitem.L_QUANTITY) (cost=30055.35 rows=148473) (actual time=0.035..113.433 rows=149413 loops=1)
-> Covering index scan on lineitem using lshipdate_idx4 (cost=15208.05 rows=148473) (actual time=0.026..82.266 rows=149814 loops=1)
其他相關(guān)主題
分區(qū)表的索引
對于分區(qū)表，不同的數(shù)據(jù)庫對分區(qū)表索引的支持不一樣，總體來說，分區(qū)表可以建立以下三種類型的索引。
- 本地分區(qū)索引（PostgreSQL/MySQL/Oracle/Opengauss）。
- 全局分區(qū)索引（Oracle）。
- 全局非分區(qū)索引（Oracle/Opengauss）。
本地分區(qū)索引
在索引維護性方面，本地索引比全局索引容易管理，當(dāng)你在進行添加、刪除、truncate表分區(qū)時，本地索引會自動維護其索引分區(qū)。MySQL和PostgreSQL只支持本地分區(qū)索引; Oracle和Opengauss創(chuàng)建本地分區(qū)索引時需指定關(guān)鍵字local。
create index lshipdate_idx on lineitem(l_shipdate) local;
全局分區(qū)索引
和表分區(qū)類似，索引的分區(qū)鍵和表的分區(qū)鍵沒有必然的關(guān)系，甚至非分區(qū)表也可以建立全局分區(qū)索引。Oracle支持全局分區(qū)索引。
全局非分區(qū)索引
對于全局非分區(qū)索引，當(dāng)你對表分區(qū)進行操作時，索引可能變得不可用，需顯式的更新或重建索引。在索引效率方面，全局索引在不包含分區(qū)字段的查詢中，效率比本地分區(qū)索引更高效。Oracle和Opengauss為分區(qū)表默認創(chuàng)建的是全局非分區(qū)索引。
create index lshipdate_idx on lineitem(l_shipdate) global;
create index lshipdate_idx on lineitem(l_shipdate);
在進行分區(qū)操作時，需要增加update global index關(guān)鍵字重建索引，否則索引不可用。
alter table t DROP PARTITION partition_name update global index;
函數(shù)索引
函數(shù)索引（或表達式索引）即基于函數(shù)或表達式的索引，它使用函數(shù)或是表達式提供計算好的值作為索引列構(gòu)建索引，可以在不修改應(yīng)用程序的情況下提高查詢性能。
函數(shù)索引的使用需要函數(shù)或是表達式和SQL查詢中的表達式嚴格匹配，所以它使用的條件較為嚴格，適合針對重要查詢或是頻次較高的查詢重點優(yōu)化。
select * from lineitem where EXTRACT(DAY from l_shipdate) = 1;
- 建在l_shipdate在執(zhí)行計劃中沒有被使用。
Seq Scan on lineitem (cost=0.00..1870.24 rows=238 width=158) (actual time=0.502..10.655 rows=1616 loops=1)
Filter: (EXTRACT(day FROM l_commitdate) = '1'::numeric)
Rows Removed by Filter: 46000
Planning Time: 0.107 ms
Execution Time: 10.709 ms
- 通過創(chuàng)建函數(shù)索引，執(zhí)行計劃中的代價降低為原來的1/10。
create index idx on lineitem(EXTRACT(DAY from l_shipdate));
Bitmap Heap Scan on lineitem (cost=6.13..593.60 rows=238 width=158) (actual time=0.216..0.981 rows=1620 loops=1)
Recheck Cond: (EXTRACT(day FROM l_shipdate) = '1'::numeric)
Heap Blocks: exact=889
-> Bitmap Index Scan on idx (cost=0.00..6.08 rows=238 width=0) (actual time=0.149..0.149 rows=1620 loops=1)
Index Cond: (EXTRACT(day FROM l_shipdate) = '1'::numeric)
Planning Time: 0.102 ms
Execution Time: 1.075 ms
條件索引
條件索引又叫部分索引(Partial index)，它是建立在一個表的子集上的索引，而該子集是由一個條件表達式定義的，該索引只包含表中那些滿足這個條件表達式的行。
條件索引被使用的條件比較嚴格，只有在數(shù)據(jù)庫能夠識別出該查詢的WHERE條件在邏輯上涵蓋了該索引的條件表達式定義時，這個部分索引才能被用于該查詢。
以下的條件索引為例，其索引的條件表達式為l_shipdate > '2022-01-01'。
create index l_partkey_idx on lineitem(l_partkey) where l_shipdate > '2022-01-01';
由于下面的查詢語句的條件l_shipdate = date '2021-12-01'沒有落到此索引條件表達式的范圍內(nèi)，該索引將不會被用到，所以執(zhí)行計劃采用的是全表掃描。
select l_partkey , count(1) from lineitem where l_shipdate = date '2021-12-01' and l_partkey < 100 group by l_partkey ;
GroupAggregate (cost=1870.25..1870.27 rows=1 width=12)
Group Key: l_partkey
-> Sort (cost=1870.25..1870.26 rows=1 width=4)
Sort Key: l_partkey
-> Seq Scan on lineitem (cost=0.00..1870.24 rows=1 width=4)
Filter: ((l_partkey < 100) AND (l_shipdate = '2021-12-01'::date))
而下面的查詢語句的條件l_shipdate = date '2022-12-01'在條件表達式的范圍內(nèi)，數(shù)據(jù)庫優(yōu)化器將會采用此索引，可以看到性能有大幅提升。
select l_partkey , count(1) from lineitem where l_shipdate = date '2022-12-01' and l_partkey < 100 group by l_partkey ;
GroupAggregate (cost=402.37..402.39 rows=1 width=12)
Group Key: l_partkey
-> Sort (cost=402.37..402.38 rows=1 width=4)
Sort Key: l_partkey
-> Index Scan using lorderkey_idx on lineitem (cost=0.28..402.36 rows=1 width=4)
Filter: ((l_partkey < 100) AND (l_shipdate = '2022-12-01'::date))
特別提示：MySQL目前還不支持條件索引，而PostgreSQL、Opengauss、Oracle都支持。
索引融合
索引融合（Index Merge）是使用多個索引來完成一次單表數(shù)據(jù)訪問的優(yōu)化技術(shù)。當(dāng)查詢中涉及一個表的多個條件時，如果這些條件分別有合適的索引，索引融合可以在回表之前將多個索引的結(jié)果合并，以提高查詢性能。
在lineitem表上有在l_shipdate以及l(fā)_partkey的單列索引，對于以下的SQL。
select * from lineitem where l_shipdate = date '2010-12-01' or l_partkey=100;
PostgreSQL的執(zhí)行計劃。
Bitmap Heap Scan on lineitem (cost=9.05..202.96 rows=59 width=158)
Recheck Cond: ((l_shipdate = '2010-12-01'::date) OR (l_partkey = 100))
-> BitmapOr (cost=9.05..9.05 rows=59 width=0)
-> Bitmap Index Scan on l_shipdate_idx (cost=0.00..4.70 rows=54 width=0)
Index Cond: (l_shipdate = '2010-12-01'::date)
-> Bitmap Index Scan on l_partkey_idx (cost=0.00..4.33 rows=5 width=0)
Index Cond: (l_partkey = 100)
MySQL的執(zhí)行計劃。
-> Filter: ((lineitem.L_SHIPDATE = DATE'2010-12-01') or (lineitem.L_PARTKEY = 100)) (cost=12.53 rows=21)
-> Deduplicate rows sorted by row ID (cost=12.53 rows=21)
-> Index range scan on lineitem using l_shipdate_idx over (L_SHIPDATE = '2010-12-01') (cost=1.11 rows=1)
-> Index range scan on lineitem using l_partkey_idx over (L_PARTKEY = 100) (cost=3.03 rows=20)
可以看出，MySQL及PostgreSQL都支持索引融合優(yōu)化優(yōu)化，索引融合的算法另文討論。
外鍵索引
應(yīng)該在外鍵上鍵上創(chuàng)建索引，這個原則似乎和第一個原則（基于您的工作負載創(chuàng)建索引）相矛盾，然而事實上卻是一致的，因為在真實應(yīng)用中，表之間的關(guān)聯(lián)絕大多數(shù)都是基于主外鍵來進行的。通過在外鍵上建立索引，可以提升表關(guān)聯(lián)的效率，特別是在支持索引融合的數(shù)據(jù)庫上。
在MySQL中，如果某個字段被定義為外鍵，默認會有對應(yīng)的索引建立在上面；而在PostgreSQL系列的數(shù)據(jù)庫中，將某些字段設(shè)置外鍵并不會自動在這些字段上建上索引。
準則3：創(chuàng)建索引時的約束條件
創(chuàng)建索引時，雖然它們可以提高讀取性能，但是索引也不是免費的午餐，創(chuàng)建索引也具有一定的代價，譬如索引會對寫入性能產(chǎn)生負面影響，因為對于數(shù)據(jù)庫管理器寫入表的每一行，它還必須更新任何受影響的索引?；诖?，我們通常會限制每個表創(chuàng)建的最大索引數(shù)量。而且索引還會占用一定的磁盤空間，在磁盤空間比較緊張的系統(tǒng)上，索引的大小和數(shù)量也需要進行控制。這部分內(nèi)容我們稱之為約束條件。我們的目標就是在給定的約束條件下，創(chuàng)建合適的索引，以最大限度的提升系統(tǒng)的整體性能。
- 單表索引數(shù)目
- 索引字段數(shù)目
- 索引磁盤空間
針對以上的約束條件，我們通常通過以下的方法創(chuàng)建和維護索引：
- 索引列取舍：通過對列的單值選擇率的評估，在過濾效果最好的列上建立索引; 通過對工作負載的分析，避免在頻繁更新的列上建立索引。
- 索引取舍：通過對工作負載的分析，在最重要的SQL或是使用頻率最高的查詢上提供索引。
- 索引合并：索引滿足組最左前綴匹配原則，所以可以通過設(shè)計索引列的排列順序，達到一個索引加速多個SQL的查詢。
- 索引刪除：通過命令或工具定期采集索引的使用情況，將不再使用的索引進行刪除。
總結(jié)
本周來講，索引創(chuàng)建的過程可以抽象化為基于以上的約束條件，定義索引的收益，使用啟發(fā)式算法，計算在滿足特定約束條件下，整個工作負載收益最大的索引集合，這也是PawSQL索引推薦引擎的內(nèi)部邏輯。
