《高性能 MySQL》笔记——第5章 创建高性能的索引（三）

声明：

本博客是本人在学习《高性能 MySQL》后整理的笔记，旨在方便复习和回顾，并非用作商业用途。

本博客已标明出处，如有侵权请告知，马上删除。

5.3 高性能的索引策略

5.3.5 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但 InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。术语 “聚簇” 表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况，本章后面将详细介绍）。

因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。本节我们主要关注 InnoDB，但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。

图 5-3 展示了聚簇索引中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但是节点页只包含了索引列。在这个案例中，索引列包含的是整数值。

一些数据库服务器允许选择哪个索引作为聚簇索引，但直到本书写作之际，还没有任何一个 MySQL 内建的存储引擎支持这一点。

InnoDB 将通过主键聚集数据，这也就是说图 5-3 中的 “被索引的列” 就是主键列。如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细地考虑聚簇索引，尤其是将表的存储引擎从 InnoDB 改成其他引擎的时候（反过来也一样）。

聚集的数据有一些重要的优点：

• 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 IO。
• 数据访问更快。聚簇索引将索引和数据保存在同一个 B-Tree 中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

同时，聚簇索引也有一些缺点：

• 聚簇数据最大限度的提高了 I/O 密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没有那么优势了。
• 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用 OPTIMIZE TABLE 命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临 “页分裂” 的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的 “行指针” 的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次 B-Tree 查找而不是一次。对于 InnoDB，自适应哈希索引能够减少这样的重复工作。

5.3.6 覆盖索引

通常大家会根据查询的 WHERE 条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是 WHERE 条件部分。索引确实是一种查找数据的高效方式，但是 MySQL 也可以使用索引来直接获取列的数据，这样就不需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要回表查询呢？如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 “覆盖索引”。

覆盖索引是非常有用的工具，能极大地提高性能。因为它只需扫描索引无需回表，会带来多少好处：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那 MySQL 就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于 I/O 密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中（这对于 MyISAM 尤其正确，因为 MyISAM 能压缩索引以变得更小）。
• 因为索引是按照列值顺存储的（至少在单个页内是如此），所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。对于某些存储引擎，例如 MyISAM 和 Percona XtraDB，甚至可以通过 OPTIMIZE 命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。
• 一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
• 由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 表特别有用。InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

在所有这些场景中，在索引中满足查询的成本一般比查询行要小得多。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

当发起一个被索引覆盖的查询时，在 EXPLAIN 的 Extra 列可以看到 “Using index” 的信息。例如，表 sakila.inventory 有一个多列索引（store_id，film_id）。MySQL 如果只需访问这两列，就可以使用这个索引做覆盖索引，如下所示：

mysql> EXPLAIN SELECT store_id, film_id FROM inventory\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_store_id_film_id
      key_len: 3
          ref: NULL
         rows: 4581
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL 查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了 WHERE 条件中的字段，但不是整个查询涉及的字段。如果条件为假（false），MySQL 5.5 和更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

来看看为什么会发生这样的情况，以及如何重写查询以解决该问题。从下面的查询开始：

这里索引无法覆盖该查询，有两个原因：

• 没有任何索引能够覆盖这个查询。因为查询从表中选择了所有的列，而没有任何索引覆盖了所有的列。不过，理论上 MySQL 还有一个捷径可以利用：WHERE 条件中的列是有索引可以覆盖的，因此 MySQL 可以使用该索引找到对应的 actor 并检查 title 是否匹配，过滤之后再读取需要的数据行。
• MySQL 不能在索引中执行 LIKE 操作。这是底层存储引擎 API 的限制，MySQL 5.5 和更早的版本中只允许在索引中做简单比较操作（例如等于、不等于以及大于)。MySQL 能在索引中做最左前缀匹配的 LIKE 比较，因为该操作可以转换为简单的比较操作，但是如果是通配符开头的 LIKE 查询，存储引擎就无法做比较匹配。这种情况下，MySQL 服务器只能提取数据行的值而不是索引值来做比较。

也有办法可以解决上面说的两个问题，需要重写查询并巧妙地设计索引。先将索引扩展至覆盖三个数据列（artist，title，prod_id），然后按如下方式重写查询：

我们把这种方式叫做延迟关联（deferred join），因为延迟了对列的访问。在查询的第一阶段 MySQL 可以使用覆盖索引，在 FROM 子句的子查询中找到匹配的 prod_id，然后根据这些 prod_id 值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好。

这样优化的效果取决于 WHERE 条件匹配返回的行数。假设这个 products 表有 100 万行，我们来看一下上面两个查询在三个不同的数据集上的表现，每个数据集都包含 100 万行：

