6.4.8 ref

当索引列进行等值查询时，与索引列匹配的对象信息。

① 比如只是一个常数或者是某个列，其 ref 是 const

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

② 当进行多表连接查询时，对被驱动表s2执行的查询引用了atguigudb1.s1.id字段进行等值查询

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

③ 当连接条件使用函数时，其 ref 就是 func

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

6.4.9 rows ☆

① 预估的需要读取的记录条目数，条目数越小越好。这是因为值越小，加载I/O的页数就越少~

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

6.4.10 filtered

经过搜索条件后过滤剩下的记录所占的百分比。百分比越高越好，比如同样 rows 是 40，如果 filter 是 100，则是从 40 条记录里进行查找，如果 filter 是 10，则是从 400 条记录里进行查找，相比较而言当然是前者的效率更高哦。

① 如果执行的是单表扫描，那么计算时需要估计除了对应搜索条件外的其他搜索条件满足的记录有多少条晕了就看看下面的例子

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';

结果是 10，表示有 347 条记录满足 key1 > ‘z’ 的条件，这 347 条记录的 10% 满足 common_field = ‘a’ 条件。

② 实际上，对于单表查询，这个字段没有太大的意义，我们更加关注连接查询时的 filtered 值，它决定了被驱动表要执行的次数。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE
s1.common_field = 'a';

结果如下。在标明驱动表 s1 提供给被驱动表的记录数是 9895 条，其中 989.5 条满足过滤条件s1.key1 = s2.key1，那么被驱动表需要执行 990 次查询。

filtered=(最终查询结果/rows列数据)*100%，越大表示过滤后的数据，越是最终结果。

相比较filtered越小，减少了数据再次过滤的性能

6.4.11 Extra ☆

顾名思义，Extra 列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一一介绍了，所以我们只挑比较重要的额外信息介绍给大家。

① No tables used

当查询语句的没有FROM子句时将会提示该额外信息

EXPLAIN SELECT 1;

② Impossible WHERE

当查询条件永远不可能满足，查不到数据时会出现该信息。

EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

③ Using where

当没有使用索引，普通的 where 查询时，会出现该信息

EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

使用索引查询，则默默使用索引，什么额外信息也没有。

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

索引加普通 where，那还是 using where

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';

④ No matching min/max row

当查询语句中有 MIN、MAX 等聚合函数，但是并没有符合 where 条件的搜索记录时，会提供额外信息 No matching min/max row（表中根本没有满足 where 条件的字句，找 min、max 没有意义）

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

⑤ Select tables optimized away

当查询语句中有 MIN、MAX 等聚合函数，有符合 where 条件的搜索记录时

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'oCUPss';

⑥ Using index

在使用覆盖索引的情况提示。所谓覆盖索引，就是索引中覆盖了需要查询的所有字段，不需要再使用聚簇索引进行回表查找。比如下面的例子，使用 key1 作为查找条件，该字段建立了索引，B+ 树可以查找到 key1 字段和主键，因此下面只查找 key1 字段就不用进行回表操作，这是非常棒的情况。

EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';

⑦ Using index condition

搜索列中虽然出现了索引列，但是不能够使用索引，这种情况是比较坑的~

比如下面的查询虽然出现了索引列作为查询条件，但是还是需要进行回表查找，回表操作是一个随机 I/O，比较耗时。

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

上面这种情况可以使用索引下推(可以通过配置项进行配置)，使我们使用 WHERE key1 > ‘z’ 得到的结果先进行模糊匹配 key1 LIKE ‘%a’，然后再去回表，就可以减少回表的次数了。

⑧ Using join buffer

在连接查询中，当被驱动表不能够有效利用索引实现提升速度，数据库就使用缓存来尽可能提升一些性能。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

⑨ Not exists

当我们使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外信息

EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

⑩ Using intersect(…) 、 Using union(…) 和 Using sort_union(…)

如果执行计划的Extra列出现了Using intersect(...)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的...表示需要进行索引合并的索引名称；

如果出现了Using union(...)提示，说明准备使用Union索引合并的方式执行查询；

如果出现了Using sort_union(...)提示，说明准备使用Sort-Union索引合并的方式执行查询

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

⑪ Zero limit

当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息

EXPLAIN SELECT * FROM s1 LIMIT 0;

