MySQL系统库详解：information_schema与performance_schema实战

1. MySQL系统库全景概览作为数据库管理员和开发者我们每天都在与MySQL的各种数据库打交道但很少有人真正深入了解MySQL自带的四个系统数据库information_schema、mysql、performance_schema和sys。这些系统库就像是MySQL的神经系统默默记录和管理着数据库的各类元数据、权限信息、性能指标等关键数据。我曾在一次数据库性能调优中深刻体会到这些系统库的价值。当时一个生产环境的查询突然变慢通过查询performance_schema中的等待事件表我们迅速定位到是某个新上线的存储过程导致了全表扫描。这种问题如果仅靠经验猜测可能需要数小时排查而借助系统库的元数据信息我们仅用10分钟就找到了症结所在。2. information_schema数据库的元数据宝库2.1 核心架构与设计原理information_schema是一个虚拟数据库它并不占用实际的存储空间其所有表都是基于内存的视图。这种设计使得查询元数据信息时无需磁盘I/O极大提高了查询效率。当你执行SHOW DATABASES或SHOW TABLES这类命令时MySQL实际上就是在查询information_schema下的对应视图。这个系统库遵循SQL标准因此在不同数据库系统中如MySQL、PostgreSQL的表结构基本一致这为跨数据库管理提供了便利。但需要注意的是MySQL对标准做了一些扩展添加了特有的表和字段。2.2 关键表详解与实用查询2.2.1 SCHEMATA表数据库清单SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN (information_schema,mysql,performance_schema,sys);这个查询可以列出所有用户创建的数据库排除了系统数据库。其中DEFAULT_CHARACTER_SET_NAME和DEFAULT_COLLATION_NAME字段特别有用可以帮助检查数据库的字符集设置是否统一。2.2.2 TABLES表表结构大全SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA your_database ORDER BY DATA_LENGTH DESC LIMIT 10;这个查询可以找出指定数据库中数据量最大的10张表对于数据库优化很有帮助。TABLE_ROWS是估算值对于InnoDB表可能不够精确但可以作为参考。2.2.3 COLUMNS表字段级元数据SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_database AND DATA_TYPE varchar AND CHARACTER_MAXIMUM_LENGTH 100;这个查询可以找出所有VARCHAR类型且长度超过100的字段对于识别可能存在的设计问题很有帮助。2.3 高级应用场景2.3.1 数据库文档自动生成SELECT t.TABLE_NAME, t.TABLE_COMMENT, c.COLUMN_NAME, c.COLUMN_TYPE, c.COLUMN_COMMENT FROM information_schema.TABLES t JOIN information_schema.COLUMNS c ON t.TABLE_SCHEMA c.TABLE_SCHEMA AND t.TABLE_NAME c.TABLE_NAME WHERE t.TABLE_SCHEMA your_database ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;这个查询可以生成完整的数据库字典包含表和字段的注释信息。配合导出功能可以自动生成数据库文档。2.3.2 外键关系分析SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA your_database AND REFERENCED_TABLE_NAME IS NOT NULL;这个查询可以分析出数据库中的所有外键关系对于理解复杂的数据模型非常有帮助。3. mysql库权限与用户管理的核心3.1 用户与权限体系mysql库是MySQL权限系统的核心它包含了用户账号、权限、角色等关键信息。其中最重要的表是user、db、tables_priv、columns_priv和procs_priv它们共同构成了MySQL的权限层级体系。3.1.1 user表全局权限控制SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv FROM mysql.user WHERE User NOT LIKE mysql.%;这个查询可以列出所有非系统用户及其基本权限。在生产环境中应该定期审计这些权限设置。3.1.2 密码安全策略MySQL 8.0引入了更严格的密码策略SELECT validate_password.policy;可以查看当前的密码策略级别取值从低到高为0(LOW)、1(MEDIUM)、2(STRONG)。3.2 常用管理操作3.2.1 创建用户并授权CREATE USER app_user192.168.1.% IDENTIFIED BY complex_password; GRANT SELECT, INSERT, UPDATE ON app_db.* TO app_user192.168.1.%; FLUSH PRIVILEGES;这是创建应用用户的推荐做法限制IP段并使用最小权限原则。3.2.2 权限问题排查SHOW GRANTS FOR problem_user%;当遇到权限问题时这个命令可以清晰显示用户实际拥有的权限。3.3 高级技巧3.3.1 批量修改用户密码UPDATE mysql.user SET authentication_string PASSWORD(new_password) WHERE User IN (user1, user2); FLUSH PRIVILEGES;注意在MySQL 8.0中应使用ALTER USER语法因为PASSWORD()函数已被移除。3.3.2 查找空密码账户SELECT User, Host FROM mysql.user WHERE authentication_string OR authentication_string IS NULL;这是一个重要的安全检查应该确保没有账户使用空密码。4. performance_schema性能监控的利器4.1 架构与配置performance_schema使用内存表存储性能数据默认情况下不是所有仪器(instrument)和消费者(consumer)都启用需要根据监控需求进行配置。查看当前配置SELECT * FROM performance_schema.setup_instruments WHERE ENABLED YES LIMIT 10; SELECT * FROM performance_schema.setup_consumers WHERE ENABLED YES;4.2 关键性能指标查询4.2.1 查看最耗资源的SQLSELECT digest_text, count_star, avg_timer_wait/1000000000 as avg_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;这个查询可以找出最耗时的SQL模式digest_text是规范化后的SQL语句。4.2.2 文件I/O统计SELECT file_name, event_name, count_read, sum_number_of_bytes_read, count_write, sum_number_of_bytes_written FROM performance_schema.file_summary_by_instance ORDER BY sum_number_of_bytes_read sum_number_of_bytes_written DESC LIMIT 10;这个查询可以找出最活跃的数据文件对于识别I/O瓶颈很有帮助。4.3 高级监控场景4.3.1 锁等待分析SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM performance_schema.events_waits_current w INNER JOIN information_schema.innodb_trx b ON b.trx_id w.blocking_instance INNER JOIN information_schema.innodb_trx r ON r.trx_id w.requesting_instance;这个查询可以找出当前的锁等待情况对于解决死锁问题非常有用。4.3.2 内存使用分析SELECT event_name, current_alloc, high_alloc FROM performance_schema.memory_summary_global_by_event_name WHERE current_count 0 ORDER BY current_alloc DESC LIMIT 10;这个查询可以查看MySQL内部的内存使用情况对于内存优化很有参考价值。5. sys库performance_schema的友好视图5.1 常用视图解析sys库提供了许多有用的视图来简化performance_schema的查询5.1.1 查看会话信息SELECT * FROM sys.session WHERE program_name mysql ORDER BY time_ms DESC LIMIT 5;这个查询可以找出最耗时的客户端会话。5.1.2 内存使用概览SELECT * FROM sys.memory_global_total;这个视图提供了MySQL总内存使用量的概览。5.2 实用查询示例5.2.1 未使用索引查询SELECT * FROM sys.schema_unused_indexes;这个查询可以找出可能不需要的冗余索引但删除前应该仔细评估。5.2.2 全表扫描查询SELECT * FROM sys.statements_with_full_table_scans ORDER BY no_index_used_count DESC LIMIT 10;这个查询可以找出执行全表扫描的SQL语句是优化的重要目标。5.3 自定义视图sys库的视图定义存储在/usr/share/mysql/mysql_sys_schema.sql文件中我们可以参考这些视图创建自己的监控视图。例如创建一个简化版的会话视图CREATE VIEW my_custom_session_view AS SELECT thd_id, conn_id, user, db, command, time_ms/1000 as time_sec, current_memory/1024/1024 as memory_mb, progress, stage, statement_latency FROM sys.session WHERE user NOT LIKE system%;6. 系统库的综合应用实战6.1 数据库健康检查脚本-- 数据库大小检查 SELECT table_schema, SUM(data_lengthindex_length)/1024/1024 as total_size_mb, SUM(data_length)/1024/1024 as data_size_mb, SUM(index_length)/1024/1024 as index_size_mb, COUNT(*) as table_count FROM information_schema.tables GROUP BY table_schema ORDER BY total_size_mb DESC; -- 用户权限审计 SELECT user, host, IF(Super_privY,YES,NO) as is_superuser, IF(Grant_privY,YES,NO) as can_grant, IF(pluginmysql_native_password,native, IF(plugincaching_sha2_password,sha2,plugin)) as auth_method, password_expired FROM mysql.user ORDER BY is_superuser DESC, user; -- 性能问题检查 SELECT SUBSTRING_INDEX(host,:,1) as client, db, command, time, state, info FROM information_schema.processlist WHERE time 60 ORDER BY time DESC;6.2 自动化监控方案可以创建一个存储过程定期收集关键指标DELIMITER // CREATE PROCEDURE collect_performance_metrics() BEGIN -- 记录当前时间戳 SET collection_time NOW(); -- 收集最活跃的10个表 INSERT INTO performance_metrics.table_activity SELECT collection_time, object_schema, object_name, count_star, timer_wait/1000000000 as wait_sec FROM performance_schema.table_io_waits_summary_by_table ORDER BY count_star DESC LIMIT 10; -- 收集最慢的5个查询 INSERT INTO performance_metrics.slow_queries SELECT collection_time, digest_text, count_star, avg_timer_wait/1000000000 as avg_sec FROM performance_schema.events_statements_summary_by_digest ORDER BY avg_timer_wait DESC LIMIT 5; END // DELIMITER ;6.3 安全审计方案-- 检查匿名账户 SELECT * FROM mysql.user WHERE User ; -- 检查root账户的访问限制 SELECT * FROM mysql.user WHERE User root AND Host NOT IN (localhost,127.0.0.1); -- 检查密码过期策略 SELECT User, Host, password_last_changed, IF(password_lifetime IS NULL, 永不过期, CONCAT(password_lifetime,天)) as expiry_days FROM mysql.user; -- 检查存储过程和函数的DEFINER SELECT routine_schema, routine_name, routine_type, definer, security_type FROM information_schema.routines WHERE definer NOT LIKE root% AND definer NOT LIKE mysql.%;7. 常见问题与解决方案7.1 information_schema查询性能问题当information_schema查询变慢时通常是因为查询了包含大量表的数据库。可以通过以下方法优化添加精确的WHERE条件缩小查询范围避免在information_schema上使用复杂的JOIN操作对于频繁查询的元数据考虑定期缓存到普通表中7.2 performance_schema内存占用过高performance_schema默认会占用一定内存如果服务器资源有限可以调整配置-- 查看当前内存分配 SELECT * FROM performance_schema.memory_summary_global_by_event_name ORDER BY SUM_NUMBER_OF_BYTES_ALLOC DESC LIMIT 10; -- 禁用不需要的instrument UPDATE performance_schema.setup_instruments SET ENABLED NO WHERE NAME LIKE memory/%;7.3 mysql.user表损坏修复如果mysql.user表损坏可以尝试以下恢复步骤停止MySQL服务启动MySQL时加上--skip-grant-tables参数执行修复命令REPAIR TABLE mysql.user;重启MySQL服务7.4 sys库视图不存在问题在某些MySQL版本中sys库可能需要手动安装# 使用MySQL提供的脚本安装sys库 mysql -u root -p /usr/share/mysql/mysql_sys_schema.sql8. 最佳实践与经验分享8.1 生产环境配置建议定期备份mysql系统库mysqldump --databases mysql mysql_backup.sql限制对系统库的直接访问REVOKE ALL PRIVILEGES ON information_schema.* FROM app_user%;为performance_schema配置适当的监控粒度避免过度影响性能8.2 监控策略建立关键指标基线记录正常情况下各性能指标的值设置告警阈值当指标超出正常范围时触发告警定期生成健康报告汇总数据库的运行状况8.3 性能优化经验通过sys.schema_redundant_indexes识别并删除冗余索引使用sys.statements_with_errors_or_warnings找出有问题的SQL语句通过sys.user_summary分析用户活动模式8.4 安全加固措施定期审计mysql.user表中的账户和权限确保所有账户都使用强密码限制远程root登录检查并删除测试数据库和匿名账户9. 进阶技巧与深度探索9.1 自定义performance_schema监控可以根据业务需求添加自定义监控点-- 添加新的instrument UPDATE performance_schema.setup_instruments SET ENABLED YES, TIMED YES WHERE NAME wait/io/file/my_custom_component/%; -- 创建自定义consumer INSERT INTO performance_schema.setup_consumers (NAME, ENABLED) VALUES (my_custom_consumer, YES);9.2 扩展sys库功能可以基于sys库的视图创建更符合业务需求的视图CREATE VIEW my_custom_performance_view AS SELECT es.event_name, es.count_star, es.sum_timer_wait/1000000000 as total_sec, es.avg_timer_wait/1000000000 as avg_sec FROM performance_schema.events_statements_summary_by_digest es ORDER BY es.sum_timer_wait DESC LIMIT 20;9.3 与其他监控系统集成可以将系统库的查询结果导出到Prometheus、Grafana等监控系统# 示例将数据库大小指标导出到Prometheus mysql -N -e SELECT CONCAT(mysql_database_size{database\,table_schema,\} , SUM(data_lengthindex_length)) FROM information_schema.tables GROUP BY table_schema /var/lib/prometheus/node_exporter/mysql_db_size.prom10. 版本差异与兼容性10.1 MySQL 5.7 vs 8.0的主要变化performance_schema在8.0中更加完善新增了许多instrumentMySQL 8.0移除了PASSWORD()函数改用更安全的认证方式sys库在8.0中增加了许多新视图8.0中information_schema的部分表改为了基于数据字典的视图性能更好10.2 升级注意事项升级前备份所有系统库检查自定义脚本是否使用了废弃的函数或语法测试performance_schema的配置是否兼容验证sys库视图是否正常工作10.3 跨版本查询兼容性技巧编写兼容不同MySQL版本的查询SELECT /*!50600 hostname as server_name, */ /*!80000 hostname as server_name, */ VERSION() as mysql_version;这种注释语法可以让MySQL根据版本号决定执行哪部分代码。