Documentation Home
MySQL 8.0 参考手册  / 第8章优化  / 8.2 优化SQL语句  /  8.2.3 优化 INFORMATION_SCHEMA 查询

8.2.3 优化 INFORMATION_SCHEMA 查询

监控数据库的应用程序可能会频繁使用 INFORMATION_SCHEMA表。INFORMATION_SCHEMA可以优化某些类型的表查询以更快地执行。目标是尽量减少文件操作(例如,扫描目录或打开表文件)以收集构成这些动态表的信息。

笔记

查询中数据库和表名称的比较行为 INFORMATION_SCHEMA可能与您的预期不同。有关详细信息,请参阅 第 10.8.7 节,“在 INFORMATION_SCHEMA 搜索中使用排序规则”

1)尝试在WHERE 子句中对数据库和表名使用常量查找值

您可以按如下方式利用此原则:

  • 要查找数据库或表,请使用计算结果为常量的表达式,例如文字值、返回常量的函数或标量子查询。

  • 避免使用非常量数据库名称查找值(或无查找值)的查询,因为它们需要扫描数据目录才能找到匹配的数据库目录名称。

  • 在数据库中,避免使用非常量表名查找值(或无查找值)的查询,因为它们需要扫描数据库目录才能找到匹配的表文件。

此原则适用于 INFORMATION_SCHEMA下表中显示的表,该表显示了常量查找值使服务器能够避免目录扫描的列。例如,如果您选择 from ,则在 子句中TABLES使用常量查找值 for可以避免数据目录扫描。 TABLE_SCHEMAWHERE

桌子 要指定的列以避免数据目录扫描 要指定的列以避免数据库目录扫描
COLUMNS TABLE_SCHEMA TABLE_NAME
KEY_COLUMN_USAGE TABLE_SCHEMA TABLE_NAME
PARTITIONS TABLE_SCHEMA TABLE_NAME
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA TABLE_NAME
STATISTICS TABLE_SCHEMA TABLE_NAME
TABLES TABLE_SCHEMA TABLE_NAME
TABLE_CONSTRAINTS TABLE_SCHEMA TABLE_NAME
TRIGGERS EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE
VIEWS TABLE_SCHEMA TABLE_NAME

仅限于特定常量数据库名称的查询的好处是只需对指定的数据库目录进行检查。例子:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test';

使用文字数据库名称test使服务器能够只检查test数据库目录,而不管可能有多少个数据库。相比之下,以下查询效率较低,因为它需要扫描数据目录以确定哪些数据库名称与模式匹配'test%'

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'test%';

对于仅限于特定常量表名称的查询,只需对相应数据库目录中的命名表进行检查。例子:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';

使用文字表名t1使服务器能够只检查t1 表的文件,而不管数据库中可能有多少表 test。相比之下,以下查询需要扫描test数据库目录以确定哪些表名与模式匹配 't%'

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't%';

以下查询需要扫描数据库目录以确定模式的匹配数据库名称 'test%',并且对于每个匹配的数据库,它需要扫描数据库目录以确定模式的匹配表名称't%'

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test%' AND TABLE_NAME LIKE 't%';

2) 编写查询以最小化必须打开的表文件的数量

对于引用某些 INFORMATION_SCHEMA表列的查询,可以使用多种优化来最大程度地减少必须打开的表文件的数量。例子:

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test';

在这种情况下,在服务器扫描数据库目录以确定数据库中表的名称之后,这些名称变得可用而无需进一步的文件系统查找。因此,TABLE_NAME不需要打开任何文件。(ENGINE存储引擎)值可以通过打开表的 .frm文件来确定,而无需触及其他表文件,例如.MYDor .MYI文件。

某些值(例如INDEX_LENGTHMyISAM)也需要打开 .MYD.MYI文件。

文件打开优化类型表示如下:

  • SKIP_OPEN_TABLE:表文件不需要打开。通过扫描数据库目录,该信息已在查询中变得可用。

  • OPEN_FRM_ONLY.frm:只需要打开 表的 文件。

  • OPEN_TRIGGER_ONLY.TRG:只需要打开 表的 文件。

  • OPEN_FULL_TABLE:未优化的信息查找。、.frm和 文件必须打开 .MYD.MYI

