mysql版本

5.7

參考

本文轉(zhuǎn)自手把手教你認(rèn)識OPTIMIZER_TRACE，綜合博主自身理解，有所修改及重新排版，轉(zhuǎn)載請注明上述鏈接

mysql官方實例

1. 背景

眾所周知，當(dāng)遇到慢查詢sql的時候，需要使用explain關(guān)鍵字來查看該sql的執(zhí)行計劃，從中找出優(yōu)化點，但是，有時候，光靠explain是不足以得出全面的優(yōu)化結(jié)論的，比如說，執(zhí)行以下語句：

explain select * from product0 as t1 where t1.name = '產(chǎn)品名稱';

結(jié)果：

explain結(jié)果

不免會有這樣的疑問：

• 已經(jīng)為name字段創(chuàng)建了索引，可是為什么查詢語句沒有使用索引，而是走了全表掃描呢？
• 如果存在多個possible keys，那么mysql又是如何抉擇出合適的key的呢？

這一切，OPTIMIZER_TRACE都將會為我們作出解答。

2. 定義

OPTIMIZER_TRACE，見名知義，優(yōu)化器跟蹤，也就是跟蹤語句的解析優(yōu)化過程，并將跟蹤到的信息記錄到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中，該功能默認(rèn)是不開啟的，并且默認(rèn)只記錄最后一次結(jié)果到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。

執(zhí)行以下語句查看相關(guān)變量：

show variables like '%optimizer_trace%';

結(jié)果：

optimizer_trace相關(guān)變量

可以看到相關(guān)變量如下：

• optimizer_trace
  • enabled：啟用/禁用optimizer_trace功能
  • one_line：執(zhí)行結(jié)果中trace字段的展示方式，on代表單行展示，off代表以json格式展示
• optimizer_trace_features：控制執(zhí)行結(jié)果中trace字段需要打印的json項和不需要打印的json項，一般全部開啟
• optimizer_trace_limit和optimizer_trace_offset：這倆個參數(shù)類似limit offset，row_count，默認(rèn)值分別是1和-1，代表在INFORMATION_SCHEMA.OPTIMIZER_TRACE表中只存儲最近最后執(zhí)行的一行結(jié)果數(shù)據(jù)
• optimizer_trace_max_mem_size：optimizer_trace內(nèi)存大小，如果跟蹤信息超過這個大小，信息會被截斷

3. 適用語句

• SELECT/INSERT/REPLACE/UPDATE/DELETE
• EXPLAIN
• SET
• DO
• DECLARE/CASE/IF/RETURN
• CALL

4. Demo

4.1 數(shù)據(jù)準(zhǔn)備

