设计联合索引时有哪些技巧

设计联合索引时有哪些技巧

一、让最频繁查询的一些条件都放到索引里去，然后在查询时如果有些字段是不使用的，可以用in (所有枚举值)的方式去写，这样可以让所有查询条件都用上你的索引，同时对范围查询的age字段必须放在最后一个，这样保证范围查询也能用上索引。

1、可以把基数较低但是频繁查询的字段放到联合索引的最左侧去
2、如果要查省、市、年龄，怎么设计索引怎么编写sql？索引(provice, city, sex, age)

	where province=xx and city=xx and sex in (‘female’, ‘male’) and age >=xx and age <=xx

3、如果要查省、市、性格、年龄，怎么设计索引怎么编写sql？索引(provice, city, sex, hobby, character, age)

	where province=xx and city=xx and sex in (‘female’, ‘male’) and hobby in (xx, xx, xx) and character=xx and age >=xx and age <=xx

4、在sql里，一旦你的一个字段做范围查询用到了索引，那么这个字段接下来的条件都不能用索引了
// 所以说，实际设计索引时，必须把经常用作范围查询的字段放在联合索引的最后一个，才能保证你sql里每个字段都能基于索引去查询

	// 只有province, city, sex, age几个字段可以用上索引
	where province=xx and city=xx and sex in (‘female’, ‘male’) and age >=xx and age <=xx and hobby in (xx, xx, xx) and character=xx

二、假设查询时

还有个条件（筛选最近7天登录过APP的用户，latest_login_time），latest_login_time <=7肯定没法走索引，因为你这里必然会用一些函数才能进行一些时间的比对；这里可以使用个技巧，把一个时间字段转换为一个枚举值字段（是否在最近7天内登录过APP，does_login_in_latest_7_days，是1、否0）；接下来的解决方案就简单化了，可以设计一个联合索引为(province, city, sex, hobby, character, does_login_in_latest_7_days, age)，

	// 如果where语句通过上面的联合索引就可以过滤掉大部分的数据，就保留小部分数据来基于磁盘文件进行order by语句的排序，最后基于limit进行分页，那么一般性能还是比较高的
	where province=xx and city=xx and sex in (‘female’, ‘male’) and hobby in (xx, xx, xx) and character=xx and does_login_in_latest_7_days=1 and age >=xx and age <=xx
	// 如果仅仅基于联合索引里一些基数特别小的字段比如性别来筛选，可能一下子筛选出上百万用户数据，接着还要磁盘文件进行排序在分页，那么这个性能可能极差
	// 针对这个问题，可以用那种基数很低的字段再加上排序字段单独额外设计一个辅助索引，专门用于解决where条件里都是基数低的字段，然后还要排序后分页的问题，
	// 比如可以设计一个联合索引为(sex, score)，此时会走索引来排序，因为具体到使用索引层面，会先对sex=’female’在索引树里筛选数据，这些数据实际上都是排列
    // 在一起的，因为在索引里，会按照sex和score两个字段进行排序，索引sex=’female’的数据都是在一块的，所以此时整体运行效率是非常高的。
	select xx from user_info where sex=’female’ order by score limit xx, xx

以此类推，完全可以通过对查询场景的分析，用(province, city, sex, hobby, character, does_login_in_latest_7_days, age)这样的联合索引去抗下复杂的where条件筛选的查询，此时走索引筛选速度很快，筛选出的数据量较少，接着进行排序和limit分页；

同时针对一些低基数字段筛选+评分排序的查询场景，可以设计类似(sex, score)的辅助索引来应对，让它快速定位到一大片低基数字段对应的数据，然后按照索引顺序去走limit语句获取指定分页的数据，速度同样会很快。

核心重点就是，尽量利用一两个复杂的多字段联合索引，抗下你80%以上的查询，然后用一两个辅助索引抗下剩余20%的非典型查询，保证你99%以上的查询都能充分利用索引，就能保证你的查询速度和性能。