高效能mysql學習心得
記得第一次看這本書的時候，我是一邊打瞌睡一邊看。。。現在回頭再看一遍，受益匪淺，於是摘抄一下第五章建立高效能索引的部分內容。
雜湊索引
雜湊索引（hash index）基於雜湊表實現，只有精確匹配索引所有列的查詢才有效 。對於每一行資料，儲存引擎都會對所有的索引列計算一個雜湊碼（hash code），雜湊碼是一個較小的值，並且不同鍵值的行計算出來的雜湊碼也不一樣。雜湊索引將所有的雜湊碼儲存在索引中，同時在雜湊表中儲存指向每個資料行的指標。
在MySQL中，只有Memory引擎顯式支援雜湊索引。這也是Memory引擎表的預設索引型別，Memory引擎同時也支援B-Tree索引。值得一提的是，Memory引擎是支援非唯一雜湊索引的，這在資料庫世界裡面是比較與眾不同的。如果多個列的雜湊值相同，索引會以連結串列的方式存放多個記錄指標到同一個雜湊條目中。
雜湊索引的限制
因為索引自身只需儲存對應的雜湊值，所以索引的結構十分緊湊，這也讓雜湊索引查詢的速度非常快。然而，雜湊索引也有它的限制：
雜湊索引只包含雜湊值和行指標，而不儲存欄位值，所以不能使用索引中的值來避免讀取行。不過，訪問記憶體中的行的速度很快，所以大部分情況下這一點對效能的影響並不明顯。
雜湊索引資料並不是按照索引值順序儲存的，所以也就無法用於排序。
雜湊索引也不支援部分索引列匹配查詢，因為雜湊索引始終是使用索引列的全部內容來計算雜湊值的。例如，在資料列（A,B）上建立雜湊索引，如果查詢只有資料列A，則無法使用該索引。
雜湊索引只支援等值比較查詢，包括=、IN()、<=>（注意<>和<=>是不同的操作）。也不支援任何範圍查詢，例如WHERE price>100 。
訪問雜湊索引的資料非常快，除非有很多雜湊衝突（不同的索引列值卻有相同的雜湊值）。當出現雜湊衝突的時候，儲存引擎必須遍歷連結串列中所有的行指標，逐行進行比較，直到找到所有符合條件的行。
如果雜湊衝突很多的話，一些索引維護操作的代價也會很高。例如，如果在某個選擇性很低（雜湊衝突很多）的列上建立雜湊索引，那麼當從表中刪除一行時，儲存引擎需要遍歷對應雜湊值的連結串列中的每一行，找到並刪除對應行的引用，衝突越多，代價越大。
ps:雖然採用較短的名稱可以節省儲存空間，但由於鍵值的長度往往遠遠大於鍵名的長度，所以這部分的節省大部分情況下並不如可讀性來得重要。
自適應雜湊索引
InnoDB引擎有一個特殊的功能叫做“自適應雜湊索引（adaptive hash index）”。當InnoDB注意到某些索引值被使用得非常頻繁時，它會在記憶體中基於B-Tree索引之上再建立一個雜湊索引，這樣就讓B-Tree索引也具有雜湊索引的一些優點，比如快速的雜湊查詢。這是一個完全自動的、內部的行為，使用者無法控制或者配置，不過如果有必要，完全可以關閉該功能。
覆蓋索引
通常大家都會根據查詢的WHERE 條件來建立合適的索引，不過這只是索引最佳化的一個方面。設計優秀的索引應該考慮到整個查詢，而不單單是WHERE 條件部分。索引確實是一種查詢資料的高效方式，但是MySQL也可以使用索引來直接獲取列的資料，這樣就不再需要讀取資料行。如果索引的葉子節點中已經包含要查詢的資料，那麼還有什麼必要再回表查詢呢?
如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱之為“覆蓋索引”。
覆蓋索引是非常有用的工具，能夠極大地提高效能。
考慮一下如果查詢只需要掃描索引而無須回表，會帶來多少好處：
索引條目通常遠小於資料行大小，所以如果只需要讀取索引，那MySQL就會極大地減少資料訪問量。這對快取的負載非常重要，因為這種情況下響應時間大部分花費在資料複製上。覆蓋索引對於I/O密集型的應用也有幫助，因為索引比資料更小，更容易全部放入記憶體中（這對於MyISAM尤其正確，因為MyISAM能壓縮索引以變得更小）。
因為索引是按照列值順序儲存的（至少在單個頁內是如此），所以對於I/O密集型的範圍查詢會比隨機從磁碟讀取每一行資料的I/O要少得多。對於某些儲存引擎，例如MyISAM和PerconaXtraDB，甚至可以透過OPTIMIZE命令使得索引完全順序排列，這讓簡單的範圍查詢能使用完全順序的索引訪問。
一些儲存引擎如MyISAM在記憶體中只快取索引，資料則依賴於作業系統來快取，因此要訪問資料需要一次系統呼叫。這可能會導致嚴重的效能問題，尤其是那些系統呼叫佔了資料訪問中的最大開銷的場景。
由於InnoDB的聚簇索引，覆蓋索引對InnoDB表特別有用。InnoDB的二級索引在葉子節點中儲存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。
索引和鎖
索引可以讓查詢鎖定更少的行。如果你的查詢從不訪問那些不需要的行，那麼就會鎖定更少的行，從兩個方面來看這對效能都有好處。首先，雖然InnoDB的行鎖效率很高，記憶體使用也很少，但是鎖定行的時候仍然會帶來額外開銷；其次，鎖定超過需要的行會增加鎖爭用並減少併發性。
InnoDB只有在訪問行的時候才會對其加鎖，而索引能夠減少InnoDB訪問的行數，從而減少鎖的數量。但這隻有當InnoDB在儲存引擎層能夠過濾掉所有不需要的行時才有效。如果索引無法過濾掉無效的行，那麼在InnoDB檢索到資料並返回給伺服器層以後，MySQL伺服器才能應用WHERE 子句 。這時已經無法避免鎖定行了：InnoDB已經鎖住了這些行，到適當的時候才釋放。在MySQL 5.1和更新的版本中，InnoDB可以在伺服器端過濾掉行後就釋放鎖，但是在早期的MySQL版本中，InnoDB只有在事務提交後才能釋放鎖。
透過下面的例子再次使用資料庫Sakila很好地解釋了這些情況：
mysql> SET AUTOCOMMIT=0;
mysql> BEGIN;
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE;//排他鎖
這條查詢僅僅會返回2～4之間的行，但是實際上獲取了1～4之間的行的排他鎖。InnoDB會鎖住第1行，這是因為MySQL為該查詢選擇的執行計劃是索引範圍掃描：
換句話說，底層儲存引擎的操作是“從索引的開頭開始獲取滿足條件actor_id<5 的記錄”，伺服器並沒有告訴InnoDB可以過濾第1行的WHERE 條件。注意到EXPLAIN 的Extra 列出現了"Usingwhere"，這表示MySQL伺服器將儲存引擎返回行以後再應用WHERE過 濾條件。
下面的第二個查詢就能證明第1行確實已經被鎖定，儘管第一個查詢的結果中並沒有這個第1行。保持第一個連線開啟，然後開啟第二個連線並執行如下查詢：
mysql> SET AUTOCOMMIT=0;
mysql> BEGIN;
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id = 1 FOR UPDATE;//排他鎖
這個查詢將會掛起，直到第一個事務釋放第1行的鎖。這個行為對於基於語句的複製的正常執行來說是必要的。
就像這個例子顯示的，即使使用了索引，InnoDB也可能鎖住一些不需要的資料。如果不能使用索引查詢和鎖定行的話問題可能會更糟糕，MySQL會做全表掃描並鎖住所有的行，而不管是不是需要。
關於InnoDB、索引和鎖有一些很少有人知道的細節：InnoDB在二級索引上使用共享（讀）鎖，但訪問主鍵索引需要排他（寫）鎖。這消除了使用覆蓋索引的可能性，並且使得SELECT FOR UPDATE 比LOCK IN SHARE MODE 或非鎖定查詢要慢很多。
本作品採用《CC 協議》，轉載必須註明作者和本文連結