MySQL元数据库information schema

一、概述

information_schema提供了对数据库元数据、统计信息以及有关MySQLServer信息的访问（例如：数据库名或表名、字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典或系统目录。

在每个MySQL实例中都有一个独立的information_schema，用来存储MySQL实例中所有其他数据库的基本信息。information_schema库下包含多个只读表（非持久表），所以在磁盘中的数据目录下没有对应的关联文件，且不能对这些表设置触发器。虽然在查询时可以使用use语句将默认数据库设置为information_schema，但该库下的所有表是只读的，不能执行insert、update、delete等数据变更操作。

二、入门

查看当前数据库下表存储的信息

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| administrable_role_authorizations     |
| applicable_roles                      |
| character_sets                        |
| check_constraints                     |
| collation_character_set_applicability |
| collations                            |
| column_privileges                     |
| column_statistics                     |
| columns                               |
| columns_extensions                    |
| enabled_roles                         |
| engines                               |
| events                                |
| files                                 |
| innodb_buffer_page                    |
| innodb_buffer_page_lru                |
| innodb_buffer_pool_stats              |
| innodb_cached_indexes                 |
| innodb_cmp                            |
| innodb_cmp_per_index                  |
| innodb_cmp_per_index_reset            |
| innodb_cmp_reset                      |
| innodb_cmpmem                         |
| innodb_cmpmem_reset                   |
| innodb_columns                        |
| innodb_datafiles                      |
| innodb_fields                         |
| innodb_foreign                        |
| innodb_foreign_cols                   |
| innodb_ft_being_deleted               |
| innodb_ft_config                      |
| innodb_ft_default_stopword            |
| innodb_ft_deleted                     |
| innodb_ft_index_cache                 |
| innodb_ft_index_table                 |
| innodb_indexes                        |
| innodb_metrics                        |
| innodb_session_temp_tablespaces       |
| innodb_tables                         |
| innodb_tablespaces                    |
| innodb_tablespaces_brief              |
| innodb_tablestats                     |
| innodb_temp_table_info                |
| innodb_trx                            |
| innodb_virtual                        |
| key_column_usage                      |
| keywords                              |
| optimizer_trace                       |
| parameters                            |
| partitions                            |
| plugins                               |
| processlist                           |
| profiling                             |
| referential_constraints               |
| resource_groups                       |
| role_column_grants                    |
| role_routine_grants                   |
| role_table_grants                     |
| routines                              |
| schema_privileges                     |
| schemata                              |
| schemata_extensions                   |
| st_geometry_columns                   |
| st_spatial_reference_systems          |
| st_units_of_measure                   |
| statistics                            |
| table_constraints                     |
| table_constraints_extensions          |
| table_privileges                      |
| tables                                |
| tables_extensions                     |
| tablespaces                           |
| tablespaces_extensions                |
| triggers                              |
| user_attributes                       |
| user_privileges                       |
| view_routine_usage                    |
| view_table_usage                      |
| views                                 |
+---------------------------------------+
79 rows in set (0.00 sec)

根据MySQL版本的不同，表的个数和存放是有所不同的。

MySQL版本 表总数 核心差异说明
5.6 59张 10张MyISAM引擎数据字典表+49张Memory引擎统计表
5.7 61张 数据字典表迁移为InnoDB引擎（10张），Memory统计表增至51张
8.0 79张 数据字典表隐藏至mysql系统库（需通过information_schema同名视图访问），
统计表仍为Memory引擎

三、表分类

information_schema中的表可以按照其功能分为以下几个主要类别：

  • 表结构相关表
  • 权限管理相关表
  • 统计信息相关表
  • 事件调度器相关表
  • 触发器相关表
  • 视图相关表
  • 字符集和校对规则相关表
  • 存储过程和函数相关表
  • 引擎相关表
  • 其他相关表

3.1 表结构相关表

描述数据库、表、列、索引、约束、分区、表空间等核心结构：

  • schemata（数据库基本信息）
  • tables（表基本信息）
  • columns（列信息）
  • columns_extensions（列扩展信息）
  • statistics（索引信息）
  • key_column_usage（键约束关联列）
  • referential_constraints（外键约束详情）
  • check_constraints（check约束）
  • table_constraints（表级约束汇总）
  • table_constraints_extensions（表约束扩展信息）
  • partitions（分区表信息）
  • tablespaces（表空间信息）
  • tablespaces_extensions（表空间扩展信息）
  • schemata_extensions（数据库扩展信息）
  • tables_extensions（表扩展信息）

3.2 权限管理相关表

记录用户、角色的权限及授权关系：

  • user_privileges（全局用户权限）
  • schema_privileges（数据库级权限）
  • table_privileges（表级权限）
  • column_privileges（列级权限）
  • administrable_role_authorizations（可管理的角色授权）
  • applicable_roles（适用的角色）
  • enabled_roles（已启用的角色）
  • role_column_grants（角色的列权限）
  • role_routine_grants（角色的存储程序权限）
  • role_table_grants（角色的表权限）
  • user_attributes（用户属性，含权限相关扩展信息）

