[MySQL] Information Schema - InnoDB 시스템 테이블

■ InnoDB 시스템 테이블

InnoDB INFORMATION_SCHEMA 시스템 테이블을 사용하여 InnoDB가 관리하는 스키마 객체에 대한 메타 데이터를 추출할 수 있습니다. 이 정보는 InnoDB 내부 시스템 테이블 (InnoDB 데이터 사전이라고도 함)에서 가져 오며, 일반 InnoDB 테이블처럼 직접 쿼리할 수 없습니다. 전통적으로 InnoDB 모니터의 기술을 사용하여 이러한 유형의 정보를 얻고, InnoDB 모니터를 설정하고 SHOW ENGINE INNODB STATUS 문에서 출력을 구문 분석합니다. InnoDB INFORMATION_SCHEMA 테이블 인터페이스를 사용하면 SQL을 사용하여이 데이터를 쿼리할 수 있습니다.

 

해당 내부 시스템 테이블이없는 INNODB_SYS_TABLESTATS를 제외하고 InnoDB INFORMATION_SCHEMA 시스템 테이블은 메모리에 캐시된 메타 데이터가 아닌 내부 InnoDB 시스템 테이블에서 직접 읽은 데이터로 채워집니다.

 

InnoDB INFORMATION_SCHEMA 시스템 테이블은 아래에 나열된 테이블을 포함합니다.

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_SYS%';
+--------------------------------------------+
| Tables_in_information_schema (INNODB_SYS%) |
+--------------------------------------------+
| INNODB_SYS_DATAFILES                       |
| INNODB_SYS_TABLESTATS                      |
| INNODB_SYS_FOREIGN                         |
| INNODB_SYS_COLUMNS                         |
| INNODB_SYS_INDEXES                         |
| INNODB_SYS_FIELDS                          |
| INNODB_SYS_TABLESPACES                     |
| INNODB_SYS_FOREIGN_COLS                    |
| INNODB_SYS_TABLES                          |
+--------------------------------------------+

 

테이블 이름은 제공된 데이터 유형을 나타냅니다.

+ INNODB_SYS_TABLES는 InnoDB 데이터 사전의 SYS_TABLES 테이블에있는 정보와 동일한 InnoDB 테이블에 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_COLUMNS는 InnoDB 데이터 사전의 SYS_COLUMNS 테이블에있는 정보와 동일한 InnoDB 테이블 열에 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_INDEXES는 InnoDB 데이터 사전의 SYS_INDEXES 테이블에있는 정보와 동일한 InnoDB 인덱스에 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_FIELDS는 InnoDB 데이터 사전의 SYS_FIELDS 테이블에있는 정보와 동일한 InnoDB 인덱스의 키 열 (필드)에 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_TABLESTATS는 인 메모리 데이터 구조에서 파생 된 InnoDB 테이블에 대한 하위 수준 상태 정보보기를 제공합니다. 해당 내부 InnoDB 시스템 테이블이 없습니다.

+ INNODB_SYS_DATAFILES는 InnoDB 데이터 딕셔너리의 SYS_DATAFILES 테이블에있는 정보와 동일한 InnoDB 테이블 당 파일 및 일반 테이블 스페이스에 대한 데이터 파일 경로 정보를 제공합니다.

+ INNODB_SYS_TABLESPACES는 InnoDB 데이터 딕셔너리의 SYS_TABLESPACES 테이블에있는 정보와 동일한 InnoDB 테이블 당 파일 및 일반 테이블 스페이스에 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_FOREIGN은 InnoDB 데이터 사전의 SYS_FOREIGN 테이블에있는 정보와 동일한 InnoDB 테이블에 정의 된 외래 키에 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_FOREIGN_COLS는 InnoDB 데이터 사전의 SYS_FOREIGN_COLS 테이블에있는 정보와 동일한 InnoDB 테이블에 정의 된 외래 키 열에 대한 메타 데이터를 제공합니다.

 

InnoDB INFORMATION_SCHEMA 시스템 테이블은 TABLE_ID, INDEX_ID 및 SPACE와 같은 필드를 통해 함께 결합될 수 있으므로 테스트나 모니터링하려는 용도에 대해 사용 가능한 모든 데이터를 쉽게 검색할 수 있습니다.

 

▶︎ 예제 1 : InnoDB INFORMATION_SCHEMA 시스템 테이블

이 예에서는 단일 인덱스(i1)가 있는 단순 테이블(t1)을 사용하여 InnoDB INFORMATION_SCHEMA 시스템 테이블에서 발견된 메타 데이터 유형을 보여줍니다.

 

