mysql 通过optimizer_trace查看sql优化过程

最新推荐文章于 2022-08-15 16:31:42 发布
最新推荐文章于 2022-08-15 16:31:42 发布
阅读量220 收藏
点赞数
分类专栏： mysql 文章标签： mysql mysql优化 索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cui544564050/article/details/116595975
版权

准备工作：

mysql 版本5.7.31

CREATE TABLE `collect` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) DEFAULT NULL,
  `vtype` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `collect_index` (`id`,`title`) USING BTREE,
  KEY `test_index` (`title`,`vtype`),
  KEY `fk` (`vtype`),
  CONSTRAINT `fk` FOREIGN KEY (`vtype`) REFERENCES `vtype_info` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1795476 DEFAULT CHARSET=utf8;

CREATE TABLE `vtype_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

初始化数据

INSERT INTO `test`.`vtype_info`(`id`, `name`) VALUES (1, 'q');
INSERT INTO `test`.`vtype_info`(`id`, `name`) VALUES (2, 'w');
INSERT INTO `test`.`vtype_info`(`id`, `name`) VALUES (3, 'e');
INSERT INTO `test`.`vtype_info`(`id`, `name`) VALUES (4, 'r');
INSERT INTO `test`.`vtype_info`(`id`, `name`) VALUES (5, 't');
INSERT INTO `test`.`vtype_info`(`id`, `name`) VALUES (6, 'y');
INSERT INTO `test`.`vtype_info`(`id`, `name`) VALUES (7, 'u');
INSERT INTO `test`.`vtype_info`(`id`, `name`) VALUES (8, 'i');
INSERT INTO `test`.`vtype_info`(`id`, `name`) VALUES (9, 'o');
INSERT INTO `test`.`vtype_info`(`id`, `name`) VALUES (10, 'p');
INSERT INTO `test`.`vtype_info`(`id`, `name`) VALUES (11, 'a');

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
  DECLARE n int DEFAULT 1;
  WHILE n < 1000000 DO
    insert into collect (title,vtype) value (CEILING(RAND()*10),CEILING(RAND()*10)); 
    set n = n + 1;
  END WHILE;

END

mysql开启optimizer_trace：

mysql -hlocalhost -uroot -p

mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on; # be readable
mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; # avoid small default
mysql> select title,vtype from collect c ,vtype_info v where c.vtype=v.id and c.vtype=1 and c.title='1' limit 10;
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

查看trace细节：

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `c`.`title` AS `title`,`c`.`vtype` AS `vtype` from `collect` `c` join `vtype_info` `v` where ((`c`.`vtype` = `v`.`id`) and (`c`.`vtype` = 1) and (`c`.`title` = '1')) limit 10"
          }
        ] /* steps */ 
      } /* join_preparation */
    },

准备工作 对查询分解标号 如果内查询会有 “select#”: 1、 “select#”: 2 … 这个标号后面会用到
下面是具体优化过程

    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`c`.`vtype` = `v`.`id`) and (`c`.`vtype` = 1) and (`c`.`title` = '1'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`c`.`title` = '1') and multiple equal(1, `c`.`vtype`, `v`.`id`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`c`.`title` = '1') and multiple equal(1, `c`.`vtype`, `v`.`id`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`c`.`title` = '1') and multiple equal(1, `c`.`vtype`, `v`.`id`))"
                }
              ] /* steps */
            } /* condition_processing */

where 条件优化：
注意前面索引

KEY `test_index` (`title`,`vtype`),

所以可以看出mysql 5.7能够根据索引字段的顺序调整查询条件顺序


         },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`collect` `c`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`vtype_info` `v`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`collect` `c`",
                "field": "title",
                "equals": "'1'",
                "null_rejecting": false
              },
              {
                "table": "`collect` `c`",
                "field": "vtype",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`collect` `c`",
                "field": "vtype",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`vtype_info` `v`",
                "field": "id",
                "equals": "1",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },

ref_optimizer_key_uses 外键索引判断
注意: null_rejecting表示判断外键字段是不是会因为包含null值而拒绝使用


          {
            "rows_estimation": [ //计算各种方式查询的效率
              {
                "table": "`collect` `c`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 998811,
                    "cost": 1.2e6
                  } /* table_scan 全表扫描 */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",  //主键
                      "usable": false, //不能使用
                      "cause": "not_applicable" //原因：不合适
                    },
                    {
                      "index": "collect_index",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "test_index",
                      "usable": true, //找到合适的索引
                      "key_parts": [
                        "title",
                        "vtype",
                        "id"
                      ] /* key_parts */
                    },

test_index是创建的覆盖索引
可以看到该索引覆盖的字段包含title，vtype，id 这也验证了联合索引中包含主键，如果从索引中取不到全部的查询字段会通过主键会表

                    {
                      "index": "fk",
                      "usable": true,
                      "key_parts": [
                        "vtype",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "best_covering_index_scan": { //最合适的索引扫描
                    "index": "test_index",
                    "cost": 219348,
                    "chosen": true
                  } /* best_covering_index_scan */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "test_index",
                        "ranges": [
                          "1 <= title <= 1 AND 1 <= vtype <= 1"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 18080,
                        "cost": 3971.5,
                        "chosen": true
                      },
                      {
                        "index": "fk",
                        "ranges": [
                          "1 <= vtype <= 1"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 201670,
                        "cost": 242005,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "intersecting_indexes": [
                        {
                          "index": "test_index",
                          "index_scan_cost": 355.49,
                          "cumulated_index_scan_cost": 355.49,
                          "disk_sweep_cost": 0,
                          "cumulated_total_cost": 355.49,
                          "usable": true,
                          "matching_rows_now": 18080,
                          "isect_covering_with_this_index": true,
                          "chosen": true
                        }
                      ] /* intersecting_indexes */,
                      "clustered_pk": {
                        "clustered_pk_added_to_intersect": false,
                        "cause": "no_clustered_pk_index"
                      } /* clustered_pk */,
                      "chosen": false,
                      "cause": "too_few_indexes_to_merge"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "test_index",
                      "rows": 18080,
                      "ranges": [
                        "1 <= title <= 1 AND 1 <= vtype <= 1"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 18080,
                    "cost_for_plan": 3971.5,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              },
              {
                "table": "`vtype_info` `v`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": false
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                  "`vtype_info` `v`"  //关联查询的主表
                ] /* plan_prefix */,
                "table": "`collect` `c`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "test_index",
                      "rows": 18080,
                      "cost": 3971.5,
                      "chosen": true
                    },
                    {
                      "access_type": "ref",
                      "index": "fk",
                      "rows": 201670,
                      "cost": 46202,
                      "chosen": false
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "test_index"
                      } /* range_details */,
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 18080,
                "cost_for_plan": 3971.5,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },

considered_execution_plans 是联表顺序优化
因为关联查询首先会根据条件筛选主表，然后再loop主表的数据查询出关联表的数据，如果是left join、right join 主表将是固定的，但是inner join mysql会选择出最优的主表，如果在使用inner join不想托管mysql优化这一过程可以使用STRAIGHT_JOIN

          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`c`.`vtype` = 1) and (`c`.`title` = '1'))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`collect` `c`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`collect` `c`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

参考 https://dev.mysql.com/doc/internals/en/tracing-example.html

cui544564050
关注
专栏目录
MySQL 调优 | OPTIMIZER_TRACE详解
ywl470812087的博客
12-30 734
OPTIMIZER_TRACE是MySQL 5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等），并将跟踪结果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中。optimizer_trace总开关，默认值：enabled=off,one_line=offenabled：是否开启optimizer_trace；on表示开启，off表示关闭。one_line：是否开启单行存储。on表示开启；
mysql optimize trace_MySQL 调优 | OPTIMIZER_TRACE详解
weixin_42490217的博客
01-19 777
TIPS本文基于MySQL 8.0编写，理论支持MySQL 5.6及更高版本。OPTIMIZER_TRACE是MySQL 5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策(比如访问表的方法、各种开销计算、各种转换等)，并将跟踪结果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中。此功能默认关闭，开启后，可分析如下语句：SELECTINSERTREPLACE...
MySQL 查看SQL语句执行阶段和进度信息
努力不掉队的DBA
10-15 4039
MySQL 查看SQL语句执行阶段和进度信息 测试MySQL版本：5.7.31 根据《MySQL性能优化金字塔法则》做的实验，好书推荐！ 我们在创建索引时，经常需要等待执行的SQL一段时间，期间可以通过此方法查看SQL的执行进度等信息。 首先要开启***收集执行阶段信息***的功能，与***统计汇总执行阶段信息***的功能 # 会话1 update performance_schema.setup_instruments set enabled='yes', timed='yes' where nam
MySQL 调优 | OPTIMIZER_TRACE 详解
ITMuch的专栏
07-22 955
点击上方IT牧场，选择置顶或者星标技术干货每日送达！TIPS本文基于MySQL 8.0编写，理论支持MySQL 5.6及更高版本。OPTIMIZER_TRACE是MySQL 5.6...
Mysql 使用 optimizer_trace 查看执行流程，分析、验证优化思路
weixin_34380781的博客
02-13 657
该博客是我在看了《MySQL实战45讲》之后的一次实践笔记。文章比较枯燥，如果你在这篇文章看到一些陌生的关键字，建议你也一定要去做实验，只有做实验且验证了各个数据的由来，才能真正弄懂。 背景 Mysql 版本 ：5.7业务需求：需要统最近一个月阅读量最大的10篇文章为了对比后面实验效果，我加了3个索引 CREATE TABLE `arti...
mysql OPTIMIZER_TRACE
最新发布
09-29
mysql OPTIMIZER_TRACE 是一个用于帮助我们分析和优化MySQL查询执行计划的工具。它提供了详细的跟踪信息，可以帮助我们理解MySQL选择某个执行计划的原因。OPTIMIZER_TRACE 主要分为三个部分：join_preparation（SQL...
手把手教你认识OPTIMIZER_TRACE
woqutechteam的博客
10-07 1452
前言 我们在日常维护数据库的时候，如果遇到慢语句查询的时候，我们一般会怎么做？执行EXPLAIN去查看它的执行计划？ 是的。我们经常会这么做，然后看到执行计划展示给我们的一些信息，告诉我们MySQL是如何执行语句的。 BUT，执行计划往往只给我们带来了最基础的分析信息，比如是否有使用索引，还有一些其他供我们分析的信息，比如使用了临时表、排序等等。我们能从这些信息里面找一些优化点，这样就足...
mysql optimizer_prune_level_MySQL实例自定义参数设置
weixin_42356811的博客
02-07 276
云数据库SQL Server实例不支持修改参数，MySQL实例支持修改参数，具体参数列表见下文MySQL参数列表。修改参数1.登录控制台->云数据库->管理云数据库实例。2. 找到需要设置参数的目标实例，单击管理3. 在云数据库详情页面最右侧点击参数设置4. 修改参数后，点击预览，预览修改的参数5. 点击保存，保存参数修改导入参数在参数设置页面，更多操作->导入参数2. 在导入参...
MySQL 5.7 跟踪优化器
cuantangnie3915的博客
07-08 236
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.12 M...
mysql5.7 trace_MySQL 5.7 跟踪优化器
weixin_33166692的博客
01-18 107
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.12 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rig...
mysql5.7bug trace与查询计划不符合
sunashe的个人随笔
04-30 1264
mysql5.7bug 执行计划与trace结果不符合暂且不说查询优化器的性能，最起码trace结果应该与执行计划是相同的吧。 查询计划：mysql&gt; explain select id,uuid,link_uuid,laundry_uuid,from_user,to_user,amount,principal,interest,sequence,pay_status,prod
MySQL·源码分析·null reject谓词
误入歧途的蜗牛
08-15 480
mysql/mariadb join 连接时，null-rejected 谓词处理; mysql/mariadb outer join 转换改写处理原则
MYSQL 字符集不同引起的join无法走索引
cuiruipan6325的博客
01-31 702
主要是分析《 MYSQL sql执行过程的一些跟踪分析(二.mysql优化器追踪分析) 》中案例的原因，以此记录下： 再仔细往上回顾。 在以上关联对sql进行转换时，可...
MySQL查询优化
一杯Mojito可以吗
03-05 159
背景 数据库性能优化 写在前面 原文链接为https://www.modb.pro/doc/21032 （杨明翰） link 怎么发现和定位慢SQL • 从当前查询中获取问题SQL • 从慢日志中获取问题SQL (常用方式) • sys schema相关视图中获取问题SQL • 从当前查询中获取问题SQL 查看MySQL服务器内部当前线程正在执行的操作 • 除非具有process权限，否则只能看到自己发起的线程信息 • Select * from information_Schema.processli
MySQL索引选择不正确并详细解析OPTIMIZER_TRACE格式
Mr_Melody的专栏
10-07 4933
一 表结构如下:  CREATE TABLE t_audit_operate_log (   Fid bigint(16) AUTO_INCREMENT,   Fcreate_time int(10) unsigned NOT NULL DEFAULT '0',   Fuser varchar(50) DEFAULT '',   Fip bigint(16) DEFAULT NULL
MySQL-SQL优化
m0_50180963的博客
02-06 1297
前言 在应用开发的早期，数据量少，开发人员开发功能时更重视功能上的实现，随着生产数据的增长，很多SQL语句开始暴露出性能问题，对生产的影响也越来越大，有时可能这些有问题的SQL就是整个系统性能的瓶颈。 SQL优化一般步骤 1、通过慢查日志等定位那些执行效率较低的SQL语句 2、explain 分析SQL的执行计划 需要重点关注type、rows、filtered、extra。 type由上至下，效率越来越高 ALL 全表扫描 index 索引全扫描 range 索引范围扫描，常用语&lt.
mysql optimizer_MySQL 5.7的Optimizer跟踪解析
weixin_33402252的博客
01-20 423
MySQL 5.7的Optimizer跟踪解析在Mysql 5.7以后引入了强大的optmizer跟踪,这样可以帮助我们在分析sql执行计划时了解mysql为什么会选择某种执行带来很大方便.对于分析和调整sql语句性能有很大帮助测试环境mysql> select version();+------------+| version() |+------------+| 5.7.10-log ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值