3.3 统计信息相关表

存储数据库对象的统计数据、运行时状态：

  • column_statistics（列统计信息）
  • processlist（当前连接进程状态）
  • profiling（sql 执行性能分析）

3.4 事件调度器相关表

仅包含事件调度器的元数据：

  • events（事件定义、执行时间等信息）

3.5 触发器相关表

仅包含触发器的元数据：

  • triggers（触发器定义、触发规则等）

3.6 视图相关表

描述视图定义及依赖关系：

  • views（视图定义语句）
  • view_table_usage（视图依赖的基础表）
  • view_routine_usage（视图依赖的存储程序）

3.7 字符集和校对规则相关表

描述字符集、排序规则及关联：

  • character_sets（支持的字符集）
  • collations（支持的校对规则）
  • collation_character_set_applicability（校对规则与字符集的对应关系）

3.8 存储过程和函数相关表

记录存储程序的元数据及参数：

  • routines（存储过程 / 函数基本信息）
  • parameters（存储程序的参数详情）

3.9 引擎相关表

主要为InnoDB引擎特有元数据，及通用引擎信息：

  • engines（支持的存储引擎特性）
  • innodb_buffer_page（innodb缓冲池页详情）
  • innodb_buffer_page_lru（innodb缓冲池lru列表页）
  • innodb_buffer_pool_stats（innodb缓冲池统计）
  • innodb_cached_indexes（innodb缓存的索引统计）
  • innodb_columns（innodb列元数据）
  • innodb_cmp（innodb压缩统计）
  • innodb_cmp_per_index（innodb索引压缩统计）
  • innodb_cmp_per_index_reset（重置索引压缩统计）
  • innodb_cmp_reset（重置压缩统计）
  • innodb_cmpmem（innodb缓冲池内存统计）
  • innodb_cmpmem_reset（重置缓冲池内存统计）
  • innodb_datafiles（innodb数据文件信息）
  • innodb_fields（innodb字段信息）
  • innodb_foreign（innodb外键信息）
  • innodb_foreign_cols（innodb外键列关联）
  • innodb_ft_being_deleted（innodb全文索引待删除记录）
  • innodb_ft_config（innodb全文索引配置）
  • innodb_ft_default_stopword（innodb默认停用词）
  • innodb_ft_deleted（innodb全文索引已删除记录）
  • innodb_ft_index_cache（innodb全文索引缓存）
  • innodb_ft_index_table（innodb全文索引表）
  • innodb_indexes（innodb索引元数据）
  • innodb_metrics（innodb性能指标）
  • innodb_session_temp_tablespaces（innodb会话临时表空间）
  • innodb_tables（innodb表元数据）
  • innodb_tablespaces（innodb表空间详情）
  • innodb_tablespaces_brief（innodb表空间简要信息）
  • innodb_tablestats（innodb表统计信息）
  • innodb_temp_table_info（innodb临时表信息）
  • innodb_trx（innodb当前事务信息）
  • innodb_virtual（innodb虚拟列信息）

3.10 其他相关表

涵盖未被上述分类覆盖的系统元数据：

  • files（数据库相关文件信息）
  • keywords（mysql关键字列表）
  • optimizer_trace（优化器执行计划跟踪）
  • plugins（已安装插件信息）
  • resource_groups（资源组配置）
  • st_geometry_columns（空间几何列信息）
  • st_spatial_reference_systems（空间参考系统）
  • st_units_of_measure（空间度量单位）

四、show命令与information_schema

很多show查询语句（例如：show databases、show tables等）可以通过查询information_schema下的表实现，以下是典型对应关系：

information_schema和show命令都是获取元数据的常用方式，但适用场景不同。以下是详细对比和功能映射：​

4.1 核心差异对比​

​

对比维度 information_schema show命令
本质 系统视图集合支持SQL语法） 内置命令（固定输出格式）
灵活性 高：支持where/join/group by等过滤、关联 低：参数固定，结果不可定制
信息范围 全面：覆盖所有元数据细节 简洁：聚焦常用场景（表结构、权限、状态）
性能 复杂查询可能耗资源（需扫描视图） 轻量高效：针对性优化，执行速度快
适用场景 复杂分析、程序化处理（脚本自动化） 临时查询、快速诊断（手动运维）

4.2 常用功能映射表

​

