MySQL优化二：如何创建高性能索引之高性能的索引策略

正确的创建和使用索引是实现高性能查询的基础。前面已经介绍了各种类型的索引及其对应的优缺点。现在我们一起来看看如果真正的发挥这些索引的优势。

高效的选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。使用哪个索引，以及如何评估选择不同索引的性能影响的技巧则需要持续不断的学习。

一 独立的列

我们通常会看到一些查询不当的使用索引，或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。

例：select id from test_table where id + 1 = 3;

凭肉眼很容易看出表达式其实等价于id = 2,但是MySQL无法自动解析这个方程式。这是完全的用户行为。我们应该养成简化where条件的习惯，始终将索引列单独放在比较符号的右侧。

下面是另一个常见错误：select id from test_table where to_days(current_date) - to_days(date_col) <= 10;

二 前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变大且慢。一个策略是前面提到过的模拟哈希索引。但是有时候这样做还不够。

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值也称为基数（cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列的前缀的前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长以便节约索引空间。前缀应该足够长，以使得前缀索引的选择性接近与索引整个列。换句话说，前缀的技术应该接近与完整的技术。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。我们可以使用下面sql来进行测试

select count(*) as num,left(address,3) as name from test_user group by name order by cnt limit 10;

然后慢慢的增加前缀数量直到所得的num数量差距之间不是很大，并且数量在一个可接受的范围。

计算合适的前缀长度的另一个办法就是计算完整列的选择性，并是前缀的选择性接近与完整列的选择性。

例：select count(distinct address)/count(*) from test_table;

通常来说这个例子中如果前缀的选择性能够接近0.031，基本上就可以使用了（例外情况除外）。可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。下面给出了如何在同一个查询中计算不同前缀长度的选择性

select count(distinct left(address,3))/count(*) ,

          count(distinct left(address,4))/count(*) ,

          count(distinct left(address,5))/count(*) ,

          count(distinct left(address,6))/count(*) ,

          count(distinct left(address,7))/count(*) ,

from test_table;

当然只看平均选择性是不够的，也有例外情况，需要考虑到最坏的情况下的选择性。平均选择会让你以为一个较短的长度就足够了，但如果数据分布不均与，可能就会碰到个坑。比如你查找一个长度limit 10 的时候看到第一个和最后一个的数量相差超过1倍以上，这就是一个很明显的不均匀情况。那么这些值的选择性就比平均选择性要低的多。

如何创建前缀索引：

alter table test_table add key(address(7));

前缀索引是一种能使索引更小、更宽的有效办法，但也有其缺点：

MySQL无法使用前缀索引做order by 和 group by，也无法使用前缀索引做覆盖扫描。

常见的场景就是针对一个很长的16进制的唯一ID使用前缀索引。例如使用基于MySQL的应用在存储网站的session时，需要在一个很长的16进制字符串上创建索引。此时如果采用长度为8的前缀索引通常可以显著的提升性能，并且这种发发对上层应用完全透明。

有时候后缀索引（suffix index）也有用途，例如查询某个域名的所有电子邮件地址。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并给予此简历前缀索引。可以通过触发器来维护这种索引。可以参考前面文章中的创建自定义哈希索引部分的内容。

三 多列索引

很多人对多列索引的理解都不够。一个常见的错误就是，为每个列单独常见索引，或者没有按照逻辑顺序创建索引。

1 为每个列创建独立的索引

可以使用show create table查询

如果你发现一个表中含有很多索引，那么这种索引策略，一般是由于人们听到一些“专家”诸如 把where条件里面的列都建立索引，这样模糊的建议导致的。实际上这个建议是非常错误的。这样一来最好的情况下也只能是一星索引，其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个三星索引，那么不如忽略掉where子句，精力优化索引列的顺序，或者创建一个全覆盖索引。

在多个列上建立独立的单列索引大部分情况下并不能提升MySQL的查询性能。MySQL5.0以上版本引入了一种叫索引合并的策略（index merge），一定程度上可以使用表上的多个单列索引来定位指定的行。更早的版本中只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的。

在MySQL5.0以上版本中，查询能够同时使用两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种or条件的联合 and条件的相交，组合前两种情况的联合及相交。

MySQL会使用这类技术优化复杂查询，所以在某些语句的Extra列中还可以看到嵌套操作。

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕：

① 当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

② 当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其追踪有些索引的选择性不高，需要合并扫描返回的大量数据的时候。

③ 更重要的是，优化器不会吧这些计算到查询成本中，优化器只关心随机页面读取。这会使得查询的成本被低估，导致该执行计划还不如直接全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说，还不如在MySQL4.1或更早的版本一样，将查询改为UNION的方式。

如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

四 选择合适的索引列顺序

