进阶-系统数据库
一、MySQL进阶
“数据库不是黑盒子，而是由四座精密的‘城市’组成：
一座管理权限，一座存储结构，一座监控性能，一座优化体验。”
—— 理解这四个系统数据库，是成为MySQL专家的必经之路
🌐 一、为什么需要系统数据库？—— 一场“数据自治”的革命
在早期数据库中，元数据（Metadata）和系统信息往往散落在各个文件中，查询困难、维护复杂。例如：
- 要查一张表的结构？需要解析
.frm文件 - 要查用户权限？需要手动检查
user表 - 要分析性能瓶颈？需要写复杂脚本
💡 2005年，MySQL 5.0 引入system databases，将元数据、权限、性能监控统一管理，彻底改变了数据库运维方式。
我们聚焦MySQL的四大核心系统数据库，它们共同构成了数据库的“操作系统”：
mysql ← 权限与安全中枢 information_schema ← 元数据仓库 performance_schema ← 性能监控仪表盘 sys ← 性能分析优化器二、第一座城市：mysql 数据库——权限与安全的“首都”
mysql 数据库是MySQL的“权力中心”，存储了所有用户账户、权限、服务器配置等核心安全信息。
📌 核心表与作用
| 表名 | 作用 | 关键字段 |
|---|---|---|
| user | 用户账户与全局权限 | User,Host,Password,Super_priv |
| db | 数据库级权限 | User,Host,Db,Select_priv |
| tables_priv | 表级权限 | User,Host,Db,Table_name,Grant_priv |
| columns_priv | 列级权限 | User,Host,Db,Table_name,Column_name |
| proxies_priv | 代理权限 | User,Host,Proxied_user,Proxied_host |
为什么它如此重要？
- 权限管理：
GRANT/REVOKE语句最终都写入这些表 - 安全审计：所有登录、操作都记录在
mysql.general_log中 - 服务器配置：
my.cnf中的配置会同步到mysql表（如max_connections）
三、第二座城市：information_schema 数据库——元数据的“中央档案馆”
information_schema 是MySQL的“元数据仓库”，提供只读视图访问数据库结构信息。
📌 核心表与作用
| 表名 | 作用 | 典型查询示例 |
|---|---|---|
| tables | 所有表的结构 | SELECT * FROM information_schema.tables WHERE table_schema='test'; |
| columns | 所有列的定义 | SELECT column_name, data_type FROM information_schema.columns WHERE table_name='users'; |
| indexes | 所有索引 | SELECT index_name, column_name FROM information_schema.statistics WHERE table_name='orders'; |
| key_column_usage | 外键关系 | SELECT referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE table_name='orders'; |
🔥 为什么它如此重要？
- 动态查询结构：应用程序可动态获取表结构（如ORM框架）
- 数据库自省：无需额外工具，直接SQL查询元数据
- 数据字典：
SHOW CREATE TABLE的底层实现
四、第三座城市：performance_schema 数据库——性能的“实时仪表盘”
performance_schema 是MySQL的“性能监控引擎”，实时收集服务器运行时指标。
📌 核心表与作用
表格
| 表名 | 作用 | 关键指标 |
|---|---|---|
| events_statements_current | 当前正在执行的SQL | DIGEST_TEXT,TIMER_WAIT,ROWS_AFFECTED |
| events_waits_current | 当前等待事件 | EVENT_NAME,TIMER_WAIT,SOURCE |
| threads | 线程状态 | THREAD_ID,PROCESSLIST_ID,STATE |
| memory_summary_global_by_event_name | 内存使用 | EVENT_NAME,SUM_NUMBER_OF_BYTES_ALLOC |
| file_summary_by_instance | 文件I/O | FILE_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ |
🔥 为什么它如此重要？
- 性能诊断：精准定位慢查询、锁等待、I/O瓶颈
- 实时监控：比
SHOW PROCESSLIST更细粒度 - 历史分析：通过
performance_schema.setup_instruments启用/禁用监控
五、第四座城市：sys 数据库——性能分析的“智能助手”
sys 数据库是performance_schema的“高级视图层”，提供更易读、更聚焦的性能分析工具。
📌 核心视图与作用
| 视图名 | 作用 | 优势 |
|---|---|---|
| sys.schema_redefine_table | 重定义表的优化建议 | 识别大表、冗余索引 |
| sys.statement_analysis | 慢查询分析 | 按执行频率、时间排序 |
| sys.statement_with_runtimes_in_95th_percentile | 95%分位数慢查询 | 识别真正影响用户体验的查询 |
| sys.x$ps_digest_95th_percentile_by_avg_latency | 95%分位数平均延迟 | 比statement_analysis更精确 |
| sys.host_summary | 主机级性能汇总 | CPU、内存、I/O 综合分析 |
🔥 为什么它如此重要？
- 简化分析：无需记忆复杂表结构，直接使用视图
- 聚焦问题：自动过滤掉无关数据，直指核心瓶颈
- 自动化：
sys.ps_check视图可自动生成优化建议
四大数据库协同工作：一次完整查询的旅程
假设执行：
SELECT * FROM orders WHERE user_id = 100;- mysql：检查
user_id=100的用户权限（mysql.user表） - information_schema：获取
orders表的结构（information_schema.columns） - performance_schema：记录查询执行时间、等待事件
- sys：在
sys.statement_analysis中汇总该查询的性能数据
🌟效果：从权限验证到性能分析，全程自动化，无需人工干预。