chapter03_架构优化和索引_4_索引实例研究

• 现在的需求是一个在线约会网站的表，包括country, state, city, sex, age, eye color等列，要支持各种条件的组合搜索用户，还要对用户进行排序

• 由于查询的组合多种多样，所以__基于索引的排序__不太合适，应该用__文件排序__

• 支持多种过滤条件

  (1) 拥有许多唯一列值的列上的索引选择性强，可以更好的筛选出结果

  (2) 查询组合可能是 (sex, country, age) 或 (sex, country, region, age) 或 (sex, country, region, city, age)，它们可以使用一个索引，就是为(sex, country, region, city, age)建立索引

  问题：sex项没什么选择性，为什么要放在索引中？

  因为这里面有一个__小技巧__： sex只可能有两种: M和F，查询时只需要在WHERE后面加上 AND sex IN (‘M’, ‘F’)就行了，这样优化器会将一个查询变成2个查询的组合，还是会用到索引。并且sex确实是查询中经常需要的列，虽然它没什么选择性，但是用上这个小技巧以后满足了需求且没怎么影响效率

  如果sex中的选项过多，例如IN中出现了__很多个备选值__，那么就__不太适用__了

  (3) 问题：age为什么要放在索引的最后一列？

  因为查询中使用age的情况往往是: WHERE age BETWEEN 25 AND 35这种带范围的查询，而前面说过索引的最左前缀原则，一旦某列使用了范围查询，那么后面的列就不能用索引了，所以age要放在最后

• 避免多个范围条件

  (1) 示例

    WHERE eye_color IN ('brown', 'blue', 'hazel')
    AND hair_color IN ('black', 'red', 'blonde')
    AND sex IN ('M', 'F')
    AND last_online > DATE_SUB('2018-01-17', INTERVAL 7 DAY)
    AND age BETWEEN 18 AND 25;
  

  这里后两个条件都是__范围查询__，所以不可能同时使用索引；但是前面3个属于__多个相等条件__，所以会使用索引

  (2) 解决办法：

  添加一个active列，用一个周期性任务维护它（例如每天半夜服务器更新一下，让7天没上线的用户的active列变成0），然后查询时变成active = 1代替last_online列

• 优化排序

  (1) 文件排序对于少量结果的排序速度可以接受，但是记录多了就慢了

  (2) 对于选择性低的列，可以添加特殊的索引

    SELECT <cols> FROM profiles WHERE sex = 'M' ORDER BY rating LIMIT 10;
  

  现在sex和rating都没有什么选择性，可以给它们建立一个索引(sex, rating)。由于查询中sex是常量值，所以这个查询会使用基于索引的排序

  (3) 但是，一旦用户界面是分页的，并且一些用户请求的不是靠近开始的页面，同样速度会很慢，例如

    SELECT <cols> FROM profiles WHERE sex = 'M' ORDER BY rating LIMIT 100000, 10;
  

  因为偏移量100000会导致大量记录扫描后被舍弃

  (4) 针对(3)中的问题，又有一种策略就是先获取主键列然后再连接回去取得所有的列

    SELECT <cols> FROM profiles
    INNER JOIN 
    (SELECT <primary key cols> FROM profiles WHERE sex = 'M' ORDER BY rating LIMIT 100000, 10) AS x
    ON <primary key cols>;
  

  原来的操作是：把所有的主键列找到，然后根据主键列找到对应的记录，然后进行偏移；现在的操作是：找到结果中的主键列，然后根据主键列找到对应的记录，减少了扫描丢弃的开销