文档首页
MySQL 8.4 参考手册
8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 74%

28.3.15 INFORMATION_SCHEMA FILES 表28.3.15 The INFORMATION_SCHEMA FILES Table

FILES 表提供有关 MySQL 表空间数据存储文件的信息。The FILES table provides information about the files in which MySQL tablespace data is stored.

FILES 表提供有关 InnoDB 数据文件的信息。在 NDB Cluster 中，该表还提供有关存储 NDB Cluster 磁盘表的文件的信息。有关 InnoDB 的更多信息，请参见本节稍后的 InnoDB 说明；有关 NDB Cluster 的更多信息，请参见 NDB 说明。The FILES table 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 additional information specific to InnoDB, see InnoDB Notes, later in this section; for additional information specific to NDB Cluster, see NDB Notes.

FILES 表具有以下列：The FILES table has these columns:

  • FILE_ID

    对于 InnoDB：表空间 ID，也称为 space_id 或 fil_space_t::id。For InnoDB: The tablespace ID, also referred to as the space_id or fil_space_t::id.

    对于 NDB：文件标识符。FILE_ID 列的值是自动生成的。For NDB: A file identifier. FILE_ID column values are auto-generated.

  • FILE_NAME

    对于 InnoDB：数据文件的名称。每表一个文件和通用表空间的文件名扩展名为 .ibd。撤销表空间前缀为 undo。系统表空间前缀为 ibdata。全局临时表空间前缀为 ibtmp。文件名包括文件路径，该路径可以相对于 MySQL 数据目录（datadir 系统变量的值）来指定。For InnoDB: The name of the data file. File-per-table and general tablespaces have an .ibd file name extension. Undo tablespaces are prefixed by undo. The system tablespace is prefixed by ibdata. The global temporary tablespace is prefixed by ibtmp. The file name includes the file path, which may be relative to the MySQL data directory (the value of the datadir system variable).

    对于 NDB：由 CREATE LOGFILE GROUP 或 ALTER LOGFILE GROUP 创建的撤消日志文件的名称，或由 CREATE TABLESPACE 或 ALTER TABLESPACE 创建的数据文件的名称。在 NDB 8.4 中，文件名显示为相对路径；对于撤消日志文件，该路径相对于目录 DataDir/ndb_NodeId_fs/LG；对于数据文件，其相对于目录 DataDir/ndb_NodeId_fs/TS。这意味着，例如，使用 ALTER TABLESPACE ts ADD DATAFILE 'data_2.dat' INITIAL SIZE 256M 创建的数据文件名称将显示为 ./data_2.dat。For NDB: The name of an undo log file created by CREATE LOGFILE GROUP or ALTER LOGFILE GROUP, or of a data file created by CREATE TABLESPACE or ALTER TABLESPACE. In NDB 8.4, the file name is shown with a relative path; for an undo log file, this path is relative to the directory DataDir/ndb_NodeId_fs/LG; for a data file, it is relative to the directory DataDir/ndb_NodeId_fs/TS. This means, for example, that the name of a data file created with ALTER TABLESPACE ts ADD DATAFILE 'data_2.dat' INITIAL SIZE 256M is shown as ./data_2.dat.

  • FILE_TYPE

    对于 InnoDB：表空间文件类型。InnoDB 文件有三种可能的文件类型。TABLESPACE 是用于任何系统、通用或每表文件表空间文件的文件类型，这些文件包含表、索引或其他形式的用户数据。TEMPORARY 是临时表空间的文件类型。UNDO LOG 是撤销表空间的文件类型，用于保存撤销记录。For InnoDB: The tablespace file type. There are three possible file types for InnoDB 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.

    对于 NDB：值为 UNDO LOG 或 DATAFILE 之一。For NDB: One of the values UNDO LOG or DATAFILE.

  • TABLESPACE_NAME

    与该文件关联的表空间名称。The name of the tablespace with which the file is associated.

    对于InnoDB：通用表空间名称在创建时指定。每表独立表空间名称显示如下格式：schema_name/table_name。InnoDB系统表空间名称为innodb_system。全局临时表空间名称为innodb_temporary。默认撤销表空间名称为innodb_undo_001和innodb_undo_002。用户创建的撤销表空间名称在创建时指定。For InnoDB: General tablespace names are as specified when created. File-per-table tablespace names are shown in the following format: schema_name/table_name. The InnoDB system tablespace name is innodb_system. The global temporary tablespace name is innodb_temporary. Default undo tablespace names are innodb_undo_001 and innodb_undo_002. User-created undo tablespace names are as specified when created.

  • TABLE_CATALOG

    此值总是为空。This value is always empty.

  • TABLE_SCHEMA

    这总是NULL。This is always NULL.

  • TABLE_NAMETABLE_NAME

    这总是NULL。This is always NULL.

  • LOGFILE_GROUP_NAME

    对于InnoDB：这总是NULL。For InnoDB: This is always NULL.

    对于 NDB：日志文件或数据文件所属的日志文件组的名称。For NDB: The name of the log file group to which the log file or data file belongs.

  • LOGFILE_GROUP_NUMBER

    对于InnoDB：这总是NULL。For InnoDB: This is always NULL.

    表，如果没有任何可变长度列（NDB: For a Disk Data undo log file, the auto-generated ID number of the log file group to which the log file belongs. This is the same as the value shown for the id column in the ndbinfo.dict_obj_info table and the log_id column in the ndbinfo.logspaces以及ndbinfo.logspaces tables for this undo log file.For NDB: For a Disk Data undo log file, the auto-generated ID number of the log file group to which the log file belongs. This is the same as the value shown for the id column in the ndbinfo.dict_obj_info table and the log_id column in the ndbinfo.logspaces and ndbinfo.logspaces tables for this undo log file.

  • ENGINEENGINE

    对于 InnoDB：此值始终为 InnoDB。For InnoDB: This value is always InnoDB.

    对于 NDB：该值始终为 ndbcluster。For NDB: This value is always ndbcluster.

  • FULLTEXT_KEYS

    这总是NULL。This is always NULL.

  • DELETED_ROWS

    这总是NULL。This is always NULL.

  • UPDATE_COUNT

    这总是NULL。This is always NULL.

  • FREE_EXTENTS

    对于 InnoDB：当前数据文件中完全空闲的区段数量。For InnoDB: The number of fully free extents in the current data file.

    对于 NDB：尚未被文件使用的区段数量。For NDB: The number of extents which have not yet been used by the file.

  • TOTAL_EXTENTS

    对于 InnoDB：当前数据文件中使用的完整区段数量。文件末尾的任何部分区段不计算在内。For InnoDB: The number of full extents used in the current data file. Any partial extent at the end of the file is not counted.

    对于 NDB：分配给文件的总区段数量。For NDB: The total number of extents allocated to the file.

  • EXTENT_SIZEEXTENT_SIZE

    对于 InnoDB：对于页面大小为 4KB、8KB 或 16KB 的文件，区段大小为 1048576（1MB）。对于页面大小为 32KB 的文件，区段大小为 2097152 字节（2MB），对于页面大小为 64KB 的文件，区段大小为 4194304（4MB）。FILES 不报告 InnoDB 页面大小。页面大小由 innodb_page_size 系统变量定义。区段大小信息也可以从 INNODB_TABLESPACES 表中获取，其中 FILES.FILE_ID = INNODB_TABLESPACES.SPACE。For InnoDB: Extent size is 1048576 (1MB) for files with a 4KB, 8KB, or 16KB page size. Extent size is 2097152 bytes (2MB) for files with a 32KB page size, and 4194304 (4MB) for files with a 64KB page size. FILES does not report InnoDB page size. Page size is defined by the innodb_page_size system variable. Extent size information can also be retrieved from the INNODB_TABLESPACES table where FILES.FILE_ID = INNODB_TABLESPACES.SPACE.

    对于 NDB：文件的扩展大小（以字节为单位）。For NDB: The size of an extent for the file in bytes.

  • INITIAL_SIZEINITIAL_SIZE

    对于 InnoDB：文件的初始大小（以字节为单位）。For InnoDB: The initial size of the file in bytes.

    表，如果没有任何可变长度列（NDB: The size of the file in bytes. This is the same value that was used in the INITIAL_SIZE子句的CREATE LOGFILE GROUP, ALTER LOGFILE GROUP, CREATE TABLESPACE，或ALTER TABLESPACE statement used to create the file.For NDB: The size of the file in bytes. This is the same value that was used in the INITIAL_SIZE clause of the CREATE LOGFILE GROUP, ALTER LOGFILE GROUP, CREATE TABLESPACE, or ALTER TABLESPACE statement used to create the file.

  • MAXIMUM_SIZE

    对于 InnoDB：文件允许的最大字节数。对于除预定义系统表空间数据文件之外的所有数据文件，该值为 NULL。最大系统表空间文件大小由 innodb_data_file_path 定义。最大全局临时表空间文件大小由 innodb_temp_data_file_path 定义。预定义系统表空间数据文件的 NULL 值表示未明确定义文件大小限制。For InnoDB: The maximum number of bytes permitted in the file. The value is NULL for all data files except for predefined system tablespace data files. Maximum system tablespace file size is defined by innodb_data_file_path. Maximum global temporary tablespace file size is defined by innodb_temp_data_file_path. A NULL value for a predefined system tablespace data file indicates that a file size limit was not defined explicitly.

    对于 NDB：此值始终与 INITIAL_SIZE 值相同。For NDB: This value is always the same as the INITIAL_SIZE value.

  • AUTOEXTEND_SIZE

    表空间的自动扩展大小。对于 NDB，AUTOEXTEND_SIZE 始终为 NULL。The auto-extend size of the tablespace. For NDB, AUTOEXTEND_SIZE is always NULL.

  • CREATION_TIME

    这总是NULL。This is always NULL.

  • LAST_UPDATE_TIME

    这总是NULL。This is always NULL.

  • LAST_ACCESS_TIME

    这总是NULL。This is always NULL.

  • RECOVER_TIME

    这总是NULL。This is always NULL.

  • TRANSACTION_COUNTER

    这总是NULL。This is always NULL.

  • VERSION

    对于InnoDB：这总是NULL。For InnoDB: This is always NULL.

    对于 NDB：文件的版本号。For NDB: The version number of the file.

  • ROW_FORMATROW_FORMAT

    对于InnoDB：这总是NULL。For InnoDB: This is always NULL.

    对于 NDB：FIXED 或 DYNAMIC 之一。For NDB: One of FIXED or DYNAMIC.

  • TABLE_ROWS

    这总是NULL。This is always NULL.

  • AVG_ROW_LENGTH

    这总是NULL。This is always NULL.

  • DATA_LENGTH

    这总是NULL。This is always NULL.

  • MAX_DATA_LENGTH

    这总是NULL。This is always NULL.

  • INDEX_LENGTH

    这总是NULL。This is always NULL.

  • DATA_FREE

    对于 InnoDB：整个表空间的总空闲空间（以字节为单位）。预定义的系统表空间，包括系统表空间和临时表表空间，可能有一个或多个数据文件。For InnoDB: 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.

    对于 NDB：该值始终为 NULL。For NDB: This is always NULL.

  • CREATE_TIME

    这总是NULL。This is always NULL.

  • UPDATE_TIME

    这总是NULL。This is always NULL.

  • CHECK_TIME

    这总是NULL。This is always NULL.

  • CHECKSUMCHECKSUM

    这总是NULL。This is always NULL.

  • STATUSSTATUS

    对于 InnoDB：此值默认是 NORMAL。InnoDB 每表文件表空间可能报告 IMPORTING，表示该表空间尚不可用。For InnoDB: This value is NORMAL by default. InnoDB file-per-table tablespaces may report IMPORTING, which indicates that the tablespace is not yet available.

    对于 NDB：对于 NDB 集群磁盘数据文件，此值始终为 NORMAL。For NDB: For NDB Cluster Disk Data files, this value is always NORMAL.

  • EXTRA

    对于InnoDB：这总是NULL。For InnoDB: This is always NULL.

    对于 NDB：对于 undo 日志文件，此列显示 undo 日志缓冲区大小；对于数据文件，它始终为 NULL。在接下来的几段中将提供更详细的解释。For NDB: For undo log files, this column shows the undo log buffer size; for data files, it is always NULL. A more detailed explanation is provided in the next few paragraphs.

    NDB 在集群中的每个数据节点上存储每个数据文件和每个 undo 日志文件的副本。FILES 表为每个此类文件包含一行。假设您在一个拥有四个数据节点的 NDB 集群上运行以下两个语句：NDB stores a copy of each data file and each undo log file on each data node in the cluster. The FILES table contains one row for each such file. Suppose that you run the following two statements on an NDB Cluster with four data nodes:

    CREATE LOGFILE GROUP mygroup
    ADD UNDOFILE 'new_undo.dat'
    INITIAL_SIZE 2G
    ENGINE NDBCLUSTER;

