伊人久久亚洲视频,91a国产精品,超碰人妻AV在线

TIPS

本文基于MySQL 8.0編寫(xiě)，理論支持MySQL 5.6及更高版本。

OPTIMIZER_TRACE是MySQL 5.6引入的一項(xiàng)跟蹤功能，它可以跟蹤優(yōu)化器做出的各種決策（比如訪問(wèn)表的方法、各種開(kāi)銷計(jì)算、各種轉(zhuǎn)換等），并將跟蹤結(jié)果記錄到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中。此功能默認(rèn)關(guān)閉，開(kāi)啟后，可分析如下語(yǔ)句：

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

OPTIMIZER_TRACE相關(guān)參數(shù)

TIPS

參考 https://dev.mysql.com/doc/internals/en/system-variables-controlling-trace.html

optimizer_trace
- optimizer_trace總開(kāi)關(guān)，默認(rèn)值：enabled=off,one_line=off
- enabled：是否開(kāi)啟optimizer_trace；on表示開(kāi)啟，off表示關(guān)閉。
- one_line：是否開(kāi)啟單行存儲(chǔ)。on表示開(kāi)啟；off表示關(guān)閉，將會(huì)用標(biāo)準(zhǔn)的JSON格式化存儲(chǔ)。設(shè)置成on將會(huì)有良好的格式，設(shè)置成off可節(jié)省一些空間。
optimizer_trace_features
- 控制optimizer_trace跟蹤的內(nèi)容，默認(rèn)值：greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on ，表示開(kāi)啟所有跟蹤項(xiàng)。
greedy_search：是否跟蹤貪心搜索，有關(guān)貪心算法詳見(jiàn) https://blog.csdn.net/weixin_42813521/article/details/105563103
- range_optimizer：是否跟蹤范圍優(yōu)化器
dynamic_range：是否跟蹤動(dòng)態(tài)范圍優(yōu)化
- repeated_subselect：是否跟蹤子查詢，如果設(shè)置成off，只跟蹤第一條Item_subselect的執(zhí)行
詳見(jiàn) https://dev.mysql.com/doc/internals/en/optimizer-features-to-trace.html
optimizer_trace_limit：控制optimizer_trace展示多少條結(jié)果，默認(rèn)1
optimizer_trace_max_mem_size：optimizer_trace堆棧信息允許的最大內(nèi)存，默認(rèn)1048576
optimizer_trace_offset：第一個(gè)要展示的optimizer trace的偏移量，默認(rèn)-1。
end_markers_in_json：如果JSON結(jié)構(gòu)很大，則很難將右括號(hào)和左括號(hào)配對(duì)。為了幫助讀者閱讀，可將其設(shè)置成on，這樣會(huì)在右括號(hào)附近加上注釋，默認(rèn)off。

參考： https://dev.mysql.com/doc/internals/en/end-markers-in-json-system-variable.html

TIPS
以上參數(shù)可用SET語(yǔ)句操作，例如，用如下命令即可打開(kāi)OPTIMIZER TRACE
>   SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
>

也可用SET GLOBAL全局開(kāi)啟。但即使全局開(kāi)啟OPTIMIZER_TRACE，每個(gè)Session也只能跟蹤它自己執(zhí)行的語(yǔ)句：
>   SET GLOBAL OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
>

optimizer_trace_limit和optimizer_trace_offset這兩個(gè)參數(shù)經(jīng)常配合使用，例如：
>   SET optimizer_trace_offset=<OFFSET>, optimizer_trace_limit=<LIMIT>
>

這兩個(gè)參數(shù)配合使用，有點(diǎn)類似MySQL里面的 limit語(yǔ)句。

默認(rèn)情況下，由于optimizer_trace_offset=-1，optimizer_trace_limit=1，記錄最近的一條SQL語(yǔ)句，展示時(shí)，每次展示1條數(shù)據(jù)；

如果改成 SET optimizer_trace_offset=-2, optimizer_trace_limit=1 ，則會(huì)記錄倒數(shù)第二條SQL語(yǔ)句；

有關(guān) optimizer_trace_offset 、optimizer_trace_limit更多細(xì)節(jié)，可參考 https://dev.mysql.com/doc/internals/en/tuning-trace-purging.html

OPTIMIZER_TRACE使用

