sys Schema

导学

Performance Schema 的数据非常底层，要找出"哪些 SQL 最慢""哪些表最热门""哪些索引没用到"，需要写复杂的聚合 SQL。sys Schema 是 MySQL 5.7 内置的一套视图和存储过程，它把 Performance Schema 和 information_schema 的原始数据加工成人类可读的性能诊断报告。一条简单的 SELECT * FROM sys.session 就能看到当前所有连接的详细状态。

定义

sys Schema：MySQL 5.7 内置的数据库（默认安装），包含基于 Performance Schema 和 information_schema 构建的视图、存储过程和函数。它将底层性能数据汇总为直观的诊断报告，大幅降低性能分析门槛。

核心视图分类

SQL 示例

场景一：查看当前连接（替代 SHOW PROCESSLIST）

执行语句：

-- 查看当前所有连接（比 SHOW PROCESSLIST 信息更丰富）
SELECT 
    thd_id,
    conn_id,
    user,
    db,
    command,
    state,
    time,
    current_statement,
    execution_engine,
    rows_examined,
    rows_sent,
    created_tmp_tables,
    created_tmp_disk_tables
FROM sys.session
WHERE conn_id IS NOT NULL
ORDER BY time DESC;

操作后结果（示例）：

结果解读：

• sys.session 整合了 performance_schema.threads 和 performance_schema.events_statements_current 的数据
• 比 SHOW PROCESSLIST 多了 rows_examined（扫描行数）、rows_sent（返回行数）、临时表统计等关键指标
• time 是当前状态持续时间（秒），值很大且 state = 'Sending data' 或 'Locked' 需要关注
• current_statement = NULL 表示连接空闲（command = 'Sleep'）

场景二：找出最慢的 SQL 语句

执行语句：

-- 查看执行时间最长的 SQL（全表扫描、临时表、排序等一目了然）
SELECT 
    query,
    db,
    exec_count,
    total_latency,
    avg_latency,
    rows_sent_avg,
    rows_examined_avg,
    full_scans,
    tmp_tables,
    tmp_disk_tables,
    last_seen
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

操作后结果（示例）：

结果解读：

• sys.statement_analysis 汇总了 events_statements_summary_by_digest 的数据
• total_latency 是累计执行时间，avg_latency 是平均每次执行时间
• rows_examined_avg 平均每次扫描行数，远大于 rows_sent_avg 说明需要优化索引
• full_scans 是全表扫描次数，值高说明没有用到索引或索引选择不当
• tmp_tables / tmp_disk_tables 是创建临时表的次数，磁盘临时表尤其需要关注

场景三：找出最热门的表

执行语句：

-- 查看各表的 I/O 统计（读、写、延迟）
SELECT 
    table_schema,
    table_name,
    rows_fetched,
    fetch_latency,
    rows_inserted,
    insert_latency,
    rows_updated,
    update_latency,
    rows_deleted,
    delete_latency,
    io_read_requests,
    io_write_requests
FROM sys.schema_table_statistics
ORDER BY fetch_latency DESC
LIMIT 10;

操作后结果（示例）：

结果解读：

• sys.schema_table_statistics 按表汇总了读写次数和 I/O 延迟
• fetch_latency 是读取累计耗时，insert_latency / update_latency / delete_latency 是写入耗时
• io_read_requests / io_write_requests 是底层 I/O 请求次数
• 结合 rows_fetched 和 fetch_latency 可以判断：是访问量大（正常）还是单次读取慢（需要优化）

场景四：找出从未使用的索引（清理冗余索引）

执行语句：

-- 查看哪些索引从未被使用（清理候选）
SELECT 
    object_schema,
    object_name,
    index_name
FROM sys.schema_unused_indexes
ORDER BY object_schema, object_name;

操作后结果（示例）：

结果解读：

• sys.schema_unused_indexes 对比 information_schema.STATISTICS（有哪些索引）和 performance_schema.table_io_waits_summary_by_index_usage（哪些索引被用到）
• 列出的索引自服务器启动以来从未被查询使用
• 清理这些索引可以减少写入开销（每次写入都要维护索引）和磁盘空间
• 注意：某些索引可能只在月度报表中使用，需要结合业务周期判断，不要仅凭短期数据删除

场景五：查看 InnoDB 缓冲池中的热点表

执行语句：

