[DB] SQL 성능 최적화 (feat. SQL Optimization)

📍 성능 최적화를 위한 조인 전략 

1. 조인 순서 최적화 

• 작은 테이블을 먼저 조인하여 데이터 양을 줄여 불필요한 연산 방지
• 조인 조건이 명확하도록 인덱스 적용 (customer_id 같은 PK, FK 활용) 

* MariaDB에서는 `STRAIGHT_JOIN`을 통해 지정한 순서대로 조인 지정 가능

조인 힌트 사용 
: SQL 실행 시 옵티마이저가 선택하는 조인 방식과 순서를 강제로 지정할 수 있는 수단 
- Nested Loop Join : 작은 테이블을 기준으로 큰 테이블에 반복 접근 (루프 방식) 
- Sort Merge Join : 양쪽 테이블을 정렬해서 병합 
- Hash Join : 한쪽 테이블 해시 테이블 생성 후 다른 쪽과 조인
* SELECT 바로 뒤에 사용  

2. 인덱스 활용 

• ON 절에 사용되는 컬럼에 인덱스 설정 시 성능 향상
• 없다면 다음과 같이 인덱스 신규 설정 = `CREATE INDEX idx_customer ON Customers(customer_id)`

인덱스가 있으면 원하는 데이터를 빠르게 찾아갈 수 있다. 만약 인덱스가 없으면 모든 데이터를 다 읽어봐야 함!

 

따라서 2가지를 조합했을 때 SQL 실행이 빠른 순서는

1. 조인 순서에 맞게 인덱스까지 잘 활용한 경우
2. 조인 순서만 튜닝한 경우
3. 조인 순서가 잘못됐지만 인덱스를 쓴 경우 
4. 조인 순서도 틀리고 인덱스도 안 쓴 경우

위와 같다!

 

테이블 명칭
- 드라이빙 테이블, 외부 테이블 : 조인 시 먼저 액세스 (읽기 시작)돼서 기준이 되는 테이블 
- 드리븐 테이블, 내부 테이블 : 조인 시 나중에 액세스 되는 테이블 

 

예제로 이해하기

아래와 같은 코드가 있다고 할 때, 

EXPLAIN -- 이 쿼리를 실제 실행하지 않고, 내부적으로 어떻게 실생되는지를 보여주는 명령어 
SELECT EMP.ENAME AS 'EMP 직원명',
       DEPT.DNAME AS 'DEPT 부서명'
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
  AND DEPT.DEPTNO = '20'
  AND EMP.ENAME = 'SCOTT';

 

`FROM EMP, DEPT`라고 썼다고 해서 조인 순서도 무조건 이렇게 되는 건 아니다

왜냐면, 옵티마이저가 효율적이라고 판단하는 순서로 자동 조인해주기 때문에..!

 

하지만 위의 문제의 경우 EMP → DEPT 순서로 강제하면 성능을 더 최적화할 수 있다. 

왜냐면, `EMP.ENAME = 'SCOTT'`가 `DEPT.DEPNO='20'`보다 더 강력한 필터 역할을 하기 때문이다. 

(= 20번이라는 부서를 가진 직원들보다 SCOTT라는 이름을 가진 사람의 수가 더 적을 것이기 때문)

 

앞서 작은 테이블을 먼저 조인하는 것이 좋다 했는데
쨌든 DEPT가 EMP보다 작은 데이터니까 먼저 하는 게 맞지 않나?

라는 의문이 들 수 있다.

하지만 이 이 문장에서의 핵심은 `작은 테이블`이 아니라 `결과 건수가 작은 테이블`이 먼저 와야 한다. 

따라서 scott이라는 강력한 필터를 적용한 emp 테이블이 dept보다 결과 건수가 적기 때문에 먼저 오는 것이 맞는 것이다. 

 

조인 순서를 강제하기 위해 아래와 같이 코드를 쓸 수 있다. 

EXPLAIN
SELECT STRAIGHT_JOIN EMP.ENAME AS 'EMP 직원명', DEPT.DNAME AS 'DEPT 부서명'
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
  AND DEPT.DEPTNO = '20'
  AND EMP.ENAME = 'SCOTT';

 

원래 STRAIGHT_JOIN은 아래와 같이 `A STRAIGHT_JOIN B` 이런 식으로 쓰는 것이 기본 문법이지만, 

위의 코드처럼 SELECT 문에 사용하면 옵티마이저가 FROM 절 순서를 그대로 조인하도록 강제할 수 있다. 

-- 기본 문법
SELECT 컬럼들
FROM 테이블1
STRAIGHT_JOIN 테이블2
ON 테이블1.컬럼 = 테이블2.컬럼
WHERE 조건

 

 

