索引优化建议

    • 单列等值查询，为该等值列加索引
    • 多列等值查询，每列求取散粒度，按从大到小排序取前N列添加到索引（N可配置）

    非等值查询优化

    • 单列非等值查询，为该非等值列加索引
    • 多列非等值查询，每列求取散粒度，为散粒度最大的列加索引。

    思考：对于多列非等值，为filtered最小列加索引可能比较好。因为输入可变，所以现在只按散粒度排序。对于高版本MySQL如果开启了Index Merge，考虑为非等值列加单列索引可能会比较好。

    2. SELECT * FROM tbl WHERE a BETWEEN 22 AND 44; -- NOT BETWEEN
    3. SELECT * FROM tbl WHERE a LIKE 'blah%'; -- NOT LIKE
    4. SELECT * FROM tbl WHERE a IS NOT NULL;
    5. SELECT * FROM tbl WHERE a IN ("xxx"); -- IN多值

    等值 & 非等值组合查询优化

    1. 先按等值查询优化为等值列添加索引
    2. 再将非等值查询优化的列追加在等值列索引后

      OR操作符

      如果使用了OR操作符，即使OR两边是简单的查询条件也会对优化器带来很大的困难。一般对OR的优化需要依赖UNION ALL或Index Merge等多索引访问技术来实现。SOAR目前不会对使用OR操作符连接的字段进行索引优化。

      GROUP BY相关字段能否加入索引列表需要依赖WHERE子句中的条件。当查询指定了WHERE条件，在满足WHERE子句只有等值查询时，可以对GROUP BY字段添加索引。当查询未指定WHERE条件，可以直接对GROUP BY字段添加索引。

      • 按照GROPU BY的先后顺序添加索引
      • GROUP BY字段出现常量，数学运算或函数运算时会给出警告

      ORDER BY子句

      • 多个字段之间如果指定顺序相同，按照ORDER BY的先后顺序添加索引
      • 多个字段之间如果指定顺序不同，所有ORDER BY字段都不添加索引
      • ORDER BY字段出现常量，数学运算或函数运算时会给出警告

      复杂查询索引优化

      JOIN索引优化算法

      • LEFT JOIN为右表加索引
      • RIGHT JOIN为左表加索引
      • INNER JOIN两张表都加索引
      • NATURAL的处理方法参考前三条
      • STRAIGHT_JOIN为后面的表加索引

      SUBQUERY和UNION的复杂查询

      对于使用了IN，EXIST等词的SUBQUERY或UNION类型的SQL，先将其拆成多条独立的SELECT语句。然后基于上面简单查询索引优化算法，对单条SELECT查询进行优化。SUBQUERY的连接列暂不考虑添加索引。

      1. SELECT * FROM film WHERE language_id = (SELECT language_id FROM language LIMIT 1);
      3. 1. SELECT * FROM film;
      4. 2. SELECT language_id FROM language LIMIT 1;

      如下类型的查询条件无法使用索引或SOAR无法给出正确的索引建议。

      1. -- MySQL无法使用索引
      2. SELECT * FROM tbl WHERE a LIKE '%blah%';
      3. SELECT * FROM tbl WHERE a IN (SELECT...)
      4. SELECT * FROM tbl WHERE DATE(dt) = 'xxx'
      5. SELECT * FROM tbl WHERE LOWER(s) = 'xxx'
      7. SELECT * FROM tbl where a NOT IN()
      8. -- SOAR不支持的索引建议
      9. SELECT * FROM tbl WHERE a = 'xxx' COLLATE xxx -- vitess语法暂不支持
      10. SELECT * FROM tbl ORDER BY a ASC, b DESC -- 8.0+支持
      11. SELECT * FROM tbl WHERE `date` LIKE '2016-12%' -- 时间数据类型隐式类型转换

      索引长度限制

      由于索引长度受数据库版本及不同配置参数影响，参考InnoDB限制。这里将索引长度限制定义为可配置值，用户可以根据实际情况进行设置。

      • 通过-max-index-bytes配置每列索引最大长度，默认为767 Bytes
      • 超过单列索引最大长度限制后程序会自动添加该列的前缀索引（max-index-bytes/CHARSET_Maxlen）
      • 通过-max-index-bytes-percolumn配置多列索引加各最大长度，默认为3072 Bytes
      • 超过多列索引最大长度限制后，由程序生成的ALTER语句会将每列前缀索引长度指定为N，用户自行调整
      1. ALTER TABLE `sakila`.`film_text` add index `idx_description` (`description`(255)) ;
      1. UPDATE film SET length = 10 WHERE language_id = 20;
      1. INSERT INTO city (country_id) SELECT country_id FROM country;
      3. SELECT country_id FROM country;
      1. REPLACE INTO city (country_id) SELECT country_id FROM country;
      3. SELECT country_id FROM country;

      散粒度计算

      Cardinality = ColumnDistinctCount/TableTotalRows * 100%

      由于直接对线上表进行COUNT(DISTINCT)操作会影响数据库请求执行效率，因此默认各列的散粒度均为1。用户可以通过指定参数开启数据采样。SOAR会将线上数据随机采样至测试环境求取散粒度。

      数据采样算法

      以下说明摘抄自PostgreSQL数据直方图采样算法。默认k(-sampling-statistic-target)设置为100，即最多采样3万行记录。

      1.  The following choice of minrows is based on the paper
      2.  "Random sampling for histogram construction: how much is enough?"
      3.  by Surajit Chaudhuri, Rajeev Motwani and Vivek Narasayya, in
      4.  Proceedings of ACM SIGMOD International Conference on Management
      5.  of Data, 1998, Pages 436-447.  Their Corollary 1 to Theorem 5
      6.  says that for table size n, histogram size k, maximum relative
      7.  error in bin size f, and error probability gamma, the minimum
      8.  random sample size is
      9.       r = 4 * k * ln(2*n/gamma) / f^2
      10.  Taking f = 0.5, gamma = 0.01, n = 10^6 rows, we obtain
      11.       r = 305.82 * k
      12.  Note that because of the log function, the dependence on n is
      13.  quite weak; even at n = 10^12, a 300*k sample gives <= 0.66
      14.  bin size error with probability 0.99.  So there's no real need to
      15.  scale for n, which is a good thing because we don't necessarily
      16.  know it at this point.

      随机采样

      检查步骤

      1. 为查询语句可能使用索引的字段添加索引
      2. 枚举用到的所有库表的已知索引
      3. 判断所有新加的索引是否与已知索引重复
      4. 判断所有新加的索引之间是否存在索引重复
      • PRIMARY > UNIQUE > KEY
      • 索引名称相同，即: idxA == idxA
      • (a, b) > (a)
      • (a, b), (b, a) 会给出警告，用户自行判断是否重复

      不足

      • 目前只支持针对InnoDB引擎添加索引建议，不支持FULLTEXT, SPATIAL等其他类型索引
      • 暂不支持索引覆盖（Covering）