淺析mysql索引優化

什么是索引

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語句的生成，禁止發生列運算、類型轉換的發生。

長字符串可以使用前綴索引，只對字符串的前面一定字符長度建立索引。

組合索引的順序合理優化（會有新文章介紹）

當多個單字段索引發生沖突時，強制使用某個索引。

MySQL SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。