InnoDB 테이블 변경 작업의 일괄 처리

MySQL 5.1 미만의 버전에서는 InnoDB 테이블의 변경 작업

(컬럼 또는 인덱스의 추가 삭제 수정)은 InnoDB 테이블 전체의 데이터를 다른 테이블로 복사를 하면서 처리된다.

이로 인해서 간단한 추가나 인덱스 작업마저도 상당한 시간이 소요되며, 

이러한 작업들은 일반적으로 해당 테이블 전체에 대해서 Lock을 점유하기 때문에

실시간 사용자들의 접근을 모두 대기 상태로 만들어 버리게 된다.

MySQL 5.1 이상의 버전에서는 InnoDB가 Plugin버전으로 제공되며,

InnoDB Plugin을 이용한 테이블에서는 인덱스 작업은 테이블의 데이터 복사를 피할 수 있다.

(하지만 여전히 Primary key 변경이나 컬럼 변경 작업은 기존과 동일한 방식으로 처리된다.)

그래서, 이러한 작업의 처리 시간을 최소화하기 위해서는

여러 가지 변경 작업들을 한꺼번에 모아서 처리하는 것이 가장 좋다.

사실 간단한 방법이지만, 이 방법을 모르는 사람들이 의외로 많은 듯 하여 글을 작성한다.

MySQL의 ALTER TABLE 명령문에는 여러 개의 변경을 동시에 명시할 수 있다.

ALTER TABLE tb_test ADD INDEX ix_fd1_fd2 (fd1, fd2);

ALTER TABLE tb_test ADD COLUMN fd3 INT NOT NULL DEFAULT 0;

위와 같은 두 개의 작업을 별도로 처리하면 tb_test 테이블의 레코드 건수만큼 2배의 작업 소요시간이 걸리고,

그 시간 동안 테이블이 잠기게 된다. 하지만, 이 처리를 아래와 같이 하나의 문장으로 묶어서 처리할 수 있다.

ALTER TABLE tb_test ADD COLUMN fd3 INT NOT NULL DEFAULT 0 ,

                    ADD INDEX ix_fd1_fd2(fd1, fd2);

이렇게 한 문장으로 묶어서 실행하면, MySQL은 이 두 가지 작업을 묶어서 한번의 테이블 데이터 복사 작업으로 완료하게 된다.

(각 ALTER 문장 단위로 구분자 ","를 꼭 붙혀줘야 한다.)

InnoDB Index (Primary key, Secondary key) 특성

Primary key (Clustering Index)

• InnoDB 테이블은 기본적으로 모두 Primary key에 의해서 클러스터링되어 있음
• Primary key가 정의되지 않은 테이블은 아래 순서대로 내부 PK를 선정함
  - Unique index로 정의된 컬럼이 있으면 그 중에서 하나를 내부적인 PK로 선정하여, 이 컬럼값으로 Clustering 구축
  - Unique index가 없으면, 내부적으로 6바이트 숫자값을 이용한 PK를 생성해서 Clustering 구축
    (내부적인 6바이트 숫자값은 사용자가 사용할 수 없음 - 숨겨진 컬럼정도로 해석) 
• Primary key 값에 의해서 레코드의 물리적 위치가 결정되며, Primary key값 밑에 모든 레코드 데이터가 저장됨
• 대량의 INSERT가 실행되는 경우, PK에 의해서 정렬되지 않은 경우에는 상당히 느려질 수 있음
• - INSERT를 위해서는 해당 데이터 페이지와 인덱스 페이지를 먼저 읽어야 함
  - 매 INSERT 문장마다, 필요한 인덱스 페이지나 데이터 페이지가 각각 다르다면 매번 해당 페이지를 읽어야 함
  - 이 경우, 페이지의 분할 및 프레그멘테이션을 유발하며, Page의 공간을 효율적으로 사용하지 못하게 될 수도 있음 
• PK에 대한 범위 검색은 빠름 (PK에 의해서 Clustering되어 있으므로)
• PK를 변경하는 작업은 테이블 전체의 재구성이 필요하므로 느림

Secondary Index

• Primary key가 아닌 모든 인덱스는 레코드의 주소가 아닌 Primary key 값을 가짐
  - PK가 아닌 인덱스로 검색 시, 실제 레코드를 찾기 위해서는 2번의 검색이 필요함
  - 레코드의 위치가 변경되어도 나머지 모든 인덱스의 내용을 변경할 필요 없음(해당 레코드의 주소값을 가지지 않으므로)
  - PK의 값이 긴 경우에는, 전체 인덱스의 사이즈가 매우 커질 수 있음 
• InnoDB 테이블의 인덱스는 트랜잭션 정보를 추가적으로 가지고 있음
  - Covering index용 쿼리에서 레코드의 Visibility를 판단하기 위함 
• Unique 가 아닌 보조 인덱스는 Insert Buffer를 통해서 즉시 Index page에 추가되지 않음
  - 실시간으로 인덱스의 변경을 적용하지 않아도 되기 때문에 인덱스 변경이 빠름
  - 시스템이 한가해지면, Insert buffer merge Thread가 병합 작업 수행

InnoDB 테이블 및 인덱스의 내부 구조 엿보기

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

----------------------------------------------------------------------------------------