几个必须掌握的SQL优化技巧(四):使用Trace工具分析优化器执行计划

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 在应用的开发过程中,由于开发初期的数据量一般都比较小,所以开发过程中一般都比较注重功能上的实现,但是当完成了一个应用或者系统之后,随着生产数据量的急剧增长,那么之前的很多sql语句的写法就会显现出一定的性能问题,对生产的影响也会越来越大,这些不恰当的sql语句就会成为整个系统性能的瓶颈,为了追求系统的极致性能,必须要对它们进行优化。

1 前言


在应用的开发过程中,由于开发初期的数据量一般都比较小,所以开发过程中一般都比较注重功能上的实现,但是当完成了一个应用或者系统之后,随着生产数据量的急剧增长,那么之前的很多sql语句的写法就会显现出一定的性能问题,对生产的影响也会越来越大,这些不恰当的sql语句就会成为整个系统性能的瓶颈,为了追求系统的极致性能,必须要对它们进行优化。


2 正文


上一篇文章:几个必须掌握的SQL优化技巧(三):Explain分析执行计划介绍了如何使用explain命令对sql的执行计划进行分析,包括在Select语句执行过程中表如何连接和连接的顺序,从而后续对其进行优化。具体的命令格式如下


explain + select语句
复制代码


MySQL体系架构这篇文章介绍过mysql的体系架构,里面说到mysql体系中有一层叫做优化器--optimizer,MYSQL5.6开始提供了对SQL的跟踪trace,通过trace文件能够进一步了解为什么optimizer如何选择执行计划的,为什么会选择A计划,而不是B计划。


今天这篇文章将会介绍如何使用trace工具分析优化器的执行计划。


1、打开trace,设置格式为json格式,并设置trace最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整展示:


set optimier_trace="enabled=on",end_markers_in_JSON=on;
set optimier_trace_max_mem_size=1000000
复制代码


结果如下:

e47daac033b941cdaa77ee357d3c3263~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg

2、然后执行一条sql语句,比如:


select * from payment where id=1
复制代码


3、最后,在系统表information_schema.optimizer_trace中查询优化器的执行计划就可以知道mysql是如何执行mysql的:


select * from information_schema.optimizer_trace \G
复制代码


具体结果如下为json格式,具体步骤以及注释如下:


mysql> select *from information_schema.optimizer_trace \G
*************************** 1. row ***************************
                            //  具体的执行的sql语句  
                            QUERY: select * from payment where id=1
                            // 优化器跟踪的步骤信息
                            TRACE: {
  // 具体步骤
  "steps": [
    {
      "join_preparation": {  //预备工作
        "select#": 1,
        "steps": [
          {
          // 将 * 解析为具体字段
            "expanded_query": "/* select#1 */ select `payment`.`amount` AS `amount` from `payment` where (`payment`.`customer_id` = 1)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
     //进行优化
      "join_optimization": {            
        "select#": 1,
        "steps": [
          {
           //条件处理
            "condition_processing": {  
              "condition": "WHERE",
              "original_condition": "(`payment`.`customer_id` = 1)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "multiple equal(1, `payment`.`customer_id`)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "multiple equal(1, `payment`.`customer_id`)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(1, `payment`.`customer_id`)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {    //替换生成的列
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [           //表的依赖关系
              {
                "table": "`payment`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [     //使用键
              {
                "table": "`payment`",
                "field": "customer_id",
                "equals": "1",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [ //行判断
              {
                "table": "`payment`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 16125,
                    "cost": 3324.1
                  } /* table_scan */,              //扫描表
                  "potential_range_indexes": [     //潜在的范围索引
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_fk_staff_id",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_fk_customer_id",
                      "usable": true,
                      "key_parts": [
                        "customer_id",
                        "payment_id"
                      ] /* key_parts */
                    },
                    {
                      "index": "fk_payment_rental",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [        //设置范围条件
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {    //分析范围选项
                    "range_scan_alternatives": 
                      {
                        "index": "idx_fk_customer_id",
                        "ranges": [
                          "1 <= customer_id <= 1"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 32,
                        "cost": 39.41,
                        "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": "idx_fk_customer_id",
                      "rows": 32,
                      "ranges": [
                        "1 <= customer_id <= 1"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 32,
                    "cost_for_plan": 39.41,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [     //考虑执行计划
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`payment`",
                "best_access_path": {           //最佳访问路径
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_fk_customer_id",
                      "rows": 32,
                      "cost": 38.4,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_fk_customer_id"
                      } /* range_details */,
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,//行过滤百分比
                "rows_for_plan": 32,
                "cost_for_plan": 38.4,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {        //将条件附加到表上
              "original_condition": "(`payment`.`customer_id` = 1)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [       //附加条件概要
                {
                  "table": "`payment`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [   //精简计划
              {
                "table": "`payment`"
              }
            ] /* 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)
复制代码


3 总结


这篇文章主要介绍了如何使用trace工具跟踪mysql优化器的执行计划,根据执行语句结合优化器的执行计划,以及前面介绍的sql频率和mysql的执行计划等信息,有助于我们后面对select查询语句进行优化。


后面将会继续分享关于sql优化的其他方法、步骤。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
SQL 存储 缓存
四、SQL语句执行过程分析
四、SQL语句执行过程分析
22 0
|
7天前
|
SQL 关系型数据库 MySQL
10个SQL中常用的分析技巧
10个SQL中常用的分析技巧
|
24天前
|
SQL JSON 数据库
在线JSON转SQL工具
JSON文件中的数据或者JSON对象转换为SQL插入语句,方便用户将数据导入到数据库中。
79 2
|
26天前
|
SQL 算法 Oracle
在线sql格式化工具
让SQL代码整洁如新,在线SQL格式化工具助力高效编程
30 1
|
27天前
|
SQL 存储 关系型数据库
MySQL(终结篇二)- SQL 语句分析与优化
MySQL(终结篇二)- SQL 语句分析与优化
75 0
|
1月前
|
SQL
leetcode-SQL-550. 游戏玩法分析 IV
leetcode-SQL-550. 游戏玩法分析 IV
13 1
|
1月前
|
SQL
leetcode-SQL-1084. 销售分析III
leetcode-SQL-1084. 销售分析III
22 0
|
1月前
|
SQL
leetcode-SQL-1158. 市场分析 I
leetcode-SQL-1158. 市场分析 I
16 1
|
1月前
|
SQL
leetcode-SQL-511. 游戏玩法分析 I
leetcode-SQL-511. 游戏玩法分析 I
12 0
|
1月前
|
SQL 数据挖掘 数据处理
「SQL面试题库」 No_71 市场分析 II
「SQL面试题库」 No_71 市场分析 II