제 홈페이지의 모든 글은 anti-nhn license에 따릅니다.



대용량 서비스 - 4. 디비 인덱스

디비에 대한 내용 중 제일 중요한 인덱스의 기본적인 내용을 다루겠습니다. 디비에 관한 퍼포먼스 향상 기법들 중  제일 중요한 게 인덱스입니다. 다른 건 잘해봐야 속도가 2~3배 정도 향상되지만, 인덱스는 수백 수천 배의 속도 차이도 가져옵니다. 특히 데이터가 많아질 수록 속도차이가 더 커집니다. 다시 얘기하면 인덱스가 제대로 안 걸린 테이블은 어느 정도 이상의 데이터가 쌓이면 서비스가 불가능해집니다.(공갈 협박 아니에요^^)

1. index는

미리 데이터를 정리해놔서 빠르게 찾게 하겠다는 것입니다. 크게 binary 방식과 hash 방식이 있으며 일반적으로는 binary 방식이 많이 쓰입니다.
binary 방식은 tree를 구성해서 "순서대로" 정렬하는 것이고, hash 방식은 hashmap 같은 방식이라고 보시면 됩니다.
따라서 hash 방식은 범위 검색이 안 됩니다. 정확히 = 검색만 됩니다. 부등호 표시는 안 됩니다. db engine에 따라 hash 방식은 지원하지 않는 경우도 있습니다.


2. 간단한 쿼리에서의 인덱스

msg_id(글 아이디. 순차적으로 증가, int 값) , 
writer(글 쓴 사람. 일단 편의상 varchar) , 
contents (글 내용, varchar 또는 text 등),
written_dt (글 쓴 날짜)

글쓴 사람을 기준으로 최신글 부터 뽑아내고자 하면 다음과 같은 쿼리를 던질 겁니다.

select * from msg where writer='홍길동' order by written_dt desc; (select * 은... 예제니까 쓰는 겁니다!! )


날짜가 똑같은 글이 여러 개 있으면???
꼬이는 것 보다는 안 꼬이는 게 좋습니다. ordering은 written_dt 가 아니라 msg_id 로 거는 것이 더 좋습니다. msg_id 는 순차적으로 증가하는 것이기 때문에 시간의 흐름과 방향이 같습니다. (date와 int 의 데이터 용량 차이를 봤을 때도 int가 유리합니다.)
논리적으로 "최근 글" 이라는 말은 "written_dt가 큰 것"이라는 의미인데, db에서는 "msg_id가 큰 것"이라는 것과 같습니다.

쿼리는 아래와 같이 바꾸겠습니다.

select * from msg where writer='홍길동' order by msg_id desc;


일단 writer를 기준으로 뽑기 때문에 writer 컬럼에 인덱스가 있어야 합니다.
두 번째로 볼 것은 order by 인데 ordering에서도 index를 사용할 수 있습니다. (binary 계열인덱스만!!)

위의 쿼리를 실행시키는 가장 바람직한 index는 (writer, msg_id) 2개의  컬럼에 걸린 index 입니다.

아래와 같은 데이터가 있을 때..

msg_idwriter
1홍길동
2김말똥
3홍길동
4홍길동
5김말똥
6홍길동

writer, msg_id 의 인덱스는 아래와 같이 생성된다고 보시면 됩니다. (각각 이름 순으로 정렬하고, 같은 이름 안에서는 msg_id 순으로 정렬하고.. )

writermsg_id
김말똥2
5
홍길동1
3
4
6

writer로 홍길동은 찾을 후 msg_id 를 뒤에서 부터 찾아면 id가 6,4,3,1 의 순서대로 보여질 겁니다.

"홍길동"이 "서울"에서 쓴 글을 최근 순서대로 보여주는 쿼리는 아래와 같을 겁니다.

select * from msg where writer='홍길동' and place = '서울' order by msg_id desc;