⑫ Using filesort

很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为**文件排序**（英文名：filesort）。这种情况时比较悲壮的~

EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

⑬ Using temporary

在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示

EXPLAIN SELECT DISTINCT common_field FROM s1;
EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;

执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用临时表。比如：扫描指定的索引idx_key1即可

 EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;

6.4.12 小结

EXPLAIN不考虑各种Cache，只考虑SQL本身

EXPLAIN不能显示MySQL在执行查询时所作的优化工作

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况部分统计

信息是估算的，并非精确值

7. EXPLAIN 的进一步使用

7.1 EXPLAIN 四种输出格式

这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式：传统格式，JSON格式， TREE格式以及可视化输出。用户可以根据需要选择适用于自己的格式。

7.1.1 传统格式

传统格式简单明了，输出是一个表格形式，概要说明查询计划。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field ='a';

7.1.2 JSON 格式

第1种格式中介绍的EXPLAIN语句输出中缺少了一个衡量执行计划好坏的重要属性–成本。而JSON格式是四种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。

JSON格式：在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON

传统格式与json格式的各个字段存在如下表所示的对应关系(mysql5.7官方文档)。

案例如下：

EXPLAIN FORMAT=JSON SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

结果如下，可以看到 json 格式的信息量会更加丰富。尤其是成本信息，是用于衡量一个执行计划的好坏的重要指标

mysql> EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field ='a'\G;
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1360.07"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "s1",
          "access_type": "ALL",
          "possible_keys": [
            "idx_key1"
          ],
          "rows_examined_per_scan": 9895,
          "rows_produced_per_join": 989,
          "filtered": "10.00",
          "cost_info": {
            "read_cost": "914.80",
            "eval_cost": "98.95",
            "prefix_cost": "1013.75",
            "data_read_per_join": "1M"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ],
          "attached_condition": "((`atguigudb1`.`s1`.`common_field` = 'a') and (`atguigudb1`.`s1`.`key1` is not null))"
        }
      },
      {
        "table": {
          "table_name": "s2",
          "access_type": "eq_ref",
          "possible_keys": [
            "idx_key2"
          ],
          "key": "idx_key2",
          "used_key_parts": [
            "key2"
          ],
          "key_length": "5",
          "ref": [
            "atguigudb1.s1.key1"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 989,
          "filtered": "100.00",
          "index_condition": "(cast(`atguigudb1`.`s1`.`key1` as double) = cast(`atguigudb1`.`s2`.`key2` as double))",
          "cost_info": {
            "read_cost": "247.38",
            "eval_cost": "98.95",
            "prefix_cost": "1360.08",
            "data_read_per_join": "1M"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ]
        }
      }
    ]
  }
}
1 row in set, 2 warnings (0.00 sec)

我们使用#后面跟随注释的形式为大家解释了 EXPLAIN FORMAT=JSON 语句的输出内容，但是大家有疑问 cost_info里边的成本看着怪怪的，它们是怎么计算出来的？

先看 s1 表的 "cost_info"部分：

"cost_info": {
  "read_cost": "914.80",
    "eval_cost": "98.95",
    "prefix_cost": "1013.75",
    "data_read_per_join": "1M"
}

read_cost 是由下边这两部分组成的：

IO成本

检测 rows × (1 - filter) 条记录的 CPU 成本

💚 rows和filter都是我们前边介绍执行计划的输出列，在JSON格式的执行计划中，rows相当于rows_examined_per_scan，filtered名称不变

eval_cost是这样计算的：

检测 rows × filter 条记录的成本。

prefix_cost就是单独查询 s1 表的成本，也就是：read_cost + eval_cost

data_read_per_join 表示在此次查询中需要读取的数据量。

对于 s2 表的 “cost_info” 部分是这样的：

"cost_info": {
  "read_cost": "247.38",
    "eval_cost": "98.95",
    "prefix_cost": "1360.08",
    "data_read_per_join": "1M"
}

由于 s2 表是被驱动表，所以可能被读取多次，这里的 read_cost 和 eval_cost 是访问多次 s2 表后累加起来的值，大家主要关注里边儿的 prefix_cost 的值代表的是整个连接查询预计的成本，也就是单次查询 s1 表和多次查询 s2 表后的成本的和，也就是：

