SQL 튜닝 - SELECT 절과 WHERE 절에 동일한 함수가 사용될 때 [성능 비교]

1번? 여러번?

SELECT 절에 있는 함수를 WHERE 절에도 사용하면서 그런 생각이 문득 들었습니다. "설마, 함수를 두 번 호출해서 계산하는건 아니겠지..? 그럼 낭비잖아!"

SELECT 
    emp_no, CONCAT(first_name, ' ', last_name) AS full_name
FROM
    employees
WHERE
    CONCAT(first_name, ' ', last_name) LIKE '%Robert%';

찾아본 결과, 쿼리를 날리면 Oracle은 라이브러리 캐시, MySQL은 쿼리 캐시에 쿼리 결과를 캐싱해서 재사용한다고 합니다. (소프트 파싱)

 

-> 하지만, 라이브러리 캐시, 쿼리 캐시는 1개의 완성된 쿼리 단위로 동작합니다. 즉, WHERE절에서 나온 결과를 SELECT절에서 재사용하게 도와주진 않습니다.

 

DBMS는 기본적으로 실행 계획을 수립하는 과정에서, 쿼리를 쪼개서 작은 단위(블록)으로 만들어서 분석하고, 비효율적인 처리를 하지 않도록 개선하고 튜닝합니다.

 

그런데 주의할 점은

 

함수를 재사용해주지는 않습니다!!

 

아래의 Reference를 보시면 Where절에서 사용했던 함수의 결과로 SELECT절을 만들어 주는 게 아니라!! Where절에 함수를 사용해서 필터링된 테이블에서, 또 함수를 사용해서 SELECT절을 만들어 주고 있음을 알 수 있습니다.

 

https://dba.stackexchange.com/questions/196220/same-function-in-select-and-where-clause

 

성능 비교

함수를 중복 호출한 SQL문과 서브 쿼리를 사용한 SQL문을 비교해봤습니다.

SELECT 
    emp_no, CONCAT(first_name, ' ', last_name) as full_name
FROM
    employees
WHERE
    CONCAT(first_name, ' ', last_name) LIKE '%Robert%';

서브쿼리를 사용하면 함수를 1번만 사용할 수 있습니다. SELECT절을 먼저 실행시켜 결과 값을 받아서, 그대로 조건절로 사용할 수 있기 때문이죠.

SELECT * FROM
    (SELECT 
        emp_no, CONCAT(first_name, ' ', last_name) AS full_name
    FROM
        employees)
WHERE
    CONCAT(first_name, ' ', last_name) LIKE '%Robert%'

정확한 측정을 위해 각각 5번씩 호출했습니다.

결과는 놀랍게도, 서브쿼리를 사용한 SQL문이 성능이 더 뛰어났습니다. (환경마다 다를 수 있음)

 

서브쿼리는 지양하자는 글을 많이 봐서 차라리 함수가 나은 줄 알았는데 아닌 경우도 있습니다.

 

<참고> ORM 에서 사용

JPA, JPQL, Sequelize 등 대부분의 ORM에서 from 절에서 서브쿼리를 사용하는 이하 인라인뷰를 지원하지 않습니다.

 

따라서 해당 방법을 사용하기 어렵습니다.

 

해결방안으로는 아래와 같은 방법이 있습니다.

• 서브쿼리보다는 Join을 사용하고 함수는 어쩔 수 없이 여러번 사용한다.
• 앱단에서 쿼리를 2개로 분리한다.
  • 또는 작업을 분할하고 특정 기능을 쿼리 대신 앱단에서 처리를 한다.
• RawQuery(이하 nativeSQL)을 사용한다.

 

마무리

Where절에 함수 사용을 지양하자는 글이 많습니다.

 

함수 자체가 인덱스도 안타고, 처리도 복잡하기 때문입니다. 아래의 문서에서도 잘 설명해주고 있습니다.

Avoid Using Functions in Where Clause

 

Where절에 함수를 사용하지 않고, 테이블 구조를 변경해서 해결할 수 있다면 참 좋겠으나, 배포중인 서비스의 테이블 스키마를 변경하는 것은 어려운 작업입니다.

 

조건절에 들어가는 함수가 그리 간단한 함수가 아니라면, 서브쿼리로 한번 감싸는 것도 방법인 것 같습니다.

 

감사합니다.