MySQL 索引建立原則及注意事項

標籤：而且   mysql查詢   欄位   資料庫   翻轉   資源   bcd   運算   sele   

一、索引建立的幾大原則：1） 最左首碼匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。2）=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢最佳化工具會幫你最佳化成索引可以識別的形式。3）盡量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用情境不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄4）索引列不能參與計算，保持列“乾淨”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的欄位值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);5）盡量的擴充索引，不要建立索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。6）定義有外鍵的資料列一定要建立索引。7）對於那些查詢中很少涉及的列，重複值比較多的列不要建立索引。8）對於定義為text、image和bit的資料類型的列不要建立索引。9）對於經常存取的列避免建立索引二、索引使用的注意點： 1、一般說來，索引應建立在那些將用於JOIN,WHERE判斷和ORDER BY排序的欄位上。盡量不要對資料庫中某個含有大量重複的值的欄位建立索引。對於一個ENUM類型的欄位來說，出現大量重複值是很有可能的情況。 2、應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num is null

最好不要給資料庫留NULL，儘可能的使用 NOT NULL填充資料庫.備忘、描述、評論之類的可以設定為 NULL，其他的，最好不要使用NULL。不要以為 NULL 不需要空間，比如：char(100) 型，在欄位建立時，空間就固定了， 不管是否插入值（NULL也包含在內），都是佔用 100個字元的空間的，如果是varchar這樣的變長欄位， null 不佔用空間。可以在num上設定預設值0，確保表中num列沒有null值，然後這樣查詢： 3、應盡量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進行全表掃描。 4、應盡量避免在 where 子句中使用 or 來串連條件，如果一個欄位有索引，一個欄位沒有索引，將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num=10 or Name = ‘xiaoming‘

可以這樣查詢，充分利用索引：

select id from t where num = 10union allselect id from t where Name = ‘xiaoming‘

5、in 和 not in 也要慎用，否則會導致全表掃描。

而且負向查詢（not , not in, not like, <>, != ,!>,!< ） 不會使用索引

select id from t where num in(1,2,3)

對於連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

很多時候用 exists 代替 in 是一個好的選擇，當然exists也不跑索引。

select num from a where num in(select num from b)

正上面的，用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

6、）下面的模糊查詢也將導致全表掃描：

select id from t where name like ‘%abc%’

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引，而like “aaa%”可以使用索引。若要提高效率，可以考慮全文檢索索引。既然談到模糊查詢下使用索引，我們就順便詳細地講講吧。1. like %keyword 索引失效，使用全表掃描。但可以通過翻轉函數+like前模糊查詢+建立翻轉函數索引=走翻轉函數索引，不走全表掃描2. like keyword% 索引有效。3. like %keyword% 索引失效，也無法使用反向索引。

//可以用explain測試，測一下有沒有走索引select * from table where code like ‘Classify_Description%‘  select * from table where code like ‘%Classify_Description%‘  select * from table where code like ‘%Classify_Description‘  

7、）如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變數，但最佳化程式不能將訪問計劃的選擇延遲到運行時；它必須在編譯時間進行選擇。然 而，如果在編譯時間建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：

select id from t where num = @num

可以改為強制查詢使用索引：

select id from t with(index(索引名)) where num = @num

應盡量避免在 where 子句中對欄位進行運算式操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2 = 100

正上面的應改為：

select id from t where num = 100*2

8、）應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3) = ’abc’       //name以abc開頭的idselect id from t where datediff(day,createdate,’2005-11-30′) = 0    -–‘2005-11-30’    //產生的id

 

應改為:

select id from t where name like ‘abc%‘select id from t where createdate >= ‘2005-11-30‘ and createdate < ‘2005-12-1‘

 

9、不要在 where 子句中的“=”左邊進行函數、算術運算或其他運算式運算，否則系統將可能無法正確使用索引。 10、在使用索引欄位作為條件時，如果該索引是複合索引（多列索引），那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓欄位順序與索引順序相一致。 11、索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要謹慎考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。 12、應儘可能的避免更新 clustered 索引資料列，因為 clustered 索引資料列的順序就是表記錄的實體儲存體順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引資料列，那麼需要考慮是否應將該索引建為 clustered 索引。 13、盡量避免向用戶端返回大資料量，若資料量過大，應該考慮相應需求是否合理。 14、MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列建立複合索引。

MySQL 索引建立原則及注意事項