📍 조인을 통한 연결 방법

Nested Loop Join

"작은 외부 → 인덱스 있는 내부" 구조에서 가장 효율적으로 소규모 조인에 적합 (인덱스 없으면 성능 저하)

outer 테이블의 각 행에 대해 inner 테이블을 반복해서 검색하는 조인 방식

 

like 이중 반복문

for each row in OuterTable:
    for each row in InnerTable:
        if 조인 조건 만족:
            결과 반환

특징

• 순차적 처리 
• 부분 범위 처리 가능 (조건이 있는 경우 일부 범위만 조회 가능)
• 처리량은 Outer table 크기에 따라 좌우 
• 인덱스 반복 구조이므로 인덱스가 없으면 성능이 급격히 떨어짐
• 일반적으로 소규모 조인에 적합 

 

Sort Merge Join

인덱스 없이 조인이 가능하며, 양쪽 테이블 모두 클 때. 대용량 데이터 처리 시 매우 유리 

조인 키를 기준으로 양쪽 테이블을 정렬한 다음, 병합하는 방식으로 조인하는 구조. 
(양쪽 데이터를 정렬 → 나란히 비교 → 일치하면 매칭하는 방식) 

• 동시 처리 (두 테이블 동시에 전체 스캔). 
• 전체 범위 처리
• 스캔 액세스 위주. 
• 조인 키 컬럼에 인덱스 없어도 됨
• 일반적으로 대용량에 유리. 

* 하지만 정렬 비용이 크므로, 사전에 정렬되어 있거나 인덱스가 잘 잡혀 있으면 좋다 

 

Hash Join

"작은 테이블 + 큰 테이블" 구조의 대용량 데이터를 빠르게 처리하며, 
인덱스와 정렬이 필요 없음 

두 테이블에서 조인 키를 기준으로 해시 (Hash) 값을 만들어,
이 해시 값을 이용해 매칭되는 행을 빠르게 찾아 조인하는 방식 

• 각 테이블을 1번만 읽음 
• 인덱스 정렬 불필요 

 

 

📍 조인이외의 연결 방법 

1. 연산자

• UNION (합집합)
• UNION ALL (합집합 중복) 
• INTERSECT (교집합)
• MINUS (차집합) 

 

2. 서브쿼리 (Sub-query)

: 서브쿼리란, SQL에서 쿼리 안에 또 다른 쿼리르 사용하는 것 

 

서브쿼리 유형 

• 스칼라 서브쿼리 (scalar subquery) 
  • `select column..., (select)` 
  • 하나의 컬럼처럼 사용, 간결한 데이터 연결 효과 
• 인라인 뷰 (Inline View) 
  • `from (select ...)`
  • 하나의 테이블처럼 사용, 모델링 대상 아닌 임시 테이블 등 
• 일반 서브쿼리
  • `where column = (select ...)`
  • 하나의 변수 (상수)처럼 사용, 서브쿼리의 결과에 따라 조건절 변경 

 

스칼라 서브쿼리 (Scalar SubQuery) 

`SELECT` 절에 쓰이며, 한 행 한 컬럼(단일값)만 반환 (변수처럼 사용), 
반복 실행되기 때문에 성능 이슈가 발생할 가능성이 높으므로,
JOIN, IN-LINE VIEW로 바꾸는 튜닝 필요 

• 작동 방식
  • 1. 메인 쿼리가 먼저 실행
  • 2. 각 행마다 서브쿼리 실행 → 반복 실행
  • 3. 반환된 값을 메인 행과 연결 
• 특징 및 주의사항
  • 1개 컬럼만 반환 : SELECT절 또는 조건절에서 단일 값처럼 사용 
  • 값이 같으면 캐싱됨 : 서브쿼리 결과가 동일하면 다시 액세스 안함 
  • 다르면 성능 영향 큼 : 메인 집합의 row 수만큼 반복 실행됨 
  • 응답이 느려질 수 있음 : 메인 집합이 크면 성능 급락 
• 성능 튜닝 팁
  • `LEFT JOIN`, 또는 `INLINE VIEW (인라인 뷰)`로 바꾸면 훨씬 빨라짐
  • 서브 쿼리가 상수 값이 아니라 매번 달라지는 값을 참조할 경우에는 반복 실행이 발생하므로 주의 필요 

그럼 항상 스칼라 서브쿼리보다 JOIN이나 VIEW 방식이 빠를까?

항상 그렇진 않다!
인덱스가 없거나 소규모라면, 스칼러 서브쿼리가 오히려 더 빠를 수 있다

