MySQL :: MySQL 8.4 參考手冊 :: 17.15.3 InnoDB INFORMATION_SCHEMA 結構描述物件表格

版本 8.4

8.0 日文

MySQL 8.4 參考手冊 / ... / InnoDB INFORMATION_SCHEMA 結構描述物件表格

17.15.3 InnoDB INFORMATION_SCHEMA 結構描述物件表格

您可以使用 InnoDB INFORMATION_SCHEMA 表格，擷取由 InnoDB 管理的結構描述物件的中繼資料。此資訊來自資料字典。傳統上，您會使用第 17.17 節「InnoDB 監視器」中的技術，設定 InnoDB 監視器並剖析來自 SHOW ENGINE INNODB STATUS 陳述式的輸出，來取得這種類型的資訊。InnoDB INFORMATION_SCHEMA 表格介面可讓您使用 SQL 查詢此資料。

InnoDB INFORMATION_SCHEMA 結構描述物件表格包括此處列出的表格

INNODB_DATAFILES
INNODB_TABLESTATS
INNODB_FOREIGN
INNODB_COLUMNS
INNODB_INDEXES
INNODB_FIELDS
INNODB_TABLESPACES
INNODB_TABLESPACES_BRIEF
INNODB_FOREIGN_COLS
INNODB_TABLES

表格名稱表示提供的資料類型

INNODB_TABLES 提供關於 InnoDB 表格的中繼資料。
INNODB_COLUMNS 提供關於 InnoDB 表格欄的中繼資料。
INNODB_INDEXES 提供關於 InnoDB 索引的中繼資料。
INNODB_FIELDS 提供關於 InnoDB 索引的索引鍵欄 (欄位) 的中繼資料。
INNODB_TABLESTATS 提供關於 InnoDB 表格的低層級狀態資訊的檢視，此資訊衍生自記憶體中的資料結構。
INNODB_DATAFILES 提供每個表格的檔案和一般表空間的資料檔案路徑資訊。
INNODB_TABLESPACES 提供關於 InnoDB 每個表格的檔案、一般和復原表空間的中繼資料。
INNODB_TABLESPACES_BRIEF 提供關於 InnoDB 表空間的中繼資料子集。
INNODB_FOREIGN 提供關於 InnoDB 表格上定義的外來鍵的中繼資料。
INNODB_FOREIGN_COLS 提供關於在 InnoDB 資料表上定義的外鍵欄位的中繼資料。

InnoDB INFORMATION_SCHEMA 的 schema 物件資料表可以透過 TABLE_ID、INDEX_ID 和 SPACE 等欄位連接在一起，讓您可以輕鬆檢索您想要研究或監控的物件的所有可用資料。

請參閱 InnoDB 的 INFORMATION_SCHEMA 文件，以了解每個資料表的欄位資訊。

範例 17.2 InnoDB INFORMATION_SCHEMA Schema 物件資料表

此範例使用一個簡單的資料表 (t1) 與單一索引 (i1) 來示範在 InnoDB INFORMATION_SCHEMA schema 物件資料表中找到的中繼資料類型。

建立測試資料庫和資料表 t1

mysql> CREATE DATABASE test;

mysql> USE test;

mysql> CREATE TABLE t1 (
       col1 INT,
       col2 CHAR(10),
       col3 VARCHAR(10))
       ENGINE = InnoDB;

mysql> CREATE INDEX i1 ON t1(col1);

建立資料表 t1 後，查詢 INNODB_TABLES 以查找 test/t1 的中繼資料
```
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: test/t1
         FLAG: 1
       N_COLS: 6
        SPACE: 57
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
 INSTANT_COLS: 0
```
資料表 t1 的 TABLE_ID 為 71。FLAG 欄位提供有關資料表格式和儲存特性的位元層級資訊。共有六個欄位，其中三個是 InnoDB 建立的隱藏欄位 (DB_ROW_ID、DB_TRX_ID 和 DB_ROLL_PTR)。資料表 SPACE 的 ID 為 57 (值為 0 表示資料表位於系統資料表空間中)。ROW_FORMAT 為 Compact。ZIP_PAGE_SIZE 僅適用於具有 Compressed 列格式的資料表。INSTANT_COLS 顯示在使用 ALGORITHM=INSTANT 的 ALTER TABLE ... ADD COLUMN 新增第一個即時欄位之前，資料表中的欄位數量。

