MYSQL 索引分析工具——trace

java熟手

已于 2024-10-05 18:26:16 修改

阅读量501

点赞数 3

分类专栏： mysql 文章标签： mysql 数据库

于 2024-09-29 20:05:01 首次发布

本文链接：https://blog.csdn.net/aquriushu/article/details/142641554

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

众所周知，mysql在执行sql语句时，有自己的优化策略，与我们预知的优化方案可能不尽相同，尤其在索引的覆盖层面，经常会与预想不同。我们可以通过mysql提供的trace工具，可以让我们明白optimizer（优化器）如何选择执行计划。

使用前先营造数据，创建表employees，为该表添加主键索引和组合索引，并通过存储过程插入10w条数据：

CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 drop procedure if exists insert_emp;
 delimiter ;;
 create procedure insert_emp()
 begin
   declare i int;
   set i=1;
   while(i<=100000)do
     insert into employees(name,age,position) values(CONCAT('zhangsan',i),i,'dev');
     set i=i+1;
   end while;
 end;;
 delimiter ;
 call insert_emp();

1，如何使用trace

开启trace工具会影响mysql性能，所以只能分析SQL时使用，使用完后需立即关闭。

1），开启trace，只在当前回话期有效。navicat新建查询再去查看优化器开启状态，为关闭状态。

-- 查询优化器开启状态
SHOW VARIABLES LIKE 'optimizer_trace';

-- 打开优化器，结果以json格式输出
set session optimizer_trace="enabled=on",end_markers_in_json=on;

2），使用trace。SQL语句后加上查询优化器，即可获取优化器分析结果

-- 业务SQL
SELECT * FROM employees WHERE name>'a';

-- 查询优化器分析结果
SELECT * FROM information_schema.OPTIMIZER_TRACE;

2，分析优化器解析结果

直接分析1.2中的查询语句，执行和查询结果如下：

结果1为name>'a'的查询结果，结果2即为优化器分析过程和结果，主要查看TRACE字段值，

优化器分析结果总共分为三步，如下图：

第一阶段的准备和第三阶段的执行都不是优化分析过程，不予理会，具体数据如下：

主要分析第二阶段，具体的分组已通过注释在代码中声明，主要有：所有索引分析，全表扫描分析，最优方案分析。最终选择依据是各个方案的预估耗时(即：cost)

    /* 第二阶段：SQL优化阶段 */
    {
      "join_optimization": {
        "select#": 1,
        "steps": [

          /*  条件处理 */
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`employees`.`name` > 'a')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },

          /* 表依赖 */
          {
            "table_dependencies": [
              {
                "table": "`employees`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },

          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },

          /* 核心：预估表的访问成本，通过预估的cost，决定使用那种优化策略 */
          {
            "rows_estimation": [
              {
                "table": "`employees`",

                "range_analysis": {
                  
                  /* 全表扫描情况 */
                  "table_scan": {
                    "rows": 100140, /* 扫描行数*/
                    "cost": 20383 /* 查询成本*/
                  } /* table_scan */,

                  /* 潜在可使用的范围索引 */
                  "potential_range_indexes": [
                    /* 主键索引 */
                    {
                      "index": "PRIMARY",
                      "usable": false, /* 未使用 */
                      "cause": "not_applicable" /* 原因：不合适 */
                    },

                    /* 组合索引 */
                    {
                      "index": "idx_name_age_position",
                      "usable": true, /* 使用 */
                      "key_parts": [
                        "name",
                        "age",
                        "position",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,

                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,

                  /* group by 或 distinct 用到的索引 */
                  "group_index_range": {
                    "chosen": false, /* 未使用 */
                    "cause": "not_group_by_or_distinct" /* 原因：不是group by 或 distinct */
                  } /* group_index_range */,

                  /* 分析各个备选方案 */
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_name_age_position",
                        "ranges": [
                          "a < name"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,  /* 使用该索引获取的记录是否按照主键排序 */
                        "using_mrr": false,
                        "index_only": false, /* 是否使用覆盖索引 */
                        "rows": 50070,   /* 预估索引扫描行数 */
                        "cost": 60085,   /* 索引使用成本 */
                        "chosen": false, /* 是否选择该索引 */
                        "cause": "cost"  /* 原因：cost预估太久 */
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },

          /* 考虑使用的执行方案 */
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`employees`",

                /* 最优访问路径 */
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 100140,
                      "access_type": "scan", /* 访问类型：为scan，全表扫描 */
                      "resulting_rows": 100140,
                      "cost": 20381, /* 预估耗时 */
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,

                "condition_filtering_pct": 100,
                "rows_for_plan": 100140,
                "cost_for_plan": 20381,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`employees`.`name` > 'a')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`employees`",
                  "attached": "(`employees`.`name` > 'a')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`employees`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },

经上述结果分析可知，根据当前表的索引（主键索引、组合索引）对比，可能采取组合索引进行查询；再预估全表扫描结果，全表扫描的cost比组合索引的cost小，mysql即判断全表扫描比组合索引效率更高，最终执行方案为全表扫描。通过explain语句可确认分析结果：