什麼是數據庫的索引？

索引

當數據庫中數據量比較少的時候，哪怕全部檢索也可以很快，但如果數據量達到了百萬，千萬，上億的時候，還是全表掃描，那麼數據查詢的速度會慢的讓人無法忍受。

索引的作用，就是爲了加快數據查詢，類似於我們查不認識的字時，使用字典的目錄一樣，在字典裏面快速查詢出不認識的字。字典可以根據讀音的首字母，偏旁部首，筆畫來查詢。同樣的，索引也有 Hash 索引，B-Tree 索引，GIN 索引等不同索引類型，根據查詢的場景不同，可以選擇創建對應的索引類型。

索引分類

數據結構實現

Postgresql 支持豐富的索引類型，並且根據索引框架支持用戶開發自定義的索引，下面列舉下常用的索引類型及適用範圍

4DfjJ5

mysql 的索引類型和數據庫引擎相關性較強，不過最常用的 B 樹索引是支持的

vDCPvD

聚簇索引與非聚簇索引

InnoDB 默認創建的主鍵索引是聚族索引（Clustered Index），其它索引都屬於輔助索引（Secondary Index），也被稱爲二級索引或非聚族索引。

聯合索引與單列索引

create index i1 on t2 (c1);
create index i2 on t2 (c1,c2);

pg 的多列 (聯合) 索引僅支持 b-tree、gist、gin、brin 類型，其中 b-tree 的多列索引，僅在索引的第一個字段出現在查詢條件中才有效 (最左匹配原則)，而其他類型的多列索引可以支持任意字段查詢 對於多字段查詢，多列索引要比單列索引的查詢速度快，可以避免回表查詢，但對於單字段查詢，多列索引就要比單列索引查詢速度慢了，這裏需要根據表的實際查詢 sql 類型、頻率，綜合考慮是否需要使用多列索引。

部分索引

部分索引是指支持在指定條件的記錄上創建索引，通過 where 條件指定這部分記錄，比如：

postgres=# create table test(id int, c1 varchar(10));
CREATE TABLE
postgres=# insert into test select generate_series(100001,100100),'invalid';
INSERT 0 100
postgres=# create index i1 on test (c1) where c1 = 'invalid';
CREATE INDEX
postgres=# explain analyze select * from test where c1 = 'invalid';
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Index Scan using i1 on t3  (cost=0.14..4.16 rows=1 width=11) (actual time=0.035..0.079 rows=100 loops=1)
 Planning Time: 0.485 ms
 Execution Time: 0.135 ms
(3 rows)

實際上對於數據分佈不均的字段，創建正常的索引，在查詢佔比較小值時也是可以走索引的，查詢佔比較大值時無法走索引，如下所示，部分索引的優勢在於索引體積小，維護代價也比較小

函數索引

函數索引指可以使用一個函數或者表達式的結果作爲索引的字段，比如：

postgres=# create index i1 on test ((lower(c1)));
CREATE INDEX
postgres=# explain analyze select * from test where lower(c1) = 'xxx';
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=20.29..783.05 rows=500 width=37) (actual time=0.063..0.063 rows=0 loops=1)
   Recheck Cond: (lower(c1) = 'xxx'::text)
   ->  Bitmap Index Scan on i1  (cost=0.00..20.17 rows=500 width=0) (actual time=0.060..0.060 rows=0 loops=1)
         Index Cond: (lower(c1) = 'xxx'::text)
 Planning Time: 0.406 ms
 Execution Time: 0.095 ms
(6 rows)

postgres=# explain analyze select * from test where c1 = 'xxx';
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..2084.00 rows=1 width=37) (actual time=19.016..19.016 rows=0 loops=1)
   Filter: (c1 = 'xxx'::text)
   Rows Removed by Filter: 100000
 Planning Time: 0.121 ms
 Execution Time: 19.048 ms
(5 rows)

此時如果直接使用 c1 字段作爲查詢條件是無法走索引的，同理如果創建的是普通索引，在查詢時對字段加上了函數或者表達式，都不會走索引，我們應始終避免出現這樣的問題

排序索引

索引非銀彈

