.gif&blockId=11b00c82-b138-8050-a58b-dcbceda2ef8f)
요약정리
1.
데이터를 변형하는 연산은 가급적 피하고, 원본 데이터를 직접 비교하는 조건을 사용하자. 이는 인덱스 활용도를 높여 쿼리 속도를 향상시킨다.
2.
OR 연산자 대신 UNION을 활용하면 각 조건을 독립적으로 최적화하고 인덱스를 효과적으로 사용할 수 있다.
3.
불필요한 Row와 Column을 제외하고 꼭 필요한 데이터만 조회하자. 이는 데이터 처리량을 최소화하여 쿼리 성능을 높힌다.
4.
ROW_NUMBER(), RANK(), LEAD(), LAG() 등의 분석 함수를 적극 활용하면 복잡한 데이터 분석을 유연하고 효율적으로 수행할 수 있다.
5.
LIKE 연산자와 와일드카드(%)를 사용할 때는 문자열 끝에 와일드카드를 두는 것이 인덱스 활용에 유리하다.
6.
복잡한 계산은 실시간으로 처리하기보다는 미리 계산해서 저장해두고 주기적으로 업데이트하는 것이 효율적이다.
Sargable operators
•
Sargable operators: =, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL
•
Sargable operators that rarely improve performance: <>, IN, OR, NOT IN, NOT LIKE
1. 좌변 연산 금지 = 원본데이터 조작 금지
문제: 좌변 연산
•
sales 테이블에서 date 컬럼의 연도가 2021년인 모든 데이터를 찾아달라는 요청
SELECT *
FROM sales
WHERE YEAR(date) = 2021; # 2021-01-01 00:00:00
SQL
복사
YEAR(date) : date 컬럼에서 연도 부분만을 추출하는 함수
◦
위 쿼리 같은 데이터 원본을 변형하여, 내가 찾고자 하는 범위와 비교하는 연산은 데이터베이스가 인덱스를 제대로 활용할 수 없게 만든다.
= 책의 페이지 번호를 함수 연산하여 계산하며 찾는 것과 같은 방식
•
인덱스는 원본 데이터를 그대로 가지고 만들어진다.
◦
예를 들어 date 컬럼에 대한 인덱스는 '2023-06-01', '2023-06-02'와 같은 날짜 값 자체를 가지고 구성
•
그래서 앞서 본 것처럼 YEAR(date) = 2021과 같이 데이터를 변형하는 연산을 수행하면, 이 인덱스를 제대로 활용할 수 없게 된다.
•
한 줄 한 줄 일일이 YEAR(date) 연산을 수행하고, 그 결과가 2021인지 확인하는 것과 동일한 연산
⇒ 데이터가 많을수록 이는 엄청난 작업량이 될 수밖에 없다.
•
이렇게 되면 데이터베이스는 인덱스를 통해 빠른 검색을 할 수 없고, 결국 모든 데이터를 처음부터 끝까지 훑어봐야만 한다.
대안: 우변에서의 데이터 필터링
•
데이터를 '변형'하지 않고, 원본 형태를 유지하면서 필요한 데이터를 찾는다.
SELECT * FROM sales
WHERE date >= '2021-01-01'
AND date <= '2021-12-31';
SQL
복사
◦
위 쿼리는 sales 테이블에서 date 컬럼의 값이 2021년 1월 1일부터 2021년 12월 31일 사이에 있는 모든 데이터를 찾아준다.
◦
주목할 점은 date 컬럼을 그대로 사용하고 있는 것.
◦
별도의 연산을 수행하지 않고, 날짜 값 자체를 직접 비교하고 있다.
•
다음과 같이 하면 데이터베이스는 date 컬럼에 대한 인덱스를 효과적으로 활용할 수 있다.
•
인덱스에 저장된 날짜 값과 쿼리에서 지정한 기간을 비교하면서, 필요한 데이터를 빠르게 찾을 수 있게 되는 것.
•
이처럼 원본 데이터를 직접 비교하는 조건을 사용하는 것이 효과적이다.
2. OR 대신 UNION을 사용할 것
문제 : OR 연산자 사용
•
'Marketing' 부서나 'IT' 부서에 속한 직원들을 찾아달라는 요청
SELECT * FROM employees
WHERE department = 'Marketing' OR department = 'IT';
SQL
복사
◦
위 쿼리와 같이 OR 연산자를 사용하면 데이터베이스는 한 번의 스캔으로 모든 조건을 확인해야 한다.
•
해당 과정에서 불필요한 데이터까지 대량으로 검색하게 되고, 특히 인덱스를 제대로 활용하지 못하는 경우가 다수이다.
•
인덱스는 단일 값에 대한 빠른 검색을 위해 최적화되어 있는데, OR은 여러 값을 동시에 찾아야 하기에 비효율적이게 된다.
대안: UNION 연산자 사용
UNION : 각 조건에 대한 쿼리를 별도로 실행한 뒤, 그 결과를 합쳐주는 연산자
•
수정된 쿼리
SELECT * FROM employees
WHERE department = 'Marketing'
UNION | UNION ALL(중복이 없다는 것이 확실할 때)
SELECT * FROM employees
WHERE department = 'IT';
SQL
복사
•
이렇게 하면 데이터베이스는 각 쿼리를 독립적으로 최적화하고 실행할 수 있다.
•
department = 'Marketing'와 department = 'IT'는 각각 인덱스를 통해 빠르게 처리될 수 있다.
•
그리고 나서 UNION이 두 결과를 합친다.
◦
이 과정에서 중복된 결과는 자동으로 제거된다.
◦
만약 중복이 없다는 게 확실하다면 UNION ALL을 사용해 중복 제거 단계를 건너뛰고 성능을 더 높일 수 있다.
•
각 조건마다 별도의 쿼리를 수행하고 인덱스를 활용하도록 함으로써, 데이터베이스의 부하를 줄이고 전체적인 조회 성능을 크게 개선할 수 있다.
3. 필요한 Row와 Column만 선택하여 성능 최적화하기
•
데이터베이스에서 데이터를 조회할 때, 불필요한 정보까지 모두 가져오는 것은 성능 저하의 주범 중 하나
•
꼭 필요한 데이터만 골라내는 것이 성능 최적화(Optimization)의 핵심
특정 조건을 만족하는 Row만 선택하기
•
ex) 'Marketing' 부서에 속하면서 매출액이 100,000 이상인 직원들의 이름과 이메일 주소를 조회하는 요청
SELECT
name
, email
FROM employees
WHERE 1=1
AND department = 'Marketing'
AND sales >= 100000;
SQL
복사
◦
해당 쿼리는 employees 테이블에서 부서가 'Marketing'이고 매출액이 100,000 이상인 직원들의 이름(name)과 이메일(email) 정보만 선택
◦
불필요한 Row를 걸러내고 필요한 Column만 선택함으로써, 데이터베이스가 처리해야 할 데이터의 양을 최소화
•
이는 쿼리의 응답 속도를 높이고 시스템 부하를 줄이는 데 큰 도움
서브쿼리를 활용하여 필요한 데이터만 추출하기
•
각 부서별로 최고 매출액을 달성한 직원의 정보를 조회하는 요청
SELECT
e.name
, e.department
, e.sales
FROM employees e
JOIN (
SELECT
department
, MAX(sales) AS max_sales
FROM employees
GROUP BY department
) d
ON e.department = d.department
AND e.sales = d.max_sales;
SQL
복사
◦
해당 코드는 서브쿼리를 활용하여 부서별 최대 매출액을 계산한 후, 이 결과와 employees 테이블을 조인하여 필요한 정보만 추출
◦
서브쿼리에서 불필요한 Column을 제외하고 오직 department와 max_sales만 선택함으로써, 중간 결과의 크기를 최소화
◦
이를 바탕으로 최종적으로 필요한 직원의 이름, 부서, 매출액 정보만 조회
•
불필요한 데이터 처리를 줄임으로써 응답 시간을 단축하고, 시스템 자원을 효율적으로 활용 가능
4. 분석 함수를 활용하여 쿼리 성능 높이기
분석 함수(Analytic Functions): SQL 쿼리의 성능을 한 단계 높이는 강력한 도구, 단순히 데이터를 처리하는 것을 넘어, 데이터 분석과 쿼리 최적화에 있어 핵심적인 역할을 함.
•
Row별로 세부적인 계산을 가능하게 한다.
•
전체 데이터에 걸쳐 다양한 통계와 계산을 유연하게 수행할 수 있게 도와준다.
•
기본 분석함수 사용법
SELECT ANALYTIC_FUNCTION(args)
OVER (
[PARTITION BY 컬럼 LIST]
[ORDER BY 컬럼LIST]
[WINDOWING절(ROWS | RANGE BETWEEN)]
)
FROM 테이블명;
SQL
복사
◦
ANALYTIC FUNCTION: 분석함수명(입력인자)
◦
OVER: 분석함수임을 나타내는 키워드
◦
PARTITION BY: 계산 대상 그룹 지정 >>GROUP BY와 비슷한 역할. 이 구문이 없으면 전체 데이터에 대해 계산이 적용됨.
◦
ORDER BY: 대상 그룹에 대한 정렬 수행
◦
WINDOWING 절 : 분석함수의 계산 대상 범위 지정
(ORDER BY절에 종속적. 기본 생략 구문 : 정렬된 결과의 처음~현재행까지)
쿼리의 효율성 증대
•
집계 함수와 달리 사전에 데이터를 그룹화할 필요가 없다.
→ 불필요한 자원 소모를 줄이고 쿼리 성능을 높힌다.
•
데이터 분석 과정에서 발생할 수 있는 중간 결과물의 저장과 재처리를 최소화할 수 있다.
→ 쿼리 실행 시간을 크게 단축
데이터 순서를 매기는 순위 결정 함수
•
ROW_NUMBER(), RANK(), DENSE_RANK()
•
부서별로 급여가 높은 직원 순으로 순위를 매기고 싶은 경우
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
SQL
복사
◦
RANK()와 DENSE_RANK() 함수도 비슷한 방식으로 동작하지만, 동일한 값에 대해 동일 순위를 부여한다는 점이 다르다.
◦
DENSE_RANK()의 경우 순위 간격을 항상 1로 유지하는 특징이 있다.
데이터 변화를 추적하는 분석 함수
•
LEAD(), LAG()
•
현재 Row와 관련하여 이전 또는 다음 Row의 데이터를 참조할 수 있게 해주는 함수
•
각 직원의 연봉 변화율 계산 방법
SELECT
name,
salary,
LAG(salary) OVER (PARTITION BY department ORDER BY hire_date) AS prev_salary,
salary - LAG(salary) OVER (PARTITION BY department ORDER BY hire_date) AS salary_increase
FROM employees;
SQL
복사
◦
각 부서 내에서 입사일자 순으로 직원을 정렬한 후, 이전 직원의 연봉과 현재 직원의 연봉 차이를 계산하여 연봉 인상액을 구하는 쿼리
•
특히 시계열 데이터나 연속적인 데이터 집합을 다룰 때, 이전 데이터 포인트와의 비교가 필요한 분석에 아주 유용하게 활용
분석 함수로 데이터 필터링 최적화
•
각 부서별로 급여가 높은 상위 3명의 직원 정보만 추출하고 싶다면 WITH와 ROW_NUMBER() 함수를 이용
WITH ranked_employees AS (
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
)
SELECT *
FROM ranked_employees
WHERE rank <= 3;
SQL
복사
5. 와일드카드(%)는 끝에 작성하는 것이 더 좋다
•
SQL에서 LIKE 연산자와 함께 와일드카드(%)를 사용하면 텍스트 데이터를 유연하게 검색할 수 있어 정말 유용
•
그런데 이 와일드카드의 위치에 따라 쿼리 성능이 크게 달라질 수 있다
•
LIKE 연산자와 와일드카드(%)를 사용할 때는 가급적 문자열 끝에 와일드카드를 두는 게 좋다.
문제 : 문자열 앞에 와일드카드를 쓰는 경우
SELECT * FROM users WHERE name LIKE '%John';
SQL
복사
•
해당 쿼리는 'John'으로 끝나는 모든 이름을 찾아내려고 한다.
•
문제
◦
와일드카드가 앞에 있으면, 데이터베이스가 'John'으로 끝나는 모든 가능한 문자열 조합을 일일이 검색
◦
인덱스가 있어도 이걸 제대로 활용할 수가 없다.
◦
결과적으로 데이터베이스는 엄청난 자원을 소모하고, 쿼리 속도도 느려질 수밖에 없다.
해결 : 문자열 뒤에 와일드카드를 쓰는 경우
SELECT * FROM users WHERE name LIKE 'John%';
SQL
복사
•
해당 쿼리는 'John'으로 시작하는 모든 이름을 찾고 있다.
•
데이터베이스가 인덱스를 활용해서 검색 범위를 효과적으로 좁힐 수 있다.
•
데이터베이스는 우선 인덱스에서 'John'으로 시작하는 첫 번째 항목을 빠르게 찾아낸다.
•
그리고 'John'으로 시작하지 않는 첫 번째 항목이 나올 때까지만 검색한다.
6. 계산값을 미리 저장해두었다가, 나중에 조회할 것
•
데이터베이스에서 복잡한 계산을 실시간으로 처리하는 것은 쿼리 성능에 큰 부담이 될 수 있다.
•
이런 상황에서는 자주 사용 되는 계산값을 미리 저장해두었다가, 필요할 때 바로 꺼내 쓰는 것이 효과적인 최적화 방법이 될 수 있다.
문제 : 실시간 계산의 비효율성
•
이커머스 사이트에서 각 상품의 평균 구매 금액, 총 매출, 구매자 수, 재구매율 등 다양한 통계치를 실시간으로 계산한다고 가정
SELECT
p.product_id
, AVG(od.quantity * od.unit_price) AS avg_order_amount
, SUM(od.quantity * od.unit_price) AS total_sales
, COUNT(DISTINCT o.customer_id) AS num_purchasers
, COUNT(DISTINCT CASE WHEN o.customer_id IN (
SELECT customer_id
FROM orders
WHERE product_id = p.product_id
GROUP BY customer_id
HAVING COUNT(*) > 1
) THEN o.customer_id END) * 1.0 / COUNT(DISTINCT o.customer_id) AS repurchase_rate
FROM
products p
JOIN order_details od
ON p.product_id = od.product_id
JOIN orders o
ON od.order_id = o.order_id
GROUP BY
p.product_id;
SQL
복사
◦
해당 쿼리는 products, order_details, orders 테이블을 조인하여 각 상품(product_id)별로 평균 구매 금액(avg_order_amount), 총 매출(total_sales), 구매자 수(num_purchasers), 재구매율(repurchase_rate)을 계산하는 쿼리이다.
◦
문제
▪
이 쿼리가 실행될 때마다 방대한 양의 주문 및 고객 데이터를 모두 읽어서 복잡한 계산을 수행해야 한다.
▪
특히 재구매율 계산을 위해 서브쿼리까지 사용되고 있어 쿼리 속도는 더욱 느려질 수밖에 없다.
▪
게다가 이런 통계치가 자주 사용된다면, 같은 복잡한 계산을 반복해서 수행하게 되니 엄청난 자원 낭비가 발생
해결 : 계산값을 저장하고 활용하기
•
계산 결과를 별도의 테이블에 저장해둘 수 있다.
CREATE TABLE product_stats AS
SELECT
p.product_id,
AVG(od.quantity * od.unit_price) AS avg_order_amount,
SUM(od.quantity * od.unit_price) AS total_sales,
COUNT(DISTINCT o.customer_id) AS num_purchasers,
COUNT(DISTINCT CASE WHEN o.customer_id IN (
SELECT customer_id
FROM orders
WHERE product_id = p.product_id
GROUP BY customer_id
HAVING COUNT(*) > 1
) THEN o.customer_id END) * 1.0 / COUNT(DISTINCT o.customer_id) AS repurchase_rate
FROM
products p
JOIN order_details od ON p.product_id = od.product_id
JOIN orders o ON od.order_id = o.order_id
GROUP BY
p.product_id;
SQL
복사
◦
해당 쿼리는 먼저 product_stats라는 새 테이블을 만들고, 앞서 본 복잡한 계산을 수행하여 각 상품의 통계치를 미리 저장하고 있다.
◦
나중에 이런 통계치가 필요할 때, 이 테이블에서 바로 값을 가져올 수 있다.
개선 : 주기적인 계산 결과 업데이트
•
배치 작업을 수행하자.
UPDATE product_stats ps
SET
avg_order_amount = (
SELECT AVG(od.quantity * od.unit_price)
FROM order_details od
WHERE od.product_id = ps.product_id
),
total_sales = (
SELECT SUM(od.quantity * od.unit_price)
FROM order_details od
WHERE od.product_id = ps.product_id
),
num_purchasers = (
SELECT COUNT(DISTINCT o.customer_id)
FROM order_details od
JOIN orders o ON od.order_id = o.order_id
WHERE od.product_id = ps.product_id
),
repurchase_rate = (
SELECT
COUNT(DISTINCT CASE WHEN o.customer_id IN (
SELECT customer_id
FROM orders
WHERE product_id = ps.product_id
GROUP BY customer_id
HAVING COUNT(*) > 1
) THEN o.customer_id END) * 1.0 / COUNT(DISTINCT o.customer_id)
FROM order_details od
JOIN orders o ON od.order_id = o.order_id
WHERE od.product_id = ps.product_id
);
SQL
복사
◦
해당 쿼리는 product_stats 테이블의 통계치들을 최신 주문 내역을 바탕으로 업데이트
◦
각 통계치별로 서브쿼리를 사용하여 최신 값을 계산하고, 그 결과로 product_stats 테이블의 값들을 갱신
◦
계산 결과를 저장하고 주기적으로 업데이트하면, 복잡한 실시간 쿼리의 부담을 크게 줄일 수 있다.
◦
자주 사용되는 통계치, 집계값 등은 미리 계산해서 저장해 두는 것이 성능 최적화에 큰 도움이 된다.
