理解索引概念最简单的方式是通过一个案例来进行，以下就是这样的一个案例。
假设我们需要设计一个在线的约会网站，这个网站的用户资料有许多列，例如国籍、省份、城市、性别、年龄、眼睛颜色等等。这个网站必须支持通过多种组合方式搜索用户资料。同时，也需要支持支持排序和根据用户最近在线时间和其他用户的评价返回有限的结果等等。对于这种复杂场景我们如何设计索引？
有点奇怪，首先要做的事情是要决定我们是否必须使用索引排序，或者检索后再排序是否能够接受。索引排序限制了索引和查询构建的方式。例如，在WHERE age BETWEEN 18 AND 25这样的查询条件和基于其他用户评价排序的场景中，我们不能使用同一个索引。如果MySQL在范围查询中使用了一个索引，那就没法在排序中使用另一个索引。假设这是一个最常用的WHERE条件，同时我们还需要支持大多数查询都可以排序。
支持多种类型的过滤
现在我们需要看看哪些列的值比较分散以及哪些列在WHERE条件中最常出现。数据列值比较分散的筛选性很好。这通常会是一个好事情，因为这让MySQL可以将高效过滤掉不相关的数据行。
国籍列可能筛选性不太好，但却可能是最常查询的。性别列通常不具备筛选性，但却也经常用于查询。基于这样的认识，我们为许多不同的列的组合创建了一系列的索引，这些索引使用(sex, country)开头。
传统的认知是对于低筛选性的列构建索引是没用的。那我们为什么要在每个索引开头都加上不具筛选性的列？ 我们有两个理由这么做。第一个理由是，如前所述，基本每个查询都会使用性别。我们甚至设计了用户一次只能搜索一个性别。但更重要的是，增加这样的列并没有多少缺点，因为我们使用了一个小招数。
这是我们的招数：即便不限制性别查询，我们也能够保证在WHERE语句中加上AND sex IN(‘m’, ‘f’)让索引生效。这不会过滤掉我们所需要的行，因此与WHERE语句中不包含性别作用相同。然而，因为MySQL会在更多列的索引中前置这个列，我们需要包含这个列。这个招术在这样的场景下有效，但是如果是这个列具有很多不同的值，那反而不起作用，这是因为这会导致IN(本文来源gaodai#ma#com搞*!代#%^码$网!)中的列过多。
这个例子阐述了一个基本的原则：在数据表设计上保留所有的选项。当你设计索引的时候，不要只想着那种查询中的那类索引，也同时考虑优化查询。当你需要一个索引却发现其他查询可能会受其影响，你应该先问问自己能否改变查询。你应该同时优化查询和索引去找到解决之道。你不一定需要设计完美的索引。
接下来，我们需要考虑可能用到的其他组合的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()技巧去提前描述以处理那种这些列没有指定的情况。
转载请注明原文链接：MySQL如何构建数据表索引