使用來自 INNODB_TABLES 的 TABLE_ID 資訊，查詢 INNODB_COLUMNS 資料表以取得資料表欄位的資訊。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS where TABLE_ID = 71\G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: col1
          POS: 0
        MTYPE: 6
       PRTYPE: 1027
          LEN: 4
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 2. row ***************************
     TABLE_ID: 71
         NAME: col2
          POS: 1
        MTYPE: 2
       PRTYPE: 524542
          LEN: 10
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 3. row ***************************
     TABLE_ID: 71
         NAME: col3
          POS: 2
        MTYPE: 1
       PRTYPE: 524303
          LEN: 10
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL

除了 TABLE_ID 和欄位 NAME 之外，INNODB_COLUMNS 還提供每個欄位的序數位置 (POS) (從 0 開始依序遞增)、欄位的 MTYPE 或「主要類型」(6 = INT，2 = CHAR，1 = VARCHAR)、PRTYPE 或「精確類型」(一個二進位值，其位元代表 MySQL 資料類型、字元集代碼和是否允許為 NULL)，以及欄位長度 (LEN)。HAS_DEFAULT 和 DEFAULT_VALUE 欄位僅適用於使用 ALGORITHM=INSTANT 的 ALTER TABLE ... ADD COLUMN 即時新增的欄位。

再次使用來自 INNODB_TABLES 的 TABLE_ID 資訊，查詢 INNODB_INDEXES 以取得與資料表 t1 相關聯的索引資訊。
```
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE TABLE_ID = 71 \G
*************************** 1. row ***************************
       INDEX_ID: 111
           NAME: GEN_CLUST_INDEX
       TABLE_ID: 71
           TYPE: 1
       N_FIELDS: 0
        PAGE_NO: 3
          SPACE: 57
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
       INDEX_ID: 112
           NAME: i1
       TABLE_ID: 71
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 4
          SPACE: 57
MERGE_THRESHOLD: 50
```
INNODB_INDEXES 返回兩個索引的資料。第一個索引是 GEN_CLUST_INDEX，如果資料表沒有使用者定義的叢集索引，則這是 InnoDB 建立的叢集索引。第二個索引 (i1) 是使用者定義的次要索引。
INDEX_ID 是索引的識別符號，在執行個體的所有資料庫中都是唯一的。TABLE_ID 識別與索引相關聯的資料表。索引 TYPE 值表示索引的類型 (1 = 叢集索引，0 = 次要索引)。N_FILEDS 值是構成索引的欄位數。PAGE_NO 是索引 B 樹的根頁面編號，而 SPACE 是索引所在的資料表空間的 ID。非零值表示索引不在系統資料表空間中。MERGE_THRESHOLD 定義索引頁面中資料量的百分比閾值。如果刪除資料列或透過更新作業縮短資料列時，索引頁面中的資料量低於此值 (預設值為 50%)，則 InnoDB 會嘗試將索引頁面與相鄰的索引頁面合併。
使用來自 INNODB_INDEXES 的 INDEX_ID 資訊，查詢 INNODB_FIELDS 以取得有關索引 i1 欄位的資訊。
```
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FIELDS where INDEX_ID = 112 \G
*************************** 1. row ***************************
INDEX_ID: 112
    NAME: col1
     POS: 0
```
INNODB_FIELDS 提供索引欄位的 NAME 及其在索引中的序數位置。如果索引 (i1) 是在多個欄位上定義的，則 INNODB_FIELDS 將會提供每個索引欄位的中繼資料。

