276_DBA_Json格式执行计划

Json格式执行计划

通过explain语句输出中缺少一个重要指标 成本， MySQL通过 explain format=json + SQL 打印json格式的执行计划

explain select * from s1 join s2 on s1.id = s2.id where s1.commen_field = "alexxxxx"

# s1 驱动表， s2 被驱动表

explain format=json select * from s1 join s2 on s1.id = s2.id where s1.commen_field = "alexxxxx"

{
  "query_block": {
    "select_id": 1, # 整个查询只有一个select 对应执行计划 ID :1
    "cost_info": {
      "query_cost": "13601.22"  # 整个查询的花费成本 13601.22 
    },
    "nested_loop": [  # 采用嵌套循环连接算法执行查询
      {
	  
	  # 以下是参与嵌套循环的各个表的执行信息 
        "table": {
          "table_name": "s1",   # 第一个出现的是驱动表 
          "access_type": "ALL",  # 全表扫描方式  对应explain 的type 
          "possible_keys": [
            "PRIMARY"
          ],
          "rows_examined_per_scan": 99385, # 查询一次s1表大致扫描99385行
          "rows_produced_per_join": 9938,  # 驱动表S1 的扇出预计是9938 意味着 select * from s1 where s1.commen_field = "alexxxxx" 预计扇出值（被驱动表要执行的次数）
          "filtered": "10.00",  # 对应explain filtered值 （condition filtering 百分比）
          "cost_info": {
            "read_cost": "9128.90", # I/O 成本 + 检测rows * (1 - fliter)条记录的CPU 成本
            "eval_cost": "993.85",  # 检测rows * flter 条记录的CPU成本 
            "prefix_cost": "10122.75", # 单次查询s1表总成本= read_cost + eval_cost
            "data_read_per_join": "17M" # 读取的数据量
          },
          "used_columns": [   # 查询中涉及的列 
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "commen_field"
          ],
          "attached_condition": "(`test`.`s1`.`commen_field` = 'alexxxxx')" # 针对S1表访问时,涉及的查询条件
        }
      },
      {
        "table": {
          "table_name": "s2",   # 被驱动表S2
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "4",  # 对应explain计划中的 key_lenth id 为int 4字节
          "ref": [
            "test.s1.id"      # 每次执行的ref 与s1.id等值匹配的对象（是一个外层查询的一个列）
          ],
          "rows_examined_per_scan": 1,  # 被驱动表多次查询，每次查询一行记录
          "rows_produced_per_join": 9938, # 被驱动表的扇出，由于后面没有join 对于被驱动表这个值意义不大
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2484.63",   # s2要多次访问，这里是累计成本
            "eval_cost": "993.85",    # s2要多次访问，这里是累计成本
            "prefix_cost": "13601.23",  #单次查询s1和多次查询 s2 表的总成本 
            "data_read_per_join": "20M" # 读取的数据量 
          },
          "used_columns": [   # 查询中涉及的列 
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "commen_field"
          ]
        }
      }
    ]
  }
}

show warnings \G;  查看执行计划的扩展信息

explain select * from s1 where key2 in (select key2 from s2 where key3 = "alex")

Level: Note   

Code: 1003   # 当code = 1003 时 message信息 是优化器重写 后的SQL语句，可见将子查询改成了 join 方式 

Message: /* select#1 */ select `alex`.`s1`.`id` AS `id`,`alex`.`s1`.`key1` AS `key1`,`alex`.`s1`.`key2` AS `key2`,`alex`.`s1`.`key3` AS `key3`,`alex`.`s1`.`key_part1` AS `key_part1`,`alex`.`s1`.`key_part2` AS `key_part2`,`alex`.`s1`.`key_part3` AS `key_part3`,`alex`.`s1`.`commen_field` AS `commen_field` from `alex`.`s2` join `alex`.`s1` where ((`alex`.`s1`.`key2` = `alex`.`s2`.`key2`) and (`alex`.`s2`.`key3` = 'alex'))