開(kāi)啟OPTIMIZER_TRACE功能，并設(shè)置要展示的數(shù)據(jù)條目數(shù)：

SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET optimizer_trace_offset=-30, optimizer_trace_limit=30;

發(fā)送你想要分析的SQL語(yǔ)句，例如：

select *
from salaries
where from_date = '1986-06-26'
  and to_date = '1987-06-26';

使用如下語(yǔ)句分析，即可獲得類似如下的結(jié)果：

  mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30 \G;
  *************************** 1. row ***************************
                            QUERY: select *
  from salaries
where from_date = '1986-06-26'
    and to_date = '1987-06-26'
                              TRACE: {
    "steps": [
      {
        "join_preparation": {
          "select#": 1,
          "steps": [
            {
              "expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))"
            }
          ] /* steps */
        } /* join_preparation */
      },
      {
        "join_optimization": {
          "select#": 1,
          "steps": [
            {
              "condition_processing": {
                "condition": "WHERE",
                "original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))",
                "steps": [
                  {
                    "transformation": "equality_propagation",
                    "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
                  },
                  {
                    "transformation": "constant_propagation",
                    "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
                  },
                  {
                    "transformation": "trivial_condition_removal",
                    "resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"
                  }
                ] /* steps */
              } /* condition_processing */
            },
            {
              "substitute_generated_columns": {
              } /* substitute_generated_columns */
            },
            {
              "table_dependencies": [
                {
                  "table": "`salaries`",
                  "row_may_be_null": false,
                  "map_bit": 0,
                  "depends_on_map_bits": [
                  ] /* depends_on_map_bits */
                }
              ] /* table_dependencies */
            },
            {
              "ref_optimizer_key_uses": [
                {
                  "table": "`salaries`",
                  "field": "from_date",
                  "equals": "DATE'1986-06-26'",
                  "null_rejecting": false
                },
                {
                  "table": "`salaries`",
                  "field": "to_date",
                  "equals": "DATE'1987-06-26'",
                  "null_rejecting": false
                }
              ] /* ref_optimizer_key_uses */
            },
            {
              "rows_estimation": [
                {
                  "table": "`salaries`",
                  "range_analysis": {
                    "table_scan": {
                      "rows": 2838216,
                      "cost": 286799
                    } /* table_scan */,
                    "potential_range_indexes": [
                      {
                        "index": "PRIMARY",
                        "usable": false,
                        "cause": "not_applicable"
                      },
                      {
                        "index": "salaries_from_date_to_date_index",
                        "usable": true,
                        "key_parts": [
                          "from_date",
                          "to_date",
                          "emp_no"
                        ] /* key_parts */
                      }
                    ] /* potential_range_indexes */,
                    "setup_range_conditions": [
                    ] /* setup_range_conditions */,
                    "group_index_range": {
                      "chosen": false,
                      "cause": "not_group_by_or_distinct"
                    } /* group_index_range */,
                    "skip_scan_range": {
                      "potential_skip_scan_indexes": [
                        {
                          "index": "salaries_from_date_to_date_index",
                          "usable": false,
                          "cause": "query_references_nonkey_column"
                        }
                      ] /* potential_skip_scan_indexes */
                    } /* skip_scan_range */,
                    "analyzing_range_alternatives": {
                      "range_scan_alternatives": [
                        {
                          "index": "salaries_from_date_to_date_index",
                          "ranges": [
                            "0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
                          ] /* ranges */,
                          "index_dives_for_eq_ranges": true,
                          "rowid_ordered": true,
                          "using_mrr": false,
                          "index_only": false,
                          "rows": 86,
                          "cost": 50.909,
                          "chosen": true
                        }
                      ] /* range_scan_alternatives */,
                      "analyzing_roworder_intersect": {
                        "usable": false,
                        "cause": "too_few_roworder_scans"
                      } /* analyzing_roworder_intersect */
                    } /* analyzing_range_alternatives */,
                    "chosen_range_access_summary": {
                      "range_access_plan": {
                        "type": "range_scan",
                        "index": "salaries_from_date_to_date_index",
                        "rows": 86,
                        "ranges": [
                          "0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
                        ] /* ranges */
                      } /* range_access_plan */,
                      "rows_for_plan": 86,
                      "cost_for_plan": 50.909,
                      "chosen": true
                    } /* chosen_range_access_summary */
                  } /* range_analysis */
                }
              ] /* rows_estimation */
            },
            {
              "considered_execution_plans": [
                {
                  "plan_prefix": [
                  ] /* plan_prefix */,
                  "table": "`salaries`",
                  "best_access_path": {
                    "considered_access_paths": [
                      {
                        "access_type": "ref",
                        "index": "salaries_from_date_to_date_index",
                        "rows": 86,
                        "cost": 50.412,
                        "chosen": true
                      },
                      {
                        "access_type": "range",
                        "range_details": {
                          "used_index": "salaries_from_date_to_date_index"
                        } /* range_details */,
                        "chosen": false,
                        "cause": "heuristic_index_cheaper"
                      }
                    ] /* considered_access_paths */
                  } /* best_access_path */,
                  "condition_filtering_pct": 100,
                  "rows_for_plan": 86,
                  "cost_for_plan": 50.412,
                  "chosen": true
                }
              ] /* considered_execution_plans */
            },
            {
              "attaching_conditions_to_tables": {
                "original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
                "attached_conditions_computation": [
                ] /* attached_conditions_computation */,
                "attached_conditions_summary": [
                  {
                    "table": "`salaries`",
                    "attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
                  }
                ] /* attached_conditions_summary */
              } /* attaching_conditions_to_tables */
            },
            {
              "finalizing_table_conditions": [
                {
                  "table": "`salaries`",
                  "original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
                  "final_table_condition   ": null
                }
              ] /* finalizing_table_conditions */
            },
            {
              "refine_plan": [
                {
                  "table": "`salaries`"
                }
              ] /* refine_plan */
            }
          ] /* steps */
        } /* join_optimization */
      },
      {
        "join_execution": {
          "select#": 1,
          "steps": [
          ] /* steps */
        } /* join_execution */
      }
    ] /* steps */
  }
  MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
            INSUFFICIENT_PRIVILEGES: 0
  1 row in set (0.00 sec)

