搜索
首页数据库mysql教程MySQL中关于information_schema的详细介绍

information_schema数据库是MySQL系统自带的数据库,它提供了数据库元数据的访问方式。感觉information_schema就像是MySQL实例的一个百科全书,记录了数据库当中大部分我们需要了结的信息,比如字符集,权限相关,数据库实体对象信息,外检约束,分区,压缩表,表信息,索引信息,参数,优化,锁和事物等等。通过information_schema我们可以窥透整个MySQL实例的运行情况,可以了结MySQL实例的基本信息,甚至优化调优,维护数据库等,可以说是真正的一部百科全书啊哈哈。

下面就是根据自己学习的一些小小经验将这些表进行一个大体的分类,方便大家了结,要是有不足的地方请指出,我会尽快修改。

1:关于字符集和排序规则相关的系统表

CHARACTER_SETS :存储数据库相关字符集信息(memory存储引擎)

COLLATIONS :字符集对应的排序规则

COLLATION_CHARACTER_SET_APPLICABILITY:就是一个字符集和连线校对的一个对应关系而已

下面我们说一下character sets和collations的区别:

字符集(character sets)存储字符串,是指人类语言中最小的表义符号。例如’A'、’B'等;

排序规则(collations)规则比较字符串,collations是指在同一字符集内字符之间的比较规则

每个字符序唯一对应一种字符集,但一个字符集可以对应多种字符序,其中有一个是默认字符序(Default Collation)

 MySQL中的字符序名称遵从命名惯例:以字符序对应的字符集名称开头;以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如:在字符序“utf8_general_ci”下,字符“a”和“A”是等价的

看一下有关于字符集和校对相关的MySQL变量

character_set_server:默认的内部操作字符集

character_set_client:客户端来源数据使用的字符集

character_set_connection:连接层字符集

character_set_results:查询结果字符集

character_set_database:当前选中数据库的默认字符集

character_set_system:系统元数据(字段名等)字符集

再看一下MySQL中的字符集转换过程:

(1). MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;

(2). 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下:

使用每个数据字段的CHARACTER SET设定值;

若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准);

若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;

若上述值不存在,则使用character_set_server设定值。

(3). 将操作结果从内部操作字符集转换为character_set_results。

2:权限相关的一些表:

SCHEMA_PRIVILEGES:提供了数据库的相关权限,这个表是内存表是从mysql.db中拉去出来的。

TABLE_PRIVILEGES:提供的是表权限相关信息,信息是从 mysql.tables_priv 表中加载的

COLUMN_PRIVILEGES :这个表可以清楚就能看到表授权的用户的对象,那张表那个库以及授予的是什么权限,如果授权的时候加上with grant option的话,我们可以看得到PRIVILEGE_TYPE这个值必须是YES。

USER_PRIVILEGES:提供的是表权限相关信息,信息是从 mysql.user 表中加载的

通过表我们可以很清晰看得到MySQL授权的层次,SCHEMA,TABLE,COLUMN级别,当然这些都是基于用户来授予的。可以看得到MySQL的授权也是相当的细密的,可以具体到列,这在某一些应用场景下还是很有用的,比如审计等。

3:存储数据库系统的实体对象的一些表:

COLUMNS:存储表的字段信息,所有的存储引擎

INNODB_SYS_COLUMNS :存放的是INNODB的元数据, 他是依赖于SYS_COLUMNS这个统计表而存在的。

ENGINES :引擎类型,是否支持这个引擎,描述,是否支持事物,是否支持分布式事务,是否能够支持事物的回滚点

EVENTS :记录MySQL中的事件,类似于定时作业

FILES :这张表提供了有关在MySQL的表空间中的数据存储的文件的信息,文件存储的位置,这个表的数据是从InnoDB in-memory中拉取出来的,所以说这张表本身也是一个内存表,每次重启重新进行拉取。也就是我们下面要说的INNODB_SYS_DATAFILES这张表。还要注意一点的是这张表包含有临时表的信息,所以说和SYS_DATAFILES 这张表是不能够对等的,还是要从INNODB_SYS_DATAFILES看。如果undo表空间也配置是InnoDB 的话,那么也是会被记录下来的。

PARAMETERS :参数表存储了一些存储过程和方法的参数,以及存储过程的返回值信息。存储和方法在ROUTINES里面存储。

PLUGINS :基本上是MySQL的插件信息,是否是活动状态等信息。其实SHOW PLUGINS本身就是通过这张表来拉取道德数据

ROUTINES:关于存储过程和方法function的一些信息,不过这个信息是不包括用户自定义的,只是系统的一些信息。

SCHEMATA:这个表提供了实例下有多少个数据库,而且还有数据库默认的字符集

