深入分析mysql索引何時建立比較好

索引可以提高資料的檢索效率，也可以降低資料庫的IO成本，並且索引還可以降低資料庫的排序成本。排序分組操作主要消耗的就是CPU資源和記憶體，所以能夠在排序分組操作中好好的利用索引將會極大地降低CPU資源的消耗。本篇文章將簡單的分析一下如何正確建立MySQL資料索引。

如何判定是否需要建立索引？

1、較頻繁地作為查詢條件的欄位

這個都知道。什麼是教頻繁呢？分析你執行的所有SQL語句。最好將他們一個個都列出來。然後分析，發現其中有些欄位在大部分的SQL語句查詢時候都會用到，那麼就果斷為他建立索引。

2、唯一性太差的欄位不適合建立索引

什麼是唯一性太差的欄位。如狀態欄位、類型欄位。那些只儲存固定幾個值的欄位，例如使用者登入狀態、訊息的status等。這個涉及到了索引掃描的特性。例如：通過索引尋找索引值為A和B的某些資料，通過A找到某條相符合的資料，這條資料在X頁上面，然後繼續掃描，又發現符合A的資料出現在了Y頁上面，那麼儲存引擎就會丟棄X頁面的資料，然後儲存Y頁面上的資料，一直到尋找完所有對應A的資料，然後尋找B欄位，發現X頁面上面又有對應B欄位的資料，那麼他就會再次掃描X頁面，等於X頁面就會被掃描2次甚至多次。以此類推，所以同一個資料頁可能會被多次重複的讀取，丟棄，在讀取，這無疑給儲存引擎極大地增加了IO的負擔。

3、更新太頻繁地欄位不適合建立索引

當你為這個欄位建立索引時候，當你再次更新這個欄位資料時，資料庫會自動更新他的索引，所以當這個欄位更新太頻繁地時候那麼就是不斷的更新索引，效能的影響可想而知。大概被檢索幾十次會更新一次的欄位才比較符合建立索引的規範。而如果一個欄位同一個時間段內被更新多次，那麼果斷不能為他建立索引。

4、不會出現在where條件中的欄位不該建立索引

注意：

並非所有的資料庫都以相同的方式使用索引。作為通用規則，只有當經常查詢索引列中的資料時，才需要在表上建立索引。索引佔用磁碟空間，並且降低添加、刪除和更新行的速度。在多數情況下，索引用於資料檢索的速度優勢大大超過它的不足之處。但是，如果應用程式非常頻繁地更新資料或磁碟空間有限，則可能需要限制索引的數量。

可以基於資料庫表中的單列或多列建立索引。多列索引使您可以區分其中一列可能有相同值的行。

如果經常同時搜尋兩列或多列或按兩列或多列排序時，索引也很有協助。例如，如果經常在同一查詢中為姓和名兩列設定判據，那麼在這兩列上建立多列索引將很有意義。

確定索引的有效性：

● 檢查查詢的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以選擇的對象。

● 對新索引進行實驗以檢查它對執行查詢效能的影響。

● 考慮已在表上建立的索引數量。最好避免在單個表上有很多索引。

● 檢查已在表上建立的索引的定義。最好避免包含共用列的重疊索引。

● 檢查某列中唯一資料值的數量，並將該數量與表中的行數進行比較。比較的結果就是該列的可選擇性，這有助於確定該列是否適合建立索引，如果適合，確定索引的類型。

MySQL何時使用索引

對一個鍵碼使用>, >=, =, <, <=, IF NULL和BETWEEN

SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
SELECT * FROM table_name WHERE key_part1 IS NULL;
當使用不以萬用字元開始的LIKE

SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
在進行連接時從另一個表中提取行時

SELECT * from t1,t2 where t1.col=t2.key_part
找出指定索引的MAX()或MIN()值

SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
一個鍵碼的首碼使用ORDER BY或GROUP BY

SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3
在所有用在查詢中的列是鍵碼的一部分時間

SELECT key_part3 FROM table_name WHERE key_part1=1

MySQL何時不使用索引

如果MySQL能估計出它將可能比掃描整張表還要快時，則不使用索引。例如如果key_part1均勻分布在1和100之間，下列查詢中使用索引就不是很好：

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
如果使用HEAP表且不用=搜尋所有鍵碼部分。

在HEAP表上使用ORDER BY。

如果不是用鍵碼第一部分

SELECT * FROM table_name WHERE key_part2=1
如果使用以一個萬用字元開始的LIKE

SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
搜尋一個索引而在另一個索引上做ORDER BY

SELECT * from table_name WHERE key_part1 = # ORDER BY key2

建立MySql資料庫索引需要注意的幾個問題

1.建立索引的時機：若表中的某欄位出現在select、過濾、排序條件中，為該欄位建立索引是值得的。
2.對於like %xxx的模糊查詢，普通的索引是無法滿足的，需要建立全文索引。
3.對於有多個條件的，比如: "...where a=xx and b=yy","...where a=xx order by b","...where a=xx group by b"。需要使用複合式索引。但是複合式索引只能在SQL語句中滿足"最左首碼"的條件下使用。且複合式索引有一些副作用，如索引尺寸可能比資料本身大，因為複合式索引的組合條目多。所以在實際應用中，要量身定做，使用慢查詢分析工具分析。
4.開啟索引緩衝，直接在記憶體中尋找索引，不用再磁碟中。
5.建立索引是有代價的，當update、delete語句執行時，會使得索引更新，將耗掉更多的時間。可以使用mysqlreport報告，瞭解select、update、delete、insert、replace各語句所佔的百分比.