• 首页
  • css3教程
  • html5教程
  • jQuery手册
  • vue手册
  • php手册
  • MySQL手册
  • apache手册
  • redis手册
  • 优化 INFORMATION_SCHEMA 查询

    监视数据库的应用程序可能会频繁使用INFORMATION_SCHEMA表。若要最有效地编写这些表的查询,请使用以下常规准则:

    • 尝试仅查询INFORMATION_SCHEMA作为数据字典表视图的表。
    • 尝试仅查询静态元数据。选择列或对动态元数据和静态元数据使用检索条件会增加处理动态元数据的开销。
    注意

    INFORMATION_SCHEMA查询中数据库名称和表名称的比较行为可能与您期望的有所不同。有关详细信息,请参见“在INFORMATION_SCHEMA搜索中使用归类”。

    这些INFORMATION_SCHEMA表被实现为数据字典表上的视图,因此对它们的查询从数据字典中检索信息:

    CHARACTER_SETS
    CHECK_CONSTRAINTS
    COLLATIONS
    COLLATION_CHARACTER_SET_APPLICABILITY
    COLUMNS
    EVENTS
    FILES
    INNODB_COLUMNS
    INNODB_DATAFILES
    INNODB_FIELDS
    INNODB_FOREIGN
    INNODB_FOREIGN_COLS
    INNODB_INDEXES
    INNODB_TABLES
    INNODB_TABLESPACES
    INNODB_TABLESPACES_BRIEF
    INNODB_TABLESTATS
    KEY_COLUMN_USAGE
    PARAMETERS
    PARTITIONS
    REFERENTIAL_CONSTRAINTS
    RESOURCE_GROUPS
    ROUTINES
    SCHEMATA
    STATISTICS
    TABLES
    TABLE_CONSTRAINTS
    TRIGGERS
    VIEWS
    VIEW_ROUTINE_USAGE
    VIEW_TABLE_USAGE
    

    某些类型的值,即使是非视图INFORMATION_SCHEMA表的值也可以通过查找从数据字典中检索到。这包括数据库和表名,表类型和存储引擎之类的值。

    一些INFORMATION_SCHEMA表包含提供表统计信息的列:

    STATISTICS.CARDINALITY
    TABLES.AUTO_INCREMENT
    TABLES.AVG_ROW_LENGTH
    TABLES.CHECKSUM
    TABLES.CHECK_TIME
    TABLES.CREATE_TIME
    TABLES.DATA_FREE
    TABLES.DATA_LENGTH
    TABLES.INDEX_LENGTH
    TABLES.MAX_DATA_LENGTH
    TABLES.TABLE_ROWS
    TABLES.UPDATE_TIME
    

    这些列表示动态表元数据。即,信息随着表内容的改变而改变。

    默认情况下,查询列时,MySQL从mysql.index_statsmysql.table_stats字典表中检索这些列的缓存值,这比直接从存储引擎中检索统计信息更为有效。如果缓存的统计信息不可用或已过期,MySQL将从存储引擎中检索最新的统计信息并将其缓存在mysql.index_statsmysql.table_stats字典表中。后续查询将检索缓存的统计信息,直到缓存的统计信息到期为止。

    information_schema_stats_expiry会话变量定义缓存统计到期之前的一段时间。默认值为86400秒(24小时),但是时间段可以延长到一年。

    要随时更新给定表的缓存值,请使用ANALYZE TABLE

    在以下情况下,查询统计信息列不会在mysql.index_statsmysql.table_stats字典表中存储或更新统计信息:

    • 缓存的统计信息尚未过期时。
    • information_schema_stats_expiry设置为0。
    • 当服务器在启动read_onlysuper_read_onlytransaction_read_only,或innodb_read_only模式。
    • 当查询还获取性能架构数据时。

    information_schema_stats_expiry是一个会话变量,每个客户端会话都可以定义自己的到期值。从存储引擎检索并由一个会话缓存的统计信息可用于其他会话。

    注意

    如果innodb_read_only启用了系统变量,则ANALYZE TABLE可能会失败,因为它无法更新使用的数据字典中的统计表InnoDB。对于ANALYZE TABLE更新密钥分发的操作,即使该操作更新表本身(例如,如果它是MyISAM表),也可能会发生故障。要获取更新的分发统计信息,请设置information_schema_stats_expiry=0

    对于INFORMATION_SCHEMA实现为数据字典表上的视图的表,基础数据字典表上的索引允许优化器构造有效的查询执行计划。要参见优化程序所做的选择,请使用EXPLAIN。要还参见服务器用于执行INFORMATION_SCHEMA查询的查询,请SHOW WARNINGS紧接着使用EXPLAIN

    考虑以下语句,该语句标识utf8mb4字符集的归类:

    mysql> SELECT COLLATION_NAME
           FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
           WHERE CHARACTER_SET_NAME = 'utf8mb4';
    +----------------------------	+
    | COLLATION_NAME             	|
    +----------------------------	+
    | utf8mb4_general_ci         	|
    | utf8mb4_bin                	|
    | utf8mb4_unicode_ci         	|
    | utf8mb4_icelandic_ci       	|
    | utf8mb4_latvian_ci         	|
    | utf8mb4_romanian_ci        	|
    | utf8mb4_slovenian_ci       	|
    ...
    

    服务器如何处理该语句?要找出答案,请使用EXPLAIN

    mysql> EXPLAIN SELECT COLLATION_NAME
           FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
           WHERE CHARACTER_SET_NAME = 'utf8mb4'\G
    *************************** 1. row 	***************************
               id	: 1
      select_type	: SIMPLE
            table	: cs
       partitions	: NULL
             type	: const
    possible_keys	: PRIMARY,name
              key	: name
          key_len	: 194
              ref	: const
             rows	: 1
         filtered	: 100.00
            Extr 	: Using index
    	*************************** 2. row 	***************************
               id	: 1
      select_type	: SIMPLE
            table	: col
       partitions	: NULL
             type	: ref
    possible_keys	: character_set_id
              key	: character_set_id
          key_len	: 8
              ref	: const
             rows	: 68
         filtered	: 100.00
            Extr 	: NULL
    2 rows in set, 1 warning (0.01 sec)
    

    要参见用于统计该语句的查询,请使用SHOW WARNINGS

    mysql> SHOW WARNINGS\G
    *************************** 1. row 	***************************
      Level	: Note
       Code	: 1003
    Message	: /	* select#1 	*/ select `mysql`.`col`.`name` AS `COLLATION_NAME`
             from `mysql`.`character_sets` `cs`
             join `mysql`.`collations` `col`
             where ((`mysql`.`col`.`character_set_id` = '45')
             and ('utf8mb4' = 'utf8mb4'))
    

    如所示SHOW WARNINGS,服务器将查询COLLATION_CHARACTER_SET_APPLICABILITY作为对系统数据库中的character_setscollations数据字典表的查询来处理mysql