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 

In addition to the SPACE  ID of the tablespace and the NAME  of the associated table, INNODB_TABLESPACES  provides tablespace FLAG  data, which is bit level information about tablespace format and storage characteristics. Also provided are tablespace ROW_FORMAT , PAGE_SIZE , and several other tablespace metadata items. 

7. Using the SPACE  information from INNODB_TABLES  once again, query INNODB_DATAFILES  for the location of the tablespace data file. 

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE SPACE = 57 \G *************************** 1. row *************************** SPACE: 57  PATH: ./test/t1.ibd 

The datafile is located in the test  directory under MySQL’s data  directory. If a file-per-table tablespace were created in a location outside the MySQL data directory using the DATA DIRECTORY clause of the CREATE TABLE  statement, the tablespace PATH  would be a fully qualified directory path. 

8. As a final step, insert a row into table t1  ( TABLE_ID = 71 ) and view the data in the INNODB_TABLESTATS  table. The data in this table is used by the MySQL optimizer to calculate which index to use when querying an InnoDB  table. This information is derived from in-memory data structures. 

mysql> INSERT INTO t1 VALUES(5, ’abc’, ’def’); Query OK, 1 row affected (0.06 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where TABLE_ID = 71 \G *************************** 1. row *************************** TABLE_ID: 71 NAME: test/t1 STATS_INITIALIZED: Initialized NUM_ROWS: 1  CLUST_INDEX_SIZE: 1  OTHER_INDEX_SIZE: 0  MODIFIED_COUNTER: 1 AUTOINC: 0 REF_COUNT: 1 

The STATS_INITIALIZED  field indicates whether or not statistics have been collected for the table. NUM_ROWS  is the current estimated number of rows in the table. The CLUST_INDEX_SIZE  and OTHER_INDEX_SIZE  fields report the number of pages on disk that store clustered and secondary indexes for the table, respectively. The MODIFIED_COUNTER  value shows the number of rows modified by DML operations and cascade operations from foreign keys. The AUTOINC  value is the next number to be issued for any autoincrement-based operation. There are no autoincrement columns defined on table t1 , so the value is 0. The REF_COUNT  value is a counter. When the counter reaches 0, it signifies that the table metadata can be evicted from the table cache. 

