智能SQL優(yōu)化與改寫(xiě)方案

1、智能SQL優(yōu)化與改寫(xiě)方案SOAR智能SQL優(yōu)化與改寫(xiě)SOAR痛點(diǎn)難點(diǎn)算法策略產(chǎn)品介紹競(jìng)品調(diào)研痛點(diǎn)難點(diǎn)未經(jīng)優(yōu)化的查詢SELECTCOUNT(DISTINCT user_id) AS TOTALFROMtblWHEREd = 3AND q = 4;例發(fā)布追加索引查詢優(yōu)化的痛點(diǎn)員：平參差不，學(xué)習(xí)成本具：動(dòng)化程度低，具雜散時(shí)間：事后緊急救，險(xiǎn)不可控查詢優(yōu)化的難點(diǎn)既要檢查語(yǔ)法，要判斷邏輯UPDATEtblSETcol1 = 1 AND col2 = 2WHEREcol3 = 3;幽靈般的隱式數(shù)據(jù)類型轉(zhuǎn)換UPDATEtblSETcol = 1WHEREuid = 312345678900;AND col2

2、 = 2)UPDATEtblSETcol1 = (1WHEREcol3 = 3;UPDATEtblSETcol = 1WHEREuid = “312345678900”;查詢優(yōu)化的難點(diǎn)復(fù)雜查詢的索引優(yōu)化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;不重不漏的索引優(yōu)化能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);競(jìng)品調(diào)研競(jìng)品調(diào)研SQL AdvisorIncepIonsqlautoreviewsqlcheckpt-query-advisorUDB Query Managerpg_index_advisor競(jìng)品調(diào)研SQL重寫(xiě)關(guān)系代數(shù)代價(jià)評(píng)估QUESTSentry One EverSQL索引建議讀法解析索引算法SQL-Advisor pg_ind

4、ex_advisor策略調(diào)優(yōu)關(guān)鍵字分析 啟發(fā)式算法pt-query-advisor sqlchecksql-tunning sqlautoreview IncepIon學(xué)習(xí)調(diào)研產(chǎn)品介紹功能特點(diǎn)使Go語(yǔ)開(kāi)發(fā)，基于抽象語(yǔ)法樹(shù)提供100+SQL改寫(xiě)建議 提供索引優(yōu)化建議提供EXPLAIN結(jié)果分析 支持SQL指紋支持SQL美化和壓縮 支持ALTER語(yǔ)句合并支持markdown, HTML多種格式報(bào)告支持DML轉(zhuǎn)SELECT量化標(biāo)準(zhǔn)，SQL打分支持對(duì)pt-query-digest和explain的 輸出內(nèi)容進(jìn)次分析優(yōu)化例SSELELECECT *TFROM lm WHERE length 120;*-

5、建議替換為指定列FROMfilm WHERElength 120;- 建議為該列添加索引命令行工具$ echo select * from film | soarVim插件WEB工具注：WEB具與內(nèi)部系統(tǒng)偶合，暫不對(duì)外開(kāi)源。輸出報(bào)告HTML格式報(bào)告可打印為PDF可定義CSS主題可定義Javascript美化產(chǎn)品價(jià)值動(dòng)生成SQL優(yōu)化報(bào)告 縮短單條SQL優(yōu)化耗時(shí)SQL主優(yōu)化 避免線上隱患SQL優(yōu)劣量化打分 代碼質(zhì)量更有保障有效貫徹SQL編程規(guī)范 不必死記硬背快速上算法策略體系架構(gòu)優(yōu)化算法啟發(fā)式算法SQL改寫(xiě)的100多條軍規(guī)正則(REGEXP)，抽象語(yǔ)法樹(shù)(AST)，測(cè)試環(huán)境多管下數(shù)據(jù)采樣算法元數(shù)據(jù)

