17.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables

17.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables

InnoDB``INFORMATION_SCHEMA 테이블을 사용하여 InnoDB가 관리하는 schema object에 대한 메타데이터를 추출할 수 있습니다. 이 정보는 데이터 딕셔너리에서 가져옵니다. 전통적으로는 Section 17.17, “InnoDB Monitors”의 기법을 사용하여, InnoDB 모니터를 설정하고 SHOW ENGINE INNODB STATUS 명령문의 출력을 파싱해서 이 종류의 정보를 얻었습니다. InnoDB``INFORMATION_SCHEMA 테이블 인터페이스를 사용하면 이 데이터를 SQL로 쿼리할 수 있습니다.

InnoDB``INFORMATION_SCHEMA schema object 테이블에는 다음과 같은 테이블이 포함됩니다:

• 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 인덱스의 키 컬럼(field)에 대한 메타데이터를 제공합니다.

• INNODB_TABLESTATS는 메모리 내 데이터 구조에서 파생된, InnoDB 테이블에 대한 저수준 상태 정보 뷰를 제공합니다.

• INNODB_DATAFILES는 InnoDB file-per-table 및 일반 테이블스페이스에 대한 데이터 파일 경로 정보를 제공합니다.

• INNODB_TABLESPACES는 InnoDB file-per-table, 일반, undo 테이블스페이스에 대한 메타데이터를 제공합니다.

• INNODB_TABLESPACES_BRIEF는 InnoDB 테이블스페이스에 대한 메타데이터의 일부(subset)를 제공합니다.

• INNODB_FOREIGN는 InnoDB 테이블에 정의된 외래 키에 대한 메타데이터를 제공합니다.

• INNODB_FOREIGN_COLS는 InnoDB 테이블에 정의된 외래 키 컬럼에 대한 메타데이터를 제공합니다.

InnoDB``INFORMATION_SCHEMA schema object 테이블은 TABLE_ID, INDEX_ID, SPACE와 같은 필드를 통해 서로 조인할 수 있으므로, 분석하거나 모니터하려는 오브젝트에 대해 사용 가능한 모든 데이터를 손쉽게 조회할 수 있습니다.

각 테이블의 컬럼에 대한 정보는 InnoDB INFORMATION_SCHEMA 문서를 참조하십시오.

Example 17.2 InnoDB INFORMATION_SCHEMA Schema Object Tables

이 예제는 단일 인덱스(i1)를 가진 간단한 테이블(t1)을 사용하여 InnoDB``INFORMATION_SCHEMA schema object 테이블에서 찾을 수 있는 메타데이터의 유형을 보여줍니다.

1. test 데이터베이스와 테이블 t1을 생성합니다:

1mysql> CREATE DATABASE test;
2
3mysql> USE test;
4
5mysql> CREATE TABLE t1 (
6          col1 INT,
7          col2 CHAR(10),
8          col3 VARCHAR(10))
9          ENGINE = InnoDB;
10
11mysql> CREATE INDEX i1 ON t1(col1);

2. 테이블 t1을 생성한 후, INNODB_TABLES를 쿼리하여 test/t1에 대한 메타데이터를 찾습니다:

1mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1' \G
2*************************** 1. row ***************************
3        TABLE_ID: 71
4            NAME: test/t1
5            FLAG: 1
6          N_COLS: 6
7           SPACE: 57
8      ROW_FORMAT: Compact
9ZIP_PAGE_SIZE: 0
10    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는 ALTERN TABLE ... ADD COLUMN을 ALGORITHM=INSTANT와 함께 사용하여 첫 instant 컬럼을 추가하기 전, 테이블에 존재하던 컬럼 수를 보여줍니다.

3. INNODB_TABLES에서 얻은 TABLE_ID 정보를 사용하여, 테이블 컬럼에 대한 정보를 얻기 위해 INNODB_COLUMNS 테이블을 쿼리합니다.

1mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS where TABLE_ID = 71\G
2*************************** 1. row ***************************
3        TABLE_ID: 71
4            NAME: col1
5             POS: 0
6           MTYPE: 6
7          PRTYPE: 1027
8             LEN: 4
9     HAS_DEFAULT: 0
10DEFAULT_VALUE: NULL
11*************************** 2. row ***************************
12        TABLE_ID: 71
13            NAME: col2
14             POS: 1
15           MTYPE: 2
16          PRTYPE: 524542
17             LEN: 10
18     HAS_DEFAULT: 0
19DEFAULT_VALUE: NULL
20*************************** 3. row ***************************
21        TABLE_ID: 71
22            NAME: col3
23             POS: 2
24           MTYPE: 1
25          PRTYPE: 524303
26             LEN: 10
27     HAS_DEFAULT: 0
28DEFAULT_VALUE: NULL

