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

版本 8.4

9.0
8.0
5.7

8.0 日文

MySQL 8.4 參考手冊 / ... / 優化 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 資料字典表的查詢來處理。

上一步首頁上一層下一步