[MySQL] 인덱스(index) 에 대해서

DB의 테이블을 읽어들이는 방식은 크게 Full-Scan과 Range-Scan으로 나뉩니다. Full-Scan은 테이블에 포함된 모든 레코드를 처음부터 끝까지 읽는 방식이고,Range-Scan은 테이블의 일부 레코드에만 접근하는 방식입니다. 이 두 가지 방식 중에 Range-Scan을 할 때 인덱스를 이용하면 성능을 향상시키는데 도움이 됩니다.

인덱스

그렇다면 인덱스는 무엇일까요? 인덱스는 책의 목차, 단어장 뒤에 있는 색인과 같이 테이블에 한 열을 이용해 특정 알고리즘을 이용해 따로 정리하여 데이터를 SELECT 할 때 빠르게 조회할 수 있도록 도움을 주는 기능입니다.

인덱스 자료구조

인덱스의 자료구조는 가장 많이 쓰이는 B-Tree방식이 있고 그 이외에 hash, Fractal-tree 방식도 있습니다. 여기서는 B-Tree에 대해서만 알아보겠습니다.

B-Tree

B-Tree는 가장 최상위에 루트노드를 기준으로 최하위에 리프노드 그리고 그 중간에 있는 노드는 브랜치 노드라고 부릅니다. 리프노드는 실제 데이터베이스의 특정 레코드에 대한 주소를 갖고 있습니다.

인덱스 없이 전체 100개의 데이터가 존재하고 id가 70부터 80에 해당하는 값을 조회하고 싶다고 할 때 B-Tree 방식을 이용해 조회하면 최상위 루트 노드부터 조회 할 부분의 크기를 해당 노드의 순서와 비교하여 범위를 줄여가며 내려가게 되며 다음 예시에선 2번만에 해당 범위의 실제 데이터 주소를 찾을 수 있게 됩니다. 막약 인덱스가 없었다면 순차적으로 1부터 데이터를 조회해서 찾아가며 불필요한 검색을 수행했을 것입니다.

 

B-Tree의 B는 Balanced(균형잡힌)이라는 뜻으로 B-Tree는 균형트리라는 뜻을 갖고 있습니다. 균형트리라는 것은 루트로 부터 리프까지의 거리가 균형을 맞추고 있다는 것을 뜻합니다. B-Tree는 루트노드에서 리프노드로 한 단계씩 내려갈 때마다 브런치 노드가 늘어나는 수는 제곱으로 늘어나게 됩니다. 그래서 아무리 데이터 수가 많아지더라도 실제로 데이터를 찾기위해 타고가는 횟수는 생각보다 늘어나지 않습니다. 결과적으로 B-Tree를 사용하게 되면 디스크 입출력에 걸리는 시간이 데이터의 수와 큰 상관 없이 균일한 성능을 보장합니다. B-Tree의 시간복잡도는 O(logN)으로 시간복잡도가 O(1)인 Hash방식보다 느리지만 Hash는 검색시 정확한 값이 일치하는 레코드만 찾을 수 있어서 범위검색이나 일부 문자가 일치하는 경우에는 검색이 불가능하다는 단점이 있어 B-Tree를 많이 사용합니다.

MySQL 인덱스 사용 예시

MySQL에 있는 테스트 데이터베이스인 world의 city라는 테이블을 사용하여 순차검색과 인덱스의 성능차이를 알아보겠습니다.

city 테이블 구조는 다음과 같습니다.

한국에 해당하는 도시만 조회하면 이런 식으로 나타나게 됩니다.

성능 비교에 사용될 쿼리는 각 지역과 지역에 해당하는 도시 수를 보여주는 SELECT 문입니다.

SELECT district, COUNT(*) FROM city GROUP BY district;

 

쿼리에 대해 어떤 실행계획을 갖는지 쿼리 앞에 'EXPLAIN'을 붙이면 확인할 수 있습니다.

