Research/Spring

[친절한 SQL 튜닝] 1장. SQL 처리 과정과 I/O

JMcunst 2025. 5. 14. 15:17
728x90
반응형

1. SQL 파싱과 최적화

구조적, 집합적, 선언적 질의 언어

내용

SQL은 일반적인 프로그래밍 언어와 달리, 다음 세 가지 특성을 동시에 지닌 고수준 질의 언어입니다.

  • 구조적 (Structured)

    정해진 문법과 형식에 따라 명확하게 작성되어야 하며, 테이블(관계형 데이터)을 중심으로 동작합니다. 구문은 SELECT, INSERT, UPDATE, DELETE와 같은 키워드로 시작하며, 각 절(clause)이 구조적으로 구성됩니다.

  • 집합적 (Set-based)

    SQL은 데이터 하나하나가 아닌 데이터의 집합을 대상으로 연산을 수행합니다. 따라서 반복문 없이도 수천 건의 데이터를 동시에 처리할 수 있으며, 이는 데이터베이스 엔진의 최적화와 병렬 처리의 기반이 됩니다.

  • 선언적 (Declarative)

    SQL은 무엇을 할 것인가를 명시하는 언어입니다. 어떻게 할 것인가는 옵티마이저가 결정하며, 사용자는 알고리즘이나 루프에 대해 고민할 필요 없이 결과를 얻고자 하는 목적만 정의하면 됩니다.

💡 선언형 언어의 핵심은 목적을 명확히 표현하는 것입니다. 구현 방법은 옵티마이저에게 맡기면 됩니다.

실제 사례 및 사용

예를 들어, 최근 한 달 간 주문 수가 많은 고객을 구하고 싶다.

선언적 SQL 방식은 아래와 같이 쓸 수 있다.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY customer_id
HAVING COUNT(*) > 10
ORDER BY order_count DESC;

쿼리는 아래와 같은 방식으로 선언되어 있습니다.

  • 무엇을 구할지 (고객별 주문 수)
  • 필터링 조건 (최근 30일)
  • 출력 조건 (주문 수가 10개 초과)

명령형 사고 방식 (개발자 입장)으로 비교해보자면,

# 명령형(Python)의 방식 예시
recent_orders = [o for o in orders if o.date >= 기준일]
by_customer = group_by_customer(recent_orders)
for c in by_customer:
    if len(c.orders) > 10:
        print(c.id, len(c.orders))

→ 코드에서는 반복, 조건 분기, 정렬 등의 로직이 명시적으로 존재합니다. 반면 SQL은 이를 모두 선언적으로 처리하게 됩니다.

관찰 포인트:

  • 선언적 SQL 작성이 능숙하지 않으면 쿼리가 과도하게 복잡하거나, 불필요한 서브쿼리/조인이 발생해 성능 저하로 이어질 수 있습니다.
  • 집합적 사고가 익숙해지면 하나의 SQL로 복잡한 리포트를 뽑아낼 수 있게 됩니다.
  • BI 도구, ETL, 분석 플랫폼에서는 대부분 집합 기반으로 설계되어 있으므로, 이러한 SQL 사고방식은 필수적입니다.

정리 및 마무리

SQL은 컴퓨터 언어지만 인간의 사고방식과도 밀접합니다. 무엇을 원하는가를 명확히 정의하고, 방법은 엔진에게 위임하는 것이 선언형 언어의 철학입니다.

프로그래머에서 데이터 엔지니어로 넘어가는 첫걸음은, 단일 행 중심 사고에서 집합 기반 사고로의 전환입니다. 이 전환이 제대로 이뤄져야만 SQL의 진정한 강점을 누릴 수 있습니다.

SQL 최적화

내용

SQL이 실행되기 전, 데이터베이스는 다음과 같은 전처리 과정을 거칩니다.

  • 구문 분석 (Syntax Check)

    SQL 문장의 문법 오류를 확인합니다. 예: 괄호 누락, 잘못된 예약어 사용 등

  • 의미 분석 (Semantic Check)

    테이블, 컬럼, 함수, 권한 등이 존재하는지 확인합니다. 존재하지 않는 객체를 참조하거나, 권한이 없는 경우 오류 발생

  • 최적화 (Optimization)

    다양한 실행 경로 중에서 가장 효율적인 실행 계획(Execution Plan)을 선택합니다. 이를 위해 DB는 데이터 통계, 인덱스 정보, 조인 조건 등을 바탕으로 예상 비용(Cost)을 계산합니다.

  • 로우 소스 분석 (Row Source Generation)

    선택된 실행 계획에 따라 실제로 데이터를 어떻게 읽고 반환할지 정의하는 구조를 생성합니다. 이 과정에서 각 연산자(예: TABLE ACCESS, NESTED LOOPS 등)는 내부적으로 Row Source Tree를 구성하며, 이는 나중에 DBMS_XPLAN.DISPLAY_CURSOR 등을 통해 확인할 수 있습니다.

💡 로우 소스는 옵티마이저의 실행 계획을 실제 수행 가능한 트리 형태의 절차로 바꾸는 단계입니다. 이 구조는 물리적 연산자들의 순서와 연결 관계를 결정합니다.

💡 SQL 성능의 시작은 최적화이며, 최적화의 시작은 데이터를 어떻게 읽을지에 대한 선택입니다.

실제 사례 및 사용

예를 들어, 상품 테이블에서 특정 카테고리의 최신 상품 10개를 조회한다면

SELECT *
FROM products
WHERE category_id = :1
ORDER BY created_at DESC
FETCH FIRST 10 ROWS ONLY;

이 SQL은 단순하지만, 옵티마이저는 다음과 같은 여러 경로를 평가할 수 있습니다.

  • 인덱스를 사용한 정렬 우선 접근: category_id, created_at DESC 인덱스가 있다면, 인덱스만으로 정렬과 필터링을 동시에 수행 가능 (최적)
  • Full Table Scan 후 정렬: 인덱스가 적절하지 않다면 전체 테이블을 스캔하고 정렬 수행 (비효율)

