Mysql合理建立索引,索引優化

• 2019 年 12 月 15 日
• 筆記

寫在前面

在我們日常使用資料庫的時候，肯定避免不了對資料庫的優化。那麼對資料庫的優化又少了不索引的知識。

是的，建立索引能極大地提高查詢的效率。那麼你知道嗎，如果合理建立索引，可以更大地榨出資料庫的性能——也就等同於進一步提高查詢效率。

寫下這篇文章就是為了記錄一下對索引的優化，合理建立索引。

什麼是索引

MySQL官方對索引的定義為：索引(Index)是幫助MySQL高效獲取數據的數據結構。

我們可以簡單理解為：快速查找排好序的一種數據結構。Mysql索引主要有兩種結構：B+Tree索引和Hash索引。

索引就相當於字典前的目錄，如果這個目錄劃分規劃更好，那麼我們找到想要的數據就會更方便，也就提高了查詢的效率。

索引的類型有：

• normal：表示普通索引
• unique：表示唯一的，不允許有重複值的索引
• full textl: 表示全文搜索的索引，用於搜索很長一篇文章的時候，效果最好。

建立索引的場景

索引不是越多越好，因為每次更新、插入數據，就需要對索引文件進行變動，會減低該類型操作的執行效率。

如果建立索引的欄位太多，影響就會很大。

所以我們只在合理的欄位上建立索引。

• 在經常用來當查詢條件(where,on,group by,order by)的欄位上建立索引。
• 在數據的維度比較大的欄位上建立索引。
• 對數據較小的列使用索引，可以使索引文件更小，同時記憶體中也可以裝載更多的索引鍵。
• 為較長的字元串使用前綴索引。比如數據的長度大部分是150個位元組，我們只建立前100個位元組的索引。

常見的可以用於建立索引的欄位場景：

① 用戶id 在訂單表中的用戶id欄位上建立索引，根據用戶id篩選訂單，則會很快查詢出用戶的訂單。 用戶一般是在自己的後台查看訂單，所以表中的其他用戶數據與他無關，如果沒有建立索引，每次查詢都是全表掃描，則會很慢。（我們每個人在淘寶平台上占的訂單比例都不到0.0001）

② 商品名 同上，如果不建立索引，我們在淘寶輸入框搜索鍵盤，怎麼快速查詢出鍵盤商品？

③ 日期 常用的活躍數據，一般都是是最近產生的，很少人沒事去翻半年前的訂單數據吧？

合適的還有很多很多場景，需要各位小夥伴自己好好思量。

索引不生效的場景

並不是建立了索引就在查詢語句中生效。

• 當語句中帶有or的時候，索引會失效
• 當索引的欄位使用like查詢，並且使用了前通配比如%Siam，索引失效。後通配會生效Siam%
• 當索引的欄位是字元類型，但是儲存的值是數字，比如 user_name:』123456』，在查詢語句中要 user_name = 『123456』而不能 user_name = 123456 否則發生類型轉換，索引失效，其他類型的欄位 比如日期等 也同理
• 當使用的條件語句，預計結果數量超過全表數據的一定比例時，會轉為全表掃描（mysql一般是30%左右）這就是為什麼在建立索引的時候要選擇維度（區別度）比較高的列，性別這種欄位不適合建立索引。
• 語句中出現列數據運算才判斷的，比如where age – 10 > 0 每一行都要運算之後才知道是否大於0 所以就是全表掃描，如果age > 10 則可使用索引。使用函數轉換列數據也一樣原理。
• 組合索引時使用的條件語句。

上面舉例的一些場景都是比較容易理解的。組合索引還沒涉及，往下繼續介紹。

mysql中，多個索引同時使用？

先來看一條語句

select * from test where user_name = 'siam_007' and create_time = 1563280050

假設在這個表中，user_name欄位和create_time都是建立了索引的。也沒有發生數據類型轉換等情況。

問題：該語句有幾個索引生效？

是2個嗎？並不是這樣子的。

雖然兩個欄位的索引都沒有問題，如果單獨使用也都能生效。

但是：MySQL會從可用的索引中猜測出效率最高的一個索引並使用它

怎麼證明？使用explain語句（詳解可以在網上其他資料找到）

只要在sql語句最前面加上該語法，則會顯示查詢類型相關的資訊。

Explain select * from mysql_index_test where user_name = 'siam_007' and create_time = 1563280050

我建立了兩個索引，都能用，都是本次查詢只能使用一個。

基於這個情況，會引申出兩個知識點

• 組合索引
• 多個單欄位索引衝突

組合索引

先來說說組合索引吧，我們在新建索引的時候（可視化軟體），是這樣子的頁面

當我們勾選上2個或者2個以上 就能把它叫為組合索引了

可以看到還有調整順序的功能

在組合索引中，欄位順序也是極為重要的。

假設我們有這樣一個索引：new : a,b,c 欄位

在查詢時使用where a = 1 and b = 2 and c= 3。那麼這個語句肯定是生效的。

如果我們使用where b = 2 and c =3沒有a條件 而a又是在組合索引中最左側的，那麼索引就不會生效。

還是要掃描全部行

where a,c where a,b where b,a,c 這樣子使用都是可以生效的。

組合索引要注意欄位順序，是指在創建索引時候的排序，而不是sql語句中where的順序，我們使用where b = 2 and a = 1 and c = 3也是 可以生效的

那麼組合索引的欄位順序要如何排比較好?

後面會有新的一篇文章會講這個

多個單欄位索引衝突

如果我們在表中，擁有兩個索引，比如user_name 用戶名, user_phone 用戶手機號

現在有這樣子的數據量： 100W條數據 user_name=』我是用戶名』 100條數據 user_phone=』110′ 5條數據 user_name=』我是用戶名』 and user_phone=』110′

假設有這樣子一條語句：

select * from test where user_name = '我是用戶名' and user_phone='110'

有兩個欄位都有索引可用，mysql會選擇一個使用。這是屬於mysql的內部處理判斷

正常情況下，如果用user_phone索引生效的話，會很快得到結果（先篩選出100條 再篩選）

如果user_name生效，則要先篩選100W條數據，再篩選user_phone

mysql的內部判斷可能使得user_name索引生效，此時效率就會很低了，我們可以強制使用某個索引

phone是索引名 是新建的時候填的

select * from test index(phone) where user_name = '我是用戶名' and user_phone='110'

此種情況出現的概率比較小，畢竟mysql的解釋器很複雜，也做了足夠多的優化。只有排查慢日誌並且分析確定索引衝突的情況才需要強制使用索引。

優化 （總結）

• 只在經常使用的欄位上建立索引，否則會拖慢數據更新和插入的速度。
• 只在維度高的欄位上建立索引，否則會使得數據比例過大，轉為全表掃描。
• 優先對數據量比較小的欄位建立索引，可以使索引文件更小，同時記憶體中也可以裝載更多的索引鍵。
• 規範sql語句的生成，禁止發生列運算、類型轉換的發生。
• 長字元串可以使用前綴索引，只對字元串的前面一定字元長度建立索引。
• 組合索引的順序合理優化（會有新文章介紹）
• 當多個單欄位索引發生衝突時，強制使用某個索引。