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

1.1 SQL 파싱과 최적화

• SQL : Structured Query Language

 

• SQL 최적화 : DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 과정
  1. SQL 문장의 문법과 의미상 오류가 없는지 파싱함.
  2. 미리 수집한 통계정보를 바탕으로 다양한 실행 경로를 생성해 가장 효율적인 길을 선택
  3. 가장 효율적인 길을 이용해 실제 로우 소스를 생성

 

• SQL 옵티마이저 : 위 두 번째 단계에서 가장 효율적인 길을 계산하는 DBMS의 핵심 엔진
  • 네비게이션처럼 미리 이동 경로를 하나씩 확인하는 방식임.
  • 그리고 결정한 이동 경로를 '실행계획'이라는 기능을 이용해서 확인할 수 있음
  • SQL 문장으로 실행계획을 ON 시켜 확인할 수도 있고, 보통 DB 툴에서 바로 확인할 수 있음.
  • 네비게이션도 항상 가장 빠른 길을 보여주는 것이 아닌 것처럼, SQL 옵티마이저가 정한 실행계획이 최선이 아닐 수 있음. 이럴 때는 힌트를 제공하여 실행계획을 변경해야 함.

 

• 힌트 : 옵티마이저에게 자신이 원하는 방향으로 실행계획을 세우도록 힌트를 주는 방법

    -- EMP 테이블에 대해서 해당 인덱스를 사용하라는 간단한 힌트
    -- 테이블을 명시할 때는 스키마를 명시하면 안 됨. 반드시 테이블명만 지정
    SELECT /*+ INDEX(EMP 인덱스명) */
    	*
    FROM ...
  
    -- 힌트를 여러 개 사용할 때는 콤마(,)를 사용하면 안 됨. 가장 앞에것만 적용돼버림
    SELECT /*+ INDEX(EMP 인덱스명) INDEX(DEPT 인덱스명) */
    	*
    FROM ...
  
    -- 테이블에 ALIAS를 지정했다면 힌트에도 반드시 지정한 ALIAS로 테이블을 표현해야 함
    SELECT /*+ INDEX(A 인덱스명) */
    	*
    FROM DEPT A ...​

 

1.2 SQL 공유 및 재사용

• SQL 문장을 한번 수행하면 최적화 후에 결정한 실행 계획을 재사용하기 위해 캐싱한다.
  • SGA(System Global Area)라는 메모리 공간에 존재하는 Library Cache에 캐싱
  • 캐싱을 하는 이유는 실행계획을 결정하기 위한 계산이 부담이 됨.
    • 테이블, 컬럼, 인덱스 구조도 파악해야 하고
    • 테이블과 인덱스, 그리고 컬럼 통계를 확인하고
    • Single Block I/O 나 MultiBlock I/O가 빠른지도 확인해야 하고 (밑에 설명)
    • 어떤 인덱스를 사용해야 할지 등등
  • 그래서 캐시에 존재하지 않아 최적화의 모든 단계를 수행하는 것을 하드 파싱이라고 표현
  • 반대로 Library Cache에서 발견해서 바로 실행단계 넘어가는 것을 소프트 파싱이라고 표현

 

• Library Cache에서 해당 SQL을 캐싱했는지 찾으려면 Map처럼 Key가 필요
  • SQL 문장은 따로 Key가 없으므로 SQL 문장 자체를 Key로 사용함.
  • SQL 문장이 조금만 달라져도 하드 파싱을 수행. 이 말은 즉 같은 SQL 문장이지만 조건절의 변수 값이 달라도 하드 파싱
  • 따라서 바인드 변수를 사용하여 SQL 문장을 작성해야 하는 이유임

    SELECT * FROM DEPT WHERE DEPT_ID = :1​

 

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

