[1원] mysql 인덱싱 - order by....

- 결합인덱스를 사용할 때 주의점.

아래와 같은 조건일 때...
------------------------------------------------------------------------------
Table_Name : 테이블명
No : auto_increment
A : 공지글(0일때 공지, 1일때 일반글)
B : Head (일반글일 경우 순차적으로 증가)
C : Order (위에서 아래로 순차증가)
D : Depth (답글의 깊이)

인덱스 추가 : ALTER TABLE Table_Name ADD INDEX (A,B,C);
-------------------------------------------------------------------------------

No,A,B,C,D 의 순서로 표기할 때 첫번째 게시물 ~ 세번째 게시물
1,1,1,1,0
2,1,2,1,0
3,1,3,1,0

여기에 공지글이 하나 달렸을 때
4,0,4,1,0

2번글에 답글이 하나 달렸을 때
5,1,2,2,1

그글에 답글 밑에 답글이 또 하나 달렸을때
6,1,2,3,2

--------------------------------------------------------------------------------

이럴 경우에 쿼리문은 아래와 같습니다.
SELECT * FROM Table_Name ORDER BY A asc,B desc,C asc
이 쿼리문의 뜻은
A 값에서 공지글(0)>일반글(1)의 순서로 정렬(asc)한 다음,
B 값에서 최근글(max) > 지난글(min) 의 순서로 정렬(desc)한 다음,
C 값에서 답글먼저올라온글(min) > 답글 나중올라온 글(max)로 정렬(asc)한다는 뜻입니다.

자... 그러면 위의 쿼리문이 인덱스를 탈까요? 못탈까요?
결론은 제대로 못탄다는 겁니다.
이유는 결합인덱스로 (A,B,C)를 줬기 때문에 설정된 인덱스가 A asc,B asc, C asc 로 잡히기 때문입니다.
ORDER 문에 의해서 A asc로 정렬할 때까지는 인덱스를 타는데, 그 다음에 B asc가 아니고 B desc로 되어있기 때문에 여기부터 일반필드를 참조해서 정렬을 해버립니다.

그러므로 결합인덱스를 제대로 사용하기 위해서는 각각의 값이 동일한 순서로 정렬이 되도록 만들어야 됩니다.
위의 예에서 B 값만 desc이기 때문에 인덱스를 정상적으로 타기 위해서는 B 값을 [순차증가값]이 아니라 [순차감소값]으로 만들면 됩니다. (예, 최초의 글이 9999999, 그 다음 글이 9999998이 되도록 한다. 혹은 최초의 글이 -1, 그 다음 글이 -2가 되도록 한다.)
그렇게 되면 [ORDER BY A asc,B asc,C asc]의 순서로 정렬을 하면 정상적으로 목록을 출력하게 되는 것인데, 이 경우에 [ORDER BY A asc] 만 해주면 결합인덱스에 의해서 B,C의 값은 자동으로 정렬이 됩니다.

위의 결과는 10만건 데이터를 가지고 실험했으며,
결합인덱스를 정상적으로 타지 못할 때 - 0.12 sec. (편차 0.05)
결합인덱스를 정상적으로 탈 때 - 0.01 sec (편차 0.01)
의 결과값을 얻었습니다.

이 실험을 하게 된 것은 동일하게 결합인덱스를 줬으나 [ ORDER BY A asc ] 일 경우와 [ ORDER BY A asc,B desc ] 의 경우가 둘다 동일한 인덱스를 타는데도 불구하고 소요시간이 너무 차이가 나서 그 차이의 원인을 찾다가 알게 된 것입니다.

(3시간만에 깨달은 팁입니다... 에효... 힘들어...)