MySQL之创建高性能的索引(十一)

创建高性能的索引

索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然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.01 sec)

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会锁住第1行，这是因为MySQL为该查询选择的执行计划是索引范围扫描:

mysql> EXPLAIN SELECT actor_id FROM sakila.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.00 sec)

换句话说，底层存储引擎的操作是"从索引的开头开始获取满足条件acotr_id < 5"的记录，服务器并没有告诉InnoDB可以过滤第1行的WHERE条件。注意到EXPLAIN的Extra列出现了"Using where"，这表示MySQL服务器将存储以前宁返回行以后再应用WHERE过滤条件。
下面的第二个查询就能证明第1行确实已经被锁定，尽管第一个查询的结果中并没有这个第1行。保持第一个链接打开，然后开启第二个连接并执行如下查询:

mysql> SET autocommit=0;
Query OK, 0 rows affected (18.96 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT actor_id FROM sakila.actor WHERE actor_id =1 FOR UPDATE;
1205 - Lock wait timeout exceeded; try restarting transaction

这个查询将会挂起，直到第一个事务释放第一行的锁。这个行为对于基于语句的复制的正常运行来说是必要的。就像这个例子显示的，即使使用了索引，InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的问题可能会更糟糕，MySQL会做全表扫描并锁住所有的行，而不管是不是需要。关于InnoDB、索引和锁有一些很少有人直到的细节:InnoDB在二级索引上使用共享(读)锁，但访问主键索引需要排他(写)锁。这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢得多

索引案例学习

理解索引最好的办法是结合示例，假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色，等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。如何设计索引满足上面的复杂需求呢？
出人意料的是第一件需要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。例如，如果希望使用索引做根据其他会员对用户的评分的排序，则WHER条件中的age BETWEEN 18 AND 25就无法使用索引。如果MySQL使用某个索引进行范围查询，也就无法再使用另一个索引(或者是该索引的后续字段)进行排序了。如果这是很常见的WHERE条件，那么我们当然就会认为很多查询需要做排序操作(例如文件排序filesort)

支持多种过滤条件

现在需要看看那些列拥有不同的取值，那些列再WHERE子句中出现得最频繁。再有更多不同值得列上创建索引的选择性会更好。一般来说这样做都是对的，因为可以让MySQL更有效地过滤掉不需要的行。country列的选择性通常不高，但可能很多查询都会用到。sex列的选择性肯定很低，但也会再很多查询中用到。所以考虑到使用的频率，还是建议在创建不同的组合索引的时候将(sex,country)列作为前缀。根据传统的经验不是说不应该在选择性低的列上创建索引的吗？那为什么这里将两个选择性都很低的字段作为所以你的前缀列?我们的脑子坏了？
我们的脑子当然没坏。这么做有两个理由:第一点，如前所述几乎所有的查询都会用到sex列。前面曾提到，几乎每一个查询都会用到sex列，甚至会把网站设计成每次都只能按某一种性别搜索用户。更重要的是，索引中加上这一列也没有坏处，即使查询没用使用sex列也可以通过一些"诀窍"绕过。
这个"诀窍"就是:如果某个查询不限制性别，那么可以通过在查询条件中新增AND SEX IN(‘m’,‘f’)来让MySQL选择该索引。这样写并不会过滤任何行，和没有这个条件时返回的结果相同。但是必须加上这个列的条件，MySQL才能够匹配索引的最左前缀。这个"诀窍"在这类场景中非常有效，但如果列有太多不同的值，就会让IN()列表太长，这样做就不行了。这个案例显示了一个基本原则:考虑表上所有的选项。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。如果发现某些查询需要创建新索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否能优化原来的查询。应该同时优化查询和索引以找到最佳的平衡，而不是闭门造车去设计最完美的索引。

接下来，需要考虑其他场景WHERE条件的组合，并需要了解哪些组合在没有合适索引的情况下会很慢。(sex,country,age)上的索引就是一个明显的选择，另外很有可能还需要(sex,country,region,age)和(sex,country,region,city,age)这样的组合个索引。这样就会需要大量的索引。如果想尽可能地重用索引而不是建立大量的组合索引，可以使用前面提到的IN()的技巧来避免同时需要(sex,country,age)和(sex,country,region,age)的索引。如果没有指定这个字段搜索，就需要定义一个全部国家列表，或者国家的全部地区列表，来确保索引前缀有同样的约束(组合所有国家、地区、性别将会是一个非常大的条件)