索引需要佔用額外的物理空間，如果表中的數據變化，也需要同步維護索引中的數據，對數據庫的性能會有一定影響。考慮到索引的維護代價、空間佔用和查詢時回表的代價，不能認爲索引越多越好。索引一定是按需創建的，並且要儘可能確保足夠輕量。一旦創建了多字段的聯合索引，我們要考慮儘可能利用索引本身完成數據查詢，減少回表的成本。不能認爲建了索引就一定有效，對於後綴的匹配查詢、查詢中不包含聯合索引的第一列、查詢條件涉及函數計算等情況無法使用索引。此外，即使 SQL 本身符合索引的使用條件，MySQL 也會通過評估各種查詢方式的代價，來決定是否走索引，以及走哪個索引。

數據庫基於成本決定是否走索引

查詢數據可以直接在聚簇索引上進行全表掃描，也可以走二級索引掃描後到聚簇索引回表。那麼 PostgreSQL/MySQL 到底是怎麼確定走哪種方案的呢。在滿足能走索引的條件下，最終是否走索引由計劃器生成的執行計劃決定，PostgreSQL/MySQL 中執行計劃是完全基於代價估計的，如果估算的代價爲全表掃描最優，則不會使用索引掃描

全表掃描，就是把聚簇索引中的記錄依次和給定的搜索條件做比較，把符合搜索條件的記錄加入結果集的過程。要計算全表掃描的代價需要兩個信息：

  1. 聚簇索引佔用的頁面數，用來計算讀取數據的 IO 成本；

  2. 表中的記錄數，用來計算搜索的 CPU 成本。

有時會因爲統計信息的不準確或成本估算的問題，實際開銷會和 MySQL 統計出來的差距較大，導致 MySQL 選擇錯誤的索引或是直接選擇走全表掃描，這個時候就需要人工干預，使用強制索引了。

索引失效

例如我們在 order 表中建立一個複合索引 idx_user_order_status(order_no, status, user_id)，如果我們使用 order_no、order_no+status、order_no+status+user_id 以及 order_no+user_id 組合查詢，則能利用到索引；而如果我們用 status、status+user_id 查詢，將無法使用到索引，這也是我們經常聽過的最左匹配原則。

常見慢 sql 情況

索引優化

子查詢優化

實際的業務 sql 中，往往要涉及多個表進行關聯查詢，這裏既可以使用子查詢，也可以使用表連接，一般我們認爲子查詢方式的查詢層次較多，且關聯時的結果集較大，所以性能會差一些，執行計劃器會對子查詢進行邏輯優化，將子查詢上提到父查詢中，與父查詢合併，過濾出較小的結果集再進行關聯

any，some，exists，not exists 基本支持, in 部分情況支持, all,not in 不支持.

寫法優化

創建合適的索引

單表索引不應該超過 5 個。複合索引字段數量一定不可超過 4 個。複合索引字段數量多主要有以下 2 個影響：1. 字段數量越多，對查詢的要求越苛刻。**查詢必須按照索引的命中規則來安排。**2. 字段數量越多，索引的體積越大。數據的扇出度（單次 IO 能得到的數據條數）越低，IO 效率也越低，而且索引被更新的概率越大，由於二級索引大部分情況下是隨機更新，所以會引起 B + 樹的平衡維護操作。

E  即 Equal。查詢中等於條件的字段優先考慮。S 即 Sort, 排序字段其次考慮。R 即 Range, 範圍查詢字段最後考慮 在經常用於查詢的字段上創建索引, 在經常用於連接的字段上創建索引, 在經常用於排序的字段上創建索引

低基數字段不應該建立單獨的索引。（該字段的不重複值個數低於總行數的 10% 的稱爲低基數字段）。比如性別字段，只有男、女兩種取值，認爲選擇性不好，不建議創建索引分佈不均勻的字段不應該建立索引。如果一定需要，應該避免使用分佈較高的值作爲查詢條件。分佈不均勻指不同的列值佔總體的比例差異很大（通常超過 50%），即某一個列值或者某幾個列值在整個數據集合中佔比非常大。例如幼兒園學生年齡分段：年齡段佔比 3~5：95% ，6~8：3%， 9~12：1%，12~20：1%，20 以上 0%

