监控数据库的应用程序可能会频繁使用
INFORMATION_SCHEMA
表。INFORMATION_SCHEMA
可以优化某些类型的表查询以更快地执行。目标是尽量减少文件操作(例如,扫描目录或打开表文件)以收集构成这些动态表的信息。
查询中数据库和表名称的比较行为
INFORMATION_SCHEMA
可能与您的预期不同。有关详细信息,请参阅
第 10.8.7 节,“在 INFORMATION_SCHEMA 搜索中使用排序规则”。
1)尝试在WHERE
子句中对数据库和表名使用常量查找值
您可以按如下方式利用此原则:
要查找数据库或表,请使用计算结果为常量的表达式,例如文字值、返回常量的函数或标量子查询。
避免使用非常量数据库名称查找值(或无查找值)的查询,因为它们需要扫描数据目录才能找到匹配的数据库目录名称。
在数据库中,避免使用非常量表名查找值(或无查找值)的查询,因为它们需要扫描数据库目录才能找到匹配的表文件。
此原则适用于
INFORMATION_SCHEMA
下表中显示的表,该表显示了常量查找值使服务器能够避免目录扫描的列。例如,如果您选择 from
,则在
子句中TABLES
使用常量查找值 for可以避免数据目录扫描。
TABLE_SCHEMA
WHERE
桌子 | 要指定的列以避免数据目录扫描 | 要指定的列以避免数据库目录扫描 |
---|---|---|
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
文件来确定,而无需触及其他表文件,例如.MYD
or
.MYI
文件。
某些值(例如INDEX_LENGTH
表
MyISAM
)也需要打开
.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
适用于所有列-
柱子 优化类型 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
-
柱子 优化类型 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
适用于所有列-
柱子 优化类型 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
于从多个数据库中搜索信息的查询,这可能需要很长时间并影响性能。输出中的Extra
值EXPLAIN
指示服务器可以使用前面描述的哪些优化(如果有)来评估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
进行优化的表之一。