CREATE TABLE t1 (
pk INT, col_int_key INT,
col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1)
);
INSERT INTO t1 VALUES
(10,7,'v','v'),(11,0,'s','s'),(12,9,'l','l'),(13,3,'y','y'),(14,4,'c','c'),
(15,2,'i','i'),(16,5,'h','h'),(17,3,'q','q'),(18,1,'a','a'),(19,3,'v','v'),
(20,6,'u','u'),(21,7,'s','s'),(22,5,'y','y'),(23,1,'z','z'),(24,204,'h','h'),
(25,224,'p','p'),(26,9,'e','e'),(27,5,'i','i'),(28,0,'y','y'),(29,3,'w','w');
CREATE TABLE t2 (
pk INT, col_int_key INT,
col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES
(1,4,'b','b'),(2,8,'y','y'),(3,0,'p','p'),(4,0,'f','f'),(5,0,'p','p'),
(6,7,'d','d'),(7,7,'f','f'),(8,5,'j','j'),(9,3,'e','e'),(10,188,'u','u'),
(11,4,'v','v'),(12,9,'u','u'),(13,6,'i','i'),(14,1,'x','x'),(15,5,'l','l'),
(16,6,'q','q'),(17,2,'n','n'),(18,4,'r','r'),(19,231,'c','c'),(20,4,'h','h'),
(21,3,'k','k'),(22,3,'t','t'),(23,7,'t','t'),(24,6,'k','k'),(25,7,'g','g'),
(26,9,'z','z'),(27,4,'n','n'),(28,4,'j','j'),(29,2,'l','l'),(30,1,'d','d'),
(31,2,'t','t'),(32,194,'y','y'),(33,2,'i','i'),(34,3,'j','j'),(35,8,'r','r'),
(36,4,'b','b'),(37,9,'o','o'),(38,4,'k','k'),(39,5,'a','a'),(40,5,'f','f'),
(41,9,'t','t'),(42,3,'c','c'),(43,8,'c','c'),(44,0,'r','r'),(45,98,'k','k'),
(46,3,'l','l'),(47,1,'o','o'),(48,0,'t','t'),(49,189,'v','v'),(50,8,'x','x'),
(51,3,'j','j'),(52,3,'x','x'),(53,9,'k','k'),(54,6,'o','o'),(55,8,'z','z'),
(56,3,'n','n'),(57,9,'c','c'),(58,5,'d','d'),(59,9,'s','s'),(60,2,'j','j'),
(61,2,'w','w'),(62,5,'f','f'),(63,8,'p','p'),(64,6,'o','o'),(65,9,'f','f'),
(66,0,'x','x'),(67,3,'q','q'),(68,6,'g','g'),(69,5,'x','x'),(70,8,'p','p'),
(71,2,'q','q'),(72,120,'q','q'),(73,25,'v','v'),(74,1,'g','g'),(75,3,'l','l'),
(76,1,'w','w'),(77,3,'h','h'),(78,153,'c','c'),(79,5,'o','o'),(80,9,'o','o'),
(81,1,'v','v'),(82,8,'y','y'),(83,7,'d','d'),(84,6,'p','p'),(85,2,'z','z'),
(86,4,'t','t'),(87,7,'b','b'),(88,3,'y','y'),(89,8,'k','k'),(90,4,'c','c'),
(91,6,'z','z'),(92,1,'t','t'),(93,7,'o','o'),(94,1,'u','u'),(95,0,'t','t'),
(96,2,'k','k'),(97,7,'u','u'),(98,2,'b','b'),(99,1,'m','m'),(100,5,'o','o');

4.2 執(zhí)行

set optimizer_trace = 'enabled=on';
SELECT SUM(alias2.col_varchar_nokey) , alias2.pk AS field2 FROM t1 AS alias1
STRAIGHT_JOIN t2 AS alias2 ON alias2.pk = alias1.col_int_key WHERE alias1.pk
GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk ;
select * from information_schema.OPTIMIZER_TRACE;
set optimizer_trace = 'enabled=off';

執(zhí)行結(jié)果：

執(zhí)行結(jié)果

5. 解析

各個字段解析如下：

• QUERY：跟蹤的SQL語句
• TRACE：跟蹤信息（JSON格式）
• MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟蹤信息過長時，被截斷的跟蹤信息的字節(jié)數(shù)
• INSUFFICIENT_PRIVILEGES：執(zhí)行跟蹤語句的用戶是否有查看對象的權(quán)限。當(dāng)不具有權(quán)限時，該列信息為1且TRACE字段為空。一般出現(xiàn)在調(diào)用帶有SQL SECURITY DEFINER的視圖或者是存儲過程的情況下

其中，TRACE詳細(xì)內(nèi)容如下：

{
    "steps" : [
        {
            "join_preparation" : {
                "select#" : 1,
                "steps" : [
                    {
                        "expanded_query" : "/* select#1 */ select sum(`t2`.`col_varchar_nokey`) AS `SUM(t2.col_varchar_nokey)`,`t2`.`pk` AS `field2` from (`t2` straight_join `t1` on((`t2`.`pk` = `t1`.`col_int_key`))) where (`t2`.`pk` between 10 and 100) group by `field2` order by `t1`.`col_int_key`,`t2`.`pk` limit 0,1000"
                    },
                    {
                        "transformations_to_nested_joins" : {
                            "expanded_query" : "/* select#1 */ select sum(`t2`.`col_varchar_nokey`) AS `SUM(t2.col_varchar_nokey)`,`t2`.`pk` AS `field2` from `t2` straight_join `t1` where ((`t2`.`pk` between 10 and 100) and (`t2`.`pk` = `t1`.`col_int_key`)) group by `field2` order by `t1`.`col_int_key`,`t2`.`pk` limit 0,1000",
                            "transformations" : [
                                "JOIN_condition_to_WHERE",
                                "parenthesis_removal"
                            ]
                        }
                    },
                    {
                        "functional_dependencies_of_GROUP_columns" : {
                            "all_columns_of_table_map_bits" : [
                                0
                            ],
                            "columns" : [
                                "training.t2.pk",
                                "training.t1.col_int_key"
                            ]
                        }
                    }
                ]
            }
        },
        {
            "join_optimization" : {
                "select#" : 1,
                "steps" : [
                    {
                        "condition_processing" : {
                            "condition" : "WHERE",
                            "original_condition" : "((`t2`.`pk` between 10 and 100) and (`t2`.`pk` = `t1`.`col_int_key`))",
                            "steps" : [
                                {
                                    "resulting_condition" : "((`t2`.`pk` between 10 and 100) and multiple equal(`t2`.`pk`, `t1`.`col_int_key`))",
                                    "transformation" : "equality_propagation"
                                },
                                {
                                    "resulting_condition" : "((`t2`.`pk` between 10 and 100) and multiple equal(`t2`.`pk`, `t1`.`col_int_key`))",
                                    "transformation" : "constant_propagation"
                                },
                                {
                                    "resulting_condition" : "((`t2`.`pk` between 10 and 100) and multiple equal(`t2`.`pk`, `t1`.`col_int_key`))",
                                    "transformation" : "trivial_condition_removal"
                                }
                            ]
                        }
                    },
                    {
                        "substitute_generated_columns" : {                      }
                    },
                    {
                        "table_dependencies" : [
                            {
                                "depends_on_map_bits" : [                               ],
                                "map_bit" : 0,
                                "row_may_be_null" : false,
                                "table" : "`t2`"
                            },
                            {
                                "depends_on_map_bits" : [
                                    0
                                ],
                                "map_bit" : 1,
                                "row_may_be_null" : false,
                                "table" : "`t1`"
                            }
                        ]
                    },
                    {
                        "ref_optimizer_key_uses" : [
                            {
                                "equals" : "`t1`.`col_int_key`",
                                "field" : "pk",
                                "null_rejecting" : true,
                                "table" : "`t2`"
                            }
                        ]
                    },
                    {
                        "rows_estimation" : [
                            {
                                "range_analysis" : {
                                    "analyzing_range_alternatives" : {
                                        "analyzing_roworder_intersect" : {
                                            "cause" : "too_few_roworder_scans",
                                            "usable" : false
                                        },
                                        "range_scan_alternatives" : [
                                            {
                                                "chosen" : true,
                                                "cost" : 19.291000,
                                                "index" : "PRIMARY",
                                                "index_dives_for_eq_ranges" : true,
                                                "index_only" : false,
                                                "ranges" : [
                                                    "10 <= pk <= 100"
                                                ],
                                                "rowid_ordered" : true,
                                                "rows" : 91,
                                                "using_mrr" : false
                                            }
                                        ]
                                    },
                                    "chosen_range_access_summary" : {
                                        "chosen" : true,
                                        "cost_for_plan" : 19.291000,
                                        "range_access_plan" : {
                                            "index" : "PRIMARY",
                                            "ranges" : [
                                                "10 <= pk <= 100"
                                            ],
                                            "rows" : 91,
                                            "type" : "range_scan"
                                        },
                                        "rows_for_plan" : 91
                                    },
                                    "group_index_range" : {
                                        "cause" : "not_single_table",
                                        "chosen" : false
                                    },
                                    "potential_range_indexes" : [
                                        {
                                            "index" : "PRIMARY",
                                            "key_parts" : [
                                                "pk"
                                            ],
                                            "usable" : true
                                        }
                                    ],
                                    "setup_range_conditions" : [                                    ],
                                    "table_scan" : {
                                        "cost" : 23.100000,
                                        "rows" : 100
                                    }
                                },
                                "table" : "`t2`"
                            },
                            {
                                "table" : "`t1`",
                                "table_scan" : {
                                    "cost" : 1,
                                    "rows" : 20
                                }
                            }
                        ]
                    },
                    {
                        "considered_execution_plans" : [
                            {
                                "best_access_path" : {
                                    "considered_access_paths" : [
                                        {
                                            "access_type" : "ref",
                                            "chosen" : false,
                                            "index" : "PRIMARY",
                                            "usable" : false
                                        },
                                        {
                                            "access_type" : "range",
                                            "chosen" : true,
                                            "cost" : 37.491000,
                                            "range_details" : {
                                                "used_index" : "PRIMARY"
                                            },
                                            "resulting_rows" : 91,
                                            "rows_to_scan" : 91
                                        }
                                    ]
                                },
                                "condition_filtering_pct" : 100,
                                "cost_for_plan" : 37.491000,
                                "plan_prefix" : [                               ],
                                "rest_of_plan" : [
                                    {
                                        "best_access_path" : {
                                            "considered_access_paths" : [
                                                {
                                                    "access_type" : "scan",
                                                    "buffers_needed" : 1,
                                                    "chosen" : true,
                                                    "cost" : 365,
                                                    "resulting_rows" : 20,
                                                    "rows_to_scan" : 20,
                                                    "using_join_cache" : true
                                                }
                                            ]
                                        },
                                        "chosen" : true,
                                        "condition_filtering_pct" : 10,
                                        "cost_for_plan" : 402.490000,
                                        "plan_prefix" : [
                                            "`t2`"
                                        ],
                                        "rows_for_plan" : 182,
                                        "table" : "`t1`"
                                    }
                                ],
                                "rows_for_plan" : 91,
                                "table" : "`t2`"
                            }
                        ]
                    },
                    {
                        "attaching_conditions_to_tables" : {
                            "attached_conditions_computation" : [                           ],
                            "attached_conditions_summary" : [
                                {
                                    "attached" : "(`t2`.`pk` between 10 and 100)",
                                    "table" : "`t2`"
                                },
                                {
                                    "attached" : "(`t1`.`col_int_key` = `t2`.`pk`)",
                                    "table" : "`t1`"
                                }
                            ],
                            "original_condition" : "((`t1`.`col_int_key` = `t2`.`pk`) and (`t2`.`pk` between 10 and 100))"
                        }
                    },
                    {
                        "clause_processing" : {
                            "clause" : "ORDER BY",
                            "items" : [
                                {
                                    "item" : "`t1`.`col_int_key`"
                                },
                                {
                                    "item" : "`t2`.`pk`"
                                }
                            ],
                            "original_clause" : "`t1`.`col_int_key`,`t2`.`pk`",
                            "resulting_clause" : "`t1`.`col_int_key`,`t2`.`pk`",
                            "resulting_clause_is_simple" : false
                        }
                    },
                    {
                        "clause_processing" : {
                            "clause" : "GROUP BY",
                            "items" : [
                                {
                                    "item" : "`t2`.`pk`"
                                }
                            ],
                            "original_clause" : "`field2`",
                            "resulting_clause" : "`field2`",
                            "resulting_clause_is_simple" : true
                        }
                    },
                    {
                        "refine_plan" : [
                            {
                                "table" : "`t2`"
                            },
                            {
                                "table" : "`t1`"
                            }
                        ]
                    }
                ]
            }
        },
        {
            "join_execution" : {
                "select#" : 1,
                "steps" : [
                    {
                        "creating_tmp_table" : {
                            "tmp_table_info" : {
                                "key_length" : 4,
                                "location" : "memory (heap)",
                                "row_length" : 18,
                                "row_limit_estimate" : 932067,
                                "table" : "intermediate_tmp_table",
                                "unique_constraint" : false
                            }
                        }
                    },
                    {
                        "filesort_execution" : [                        ],
                        "filesort_information" : [
                            {
                                "direction" : "asc",
                                "field" : "col_int_key",
                                "table" : "intermediate_tmp_table"
                            },
                            {
                                "direction" : "asc",
                                "field" : "pk",
                                "table" : "intermediate_tmp_table"
                            }
                        ],
                        "filesort_priority_queue_optimization" : {
                            "cause" : "quicksort_is_cheaper",
                            "chosen" : false,
                            "limit" : 1000,
                            "memory_available" : 262144,
                            "row_size" : 17,
                            "rows_estimate" : 10
                        },
                        "filesort_summary" : {
                            "examined_rows" : 0,
                            "number_of_tmp_files" : 0,
                            "rows" : 0,
                            "sort_buffer_size" : 376,
                            "sort_mode" : "<sort_key, rowid>"
                        }
                    }
                ]
            }
        }
    ]
}

對上述json串進行格式化，可以明顯看到以下三個子節(jié)點：

• join_preparation：準(zhǔn)備階段
• join_optimization：優(yōu)化階段
• join_execution：執(zhí)行階段

主結(jié)構(gòu)

5.1 join_preparation

準(zhǔn)備階段
展開join_preparation，可以看到主結(jié)構(gòu)主要有倆個：

• select#：代表跟蹤分析的是第幾條語句，如果使用union，那么這里就會有倆條
• steps：對應(yīng)語句的執(zhí)行步驟
  
  join_preparation主結(jié)構(gòu)

union下join_preparation主結(jié)構(gòu)

5.1.1 expanded_query

{
    "expanded_query":"/* select#1 */ select sum(`t2`.`col_varchar_nokey`) AS `SUM(t2.col_varchar_nokey)`,`t2`.`pk` AS `field2` from (`t2` straight_join `t1` on((`t2`.`pk` = `t1`.`col_int_key`))) where (`t2`.`pk` between 10 and 100) group by `field2` order by `t1`.`col_int_key`,`t2`.`pk` limit 0,1000"
}

可以看到，steps中的expanded_query內(nèi)容是原本的select *語句轉(zhuǎn)化為具體庫名、字段名、列名等后的結(jié)果

limit語句應(yīng)該是數(shù)據(jù)庫可視化軟件默認(rèn)加上的，這里不去追究

5.1.2 transformations_to_nested_joins

{
    "transformations_to_nested_joins":{
        "expanded_query":"/* select#1 */ select sum(`t2`.`col_varchar_nokey`) AS `SUM(t2.col_varchar_nokey)`,`t2`.`pk` AS `field2` from `t2` straight_join `t1` where ((`t2`.`pk` between 10 and 100) and (`t2`.`pk` = `t1`.`col_int_key`)) group by `field2` order by `t1`.`col_int_key`,`t2`.`pk` limit 0,1000",
        "transformations":[
            "JOIN_condition_to_WHERE",
            "parenthesis_removal"
        ]
    }
}

從名字和expanded_query內(nèi)容來看，是將on子句轉(zhuǎn)化為where子句

5.2 join_optimization

優(yōu)化階段
優(yōu)化內(nèi)容，相對內(nèi)容多且復(fù)雜，主要分為以下幾個部分：

• condition_processing：對where子句和having子句的處理
• substitute_generated_columns：替換虛擬生成列
• table_dependencies：梳理表之間的依賴關(guān)系
• ref_optimizer_key_uses： 如果優(yōu)化器認(rèn)為查詢可以使用ref的話，在這里列出可以使用的索引
• rows_estimation：估算表行數(shù)和掃描的代價。如果查詢中存在range掃描的話，對range掃描進行計劃分析及代價估算
• considered_execution_plans：對比各可行計劃的代價，選擇相對最優(yōu)的執(zhí)行計劃
• attaching_conditions_to_tables：添加附加條件，使得條件盡可能篩選單表數(shù)據(jù)
• clause_processing：對DISTINCT、GROUP BY、ORDER BY等語句進行優(yōu)化
• refine_plan：優(yōu)化后的執(zhí)行計劃

5.2.1 condition_processing

{
    "condition_processing":{
        "condition":"WHERE",
        "original_condition":"((`t2`.`pk` between 10 and 100) and (`t2`.`pk` = `t1`.`col_int_key`))",
        "steps":[
            {
                "resulting_condition":"((`t2`.`pk` between 10 and 100) and multiple equal(`t2`.`pk`, `t1`.`col_int_key`))",
                "transformation":"equality_propagation"
            },
            {
                "resulting_condition":"((`t2`.`pk` between 10 and 100) and multiple equal(`t2`.`pk`, `t1`.`col_int_key`))",
                "transformation":"constant_propagation"
            },
            {
                "resulting_condition":"((`t2`.`pk` between 10 and 100) and multiple equal(`t2`.`pk`, `t1`.`col_int_key`))",
                "transformation":"trivial_condition_removal"
            }
        ]
    }
}

對條件語句where和having的處理：

• condition：條件語句類型，可以是where或者having
• original_condition：原始條件查詢語句
• steps：處理步驟
  • resulting_condition：轉(zhuǎn)換之后的語句
  • transformation：轉(zhuǎn)換類型
    • equality_propagation：等值條件句轉(zhuǎn)換
    • constant_propagation：常量條件句轉(zhuǎn)換
    • trivial_condition_removal：無效條件移除的轉(zhuǎn)換

demo中的where字句只命中了equality_propagation一種情況（雖然也有constant_propagation和trivial_condition_removal的部分，但是可以看到resulting_condition并沒有發(fā)生變化，也就是相當(dāng)于只命中了equality_propagation一種情況），可以通過以下語句命中全部三種情況：

select * from t1 join t2 on t1.pk=t2.pk+1 where t2.pk = 5 and 1 =1 ;

{
    "condition_processing":{
        "condition":"WHERE",
        "original_condition":"((`t2`.`pk` = 5) and (1 = 1) and (`t1`.`pk` = (`t2`.`pk` + 1)))",
        "steps":[
            {
                "resulting_condition":"((1 = 1) and (`t1`.`pk` = (5 + 1)) and multiple equal(5, `t2`.`pk`))",
                "transformation":"equality_propagation"
            },
            {
                "resulting_condition":"((1 = 1) and (`t1`.`pk` = 6) and multiple equal(5, `t2`.`pk`))",
                "transformation":"constant_propagation"
            },
            {
                "resulting_condition":"((`t1`.`pk` = 6) and multiple equal(5, `t2`.`pk`))",
                "transformation":"trivial_condition_removal"
            }
        ]
    }
}

分析如下：

• equality_propagation：將原始語句中的t2.pk = 5 轉(zhuǎn)化為multiple equal(5, t2.pk)；之后t1.pk = (t2.pk + 1)結(jié)合前面的t2.pk = 5將t2.pk做了替換，轉(zhuǎn)化成了t1.pk = (5 + 1)，其余保持不變
• constant_propagation：將上一步中的結(jié)果做常量計算，t1.pk = (5 + 1)變成t1.pk = 6
• trivial_condition_removal：移除1 = 1這個恒為true的無效條件

5.2.2 table_dependencies

{
    "table_dependencies":[
        {
            "depends_on_map_bits":[

            ],
            "map_bit":0,
            "row_may_be_null":false,
            "table":"`t2`"
        },
        {
            "depends_on_map_bits":[
                0
            ],
            "map_bit":1,
            "row_may_be_null":false,
            "table":"`t1`"
        }
    ]
}

在這個結(jié)點中我們可以看到表之間的依賴關(guān)系：

• table：表名及其別名
• row_may_be_null：join之后的列是否可能為null，注意，不是表中列屬性是否設(shè)置為null，比如說左連接，那么后一張表中有些列可能為null，此時row_may_be_null顯示為true
• map_bit：表序號
• depends_on_map_bits：依賴的表的map_bit

5.2.3 ref_optimizer_key_uses

{
    "ref_optimizer_key_uses":[
        {
            "equals":"`t1`.`col_int_key`",
            "field":"pk",
            "null_rejecting":true,
            "table":"`t2`"
        }
    ]
}

該節(jié)點列出了所有可用的ref類型的索引，如果使用了組合索引的多個部分，則會列出多個結(jié)構(gòu)體。單個結(jié)構(gòu)體會列出單表ref使用的索引及其對應(yīng)值。

5.2.4 rows_estimation

{
    "rows_estimation":[
        {
            "range_analysis":{
                "analyzing_range_alternatives":{
                    "analyzing_roworder_intersect":{
                        "cause":"too_few_roworder_scans",
                        "usable":false
                    },
                    "range_scan_alternatives":[
                        {
                            "chosen":true,
                            "cost":19.291,
                            "index":"PRIMARY",
                            "index_dives_for_eq_ranges":true,
                            "index_only":false,
                            "ranges":[
                                "10 <= pk <= 100"
                            ],
                            "rowid_ordered":true,
                            "rows":91,
                            "using_mrr":false
                        }
                    ]
                },
                "chosen_range_access_summary":{
                    "chosen":true,
                    "cost_for_plan":19.291,
                    "range_access_plan":{
                        "index":"PRIMARY",
                        "ranges":[
                            "10 <= pk <= 100"
                        ],
                        "rows":91,
                        "type":"range_scan"
                    },
                    "rows_for_plan":91
                },
                "group_index_range":{
                    "cause":"not_single_table",
                    "chosen":false
                },
                "potential_range_indexes":[
                    {
                        "index":"PRIMARY",
                        "key_parts":[
                            "pk"
                        ],
                        "usable":true
                    }
                ],
                "setup_range_conditions":[

                ],
                "table_scan":{
                    "cost":23.1,
                    "rows":100
                }
            },
            "table":"`t2`"
        },
        {
            "table":"`t1`",
            "table_scan":{
                "cost":1,
                "rows":20
            }
        }
    ]
}

rows_estimation展示估算的表掃描行數(shù)及其代價，從結(jié)構(gòu)體中可以看到，是以表為單位展示對應(yīng)結(jié)果的

首先看一下t1，t1表由于沒有索引，所以走的是全表掃描，結(jié)構(gòu)體也比較簡單，只有倆個結(jié)點：

{
    "table":"`t1`",
    "table_scan":{
        "cost":1,
        "rows":20
    }
}

• table：表名及其別名
• table_scan
  • rows：掃描行數(shù)
  • cost：代價

接下來看一下t2，t2相對復(fù)雜很多，主要看一下range_analysis，分為以下幾個部分：

• analyzing_range_alternatives
• chosen_range_access_summary
• group_index_range
• potential_range_indexes
• setup_range_conditions
• table_scan

5.2.4.1 analyzing_range_alternatives

{
    "analyzing_roworder_intersect":{
        "cause":"too_few_roworder_scans",
        "usable":false
    },
    "range_scan_alternatives":[
        {
            "chosen":true,
            "cost":19.291,
            "index":"PRIMARY",
            "index_dives_for_eq_ranges":true,
            "index_only":false,
            "ranges":[
                "10 <= pk <= 100"
            ],
            "rowid_ordered":true,
            "rows":91,
            "using_mrr":false
        }
    ]
}

分析可選方案的代價，包括

• analyzing_roworder_intersect：index merge分析
• range_scan_alternatives：range掃描分析

倆個階段，分別針對不同的情況進行執(zhí)行代價的分析，從中選擇出更優(yōu)的執(zhí)行計劃。

5.2.4.1.1 analyzing_roworder_intersect

由于示例沒有使用index merge，所以在這一段僅僅給出了不使用index merge的原因。如果是語句可以使用index_merge的情況，在該階段會分析使用index_merge過程中消耗的代價（index_scan_cost、disk_sweep_cost等），并匯總merge的代價確認(rèn)是否選擇使用index_merge以及對應(yīng)使用的索引。

5.2.4.1.2 range_scan_alternatives

range掃描分析針對所有可用于range掃描的索引進行了代價分析，并根據(jù)分析結(jié)果確認(rèn)選擇使用的索引，包含如下字段：

• choosen：是否使用該索引
• cost：代價
• index：索引名稱
• index_dives_for_eq_ranges：是否使用了index dive。這個值會被參數(shù)eq_range_index_dive_limit設(shè)定值影響
• index_only：是否是覆蓋索引
• ranges：range掃描的條件句范圍
• rowid_ordered：掃描的結(jié)果集是否根據(jù)PK排序
• rows：掃描行數(shù)
• using_mrr：是否使用mrr

5.2.4.2 chosen_range_access_summary

{
    "chosen":true,
    "cost_for_plan":19.291,
    "range_access_plan":{
        "index":"PRIMARY",
        "ranges":[
            "10 <= pk <= 100"
        ],
        "rows":91,
        "type":"range_scan"
    },
    "rows_for_plan":91
}

在前一個步驟中分析了各類索引使用的方法及代價，得出了一定的中間結(jié)果之后，在summary階段匯總前一階段的中間結(jié)果確認(rèn)最后的方案

• range_access_plan：range掃描最終選擇的執(zhí)行計劃。在該結(jié)構(gòu)體中會給出執(zhí)行計劃的type，使用的索引以及掃描行數(shù)。如果range_access_plan.type是index_roworder_intersect（即index merge）的話，在該結(jié)構(gòu)體下還會列intersect_of結(jié)構(gòu)體給出index merge的具體信息。
• rows_for_plan：該執(zhí)行計劃掃描行數(shù)
• cost_for_plan：該執(zhí)行計劃代價
• chosen：是否選擇該執(zhí)行計劃

5.2.4.3 group_index_range

{
    "cause":"not_single_table",
    "chosen":false
}

評估在使用了GROUP BY或者是DISTINCT的時候是否有適合的索引可用:

• 當(dāng)語句中沒有GROUP BY或者是DISTINCT的時候，該結(jié)構(gòu)體下顯示"chosen":"false"以及"cause":"'not_group_by_or_distinct"
• 如果語句中在多表關(guān)聯(lián)時使用了GROUP BY或DISTINCT時，在該結(jié)構(gòu)體下顯示"chosen":"false"以及"cause":"not_single_table"
• 其他情況下會去嘗試分析可用的索引（potential_group_range_indexes）并且計算對應(yīng)的掃描行數(shù)及其所需代價

5.2.4.4 potential_range_indexes

[
    {
        "index":"PRIMARY",
        "key_parts":[
            "pk"
        ],
        "usable":true
    }
]

列出表中所有的索引，并分析其是否可用，結(jié)點釋義如下：

• index：索引名稱
• key_parts：建立該索引的字段
• usable：是否可用

5.2.4.5 table_scan

同前面t1的釋義，全表掃描的行數(shù)及代價

5.2.5 considered_execution_plans

[
    {
        "best_access_path":{
            "considered_access_paths":[
                {
                    "access_type":"ref",
                    "chosen":false,
                    "index":"PRIMARY",
                    "usable":false
                },
                {
                    "access_type":"range",
                    "chosen":true,
                    "cost":37.491,
                    "range_details":{
                        "used_index":"PRIMARY"
                    },
                    "resulting_rows":91,
                    "rows_to_scan":91
                }
            ]
        },
        "condition_filtering_pct":100,
        "cost_for_plan":37.491,
        "plan_prefix":[

        ],
        "rest_of_plan":[
            {
                "best_access_path":{
                    "considered_access_paths":[
                        {
                            "access_type":"scan",
                            "buffers_needed":1,
                            "chosen":true,
                            "cost":365,
                            "resulting_rows":20,
                            "rows_to_scan":20,
                            "using_join_cache":true
                        }
                    ]
                },
                "chosen":true,
                "condition_filtering_pct":10,
                "cost_for_plan":402.49,
                "plan_prefix":[
                    "`t2`"
                ],
                "rows_for_plan":182,
                "table":"`t1`"
            }
        ],
        "rows_for_plan":91,
        "table":"`t2`"
    }
]

負(fù)責(zé)對比各可行計劃的代價，選擇相對最優(yōu)的執(zhí)行計劃，由于我們使用了straight join，強制決定了連接順序，由t2表驅(qū)動t1表，所以considered_execution_plans下面只有一個元素，也就代表只有一個可行的執(zhí)行計劃，若是使用join，那么此時就會有倆個元素，由優(yōu)化器幫我們決定使用哪一個執(zhí)行計劃：

{
    "considered_execution_plans":[
        {
            "best_access_path":{
                "considered_access_paths":[
                    {
                        "access_type":"scan",
                        "chosen":true,
                        "cost":5,
                        "resulting_rows":20,
                        "rows_to_scan":20
                    }
                ]
            },
            "condition_filtering_pct":100,
            "cost_for_plan":5,
            "plan_prefix":[

            ],
            "rest_of_plan":[
                {
                    "best_access_path":{
                        "considered_access_paths":[
                            {
                                "access_type":"eq_ref",
                                "cause":"clustered_pk_chosen_by_heuristics",
                                "chosen":true,
                                "cost":24,
                                "index":"PRIMARY",
                                "rows":1
                            },
                            {
                                "access_type":"range",
                                "cause":"heuristic_index_cheaper",
                                "chosen":false,
                                "range_details":{
                                    "used_index":"PRIMARY"
                                }
                            }
                        ]
                    },
                    "chosen":true,
                    "condition_filtering_pct":100,
                    "cost_for_plan":29,
                    "plan_prefix":[
                        "`t1`"
                    ],
                    "rows_for_plan":20,
                    "table":"`t2`"
                }
            ],
            "rows_for_plan":20,
            "table":"`t1`"
        },
        {
            "best_access_path":{
                "considered_access_paths":[
                    {
                        "access_type":"ref",
                        "chosen":false,
                        "index":"PRIMARY",
                        "usable":false
                    },
                    {
                        "access_type":"range",
                        "chosen":true,
                        "cost":37.491,
                        "range_details":{
                            "used_index":"PRIMARY"
                        },
                        "resulting_rows":91,
                        "rows_to_scan":91
                    }
                ]
            },
            "condition_filtering_pct":100,
            "cost_for_plan":37.491,
            "plan_prefix":[

            ],
            "pruned_by_cost":true,
            "rows_for_plan":91,
            "table":"`t2`"
        }
    ]
}

字段釋義：

• best_access_path：當(dāng)前最優(yōu)的執(zhí)行順序信息結(jié)果集，可以看到主要內(nèi)容是considered_access_paths。根據(jù)索引的使用與否和具體的使用方法可能會產(chǎn)生多個considered_access_paths
  • access_type：參照explain的type，不同的type會在considered_access_paths下展示不同的字段，詳見5.2.5.1
• condition_filtering_pct：類似于explain中的filtered列，這是一個估算值
• rows_for_plan：該執(zhí)行計劃最終的掃描行數(shù)，這里的行數(shù)其實也是估算值，是由considered_access_paths的resulting_rows相乘之后再乘以condition_filtering_pct獲得
• cost_for_plan：該執(zhí)行計劃的執(zhí)行代價，由considered_access_paths的cost相加而得
• plan_prefix：前置表名稱，也就是驅(qū)動表的名稱
• table：分析的表對象名稱及其別名
• rest_of_plan：下一張表的計劃，理解為類似鏈表的next，可以看到，其下的結(jié)點與第一層結(jié)構(gòu)是類似的

5.2.5.1 access_type

• 使用索引的情況(t2 join t1)

[
    {
        "access_type":"ref",
        "chosen":false,
        "index":"PRIMARY",
        "usable":false
    },
    {
        "access_type":"range",
        "chosen":true,
        "cost":37.491,
        "range_details":{
            "used_index":"PRIMARY"
        },
        "resulting_rows":91,
        "rows_to_scan":91
    }
]

可以看到，這時候有多條數(shù)據(jù)，第一條數(shù)據(jù)使用ref類型的索引，但是該索引不可用（"useable":false）,所以不被選擇（"chosen":false）;第二條數(shù)據(jù)使用了range類型的索引，使用了名稱為PRIMARY的索引，并且展示了掃描的行數(shù)，掃描結(jié)果行數(shù)以及代價，最后的是在選擇比較的結(jié)果中是否選擇了該方式（chosen）

• 使用掃描的情況(t1 join t2)

{
    "access_type":"scan",
    "chosen":true,
    "cost":5,
    "resulting_rows":20,
    "rows_to_scan":20
}

該結(jié)構(gòu)體下列出了該表的掃描行數(shù)（rows_to_scan），由于這里沒有列出index所以能分析出這里的access_type中的scan在這里指的是全表掃描。

5.2.6 attaching_conditions_to_tables

{
    "attaching_conditions_to_tables":{
        "attached_conditions_computation":[

        ],
        "attached_conditions_summary":[
            {
                "attached":"(`t2`.`pk` between 10 and 100)",
                "table":"`t2`"
            },
            {
                "attached":"(`t1`.`col_int_key` = `t2`.`pk`)",
                "table":"`t1`"
            }
        ],
        "original_condition":"((`t1`.`col_int_key` = `t2`.`pk`) and (`t2`.`pk` between 10 and 100))"
    }
}

基于considered_execution_plans中已選執(zhí)行計劃改造原有的where條件句并針對表的增加適當(dāng)?shù)馗郊訔l件便于單表數(shù)據(jù)的篩選。這部分條件的增改主要是為了便于ICP，但是ICP是否開啟并不影響該部分的構(gòu)造

