Mysql - Index 학습

date

Jun 30, 2022

thumbnail

slug

mysql-index

author

status

Published

배경

최근 DB 설계 시 무작정 SELECT 쿼리의 WHERE절에 사용되는 컬럼에 인덱스를 설정하려는 자신을 보면서 공부의 필요성을 느꼈고, 대표적으로 아래와 같은 고민이 들었다.

varchar에 인덱스를 설정할 때와 integer에 인덱스를 설정할 때 차이점

WHERE절에서 AND로 여러 조건이 결합되는 경우, 결합 인덱스를 설정했을 때와 개별 필드들에 인덱스를 설정했을 때의 성능 차이

결합 인덱스에서 일부 필드만 조건으로 사용되는 경우의 인덱스의 사용 여부

인덱스

인덱스란 데이터의 저장(INSERT, UPDATE, DELETE) 의 성능을 희생하고 그 대신에 데이터의 읽기 속도를 높이는 테이블의 동작속도(조회)를 높여주는 자료구조이다. 즉 테이블의 컬럼들을 기준으로 빨리 접근하기 위한 목차를 생성하는것이다.

Mysql innoDB 스토리지 엔진은 이를 위해 B+ Tree구조를 사용한다.

왜 B+ Tree 구조를 사용할까?

읽고 쓰는데에 Hash table구조를 사용한다면 O(1)에 가능할 것이다. 하지만 보통 DB를 사용할 때 range scan을 주로 하게되는데, 이런 정렬된 상태를 hash table로는 유지할 수 없다.

그럼 다른 AVL 트리나 Red-Black 트리같은 균형잡힌 이진 탐색 트리는 왜 사용하지 않을까? 이들도 분명 log의 시간복잡도를 만족한다. 하지만 가장 큰 차이는 B트리 구조는 하나의 노드의 여러 개의 데이터를 저장한다. B-Tree의 각 노드는 배열로서 실제 메모리 상에 차례대로 저장이 되어 있다. 즉, 같은 노드 상 데이터를 탐색할 때는 포인터 접근을 하는 것이 아니라 실제 메모리 디스크에서 바로 다음 인덱스에 접근을 하는 것이다. 하지만 이진 탐색 트리는 각 노드마다 무조건 하나의 데이터를 가지므로 참조 포인터를 이용할 수 밖에 없다. 즉 메모리의 접근하는 효율성, 캐시의 지역성과 관련된 이유로 B트리 구조를 사용하게 된다.

가끔 다른 글들을 보면 B트리보다 B+트리가 더 향상된 거라는 글들을 볼 수 있는데, 완전히 동의하기는 어려워보인다. 조회를 전제로 하는 인덱스에서는 맞는 말이지만, B+트리가 수정,삭제의 속도는 감소하기 때문이다.

장점

데이터를 조회(SELECT)할 때 빠르게 접근이 가능하도록 도와준다.

그 결과 쿼리의 부하가 줄어서, 결국 시스템 전체의 성능이 향상된다.

단점

인덱스를 위한 추가적인 공간이 요구된다. (대략 데이터베이스 크기의 10% 정도)

INSERT, UPDATE, DELETE가 빈번하게 일어나는 경우, 오히려 성능이 나빠질 수 있다.

MySQL은 데이터를 한곳에다가 다 저장하는것이 아닌, 페이지(page)단위로 쪼개어 저장하는데, 기본으로 설정된 페이지의 크기 기본값은 16KB 정도이다.

인덱스의 종류

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

테이블 당 1개만 생성이 가능하다. PK를 지정한 컬럼에 대해서는 자동으로 클러스터형 인덱스가 생성된다.

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

NOT NULL옵션의 Unique Index 중 첫 번째 인덱스가 설정된 컬럼을 선택

자동으로 유니크한 값을 갖는 Auto Increment 컬럼을 내부적으로 추가한 후 선택

데이터를 인덱스로 지정한 컬럼에 맞춰서 물리적으로 정렬한다. 즉 실제 데이터 페이지 자체가 이미 정렬된 상태이므로 범위 검색이나 정렬 시 우수한 성능을 기대할 수 있다.

클러스터형 인덱스를 생성 시 데이터 페이지 전체를 다시 정렬하므로, 이미 대용량 데이터가 입력된 상태라면 시스템에 큰 부하를 주게된다.

단 CREATE/UPDATE/DELETE 시 정렬이 일어나는 경우, 페이지가 가득 차서 페이지 분할이 발생하면 성능 상 부담이 발생한다.

보조 인덱스 (Secondary Index, non-clustered index)

테이블에 여러 개 생성이 가능하다. Unique 제약조건을 지정한 컬럼에 대해서는 자동으로 보조 인덱스가 생성된다. 별도의 인덱스 페이지가 만들어지고, 데이터 페이지에는 변경이 발생하지 않는다. 인덱스의 리프 페이지는 데이터가 아닌 데이터가 위치하는 주소값(RID)이다. 클러스터형 인덱스와 함께 사용되는 경우, 엔덱스의 리프 페이지는 클러스터형 인덱스의 키값을 갖는다. 데이터를 자동으로 정렬하는 것이 아니기 때문에, 일반적으로 클러스터형 보다 검색 속도는 더 느리지만 데이터의 CREATE/UPDATE/DELETE애 대한 부담이 더 적다.

