SQL 쿼리 성능 최적화를 위한 튜닝 팁
인덱싱이란?
책의 특정 내용을 찾고 싶을 때 목차를 보고 해당 페이지로 갈 수 있다.
이처럼 목차가 책 내에서 원하는 정보를 빠르게 찾도록 도와주는 것처럼, DB에서도 인덱스가 비슷한 역할을 한다.
즉, 인덱싱은 DB에서 원하는 정보를 찾기 위한 지름길이다. 이로 인해 데이터 검색 속도는 크게 향상되며, 데이터를 효율적으로 관리하는 데 도움이 된다.
인덱스를 타지 않는 대표적인 쿼리들
함수나 연산자를 사용하는 경우LIKE문 검색에서 와일드카드의 위치OR절을 사용하는 경우NULL 값을 비교하는 경우테이블 전체를 반환하는 경우컬럼의 자료형이 다른 검색을 하는 경우IN 연산자를 사용한 검색에서 IN 목록의 개수가 많은 경우
함수나 연산자를 사용하는 경우
LIKE문 검색에서 와일드카드의 위치
OR절을 사용하는 경우
NULL 값을 비교하는 경우
테이블 전체를 반환하는 경우
컬럼의 자료형이 다른 검색을 하는 경우
IN 연산자를 사용한 검색에서 IN 목록의 개수가 많은 경우
1. 좌변을 연산하지 않을 것
좌변 연산이란, SQL 쿼리에서 WHERE 절의 왼쪽에 함수나 연산을 적용하는 것을 말한다. 이는 종종 인덱스 사용을 방해하며 쿼리 성능을 저하시킬 수 있다.
좌변 연산의 문제점
예를 들어, 특정 년도의 데이터만 필터링 하고 싶다면 다음과 같은 쿼리를 할 수 있다.
이 방식은 매우 직관적이고 이해가 쉽지만, DB 관점에서는 효율성에 큰 문제가 생길 수 있다.
데이터 원본을 변형하여, 내가 찾고자 하는 범위와 비교하는 연산은 DB가 인덱스를 제대로 활용할 수 없게 만든다.
마치 책 페이지 번호를 함수 연산하여 계산하며 찾는 것과 같다.
인덱스는 원본 데이터를 그대로 가지고 만들어진다. 예를 들어 date 컬럼에 대한 인덱스는 '2023-06-01', '2023-06-02'와 같은 날짜 값 자체를 가지고 구성된다.
그래서 위 쿼리처럼 YEAR(date) = 2021과 같이 데이터를 변형하는 연산을 수행하면, 이 인덱스를 제대로 활용할 수 없게 된다.
한 줄 한 줄 YEAR(date) 연산을 수행하고, 그 결과가 2021인지 확인하는 것이다. 데이터가 많을수록 이는 엄청난 작업량이 된다.
효과적인 대안: 우변에서의 데이터 필터링
데이터를 “변형”하지 않고, 원본 형태를 유지하면서 필요한 데이터를 찾아야 한다.
아래 쿼리처럼, 이를 위해 “기간”을 명시하여 데이터를 필터링하는 방식을 활용할 수 있다.
이 쿼리는 sales 테이블에서 date 컬럼의 값이 2021년 1월 1일부터 2021년 12월 31일 사이에 있는 모든 데이터를 찾아준다.
여기서 주목할 점은 “date 컬럼을 그대로 사용하고 있다.” 이다. 별도의 연산을 수행하지 않고, 날짜 값 자체를 직접 비교하고 있다.
이렇게 하면 DB는 date 컬럼에 대한 인덱스를 효과적으로 활용할 수 있다. 이처럼 원본 데이터를 직접 비교하는 조건을 사용하는 것이 인덱스를 최대한 활용하고 쿼리 성능을 높이는 핵심 비결이라고 할 수 있다.
2. OR 대신 UNION을 사용할 것
데이터를 조회할 때 여러 조건을 만족하는 결과를 얻기 위해 흔히 OR 연산자를 사용한다.
이 쿼리도 간단하고 직관적이지면, 성능 면에서는 최선의 선택이 아닐 수 있다.
OR 연산자를 사용하면 DB는 한 번의 스캔으로 모든 조건을 확인해야 한다. 이 과정에서 불필요한 데이터까지 대량으로 검색하게 되고 특히 인덱스를 제대로 활용하지 못하는 경우가 많아진다.
department 컬럼에 인덱스가 있다고 가정할 때, OR 연산자로 인해 DB는 인덱스를 효율적으로 사용할 수 없게 된다.
인덱스는 단일 값에 대한 빠른 검색을 위해 최적화되어 있는데, OR은 여러 값을 동시에 찾아야한다. 결국 인덱스의 장점을 살리지 못하고 전체 데이터를 모두 뒤져야 하는 상황이 발생한다.
OR 연산자의 경우 앞 조건이 어떻든 간에 뒷 조건도 확인해야 한다.
어차피 뒷 조건도 확인할 것이면 (풀테이블 스캔) + (인덱스 레인지 스캔) 보다 (풀 테이블 스캔) 1번이 더 빠르기 때문에 인덱스를 사용하지 않고 전체 조회를 하게 되는 것이다.
즉, OR 연산자를 사용하면 OR 연산자에 관계된 모든 컬럼이 복합키로 설정되어 있지 않다면 인덱스를 사용하지 않는다.
이런 문제를 해결하기 위해 UNION을 활용할 수 있다.
UNION은 각 조건에 대한 쿼리를 별도로 실행하고 그 결과를 합쳐주는 연산자이다.
이렇게 변경하면, 각 쿼리는 독립적으로 최적화하고 실행할 수 있다.
각 쿼리는 인덱스를 통해 빠르게 처리되고, UNION이 두 결과를 합치면서 중복된 결과는 자동으로 제거된다.
만약 중복이 없다는 것이 확실하면 UNION ALL을 사용해 중복 제거 단계를 건너뛰고 성능을 더 높일 수 있다.
대부분의 경우 정확한 결과를 위해 UNION이 권장된다.
이처럼 OR 대신 UNION을 사용하는 것은 복잡한 쿼리의 성능을 최적화하는 효과적인 방법 중 하나이다.
각 조건마다 별도의 쿼리를 수행하고, 인덱스를 활용하도록 함으로써, DB의 부하를 줄이고 전체적인 조회 성능을 크게 개선할 수 있다.
그럼 무조건 UNION?
OR과 UNION의 선택 기준에 대해서 고민해봐야 한다.
데이터 크기와 분포
소량: OR이 간단할 수 있음
대량: UNION이 효과적일 수 있음
인덱스 구조
복합 인덱스가 OR 조건을 모두 커버한다면 OR 사용 가능
개별 인덱스만 존재: UNION이 효과적일 수 있음
쿼리 복잡도
단순 쿼리: OR이 가독성이 좋음
복잡 쿼리: UNION이 명확할 수 있음
성능 요구사항
높은 성능 필요: UNION
일반적인 성능: OR로도 충분히 커버 가능할 수 있음
OR과 UNION 모두 테스트하고 실행 계획을 비교해야 한다.
3. 필요한 Row와 Column만 선택하여 성능 최적화 하기
DB를 조회할 때, 불필요한 정보까지 모두 가져오는 것이 성능 저하의 주범 중 하나이다.
특정 조건을 만족하는 Row만 선택하기
‘Marketing’ 부서에만 속하면서 매출액이 100,000 이상인 직원들의 이름과 이메일 주소를 조회하고 싶다.
위 쿼리는 employees 테이블에서 조건에 해당하는 직원들의 이름(name)과 이메일(email)을 조회한다.
이렇게 불필요한 Row는 걸러내고 필요한 Column만 선택하는 쿼리는 응답 속도를 높이고 시스템 부하를 줄이는데 큰 도움이 된다.
서브쿼리를 활용해 필요한 데이터만 추출하기
각 부서별로 최고 매출액을 달성한 직원의 정보를 조회한다고 가정하자.
위 쿼리에서는 서브쿼리를 활용해, 부서별 최대 매출액을 계산하고 결과와 함께 employees 테이블을 조인하여 필요한 정보를 추출하고 있다.
서브쿼리에서 불필요한 Column을 제외하고 오직 department와 max_sales만 선택함으로써, 중간 결과의 크기를 최소화하고 있다.
이렇게 불필요한 데이터 처리를 최소하 하는 것이 DB 성능 최적화에서 기본 중 하나라고 할 수 있다.
4. 분석 함수를 활용해 쿼리 성능 높이기
분석 함수(Analytic Functions)는 SQL 쿼리의 성능을 한 단계 높이는 강력한 도구이다.
단순히 데이터 처리를 넘어, 데이터 분석과 쿼리 최적화에 있어서 핵심적인 역할을 수행한다.
이런 함수들은 복잡한 데이터 집합 내에서 각 Row 별로 세부적인 계산을 가능하게 해준다. 전체 데이터에 걸쳐 다양한 통계와 계산을 유연하게 수행하도록 도와주는 것이다.
대표적으로
ROW_NUMBER(),RANK(),DENSE_RANK(),LEAD(),LAG()등의 함수가 있다.
쿼리 효율성을 높이는 분석 함수
분석 함수는 전통적인 집계 함수와 달리 사전에 데이터를 그룹화 할 필요가 없다. 이는 불필요한 자원 소모를 줄이고 쿼리 성능을 높이는 데 큰 도움이 된다.
또한, 복잡한 데이터 분석 과정에서 발생할 수 있는 중간 결과물의 저장과 재처리를 최소화 할 수 있다.
순위 결정 함수로 데이터 순서 매기기
ROW_NUMBER(), RANK(), DENSE_RANK() 같은 함수는 데이터 내 각 항목의 순위를 정하는 데 유용하다.
예시로 부서별로 급여가 높은 직원 순으로 순위를 정하고 싶다면 ROW_NUMBER() 함수를 이용할 수 있다.
RANK()와 DENSE_RANK() 함수도 비슷한 방식으로 동작하지만, 동일한 값에 대해 동일 순위를 부여한다는 점이 다르다. DENSE_RANK()의 경우 순위 간격을 항상 1로 유지하는 특징이 있다.
데이터 변화를 추적하는 분석 함수
LEAD(), LAG() 함수는 현재 Row와 관련하여 이전 또는 다음 Row의 데이터를 참조할 수 있게 해준다.
위 쿼리는 각 부서 내에서 입사일자 순으로 직원을 정렬하고, 이전 직원의 연봉과 현재 직원의 연봉 차이를 계산하여 연봉 인상액을 구하고 있다.
이런 함수들은 특히 시계열 데이터나 연속적인 데이터 집합을 다룰 때, 이전 데이터 포인트와의 비교가 필요한 분석에 유용하게 활용된다.
분석 함수로 데이터 필터링 최적화하기
각 부서별로 급여가 높은 상위 3명의 직원 정보만 추출하고 싶다면 ROW_NUMBER() 를 이용해 쿼리를 작성할 수 있다.
위 쿼리는 각 부서내에서 급여 순위를 매긴 후, 순위가 3 이하인 직원들만 선택하는 방식으로 동작한다.
이렇게 하면 전체 데이터를 먼저 스캔하지 않고도 필요한 결과만 빠르게 필터링 할 수 있다.
5. 와일드카드(%)는 끝에 작성하는 것이 더 좋다.
SQL에서 LIKE 연산자와 함께 와일드카드를 사용하면 텍스트 데이터를 유연하게 검색할 수 있어 유용하다.
그런데 와일드카드의 위치에 따라 쿼리 성능이 크게 달라질 수 있다.
와일드카드의 위치가 중요한 이유
위 쿼리는 “John”으로 끝나는 모든 이름을 찾는 쿼리이다. 문제는 와일드카드가 앞에 있으면, DB가 “John”으로 끝나는 모든 가능한 문자열 조합을 일일이 검색해야 한다.
인덱스가 있어도 이를 활용할 수 없다. 결과적으로 DB에는 많은 부하가 생기고 쿼리 속도가 느려진다.
문자열 뒤에 와일드카드를 사용하는 경우를 보자.
쿼리는 “John”으로 시작하는 모든 텍스트를 조회하고 있다. 이렇게 하면 DB가 인덱스를 활용하여 검색 범위를 효과적으로 좁힐 수 있다.
먼저, DB는 인덱스에서 “John”으로 시작하는 첫 번째 항목을 찾아낸다. 그리고 “John”으로 시작하지 않는 첫 번째 항목이 나올 때까지만 검색하면 된다.
이처럼 Like 연산자와 와일드카드를 사용할 때 DB가 인덱스를 더 잘활용할 수 있게 가급적 문자열 끝에 와일드카드를 두는 것이 좋다.
6. 계산값을 미리 저장하고 나중에 조회할 것
DB에서 복잡한 연산을 실시간으로 처리하는 것은 쿼리 성능에 큰 부담이 될 수 있다. 특히 대용량 데이터에서는 더욱 그렇다. 이런 상황에서 자주 사용되는 계산값을 미리 저장해두고, 필요할 때 꺼내 쓰는것이 효과적인 최적화 방법이 될 수 있다.
실시간 계산의 비효율성
이커머스 사이트에서 각 상품의 통계치를 실시간으로 계산한다고 가정해보자.
이 쿼리는 products, order_details, orders 테이블을 조인하여 각 상품(product_id)별로 평균 구매 금액(avg_order_amount), 총 매출(total_sales), 구매자 수(num_purchasers), 재구매율(repurchase_rate)을 계산하고 있다.
문제는 이 쿼리가 실행될 때마다 방대한 양의 주문 및 고객 데이터를 모두 읽어 복잡한 계산을 수행해야 한다는 것이다. 특히 재구매율 계산을 위해 서브쿼리까지 사용되어 쿼리 속도는 저하될 수밖에 없다.
계산값을 저장하고 활용하기
이런 문제를 해결하기 위해 계산 결과를 별도의 테이블에 저장할 수 있다.
이 쿼리는 먼저 product_stats라는 새 테이블을 만들고, 앞서 본 복잡한 계산을 수행하여 각 상품의 통계치를 미리 저장하고 있다.
이렇게 해두면 나중에 이런 통계치가 필요할 때, 이 테이블에서 바로 값을 가져올 수 있다. 복잡한 실시간 계산 대신 미리 저장된 값을 사용하니, 쿼리 속도가 훨씬 빨라질 수 있다.
주기적인 계산 결과 업데이트
물론 데이터가 새로 추가될 때마다 통계치를 업데이트 해야 한다. 하지만 이건 실시간으로 할 필요가 없지만 일정 주기(하루)마다 통계치를 업데이트하는 배치 작업을 수행하면 된다.
이 쿼리는 product_stats 테이블의 통계치들을 최신 주문 내역을 바탕으로 업데이트한다.
각 통계치별로 서브쿼리를 사용하여 최신 값을 계산하고, 그 결과로 product_stats 테이블의 값들을 갱신하고 있다.
이런 식으로 계산 결과를 저장하고 주기적으로 업데이트하면, 복잡한 실시간 쿼리의 부담을 크게 줄일 수 있다.
자주 사용되는 통계치, 집계값 등은 미리 계산해서 저장해 두는 것이 성능 최적화에 큰 도움이 된다는 사실이다.
요약
데이터를 변형하는 연산은 피하고, 원본 데이터를 직접 비교하는 조건을 사용하자. 이는 인덱스 활용도를 높여 쿼리 속도를 향상시킨다.
OR 연산자 대신 UNION을 활용하면 각 조건을 독립적으로 최적화하고 인덱스를 효과적으로 사용할 수 있다.
OR는 인덱스를 활용할 수 없다.
불필요한 레코드와 컬럼을 제외하고 꼭 필요한 데이터만 조회한다.
복잡한 데이터 분석에서는 분석 함수를 적극 활용하자.
LIKE 연산자와 와일드카드를 같이 사용할 때, 문자열 끝에 와일드카드를 두는 것이 인덱스 활용에 유리하다.
복잡한 계산은 실시간으로 처리 하지말고, 미리 계산 해서 저장한다음 주기적으로 업데이트하는 것이 효율적이다.
Last updated