해당 열 중 Extra를 보면 Using temporary라는 내용이 보이는데 이는 데이터를 조회하기 위해 임시 공간을 생성해서 조회한다는 뜻으로 데이터베이스가 어떤 처리를 수행하기 위해 데이터를 일시적으로 보존하기 위해 사용하는 영역입니다. 일반적으로는 실제 저장소나 인덱스가 사용되지만 임시영역을 사용하게 되면 성능문제를 일으 킬 수 있습니다.

실제로 조회를 해보니 제 컴퓨터에서는 0.03s 가 걸렸습니다.

이번엔 인덱스를 사용해보겠습니다. 그전에 먼저 해당 테이블에 어떤 인덱스가 걸려있는지 확인하기 위해 테이블의 인덱스 정보를 확인해보겠습니다.

 

특정 테이블의 인덱스 확인 쿼리

SHOW index FROM table_name

두 개의 인덱스를 확인 할 수 있는데 기본키와 외래키에 대해서 B-Tree형식으로 인덱스가 존재하는 것을 확인 할 수 있습니다. district에 대한 인덱스는 존재하지 않으므로 인덱스를 생성해보겠습니다.

 

인덱스 추가 쿼리

ALTER TABLE tablename ADD INDEX indexname (column1, column2);

다음과 같이 인덱스 생성을 완료했다면 다시 실행계획을 살펴보겠습니다.

 

Extra를 보면 Using index, 즉 인덱스를 사용하여 쿼리를 수행하는 것을 알 수 있습니다.

실제로 쿼리를 수행해보니 0.03s에서 0.00Xs 로 쿼리 수행시간이 크게 줄어든 것을 확인 할 수 있었습니다.

인덱스 사용 시 주의 사항

인덱스는 이렇게 쿼리를 수정하지 않고도 성능개선에 효과를 줄 수 있습니다. 하지만 인덱스 사용에 몇 가지 고려할 사항이 있습니다.

 

1. 테이블에 새로운 데이터를 추가하거나 기존 데이터의 갱신, 삭제 시에 인덱스도 같이 추가, 갱신되어 오버헤드가 발생 할 수 있습니다. 그래서 추가, 갱신, 삭제가 자주 일어나는 테이블에선 인덱스 사용시 주의해야 됩니다

 

2. 쿼리 수행 시 의도한 것과 다른 인덱스가 사용될 수 있습니다. 하나의 테이블에 여러 인덱스를 만든 경우 의도치 않은 인덱스를 사용하여 응답시간이 상대적으로 오래 걸릴 수 있다. 이런 경우 어떤 인덱스를 타고가는지 EXPLAIN 명령어를 사용해 실행계획을 확인해봐야 합니다.

 

3. 기본키나 외래키 등 유일성 제약이 부여된 키는 기본적으로 인덱스가 같이 생성되므로 중복 생성하면 안됩니다.

 

4. 카디널러티(Cardinality)가 낮은 열에는 인덱스를 만들면 안됩니다. 카디널리티는 데이터의 분산도를 나타내는 것으로 데이터의 분산도가 낮다는 것은 하나의 열에 중복되는 데이터가 많이 존재한다는 뜻이고 분산도가 높다는 것은 중복되는 데이터가 거의 없는 것을 뜻합니다.

 

주민등록번호의 경우 카디널리티가 높고 사람의 성별의 경우 카디널리티가 낮다고 할 수 있습니다. 만약 A와 B로 시작하는 사전에서 A와 B로만 단어들을 분리해놓고 무작위로 단어들이 들어있다면 특정 단어를 찾기 힘들 것입니다. 하지만 AA, AB, AC ... BZ 처럼 단어가 시작하는 두 글 자를 기준으로 단어를 정리해놓았다면 상대적으로 조회하기가 더욱 편할 것입니다. 인덱스의 경우도 중복이 없을 수록 타고가는 조작이 적어져 불필요한 오버헤드가 발생하지 않습니다.