分析sql执行效率

1、定位慢sql 
   （1）查看满查询日志确定已经执行完的慢查询
           开启慢查询日志、设置慢查询阀值、确定慢查询日志路径、确定慢查询日志的文件名
           slow_query_log 决定是否开启（set global slow_query_log = on）
           设置阀值（sel global long_query_time = 1）（建议设置一秒 要求高的设置0.1秒 测试环境为生产环境的一半 如生产环境一秒测试环境则设置0.5秒）
           确定路径（show global variables like "datadir"）
           确定文件名（show global variables like "slow_query_log_file"）
   （2）show processlist查看正在从执行的慢查询(show processlist\G)
2、使用explain分析慢查询
    

CREATE DATABASE muke;           /* 创建测试使用的database，名为muke */
    use muke;                       /* 使用muke这个database */
    drop table if exists t1;        /* 如果表t1存在则删除表t1 */

    CREATE TABLE `t1` (             /* 创建表t1 */
      `id` int(11) NOT NULL auto_increment,
      `a` int(11) DEFAULT NULL,
      `b` int(11) DEFAULT NULL,
      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
      PRIMARY KEY (`id`),
      KEY `idx_a` (`a`),
      KEY `idx_b` (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;    

    drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */
    delimiter ;;
    create procedure insert_t1()        /* 创建存储过程insert_t1 */
    begin
      declare i int;                    /* 声明变量i */
      set i=1;                          /* 设置i的初始值为1 */
      while(i<=1000)do                  /* 对满足i<=1000的值进行while循环 */
        insert into t1(a,b) values(i, i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
        set i=i+1;                      /* 将i加1 */
      end while;
    end;;
    delimiter ;                 /* 创建批量写入1000条数据到表t1的存储过程insert_t1 */
    call insert_t1();           /* 运行存储过程insert_t1 */

    drop table if exists t2;    /* 如果表t2存在则删除表t2 */
    create table t2 like t1;    /* 创建表t2，表结构与t1一致 */
    insert into t2 select * from t1;   /* 将表t1的数据导入到t2 */

    mysql> explain select * from t1 where b=100;

3、show profile 分析慢查询
    （1）查询是否支持profile（select @@have_profiling）
    （2）查询profile是否关闭（select @@profiling）
    （3）设置profile（set profiling = 1）
    （4）执行语句 select * from t1 where b = 1000
    （5）确定sql的query id （show profiles）
    （6）查询sql 执行详情（show profile for query 1）=>1是步骤5查询到的query id
4、trace 分析sql 优化器
    先开启trace 设置格式为json 再执行需要分析的sql 最后查看分析结果（该功能影响mysql性能 建议临时开启）
  

 mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
    /* optimizer_trace="enabled=on" 表示开启 trace；end_markers_in_json=on 表示 JSON 输出开启结束标记 */
    Query OK, 0 rows affected (0.00 sec)

    mysql> select * from t1 where a >900 and b > 910 order  by a;
    +------+------+------+
    | id   | a    | b    |
    +------+------+------+
    |    1 |    1 |    1 |
    |    2 |    2 |    2 |

    ......

    | 1000 | 1000 | 1000 |
    +------+------+------+
    1000 rows in set (0.00 sec)

    mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
    *************************** 1. row ***************************
    QUERY: select * from t1 where a >900 and b > 910 order  by a    --SQL语句
    TRACE: {
      "steps": [
        {
          "join_preparation": {                --SQL准备阶段
            "select#": 1,
            "steps": [
              {
                "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `update_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
              }
            ] /* steps */
          } /* join_preparation */
        },
        {
          "join_optimization": {            --SQL优化阶段
            "select#": 1,
            "steps": [
              {
                "condition_processing": {    --条件处理
                  "condition": "WHERE",
                  "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",        --原始条件
                  "steps": [
                    {
                      "transformation": "equality_propagation",
                      "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"         --等值传递转换
                    },
                    {
                      "transformation": "constant_propagation",
                      "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"       --常量传递转换
                    },
                    {
                      "transformation": "trivial_condition_removal",
                      "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"        --去除没有的条件后的结构
                    }
                  ] /* steps */
                } /* condition_processing */
              },
              {
                "substitute_generated_columns": {
                } /* substitute_generated_columns */   --替换虚拟生成列
              },
              {
                "table_dependencies": [        --表依赖详情
                  {
                    "table": "`t1`",
                    "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 */
              },
              {
                "rows_estimation": [    --预估表的访问成本
                  {
                    "table": "`t1`",
                    "range_analysis": {
                      "table_scan": {
                        "rows": 1000,       --扫描行数
                        "cost": 207.1       --成本
                      } /* table_scan */,
                      "potential_range_indexes": [    --分析可能使用的索引
                        {
                          "index": "PRIMARY",
                          "usable": false,       --为false，说明主键索引不可用
                          "cause": "not_applicable"
                        },
                        {
                          "index": "idx_a",      --可能使用索引idx_a
                          "usable": true,
                          "key_parts": [
                            "a",
                            "id"
                          ] /* key_parts */
                        },
                        {
                          "index": "idx_b",      --可能使用索引idx_b
                          "usable": true,
                          "key_parts": [
                            "b",
                            "id"
                          ] /* key_parts */
                        }
                      ] /* 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_a",    --使用索引idx_a的成本
                            "ranges": [
                              "900 < a"            --使用索引idx_a的范围
                            ] /* ranges */,
                            "index_dives_for_eq_ranges": true, --是否使用index dive（详细描述请看下方的知识扩展）
                            "rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
                            "using_mrr": false,      --是否使用mrr
                            "index_only": false,    --是否使用覆盖索引
                            "rows": 100,            --使用该索引获取的记录数
                            "cost": 121.01,         --使用该索引的成本
                            "chosen": true          --可能选择该索引
                          },
                          {
                            "index": "idx_b",       --使用索引idx_b的成本
                            "ranges": [
                              "910 < b"
                            ] /* ranges */,
                            "index_dives_for_eq_ranges": true,
                            "rowid_ordered": false,
                            "using_mrr": false,
                            "index_only": false,
                            "rows": 90,
                            "cost": 109.01,
                            "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_b",
                          "rows": 90,
                          "ranges": [
                            "910 < b"
                          ] /* ranges */
                        } /* range_access_plan */,
                        "rows_for_plan": 90,
                        "cost_for_plan": 109.01,
                        "chosen": true
                      } /* chosen_range_access_summary */
                    } /* range_analysis */
                  }
                ] /* rows_estimation */
              },
              {
                "considered_execution_plans": [  --考虑的执行计划
                  {
                    "plan_prefix": [
                    ] /* plan_prefix */,
                    "table": "`t1`",
                    "best_access_path": {          --最优的访问路径
                      "considered_access_paths": [ --决定的访问路径
                        {
                          "rows_to_scan": 90,      --扫描的行数
                          "access_type": "range",  --访问类型：为range
                          "range_details": {
                            "used_index": "idx_b"  --使用的索引为：idx_b
                          } /* range_details */,
                          "resulting_rows": 90,    --结果行数
                          "cost": 127.01,          --成本
                          "chosen": true,           --确定选择
                          "use_tmp_table": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 90,
                    "cost_for_plan": 127.01,
                    "sort_cost": 90,
                    "new_cost_for_plan": 217.01,
                    "chosen": true
                  }
                ] /* considered_execution_plans */
              },
              {
                "attaching_conditions_to_tables": {  --尝试添加一些其他的查询条件
                  "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
                  "attached_conditions_computation": [
                  ] /* attached_conditions_computation */,
                  "attached_conditions_summary": [
                    {
                      "table": "`t1`",
                      "attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
                    }
                  ] /* attached_conditions_summary */
                } /* attaching_conditions_to_tables */
              },
              {
                "clause_processing": {
                  "clause": "ORDER BY",
                  "original_clause": "`t1`.`a`",
                  "items": [
                    {
                      "item": "`t1`.`a`"
                    }
                  ] /* items */,
                  "resulting_clause_is_simple": true,
                  "resulting_clause": "`t1`.`a`"
                } /* clause_processing */
              },
              {
                "reconsidering_access_paths_for_index_ordering": {
                  "clause": "ORDER BY",
                  "index_order_summary": {
                    "table": "`t1`",
                    "index_provides_order": false,
                    "order_direction": "undefined",
                    "index": "idx_b",
                    "plan_changed": false
                  } /* index_order_summary */
                } /* reconsidering_access_paths_for_index_ordering */
              },
              {
                "refine_plan": [          --改进的执行计划
                  {
                    "table": "`t1`",
                    "pushed_index_condition": "(`t1`.`b` > 910)",
                    "table_condition_attached": "(`t1`.`a` > 900)"
                  }
                ] /* refine_plan */
              }
            ] /* steps */
          } /* join_optimization */
        },
        {
          "join_execution": {             --SQL执行阶段
            "select#": 1,
            "steps": [
              {
                "filesort_information": [
                  {
                    "direction": "asc",
                    "table": "`t1`",
                    "field": "a"
                  }
                ] /* filesort_information */,
                "filesort_priority_queue_optimization": {
                  "usable": false,             --未使用优先队列优化排序
                  "cause": "not applicable (no LIMIT)"     --未使用优先队列排序的原因是没有limit
                } /* filesort_priority_queue_optimization */,
                "filesort_execution": [
                ] /* filesort_execution */,
                "filesort_summary": {           --排序详情
                  "rows": 90,
                  "examined_rows": 90,          --参与排序的行数
                  "number_of_tmp_files": 0,     --排序过程中使用的临时文件数
                  "sort_buffer_size": 115056,
                  "sort_mode": "<sort_key, additional_fields>"   --排序模式（详解请看下方知识扩展）
                } /* filesort_summary */
              }
            ] /* steps */
          } /* join_execution */
        }
      ] /* steps */
    }
    MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0    --该字段表示分析过程丢弃的文本字节大小，本例为0，说明没丢弃任何文本
              INSUFFICIENT_PRIVILEGES: 0    --查看trace的权限是否不足，0表示有权限查看trace详情
    1 row in set (0.00 sec)
    ------------------------------------------------
    ------------------------------------------------


    mysql> set session optimizer_trace="enabled=off";
    /* 及时关闭trace */

    根据analyzing_range_alternatives 这项里头的const成本项可以看出选择idx_b