索引优化建议

以下优化算法基于个人当前理解，能力有限，如有偏颇还请斧正。

简单查询索引优化

等值查询优化

  • 单列等值查询，为该等值列加索引
  • 多列等值查询，每列求取散粒度，按从大到小排序取前N列添加到索引（N可配置）
  1. SELECT * FROM tbl WHERE a = 123;
  2. SELECT * FROM tbl WHERE a = 123 AND b = 456;
  3. SELECT * FROM tbl WHERE a IS NULL;
  4. SELECT * FROM tbl WHERE a <=> 123;
  5. SELECT * FROM tbl WHERE a IS TRUE;
  6. SELECT * FROM tbl WHERE a IS FALSE;
  7. SELECT * FROM tbl WHERE a IS NOT TRUE;
  8. SELECT * FROM tbl WHERE a IS NOT FALSE;
  9. SELECT * FROM tbl WHERE a IN ("xxx"); -- IN单值

非等值查询优化

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

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

  1. SELECT * FROM tbl WHERE a >= 123 -- <, <=, >=, >, !=, <>
  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. 再将非等值查询优化的列追加在等值列索引后
  1. SELECT * FROM tbl WHERE c = 9 AND a > 12 AND b > 345; -- INDEX(c, a)或INDEX(c, b)

OR操作符

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

GROUP BY子句

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

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

ORDER BY子句

ORDER BY相关字段能否加入索引列表需要依赖WHERE子句和GROUP BY子句中的条件。当查询指定了WHERE条件，在满足WHERE子句只有等值查询且无GROUP BY子句时，可以对ORDER BY字段添加索引。当查询未指定WHERE条件，在满足无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;
  1. SELECT * FROM city a LEFT JOIN country b ON a.country_id=b.country_id
  2. UNION
  3. SELECT * FROM city a RIGHT JOIN country b ON a.country_id=b.country_id;
  5. 1. SELECT * FROM city a LEFT JOIN country b ON a.country_id=b.country_id;
  6. 2. SELECT * FROM city a RIGHT JOIN country b ON a.country_id=b.country_id;

无法使用索引的情况

如下类型的查询条件无法使用索引或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'
  6. SELECT * FROM tbl WHERE CAST(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)) ;

更新语句转换为只读查询

SOAR支持将DELETE， UPDATE， INSERT， REPLACE四种类型语句转换为SELECT查询。对转换后的SELECT查询进行索引优化。以下为转换示例。

  1. UPDATE film SET length = 10 WHERE language_id = 20;
  3. SELECT * FROM film WHERE language_id = 20;
  1. DELETE FROM film WHERE length > 100;
  3. SELECT * FROM film WHERE length > 100;
  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。用户可以通过指定-sampling参数开启数据采样。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.

随机采样

随机采样使用的SQL如下，其中变量r, n的含义见上面的说明。

  1. SELECT * FROM `tbl` WHERE RAND() < r LIMIT n;

索引去重

检查步骤

  1. 为查询语句可能使用索引的字段添加索引
  2. 枚举用到的所有库表的已知索引
  3. 判断所有新加的索引是否与已知索引重复
  4. 判断所有新加的索引之间是否存在索引重复

检查规则

  • PRIMARY > UNIQUE > KEY
  • 索引名称相同，即: idxA == idxA
  • (a, b) > (a)
  • (a, b), (b, a) 会给出警告，用户自行判断是否重复

不足

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