浅谈《高性能MySQL》之“创建高性能索引“

《高性能mysql(第3版)》是mysql 领域的经典之作，拥有广泛的影响力。第3 版更新了大量的内容，不但涵盖了最新mysql 5.5版本的新特性，也讲述了关于固态盘、高可扩展性设计和云计算环境下的数据库相关的新内容，原有的基准测试和性能优化部分也做了大量的扩展和补充。

索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。

本次分享主要介绍“如何创建高性能索引”

一、单列索引和多列索引

利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。即以最左边的为起点任何连续的索引都能匹配上

例如：当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引
想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；

二、使用索引扫描来做排序

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

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

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

三、冗余和重复索引

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来说主键列已经包含在二级索引中了，所以这也是冗余的。

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

四、索引和锁

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

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

五、索引案例学习

假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、 眼睛颜色，等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许根据 用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。如 何设计索引满足上面的复杂需求呢？

1、支持多种过濾条件

现在需要看看哪些列拥有很多不同的取值，哪些列在WHERE子句中出现得最频繁。在有 更多不同值的列上创建索引的选择性会更好。一般来说这样做都是对的，因为可以让 MySQL更有效地过滤掉不需要的行。country列的选择性通常不高，但可能很多査询都会用到。sex列的选择性肯定很低，但 也会在很多査询中用到。所以考虑到使用的频率，还是建议在创建不同组合索引的时候 将（sex, country）列作为前缀。但根据传统的经验不是说不应该在选择性低的列上创建索引的吗？那为什么这里要将两 个选择性都很低的字段作为索引的前缀列？

这么做有两个理由：第一点，如前所述几乎所有的査询都会用到 sex列。前面曾提到，几乎每一个査询都会用到sex列，甚至会把网站设计成每次都只 能按某一种性别搜索用户。更重要的一点是，索引中加上这一列也没有坏处，即使査询 没有使用sex列也可以通过下面的“诀窍”绕过。

这个“诀窍”就是：如果某个査询不限制性别，那么可以通过在査询条件中新增AND SEX IN （'m','f'）来让MySQL选择该索引。这样写并不会过滤任何行，和没有这个条件 时返回的结果相同。但是必须加上这个列的条件，MySQL才能够匹配索引的最左前缀。 这个“诀窍”在这类场景中非常有效，但如果列有太多不同的值，就会让IN（）列表太长, 这样做就不行了。

 

2、避免多个范围条件

从EXPLAIN的输出很难区分MySQL是要查询范围值,还是查询列表值。 EXPLAIN使用同样的词“ range”来描述这两种情况。例如，从type列来看， MySQL会把下面这种查询当作是“ range”类型：

explain select id from people where id>45

       但是下面这条语句呢?

explain select id from people where id in (1,4,99)

       从EXPLAIN的结果是无法区分这两者的，但可以从值的范围和多个等于条件来得出不同。在我们看来，第二个查询就是多个等值条件查询。

       我们不是挑剔：这两种访问效率是不同的。对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但是对于“多个等值条件查询”则没有这个限制。

       假设我们有一个last_online列并希望通过下面的查询显示在过去几周上线过的用户：

...
WHERE eye_color IN('brown','blue','hazel')
AND hair_color IN('black','red','blonde','brown')       
AND sex   IN('M','F')
AND last_online >DATE_SUB(NOW(),INTERVAL 7 DAY)
AND age BETWEEN 18 AND 25

       这个查询有一个问题：它有两个范围条件，last_online列和age列，MySQL可以使用last_online列索引或者age列索引，但无法同时使用它们。

       如果条件中只有 last_online而没有age，那么我们可能考虑在索引的后面加上last_online列。这里考虑如果我们无法把age字段转换为一个IN()的列表，并且仍要求对于同时有last_online和age这两个维度的范围查询的速度很快，那该怎么办?答案是，很遗憾没有一个直接的办法能够解决这个问题。但是我们能够将其中的一个范围查询转换为一个简单的等值比较。为了实现这一点，我们需要事先计算好一个active列，这个字段由定时任务来维护。当用户每次登录时，将对应值设置为1，并且将过去连续七天未曾登录的用户的值设置为0。

       这个方法可以让 MySQL使用(active, sex, country,age)索引。 active列并不是完全精确的，但是对于这类査询来说，对精度的要求也没有那么高。如果需要精确数据，可以把 last_online列放到WHERE子句，但不加入到索引中。这和本章前面通过计算URL哈希值来实现URL的快速查找类似。所以这个查询条件没法使用任何索引，但因为这个条件的过滤性不高，即使在索引中加入该列也没有太大的帮助。换个角度来说,缺乏合适的索引对该查询的影响也不明显。

       到目前为止，我们可以看到：如果用户希望同时看到活跃和不活跃的用户,可以在查询中使用IN()列表。我们已经加入了很多这样的列表，但另外一个可选的方案就只能是为不同的组合列创建单独的索引。至少需要建立如下的索引:( active,sex, country, age)，(active, country,age)，(sex, country,age)和( country,age)。这些索引对某个具体的查询来说可能都是更优化的，但是考虑到索引的维护和额外的空间占用的代价，这个可选方案就不是一个好策略了。

七、总结：

在选择索引和编写利用这些索引的査询时，有如下三个原则始终需要记住：

1、单行访问是很慢的。特别是在机械硬盘存储中（SSD的随机I/O要快很多，不过这 一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么 就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创 建位置引用以提升效率。

2、按顺序访问范围数据是很快的，这有两个原因。第一，顺序I/O不需要多次磁盘寻道， 所以比随机I/O要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序 读取数据，那么就不再需要额外的排序操作，并且GROUP BY査询也无须再做排序和 将行按组进行聚合计算了。

3、索引覆盖査询是很快的。如果一个索引包含了査询需要的所有列，那么存储引擎就 不需要再回表査找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。