8.2.3 优化 INFORMATION_SCHEMA 查询

监视数据库的应用程序可能会频繁使用INFORMATION_SCHEMAtable。可以对INFORMATION_SCHEMAtable 的某些类型的查询进行优化以更快地执行。目标是最大程度地减少文件操作(例如，扫描目录或打开 table 文件)以收集构成这些动态 table 的信息。

Note

INFORMATION_SCHEMA查询中数据库名称和 table 名称的比较行为可能与您期望的不同。有关详细信息，请参见第 10.8.7 节“在 INFORMATION_SCHEMA 搜索中使用归类”。

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

您可以利用以下原理：

要查找数据库或 table，请使用计算结果为常量的 table 达式，例如 Literals 值，返回常量的函数或标量子查询。
避免使用非恒定数据库名称查找值(或不使用查找值)的查询，因为它们需要扫描数据目录才能找到匹配的数据库目录名称。
在数据库内，请避免使用非恒定 table 名查找值(或不使用查找值)的查询，因为它们需要扫描数据库目录以查找匹配的 table 文件。

此原理适用于下 table 中显示的INFORMATION_SCHEMAtable，该 table 显示了其常量查找值使服务器能够避免目录扫描的列。例如，如果要从TABLES选择，则在WHERE子句中为TABLE_SCHEMA使用恒定的查找值可以避免进行数据目录扫描。

Table	指定要避免数据目录扫描的列	指定要避免数据库目录扫描的列
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';

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

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

对于限于特定常量 table 名称的查询，仅需要检查相应数据库目录中的命名 table。例：

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

使用 Literalstable 名t1可使服务器仅检查t1table 的文件，而不管test数据库中可能有多少个 table。相比之下，以下查询需要扫描test数据库目录以确定哪些 table 名称与模式't%'匹配：

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

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

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

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

对于引用某些INFORMATION_SCHEMAtable 列的查询，可以使用几种优化来最大程度地减少必须打开的 table 文件的数量。例：

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

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

某些值(例如MyISAMtable 的INDEX_LENGTH)也需要打开.MYD或.MYI文件。

文件打开优化类型 table 示为：

SKIP_OPEN_TABLE：不需要打开 table 文件。通过扫描数据库目录，该信息已在查询中可用。
OPEN_FRM_ONLY：仅需要打开 table 的.frm文件。
OPEN_TRIGGER_ONLY：仅需要打开 table 的.TRG文件。
OPEN_FULL_TABLE：未优化的信息查找。必须打开.frm，.MYD和.MYI文件。

以下列 table 指示了前面的优化类型如何应用于INFORMATION_SCHEMAtable 列。对于未命名的 table 和列，没有任何优化适用。

COLUMNS：OPEN_FRM_ONLY适用于所有列
KEY_COLUMN_USAGE：OPEN_FULL_TABLE适用于所有列
PARTITIONS：OPEN_FULL_TABLE适用于所有列
REFERENTIAL_CONSTRAINTS：OPEN_FULL_TABLE适用于所有列
STATISTICS:

Column	Optimization type
`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:

Column	Optimization type
`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_CONSTRAINTS：OPEN_FULL_TABLE适用于所有列
TRIGGERS：OPEN_TRIGGER_ONLY适用于所有列
VIEWS:

Column	Optimization type
`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查询，这可能会花费很长时间并影响性能。 EXPLAIN输出中的Extra值指示服务器可以使用哪些较早描述的优化来评估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

使用常量数据库和 table 查找值可使服务器避免目录扫描。对于VIEWS.TABLE_NAME的引用，仅需要打开.frm文件。

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

没有提供查找值(没有WHERE子句)，因此服务器必须扫描数据目录和每个数据库目录。对于这样标识的每个 table，选择 table 名和行格式。 TABLE_NAME不需要再打开任何 table 文件(使用SKIP_OPEN_TABLE优化)。 ROW_FORMAT要求打开所有 table 文件(OPEN_FULL_TABLE适用)。 EXPLAIN报告OPEN_FULL_TABLE，因为它比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

没有提供 table 名查找值，因此服务器必须扫描test数据库目录。对于TABLE_NAME和TABLE_TYPE列，分别应用SKIP_OPEN_TABLE和OPEN_FRM_ONLY优化。 EXPLAIN报告OPEN_FRM_ONLY，因为它更贵。

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

对于第一个EXPLAIN输出行：常量数据库和 table 查找值使服务器可以避免对TABLES值进行目录扫描。引用TABLES.TABLE_NAME不需要其他 table 文件。

对于第二个EXPLAIN输出行：所有COLUMNStable 的值都是OPEN_FRM_ONLY查找，因此COLUMNS.TABLE_NAME需要打开.frm文件。

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_SCHEMAtable 之一，因此不应用优化。

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35