功能场景 show命令示例 information_schema查询示例
查看数据库列表 show databases; select schema_name from
information_schema.schemata;
查看表结构 show columns from table_name; select column_name, data_type from
information_schema.columns
where table_name = 'table_name';
查看索引信息 show index from table_name; select index_name, column_name from
information_schema.statistics
where table_name = 'table_name';
查看表状态 show table status like 'table_name'; select * from information_schema.tables
where table_name = 'table_name';
查看权限信息 show grants for 'user'@'host'; select * from information_schema.user_privileges
where grantee = "'user'@'host'";
查看字符集 show character set; select * from information_schema.character_sets;
查看存储引擎 show engines; select * from information_schema.engines;

4.3 常用show命令汇总（快速参考）

常用的show命令如下：

show databases;                          #查看所有数据库
show tables;                             #查看当前库的所有表
show tables from  库名                   #查看某个指定库下的表
show create database 库名                #查看建库语句
show create table 库名.表名              #查看建表语句
show grants for  root@'localhost'        #查看用户的权限信息
show charset；                           #查看字符集
show collation                           #查看校对规则
show processlist;                        #查看数据库连接情况
show index from                          #表的索引情况
show status                              #数据库状态查看
show status like '%lock%';               #模糊查询数据库某些状态
show variables                           #查看所有配置信息
show variables like '%lock%';            #查看部分配置信息
show engines                             #查看支持的所有的存储引擎
show engine innodb status\G              #查看InnoDB引擎相关的状态信息
show binary logs                         #列举所有的二进制日志
show master status                       #查看数据库的日志位置信息
show binlog evnets in                    #查看二进制日志事件
show slave status \G                     #查看从库状态
show relaylog events                     #查看从库relaylog事件信息
desc (show colums from city)             #查看表的列定义信息
-- https://dev.mysql.com/doc/refman/8.0/en/show.html

五、实践操作

通过查询information_schema中的表，可以精准确定位性能瓶颈，制定针对性的优化策略。以下是利用information_schema优化数据库性能的具体方法和实践案例：

5.1 分析索引使用效率，消除无效索引

索引是提升查询性能的核心，但冗余或低效索引会拖慢写入速度（如insert/update/delete）。通过information_schema可识别无用索引并清理。

关键表：

  • statistics：存储索引基本信息（索引名、关联列、是否为主键等）。
  • index_statistics（部分版本支持）：记录索引的访问频率、扫描行数等使用统计。
  • innodb_indexes：InnoDB引擎索引的详细元数据。

优化方法：

  1. 查找未被使用的索引

结合索引的访问统计（如index_statistics的rows_read），筛选长期无访问的索引：

-- 示例：查询某数据库中所有索引及其访问次数（需版本支持index_statistics）
select 
  t.table_schema,
  t.table_name,
  s.index_name,
  s.rows_read  -- 索引被访问的行数（0表示未使用）
from 
  information_schema.statistics s
join 
  information_schema.tables t on s.table_schema = t.table_schema 
                              and s.table_name = t.table_name
left join 
  information_schema.index_statistics ist on s.table_schema = ist.table_schema
                                         and s.table_name = ist.table_name
                                         and s.index_name = ist.index_name
where 
  t.table_schema = 'your_db'  -- 替换为目标数据库
  and s.index_name not in ('primary')  -- 排除主键（通常必需）
having 
  coalesce(s.rows_read, 0) = 0;  -- 筛选未被访问的索引

优化动作：删除长期未使用的非主键索引（需先确认业务无依赖）。

  1. 识别重复/冗余索引

重复索引指同一列（或列组合）上创建多个索引（如index(a)和index(a)），冗余索引指索引列包含在其他索引中（如index(a,b)已包含index(a)的功能）。通过statistics表查询索引列组合：

-- 示例：查询某表的所有索引及关联列，用于手动分析重复/冗余
select 
  index_name,
  group_concat(column_name order by seq_in_index) as index_columns
from 
  information_schema.statistics
where 
  table_schema = 'your_db' 
  and table_name = 'your_table'  -- 替换为目标表
group by 
  index_name;

优化动作：删除重复索引，合并冗余索引（如保留index(a,b)，删除index(a)）。

5.2 定位慢查询相关的表和列

慢查询常因表数据量大、无索引或索引低效导致。通过information_schema可分析表的大小、行数及列的统计信息，辅助优化。

关键表：

  • tables：包含表的行数（table_rows）、数据大小（data_length）、索引大小（index_length）等。
  • columns：列的数据类型、是否为null等（影响索引效率，如null列不适合建索引）。
  • column_statistics：列的分布统计（如值的基数，基数低的列建索引效果差）。

优化方法：

  1. 查找大表（可能导致全表扫描缓慢）

数据量过大的表（尤其是无索引的表）易引发慢查询，通过tables表定位：