分析完成，關(guān)閉OPTIMIZER_TRACE
```
SET optimizer_trace="enabled=off";
```

OPTIMIZER_TRACE結(jié)果分析

由上面的結(jié)果可知，OPTIMIZER_TRACE有四個(gè)字段：

QUERY：查詢語(yǔ)句
TRACE：QUERY字段對(duì)應(yīng)語(yǔ)句的跟蹤信息
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟蹤信息過(guò)長(zhǎng)時(shí)，被截?cái)嗟母櫺畔⒌淖止?jié)數(shù)。
INSUFFICIENT_PRIVILEGES：執(zhí)行跟蹤語(yǔ)句的用戶是否有查看對(duì)象的權(quán)限。當(dāng)不具有權(quán)限時(shí)，該列信息為1且TRACE字段為空，一般在調(diào)用帶有SQL SECURITY DEFINER的視圖或者是存儲(chǔ)過(guò)程的情況下，會(huì)出現(xiàn)此問(wèn)題。

TIPS

參考： https://dev.mysql.com/doc/refman/8.0/en/optimizer-trace-table.html

最核心的是TRACE字段的內(nèi)容。我們逐段分析：

join_preparation

join_preparation段落展示了準(zhǔn)備階段的執(zhí)行過(guò)程。

{
  "join_preparation": {
    "select#": 1,
    "steps": [
      {
        -- 對(duì)比下原始語(yǔ)句，可以知道，這一步做了個(gè)格式化。
        "expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))"
      }
    ]
    /* steps */
  }
  /* join_preparation */
}

join_optimization

join_optimization展示了優(yōu)化階段的執(zhí)行過(guò)程，是分析OPTIMIZER TRACE的重點(diǎn)。這段內(nèi)容超級(jí)長(zhǎng)，而且分了好多步驟，不妨按照步驟逐段分析：

condition_processing

該段用來(lái)做條件處理，主要對(duì)WHERE條件進(jìn)行優(yōu)化處理。

"condition_processing": {
  "condition": "WHERE",
  "original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))",
  "steps": [
    {
      "transformation": "equality_propagation",
      "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
    },
    {
      "transformation": "constant_propagation",
      "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
    },
    {
      "transformation": "trivial_condition_removal",
      "resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"
    }
  ] /* steps */
} /* condition_processing */

其中：

