Schema的优化和索引 - 高性能的索引策略 - 索引和锁

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