MySQL Clustered Index 와 Fast Index Creation In InnoDB

Last Updated on 2월 15, 2021 by 태랑(정현호)

InnoDB - PK - Clustered Index



Clustered Index(클러스터형 인덱스)


기본 키(PK) 인덱스에 대한 InnoDB 용어입니다
InnoDB의 테이블은 Primary Key 값을 기반으로 구성되며 Cluster 라는 용어 의미대로 PK 값이 비슷한 레코드끼리 묶어서 저장되어 사용하고 그것을 Clustered Index(PK) 라고 합니다.

PK 기반의 테이블 구조이기 때문에 최상의 성능을 위해서는 성능에 가장 중요한 쿼리를 기반으로 신중하게 기본 키 컬럼이 선택되어야 합니다.

PK 값으로 클러스터링된 테이블은 PK 값 자체에 대한 의존도가 높기 때문에 Clustered Index(PK) 의 열을 수정하는 것은 매우 많은 비용이 들어갑니다.
그러므로 가급적 거의 업데이트되지 않거나 전혀 업데이트되지 않는 기본 열을 선택해야 합니다.





Clustered Index 의 구조는 일반 B-Tree 인덱스와 비슷한 구조로 되어 있으나 B-Tree Index 와 달리 Clustered Inex의 리프노드에는 레코드의 모든 컬럼 값이 같이 저장되어 있습니다

Clustered Index 그 자체가 하나의 테이블이며 인덱스 구조로 관리 되는 것입니다.

Oracle Database 에서는 이러한 유형의 테이블을 IOT(인덱스 구성 테이블) 이라고 합니다.


InnoDB에서 테이블 생성시 PK가 없으면 아래 순서대로 PK를 대체할 컬럼을 선택하게 됩니다.

1)NOT NULL 옵션의 유니크 인덱스 (unique index)중에서 첫 번째 인덱스를 클러스터 키로 선택
2)자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후 클러스터 키로 선택

InnoDB 스토리지 엔진이 적절한 키를 찾지 못하여 내부적으로 자동 증가 컬럼을 추가한 경우 이는 사용자에게 노출되지 않으며 쿼리에서 명시적으로 사용할 수 없습니다.




Fast Index Creation In InnoDB


Mysql 5.0 이하의 버전까지는 기존 데이터가 있는 테이블에 인덱스를 추가하거나 삭제하는 것은 매우 느리게 수행되었습니다.

구조적인 특징에 따라서 CREATE INDEX 및 DROP INDEX 문 수행시 새로운 테이블을 만든 다음 기존 행을 새 테이블에 하나씩 one-by-one 형식으로 복사하여 행이 삽입 될 때 인덱스를 업데이트하는 방식으로 작동하였습니다.

원본 테이블의 모든 행이 새로운 테이블로 복사 된 후 원본 테이블이 삭제되고 새로운 복사본 테이블의 이름이 원본 테이블 이름으로 rename 되는 형식으로 처리됩니다.


MySQL 5.5 이상 또는 InnoDB 플러그인이 있는 MySQL 5.1을 사용하면 InnoDB 테이블에 대한 보조 인덱스(Secondary Index) 생성 및 삭제가 이전보다 훨씬 빠르게 수행 할 수 있게 기능이 개선되었습니다.

이러한 기능 개선을 Fast Index Creation 이라고 합니다

5.6부터는 여기에서 더욱 더 많은 ONLINE Operation 이 가능하게 기능을 추가하여 Online DDL 기능이 추가 되었습니다.

빠른 생성을 위한 Fast Index Creation 에 의한 속도 향상은 Primary Key(기본키) 에는 적용되지 않고 Secondary Index(보조 인덱스) 에 만 해당 되는 사항 입니다.




InnoDB Primary Index is like IOT


InnoDB는 두가지 타입의 인덱스가 존재 합니다.

Clustered Index 와 Secondary Index

1.clustered index
InnoDB의 테이블의 row는 기본키 기반으로 구성된 clustered index에 저장되어 있고
clustered index 는 일반적으로 데이터를 포함되어 있는 B-tree구조 인덱스로 일부 다른 데이터베이스에서는 이러한 구조를 IOT(index-organized table) 라고 부릅니다.

테이블 구조가 기본키와 매우 밀접한 관계를 맺고 있기 때문에 PK 인덱스에 대한 Redefine Operation(adding or dropping) 작업은 어쩔수 없이 Fast Index Creation 기능 대신에 기존과 동일하게 table creating, data copy, rename 의 작업이 동반 됩니다.






2. secondary index

secondary index 는 오직 보조 인덱스는 실제 레코드가 저장된 주소값을 가지고 있는것이 아니라 프라이머리 키값을 저장하도록 구현돼 있습니다

이러한 인덱스 타입은 Created or Dropped 할때 데이터에 대한 복사 작업등이 불필요 하게 됩니다.
위에서 설명한것 처럼 각 Secondary index 는 primary key value 가지고 있기 때문입니다.
(사용이 필요하다면 clustered index를 접근해 사용함)


Dropping Secondary Index

dropping secondary index 는 매우 심플하게 진행 됩니다.
Internal InnoDB의 System Table 과 Data Dictionary Table 이 수정되고 사용하던 공간은 release(해제) 됩니다.
릴리즈된 공간은 테이블의 데이터나 새로운 index 등에서 사용되게 됩니다.


Adding Secondary Index

사용 중인 테이블에 Secondary Index 를 추가 할 경우 먼저 InnoDB 테이블을 스캔 합니다.
메모리 buffer 와 temporary file 을 이용하여 생성하려고 하는 secondary index의 key 컬럼의 값으로 정렬 합니다.
그리고 B-tree 방식으로 key-value로 정렬된 값으로 index를 생성하게 됩니다
이는 임의 순서로 인덱스 행을 생성하는 것보다 더 효율적이기 때문입니다.

