non clustered index 로 검색할때 order by, limit 조건에 따라 지연 현상(mysql)

약 20만개의 데이터가 들어 있는 테이블을 날짜로 조회하려고 하니, 평소와는 다르게 지연이 생겼다. 이를 찾다, 찾다 확인해보니 해당 쿼리의 응답이 늦는 것을 확인 할 수 있었다.

 

1. 지연이 생기는 코드

SELECT * FROM table WHERE date BETWEEN "2021-06-24" AND "2021-06-25"  ORDER BY id desc LIMIT 100;

 

index를 걸어둔 date를 해당 구간으로 조회를 한 후 100를 id 기준 내림차순으로 정렬하는 쿼리이다. 하지만 결과가 제한된 수인 100개 이상일 때는 문제가 없지만 이 이하가 되는 경우 응답이 늦었다.(정렬기준이 오름차순도 마찬가지였다. 즉 limit 수보다 근소하게 작을때, 오름차순이든, 내림차순이든 정렬을 함께 하게되면 지연이 생겼다.)

그리고, 정렬을 하지 않거나 차라리 1000개, 10000개를 정렬하여 가져오는게 더 빨랐다.

또한, 정렬과 limit 조건을 함께 사용하고 싶은 경우 두 개를 나눠 서브쿼리로 처리하면 또 빨랐다. 

왜이럴까????

 

느림

SELECT * FROM table WHERE date BETWEEN "2021-06-24" AND "2021-06-25"  ORDER BY id desc LIMIT 100;

조회결과 60개

SELECT * FROM table WHERE date BETWEEN "2021-06-24" AND "2021-06-25"  ORDER BY id LIMIT 100;  

조회결과 60개

 

빠름

SELECT * FROM table WHERE date BETWEEN "2021-06-24" AND "2021-06-25"  LIMIT 100;

조회결과 100개

SELECT * FROM table WHERE date BETWEEN "2021-06-24" AND "2021-06-25"  ORDER BY id;

조회결과 60개

SELECT * FROM table WHERE date BETWEEN "2021-06-24" AND "2021-06-25"  ORDER BY id LIMIT 1000;  

조회결과 60개

SELECT * FROM table WHERE date BETWEEN "2021-06-23" AND "2021-06-25"  ORDER BY id LIMIT 100;  

조회결과 100개

select * from (

SELECT * FROM table WHERE date BETWEEN "2021-06-24" AND "2021-06-25" order by id desc

 ) as a

limit 100;

조회결과 60개

 

 

마지막으로 order by를 id 가 아닌 date로 하였을때, 빠르게 결과값을 확인 할 수 있었다.

 

즉 문제는 where 조건은 인덱스를 생성한 date로 하고, 정렬은 다른 index인 id로 하면서 limit 수가 근소하게 작은 경우 지연이 생기는 것이다.  문제는 찾았는데 정확한 해결 방법을 알지 못하겠다.

짧은 지식으로는 pk 인 id는 clustered index가 되고, 임의로 생성한 date는 non clustered index다. 그리고 이들의 구조는 아래와 같이 다르다.

드는 추측은 혹시 검색은 no clustered index를 통해 하고, 정렬은 또 clustered index로 해서 생긴 문제가 아닐까 한다.

근소하게 작은 경우 생긴 이유는 구조상 남은 부분을 찾으려고 다른 node들을 추가 탐색하는데, 결과를 만족하지 못했기 때문에 전체 탐색까지 이어지기 때문에 지연이 발생한 것이라는 것이다.

최근 클러스터와 비클러스터인덱스에 대한 생각을 해보았기 때문에 정확하지도, 정답이라고도 생각하지 않는다.

이후 이 문제를 공유하여 추가 정보와 지식을 습득하고자 한다.

 

clustered index

 

 

non clustered index