CREATE TABLESPACE myts
    ADD DATAFILE 'data_1.dat'
    USE LOGFILE GROUP mygroup
    INITIAL_SIZE 256M
    ENGINE NDBCLUSTER;

    在成功运行这两个语句后，您应该会看到与针对 FILES 表的查询结果类似的结果：After running these two statements successfully, you should see a result similar to the one shown here for this query against the FILES table:

    mysql> SELECT LOGFILE_GROUP_NAME, FILE_TYPE, EXTRA
    ->     FROM INFORMATION_SCHEMA.FILES
    ->     WHERE ENGINE = 'ndbcluster';

+--------------------+-----------+--------------------------+
| LOGFILE_GROUP_NAME | FILE_TYPE | EXTRA                    |
+--------------------+-----------+--------------------------+
| mygroup            | UNDO LOG  | UNDO_BUFFER_SIZE=8388608 |
| mygroup            | DATAFILE  | NULL                     |
+--------------------+-----------+--------------------------+

说明Notes

  • FILES 是一个非标准的 INFORMATION_SCHEMA 表。FILES is a nonstandard INFORMATION_SCHEMA table.

  • 你必须拥有 PROCESS 权限才能查询此表。You must have the PROCESS privilege to query this table.

InnoDB 说明InnoDB Notes

