Schema的最佳化和索引

InnoDB中，索引所扮演的角色是非常重要的。因為它們可以能讓語句鎖定更少的行。這是個要考慮的事情，因為在MySQL5.0 InnoDB中一個事物提前之前，是不會釋放鎖的。

如果查詢語句不會檢索它們不需要的行。它們將鎖定更少的行。並且對於效能有提高，原因有二：首先，即使InnoDB行鎖是非常有效率的並且使用更少的記憶體，但是行鎖也會消耗一定的資源。其次，鎖定很多的行就提高了鎖的競爭並且降低了並發。

僅當InnoDB訪問行的時候，才對它們加鎖，並且一個索引可以降低InnoDB所要訪問的行，因此也會降低鎖。然而，這種情況只適用於在儲存引擎層級中，InnoDB過濾了不期望的行。如果索引不允許InnoDB那麼做，MySQL伺服器就會在InnoDB取到這些值並且返回服務層之後，應用WHERE條件了。這種情況下，避免行的鎖定就太晚了：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;
+----------+
| actor_id |
+----------+
| 　 　 　 　2 |
| 　 　 　 　3 |
| 　 　 　 　4 |
+----------+

這個查詢返回了2到4行，但是實際上它已經獲得了1到4行的獨佔鎖。InnoDB鎖定了第一行，因為這個語句是個索引範圍讀取：

mysql> EXPLAIN SELECT actor_id FROM sakila.actor
　　 　-> WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE;
+----+-------------+-------+-------+---------+--------------------------+
| id | select_type | table | type 　| key 　 　 | Extra 　 　 　 　 　 　 　 　 　 　|
+----+-------------+-------+-------+---------+--------------------------+
| 　1 | SIMPLE 　 　 　| actor | range | PRIMARY | Using where; Using index |
+----+-------------+-------+-------+---------+--------------------------+

換句話說，這個低層級的儲存引擎操作是“從索引開始並且擷取所有的行直到actor_id<5為false”。伺服器是不會告訴InnoDB,WHERE　actor_id　<>1的條件。來看下EXTRA列的Using where。這就說明了在儲存引擎返回行之後，MySQL伺服器用WHERE進行了過濾。

下面的語句證明了第一行已經被鎖了，即使它不會出現在第一個查詢結果之中。丟掉第一個串連，開始第二個串連執行下列語句。

mysql> SET AUTOCOMMIT=0;
mysql> BEGIN;
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id = 1 FOR UPDATE;

這個查詢會被掛起。等待第一個事物是否第一行的鎖。這個行為是為了保證基於語句級的複製是正確的。（在複製的一節會說道。）

就像這個例子所顯示的，即使使用了索引，InnoDB還回會鎖定它並不是真正需要的行。當不使用索引去尋找和鎖定行，這樣的問題會更嚴重：如果這個語句沒有索引，不管需不需要，MySQL都會掃描整張表並且鎖定每個行。

下面談一下InnoDB，索引和鎖的一些鮮為人知的細節：InnoDB會把共用鎖定放置在次要索引上，但是獨佔鎖需要訪問主鍵。這就降低了使用覆蓋索引的可能性並且會導致SELECT FOR UPDATE 慢於LOCK IN SHARE MODE 或者沒有鎖的查詢。