mysql执行成本分析2

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第18天，点击查看活动详情

认识trace工具

我们正常一个sql语句如何走的索引，为什么同样的语句不同的数据量，有的走索引有的不走？

带着这个疑惑我们来认识一下trace工具，最后附上完成分析代码。

首先看一下如何使用：

set session optimizer_trace="enabled=on",end_markers_in_json=on;  -- 开启trace。切记该开关仅临时使用，测试完记得关闭，影响性能。
select * from test_index where name > '北' order by addr; -- 执行语句
 
SELECT * FROM information_schema.OPTIMIZER_TRACE; -- 分析数据

第一阶段：SQL准备阶段，格式化sql

    {
      "join_preparation": { -- 第一阶段：SQL准备阶段，格式化sql
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `test_index`.`id` AS `id`,`test_index`.`name` AS `name`,`test_index`.`score` AS `score`,`test_index`.`addr` AS `addr`,`test_index`.`ctime` AS `ctime` from `test_index` where (`test_index`.`name` > '北') order by `test_index`.`addr`"
          }
        ] /* steps */
      } /* join_preparation */
    }

第二阶段：SQL优化阶段（分析具体用哪个索引，以及分析使用的原因）

条件处理

          {
					/* begin-condition_processing */
            "condition_processing": { -- --条件处理
              "condition": "WHERE",
              "original_condition": "(`test_index`.`name` > '北')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`test_index`.`name` > '北')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`test_index`.`name` > '北')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`test_index`.`name` > '北')"
                }
              ] /* steps */
            } /* end-condition_processing */
          }

表依赖详情

	/* begin-table_dependencies */
          {
            "table_dependencies": [ -- 表依赖详情
              {
                "table": "`test_index`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* end-table_dependencies */
          }

预估表的访问成本

					/* begin-rows_estimation */
          {
            "rows_estimation": [  -- 预估表的访问成本
              {
                "table": "`test_index`",
                "range_analysis": {
                  "table_scan": { -- 全表扫描情况
                    "rows": 106, --  扫描行数
                    "cost": 12.95 -- 扫描成本
                  } /* table_scan */,
									
									/* begin-potential_range_indexes */
                  "potential_range_indexes": [ -- 查询可能使用的索引
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "union_name_score_addr", -- 辅助联合索引
                      "usable": true,
                      "key_parts": [
                        "name",
                        "score",
                        "addr",
                        "id"
                      ] /* key_parts */	
                    }
                  ] /* end-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": "union_name_score_addr",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
									
									/* begin-analyzing_range_alternatives */
                  "analyzing_range_alternatives": { -- 分析各个索引使用成本
                    "range_scan_alternatives": [
                      {
                        "index": "union_name_score_addr",
                        "ranges": [
                          "北 < name" -- 索引使用范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false, -- 使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,
                        "index_only": false, -- 是否使用覆盖索引
                        "in_memory": 1,
                        "rows": 93, -- 索引扫描行数
                        "cost": 32.81, -- 索引使用成本
                        "chosen": false, -- 是否选择该索引
                        "cause": "cost"
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* end-analyzing_range_alternatives */
                } /* range_analysis */
              }
            ] /* end-rows_estimation */
          }