索引 Index

Database 最佳化: 索引 Index
Categories:
Tags:

索引順序

在所有的資料庫內，建立索引是提昇資料庫資料存取效率的很重要的方式，但是錯誤的索引順序或是 SQL 語法 都可能讓查詢語法變成了 slow query。

假設我們有一個部落格文章的資料表，裡頭存放所有使用者的部落格文章，資料表資料如下：

posts

post_id post_status author content
1 正常 kejyun 文章 1
2 刪除 kejyun 文章 2
3 正常 kejyun 文章 3
4 正常 kj 文章 4
5 刪除 kj 文章 5

如果我們要撈取 作者為 kejyun 且 狀態正常 的文章，我們可能會用下列語法去撈取

SQL 語法 1

SELECT *
FROM posts
WHERE post_status = '正常'
AND author = 'kejyun'

或者用這個語法撈取

SQL 語法 2

SELECT *
FROM posts
WHERE author = 'kejyun'
AND post_status = '正常'

這兩句 SQL 語法都可以撈出我們想要的結果，但是對於不同的索引執行的效率卻是差異很多

如果我們的索引是 post_status、author 的順序，SQL 語法 1 則會正確的使用索引來做查詢，執行的效率會很快。

但是對於SQL 語法 2，會因為找不到適合該語法的索引去做查詢，所以會變成不使用索引，而對整個資料表作完整的檢索，去查找出資料來。

主要原因是

在 SQL 語法 WHERE 的順序會影響查詢索引的順序

同樣的索引資料在不同的順序，表示為不同的索引，所以 post_status、author 與 author、post_status 這兩個索引雖然是使用相同的資料欄位，但因為順序不同所帶來的查詢效果也不同

同一句 SQL 語法只能使用單一個索引，所以不同的索引沒辦法共用

資料庫沒有這麼人工智慧，會去判斷兩個 SQL 語法可以使用相同的索引去查找出相同的資料，他會依照程式設計師給的語法，依序去查找是否有可用的索引。

分析 SQL 語法 1

在 SQL 語法 1 中，我們 WHERE 的第 1 個條件是 post_status = '正常'，所以資料庫會去查找有沒有索引開頭是使用 post_status 的索引。

若有該索引，則再判斷 WHERE 的第 2 個條件 author = 'kejyun'，所以資料庫會去查找有沒有索引開頭是使用 post_status 的索引，且該索引的第 2 個索引是使用 author 的索引。

資料庫會一直判斷比較 SQL 語法 WHERE 條件與索引之間的順序關係，直到沒辦法匹配後，後面的沒辦法匹配索引則使用完整比對的方式去進行查詢

分析 SQL 語法 2

在 SQL 語法 2 中，我們 WHERE 的第 1 個條件是 author = 'kejyun' 但我們資料庫沒有建立索引開頭是使用 author 的索引，所以只能對資料庫使用完整比對。

原理

索引就像是書籍的目錄一樣，若以童話故事書為例，我們書中會收錄世界各國的故事，且每個故事有他自己的類型，像是奇幻、驚悚、傳說、神話。

如果我們以國家當做大標題（e.g. 第 1 索引），以故事類型當作小標題（e.g. 第 2 索引），那麼書籍目錄會像：

  • 台灣
    • 奇幻
    • 驚悚
    • 傳說
    • 神話
  • 日本
    • 奇幻
    • 驚悚
    • 傳說
    • 神話
  • 歐美
    • 奇幻
    • 驚悚
    • 傳說
    • 神話
  • 印度
    • 奇幻
    • 驚悚
    • 傳說
    • 神話
  • etc…

如果我們要找台灣且類型為傳說的童話故事，我們的目光會先移動到台灣的區段，然後在這個區塊下找到類型為傳說的故事

  • 台灣
    • 奇幻
    • 驚悚
    • 傳說
    • 神話

但是如果我們要找所有類型為傳說的故事中，發生在台灣的故事，依照步驟我們會希望先把所有傳說的故事先列出來，再從這個目錄下去找屬於台灣的故事，所以我們希望會看到像這樣的目錄（索引）：

  • 傳說
    • 台灣
    • 日本
    • 歐美
    • 印度

但是在這本故事書的目錄（索引）中，我們沒有看到這樣的目錄結構，所以我們沒辦法透過目錄快速的找到我們要看的所有類型為傳說的章節資料，只好從頭到尾的去翻閱整本書，直到找到全部我們傳說章節的故事，然後再從這些找出來的傳說故事中，再去區別出哪些為台灣的故事。

所以目錄（索引）的規則就是希望看故事書的人要怎麼快速找到他想要的東西，當沒有我們可以參考的目錄的話，就像資料庫沒有可參考的索引一樣，就會找得比較慢（但還是找得出來）。

結論

因為索引順序的不同，以及 SQL WHERE 條件順序的不同，會使得資料庫在使用索引進行查詢有不同的效率，所以要謹慎的使用索引及 SQL 語法，才能達到高效率的查詢結果。

控制索引更新

關閉索引更新：

ALTER TABLE table_name DISABLE KEYS;

開啟索引更新：

ALTER TABLE table_name ENABLE KEYS;

MySQL在新增（INSERT）、刪除（DELETE）、更新（UPDATE）的時候會去更新現有的索引表，而更新索引表也需要花費一些時間，當異動一筆資料的時候，索引表也做一次的異動，但當在做大量資料異動的時候，例如異動1000筆資料，索引表也需要異動1000次，而其實我們只需要最後一次（最新）的異動就好了，前面的999次都是不需要做的索引表異動更新，所以在異動大量資料前，可以使用指令 ALTER TABLE table_name DISABLE KEYS; 關閉索引更新，等異動完成後，再使用指令 ALTER TABLE table_name ENABLE KEYS; 開啟索引更新。

ALTER TABLE users DISABLE KEYS;
異動（INSERT、DELETE、UPDATE）大量資料SQL語法
ALTER TABLE users ENABLE KEYS;

自定義Hash Index做字串完整比對

我們知道在對字串（CHAR或VARCHAR）去做查找的時候效率會遠比對整數（INT）查找還慢，因CRC32對字串做校驗後會回傳整數的校驗碼，我們在資料表增加一個整數型態欄位，儲存要比對字串的校驗碼。

crc32

建立str 的 32 位迴圈冗余校驗碼多項式。這通常用於檢查傳輸的資料是否完整。

由於 PHP 的整數是帶符號的，許多 crc32 校驗碼將返回負整數，因此你需要使用 sprintf() 或 printf() 的「%u」格式符來取得表示無符號 crc32 校驗碼的字串。 在原本實作email登入時會對email欄位做索引，所以會先去查找email字串欄位的資料，之後再去比對密碼是否正確，但若資料過多字串比對的效率會降低很多

SELECT id,name,email
FROM user
WHERE email = "kejyun@gmail.com"
AND password = "xxx"

我們加入了 emailcrc 的欄位去儲存對 email 字串的校驗碼，再查找 email 字串欄位的資料前，先透過crc整數校驗碼快速過濾掉不可能的資料，之後再從少數的資料中做 email 字串欄位字串比對，如果資料量很大，這樣的效率會提升很多。

SELECT id,name,email
FROM user
WHERE emailcrc = CRC32("kejyun@gmail.com")
AND email="kejyun@gmail.com"
AND password="xxx"

這邊要注意的是沒辦法只使用 crc 校驗碼去當作唯一的條件，不同的字串可能會出現相同的校驗碼，所以最後還是要對你要比對的字串做比對，避免查詢發生錯誤。

email emailcrc
kejyun1@gmail.com 1234567890
kejyun2@gmail.com 1234567890

參考資料