目录

  • 第八章 优化（二十九）—— 优化 INFORMATION_SCHEMA 查询
  • 8.2 优化SQL语句
  • 8.2.3 优化 INFORMATION_SCHEMA（信息概要）查询


第八章 优化（二十九）—— 优化 INFORMATION_SCHEMA 查询

8.2 优化SQL语句

8.2.3 优化 INFORMATION_SCHEMA（信息概要）查询

监视数据库的应用程序可能会经常使用INFORMATION_SCHEMA表。想要最有效地编写对这些表的查询，请使用以下一般准则：

  • 尝试只查询数据字典表的视图INFORMATION_SCHEMA表。
  • 尝试只查询静态元数据。在查询静态元数据的同时选择列或使用检索条件来得到动态元数据会增加处理动态元数据的开销。

备注：
INFORMATION_SCHEMA查询中数据库和表名的比较行为可能与您期望的不同。详细信息请参见10.8.7“在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_stats和mysql.table_stats字典表的这些列的缓冲值，这比直接从存储引擎中检索统计信息更有效。如果缓冲的统计信息不可用或已过期，那么，MySQL会从存储引擎中检索最新的统计信息，并把它们缓冲到 mysql.index_stats和 mysql.table_stats字典表中。后续的查询检索这些缓冲的统计信息，直到这些信息过期。

information_schema_stats_expiry会话变量定义了缓存统计数据过期的时间周期，默认值为86400秒(24小时)，但是时间周期可以延长到一年。

想要随时对给定表更新缓冲值，请使用ANALYZE TABLE。

在以下情况下，查询统计列不会保存或更新 mysql.index_stats和 mysql.table_stats字典表中的统计信息：

  • 当缓冲统计信息没有过期。
  • 当information_schema_stats_expiry设置为0。
  • 当服务器以read_only（只读）、super_read_only、transaction_read_only 或 innodb_read_only模式启动。
  • 当查询也获取性能概要（Performance Schema）数据。

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

备注：
如果开启innodb_read_only系统变量，ANALYZE TABLE可能会失败，因为它不能更新使用InnoDB的数据字典中的统计表。对于更新键分布的ANALYZE TABLE操作，即使此操作更新了表本身（例如，如果它是一个MyISAM表），也可能发生失败。要获得更新的分布统计信息，请设置information_schema_stats_expiry=0。

对于实现为数据字典表视图的INFORMATION_SCHEMA表，底层数据字典表上的索引允许优化器构造高效的查询执行计划。要查看优化器所做的选择，请使用EXPLAIN。要查看服务器用于执行INFORMATION_SCHEMA查询的查询，请在EXPLAIN之后立即使用SHOW WARNINGS。

考虑以下语句，它标识了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
        Extra: 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
        Extra: 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查询处理为对mysql系统数据库中的character_sets和collations数据字典表的查询。