247.38 + 98.95 + 1013.75 = 1360.08

7.1.3 TREE 格式

TREE 格式是 8.0.16 版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。

mysql> EXPLAIN FORMAT=TREE SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field ='a'\G;
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=1360.08 rows=990)
    -> Filter: ((s1.common_field = 'a') and (s1.key1 is not null))  (cost=1013.75 rows=990)
        -> Table scan on s1  (cost=1013.75 rows=9895)
    -> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double))  (cost=0.25 rows=1)
1 row in set, 1 warning (0.00 sec)

7.1.4 可视化输出

可视化输出，可以通过 MySQL Workbench 可视化查看 MySQL 的执行计划。通过点击 Workbench 的放大镜图标，即可生成可视化的查询计划

上图按从左到右的连接顺序显示表。红色框表示全表扫描，而绿色框表示使用索引查找对于每个表，显示使用的索引。还要注意的是，每个表格的框上方是每个表访问所发现的行数的估计值以及访问该表的成本

7.2 SHOW WARNINGS 的使用

可以显示数据库真正执行的 SQL ，因为有时候MySQL执行引擎会对我们的SQL进行优化~

① 先使用 Explain，我们写的 sql 按道理是使用 s1 作为驱动表，s2作为被驱动表

EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;*

但是执行结果把 s2 作为了驱动表，s1 作为了被驱动表

紧接着使用SHOW WARNINGS ，原来执行引擎将 LEFT JOIN优化成了 INNER JOIN

mysql> SHOW WARNINGS\G;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `atguigudb1`.`s1`.`key1` AS `key1`,`atguigudb1`.`s2`.`key1` AS `key1` 
from `atguigudb1`.`s1` 
join `atguigudb1`.`s2` 
where ((`atguigudb1`.`s1`.`key1` = `atguigudb1`.`s2`.`key1`) 
and (`atguigudb1`.`s2`.`common_field` is not null))
1 row in set (0.00 sec)

上面 message 中显示的是数据库优化、重写后真正执行的查询语句。果然它帮我们做了优化

② 再举一个例子：下面是一个子查询SQL，应该对应着两个不同的id~

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

但是真正执行后，对应着竟然是相同的id

我们使用SHOW WARNINGS\G;进行分析，发现执行引擎将其优化成了 多表连接查询的方式

mysql> SHOW WARNINGS\G;
*************************** 1. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use ref access on index 'idx_key1' due to type or collation conversion on field 'key1'
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `atguigudb1`.`s1`.`id` AS `id`,`atguigudb1`.`s1`.`key1` AS `key1`,`atguigudb1`.`s1`.`key2` AS `key2`,`atguigudb1`.`s1`.`key3` AS `key3`,`atguigudb1`.`s1`.`key_part1` AS `key_part1`,`atguigudb1`.`s1`.`key_part2` AS `key_part2`,`atguigudb1`.`s1`.`key_part3` AS `key_part3`,`atguigudb1`.`s1`.`common_field` AS `common_field` 
from `atguigudb1`.`s2` 
join `atguigudb1`.`s1` 
where ((`atguigudb1`.`s2`.`common_field` = 'a') 
and (cast(`atguigudb1`.`s1`.`key1` as double) = cast(`atguigudb1`.`s2`.`key2` as double)))
2 rows in set (0.00 sec)

8. 分析优化器执行计划：trace

OPTIMIZE_TRACE 是 mysql 5.6 中引入的一个跟踪工具，它可以跟踪优化器做出的各种决策，比如访问表的方法，各种开销计算，各种转换，结果会被记录到 information_schema.optimizer_trace中。

此功能默认关闭。开启trace，并设置格式为JSON，同时设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。命令如下：

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

开启后，可分析如下语句：

SELECT
INSERT
REPLACE
UPDATE
DELETE
EXPLAIN
SET
DECLARE
CASE
IF
RETURN
CALL

测试：执行如下 SQL 语句

select * from student where id < 10;

最后，查询 information_schema.optimizer_trace 就可以知道 MySQL 是如何执行 SQL 的

select * from information_schema.optimizer_trace\G;

结果如下