따라서 실제 데이터 구조, 인덱스, 집합 크기, 캐시 가능성 등을 고려한 튜닝이 필요하다

 

그렇다면 왜 키의 유무에 따라서 성능이 달라지는 것인지?
: SQL 성능 최적화에서 키(기본키 PK, 외래키 FK)의 역할 

키가 있다는 것은 `데이터의 관계가 명확`하다는 것을 의미한다 

키 유형	의미
기본키(PK)	이 테이블의 특정 컬럼은 절대 중복이 없다 (고유성 보장)
외래키(FK)	다른 테이블의 기본키를 참조한다는 것 (관계 명시)

 

위처럼 기본키와 외래키가 잘 정리되어 있을 때, 
데이터베이스는 키 정보를 바탕으로 `예측`과 `최적화`를 수행할 수 있다!

 

• 키가 있을 경우
  • 옵티마이저는 조인 `EMP.DEPTNO → DEPT.DEPTNO` 식으로 연결될 때 무조건 1:1 매칭임을 알고 있는 것
• 키가 없을 경우 
  • 조인이 1:1인지, 1:N인지, N:N인지 모름 
  • 따라서, 정확한 실행 계획을 세우기 어려워짐 

PK/FK는 단순히 제약 조건이 아니라, 
옵티마이저가 성능 최적화를 할 수 있도록 알려주는 힌트이자 약속!

 

3. 함수 

• 함수는 특정 연산이나 변환을 수행하는 SQL 내장 기능 
• 데이터 값을 가공하거나 집계하는 데 사용

* 함수 예시 

delimiter $$

create function `fn_ename`(v_empno int(4))  
returns varchar(10) charset utf8mb4 COLLATE utf8mb4_general_ci
begin
    declare v_ename varchar(10);
    set v_ename = (select ENAME from EMP where EMPNO = v_empno);
    return v_ename;
end $$

delimiter ;

 

트리거와 프로시저 

관계형 데이터베이스의 저장형 프로그램 

	트리거 (Trigger)	프로시저 (Procedure)
정의	특정 이벤트 발생 시 자동 실행	사용자가 명시적으로 호출
호출 시점	INSERT, UPDATE, DELETE 등 데이터 변경 시 자동	CALL 프로시저명(...) 명령으로 실행
용도	무조건성 자동 처리 (감시/기록/검사 등)	명령어 묶음 실행 (복잡 로직 처리)
호출 주체	DBMS 내부 트랜잭션 흐름	사용자 또는 애플리케이션
대표 기능	데이터 무결성 유지, 자동 로그, 감사, 알림	반복 작업, 배치, 조건 분기, 동적 로직

 

그럼 함수와 프로시저의 차이?

• 함수는 결과를 반환함
• 프로시저는 작업 지시서처럼 명령어 묶음을 실행 (결과를 반환한다기보다는 일을 처리한다!)

 

인덱스와 PK, FK의 차이가 도대체 뭐야~!?

3개 모두 데이터베이스에서 성능과 무결성을 유지하는 핵심 요소

• PK = 제약 조건 + 인덱스 (자동)
• FK = 제약 조건 (참조 관계), 성능 위해 인덱스 따로 생성 필요
• INDEX = 제약 없음, 순수 성능 최적화 도구

 

그럼 ID와 Index와 차이가 뭐야~!?

• ID는 사용자가 실제 정의한 칼럼으로, 레코드 식별용 (PK 후보)
• Index는 사용자가 직접 볼 수 있는 컬럼이 아니며, DB 내부 구조를 의미하는 것 

 

 

📍 SQL 튜닝 (SQL Optimization)

SQL 성증을 미치는 영향 요소 (SQL 최적화 대상)

• 데이터 크기 
  • 데이터가 많을수록 조회 속도가 느려짐
• 조인의 효율성
  • 불필요한 JOIN을 줄이고, 적절함 INDEX를 사용 
• 인덱스 (Index) 활용
  • 인덱스를 잘 활용하면 검색 속도를 크게 향상 
• 필요한 데이터만 조회
  • SELECT * 대신 필요한 칼럼만 조회 
• 서브쿼리 vs 조인 
  • 서브쿼리는 느릴 수 있으므로 조인으로 대체하는 것을 권장 

 

SQL 튜닝 체크리스트

1. `SELECT *` 대신 필요한 컬럼만 조회

2. 서브쿼리 대신 `JOIN` 사용

3. OR 대신 `UNION` 활용 

4. `인덱스`를 활용해 검색 속도 최적화 

5. COUNT(*) 대신 `COUNT(컬럼)` 사용