优化特定类型的查询题

参考资料 高性能MySQL P.236

优化COUNT查询

• 统计某个非空列值的数量
• 统计行数

注意:

• 当MySQL确认括号内的表达式不可能为空时，实际上就是统计行数例如count(*)，count(1)。
• 如果希望统计结果集行数，最好使用count(*)。
• MyISAM的count()函数非常快的前提是没有where子句*。

使用近似值

• 例如去除DISTINCT（不允许重复）这样的约束来避免文件排序。
• 优化where条件(去掉一些对总数影响较小的)
• 引入缓存系统，汇总表等等牺牲一致性其实也是牺牲精确性

优化关联查询

• 1.确保on或者using子句的列上有索引(注:mysql-on-vs-using)

  • inner join cross join join在MySQL语法中是等价的
  • 创建索引的时候需要考虑关联顺序，如果关联顺序是B,A on b.col1=A.clo2 那么外层循环遍历B，内层取A.col2=const，所以只需要在A上建立索引。

• 2.确保任何的group by和order by中的表达式只涉及到一个表中的列。（why? B+树的维度是一个表）

优化子查询(尽可能使用关联查询代替)

优化LIMIT分页

limit m（偏移量）,n;
limit n;

解决这类问题的思路有

• 解决一个计算机问题的最好方法就是恰好不需要

  • 产品设计上不提供具体到某一页的查询，只提供上一页，下一页等。问题在于程序or爬虫访问

• 优化大偏移量的性能

  • 优化此类分页查询的一个最简单的办法就是尽可能的使用索引覆盖扫描，而不是查询所有的列，然后做一次关联操作再返回所需的列。如果要分页，一般就存在排序。select a,b from t where c=const order by d limit m,n这样的sql。对mysql来说最高效的就是使用索引来避免排序。上面的sql可以通过建立索引 (c,d)来直接避免排序。更进一步的，为了避免回表，可以建立(c，d，a，b)这样的覆盖索引。程序使用c，d来定位需要得到的行的位置，然后直接从(c,d,a,b)这个索引中获取所有数据，效率最高。
    
    • 需要排序的列上需要建立索引

  title和film_id建有索引

  select film_id,description from sakila.film order by title limit 50,5;

  *注意*limit 子句总是最后执行，所以会有先扫描，description不能被覆盖到。

  select film.film_i,film.description 
  from saklia.flim
  inner join(
        select film_id from skalia.film
        order by title limit 50,5
  )as lim using(flim_id);   

  • 汇总表类似pg_class，自己维护一个常用的统计信息，周期扫表，存表，然后查这个统计表
  • 由于正常用户的访问，通常不会导致limit m，n的m很大，所以如果是业务要导全表的数据，那么可以类似这样的sql：

SELECT id, k, c,pad FROM fsbtest.aaa FORCE INDEX(PRIMARY) 
WHERE ((id >= '1604343')) AND ((id < '1605526'))

由于每个表都有主键，所以每次范围扫描都有一个明确的开始和结束位置，最终可以扫描完全表。这里不使用limit，而是每次select后，判断刚才的select得到的行数，动态的决定下次的id的最大值是多少。最终达到每次都取得近似的数据条数，例如1000条。通过这种方式，就很天然的避免了排序，缺点是每次返回的数据行的数目不确定。（当然这里的前提是id,order by的第一个参数）。比较通用的说法应该是，按照 order by的字段的范围上取，在where子句中
限制where子句的范围。
*使用 NoSql 例如redis的sorted set 存进去就天然有序，取出来的时候不用排序，但是需要应用双写，比较麻烦，使用场景也很特定。