fix XiaoMi#98

XiaoMi#98
jerry-00 · Apr 18, 2019 · 5533b04 · 5533b04
1 parent ba20f34
commit 5533b04
Show file tree

Hide file tree

Showing 14 changed files with 545 additions and 442 deletions.
diff --git a/advisor/explainer.go b/advisor/explainer.go
@@ -222,7 +222,7 @@ func DigestExplainText(text string) {
 			return
 		}
 		expSuggest := ExplainAdvisor(explainInfo)
-		_, output := FormatSuggest("", common.Config.ReportType, expSuggest)
+		_, output := FormatSuggest("", "", common.Config.ReportType, expSuggest)
 		if common.Config.ReportType == "html" {
 			fmt.Println(common.MarkdownHTMLHeader())
 			fmt.Println(common.Markdown2HTML(output))

diff --git a/advisor/rules.go b/advisor/rules.go
@@ -1202,7 +1202,7 @@ func InBlackList(sql string) bool {
 }
 
 // FormatSuggest 格式化输出优化建议
-func FormatSuggest(sql string, format string, suggests ...map[string]Rule) (map[string]Rule, string) {
+func FormatSuggest(sql string, currentDB string, format string, suggests ...map[string]Rule) (map[string]Rule, string) {
 	common.Log.Debug("FormatSuggest, Query: %s", sql)
 	var fingerprint, id string
 	var buf []string
@@ -1252,17 +1252,7 @@ func FormatSuggest(sql string, format string, suggests ...map[string]Rule) (map[
 	common.Log.Debug("FormatSuggest, format: %s", format)
 	switch format {
 	case "json":
-		js, err := json.MarshalIndent(Result{
-			ID:          id,
-			Fingerprint: fingerprint,
-			Sample:      sql,
-			Suggest:     suggest,
-		}, "", "  ")
-		if err == nil {
-			buf = append(buf, fmt.Sprintln(string(js)))
-		} else {
-			common.Log.Error("FormatSuggest json.Marshal Error: %v", err)
-		}
+		buf = append(buf, formatJSON(sql, currentDB, suggest))
 
 	case "text":
 		for item, rule := range suggest {
@@ -1452,6 +1442,80 @@ func FormatSuggest(sql string, format string, suggests ...map[string]Rule) (map[
 	return suggest, str
 }
 
+// JSONSuggest json format suggestion
+type JSONSuggest struct {
+	ID             string   `json:"ID"`
+	Fingerprint    string   `json:"Fingerprint"`
+	Score          int      `json:"Score"`
+	Sample         string   `json:"Sample"`
+	Explain        []Rule   `json:"Explain"`
+	HeuristicRules []Rule   `json:"HeuristicRules"`
+	IndexRules     []Rule   `json:"IndexRules"`
+	Tables         []string `json:"Tables"`
+}
+
+func formatJSON(sql string, db string, suggest map[string]Rule) string {
+	var id, fingerprint, result string
+
+	fingerprint = query.Fingerprint(sql)
+	id = query.Id(fingerprint)
+
+	sug := JSONSuggest{
+		ID:          id,
+		Fingerprint: fingerprint,
+		Sample:      sql,
+		Tables:      ast.SchemaMetaInfo(sql, db),
+	}
+
+	// Explain info
+	var sortItem []string
+	for item := range suggest {
+		if strings.HasPrefix(item, "EXP") {
+			sortItem = append(sortItem, item)
+		}
+	}
+	sort.Strings(sortItem)
+	for _, i := range sortItem {
+		sug.Explain = append(sug.Explain, suggest[i])
+	}
+	sortItem = make([]string, 0)
+
+	// Index advisor
+	for item := range suggest {
+		if strings.HasPrefix(item, "IDX") {
+			sortItem = append(sortItem, item)
+		}
+	}
+	sort.Strings(sortItem)
+	for _, i := range sortItem {
+		sug.IndexRules = append(sug.IndexRules, suggest[i])
+	}
+	sortItem = make([]string, 0)
+
+	// Heuristic rules
+	for item := range suggest {
+		if !strings.HasPrefix(item, "EXP") && !strings.HasPrefix(item, "IDX") {
+			if strings.HasPrefix(item, "ERR") && suggest[item].Content == "" {
+				continue
+			}
+			sortItem = append(sortItem, item)
+		}
+	}
+	sort.Strings(sortItem)
+	for _, i := range sortItem {
+		sug.HeuristicRules = append(sug.HeuristicRules, suggest[i])
+	}
+	sortItem = make([]string, 0)
+
+	js, err := json.MarshalIndent(sug, "", "  ")
+	if err == nil {
+		result = fmt.Sprint(string(js))
+	} else {
+		common.Log.Error("formatJSON json.Marshal Error: %v", err)
+	}
+	return result
+}
+
 // ListHeuristicRules 打印支持的启发式规则，对应命令行参数-list-heuristic-rules
 func ListHeuristicRules(rules ...map[string]Rule) {
 	switch common.Config.ReportType {

diff --git a/advisor/testdata/TestDigestExplainText.golden b/advisor/testdata/TestDigestExplainText.golden
@@ -3,7 +3,7 @@
 | id | select\_type | table | partitions | type | possible_keys | key | key\_len | ref | rows | filtered | scalability | Extra |
 |---|---|---|---|---|---|---|---|---|---|---|---|---|
 | 1  | SIMPLE | *country* | NULL | index | PRIMARY,<br>country\_id | country | 152 | NULL | 0 | 0.00% | ☠️ **O(n)** | Using index |
-| 1  | SIMPLE | *city* | NULL | ref | idx\_fk\_country\_id,<br>idx\_country\_id\_city,<br>idx\_all,<br>idx\_other | idx\_fk\_country\_id | 2 | sakila.country.country\_id | 0 | 0.00% | ☠️ **O(n)** | Using index |
+| 1  | SIMPLE | *city* | NULL | ref | idx\_fk\_country\_id,<br>idx\_country\_id\_city,<br>idx\_all,<br>idx\_other | idx\_fk\_country\_id | 2 | sakila.country.country\_id | 0 | 0.00% | O(log n) | Using index |
 
 
 
@@ -105,7 +105,7 @@ a:link,a:visited{text-decoration:none}h3,h4{margin-top:2em}h5,h6{margin-top:20px
 <td>sakila.country.country_id</td>
 <td>0</td>
 <td>0.00%</td>
-<td>☠️ <strong>O(n)</strong></td>
+<td>O(log n)</td>
 <td>Using index</td>
 </tr>
 </tbody>

diff --git a/cmd/soar/soar.go b/cmd/soar/soar.go
@@ -37,11 +37,13 @@ func main() {
 	// 全局变量
 	var err error
 	var sql string                                            // 单条评审指定的 sql 或 explain
+	var currentDB string                                      // 当前 SQL 使用的 database
 	sqlCounter := 1                                           // SQL 计数器
 	lineCounter := 1                                          // 行计数器
 	var alterSQLs []string                                    // 待评审的 SQL 中所有 ALTER 请求
 	alterTableTimes := make(map[string]int)                   // 待评审的 SQL 中同一经表 ALTER 请求计数器
 	suggestMerged := make(map[string]map[string]advisor.Rule) // 优化建议去重, key 为 sql 的 fingerprint.ID
+	var suggestStr []string                                   // string 形式格式化之后的优化建议，用于 -report-type json
 	tables := make(map[string][]string)                       // SQL 使用的库表名
 
 	// 配置文件&命令行参数解析
@@ -74,7 +76,7 @@ func main() {
 	// 对指定的库表进行索引重复检查
 	if common.Config.ReportType == "duplicate-key-checker" {
 		dupKeySuggest := advisor.DuplicateKeyChecker(rEnv)
-		_, str := advisor.FormatSuggest("", common.Config.ReportType, dupKeySuggest)
+		_, str := advisor.FormatSuggest("", currentDB, common.Config.ReportType, dupKeySuggest)
 		if str == "" {
 			fmt.Printf("%s/%s 未发现重复索引\n", common.Config.OnlineDSN.Addr, common.Config.OnlineDSN.Schema)
 		} else {
@@ -129,6 +131,7 @@ func main() {
 
 		// +++++++++++++++++++++小工具集[开始]+++++++++++++++++++++++{
 		fingerprint := strings.TrimSpace(query.Fingerprint(sql))
+		currentDB = env.CurrentDB(sql, currentDB)
 		switch common.Config.ReportType {
 		case "fingerprint":
 			// SQL 指纹
@@ -176,6 +179,7 @@ func main() {
 				continue
 			}
 		}
+		tables[id] = ast.SchemaMetaInfo(sql, currentDB)
 		// +++++++++++++++++++++小工具集[结束]+++++++++++++++++++++++}
 
 		// +++++++++++++++++++++语法检查[开始]+++++++++++++++++++++++{
@@ -202,8 +206,6 @@ func main() {
 
 		switch common.Config.ReportType {
 		case "tables":
-			env.ChangeDB(vEnv.Connector, q.Query)
-			tables[id] = ast.SchemaMetaInfo(sql, vEnv.Database)
 			continue
 		case "query-type":
 			fmt.Println(syntaxErr)
@@ -384,10 +386,11 @@ func main() {
 
 		// +++++++++++++++++++++打印单条 SQL 优化建议[开始]++++++++++++++++++++++++++{
 		common.Log.Debug("start of print suggestions, Query: %s", q.Query)
-		sug, str := advisor.FormatSuggest(q.Query, common.Config.ReportType, heuristicSuggest, idxSuggest, expSuggest, proSuggest, traceSuggest, mysqlSuggest)
+		sug, str := advisor.FormatSuggest(q.Query, currentDB, common.Config.ReportType, heuristicSuggest, idxSuggest, expSuggest, proSuggest, traceSuggest, mysqlSuggest)
 		suggestMerged[id] = sug
 		switch common.Config.ReportType {
 		case "json":
+			suggestStr = append(suggestStr, str)
 		case "tables":
 		case "duplicate-key-checker":
 		case "rewrite":
@@ -428,13 +431,7 @@ func main() {
 
 	// 以 JSON 格式化输出
 	if common.Config.ReportType == "json" {
-		js, err := json.MarshalIndent(suggestMerged, "", "  ")
-		if err == nil {
-			fmt.Println(string(js))
-		} else {
-			common.Log.Error("FormatSuggest json.Marshal Error: %v", err)
-		}
-		return
+		fmt.Println("[\n", strings.Join(suggestStr, ",\n"), "\n]")
 	}
 
 	// 以 JSON 格式输出 SQL 影响的库表名

diff --git a/cmd/soar/testdata/Test_Main_verboseInfo.golden b/cmd/soar/testdata/Test_Main_verboseInfo.golden
@@ -1,5 +1,7 @@
 Syntax check OK!
 MySQL environment verbose info
 * test-dsn: 127.0.0.1:3306 is disable, please check log.
+* online-dsn: 127.0.0.1:3306 is disable, please check log.
 MySQL environment verbose info
+* test-dsn: 127.0.0.1:3306 is disable, please check log.
 * online-dsn: 127.0.0.1:3306 is disable, please check log.
diff --git a/common/config.go b/common/config.go
@@ -882,7 +882,7 @@ var ReportTypes = []ReportType{
 	{
 		Name:        "tables",
 		Description: "以 JSON 格式输出 SQL 使用的库表名",
-		Example:     `echo "select * from film" | soar -report-type meta`,
+		Example:     `echo "select * from film" | soar -report-type tables`,
 	},
 	{
 		Name:        "query-type",

diff --git a/common/testdata/TestListReportTypes.golden b/common/testdata/TestListReportTypes.golden
@@ -58,13 +58,21 @@ echo "select * from film" | soar -report-type tiast
 ```bash
 echo "select * from film" | soar -report-type tiast-json
 ```
-## meta
+## tables
 * **Description**:以 JSON 格式输出 SQL 使用的库表名
 
 * **Example**:
 
 ```bash
-echo "select * from film" | soar -report-type meta
+echo "select * from film" | soar -report-type tables
+```
+## query-type
+* **Description**:SQL 语句的请求类型
+
+* **Example**:
+
+```bash
+echo "select * from film" | soar -report-type query-type
 ```
 ## fingerprint
 * **Description**:输出SQL的指纹