智能SQL优化与改写

1. 全球敏捷运维峰会北京站

2. 智能SQL优化与改写 SOAR 全球敏捷运维峰会北京站

3. 个⼈人介绍 ●  张良 ●  小米智能云DBA负责⼈人 ●  数据库运维&开发全球敏捷运维峰会北京站

4. 竞品调研痛点难点全球敏捷运维峰会北京站算法策略产品介绍

5. 痛点难点全球敏捷运维峰会北京站

6. 未经优化的查询 SELECT COUNT(DISTINCT user_id) AS TOTAL FROM tbl WHERE d=3 AND q = 4; 全球敏捷运维峰会北京站追加索引例⾏行发布

7. 查询优化的痛点 ●  ⼈人员：⽔水平参差不⻬齐，学习成本⾼高 ●  ⼯工具：⾃自动化程度低，⼯工具⼩小杂散 ●  时间：事后紧急救⽕火，⻛风险不可控全球敏捷运维峰会北京站

8. 查询优化的难点 ●  既要检查语法，⼜又要判断逻辑 ●  幽灵般的隐式数据类型转换 UPDATE tbl SET col1 = (1 AND col2 = 2 1 AND col2 = 2) WHERE col3 = 3; UPDATE tbl SET col = 1 WHERE uid = “312345678900”; 312345678900; 全球敏捷运维峰会北京站

