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 或者没有锁的查询。