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

创建高性能的索引

支持多种过滤条件

这些索引将满足大部分最常见的搜索查询，但是如何为一些生僻的搜索条件(比如has_pictures、eye_color、hair_colr和education)来设计索引呢？这些列的选择性搞，使用也不频繁，可以选择忽略它们，让MySQL多扫描一些额外的行即可。另一个可选的方法是在age列的前面加上这些列，在查询时使用前面提到过的IN()基数来处理搜索时没有指定这些列的场景。你可能已经注意到了，我们一直将age列放在索引的最后面。age列有什么特殊的地方吗？为什么要放在索引的最后？我们总是尽可能让MySQL使用更多的索引列，因为查询只能使用索引的最左前缀，直到遇到第一个范围条件列。前面提到的列在WHERE子句中都是等于条件，但age列则多半是范围查询(例如查找年龄在18~25岁之间的人)。当然，也可以使用IN()来代替范围查询，例如年龄条件改写为IN(18,19,20,21,22,23,24,25)但不是所有的范围查询都可以转换。这里描述的基本原则是，尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能的索引列。前面提到可以在索引中假如更多的列，并通过IN()方式覆盖那些不在WHERE子句中的列。但这种技巧也不能滥用，否则可能会带来麻烦。因为每额外增加一个IN()条件，优化器需要做的组合都将以指数形式增加，最终可能会极大地降低查询性能。考虑下面地子句:

WHERE eye_color IN ('brown', 'blue','hazel') AND hair_color IN ('black','red','blonde','brown') AND sex IN('M','F')
1

优化器则会转换成4x3x2=24种组合，执行计划需要检查WHERE子句中所有地24种组合。对于MySQL来说，24种组合并不是很夸张，但如果组合数达到上千个则需要特别小心。老版本的MySQL在IN()组合条件过多的时候会有很多问题。查询优化可能需要花很多时间，并消耗大量的内存。新版本的MySQL在组合数超过一定数量后就不再进行执行计划评估了，这可能会导致MySQL不能很好地利用索引。

避免多个范围条件

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

WHERE eye_color IN ('brown','blue','hazel')
AND hair_color IN('blackj','red','blonde','brown')
AND sex IN ('M','F')
AND last_online > DATE_SUB(NOW, INTERVAL 7 DAY)
AND age BETWEEN 18 AND 25
1
2
3
4
5

什么是范围条件?
从EXPLAIN的输出很难区分MySQL是要查询范围值还是查询列表值.EXPLAIN使用同样的词"range"来描述这两种情况.例如，从type列来看，MySQL会把下下面这种查询当作是"range"类型:

mysql> EXPLAIN SELECT actor_id FROM sakila.actor WHERE actor_id > 45\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: 155
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

但是下面这条查询呢？

mysql> EXPLAIN SELECT actor_id FROM sakila.actor WHERE actor_id IN(1,4,99)\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: 3
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

从EXPLAIN的结果是无法区分这两者的，但可以从值的范围和多个等于条件来得出不同。在我们看来，第二个查询就是多个等值条件查询的。我们不是挑剔:这两种访问效率是不同的。对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但是对于"多个等值条件查询"则没有这个限制

这个查询有一个问题:它有两个范围条件，last_online列和agelie，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)。这些索引对某个具体的查询来说可能都是更优化的了，但是考虑到索引的维护和额外的空间占用的代价，这个可选方案就不是一个好策略了。
在这个案例中，优化器的特性是影响索引策略的一个很重要的因素。如果未来版本的MySQL能够实现松散索引扫描，就能在一个索引上使用多个范围条件，那也就不需要为上面考虑的这类查询使用IN()列表了

优化排序

最后要介绍的是排序。使用文件排序对小数据集是很快的，但如果一个查询匹配的结果有上百万行的话会怎样?例如如果WHERE子句只有sex列，如何排序？对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。例如，可以创建(sex,rating)索引用于下面的查询:

mysql SELECT <cols>  FROM profiles WHERE sex= 'M'  ORDER BY rating LIMIT 10;
1

这个查询同时使用了ORDER BY 和LIMIT，如果没有索引的话会很慢。即使有索引，如果用户界面山需要翻页，并且翻页翻到比较靠后时查询也可能非常慢。下面这个查询就通过ORDER BY 和LIMIT偏移量的组合翻页到很后面的时候:

mysql> SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 1000000，10；
1

无论如何创建索引，这种查询都是个严重的问题，因为随者偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。反范式化、预先计算和缓存可能是解决这类查询的仅有策略。一个更好的办法是限制yoghurt能够翻页的数量，实际上这对用户体验的影响不大，因为用户很少会真正在乎搜索结果的第10 000页。优化这类索引的另一个比较好的策略是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联源表获得需要的行。这可以减少MySQL扫那些需要丢弃的行数。下面这个查询显示了如何高效地使用(sex,rating)索引进行排序和分页:

mysql> SELECT <cols> FROM profiles INNER JOIN(SELECT <primary key cols> FROM profiles WHERE x.sex ='M' ORDER BY rating LIMIT 1000000,10) AS x USING(<primary key cols>);
1