一些人建立索引时,喜欢对大量字段做单个字段的索引,这样在一些情况下是不能很好的完成任务的,因为 mysql 在执行查询优化时,最终只会使用一个字段作为索引。
比如,一个在线社交网站,保存有性别(sex),年龄(age),国家(country),创建时间(createTime) 等等字段。需求是对性别/年龄搜索符合条件的用户,那么,一般人会建立如下索引 index_sex(sex), index_age(age),查询时,使用 where sex=‘M’ and age=25 ,那么这个 sql ,在大数据量的情况下,性能会非常差,原因是,mysql 在优化时,只会使用到 index_sex 或者 index_age 其中的一个,并不会同时使用到两个索引。
那么问题就来了,我们要如何解决这种问题呢?
一个可行的解决方案是,建立一个联合索引 index_sex_age,对 性别/年龄建立联合索引,则查询时,就会使用到这个索引。这个时候也有个问题,就是如何对 age 做单独查询?
这个问题的解决方式,比较其妙,也是建立这样索引的精髓所在,可以使用 where sex in(‘M’,‘F’) and age = 25 这样的sql,那么mysql 优化器就会使用联合索引做查询。
这里面有两个需要注意的点:一、对 sex 一定要使用 in 操作,并且要列出 sex 所有可能的值;二、联合索引的 sex 一定要在前面。
可以看到这样子一个索引,如果分别单独建索引的话,那么在做两个条件同时查询时,效率会非常低,因为 mysql 最终只能使用到其中一个索引。
这样子的索引,也为我们实际生产环境监理索引提供了一个思路,也就是可以在补增加索引个数的情况下,完成对业务的优化。