• 데이터베이스 저장 구조 (큰 순서대로 나열)
  • 테이블스페이스 : 세그먼트를 담는 컨테이너
  • 세그먼트 : 테이블, 인덱스, 파티션처럼 데이터 저장공간이 필요한 오브젝트. 세그먼트는 여러 익스텐트로 구성됨.
  • 익스텐트 : 세그먼트(오브젝트)의 공간이 부족할 경우, 공간을 확장하는 단위. 만약 테이블에 데이터가 많이 쌓여서 공간이 부족할 경우 익스텐트를 추가로 할당받음.
  • 블록 : 사용자가 입력한 레코드를 실제로 저장하는 공간. 읽고 쓰는 단위임
    • 특정 레코드를 하나만 가지고 오고 싶어도, 블록 단위로 읽기 때문에 블록을 통째로 읽음
    • 블록의 디폴트 크기는 8KB

 

• 블록을 읽는 방법에는 시퀀셜 액세스와 랜덤 액세스과 있음
  • 시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식. 즉 Full Scan
  • 랜덤 액세스 : 특정한 랜덤 방식으로 블록을 읽는 방식

 

• 캐시
  • 위에서 설명했지만, 실행계획은 SGA(System Global Area)라는 메모리 공간에 존재하는 Library Cache에 캐싱함. 이 외에도 함수/프로시저 등도 캐싱하기 때문에 '코드 캐시' 라고도 함.
  • 실제 쿼리를 수행해서 디스크 I/O에서 가지고 온 데이터 자체도 캐시에 저장하는데, SGA라는 메모리 공간에 존재하는 DB 버퍼 캐시에 캐싱함. 데이터를 캐시 하므로 '데이터 캐시'라고도 함.

 

• 1.3.5 논리적 I/O와 물리적 I/O (세 번째 읽고 있지만, 여전히 이해가 안 됨..)
  • 논리적 I/O : DB 버퍼 캐시에서 가지고 온 블록
  • 물리적 I/O : 디스크에서 가지고 온 블록. 당연 디스크에서 가지고 오므로 1000배 정도 느림
  • 이 다음 설명부터 이해되지 않음..
  • SQL이 느린 이유는 거의 무조건 디스크 I/O 때문
  • SQL 튜닝 : 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것

 

• Single vs Multi Block I/O
  • Single Block I/O : 한 번에 한 블록씩 요청해서 버퍼 캐시에 적재하는 방식
    • 디폴트로 인덱스와 테이블 블록 모두 이 방식을 사용
    • 또한 인덱스는 소량 데이터를 읽을 때 주로 사용하므로 이 방식이 효율적
  • Multi Block I/O : 한 번에 여러 개 블록을 버퍼 캐시에 적재하는 방식
    • 대용량 테이블을 Full Scan 해야 한다면 이 방식이 효율적
    • 일반적으로 1MB. DBMS에서는 더 크게 설정은 할 수 있어도 OS에서 최대 지원하는 I/O 단위가 1MB
    • 블록을 한 번에 가지고 올 때, 한 익스텐트에서만 동작함. 다른 익스텐트로 넘어가서까지 한 번에 가지고 오지는 않음

 

• Scan 방식은 아주 크게 두 가지로 나눌 수 있음
  • Table Full Scan : 테이블에 속한 블록 전체를 읽는 방식
    • 이 방식이 항상 나쁜 것은 아님
    • 집계용 SQL 이나 배치성 SQL처럼 많은 데이터를 가지고 와야 한다면 이 방식이 빠를 수 있음.
    • 왜냐하면 Multi Block 방식으로 여러 블록을 한 번에 가지고 오기 때문
  • Index Range Scan : 인덱스를 이용하여 읽는 방식
    • 소량의 데이터를 읽을 때 좋은 방식

 

• 버퍼 캐시 탐색 방법
  1. 해시함수를 사용해서 나온 결과로 링크드 리스트 헤더를 찾음
  2. 찾으려고 하는 블록이 나올 때까지 리스트 탐색
  3. 발견하면 끝
  4. 없다면 디스크로부터 읽어서 리스트에 추가

 

• 버퍼는 동시에 사용할 수 없어 락이 필요해서 Latch라는 방식으로 각각의 링크드 리스트마다 락을 걸음

 

• 버퍼 캐시를 탐색하는 것도 락에 의해서 느릴 수 있으므로, 버퍼 캐시조차 접근하는 것을 줄여야 한다. 즉, 논리적 I/O를 줄여야 한다.