Last Updated on 3월 2, 2024 by Jade(정현호)
Contents
InnoDB - PK - Clustered Index
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 이라고 합니다
MySQL 5.6 버전 부터는 여기에서 더욱 더 많은 ONLINE Operation 이 가능하게 기능을 추가하여 Online DDL 기능이 추가되었습니다.
빠른 생성을 위한 Fast Index Creation 에 의한 속도 향상은 Primary Key(기본 키) 에는 적용되지 않고 Secondary Index(보조 인덱스) 에 만 해당되는 사항입니다.
InnoDB Primary Index is like IOT
InnoDB 에는 두가지 타입의 인덱스가 존재합니다.
- Clustered Index
- Secondary Index(Non Clustered Index)
Clustered Index
InnoDB 의 테이블의 row는 기본키 기반으로 구성된 clustered index에 저장되어 있고 clustered index 는 일반적으로 데이터를 포함되어 있는 B-tree구조 인덱스로 일부 다른 데이터베이스에서는 이러한 구조를 IOT(index-organized table) 라고 부릅니다.
InnoDB의 Clustered Index 구조는 실제로 데이터가 저장되는 데이터 페이지가 기본키(Primary Key) 값 순서로 정렬되어 저장됩니다.
PK 이기 때문에 당연히 고유성(Unique)도 필요하지만 PK로 입력되는 값은 순차적으로(순서를 보장하여) 입력하는 형태로 사용하는 것이 좋습니다.
테이블 구조가 기본키와 매우 밀접한 관계를 맺고 있기 때문에 PK 인덱스에 대한 Redefine Operation(adding or dropping) 작업은 어쩔 수 없이 Fast Index Creation 기능 대신에 기존과 동일하게 table creating, data copy, rename 의 작업이 동반됩니다.
Clustered Index 대상이 될 수 있는 대상으로는 먼저 PK 가 되며, PK가 없다면 유니크키 , 둘 다 없을 경우 6byte의 Hidden Key를 사용하게 됩니다.
Secondary Index
Secondary index(Non Clustered Index) 는 보조 인덱스로 인덱스의 컬럼의 데이터와 PK 주소 값 과 같은 정보가 아닌 실제 PK(프라이머리 키) 키 값을 저장하도록 구현돼 있습니다
그래서 PK 키 값이 길면 Secondary Index(Non Clustered Index) 도 더 많은 공간을 사용하게 됩니다
(인덱스 컬럼의 값과 Clustered Key(PK) 가 포함)
이러한 인덱스 타입은 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)의 정의를 변경하면 모든 보조 인덱스도 다시 생성되게 됩니다.
Covering Index(Covered Index)
인덱스의 종류 보다는 사용조건이 맞을 경우 사용할 수 있는 기능 정도로 표현할 수 있을 것 같습니다.
데이터 조회시 쿼리에서 충족되는 모든 데이터를 인덱스에서 가지고 있다면 테이블에서 row 가져오지 않고 인덱스의 데이터로만 데이터를 조회하는 경우 Covering Index(커버링 인덱스, 커브드 인덱스) 라고 합니다.
커버링 인덱스 사용이 될 경우 Explain 의 Extra 에서 Using index 로 표기됩니다.
- Extra : Using Index
그렇지 않을 경우 Using where 로 표시되며 Range Scan 이후 데이터는 직접 테이블 데이터에서 출한 것으로 생각하시면 됩니다.
위의 그림과 같이 Non-Clustered Index(Secondary Index) 에는 Index 컬럼 값(데이터) 과 PK 키 값이 포함되어 있습니다.
예) 2012-05-23 / 73 <-- PK 키 값
• 커버링 인덱스 조건 : 조회 시 WHERE 절 조건에 맞는 인덱스 컬럼이 있으며 SELECT 절에서 컬럼도 인덱스에 포함되어 있을 경우, 커버링 인덱스 로 수행됨
• PK Look-up이 필요한 경우: WHERE 절 조건이 인덱스 컬럼과 맞더라도 SELECT 절의 컬럼이 인덱스에 포함된 컬럼 이외 다른 컬럼이 포함되어 있을 경우, PK(Clustered Index) 를 통해 row 를 찾는 과정이 필요 합니다.
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에서 개선되게 됩니다.
Reference
Reference 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
이어지는 다음 글
관련된 다른 글
MHA 는 Master High Availability 약자로 Master DB의 고가용성을 위해 Yoshinori Matsunobu(현 Facebook) 에 의해 개발 되었으며 GPL v2 라이센스 기반의 오픈소스 입니다.
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