주의할 점:

  • 통계 정보가 부정확하면 옵티마이저가 잘못된 실행 계획을 선택할 수 있습니다.
  • WHERE 절 조건, ORDER BY 절, LIMIT/FETCH 절은 옵티마이저의 결정 포인트입니다.
  • 쿼리 리팩토링을 통해 옵티마이저가 더 좋은 계획을 선택할 수 있게 유도해야 합니다.
-- 예: 함수 사용은 인덱스를 무력화할 수 있음
-- 잘못된 예
WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = '2025-05-14'

-- 리팩토링 예
WHERE order_date BETWEEN TO_DATE('2025-05-14', 'YYYY-MM-DD')
                    AND TO_DATE('2025-05-14', 'YYYY-MM-DD') + 1

정리 및 마무리

SQL 최적화는 수학적 문제라기보다 전략적 판단의 영역입니다. 옵티마이저는 정직하지만, 가끔 어리석습니다. 우리가 할 일은 옵티마이저가 현명한 선택을 하도록 충분한 정보와 명확한 구조를 제공하는 것입니다.

즉, 성능 좋은 SQL을 만드는 핵심은 단순히 짧고 빠른 코드가 아니라, DB가 잘 이해할 수 있도록 작성하는 문장력입니다.

SQL 옵티마이저

내용

옵티마이저(Optimizer)는 SQL을 실행하는 데 있어 가장 효율적인 실행 계획을 선택하는 데이터베이스 내부의 결정 엔진입니다. 사용자는 단순히 "무엇을" 원한다고만 선언할 뿐이며, "어떻게" 얻을지는 전적으로 옵티마이저의 판단에 맡겨집니다.

옵티마이저는 SQL이 제출되었을 때 다음과 같은 요소들을 기반으로 여러 실행 경로를 평가합니다.

  • 데이터 통계 (Statistics)

    테이블의 총 건수, NULL 비율, 컬럼 값 분포(히스토그램), 인덱스 선택도 등

  • 객체 정보

    사용 가능한 인덱스, 파티션, 뷰 등

  • 조건절과 조인 방식

    WHERE 절 조건의 선택도(Selectivity), JOIN 조건의 형태 (등치 조인 / 비등치 조인)

  • 쿼리 힌트 / 패러미터 설정값

    옵티마이저의 기본 판단을 바꾸는 힌트 또는 세션 파라미터들 (예: optimizer_mode, parallel_degree_policy)

옵티마이저는 크게 두 가지 유형으로 나뉩니다.

  1. 규칙 기반 옵티마이저 (RBO, Rule-Based Optimizer)

    사전에 정의된 규칙에 따라 고정된 방식으로 실행 계획을 선택합니다. (예: 인덱스가 있으면 항상 사용)

  2. 비용 기반 옵티마이저 (CBO, Cost-Based Optimizer)

    실행 비용(Cost)을 계산하여 가장 비용이 낮은 실행 계획을 선택합니다. 현대의 DBMS는 대부분 CBO를 기본으로 사용합니다.

💡 Oracle은 10g 이후 CBO만을 지원합니다. PostgreSQL, MySQL도 모두 비용 기반 전략을 채택하고 있습니다.

실제 사례 및 사용

  1. 옵티마이저가 잘 선택한 실행 계획

     SELECT * FROM employees WHERE emp_no = :1;
    • emp_no에 유니크 인덱스가 있다면, 옵티마이저는 자동으로 INDEX UNIQUE SCAN을 선택합니다.
    • 이는 단 1건만 반환되므로 가장 효율적인 접근 방식입니다.

  2. 옵티마이저가 잘못 판단할 수 있는 경우

     SELECT * FROM orders WHERE status = 'READY';
    • status 컬럼에 인덱스가 있어도, 해당 값이 전체 데이터의 80% 이상이라면 인덱스를 통한 접근보다 Full Table Scan이 더 빠를 수 있습니다.
    • 옵티마이저는 이러한 선택도(Selectivity)를 기준으로 판단하며, 이때 통계 정보가 부정확하면 비효율적인 실행 계획이 선택될 수 있습니다.

팁:

  • DBMS_STATS를 이용해 정기적으로 통계 정보를 갱신해야 옵티마이저가 정확한 결정을 할 수 있습니다.
  • 쿼리 결과가 항상 비슷하게 나오는 경우라도, 통계 정보가 바뀌면 옵티마이저의 선택이 달라질 수 있습니다.
  • 실행 계획 분석 도구: Oracle의 EXPLAIN PLAN, AUTOTRACE, DBMS_XPLAN.DISPLAY_CURSOR 등을 통해 옵티마이저의 판단을 추적하고 학습할 수 있습니다.

정리 및 마무리

옵티마이저는 단순한 알고리즘이 아닙니다. 데이터베이스 내부의 ‘판단자’이자 ‘설계자’입니다. 하지만 이 판단자는 통계 정보가 전부입니다. 가짜 정보로 판단하게 하면, 아무리 똑똑한 옵티마이저라도 잘못된 결정을 내릴 수밖에 없습니다.

우리는 SQL을 잘 짜는 것뿐만 아니라, 옵티마이저가 현명하게 판단할 수 있도록 환경을 설계해야 합니다. 그것이 진짜 SQL 튜닝입니다.

실행계획과 비용

내용

실행 계획(Execution Plan)은 SQL을 실행하기 위해 데이터베이스가 선택한 물리적인 접근 경로와 처리 절차를 의미합니다. 사용자가 SQL을 작성하면, 옵티마이저는 가능한 여러 실행 경로 중 가장 효율적인 하나를 선택하고, 그에 해당하는 실행 계획을 구성합니다.

