MySQL 优化：优化 SQL 语句：优化 INFORMATION SCHEMA 查询

关于

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

INFORMATION_SCHEMA 查询中数据库名称和表名称的比较行为可能与您期望的不同。

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

可以利用以下原理：

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

此原理适用于下表中显示的 INFORMATION_SCHEMA 表，该表显示了其常量查找值使服务器能够避免目录扫描的列：

例如，如果要从 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%';

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

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

使用 Literals 表名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%';

编写查询以最小化必须打开的表文件的数量【？】

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

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

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

某些值（例如 MyISAM 表的INDEX_LENGTH）也需要打开.MYD或.MYI文件。

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

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

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

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

使用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

使用常量数据库和表查找值可使服务器避免目录扫描。对于 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_NAME 不需要再打开任何表文件（使用SKIP_OPEN_TABLE优化）。 ROW_FORMAT 要求打开所有表文件（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

没有提供表名查找值，因此服务器必须扫描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 输出行：常量数据库和表查找值使服务器可以避免对TABLES值进行目录扫描。引用 TABLES.TABLE_NAME 不需要其他表文件。

对于第二个 EXPLAIN 输出行：所有 COLUMNS 表的值都是 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_SCHEMA 表之一，因此不应用优化。

MySQL 优化：优化 SQL 语句：优化 INFORMATION SCHEMA 查询

关于

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

编写查询以最小化必须打开的表文件的数量【？】

使用EXPLAIN确定服务器是否可以对查询使用INFORMATION_SCHEMA优化【？】

导航菜单

搜索