[SQL] 쿼리 튜닝 정리

📌 참고사항

• Postgresql 위주로 작성

쿼리 튜닝을 하게된 이유

마이그레이션 작업중 급격하게 늘어난 데이터로 인해 시스템 목록 조회할때 많은 시간이 소요되는 현상발견

 

원인

1. 과도한 JOIN 으로 인한 원인

2. WHERE 절에 LIKE문을 앞뒤로 % 붙여 사용으로 인한 원인

3. 특정 조건절에서만 사용하는 불필요한 테이블 JOIN으로 인한 원인

4. 과도한 집계함수 사용으로 인한 원인

 

해결

1. WITH절 사용하여 인덱스 적용되는 실행 우선순위별로 쪼개서 해결

≫ 원본

SELECT *
FROM A a 
JOIN B b ON a.id = b.id 
JOIN C c ON a.id = c.id 
...
WHERE a.id = 'A' 
...
LIMIT 10 OFFSET 0

≫ 수정본

WITH withA AS (
  SELECT a.id, COUNT(a.id) AS a_cnt
  FROM A a
  WHERE a.id = 'A'
  ...
  GROUP BY a.id
)
SELECT *
FROM withA wa
JOIN B b ON wa.id = b.id
...
LIMIT 10 OFFSET 0

 

2. WHERE 절 조건에 인덱스 사용할수 있도록 수정 (앞에 % 제거)

≫ 원본

SELECT * FROM TEST
WHERE test_name like '%테스트%'

≫ 수정본

SELECT * FROM TEST
WHERE test_name like '테스트%'

 

3. 특정 조건에만 사용하는 테이블 조건 조회할때만 사용하도록 분리

≫ 원본

SELECT test_id, test_name
FROM TEST t
LEFT JOIN T_USER tu ON t.test_id = tu.test_id
WHERE t.test_name like '테스트%'
<if test="userName != null and userName != ''">
AND tu.user_name = #{userName}
</if>

≫ 수정본

SELECT test_id, test_name
FROM TEST t
WHERE t.test_name like '테스트%'
<if test="userName != null and userName != ''">
AND EXIST (
    SELECT 1
    FROM T_USER tu
    WHERE tu.test_id = t.test_id
    AND tu.user_name = #{userName}
)
</if>