클러스터형 인덱스와 보조 인덱스 모두 설정된 경우

왜 보조 인덱스의 리프 페이지에서 바로 데이터가 위치하는 주소값(RID)를 가리키지 않을까?

보조 인덱스에서 곧 바로 RID를 갖고 있다면 SELECT시 더 빠르게(접근하는 페이지 개수가 더 적게) 데이터에 접근할 수 있을 것이다. 하지만 테이블에 클러스터형 인덱스도 설정되어 있으므로 INSERT, UPDATE, DELETE시 정렬이 발생하고, 그로 인해 데이터의 위치의 변경이 일어난다.

만약 보조 인덱스의 리프 페이지가 RID를 가리키고 있었다면, 정렬로 인해 위치가 변경된 모든 RID를 참조하는 부분의 갱신이 필요하게 된다. 하지만 RID가 아닌 클러스터링 인덱스의 키값을 가리키고 있다면 이런 갱신이 필요없다.

효율적인 인덱스 설계

빈번하게 WHERE 절에 사용되는 컬럼

JOIN절에 자주 사용되는 컬럼

ORDER BY 절에 사용되는 열은 데이터 페이지가 자동 정렬됐기 때문에 클러스터형 인덱스가 유리하다

ORDER BY 자체가 무거운 연산인데, 클러스터형 인덱스는 데이터가 이미 설정된 컬럼에 맞춰서 정렬된 상태이므로 추가적인 정렬이 필요 없게 된다

대용량 데이터가 자주 INSERT 되는 경우, 클러스터형 인덱스는 빈번한 페이지 분할이 발생하므로 부하가 크다. 따라서 보조 인덱스만 설정하는 편이 나을 수 있다.

데이터 중복도가 낮은 컬럼에 인덱스를 설정하느 것이 좋다. 중복도가 높은 경우, 인덱스를 사용하는 것이 효율이 없지는 않지만 어차피 데이터를 읽기 위해 많은 페이지를 읽어야 하는 것은 마찬가지이기 때문에 인덱스의 이점이 많이 사라진다. 오히려 인덱스의 관리 비용을 생각하면은 설정하지 않는 것이 좋을 수 있다.

INSERT/UPDATE/DELETE가 얼마나 자주 일어나는지를 고려해야 한다.

주의 사항

검색할 데이터가 전체 데이터의 20% 이상이라면, MySQL에서 인덱스를 사용하지 않는다. (인덱스 손익분기점) 테이블이 가지고 있는 전체 데이터양의 5~20%이내의 데이터가 출력 될 때만 INDEX를 타는게 효율적이고, 그 이상이 될 때에는 오히려 풀스캔이 보통 빠르다. 추출 건수가 늘어나면 인덱스 스캔량도 늘고, 테이블 랜덤 엑세스가 늘어나기 때문에 전체적인 데이터의 수가 더 많아진다면 손익분기점은 더욱 낮아진다.

AND연산자는 각 조건들이 읽어와야할 ROW수를 줄이는 역할을 하지만, OR연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높다.

인덱스로 사용된 컬럼을 그대로 사용해야만 인덱스가 사용된다. 예를 들어 age컬럼에 인덱스가 설정되어 있는 경우, WHERE age * 10 > 150는 인덱스를 못타지만, WHERE age > 150 / 10 은 인덱스를 사용한다.

또한 컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않는다. 정확한 타입을 사용해야만 한다.

고민의 해결

VARCHAR에 인덱스를 설정할 때와 integer에 인덱스를 설정할 때 차이점

VARCHAR에 인덱스를 설정하게 되면, 결국 정렬의 기준이 되는 값이 문자열이 되므로 인덱스의 크기가 커지게 된다. 따라서 꼭 필요한게 아니라면 interger로 사용하는게 좋아보인다.

WHERE절에서 AND로 여러 조건이 결합되는 경우, 결합 인덱스를 설정했을 때와 개별 필드들에 인덱스를 설정했을 때의 성능 차이

AND조건으로 여러 개의 필드를 사용해도 단일 인덱스만 설정되어있다면 1개의 필드만 인덱스를 탈 수 밖에 없다. 서로 다른 개별 필드에 설정된 트리 구조의 접점이 없기 때문이다. 따라서 결합인덱스를 설정하는 것이 성능적으로 좋다.

결합 인덱스에서 일부 필드만 조건으로 사용되는 경우의 인덱스의 사용 여부

결합인덱스를 설정한다는 것은 일반 인덱스를 설정하는 것과 크게 다르지 않다. 다만 B+트리의 정렬을 위한 값에 여러 필드가 사용되는 것이다. 하지만 정렬이라는 것은 필드가 여러 개라면 우선 순위가 필요하다. 따라서 결합 인덱스의 앞에 있는 필드를 기준으로 트리 구조가 형성이 되므로 앞에 위치하는 필드가 일치하는 것이 중요하다.

참고

MySQL Clustered Index 와 Fast Index Creation In InnoDB

다중컬럼 인덱스와 사용되는 인덱스 만들기

테이블 랜덤 엑세스, ROWID, 인덱스 손익분기점