6、收集采樣有效性隨機(jī)采樣算法索引優(yōu)化算法SQL轉(zhuǎn)寫(xiě)算法 簡(jiǎn)單查詢優(yōu)化 復(fù)雜查詢優(yōu)化啟發(fā)式算法序列化（Tokenizer）生成抽象語(yǔ)法樹(shù)（AST）Foreach HeurisIcRuleMaps（100+）建議使AS關(guān)鍵字顯聲明個(gè)別名 別名與原表名或列名重名不建議使前項(xiàng)通配符查找4.沒(méi)有通配符的LIKE查詢 .輸出建議京站抽象語(yǔ)法樹(shù)（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: 優(yōu)化建議, Case:SQL例,Func:(*Query

11、4Audit).函數(shù)名,優(yōu)化策略ALI (Alias, AS) 3項(xiàng)ALT (Alter) 4項(xiàng)ARG (Argument) 11項(xiàng)CLA (Classic) 17項(xiàng)COL (Column) 16項(xiàng)DIS (DisInct) 3項(xiàng)FUN (FuncIon) 6項(xiàng)JOI (Join) 8項(xiàng)KEY (Key) 9項(xiàng)KWR (Keyword) 3項(xiàng)LIT (Literal) 4項(xiàng)LCK(Lock)2項(xiàng)RES (Result) 7項(xiàng)SEC (Security) 3項(xiàng)STA (Standard) 3項(xiàng)SUB (Subquery) 6項(xiàng)TBL (Table) 5項(xiàng)注：以上統(tǒng)計(jì)數(shù)據(jù)截止2018年9月，建議類

12、型及數(shù)量還在不斷增加中MORE數(shù)據(jù)采樣SQL輸數(shù)據(jù)采樣算法為什么要對(duì)數(shù)據(jù)進(jìn)采樣?數(shù)據(jù)量大統(tǒng)計(jì)耗時(shí) 線上執(zhí)統(tǒng)計(jì)影響服務(wù)采樣數(shù)據(jù)量如何確定?怎樣采樣影響最?- 危險(xiǎn)的法SELECT * FROM tbl ORDER BY RAND() LIMIT r;- 推薦的做法SELECT * FROM tbl WHERE RAND() r/n LIMIT r;元數(shù)據(jù)/統(tǒng)計(jì)信息收集更新請(qǐng)求轉(zhuǎn)SELECT列信息提取WHEREJOINGROUP BYORDER BY元數(shù)據(jù)采集數(shù)據(jù)類型散粒度定義度字符集庫(kù)、表以及別名等信息索引優(yōu)化算法SQL化繁為簡(jiǎn)WHERE條件GROUP BYORDER BYJOIN條件索引合并索

13、引優(yōu)化算法簡(jiǎn)單查詢優(yōu)化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多值等值查詢條件等值查詢條件索引優(yōu)化算法簡(jiǎn)單查

15、詢優(yōu)化- 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語(yǔ)法暫不支持 SELECT * FROM tbl ORDER BY a ASC, b DESC - 8.0+支持SELECT * FROM tbl WHERE date LIKE 2016-12% - 時(shí)間數(shù)據(jù)類型隱式類型轉(zhuǎn)換- 如果使了OR操作符，對(duì)OR兩邊條件不進(jìn)索引優(yōu)化SELECT * FROM tbl WHERE col1 = xxx OR col2 = xxx;法使索引的查詢條件索引優(yōu)化算法簡(jiǎn)單查詢優(yōu)化WHERE條件等值查詢條件按散粒度排序添加索引非等值查詢條件按散粒度排序后取最大列GROUP BY依賴WHERE條件WHERE條件包含無(wú)法使用索引的列GROUP BY無(wú)法使

17、用索引WHERE條件所有列均為等值列可添加索引時(shí)GROUP BY列按順序添加索引GROUP BY使用了數(shù)學(xué)運(yùn)算或函數(shù)時(shí)無(wú)法添加索引ORDER BY依賴WHERE條件和GROUP BYWHERE條件或GROUP BY包含無(wú)法使用索引的列時(shí)ORDER BY列不添加索引WHERE條件所有列均可添加索引且無(wú)GROUP BY條件時(shí)ORDER BY列按順序添加索引ORDER BY使用了數(shù)學(xué)運(yùn)算或函數(shù)時(shí)無(wú)法添加索引ORDER BY多列排序方向不同時(shí)無(wú)法添加索引案例SELECTrelease_yearFROMfilmWHERElength = 123GROUP BYrelease_yearORDER BYla

18、nguage_idINDEX (length,release_year)SELECTrelease_yearFROMfilmWHERElength 123GROUP BYrelease_yearORDER BYlanguage_idINDEX (length,language_id)INDEX (length)索引優(yōu)化算法復(fù)雜查詢優(yōu)化JOINLEFT JOIN為右表加索引RIGHT JOIN為左表加索引INNER JOIN為兩張表加索引NATURAL的處理法參考前三條STRAIGHT_JOIN為后的表加索引UNION & SUBQUERY先將其拆成多條獨(dú)立的SELECT語(yǔ)句基于簡(jiǎn)單查詢索引優(yōu)化算法對(duì)單條SELECT查詢進(jìn)優(yōu)化SUBQUERY的連接列暫不考慮添加索引案例SELECT*FROMcity aINNER JOINcountr