condition：優(yōu)化對(duì)象類型。WHERE條件句或者是HAVING條件句
original_condition：優(yōu)化前的原始語(yǔ)句
steps：主要包括三步，分別是quality_propagation（等值條件句轉(zhuǎn)換），constant_propagation（常量條件句轉(zhuǎn)換），trivial_condition_removal（無(wú)效條件移除的轉(zhuǎn)換）
- transformation：轉(zhuǎn)換類型句
- resulting_condition：轉(zhuǎn)換之后的結(jié)果輸出

substitute_generated_columns

substitute_generated_columns用于替換虛擬生成列

"substitute_generated_columns": {
} /* substitute_generated_columns */

table_dependencies

分析表之間的依賴關(guān)系

{
  "table_dependencies": [
    {
      "table": "`salaries`",
      "row_may_be_null": false,
      "map_bit": 0,
      "depends_on_map_bits": [
      ] /* depends_on_map_bits */
    }
  ] /* table_dependencies */
}

其中：

table：涉及的表名，如果有別名，也會(huì)展示出來(lái)
row_may_be_null：行是否可能為NULL，這里是指JOIN操作之后，這張表里的數(shù)據(jù)是不是可能為NULL。如果語(yǔ)句中使用了LEFT JOIN，則后一張表的row_may_be_null會(huì)顯示為true
map_bit：表的映射編號(hào)，從0開(kāi)始遞增
depends_on_map_bits：依賴的映射表。主要是當(dāng)使用STRAIGHT_JOIN強(qiáng)行控制連接順序或者LEFT JOIN/RIGHT JOIN有順序差別時(shí)，會(huì)在depends_on_map_bits中展示前置表的map_bit值。

ref_optimizer_key_uses

列出所有可用的ref類型的索引。如果使用了組合索引的多個(gè)部分（例如本例，用到了index(from_date, to_date) 的多列索引），則會(huì)在ref_optimizer_key_uses下列出多個(gè)元素，每個(gè)元素中會(huì)列出ref使用的索引及對(duì)應(yīng)值。

{
  "ref_optimizer_key_uses": [
    {
      "table": "`salaries`",
      "field": "from_date",
      "equals": "DATE'1986-06-26'",
      "null_rejecting": false
    },
    {
      "table": "`salaries`",
      "field": "to_date",
      "equals": "DATE'1987-06-26'",
      "null_rejecting": false
    }
  ] /* ref_optimizer_key_uses */
}

rows_estimation

顧名思義，用于估算需要掃描的記錄數(shù)。

{
  "rows_estimation": [
    {
      "table": "`salaries`",
      "range_analysis": {
        "table_scan": {
          "rows": 2838216,
          "cost": 286799
        } /* table_scan */,
        "potential_range_indexes": [
          {
            "index": "PRIMARY",
            "usable": false,
            "cause": "not_applicable"
          },
          {
            "index": "salaries_from_date_to_date_index",
            "usable": true,
            "key_parts": [
              "from_date",
              "to_date",
              "emp_no"
            ] /* key_parts */
          }
        ] /* potential_range_indexes */,
        "setup_range_conditions": [
        ] /* setup_range_conditions */,
        "group_index_range": {
          "chosen": false,
          "cause": "not_group_by_or_distinct"
        } /* group_index_range */,
        "skip_scan_range": {
          "potential_skip_scan_indexes": [
            {
              "index": "salaries_from_date_to_date_index",
              "usable": false,
              "cause": "query_references_nonkey_column"
            }
          ] /* potential_skip_scan_indexes */
        } /* skip_scan_range */,
        "analyzing_range_alternatives": {
          "range_scan_alternatives": [
            {
              "index": "salaries_from_date_to_date_index",
              "ranges": [
                "0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
              ] /* ranges */,
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": true,
              "using_mrr": false,
              "index_only": false,
              "rows": 86,
              "cost": 50.909,
              "chosen": true
            }
          ] /* range_scan_alternatives */,
          "analyzing_roworder_intersect": {
            "usable": false,
            "cause": "too_few_roworder_scans"
          } /* analyzing_roworder_intersect */
        } /* analyzing_range_alternatives */,
        "chosen_range_access_summary": {
          "range_access_plan": {
            "type": "range_scan",
            "index": "salaries_from_date_to_date_index",
            "rows": 86,
            "ranges": [
              "0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
            ] /* ranges */
          } /* range_access_plan */,
          "rows_for_plan": 86,
          "cost_for_plan": 50.909,
          "chosen": true
        } /* chosen_range_access_summary */
      } /* range_analysis */
    }
  ] /* rows_estimation */
}

