EXPLAIN四种输出格式&MySQL监控分析视图——sys schema

EXPLAIN四种输出格式

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

EXPLAIN四种输出格式

传统格式

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

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

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

  • JSON格式:在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON
EXPLAIN FORMAT=JSON SELECT ....
  • EXPLAIN的Column与JSON的对应关系:(来源于MySQL 5.7文档)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wWW3PVdv-1648199800627)(C:/Users/losser/AppData/Roaming/Typora/typora-user-images/image-20220324212250489.png)]

这样我们就可以得到一个json格式的执行计划,里面包含该计划花费的成本,比如这样:

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2  WHERE s1.common_field = 'a'\G

这种事JSON格式的

{
  "query_block": {
    "select_id": 1,
    # 花费的成本信息
    "cost_info": {
      "query_cost": "2102.20"
    },
    "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": {
        	# IO成本+检测的rows * (1 - filter)条记录的CPU的成本
            "read_cost": "989.50",
            "eval_cost": "98.95",
            "prefix_cost": "2102.20",
            "data_read_per_join": "1M"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ]
        }
      }
    ]
  }
}

  • 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": "914.80",
            "eval_cost": "98.95",
            "prefix_cost": "1013.75",
            "data_read_per_join": "1M"
          },

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

968.80 + 193.76 + 2034.60 = 3197.16
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=2102.20 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=1.00 rows=1)

1 row in set, 1 warning (0.00 sec)
可视化输出

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

<think>嗯,用户现在想了解如何监控MySQL的连接使用率和性能,看来他们之前已经询问过最大连接数的问题,现在想更深入地监控和管理数据库。用户引用了五篇资料,但核心需求很明确:需要实用的监控方法和工具推荐。从引用材料来看,用户可能是个数据库管理员或开发人员,正在寻找专业级的监控方案。引用1和3提到性能监控的重要性,引用2和4分别介绍了第三方工具和连接池监控思路,引用5则提供了索引监控的SQL语句。这些材料质量不错,但用户需要的是整合后的实操方案。在之前的回复中,我已经详细解释过max_connections的配置,现在需要延续这个专业深度。用户可能面临的实际场景是:数据库偶尔出现性能瓶颈,需要提前发现连接数不足或响应延迟的问题。他们真正的需求不仅是监控工具列表,更是如何建立完整的性能监控体系。从技术角度,这个问题可以拆解为三个层面:基础指标监控(如连接数、活动线程)、高级性能分析(如慢查询、锁竞争)和工具链搭建。特别要注意引用5提到的performance_schema,这是MySQL内置的强大工具,应该作为重点推荐。用户可能没意识到但同样重要的是:监控数据的解读和告警策略。比如Threads_connected接近max_connections时该如何处理,或者如何区分短时峰值和持续过载。这些实战经验需要整合进回答。关于工具推荐,考虑到不同用户的技术栈:中小企业可能更适合开源方案如Prometheus,而大企业可推荐ApplicationsManager这类商用方案(引用2提到它的深度监控能力)。连接池监控部分(引用4)需要单独说明,因为Java应用的连接池管理和数据库原生连接不是同一维度。需要特别强调时间序列数据的价值——比如用grafana展示连接数变化曲线,比单纯看数字直观得多。同时要提醒监控频率设置,过于频繁的采集可能影响数据库性能本身。</think> 监控MySQL连接使用率和性能需要结合**系统命令、内置工具、SQL查询和第三方工具**,以下是分步骤的实用方案: --- ### **一、核心监控指标** #### 1. **连接使用率** - **当前连接数**:`SHOW STATUS LIKE 'Threads_connected';` - **最大连接数**:`SHOW VARIABLES LIKE 'max_connections';` - **使用率计算公式**: ``` $$ \text{连接使用率} = \frac{\text{Threads\_connected}}{\text{max\_connections}} \times 100\% $$ ``` > 安全阈值:建议 ≤80%(超过需扩容或优化) #### 2. **连接状态分布** ```sql SHOW STATUS LIKE 'Threads_%'; -- 重点关注: -- Threads_running(活跃查询线程) -- Threads_created(历史创建连接数) -- Threads_cached(缓存中的线程) ``` #### 3. **连接来源分析** ```sql SELECT user, host, COUNT(*) FROM information_schema.processlist GROUP BY user, host; ``` --- ### **二、内置工具监控** #### 1. **`SHOW PROCESSLIST`** 实时查看连接状态和执行的SQL: ```sql SHOW FULL PROCESSLIST; -- 显示完整SQL(避免截断) ``` > 关键列:`Command`(如`Query`/`Sleep`)、`Time`(执行时长)、`State`(状态) #### 2. **Performance Schema** 启用后(需配置)可获取深度数据: - **连接池监控**(引用[4]思路): ```sql SELECT * FROM performance_schema.threads; ``` - **索引使用率**(引用[5]): ```sql SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage; ``` #### 3. **Sys Schema** 预置诊断视图MySQL 5.7+): ```sql -- 连接等待最多的主机 SELECT * FROM sys.host_summary_by_statement_latency; -- 慢SQL分析 SELECT * FROM sys.statement_analysis; ``` --- ### **三、第三方工具整合** #### 1. **Prometheus + Grafana** - **部署Exporter**:使用 `mysqld_exporter` 采集指标 - **关键Dashboard指标**: - 连接数时序曲线 - 活跃查询占比 - 每秒查询量(QPS) - 慢查询计数 #### 2. **Applications Manager**(引用[2]) - **深度监控能力**: - 自动预警连接池枯竭 - 事务锁竞争分析 - 复制延迟检测 #### 3. **Percona Monitoring & Management (PMM)** 开源方案提供: - **Query Analytics**:解析SQL执行计划 - **资源利用率关联**:CPU/内存与连接数的关联分析 --- ### **四、关键场景诊断** #### 1. **连接泄露** - **现象**:`Threads_created`持续增长,但`Threads_cached`不增加 - **定位**:检查长时间`Sleep`的连接: ```sql SELECT * FROM information_schema.processlist WHERE COMMAND='Sleep' AND TIME > 300; -- 5分钟以上空闲连接 ``` #### 2. **连接池过载** - **现象**:`Threads_running`持续接近`max_connections` - **优化**: - 调整`thread_cache_size` - 启用连接复用(如PHP的`pconnect`) - 检查应用侧连接池配置(引用[4]) --- ### **五、最佳实践建议** 1. **基线建立**:业务低峰期记录正常指标范围 2. **自动化告警**: - 连接使用率 >80% - `Threads_running` > 50% max_connections - 慢查询数突增 3. **定期优化**: - 每月分析索引使用率(引用[5]) - 清理不活跃账号(减少冗余连接) > 通过组合内置指标和可视化工具(如Grafana),可构建实时监控看板实现快速响应[^1][^2][^3]。 --- **相关问题**: 1. MySQL慢查询日志如何配置并分析性能瓶颈? 2. 如何通过`EXPLAIN`优化高延迟SQL语句? 3. 数据库连接池配置参数有哪些调优策略? 4. Prometheus监控MySQL时需关注哪些关键指标?
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值