MySQL :: MySQL 9.0 參考手冊 :: 10.2.3 優化 INFORMATION_SCHEMA 查詢

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 優化 INFORMATION_SCHEMA 查詢

10.2.3 優化 INFORMATION_SCHEMA 查詢

監控資料庫的應用程式可能會頻繁使用 INFORMATION_SCHEMA 資料表。為了最有效率地撰寫這些資料表的查詢，請使用下列一般指南

嘗試僅查詢資料字典資料表上的檢視 INFORMATION_SCHEMA 資料表。
嘗試僅查詢靜態中繼資料。選取資料行或使用動態中繼資料的擷取條件以及靜態中繼資料，會增加處理動態中繼資料的額外負荷。

注意

INFORMATION_SCHEMA 查詢中資料庫和資料表名稱的比較行為可能與您的預期不同。如需詳細資訊，請參閱第 12.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.innodb_table_stats 字典資料表擷取這些資料行的快取值，這比直接從儲存引擎擷取統計資訊更有效率。如果快取的統計資訊不可用或已過期，MySQL 會從儲存引擎擷取最新的統計資訊，並將其快取在 mysql.index_stats 和 mysql.innodb_table_stats 字典資料表中。後續查詢會擷取快取的統計資訊，直到快取的統計資訊過期。重新啟動伺服器或第一次開啟 mysql.index_stats 和 mysql.innodb_table_stats 資料表不會自動更新快取的統計資訊。

information_schema_stats_expiry 工作階段變數定義快取統計資訊過期之前的時間段。預設值為 86400 秒（24 小時），但時間段可以延長到長達一年。

若要隨時更新指定資料表的快取值，請使用 ANALYZE TABLE。

在下列情況下，查詢統計資訊資料行不會在 mysql.index_stats 和 mysql.innodb_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 資料字典表格的查詢。

上一步首頁上一層下一步