글쓴이:전영규  [번역] MySQL은 어떻게 LIMIT 를 최적화하는가 조회수:542






LIMIT # 에 대해 일단 쿼리 후 # 만큼 가져오는 것으로 생각했는데,
mysql 이 몇가지 최적화를 하고 있더군요...
이 최적화가 실제로 얼마나 효과가 있을지는 약간 의문이지만,
나름대로 알고 있는 것도 괜찮을 것 같습니다.

부족한 영문실력에 번역하려다 보니... 시간도 걸리고 오역도 있는 것 같고...

오역이 있다면 바로 수정 코멘트 부탁드립니다...

--> 는 제가 첨부한 부분입니다..

http://www.mysql.com/doc/en/LIMIT_optimisation.html
5.2.8 How MySQL Optimises LIMIT


In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:

If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BYs.
As soon as MySQL has sent the first # rows to the client, it will abort the query (if you are not using SQL_CALC_FOUND_ROWS).
LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.
When the server uses temporary tables to resolve the query, the LIMIT # is used to calculate how much space is required.
5.2.8 MySQL은 어떻게 LIMIT를 최적화하는가


HAVING 을 사용하지 않고, LIMIT # 를 사용했을 때, 다음 최적화를 수행합니다:

(전체 레코드보다 작은 레코드를 찾기 위해) LIMIT 을 사용했을 때 , 전체 레코드를 순차적으로 검색하는 것보다 낫다고 판단되면 인덱스를 사용합니다.
LIMIT # 를 ORDER BY 와 같이 사용한 경우, 전체 테이블을 모두 정렬하는 것이 아니라, # 라인에 다다랐을 때 정렬을 끝냅니다.
--> ORDER BY에서 일단 레코드는 모두 찾고, 이후 정렬이 줄어들 수 있는 것 같음..
--> 아니면, ORDER BY에서 레코드를 찾다가 필드값이 정수일 경우 0~# 까지를 찾으면 끝내는 등의 섬세한 최적화를.. ?

LIMIT # 과 DISTINCT 이 같은 필드에 대해 사용되었을 때, MySQL 은 # 만큼의 유일한 레코드를 찾으면 즉시 쿼리를 멈춥니다.
때때로 GROUP BY 은 키값을 순서대로 읽는 단계나 키값을 정렬하는 단계에서 해석(수행이 완료)될 수 있고, 그리고 나서 키값이 변경되기 전까지 '요약(결과값)'을 계산합니다. 이 모든 작업에서, LIMIT # 은 불필요한 GROUP BY 연산을 하지 않습니다.
--> 불필요한 GROUP BY 연산 이란 limit 를 넘어서는 연산을 의미하는 듯.

MySQL 은 # 개의 레코드를 클라이언트에 보낸 후, 쿼리를 즉시 중단합니다. (단,  SQL_CALC_FOUND_ROWS 구문을 사용했을 때는 쿼리를 끝까지 진행합니다.).
LIMIT 0 는 쿼리를 진행하지 않고, 곧바로 텅빈(empty) 결과를 리턴합니다. 이 최적화는 쿼리문을 체크거나 컬럼타입을 조회하는 목적을 위해 유용합니다.
MySQL이 쿼리를 진행하면서 임시파일을 사용할 때, LIMIT # 은 얼마나 많은 공간이 필요할 지 계산하는 데 쓰입니다.
--> 사용자입장에서는 별로 필요없는 최적화같네요.. -_-



 
 

 
 Fencer 당연히 후자라고 봅니다.
인덱스가 잡혀있는 것을 정렬할 경우
최소값을 찾는 것은 전체를 뒤지지 않아도 가능하니까요.
전체를 뒤질 거면 인덱스를 잡을 이유가 별로 없죠. ㅡ.ㅡ; 05/17 0:19:01 
 
 깨비 explain 이란 것을 통하여 쿼리를 분석을 해보았습니다.