이 쿼리는 (writer, msg_id) 로는 제대로 작동하지 않습니다. 중간에 place 컬럼에 대한 조사가 필요하기 때문입니다. 이 쿼리를 빠르게 돌아가게 하기 위해서는 (writer, place, msg_id) 의 인덱스나 (place, writer, msg_id) 의 인덱스를 거는 것이 좋습니다.

쿼리는 살짝만 달라져도 타는 인덱스가 확 바뀔 수 있습니다. 따라서 인덱스를 만들고 변경하고 삭제할 때는 반드시 쿼리 로그를 다 살펴보고 해야 합니다. 그렇지 않으면 갑자기 성능이 급격하게 저하되는 일이 발생할 수 있습니다.

M개의 데이터 에서 N개를 찾아서 정렬할 경우 인덱스가 없으면
찾는데 M만큼의 비용이들고, 정렬하는데 N*log(N)의 비용이 듭니다. (log는 밑이 2) 1천 만개중 1백만개를 찾아서 정렬하다고 치면,
찾는데 드는 비용 : 10,000,000 
정렬하는데 드는 비용 1,000,000 * log(1,000,000) => 대략 20,000,000 
즉 검색된 데이터가 많으면 검색비용보다 정렬비용이 훨씬 많이 들 수 있습니다. 따라서 정렬이 있을 경우에는 반드시 index를 타게 만들어야 속도가 나옵니다.

덤으로... mysql의 innodb의 경우는 인덱스의 맨 마지막에 primary column을 추가 시킵니다. 위의 테이블의 경우는 writer에만 인덱스를 걸면 자동으로 primary column 으로 보이는 msg_id가 따라서 인덱스로 붙습니다. 이것은 기본적으로 데이터 저장방식의 차이 때문에 생기는 것인데, innodb의 경우는 primary 키를 기준으로 data row(각 데이터 한 줄..) 를 저장하기 때문에 index를 타고 데이터를 가져오더라도 다시 primary 키를 기준으로 데이터를 뽑아와야 하기 때문에 강제로 primary key 컬럼이 index의 마지막 컬럼에 더해집니다. innodb에서는 (writer) 로 만들어도 내부적으로는 (writer, msg_id) 로 처리가 되기 때문에 (writer, msg_id ) 로 만들 필요가 없습니다.


3. 인덱스를 제대로 쓸 수 없는 쿼리들

select * from msg where writer in('홍길동', '김말똥') order by msg_id desc;

김말똥이 쓴 것과 홍길동이 쓴 것을 합쳐서 msg_id 순서대로 정렬을 해야 하기 때문입니다. where 절에서는 index를 잘 타겠지만, ordering에서는 탈수가 없습니다.(합쳐서 정렬해야 하기 때문에..) index를 안 거는 것보다는 좋겠지만, 정렬의 부하가 큽니다.



select * from msg where writer like '홍%' order by msg_id desc;

홍으로 시작하는 놈을 찾는 것은 인덱스를 타겠지만, 홍으로 시작하는 여러놈을 뽑은 다음에 정렬은 위와 비슷한 현상이 발생합니다.



select * from kakao_talk where (sender = '홍길동' and receiver = '김말똥' ) or (sender = '김말똥' and receiver = '홍길동' ) order by talk_id desc;

카카오톡에서 홍길동과 김말똥이 서로 주고 받은 메세지를 최근 글 순서로 정렬하는 것입니다. index는 (sender, receiver, talk_id) 로 걸려있다고 했을 때, where 절 까지만 index가 잘 타고 맙니다. 또 정렬의 문제가 생깁니다.



select * from msg where name like '%동';

위에서 말씀드린 것처럼 인덱스는 순차적으로 정렬이 되어 있는 것이기 때문에 "동"으로 끝나는 모든 사람을 찾기 위해서는 다 뒤지는 수 밖에 없습니다. 이것은 인덱스를 전혀 타지 못합니다.



select * from location where x between 38.12345 and 42.12345 and y between 120.12345 and 130.12345;