• original_condition：在準(zhǔn)備階段原始SQL語句以及considered_execution_plans中使用的索引基礎(chǔ)上，改寫語句，盡可能將原有語句中不能使用索引的條件句綁定到單表中對單表進行數(shù)據(jù)篩選
• attached_conditions_computation：使用啟發(fā)式算法計算已使用的索引，如果已使用的索引的訪問類型是ref的話，計算使用range方式訪問是否能使用組合索引中更多的索引列，如果可以的話，用range的方式替換ref的訪問方式
• attached_conditions_summary：針對上述的附加之后的情況匯總
  • attached：附加的條件或者是原語句中能直接下推給單表篩選的條件
  • table：對象表及其別名

5.2.7 clause_processing

{
    "clause_processing":{
        "clause":"ORDER BY",
        "items":[
            {
                "item":"`t1`.`col_int_key`"
            },
            {
                "item":"`t2`.`pk`"
            }
        ],
        "original_clause":"`t1`.`col_int_key`,`t2`.`pk`",
        "resulting_clause":"`t1`.`col_int_key`,`t2`.`pk`",
        "resulting_clause_is_simple":false
    }
}

主要是對DISTINCT、GROUP BY、ORDER BY等語句進行優(yōu)化

• clause：優(yōu)化語句關(guān)鍵字（DISTINCT、GROUP BY、ORDER BY）
• original_clause：優(yōu)化對象的原始語句
• items：original_clause中包含的對象
  • item：對象名
  • eq_ref_to_preceding_items：與前置表關(guān)聯(lián)的是否是唯一索引。在這個示例中，并沒有這樣的情況所以沒有列出。如果語句稍微改寫一下，將原始查詢語句改寫為t1 STRAIGHT_JOIN t2，讓t1去驅(qū)動t2表，就發(fā)現(xiàn)在分析items[1].item = t2.pk時出現(xiàn)了該字段。這是由于t1與t2通過t2的主鍵pk列進行關(guān)聯(lián)，這就意味著，t1中的一行數(shù)據(jù)最多只能在t2中關(guān)聯(lián)出一列，所以在后續(xù)優(yōu)化的結(jié)果語句中order by的t2.pk列被優(yōu)化掉了，因為這一列已經(jīng)確認(rèn)唯一不需要再進行排序：