TABLE_ID와 컬럼 NAME 외에도, INNODB_COLUMNS는 각 컬럼의 ordinal 위치(POS)를 제공합니다(0부터 시작하여 순차적으로 증가). 컬럼의 MTYPE 또는 “main type”(6 = INT, 2 = CHAR, 1 = VARCHAR), MySQL 데이터 타입, 문자 집합 코드, null 허용 여부를 나타내는 비트로 구성된 바이너리 값인 PRTYPE 또는 “precise type”, 컬럼 길이(LEN)를 제공합니다. HAS_DEFAULT 및 DEFAULT_VALUE 컬럼은 ALTERN TABLE ... ADD COLUMN을 ALGORITHM=INSTANT와 함께 사용하여 instant로 추가된 컬럼에만 적용됩니다.

4. 다시 INNODB_TABLES에서 얻은 TABLE_ID 정보를 사용하여, 테이블 t1에 연관된 인덱스에 대한 정보를 얻기 위해 INNODB_INDEXES를 쿼리합니다.

1mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE TABLE_ID = 71 \G
2*************************** 1. row ***************************
3          INDEX_ID: 111
4              NAME: GEN_CLUST_INDEX
5          TABLE_ID: 71
6              TYPE: 1
7          N_FIELDS: 0
8           PAGE_NO: 3
9             SPACE: 57
10MERGE_THRESHOLD: 50
11*************************** 2. row ***************************
12          INDEX_ID: 112
13              NAME: i1
14          TABLE_ID: 71
15              TYPE: 0
16          N_FIELDS: 1
17           PAGE_NO: 4
18             SPACE: 57
19MERGE_THRESHOLD: 50

INNODB_INDEXES는 두 개의 인덱스에 대한 데이터를 반환합니다. 첫 번째 인덱스는 GEN_CLUST_INDEX로, 테이블에 사용자 정의 클러스터형 인덱스가 없는 경우 InnoDB가 생성하는 클러스터형 인덱스입니다. 두 번째 인덱스(i1)는 사용자가 정의한 보조 인덱스입니다.

INDEX_ID는 인스턴스 내 모든 데이터베이스에서 고유한 인덱스 식별자입니다. TABLE_ID는 인덱스가 연관된 테이블을 식별합니다. 인덱스 TYPE 값은 인덱스 유형을 나타냅니다(1 = Clustered Index, 0 = Secondary index). N_FILEDS 값은 인덱스를 구성하는 필드 수입니다. PAGE_NO는 인덱스 B-tree의 루트 페이지 번호이며, SPACE는 인덱스가 존재하는 테이블스페이스의 ID입니다. 0이 아닌 값은 인덱스가 시스템 테이블스페이스에 존재하지 않음을 의미합니다. MERGE_THRESHOLD는 인덱스 페이지 내 데이터 양에 대한 백분율 임계값을 정의합니다. 로우가 삭제되거나 업데이트 작업으로 인해 로우가 짧아져 인덱스 페이지의 데이터 양이 이 값(기본값 50%) 아래로 떨어지면, InnoDB는 인덱스 페이지를 인접 인덱스 페이지와 병합하려고 시도합니다.

5. INNODB_INDEXES에서 얻은 INDEX_ID 정보를 사용하여, 인덱스 i1의 필드에 대한 정보를 얻기 위해 INNODB_FIELDS를 쿼리합니다.

1mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FIELDS where INDEX_ID = 112 \G
2*************************** 1. row ***************************
3INDEX_ID: 112
4       NAME: col1
5        POS: 0

INNODB_FIELDS는 인덱스 필드의 NAME과 인덱스 내에서의 ordinal 위치를 제공합니다. 인덱스(i1)가 여러 필드에 대해 정의되어 있다면, INNODB_FIELDS는 각 인덱스 필드에 대한 메타데이터를 제공합니다.

6. INNODB_TABLES에서 얻은 SPACE 정보를 사용하여, 테이블의 테이블스페이스에 대한 정보를 얻기 위해 INNODB_TABLESPACES 테이블을 쿼리합니다.

1mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE = 57 \G
2*************************** 1. row ***************************
3             SPACE: 57
4             NAME: test/t1
5             FLAG: 16417
6       ROW_FORMAT: Dynamic
7        PAGE_SIZE: 16384
8    ZIP_PAGE_SIZE: 0
9       SPACE_TYPE: Single
10    FS_BLOCK_SIZE: 4096
11        FILE_SIZE: 114688
12ALLOCATED_SIZE: 98304
13AUTOEXTEND_SIZE: 0
14SERVER_VERSION: 8.4.0
15    SPACE_VERSION: 1
16       ENCRYPTION: N
17            STATE: normal