以下列表说明了前面的优化类型如何应用于INFORMATION_SCHEMA表列。对于未命名的表和列,没有任何优化适用。

  • COLUMNSOPEN_FRM_ONLY适用于所有列

  • KEY_COLUMN_USAGEOPEN_FULL_TABLE适用于所有列

  • PARTITIONSOPEN_FULL_TABLE适用于所有列

  • REFERENTIAL_CONSTRAINTSOPEN_FULL_TABLE适用于所有列

  • STATISTICS:

    柱子 优化类型
    TABLE_CATALOG OPEN_FRM_ONLY
    TABLE_SCHEMA OPEN_FRM_ONLY
    TABLE_NAME OPEN_FRM_ONLY
    NON_UNIQUE OPEN_FRM_ONLY
    INDEX_SCHEMA OPEN_FRM_ONLY
    INDEX_NAME OPEN_FRM_ONLY
    SEQ_IN_INDEX OPEN_FRM_ONLY
    COLUMN_NAME OPEN_FRM_ONLY
    COLLATION OPEN_FRM_ONLY
    CARDINALITY OPEN_FULL_TABLE
    SUB_PART OPEN_FRM_ONLY
    PACKED OPEN_FRM_ONLY
    NULLABLE OPEN_FRM_ONLY
    INDEX_TYPE OPEN_FULL_TABLE
    COMMENT OPEN_FRM_ONLY
  • TABLES:

    柱子 优化类型
    TABLE_CATALOG SKIP_OPEN_TABLE
    TABLE_SCHEMA SKIP_OPEN_TABLE
    TABLE_NAME SKIP_OPEN_TABLE
    TABLE_TYPE OPEN_FRM_ONLY
    ENGINE OPEN_FRM_ONLY
    VERSION OPEN_FRM_ONLY
    ROW_FORMAT OPEN_FULL_TABLE
    TABLE_ROWS OPEN_FULL_TABLE
    AVG_ROW_LENGTH OPEN_FULL_TABLE
    DATA_LENGTH OPEN_FULL_TABLE
    MAX_DATA_LENGTH OPEN_FULL_TABLE
    INDEX_LENGTH OPEN_FULL_TABLE
    DATA_FREE OPEN_FULL_TABLE
    AUTO_INCREMENT OPEN_FULL_TABLE
    CREATE_TIME OPEN_FULL_TABLE
    UPDATE_TIME OPEN_FULL_TABLE
    CHECK_TIME OPEN_FULL_TABLE
    TABLE_COLLATION OPEN_FRM_ONLY
    CHECKSUM OPEN_FULL_TABLE
    CREATE_OPTIONS OPEN_FRM_ONLY
    TABLE_COMMENT OPEN_FRM_ONLY
  • TABLE_CONSTRAINTSOPEN_FULL_TABLE适用于所有列

  • TRIGGERSOPEN_TRIGGER_ONLY适用于所有列

  • VIEWS:

    柱子 优化类型
    TABLE_CATALOG OPEN_FRM_ONLY
    TABLE_SCHEMA OPEN_FRM_ONLY
    TABLE_NAME OPEN_FRM_ONLY
    VIEW_DEFINITION OPEN_FRM_ONLY
    CHECK_OPTION OPEN_FRM_ONLY
    IS_UPDATABLE OPEN_FULL_TABLE
    DEFINER OPEN_FRM_ONLY
    SECURITY_TYPE OPEN_FRM_ONLY
    CHARACTER_SET_CLIENT OPEN_FRM_ONLY
    COLLATION_CONNECTION OPEN_FRM_ONLY

3) EXPLAIN用于确定服务器是否可以INFORMATION_SCHEMA 对查询使用优化

这尤其适用 INFORMATION_SCHEMA于从多个数据库中搜索信息的查询,这可能需要很长时间并影响性能。输出中的ExtraEXPLAIN指示服务器可以使用前面描述的哪些优化(如果有)来评估INFORMATION_SCHEMA 查询。Extra以下示例演示了您可以在值 中看到的信息类型 。

mysql> EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE
       TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: VIEWS
         type: ALL
possible_keys: NULL
          key: TABLE_SCHEMA,TABLE_NAME
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Open_frm_only; Scanned 0 databases

Use of constant database and table lookup values enables the server to avoid directory scans. For references to VIEWS.TABLE_NAME, only the .frm file need be opened.

mysql> EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLES
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Open_full_table; Scanned all databases

No lookup values are provided (there is no WHERE clause), so the server must scan the data directory and each database directory. For each table thus identified, the table name and row format are selected. TABLE_NAME requires no further table files to be opened (the SKIP_OPEN_TABLE optimization applies). ROW_FORMAT requires all table files to be opened (OPEN_FULL_TABLE applies). EXPLAIN reports OPEN_FULL_TABLE because it is more expensive than SKIP_OPEN_TABLE.

mysql> EXPLAIN SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_SCHEMA = 'test'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLES
         type: ALL
possible_keys: NULL
          key: TABLE_SCHEMA
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Open_frm_only; Scanned 1 database

No table name lookup value is provided, so the server must scan the test database directory. For the TABLE_NAME and TABLE_TYPE columns, the SKIP_OPEN_TABLE and OPEN_FRM_ONLY optimizations apply, respectively. EXPLAIN reports OPEN_FRM_ONLY because it is more expensive.

mysql> EXPLAIN SELECT B.TABLE_NAME
       FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B
       WHERE A.TABLE_SCHEMA = 'test'
       AND A.TABLE_NAME = 't1'
       AND B.TABLE_NAME = A.TABLE_NAME\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: A
         type: ALL
possible_keys: NULL
          key: TABLE_SCHEMA,TABLE_NAME
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Skip_open_table; Scanned 0 databases
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: B
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Open_frm_only; Scanned all databases;
               Using join buffer

For the first EXPLAIN output row: Constant database and table lookup values enable the server to avoid directory scans for TABLES values. References to TABLES.TABLE_NAME require no further table files.

For the second EXPLAIN output row: All COLUMNS table values are OPEN_FRM_ONLY lookups, so COLUMNS.TABLE_NAME requires the .frm file to be opened.

mysql> EXPLAIN SELECT * FROM INFORMATION_SCHEMA.COLLATIONS\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: COLLATIONS
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:

在这种情况下,没有应用任何优化,因为 COLLATIONS它不是可以 INFORMATION_SCHEMA进行优化的表之一。