*************************** 1. row ***************************
 //第1部分：查询语句
 QUERY: select * from student where id < 10
 //第2部分：QUERY字段对应语句的跟踪信息
 TRACE: {
 "steps": [
 {
   "join_preparation": {  //预备工作
    "select#": 1,
    "steps": [
    {
      "expanded_query": "/* select#1 */ select `student`.`id` AS
`id`,`student`.`stuno` AS `stuno`,`student`.`name` AS `name`,`student`.`age` AS
`age`,`student`.`classId` AS `classId` from `student` where (`student`.`id` < 10)"
    }
   ] /* steps */
  } /* join_preparation */
 },
 {
   "join_optimization": {  //进行优化
    "select#": 1,
    "steps": [
    {
      "condition_processing": {  //条件处理
       "condition": "WHERE",
       "original_condition": "(`student`.`id` < 10)",
       "steps": [
       {
         "transformation": "equality_propagation",
         "resulting_condition": "(`student`.`id` < 10)"
       },
       {
         "transformation": "constant_propagation",
         "resulting_condition": "(`student`.`id` < 10)"
       },
       {
         "transformation": "trivial_condition_removal",
         "resulting_condition": "(`student`.`id` < 10)"
       }
] /* steps */
     } /* condition_processing */
    },
    {
      "substitute_generated_columns": {  //替换生成的列
     } /* substitute_generated_columns */
    },
    {
      "table_dependencies": [   //表的依赖关系
      {
        "table": "`student`",
        "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": "`student`",
        "range_analysis": {
         "table_scan": {
          "rows": 3973767,
          "cost": 408558
        } /* table_scan */,   //扫描表
         "potential_range_indexes": [   //潜在的范围索引
         {
           "index": "PRIMARY",
           "usable": true,
           "key_parts": [
            "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 */,
         "skip_scan_range": {
          "potential_skip_scan_indexes": [
          {
            "index": "PRIMARY",
            "usable": false,
            "cause": "query_references_nonkey_column"
          }
         ] /* potential_skip_scan_indexes */
        } /* skip_scan_range */,
         "analyzing_range_alternatives": {  //分析范围选项
          "range_scan_alternatives": [
          {
"index": "PRIMARY",
            "ranges": [
             "id < 10"
           ] /* ranges */,
            "index_dives_for_eq_ranges": true,
            "rowid_ordered": true,
            "using_mrr": false,
            "index_only": false,
            "rows": 9,
            "cost": 1.91986,
            "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": "PRIMARY",
           "rows": 9,
           "ranges": [
            "id < 10"
          ] /* ranges */
         } /* range_access_plan */,
          "rows_for_plan": 9,
          "cost_for_plan": 1.91986,
          "chosen": true
        } /* chosen_range_access_summary */
       } /* range_analysis */
      }
     ] /* rows_estimation */
    },
    {
      "considered_execution_plans": [  //考虑执行计划
      {
        "plan_prefix": [
       ] /* plan_prefix */,
        "table": "`student`",
        "best_access_path": {  //最佳访问路径
         "considered_access_paths": [
         {
           "rows_to_scan": 9,
           "access_type": "range",
           "range_details": {
            "used_index": "PRIMARY"
          } /* range_details */,
           "resulting_rows": 9,
           "cost": 2.81986,
           "chosen": true
         }
        ] /* considered_access_paths */
       } /* best_access_path */,
        "condition_filtering_pct": 100,  //行过滤百分比
        "rows_for_plan": 9,
        "cost_for_plan": 2.81986,
        "chosen": true
      }
     ] /* considered_execution_plans */
    },
    {
      "attaching_conditions_to_tables": {  //将条件附加到表上
       "original_condition": "(`student`.`id` < 10)",
       "attached_conditions_computation": [
      ] /* attached_conditions_computation */,
       "attached_conditions_summary": [  //附加条件概要
       {
         "table": "`student`",
         "attached": "(`student`.`id` < 10)"
       }
      ] /* attached_conditions_summary */
     } /* attaching_conditions_to_tables */
    },
    {
      "finalizing_table_conditions": [
      {
        "table": "`student`",
        "original_table_condition": "(`student`.`id` < 10)",
        "final_table_condition  ": "(`student`.`id` < 10)"
      }
     ] /* finalizing_table_conditions */
    },
    {
      "refine_plan": [  //精简计划
      {
        "table": "`student`"
      }
     ] /* refine_plan */
    }
   ] /* steps */
  } /* join_optimization */
 },
 {
   "join_execution": {   //执行
    "select#": 1,
    "steps": [
   ] /* steps */
  } /* join_execution */
 }
] /* steps */
}
//第3部分：跟踪信息过长时，被截断的跟踪信息的字节数。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0  //丢失的超出最大容量的字节
//第4部分：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一般在
调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。
INSUFFICIENT_PRIVILEGES: 0  //缺失权限
1 row in set (0.00 sec)

9. MySQL 监控分析视图 sys schema

9.1 Sys schema视图摘要

主机相关：以 host_summary 开头，主要汇总了 IO 延迟的信息。

Innodb 相关：以 innodb 开头，汇总了 innodb buffer 信息和事务等待 innodb 锁的信息。

I/O相关：以 IO 开头，汇总了等待 I/O、I/O 使用量情况。

内存使用情况：以 memory 开头，从主机、线程、事件等角度展示内存的使用情况

连接与会话信息：processlist 和 session 相关视图，总结了会话相关信息。

表相关：以 schema_table 开头的视图，展示了表的统计信息。

索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。

语句相关：以 statement 开头，包含执行全表扫描、使用临时表、排序等的语句信息。

用户相关：以 user 开头的视图，统计了用户使用的文件 I/O、执行语句统计信息。

等待事件相关信息：以 wait 开头，展示等待事件的延迟情况。

9.2 Sys schema视图使用场景

1. 索引情况

#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname';

举例：比如我们查看下数据的的冗余索引

select * from sys.schema_redundant_indexes;

我们任意选择一条，比如最后一条，然后查看下student_info的索引情况，看看是否idx_cre_time冗余了

可以看到 idx_cre_time和idx_cre_time_sid两个索引中都有 create_time。而且联合索引性能要高于单列索引，所以idx_cre_time 完全可以删掉~

2. 表相关

# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查询占用bufferpool较多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';

例如：查询表的访问量

# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys

3. 语句相关

#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;

4. IO相关

#查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read  limit 10;

5. InnoDB相关

#行锁阻塞情况
select * from sys.innodb_lock_waits;

风险提示：

通过sys库去查询时，MySQL会消耗大量资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。建议生产上不要频繁的去查询sys或者performance_ schema、 information_ schema来完成监控、巡检等工作。

10. 小结

查询时数据库中最频繁的操作，提高查询速度可以有效地提高MySQL数据库的性能。通过对查询语句的分析可以了解查询语句的执行情况，找出查询语句执行的瓶颈，从而优化查询语句！

文章知识点与官方知识档案匹配，可进一步学习相关知识

《MySQL高级篇》七、性能分析工具的使用（慢查询日志 | EXPLAIN | SHOW PROFILING | 视图分析）（四）

6.4.8 ref

6.4.9 rows ☆

6.4.10 filtered

6.4.11 Extra ☆

6.4.12 小结

7. EXPLAIN 的进一步使用

7.1 EXPLAIN 四种输出格式

7.1.1 传统格式

7.1.2 JSON 格式

7.1.3 TREE 格式

7.1.4 可视化输出

7.2 SHOW WARNINGS 的使用

8. 分析优化器执行计划：trace

9. MySQL 监控分析视图 sys schema

9.1 Sys schema视图摘要

9.2 Sys schema视图使用场景

10. 小结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

《MySQL高级篇》七、性能分析工具的使用（慢查询日志 | EXPLAIN | SHOW PROFILING | 视图分析 ）（四）

6.4.8 ref

6.4.9 rows ☆

6.4.10 filtered

6.4.11 Extra ☆

6.4.12 小结

7. EXPLAIN 的进一步使用

7.1 EXPLAIN 四种输出格式

7.1.1 传统格式

7.1.2 JSON 格式

7.1.3 TREE 格式

7.1.4 可视化输出

7.2 SHOW WARNINGS 的使用

8. 分析优化器执行计划：trace

9. MySQL 监控分析视图 sys schema

9.1 Sys schema视图摘要

9.2 Sys schema视图使用场景

10. 小结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

《MySQL高级篇》七、性能分析工具的使用（慢查询日志 | EXPLAIN | SHOW PROFILING | 视图分析）（四）