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

<think>我们正在讨论MySQL 8中是否可以通过系统视图查询慢SQL。根据之前的引用和用户的问题，我们需要关注MySQL 8中慢查询日志相关的系统表或视图。 在MySQL 8中，慢查询日志可以通过设置`slow_query_log`来开启，并且可以通过`performance_schema`中的表来查询慢查询信息。此外，MySQL 8还提供了`sys`模式，其中包含了一些视图可以方便地查看慢查询信息。 具体来说，我们可以使用以下方法： 1. **开启慢查询日志**：在MySQL配置文件中设置（或动态设置）： ```sql SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; -- 设置慢查询阈值（秒） ``` 2. **查询慢查询日志**：慢查询日志默认记录在文件中，但MySQL 8.0.14及以上版本支持将慢查询日志记录到`performance_schema`中的`events_statements_summary_by_digest`表（但注意，默认情况下慢查询日志是写入文件的）。 3. **使用`sys`模式视图**：MySQL提供了`sys`模式，其中包含了一些视图可以方便地查看慢查询信息，例如： - `sys.statements_with_errors_or_warnings`：包含有错误或警告的语句。 - `sys.statements_with_full_table_scans`：包含全表扫描的语句。 - `sys.statements_with_runtimes_in_95th_percentile`：运行时间在95百分位以上的语句（即最慢的5%）。 - `sys.slow_statements`：直接查看慢查询（需要先开启慢查询日志并设置`log_output='TABLE'`，这样慢查询会记录到`mysql.slow_log`表中）。 但是，默认情况下，慢查询日志是输出到文件的。如果希望将慢查询日志记录到表中，可以设置： ```sql SET GLOBAL log_output = 'TABLE'; -- 将慢查询日志输出到mysql.slow_log表 ``` 然后，我们就可以通过查询`mysql.slow_log`表来查看慢查询了： ```sql SELECT * FROM mysql.slow_log; ``` 另外，`sys`模式中的`schema_table_statistics_with_buffer`、`x$schema_table_statistics_with_buffer`等视图也可以提供一些关于表访问的统计信息，包括全表扫描等，这些信息有助于识别慢查询。 不过，需要注意的是，`sys`模式中的视图主要是基于`performance_schema`和`information_schema`的，它们并不直接等同于慢查询日志，而是提供了一些性能相关的统计。 因此，总结回答： **MySQL 8可以通过系统视图查询慢SQL，但需要配置慢查询日志输出到表（mysql.slow_log）或者使用sys模式下的相关视图。同时，performance_schema中的events_statements_history_long表也可以记录慢查询（需要配置）[^2][^5]。** 具体操作步骤： 1. 配置慢查询日志记录到表： ```sql SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 1; -- 设置慢查询阈值，单位秒 SET GLOBAL log_output = 'TABLE'; -- 输出到表 ``` 2. 查询慢查询日志表： ```sql SELECT * FROM mysql.slow_log; ``` 3. 或者使用sys模式中的视图（例如，查看最慢的语句）： ```sql SELECT * FROM sys.slow_statements; ``` 注意：`sys.slow_statements`视图实际上是从`performance_schema`的事件语句历史表中获取数据，并过滤出执行时间超过`long_query_time`的语句。但这种方式需要确保`performance_schema`已经启用，并且相关消费者已开启（默认可能未开启所有事件）。 另外，引用[2]提到可以使用`information_schema.PROCESSLIST`和`performance_schema.events_statements_current`等视图来获取正在执行的查询和历史查询的信息。特别是`performance_schema.events_statements_history_long`表，它可以记录每个线程最近执行的大量语句（具体数量由配置决定），通过这个表我们可以查询到执行时间较长的语句。 例如，查询执行时间超过1秒的语句： ```sql SELECT * FROM performance_schema.events_statements_history_long WHERE SQL_TEXT IS NOT NULL AND ELAPSED_TIME > 1000000000; -- 时间单位为皮秒（1秒=1e12皮秒，但这里注意单位，实际配置中可能需要调整） ``` 注意：`performance_schema`中时间的单位是皮秒（1秒=1e12皮秒），所以1秒就是1000000000000皮秒。但是，在MySQL中，`events_statements_history_long`表中`ELAPSED_TIME`的单位是皮秒，所以1秒=1,000,000,000,000皮秒。因此，查询条件可以写为`ELAPSED_TIME > 1000000000000`。 然而，使用`performance_schema`记录所有语句可能会对性能产生较大影响，因此通常只用于临时诊断。 综上所述，MySQL 8提供了多种方式来查询慢SQL，包括： - 慢查询日志表（mysql.slow_log） - sys模式下的视图（如sys.slow_statements） - performance_schema中的事件语句表 但需要注意配置和性能开销。 §§相关问题§§ 1. MySQL 8中如何配置慢查询日志输出到表？ 2. 使用sys模式查询慢SQL需要哪些前提条件？ 3. 如何优化MySQL的慢查询？ 4. performance_schema中记录语句历史的相关配置参数有哪些？ [^1]: [mysql查询视图数据慢_MySQL - 视图 - 超慢查询](https://example.com/ref1) [^2]: [mysql如何发现慢查询sql](https://example.com/ref2) [^5]: [SQL基础——Mysql的视图](https://example.com/ref5)</think>### MySQL 8 查询慢 SQL 的方法 在 MySQL 8 中，可以通过系统视图和性能模式表直接查询慢 SQL 语句，主要使用以下两种方法： #### 方法 1：通过 `performance_schema` 查询（无需额外配置） ```sql SELECT THREAD_ID, SQL_TEXT, TIMER_WAIT/1000000000000 AS exec_time_sec -- 将皮秒转换为秒 FROM performance_schema.events_statements_history_long WHERE SQL_TEXT IS NOT NULL AND TIMER_WAIT > 1 * 1000000000000 -- 筛选执行超过1秒的语句 ORDER BY TIMER_WAIT DESC LIMIT 10; ``` **关键说明**： 1. **数据来源**： - `events_statements_history_long` 表存储历史 SQL 语句的执行统计 - 默认记录最近语句（数量由 `performance_schema_events_statements_history_size` 控制） 2. **字段解释**： - `SQL_TEXT`：完整 SQL 语句 - `TIMER_WAIT`：执行耗时（皮秒） - `ROWS_EXAMINED`：扫描行数 - `ROWS_SENT`：返回行数 3. **优势**： - 无需开启慢查询日志 - 实时获取执行数据 - 可关联其他性能表（如 `threads` 表获取连接信息） #### 方法 2：通过慢查询日志表（需配置） ```sql -- 启用慢查询日志到表 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL log_output = 'TABLE'; -- 输出到mysql.slow_log表 -- 查询记录的慢SQL SELECT start_time, query_time, lock_time, rows_sent, rows_examined, sql_text FROM mysql.slow_log WHERE query_time > 2 -- 筛选超过2秒的查询 ORDER BY query_time DESC; ``` **配置要点**： 1. 修改 `my.cnf` 永久生效： ```ini [mysqld] slow_query_log = 1 long_query_time = 1 # 超过1秒视为慢查询 log_output = TABLE # 或 FILE 输出到文件 ``` 2. **慢日志表字段**： - `query_time`：实际执行时间 - `user_host`：发起查询的用户 - `rows_examined`：扫描行数（优化关键指标） #### 性能分析建议 1. **关键优化指标**： - `rows_examined / rows_sent > 1000` 👉 可能缺少索引 - `lock_time` 过高 👉 存在锁竞争 - 频繁出现 `filesort` 👉 需优化排序字段 2. **关联分析示例**： ```sql SELECT eshl.SQL_TEXT, format_pico_time(eshl.TIMER_WAIT) AS latency, eshl.ROWS_EXAMINED, eshl.ROWS_SENT, eshl.CREATED_TMP_TABLES -- 临时表数量 FROM performance_schema.events_statements_history_long eshl JOIN performance_schema.threads t ON eshl.THREAD_ID = t.THREAD_ID WHERE t.PROCESSLIST_USER <> 'SYSTEM' ORDER BY eshl.TIMER_WAIT DESC LIMIT 5; ``` #### 注意事项 1. **性能影响**： - `performance_schema` 默认启用，开销约 5-10%[^2] - 慢查询日志写入表时，高频写入可能影响性能 2. **视图优化**： 当视图查询变慢时（如引用[1][4]所述问题）： - 检查基础表索引 - 使用 `EXPLAIN` 分析视图执行计划 - 考虑物化视图替代方案（MySQL 8 不支持原生物化视图，可用临时表模拟） > **提示**：对于复杂视图导致的性能问题，可尝试使用 `ALTER VIEW ... SQL SECURITY INVOKER` 改变视图执行权限，或添加 `NO_MERGE` 优化器提示阻止视图合并[^3][^5]。