idx와 col이 둘다 인덱스로 잡혀있더라도
where col=1 order by idx desc;
인 경우에는 using index가 아니라 파일 소트로 나오더군요..
반면에
where idx>'start' and idx<'end' order by idx desc;
인 경우에는 index를 사용하는 것 같았습니다.

저는 무조건 인덱스가 잡혀있는 필드를 사용하면
인덱스를 사용할 줄 알았는데 항상 그렇지는 않네요..

위에서 limit 의경우에는 인덱스가 잡혀있는 경우에 한해서만
최적화가 이루어진다면 where 절에서 사용하는 인덱스필드와
order by 에서 사용하는 인덱스 필드가 다른경우에
즉, Using Index가 아니라 파일 소트인 경우에는
limit 의 최적화가 적용되지 않는 걸까요? 05/17 1:00:29 
 
 Fencer 깨비님의 첫번째 예제는
생각해 보면 당연한 것일 수 있습니다.
col 에 맞는 것을 인덱스에서 찾았다 하더라도
그것을 idx와 매칭시키려면 조합을 해보아야 하니까요.
그래서 index(col, idx) 가 필요한 것입니다. 05/17 2:30:32 
 
 한마디 desc 로 정렬을 하기때문에 filesort 가 나오는걸텐데
뒤에쿼리는 index 를 탄다니 좀 이상하네요.
다시한번 해보세요. 분명 두쿼리모두 filesort 나올겁니다.
참고로 4.0 이상에선 desc,asc 똑같이 인덱스탄다는군요.
 05/17 2:59:25 
 
 깨비 Fencer >>
아 인덱스를 그렇게 주면 되는거군요...

한마디>>
저도 explain 을 어떻게 활용하는지 여지껏 잘 몰랐습니다.
한마디님 말씀듣고 다시 해보았는데요 이런 결과가 나왔습니다.
col과 idx는 각각 정수형 인덱스 필드입니다. 이 표를 완전히
해석하지는 못하지만 뒤에 파일소트라고 나오면 안좋은 거라는
것만 알고 있어서요;;;


mysql> explain select * from board2 where idx > 1 and idx < 15 order by idx desc;
+--------+-------+---------------+------+---------+------+------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
+--------+-------+---------------+------+---------+------+------+
| board2 | range | idx | idx | 4 | NULL | 12 | Using where |
+--------+-------+---------------+------+---------+------+------+
1 row in set (0.00 sec)

mysql> explain select * from board2 where col=1 order by idx desc;
+--------+------+---------------+------+---------+-------+------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
+--------+------+---------------+------+---------+-------+------+
| board2 | ref | col | col | 4 | const | 26 | Using where; Using filesort
+--------+------+---------------+------+---------+-------+------+
1 row in set (0.00 sec) 05/17 3:49:29 
 
 Fencer desc, asc 둘 다 인덱스 잘 되는 게 정상입니다.
다만 줄이 많을 경우엔 desc 가 느리죠.

// 톡박에는 안 어울리는 ;;; 05/17 4:15:48 
 
 한마디 깨비님 mysql 버젼이 어떻게되나요?
상단에 있는쿼리는 제가 사용하는 쿼리랑 100% 똑같습니다.
다시해봤지만 전 똑같은경우에서 desc 할경우는
filesort 가 나옵니다.

asc  05/17 5:18:35 
 
 전영규 show index from board2 해보세요...
 05/17 8:45:19 
 
 거친마루 이글을 팁텍으로 보냅시다에 한표! 05/17 9:09:26 
 
 깨비 MySQL server version: 4.0.12
였네요... 이걸 위 코멘트에 같이 적었어야 했는데... ^^;; 05/17 10:19:22 
 
 우수한 MySQL 4에선 DESC도 인덱스를 타는군요.
에궁, 일부러 역순 인덱스를 만들어서 썼는데, 해당 칼럼을 다 지워야되겠네. --;; 05/17 18:08:12 
 
 한걸음 영규님의 노고에 감사드립니다.