1. 第一个数据集，Sean Carrey 出演了 30 000 部作品，其中有 20 000 部的标了 Apollo。
2. 第二个数据集，Sean Carrey 出演了 30 000 部作品，其中 40 部的标题中包含了 Apollo。
3. 第三个数据集，Sean Carrey 出演了 50 部作品，其中 10 部的标题中包含了 Apollo。

使用上面的三种数据集来测试两种不同的查询，得到的结果如表 5-2 所示。

下面是对结果的分析：

• 在示例 1 中，查询返回了一个很大的结果集，因此看不到优化的效果。大部分时间都花在读取和发送数据上了。
• 在示例 2 中，经过索引过滤，尤其是第二个条件过滤后只返回了很少的结果集，优化的效果非常明显：在这个数据集上性能提高了 5 倍，优化后的查询的效率主要得益于只需要读取 40 行完整数据行，而不是原查询中需要的 30 000 行。
• 在示例 3 中，显示了子查询效率反而下降的情况。因为索引过滤时符合第一个条件的结果集已经很小，所以子查询带来的成本反而比从表中直接提取完整行更高。

在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以更进一步优化 InnoDB。回想一下，InnoDB 的二级索引的叶子节点都包含了主键的值，这意味着 InnoDB 的二级索引可以有效地利用这些 “额外” 的主键列来覆盖查询。

例如，sakila.actor 使用 InnoDB 存储引擎，并在 last_name 字段有二级索引，虽然该索引的列不包括主键 actor_id，但也能够用于对 actor_id 做覆盖查询：

5.3.7 使用索引扫描来做排序

MySQL 有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描。如果 EXPLAIN 出来的 type 列的值为 “index”，则说明 MySQL 使用了索引扫描来做排序（不要和 Extra 列的 “Using index” 搞混淆了）。

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机 I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在 IO 密集型的工作负载时。

MySQL 可以使用同一个索引既满足排序，又用于查找行。因此，如果可能设计索引时应该尽可能地同时满足这两种任务，这样是最好的。

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL 才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当 ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL 都需要执行排序操作，而无法利用索引排序。

有一种情况下 ORDER BY 子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果 WHERE 子句或者 JOIN 子句中对这些列指定了常量，就可以 “弥补” 索引的不足。

例如，Sakila 示例数据库的表 rental 在列（rental_date, inventory_id, customer_id）上有名为 rental_date 的索引。

CREATE TABLE `rental` (
  ...
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  ...
);

MySQL 可以使用 rental_date 索引为下面的查询做排序，从 EXPLAIN 中可以看到没有出现文件排序（filesort）操作：

mysql> EXPLAIN SELECT rental_id, staff_id FROM rental
    -> WHERE rental_date = '2005-05-25'
    -> ORDER BY inventory_id, customer_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: rental_date
          key: rental_date
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

即使 ORDER BY 子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数。

还有更多可以使用索引做排序的查询示例。下面这个查询可以利用索引排序，是因为查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：

... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;

下面这个查询也没问题，因为 ORDER BY 使用的两列就是索引的最左前缀：

... WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id;

下面是一些不能使用索引做排序的查询：

• 下面这个查询使用了两种不同的排序方向，但是索引列都是正序排序的：

  ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;
  

• 下面这个查询的 ORDER BY 子句中引用了一个不在索引中的列：

  ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;
  

• 下面这个查询的 WHERE 和 ORDER BY 中的列无法组合成索引的最左前缀：

  ... WHERE rental_date = '2005-05-25' ORDER BY customer_id;
  

• 下面这个查询在索引列的第一列上是范围条件，所以 MySQL 无法使用索引的其余列：

  ... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;
  

• 这个查询在 inventory_id 列上有多个等于条件。对于排序来说，这也是一种范围查询：

  ... WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_id;
  

下面这个例子理论上是可以使用索引进行关联排序的，但由于优化器在优化时将 film_actor 表当作关联的第二张表，所以实际上无法使用索引：

mysql> EXPLAIN SELECT actor_id, title FROM film_actor
    -> INNER JOIN film USING(film_id) ORDER BY actor_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.film.film_id
         rows: 5
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.01 sec)

使用索引做排序的一个最重要的用法是当查询同时有 ORDER BY 和 LIMIT 子句的时候。后面我们会具体介绍这些内容。

5.3.8 压缩（前缀压缩）索引

MyISAM 使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。

MyISAM 压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块中的第一个值是 “perform”，第二个值是 “performance”，那么第二个值的前缀压缩后存储的是类似 “7,ance” 这样的形式。MyISAM 对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以 MyISAM 查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描——例如ORDER BY DESC——就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。

