当前位置：首页 > Mysql > 正文内容

83 案例实战：陌生人社交APP的MySQL索引设计实战（三）

CarlZhou3年前 (2021-05-09)Mysql828

上一次我们讲到我们的联合索引已经设计为了（province, city, sex）的样子，把省份、城市和性别三个几乎每次查询都会加的条件放入了联合索引的最左侧去，接着我们今天继续分析这个联合索引里还要放哪些字段。

分析这个问题之前，我们先来分析一个问题，那就是假设查询的时候，不指定性别，就指定了省份，城市，还有加了一个年龄，也就是说where province=xx and city=xx and age between xx and xx，那么此时怎么办呢？因为age不在索引里，所以就根本没法通过age去在索引里进行筛选了。

那如果把索引设计成（province, city, sex, age），此时你的语句写成where province=xx and city=xx and age>=xx and age<=xx，也是没法让age用上索引去筛选的，因为city和age中间差了一个sex，所以此时就不符合最左侧连续多个字段的原则了。

其实针对这个问题，大家完全没必要太担心，因为假设有上述场景，那么我们完全是可以把age放入联合索引的，设计成（province, city, sex, age）这样的索引，那么在搜索的时候就根据省份、城市和年龄来筛选，性别是不限的，此时就可以把where语句写成：where province=xx and city=xx and sex in ('female', 'male') and age >=xx and age<=xx。

如果我们把语句写成上面那样子，那么就可以让整个where语句里的条件全部都在索引树里进行筛选和搜索了！

另外，假设我们在查询语句里还有一些频繁使用的条件，通常都是兴趣爱好和性格特点，这个兴趣爱好和性格特点，往往都是有固定的一些枚举值的

比如兴趣爱好可以有下述的值可选：运动、电影、旅游、烹饪，性格特点可能包含下面的值：温柔、霸气、御姐、体贴、善良，等等。

那么针对这样的一些频繁使用的包含枚举值范围的一些字段，也完全可以加入到联合索引里去，可以设计成（province, city, sex, hobby, character, age）这样的一个联合索引，此时假设出现了这样一个查询，按照省份、城市、性格和年龄进行搜索，此时SQL怎么写？

还是用之前的那个策略和思路，就是写成where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx

也就是说，即使你不需要按性别和爱好进行筛选，但是在SQL里你可以对这两个字段用in语句，把他们所有的枚举值都放进去。这样的话，就可以顺利的让province，city，character和age四个真正要筛选的字段用上索引，直接在索引里进行筛选都是没有问题的。

那么我们为什么一直强调，age字段必须要放在联合索引的最后一个呢？

很简单，因为之前我们讲索引使用规则的时候说过，假设你where语句里有等值匹配，还有范围匹配，此时必须是先让联合索引最左侧开始的多个字段使用等值匹配，接着最后一个字段是范围匹配。

就比如上面的语句where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx，他们完全是按照联合索引最左侧开始的，province、city、sex、hobby、character都是联合索引最左侧开始的多个字段，他们都是等值匹配，然后最后一个age字段使用的是范围匹配，这种就是可以完全用上索引的。

但是假设你要是在联合索引里把age放在中间的位置，设计一个类似（province, city, sex, age, hobby, character）的联合索引，接着SQL写成where province=xx and city=xx and sex in(xx, xx) and age>=xx and age<=xx and hobby in (xx, xx, xx, xx) and character=xx的话，那么不好意思，只有province, city, sex, age几个字段可以用上索引。

因为在SQL里，一旦你的一个字段做范围查询用到了索引，那么这个字段接下来的条件都不能用索引了，这就是规则！

所以说，实际设计索引的时候，必须把经常用做范围查询的字段放在联合索引的最后一个，才能保证你SQL里每个字段都能基于索引去查询。

下次我们再针对这个场景下更多的一些特殊搜索场景去给大家讲，设计联合索引的时候还有哪些技巧！