forked from XiaoMi/soar
/
explain.go
1158 lines (1054 loc) · 45.1 KB
/
explain.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
/*
* Copyright 2018 Xiaomi, Inc.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package database
import (
"encoding/json"
"errors"
"fmt"
"regexp"
"runtime"
"sort"
"strconv"
"strings"
"github.com/XiaoMi/soar/ast"
"github.com/XiaoMi/soar/common"
tidb "github.com/pingcap/parser/ast"
"github.com/tidwall/gjson"
"vitess.io/vitess/go/vt/sqlparser"
)
// format_type 支持的输出格式
// https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
const (
TraditionalFormatExplain = iota // 默认输出
JSONFormatExplain // JSON格式输出
)
// ExplainFormatType EXPLAIN 支持的 FORMAT_TYPE
var ExplainFormatType = map[string]int{
"traditional": 0,
"json": 1,
}
// explain_type
const (
TraditionalExplainType = iota // 默认转出
ExtendedExplainType // EXTENDED输出
PartitionsExplainType // PARTITIONS输出
)
// ExplainType EXPLAIN命令支持的参数
var ExplainType = map[string]int{
"traditional": 0,
"extended": 1,
"partitions": 2,
}
// 为TraditionalFormatExplain准备的结构体 { start
// ExplainInfo 用于存放Explain信息
type ExplainInfo struct {
SQL string
ExplainFormat int
ExplainRows []ExplainRow
ExplainJSON *ExplainJSON
Warnings []ExplainWarning
QueryCost float64
}
// ExplainRow 单行Explain
type ExplainRow struct {
ID int
SelectType string
TableName string
Partitions string // explain partitions
AccessType string
PossibleKeys []string
Key string
KeyLen string // 索引长度，如果发生了index_merge， KeyLen 格式为 N,N，所以不能定义为整型
Ref []string
Rows int64
Filtered float64 // 5.6 JSON, 5.7+, 5.5 EXTENDED
Scalability string // O(1), O(n), O(log n), O(log n)+
Extra string
}
// ExplainWarning explain extended 后 SHOW WARNINGS 输出的结果
type ExplainWarning struct {
Level string
Code int
Message string
}
// 为TraditionalFormatExplain准备的结构体 end }
// 为JSONFormatExplain准备的结构体 { start
// ExplainJSONCostInfo JSON
type ExplainJSONCostInfo struct {
ReadCost string `json:"read_cost"`
EvalCost string `json:"eval_cost"`
PrefixCost string `json:"prefix_cost"`
DataReadPerJoin string `json:"data_read_per_join"`
QueryCost string `json:"query_cost"`
SortCost string `json:"sort_cost"`
}
// ExplainJSONMaterializedFromSubquery JSON
type ExplainJSONMaterializedFromSubquery struct {
UsingTemporaryTable bool `json:"using_temporary_table"`
Dependent bool `json:"dependent"`
Cacheable bool `json:"cacheable"`
QueryBlock *ExplainJSONQueryBlock `json:"query_block"`
}
// 该变量用于存放 JSON 到 Traditional 模式的所有 ExplainJSONTable
var explainJSONTables []*ExplainJSONTable
// ExplainJSONTable JSON
type ExplainJSONTable struct {
TableName string `json:"table_name"`
AccessType string `json:"access_type"`
PossibleKeys []string `json:"possible_keys"`
Key string `json:"key"`
UsedKeyParts []string `json:"used_key_parts"`
KeyLength string `json:"key_length"`
Ref []string `json:"ref"`
RowsExaminedPerScan int64 `json:"rows_examined_per_scan"`
RowsProducedPerJoin int `json:"rows_produced_per_join"`
Filtered string `json:"filtered"`
UsingIndex bool `json:"using_index"`
UsingIndexForGroupBy bool `json:"using_index_for_group_by"`
CostInfo ExplainJSONCostInfo `json:"cost_info"`
UsedColumns []string `json:"used_columns"`
AttachedCondition string `json:"attached_condition"`
AttachedSubqueries []ExplainJSONSubqueries `json:"attached_subqueries"`
MaterializedFromSubquery ExplainJSONMaterializedFromSubquery `json:"materialized_from_subquery"`
}
// ExplainJSONNestedLoop JSON
type ExplainJSONNestedLoop struct {
Table ExplainJSONTable `json:"table"`
}
// ExplainJSONBufferResult JSON
type ExplainJSONBufferResult struct {
UsingTemporaryTable bool `json:"using_temporary_table"`
NestedLoop []ExplainJSONNestedLoop `json:"nested_loop"`
Table ExplainJSONTable `json:"table"`
}
// ExplainJSONSubqueries JSON
type ExplainJSONSubqueries struct {
Dependent bool `json:"dependent"`
Cacheable bool `json:"cacheable"`
QueryBlock ExplainJSONQueryBlock `json:"query_block"`
}
// ExplainJSONGroupingOperation JSON
type ExplainJSONGroupingOperation struct {
UsingTemporaryTable bool `json:"using_temporary_table"`
UsingFilesort bool `json:"using_filesort"`
Table ExplainJSONTable `json:"table"`
CostInfo ExplainJSONCostInfo `json:"cost_info"`
NestedLoop []ExplainJSONNestedLoop `json:"nested_loop"`
GroupBySubqueries []ExplainJSONSubqueries `json:"group_by_subqueries"`
}
// ExplainJSONDuplicatesRemoval JSON
type ExplainJSONDuplicatesRemoval struct {
UsingTemporaryTable bool `json:"using_temporary_table"`
UsingFilesort bool `json:"using_filesort"`
BufferResult ExplainJSONBufferResult `json:"buffer_result"`
GroupingOperation ExplainJSONGroupingOperation `json:"grouping_operation"`
Table ExplainJSONTable `json:"table"`
}
// ExplainJSONOrderingOperation JSON
type ExplainJSONOrderingOperation struct {
UsingFilesort bool `json:"using_filesort"`
Table ExplainJSONTable `json:"table"`
DuplicatesRemoval ExplainJSONDuplicatesRemoval `json:"duplicates_removal"`
GroupingOperation ExplainJSONGroupingOperation `json:"grouping_operation"`
OrderbySubqueries []ExplainJSONSubqueries `json:"order_by_subqueries"`
OptimizedAwaySubqueries []ExplainJSONSubqueries `json:"optimized_away_subqueries"`
}
// ExplainJSONQueryBlock JSON
type ExplainJSONQueryBlock struct {
SelectID int `json:"select_id"`
CostInfo ExplainJSONCostInfo `json:"cost_info"`
Table ExplainJSONTable `json:"table"`
NestedLoop []ExplainJSONNestedLoop `json:"nested_loop"`
OrderingOperation ExplainJSONOrderingOperation `json:"ordering_operation"`
GroupingOperation ExplainJSONGroupingOperation `json:"grouping_operation"`
OptimizedAwaySubqueries []ExplainJSONSubqueries `json:"optimized_away_subqueries"`
HavingSubqueries []ExplainJSONSubqueries `json:"having_subqueries"`
SelectListSubqueries []ExplainJSONSubqueries `json:"select_list_subqueries"`
UpdateValueSubqueries []ExplainJSONSubqueries `json:"update_value_subqueries"`
QuerySpecifications []ExplainJSONSubqueries `json:"query_specifications"`
UnionResult ExplainJSONUnionResult `json:"union_result"`
Message string `json:"message"`
}
// ExplainJSONUnionResult JSON
type ExplainJSONUnionResult struct {
UsingTemporaryTable bool `json:"using_temporary_table"`
TableName string `json:"table_name"`
AccessType string `json:"access_type"`
QuerySpecifications []ExplainJSONSubqueries `json:"query_specifications"`
}
// ExplainJSON 根结点
type ExplainJSON struct {
QueryBlock ExplainJSONQueryBlock `json:"query_block"`
}
// 为JSONFormatExplain准备的结构体 end }
// ExplainKeyWords 需要解释的关键字
var ExplainKeyWords = []string{
"access_type",
"attached_condition",
"attached_subqueries",
"buffer_result",
"cacheable",
"cost_info",
"data_read_per_join",
"dependent",
"duplicates_removal",
"eval_cost",
"filtered",
"group_by_subqueries",
"grouping_operation",
"having_subqueries",
"key",
"key_length",
"materialized_from_subquery",
"message",
"nested_loop",
"optimized_away_subqueries",
"order_by_subqueries",
"ordering_operation",
"possible_keys",
"prefix_cost",
"query_block",
"query_cost",
"query_specifications",
"read_cost",
"ref",
"rows_examined_per_scan",
"rows_produced_per_join",
"select_id",
"select_list_subqueries",
"sort_cost",
"table",
"table_name",
"union_result",
"update_value_subqueries",
"used_columns",
"used_key_parts",
"using_filesort",
"using_index",
"using_index_for_group_by",
"using_temporary_table",
}
/*
// ExplainColumnIndent EXPLAIN表头
var ExplainColumnIndent = map[string]string{
"id": "id为SELECT的标识符. 它是在SELECT查询中的顺序编号. 如果这一行表示其他行的union结果, 这个值可以为空. 在这种情况下, table列会显示为形如<union M,N>, 表示它是id为M和N的查询行的联合结果.",
"select_type": "表示查询的类型. ",
"table": "输出行所引用的表.",
"type": "type显示连接使用的类型, 有关不同类型的描述, 请参见解释连接类型.",
"possible_keys": "指出MySQL能在该表中使用哪些索引有助于查询. 如果为空, 说明没有可用的索引.",
"key": "MySQL实际从possible_keys选择使用的索引. 如果为NULL, 则没有使用索引. 很少情况下, MySQL会选择优化不足的索引. 这种情况下, 可以在select语句中使用USE INDEX (indexname)来强制使用一个索引或者用IGNORE INDEX (indexname)来强制MySQL忽略索引.",
"key_len": "显示MySQL使用索引键的长度. 如果key是NULL, 则key_len为NULL. 使用的索引的长度. 在不损失精确性的情况下, 长度越短越好.",
"ref": "显示索引的哪一列被使用了.",
"rows": "表示MySQL认为必须检查的用来返回请求数据的行数.",
"filtered": "表示返回结果的行占需要读到的行(rows列的值)的百分比.",
"Extra": "该列显示MySQL在查询过程中的一些详细信息, MySQL查询优化器执行查询的过程中对查询计划的重要补充信息.",
}
*/
// ExplainSelectType EXPLAIN中SELECT TYPE会出现的类型
var ExplainSelectType = map[string]string{
"SIMPLE": "简单SELECT(不使用UNION或子查询等).",
"PRIMARY": "最外层的select.",
"UNION": "UNION中的第二个或后面的SELECT查询, 不依赖于外部查询的结果集.",
"DEPENDENT": "UNION中的第二个或后面的SELECT查询, 依赖于外部查询的结果集.",
"UNION RESULT": "UNION查询的结果集.",
"SUBQUERY": "子查询中的第一个SELECT查询, 不依赖于外部查询的结果集.",
"DEPENDENT SUBQUERY": "子查询中的第一个SELECT查询, 依赖于外部查询的结果集.",
"DERIVED": "用于from子句里有子查询的情况. MySQL会递归执行这些子查询, 把结果放在临时表里.",
"MATERIALIZED": "Materialized subquery.",
"UNCACHEABLE SUBQUERY": "结果集不能被缓存的子查询, 必须重新为外层查询的每一行进行评估.",
"UNCACHEABLE UNION": "UNION中的第二个或后面的select查询, 属于不可缓存的子查询（类似于UNCACHEABLE SUBQUERY）.",
}
// ExplainAccessType EXPLAIN中ACCESS TYPE会出现的类型
var ExplainAccessType = map[string]string{
"system": "这是const连接类型的一种特例, 该表仅有一行数据(=系统表).",
"const": `const用于使用常数值比较PRIMARY KEY时, 当查询的表仅有一行时, 使用system. 例:SELECT * FROM tbl WHERE col = 1.`,
"eq_ref": `除const类型外最好的可能实现的连接类型. 它用在一个索引的所有部分被连接使用并且索引是UNIQUE或PRIMARY KEY, 对于每个索引键, 表中只有一条记录与之匹配. 例: 'SELECT * FROM RefTbl, tbl WHERE RefTbl.col=tbl.col;'.`,
"ref": `连接不能基于关键字选择单个行, 可能查找到多个符合条件的行. 叫做ref是因为索引要跟某个参考值相比较. 这个参考值或者是一个数, 或者是来自一个表里的多表查询的结果值. 例:'SELECT * FROM tbl WHERE idx_col=expr;'.`,
"fulltext": "查询时使用 FULLTEXT 索引.",
"ref_or_null": "如同ref, 但是MySQL必须在初次查找的结果里找出null条目, 然后进行二次查找.",
"index_merge": `表示使用了索引合并优化方法. 在这种情况下. key列包含了使用的索引的清单, key_len包含了使用的索引的最长的关键元素. 详情请见 8.2.1.4, “Index Merge Optimization”.`,
"unique_subquery": `在某些IN查询中使用此种类型，而不是常规的ref:'value IN (SELECT PrimaryKey FROM SingleTable WHERE SomeExpr)'.`,
"index_subquery": "在某些IN查询中使用此种类型, 与 unique_subquery 类似, 但是查询的是非唯一索引性索引.",
"range": `只检索给定范围的行, 使用一个索引来选择行. key列显示使用了哪个索引. key_len包含所使用索引的最长关键元素.`,
"index": "全表扫描, 只是扫描表的时候按照索引次序进行而不是行. 主要优点就是避免了排序, 但是开销仍然非常大.",
"ALL": `最坏的情况, 从头到尾全表扫描.`,
}
// ExplainScalability ACCESS TYPE对应的运算复杂度 [AccessType]scalability map
var ExplainScalability = map[string]string{
"NULL": "NULL",
"ALL": "O(n)",
"index": "O(n)",
"range": "O(log n)+",
"index_subquery": "O(log n)+",
"unique_subquery": "O(log n)+",
"index_merge": "O(log n)+",
"ref_or_null": "O(log n)+",
"fulltext": "O(log n)+",
"ref": "O(log n)",
"eq_ref": "O(log n)",
"const": "O(1)",
"system": "O(1)",
}
// ExplainExtra Extra信息解读
// https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
// sql/opt_explain_traditional.cc:traditional_extra_tags
var ExplainExtra = map[string]string{
"Using temporary": "表示MySQL在对查询结果排序时使用临时表. 常见于排序order by和分组查询group by.",
"Using filesort": "MySQL会对结果使用一个外部索引排序,而不是从表里按照索引次序读到相关内容. 可能在内存或者磁盘上进行排序. MySQL中无法利用索引完成的排序操作称为'文件排序'.",
"Using index condition": "在5.6版本后加入的新特性（Index Condition Pushdown）。Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。",
"Range checked for each record": "MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。",
"Using where with pushed condition": "这是一个仅仅在NDBCluster存储引擎中才会出现的信息，打开condition pushdown优化功能才可能被使用。",
"Using MRR": "使用了 MRR Optimization IO 层面进行了优化，减少 IO 方面的开销。",
"Skip_open_table": "Tables are read using the Multi-Range Read optimization strategy.",
"Open_frm_only": "Table files do not need to be opened. The information is already available from the data dictionary.",
"Open_full_table": "Unoptimized information lookup. Table information must be read from the data dictionary and by reading table files.",
"Scanned": "This indicates how many directory scans the server performs when processing a query for INFORMATION_SCHEMA tables.",
"Using index for group-by": "Similar to the Using index table access method, Using index for group-by indicates that MySQL found an index that can be used to retrieve all columns of a GROUP BY or DISTINCT query without any extra disk access to the actual table. Additionally, the index is used in the most efficient way so that for each group, only a few index entries are read.",
"Start temporary": "This indicates temporary table use for the semi-join Duplicate Weedout strategy.Start",
"End temporary": "This indicates temporary table use for the semi-join Duplicate Weedout strategy.End",
"FirstMatch": "The semi-join FirstMatch join shortcutting strategy is used for tbl_name.",
"Materialize": "Materialized subquery",
"Start materialize": "Materialized subquery Start",
"End materialize": "Materialized subquery End",
"unique row not found": "For a query such as SELECT ... FROM tbl_name, no rows satisfy the condition for a UNIQUE index or PRIMARY KEY on the table.",
// "Scan": "",
// "Impossible ON condition": "",
// "Ft_hints:": "",
// "Backward index scan": "",
// "Recursive": "",
// "Table function:": "",
"Index dive skipped due to FORCE": "This item applies to NDB tables only. It means that MySQL Cluster is using the Condition Pushdown optimization to improve the efficiency of a direct comparison between a nonindexed column and a constant. In such cases, the condition is “pushed down” to the cluster's data nodes and is evaluated on all data nodes simultaneously. This eliminates the need to send nonmatching rows over the network, and can speed up such queries by a factor of 5 to 10 times over cases where Condition Pushdown could be but is not used.",
"Impossible WHERE noticed after reading const tables": "查询了所有const(和system)表, 但发现WHERE查询条件不起作用.",
"Using where": "WHERE条件用于筛选出与下一个表匹配的数据然后返回给客户端. 除非故意做的全表扫描, 否则连接类型是ALL或者是index, 且在Extra列的值中没有Using Where, 则该查询可能是有问题的.",
"Using join buffer": "从已有连接中找被读入缓存的数据, 并且通过缓存来完成与当前表的连接.",
"Using index": "只需通过索引就可以从表中获取列的信息, 无需额外去读取真实的行数据. 如果查询使用的列值仅仅是一个简单索引的部分值, 则会使用这种策略来优化查询.",
"const row not found": "空表做类似 SELECT ... FROM tbl_name 的查询操作.",
"Distinct": "MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.",
"Full scan on NULL key": "子查询中的一种优化方式, 常见于无法通过索引访问null值.",
"Impossible HAVING": "HAVING条件过滤没有效果, 返回已有查询的结果集.",
"Impossible WHERE": "WHERE条件过滤没有效果, 最终是全表扫描.",
"LooseScan": "使用半连接LooseScan策略.",
"No matching min/max row": "没有行满足查询的条件, 如 SELECT MIN(...) FROM ... WHERE condition.",
"no matching row in const table": "对于连接查询, 列未满足唯一索引的条件或表为空.",
"No matching rows after partition pruning": "对于DELETE 或 UPDATE, 优化器在分区之后, 未发现任何要删除或更新的内容. 类似查询 Impossible WHERE.",
"No tables used": "查询没有FROM子句, 或者有一个 FROM DUAL子句.",
"Not exists": "MySQL能够对LEFT JOIN查询进行优化, 并且在查找到符合LEFT JOIN条件的行后, 则不再查找更多的行.",
"Plan isn't ready yet": "This value occurs with EXPLAIN FOR CONNECTION when the optimizer has not finished creating the execution plan for the statement executing in the named connection. If execution plan output comprises multiple lines, any or all of them could have this Extra value, depending on the progress of the optimizer in determining the full execution plan.",
"Select tables optimized away": "仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。如：在没有 GROUP BY 子句的情况下，基于索引优化 MIN/MAX 操作，或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。",
"Using intersect": "开启了index merge，即：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并，使用的算法为：index_merge_intersection",
"Using union": "开启了index merge，即：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并，使用的算法为：index_merge_union",
"Using sort_union": "开启了index merge，即：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并，使用的算法为：index_merge_sort_union",
}
// 提取ExplainJSON中所有的ExplainJSONTable, 将其写入全局变量explainJSONTables
// depth只是用于debug，逻辑上并未使用
func findTablesInJSON(explainJSON string, depth int) {
common.Log.Debug("findTablesInJSON Enter: depth(%d), json(%s)", depth, explainJSON)
// 去除注释，语法检查
explainJSON = RemoveSQLComments(explainJSON)
if !gjson.Valid(explainJSON) {
return
}
// 提取所有ExplainJSONTable struct
for _, key := range ExplainKeyWords {
result := gjson.Get(explainJSON, key)
if result.String() == "" {
continue
}
if key == "table" {
table := new(ExplainJSONTable)
common.Log.Debug("findTablesInJSON FindTable: depth(%d), table(%s)", depth, result.String)
err := json.Unmarshal([]byte(result.Raw), table)
common.LogIfError(err, "")
if table.TableName != "" {
explainJSONTables = append(explainJSONTables, table)
}
findTablesInJSON(result.String(), depth+1)
} else {
common.Log.Debug("findTablesInJSON ScanOther: depth(%d), key(%s), array_len(%d), json(%s)", depth, key, len(result.Array()), result.String)
for _, val := range result.Array() {
if val.String() != "" {
findTablesInJSON(val.String(), depth+1)
}
}
findTablesInJSON(result.String(), depth+1)
}
}
}
// FormatJSONIntoTraditional 将JSON形式转换为TRADITIONAL形式，方便前端展现
func FormatJSONIntoTraditional(explainJSON string) []ExplainRow {
// 查找JSON中的所有ExplainJSONTable
explainJSONTables = []*ExplainJSONTable{}
findTablesInJSON(explainJSON, 0)
var explainRows []ExplainRow
id := -1
for _, table := range explainJSONTables {
keyLen := table.KeyLength
filtered, err := strconv.ParseFloat(table.Filtered, 64)
if err != nil {
filtered = 0.00
}
if filtered > 100.00 {
filtered = 100.00
}
explainRows = append(explainRows, ExplainRow{
ID: id + 1,
SelectType: "",
TableName: table.TableName,
Partitions: "NULL",
AccessType: table.AccessType,
PossibleKeys: table.PossibleKeys,
Key: table.Key,
KeyLen: keyLen,
Ref: table.Ref,
Rows: table.RowsExaminedPerScan,
Filtered: filtered,
Scalability: ExplainScalability[table.AccessType],
Extra: "",
})
}
return explainRows
}
// ConvertExplainJSON2Row 将 JSON 格式转成 ROW 格式，为方便统一做优化建议
// 但是会损失一些 JSON 特有的分析结果
func ConvertExplainJSON2Row(explainJSON *ExplainJSON) []ExplainRow {
buf, err := json.Marshal(explainJSON)
if err != nil {
return nil
}
return FormatJSONIntoTraditional(string(buf))
}
// 用于检测 MySQL 版本是否低于 MySQL5.6
// 低于5.6 返回 true， 表示需要改写非 SELECT 的 SQL --> SELECT
func (db *Connector) supportExplainWrite() (bool, error) {
defer func() {
err := recover()
if err != nil {
common.Log.Error("Recover supportExplainWrite() Error:", err)
}
}()
// 5.6以上版本支持 EXPLAIN UPDATE/DELETE 等语句，但需要开启写入
// 如开启了 read_only, EXPLAIN UPDATE/DELETE 也会受限制
if common.Config.TestDSN.Version >= 50600 {
readOnly, err := db.SingleIntValue("read_only")
if err != nil {
return false, err
}
superReadOnly, err := db.SingleIntValue("super_read_only")
// Percona, MariaDB 5.6就已经有super_read_only了，但社区版5.6还没有这个参数
if err != nil {
if !strings.Contains(err.Error(), "Unknown system variable") {
return false, err
}
superReadOnly = readOnly
}
if readOnly == 1 || superReadOnly == 1 {
return true, nil
}
return false, nil
}
return true, nil
}
// 将SQL语句转换为可以被Explain的语句，如：写转读
// 当输出为空时，表示语法错误或不支持EXPLAIN
func (db *Connector) explainAbleSQL(sql string) (string, error) {
stmt, err := sqlparser.Parse(sql)
if err != nil {
// TODO: charset, collation
tiStmt, tiErr := ast.TiParse(sql, "", "")
if tiErr != nil {
common.Log.Error("explainAbleSQL ast.TiParse Error: %v", tiErr)
return "", tiErr
}
var isSelect bool
for _, st := range tiStmt {
switch st.(type) {
// SetOprStmt represents "union/except/intersect statement"
case *tidb.SelectStmt, *tidb.SetOprStmt:
isSelect = true
default:
isSelect = false
}
if !isSelect {
break
}
}
if isSelect {
return sql, nil
}
common.Log.Error("explainAbleSQL sqlparser.Parse Error: %v", err)
return "", err
}
switch stmt.(type) {
case *sqlparser.Insert, *sqlparser.Update, *sqlparser.Delete: // REPLACE 和 INSERT 的 AST 基本相同，只是 Action 不同
// 判断 Explain 的 SQL 是否需要被改写
need, err := db.supportExplainWrite()
if err != nil {
common.Log.Error("explainAbleSQL db.supportExplainWrite Error: %v", err)
return "", err
}
if need {
rw := ast.NewRewrite(sql)
if rw != nil {
return rw.RewriteDML2Select().NewSQL, nil
}
}
return sql, nil
case *sqlparser.Union, *sqlparser.ParenSelect, *sqlparser.Select, sqlparser.SelectStatement:
return sql, nil
default:
}
return "", nil
}
// explainQuery 生成可执行的 explain 查询请求
func (db *Connector) explainQuery(sql string, explainType int, formatType int) string {
var err error
sql, err = db.explainAbleSQL(sql)
if sql == "" || err != nil {
return sql
} else {
// MySQL 5.7 support MAX_EXECUTION_TIME hint
// ref: https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html
re := regexp.MustCompile(`(?i)(^select)(.*)`)
sql = re.ReplaceAllString(sql, "SELECT /*+ MAX_EXECUTION_TIME(1000) */${2}")
}
// 5.6以上支持 FORMAT=JSON
explainFormat := ""
switch formatType {
case JSONFormatExplain:
if common.Config.TestDSN.Version >= 50600 {
explainFormat = "FORMAT=JSON"
}
}
// 执行 explain
switch explainType {
case ExtendedExplainType:
// 5.6以上extended关键字已经不推荐使用，8.0废弃了这个关键字
if common.Config.TestDSN.Version >= 50600 {
sql = fmt.Sprintf("explain %s", sql)
} else {
sql = fmt.Sprintf("explain extended %s", sql)
}
case PartitionsExplainType:
sql = fmt.Sprintf("explain partitions %s", sql)
default:
sql = fmt.Sprintf("explain %s %s", explainFormat, sql)
}
return sql
}
// MySQLExplainWarnings WARNINGS信息中包含的优化器信息
func MySQLExplainWarnings(exp *ExplainInfo) string {
content := "## MySQL优化器调优结果\n\n```sql\n"
for _, row := range exp.Warnings {
content += "\n" + row.Message + "\n"
}
content += "\n```"
return content
}
// MySQLExplainQueryCost 将last_query_cost信息补充到评审结果中
func MySQLExplainQueryCost(exp *ExplainInfo) string {
var content string
if exp == nil {
return content
}
if exp.QueryCost > 0 {
tmp := fmt.Sprintf("%.3f\n", exp.QueryCost)
content = "Query cost: "
if exp.QueryCost > float64(common.Config.MaxQueryCost) {
content += fmt.Sprintf("☠️ **%s**", tmp)
} else {
content += tmp
}
}
return content
}
// ExplainInfoTranslator 将explain信息翻译成人能读懂的
func ExplainInfoTranslator(exp *ExplainInfo) string {
var buf []string
var selectTypeBuf []string
var accessTypeBuf []string
var extraTypeBuf []string
buf = append(buf, fmt.Sprint("### Explain信息解读\n"))
rows := exp.ExplainRows
if exp.ExplainFormat == JSONFormatExplain {
// JSON形式遍历分析不方便，转成Row格式统一处理
rows = ConvertExplainJSON2Row(exp.ExplainJSON)
}
if len(rows) == 0 {
return ""
}
// SelectType信息解读
explainSelectType := make(map[string]string)
for k, v := range ExplainSelectType {
explainSelectType[k] = v
}
for _, row := range rows {
if _, ok := explainSelectType[row.SelectType]; ok {
desc := fmt.Sprintf("* **%s**: %s\n", row.SelectType, explainSelectType[row.SelectType])
selectTypeBuf = append(selectTypeBuf, desc)
delete(explainSelectType, row.SelectType)
}
}
if len(selectTypeBuf) > 0 {
buf = append(buf, fmt.Sprint("#### SelectType信息解读\n"))
sort.Strings(selectTypeBuf)
buf = append(buf, strings.Join(selectTypeBuf, "\n"))
}
// #### Type信息解读
explainAccessType := make(map[string]string)
for k, v := range ExplainAccessType {
explainAccessType[k] = v
}
for _, row := range rows {
if _, ok := explainAccessType[row.AccessType]; ok {
var warn bool
var desc string
for _, t := range common.Config.ExplainWarnAccessType {
if row.AccessType == t {
warn = true
}
}
if warn {
desc = fmt.Sprintf("* ☠️ **%s**: %s\n", row.AccessType, explainAccessType[row.AccessType])
} else {
desc = fmt.Sprintf("* **%s**: %s\n", row.AccessType, explainAccessType[row.AccessType])
}
accessTypeBuf = append(accessTypeBuf, desc)
delete(explainAccessType, row.AccessType)
}
}
if len(accessTypeBuf) > 0 {
buf = append(buf, fmt.Sprint("#### Type信息解读\n"))
sort.Strings(accessTypeBuf)
buf = append(buf, strings.Join(accessTypeBuf, "\n"))
}
// #### Extra信息解读
if exp.ExplainFormat != JSONFormatExplain {
explainExtra := make(map[string]string)
for k, v := range ExplainExtra {
explainExtra[k] = v
}
for _, row := range rows {
for k, c := range explainExtra {
if strings.Contains(row.Extra, k) {
if k == "Impossible WHERE" && strings.Contains(row.Extra, "Impossible WHERE noticed after reading const tables") {
continue
}
if k == "Using index" && strings.Contains(row.Extra, "Using index condition") {
continue
}
warn := false
for _, w := range common.Config.ExplainWarnExtra {
if k == w {
warn = true
}
}
if warn {
extraTypeBuf = append(extraTypeBuf, fmt.Sprintf("* ☠️ **%s**: %s\n", k, c))
} else {
extraTypeBuf = append(extraTypeBuf, fmt.Sprintf("* **%s**: %s\n", k, c))
}
delete(explainExtra, k)
}
}
}
}
if len(extraTypeBuf) > 0 {
buf = append(buf, fmt.Sprint("#### Extra信息解读\n"))
sort.Strings(extraTypeBuf)
buf = append(buf, strings.Join(extraTypeBuf, "\n"))
}
return strings.Join(buf, "\n")
}
// ParseExplainText 解析explain文本信息（很可能是用户复制粘贴得到），返回格式化数据
func ParseExplainText(content string) (exp *ExplainInfo, err error) {
exp = &ExplainInfo{ExplainFormat: TraditionalFormatExplain}
content = strings.TrimSpace(content)
verticalFormat := strings.HasPrefix(content, "*")
jsonFormat := strings.HasPrefix(content, "{")
traditionalFormat := strings.HasPrefix(content, "+")
if verticalFormat && traditionalFormat && jsonFormat {
return nil, errors.New("not supported explain type")
}
if verticalFormat {
exp.ExplainRows, err = parseVerticalExplainText(content)
}
if jsonFormat {
exp.ExplainFormat = JSONFormatExplain
exp.ExplainJSON, err = parseJSONExplainText(content)
}
if traditionalFormat {
exp.ExplainRows, err = parseTraditionalExplainText(content)
}
return exp, err
}
// 解析文本形式传统形式Explain信息
func parseTraditionalExplainText(content string) (explainRows []ExplainRow, err error) {
LS := regexp.MustCompile(`^\+`) // 华丽的分隔线:)
// 格式正确性检查
lines := strings.Split(content, "\n")
if len(lines) < 3 {
return nil, errors.New("explain Rows less than 3")
}
// 提取头部，用于后续list到map的转换
var header []string
for _, h := range strings.Split(strings.Trim(lines[1], "|"), "|") {
header = append(header, strings.TrimSpace(h))
}
colIdx := make(map[string]int)
for i, item := range header {
colIdx[strings.ToLower(item)] = i
}
// explain format=json未把外面的框去了
if strings.ToLower(header[0]) == "explain" {
return nil, errors.New("json format explain need remove")
}
// 将每一列填充至ExplainRow结构体
colsMap := make(map[string]string)
for _, l := range lines[3:] {
var keylen string
var rows int64
var filtered float64
var partitions string
// 跳过分割线
if LS.MatchString(l) || strings.TrimSpace(l) == "" {
continue
}
// list到map的转换
var cols []string
for _, c := range strings.Split(strings.Trim(l, "|"), "|") {
cols = append(cols, strings.TrimSpace(c))
}
for item, i := range colIdx {
colsMap[item] = cols[i]
}
// 值类型转换
id, err := strconv.Atoi(colsMap["id"])
if err != nil {
return nil, err
}
// 不存在字段给默认值
if colsMap["partitions"] == "" {
partitions = "NULL"
} else {
partitions = colsMap["partitions"]
}
keylen = colsMap["key_len"]
rows, err = strconv.ParseInt(colsMap["Rows"], 10, 64)
if err != nil {
rows = 0
}
filtered, err = strconv.ParseFloat(colsMap["filtered"], 64)
if err != nil {
filtered = 0.00
}
// filtered may larger than 100.00
// https://bugs.mysql.com/bug.php?id=34124
if filtered >= 100.00 {
filtered = 100.00
}
// 拼接结构体
explainRows = append(explainRows, ExplainRow{
ID: id,
SelectType: colsMap["select_type"],
TableName: colsMap["table"],
Partitions: partitions,
AccessType: colsMap["type"],
PossibleKeys: strings.Split(colsMap["possible_keys"], ","),
Key: colsMap["key"],
KeyLen: keylen,
Ref: strings.Split(colsMap["ref"], ","),
Rows: rows,
Filtered: filtered,
Scalability: ExplainScalability[colsMap["type"]],
Extra: colsMap["extra"],
})
}
return explainRows, nil
}
// 解析文本形式竖排版 Explain信息
func parseVerticalExplainText(content string) (explainRows []ExplainRow, err error) {
var lines []string
explainRow := ExplainRow{
Partitions: "NULL",
Filtered: 0.00,
}
LS := regexp.MustCompile(`^\*.*\*$`) // 华丽的分隔线:)
// 格式正确性检查
for _, l := range strings.Split(content, "\n") {
lines = append(lines, strings.TrimSpace(l))
}
if len(lines) < 11 {
return nil, errors.New("explain rows less than 11")
}
// 将每一行填充至ExplainRow结构体
for _, l := range lines {
if LS.MatchString(l) || strings.TrimSpace(l) == "" {
continue
}
if strings.HasPrefix(l, "id:") {
id := strings.TrimPrefix(l, "id: ")
explainRow.ID, err = strconv.Atoi(id)
if err != nil {
return nil, err
}
}
if strings.HasPrefix(l, "select_type:") {
explainRow.SelectType = strings.TrimPrefix(l, "select_type: ")
}
if strings.HasPrefix(l, "table:") {
explainRow.TableName = strings.TrimPrefix(l, "table: ")
}
if strings.HasPrefix(l, "partitions:") {
explainRow.AccessType = strings.TrimPrefix(l, "partitions: ")
}
if strings.HasPrefix(l, "type:") {
explainRow.AccessType = strings.TrimPrefix(l, "type: ")
explainRow.Scalability = ExplainScalability[explainRow.AccessType]
}
if strings.HasPrefix(l, "possible_keys:") {
explainRow.PossibleKeys = strings.Split(strings.TrimPrefix(l, "possible_keys: "), ",")
}
if strings.HasPrefix(l, "key:") {
explainRow.Key = strings.TrimPrefix(l, "key: ")
}
if strings.HasPrefix(l, "key_len:") {
keyLen := strings.TrimPrefix(l, "key_len: ")
explainRow.KeyLen = keyLen
}
if strings.HasPrefix(l, "ref:") {
explainRow.Ref = strings.Split(strings.TrimPrefix(l, "ref: "), ",")
}
if strings.HasPrefix(l, "Rows:") {
rows := strings.TrimPrefix(l, "Rows: ")
explainRow.Rows, err = strconv.ParseInt(rows, 10, 64)
if err != nil {
explainRow.Rows = 0
}
}
if strings.HasPrefix(l, "filtered:") {
filtered := strings.TrimPrefix(l, "filtered: ")
explainRow.Filtered, err = strconv.ParseFloat(filtered, 64)
if err != nil {
return nil, err
} else if explainRow.Filtered > 100.00 {
explainRow.Filtered = 100.00
}
}
if strings.HasPrefix(l, "Extra:") {
explainRow.Extra = strings.TrimPrefix(l, "Extra: ")
explainRows = append(explainRows, explainRow)
}
}
return explainRows, err
}
// 解析文本形式JSON Explain信息
func parseJSONExplainText(content string) (*ExplainJSON, error) {
explainJSON := new(ExplainJSON)
err := json.Unmarshal([]byte(RemoveSQLComments(content)), explainJSON)
return explainJSON, err
}
// ParseExplainResult 分析 mysql 执行 explain 的结果，返回 ExplainInfo 结构化数据
func ParseExplainResult(res QueryResult, formatType int) (exp *ExplainInfo, err error) {
exp = &ExplainInfo{
ExplainFormat: formatType,
}
// JSON 格式直接调用文本方式解析
if formatType == JSONFormatExplain {
if res.Rows.Next() {
var explainString string
err = res.Rows.Scan(&explainString)
if err != nil {
common.Log.Debug(err.Error())
}
exp.ExplainJSON, err = parseJSONExplainText(explainString)
}
res.Rows.Close()
return exp, err
}
/*
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
*/
// Different MySQL version has different columns define
var selectType, table, partitions, accessType, possibleKeys, key, keyLen, ref, extra, rows, filtered []byte
expRow := ExplainRow{}
explainFields := make([]interface{}, 0)
fields := map[string]interface{}{
"id": &expRow.ID,
"select_type": &selectType,
"table": &table,
"partitions": &partitions,
"type": &accessType,
"possible_keys": &possibleKeys,
"key": &key,
"key_len": &keyLen,
"ref": &ref,
"rows": &rows,
"filtered": &filtered,
"Extra": &extra,