本文介紹索引創建時需遵循一些準則，以達到最佳的查詢性能和數據維護效率，這些準則也是PawSQL索引推薦引擎的內部工作邏輯。本文基于最常用的B+樹索引來舉例，其他索引的使用有一定的限制條件，具體請參考??《數據庫索引的類型》??。
準則1：基于您的工作負載創建索引
創建高效的索引最重要的原則是，基于您的工作負載（workload）創建索引，而不是基于您的表結構。針對數據庫執行的所有 SQL 語句構成了該數據庫的工作負載。索引的目的是為了提升數據庫中操作的效率，因此任何其他不以工作負載出發的索引創建方法都是錯誤的。
在針對一個工作負載構建一組索引時，需要考慮工作負載的以下屬性：
- SQL類型，在用戶執行頻繁插入新數據和修改現有數據的OLTP場景，多個索引可能會對性能產生負面影響，并對系統資源造成壓力，建議創建最少數量的索引來滿足您的索引要求。而在以查詢為主的OLAP場景，您可以添加更多索引，每個索引具有多個鍵列，甚至可以添加函數索引和條件索引。
- SQL頻率，為最頻繁使用的查詢創建索引，通過為這些查詢創建最好的索引，能夠最大限度的提升系統的整體性能。
- SQL的重要性，查詢越重要，您可能越想通過創建索引來進行性能優化。
- SQL的本身的結構，詳見下一章節。
準則2：基于單個SQL的結構創建索引
索引的作用如下：
- 快速定位數據
- 避免排序
- 避免回表
- 實現行級鎖(MySQL，另文討論)
- 實現唯一性約束
本章節我們將通過分析SQL的結構來實現以上的功能，創建高效的索引。
為快速定位創建索引
索引可以通過匹配查詢的條件快速的定位數據，查詢的條件可能在WHERE子句、HAVING子句、ON子句中。索引和條件的匹配原則遵循最左前綴匹配原則.
最左前綴原則
最左前綴原則指的是，如果查詢的時候等值的查詢條件精確匹配索引的左邊連續一列或幾列，則此列就可以被用到，同時遇到范圍查詢(>、<、between、like)就會停止匹配，包括范圍條件。
對于聯合索引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對于的執行計劃如下：
-> 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)
由于最左前綴原則，在創建聯合索引時，索引字段的順序需要考慮字段值去重之后的個數(Cardinality)，Cardinality較大的放前面。
等值條件（Index lookup）
- 關聯等值條件, 在一個表作為被驅動表時的等值關聯條件也可以被認為時等值條件被索引匹配使用。
- 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'
范圍條件也可以用來快速定位數據。
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)由于最左匹配原則，位于范圍條件后面的索引列無法利用該索引。
為避免排序創建索引
對于B+樹索引，由于其是按照索引鍵排序的，因此可以通過索引來避免在SQL執行中進行排序。涉及的SQL結構主要包括：
- GROUP BY
- ORDER BY
- DISTINCT
- PARTITION BY... ORDER BY...
create index lshipdate_idx on lineitem(l_shipdate);
可以看到下面的SQL的執行計劃通過訪問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)
需要注意：
- 對于分組和去重，順序不一致是沒有關系的。
- 對于排序，排序字段的順序需要和索引字段的順序一致，否則沒有辦法利用索引來避免排序。
- 對于同時有分組和排序的，需要把排序的索引列放在前面。
譬如對于下面的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)
為避免回表創建索引（Covering index scan）
當查詢中的列都在索引列中時，數據庫只需要訪問索引即可獲取所需的數據，避免了回表操作。在某些場景下，可以大幅的提升查詢效率。
對于如下的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??，需要回表，執行計劃如下，代價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，不需要回表，執行計劃如下，代價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)
其他相關主題
分區表的索引
對于分區表，不同的數據庫對分區表索引的支持不一樣，總體來說，分區表可以建立以下三種類型的索引。
- 本地分區索引（PostgreSQL/MySQL/Oracle/Opengauss）。
- 全局分區索引（Oracle）。
- 全局非分區索引（Oracle/Opengauss）。
本地分區索引
在索引維護性方面，本地索引比全局索引容易管理，當你在進行添加、刪除、truncate表分區時，本地索引會自動維護其索引分區。MySQL和PostgreSQL只支持本地分區索引; Oracle和Opengauss創建本地分區索引時需指定關鍵字local。
create index lshipdate_idx on lineitem(l_shipdate) local;
全局分區索引
和表分區類似，索引的分區鍵和表的分區鍵沒有必然的關系，甚至非分區表也可以建立全局分區索引。Oracle支持全局分區索引。
全局非分區索引
對于全局非分區索引，當你對表分區進行操作時，索引可能變得不可用，需顯式的更新或重建索引。在索引效率方面，全局索引在不包含分區字段的查詢中，效率比本地分區索引更高效。Oracle和Opengauss為分區表默認創建的是全局非分區索引。
create index lshipdate_idx on lineitem(l_shipdate) global;
create index lshipdate_idx on lineitem(l_shipdate);
在進行分區操作時，需要增加update global index關鍵字重建索引，否則索引不可用。
alter table t DROP PARTITION partition_name update global index;
函數索引
函數索引（或表達式索引）即基于函數或表達式的索引，它使用函數或是表達式提供計算好的值作為索引列構建索引，可以在不修改應用程序的情況下提高查詢性能。
函數索引的使用需要函數或是表達式和SQL查詢中的表達式嚴格匹配，所以它使用的條件較為嚴格，適合針對重要查詢或是頻次較高的查詢重點優化。
select * from lineitem where EXTRACT(DAY from l_shipdate) = 1;
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
- 通過創建函數索引，執行計劃中的代價降低為原來的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)，它是建立在一個表的子集上的索引，而該子集是由一個條件表達式定義的，該索引只包含表中那些滿足這個條件表達式的行。
條件索引被使用的條件比較嚴格，只有在數據庫能夠識別出該查詢的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'沒有落到此索引條件表達式的范圍內，該索引將不會被用到，所以執行計劃采用的是全表掃描。
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'在條件表達式的范圍內，數據庫優化器將會采用此索引，可以看到性能有大幅提升。
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）是使用多個索引來完成一次單表數據訪問的優化技術。當查詢中涉及一個表的多個條件時，如果這些條件分別有合適的索引，索引融合可以在回表之前將多個索引的結果合并，以提高查詢性能。
在lineitem表上有在l_shipdate以及l_partkey的單列索引，對于以下的SQL。
select * from lineitem where l_shipdate = date '2010-12-01' or l_partkey=100;
PostgreSQL的執行計劃。
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的執行計劃。
-> 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都支持索引融合優化優化，索引融合的算法另文討論。
外鍵索引
應該在外鍵上鍵上創建索引，這個原則似乎和第一個原則（基于您的工作負載創建索引）相矛盾，然而事實上卻是一致的，因為在真實應用中，表之間的關聯絕大多數都是基于主外鍵來進行的。通過在外鍵上建立索引，可以提升表關聯的效率，特別是在支持索引融合的數據庫上。
在MySQL中，如果某個字段被定義為外鍵，默認會有對應的索引建立在上面；而在PostgreSQL系列的數據庫中，將某些字段設置外鍵并不會自動在這些字段上建上索引。
準則3：創建索引時的約束條件
創建索引時，雖然它們可以提高讀取性能，但是索引也不是免費的午餐，創建索引也具有一定的代價，譬如索引會對寫入性能產生負面影響，因為對于數據庫管理器寫入表的每一行，它還必須更新任何受影響的索引。基于此，我們通常會限制每個表創建的最大索引數量。而且索引還會占用一定的磁盤空間，在磁盤空間比較緊張的系統上，索引的大小和數量也需要進行控制。這部分內容我們稱之為約束條件。我們的目標就是在給定的約束條件下，創建合適的索引，以最大限度的提升系統的整體性能。
針對以上的約束條件，我們通常通過以下的方法創建和維護索引：
- 索引列取舍：通過對列的單值選擇率的評估，在過濾效果最好的列上建立索引; 通過對工作負載的分析，避免在頻繁更新的列上建立索引。
- 索引取舍：通過對工作負載的分析，在最重要的SQL或是使用頻率最高的查詢上提供索引。
- 索引合并：索引滿足組最左前綴匹配原則，所以可以通過設計索引列的排列順序，達到一個索引加速多個SQL的查詢。
- 索引刪除：通過命令或工具定期采集索引的使用情況，將不再使用的索引進行刪除。
總結
本周來講，索引創建的過程可以抽象化為基于以上的約束條件，定義索引的收益，使用啟發式算法，計算在滿足特定約束條件下，整個工作負載收益最大的索引集合，這也是PawSQL索引推薦引擎的內部邏輯。