以下说明适用于 InnoDB 数据文件。The following notes apply to InnoDB data files.

  • FILES 报告的信息是从 InnoDB 的内存缓存中获取的，而 INNODB_DATAFILES 的数据则来自 InnoDB 的 SYS_DATAFILES 内部数据字典表。Information reported by FILES is obtained from the InnoDB in-memory cache for open files, whereas INNODB_DATAFILES gets its data from the InnoDB SYS_DATAFILES internal data dictionary table.

  • FILES 提供的信息包括全局临时表空间的信息，这些信息在 InnoDB 的 SYS_DATAFILES 内部数据字典表中不可用，因此未包含在 INNODB_DATAFILES 中。The information provided by FILES includes global temporary tablespace information which is not available in the InnoDB SYS_DATAFILES internal data dictionary table, and is therefore not included in INNODB_DATAFILES.

  • 当存在独立的撤销表空间时（在 MySQL 8.4 中默认如此），撤销表空间信息会显示在 FILES 中。Undo tablespace information is shown in FILES when separate undo tablespaces are present, as they are by default in MySQL 8.4.

  • 下面的查询返回与 InnoDB 表空间相关的所有 FILES 表信息。The following query returns all FILES table information relating to InnoDB tablespaces.

    SELECT
  FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS,
  TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE,
  AUTOEXTEND_SIZE, DATA_FREE, STATUS