{
    "clause_processing":{
        "clause":"ORDER BY",
        "items":[
            {
                "item":"`t1`.`col_int_key`"
            },
            {
                "eq_ref_to_preceding_items":true,
                "item":"`t2`.`pk`"
            }
        ],
        "original_clause":"`t1`.`col_int_key`,`t2`.`pk`",
        "resulting_clause":"`t1`.`col_int_key`",
        "resulting_clause_is_simple":true
    }
}

• resulting_clause_is_simple：優(yōu)化后的結(jié)果語句是否是簡單語句
• resulting_clause：優(yōu)化后的結(jié)果語句

那么顯然，這一塊我們需要重點關(guān)注的就是original_clause和resulting_clause，通過對比結(jié)果，再去查看細(xì)節(jié)，探究mysql在對應(yīng)從句上是做了哪些優(yōu)化

5.2.8 refine_plan

{
    "refine_plan":[
        {
            "table":"`t2`"
        },
        {
            "table":"`t1`"
        }
    ]
}

該階段展示的是改善之后的執(zhí)行計劃，如圖所示只展示了對應(yīng)的表對象沒有其他的字段的話，說明在之前已經(jīng)確定的執(zhí)行計劃中沒有需要再優(yōu)化的地方，可直接應(yīng)用

• table：涉及的表名及其別名
• pushed_index_condition：可使用到ICP的條件句
• table_condition_attached：在attaching_conditions_to_tables階段添加了附加條件的條件語句
• access_type：優(yōu)化后的索引訪問類型

5.3 join_execution

執(zhí)行階段
在join_execution階段，如果分析的語句是explain的話，該階段的代碼是空結(jié)構(gòu)體，只有在真正執(zhí)行語句之后，該階段的代碼才會有具體的步驟過程

• creating_tmp_table：創(chuàng)建臨時表
• filesort_information：文件排序信息
• filesort_priority_queue_optimization：文件排序-優(yōu)先隊列優(yōu)化
• filesort_execution：文件排序執(zhí)行
• filesort_summary：文件排序匯總信息

5.3.1 creating_tmp_table

{
    "creating_tmp_table":{
        "tmp_table_info":{
            "key_length":4,
            "location":"memory (heap)",
            "row_length":18,
            "row_limit_estimate":932067,
            "table":"intermediate_tmp_table",
            "unique_constraint":false
        }
    }
}

• converting_tmp_table_to_ondisk：將臨時表落地到磁盤中，如果臨時表的大小超過了設(shè)置的max_heap_table_size或者是tmp_table_size參數(shù)的話，那么將會出現(xiàn)該節(jié)點（本示例中并沒有出現(xiàn)）
• tmp_table_info：臨時表信息
  • table：臨時表的名稱
  • row_length：臨時表的單行長度
  • key_length：臨時表索引長度
  • unique_constraint：是否有使用唯一約束
  • location：表存儲位置，比如內(nèi)存表memory (heap)，或者是轉(zhuǎn)換到磁盤的物理表disk (InnoDB)
  • row_limit_estimate：該臨時表中能存儲的最大行數(shù)

5.3.2 filesort

{
    "filesort_execution":[

    ],
    "filesort_information":[
        {
            "direction":"asc",
            "field":"col_int_key",
            "table":"intermediate_tmp_table"
        },
        {
            "direction":"asc",
            "field":"pk",
            "table":"intermediate_tmp_table"
        }
    ],
    "filesort_priority_queue_optimization":{
        "cause":"quicksort_is_cheaper",
        "chosen":false,
        "limit":1000,
        "memory_available":262144,
        "row_size":17,
        "rows_estimate":10
    },
    "filesort_summary":{
        "examined_rows":0,
        "number_of_tmp_files":0,
        "rows":0,
        "sort_buffer_size":376,
        "sort_mode":"<sort_key, rowid>"
    }
}

在創(chuàng)建臨時表之后如果臨時表不需要轉(zhuǎn)換為磁盤表的話，即開始對文件排序進行處理

• filesort_information：文件排序信息（如果有多列，filesort_information下會有多個結(jié)構(gòu)體）
  • direction：排序列是升序還是降序
  • table：排序的表對象名
  • field：排序列
• filesort_priority_queue_optimization：優(yōu)先隊列優(yōu)化排序，一般在使用limit子句的時候會使用優(yōu)先隊列
  • usable：是否有使用
  • cause：沒有使用的原因
  • limit：限制查詢的行數(shù)
  • memory_available：可用內(nèi)存大小
  • row_size：單行大小
  • rows_estimate：估算的行數(shù)
• filesort_execution：執(zhí)行文件排序
• filesort_summary：文件排序匯總信息
  • rows：預(yù)計掃描行數(shù)
  • examined_rows：參與排序的行數(shù)
  • number_of_tmp_files：使用臨時文件的個數(shù)，這個值為0代表全部使用sort_buffer內(nèi)存排序，否則表示使用了磁盤文件排序
  • sort_buffer_size：使用的sort_buffer的大小
  • sort_mode：排序方式

6. 總結(jié)

在整個optimizer_trace中我們重點其實就是在跟蹤記錄TRACE的JSON樹，我們通過這棵樹中的內(nèi)容可以具體去分析優(yōu)化器究竟做了什么事情，進行了哪些選擇，是基于什么原因做的選擇，選擇的結(jié)果及依據(jù)。這一系列都可以輔助驗證我們的一些觀點及優(yōu)化，更好的幫助我們對我們的數(shù)據(jù)庫的實例進行調(diào)整。