인덱스란 DBMS 에서 데이터를 쉽고 빠르게 조회하기 위한 보조 수단이다. 일반적으로 책의 색인을 예시로 드는데, 색인은 특정 내용이 어느 페이지에 작성되어 있는지 안내한다. 이처럼 인덱스도 원하는 데이터가 어디에 있는지 알기 쉽도록 키와 값의 쌍(key-value) 으로 인덱스를 관리한다. 또한 데이터의 양이 많아지면 많아질 수록 색인으로 관리하더라도 색인에서 원하는 데이터가 어디에 있는지조차 찾기 어려워진다. 그래서 가나다 순으로 정렬을 하는데 인덱스도 마찬가지로 컬럼의 값을 주어진 순서대로 미리 정렬하여 보관한다.
인덱스 자체가 저장된 데이터를 빠르게 조회하는 것이 목적이므로 읽기 속도는 향상될 수 있으나, 인덱스를 유지하기 위해서 인덱스에 걸려있는 데이터들을 매번 정렬해주어야 한다. 따라서 읽기 성능은 좋아질 수 있지만 저장의 성능은 그만큼 떨어진다. 어느정도까지 조회 성능을 높이고 저장 성능을 감안할 것인지 적절한 선을 조절해야 한다.
인덱스 알고리즘에서 가장 일반적이고 많이 사용되는 알고리즘은 B-Tree 알고리즘이다. B-Tree 알고리즘은 이름대로 균형 트리 형태로 인덱스를 관리한다. 최상단의 루트 노드, 최하위의 리프 노드, 그 중간의 브랜치 노드들로 구성되어 있다.
B-Tree 인덱스의 구조
루트 노드, 브랜치 노드에서는 이후의 노드 주소가 저장되어 있고, 가장 마지막에 위치한 리프 노드에 실제 PK 주소값이 저장되어있어, 인덱스를 타면 순차적으로 순회 후 PK 의 저장위치로 접근할 수 있게 된다. 인덱스 테이블들을 타고 순회하여, 전체 테이블을 스캔하고 읽는 것보다 속도가 빠르다. InnoDB 스토리지 엔진에서는 B-Tree 의 리프 노드에 PK 가 저장되어 있어 PK 를 저장하고 있는 B-Tree 를 다시 한번 순회해야 한다는 특징이 있다. MySQL 내부적으로 관리하는 레코드 ID 의 값도 있지만, PK 의 값을 변경할 경우 그에 해당하는 레코드 ID 도 변경된다. 만약 인덱스 리프 노드에 저장된 값이 PK 가 아닌 레코드 ID 였다면 PK 가 변경될 때 레코드의 ID 가 변경되어 데이터 유실이 발생할 수 있다.
B-Tree 인덱스는 빠른 조회 성능을 위해서 균형 트리 형태를 유지해야 한다. 데이터를 저장 한다면 균형 트리 형태를 유지하며 리프 노드에 데이터를 추가한다. 만약 리프 노드가 꽉 차면 균형 트리를 유지하기 위해서 루트 노드 및 브랜치 노드를 확장하게 된다. 이 과정에서 쓰기 성능이 나빠질 수 있다. 인덱스의 키를 삭제 하는 경우에는 리프 노드의 일부분을 제거하면 되므로 별다른 과정이 없다. 인덱스 키를 변경 하는 경우에는 키를 삭제 후 저장하기 때문에 성능이 나쁠 수 있다.
클러스터링이란 여러 개를 하나로 묶는 것을 의미한다. 클러스터링 인덱스도 동일한 의미로 사용한다. 클러스터링 인덱스는 테이블의 PK 에만 적용되는 내용인데, PK 가 비슷한 레코드들끼리 묶어서 저장하는 것을 의미한다.
클러스터링 인덱스(테이블) 구조
PK 의 값이 변경되면 리프 노드의 위치가 변경되므로 PK 는 변경이 없는 데이터로 설정하는 것이 좋다.
그렇다면 PK 가 없는 테이블은 어떻게 관리될까? PK 가 없을 경우 MySQL 에서 내부적으로 PK 를 생성하여 테이블을 관리한다.
유니크는 인덱스보다는 제약 조건에 가깝다. 특정 컬럼에 대해 동일한 값이 없음을 보장한다. 하지만 NULL 은 특정 값이 아니므로 중복이 허용된다.
유니크 인덱스와 일반적인 세컨더리 인덱스를 비교해보자. 유니크 인덱스의 값이 하나만 있는 것을 보장하는 속성이 있기 때문에 읽기 속도가 빠르다고 생각할 수 있는데 세컨더리 인덱스는 비교적 저장된 데이터가 많기 때문에 시간이 더 걸릴 뿐 성능은 비슷한 편이다. 오히려 유니크 인덱스에 해당하는 컬럼을 저장할 때에는 세컨더리 인덱스보다 처리 과정이 하나 더 있다. 유니크 인덱스는 해당 컬럼의 값이 유니크함을 보장하기 때문에 테이블에 동일한 값이 있는지 검증하는 작업이 필요하다. MySQL 에서는 유니크 인덱스에서 중복된 값을 체크할 때 읽기 잠금을 사용하고, 쓰기할 때에는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 빈번하게 발생한다.