行業動態

1、概念

索引存儲在內存中，為服務器存儲引擎為了快速找到記錄的一種數據結構。

2、優缺點

優點

• 通過索引進行查詢，減少表的檢索行數，提高查詢效率

• 建立唯一索引或者主鍵索引,保證數據字段的唯一性

• 檢索時有分組和排序需求時，可以減少服務器排序的時間

缺點

• 創建和維護索引需要消耗時間及內存，隨著數據的增加而增加

• 索引字段過多，數據量巨大時，索引占據空間可能比表更大。

• 當對表的數據進行更新操作時,索引也要動態的維護,這樣就會降低數據的維護速度。

3、索引的使用注意

• 表數據較小時不建議使用，此時全表掃描可能效率更好

• 表的主鍵、外鍵必須有索引。因為主鍵具有唯一性，外鍵關聯的是主表的主鍵，查詢時可以快速定位

• 在經常需要where、排序、分組、取區間的列上建議使用。

• 列不能作為表達式的一部分，或者用作函數參數，否則失效。

• 當表更新操作遠大于select操作時，不建議添加索引。

4.索引數據結構

數據組織方面

• 選擇樹形存儲

基礎數據結構中，hash時間復雜度(O(1))但支持順序查找困難。數組鏈表復雜度(O(n))。樹在查找上時間復雜度居中(O(logn))，天然支持順序。

• 存儲引擎等塊

每塊數據長度不定，索引中至少必須存儲磁盤id、起始號、偏移號這三個值。由此問題，設計出以塊為單位，避免跨頁讀取數據，塊的單位一般等同磁盤的頁或其倍數。(innodb塊大小為16k，操作系統一頁=4k)

• 有序存儲

索引有序 + 磁盤內有序，加速查找時間 且 更好的支持順序查找。

• 聚簇索引

數據組織方式。innodb使用聚簇索引，葉子節點中包含索引+數據； MyIsm引擎非聚簇，葉子節點中包含索引+數據指針，數據被存儲在其他地方。

B樹

平衡多路查找樹，一顆m階的B樹

特性：

1. 樹中每個結點最多含有m個孩子( m >= 2 );

2. 除根結點和葉子結點外，其他每個結點至少 m/2 個孩子。

3. 若根結點不是葉子，至少2個孩子。

4. 有 j 個孩子的非葉節點恰好有 j-1 個關鍵碼，關鍵碼按遞增次序排序。

B樹存在磁盤中，我們想要查找29，查找過程：

1. 根據根結點找到文件目錄的根磁盤塊1，將其中信息導入內存。 【磁盤IO操作一次】

2. 此時內存中有兩個文件17,35和三個存儲其他磁盤頁面地址的數據。 比較：17<29<35，因此我們訪問指針P2

3. 根據P2指針，我們定位到磁盤3，并將其信息導入內存?！敬疟PIO操作2次】

4. 此時內存中有兩個文件26，30和三個存儲其他磁盤頁面地址信息的指針，26<29<30，因此我們找到P2指針。

5. 根據P2指針，定位到磁盤8，將其中信息導入內存?！敬疟PIO操作3次】

B+

相對B樹的不同特性：

1. 非葉子節點的值會以最大或最小值出現在其子節點中，即葉子節點包含所有元素。

2. 非葉子節點帶有索引數據和指向葉子節點的指針，不包含指向實際元素數據的地址信息。僅葉子節點有所有元素信息。

3. 每個元素不保存數據，只保存索引值即主鍵。

4. 所有葉子節點形成一個有序鏈表。

單行查詢時與B樹相同

范圍查詢時，比如查找大于3小于8的數據，根據單行查找方式查找到3之后，通過鏈表直接遍歷后面的元素。

B+樹優勢

1. B+樹的磁盤讀寫代價更低/效率更高。同樣的一塊磁盤大小，B樹需要存儲表元素數據，B+只需要存儲索引，可以存儲更多節點。同等元素數據量下，B+樹層數更少。

2. B+樹的查詢效率穩定。因為非終結點只是關鍵字的索引，所以任何關鍵字的查找必須走一條根到葉子的路。

