MySQL索引的使用策略及優化有哪些

本篇內容介紹了“MySQL索引的使用策略及優化有哪些”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

五、索引的使用策略

什么時候要使用索引？

• 主鍵自動建立唯一索引；

• 經常作為查詢條件在WHERE或者ORDER BY 語句中出現的列要建立索引；

• 作為排序的列要建立索引；

• 查詢中與其他表關聯的字段，外鍵關系建立索引

• 高并發條件下傾向組合索引；

• 用于聚合函數的列可以建立索引，例如使用了max(column_1)或者count(column_1)時的column_1就需要建立索引

什么時候不要使用索引？

• 經常增刪改的列不要建立索引；

• 有大量重復的列不建立索引；

• 表記錄太少不要建立索引。只有當數據庫里已經有了足夠多的測試數據時，它的性能測試結果才有實際參考價值。如果在測試數據庫里只有幾百條數據記錄，它們往往在執行完第一條查詢命令之后就被全部加載到內存里，這將使后續的查詢命令都執行得非常快--不管有沒有使用索引。只有當數據庫里的記錄超過了1000條、數據總量也超過了MySQL服務器上的內存總量時，數據庫的性能測試結果才有意義。

索引失效的情況：

• 在組合索引中不能有列的值為NULL，如果有，那么這一列對組合索引就是無效的。

• 在一個SELECT語句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。

• LIKE操作中，'%aaa%'不會使用索引，也就是索引會失效，但是‘aaa%’可以使用索引。

• 在索引的列上使用表達式或者函數會使索引失效，例如：select * from users where YEAR(adddate)<2007，將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成：select * from users where adddate<’2007-01-01′。其它通配符同樣，也就是說，在查詢條件中使用正則表達式時，只有在搜索模板的第一個字符不是通配符的情況下才能使用索引。

• 在查詢條件中使用不等于，包括<符號、>符號和！=會導致索引失效。特別的是如果對主鍵索引使用！=則不會使索引失效，如果對主鍵索引或者整數類型的索引使用<符號或者>符號不會使索引失效。（經erwkjrfhjwkdb同學提醒，不等于，包括<符號、>符號和！，如果占總記錄的比例很小的話，也不會失效）

• 在查詢條件中使用IS NULL或者IS NOT NULL會導致索引失效。

• 字符串不加單引號會導致索引失效。更準確的說是類型不一致會導致失效，比如字段email是字符串類型的，使用WHERE email=99999 則會導致失敗，應該改為WHERE email='99999'。

• 在查詢條件中使用OR連接多個條件會導致索引失效，除非OR鏈接的每個條件都加上索引，這時應該改為兩次查詢，然后用UNION ALL連接起來。

• 如果排序的字段使用了索引，那么select的字段也要是索引字段，否則索引失效。特別的是如果排序的是主鍵索引則select * 也不會導致索引失效。

• 盡量不要包括多列排序，如果一定要，最好為這隊列構建組合索引；

六、索引的優化

1、最左前綴

索引的最左前綴和和B+Tree中的“最左前綴原理”有關，舉例來說就是如果設置了組合索引<col1,col2,col3>那么以下3中情況可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。

根據最左前綴原則，我們一般把排序分組頻率最高的列放在最左邊，以此類推。

2、帶索引的模糊查詢優化

在上面已經提到，使用LIKE進行模糊查詢的時候，'%aaa%'不會使用索引，也就是索引會失效。如果是這種情況，只能使用全文索引來進行優化（上文有講到）。

3、為檢索的條件構建全文索引，然后使用

SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);

4、使用短索引

對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的 列，如果在前10 個或20 個字符內，多數值是惟一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。

“MySQL索引的使用策略及優化有哪些”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！