1. 项目概述MySQL 8.0的“系统心脏”当你安装好MySQL 8.0兴冲冲地登录进去准备大展拳脚创建自己的第一个数据库时有没有注意到在SHOW DATABASES;命令的返回结果里除了你创建的库总有几个“不请自来”的家伙它们就是information_schema、mysql、performance_schema和sys。对于很多刚入门的朋友来说这几个数据库既熟悉又陌生——知道它们很重要但又不敢轻易动它们甚至有点“敬而远之”。其实这四个数据库是MySQL 8.0默认安装的系统数据库你可以把它们理解为MySQL数据库管理系统的“操作系统”或“控制中心”。它们不存储你的业务数据而是存储着MySQL服务器运行所需的所有元数据、权限信息、性能指标和优化工具。理解它们是每一位从“会用MySQL”迈向“懂MySQL”的开发者或DBA的必经之路。无论是排查一个诡异的权限问题还是深挖一条慢SQL的性能瓶颈亦或是进行日常的运维监控你的操作都离不开与这四个系统数据库打交道。今天我们就来彻底拆解MySQL 8.0中这四位“幕后英雄”。我会结合十多年踩坑填坑的经验不仅告诉你它们是什么更会深入讲解它们怎么用以及在什么场景下能成为你的“救命稻草”。你会发现用好它们你的数据库运维和开发效率将提升一个档次。2. 核心设计思路为何是这四个库在深入每个库之前我们先从整体上理解MySQL设计者的思路。这四个库并非随意堆砌它们构成了一个层次清晰、职责分明的运维支撑体系。2.1 分层监控与管理架构你可以把这四个系统数据库想象成一个现代化城市的四大职能部门information_schema市政信息查询中心。这里提供全市整个MySQL实例所有建筑物数据库、房间表、住户列的静态档案信息。你想知道某个表有多少列、是什么类型、有没有索引来这里查档案最快。performance_schema实时交通与资源监控中心。这里布满了摄像头和传感器实时收集每条道路SQL语句的车流量执行次数、拥堵情况锁等待、资源消耗内存、I/O。用于性能分析和瓶颈定位。sys市长决策支持与市民服务大厅。它基于前两个中心特别是performance_schema的原始数据加工成高度可读的报告、视图和函数。把晦涩的监控数据变成“过去一小时最堵的十条路”、“哪个小区用水量异常”这样直观的结论并提供一键优化的建议。mysql公安与户籍管理系统。这里存储了所有市民用户的身份信息、护照权限、以及一些基础的城市运行规则时区、插件等。负责安全和访问控制。2.2 从存储引擎到内存仪表的演进在MySQL 5.6之前系统的信息分散且不易查询。information_schema虽然存在但部分数据来自存储引擎查询可能触发昂贵的I/O操作。performance_schema在5.5版本引入并在后续版本中不断增强它最大的特点是在内存中完成绝大部分性能数据的收集对业务性能影响极小。到了MySQL 8.0performance_schema默认开启且功能空前强大而sys库则作为其最佳拍档让性能数据变得“平易近人”。这种设计体现了MySQL从“一个数据存储软件”向“一个可观测性极强的数据平台”的演进。作为使用者我们的运维思路也应该从“出问题了再登录服务器看日志”转变为“通过系统数据库主动洞察潜在风险”。注意这四个系统数据库在物理上大多以InnoDB表或特殊的内存表形式存在。绝对不要尝试用DROP DATABASE命令删除它们这会导致MySQL实例崩溃或无法启动。也尽量避免直接修改其中的表数据mysql库中的权限表除外但需使用GRANT、REVOKE等专用命令。3.information_schema数据库的“活字典”这是最常用也最容易被误解的系统数据库。很多人把它当作一系列只读视图的集合用来查表结构。这没错但只看到了它功能的冰山一角。3.1 核心功能与常用视图解析information_schema库中的所有对象都是视图VIEW而非实体表。这意味着你查询它们时MySQL会动态地从系统元数据中生成结果。它的视图大致可分为以下几类模式与对象元数据这是最常用的部分。SCHEMATA查看所有数据库。TABLES查看所有表的信息包括表引擎、行数、数据长度、创建时间等。这里TABLE_ROWS对于InnoDB表是估算值并不精确用于快速了解数据规模。COLUMNS查看所有表的列信息包括数据类型、是否为空、默认值等。STATISTICS查看表索引的详细信息包括索引名称、包含的列、唯一性、基数等。索引基数CARDINALITY是优化器选择索引的关键参考。KEY_COLUMN_USAGETABLE_CONSTRAINTS查看主键、外键、唯一键等约束信息。权限与安全信息SCHEMA_PRIVILEGESTABLE_PRIVILEGESCOLUMN_PRIVILEGES查看数据库、表、列级别的权限授予情况。比直接查mysql.user表更清晰。服务器状态与设置GLOBAL_VARIABLESSESSION_VARIABLES查看全局和当前会话的系统变量。等同于SHOW GLOBAL VARIABLES和SHOW VARIABLES命令。GLOBAL_STATUSSESSION_STATUS查看全局和当前会话的状态变量。等同于SHOW GLOBAL STATUS和SHOW STATUS命令。进程与锁信息PROCESSLIST查看当前所有连接线程的信息。功能类似于SHOW PROCESSLIST但可以通过SQL条件过滤更灵活。INNODB_LOCKSINNODB_LOCK_WAITS在8.0中部分功能已迁移至performance_schema用于诊断InnoDB锁等待问题。3.2 实战应用场景与脚本示例场景一快速生成数据库文档作为开发或DBA经常需要梳理数据库结构。你可以写一个查询快速导出所有表的字段清单。-- 查询某个数据库下所有表的核心字段信息 SELECT TABLE_SCHEMA as 数据库, TABLE_NAME as 表名, COLUMN_NAME as 字段名, COLUMN_TYPE as 数据类型, IS_NULLABLE as 可空, COLUMN_DEFAULT as 默认值, COLUMN_COMMENT as 字段说明 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_database_name ORDER BY TABLE_NAME, ORDINAL_POSITION;场景二找出没有主键的表没有主键的InnoDB表对性能和复制都很不友好。可以用以下语句筛查。SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES t LEFT JOIN information_schema.STATISTICS s ON t.TABLE_SCHEMA s.TABLE_SCHEMA AND t.TABLE_NAME s.TABLE_NAME AND s.INDEX_NAME PRIMARY WHERE t.TABLE_SCHEMA NOT IN (mysql, information_schema, performance_schema, sys) AND s.INDEX_NAME IS NULL AND t.TABLE_TYPE BASE TABLE;场景三监控长时间运行的SQL结合PROCESSLIST和TIME字段可以定期检查是否有执行时间过长的查询。SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 100) AS SQL_Snippet -- 截取前100个字符避免信息过长 FROM information_schema.PROCESSLIST WHERE COMMAND Query AND TIME 60 -- 查找执行超过60秒的查询 ORDER BY TIME DESC;实操心得查询information_schema中的TABLES视图获取InnoDB表的行数TABLE_ROWS时这个值是通过采样统计估算出来的在数据量频繁变更的表上可能误差极大。如果需要精确行数请务必使用SELECT COUNT(*) FROM table_name但要注意后者会对大表产生全表扫描的成本。通常information_schema.TABLE_ROWS用于快速评估数据量级而不是精确计算。4.performance_schema深潜性能的“仪表盘”如果说information_schema是档案室那performance_schema简称P_S就是配备了高速摄像机和各类传感器的指挥中心。它默认启用专注于收集服务器运行时的性能事件数据其设计目标是对生产环境性能影响最小通常开销在1%-3%。4.1 核心概念消费者、生产者与仪器理解P_S先要理解三个核心概念仪器Instruments代码中的“埋点”。MySQL在关键代码路径如函数调用、锁操作、I/O等待上插装了成千上万个仪器。例如wait/io/file/innodb/innodb_data_file这个仪器就用于收集InnoDB数据文件I/O等待事件。消费者Consumers存储事件的“表”。仪器产生的事件原始数据会被存储到不同的消费者表中。主要分为几类events_waits_*等待事件如I/O、锁等待。events_statements_*语句执行事件SQL语句。events_stages_*阶段事件语句执行的子阶段如解析、排序。events_transactions_*事务事件。summary_*上述事件的聚合摘要表最常用。配置表setup_*开头的表用于动态启用/禁用仪器和消费者设置事件过滤等。这是灵活使用P_S的关键。4.2 关键配置与启停P_S功能强大但复杂默认不会收集所有数据以免开销过大。常用配置如下-- 查看当前启用了哪些消费者 SELECT * FROM performance_schema.setup_consumers; -- 启用所有语句事件消费者会略微增加开销但非常有用 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %events_statements_%; -- 启用所有等待事件消费者用于分析I/O、锁瓶颈 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %events_waits_%; -- 查看和启用特定仪器例如启用所有与文件I/O相关的仪器 SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE %file%; UPDATE performance_schema.setup_instruments SET ENABLED YES, TIMED YES WHERE NAME LIKE wait/io/file/%;注意事项在生产环境修改P_S配置需谨慎。建议先在测试环境或业务低峰期进行。开启过多仪器和消费者会增加内存和CPU开销。通常按需开启如排查问题时开启问题解决后关闭是更稳妥的策略。4.3 实战性能诊断案例案例找出全表扫描的罪魁祸首全表扫描是性能杀手。我们可以利用events_statements_summary_by_digest视图来发现它。这个视图将SQL语句按“摘要”去掉参数值后的模式聚合非常强大。-- 首先确保语句事件消费者已开启见上文配置 -- 执行一些业务查询后运行以下诊断语句 SELECT SCHEMA_NAME, DIGEST_TEXT AS normalized_sql_pattern, -- 标准化后的SQL模式 COUNT_STAR AS exec_count, -- 执行次数 SUM_ROWS_EXAMINED AS total_rows_examined, -- 总共检查的行数如果远大于返回行数可能索引不佳 SUM_ROWS_SENT AS total_rows_sent, -- 总共返回的行数 ROUND(SUM_ROWS_EXAMINED / COUNT_STAR) AS avg_rows_examined_per_exec, -- 每次执行平均检查行数 ROUND(SUM_ROWS_SENT / COUNT_STAR) AS avg_rows_sent_per_exec, -- 每次执行平均返回行数 FIRST_SEEN, LAST_SEEN FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME IS NOT NULL AND DIGEST_TEXT LIKE SELECT% -- 关注SELECT语句 AND SUM_ROWS_EXAMINED 10000 -- 检查行数超过1万可能有问题 ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 10;这个查询能帮你快速定位那些“检查了大量数据但只返回少量结果”的SQL这些SQL通常就是全表扫描或索引使用不当的候选者。你可以根据DIGEST_TEXT找到具体的SQL模式然后去优化它。4.4 深入锁等待分析在MySQL 8.0中InnoDB的锁信息更深度地集成到了P_S中。-- 查看当前正在发生的锁等待 SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id w.requesting_trx_id; -- 结合P_S查看更详细的锁等待事件历史 SELECT EVENT_ID, THREAD_ID, EVENT_NAME, SOURCE, TIMER_WAIT/1000000000 AS wait_time_sec, -- 将皮秒转换为秒 OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS FROM performance_schema.events_waits_history_long WHERE EVENT_NAME LIKE %innodb%lock% ORDER BY TIMER_WAIT DESC LIMIT 10;5.sys库化繁为简的“性能仪表盘”直接查询performance_schema的表数据原始且字段繁多对新手不友好。sys库应运而生它由一系列视图、函数和存储过程构成全部基于performance_schema和information_schema目的是将复杂的性能数据转化为人类可读的格式。5.1 视图分类与核心视图解读sys库的视图命名非常有规律通常以x$开头的视图提供原始数据而不带x$的同名视图提供格式化后的友好输出。主要类别包括主机与进程摘要host_summary,processlistIO与内存摘要io_global_by_file_by_bytes,memory_global_by_current_bytes语句与模式摘要statement_analysis,schema_table_statistics用户与索引摘要user_summary,schema_index_statistics5.2 一键式性能诊断报告sys库最强大的功能之一是它的存储过程能生成一份全面的健康报告。-- 生成一份标准的诊断报告输出到控制台 CALL sys.diagnostics(1, 10, 0); -- 参数解释 -- 1: 诊断间隔秒这里指收集1秒内的瞬时数据。 -- 10: 诊断次数这里指连续收集10次。 -- 0: 是否包含PSperformance_schema的原始数据0为不包含更简洁。这个报告会包含大量的章节如系统变量、状态变量差值、等待事件排名、全表扫描的SQL、索引使用情况等是进行周期性健康检查或故障排查的利器。5.3 日常运维必备查询1. 查看当前最消耗资源的SQL简化版-- 查看平均执行时间最长的SQL SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 5; -- 查看总执行时间最长的SQL可能因为执行次数多 SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 5; -- 查看全表扫描或临时表使用的SQL SELECT * FROM sys.statements_with_full_table_scans LIMIT 5; SELECT * FROM sys.statements_with_temp_tables LIMIT 5;2. 查看索引使用情况-- 找出从未使用过的索引冗余索引候选 SELECT * FROM sys.schema_unused_indexes; -- 查看索引使用统计读/写比例 SELECT * FROM sys.schema_index_statistics WHERE table_schema your_db ORDER BY rows_selected DESC LIMIT 10;3. 查看内存使用情况-- 按线程/用户/事件查看内存分配 SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10; -- 查看InnoDB缓冲池的使用详情 SELECT * FROM sys.innodb_buffer_stats_by_schema;5.4 自定义视图与扩展sys库本身是开源的位于/usr/share/mysql或MySQL安装目录的share文件夹下其视图定义就是标准的SQL。这意味着你可以学习它的写法甚至创建自己的自定义视图来监控你关心的特定指标。例如你可以创建一个视图专门监控某个业务库中特定表的I/O延迟。实操心得对于日常运维我强烈建议将sys库的statement_analysis、schema_unused_indexes、host_summary_by_statement_latency等视图的查询集成到你的监控系统如Zabbix、Prometheus或定期巡检脚本中。它们提供的信息质量远高于简单的SHOW STATUS能让你提前发现潜在的性能退化问题而不是等到用户投诉。6.mysql库权限与系统的“基石”这是最“古老”也最核心的系统数据库。它存储了用户账户、权限、存储过程/函数/触发器定义、时区、插件加载信息等。直接操作这个库的表风险很高但理解其结构至关重要。6.1 核心表结构解析用户与权限表user用户账户、全局权限、密码插件、密码过期策略等。一行代表一个用户主机。db数据库级别的权限。tables_priv表级别的权限。columns_priv列级别的权限。procs_priv存储过程和函数的权限。role_edges和default_rolesMySQL 8.0引入的角色Role相关表。其他系统表time_zone_*时区信息表。servers用于FEDERATED存储引擎。plugin已安装的插件信息。general_log和slow_log如果通用查询日志和慢查询日志设置为写入表而非文件日志内容就存在这里。6.2 权限系统的工作流与安全实践当客户端发起连接并执行操作时MySQL的权限验证流程如下连接验证检查mysql.user表中的Host,User,authentication_string密码以及账户是否被锁定。权限检查这是一个从全局(user)到数据库(db)再到表(tables_priv)最后到列(columns_priv)的逐级检查过程。只要在某一级找到匹配的权限并验证通过即允许操作。绝对不要使用INSERT,UPDATE,DELETE语句直接修改mysql库中的权限表这会导致权限缓存不一致必须执行FLUSH PRIVILEGES;才能刷新而此操作在高并发下可能引发阻塞。正确做法是使用MySQL提供的权限管理语句-- 创建用户并授权8.0推荐方式创建和授权分离 CREATE USER app_user192.168.1.% IDENTIFIED BY StrongPassword123!; GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO app_user192.168.1.%; -- 使用角色管理权限8.0新特性更清晰 CREATE ROLE app_read_only; GRANT SELECT ON app_db.* TO app_read_only; GRANT app_read_only TO report_user%; SET DEFAULT ROLE app_read_only TO report_user%;6.3 密码管理与安全加固MySQL 8.0默认使用caching_sha2_password插件比旧的mysql_native_password更安全。在mysql.user表中可以看到相关字段。密码过期策略可以通过ALTER USER ... PASSWORD EXPIRE;强制用户定期修改密码。账户锁定ALTER USER ... ACCOUNT LOCK;可以临时锁定可疑账户。密码历史通过password_reuse_interval和password_reuse_history系统变量可以防止重复使用旧密码。6.4 误操作与恢复如果不慎误删了root用户或其他关键用户在还能通过其他方式如--skip-grant-tables启动访问数据库的情况下可以重建-- 在跳过权限表模式下启动MySQL后 USE mysql; -- 重建rootlocalhost用户请替换为你自己的强密码 CREATE USER rootlocalhost IDENTIFIED WITH caching_sha2_password BY YourNewStrongPassword; GRANT ALL PRIVILEGES ON *.* TO rootlocalhost WITH GRANT OPTION; FLUSH PRIVILEGES;踩坑记录曾经有一次同事直接在mysql.user表里删除了一个用户然后业务立刻报错连接不上但FLUSH PRIVILEGES后依然不行。原因是某些连接池或长连接在连接建立时就缓存了权限信息即使服务器端刷新了客户端会话可能仍持有旧的权限缓存。最终是通过重启应用服务器迫使连接池重建连接才解决的。所以权限变更后要考虑对现有连接的影响对于重要业务可能需要在低峰期操作并安排应用重启。7. 常见问题排查与运维技巧实录在实际工作中这四个系统数据库是排查问题的“瑞士军刀”。下面记录几个典型场景。7.1 连接数爆满是谁干的应用突然报“Too many connections”。首先增大max_connections是临时办法找到根源才是关键。-- 1. 查看当前所有连接详情来自information_schema SELECT * FROM information_schema.PROCESSLIST; -- 或者使用sys库更清晰的视图 SELECT * FROM sys.processlist WHERE conn_id IS NOT NULL; -- 2. 按用户和主机分组看哪个来源的连接最多可能是连接池配置错误或攻击 SELECT USER, HOST, COUNT(*) as connection_count FROM information_schema.PROCESSLIST GROUP BY USER, HOST ORDER BY connection_count DESC; -- 3. 查看正在执行的SQL找出可能卡住的慢查询 SELECT * FROM sys.session WHERE command Query ORDER BY time DESC LIMIT 10; -- 4. 如果发现大量“Sleep”状态的空闲连接可能是应用没有正确关闭连接。 -- 可以设置 interactive_timeout 和 wait_timeout 来断开超时空闲连接。7.2 数据库突然变慢如何快速定位瓶颈业务反馈系统变慢你需要像侦探一样快速收集线索。-- 第一步快速健康检查使用sys库 -- 查看当前最慢的SQL SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 5; -- 查看当前的锁等待 SELECT * FROM sys.innodb_lock_waits; -- 第二步检查系统资源结合performance_schema -- 查看哪些文件IO延迟最高 SELECT FILE_NAME, COUNT_READ, COUNT_WRITE, SUM_NUMBER_OF_BYTES_READ, SUM_NUMBER_OF_BYTES_WRITE, (SUM_TIMER_WAIT/COUNT_STAR)/1000000000 as avg_latency_sec FROM performance_schema.file_summary_by_instance ORDER BY avg_latency_sec DESC LIMIT 5; -- 第三步检查内存使用 -- 查看哪些SQL使用了最多的内存可能导致磁盘临时表 SELECT * FROM sys.statements_with_temp_tables ORDER BY disk_tmp_tables DESC LIMIT 5;7.3 如何安全地清理performance_schema和sys库的数据P_S的数据默认存储在内存表中重启MySQL实例会清零。但一些汇总表summary_*可能会积累大量历史数据。你可以安全地重置它们-- 重置所有performance_schema的汇总表和事件历史表 TRUNCATE TABLE performance_schema.events_waits_history; TRUNCATE TABLE performance_schema.events_waits_history_long; TRUNCATE TABLE performance_schema.events_statements_history; TRUNCATE TABLE performance_schema.events_statements_history_long; -- 重置所有摘要表这是最常用的清理操作对运行中服务无影响 TRUNCATE TABLE performance_schema.events_statements_summary_by_digest; TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name; -- ... 可以按需重置其他summary表 -- 注意不要对setup_*配置表执行TRUNCATE或DELETE7.4 迁移或复制时系统数据库如何处理这是一个常见误区。在大多数情况下你不需要也不应该复制这四个系统数据库。逻辑备份如mysqldump默认使用--databases或--all-databases参数时会包含mysql库因为里面有用户权限但通常不包含information_schema,performance_schema,sys。这是正确的因为后三个库的数据是实例运行时动态生成或特定的。物理备份如直接复制数据文件备份整个数据目录时自然包含了它们。但在恢复到新服务器时mysql库中的用户权限信息会被覆盖而其他三个库的数据在新实例启动后会被重置或重新生成。主从复制系统数据库的表默认不会被复制。权限的复制需要通过复制mysql库的相关表来实现但这需要特别配置--replicate-ignore-db除外且容易出错。更推荐的做法是在主从库上分别管理权限或使用像pt-table-sync这样的工具同步mysql库。最佳实践将用户和权限的创建、变更做成SQL脚本纳入版本管理Git。在搭建新从库或恢复备份后先恢复业务数据然后执行这份权限脚本来重建用户而不是直接复制mysql库。对于performance_schema的配置如果有自定义调整如启用了某些仪器也应记录下相应的UPDATE语句作为初始化脚本的一部分。