MySQL如何构建数据表索引

原作者: [db:作者] 来自: [db:来源] 收藏邀请

假设我们需要设计一个在线的约会网站，这个网站的用户资料有许多列，例如国籍、省份、城市、性别、年龄、眼睛颜色等等。这个网站必须支持通过多种组合方式搜索用户资料。同时，也需要支持支持排序和根据用户最近在线时间和其他用户的评价返回有限的结果等等。对于这种复杂场景我们如何设计索引？

有点奇怪，首先要做的事情是要决定我们是否必须使用索引排序，或者检索后再排序是否能够接受。索引排序限制了索引和查询构建的方式。例如，在WHERE age BETWEEN 18 AND 25这样的查询条件和基于其他用户评价排序的场景中，我们不能使用同一个索引。如果MySQL在范围查询中使用了一个索引，那就没法在排序中使用另一个索引。假设这是一个最常用的WHERE条件，同时我们还需要支持大多数查询都可以排序。

支持多种类型的过滤

现在我们需要看看哪些列的值比较分散以及哪些列在WHERE条件中最常出现。数据列值比较分散的筛选性很好。这通常会是一个好事情，因为这让MySQL可以将高效过滤掉不相关的数据行。

国籍列可能筛选性不太好，但却可能是最常查询的。性别列通常不具备筛选性，但却也经常用于查询。基于这样的认识，我们为许多不同的列的组合创建了一系列的索引，这些索引使用(sex, country)开头。

传统的认知是对于低筛选性的列构建索引是没用的。那我们为什么要在每个索引开头都加上不具筛选性的列？我们有两个理由这么做。第一个理由是，如前所述，基本每个查询都会使用性别。我们甚至设计了用户一次只能搜索一个性别。但更重要的是，增加这样的列并没有多少缺点，因为我们使用了一个小招数。

这是我们的招数：即便不限制性别查询，我们也能够保证在WHERE语句中加上AND sex IN('m', 'f')让索引生效。这不会过滤掉我们所需要的行，因此与WHERE语句中不包含性别作用相同。然而，因为MySQL会在更多列的索引中前置这个列，我们需要包含这个列。这个招术在这样的场景下有效，但是如果是这个列具有很多不同的值，那反而不起作用，这是因为这会导致IN()中的列过多。

这个例子阐述了一个基本的原则：在数据表设计上保留所有的选项。当你设计索引的时候，不要只想着那种查询中的那类索引，也同时考虑优化查询。当你需要一个索引却发现其他查询可能会受其影响，你应该先问问自己能否改变查询。你应该同时优化查询和索引去找到解决之道。你不一定需要设计完美的索引。

接下来，我们需要考虑可能用到的其他组合的WHERE条件，然后考虑其中的哪些组合在没有合理索引的情况下会变慢。(sex, country, age)这样的索引是很明显的选择，但我们也可能需要(sex, country, region, age)和(sex, country, region, city, age)这样的索引。

这会导致需要建立很多的索引。如果我们能够重复利用索引，那就不会产生过多的组合。我们可以使用IN()这种小招数来去掉(sex, country, age)和(sex, country, region, age)索引。如果这些列在搜索表单中没有指定，我们可以使用国家清单、地区清单来保证满足索引前置的约束（全部国家，全部地区和全部性别的组合可能很多）。

这些索引会满足指定的大部分搜索查询，但我们如何设计那些不那么常见的筛选，例如上传了图片(has_pictures)，眼睛颜色（eye_color），头发颜色（hair_color）和教育水平（education）？如果这些列不是那么具有筛选性并且不那么常用，我们可以直接跳过他们，让MySQL去扫描额外的一些数据行。相应地，我们可以在age列前增加他们，并且使用IN()技巧去提前描述以处理那种这些列没有指定的情况。

你也许注意到我们将age放到了索引的最后面。为什么要特别处理这个列？我们在试图保证MySQL能够尽可能多地利用索引列。由于MySQL使用最左匹配规则，直到遇到第一个范围查询条件。所有我们提到的列都可以在WHERE语句中使用相等条件，但年龄(age)大概率是范围查询。

