8.2.4. Optimizing INFORMATION_SCHEMA Queries

    监测数据库的应用程序可以频繁使用INFORMATION_SCHEMA表。某些类型的关于INFORMATION_SCHEMA表的查询可以被优化以更迅速地执行。该目标是尽量减少文件操作（例如，扫描的目录或打开一个文件表）来收集，弥补这些动态表中的信息。这些优化做好对如何排序的效果 被用于在INFORMATION_SCHEMA表搜索。更多信息，相见 Section 10.1.7.9,“Collation and INFORMATION_SCHEMA Searches”。

1） Try to use constant lookup values for database and table names in the WHERE clause（尝试使用WHERE子句中的数据库和表名不变查找值）

    可以利用这个原理如下：

• 要查找数据库或表， 使用计算表达式为一个常数，如文字值，返回一个恒定的功能，或标量子查询。
  
  
• 避免使用非恒定数据库名称查找值（或没有查找值），因为他们需要的数据目录的扫描以查找匹配的数据库目录名称的查询。
  
  
• 在数据库中，避免使用非恒定表名称查找值（或没有查找值），因为它们需要在数据库目录的扫描以查找匹配的表文件的查询。

    被限制在一个特定的恒定数据库名称查询的好处是检查需要进行只为命名数据库目录。例如：

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = '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表，不管有多少表有可能是在测试数据库中。与此相反，下面的查询需要的测试数据库目录的扫描以确定哪些表名匹配模式'T％“：

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't%';

2）Write queries that minimize the number of table files that must be opened（写查询最小化必须打开的表文件数）

    对于提及某些INFORMATION_SCHEMA表列的查询，一些优化是可用的最小化必须被打开表的文件的数量。例如：

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'test';

    一些值，比如MyISAM表的INDEX_LENGTH，需要打开.MYD和.MYI文件。

    在打开文件优化类型因而表示：

• SKIP_OPEN_TABLE : 表文件不需要被打开。信息已成为可在查询中通过扫描数据库目录
  
  
• OPEN_FRM_ONLY : 只有表的.frm文件需要被打开。
  
  
• OPEN_TRIGGER_ONLY : 只有表的.TRG文件需要被打开。
  
  
•  OPEN_FULL_TABLE: 未经优化的信息查询。该.frm，.MYD和.MYI文件必须被打开。   

3）    Use EXPLAIN to determine whether the server can use INFORMATION_SCHEMA optimizations for a query （使用EXPLAIN来确定服务器是否可以使用INFORMATION_SCHEMA优化的查询）

    这尤其适用于该搜索信息从多个数据库INFORMATION_SCHEMA查询，这可能需要很长的时间和影响性能。在EXPLAIN输出该额外值表示，如果有的话，前面所述的服务器的优化可用于评估INFORMATION_SCHEMA查询。下面的例子演示了各种信息，你可以期望看到的额外价值。

        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

没有表名称查找值提供的，因此该服务器必须扫描测试数据库的目录。