인덱스 (1)
글을 쓰게 된 이유
DB 공부를 하던 중에 인덱스가 성능 이슈를 해결하는 방법 중 하나라고 알고 있었습니다.
하지만 실제로 인덱스를 써본 적이 없어서 실습을 하며 인덱스는 왜 사용하는지 알아보고자 이 글을 작성했습니다.
1편에서는 인덱스 정의 및 간단한 실습을 해보고,
2편에서는 인덱스의 종류, 실습을 통해서 좀 더 깊이 파보도록 하겠습니다.
인덱스는 무엇인가?
index의 뜻은 색인이라는 뜻으로 책의 목차라고 생각해 주시면 됩니다.
목차를 보면 어떤 내용이 어떤 페이지에 있는지 바로 알 수 있기 때문에 우리는 굳이 책의 전체를 살펴보지 않아도 원하는 내용이 있는 페이지를 찾을 수 있습니다.
데이터베이스의 index도 책에서의 목차 와 같은 역할을 합니다.
데이터를 조회할 때 테이블의 모든 데이터를 살피면서 원하는 조건의 데이터를 가져오는 것이 아닌 index를 통해서 원하는 조건의 데이터를 더 빨리 조회할 수 있습니다.
여기서 제일 중요한 점은 정렬입니다. 우리는 목차를 볼 때, 1단원부터 정렬되어 있듯이 인덱스도 칼럼의 값을 기준으로 정렬하여 저장합니다.
인덱스의 구조
인덱스에는 key-value로 저장되있습니다.
key는 칼럼의 값을 가지고 있고, value에는 해당 레코드가 있는 주소 값을 가지고 있습니다.
그리고 제일 중요한 key는 정렬이 되어있죠.
일반적으로 where에서 조회를 하게 되면, 인덱스에서 그 해당 값을 찾아 주소값을 통해서 기존 테이블에 다시 조회하여 값을 가져오게 됩니다.
왜 일반적이라 했냐면 인덱스에는 다양한 종류가 있고, 굳이 인덱스의 주소값을 참조하지 않아도 바로 값을 가져올 수 있는 경우도 있기 때문입니다.
인덱스 생성, 조회, 삭제
create index index_a on index_practice (a)
show index from index_practice;
index_practice라는 테이블을 생성하고, 해당 테이블 a 칼럼에 대한 index_a라는 인덱스를 생성하고 나서 조회한 결과입니다.
create index [인덱스 이름] on [테이블 이름] (칼럼1, 칼럼2, ...)
create index index_b_a on index_practice (b, a);
create index index_a_b on index_practice (a, b);
위와 같이 두 개의 열을 하나의 인덱스로 생성할 수 있습니다.
이러한 인덱스를 multicolumn index라고 합니다.
seq_in_index를 보면 1, 2 이렇게 순서가 나와있어서 multicolumn index라고 알 수 있습니다.
(a, b)인 인덱스는 a가 seq 1, b가 seq 2입니다. 반대로 (b, a)인 인덱스는 b가 1, a는 2가 됩니다.
alter table [테이블 이름] drop index [인덱스 이름]
alter table index_practice drop index index_a_b;
alter table index_practice drop index index_b_a;
인덱스의 장단점
인덱스의 장단점은 구글링만 해도 알 수 있기 때문에 간단하게 짚고만 넘어가도록 하겠습니다.
장점은 검색 속도를 향상해 줍니다.
select 뿐만 아니라 update, delete에서도 where 절을 사용하게 된다면 인덱스가 사용됩니다.
하지만 데이터 연산이 자주 발생하게 되면 성능이 나빠질 수 있다는 점입니다.
뒤에서 말하겠지만 MySQL은 B-tree 구조로 인덱스가 생성되기 때문에 추가, 삭제될 때마다 트리 구조가 변경되기 때문에 연산이 자주 발생하면 성능이 나빠질 수 있습니다.
실습
가정 상황
index_practice table은 a, b 숫자 칼럼을 가지고 있고, 프로시저를 통해 1~1000 사이의 값을 난수를 850000개를 생성했습니다.
인덱스가 없을 때 a에 관한 조회
select SQL_NO_CACHE *
from index_practice
where a = 700;
캐싱으로 인해 정확한 시간 측정이 어려울 수 있어서 SQL_NO_CACHE 키워드를 사용했습니다.
당연히 인덱스가 없기 때문에 인덱스를 타지 않고, 시간을 보면 약 1.5초가 걸렸습니다.
이제 인덱스를 생성해 보도록 하겠습니다.
인덱스가 있을 때 a에 관한 조회
먼저 a 칼럼에 대한 인덱스를 생성해 보겠습니다.
85만 개 데이터에서 a에 관한 인덱스를 생성하는데도 약 8초가량이 걸렸습니다.
85만 개의 데이터를 가진 테이블의 인덱스를 생성하는데 8초가 걸린다면 실제 더 많은 데이터가 존재하는 현업에서 인덱스를 만들 때는 더 오랜 시간이 걸린다고 생각합니다.
그래서 이 인덱스를 하나 도입하는데도 많은 생각과 고민을 할 것 같습니다.
이제 인덱스가 없을 때 실행했던 쿼리를 그대로 실행해 보겠습니다.
select SQL_NO_CACHE *
from index_practice
where a = 700;
1.5초에서 0.1초로 시간이 매우 단축되는 것을 알 수 있습니다.
실행 계획을 봐도 type, possible_keys, key를 통해 인덱스가 탄 것을 알 수 있습니다.
인덱스는 어느 칼럼에 사용하는 게 가장 좋을까요?
그렇다면 인덱스는 언제 사용하는게 가장 효율적일까요??
당연히 수정, 삭제, 삽입 연산이 활발하지 않은 테이블에 인덱스가 있는 게 좋습니다.
또한, 칼럼의 카디널리티 및 선택도가 높으면 좋다고 합니다.
카디널리티와 선택도는 인덱스를 생성할 때, 인덱스 키 값에서의 유니크한 값의 수를 의미합니다.
만약 인덱스 키 값이 100개 존재할 때, 유니크 한 값이 10개라면 선택도는 10이라고 할 수 있습니다.
그렇다면 왜 유니크 한 값이 많으면 좋은 걸까요??
예시를 한번 들어보겠습니다.
Email은 대부분 유니크하기 때문에 높은 카디널리티, 선택도를 갖고,
성별은 남, 녀 등과 같이 카디널리티가 낮습니다.
만약 Email을 검색할 때, 유니크하기 때문에 DB 입장에서는 검색 범위가 매우 좁아집니다.
하지만 남자 또는 여자는 유니크하지 않기 때문에 인덱스 입장에서는 검색 범위를 좁히는데 전혀 도움이 되지 않습니다. 최악으론 인덱스를 타지 않고 풀 테이블 스캔을 할 수 있습니다.
한 가지 예시를 더 들자면 옵티마이저가 100개 중에 10개만 유니크라면 데이터를 조회할 때
조건에 맞는 데이터를 찾고 나서 같은 데이터를 10개만 읽으면 되지만, 100개가 모두 중복이라고 하면 그 데이터를 다 읽어야 합니다.
인덱스의 정보를 보면 Cardinality 칼럼이 존재합니다. 즉, 옵티마이저는 해당 인덱스 키 값의 중복을 알고 있습니다.
그래서 모든 키 값이 유니크할 때, a = 500 인 값을 조회하면 유니크하기 때문에 a = 500인 값을 찾으면 더 이상 탐색하지 않아도 됩니다.
결국 인덱스에서 유니크한 값의 개수는 효율적인 면에서 매우 큰 영향을 끼친다고 할 수 있습니다.
인덱스의 분류
인덱스를 분류하는 방법에는 데이터를 관리하는 방식과 중복 값의 허용 여부가 있습니다.
중복 값의 허용 여부는 이전에 말했던 것처럼 탐색 범위를 줄여주는데 많은 기여를 합니다. 그래서 유니크 인덱스와 유니크하지 않은 인덱스를 나눌 수 있습니다.
데이터를 관리하는 방식으로는 B-Tree, Hash 등이 있습니다.
이와 관련한 인덱스의 종류는 2편에서 작성하도록 하겠습니다.
언제부터 옵티마이저는 인덱스를 사용할까?
문득 쿼리를 계속 실행해 보다가 일치하는 칼럼의 개수가 몇 개일 때부터 인덱스를 타는지 궁금해졌습니다.
궁금하면 무조건 찾아야 하기 때문에 where 절에 있는 숫자 조건을 하나씩 올려서 개수를 줄여보았습니다.
조회 데이터의 수치가 인덱스를 적용하는 절대적인 기준도 아니고, 데이터 타입마다 달라질 수 있기 때문에 그냥 가볍게 보시면 좋을 것 같습니다.
구글링을 하니 20~25 % 이상이 되면 인덱스를 사용한다고 해서 이때부터 개수를 세기 시작했습니다.
저는 총 85만 개의 데이터이기 때문에 20% 정도면 17만 개 이하일 경우 인덱스를 탄다고 가정할 수 있습니다.
select count(*)
from index_practice
where a >= 860;
112326개가 나오지만 풀 테이블 스캔을 하게 됩니다.
그래서 숫자를 하나씩 계속 늘려가면서 개수와 실행 계획을 통해 경곗값을 찾았습니다.
explain
select *
from index_practice
where a >= 869;
select count(*)
from index_practice
where a >= 869;
이때 조건에서는 105227개가 나왔습니다. 총데이터가 85만 개이니까 12.3796 % 가 나옵니다.
하지만 아직까지 테이블 풀 스캔을 하게 됩니다.
explain
select count(*)
from index_practice
where a >= 870;
이때 개수는 104413 개로 12.2538 % 가 됩니다.
이때 쿼리 실행 계획을 보면 a 인덱스를 탄 것을 알 수 있습니다.
12.2538 % 가 확실한 것은 아니기 때문에 그냥 재미로 보시면 좋을 것 같습니다.
결론
이번 글에서는 인덱스의 정리를 간략하게 알아보았고, 실습을 통해 인덱스가 성능에 얼마나 많은 영향을 주는지 알 수 있었습니다.
하지만 인덱스를 생성하는 시간이나, 인덱스를 만들었는데 실제 조회된 데이터의 개수가 너무 많게 되면 인덱스를 타지 않을 수 있습니다.
그렇기 때문에 인덱스는 많은 생각과 쿼리 실행 계획을 통해 충분한 고민이 필요한 것 같은 느낌이 들었습니다.
다음 글에서는 인덱스의 종류 및 인덱스는 언제 동작하지 않는지 등 실습을 통해 좀 더 깊이 공부해 보도록 하겠습니다.
REFERENCES
Real MySQL