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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: 《MySQL高级篇》七、性能分析工具的使用(慢查询日志 | EXPLAIN | SHOW PROFILING | 视图分析 )

6.4.8 ref

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

① 比如只是一个常数或者是某个列,其 refconst

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

d39b2803e548ded871be1d11b8c39e54.png

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

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

943b870f90f806ae36c59dfbc58153f7.png

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

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


b86e40864ae71873b48563dc79324d44.png

6.4.9 rows ☆

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

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

8e6c3310d2274c233a39b5cbcff0faea.png


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’ 条件。

87715dbb91a61e45c4e28e762fb23c05.png

② 实际上,对于单表查询,这个字段没有太大的意义,我们更加关注连接查询时的 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 次查询。


4c46aef74b172674355b6682a8ec664b.png


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

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


6.4.11 Extra ☆


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


① No tables used


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


EXPLAIN SELECT 1;

d5e0a6b30d7af406764fb914d1ce28de.png


② Impossible WHERE


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


EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

a0b1e61fa25cf7aafe60f707a0e4b568.png


③ Using where


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

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

89341460345dfded0bd56d76365f9522.png


  • 使用索引查询,则默默使用索引,什么额外信息也没有。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';


8ae606fdcaeaa0e168ae0bd841b5dc99.png


  • 索引加普通 where,那还是 using where
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';


7ea6cd7f6ade1d2bcf622c216326fe27.png


④ 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';

967c60fcf417121c34c01c8a5a1d7d91.png


⑤ Select tables optimized away


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


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

624ba7e7e0e70765204a8d1f262bc214.png


⑥ Using index


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


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

1f4f3e7866d4628c08f4facb306bf025.png


⑦ Using index condition


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


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


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

4710de444b1372bb37080b1f87a4e891.png


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


⑧ Using join buffer


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


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

4d1f084f089f6b1e0ba42a5acba5b64d.png


⑨ 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;

bd734aba5d3e7dbe2330c82a10fe760e.png


⑩ 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';

7aa84d8dec10c6ce420f87f81aca9b94.png


⑪ Zero limit


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


EXPLAIN SELECT * FROM s1 LIMIT 0;

d1da91c0cd4c8df7a3de7800f5b37f2f.png


⑫ Using filesort


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


EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

7613ab9833c481e0b2f6ab4d18a7d08b.png


⑬ 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;

acf6bb063b8573f2fc4d5186dc156486.png


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


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

47426e2aea58b9c411ab07fc8b3bb670.png


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';

22e27a8f8b50fa1141052c442d7b1804.png


7.1.2 JSON 格式


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


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


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

ba5e3ff12f7639a5addaee7e198623f5.png

案例如下:

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 的放大镜图标,即可生成可视化的查询计划


a11259fdedf44db1c888723e4afe304c.png

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


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 作为了被驱动表

219dfb90ff566a5e249a75589ce21a31.png

紧接着使用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


c04be4cc5f90ec99d85736c11296772d.png

我们使用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

9d406060efdc6f72c8348b422bedd57f.png

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;


c3555f95e86cd2dc4358a0b8f3343d9d.png

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


a18799a495c9a257fd847443cd082932.png


可以 看到 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


e6b93efd8e8ea0085d5117f268e231b7.png


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数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
canal 消息中间件 关系型数据库
Canal作为一款高效、可靠的数据同步工具,凭借其基于MySQL binlog的增量同步机制,在数据同步领域展现了强大的应用价值
【9月更文挑战第1天】Canal作为一款高效、可靠的数据同步工具,凭借其基于MySQL binlog的增量同步机制,在数据同步领域展现了强大的应用价值
645 4
|
1月前
|
SQL 分布式计算 关系型数据库
Hadoop-21 Sqoop 数据迁移工具 简介与环境配置 云服务器 ETL工具 MySQL与Hive数据互相迁移 导入导出
Hadoop-21 Sqoop 数据迁移工具 简介与环境配置 云服务器 ETL工具 MySQL与Hive数据互相迁移 导入导出
52 3
|
2月前
|
SQL 缓存 关系型数据库
MySQL高级篇——性能分析工具
MySQL的慢查询日志,用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long-query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为 10,意思是运行10秒以上(不含10秒)的语句,认为是超出了我们的最大忍耐时间值。它的主要作用是,帮助我们发现那些执行时间特别长的 SOL 查询,并且有针对性地进行优化,从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候,检查一下慢查询日志,找到那些慢查询,对解决问题很有帮助。
MySQL高级篇——性能分析工具
|
2月前
|
安全 关系型数据库 MySQL
Navicat工具设置MySQL权限的操作指南
通过上述步骤,您可以使用Navicat有效地为MySQL数据库设置和管理用户权限,确保数据库的安全性和高效管理。这个过程简化了数据库权限管理,使其既直观又易于操作。
352 4
|
3月前
|
SQL 关系型数据库 MySQL
【MySQL 慢查询秘籍】慢SQL无处遁形!实战指南:一步步教你揪出数据库性能杀手!
【8月更文挑战第24天】本文以教程形式深入探讨了MySQL慢SQL查询的分析与优化方法。首先介绍了如何配置MySQL以记录执行时间过长的SQL语句。接着,利用内置工具`mysqlslowlog`及第三方工具`pt-query-digest`对慢查询日志进行了详细分析。通过一个具体示例展示了可能导致性能瓶颈的查询,并提出了相应的优化策略,包括添加索引、缩小查询范围、使用`EXPLAIN`分析执行计划等。掌握这些技巧对于提升MySQL数据库性能具有重要意义。
266 1
|
3月前
|
SQL 关系型数据库 MySQL
在Linux中,mysql 数据备份工具有哪些?
在Linux中,mysql 数据备份工具有哪些?
|
3月前
|
SQL 存储 关系型数据库
MySQL备份:mydumper 备份恢复工具生产实战
MySQL备份:mydumper 备份恢复工具生产实战
|
3月前
|
前端开发 C# 设计模式
“深度剖析WPF开发中的设计模式应用:以MVVM为核心,手把手教你重构代码结构,实现软件工程的最佳实践与高效协作”
【8月更文挑战第31天】设计模式是在软件工程中解决常见问题的成熟方案。在WPF开发中,合理应用如MVC、MVVM及工厂模式等能显著提升代码质量和可维护性。本文通过具体案例,详细解析了这些模式的实际应用,特别是MVVM模式如何通过分离UI逻辑与业务逻辑,实现视图与模型的松耦合,从而优化代码结构并提高开发效率。通过示例代码展示了从模型定义、视图模型管理到视图展示的全过程,帮助读者更好地理解并应用这些模式。
96 0
|
1月前
|
人工智能 自然语言处理 关系型数据库
阿里云云原生数据仓库 AnalyticDB PostgreSQL 版已完成和开源LLMOps平台Dify官方集成
近日,阿里云云原生数据仓库 AnalyticDB PostgreSQL 版已完成和开源LLMOps平台Dify官方集成。
|
1月前
|
人工智能 分布式计算 数据管理
阿里云位居 IDC MarketScape 中国实时湖仓评估领导者类别
国际数据公司( IDC )首次发布了《IDC MarketScape: 中国实时湖仓市场 2024 年厂商评估》,阿里云在首次报告发布即位居领导者类别。