Mysql索引分析：適合建索引？不適合建索引？【轉】

標籤：io   ar   使用   sp   on   資料   div   問題   log   

資料庫建立索引常用的規則如下： 1、表的主鍵、外鍵必須有索引； 2、資料量超過300的表應該有索引； 3、經常與其他表進行串連的表，在串連欄位上應該建立索引； 4、經常出現在Where子句中的欄位，特別是大表的欄位，應該建立索引； 5、索引應該建在選擇性高的欄位上； 6、索引應該建在小欄位上，對於大的文字欄位甚至超長欄位，不要建索引； 7、複合索引的建立需要進行仔細分析；盡量考慮用單欄位索引代替：   A、正確選擇複合索引中的主欄欄位，一般是選擇性較好的欄位；   B、複合索引的幾個欄位是否經常同時以AND方式出現在Where子句中？單欄位查詢是否      極少甚至沒有？如果是，則可以建立複合索引；否則考慮單欄位索引；   C、如果複合索引中包含的欄位經常單獨出現在Where子句中，則分解為多個單欄位索引；   D、如果複合索引所包含的欄位超過3個，那麼仔細考慮其必要性，考慮減少複合的欄位；   E、如果既有單欄位索引，又有這幾個欄位上的複合索引，一般可以刪除複合索引； 8、頻繁進行資料操作的表，不要建立太多的索引； 9、刪除無用的索引，避免對執行計畫造成負面影響； 以上是一些普遍的建立索引時的判斷依據。  索引的建立必須謹慎，對每個索引的必要性都應該經過仔細分析，要有建立的依據。   因為太多的索引與不充分、不正確的索引對效能都毫無益處：在表上建立的每個索引都會增加儲存開銷，索引對於插入、刪除、更新操作也會增加處理上的開銷。 另外，過多的複合索引，在有單欄位索引的情況下，一般都是沒有存在價值的；相反，還會降低資料增加刪除時的效能，特別是對頻繁更新的表來說，負面影響更大。     總的來說，小型表肯定不建索引，  或者資料庫記錄在億條資料級以上，還是建議使用非關係型資料庫。  還有些特殊欄位的資料庫，比如BLOB，CLOB欄位肯定也不適合建索引。  其實這個問題更感覺偏向於做軟體項目的一種經驗。

對千萬級MySQL資料庫建立索引的事項及提高效能的手段

一、注意事項：
首先，應當考慮資料表空間和磁碟空間是否足夠。我們知道索引也是一種資料，在建立索引的時候勢必也會佔用大量資料表空間。因此在對一大表建立索引的時候首先應當考慮的是空間容量問題。
其次，在對建立索引的時候要對錶進行加鎖，因此應當注意操作在業務閒置時候進行。
二、效能調整方面：
首當其衝的考慮因素便是磁碟I/O。物理上，應當盡量把索引與資料分散到不同的磁碟上（不考慮陣列的情況）。邏輯上，資料資料表空間與索引資料表空間分開。這是在建索引時應當遵守的基本準則。
其次，我們知道，在建立索引的時候要對錶進行全表的掃描工作，因此，應當考慮調大初始化參數db_file_multiblock_read_count的值。一般設定為32或更大。

再次，建立索引除了要進行全表掃描外同時還要對資料進行大量的排序操作，因此，應當調整排序區的大小。
    9i之前，可以在session層級上加大sort_area_size的大小，比如設定為100m或者更大。
    9i以後，如果初始化參數workarea_size_policy的值為TRUE，則排序區從pga_aggregate_target裡自動分配獲得。
最後，建立索引的時候，可以加上nologging選項。以減少在建立索引過程中產生的大量redo，從而提高執行的速度。

MySql在建立索引最佳化時需要注意的問題 設計好MySql的索引可以讓你的資料庫飛起來，大大的提高資料庫效率。設計MySql索引的時候有一下幾點注意： 1，建立索引 對於查詢佔主要的應用來說，索引顯得尤為重要。很多時候效能問題很簡單的就是因為我們忘了添加索引而造成的，或者說沒有添加更為有效索引導致。如果不加索引的話，那麼尋找任何哪怕只是一條特定的資料都會進行一次全表掃描，如果一張表的資料量很大而合格結果又很少，那麼不加索引會引起致命的效能下降。但是也不是什麼情況都非得建索引不可，比如性別可能就只有兩個值，建索引不僅沒什麼優勢，還會影響到更新速度，這被稱為過度索引。 2，複合索引 比如有一條語句是這樣的：select * from users where area=’beijing’ and age=22; 如果我們是在area和age上分別建立單個索引的話，由於mysql查詢每次只能使用一個索引，所以雖然這樣已經相對不做索引時全表掃描提高了很多效 率，但是如果在area、age兩列上建立複合索引的話將帶來更高的效率。如果我們建立了(area, age, salary)的複合索引，那麼其實相當於建立了(area,age,salary)、(area,age)、(area)三個索引，這被稱為最佳左首碼 特性。因此我們在建立複合索引時應該將最常用作限制條件的列放在最左邊，依次遞減。 3，索引不會包含有NULL值的列 只要列中包含有NULL值都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的預設值為NULL。 4，使用短索引 對串列進行索引，如果可能應該指定一個前置長度。例如，如果有一個CHAR(255)的 列，如果在前10 個或20 個字元內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。 5，排序的索引問題 mysql查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列建立複合索引。 6，like語句操作 一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “?a%” 不會使用索引而like “aaa%”可以使用索引。 7，不要在列上進行運算 select * from users where YEAR(adddate) 8，不使用NOT IN和操作 NOT IN和操作都不會使用索引將進行全表掃描。NOT IN可以NOT EXISTS代替，id3則可使用id>3 or id

 

Mysql索引分析：適合建索引？不適合建索引？【轉】