InnoDB의 Clustered index

Clustered index 에 대해 모두 잘 알고 계시리라 생각합니다만 오늘 팀 내에서 Clustered index 에 대한 이야기가 나와서 간단히 적어 보려 합니다.

Index 와 Clustered index

Index는 우리에게 매우 익숙합니다. 테이블을 생성한 후 많은 데이터를 빠르게 찾기 위해 Index를 생성합니다. PRIMARY KEY인 경우 자동으로 Index가 되고 그 외 컬럼은 임의로 지정해 주어야 합니다. 경우에 따라서는 2~3초 걸리던 SQL 쿼리도 0.몇 초 이내에 결과가 나오기도 합니다.

테이블의 Index는 아래 그림과 같이 Index에 포함된 컬럼이 일렬로 나열됩니다. 그런데 눈에 띄는 게 있습니다. No 가 1부터 4까지 순서대로 나열되어 있지 않습니다.  MySQL에서 Index는 빠른 검색을 위한 목차를 만들 때 정렬을 하지 않고 나열만 합니다. 그렇기 때문에 Index 는 어떤 하나의 값을 찾는데 빠릅니다. 예) WHERE no=1000

Clustered index는 컬럼을 컬럼의 값을 정렬해서 나열합니다. No 1, 2,3,4 … 처럼 Index 가 순서대로 생성되기 때문에 부분 전체를 구할 때 Non-clustered Index 에 비해서 빠른 결과를 기대할 수 있습니다. 예) WHERE no > 1000. 대신에 INSERT 와 UPDATE, DELETE 를 수행할 때마다 Index 를 정렬해야 하기 때문에 Non-clustered Index 방식에 비해서 비용이 많이 듭니다.

Clustered index 는 InnoDB 에서 사용할 수 있습니다. InnoDB 엔진을 사용해서 테이블을 만들고 PRIMARY KEY를 지정하면 MySQL은 PRIMARY KEY로 지정된 컬럼에 대해 Clustered index 를 생성합니다. 따로 지정해 줄 필요는 없습니다. 단, SHOW INDEX로 확인할 수는 없습니다. 만약 PRIMARY KEY 가 아닌 컬럼에 대해 Clustered index 를 생성하고자 한다면 먼저 NOT NULL을 지정하고 UNIQUE INDEX 를 생성하면 됩니다.

Clustered index 의 장점은 앞에서도 언급했듯이 정렬되어 있기 때문에 디스크 I/O를 줄일 수 있고 속도가 빠릅니다. 수백, 수천만건의 데이터를 가지고 있는 테이블에서 페이징과 같이 일부분의 집합을 얻고자 할 때엔 아래 그림과 같이 한번에 가져올 수 있기 때문입니다.

Clustered index 로 지정되어 있는 컬럼은 가능하면 변경되지 않아야 경제적입니다. 잦은 변경은 재정렬로 인해 더 느려지거나 더 많은 부하를 일으킬 수 있을 테니까요.

참고 : MySQL Clustered and Secondary Indexes