mysql中optimizer trace的作用

最新推荐文章于 2024-08-21 23:28:12 发布

韩朝洋

最新推荐文章于 2024-08-21 23:28:12 发布

阅读量827

点赞数 19

分类专栏： # 从根儿上理解MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_41534540/article/details/139429884

版权

从根儿上理解MySQL 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

大家好。对于MySQL 5.6以及之前的版本来说，查询优化器就像是一个黑盒子一样，我们只能通过EXPLAIN语句查看到最后优化器决定使用的执行计划，却无法知道它为什么做这个决策。于是在MySQL5.6以及之后的版本中，MySQL新增了一个optimizer trace的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程，今天我们就来简单了解一下这个功能。

optimizer trace功能的开启与关闭由系统变量optimizer_trace决定，我们看一下：
在这里插入图片描述
可以看到enabled值为off ，表明这个功能默认是关闭的。 one_line的值是控制输出格式的，如果为on那么所有输出都将在一行中展示，不适合人阅读。

如果想打开optimizer trace功能，必须首先把enabled的值改为on ，我们可以通过下边这个sql语句修改enabled的值：

SET optimizer_trace="enabled=on";

enabled的值改为on后我们就可以输入我们想要查看优化过程的查询语句，当该查询语句执行完成后，就可以到information_schema数据库下的OPTIMIZER_TRACE表中查看完整的优化过程。这个 OPTIMIZER_TRACE 表有4个列，分别是：

QUERY ： 表示我们的查询语句。

TRACE ： 表示优化过程的JSON格式文本。

MISSING_BYTES_BEYOND_MAX_MEM_SIZE ： 由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。

INSUFFICIENT_PRIVILEGES ： 表示是否没有权限查看优化过程，默认值是0。

完整的使用optimizer trace 功能的步骤总结如下：

#1. 打开optimizer trace功能 (默认情况下它是关闭的):

SET optimizer_trace="enabled=on";

#2. 输入自己的查询语句

SELECT ...;

#3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程

SELECT * FROM information_schema.OPTIMIZER_TRACE;

#4. 可能你还要观察其他语句执行的优化过程，重复上边的第2、3步。
#5. 当你停止查看语句的优化过程时，把optimizer trace功能关闭。

SET optimizer_trace="enabled=off"

下面我们以一个复杂一点的sql为例，来聊一聊如何使用optimizer trace功能。
在这里插入图片描述

可以看到该查询可能使用到的索引有3个，那么为什么优化器最终选择了idx_key1而不选择其他的索引或者直接全表扫描呢？这时候就可以通过otpimzer trace 功能来查看优化器的具体工作过程：

SET optimizer_trace="enabled=on"; 
SELECT * FROM s1 WHERE  
   key1 > 'z' AND  
   key2 < 1000000 AND  
   key3 IN ('a', 'b', 'c') AND  
   common_field = 'abc'; 
SELECT * FROM information_schema.OPTIMIZER_TRACE\G;

我们直接看一下通过查询OPTIMIZER_TRACE 表得到的输出:

