검색 성능 개선 #8 Optimizer에 의한 full scan 발생

문제 발생

위 사진을 본다면 star_score column은 B-TREE index로 등록이 되어 있는 것으로 나온다.

---

select * from store where star_score between 3 and 5;

하지만 해당 쿼리문을 날려보면 where절을 모두 사용하는 full scan이 발생하는 것을 볼 수 있다.

index를 탔다고 보기엔 너무 오래걸리는 시간이다 즉 index를 타지 않았다.

---

explain select * from store where star_score between 3 and 5;

실제로 explain으로 확인해보면 where절을 모두 사용한 것으로 나온다.

---

원인

explain select * from store where star_score between 3 and 5;
explain select * from store where star_score between 4 and 5;
explain select * from store where star_score between 4.5 and 5;

위 사진을 보면은 별점이 4.5 부터 5개 사이의 영역이라면 index condition 즉 index를 타는 것을 볼 수 있다.

이러한 현상이 발생 하는 이유는

DB의 Optimizer에 의해 최적의 결과를 얻을 수 있는 실행을 하기 때문이다.

---

Optimizer의 역할

• 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행한다.

즉, SQL실행시 실행계획을 수립하는 것이다.

• 사용자 요구사항을 만족하는 결과를 추출할 수 있는 다양한 실행 방법들 중에서 최적의 실행 방법을 결정하는 것이 옵티마이저의 역할이다.

---

Optimizer의 우선순위

 

즉 가장 후순위인 full table scan이 발생했다.

왜 full scan이 발생했냐면

index로 타기에는 이미 너무 많은 데이터를 조회해야 하기 때문이다.

---

select * from store where star_score between 3 and 5;

별점이 3부터 5개 사이의 데이터를 가져오는 쿼리를 작성했을때 약 80만개의 데이터를 조회하기 되는데

이는 총 200만개의 데이터중에서 80만개를 데이터를 조회하는 것이므로

Optimizer가 full scan으로 조회하는 것이 더 효율적이라고 판단했기에 full scan으로 조회한 것이다.

---

만약 강제로 index를 타게 한다면..

1. Full Scan

select * from store where star_score between 3 and 5;

full scan 수행 결과 : 7.29초

 

1. Index

select * from store force index(star_score_idx)  where star_score between 3 and 5;

index 강제 실행 수행 결과 : 7.45초

 

결과를 보면 index를 타는 것보다 full scan을 하는 것이 더 빠르다는 것을 볼 수 있다.

80만건의 데이터를 조회한 뒤 인덱싱되어 있는 데이터로 가서 다시 조회를 하기 때문에 시간이 더 걸리는 것이다.

---

index를 제어하는 쿼리문은 아래와 같다.

1. FORCE INDEX(인덱스명)
2. 쿼리 수행 시 지정한 인덱스만 사용하게 만들어준다
3. USE INDEX(인덱스명)하지만 무조건 사용하는 것은 아니기 때문에
4. 쿼리 수행 시 다른 인덱스를 사용하는 것이 낫다고 판단하면 다른 인덱스를 사용하기도 한다
5. 인덱스 사용 시 지정한 인덱스를 우선적으로 사용한다
6. IGNORE INDEX(인덱스명)
7. 지정한 인덱스를 사용하지 않고 다른 인덱스만 사용하게 만들어 준다

---

해결

이러한 문제 자체가 별점은 0점부터 5점까지 한정적인데 이를 제어하려 했기 때문이다.

데이터가 200만건이나 되기 때문에 이는 무리가 있고

가게, 도로명 주소, 업종 별 키워드에 별점을 추가하는 방식으로 한다면 해결이 가능하다.

또는 limit을 걸어두는 방식이 있다.

---

1. 키워드에 별점을 추가하는 방식

explain select * from store where type_of_business like "일식%" and star_score between 3 and 5;

업종을 기준으로 한다면 일식에 해당하는 업종이 미리 걸려있는 index에 의해 index condition으로 데이터를

찾아 오게 되고 그 찾아 온 데이터에서 별점에 대한 쿼리를 실행한다.

select * from store where type_of_business like "일식%" and  star_score between 3 and 5;

수행시간은 4975개의 데이터가 0.09초가 걸렸다.

select * from store where match(store_name) against("성수") and  star_score between 3 and 5;

full-text index를 거친 쿼리문 또한 빠른 검색결과가 나온다.

---

2. limit을 걸어두는 방식

.limit(limitCnt(condition))

private int limitCnt(SearchCondition condition) {
        if (!(org.apache.commons.lang3.StringUtils.isEmpty(condition.getStoreName()))) {
            return 10;
        }
        if (condition.getStarScore() > 0) {
            return 100;
        }
        return 1000;
    }
//.....이하 생략

condition을 매개변수로 받아서 동적으로 limit값을 임의로 정해주는 방법도 생각해 볼 수 있다.

그렇다면 limit에 해당하는 값은 orderBy절에 의해 정렬된 순서에 따라서 limit까지의 데이터가 return된다.

 

쿼리문 검색 결과

select * from store force index(star_score_idx)  where star_score between 3 and 5 limit 100;

select * from store where star_score between 3 and 5 limit 100;

인덱스를 강제로 타게했을때와 그렇지 않았을때 결과는 시간이 동일하다.

explain select * from store where star_score between 3 and 5 limit 100;
explain select * from store force index(star_score_idx)  where star_score between 3 and 5 limit 100;

이유는 두 쿼리 모두 index를 통해 결과가 출력되었기 때문이다.