怎么在MySQL中构建数据表索引

本篇文章给大家分享的是有关怎么在MySQL中构建数据表索引，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

支持多种类型的过滤

现在我们需要看看哪些列的值比较分散以及哪些列在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_colorIN('black', 'red', 'blonde', 'brown')  AND sex IN('M', 'F')

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

避免多个范围查询

让我们假设有一个last_online(最近在线时间）的列，然后我们需要展示最近一周在线的用户：

WHERE eye_colorIN('brown', 'blue', 'hazel')AND hair_colorIN('black', 'red', 'blonde', 'brown')  AND sex IN('M', 'F') AND last_online > DATE_SUB(NOW(), INTERVAL 7 DAY) AND ageBETWEEN 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, 10AS x USING(<primary key cols>);

以上就是怎么在MySQL中构建数据表索引，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注编程网数据库频道。