실행 계획은 다음과 같은 정보를 포함합니다.

  • Operation: 수행되는 연산 (예: TABLE ACCESS FULL, INDEX RANGE SCAN, NESTED LOOPS)
  • Object Name: 접근 대상 테이블 또는 인덱스의 이름
  • Rows: 각 단계에서 처리될 것으로 예측되는 행의 개수
  • Cost: 해당 연산을 수행하는 데 소요되는 예상 비용
  • Access/Filter predicates: 특정 조건이 언제 적용되는지

실행 계획은 트리 구조로 표현되며, 아래에서 위로 실행된다고 이해하는 것이 일반적입니다.

💡 실행 계획은 SQL이 실제로 데이터를 어떻게 읽고 처리하는지를 보여주는 청사진입니다.

실제 사례 및 사용

  1. 전체 조회

     SELECT * FROM employees WHERE department_id = 10;

    | Id | Operation | Name | Rows | Cost | |----|-------------------|-----------|------|------| | 0 | SELECT STATEMENT | | 5 | 2 | | 1 | TABLE ACCESS FULL | EMPLOYEES | 5 | 2 |

    • TABLE ACCESS FULL은 전체 테이블을 읽는 방식입니다.
    • 옵티마이저가 인덱스를 사용하지 않은 이유는 department_id가 인덱스에 없거나, 해당 값이 자주 등장하여 선택도가 낮기 때문일 수 있습니다.

  2. 인덱스 사용

     SELECT * FROM employees WHERE employee_id = :1;

    | Id | Operation | Name | Rows | Cost | |----|-------------------|--------------|------|------| | 0 | SELECT STATEMENT | | 1 | 1 | | 1 | INDEX UNIQUE SCAN | EMPLOYEES_PK | 1 | 1 |

활용 포인트:

  • Cost 값은 상대적 지표입니다. 다른 쿼리들과 비교할 때 유용하며, 절대적인 시간/리소스 수치를 의미하지는 않습니다.
  • 실행 계획에서 Filter Predicates가 나중에 적용되는 경우, 불필요한 I/O가 먼저 발생할 수 있으므로 주의해야 합니다.
  • 실행 계획 분석 도구 활용:
    • Oracle: EXPLAIN PLAN, AUTOTRACE, DBMS_XPLAN.DISPLAY_CURSOR
    • PostgreSQL: EXPLAIN (ANALYZE, BUFFERS)
    • MySQL: EXPLAIN, EXPLAIN FORMAT=JSON

정리 및 마무리

실행 계획을 해석할 줄 아는 사람은, 데이터베이스의 언어를 읽을 줄 아는 사람입니다. SQL이 어떻게 동작하는지를 안다는 것은 단순한 성능 튜닝을 넘어, 전체 시스템의 자원 소비와 병목 구조를 이해하는 것으로 이어집니다.

따라서 실행 계획은 "왜 느린가?"를 푸는 열쇠이자, "어떻게 빠르게 할까?"를 설계하는 시작점입니다.

옵티마이저 힌트

내용

옵티마이저 힌트(Optimizer Hint)는 데이터베이스 옵티마이저에게 특정 실행 계획을 유도하거나 강제하기 위해 SQL 내에 작성하는 지시문입니다. 일반적으로 SQL은 옵티마이저가 비용 기반으로 실행 계획을 결정하지만, 특정 조건에서는 옵티마이저의 판단이 비효율적일 수 있으므로, 힌트를 통해 원하는 계획을 유도할 수 있습니다.

힌트는 SQL 문장의 주석 형식으로 삽입되며, 아래와 같은 형태를 가집니다.

SELECT /*+ HINT_NAME */ ...

SELECT /*+ INDEX(employees emp_dept_idx) */ * FROM employees WHERE department_id = 10;

💡 힌트는 옵티마이저의 자동 결정권을 잠시 빌려오는 도구입니다. 튜닝의 강제 장치로 사용되지만, 남용은 피해야 합니다.

사용 시 주의사항:

  • 힌트는 정확한 문법과 대상 객체 이름이 일치하지 않으면 무시됩니다.
  • 일부 DBMS에서는 힌트 지원이 제한적입니다. (Oracle은 강력하게 지원, PostgreSQL은 거의 지원하지 않음)

실제 사례 및 사용

  1. 인덱스를 명시적으로 사용하도록 유도

     SELECT /*+ INDEX(emp emp_dept_idx) */ * 
 FROM emp 
 WHERE department_id = :1;

    옵티마이저가 Full Scan을 선택하려는 상황에서 명시적으로 인덱스를 사용하도록 유도

  2. 조인 순서를 지정

     SELECT /*+ LEADING(dept emp) */ * 
 FROM dept 
 JOIN emp ON dept.dept_id = emp.dept_id;

    옵티마이저가 emp → dept 순으로 조인하려는 상황에서 dept를 먼저 읽게 유도

  3. 병렬 처리 유도

     SELECT /*+ PARALLEL(sales 4) */ * 
 FROM sales;

    sales 테이블을 4개의 병렬 프로세스로 읽도록 설정

  4. 조인 방식 지정

     SELECT /*+ USE_HASH(emp) */ * 
 FROM dept 
 JOIN emp ON dept.dept_id = emp.dept_id;

    emp 테이블을 Hash Join 방식으로 조인하도록 유도

정리 및 마무리

옵티마이저 힌트는 칼이다. 잘 쓰면 정확한 실행 계획을 유도해 성능을 극적으로 향상시키지만, 남용하면 유지보수성과 이식성을 해치고 옵티마이저의 자율 판단력을 훼손할 수 있다.

궁극적으로는 힌트가 아닌 좋은 통계 정보와 잘 설계된 인덱스 구조를 통해 옵티마이저가 스스로 옳은 결정을 내릴 수 있게 하는 환경을 만들어야 한다. 힌트는 그 마지막 수단으로만 사용하라.

2. SQL 공유 및 재사용

소프트 파싱 vs 하드 파싱

내용

