DB 쿼리 최적화 개선 경험 공유

• 목적
  디비에 저장되어있는 오브젝트 키 목록을 들고오기 위해 사용되는 쿼리 성능을 향상시키고자 함을 목표로 한다.

• 문제점
  지금 10만건의 디비 데이터를 사용했을 때는 평균 110ms 속도가 나오지만 1000만건의 데이터로 사용했을 때는 약 2m 이라는 시간이 걸리는 것을 확인할 수 있었다.
  

• 원인 파악
  더미 데이터를 넣은 테이블로 EXPLAIN ANALYZE를 돌려보면

-> Limit: 1000 row(s) (cost=1.6e+6..1.6e+6 rows=1000) (actual time=115673..115673 rows=1000 loops=1) 
...
-> Temporary table with deduplication (cost=1.32e+6..1.32e+6 rows=2.48e+6) (actual time=101014..101014 rows=10e+6 loops=1)
-> Filter: ((~~~~.FileKey like <cache>(concat('LOCALDISK01/','%')))) (cost=1.07e+6 rows=2.48e+6) (actual time=0.049..23026 rows=10e+6 loops=1) 
-> Table scan on [tableName] (cost=1.07e+6 rows=9.92e+6) (actual time=0.0429..15644 rows=10e+6 loops=1)

이렇게 나온 것을 확인할 수 있다.
여기서 분석을 해보면
첫 번째, 일단 인덱스가 안잡히고 풀 스캔을 하고있다.(현재 fileKey라는 컬럼 인덱스 걸려있음)
두 번째, Temporary table with deduplication 임시 테이블이 만들어졌는데 거기에 쿼리 갯수가 너무 많았다.

• 해결

1. 일단 인덱스를 잡는게 우선이었다.
   인덱스가 잡히게 하려면 CONCAT이나 다른 연산 함수가 들어가게 되면 인덱스를 안잡히도록 해놨다고 한다. 그래서 concat 없애기로 했다. 하지만 concat을 사용한 이유가 값이 변동되는 값이여서 사용한거라 코드에서 파라메터를 수정해서 보내주는 것으로 변경하기로 했다.

concat 제거 결과

별 변화가 없는 것을 확인할 수 있었다.
분석을 해보니 concat을 적용하기 전과 똑같았다.
이유를 살펴보니
옵티마이저는 항상 인덱스를 적용하지 않는다.
실행 계획을 짤 때 인덱스를 사용할지, 풀스캔을 할지 결정한다. 지금 쿼리에서는 조건을 만족하는 데이터가 너무 많으니 인덱스보다는 풀스캔이 더 빠르다고 판단하여 인덱스가 걸리지 않은 것이다.
하지만 limit 같이 적은 결과에서 데이터를 추출할 때는 인덱스 범위가 좁기 때문에 인덱스를 건다.

2. limit 갯수가 서브쿼리 밖에 있는 것을 확인할 수 있었다. distinct 이후에 limit을 감싸고 있어서 옵티마이저가 임시 테이블을 만들어서 전체 결과 -> 중복 제거 -> limit을 처리한 것이다. 그래서 시간이 오래걸린 것이다.
   그래서 필요없는 서브쿼리를 없애고 바로 distinct, limit을 사용했다.
   

-> Limit: 1000 row(s)  (cost=1.32e+6..1.32e+6 rows=1000) (actual time=91.2..91.3 rows=1000 loops=1)
    -> Table scan on <temporary>  (cost=1.32e+6..1.35e+6 rows=2.48e+6) (actual time=20.1..20.2 rows=1000 loops=1)
        -> Temporary table with deduplication  (cost=1.32e+6..1.32e+6 rows=2.48e+6) (actual time=20.1..20.1 rows=1000 loops=1)
            -> Limit table size: 1000 unique row(s)
                -> Filter: ([tableName].VolumeId in ('LOCALDISK01','/stg2'))  (cost=1.07e+6 rows=2.48e+6) (actual time=11.4..16.2 rows=1001 loops=1)
                    -> Index range scan on [tableName] using [indexName] over ('LOCALDISK01/' <= FileKey <= 'LOCALDISK01/􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿'), with index condition: ([tableName].FileKey like 'LOCALDISK01/%')  (cost=1.07e+6 rows=4.96e+6) (actual time=0.109..4.1 rows=1001 loops=1)

분석 결과를 보면 인덱스에서 걸러지고 limit을 걸고 distinct를 하기 때문에 시간이 확실히 줄어든 것을 확인할 수 있었다.

• 성과
  개선 전 평균 : 111,643 ms
  개선 후 평균 : 66.2 ms
  약 1,687배 성능 향상 및 실행 시간이 99.94% 단축되었다.

• 결론
  꼭 인덱스 적용이 되어있는지 확인하고 스토리지 엔진에서 어떻게하면 데이터를 작게 가져올 수 있는지 생각해보자