10bet网址
MySQL 8.0参考手册
相关的文档10bet官方网站 本手册下载 从本手册中摘录

15.15.3 InnoDB Information_Schema架构对象表

您可以提取有关由此管理的架构对象的元数据Innodb.使用Innodb.Information_Schema.表。这些信息来自于数据字典。传统上,您将使用来自的技术获得这类信息第15.17节“InnoDB Monitors”,设置Innodb.监视并解析输出显示发动机InnoDB状态声明。的Innodb.Information_Schema.表接口允许您使用SQL查询该数据。

Innodb.Information_Schema.模式对象表包括下面列出的表。

innodb_datafiles innodb_tablestats innodb_foreign innodb_columns innodb_indexes innodb_fields innodb_fields innodb_tablespaces innodb_tablespaces_briefnnodb_foreign_colsInnodb_tables

表名称表示提供的数据类型:

Innodb.Information_Schema.模式对象表可以通过诸如table_id.index_id.,空间,允许您轻松检索要学习或监视的对象的所有可用数据。

指的是Innodb.Information_Schema.10bet官方网站有关每个表列的信息的文档。

例15.2 InnoDB INFORMATION_SCHEMA Schema对象表

此示例使用简单的表格(T1.)单一索引(i1的元数据类型Innodb.Information_Schema.架构对象表。

  1. 创建测试数据库和表T1.

    mysql> CREATE DATABASE test;mysql >使用测试;MySQL>创建表T1(Col1 Int,Col2 Char(10),Col3 Varchar(10))Engine = InnoDB;MySQL>在T1(COL1)上创建索引I1;
  2. 创建表格后T1., 询问INNODB_TABLES找到元数据测试/ t1.

    MySQL> Select * from Information_schema.innodb_tables,其中name ='test / t1'\ g *************************** 1.行*************************** table_id:71名称:test / t1标志:1 n_cols:6空间:57 row_format:compact zip_page_size:0 Instant_Cols:0.

    桌子T1.有一个table_id.71.旗帜字段提供关于表格式和存储特性的位级别信息。有六列,其中三列是创建的隐藏列Innodb.DB_ROW_IDdb_trx_id.,db_roll_ptr.)。表的id空间是57(值0将表示表驻留在系统表空间中)。的ROW_FORMAT紧凑。ZIP_PAGE_SIZE只适用于桌子压缩行格式。Instant_cols.在添加第一即时列之前,在表中显示表中的列数ALTER TABLE……添加一列算法=瞬发

  3. 使用table_id.信息来自INNODB_TABLES,查询INNODB_COLUMNS表获取关于表列的信息。

    mysql> SELECT * FROM INFORMATION_SCHEMA。INNODB_COLUMNSwhere TABLE_ID = 71\G *************************** 1. row *************************** TABLE_ID: 71 NAME: col1 POS: 0 MTYPE: 6 PRTYPE: 1027 LEN: 4 HAS_DEFAULT: 0 DEFAULT_VALUE: NULL *************************** 2. row *************************** TABLE_ID: 71 NAME: col2 POS: 1 MTYPE: 2 PRTYPE: 524542 LEN: 10 HAS_DEFAULT: 0 DEFAULT_VALUE: NULL *************************** 3. row *************************** TABLE_ID: 71 NAME: col3 POS: 2 MTYPE: 1 PRTYPE: 524303 LEN: 10 HAS_DEFAULT: 0 DEFAULT_VALUE: NULL

    除了table_id.和列名称INNODB_COLUMNS提供序号位置(p)每个列(从0开始,顺序递增),列MTYPE或者主要类型(6 = int,2 = char,1 = varchar),PRTYPE或者精确类型(一个二进制值,其中位表示MySQL数据类型、字符集代码和可空性),以及列长度(Len.)。的has_default.默认值列仅适用于立即添加的列ALTER TABLE……添加一列算法=瞬发

  4. 使用table_id.信息来自INNODB_TABLES再一次,查询Innodb_Indexes.有关与表关联的索引的信息T1.

    mysql> SELECT * FROM INFORMATION_SCHEMA。Innodb_Indexes.WHERE TABLE_ID = 71 \G *************************** 1. row *************************** INDEX_ID: 111 NAME: GEN_CLUST_INDEX TABLE_ID: 71 TYPE: 1 N_FIELDS: 0 PAGE_NO: 3 SPACE: 57 MERGE_THRESHOLD: 50 *************************** 2. row *************************** INDEX_ID: 112 NAME: i1 TABLE_ID: 71 TYPE: 0 N_FIELDS: 1 PAGE_NO: 4 SPACE: 57 MERGE_THRESHOLD: 50

    Innodb_Indexes.返回两个索引的数据。第一个索引是GEN_CLUST_INDEX,它是由Innodb.如果表没有用户定义的聚集索引。第二个索引(i1)是用户定义的次要索引。

    index_id.是索引的标识符,该索引在实例中的所有数据库中都是唯一的。的table_id.标识索引与之关联的表。索引类型value表示索引类型(1 =聚集索引,0 =次要索引)。的n_fileds.值是包含索引的字段数。Page_no.是索引B树的根页码,以及空间是索引所在的表空间的ID。非零值表示索引不在系统表空间中。merge_threshold.定义索引页中数据量的百分比阈值。如果在删除一行或通过更新操作缩短一行时,索引页中的数据量低于此值(默认为50%),Innodb.尝试使用相邻索引页面合并索引页面。

  5. 使用index_id.信息来自Innodb_Indexes., 询问INNODB_FIELDS有关索引字段的信息i1

    mysql> SELECT * FROM INFORMATION_SCHEMA。INNODB_FIELDSwhere INDEX_ID = 112 \G *************************** 1. row *************************** INDEX_ID: 112 NAME: col1 POS: 0

    INNODB_FIELDS提供名称索引字段及其在索引内的序号。如果索引(I1)已在多个字段上定义,INNODB_FIELDS将为每个索引字段提供元数据。

  6. 使用空间信息来自INNODB_TABLES, 询问innodb_tablespaces.表获取关于表的表空间的信息。

    mysql> SELECT * FROM INFORMATION_SCHEMA。innodb_tablespaces.WHERE SPACE = 57 \G *************************** 1. row *************************** SPACE: 57 NAME: test/t1 FLAG: 16417 ROW_FORMAT: Dynamic PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single FS_BLOCK_SIZE: 4096 FILE_SIZE: 114688 ALLOCATED_SIZE: 98304 AUTOEXTEND_SIZE: 0 SERVER_VERSION: 8.0.23 SPACE_VERSION: 1 ENCRYPTION: N STATE: normal

    除了空间表空间的id和名称关联表的,innodb_tablespaces.提供表空间旗帜数据是关于表空间格式和存储特性的位级别信息。还提供了表空间ROW_FORMAT页面大小,以及其他几个表空间元数据项。

  7. 使用空间信息来自INNODB_TABLES再一次,查询innodb_datafiles.查找表空间数据文件的位置。

    MySQL> Select * from Information_schema.innodb_datafiles,其中space = 57 \ g *************************** 1.行***************************空间:57路径:./test/t1.ibd

    数据文件位于测试MySQL下的目录数据目录中。如果一个文件表空间是在MySQL数据目录之外的位置创建的数据目录条款的创建表声明,表空间路径将是一个完全限定的目录路径。

  8. 最后一步,向表中插入一行T1.table_id = 71.)查看数据Innodb_tablestats.桌子。MySQL Optimizer使用此表中的数据来计算查询时要使用的索引Innodb.桌子。此信息来自内存中的数据结构。

    mysql>插入t1值(5,'abc','def');查询OK,1行受影响(0.06秒)mysql> select * from Information_schema.innodb_tableStats其中table_id = 71 \ g **************************************************************************************************************** 1.行*************************** table_id:71名称:test / t1 stats_initialized:初​​始化num_rows:1 clust_index_size:1其他_index_size:0 Modified_counter:1 autoinc:0 Ref_count:1

    stats_initialized字段表示是否已为表收集统计信息。NUM_ROWS表中当前估计的行数。的CLUST_INDEX_SIZEoother_index_size.字段报告分别存储表的群集和辅助索引的磁盘上的页数。的modified_counter.值显示由DML操作修改的行数和来自外键的级联操作。的自动化值是用于任何基于自动增量的操作的下一个数字。表中没有定义自动增量列T1.,所以值是0。的REF_COUNT价值是一个计数器。当计数器达到0时,它表示可以从表缓存中逐出表元数据。


