2023年3月31日 (五) 15:17 Eijux

2023-03-31T15:17:04Z

←上一版本		2023年3月31日 (五) 23:17的版本
第1行：		第1行：
	[[category:~~MySQL~~]]		[[category:MySQL文档]]

	== 关于 ==		== 关于 ==

Eijux：建立内容为“category:MySQL == 关于 == 监视数据库的应用程序可能会频繁使用 INFORMATION_SCHEMA 表。可以对 INFORMATION_SCHEMA 表的某些类型的…”的新页面

2021-04-26T02:30:02Z

建立内容为“category:MySQL == 关于 == 监视数据库的应用程序可能会频繁使用 INFORMATION_SCHEMA 表。可以对 INFORMATION_SCHEMA 表的某些类型的…”的新页面

新页面

[[category:MySQL]]

== 关于 ==
监视数据库的应用程序可能会频繁使用 INFORMATION_SCHEMA 表。可以对 INFORMATION_SCHEMA 表的某些类型的查询进行优化以更快地执行。目标是最大程度地减少文件操作（例如，扫描目录或打开表文件）以收集构成这些动态表的信息。
* INFORMATION_SCHEMA 查询中数据库名称和表名称的比较行为可能与您期望的不同。

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

此原理适用于下表中显示的 INFORMATION_SCHEMA 表，该表显示了其常量查找值使服务器能够避免目录扫描的列：
: 例如，如果要从 TABLES 选择，则在 WHERE 子句中为 TABLE_SCHEMA 使用恒定的查找值可以避免进行数据目录扫描。
{| class="wikitable"
! 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
|}

限于特定的常量数据库名称的查询的好处是只需要对命名数据库目录进行检查。例：
<syntaxhighlight lang="mysql">
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test';
</syntaxhighlight>
使用 Literals 数据库名称test可使服务器仅检查test数据库目录，而不管可能有多少个数据库。相比之下，以下查询效率较低，因为它需要扫描数据目录以确定哪些数据库名称与'test%'模式匹配：
<syntaxhighlight lang="mysql">
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'test%';
</syntaxhighlight>
对于限于特定常量表名称的查询，仅需要检查相应数据库目录中的命名表。例：
<syntaxhighlight lang="mysql">
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
</syntaxhighlight>
使用 Literals 表名t1可使服务器仅检查t1表的文件，而不管test数据库中可能有多少个表。相比之下，以下查询需要扫描test数据库目录以确定哪些表名称与模式't%'匹配：
<syntaxhighlight lang="mysql">
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't%';
</syntaxhighlight>
以下查询需要扫描数据库目录以确定模式'test%'的匹配数据库名称，并且对于每个匹配的数据库，都需要扫描数据库目录以确定模式't%'的匹配表名称：
<syntaxhighlight lang="mysql">
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test%' AND TABLE_NAME LIKE 't%';
</syntaxhighlight>

=== 编写查询以最小化必须打开的表文件的数量【？】 ===
对于引用某些 INFORMATION_SCHEMA 表列的查询，可以使用几种优化来最大程度地减少必须打开的表文件的数量。例：
<syntaxhighlight lang="mysql">
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test';
</syntaxhighlight>
在这种情况下，服务器扫描数据库目录以确定数据库中表的名称之后，这些名称将可用，而无需进行进一步的文件系统查找。因此，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：
:{| class="wikitable"
! 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：
:{| class="wikitable"
! 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：
:{| class="wikitable"
! 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值中看到的各种信息：

<syntaxhighlight lang="mysql" highlight="4,8,10-13">
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
</syntaxhighlight>
使用常量数据库和表查找值可使服务器避免目录扫描。对于 VIEWS.TABLE_NAME 的引用，仅需要打开 .frm 文件。

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

<syntaxhighlight lang="mysql" highlight="4,8,10-12">
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
</syntaxhighlight>
没有提供表名查找值，因此服务器必须扫描test数据库目录。对于 TABLE_NAME 和 TABLE_TYPE 列，分别应用 SKIP_OPEN_TABLE 和 OPEN_FRM_ONLY 优化。 EXPLAIN 报告 OPEN_FRM_ONLY，因为它更贵。

<syntaxhighlight lang="mysql">
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
</syntaxhighlight>
对于第一个 EXPLAIN 输出行：常量数据库和表查找值使服务器可以避免对TABLES值进行目录扫描。引用 TABLES.TABLE_NAME 不需要其他表文件。

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

<syntaxhighlight lang="mysql">
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:
</syntaxhighlight>
在这种情况下，由于 COLLATIONS 不是可进行优化的 INFORMATION_SCHEMA 表之一，因此不应用优化。

MySQL 优化：优化 SQL 语句：优化 INFORMATION SCHEMA 查询 - 版本历史

2023年3月31日 (五) 15:17 Eijux

Eijux：​建立内容为“category:MySQL == 关于 == 监视数据库的应用程序可能会频繁使用 INFORMATION_SCHEMA 表。可以对 INFORMATION_SCHEMA 表的某些类型的…”的新页面

Eijux：建立内容为“category:MySQL == 关于 == 监视数据库的应用程序可能会频繁使用 INFORMATION_SCHEMA 表。可以对 INFORMATION_SCHEMA 表的某些类型的…”的新页面