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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,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数据库的性能。通过对查询语句的分析可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句!


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


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
存储 监控 Ubuntu
Linux系统之GoAccess实时Web日志分析工具的基本使用
【5月更文挑战第22天】Linux系统之GoAccess实时Web日志分析工具的基本使用
13 0
|
3天前
|
SQL 存储 关系型数据库
MySQL慢日志的介绍以及如何使用问题
【5月更文挑战第19天】MySQL慢日志的介绍以及如何使用问题
13 2
|
11天前
|
关系型数据库 MySQL 数据库
mysql数据库bin-log日志管理
mysql数据库bin-log日志管理
|
13天前
|
存储 监控 NoSQL
【MongoDB 专栏】MongoDB 的日志管理与分析
【5月更文挑战第11天】MongoDB日志管理与分析至关重要,包括系统日志和操作日志,用于监控、故障排查和性能优化。合理配置日志详细程度、存储位置和保留策略,使用日志分析工具提升效率,发现性能瓶颈和安全性问题。日志分析有助于优化查询、调整配置,确保数据安全,并可与其他监控系统集成。面对日志量增长的挑战,需采用新技术如分布式存储和数据压缩来保障存储和传输。随着技术发展,不断进化日志管理与分析能力,以支持MongoDB的稳定高效运行。
【MongoDB 专栏】MongoDB 的日志管理与分析
|
13天前
|
关系型数据库 MySQL 数据管理
MySQL通过 bin-log 恢复从备份点到灾难点之间数据
MySQL通过 bin-log 恢复从备份点到灾难点之间数据
198 0
|
13天前
|
DataWorks 关系型数据库 MySQL
DataWorks产品使用合集之在DataWorks中,如何通过PolarDB for MySQL来查看binlog日志
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
35 1
|
13天前
|
存储 监控 关系型数据库
PHP编写的电脑监控软件:用户登录日志记录与分析
使用PHP编写简单但功能强大的电脑监控软件,记录用户登录日志并进行分析。代码示例展示了如何获取并存储用户IP地址和登录时间到数据库,然后进行登录数据的分析,如计算登录频率和常见登录时间。此外,还介绍了如何通过定时任务自动将监控数据提交到网站,以便实时监控用户活动,提升系统安全性和稳定性。
82 0
|
13天前
|
SQL 监控 关系型数据库
【MySQL学习】MySQL的慢查询日志和错误日志
【MySQL学习】MySQL的慢查询日志和错误日志
|
13天前
|
数据可视化
R语言两阶段最小⼆乘法2SLS回归、工具变量法分析股息收益、股权溢价和surfaces曲面图可视化
R语言两阶段最小⼆乘法2SLS回归、工具变量法分析股息收益、股权溢价和surfaces曲面图可视化
|
11天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。