我们遇到的最容易引起困惑的问题就是多列索引的顺序。正确的顺序依赖与使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要（主要针对B-Tree索引讨论，哈希或其他类型索引并不会像B-Tree一样按顺序存储）。

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等待。所以，索引可以按照升序或降序进行扫描，以满足精确符合列顺序的order by、group bu和distinct等子句的查询需求。

所以多列索引的顺序是很重要的。在Lahdenmaki和Leach的三星索引系统中，列顺序也决定了一个索引是否能够不称为一个三星索引。

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前面。但这个法则没有避免随机IO和排序那么重要，考虑问题需要更全面，主要还是需要根据业务场景来考虑。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化where条件的查找。在这种情况下，河阳的设计的索引确实能够最快的过滤出需要的行，对于在where子句中只使用了索引部分前缀列的查找来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据哪些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

当使用前缀索引的时候，在某些条件值的技术比正常值高的时候，问题就来了。例如，在某些应用程序中，对于没有登陆的用户，都将其用户名记录为guset，在记录用户session表和其他记录用户活动的表中guest就成了一个特殊用户id。一旦查询设计到这个用户，那么对于正常用户的查询就大有不同了，因为通常有很多会话都是没有登陆的。系统账号也会导致类似的问题。一个应用通常都有一个特殊的管理员账号，和普通账号不同，它并不是一个具体的用户，系统中所有的其他用户都是这个用户的好友，所以系统往往通过它想网站的所有与用户发送状态通知和其他消息。这个账号的巨大的好友列表很容易导致网站出现服务器性能问题。

这是一个非常典型的问题。任何的异常用户，不仅仅是哪些用于管理应用的设计糟糕的账号会有相同的问题；哪些拥有大量好友图片、状态、收藏的用农户，也会出现这样的系统账号问题。这个问题会导致查询运行的非常慢。

我们会用EXPLAIN的结果看到这个查询也选择了最喝水的索引，如果不考虑列的技术，这看起来是一个非常合理的选择。但如果考虑一下这个条件查询的匹配行数，可能就会有不同的想法了。

因为当一个条件满足大部分记录的时候，那么这个条件基本上已经没用了。

尽管关于选择性和基数的经验法尔值得去研究和分析，但一定要记住别忘了where子句中的排序、分组和范围条件等其他因素，这些因素可能对哈讯的性能造成非常大的影响。

五  聚簇索引

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

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

因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。

上图追踪记录了聚簇索引是如何存放记录的。注意到叶子页包含了行的所有数据，但是节点页只包含了索引列。例子中索引是整数值。

一些数据库服务器允许选择哪个索引作为聚簇索引，目前MySQL内建的村吃醋引擎好像还没有支持者一点。InnoDB将通过主键聚集数据，也就是说上图中被索引的列就是主键列。

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含了相邻键值页面可能会相距甚远。

聚集主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细考虑聚簇索引，尤其是将表的存储引擎改变的时候。

聚集的数据有以下优点：

① 可以把相关数据保存在一起。例如保存消息时，可以根据用户ID来聚集数据。没有聚簇索引可能每个消息读一次磁盘I/O。

② 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常会快很多。

③ 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

如果在设计表和查询时能宠妃利用上面的优点，那就能极大的提升性能。同时，聚簇索引也有一些缺点：

① 聚簇数据最大限度的提高了I/O密集型应用的性能，但是，如果数据全部放在内存中，那么访问顺序就不重要了，聚簇索引也没有什么用处了。

② 插入速度严重依赖与插入顺序按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。

③ 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置上。

④ 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行时，可能会面临页分裂的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该也分成两个页面来容纳该行，这就是一次页分裂操作。会导致表占用更多的磁盘。

⑤ 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续时。

⑥ 二级索引可能比想象的更大，因为二级索引的叶子节点包含了引用行的主键列。

⑦ 二级索引访问需要两次索引查找，而不是一次。

为什么二级索引需要两次索引查找呢？因为二级索引中保存的是行指针。二级索引椰子汁节点保存的不是指向行物理位置的指针，而是行的主键值。

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

六 覆盖索引

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

覆盖索引是非常有用的工具，能够极大的提高性能：

① 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大的减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据考贝上。覆盖索引对于I/O秘籍性应用也有帮助，因为索引比数据更小，更容易全部放入内存中（在MyISAM中能压缩索引）。

② 因为索引是按照列值顺序存储的（单页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。对于某些存储引擎，例如MyISAM和Percona XtraDB，甚至可以通过OPTIMIZE命令使索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。

③ 一些村吃醋引擎如MyISAM在内存中只缓存索引，数据则依赖与操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致CPU占用问题。

④ 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

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

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

索引覆盖查询还有很多坑可能会导致无法实现优化。MySQL查询优化器会在执行查询前判断是够有一个索引能进行覆盖。假设索引覆盖了where条件红的字段，但不是整个查询设计的字段。如果条件为false，MySQL5.5以前也ong是会回表获取数据行，尽管执行后这一行会被过滤掉。