使用來自 INNODB_TABLES 的 SPACE 資訊，查詢 INNODB_TABLESPACES 資料表以取得資料表資料表空間的資訊。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE = 57 \G
*************************** 1. row ***************************
          SPACE: 57
          NAME: test/t1
          FLAG: 16417
    ROW_FORMAT: Dynamic
     PAGE_SIZE: 16384
 ZIP_PAGE_SIZE: 0
    SPACE_TYPE: Single
 FS_BLOCK_SIZE: 4096
     FILE_SIZE: 114688
ALLOCATED_SIZE: 98304
AUTOEXTEND_SIZE: 0
SERVER_VERSION: 8.4.0
 SPACE_VERSION: 1
    ENCRYPTION: N
         STATE: normal

除了資料表空間的 SPACE ID 和關聯資料表的 NAME 之外，INNODB_TABLESPACES 還提供資料表空間 FLAG 資料，這是關於資料表空間格式和儲存特性的位元層級資訊。此外還提供了資料表空間 ROW_FORMAT、PAGE_SIZE 和其他數個資料表空間中繼資料項目。

再次使用來自 INNODB_TABLES 的 SPACE 資訊，查詢 INNODB_DATAFILES 以取得資料表空間資料檔案的位置。
```
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE SPACE = 57 \G
*************************** 1. row ***************************
SPACE: 57
 PATH: ./test/t1.ibd
```
資料檔案位於 MySQL 的 data 目錄下的 test 目錄中。如果使用 CREATE TABLE 陳述式的 DATA DIRECTORY 子句，在 MySQL 資料目錄之外的位置建立了每表一個檔案的資料表空間，則資料表空間 PATH 會是完整合格的目錄路徑。
最後一個步驟，將資料列插入資料表 t1 (TABLE_ID = 71)，並檢視 INNODB_TABLESTATS 資料表中的資料。MySQL 最佳化工具會使用此資料表中的資料來計算在查詢 InnoDB 資料表時要使用的索引。此資訊是從記憶體中的資料結構衍生而來的。
```
mysql> INSERT INTO t1 VALUES(5, 'abc', 'def');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where TABLE_ID = 71 \G
*************************** 1. row ***************************
         TABLE_ID: 71
             NAME: test/t1
STATS_INITIALIZED: Initialized
         NUM_ROWS: 1
 CLUST_INDEX_SIZE: 1
 OTHER_INDEX_SIZE: 0
 MODIFIED_COUNTER: 1
          AUTOINC: 0
        REF_COUNT: 1
```
STATS_INITIALIZED 欄位表示是否已收集資料表的統計資料。NUM_ROWS 是資料表中目前估計的資料列數。CLUST_INDEX_SIZE 和 OTHER_INDEX_SIZE 欄位會報告磁碟上儲存資料表叢集索引和次要索引的頁面數。 MODIFIED_COUNTER 值顯示透過 DML 作業和來自外鍵的串聯作業修改的資料列數。 AUTOINC 值是針對任何自動遞增作業發出的下一個號碼。資料表 t1 上未定義任何自動遞增欄位，因此值為 0。REF_COUNT 值是一個計數器。當計數器達到 0 時，表示資料表的中繼資料可以從資料表快取中逐出。

範例 17.3 外鍵 INFORMATION_SCHEMA Schema 物件資料表

INNODB_FOREIGN 和 INNODB_FOREIGN_COLS 資料表提供有關外鍵關係的資料。此範例使用具有外鍵關係的父資料表和子資料表，來示範在 INNODB_FOREIGN 和 INNODB_FOREIGN_COLS 資料表中找到的資料。

建立具有父資料表和子資料表的測試資料庫

mysql> CREATE DATABASE test;

mysql> USE test;

mysql> CREATE TABLE parent (id INT NOT NULL,
       PRIMARY KEY (id)) ENGINE=INNODB;