(x,y) 로 인덱스를 걸어서 특정 위경도 안에 있는 것을 뽑아내려는 경우입니다. x에 대해서 검색하고 그 중 y에 대해 다시 검색하기 때문에 x에 대해서만 인덱스가 걸려있는 것과 크게 다르지 않습니다. (x의 값의 갯수가 적게 나오는 경우라면 y의 검색에서도 인덱스가 의미가 있을 수도 있습니다.)



select * from event_table where open_dt > now() and close_dt < now() order by close_dt desc limit 10;

현재 진행중인 이벤트 ( 즉, 시작일 < 현재 < 종료일 순인 경우) 를 찾는 경우, 인덱스를 (open_dt , close_dt) 로 걸어 봤자 open_dt 로 검색하는 부분 밖에 인덱스를 타지 못합니다. 바로 위의 예제와 비슷한 경우가 됩니다.



select writer , max(msg_id) as latest_msg_id from msg group by writer order by latest_msg_id limit 10;

가장 최근에 글을 쓴 사용자10명을 뽑아오는 쿼리 입니다. index는 (writer, msg_id)로 걸려있다고 가정하면, 사용자별 최근 글을 뽑아오는데까지는 인덱스를 잘 타겠지만, 그것을 최근 글 쓴 날짜 순으로 정렬하는 것은 인덱스를 타지 못합니다. 뒤에 limit는 결국 정렬을 다 한 다음에 뽑아와야 하기 때문입니다.


4. 인덱스를 잘 타더라도 성능이 저하될 수 있는 쿼리

select count(*)  from msg where name='홍길동';

각 사용자가  쓴 글 갯수를 뽑는 쿼리입니다.(홍길동 씨가 수천만개의 글을 썼다고 칩시다.)  갯수를 세야하기 때문에 인덱스를 아무리 잘 타도 이건 처리해야 하는 양이 너무 많아서 성능이 나오기 어렵습니다. 최근 글만 뽑아오는 것은 괜찮지만 갯수를 세는 것은 별 수 없습니다.

mysql의 myisam이 count가 굉장히 빠르다는 낭설이 있는데, 뻥입니다. myisam의 count가 빠른 건

select count(*) from table ;

의 형태 뿐입니다. myisam은 테이블의 전체 줄 수를 따로 저장하고 있기 때문에 where 절이 없는 count의 경우는 따로 저장된 값을 가져올 뿐 실제 테이블에 접근하지 않기 때문입니다. where 절이 들어가 있는 count 같은 경우는 엔진에 상관없이 별 수 없습니다.


다음 글에서 위에서 제기한 문제들에 대한 해법을 찾아보겠습니다. 간단하게 쓸려고 했는데 간단해 지질 않네요..ㅜㅜ

by 삼실청년 | 2012/03/29 02:05 | 컴터질~ | 트랙백 | 덧글(5)

트랙백 주소 : http://iilii.egloos.com/tb/5621264
☞ 내 이글루에 이 글과 관련된 글 쓰기 (트랙백 보내기) [도움말]
Commented by alstn at 2012/04/14 23:46
다음 글이 기대가 되네요~ .잘 봤습니다.
Commented by 삼실청년 at 2012/06/16 23:41
쩝 이래저래 하다보니 2달이 넘게 걸렸네요.. 글 올렸습니다.^^
Commented by 만두 at 2012/05/09 00:20
언제쯤 다음 위에서 제기한 문제들에 대한 해법편을 볼 수 있을까요.? 무척 기대 됩니다.
Commented by 삼실청년 at 2012/06/16 23:41
바로 지금요.ㅋㅋ 오래 걸렸네요ㅜㅜ
Commented by 만두징 at 2012/07/14 02:06
^^ 잘 봤습니다. 실무하면서 대략적은 맥락은 비슷하게 구현했는데 글 읽고 많이 공감하고
도움이 되네요. 감사 드립니다.

:         :

:

비공개 덧글

◀ 이전 페이지          다음 페이지 ▶