陌生人社交APP的mysql索引实战

  1. 假设有一个user_info表。里面包含省份，城市，性别，年龄，身高，体重兴趣爱好，性格特点，照片，最近在线时间，综合评分等。
  2. 假设查询sql是select xxx from user_info where age between 20 and 25 order by score.联合索引age在最左侧，那where可以用索引，order by无法用索引。实际场景中可能where和 order by没法同时都用到索引。
  3. 如果where和order by没法都用到索引的时候，通常是用where条件去使用索引筛选出来一部分，接着进行排序。
  4. 根据where设计联合索引，里面应该包括省份，城市，性别。 即使省份，城市，性别的基数很少，为了避免筛选完还需要用这几个字段过滤，直接每次查询的时候从索引树里进行筛选。
  5. 如果在查询的时候需要加上年龄，select xx from table where province =xx and city = xx and age>xx and age< xx.这样age无法使用索引，因为中间隔了sex，但仍然可以(province,city,sex,age) 建立索引，sql语句select xx from table where province =xx and city = xx and sex in(‘male’,’famale’) and age>xx and age< xx.
  6. age范围查询必须放在最后，根据规则，等值查询+范围查询，最左匹配才能保证都可以使用上索引，如果select xx from table where province =xx and city = xx and age>xx and age< xx and sex in(‘male’,’famale’) ，这样只有province，city，age能使用上索引。
  7. 利用一两个复杂的多字段联合索引，抗下80%以上的查询，一两个辅助索引抗下20%的非典型查询，保证99%以上的查询都可以充分利用到索引。