例如使用了where name = "abc" address like “%abc%” \G 这样的查询，这里有两个原因导致无法使用索引：

① 没有任何索引能够覆盖这样的查询。因为查询中从表中选择了所有的列，而没有任何索引覆盖所有的列。不过理论上MySQL还有一个捷径可以利用；where条件中的列是有索引可以覆盖的，因此MySQL可以使用该索引找到对应的name并检查address是否匹配，过滤之后再读取需要的数据行。

② MySQL不能在索引中执行like操作。这是底层存储引擎API的限制，MySQL5.5以前版本中只允许爱索引红做简单的比较操作（>  <  =  !=）。MySQL能再索引中做最左前缀匹配的like比较，因为该操作可以转换为简单的比较操作，但是如果是通过通配符开头的like查询，存储就无法做比较匹配。这种情况下，MySQL服务器只能提取数据行的值而不是索引值来做比较。

不过也有办法可以解决上述两个问题，需要重写查询并建立一个3列的索引。(name,address,book_id)

select * from user join(select book_id from user where name = "abc" address like “%abc%” ) as t1 on(t1.book_id = user.book_id)\G

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

这样优化效果取决于where条件匹配返回的行数。假设这个表中有100W行，那么只有当条件一的数据匹配行数远远超过条件二的匹配行数才能看到优化效果。

① 当两个匹配量都很大的时候那么大部分时间都花在读取和发送数据上了。

② 当条件一的匹配行数远超于条件二的匹配行数的时候，那么只需要读取很少的完整数据行。

③ 当条件一的匹配行数不是很多时，那么查询成本会比从表中直接提取行更高。

七 使用索引扫描来做排序

MySQL有两种凡是可以生成有序的结果；通过排序操作或按照索引顺序扫描。如果EXPLAIN出来的type列的值为index，则说明MySQL使用了索引扫描来做排序。

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

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

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

当前导列为常量的时候，order by子句可以不满足索引的最左前缀要求。如果where子句或join子句中对这些列指定了常量，就可以弥补索引的不足。

八 压缩索引（前缀压缩）

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

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

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

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

可以在create table 语句中指定pack_keys参数来控制索引压缩的方式

九 冗余和重复索引

MySQL允许在相同列上创建多个索引，无论是有意还是无意的。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的前缀压缩导致的。

例，有以下两个查询sql,假设state上有一个索引：

例1：select count(*) from user where state = 2;

例2：select state,city,address from user where state = 2;

对于这两个查询，如果想优化 例2的sql可以创建索引为（state,city,address），让索引能覆盖查询，这样不会影响到 例1的sql查询性能，这样原来的单列索引就是冗余的。

表中的索引越多插入速度回越慢。一般来说，增加新索引会导致insert、update、delete等操作的速度变慢，特别是当新增索引后导致到达了内存瓶颈的时候。

解决冗余索引和重复索引的方法很简单，删除这些索引就可以，但首先做的是找出这样的索引，可以通过写一些复杂的访问INFORMATION_SCHEMA表来查找，不过还有两个更简单的方法。可以使用Shlomi Noach的common_schema中的一些视图来定位，common_schema是一系列可以安装到服务器上的存储和视图（http://code.google.com/p/coommon-schema/）。这比自己编写查询要快而且简单。另外也可以使用Percona Toolkit中的pt-duplicate-key-checker，该工具通过分析表结构来找出冗余和重复的索引。对于大型服务器来说，使用外部的工具可能更合适些；如果服务器上游大量的数据或大量的表，那么查询INFORMATION_SCHEMA表可能会导致性能问题。

在决定哪些索引可以办删除的时候要非常小心。建议使用Percona工具中的pt-upgrade来仔细检查计划中的索引变更。

十 未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远用不到的索引。这样的索引完全是累赘，建议考虑删除。有两个工具可以帮助定位为只用的索引。最简单的方法是在Percona Server或MariaDB中先打开userstates服务器变量（默认关闭），然后让服务器正常运行一段时间，在通过查询INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每个索引的使用频率。

另外，还可以使用Percona Toolkit中的pt-index-usage，该工具可以读取查询日志，并对日志中的每条查询进行EXPLAIN操作，然后打印出关于索引和查询的报告。这个工具不仅可以找出那些索引是未使用的，还可以了解查询的执行计划--例如在某些情况有些类似的查询的执行方式不一样，这可以帮助你定位到那些偶尔服务质量差的查询，可以优化他们得到进一步的性能提升。该工具也可以将结果写入MySQL的表中，方便查询结果。

十一 索引和锁

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

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

关于InnoDB、索引和锁有协议很少有人知道的细节：InnoDB在二级索引上使用共享锁，但访问主键索引需要排它锁。这消除了使用覆盖索引的可能性，并且使得select  for  update比lock  in  share  mode或非锁定查询要慢很多。