InnoDB는 innodb_table_monitor 를 이용한 테이블 및 인덱스 구조 및 메타 정보 확인하는 방법을 제공하고 있다.
create table innodb_table_monitor (fd1 int) engine=innodb;
이 명령을 실행해서 테이블을 생성해두면, 특정 시간 (몇 초)단위로 테이블의 정보를 MySQL 에러 로그 파일로 덤프하게 된다.
간단히 아래와 같은 테스트용 테이블을 생성해두고, 덤프 내용과 한번 비교를 해보았다.
CREATE TABLE tab_pk (
fdpk int(10) unsigned NOT NULL AUTO_INCREMENT,
fd1 tinyint(3) unsigned NOT NULL DEFAULT '0',
fd2 varchar(200) NOT NULL DEFAULT '',
fd3 mediumtext,
fd4 datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
fd5 datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
fd6 int(10) unsigned NOT NULL DEFAULT '0',
fd7 enum('sticky','general') NOT NULL DEFAULT 'general',
PRIMARY KEY (fdpk),
KEY ix_fd1_fd7_fdpk_fd6 (fd1,fd7,fdpk,fd6),
KEY ix_fd2 (fd2),
KEY ix_fd2_fdpk (fd2, fdpk)
) ENGINE=InnoDB;
CREATE TABLE tab_nopk (
fd1 int(11) DEFAULT NULL,
fd2 char(10) DEFAULT NULL,
fd3 varchar(10) DEFAULT NULL,
KEY ix_index (fd2)
) ENGINE=InnoDB;
----------------------------------------------------------------------------------------
tab_pk 테이블의 구조 (내용의 배치는 조금 변경됨)
----------------------------------------------------------------------------------------
TABLE: name test/tab_pk, id 0 106, columns 11, indexes 4, appr.rows 25
COLUMNS:
fdpk: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4;
fd1: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 1;
fd2: DATA_VARMYSQL DATA_NOT_NULL len 600;
fd3: DATA_BLOB len 11;
fd4: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 8;
fd5: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 8;
fd6: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4;
fd7: DATA_INT DATA_UNSIGNED DATA_NOT_NULL len 1;
DB_ROW_ID: DATA_SYS prtype 256 len 6;
DB_TRX_ID: DATA_SYS prtype 257 len 6;
DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
INDEX: name PRIMARY, id 0 194, fields 1/10, uniq 1, type 3
root page 3, appr.key vals 25, leaf pages 3, size pages 4
FIELDS:
fdpk
DB_TRX_ID
DB_ROLL_PTR
fd1
fd2
fd3
fd4
fd5
fd6
fd7
INDEX: name ix_fd1_fd7_fdpk_fd6, id 0 195, fields 4/4, uniq 4, type 0
root page 4, appr.key vals 21, leaf pages 1, size pages 1
FIELDS:
fd1
fd7
fdpk
fd6
INDEX: name ix_fd2, id 0 196, fields 1/2, uniq 2, type 0
root page 5, appr.key vals 11, leaf pages 1, size pages 1
FIELDS:
fd2
fdpk
INDEX: name ix_fd2_fdpk, id 0 197, fields 2/2, uniq 2, type 0
root page 6, appr.key vals 21, leaf pages 1, size pages 1
FIELDS:
fd2
fdpk
----------------------------------------------------------------------------------------
이 결과 내용 몇 가지를 간단히 짚어보면
- COLUMNS 영역을 보면, InnoDB가 내부적으로 생성한 컬럼 3개 (DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR)를 확인할 수 있다.
- DB_ROW_ID는 InnoDB가 테이블에서 Cluster key로 사용할 값이 없을 때를 위한 내부 컬럼이다.
- DB_TRX_ID는 현재 레코드를 변경했던, 또는 하고 있는 트랜잭션의 번호를 저장하는 컬럼이다.
- DB_ROLL_PTR은 트랜잭션 롤백이나 MVCC를 위한 Rollback/Undo 데이터를 가리키는 포인터가 저장되는 컬럼이다.
- INDEX중에서 PRIMARY 라고 이름 붙어있는 것이 PRIMARY KEY (CLUSTER KEY)이다. 그래서 모든 COLUMNS 영역에 표시되어 있는 모든 컬럼들이 나열되어 있는 것을 확인할 수 있다. 하지만 DB_ROW_ID는 여기에서 빠져 있다. InnoDB 테이블은 Clustering Index를 사용하므로, Primary key에 모든 컬럼이 붙어 있어야 하는 것이 정상이다. 그런데 왜 여기에 DB_ROW_ID가 표기되지 않았을까 ? (모르겠다. 실제로 PK나 NOT NULL UNIQUE KEY가 없는 경우에만 만들어지는 컬럼으로 알고 있었는데... 실제로는 없는데 가상 컬럼으로 보여주고 있는것인지도 모르겠다.)
- ix_fd2 INDEX의 내용을 보면, fd2 컬럼으로만 인덱스를 만들었지만, InnoDB 인덱스의 특성상(Clustering) 뒤에 자동적으로 fdpk 컬럼이 추가된 것을 확인할 수 있다.
- ix_fd2_fdpk INDEX의 내용을 보면, 명시적으로 fd2와 fdpk 컬럼으로 인덱스를 만들었지만, 내부적으로는 fd2 컬럼만으로 인덱스를 생성한 것과 동일하게 PRIMARY KEY인 fdpk가 두번 연속으로 추가되지 않았다는 것을 확인할 수 있다.
- ix_fd1_fd7_fdpk_fd6 INDEX의 내용을 보면, fdpk가 3번째 컬럼으로 추가되었음에도 불구하고, 마지막에 fdpk가 추가되지 않는다는 것을 알 수 있다.
- 이 결과를 통해서, InnoDB 테이블의 모든 인덱스에는 Index를 구성하는 컬럼에 전혀 관계없이 항상 마지막에 PRIMARY KEY가 포함될 것으로 생각했지만, 상황에 따라서 PRIMARY KEY가 마지막이 아닌 중간에 추가될 수도 있다는 것을, 그리고 중복되어서 추가되지 않는다는 것을 알 수 있다.
----------------------------------------------------------------------------------------
tab_nopk 테이블의 구조 (내용의 배치는 조금 변경됨)
----------------------------------------------------------------------------------------
TABLE: name test/tab_nopk, id 0 108, columns 6, indexes 2, appr.rows 0
COLUMNS:
fd1: DATA_INT DATA_BINARY_TYPE len 4;
fd2: DATA_MYSQL len 30;
fd3: DATA_VARMYSQL len 30;
DB_ROW_ID: DATA_SYS prtype 256 len 6;
DB_TRX_ID: DATA_SYS prtype 257 len 6;
DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
INDEX: name GEN_CLUST_INDEX, id 0 199, fields 0/6, uniq 1, type 1
root page 3, appr.key vals 0, leaf pages 1, size pages 1
FIELDS:
DB_ROW_ID
DB_TRX_ID
DB_ROLL_PTR
fd1
fd2
fd3
INDEX: name ix_index, id 0 200, fields 1/2, uniq 2, type 0
root page 4, appr.key vals 0, leaf pages 1, size pages 1
FIELDS:
fd2
DB_ROW_ID
----------------------------------------------------------------------------------------
이번에는 PRIMARY KEY가 없는 테이블의 구조를 잠깐 살펴보면,
- COLUMNS 항목의 내용은 PRIMARY KEY 여부에 관계없이 동일하게 출력되었다.
- GEN_CLUST_INDEX INDEX의 내용을 보면,InnoDB 테이블과 인덱스를 생성하게 되면, DB_ROW_ID 라는 내부 컬럼이 제일 먼저 위치해 있는 것을 확인할 수 있다. 즉, 이 테이블에서 가상의 PRIMARY KEY 역할을 DB_ROW_ID라는 컬럼이 대행하고 있음을 확인할 수 있다. 또한, 인덱스의 이름이 PRIMARY 가 아니라 GEN_CLUST_INDEX라는 내부적으로 명명된 이름이 사용되고 있다.
- ix_index INDEX의 내용을 보면, fd2 컬럼만으로 인덱스를 생성했지만, 뒤에 PRIMARY KEY 컬럼을 대신해서 DB_ROW_ID 컬럼이 자동적으로 추가된 것을 확인할 수 있다.
- 이 내용을 통해서 명시적으로 PRIMARY KEY를 추가하지 않아도, InnoDB가 내부적으로 가상의 PRIMARY KEY 대행 컬럼을 사용하는 것을 확인할 수 있다. 하지만, DB_ROW_ID 컬럼은 외부에 노출되지 않으므로 쿼리에 사용할 수 없다. 그래서 필요치 않다 하더라도 INTEGER와 같은 대체키(Surrogate)를 포함시켜 주는 것이 좋을 듯 하다.
그 이외에도, 간단히 각 인덱스의 ROOT 페이지의 수와 LEAF 페이지의 수를 확인해 볼 수 있는데, 인덱스의 페이지 수는 쿼리의 처리 성능과도 밀접한 관계가 있으므로 면밀히 검토해 보는 것은 의미가 있을 듯 하다.
참고로, 여러 개의 컬럼이 PRIMARY KEY를 구성하는 경우, 각 상황 별 인덱스의 구성도 확인해 보았다.
PRIMARY KEY의 구성에 따라서, 각 인덱스에 PRIMARY KEY 컬럼이 어떻게 자동적으로 추가되는지 확인해 보는 것도 좋을 듯 하다. (PRIMARY KEY가 자동으로 추가되는 방식에 따라서 인덱스 검색이나 정렬의 사용 여부도 영향이 있을 듯 하므로)
CREATE TABLE tab_doublepk (
fdpk1 int(11) NOT NULL DEFAULT '0',
fdpk2 int(11) NOT NULL DEFAULT '0',
fd1 int(11) DEFAULT NULL,
fd2 int(11) DEFAULT NULL,
fd3 int(11) DEFAULT NULL,
PRIMARY KEY (fdpk1,fdpk2),
KEY ix_fd1_fdpk1_fd2 (fd1,fdpk1,fd2),
KEY ix_fd1_fdpk2_fd2 (fd1,fdpk2,fd2),
KEY ix_fd1_fdpk2_fdpk1_fd2 (fd1,fdpk2,fdpk1,fd2),
KEY ix_fd1_fd2 (fd1,fd2)
) ENGINE=InnoDB
----------------------------------------------------------------------------------------
tab_doublepk 테이블의 구조 (내용의 배치는 조금 변경됨)
----------------------------------------------------------------------------------------
TABLE: name test/tab_doublepk, id 0 109, columns 8, indexes 5, appr.rows 0
COLUMNS:
fdpk1: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;
fdpk2: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;
fd1: DATA_INT DATA_BINARY_TYPE len 4;
fd2: DATA_INT DATA_BINARY_TYPE len 4;
fd3: DATA_INT DATA_BINARY_TYPE len 4;
DB_ROW_ID: DATA_SYS prtype 256 len 6;
DB_TRX_ID: DATA_SYS prtype 257 len 6;
DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
INDEX: name PRIMARY, id 0 201, fields 2/7, uniq 2, type 3
root page 3, appr.key vals 0, leaf pages 1, size pages 1
FIELDS:
fdpk1
fdpk2
DB_TRX_ID
DB_ROLL_PTR
fd1
fd2
fd3
INDEX: name ix_fd1_fdpk1_fd2, id 0 202, fields 3/4, uniq 4, type 0
root page 4, appr.key vals 0, leaf pages 1, size pages 1
FIELDS:
fd1
fdpk1
fd2
fdpk2
INDEX: name ix_fd1_fdpk2_fd2, id 0 203, fields 3/4, uniq 4, type 0
root page 5, appr.key vals 0, leaf pages 1, size pages 1
FIELDS:
fd1
fdpk2
fd2
fdpk1
INDEX: name ix_fd1_fdpk2_fdpk1_fd2, id 0 204, fields 4/4, uniq 4, type 0
root page 6, appr.key vals 0, leaf pages 1, size pages 1
FIELDS:
fd1
fdpk2
fdpk1
fd2
INDEX: name ix_fd1_fd2, id 0 205, fields 2/4, uniq 4, type 0
root page 7, appr.key vals 0, leaf pages 1, size pages 1
FIELDS:
fd1
fd2
fdpk1
fdpk2
----------------------------------------------------------------------------------------
댓글 없음:
댓글 쓰기