SQL 문장은 실행되기 전 파싱(Parsing) 과정을 거치며, 이때 기존에 동일한 SQL이 라이브러리 캐시(Library Cache)에 존재하는지 확인합니다. 이 확인을 기준으로 두 가지 파싱 방식이 구분됩니다.

  1. 하드 파싱(Hard Parsing)

    SQL 문장이 처음 제출되었거나, 문자 하나라도 다른 문장이 들어온 경우 수행됩니다. 이때는 SQL 파싱, 최적화, 실행 계획 생성이 모두 수행되며, CPU 사용량이 크고 라이브러리 캐시 경합이 발생할 수 있습니다.

  2. 소프트 파싱(Soft Parsing)

    이전에 생성된 실행 계획이 그대로 재사용되는 경우입니다. SQL 문장이 완전히 동일하고, 바인드 변수 등을 적절히 사용했다면 옵티마이저는 새로 계획을 만들지 않고 기존 것을 활용합니다. 이 경우 성능에 거의 영향이 없습니다.

💡 실질적인 시스템 성능의 열쇠는 쿼리의 속도보다 "파싱을 얼마나 적게 하느냐"입니다.

파싱 비교 요약

항목 하드 파싱 소프트 파싱
실행 계획 생성 매번 새로 생성 기존 계획 재사용
자원 소모 높음 (CPU, 메모리) 낮음
속도 상대적으로 느림 매우 빠름
SQL 일치 기준 공백, 주석, 대소문자까지 구분 완전히 동일한 형태 필요
주요 원인 바인드 변수 미사용, 리터럴 사용 바인드 변수 사용 등 동일 문장

실제 사례 및 사용

  1. 하드 파싱 발생

     -- 매번 다른 리터럴 값을 사용함
 SELECT * FROM orders WHERE customer_id = 1001;
 SELECT * FROM orders WHERE customer_id = 1002;

    이처럼 리터럴 값이 다른 SQL은 실행 계획을 공유할 수 없어 매번 하드 파싱이 발생합니다. 대규모 트래픽 시스템에서는 이로 인해 라이브러리 캐시가 오염되고, 성능 병목이 발생할 수 있습니다.

  2. 소프트 파싱 유도

     -- 바인드 변수 사용
 SELECT * FROM orders WHERE customer_id = :1;

    SQL 문장이 동일하게 유지되므로 실행 계획 재사용이 가능해지고, 파싱 비용을 절감할 수 있습니다.

팁:

  • Java: PreparedStatement 사용
  • Python: cursor.execute(sql, [param]) 또는 ORM의 query parameterization
  • 대형 트래픽 API에서는 SQL 템플릿 정규화 정책을 도입해야 함
  • Oracle에서는 v$sql 또는 v$sqlarea 뷰를 통해 하드 파싱 비율을 점검할 수 있음

정리 및 마무리

소프트 파싱은 DB가 학습한 것을 기억해서 다시 쓰는 지능적 접근이며, 하드 파싱은 매번 같은 문제를 처음부터 다시 푸는 낭비적인 접근입니다.

쿼리의 성능만 보고 이를 간과하지만, 실제 시스템 성능의 절반 가량 "얼마나 동일한 SQL이 반복되는가"에 달려 있습니다.

따라서 튜닝은 쿼리의 빠름만 보는 것이 아니라, 재사용성을 높이는 방향으로 접근해야 합니다.

바인드 변수의 중요성

내용

바인드 변수(Bind Variable)는 SQL 문장에서 리터럴 값을 변수로 대체하여 작성하는 방식입니다. 이 방식은 SQL 실행 시점에 값만 바인딩(bind)되며, 문장 자체는 그대로 유지되므로 SQL 재사용(소프트 파싱)을 가능하게 만드는 핵심 기법입니다.

바인드 변수를 사용하면 다음과 같은 이점이 있습니다.

  • 하드 파싱 방지

    SQL 문장이 동일하게 유지되므로, 옵티마이저가 이미 만들어 놓은 실행 계획을 재사용할 수 있음

  • 라이브러리 캐시 효율 향상

    동일한 SQL이 하나의 캐시 슬롯을 공유하므로 메모리 사용량 감소 및 경합 완화

  • 보안 강화

    SQL Injection과 같은 보안 취약점 방지 효과 (특히 동적 SQL을 생성하는 환경에서)

  • 성능 예측 가능성 향상

    계획이 고정되기 때문에 예측 가능한 성능 유지 가능

💡 바인드 변수는 "성능을 위한 문법"이자 "보안을 위한 약속"입니다.

실제 사례 및 사용

  1. 리터럴 vs 바인드 변수

    1. 리터럴 사용 (하드 파싱 유발)

       SELECT * FROM orders WHERE customer_id = 1001;
 SELECT * FROM orders WHERE customer_id = 1002;

      SQL 문장이 매번 달라지므로 하드 파싱이 발생하고, 라이브러리 캐시를 오염시킴

    2. 바인드 변수 사용 (소프트 파싱 유도)

       -- Oracle, JDBC 등에서는 :1, ? 형식으로 사용
 SELECT * FROM orders WHERE customer_id = :1;

      동일한 SQL 문장을 여러 번 실행하더라도 파싱 및 실행 계획 생성은 한 번만 수행

  2. 프로그래밍 언어에서의 바인드 변수 사용

    1. Java

       String sql = "SELECT * FROM orders WHERE customer_id = ?";
 PreparedStatement stmt = conn.prepareStatement(sql);
 stmt.setInt(1, 1001);
 ResultSet rs = stmt.executeQuery();

    2. Python

       cursor.execute("SELECT * FROM orders WHERE customer_id = %s", (1001,))

팁:

  • 대량 트래픽 API에서는 반드시 바인드 변수를 사용해 SQL 문장 수를 최소화해야 함
  • ORM을 사용할 경우에도 Named Parameter / Positional Parameter 사용 여부를 확인
  • Oracle 기준으로는 cursor_sharing = FORCE를 사용하여 강제로 바인드 효과를 줄 수도 있으나, 이는 임시 방편이며 코드 정리가 우선되어야 함

3. 데이터 저장 구조 및 I/O 메커니즘

SQL이 느린 이유

내용