9. 查询优化的难点 ●  复杂查询的索引优化 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 desc; ●  不重不漏的索引优化能⼒力 ALTER TABLE tbl ALTER TABLE tbl ADD NDEX dx_a a), ADD IINDEX iidx_a ((a), ADD NDEX dx_a_b a, b); ADD IINDEX iidx_a_b ((a, b); 全球敏捷运维峰会北京站

10. 竞品调研全球敏捷运维峰会北京站

11. 竞品调研 sqlcheck SQL Advisor IncepIon sqlautoreview pg_index_advisor pt-‐query-‐advisor UDB Query Manager 全球敏捷运维峰会北京站

12. 竞品调研 SQL重写索引建议策略调优关键字分析启发式算法 pt-‐query-‐advisor sqlcheck sql-‐tunning sqlautoreview IncepIon 读法解析索引算法 SQL-‐Advisor pg_index_advisor 全球敏捷运维峰会北京站关系代数代价评估 QUEST Sentry One EverSQL

13. 学习调研全球敏捷运维峰会北京站

14. 产品介绍全球敏捷运维峰会北京站

15. 功能特点 ●  使⽤用Go语⾔言开发，基于抽象语法树 ●  提供100+SQL改写建议 ●  提供索引优化建议 ●  提供EXPLAIN结果分析 ●  支持SQL指纹 ●  支持SQL美化和压缩 ●  支持ALTER语句合并 ●  支持markdown, HTML多种格式报告 ●  支持DML转SELECT ●  量化标准，SQL打分 ●  ⽀支持对pt-‐query-‐digest和explain的输出内容进⾏行⼆二次分析全球敏捷运维峰会北京站

16. 优化⽰示例 SELECT * FROM `ﬁlm` WHERE `length` > 120; SELECT * -- 建议替换为指定列 FROM `film` WHERE `length` > 120; -- 建议为该列添加索引全球敏捷运维峰会北京站

17. 命令行工具 $ echo "select * from film" | soar 全球敏捷运维峰会北京站

18. Vim插件全球敏捷运维峰会北京站

19. WEB工具注：WEB⼯工具与内部系统偶合，暂不对外开源。全球敏捷运维峰会北京站

20. 输出报告 ●  ●  ●  ●  HTML格式报告可打印为PDF 可⾃自定义CSS主题可⾃自定义Javascript美化全球敏捷运维峰会北京站

21. 全球敏捷运维峰会北京站

22. 产品价值⾃自动⽣生成SQL优化报告缩短单条SQL优化耗时有效贯彻SQL编程规范不必死记硬背快速上⼿手 SQL⾃自主优化避免线上隐患 SQL优劣量化打分代码质量更有保障全球敏捷运维峰会北京站

23. 算法策略全球敏捷运维峰会北京站

24. 体系架构全球敏捷运维峰会北京站

25. 优化算法 ●  启发式算法 ○  ○  SQL改写的100多条军规正则(REGEXP)，抽象语法树(AST)，测试环境多管⻬齐下 ●  数据采样算法 ○  ○  ○  元数据收集采样有效性随机采样算法 ●  索引优化算法 ○  ○  ○  SQL转写算法简单查询优化复杂查询优化全球敏捷运维峰会北京站

26. 启发式算法 1.  序列化（Tokenizer） 2.  ⽣生成抽象语法树（AST） 3.  Foreach HeurisIcRuleMaps（100+） ○  建议使⽤用AS关键字显⽰示声明⼀一个别名 ○  别名与原表名或列名重名 ○  不建议使⽤用前项通配符查找 ○  没有通配符的LIKE查询 ○  … ... 4.  输出建议全球敏捷运维峰会北京站

27. 抽象语法树（AST） &sqlparser.Select{ Cache: "", Comments: nil, Distinct: "", Hints: "", SelectExprs: { &sqlparser.StarExpr{}, }, From: { &sqlparser.AliasedTableExpr{ Expr: sqlparser.TableName{ Name: sqlparser.TableIdent{v:"ﬁlm"}, Qualiﬁer: sqlparser.TableIdent{}, SELECT }, Partitions: nil, As: sqlparser.TableIdent{}, Hints: (*sqlparser.IndexHints)(nil), * }, }, Where: &sqlparser.Where{ Type: "where", Expr: &sqlparser.ComparisonExpr{ Operator: ">", Left: &sqlparser.ColName{ Metadata: nil, Name: sqlparser.ColIdent{ _: { }, FROM `film` val: "length", lowered: "", }, Qualiﬁer: sqlparser.TableName{}, }, Right: &sqlparser.SQLVal{ Type: 1, WHERE Val: {0x31, 0x32, 0x30}, }, Escape: nil, `length` > 120; }, }, GroupBy: nil, Having: (*sqlparser.Where)(nil), OrderBy: nil, Limit: (*sqlparser.Limit)(nil), Lock: "", 全球敏捷运维峰会北京站 }

28. HeurisDcRule type Rule struct { Item string `json:"Item"` Severity string `json:"Severity"` Summary string `json:"Summary"` Content string `json:"Content"` Case string `json:"Case"` Func func(*Query4Audit) string `json:"-"` } // CLA.002 func (q *Query4Audit) RuleOrderByRand() string { var rule = "OK" sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) { switch n := node.(type) { case sqlparser.OrderBy: for _, order := range n { switch expr := order.Expr.(type) { c s * qp r e. u c x r a e s l as rF n E p: "CLA.002": { Item: "CLA.002", Severity: "note", Summary: "摘要", Content: "优化建议", Case: "SQL⽰示例", Func: (*Query4Audit).函数名, } if expr.Name.String() == "rand" { rule = "CLA.002" return false, nil } } } }, q.Stmt) return rule 全球敏捷运维峰会北京站 } return true, nil }

29. 优化策略 ●  ●  ●  ●  ●  ●  ●  ●  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⽉月，建议类型及数量还在不断增加中全球敏捷运维峰会北京站

30. MORE 全球敏捷运维峰会北京站

31. 数据采样 SQL输⼊入全球敏捷运维峰会北京站

32. 数据采样算法 ●  为什么要对数据进⾏行采样? ○  ○  数据量⼤大统计耗时⻓长线上执⾏行统计影响服务 ●  采样数据量如何确定? ●  怎样采样影响最⼩小? -- 危险的⽅方法 SELECT * FROM tbl ORDER BY RAND() LIMIT r; -- 推荐的做法 SELECT * FROM `tbl` WHERE RAND() < r/n LIMIT r; 全球敏捷运维峰会北京站

33. 元数据/统计信息收集 ●  更新请求转SELECT ●  列信息提取 ○  ○  ○  ○  WHERE JOIN GROUP BY ORDER BY ●  元数据采集 ○  ○  ○  ○  ○  数据类型散粒度定义⻓长度字符集库、表以及别名等信息全球敏捷运维峰会北京站

34. 索引优化算法 1. SQL化繁为简 2. WHERE条件 3. GROUP BY 4. ORDER BY 5. JOIN条件 6. 索引合并全球敏捷运维峰会北京站

35. 索引优化算法——简单查询优化等值查询条件 SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT * * * * * * * * * FROM FROM FROM FROM FROM FROM FROM FROM FROM tbl tbl tbl tbl tbl tbl tbl tbl tbl ⾮非等值查询条件 WHERE WHERE WHERE WHERE WHERE WHERE WHERE WHERE WHERE a a a a a a a a a = 123; = 123 AND b = 456; IS NULL; <=> 123; IS TRUE; IS FALSE; IS NOT TRUE; IS NOT FALSE; IN ("xxx"); -- IN单值 SELECT * FROM <> SELECT * FROM BETWEEN SELECT * FROM SELECT * FROM SELECT * FROM 全球敏捷运维峰会北京站 tbl WHERE a >= 123 -- <, <=, >=, >, !=, tbl WHERE a BETWEEN 22 AND 44; -- NOT tbl WHERE a LIKE 'blah%'; -- NOT LIKE tbl WHERE a IS NOT NULL; tbl WHERE a IN (”x”, ”x"); -- IN多值

36. 索引优化算法——简单查询优化⽆无法使⽤用索引的查询条件 -- 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 …) = 'xxx' SELECT * FROM tbl where a NOT IN() -- SOAR不⽀支持索引建议 SELECT * FROM tbl WHERE a = 'xxx' COLLATE xxx -- 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'; 全球敏捷运维峰会北京站

37. 索引优化算法——简单查询优化 ●  WHERE条件 ○  等值查询条件按散粒度排序添加索引 ○  非等值查询条件按散粒度排序后取最大列 ●  GROUP BY依赖WHERE条件 ○  ○  ○  WHERE条件包含无法使用索引的列GROUP BY无法使用索引 WHERE条件所有列均为等值列可添加索引时GROUP BY列按顺序添加索引 GROUP BY使用了数学运算或函数时无法添加索引 ●  ORDER BY依赖WHERE条件和GROUP BY ○  ○  ○  ○  WHERE条件或GROUP BY包含无法使用索引的列时ORDER BY列不添加索引 WHERE条件所有列均可添加索引且无GROUP BY条件时ORDER BY列按顺序添加索引 ORDER BY使用了数学运算或函数时无法添加索引 ORDER BY多列排序方向不同时无法添加索引全球敏捷运维峰会北京站

38. 案例 SELECT release_year FROM ﬁlm WHERE length = 123 GROUP BY release_year ORDER BY language_id INDEX (`length`,`release_year`) SELECT release_year FROM ﬁlm WHERE length > 123 GROUP BY release_year ORDER BY language_id INDEX (`length`,`language_id`) 全球敏捷运维峰会北京站 INDEX (`length`)

39. 索引优化算法——复杂查询优化 ●  JOIN ○  ○  ○  ○  ○  LEFT JOIN为右表加索引 RIGHT JOIN为左表加索引 INNER JOIN为两张表加索引 NATURAL的处理⽅方法参考前三条 STRAIGHT_JOIN为后⾯面的表加索引 ●  UNION & SUBQUERY ○  ○  ○  先将其拆成多条独立的SELECT语句基于简单查询索引优化算法对单条SELECT查询进⾏行优化 SUBQUERY的连接列暂不考虑添加索引全球敏捷运维峰会北京站

40. 案例 SELECT * FROM city a INNERJOIN RIGHT LEFT JOIN country b ON a.country_id = b.country_id; INDEX (a.`country_id`) INDEX(a.`country_id`) b.`country_id`), b.`country_id`) 全球敏捷运维峰会北京站

41. 未来规划 Proﬁle + Trace 强化启发式算法整合Proﬁle及Trace 更丰富的问题定位⼿手段更精准的优化⽅方案选择强化SQL改写能力支持SUB/JOIN互转 SQL改写前后效率打分全球敏捷运维峰会北京站美化建议展现形式规则数200+, 300+ … 线上操作支持DDL在线操作支持DML备份回滚支持SQL结果检查

42. Thanks zhangliang3@xiaomi.com 全球敏捷运维峰会北京站

43. THANK YOU！全球敏捷运维峰会北京站