TRIGGERS :这个表记录的就是触发器的信息,包括所有的相关的信息。系统的和自己用户创建的触发器。

VIEWS :视图的信息,也是系统的和用户的基本视图信息。

这些表存储的都是一些数据库的实体对象,方便我们进行查询和管理,对于一个DBA来说,这些表能够大大方便我们的工作,更快更方便的了结和查询数据库的相关信息。

4:约束外键等相关的一些表:

REFERENTIAL_CONSTRAINTS:这个表提供的外键相关的信息,而且只提供外键相关信息

TABLE_CONSTRAINTS :这个表提供的是 相关的约束信息

INNODB_SYS_FOREIGN_COLS :这个表也是存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的

INNODB_SYS_FOREIGN :存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的,只不过是单独对于INNODB来说的

KEY_COLUMN_USAGE:数据库中所有有约束的列都会存下下来,也会记录下约束的名字和类别

为什么要把外键和约束单列出来呢,因为感觉这是一块独立的东西,虽然我们的生产环境大部分都不会使用外键,因为这会降低性能,但是合理的利用约束还是一个不错的选择,比如唯一约束。

5:关于管理的一些的一些表:

GLOBAL_STATUS ,GLOBAL_VARIABLES,SESSION_STATUS,SESSION_VARIABLES:这四张表分别记录了系统的变量,状态(全局和会话的信息),作为DBA相信大家也都比较熟悉了,而且这几张表也是在系统重启的时候回重新加载的。也就是内存表。

PARTITIONS :MySQL分区表相关的信息,通过这张表我们可以查询到分区的相关信息(数据库中已分区的表,以及分区表的分区和每个分区的数据信息),分区相关详细信息参见MySQL分区管理

PROCESSLIST:show processlist其实就是从这个表拉取数据,PROCESSLIST的数据是他的基础。由于是一个内存表,所以我们相当于在内存中查询一样,这些操作都是很快的。

INNODB_CMP_PER_INDEXINNODB_CMP_PER_INDEX_RESET:这两个表存储的是关于压缩INNODB信息表的时候的相关信息,有关整个表和索引信息都有.我们知道对于一个INNODB压缩表来说,不管是数据还是二级索引都是会被压缩的,因为数据本身也可以看作是一个聚集索引。关于压缩表在information_schema系列十一有些许简单的介绍。

INNODB_CMPMEM ,INNODB_CMPMEM_RESET:这两个表是存放关于MySQL INNODB的压缩页的buffer pool信息,但是要注意一点的就是,用这两个表来收集所有信息的表的时候,是会对性能造成严重的影响的,所以说默认是关闭状态的。如果要打开这个功能的话我们要设置innodb_cmp_per_index_enabled参数为ON状态。

INNODB_BUFFER_POOL_STATS :表提供有关INNODB 的buffer pool相关信息,和show engine innodb status提供的信息是相同的。也是show engine innodb status的信息来源。

INNODB_BUFFER_PAGE_LRU,INNODB_BUFFER_PAGE :维护了INNODB LRU LIST的相关信息,详细请见小编笔记innodb buffer pool小解

INNODB_BUFFER_PAGE :这个表就比较屌了,存的是buffer里面缓冲的页数据。查询这个表会对性能产生很严重的影响,千万不要再我们自己的生产库上面执行这个语句,除非你能接受服务短暂的停顿,详细请见小编笔记innodb buffer pool小解

INNODB_SYS_DATAFILES :这张表就是记录的表的文件存储的位置和表空间的一个对应关系(INNODB)

INNODB_TEMP_TABLE_INFO :这个表惠记录所有的INNODB的所有用户使用到的信息,但是只能记录在内存中和没有持久化的信息。

INNODB_METRICS :提供INNODB的各种的性能指数,是对INFORMATION_SCHEMA的补充,收集的是MySQL的系统统计信息。这些统计信息都是可以手动配置打开还是关闭的。有以下参数都是可以控制的:innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all

INNODB_SYS_VIRTUAL :表存储的是INNODB表的虚拟列的信息,当然这个还是比较简单的,在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与实际存储一列数据相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。

INNODB_CMP,INNODB_CMP_RESET:存储的是关于压缩INNODB信息表的时候的相关信息,详细请见推荐笔记。

为什么把这些表列为管理相关的表呢,因为我感觉像连接,分区,压缩表,innodb buffer pool等表,我们通过这些表都能很清晰的看到自己数据库的相关功能的状态,特别是我们通过一些变量更容易窥透MySQL的运行状态,方便我们进行管理。相关笔记有推荐innodb buffer pool小解,MySQL分区管理,information_schema系列十一。都是小编自己的笔记。

