싴갤러스에서 사용하고 있는 쿼리도 여러번 바뀌었는데 다 기억은 못하고..
글 내용을 말하기에 앞서 테이블 구조부터 소개를 하자면,
아래와 같이 생겼다. 아주 심플한 테이블이라 할 수 있다.
CREATE TABLE `TABLE` ( `index` int(11) NOT NULL AUTO_INCREMENT, `date` varchar(10) NOT NULL, `time` varchar(10) NOT NULL, `nick` varchar(30) NOT NULL, `data` varchar(200) NOT NULL, PRIMARY KEY (`index`), KEY `indx_date` (`date`), KEY `indx_nick` (`nick`) ) ENGINE=InnoDB AUTO_INCREMENT=4016741 DEFAULT CHARSET=utf8
사실 처음에 몇만건 몇십만건 됐을때는 아무렇게나 짜도 무리가 없었는데 백만단위로 들어가면서부터 검색 범위를 제한하게 됐다.
유쾌하지 않은건 사실이다. 성능 때문이라는 변명에 괜히 숨는 느낌이라고 해야하나...
그래서 항상 고민이 많다. 이 글을 작성하는 시점에는 멀쩡한데 저녁에 서비스가 몰리는 시간엔 어찌될지...
검색 쿼리는 다음 조건을 가진다.
1. 날짜 범위 지정
2. 닉네임은 매칭 / 내용은 와일드카드 검색
3. 최근 순서대로 정렬
이를 간단히 생각하면 다음과 같이 작성해볼 수 있을 것 같다.
(실제로 생활코딩에 질문을 올렸을때 어떤 누가 이런식으로 쿼리를 짜서 주었음)
select `index`, `date`,`time`, `nick`, `data` from TABLE where `date` BETWEEN '20150709' AND '20150716' and `data` like '%검색어%' OR `nick` = '검색어' order by date desc
별 다른 특이사항이 보이지 않는 쿼리이다. 과연 성능은 어떨까?
Explain extended 를 사용하여 쿼리가 어떻게 수행되는지 확인을 해보자.
SIMPLE TABLE ALL indx_date,indx_nick 387809 100.00 Using where; Using filesort
검색은 SIMPLE하고, possible한 key도 기재가 되지만, 실제로는 인덱스를 타지 않는다.
왜냐면 컬럼중에 인덱싱 범주에 포함되지 않는것들이 다수 있기때문이다.
또한 검색에 소요되는 Row의 수도 거의 테이블 전체를 seeking 해야하는 수준이며, 검색 조건중 반드시 배제되어야 된다는 filesort를 통해 order by를 수행한다.
따라서, 위 쿼리로 서비스를 했다간 그냥 db가 뻗어버릴것이다
그래서 두번째로 생각한것이 서브쿼리를 이용해보는것이었다.
내 착안은 이렇다.
1. 날짜 범위별로 검색범위를 제한한다.
2. 서브쿼리에서 배달된 (delivered) 쿼리중에서만 추가검색을 진행한다.
결론부터 말하자면 근접한 방법이긴한데 정답은 아니다.
쿼리를 보면서 계속하자
SELECT `index`, `date`,`time`, `nick`, `data` FROM ( select `index`, `date`,`time`, `nick`, `data` from TABLE WHERE `date` BETWEEN '20150700' AND '20150716' ) as origindata WHERE ( `data` like '%검색어%' OR `nick` = '검색어' ) ORDER BY `index` DESC
서브쿼리를 이용해서 인덱싱이 되어있는 Date 컬럼을 이용해 범위를 제한하고,
나온 결과에서만 검색을 수행하겠다는 계획이고, 실제로 위 쿼리로 서비스를 오래 했다.
이 쿼리의 explain은 다음과 같다.
1 PRIMARY <derived2> ALL 177838 100.00 Using where; Using filesort 2 DERIVED TABLE ALL indx_date 386966 45.96 Using where
읽는 순서는 역순으로..
인덱스를 타라고 일부러 서브쿼리로 나눠놨는데 위에서 언급한것과 마찬가지로 인덱싱이 되지 않는 컬럼을 select하기때문에 결국 전체 테이블을 seeking하게 된다.
결과적으로 놓고보면, 첫번째 쿼리와 다를게 없다....
filesort도 그대로 사용하고 있고, 인덱스도 여전히 이용하지 않고 있다.
그럼 어떻게 해야할까? 우선 인덱스를 타게 해야겠다고 생각했다.
그래서 쿼리를 좀 고쳐봤다. 요렇게
select b.`index`, `date`,`time`, `nick`, `data` from TABLE as b left join ( select `index` from TABLE where `date` BETWEEN '20150709' AND '20150716' ) a on a.`index` = b.`index` where a.`index` = b.`index` and `data` like '%검색어%' OR `nick` = '검색어' ORDER BY b.`index` DESC
위 쿼리는 인덱싱이 되어있는 `index`컬럼과 `date`컬럼을 사용해보자고 생각을 했다.
그래서 서브쿼리 조인을 사용해서, 날짜에 해당하는 index들만 뽑아낸 후 바깥 테이블에서 해당 primary index에 해당하는 녀석들에서만 검색을 해보자고 시도를 해봤다. 결과는 실패였지만 과정은 괜찮았다.
1 PRIMARY b index PRIMARY,indx_nick PRIMARY 4 387047 100.00 Using where 1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 4 lute.b.index 10 100.00 Using where; Using index 2 DERIVED TABLE range indx_date indx_date 32 179508 100.00 Using where; Using index
드디어 Extra에 using index가 등장하기 시작했다.
하지만 결국 b 테이블은 전체 테이블을 seek해서 배달되는 index랑 비교를 해야되기때문에 컬럼수가 무자비했고, 간혹 돌리다보니 using filesort뿐 아니라 using temporary까지 .....
오히려 더 안좋은 결과가 되었다. 실제 쿼리타임도 쿼리를 두번 돌려야되는 꼴이기때문에 더 느린건 두말할것도 없다
하지만 아이디어 자체가 나쁘다고 생각하지 않았기때문에 using temporary라는 키워드로 검색해보다가 이곳을 발견하게 되었다.
이제 내가 해결해야될 문제는 명확했다.
1. using index를 무조건 띄워야된다.
2. filesort 및 temporary가 뜨면 안된다
3. 최소한의 Row를 사용해야한다
링크한 블로그의 커버링 인덱스(covering index)를 참조하여 다시 작성한 쿼리는 다음과 같다.
select a.`index`, a.`date`,a.`time`, a.`nick`, a.`data` from ( select `index` from TABLE where `date` BETWEEN '20150700' AND '20150716' order by `date` desc ) b join TABLE a on b.`index` = a.`index` where a.`data` like '%검색어%' OR a.`nick` = '검색어'
사실 뭐가 다른지도 잘 구분이 안된다만 엄연히 다르니 잘 보자
따로 말로 설명하면 길어질것 같으니 성능부터 보자
1 PRIMARY <derived2> ALL 193547 100.00 1 PRIMARY a eq_ref PRIMARY,indx_nick PRIMARY 4 b.index 1 100.00 Using where 2 DERIVED TABLE range indx_date indx_date 32 193547 100.00 Using where; Using index
확연한 차를 보이고 있고, 제시한 모든 문제가 다 해결되었다.
최소한의 Row를 사용하고, index를 이용해 order by 까지 처리하는걸 볼 수 있다.
이번글에서는 엄청난 내용을 소개한것은 아니다.
하지만 쿼리 한개라도 아무렇게나 작성하면 안되고 충분한 테스트 및 성능 검증이 필요하다고 말하고 싶었다.
그래서 일일히 삽질한 쿼리도 공개를 해보면서 풀어나가듯이 작성해봤다.
어쨌든 디비쿼리와의 전쟁은 계속되겠고 최적화를 위해서 계속 삽질을 하자.
Reference
http://gywn.net/2012/04/mysql-covering-index/