示例15.3外键信息_schema架构对象表

Innodb_foreign.innodb_foreign_cols.表提供关于外键关系的数据。属性中找到的数据,此示例使用具有外键关系的父表和子表Innodb_foreign.innodb_foreign_cols.表。

  1. 使用父表创建测试数据库:

    mysql> CREATE DATABASE test;mysql >使用测试;mysql> CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;mysql> CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), CONSTRAINT fk1 FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) ENGINE=INNODB;
  2. 创建父表和子表之后,进行查询Innodb_foreign.并找到外国关键数据测试/孩子测试/父外键关系:

    mysql> SELECT * FROM INFORMATION_SCHEMA。Innodb_foreign.\G *************************** 1. row *************************** ID: test/fk1 FOR_NAME: test/child REF_NAME: test/parent N_COLS: 1 TYPE: 1

    元数据包括外键IDfk1、),它被命名为约束在子表上定义的。的for_name.是定义外键的子表的名称。ref_name.父表的名称是引用桌子)。n_cols.是外键索引中的列数。类型是一个数值,表示位标志,提供关于外键列的附加信息。在这种情况下类型价值是1,表示在删除级联选项为外键指定。看到Innodb_foreign.表定义有关的更多信息类型价值观。

  3. 使用外键ID, 询问innodb_foreign_cols.查看有关外键列的数据。

    MySQL> Select * from Information_schema.innodb_foreign_cols其中Id ='test / fk1'\ g *************************** 1.行*************************** ID:test / fk1 for_col_name:parent_id ref_col_name:ID POS:0

    FOR_COL_NAME是子表中的外键列的名称,以及REF_COL_NAME是父表中引用列的名称。的p值是外键索引内的关键字字段的序号位置,从零开始。


