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

mysql索引是可以提高數據庫的查詢速度了，但是需要合理的設置mysql索引才可以達到我們要的需求了，下面小編來為各位整理一篇關於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各語句所占的百分比.