-- 查看哪些表占用了最多的 InnoDB 缓冲池空间
SELECT 
    object_schema,
    object_name,
    allocated,
    data,
    pages,
    pages_hashed,
    pages_old,
    rows_cached
FROM sys.innodb_buffer_stats_by_table
ORDER BY allocated DESC
LIMIT 10;

操作后结果（示例）：

结果解读：

• sys.innodb_buffer_stats_by_table 按表统计 InnoDB 缓冲池中的页占用情况
• allocated 是缓冲池中为该表分配的总空间（包括索引页和数据页）
• pages 是占用的页数（每页 16KB），pages_hashed 是自适应哈希索引中的页数
• 如果某表 allocated 很大但业务上不是热点表，说明可能有全表扫描把它刷进了缓冲池，挤占了其他热数据

场景六：生成诊断报告（存储过程）

执行语句：

-- 生成指定时间段的诊断报告（需要 SUPER 权限）
CALL sys.diagnostics(60, 30, 'current');

参数说明：

结果解读：

• sys.diagnostics() 存储过程会自动采集 Performance Schema 数据，生成包含以下内容的报告：
  • 当前连接状态
  • 最慢的 SQL
  • 最热门的表和索引
  • InnoDB 事务和锁状态
  • 内存使用统计
• 适合在性能问题发生时快速采集现场数据，比手动写 SQL 高效得多
• 输出是文本格式，可以直接复制到性能分析报告中

常见误区

面试考点

Q：sys Schema 和 Performance Schema 的关系？

sys Schema 是基于 Performance Schema 和 information_schema 构建的视图层。Performance Schema 提供底层原始事件数据（如每个语句的耗时、扫描行数），sys Schema 将这些数据汇总加工成人类可读的诊断报告（如最慢 SQL、未使用索引、热点表）。两者关系类似"数据仓库的原始表 vs 报表视图"。

Q：如何找出数据库中冗余的索引？

查询 sys.schema_unused_indexes，它列出了自服务器启动以来从未被使用过的索引。但删除前应确认：1. 业务是否有周期性查询（如月度报表）用到该索引；2. 该索引是否是唯一约束（唯一索引即使不用于查询也有数据完整性作用）；3. 在测试环境验证删除后的性能影响。

Q：sys.statement_analysis 中的 full_scans 高说明什么？

说明该 SQL 执行了全表扫描（或全索引扫描），没有有效利用索引。可能原因：1. WHERE 条件没有索引；2. 索引选择性差（如性别字段）；3. 数据量小，优化器认为全表扫描更快；4. 查询条件使用了函数或类型转换，导致索引失效。

Q：sys Schema 在生产环境怎么用？

1. 问题发生时快速诊断：SELECT * FROM sys.session WHERE command != 'Sleep' ORDER BY time DESC 看当前慢连接；2. 定期巡检：SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 20 找慢 SQL；3. 索引优化：SELECT * FROM sys.schema_unused_indexes 找冗余索引；4. 配合 sys.diagnostics() 生成诊断报告。注意：sys 数据重启后清空，持续监控需要外部工具。

Q：sys.session 中的 rows_examined 和慢查询日志的 Rows_examined 有什么区别？

本质来源相同（都来自 Performance Schema），但 sys.session 是实时当前值（events_statements_current），慢查询日志是语句执行完成后的最终值。sys.session 可以在语句执行过程中观察进度，慢查询日志只能事后分析。

小结

• sys Schema 是 MySQL 5.7 内置的诊断视图库，基于 Performance Schema 和 information_schema 构建
• sys.session 替代 SHOW PROCESSLIST，信息更丰富（扫描行数、临时表等）
• sys.statement_analysis 汇总慢 SQL，关注 full_scans、tmp_disk_tables、rows_examined_avg
• sys.schema_unused_indexes 找出从未使用的索引，是清理冗余索引的依据
• sys.innodb_buffer_stats_by_table 看缓冲池热点，判断是否有冷数据挤占热数据
• sys.diagnostics() 存储过程可一键生成综合诊断报告
• sys Schema 只是视图层，不存储数据，查询开销低，适合临时诊断

下一章引子：sys Schema 让我们看清了"哪里慢"，但有时候优化器没有选择最优的执行计划——比如明明有合适的索引却走了全表扫描。Optimizer Hints 和 Index Hints 让你能手动干预优化器的决策。