FROM INFORMATION_SCHEMA.FILES
WHERE ENGINE='InnoDB'\G

NDB 说明NDB Notes

  • FILES 表仅提供关于磁盘数据 文件 的信息；你不能用它来确定单个 NDB 表的磁盘空间分配或可用性。然而，可以使用 ndb_desc 查看存储在磁盘上的每个 NDB 表分配了多少空间，以及该表在磁盘上用于存储数据的可用空间还剩多少。The FILES table provides information about Disk Data files only; you cannot use it for determining disk space allocation or availability for individual NDB tables. However, it is possible to see how much space is allocated for each NDB table having data stored on disk—as well as how much remains available for storage of data on disk for that table—using ndb_desc.

  • FILES 表中包含的大部分信息也可以在 ndbinfo files 表中找到。Much of the information contained in the FILES table can also be found in the ndbinfo files table.

  • CREATION_TIME、LAST_UPDATE_TIME 和 LAST_ACCESSED 的值是由操作系统报告的，而不是由 NDB 存储引擎提供的。如果操作系统未提供值，这些列显示 NULL。The CREATION_TIME, LAST_UPDATE_TIME, and LAST_ACCESSED values are as reported by the operating system, and are not supplied by the NDB storage engine. Where no value is provided by the operating system, these columns display NULL.

  • TOTAL EXTENTS 列和 FREE_EXTENTS 列之间的差值是文件当前正在使用的区段数：The difference between the TOTAL EXTENTS and FREE_EXTENTS columns is the number of extents currently in use by the file:

    SELECT TOTAL_EXTENTS - FREE_EXTENTS AS extents_used
    FROM INFORMATION_SCHEMA.FILES
    WHERE FILE_NAME = './myfile.dat';

    要近似计算文件使用的磁盘空间量，可以将该差值乘以 EXTENT_SIZE 列的值，该值表示文件中一个区段的大小（以字节为单位）：To approximate the amount of disk space in use by the file, multiply that difference by the value of the EXTENT_SIZE column, which gives the size of an extent for the file in bytes:

    SELECT (TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE AS bytes_used
    FROM INFORMATION_SCHEMA.FILES
    WHERE FILE_NAME = './myfile.dat';

    类似地，你可以通过将 FREE_EXTENTS 乘以 EXTENT_SIZE 来估算给定文件中剩余的可用空间：Similarly, you can estimate the amount of space that remains available in a given file by multiplying FREE_EXTENTS by EXTENT_SIZE:

    SELECT FREE_EXTENTS * EXTENT_SIZE AS bytes_free
    FROM INFORMATION_SCHEMA.FILES
    WHERE FILE_NAME = './myfile.dat';
    Important

    前述查询产生的字节值只是近似值，其精度与 EXTENT_SIZE 的值成反比。也就是说，EXTENT_SIZE 值越大，近似值的准确性越低。The byte values produced by the preceding queries are approximations only, and their precision is inversely proportional to the value of EXTENT_SIZE. That is, the larger EXTENT_SIZE becomes, the less accurate the approximations are.

    同样重要的是要记住，一旦一个扩展被使用，就不能在不删除其所属数据文件的情况下再次释放。这意味着从磁盘表中删除数据不会释放磁盘空间。It is also important to remember that once an extent is used, it cannot be freed again without dropping the data file of which it is a part. This means that deletes from a Disk Data table do not release disk space.

    扩展大小可以在CREATE TABLESPACE语句中设置。欲了解更多信息，请参见第 15.1.21 节，“CREATE TABLESPACE 语句”。The extent size can be set in a CREATE TABLESPACE statement. For more information, see Section 15.1.21, “CREATE TABLESPACE Statement”.

  • 您可以使用 ndb_desc 工具获取有关磁盘表空间的信息。有关更多信息，请参见 第 25.6.11.1 节，“NDB 集群磁盘数据对象”，以及 ndb_desc 的描述。You can obtain information about Disk Data tablespaces using the ndb_desc utility. For more information, see Section 25.6.11.1, “NDB Cluster Disk Data Objects”, as well as the description of ndb_desc.

  • 有关更多信息以及创建、删除和获取 NDB 集群磁盘数据对象信息的示例，请参见 第 25.6.11 节，“NDB 集群磁盘表”。For additional information, and examples of creating, dropping, and obtaining information about NDB Cluster Disk Data objects, see Section 25.6.11, “NDB Cluster Disk Data Tables”.

首页   上一级   下一节
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 40.2Mb
PDF (A4) - 40.3Mb
Man Pages (TGZ) - 261.9Kb
Man Pages (Zip) - 367.4Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb