MySQL中innodb的索引和锁

  innodb是支持行锁的。并且，索引可以让查询锁定更少的行。

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

1.   虽然innodb的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外的开销
2.   锁定超过需要的行会增加锁征用并减少并发性。

  innodb只有在访问行的时候才会对其加锁，而索引能够减少innodb访问的行数，从而减少锁的数量。

  但这只有当innodb在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在innodb检索到数据并返回给服务层以后，MySQL服务器才能应用where子句。这时已经无法避免锁定行了：innodb已经锁定了这些行，到适当的时候才释放。innodb可以在服务器端过滤掉行后就释放。

  通过sakila数据库可以解释这些情况：

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select actor_id from actor where actor_id < 5 and actor_id <> 1 for update;
+----------+
| actor_id |
+----------+
|        2 |
|        3 |
|        4 |
+----------+
3 rows in set (0.00 sec)

  这条查询仅仅会返回2-4行之间的数据，但是实际上获取了1-4之间的行的排他锁。innodb会锁住第1行，这时因为MySQL为该查询选择的执行计划是索引范围的扫描：

mysql> explain select actor_id from actor where actor_id < 5 and actor_id <> 1 for update;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | actor | NULL       | range | PRIMARY       | PRIMARY | 2       | NULL |    4 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

 其中的type为range说明进行的是范围扫描。由于MySQL仅仅支持最左前缀索引，所以会在引擎层返回actor_id<5的索引(所以会锁住actor_id小于5的全部行)，然后在服务器层对数据进行where(这个where过滤只能在服务器层使用，但是高性能MySQL的181页中有个注释，说是在MySQL5.6对于这个问题有帮助，我的是5.7，还是锁住了第二个查询。所以不是很懂什么意思)的筛选。

  第二个查询就能证明第一行确实已经被锁定(重新开启一个MySQL的consule)。保持第一个连接的打开，然后开启第二个连接并执行如下查询：

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select actor_id from actor where actor_id = 1 for update;

  这个查询会一直堵塞，直到第一个查询commit。

 第一个查询commit之后，第二个查询的结果出现，如下：

+----------+
| actor_id |
+----------+
|        1 |
+----------+
1 row in set (12.59 sec)