*************************** 1. row ***************************
# 分析的查询语句是什么 
QUERY: SELECT * FROM single_table WHERE key1 > 'z' AND key2 < 1000000 AND key3 IN ('a', 'b', 'c')AND common_field = 'abc'
# 优化的具体过程 
TRACE: {
  "steps": [
    {
      "join_preparation": {  # prepare阶段 
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          {
            "expanded_query": "/* select#1 */ select `single_table`.`id` AS `id`,`single_table`.`key1` AS `key1`,`single_table`.`key2` AS `key2`,`single_table`.`key3` AS `key3`,`single_table`.`key_part1` AS `key_part1`,`single_table`.`key_part2` AS `key_part2`,`single_table`.`key_part3` AS `key_part3`,`single_table`.`common_field` AS `common_field` from `single_table` where ((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"
          }
        ]
      }
    },
    {
      "join_optimization": {  # optimize阶段 
        "select#": 1,
        "steps": [
          {
            "condition_processing": {   # 处理搜索条件
              "condition": "WHERE",
              # 原始搜索条件 
              "original_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))",
              "steps": [
                {
                  # 等值传递转换 
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"
                },
                {
                  # 常量传递转换     
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"
                },
                {
                  # 去除没用的条件 
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"
                }
              ]
            }
          },
          {
            # 替换虚拟生成列 
            "substitute_generated_columns": {
            }
          },
          {
            # 表的依赖信息 
            "table_dependencies": [
              {
                "table": "`single_table`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            # 预估不同单表访问方法的访问成本 
            "rows_estimation": [
              {
                "table": "`single_table`",
                "range_analysis": {
                  "table_scan": {  # 全表扫描的行数以及成本 
                    "rows": 9823,
                    "cost": 1012.48
                  },
                   # 分析可能使用的索引 
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",   # 主键不可用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_key2",  # idx_key2可能被使用 
                      "usable": true,
                      "key_parts": [
                        "key2"
                      ]
                    },
                    {
                      "index": "idx_key1",  # idx_key1可能被使用 
                      "usable": true,
                      "key_parts": [
                        "key1",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_key3",  # idx_key3可能被使用 
                      "usable": true,
                      "key_parts": [
                        "key3",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_key_part",   # idx_keypart不可用
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_skip_scan": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "idx_key2",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      },
                      {
                        "index": "idx_key1",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      },
                      {
                        "index": "idx_key3",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ]
                  },
                  # 分析各种可能使用的索引的成本 
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        # 使用idx_key2的成本分析
                        "index": "idx_key2",
                        # 使用idx_key2的范围区间 
                        "ranges": [
                          "NULL < key2 < 1000000"
                        ],
                        "index_dives_for_eq_ranges": true,  # 是否使用index dive 
                        "rowid_ordered": false,  # 使用该索引获取的记录是否按照主键排序 
                        "using_mrr": false,   # 是否使用mrr 
                        "index_only": false,  # 是否是索引覆盖访问
                        "in_memory": 1,   
                        "rows": 10000,  # 使用该索引获取的记录条数 
                        "cost": 3895.04,  # 使用该索引的成本 
                        "chosen": false,  # 是否选择该索引
                        "cause": "cost"  # 因为成本太大所以不选择该索引 
                      },
                      {
                        # 使用idx_key1的成本分析 
                        "index": "idx_key1",
                        # 使用idx_key1的范围区间 
                        "ranges": [
                          "'z' < key1"
                        ],
                        "index_dives_for_eq_ranges": true,  # 是否使用index dive 
                        "rowid_ordered": false, # 使用该索引获取的记录是否按照主键排序 
                        "using_mrr": false,  # 是否使用mrr
                        "index_only": false, # 是否是索引覆盖访问
                        "in_memory": 0.0769231,
                        "rows": 1, # 使用该索引获取的记录条数
                        "cost": 0.688947, # 使用该索引的成本 
                        "chosen": true # 是否选择该索引
                      },
                      {
                        # 使用idx_key3的成本分析 
                        "index": "idx_key3",
                        # 使用idx_key3的范围区间 
                        "ranges": [
                          "key3 = 'a'",
                          "key3 = 'b'",
                          "key3 = 'c'"
                        ],
                        "index_dives_for_eq_ranges": true,  # 是否使用index dive 
                        "rowid_ordered": false, # 使用该索引获取的记录是否按照主键排序 
                        "using_mrr": false,  # 是否使用mrr
                        "index_only": false, # 是否是索引覆盖访问
                        "in_memory": 0.0769231,
                        "rows": 3, # 使用该索引获取的记录条数
                        "cost": 2.04684, # 使用该索引的成本 
                        "chosen": false, # 是否选择该索引
                        "cause": "cost"  # 因为成本太大所以不选择该索引 
                      }
                    ],
                    # 分析使用索引合并的成本 
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  # 对于上述单表查询最优的访问方法 
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_key1",
                      "rows": 1,
                      "ranges": [
                        "'z' < key1"
                      ]
                    },
                    "rows_for_plan": 1,
                    "cost_for_plan": 0.688947,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
           # 分析各种可能的执行计划 
           #（对多表查询这可能有很多种不同的方案，单表查询的方案上边已经分析过了，直接选取idx_key1就好）
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`single_table`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_key1"
                      },
                      "resulting_rows": 1,
                      "cost": 0.788947,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 0.788947,
                "chosen": true
              }
            ]
          },
          {
            # 尝试给查询添加一些其他的查询条件 
            "attaching_conditions_to_tables": {
              "original_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`single_table`",
                  "attached": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"
                }
              ]
            }
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`single_table`",
                "original_table_condition": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))",
                "final_table_condition   ": "((`single_table`.`key1` > 'z') and (`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"
              }
            ]
          },
          {
            # 再稍稍的改进一下执行计划 
            "refine_plan": [
              {
                "table": "`single_table`",
                "pushed_index_condition": "(`single_table`.`key1` > 'z')",
                "table_condition_attached": "((`single_table`.`key2` < 1000000) and (`single_table`.`key3` in ('a','b','c')) and (`single_table`.`common_field` = 'abc'))"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {   # execute阶段
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
# 因优化过程文本太多而丢弃的文本字节大小，值为0时表示并没有丢弃 
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
# 权限字段 
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)

通过上述的信息我们得知，优化过程大致分为了三个阶段：prepare 阶段、optimize阶段和execute阶段。

我们所说的基于成本的优化主要集中在optimize阶段，对于单表查询来说，我们主要关注optimize阶段的"rows_estimation"这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；对于多表连接查询来说，我们更多需要关注"considered_execution_plans"这个过程，这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终会选择成本最低的那种方案来作为最终的执行计划，也就是我们使用EXPLAIN语句所展现出的那种方案。

好了，到这里我们就讲完了，大家有什么想法欢迎留言讨论。也希望大家能给作者点个关注，谢谢大家！最后依旧是请各位老板有钱的捧个人场，没钱的也捧个人场，谢谢各位老板！

韩朝洋

关注

19
点赞
踩
13

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql中optimizer trace的作用

我们所说的基于成本的优化主要集中在optimize阶段，对于单表查询来说，我们主要关注optimize阶段的"rows_estimation"这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；enabled的值改为on后我们就可以输入我们想要查看优化过程的查询语句，当该查询语句执行完成后，就可以到information_schema数据库下的OPTIMIZER_TRACE表中查看完整的优化过程。one_line的值是控制输出格式的，如果为on那么所有输出都将在一行中展示，不适合人阅读。
复制链接

扫一扫