많은 개발자들은 "SQL이 느리다"고 느낄 때, 단순히 인덱스 유무나 WHERE 조건만을 떠올립니다. 하지만 실질적으로 SQL의 성능 저하 원인은 대부분 다음 두 가지에서 비롯됩니다.

  1. 불필요한 데이터 접근량 증가

    • 쿼리가 반환할 필요가 없는 데이터까지 읽고 처리함
    • 적절한 인덱스가 없거나, 옵티마이저가 잘못된 실행 계획을 선택한 경우

  2. I/O 비용이 과도하게 발생

    • SQL 실행 시 디스크에서 읽어야 할 데이터 블록이 너무 많음
    • 캐시 미스(cache miss)로 인해 물리적인 디스크 접근이 반복됨

즉, SQL이 느려지는 본질적인 이유는 단순한 문장 구조의 문제가 아니라, 어떤 데이터를 얼마나 읽어야 하는가에 대한 물리적 비용과 관련됩니다.

💡 SQL이 느리다는 건 "생각보다 많은 일을 하고 있다"는 뜻입니다.

실제 사례 및 사용

  1. 조건절이 인덱스를 무력화

     -- 함수 사용으로 인덱스 사용 불가
 SELECT * FROM users WHERE TO_CHAR(created_at, 'YYYY-MM-DD') = '2025-05-14';

    created_at 컬럼에 인덱스가 있어도 함수 사용으로 인해 인덱스를 활용하지 못함 → Full Table Scan

  2. WHERE 조건 없이 불필요한 전체 스캔

     -- 비즈니스상 불필요한 전 테이블 스캔
 SELECT * FROM logs;

    수천만 건의 로그가 존재하는 테이블에 조건 없이 접근하면 성능 저하는 당연함

팁:

  • WHERE 조건은 반드시 인덱스 컬럼에 함수/연산 없이 직접 비교되도록 작성
  • EXPLAIN PLAN, AUTOTRACE, DBMS_XPLAN 등을 사용하여 실제로 읽는 블록 수를 확인
  • 쿼리 튜닝은 로직의 정렬이 아니라, 읽지 않아도 될 데이터를 덜 읽게 만드는 과정임

정리 및 마무리

SQL은 "한 줄"이지만, 그 안에 숨겨진 일은 수천 번의 디스크 탐색일 수도 있습니다.

느린 SQL의 원인은 대부분 의도하지 않은 I/O에서 발생하며, 이것이 바로 우리가 데이터 저장 구조와 접근 방식을 공부해야 하는 이유입니다.

결론적으로, "왜 느린가"라는 질문에는 반드시 "얼마나 읽었는가"라는 답으로 접근해야 합니다.

데이터베이스 저장구조

내용

데이터베이스에서 테이블은 단순히 행과 열의 모음이 아닙니다. 실제로는 디스크에 저장되는 물리적 구조를 기반으로 하며, 이 저장 방식이 SQL 성능에 큰 영향을 미칩니다.

Oracle을 포함한 대부분의 RDBMS는 데이터를 다음과 같은 계층적 구조로 저장합니다.

  1. 테이블스페이스(Tablespace)

    논리적인 저장 단위. 데이터 파일로 구성되며, 하나 이상의 테이블 또는 인덱스를 포함함

  2. 세그먼트(Segment)

    테이블, 인덱스 등 하나의 객체가 차지하는 전체 공간

  3. 익스텐트(Extent)

    세그먼트가 차지하는 연속된 블록 집합. 점진적으로 확장됨

  4. 블록(Block)

    디스크에서의 최소 I/O 단위. 실제 데이터가 저장되는 단위

  5. 행(Row)

    최종적으로 사용자가 접근하는 데이터 레벨. 하나의 블록에는 여러 개의 행이 존재할 수 있음

💡 이 구조는 단지 저장을 위한 구성이 아니라, I/O 전략과 접근 방식을 결정짓는 성능의 기반입니다.

실제 사례 및 사용

  1. 한 테이블이 사용하는 세그먼트 및 블록 정보 확인 (Oracle 기준)

     SELECT segment_name, blocks, extents
 FROM dba_segments
 WHERE segment_name = 'ORDERS';

    해당 테이블이 얼마나 많은 디스크 공간을 사용하는지, 얼마나 많은 블록에 걸쳐 저장되어 있는지를 파악할 수 있음

  2. 세그먼트 확장

    • 처음 테이블 생성 시 64KB만 차지하다가, 데이터 증가에 따라 익스텐트가 추가됨
    • 데이터가 무작위로 삽입되면 익스텐트가 비연속적으로 분산되어 성능에 악영향을 줄 수 있음

팁:

  • 인서트 패턴이 순차적일수록 블록 정렬이 잘 유지되어 성능이 좋음
  • DDL 설계 시 초기 익스텐트 크기와 증가 단위를 고려하여 조정 가능
  • 테이블 및 인덱스의 저장 구조가 분산(fragmentation)되어 있다면 MOVE, REBUILD 등을 고려할 수 있음

정리 및 마무리

데이터베이스는 메모리에 존재하는 것이 아니라, 디스크 위에 층층이 쌓인 구조물입니다. 우리가 SQL을 작성할 때마다, 그 한 줄은 결국 어느 블록을 얼마큼 읽어야 할지를 결정합니다.

저장 구조를 이해한다는 것은, 디스크를 어떻게 효과적으로 탐색할지를 이해하는 것이며, 이는 고급 SQL 성능 튜닝의 전제조건입니다.

블록단위 I/O

내용

데이터베이스는 디스크 I/O를 블록 단위로 수행합니다. 즉, SQL이 테이블에서 단 하나의 행(row)을 요청하더라도, 디스크는 해당 행이 포함된 전체 블록(block)을 읽습니다. 이 블록 단위의 처리 방식은 성능에 중대한 영향을 줍니다.

  • 데이터 블록 (Data Block)

    디스크에서 읽고 쓰는 최소 단위. Oracle에서는 기본적으로 8KB 또는 16KB 크기를 가집니다.

  • 한 블록에는 여러 개의 행(row) 이 존재하며, 연속된 데이터일수록 하나의 블록에 많이 담김 → 성능이 향상됨

  • I/O는 항상 블록 단위로 발생하므로, 불필요한 블록을 덜 읽는 것이 쿼리 최적화의 핵심입니다.

