包含了表空间数据存储的文件
The FILES
table provides information about the files in which MySQL tablespace data is stored.
INFORMATION_SCHEMA.FILES
provides information about InnoDB
data files. In NDB Cluster this table also provides information about the files in which NDB Cluster Disk Data tables are stored. For information specific to InnoDB
, see InnoDB Notes, later in this section; for information specific to NDB Cluster, see NDB Notes.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
FILE_ID | MySQL extension | |
FILE_NAME | MySQL extension | |
FILE_TYPE | MySQL extension | |
TABLESPACE_NAME | MySQL extension | |
TABLE_CATALOG | MySQL extension | |
TABLE_SCHEMA | MySQL extension | |
TABLE_NAME | MySQL extension | |
LOGFILE_GROUP_NAME | MySQL extension | |
LOGFILE_GROUP_NUMBER | MySQL extension | |
ENGINE | MySQL extension | |
FULLTEXT_KEYS | MySQL extension | |
DELETED_ROWS | MySQL extension | |
UPDATE_COUNT | MySQL extension | |
FREE_EXTENTS | MySQL extension | |
TOTAL_EXTENTS | MySQL extension | |
EXTENT_SIZE | MySQL extension | |
INITIAL_SIZE | MySQL extension | |
MAXIMUM_SIZE | MySQL extension | |
AUTOEXTEND_SIZE | MySQL extension | |
CREATION_TIME | MySQL extension | |
LAST_UPDATE_TIME | MySQL extension | |
LAST_ACCESS_TIME | MySQL extension | |
RECOVER_TIME | MySQL extension | |
TRANSACTION_COUNTER | MySQL extension | |
VERSION | MySQL extension | |
ROW_FORMAT | MySQL extension | |
TABLE_ROWS | MySQL extension | |
AVG_ROW_LENGTH | MySQL extension | |
DATA_LENGTH | MySQL extension | |
MAX_DATA_LENGTH | MySQL extension | |
INDEX_LENGTH | MySQL extension | |
DATA_FREE | MySQL extension | |
CREATE_TIME | MySQL extension | |
UPDATE_TIME | MySQL extension | |
CHECK_TIME | MySQL extension | |
CHECKSUM | MySQL extension | |
STATUS | MySQL extension | |
EXTRA | MySQL extension |
InnoDB Notes
The following notes apply to InnoDB
data files. INFORMATION_SCHEMA.FILES
fields that are not described below are not applicable to InnoDB
and report a NULL
value.
-
Data reported by
INFORMATION_SCHEMA.FILES
is reported from theInnoDB
in-memory cache for open files. By comparison,INFORMATION_SCHEMA.INNODB_SYS_DATAFILES
reports data from theInnoDB
SYS_DATAFILES
internal data dictionary table. -
The data reported by
INFORMATION_SCHEMA.FILES
includes temporary tablespace data. This data is not available in the internalSYS_DATAFILES
data dictionary table, and is therefore not reported byINNODB_SYS_DATAFILES
. -
Undo tablespace data is reported by
INFORMATION_SCHEMA.FILES
. -
FILE_ID
is the tablespace ID, also referred to as thespace_id
orfil_space_t::id
. -
FILE_NAME
is the name of the data file. File-per-table and general tablespaces have a.ibd
file name extension. Undo tablespaces are prefixed byundo
. The system tablespace is prefixed byibdata
. Temporary tablespaces are prefixed byibtmp
. The file name includes the file path, which may be relative to the MySQL data directory (datadir
). -
FILE_TYPE
is the tablespace file type. There are three possible file types forInnoDB
files.TABLESPACE
is the file type for any system, general, or file-per-table tablespace file that holds tables, indexes, or other forms of user data.TEMPORARY
is the file type for temporary tablespaces.UNDO LOG
is the file type for undo tablespaces, which hold undo records. -
TABLESPACE_NAME
is the SQL name for the tablespace. A general tablespace name is theSYS_TABLESPACES.NAME
value. For other tablespace files, names start withinnodb_
, such asinnodb_system
,innodb_undo
, andinnodb_file_per_table
. The file-per-table tablespace name format isinnodb_file_per_table_
, where##
##
is the tablespace ID. -
ENGINE
is the storage engine. ForInnoDB
files, the value is alwaysInnoDB
. -
FREE_EXTENTS
is the number of fully free extents in the current data file. -
TOTAL_EXTENTS
is the number of full extents used in the current data file. Any partial extent at the end of the file is not counted. -
EXTENT_SIZE
is 1048576 (1MB) for files with a 4k, 8k, or 16k page size. Extent size is 2097152 bytes (2MB) for files with a 32k page size, and 4194304 (4MB) for files with a 64k page size.INFORMATION_SCHEMA.FILES
does not reportInnoDB
page size. Page size is defined by theinnodb_page_size
option. Extent size information can also be retrieved fromINNODB_SYS_TABLESPACES
whereFILES.FILE_ID = INNODB_SYS_TABLESPACES.SPACE_ID
. -
INITIAL_SIZE
is the initial size of the file, in bytes. -
MAXIMUM_SIZE
is the maximum number of bytes allowed in the file. The value isNULL
for all data files except for predefined system tablespace data files. Maximum system tablespace file size is defined byinnodb_data_file_path
. Maximum temporary tablespace file size is defined byinnodb_temp_data_file_path
. ANULL
value for a predefined system tablespace data file indicates that a file size limit was not defined explicitly. -
AUTOEXTEND_SIZE
is the auto-extend size defined byinnodb_data_file_path
for the system tablespace, or defined byinnodb_temp_data_file_path
for temporary tablespaces. -
DATA_FREE
is the total amount of free space (in bytes) for the entire tablespace. Predefined system tablespaces, which include the system tablespace and temporary table tablespaces, may have one or more data files. -
STATUS
isNORMAL
by default.InnoDB
file-per-table tablespaces may reportIMPORTING
, which indicates that the tablespace is not yet available. -
The following query returns all data pertinent to
InnoDB
tablespaces.