데이터베이스 : INDEX, EXPLAIN

어떤 도시에 100만명의 사람이 살고 있다고 합니다.

여기서 김씨 성을 가진 사람을 찾으려면 1번부터 100만번 까지 "너 김씨야?" 라고 물어봐야겠죠?

어마어마한 시간이 걸릴 것이고, 이를 데이터베이스에서 Full scan 한다고 말합니다.

이 때, 이 검색 시간을 줄여주는 것이 바로 Index scan입니다.

 

인덱스(Index)는 책의 색인으로 많이 비유를 하게 되는데, 저는 영어 교과서를 빗대어 말해보겠습니다.

어떤 영어 교과서의 앞에는 여러 글이 있고, 뒤에 부록으로 단어와 해당 단어가 나온 페이지가 써져 있다고 합시다.

"Database" 라는 단어를 영어 교과서를 처음부터 끝까지 보면서 찾으면 굉장히 많은 시간과 노동이 들겠죠?

반면, 부록을 보고 Database라는 단어가 70페이지에 있다고 나오면 우리는 그냥 70페이지를 펼치면 됩니다.

이렇게 인덱스란 어떠한 컬럼을 색인화 하여 검색 시간을 줄여주는데 쓰입니다.

---

CREATE INDEX, DROP INDEX

---

그렇다면 이제 이 인덱스를 어떻게 만들고, 검색에서 어떻게 활용되는지를 알아볼 시간입니다.

Student 테이블에 인덱스를 적용할 건데, 우선 그 전에 이 테이블의 상태를 보겠습니다.

desc student;
explain select * from student;

desc는 알겠는데, explain은 처음 나오는 명령어죠?

간단히 설명하자면 explain은 그 뒤에 오는 쿼리문의 실행계획을 살펴보는 것이며,

인덱스를 생성한 뒤 이 쿼리에서 인덱스가 정상적으로 적용되었는지 확인하기 위해 사용됩니다.

 

이제 학생을 이름으로 찾기 위해 인덱스를 생성해 보겠습니다. 

다만, 성/이름 컬럼(애트리뷰트)을 나눠놓고 성에 인덱스를 생성하는 식으로 하면 인덱스의 기능을 바람직하게 구현하는 것이지만,  현재 예제로 사용하는 데이터베이스는 Name 컬럼에 성-이름이 모두 있어서 불가피하게 인덱스를 요렇게 설정한 점은 양해 바랍니다.. ㅜ 추후 다른 DB로 수정해서 작성하려 합니다..

create index [index 이름] on [table 이름]([column 이름]) using btree;

인덱스를 위와 같이 생성하고 desc로 확인하니 Name의 Key가 MUL로 바뀐 것을 볼 수 있습니다.

MUL은 .Multiple occurrences column의 약자를 뜻합니다.

이 상태에서 그냥 select 쿼리를 돌리면 인덱스가 없을 때와 같은 결과가 나옵니다.

그러나 100만개정도 되는 대형 데이터가 담긴 DB라면 검색 시간이 현저히 줄어들겠죠!

 

이제 앞에 explain을 붙인 쿼리를 돌려보겠습니다.

explain의 출력이 인덱스가 없을 때랑 많이 바뀌었죠? 하나씩 알아보겠습니다.

type : ref // 지금 검색에서 인덱스를 참조(reference)하고 있다
possible_keys : student_name // 참조할 수 있는 후보 인덱스 목록
key : student_name // 참조한 인덱스의 이름
key_len : 62 // 사용한 인덱스의 길이(인덱스에서 사용한 바이트 수)
ref : const // 행을 추출하는데 인덱스와 함께 사용된 것 : 상수면 const, 다른 테이블의 컬럼이면 해당 컬럼 이름
rows : 1 // 쿼리문으로 찾은 투플의 갯수 (예상값, 추정치)

 

여기에는 여러 type의 종류가 있는데, 이러한 type에 따라서도 성능이 결정됩니다.

아래로 갈 수록 느리고 안좋은 type이라고 생각하시면 됩니다.

system
const
eq_ref : PK나 unique not null 컬럼으로 생성된 인덱스로 조인을 하는 경우
ref : 인덱스로 지정된 컬럼끼리의 단순 비교로 수행되는 조인인 경우 (= 혹은 <=> 연산자)
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range : 특정한 범위의 row들을 매칭시킬 때 인덱스가 사용된 경우 (쿼리에 Between, in, >, >= 등이 사용될 때)
all : full scan

이러한 인덱스를 지우려면 다음과 같이 DROP하면 됩니다.

drop index [index 이름] on [table 이름];

테이블을 생성할 때에도 다음과 같이 인덱스를 걸어줄 수 있습니다.

create table [table 이름] ([column] [type], key [index 이름]([column 이름]));

create table t1 (id int, addr varchar(20), key t1_id_index(id));

 

 

덧붙여 인덱스에 사용되는 쿼리는 Exact Match Query와 Range Query가 있으며,

각각에 사용되는 자료구조는 Hash와 B+-Tree(B-Tree)로 볼 수 있습니다.

위에서 말했듯 MySQL에서는 B+-Tree 인덱스를 디폴트 값으로 가지며, 이를 주로 사용합니다.

---

INDEX 사용 조건

---

그렇다면 인덱스가 빛을 발하려면 어떠한 조건이 있어야 할까요?

1. JOIN한 애트리뷰트에 건다.
2. Cardinality가 큰 테이블에 건다. (데이터 10만개 이상)
3. 인덱스 갯수는 2~3개가 적절하며, 다중 컬럼에 걸지 않는다.
4. READ-ONLY인 데이터에 건다.
5. 정수값을 가진 데이터에 효과가 좋다.

우선 인덱스는 복잡하고 갯수가 많은 데이터에서 SELECT를 사용할 때 효과를 볼 수 있습니다.

따라서 JOIN한 애트리뷰트나 데이터의 갯수가 많은 테이블에 인덱스를 거는 것이 효과가 좋습니다.

오히려 소형 데이터에 인덱스를 걸면 아래의 이유로 인해 느려지는 역효과를 냅니다.

 

또한 기본적으로 인덱스를 걸면 SELECT문의 속도는 높아지나 INSERT, UPDATE 의 속도가 느려지므로,

값이 변동할 일이 거의 없는 테이블, 되도록 Read-only인 테이블에 거는 것이 좋습니다.

 

마지막으로 다중 컬럼에 인덱스를 거는 것을 지양해야 하는데, 다중 컬럼에 건다는 것은 다음과 같습니다.

create table t3 (id int, addr varchar(20), key t3_id_index(id));
-- 단일 컬럼에 인덱스를 걸음

create table t3 (id int, addr varchar(20), key t3_id_index(id, addr));
-- 다중 컬럼에 인덱스를 걸음

두번째 CREATE문과 같이 인덱스를 다중 컬럼에 걸게 되면,

ID만 검색했을 때 쿼리에 오류가 날 수 있으므로 보통 인덱스는 단일 컬럼에만 걸어줍니다.

 

 

<틀린 점이 있다면 지적 부탁드립니다. 감사합니다.>