6:关于表信息和索引信息的一些表

TABLES,TABLESPACES,INNODB_SYS_TABLES ,INNODB_SYS_TABLESPACES :

TABLES这张表毫无疑问了,就是记录的数据库中表的信息,其中包括系统数据库和用户创建的数据库。show table status like 'test1'\G的来源就是这个表;

TABLESPACES 却是标注的活跃表空间。 这个表是不提供关于innodb的表空间信息的,对于我们来说并没有太大作用,因为我们生产库是强制INNODB的;

INNODB_SYS_TABLES 这张表依赖的是SYS_TABLES数据字典中拉取出来的。此表提供了有关表格的格式和存储特性,包括行格式,压缩页面大小位级别的信息(如适用)

提供的是关于INNODB的表空间信息,其实和SYS_TABLESPACES 中的INNODB信息是一致的。

STATISTICS:这个表提供的是关于表的索引信息,所有索引的相关信息。

INNODB_SYS_INDEXES:提供相关INNODB表的索引的相关信息,和SYS_INDEXES 这个表存储的信息基本是一样的,只不过后者提供的是所有存储引擎的索引信息,后者只提供INNODB表的索引信息。

INNODB_SYS_TABLESTATS

这个表就比较重要了,记录的是MySQL的INNODB表信息以及MySQL优化器会预估SQL选择合适的索引信息,其实就是MySQL数据库的统计信息

这个表的记录是记录在内存当中的,是一个内存表,每次重启后就会重新记录,所以只能记录从上次重启后的数据库统计信息。有了这个表,我们对于索引的维护就更加方便了,我们可以查询索引的使用次数,方便清理删除不常用的索引,提高表的更新插入等效率,节省磁盘空间。

INNODB_SYS_FIELDS :这个表记录的是INNODB的表索引字段信息,以及字段的排名

INNODB_FT_CONFIG :这张表存的是全文索引的信息

INNODB_FT_DEFAULT_STOPWORD:这个表存放的是stopword 的信息,是和全文索引匹配起来使用的,和innodb的 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 是相同的,这个STOPWORD必须是在创建索引之前创建,而且必须指定字段为varchar。stopword 也就是我们所说的停止词,全文检索时,停止词列表将会被读取和检索,在不同的字符集和排序方式下,会造成命中失败或者找不到此数据,这取决于停止词的不同的排序方式。我们可以使用这个功能筛选不必要字段。

INNODB_FT_INDEX_TABLE:这个表存储的是关于INNODB表有全文索引的索引使用信息的,同样这个表也是要设置innodb_ft_aux_table以后才能够使用的,一般情况下是空的

INNODB_FT_INDEX_CACHE :这张表存放的是插入前的记录信息,也是为了避免DML时候昂贵的索引重组

7:关于MySQL优化相关的一些表

OPTIMIZER_TRACE :提供的是优化跟踪功能产生的信息.关于这个我也谢了做了一个小测试,MySQL追踪优化器小试

PROFILING:SHOW PROFILE可以深入的查看服务器执行语句的工作情况。以及也能帮助你理解执行语句消耗时间的情况。一些限制是它没有实现的功能,不能查看和剖析其他连接的语句,以及剖析时所引起的消耗。

SHOW PROFILES显示最近发给服务器的多条语句,条数根据会话变量profiling_history_size定义,默认是15,最大值为100。设为0等价于关闭分析功能。详细信息请见MySQL profile

INNODB_FT_BEING_DELETED,INNODB_FT_DELETED: INNODB_FT_BEING_DELETED 这张表是INNODB_FT_DELETED的一个快照,只在OPTIMIZE TABLE 的时候才会使用。详细信息详见我的OPTIMIZE TABLE 小解

8:关于MySQL事物和锁的相关的一些表

INNODB_LOCKS:现在获取的锁,但是不含没有获取的锁,而且只是针对INNODB的。

INNODB_LOCK_WAITS:系统锁等待相关信息,包含了阻塞的一行或者多行的记录,而且还有锁请求和被阻塞改请求的锁信息等。

INNODB_TRX:包含了所有正在执行的的事物相关信息(INNODB),而且包含了事物是否被阻塞或者请求锁。

我们通过这些表就能够很方便的查询出来未结束的事物和被阻塞的进程,这是不是更方便了,详细可见information_schema系列八(事物,锁)

 

以上是MySQL中关于information_schema的详细介绍的详细内容。更多信息请关注PHP中文网其他相关文章!

声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
解释InnoDB缓冲池及其对性能的重要性。解释InnoDB缓冲池及其对性能的重要性。Apr 19, 2025 am 12:24 AM