其中：

table：表名
range_analysis：
- table_scan：如果全表掃描的話，需要掃描多少行（row，2838216），以及需要的代價(jià)（cost，286799）
- potential_range_indexes：列出表中所有的索引并分析其是否可用。如果不可用的話，會(huì)列出不可用的原因是什么；如果可用會(huì)列出索引中可用的字段；
- setup_range_conditions：如果有可下推的條件，則帶條件考慮范圍查詢
- group_index_range：當(dāng)使用了GROUP BY或DISTINCT時(shí)，是否有合適的索引可用。當(dāng)未使用GROUP BY或DISTINCT時(shí)，會(huì)顯示chosen=false, cause=not_group_by_or_distinct；如使用了GROUP BY或DISTINCT，但是多表查詢時(shí)，會(huì)顯示chosen=false，cause =not_single_table。其他情況下會(huì)嘗試分析可用的索引（potential_group_range_indexes）并計(jì)算對(duì)應(yīng)的掃描行數(shù)及其所需代價(jià)
- skip_scan_range：是否使用了skip scan
  
  TIPS
  
  skip_scan_range是MySQL 8.0的新特性，感興趣的可詳見(jiàn) https://blog.csdn.net/weixin_43970890/article/details/89494915
- analyzing_range_alternatives：分析各個(gè)索引的使用成本
  - range_scan_alternatives：range掃描分析
    - index：索引名
    - ranges：range掃描的條件范圍
    - index_dives_for_eq_ranges：是否使用了index dive，該值會(huì)被參數(shù)eq_range_index_dive_limit變量值影響。
    - rowid_ordered：該range掃描的結(jié)果集是否根據(jù)PK值進(jìn)行排序
    - using_mrr：是否使用了mrr
    - index_only：表示是否使用了覆蓋索引
    - rows：掃描的行數(shù)
    - cost：索引的使用成本
    - chosen：表示是否使用了該索引
  - analyzing_roworder_intersect：分析是否使用了索引合并（index merge），如果未使用，會(huì)在cause中展示原因；如果使用了索引合并，會(huì)在該部分展示索引合并的代價(jià)。
- chosen_range_access_summary：在前一個(gè)步驟中分析了各類索引使用的方法及代價(jià)，得出了一定的中間結(jié)果之后，在summary階段匯總前一階段的中間結(jié)果確認(rèn)最后的方案
  - range_access_plan：range掃描最終選擇的執(zhí)行計(jì)劃。
    - type：展示執(zhí)行計(jì)劃的type，如果使用了索引合并，則會(huì)顯示index_roworder_intersect
    - index：索引名
    - rows：掃描的行數(shù)
    - ranges：range掃描的條件范圍
  - rows_for_plan：該執(zhí)行計(jì)劃的掃描行數(shù)
  - cost_for_plan：該執(zhí)行計(jì)劃的執(zhí)行代價(jià)
  - chosen：是否選擇該執(zhí)行計(jì)劃

considered_execution_plans

負(fù)責(zé)對(duì)比各可行計(jì)劃的開(kāi)銷，并選擇相對(duì)最優(yōu)的執(zhí)行計(jì)劃。

{
  "considered_execution_plans": [
    {
      "plan_prefix": [
      ] /* plan_prefix */,
      "table": "`salaries`",
      "best_access_path": {
        "considered_access_paths": [
          {
            "access_type": "ref",
            "index": "salaries_from_date_to_date_index",
            "rows": 86,
            "cost": 50.412,
            "chosen": true
          },
          {
            "access_type": "range",
            "range_details": {
              "used_index": "salaries_from_date_to_date_index"
            } /* range_details */,
            "chosen": false,
            "cause": "heuristic_index_cheaper"
          }
        ] /* considered_access_paths */
      } /* best_access_path */,
      "condition_filtering_pct": 100,
      "rows_for_plan": 86,
      "cost_for_plan": 50.412,
      "chosen": true
    }
  ] /* considered_execution_plans */
}

其中：