💡 데이터베이스는 "행" 단위로 보여주지만, "블록" 단위로 일합니다.

실제 사례 및 사용

  1. 단 하나의 행을 읽어도 블록 전체가 읽힌다

     SELECT * FROM employees WHERE emp_no = 1001;
    • emp_no 인덱스를 통해 빠르게 접근하더라도, 해당 행이 속한 블록 전체가 읽힘
    • 해당 블록에 여러 행이 함께 저장되어 있다면 효율적, 그렇지 않다면 낭비

  2. Full Table Scan의 블록 수 확인 (Oracle)

     SELECT table_name, blocks 
 FROM user_tables 
 WHERE table_name = 'ORDERS';

    ORDERS 테이블이 얼마나 많은 블록에 걸쳐 저장되어 있는지 파악 가능

팁:

  • 한 블록당 저장 가능한 행 수는 행의 크기와 블록 크기에 비례
  • 자주 접근하는 열만 별도로 분리하면 I/O를 줄일 수 있음 (예: hot column 분리)
  • 블록이 연속적으로 배치되어 있다면 성능 향상 (반대로 조각나 있으면 성능 저하)

정리 및 마무리

데이터베이스는 우리에게 레코드 기반의 환상을 제공하지만, 실제로는 블록 기반의 하드웨어적 세계에서 동작합니다.

"몇 건 읽었는가?"가 아니라 "몇 개의 블록을 읽었는가?"가 성능의 진실입니다.

SQL 튜닝은 결국, 최소한의 블록만 읽고도 원하는 결과를 얻는 설계로 귀결됩니다.

시퀀셜 액세스 vs 랜덤 액세스

내용

데이터베이스에서 데이터를 읽는 방식은 크게 두 가지로 구분됩니다.

  1. 시퀀셜 액세스(Sequential Access)

    디스크 상에서 연속된 블록들을 순서대로 읽는 방식입니다.

    Full Table Scan처럼 많은 양의 데이터를 한번에 연속적으로 읽는 작업에서 주로 사용됩니다.

    디스크 헤드 이동이 적기 때문에 처리량(throughput)이 높고, 멀티블록 I/O에 유리합니다.

  2. 랜덤 액세스(Random Access)

    디스크 상의 임의 위치에 있는 블록들을 점프해서 읽는 방식입니다.

    인덱스 기반 조회(Index Scan)에서 주로 발생하며, 디스크 헤드의 이동이 많아 I/O 효율이 떨어질 수 있습니다.

    단, 결과 건수가 적은 경우에는 빠릅니다.

💡 I/O 관점에서 보면, 인덱스가 항상 빠른 것은 아닙니다. 결과가 많으면 오히려 시퀀셜 접근이 더 효율적입니다.

비교 요약

항목 시퀀셜 액세스 랜덤 액세스
읽는 방식 연속된 블록 순차 읽기 임의 블록을 점프하여 읽기
발생 조건 Full Scan, Range Scan Index Scan, Key Lookup
속도 특성 데이터 양이 많을수록 유리 적은 건수 조회 시 유리
디스크 I/O 효율 좋음 (멀티블록 처리 가능) 나쁨 (디스크 seek overhead)

실제 사례 및 사용

  1. 시퀀셜 액세스 (Full Table Scan)

     SELECT * FROM sales WHERE year = 2024;
    • year에 인덱스가 없다면, 전체 테이블을 순차적으로 블록별로 읽음
    • 블록이 연속되어 있을 경우 빠르게 처리

  2. 랜덤 액세스 (Index Lookup)

     SELECT * FROM users WHERE user_id = 1001;
    • 인덱스를 통해 해당 키를 찾아간 뒤, 해당 행이 위치한 블록을 랜덤하게 접근
    • 대상이 많아질수록 디스크 I/O가 많아지고 느려질 수 있음

팁:

  • 대량 데이터 검색에는 Full Scan이 오히려 효율적일 수 있음
  • 결과 건수에 따라 접근 방식이 달라짐 → 옵티마이저가 판단 기준
  • 디스크 기반 DB에서는 시퀀셜이 빠르지만, SSD 기반 환경에서는 랜덤 접근의 차이가 줄어들 수 있음

정리 및 마무리

I/O는 속도의 문제가 아니라 물리 구조의 문제입니다. 시퀀셜은 "책장을 넘기는 것", 랜덤은 "책장 속 임의 페이지를 계속 펼치는 것"입니다.

결국, 데이터의 배치와 접근 빈도에 따라 어느 방식이 더 나은지는 정해져 있습니다.

SQL 튜닝은 '인덱스 쓸까 말까'의 문제가 아니라, '순차적으로 읽을 수 있는 환경인가 아닌가'를 설계하는 일입니다.

논리적 I/O vs 물리적 I/O

내용

SQL이 데이터를 읽는 과정에서 발생하는 I/O는 크게 두 가지로 구분됩니다.

  1. 논리적 I/O (Logical I/O)

    데이터베이스의 버퍼 캐시(buffer cache)에서 데이터를 읽는 작업입니다. 즉, 디스크까지 내려가지 않고 메모리에 이미 올라온 블록을 읽는 경우입니다. 성능에 큰 영향을 주지 않으며, SQL이 빠르게 응답하는 경우 대부분 이 방식입니다.

  2. 물리적 I/O (Physical I/O)

    디스크에서 블록을 직접 읽어오는 작업입니다. 캐시에 해당 블록이 존재하지 않으면 디스크까지 내려가야 하며, 이는 가장 비용이 큰 I/O입니다.

이 두 I/O는 외형상 동일한 SQL이라도 실행 시점의 캐시 상태에 따라 전혀 다른 성능을 나타낼 수 있습니다.