InnoDBBufferPool通过缓存数据和索引页来减少磁盘I/O,提升数据库性能。其工作原理包括:1.数据读取:从BufferPool中读取数据;2.数据写入:修改数据后写入BufferPool并定期刷新到磁盘;3.缓存管理:使用LRU算法管理缓存页;4.预读机制:提前加载相邻数据页。通过调整BufferPool大小和使用多个实例,可以优化数据库性能。

MySQL与其他编程语言:一种比较MySQL与其他编程语言:一种比较Apr 19, 2025 am 12:22 AM

MySQL与其他编程语言相比,主要用于存储和管理数据,而其他语言如Python、Java、C 则用于逻辑处理和应用开发。 MySQL以其高性能、可扩展性和跨平台支持着称,适合数据管理需求,而其他语言在各自领域如数据分析、企业应用和系统编程中各有优势。

学习MySQL:新用户的分步指南学习MySQL:新用户的分步指南Apr 19, 2025 am 12:19 AM

MySQL值得学习,因为它是强大的开源数据库管理系统,适用于数据存储、管理和分析。1)MySQL是关系型数据库,使用SQL操作数据,适合结构化数据管理。2)SQL语言是与MySQL交互的关键,支持CRUD操作。3)MySQL的工作原理包括客户端/服务器架构、存储引擎和查询优化器。4)基本用法包括创建数据库和表,高级用法涉及使用JOIN连接表。5)常见错误包括语法错误和权限问题,调试技巧包括检查语法和使用EXPLAIN命令。6)性能优化涉及使用索引、优化SQL语句和定期维护数据库。

MySQL:初学者的基本技能MySQL:初学者的基本技能Apr 18, 2025 am 12:24 AM

MySQL适合初学者学习数据库技能。1.安装MySQL服务器和客户端工具。2.理解基本SQL查询,如SELECT。3.掌握数据操作:创建表、插入、更新、删除数据。4.学习高级技巧:子查询和窗口函数。5.调试和优化:检查语法、使用索引、避免SELECT*,并使用LIMIT。

MySQL:结构化数据和关系数据库MySQL:结构化数据和关系数据库Apr 18, 2025 am 12:22 AM

MySQL通过表结构和SQL查询高效管理结构化数据,并通过外键实现表间关系。1.创建表时定义数据格式和类型。2.使用外键建立表间关系。3.通过索引和查询优化提高性能。4.定期备份和监控数据库确保数据安全和性能优化。

MySQL:解释的关键功能和功能MySQL:解释的关键功能和功能Apr 18, 2025 am 12:17 AM

MySQL是一个开源的关系型数据库管理系统,广泛应用于Web开发。它的关键特性包括:1.支持多种存储引擎,如InnoDB和MyISAM,适用于不同场景;2.提供主从复制功能,利于负载均衡和数据备份;3.通过查询优化和索引使用提高查询效率。

SQL的目的:与MySQL数据库进行交互SQL的目的:与MySQL数据库进行交互Apr 18, 2025 am 12:12 AM

SQL用于与MySQL数据库交互,实现数据的增、删、改、查及数据库设计。1)SQL通过SELECT、INSERT、UPDATE、DELETE语句进行数据操作;2)使用CREATE、ALTER、DROP语句进行数据库设计和管理;3)复杂查询和数据分析通过SQL实现,提升业务决策效率。

初学者的MySQL:开始数据库管理初学者的MySQL:开始数据库管理Apr 18, 2025 am 12:10 AM

MySQL的基本操作包括创建数据库、表格,及使用SQL进行数据的CRUD操作。1.创建数据库:CREATEDATABASEmy_first_db;2.创建表格:CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入数据:INSERTINTObooks(title,author,published_year)VA

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热工具

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

MinGW - 适用于 Windows 的极简 GNU

MinGW - 适用于 Windows 的极简 GNU

这个项目正在迁移到osdn.net/projects/mingw的过程中,你可以继续在那里关注我们。MinGW:GNU编译器集合(GCC)的本地Windows移植版本,可自由分发的导入库和用于构建本地Windows应用程序的头文件;包括对MSVC运行时的扩展,以支持C99功能。MinGW的所有软件都可以在64位Windows平台上运行。

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

mPDF

mPDF

mPDF是一个PHP库,可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件,并处理不同的语言。与原始脚本如HTML2FPDF相比,它的速度较慢,并且在使用Unicode字体时生成的文件较大,但支持CSS样式等,并进行了大量增强。支持几乎所有语言,包括RTL(阿拉伯语和希伯来语)和CJK(中日韩)。支持嵌套的块级元素(如P、DIV),

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境