mysql 建立索引的一個規則

一些人建立索引時,喜歡對大量字段做單個字段的索引,這樣在一些情況下是不能很好的完成任務的,因為 mysql 在執行查詢優化時,最終只會使用一個字段作為索引。


比如,一個在線社交網站,保存有性別(sex),年齡(age),國家(country),創建時間(createTime) 等等字段。需求是對性別/年齡搜索符合條件的用戶,那麼,一般人會建立如下索引 index_sex(sex), index_age(age),查詢時,使用 where sex='M' and age=25 ,那麼這個 sql ,在大數據量的情況下,性能會非常差,原因是,mysql 在優化時,只會使用到 index_sex 或者 index_age 其中的一個,並不會同時使用到兩個索引。


那麼問題就來了,我們要如何解決這種問題呢?


一個可行的解決方案是,建立一個聯合索引 index_sex_age,對 性別/年齡建立聯合索引,則查詢時,就會使用到這個索引。這個時候也有個問題,就是如何對 age 做單獨查詢?


這個問題的解決方式,比較其妙,也是建立這樣索引的精髓所在,可以使用 where sex in('M','F') and age = 25 這樣的sql,那麼mysql 優化器就會使用聯合索引做查詢。


這裡面有兩個需要注意的點:一、對 sex 一定要使用 in 操作,並且要列出 sex 所有可能的值;二、聯合索引的 sex 一定要在前面。


可以看到這樣子一個索引,如果分別單獨建索引的話,那麼在做兩個條件同時查詢時,效率會非常低,因為 mysql 最終只能使用到其中一個索引。


這樣子的索引,也為我們實際生產環境監理索引提供了一個思路,也就是可以在補增加索引個數的情況下,完成對業務的優化。