💡 "느려졌다"는 대부분의 현상은 논리적 I/O가 물리적 I/O로 바뀌었기 때문입니다.

비교 요약

항목 논리적 I/O 물리적 I/O
데이터 위치 메모리(Buffer Cache) 디스크
속도 빠름 느림 (디스크 접근 필요)
비용 낮음 높음
발생 조건 데이터가 캐시에 존재 캐시에 없을 경우 디스크 읽기
성능 영향 낮음 큼

실제 사례 및 사용

  1. 논리적 I/O가 대부분인 경우 (캐시 히트)

     -- 자주 조회되어 이미 캐시에 올라와 있음
 SELECT * FROM config_settings WHERE setting_name = 'theme';
    • 설정 정보처럼 자주 읽히는 테이블은 대부분 캐시에 상주
    • 물리적 I/O 없이 버퍼에서 처리되므로 응답 속도가 매우 빠름

  2. 물리적 I/O가 발생한 경우 (캐시 미스)

     -- 수백만 건의 로그 테이블, 조건 없음
 SELECT * FROM audit_log;
    • 처음 조회되는 대용량 테이블은 버퍼에 없으므로 디스크에서 직접 읽기
    • 이는 수천 ~ 수만 개의 물리적 I/O를 유발하며 성능 저하 원인

팁:

  • Oracle에서는 v$sql, v$session_event, AUTOTRACE 등을 통해 물리적/논리적 I/O를 분석 가능
  • 캐시 적중률을 높이기 위해 자주 사용하는 데이터를 고정(PIN)하거나, 읽기 순서를 조정
  • 일부 고속 처리 요구 쿼리는 Result Cache 또는 Materialized View로 대체 가능

정리 및 마무리

데이터는 항상 논리적으로 읽히지만, 실제로는 물리적으로 읽는 순간 성능 병목이 시작됩니다.

즉, 성능 저하는 새로운 SQL을 작성해서가 아니라, 기존 SQL이 캐시에 없는 상태에서 실행되면서 시작됩니다.

SQL 튜닝은 단순히 쿼리를 줄이는 것이 아니라, 디스크를 건드릴 일이 없도록 만드는 설계의 예술입니다.

Single Block I/O vs Multiblock I/O

내용

데이터베이스는 디스크에서 데이터를 읽을 때 한 번에 몇 개의 블록을 읽을 것인가를 전략적으로 선택합니다. 이 전략은 다음 두 가지로 나뉩니다.

  1. Single Block I/O (단일 블록 I/O)

    한 번에 하나의 블록만 읽는 방식입니다. 주로 인덱스를 통한 랜덤 액세스나, 정밀한 조회에서 사용됩니다. 빠르게 특정 행을 찾아야 할 때 적합하지만, 반복 호출이 많을 경우 성능 저하가 발생할 수 있습니다.

  2. Multiblock I/O (다중 블록 I/O)

    한 번의 I/O 요청으로 여러 개의 연속된 블록을 한꺼번에 읽는 방식입니다. 주로 Full Table Scan이나 시퀀셜 액세스에서 사용되며, 대량 처리 시 매우 효율적입니다. Oracle은 db_file_multiblock_read_count 파라미터로 이 최대 블록 수를 제어합니다.

💡 "몇 번 읽을 것인가"보다 "한 번에 얼마나 읽는가"가 전체 성능에 더 큰 영향을 줄 수 있습니다.

비교 요약

항목 Single Block I/O Multiblock I/O
읽는 단위 1개 블록 여러 개의 연속 블록
주요 사용 시점 인덱스 탐색, 랜덤 접근 Full Table Scan, 시퀀셜 처리
속도 특성 정밀 조회에 유리 대량 처리에 유리
오버헤드 반복 호출 시 비용 커짐 한번에 처리하므로 상대적으로 적음
제어 파라미터 없음 db_file_multiblock_read_count (Oracle)

실제 사례 및 사용

  1. Single Block I/O (Index Access)

     SELECT * FROM employees WHERE employee_id = :1;
    • 옵티마이저는 인덱스를 사용해 해당 키에 대한 단일 블록을 읽음
    • 수천 건을 반복적으로 조회하면, 많은 I/O 호출이 발생하여 성능 저하 가능

  2. Multiblock I/O (Full Scan)

     SELECT * FROM employees WHERE department_id = 10;
    • 조건이 인덱스에 적합하지 않거나 많은 건수를 반환할 경우 Full Scan으로 전환
    • 이 경우 여러 블록을 한꺼번에 읽어오는 방식으로 처리됨

팁:

  • 데이터가 순차적으로 저장되어 있다면 Multiblock I/O가 더 유리
  • 인덱스를 과신하지 말고, 처리 대상의 양에 따라 I/O 전략이 달라질 수 있음을 고려
  • 대용량 데이터를 반복 조회하는 쿼리는 Batch 처리 또는 캐시 전략이 필요함

정리 및 마무리

I/O의 단위는 보이지 않지만, 성능은 그것에 따라 결정됩니다. 쿼리는 단순하지만, 실제로는 수천 번의 블록 읽기가 발생할 수 있습니다.

결국, "한 번에 얼마나 많이 읽을 수 있는가"는 데이터베이스 성능 최적화의 핵심 전략 중 하나입니다.

블록 수를 줄이는 것만큼, I/O 단위를 키우는 것 역시 중요한 성능 설계 요소임을 기억하십시오.

Table Full Scan vs Index Range Scan

내용