1. 테스트용 데이터베이스와 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);

 

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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: test/t1
         FLAG: 1
       N_COLS: 6
        SPACE: 57
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
...

테이블 t1의 TABLE_ID는 71입니다. FLAG필드는 테이블 형식 및 저장 특성에 대한 비트 레벨 정보를 제공합니다. 6개의 컬럼이 있으며 그 중 3개는 InnoDB (DB_ROW_ID, DB_TRX_ID 및 DB_ROLL_PTR)에서 만든 숨겨진 컬럼입니다. 테이블의 SPACE ID는 57입니다 (값 0은 테이블이 시스템 테이블 스페이스에 있음을 나타냄). FILE_FORMAT은 Antelope이고 ROW_FORMAT은 Compact입니다. ZIP_PAGE_SIZE는 압축된 행 형식이 있는 테이블에만 적용됩니다.

 

3. INNODB_SYS_TABLES의 TABLE_ID 정보를 사용하여 INNODB_SYS_COLUMNS 테이블에서 테이블의 컬럼에 대한 정보를 쿼리합니다.

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

TABLE_ID 및 컬럼 NAME 외에도 INNODB_SYS_COLUMNS는 각 컬럼 (0에서 시작하여 순차적으로 증가), 컬럼 MTYPE 또는 "주요 유형"(6=INT, 2=CHAR, 1=VARCHAR), PRTYPE 또는 "정확한 유형"(MySQL 데이터 유형, 문자 세트 코드 및 널 허용 여부를 나타내는 비트가있는 이진 값) 및 열 길이 (LEN)의 서수 위치 (POS)를 제공합니다.

 

4. INNODB_SYS_TABLES의 TABLE_ID 정보를 다시 사용하여 INNODB_SYS_INDEXES를 쿼리하여 테이블 t1과 관련된 인덱스에 대한 정보를 확인합니다.

 

 

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

 

INDEX_ID는 인스턴스의 모든 데이터베이스에서 고유한 인덱스의 식별자입니다. TABLE_ID는 인덱스가 연결된 테이블을 식별합니다. 인덱스 TYPE값은 인덱스 유형을 나타냅니다(1=클러스터형 인덱스, 0=보조 인덱스). N_FILEDS 값은 인덱스를 구성하는 필드의 수입니다. PAGE_NO는 인덱스 B-tree의 루트 페이지 번호이고 SPACE는 인덱스가 있는 테이블 스페이스의 ID입니다. 0이 아닌 값은 인덱스가 시스템 테이블 스페이스에 상주하지 않음을 나타냅니다. MERGE_THRESHOLD는 인덱스 페이지의 데이터 양에 대한 백분율 임계 값을 정의합니다. 행이 삭제되거나 업데이트 작업으로 인해 행이 축소 될 때 인덱스 페이지의 데이터 양이이 값 (기본값은 50 %) 미만으로 떨어지면 InnoDB는 인덱스 페이지를 인접 인덱스 페이지와 병합하려고 시도합니다.

 

 

 

5. INNODB_SYS_INDEXES의 INDEX_ID 정보를 사용하여 인덱스 i1의 필드에 대한 정보를 INNODB_SYS_FIELDS에 쿼리합니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS where INDEX_ID = 112 \G
*************************** 1. row ***************************
INDEX_ID: 112
    NAME: col1
     POS: 0

INNODB_SYS_FIELDS는 인덱싱된 필드의 이름과 인덱스 내 서수 위치를 제공합니다. 인덱스(i1)가 여러 필드에 정의된 경우 INNODB_SYS_FIELDS는 인덱싱된 각 필드에 대한 메타 데이터를 제공합니다.

 

6. INNODB_SYS_TABLES의 SPACE 정보를 사용하여 INNODB_SYS_TABLESPACES 테이블에서 테이블의 테이블 스페이스에 대한 정보를 쿼리합니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 57 \G
*************************** 1. row ***************************
        SPACE: 57
         NAME: test/t1
         FLAG: 0
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact or Redundant
    PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0

INNODB_SYS_TABLESPACES는 테이블 스페이스의 SPACE ID 및 관련 테이블의 NAME 외에도 테이블 스페이스 형식 및 스토리지 특성에 대한 비트 레벨 정보인 테이블 스페이스 FLAG 데이터를 제공합니다. 테이블 스페이스 FILE_FORMAT, ROW_FORMAT, PAGE_SIZE 및 기타 여러 테이블 스페이스 메타 데이터 항목도 제공됩니다.

 

7. 다시 한 번 INNODB_SYS_TABLES의 SPACE 정보를 사용하여 INNODB_SYS_DATAFILES에서 테이블 스페이스 데이터 파일의 위치를 쿼리합니다.

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

