Mysql Index

개요 §

이 포스팅의 내용은 <<대규모 서비스를 지탱하는 기술>>의 DB index 관련 부분 내용을 요약한 것이다.

index란 §

index는 DB에 추가할 수 있는, 데이터를 조회하기 위하여 미리 데이터를 인덱싱 해 놓는 것이다. PK에는 자동적으로 인덱스가 걸리고, PK가 아닌 컬럼에도 인덱스를 걸 수 있다.

데이터의 수정/삽입/삭제를 할 때마다 새로 인덱싱을 하기 때문에 앞의 세 동작을 할때는 속도의 저하가 있지만, 데이터를 읽을 때는 압도적으로 빠르다.

그러므로 인덱스를 추가해야 하는 어플리케이션은 다음의 가정을 만족해야 한다 : 데이터의 조회가 수정/삽입/삭제보다 훨씬 더 빈번하게 발생한다.

실제로 데이터의 순서와 인덱스 데이터 순서를 일치시키는 것을 클러스터드 인덱스라고 하고, 아닌 것을 넌 클러스터드 인덱스라고 한다.

데이터의 순서와 인덱스 데이터 순서를 일치시키는 방법은 당연하지만 한 가지밖에 없기 때문에, 하나의 테이블에 클러스터드 인덱스는 하나만 걸 수 있다. PK는 기본적으로 클러스터드 인덱스가 걸려 있다.

B-Tree §

인덱스를 걸면, 내부적으로 B-Tree라는 자료구조를 사용한다. 이는 탐색을 하기 위해 미리 데이터 구조를 구성해 놓는 것이다. B-Tree 는 리프노드에만 데이터를 가지고 있는 자료구조이다.

탐색 순서는 다음과 같다.

• 루트부터 시작하여 각 노드에 찾고 있는 값이 저장되어 있는지 확인한다. 값으로 10이 들어왔다고 가정하자.
• 노드를 확인해 보면 13은 첫번째 자식, 46은 두번째 자식, 7~10은 세번째 자식으로 가는걸 알 수 있다. 세번째 노드로 가 보자.
• 세번째 노드에서는 7~9는 첫번째 자식, 10은 두번째 자식으로 가는걸 알 수 있다.
• 두번째 자식으로 가면 리프노드이다. 디스크의 어떤 곳에 데이터가 있는지 위치를 알 수 있다.

인덱스의 작용 §

모든 쿼리를 던진다고 인덱스를 항상 타는 것은 아니다. 기본적으로 인덱스가 적용되는 컬럼은 다음과 같다.

where, order by, gruop by 의 조건에 지정된 컬럼

예를 들어

select * from entry where url = 'http://...'

라는 쿼리가 있다고 가정해 보자. 위의 조회 조건에 따르면, url에 인덱스가 걸려 있으면 인덱스를 타게 된다.

문제는 바로 복수의 컬럼이 인덱스 작용의 대상이 되는 경우이다. 다음과 같은 쿼리가 있다고 가정해 보자

select * from entry wher url like 'http://~' order by timestamp

url과 timestamp에 각각 인덱스를 걸었다면, 먼저 url로 인덱스를 타고 나온 데이터를 다시 timestamp를 기준으로 인덱스를 태우기를 바랄 것이다.

그러나 MYSQL은 한 번의 쿼리에서 하나의 인덱스만 사용한다는 특성이 있기 때문에, 위와 같은 경우에는 어느 한 쪽의 인덱스만 사용된다.

위와 같은 경우에 둘 다 인덱스를 태우려면 (url, timestamp)로 복합 인덱스를 잡아야 한다.

explain §

그러면 인덱스를 제대로 탔는지 확인하려면 어떻게 해야 할까? MySQL은 explain이라는 명령어가 존재한다. explain 명령어를 사용하면 인덱스를 타는지 여부를 확인할 수 있다.

mysql> explain select url from entry where eid = 9615899

이 명령어를 날리면 쿼리 실행 계획이 나오게 된다. 주목해서 봐야 할 컬럼은 다음과 같다.

• possible_keys : 이 쿼리로 검색했을때 탈 가능성이 있는 인덱스의 이름
• key : 실제로 사용되고 있는 키
• rows : 조사한 행의 개수

possible_keys와 key의 차이점은 mysql이 선택한 인덱스 / 실제로 사용할 인덱스이다. 사용자가 force index, use index, ignore index같은 걸 이용하여 key에서 실제로 사용할 인덱스를 지정할 수 있다.

Extra §

explain 을 했을 때 extra에 나오는 정보 또한 중요하다. Using where 대신에 Using filesort나 Using temporary와 같은 항목이 나올 경우가 있다. 각각 레코드 정렬에 외부 정렬(외부 파일을 이용한 정렬)이나 임시 테이블이 필요하다는 이야기이다.

기본적으로는 Using filesort나 Using temporary가 나오는 것이 좋은 쿼리라고 할 수 없으므로, 가능한 한 나오지 않도록 쿼리나 인덱스를 튜닝할 필요가 있다.