SQL에서 데이터를 검색할 때 가장 대표적인 두 가지 접근 방식은 아래와 같습니다.

  1. Table Full Scan

    테이블의 모든 블록을 처음부터 끝까지 읽는 방식입니다. 조건절이 인덱스를 사용할 수 없거나, 조회 대상이 테이블 전체의 상당수를 차지할 때 옵티마이저가 선택합니다. 주로 Multiblock I/O로 처리되며, 연속된 블록을 순차적으로 읽어 처리량이 높습니다.

  2. Index Range Scan

    인덱스를 활용하여 특정 범위의 레코드를 찾아가는 방식입니다. 인덱스에 정의된 컬럼 조건이 WHERE절에 정확히 명시된 경우 사용되며, 결과가 소수인 경우 매우 빠릅니다. 단, 해당 레코드를 찾은 이후에도 테이블에서 실제 데이터 블록을 읽는 추가 I/O가 발생합니다 (table access by ROWID).

💡 Index가 있다고 항상 사용하는 게 아닙니다. 결과가 많다면 오히려 Full Scan이 더 빠릅니다.

비교 요약

항목 Table Full Scan Index Range Scan
접근 방식 전체 테이블 블록 순차 읽기 인덱스를 통해 특정 범위 접근
I/O 방식 Multiblock I/O 주로 Single Block I/O + ROWID 탐색
성능 특성 조회 건수가 많을수록 유리 조회 건수가 적을수록 유리
인덱스 필요 여부 불필요 필수
예외적 사용 예시 인덱스 존재하더라도 조건이 부적절할 경우 바인드 피킹에 따라 비효율 발생 가능

실제 사례 및 사용

  1. Table Full Scan

     SELECT * FROM products WHERE UPPER(name) = 'DESK';
    • 인덱스가 존재하더라도 UPPER(name)처럼 함수를 사용하면 인덱스 무력화
    • 옵티마이저는 Full Scan을 선택

  2. Index Range Scan

     SELECT * FROM orders WHERE order_date BETWEEN :start AND :end;
    • order_date에 인덱스가 존재하면, 옵티마이저는 해당 범위만 인덱스를 통해 추출
    • 빠른 결과 도출 가능

팁:

  • 함수, 연산, 형 변환 등이 WHERE절에 포함되면 인덱스가 무효화될 수 있음
  • Full Scan은 나쁜 것이 아니라, 조건이 넓거나 결과가 많은 경우 합리적 선택
  • Oracle에서는 AUTOTRACE, DBMS_XPLAN.DISPLAY_CURSOR로 실제 선택된 방식 확인

정리 및 마무리

Full Scan과 Index Scan은 속도의 문제가 아니라 전략의 차이입니다.

오히려 잘못된 인덱스 접근은 무수한 랜덤 I/O를 초래하여 성능을 망칠 수 있습니다.

옵티마이저가 어떤 전략을 선택했는지 이해하고, 그 이유를 설명할 수 있어야 진짜 튜닝을 할 수 있습니다. 그리고 때로는 인덱스를 지우는 것이 더 빠를 수도 있다는 것을 기억하자.

캐시 탐색 메커니즘

내용

데이터베이스는 디스크 I/O 비용을 줄이기 위해 메모리 캐시를 적극적으로 활용합니다. 이 메커니즘은 SQL 성능에 절대적인 영향을 끼치며, 특히 같은 데이터를 여러 번 읽는 환경에서 큰 차이를 만들어냅니다.

DBMS에서 사용하는 주요 캐시 영역은 아래와 같습니다.

  1. 버퍼 캐시 (Buffer Cache)

    디스크에서 읽은데이터 블록을 저장하는 메모리 공간입니다. 다음 번 동일 블록 요청 시 물리적 I/O 없이 논리적 I/O만 수행됩니다.

  2. 라이브러리 캐시 (Library Cache)

    SQL 문장의 파싱 결과와 실행 계획이 저장되는 공간입니다. 같은 SQL이 들어왔을 때 하드 파싱 없이 소프트 파싱으로 처리할 수 있도록 합니다.

  3. 데이터 딕셔너리 캐시 (Dictionary Cache)

    테이블, 컬럼, 사용자 권한 등 시스템 메타데이터 정보를 캐싱합니다.

캐시 탐색은 일반적으로 아래 순서로 동작합니다.

  1. SQL 문장이 캐시에 있는지 확인 (Library Cache)
  2. 실행 계획이 있다면 해당 블록이 Buffer Cache에 있는지 확인
  3. 없다면 디스크에서 읽어 Buffer Cache에 적재
  4. 이후 동일 블록 요청 시 메모리에서 탐색

💡 "느린 쿼리도 두 번 돌리면 빨라지는 이유"는 대부분 캐시 덕분입니다.

실제 사례 및 사용

  1. 동일 쿼리 반복 시 성능 차이

     -- 대량 데이터 첫 실행 (물리적 I/O 발생)
 SELECT * FROM sales WHERE year = 2024;

 -- 동일 쿼리 두 번째 실행 (Buffer Cache에서 처리)
 SELECT * FROM sales WHERE year = 2024;
    • 첫 번째 실행은 디스크에서 블록 읽기 (물리적 I/O)
    • 두 번째 실행은 메모리에서 탐색 (논리적 I/O) → 수 ms 내 응답

팁:

  • 자주 사용하는 쿼리와 테이블은 캐시에 머무를 수 있는 구조로 설계 (예: 정기적 조회 대상 → 조회 전 warm-up)
  • 캐시 히트율은 Oracle의 경우 v$buffer_pool_statistics, PostgreSQL은 pgstatio* 뷰로 확인 가능
  • 사용자 정의 함수(UDF)나 비정형 쿼리는 캐시 효과를 저해할 수 있음 → 정형화된 SQL 작성 권장

정리 및 마무리

데이터베이스 성능의 핵심은 디스크를 덜 쓰고 메모리를 더 쓰게 만드는 것입니다. 그리고 그 메모리를 효과적으로 활용하기 위한 장치가 바로 캐시 탐색 메커니즘입니다.

쿼리는 언제나 느리게 시작하고, 반복될수록 빨라집니다. 이 반복의 효과를 극대화하려면, 잘 설계된 캐시 유도 전략이 필요합니다. 튜닝은 단지 쿼리를 줄이는 게 아니라, 시스템이 기억할 수 있는 쿼리를 쓰는 것입니다.

728x90
반응형