plan_prefix：當(dāng)前計(jì)劃的前置執(zhí)行計(jì)劃。
table：涉及的表名，如果有別名，也會(huì)展示出來(lái)
best_access_path：通過(guò)對(duì)比considered_access_paths，選擇一個(gè)最優(yōu)的訪問(wèn)路徑
- considered_access_paths：當(dāng)前考慮的訪問(wèn)路徑
  - access_type：使用索引的方式，可參考explain中的type字段
  - index：索引
  - rows：行數(shù)
  - cost：開(kāi)銷
  - chosen：是否選用這種執(zhí)行路徑
condition_filtering_pct：類似于explain的filtered列，是一個(gè)估算值
rows_for_plan：執(zhí)行計(jì)劃最終的掃描行數(shù)，由considered_access_paths.rows X condition_filtering_pct計(jì)算獲得。
cost_for_plan：執(zhí)行計(jì)劃的代價(jià)，由considered_access_paths.cost相加獲得
chosen：是否選擇了該執(zhí)行計(jì)劃

attaching_conditions_to_tables

基于considered_execution_plans中選擇的執(zhí)行計(jì)劃，改造原有where條件，并針對(duì)表增加適當(dāng)?shù)母郊訔l件，以便于單表數(shù)據(jù)的篩選。

TIPS

這部分條件的增加主要是為了便于ICP（索引條件下推），但I(xiàn)CP是否開(kāi)啟并不影響這部分內(nèi)容的構(gòu)造。

ICP參考文檔：https://www.cnblogs.com/Terry-Wu/p/9273177.html

{
  "attaching_conditions_to_tables": {
    "original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
    "attached_conditions_computation": [
    ] /* attached_conditions_computation */,
    "attached_conditions_summary": [
      {
        "table": "`salaries`",
        "attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
      }
    ] /* attached_conditions_summary */
  } /* attaching_conditions_to_tables */
}

其中：

original_condition：原始的條件語(yǔ)句
attached_conditions_computation：使用啟發(fā)式算法計(jì)算已使用的索引，如果已使用的索引的訪問(wèn)類型是ref，則計(jì)算用range能否使用組合索引中更多的列，如果可以，則用range的方式替換ref。
attached_conditions_summary：附加之后的情況匯總
- table：表名
- attached：附加的條件或原語(yǔ)句中能直接下推給單表篩選的條件。

finalizing_table_conditions

最終的、經(jīng)過(guò)優(yōu)化后的表?xiàng)l件。

{
  "finalizing_table_conditions": [
    {
      "table": "`salaries`",
      "original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
      "final_table_condition   ": null
    }
  ] /* finalizing_table_conditions */
}

refine_plan

改善執(zhí)行計(jì)劃：

{
  "refine_plan": [
    {
      "table": "`salaries`"
    }
  ] /* refine_plan */
}

其中：

table：表名及別名

join_execution

join_execution段落展示了執(zhí)行階段的執(zhí)行過(guò)程。

"join_execution": {
  "select#": 1,
  "steps": [
  ] /* steps */
}

參考文檔

Tracing the Optimizer
手把手教你認(rèn)識(shí)OPTIMIZER_TRACE
MYSQL sql執(zhí)行過(guò)程的一些跟蹤分析(二.mysql優(yōu)化器追蹤分析)
使用 Trace 進(jìn)行執(zhí)行計(jì)劃分析

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySQL 調(diào)優(yōu) | OPTIMIZER_TRACE詳解

MySQL 調(diào)優(yōu) | OPTIMIZER_TRACE詳解

OPTIMIZER_TRACE相關(guān)參數(shù)

OPTIMIZER_TRACE使用

OPTIMIZER_TRACE結(jié)果分析

join_preparation

join_optimization

condition_processing

substitute_generated_columns

table_dependencies

ref_optimizer_key_uses

rows_estimation

considered_execution_plans

attaching_conditions_to_tables

finalizing_table_conditions

refine_plan

join_execution

參考文檔

相關(guān)文章

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

MySQL 調(diào)優(yōu) | OPTIMIZER_TRACE詳解

OPTIMIZER_TRACE相關(guān)參數(shù)

OPTIMIZER_TRACE使用

OPTIMIZER_TRACE結(jié)果分析

join_preparation

join_optimization

condition_processing

substitute_generated_columns

table_dependencies

ref_optimizer_key_uses

rows_estimation

considered_execution_plans

attaching_conditions_to_tables

finalizing_table_conditions

refine_plan

join_execution

參考文檔

相關(guān)文章

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av