[MySQL] 클러스터 인덱스 미생성 테이블

개요

MS-SQL에서는 클러스터 인덱스가 없는 힙 테이블이란 개념이 존재했었는데 MySQL은 테이블 생성 시 내부적으로 클러스터 인덱스가 생성되어 어떤 경우에 해당 인덱스가 생성되는지 알아보겠습니다.

 

 

클러스터 인덱스가 없는 테이블 생성

MySQL의 InnoDB의 경우 Primary Key. 즉 클러스터 인덱스가 없는 테이블을 생성할 경우 칼럼이 NOT NULL로 구성된 UNIQUE KEY가 있을 경우 클러스터로 사용합니다. 별도의 키가 없을 경우에는 GEN_CLUST_INDEX라는 이름의 클러스터 인덱스가 생성됩니다. 해당 인덱스는 6바이트 크기로 물리적으로 저장된 순서로 인덱스 값이 부여됩니다.

 

 

case1. 인덱스 미생성 시

CREATE TABLE non_index(
  col1 int(11) NULL,
  col2 char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

SELECT * 
 FROM information_schema.INNODB_SYS_INDEXES
WHERE TABLE_ID = (SELECT TABLE_ID  FROM information_schema.INNODB_SYS_TABLES ist WHERE name like 'omty/non_index')

INFORMATION_SCHEMA의 INNODB_SYS_INDEXES 테이블을 조회할 경우 GEN_CLUST_INDEX라는 인덱스가 생성된 것을 확인할 수 있습니다.

 

 

case 2. 보조 인덱스 생성 시

CREATE TABLE exist_index(
  col1 int(11) NULL,
  col2 char(10) DEFAULT NULL,
  KEY ix_index (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

SELECT * 
 FROM information_schema.INNODB_SYS_INDEXES
WHERE TABLE_ID = (SELECT TABLE_ID  FROM information_schema.INNODB_SYS_TABLES ist WHERE name like 'omty/exist_index')

보조 인덱스 생성 시에도 클러스터 인덱스를 대체할 키가 없을 경우 GEN_CLUST_INDEX 키가 생성됩니다.

 

 

case 3. 유니크 인덱스 생성 시(Null)

CREATE TABLE exist_unique_index_null(
  col1 int(11) NULL,
  col2 char(10) DEFAULT NULL,
  UNIQUE KEY ix_index (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

SELECT * 
 FROM information_schema.INNODB_SYS_INDEXES
WHERE TABLE_ID = (SELECT TABLE_ID  FROM information_schema.INNODB_SYS_TABLES ist WHERE name like 'omty/exist_unique_index_null')

UNIQUE 키를 생성할 경우에도 키 값이 NULL 허용일 경우에도 클러스터 인덱스 대체가 불가능하기에 GEN_CLUST_INDEX가 생성됩니다,

 

 

case4. 유니크 인덱스 생성 시(Not Null)

CREATE TABLE exist_unique_index_not_null(
  col1 int(11) NOT NULL,
  col2 char(10) DEFAULT NULL,
  UNIQUE KEY ix_index (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

SELECT * 
 FROM information_schema.INNODB_SYS_INDEXES
WHERE TABLE_ID = (SELECT TABLE_ID  FROM information_schema.INNODB_SYS_TABLES ist WHERE name like 'omty/exist_unique_index_not_null')

UNIQUE키의 컬럼이 NOT NULL일 경우 클러스터 인덱스 대체가 가능하기에 별도의 내부 인덱스 생성 없이 해당 인덱스를 클러스터 인덱스로 사용하게 됩니다.

 

 

결론

일부 DBMS의 경우 클러스터 인덱스가 없는 힙 테이블을 사용하여 인덱스 구성에 사용하는 비용을 최소화해 사용하는 경우도 있는데 MySQL의 경우 Primary Key를 지정하지 않아도 클러스터 인덱스가 내부적으로 생성되는 점을 유의해야 합니다.