Secondary Index에는 위에서 설명한것 처럼 기본 키 값 (클러스터형 인덱스에 액세스하는 데 사용됨)의 복사본이 포함되어 있으므로 기본 키(Clustered Index)의 정의를 변경하면 모든 보조 인덱스도 다시 생성되게 됩니다.





Concurrency Considerations for Fast Index Creation


secondary index가 Create 또는 Drop될때, table은 shared mode 로 lock 을 획득하게 됩니다.

테이블에 대한 write 작업은 blocked 되지만 read 작업은 가능합니다.

만약 clustered index를 alter(변경) 하였을 경우,테이블을 exclusive mode로 lock이 되기 때문에 write와 read 모두 허용 되지 않고 모든 작업이 blocked 처리가 되게 됩니다.
왜냐하면 이시간동안 데이터를 복제하는 작업이 수행되기 때문입니다.

새로운 테이블을 만든 다음 기존 행을 새 테이블에 하나씩 one-by-one 형식으로 복사하여 행이 삽입 될 때 인덱스를 업데이트하는 방식으로 동작하고 원본 테이블의 모든 행이 새로운 테이블로 복사 된 후 원본 테이블이 삭제 되고 새로운 복사본 테이블의 이름이 원본 테이블 이름으로 rename 되는 형식으로 처리됩니다

그래서 Clustered index(PK) 에대한 변경(alter) 를 수행 하는 과정 동안 모든 테이블에 대한 Operation은 block 됩니다

create index or alter table 구문은 항상 InnoDB 테이블에서 먼저 수행중인 트랜잭션에 대해서 commit 혹은 rollback이 될때, 특 트랜잭션 종료 될때 까지 기다리게 되고 선행 트랜잭션이 모두 완료가 된 후 수행할 수 있습니다.

alter table statement 로 InnoDB Primary key를 redefine 하게 되면 위에서 설명된 것 처럼 테이블을 엑세스하는 select 구문 과 commit이 포함된 트랜잭션 등 모든 트랜잭션은 대기 하게 됩니다.


New Secondary Index 
새롭게 만들어진 InnoDB Secondary Index에는 CREATE INDEX 나 ALTER TABLE 문장이 시작 되는 시점에서 Committed 된 데이터만 포함되게 됩니다.

새로 생성 된 인덱스에는 인덱스 생성 당시의 현재 데이터에 대한 정보 만 포함되어 있기 때문에 인덱스 생성 전에 삭제되거나 변경된 데이터를 확인해야하는 쿼리는 인덱스를 사용할 수 없습니다




Crash Recovery - Index Creation


Index 생성 과정에서 DB Crash가 발생하면 데이터는 손실되지 않지만 Index에 대한 Recovery 관련 수행 프로세스가 있습니다.
Clustered Index 와 Secondary Index 에 따라 복구 프로세스에서 차이가 있습니다.

InnoDB의 Secondary index가 생성 도중 Crash가 발생되었다면 복구시 Mysql 은 부분적으로 생성된(생성이 다 되지 못한) 인덱스에 대해서 삭제를 하게 됩니다.

즉 그 다음 Secondary Index 생성 작업을 다시 수행하면 됩니다.

Clustered index를 만드는 동안 Crash 가 발생하면 데이터가 손실되지 않지만 프로세스 중에 생성된 temp table 을 사용하여 복구 프로세스를 완료해야합니다.




Limitations of Fast Index Creation


- InnoDB 데이터 사전과 MySQL 데이터 사전 간의 일관성 문제를 방지하기 위해 ALTER TABLE … RENAME COLUMN 구문을 사용할 때 빠른 인덱스 생성을 사용하는 대신 테이블이 복사됩니다.


- 동일한 인덱스를 지칭하여 DROP INDEX 및 ADD INDEX 절이 같이 포함 된 ALTER TABLE 문은 빠른 인덱스 생성이 아닌 테이블 복사본을 사용합니다.

(하나의 단일) ALTER TABLE 문에서

DROP INDEX 및 ADD INDEX 절을 포함하는 구문이 포함되어 있고
모두 동일한 인덱스 사용시
아래 같은 Alter 문은 Fast Index Creation 이 아닌 테이블 복사가 사용한다고 명시 되어 있습니다

ALTER TABLE MyTable
DROP INDEX MyIndex
ADD INDEX MyIndex(MyColumn);


- InnoDB 테이블에 대한 OPTIMIZE TABLE 은 테이블을 재 구축하고 인덱스 통계를 업데이트하고 클러스터형 인덱스에서 사용 되지 않은 공간을 해제하기 위해 ALTER TABLE 작업과 매핑 됩니다.

그렇기 때문에 OPTIMIZE TABLE 작업은 Fast Index Creation 이 을 사용되지 않게 됩니다.


- MySQL 5.5 까지는 FOREIGN KEY 제약 조건의 효율적인 생성 또는 삭제를 지원하지 않습니다.

그래서 ALTER TABLE을 사용하여 REFERENCES 제약 조건을 추가하거나 제거 작업에서 Fast Index Creation 을 사용하지 않고 child table is copied 가 수행됩니다.
 => 이 부분은 5.6에서 개선되게 됩니다.


Ref link
https://downloads.mysql.com/docs/innodb-1.1-en.pdf
https://stackoverflow.com/questions
https://stackoverflow.com/questions
https://bugs.mysql.com/bug.php?id=33650
https://sqlhints.com/2018/05/03




이어지는 다음 글
Mysql Online DDL - InnoDB Online DDL - Alter Online



관련된 다른 글

답글 남기기