데이터 파일은 MySQL의 데이터 디렉토리 아래에있는 테스트 디렉토리에 있습니다. CREATE TABLE 문의 DATA DIRECTORY 절을 사용하여 MySQL 데이터 디렉토리 외부에 테이블 당 파일 테이블 스페이스가 생성된 경우 테이블 스페이스 PATH는 정규화된 디렉토리 경로가 됩니다.

 

8. 마지막 단계로 테이블 t1 (TABLE_ID=71)에 행을 삽입하고 INNODB_SYS_TABLESTATS 테이블의 데이터를 확인합니다. 이 테이블의 데이터는 MySQL 최적화 프로그램에서 InnoDB 테이블을 쿼리할 때 사용할 인덱스를 계산하는 데 사용됩니다. 이 정보는 메모리 내 데이터 구조에서 파생됩니다. 해당 내부 InnoDB 시스템 테이블이 없습니다.

mysql> INSERT INTO t1 VALUES(5, 'abc', 'def');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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에 도달하면 테이블 캐시에서 테이블 메타 데이터를 제거할 수 있음을 나타냅니다.

 

▶︎ 예제 2 : 외래키 INFORMATION_SCHEMA 시스템 테이블

INNODB_SYS_FOREIGN 및 INNODB_SYS_FOREIGN_COLS 테이블은 외래키 관계에 대한 데이터를 제공합니다. 이 예에서는 INNODB_SYS_FOREIGN 및 INNODB_SYS_FOREIGN_COLS 테이블에서 찾은 데이터를 보여주기 위해 외래 키 관계가있는 상위 테이블 및 하위 테이블을 사용합니다.

 

1. 상위 및 하위 테이블이 있는 테스트 데이터베이스를 만듭니다.

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;

 

2. 상위 및 하위 테이블이 생성된 후 INNODB_SYS_FOREIGN을 쿼리하고 test/child 및 test/parent 외래키 관계에 대한 외래키 데이터를 찾습니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN \G
*************************** 1. row ***************************
      ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1

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

 

3. 외래키 ID를 사용하여 INNODB_SYS_FOREIGN_COLS를 쿼리하여 외래키의 컬럼에 대한 데이터를 봅니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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 is the name of the foreign key column in the child table, and REF_COL_NAME is the name of the referenced column in the parent table. The POS value is the ordinal position of the key field within the foreign key index, starting at zero.

FOR_COL_NAME은 자식 테이블에 있는 외래키 컬럼의 이름이고 REF_COL_NAME은 부모 테이블에 있는 참조된 컬럼의 이름입니다. POS 값은 0에서 시작하는 외래 키 인덱스 내 키 필드의 서수 위치입니다.

 

▶︎ 예제 3 : InnoDB INFORMATION_SCHEMA 시스템 테이블 결합

이 예는 세 개의 InnoDB INFORMATION_SCHEMA 시스템 테이블 (INNODB_SYS_TABLES, INNODB_SYS_TABLESPACES 및 INNODB_SYS_TABLESTATS)을 결합하여 직원 샘플 데이터베이스의 테이블에 대한 파일 형식, 행 형식, 페이지 크기 및 인덱스 크기 정보를 수집하는 방법을 보여줍니다.

 

다음 테이블 이름 별칭은 쿼리 문자열(테이블 혹은 뷰)을 짧게 줄이는데 사용됩니다.

+ INFORMATION_SCHEMA.INNODB_SYS_TABLES: a

+ INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES: b

+ INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS: c

 

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

mysql> SELECT a.NAME, a.FILE_FORMAT, 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_SYS_TABLES a
       INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES b on a.NAME = b.NAME
       INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS c on b.NAME = c.NAME
       WHERE a.NAME LIKE 'employees/%'
       ORDER BY a.NAME DESC;
+------------------------+-------------+------------+-----------+-------+-----------+
| NAME                   | FILE_FORMAT | ROW_FORMAT | page_size | pk_mb | secidx_mb |
+------------------------+-------------+------------+-----------+-------+-----------+
| employees/titles       | Antelope    | Compact    |     16384 |    20 |        11 |
| employees/salaries     | Antelope    | Compact    |     16384 |    91 |        33 |
| employees/employees    | Antelope    | Compact    |     16384 |    15 |         0 |
| employees/dept_manager | Antelope    | Compact    |     16384 |     0 |         0 |
| employees/dept_emp     | Antelope    | Compact    |     16384 |    12 |        10 |
| employees/departments  | Antelope    | Compact    |     16384 |     0 |         0 |
+------------------------+-------------+------------+-----------+-------+-----------+