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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
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数据库的性能。通过对查询语句的分析可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句!


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


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
25天前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1599 14
|
11天前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则,包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限,后者又优先于表级别权限。在权限冲突时,更严格的权限将被优先执行,确保数据库的安全性与资源合理分配。
|
28天前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
63 4
|
27天前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库,实现自然语言查询功能,包括环境搭建和配置流程。
135 0
|
2月前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
234 2
|
2月前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施,可以有效地维护MySQL主从复制环境中数据的一致性和完整性,确保数据库系统的稳定性和可靠性。
94 6
|
29天前
|
SQL 关系型数据库 MySQL
MySQL EXPLAIN该如何分析?
本文将详细介绍MySQL中`EXPLAIN`关键字的工作原理及结果字段解析,帮助优化查询性能。`EXPLAIN`可显示查询SQL的执行计划,其结果包括`id`、`select_type`、`table`等字段。通过具体示例和优化建议,帮助你理解和应用`EXPLAIN`,提升数据库查询效率。
57 0
|
19天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
54 3
Mysql(4)—数据库索引
|
4天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
29 2
|
7天前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
32 4

热门文章

最新文章

推荐镜像

更多