智能SQL优化与改写方案

1、智能SQL优化与改写方案SOAR智能SQL优化与改写SOAR痛点难点算法策略产品介绍竞品调研痛点难点未经优化的查询SELECTCOUNT(DISTINCT user_id) AS TOTALFROMtblWHEREd = 3AND q = 4;例发布追加索引查询优化的痛点员：平参差不，学习成本具：动化程度低，具杂散时间：事后紧急救，险不可控查询优化的难点既要检查语法，要判断逻辑UPDATEtblSETcol1 = 1 AND col2 = 2WHEREcol3 = 3;幽灵般的隐式数据类型转换UPDATEtblSETcol = 1WHEREuid = 312345678900;AND col2

2、 = 2)UPDATEtblSETcol1 = (1WHEREcol3 = 3;UPDATEtblSETcol = 1WHEREuid = “312345678900”;查询优化的难点复杂查询的索引优化SELECT id, name FROM( SELECT address FROM customer_list WHERE SID = 1 ORDER BY phone limit 50, 10) a JOIN customer_list l ON (a.address = l.address) JOIN city c ON (c.city = l.city) ORDER BY phone de

3、sc;不重不漏的索引优化能ALTER TABLE tblADD INDEX idx_a (a),ADD INDEX idx_a_b (a, b);ALTER TABLE tblADD INDEX idx_a (a),ADD INDEX idx_a_b (a, b);竞品调研竞品调研SQL AdvisorIncepIonsqlautoreviewsqlcheckpt-query-advisorUDB Query Managerpg_index_advisor竞品调研SQL重写关系代数代价评估QUESTSentry One EverSQL索引建议读法解析索引算法SQL-Advisor pg_ind

4、ex_advisor策略调优关键字分析 启发式算法pt-query-advisor sqlchecksql-tunning sqlautoreview IncepIon学习调研产品介绍功能特点使Go语开发，基于抽象语法树提供100+SQL改写建议 提供索引优化建议提供EXPLAIN结果分析 支持SQL指纹支持SQL美化和压缩 支持ALTER语句合并支持markdown, HTML多种格式报告支持DML转SELECT量化标准，SQL打分支持对pt-query-digest和explain的 输出内容进次分析优化例SSELELECECT *TFROM lm WHERE length 120;*-

5、建议替换为指定列FROMfilm WHERElength 120;- 建议为该列添加索引命令行工具$ echo select * from film | soarVim插件WEB工具注：WEB具与内部系统偶合，暂不对外开源。输出报告HTML格式报告可打印为PDF可定义CSS主题可定义Javascript美化产品价值动生成SQL优化报告 缩短单条SQL优化耗时SQL主优化 避免线上隐患SQL优劣量化打分 代码质量更有保障有效贯彻SQL编程规范 不必死记硬背快速上算法策略体系架构优化算法启发式算法SQL改写的100多条军规正则(REGEXP)，抽象语法树(AST)，测试环境多管下数据采样算法元数据

6、收集采样有效性随机采样算法索引优化算法SQL转写算法 简单查询优化 复杂查询优化启发式算法序列化（Tokenizer）生成抽象语法树（AST）Foreach HeurisIcRuleMaps（100+）建议使AS关键字显声明个别名 别名与原表名或列名重名不建议使前项通配符查找4.没有通配符的LIKE查询 .输出建议京站抽象语法树（AST）SELECT* FROMfilm WHERElength 120;&sqlparser.Select Cache: , Comments: nil,Distinct: ,Hints: ,SelectExprs: &sqlparser.StarExpr, ,Fr

7、om: &sqlparser.AliasedTableExprExpr: sqlparser.TableNameName: sqlparser.TableIdentv:film, Qualifier: sqlparser.TableIdent,Partitions: nil,As:sqlparser.TableIdent, Hints: (*sqlparser.IndexHints)(nil),Where: &sqlparser.Where Type: where,Expr: &sqlparser.ComparisonExprOperator: ,Left: &sqlparser.ColNam

8、eMetadata: nil,Name: sqlparser.ColIdent_: ,val: length,lowered: ,Qualifier: sqlparser.TableName,Right: &sqlparser.SQLValType: 1,Val: 0 x31, 0 x32, 0 x30,Escape: nil,GroupBy: nil,Having: (*sqlparser.Where)(nil), OrderBy: nil,Limit: (*sqlparser.Limit)(nil),Lock: ,HeurisDcRule/ CLA.002func (q *Query4Au

9、dit) RuleOrderByRand() string var rule = OKsqlparser.Walk(func(node sqlparser.SQLNode) (kontinuebool, err error) switch n := node.(type) case sqlparser.OrderBy:for _, order := range n switchexpr := order.Expr.(type) case*sqlparser.FuncExpr:if expr.Name.String() = rand rule = CLA.002return false, nil

10、return true, nil, q.Stmt)return ruletype Rule struct Itemstring Severity string Summary string Content stringjson:Itemjson:Severityjson:Summaryjson:ContentCase stringjson:Case Funcfunc(*Query4Audit) stringjson:-CLA.002: Item:CLA.002,Severity: note,Summary: 摘 要 , Content: 优化建议, Case:SQL例,Func:(*Query

11、4Audit).函数名,优化策略ALI (Alias, AS) 3项ALT (Alter) 4项ARG (Argument) 11项CLA (Classic) 17项COL (Column) 16项DIS (DisInct) 3项FUN (FuncIon) 6项JOI (Join) 8项KEY (Key) 9项KWR (Keyword) 3项LIT (Literal) 4项LCK(Lock)2项RES (Result) 7项SEC (Security) 3项STA (Standard) 3项SUB (Subquery) 6项TBL (Table) 5项注：以上统计数据截止2018年9月，建议类

12、型及数量还在不断增加中MORE数据采样SQL输数据采样算法为什么要对数据进采样?数据量大统计耗时 线上执统计影响服务采样数据量如何确定?怎样采样影响最?- 危险的法SELECT * FROM tbl ORDER BY RAND() LIMIT r;- 推荐的做法SELECT * FROM tbl WHERE RAND() r/n LIMIT r;元数据/统计信息收集更新请求转SELECT列信息提取WHEREJOINGROUP BYORDER BY元数据采集数据类型散粒度定义度字符集库、表以及别名等信息索引优化算法SQL化繁为简WHERE条件GROUP BYORDER BYJOIN条件索引合并索

13、引优化算法简单查询优化SELECT * FROM tbl WHERE a = 123;SELECT * FROM tbl WHERE a = 123 AND b = 456;SELECT * FROM tbl WHERE a IS NULL; SELECT * FROM tbl WHERE a 123; SELECT * FROM tbl WHERE a IS TRUE; SELECT * FROM tbl WHERE a IS FALSE; SELECT * FROM tbl WHERE a IS NOT TRUE; SELECT * FROM tbl WHERE a IS NOT FALS

14、E;SELECT * FROM tbl WHERE a IN (xxx); - IN单值SELECT * FROM tbl WHERE a = 123 - , =, , !=,SELECT * FROM tbl WHERE a BETWEEN 22 AND 44; - NOT BETWEENSELECT * FROM tbl WHERE a LIKE blah%; - NOT LIKE SELECT * FROM tbl WHERE a IS NOT NULL;SELECT * FROM tbl WHERE a IN (”x”, ”x); - IN多值等值查询条件等值查询条件索引优化算法简单查

15、询优化- MySQL法使索引SELECT * FROM tbl WHERE a LIKE %blah%;SELECT * FROM tbl WHERE a IN (SELECT.) SELECT * FROM tbl WHERE DATE(dt) = xxx SELECT * FROM tbl WHERE LOWER(s) = xxx SELECT * FROM tbl WHERE CAST(s ) = xxxSELECT * FROM tbl where a NOT IN()- SOAR不支持索引建议SELECT * FROM tbl WHERE a = xxx COLLATE xxx -

16、vitess语法暂不支持 SELECT * FROM tbl ORDER BY a ASC, b DESC - 8.0+支持SELECT * FROM tbl WHERE date LIKE 2016-12% - 时间数据类型隐式类型转换- 如果使了OR操作符，对OR两边条件不进索引优化SELECT * FROM tbl WHERE col1 = xxx OR col2 = xxx;法使索引的查询条件索引优化算法简单查询优化WHERE条件等值查询条件按散粒度排序添加索引非等值查询条件按散粒度排序后取最大列GROUP BY依赖WHERE条件WHERE条件包含无法使用索引的列GROUP BY无法使

17、用索引WHERE条件所有列均为等值列可添加索引时GROUP BY列按顺序添加索引GROUP BY使用了数学运算或函数时无法添加索引ORDER BY依赖WHERE条件和GROUP BYWHERE条件或GROUP BY包含无法使用索引的列时ORDER BY列不添加索引WHERE条件所有列均可添加索引且无GROUP BY条件时ORDER BY列按顺序添加索引ORDER BY使用了数学运算或函数时无法添加索引ORDER BY多列排序方向不同时无法添加索引案例SELECTrelease_yearFROMfilmWHERElength = 123GROUP BYrelease_yearORDER BYla

18、nguage_idINDEX (length,release_year)SELECTrelease_yearFROMfilmWHERElength 123GROUP BYrelease_yearORDER BYlanguage_idINDEX (length,language_id)INDEX (length)索引优化算法复杂查询优化JOINLEFT JOIN为右表加索引RIGHT JOIN为左表加索引INNER JOIN为两张表加索引NATURAL的处理法参考前三条STRAIGHT_JOIN为后的表加索引UNION & SUBQUERY先将其拆成多条独立的SELECT语句基于简单查询索引优化算法对单条SELECT查询进优化SUBQUERY的连接列暂不考虑添加索引案例SELECT*FROMcity aINNER JOINcountr