我们也能够将范围查询改为清单使用IN查询，例如age IN(18, 19, 20, 21, 22, 23, 24, 25)来替代age BETWEEN 18 AND 25，但这并不总是能够这么做。通用的原则是我们尽量将范围判决条件放到索引的末尾，因此优化器会尽可能地使用索引。

我们提到你可以使用尽可能多的列使用IN查询去覆盖那些在WHERE条件中未指定的索引条件。但你可能做得过头了导致新的问题。使用更多的这样的IN查询清单导致优化器需要评估大量的组合，这反而可能降低查询速度。考虑下面的查询条件语句：

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

这个优化器会转变为432=24种组合，WHERE条件会检查每一种情况。24还不是一个很大的组合数字，但如果数量达到了几千。旧版本的MySQL在IN查询中数量过多时可能会有更多的问题。查询优化器会执行更慢并且消耗很多内存。新版本的MySQL会在组合过多时停止评估，但这会影响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

这个查询的问题在于它有两个范围查询。MySQL可以使用last_online或age条件，但不能同时使用。如果last_online约束出现时没有age约束，或last_online比age更有筛选性，我们可能希望增加另一组索引，将last_online放到最后面。但是如果我们不能将age转换为IN查询，而我们也希望能够在同时有last_oinline和age范围查询时提高查询速度怎么办？这个时候，我们没有直接的方法。但我们可以将一个范围转换为相等比较。去这么做的时候，我们增加一个预先计算的active列，这个列我们会定期维护。如果用户登录后，我们标记为1，如果7天内没有连续登录则重新标记为0。

这个方法可以让MySQL使用如(active, sex, country, age)这样的索引。这个列也许没那么精准，但这类查询也许不需要很高的精准度。如果我们需要精准查询，我们可以保留last_online在WHERE条件中，但不增加索引。这种技巧与URL查找的情况类似。这种条件不会使用任何索引，因为它不太可能会将索引命中的行给过滤掉。增加索引未必能够让查询收益。

现在，你可以看到这个模式：如果用户想同时查找活跃和不活跃的结果，我们可以使用IN查询。我们增加了很多这样的清单查询，一个变通的方式是通过将各个组合分开的查询单独建立索引，例如，我们可以使用如下的索引：(active, sex, country, age)，(active, country, age)，(sex, country, age)和(country, age)。虽然这样的索引对于特定的查询可能是更优的选择，但维护这些组合的负面效果，组合所需的额外存储空间都可能导致是一个很弱的策略。

这是一个优化器改变后可以真正影响索引优化的案例。如果在未来的MySQL版本中可以真正丢弃索引扫描，它可能能够在一个索引上使用多个范围条件，此时我们不再需要通过IN查询这种方式解决此类问题。

优化排序

最后一个议题是排序。小数据量的结果使用文件排序(filesort)很快，但如果是上百万行数据呢？例如，如果只在WHERE条件中指定了性别时的排序。

对于这类低筛选性的场景，我们可以增加特定的索引用于排序。例如，一个(sex, rating)的索引可以用于下面的查询：

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

这个查询同时有排序和LIMIT子句，在没有索引的情况下可能很慢。即便是有索引，这个查询在用户界面有分页查询，而页码不在起始位置附近时也可能很慢。下面的例子的ORDER BY和LIMIT造成了一个糟糕的组合：

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

即便有索引，这样的查询也可能导致十分严重的问题。这是因为很高的偏移会导致花费大量的时间扫描大量的数据，且这些数据会被丢弃。反范式设计，提前计算和缓存可能能够解决这类查询的问题。一个更好的策略是限制用户可查询的页码。这不太可能会降低用户的体验，因为实际上不会有人会关心第10000页的搜索结果。

另一个好的策略是使用推断联合查询，这是我们利用覆盖索引去获取主键列后再获取数据行的方式。你可以将需要获取的列全部联合，这会减少MySQL收集那些需要丢弃的数据的工作。下面是一个例子：

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

以上就是MySQL如何构建数据表索引的详细内容，更多关于MySQL构建数据表索引的资料请关注极客世界其它相关文章！