mysql加索引的坏处_「Mysql索引原理（十二）」索引案例1-支持多种过滤条件

理解索引最好的办法就是结合案例。

案例

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

思考

第一件要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。例如，如果希望使用索引做根据其他会员对用户的评分的排序，则WHERE条件中的age between 18 and 25就无法使用索引。如果MySQL使用某个索引进行范围查询，也就无法再使用另一个索引(或者该索引的后续字段)进行排序了。如果这是很常见的where条件，那么我们当然就会认为很多查询需要做排序操作(例如文件排序filesort)。

分析

先在需要看看那些列拥有很多不同的取值，哪些列在WHERE子句中出现的最频繁。在有更多不同值的列上创建爱你索引的选择性会更好。一般来说这样做是对的，因为可以让MySQL更有效地过滤掉不需要的行。

country列的选择性不高，但可能很多查询都会用到。sex列的选择性肯定很低，但也会在很多查询中用到。所以考虑到使用的频率，还是建议在创建不同组合索引的时候将(sex，country)列作为前缀。

但根据传统的经验不是说不应该在选择性低的列上创建索引的吗？那为什么要将两个选择性都很低的字段作为索引的前缀列？

这么做有两点理由：

如前所述几乎所有的查询都会用到sex列。前面曾提到过，几乎每一个查询都会用到sex列，甚至会把网站设计成每次都只能按某一种性别搜索用户。

更重要的一点是，索引中加上这一列也没有坏处，即使查询没有使用sex列也可以通过诀窍绕过：如果某个查询不限制性别，那么可以通过在查询条件中新增and sex in('m','f')来让MySQL选择索引。这样写并不会过滤任何行，和没有这个条件时返回的结果相同。但是必须加上这个列的条件，MySQL才能匹配索引的最左前缀。这个诀窍在这类场景中非常有效，但如果列有太多不同的值，就会让in()列表太长，这样做就不行了。