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

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 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 單表檔案（file-per-table）和通用表空間的資料檔案路徑資訊。
INNODB_TABLESPACES 提供關於 InnoDB 單表檔案、通用和復原表空間的元數據。
INNODB_TABLESPACES_BRIEF 提供關於 InnoDB 表空間的元數據子集。
INNODB_FOREIGN 提供關於在 InnoDB 資料表上定義的外鍵的元數據。
INNODB_FOREIGN_COLS 提供關於在 InnoDB 資料表上定義的外鍵欄位的元數據。

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

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

範例 17.2 InnoDB INFORMATION_SCHEMA 綱要物件資料表

此範例使用一個簡單的資料表 (t1) 和單一索引 (i1) 來示範在 InnoDB INFORMATION_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 顯示使用 ALTER TABLE ... ADD COLUMN 和 ALGORITHM=INSTANT 新增第一個立即欄位之前，資料表中的欄位數。

使用來自 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 資料類型、字元集程式碼和可空性）以及欄位長度 (LEN)。HAS_DEFAULT 和 DEFAULT_VALUE 欄位僅適用於使用 ALTER TABLE ... ADD COLUMN 和 ALGORITHM=INSTANT 立即新增的欄位。

再次使用來自 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 綱要物件資料表

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 |
+------------------------+------------+-----------+-------+-----------+