테이블스페이스의 SPACE ID와 연결된 테이블의 NAME 외에도, INNODB_TABLESPACES는 테이블스페이스 포맷 및 스토리지 특성에 대한 비트 레벨 정보를 담은 테이블스페이스 FLAG 데이터를 제공합니다. 또한 테이블스페이스 ROW_FORMAT, PAGE_SIZE 및 여러 다른 테이블스페이스 메타데이터 항목도 제공합니다.

7. 다시 INNODB_TABLES에서 얻은 SPACE 정보를 사용하여, 테이블스페이스 데이터 파일의 위치를 얻기 위해 INNODB_DATAFILES를 쿼리합니다.

1mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE SPACE = 57 \G
2*************************** 1. row ***************************
3SPACE: 57
4    PATH: ./test/t1.ibd

데이터 파일은 MySQL의 data 디렉터리 아래 test 디렉터리에 위치합니다. CREATE TABLE 명령문의 DATA DIRECTORY 절을 사용하여 MySQL 데이터 디렉터리 외부 위치에 file-per-table 테이블스페이스를 생성한 경우, 테이블스페이스 PATH는 완전한 디렉터리 경로(fully qualified directory path)가 됩니다.

8. 마지막 단계로, 테이블 t1(TABLE_ID = 71)에 로우를 하나 insert하고 INNODB_TABLESTATS 테이블의 데이터를 확인합니다. 이 테이블의 데이터는 MySQL 옵티마이저가 InnoDB 테이블을 쿼리할 때 어떤 인덱스를 사용할지 계산하는 데 사용됩니다. 이 정보는 메모리 내 데이터 구조에서 파생됩니다.

1mysql> INSERT INTO t1 VALUES(5, 'abc', 'def');
2Query OK, 1 row affected (0.06 sec)
3
4mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where TABLE_ID = 71 \G
5*************************** 1. row ***************************
6            TABLE_ID: 71
7                NAME: test/t1
8STATS_INITIALIZED: Initialized
9            NUM_ROWS: 1
10    CLUST_INDEX_SIZE: 1
11    OTHER_INDEX_SIZE: 0
12    MODIFIED_COUNTER: 1
13             AUTOINC: 0
14           REF_COUNT: 1

STATS_INITIALIZED 필드는 테이블에 대해 통계가 수집되었는지 여부를 나타냅니다. NUM_ROWS는 테이블의 현재 추정 로우 수입니다. CLUST_INDEX_SIZE 및 OTHER_INDEX_SIZE 필드는 각각 테이블의 클러스터형 인덱스 및 보조 인덱스를 저장하는 디스크 상 페이지 수를 보고합니다. MODIFIED_COUNTER 값은 DML 작업 및 외래 키에서의 cascade 작업에 의해 수정된 로우 수를 보여줍니다. AUTOINC 값은 autoincrement 기반 작업에 대해 다음에 발급될 숫자입니다. 테이블 t1에는 autoincrement 컬럼이 정의되어 있지 않으므로, 값은 0입니다. REF_COUNT 값은 카운터이며, 이 카운터가 0이 되면 테이블 메타데이터를 테이블 캐시에서 제거(evict)할 수 있음을 의미합니다.

Example 17.3 Foreign Key INFORMATION_SCHEMA Schema Object Tables

INNODB_FOREIGN 및 INNODB_FOREIGN_COLS 테이블은 외래 키 관계에 대한 데이터를 제공합니다. 이 예제에서는 외래 키 관계를 가진 parent 테이블과 child 테이블을 사용하여 INNODB_FOREIGN 및 INNODB_FOREIGN_COLS 테이블에서 볼 수 있는 데이터를 보여줍니다.

1. parent 및 child 테이블을 포함하는 test 데이터베이스를 생성합니다:

1mysql> CREATE DATABASE test;
2
3mysql> USE test;
4
5mysql> CREATE TABLE parent (id INT NOT NULL,
6          PRIMARY KEY (id)) ENGINE=INNODB;
7
8mysql> CREATE TABLE child (id INT, parent_id INT,
9       ->     INDEX par_ind (parent_id),
10       ->     CONSTRAINT fk1
11       ->     FOREIGN KEY (parent_id) REFERENCES parent(id)
12       ->     ON DELETE CASCADE) ENGINE=INNODB;

2. parent 및 child 테이블을 생성한 후, INNODB_FOREIGN을 쿼리하여 test/child와 test/parent 간 외래 키 관계에 대한 외래 키 데이터를 찾습니다:

1mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN \G
2*************************** 1. row ***************************
3         ID: test/fk1
4FOR_NAME: test/child
5REF_NAME: test/parent
6     N_COLS: 1
7       TYPE: 1

메타데이터에는 외래 키 ID(fk1)가 포함되며, child 테이블에 정의된 CONSTRAINT 이름을 따서 지정됩니다. FOR_NAME은 외래 키가 정의된 child 테이블의 이름입니다. REF_NAME은 parent 테이블(“referenced” 테이블)의 이름입니다. N_COLS는 외래 키 인덱스의 컬럼 수입니다. TYPE은 외래 키 컬럼에 대한 추가 정보를 제공하는 비트 플래그를 나타내는 숫자 값입니다. 이 경우 TYPE 값은 1이며, 외래 키에 대해 ON DELETE CASCADE 옵션이 지정되었음을 나타냅니다. TYPE 값에 대한 자세한 내용은 INNODB_FOREIGN 테이블 정의를 참조하십시오.

3. 외래 키 ID를 사용하여, 외래 키 컬럼에 대한 데이터를 보기 위해 INNODB_FOREIGN_COLS를 쿼리합니다.

1mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE ID = 'test/fk1' \G
2*************************** 1. row ***************************
3             ID: test/fk1
4FOR_COL_NAME: parent_id
5REF_COL_NAME: id
6            POS: 0

FOR_COL_NAME은 child 테이블에서 외래 키 컬럼의 이름이며, REF_COL_NAME은 parent 테이블에서 참조되는 컬럼의 이름입니다. POS 값은 0부터 시작하는 외래 키 인덱스 내 키 필드의 ordinal 위치입니다.

Example 17.4 Joining InnoDB INFORMATION_SCHEMA Schema Object Tables

이 예제는 세 개의 InnoDB``INFORMATION_SCHEMA schema object 테이블(INNODB_TABLES, INNODB_TABLESPACES, INNODB_TABLESTATS)을 조인하여 employees 샘플 데이터베이스 내 테이블에 대한 파일 포맷, 로우 포맷, 페이지 크기, 인덱스 크기 정보를 수집하는 방법을 보여줍니다.

쿼리 문자열을 짧게 하기 위해 다음과 같은 테이블 별칭을 사용합니다:

• INFORMATION_SCHEMA.INNODB_TABLES:
  a

• INFORMATION_SCHEMA.INNODB_TABLESPACES:
  b

• INFORMATION_SCHEMA.INNODB_TABLESTATS:
  c

압축된 테이블을 처리하기 위해 IF() 제어 흐름 함수가 사용됩니다. 테이블이 압축된 경우, 인덱스 크기는 PAGE_SIZE가 아닌 ZIP_PAGE_SIZE를 사용하여 계산됩니다. 바이트 단위로 보고되는 CLUST_INDEX_SIZE 및 OTHER_INDEX_SIZE는 1024*1024로 나누어 메가바이트(MB) 단위 인덱스 크기를 제공합니다. MB 값은 ROUND() 함수를 사용하여 소수점 이하 자릿수를 0으로 반올림합니다.

1mysql> SELECT a.NAME, a.ROW_FORMAT,
2        @page_size :=
3         IF(a.ROW_FORMAT='Compressed',
4          b.ZIP_PAGE_SIZE, b.PAGE_SIZE)
5          AS page_size,
6         ROUND((@page_size * c.CLUST_INDEX_SIZE)
7          /(1024*1024)) AS pk_mb,
8         ROUND((@page_size * c.OTHER_INDEX_SIZE)
9          /(1024*1024)) AS secidx_mb
10       FROM INFORMATION_SCHEMA.INNODB_TABLES a
11       INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES b on a.NAME = b.NAME
12       INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESTATS c on b.NAME = c.NAME
13       WHERE a.NAME LIKE 'employees/%'
14       ORDER BY a.NAME DESC;
15+------------------------+------------+-----------+-------+-----------+
16| NAME                   | ROW_FORMAT | page_size | pk_mb | secidx_mb |
17+------------------------+------------+-----------+-------+-----------+
18| employees/titles       | Dynamic    |     16384 |    20 |        11 |
19| employees/salaries     | Dynamic    |     16384 |    93 |        34 |
20| employees/employees    | Dynamic    |     16384 |    15 |         0 |
21| employees/dept_manager | Dynamic    |     16384 |     0 |         0 |
22| employees/dept_emp     | Dynamic    |     16384 |    12 |        10 |
23| employees/departments  | Dynamic    |     16384 |     0 |         0 |
24+------------------------+------------+-----------+-------+-----------+