例15.4连接InnoDB INFORMATION_SCHEMA Schema对象表

这个例子演示了如何连接三个Innodb.Information_Schema.架构对象表(INNODB_TABLESinnodb_tablespaces.,Innodb_tablestats.)收集关于员工示例数据库中表的文件格式,行格式,页面大小和索引大小信息。

下表名称别名用于缩短查询字符串:

一个如果()控制流函数用于解释压缩表。如果压缩表,则使用索引大小进行计算ZIP_PAGE_SIZE而不是页面大小CLUST_INDEX_SIZEoother_index_size.(以字节为单位报告)被除以1024 * 1024提供以兆字节为单位的索引大小。使用圆形的()函数。

mysql> SELECT a. name, a. row_format, @page_size:= IF(a. row_format, @page_size);ROW_FORMAT='Compressed', b.ZIP_PAGE_SIZE, b.PAGE_SIZE) AS page_size, ROUND((@page_size * c.CLUST_INDEX_SIZE) /(1024*1024)) AS pk_mb, ROUND((@page_size * c.OTHER_INDEX_SIZE) /(1024*1024)) AS secidx_mb FROM INFORMATION_SCHEMA.INNODB_TABLES a INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES b on a.NAME = b.NAME INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESTATS c on b.NAME = c.NAME WHERE a.NAME LIKE 'employees/%' ORDER BY a.NAME DESC; +------------------------+------------+-----------+-------+-----------+ | NAME | ROW_FORMAT | page_size | pk_mb | secidx_mb | +------------------------+------------+-----------+-------+-----------+ | employees/titles | Dynamic | 16384 | 20 | 11 | | employees/salaries | Dynamic | 16384 | 93 | 34 | | employees/employees | Dynamic | 16384 | 15 | 0 | | employees/dept_manager | Dynamic | 16384 | 0 | 0 | | employees/dept_emp | Dynamic | 16384 | 12 | 10 | | employees/departments | Dynamic | 16384 | 0 | 0 | +------------------------+------------+-----------+-------+-----------+