-- 示例：查询数据库中数据量前10的表（单位：MB）
select 
  table_schema,
  table_name,
  round((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
  table_rows
from 
  information_schema.tables
where 
  table_schema = 'your_db'
order by 
  total_mb desc
limit 10;

优化动作：对大表进行分区（通过partitions表规划分区策略）、添加合适索引，或拆分表（如按时间分表）。

  1. 分析低基数列（不适合建索引）

列的基数（不同值的数量）过低（如性别、状态列），索引过滤效果差，反而增加维护成本。通过column_statistics查看：

-- 示例：查询某表列的基数（需版本支持column_statistics）
select 
  column_name,
  cardinality  -- 列的基数（值越接近行数，索引效果越好）
from 
  information_schema.column_statistics
where 
  table_schema = 'your_db'
  and table_name = 'your_table';

优化动作：删除低基数列上的索引，改用全表扫描或其他过滤方式。

5.3 监控数据库连接和锁等待

连接数过高或锁等待会导致数据库响应缓慢，information_schema可实时监控这些状态。

关键表：

  • processlist：当前所有数据库连接的信息（进程ID、状态、执行的SQL等）。
  • innodb_trx：InnoDB引擎的当前事务信息（事务ID、状态、持有的锁等）。
  • innodb_locks（部分版本，MySQL 8.0后由performance_schema替代）：记录锁的详细信息。

优化方法：

  1. 定位阻塞连接

通过processlist查找状态为Locked或Waiting for table metadata lock的连接，结合Innodb_trx分析事务阻塞：

-- 示例：查询阻塞其他进程的连接
select 
  id as 进程id,
  user as 用户,
  host as 来源主机,
  db as 数据库,
  command as 命令,
  time as 持续时间（秒）,
  state as 状态,
  info as 执行的SQL
from 
  information_schema.processlist
where 
  state like '%lock%' or state like '%wait%';

优化动作：杀死长期阻塞的进程（kill进程id），优化事务逻辑（减少锁持有时间）。

  1. 分析事务等待锁的原因

通过innodb_trx查看事务的等待状态，定位锁冲突的表和行：

-- 示例：查询InnoDB事务的等待情况
select 
  trx_id as 事务ID,
  trx_state as 事务状态,
  trx_startedas as 开始时间,
  trx_wait_started as 等待开始时间,
  trx_rows_locked as 锁定行数,
  trx_query as 事务SQL
from 
  information_schema.innodb_trx
where 
  trx_state = 'lock wait';  -- 筛选等待锁的事务

优化动作：调整事务执行顺序，避免循环锁；缩小事务范围，减少锁冲突。

5.4 优化存储引擎和表空间配置

不同存储引擎（如InnoDB、MyISAM）的性能特性不同，表空间配置也会影响I/O效率。

关键表：

  • engines：存储引擎的支持情况（是否支持事务、索引、分区等）。
  • innodb_tablespaces：InnoDB表空间的详细信息（文件路径、大小、是否为临时表空间等）。
  • files：数据库相关文件（数据文件、日志文件）的存储路径和大小。

优化方法：

  1. 检查存储引擎是否合理

例如，需要事务支持的表应使用InnoDB，而非MyISAM。通过tables表查看表的引擎：

-- 示例：查询非InnoDB引擎的表（可能需要事务支持）
select 
  table_schema,
  table_name,
  engine
from 
  information_schema.tables
where 
  table_schema = 'your_db'
  and engine != 'InnoDB';

优化动作：将需要事务、行锁的表转换为InnoDB引擎（alter table table_name engine=innodb;）。

  1. 分析表空间碎片化

InnoDB表空间碎片化会导致I/O效率下降，通过innodb_tablespaces查看表空间使用情况：

-- 示例：查询InnoDB表空间的大小和碎片化（简化版）
select 
  name as 表空间名,
  file_size / 1024 / 1024 as 文件大小（MB）,
  allocated_size / 1024 / 1024 as 已分配大小（MB）
from 
  information_schema.innodb_tablespaces
where 
  name like 'your_db/%';  -- 目标数据库的表空间

优化动作：对碎片化严重的表执行optimize table（InnoDB会重建表空间）。

5.5 小结：information_schema性能优化流程

  1. 索引优化：用statistics和index_statistics清理无效/冗余索引。
  2. 表结构优化：用tables和column_statistics定位大表、低基数列，调整结构或分区。
  3. 连接与锁优化：用processlist和innodb_trx解决阻塞和锁等待。
  4. 存储配置优化：用engines和innodb_tablespaces调整引擎和表空间。

六、总结

information_schema是MySQL元数据查询的核心入口，掌握其核心表（tables、columns、statistics）的查询方法，能大幅提升数据库运维效率。无论是日常的结构查询、性能优化，还是自动化工具开发，它都是不可或缺的工具。关键在于理解各表的核心字段含义，结合实际场景灵活组合查询，同时注意数据精度和性能影响，避免滥用。

posted @ 2025-11-02 01:09  夏尔_717  阅读(3)  评论(0)    收藏  举报