3. B+樹中葉子結點也形成一個鏈表，所以B+樹在面對范圍查詢時比B樹更加高效。

5、InnoDB索引使用

索引分主索引和輔助索引

主索引在表創建后即存在。以主鍵為索引，葉子節點存儲元素數據。

為主鍵外的字段添加的索引為輔助索引。以字段內容為索引，葉子節點存儲元素對應主鍵。

MyISAM不同點在于葉子存儲的不是元素數據，而是元素數據地址。實現索引與實際數據分離。

如何高效率使用索引

獨立列查詢

SQL語句使用不當時，將無法使用現存索引而去全表掃描。所以需要注意：索引列不能是表達式的一部分，也不能是函數的參數。

通過在查詢SQL前加explain，查看是否有使用索引。

上圖中，為timestamp字段添加了索引。 明顯使用DATE()函數后，timestamp不使用索引，rows行數為總數據行數。

前綴索引查詢（注意選擇性把握）

選擇性指不重復的索引值和數據表的記錄總數的比值。選擇性最高時，即所有鍵不重復時選擇性為1。

由上面對索引內部實現的描述我們得知，我們索引的字段越長時，所占內存也就越大。前綴索引意在保持較高選擇性的情況下，取字段的前綴部分用于索引，降低內存使用率。

我對測試表中pdl字段及前綴部分的選擇性進行觀測如下：

如圖，前綴為9時選擇性已經較高，再增加時，沒有明顯提升。這時，如果pdl字段很長，就可以考慮使用pdl的前綴9個字符作為前綴索引。

注意：無法使用前綴索引做ORDER BY 和 GROUP BY，考慮業務場景做取舍。

多列索引合并

很多時候我們為了查詢方便，為很多列單獨創建索引。但我們在使用where篩選時，卻多使用AND，OR等條件。

當我為表的pdl，timestamp字段單獨設置索引時，and查詢為：

這里的僅使用了pdl字段索引。（高性能提到5.0之后的版本會各自使用pdl和timestamp字段，然后SQL服務器對多個索引結果做相交(AND)或聯合操作(OR)操作，通過extra可查詢，但是我的5.7沒有這種優化，不知道為什么~~）

如上，僅使用where條件的第一個字段索引 或者 服務器消耗CPU，內存等資源去做合并工作，都會影響查詢性能。

這是有必要合并索引，創建pdl_time(pdl, timestamp)索引后同樣的查詢結果如下：

pdl_time索引被使用，filtered達到100%。

在創建多列索引時注意：

- 通常將選擇性高的字段放在前面

- 多列字段的前綴也可以作為索引(例如(a,b)索引時，可以單獨使用a索引，但不能單獨使用b索引)

聚簇索引

聚簇索引指的是一種數據組織結構。判斷標準為：索引的葉子節點中，存儲的是數據還是只想數據塊的指針。如果是指向數據塊指針，則為非聚簇索引。

索引類型依賴存儲引擎，Innodb使用的是聚簇索引，MyISAM使用非聚簇索引

Innodb主鍵索引圖：

如圖為Innodb存儲引擎生成的主鍵索引結構。非葉子節點存儲主鍵，葉子節點存儲主鍵和行數據(還有事務ID和回滾指針)。

Innodb輔助索引圖：

如圖為Innodb存儲引擎生成的輔助索引結構。葉子節點存儲索引字段和對應的主鍵值，索引到主鍵值后，根據主鍵值再去主鍵索引中查找對應的數據。

優點在于：

減少磁盤IO次數。使用索引查詢數據時，索引節點和數據被一起載入內存，不需要根據指針再進行一次IO讀取。

無需維護輔助索引。當出現數據頁分裂時，無需更新索引中的數據塊指針。

非聚簇索引圖：

非聚簇索引主鍵索引和輔助索引結構一致。

SQL慢查詢原因

先確保服務響應慢時，不是一些偶然情況或者機器性能問題，確定響應慢源頭是SQL操作。

• 定位慢查詢

• explain

• profile