高頻更新字段不應該建立索引, 高頻更新字段，會以更新頻率同步去更新索引。這會引起索引的刪除、插入操作。頻繁地刪除索引上的數據，索引頁會造成大量的空洞，進而引發樹的平衡維護。

例如 同時存在  idx_A_B(A,B)  ,idx_A(A) 兩個索引

按數據頁 16K 計算，我們期望單個索引頁至少應該存納 70 個索引。默認的 innodb 的頁上會留有 1/16 的空閒區域。這個空閒區域的主要作用是減少樹的平衡操作。

InnoDB 是如何存儲和查詢數據的

MySQL 把數據存儲和查詢操作抽象成了存儲引擎，不同的存儲引擎，對數據的存儲和讀取方式各不相同。MySQL 支持多種存儲引擎，並且可以以表爲粒度設置存儲引擎。因爲支持事務，我們最常使用的是 InnoDB。

每個數據頁中的記錄按照主鍵順序組成單向鏈表；每一個數據頁中有一個頁目錄，方便按照主鍵查詢記錄。

頁目錄通過槽把記錄分成不同的小組，每個小組有若干條記錄。如圖所示，記錄中最前面的小方塊中的數字，代表的是當前分組的記錄條數，最小和最大的槽指向 2 個特殊的僞記錄。有了槽之後，我們按照主鍵搜索頁中記錄時，就可以採用二分法快速搜索，無需從最小記錄開始遍歷整個頁中的記錄鏈表。

如果要搜索主鍵（PK）=15 的記錄：先二分得出槽中間位是 (0+6)/2=3，看到其指向的記錄是 12＜15，所以需要從 #3 槽後繼續搜索記錄；再使用二分搜索出 #3 槽和 #6 槽的中間位是 (3+6)/2=4.5 取整 4，#4 槽對應的記錄是 16＞15，所以記錄一定在 #4 槽中；再從 #3 槽指向的 12 號記錄開始向下搜索 3 次，定位到 15 號記錄。

B + 樹的特點包括：1. 最底層的節點叫做葉子節點，用來存放數據；2. 其他上層節點叫作非葉子節點，僅用來存放目錄項，作爲索引；3. 非葉子節點分爲不同層次，通過分層來降低每一層的搜索量；4. 所有節點按照索引鍵大小排序，構成一個雙向鏈表，加速範圍查找。因此，InnoDB 使用 B + 樹，既可以保存實際數據，也可以加速數據搜索，這就是聚簇索引。如果把上圖葉子節點下面方塊中的省略號看作實際數據的話，那麼它就是聚簇索引的示意圖。由於數據在物理上只會保存一份，所以包含實際數據的聚簇索引只能有一個, 這也就是爲什麼主鍵只能有一個的原因。

（唯一定義一條記錄的單個或多個字段）作爲聚簇索引的索引鍵（如果沒有主鍵，就選擇第一個不包含 NULL 值的唯一列）。上圖方框中的數字代表了索引鍵的值，對聚簇索引而言一般就是主鍵。

比如，我們要搜索 PK=4 的數據，通過根節點中的索引可以知道數據在第一個記錄指向的 2 號頁中，通過 2 號頁的索引又可以知道數據在 5 號頁，5 號頁就是實際的數據頁，然後再通過二分法查找頁目錄馬上可以找到記錄的指針。

二級索引，也是利用的 B + 樹的數據結構，如下圖所示：

這次二級索引的葉子節點中保存的不是實際數據，而是主鍵，獲得主鍵值後去聚簇索引中獲得數據行。這個過程就叫作回表。比如有個索引是針對用戶名字段創建的，索引記錄上面方塊中的字母是用戶名，按照順序形成鏈表。如果我們要搜索用戶名爲 b 的數據，經過兩次定位可以得出在 #5 數據頁中，查出所有的主鍵爲 7 和 6，再拿着這兩個主鍵繼續使用聚簇索引進行兩次回表得到完整數據。

🍭 總結

以上就是索引的創建及使用時注意事項，最後彙總了一些索引優化方式，並分析InnoDB是如何存儲和查詢數據的。

投稿人 stephenXu，公衆號 程序員 HopeX

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/tBf2s0_ZQ_D4H0YG0l9JuQ

猜你喜歡