监视数据库的应用程序可能经常使用INFORMATION_SCHEMA
表。的某些类型的查询INFORMATION_SCHEMA
可以优化表以更快地执行。目标是最小化文件操作(例如,扫描目录或打开表文件),以收集构成这些动态表的信息。
中的数据库名和表名的比较行为INFORMATION_SCHEMA
查询可能与您期望的有所不同。有关详细信息,请参见第10.8.7节“在INFORMATION_SCHEMA搜索中使用排序规则”。
中的数据库和表名使用常量查找值在哪里
条款
你可以利用这个原则如下:
要查找数据库或表,请使用求值为常量的表达式,如文字值、返回常量的函数或标量子查询。
避免使用非常量数据库名称查找值(或没有查找值)的查询,因为它们需要扫描数据目录以查找匹配的数据库目录名称。
在数据库中,避免使用非常量表名查找值(或没有查找值)的查询,因为它们需要扫描数据库目录来查找匹配的表文件。
这一原则适用于INFORMATION_SCHEMA
下表中所示的表,其中显示了常量查找值使服务器能够避免目录扫描的列。例如,如果您正在从表
,使用常量查找值TABLE_SCHEMA
在在哪里
子句允许避免数据目录扫描。
表格 | 列指定,以避免数据目录扫描 | 列指定以避免数据库目录扫描 |
---|---|---|
列 |
TABLE_SCHEMA |
TABLE_NAME |
KEY_COLUMN_USAGE |
TABLE_SCHEMA |
TABLE_NAME |
分区 |
TABLE_SCHEMA |
TABLE_NAME |
REFERENTIAL_CONSTRAINTS |
CONSTRAINT_SCHEMA |
TABLE_NAME |
统计数据 |
TABLE_SCHEMA |
TABLE_NAME |
表 |
TABLE_SCHEMA |
TABLE_NAME |
TABLE_CONSTRAINTS |
TABLE_SCHEMA |
TABLE_NAME |
触发器 |
EVENT_OBJECT_SCHEMA |
EVENT_OBJECT_TABLE |
的观点 |
TABLE_SCHEMA |
TABLE_NAME |
限制于特定常量数据库名的查询的好处是只需要对指定的数据库目录进行检查。例子:
从information_schema中选择table_name。WHERE TABLE_SCHEMA = 'test';
使用字面数据库名称测验
使服务器只检查测验
数据库目录,不管可能有多少数据库。相比之下,下面的查询效率较低,因为它需要扫描数据目录来确定哪些数据库名称与模式匹配“测试%”
:
从information_schema中选择table_name。WHERE TABLE_SCHEMA LIKE 'test%';
对于局限于特定常量表名的查询,只需要对对应数据库目录中的命名表进行检查。例子:
从information_schema中选择table_name。表在哪里TABLE_SCHEMA= 'test' AND TABLE_NAME = 't1';
字面表名的使用t1
使服务器只检查文件t1
表中,无论可能有多少个表测验
数据库。相比之下,下面的查询需要扫描测验
数据库目录,以确定哪些表名与模式匹配“t %”
:
从information_schema中选择table_name。表在哪里TABLE_SCHEMA= 'test' AND TABLE_NAME LIKE 't%';
下面的查询需要扫描数据库目录,以确定与模式匹配的数据库名称“测试%”
,对于每个匹配的数据库,它需要扫描数据库目录,以确定模式的匹配表名“t %”
:
从information_schema中选择table_name。表在哪里TABLE_SCHEMA= 'test%' AND TABLE_NAME LIKE 't%';
2)编写查询,最小化必须打开的表文件的数量
用于引用某些INFORMATION_SCHEMA
对于表列,有几种优化可以最小化必须打开的表文件的数量。例子:
选择table_name, engine from information_schema。WHERE TABLE_SCHEMA = 'test';
在这种情况下,在服务器扫描数据库目录以确定数据库中表的名称之后,这些名称就可以使用了,而无需进行进一步的文件系统查找。因此,TABLE_NAME
不需要打开任何文件。的引擎
(存储引擎)值可以通过打开表来确定.frm
文件,而不涉及其他表文件,如.MYD
或.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
表列。对于未命名的表和列,任何优化都不适用。
列
:OPEN_FRM_ONLY
适用于所有列KEY_COLUMN_USAGE
:OPEN_FULL_TABLE
适用于所有列分区
: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
排序
OPEN_FRM_ONLY
基数
OPEN_FULL_TABLE
SUB_PART
OPEN_FRM_ONLY
包装
OPEN_FRM_ONLY
可以为空
OPEN_FRM_ONLY
INDEX_TYPE
OPEN_FULL_TABLE
评论
OPEN_FRM_ONLY
表
:列 优化型 TABLE_CATALOG
SKIP_OPEN_TABLE
TABLE_SCHEMA
SKIP_OPEN_TABLE
TABLE_NAME
SKIP_OPEN_TABLE
TABLE_TYPE
OPEN_FRM_ONLY
引擎
OPEN_FRM_ONLY
版本
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
校验和
OPEN_FULL_TABLE
CREATE_OPTIONS
OPEN_FRM_ONLY
TABLE_COMMENT
OPEN_FRM_ONLY
TABLE_CONSTRAINTS
:OPEN_FULL_TABLE
适用于所有列触发器
: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
定义者
OPEN_FRM_ONLY
SECURITY_TYPE
OPEN_FRM_ONLY
CHARACTER_SET_CLIENT
OPEN_FRM_ONLY
COLLATION_CONNECTION
OPEN_FRM_ONLY
3)使用解释
来确定服务器是否可以使用INFORMATION_SCHEMA
查询的优化
这尤其适用于INFORMATION_SCHEMA
从多个数据库中搜索信息的查询,这可能需要很长时间并影响性能。的额外的
价值解释
输出指示服务器可以使用前面描述的哪些优化(如果有的话)进行评估INFORMATION_SCHEMA
查询。下面的示例演示了您可能会在额外的
价值。
mysql>解释SELECT TABLE_NAME FROM INFORMATION_SCHEMA。的观点在哪里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_NAME
,只有.frm
文件需要打开。
mysql>解释SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA。表\ G *************************** 1。row *************************** id: 1 select_type: SIMPLE table: TABLES type: ALL possible le_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Open_full_table;扫描所有数据库
没有提供查找值(没有在哪里
子句),因此服务器必须扫描数据目录和每个数据库目录。对于这样标识的每个表,将选择表名和行格式。TABLE_NAME
不需要打开更多的表文件SKIP_OPEN_TABLE
优化应用)。ROW_FORMAT
要求打开所有表文件(OPEN_FULL_TABLE
适用)。解释
报告OPEN_FULL_TABLE
因为它比SKIP_OPEN_TABLE
。
mysql>解释SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA。表在TABLE_SCHEMA = '测试' \ G *************************** 1。row *************************** id: 1 select_type: SIMPLE table: TABLES type: ALL possible le_keys: NULL key: TABLE_SCHEMA key_len: NULL ref: NULL rows: NULL Extra: Using where;Open_frm_only;扫描1数据库
没有提供表名查找值,因此服务器必须扫描测验
数据库目录。为TABLE_NAME
而且TABLE_TYPE
列,SKIP_OPEN_TABLE
而且OPEN_FRM_ONLY
分别优化应用。解释
报告OPEN_FRM_ONLY
因为它更贵。
mysql>解释SELECT B.TABLE_NAME FROM INFORMATION_SCHEMA。表作为,information_schema。列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
第一解释
输出行:常量数据库和表查找值使服务器能够避免目录扫描表
值。引用表。TABLE_NAME
不需要更多的表文件。
对于第二个解释
输出行:所有列
表值OPEN_FRM_ONLY
查找,所以列。TABLE_NAME
要求.frm
待打开的文件。
mysql>解释SELECT * FROM INFORMATION_SCHEMA。整理\ G *************************** 1。row *************************** id: 1 select_type: SIMPLE table: COLLATIONS type: ALL possible le_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL额外:
在本例中,不应用任何优化,因为整理
是不是其中之一INFORMATION_SCHEMA
可用于优化的表。