测试表明，对于 CPU 密集型应用，因为扫描需要随机查找，压缩索引使得 MyISAM 在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在 CPU 内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是 I/O 密集型应用，对某些查询带来的好处会比成本多很多。

可以在 CREATE TABLE 语句中指定 PACK_KEYS 参数来控制索引压缩的方式。

5.3.9 冗余和重复索引

MySQL 允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL 需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

有时会在不经意间创建了重复索引，例如下面的代码：

CREATE TABLE test(
  ID INT NOT NULL PRIMARY KEY,
  A INT NOT NULL,
  B INT NOT NULL,
  UNIQUE(ID),
  INDEX(ID),
) ENGINE=InnoDB;

一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。事实上，MySQL 的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求。

冗余索引和重复索引有一些不同。如果创建了索引 (A,B)，再创建索引 (A) 就是冗余索引，因为这只是前一个索引的前缀索引。因此索引 (A,B) 也可以当做索引 (A) 来使用（这种冗余只是对 B-Tree 索引来说的）。但是如果再创建索引 (B,A)，则不是冗余索引，索引 (B) 也不是，因为 B 不是索引 (A,B) 的最左前缀。另外，其他不同类型的索引（例如哈希索引或者全文索引）也不会是 B-Tree 索引的冗余索引，而无论覆盖的索引列是什么。

冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引 (A,B) 而不是扩展已有的索引 (A)。还有一种情况是将一个索引扩展为 (A,ID)，其中 ID 是主键，对于 InnoDB 来说主键列已经包含在二级索引中了，所以这也是冗余的。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

例如，如果在整数列上有一个索引，现在需要额外增加一个很长的 VARCHAR 列来扩展该索引，那性能可能会急剧下降。特别是有查询把这个索引当做覆盖索引，或者这是 MyISAM 表并且有很多范围查询（由于 MyISAM 的前缀压缩）的时候。

有一个 userinfo 表，这个表有 1000 000 行，对每个 state_id 值大概有 20 000 条记录。在 state_id 列有一个索引对下面的查询有用，假设查询名为 Q1：

SELECT count(*) FROM userinfo WHERE state_id=5;

一个简单的测试表明该查询的执行速度大概是每秒 115 次（QPS）。还有一个相关查询需要检索几个列的值，而不是只统计行数，假设名为 Q2：

SELECT state_id,city,address FROM userinfo WHERE state_id=5;

对于这个查询，测试结果 QPS 小于 10。提升该查询性能的最简单办法就是扩展索引为 (state_id,city,address)，让索引能覆盖查询：

ALTER TABLE userinfo DROP KEY state_id, ADD KEY state_id_2(state_id,city,address);

索引扩展后，Q2 运行得更快了，但是 Q1 却变慢了。如果我们想让两个查询都变得更快，就需要两个索引，尽管这样一来原来的单列索引是冗余的了。表 5-3 显示这两个查询在不同索引策略下的详细结果，分别使用 MyISAM 和 InnoDB 存储引擎。注意到只有 state_id_2 索引时，InnoDB 引擎上的查询 Q1 的性能下降并不明显，这是因为 InnoDB 没有使用索引压缩。

有两个索引的缺点是索引成本更高。表 5-4 显示了向表中插入 100 万行数据所需里时间。

可以看到，表中的索引越多插入速度越慢。一般来说，增加新索引将会导致 INSERT、UPDATE、DELETE 等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。

5.3.10 未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不使用的索引。这样的索引完全是累赘，建议考虑删除。

5.3.11 索引和锁

索引可以让查询锁定更少的行，如果查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然 InnoDB 的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；其次，锁定超过需要的行会增加锁争用并减少并发性。

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

通过下面的例子再次使用数据库 Sakila 很好的解释这些情况：

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 < 5 AND actor_id <> 1 FOR UPDATE;
+----------+
| actor_id |
+----------+
|        2 |
|        3 |
|        4 |
+----------+
3 rows in set (0.01 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\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

换句话说，底层存储引擎的操作是 “从索引的开头获取满足条件 actor_id < 5 的记录”，服务器并没有告诉 InnoDB 可以过滤第 1 行的 WHERE 条件。注意到 EXPLAIN 的 Extra 出现 “Using Where” 表示 MySQL 服务器将存储引擎返回行以后再应用 WHERE 过滤条件。

下面的第二个查询就能证明第一行确实已经被锁定（重新开启一个 MySQL 的控制台），尽管第一个查询的结果并没有这个第一行。保持第一个连接的打开，然后开启第二个连接并执行如下查询：

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;

这个查询将会挂起，直到第一个事物释放第一行的锁。这个行为对于基于语句的复制的正常运行来说是必要的。

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