「Mysql索引原理（十一）」索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。

首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外的开销；

其次，锁定超过需要的行会增加锁争用并减少并发性；

InnoDB，只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用where子句。这时已经无法避免锁定行了，InnoDB已经锁住了这些行，到适当的时候才释放。在MySQL5.1及以后版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早起版本中，InnoDB只有在事务提交后才能释放锁。

举例：

set autocommit=0;
begin;
select id from people where id<5 and id<>1 for update;

for update表示行锁，每次拿数据的时候都会给数据上锁，当别的线程想要拿数据时，就会阻塞。直到给数据上锁的线程将事务提交或者回滚。 这条查询仅仅返回24之间的行，但是实际上获取了14之间的行的排他锁。InnoDB会锁住第一行，这是因为Mysql为该查询选择的执行计划是索引范围扫描：

explain select id from people where id<5 and id<>1 for update;

换句话说，底层存储引擎的操作是“从索引的开头开始获取满足条件id<5的记录”，服务器并没有告诉InnoDB可以过滤第一行的WHERE条件。注意到explain的extra出现了useing where，这表示mysql服务器将存储引擎返回行以后再应用where过滤条件。 下面的第二个查询就能证明第1行确实已经被锁定了，尽管第一个查询的结果中并没有这个第1行。

set autocommit=0;
begin;
select id from people where id=1;

这个查询将会挂起，直到第一个事务释放第一行的锁。

就像这个例子显示的，即使使用了索引，InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话问题可能会更糟糕，MySQL会做全表扫描并锁住所有的行，而不管是不是需要。

关于InnoDB、索引和锁有一些很少有人知道的细节：InnoDB在二级索引上使用共享锁。