mysql> CREATE TABLE child (id INT, parent_id INT,
    ->     INDEX par_ind (parent_id),
    ->     CONSTRAINT fk1
    ->     FOREIGN KEY (parent_id) REFERENCES parent(id)
    ->     ON DELETE CASCADE) ENGINE=INNODB;

建立父資料表和子資料表後，查詢 INNODB_FOREIGN，並找出 test/child 和 test/parent 外鍵關係的外鍵資料
```
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN \G
*************************** 1. row ***************************
      ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1
```
中繼資料包含外鍵 ID (fk1)，其名稱是根據在子表格上定義的 CONSTRAINT 而來。FOR_NAME 是定義外鍵的子表格名稱。REF_NAME 是父表格（即「參考」的表格）的名稱。N_COLS 是外鍵索引中的欄位數量。TYPE 是一個數值，代表提供關於外鍵欄位的額外資訊的位元旗標。在此案例中，TYPE 的值為 1，表示該外鍵指定了 ON DELETE CASCADE 選項。關於 TYPE 值的詳細資訊，請參閱 INNODB_FOREIGN 表格的定義。
使用外鍵 ID，查詢 INNODB_FOREIGN_COLS，以檢視外鍵欄位的資料。
```
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE ID = 'test/fk1' \G
*************************** 1. row ***************************
          ID: test/fk1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
         POS: 0
```
FOR_COL_NAME 是子表格中外鍵欄位的名稱，而 REF_COL_NAME 是父表格中參考的欄位名稱。POS 值是外鍵索引中鍵值欄位的序數位置，從零開始。

範例 17.4 聯結 InnoDB INFORMATION_SCHEMA 結構描述物件表格

此範例示範如何聯結三個 InnoDB INFORMATION_SCHEMA 結構描述物件表格（INNODB_TABLES、INNODB_TABLESPACES 和 INNODB_TABLESTATS），以收集關於 employees 範例資料庫中表格的檔案格式、列格式、頁面大小和索引大小資訊。

以下表格別名用於縮短查詢字串：

使用 IF() 控制流程函數來考量壓縮的表格。如果表格是壓縮的，則索引大小會使用 ZIP_PAGE_SIZE 而不是 PAGE_SIZE 來計算。CLUST_INDEX_SIZE 和 OTHER_INDEX_SIZE（以位元組為單位回報）會除以 1024*1024 以提供以 MB（百萬位元組）為單位的索引大小。MB 值使用 ROUND() 函數四捨五入到零位小數。

mysql> SELECT a.NAME, a.ROW_FORMAT,
        @page_size :=
         IF(a.ROW_FORMAT='Compressed',
          b.ZIP_PAGE_SIZE, b.PAGE_SIZE)
          AS page_size,
         ROUND((@page_size * c.CLUST_INDEX_SIZE)
          /(1024*1024)) AS pk_mb,
         ROUND((@page_size * c.OTHER_INDEX_SIZE)
          /(1024*1024)) AS secidx_mb
       FROM INFORMATION_SCHEMA.INNODB_TABLES a
       INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES b on a.NAME = b.NAME
       INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESTATS c on b.NAME = c.NAME
       WHERE a.NAME LIKE 'employees/%'
       ORDER BY a.NAME DESC;
+------------------------+------------+-----------+-------+-----------+
| NAME                   | ROW_FORMAT | page_size | pk_mb | secidx_mb |
+------------------------+------------+-----------+-------+-----------+
| employees/titles       | Dynamic    |     16384 |    20 |        11 |
| employees/salaries     | Dynamic    |     16384 |    93 |        34 |
| employees/employees    | Dynamic    |     16384 |    15 |         0 |
| employees/dept_manager | Dynamic    |     16384 |     0 |         0 |
| employees/dept_emp     | Dynamic    |     16384 |    12 |        10 |
| employees/departments  | Dynamic    |     16384 |     0 |         0 |
+------------------------+------------+-----------+-------+-----------+