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

친절한SQL튜닝 책 정리

 

 

1. SQL 파싱과 최적화

 

(1) SQL의 실행과정

 > SQL 구문 인식 - SQL 최적화 - 실행 코드 생성

 

(2) SQL 옵티마이저

 > SQL 최적화 과정은 자동차 내비게이션과 흡사하다.

    가장 최단길(실행시간이 짧은)을 찾는 과정이다.

 

(3) 옵티마이저 힌트

 > SQL 최적화는 항상 최적의 길만 안내하지 않는다.

    이럴 때 옵티마이저 힌트를 이용해 경로를 바꿀 수 있다.

 

 

2. SQL 공유 및 재사용

 

(1) 소프트 파싱 vs 하드 파싱

    > SQL 최적화를 통해 만든 실행 코드는 재사용할 수 있게 라이브러리 캐시에 저장된다.

       소프트 파싱 : 캐시에서 실행코드를 찾아 곧바로 실행 

       하드 파싱 : 최적화를 진행하여 실행코드 생성

    > 하드 파싱에서 최적화는 수십만가지의 경우의 수를 고려하므로 소프트 파싱보다 훨씬 더 무거운 과정이다. 

 

(2) 바인드 변수의 중요성

    > 1) SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'A'

       2) SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'B'

       3) SELECT * FROM CUSTOMER WHERE LOGIN_ID = :1

    > 위의 1번과 2번은 서로 다른 SQL이다. 200만명이 로그인 한다면 200만번의 최적화를 진행한다.

       하지만 3번은 1,2번을 공유해 사용하는 SQL이다 최적화 없이 소프트 파싱이 진행된다.

    > JAVA에서 binding code 

       String SQL = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?";

       PreparedStatement st = con.prepareStatement(SQL);

       st.setString(1, login_id);

       는 3번의 SELECT * FROM CUSTOMER WHERE LOGIN_ID = :1 로 적용된다.

 

 

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

 

 (1) SQL이 느린 이유

     > 디스크 I/O 때문이다. 여러곳에서 접근하는 경우 순차적으로 처리된다.

 

 (2) 데이터베이스 저장 구조

     > 블록 : 데이터의 묶음 단위

        익스텐트 : 블록의 집합, 공간 할당 단위

        세그먼트 : 데이터 저장공간이 필요한 오브젝트 (테이블, 인덱스 등)

        테이블스페이스 : 세그먼트를 담는 컨테이너

        데이터파일 : 디스트 상의 물리적인 OS 파일

 

 (3) 블록 단위 I/O

     > 레코드를 하나 읽을 때도 블록을 통째로 읽는다. 디폴트는 보통 8KB이다.

 

 (4) 시퀀셜 액세스 vs 랜덤 액세스 

     > 시퀀셜 액세스: 물리적 or 논리적 순서에 따라 순차적으로 스캔

        랜덤 액세스 : 물리적 순서를 따르지 않고 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식 

 

 (5) 논리적 I/O vs 물리적 I/O

     > DB버퍼캐시 : '라이브러리 캐시'가 '코드 캐시'라고 한다면 'DB버퍼캐시'는 '데이터 캐시'이다.

        데이트 블록을 읽을 때 버퍼 캐시부터 탐색한다. 이럴 경우 이스크 I/O를 하지 않아도 되고

        같은 블록을 읽는 다른 프로세스도 득을 볼 수 있다. 약 10000배 정도 빠르다.

        메모리 I/O 는 전기적 신호(빛의 속도 ㄷ)이고 디스크 I/O는 액세스 암을 통한 물리적 작용이기 때문이다.

        참고 : SSD를 쓰면 DBMS가 빨라질까? (naver.com)

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

        논리적 I/O : SQL을 수행하면 읽어야 하는 블록 I/O

        물리적 I/O : DB 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O

        버퍼 캐시 히트율 = ((논리적 I/O - 물리적 I/O) / 논리적 I/O)* 100 (%)

        속도 향상을 위해서는 논리적 I/O를 줄여야 한다. 물리적 I/O는 통제 불가능한 변수이다.

       즉 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.

 

 (6) Single Block I/O vs Multiblock I/O

     > Single Block I/O : 디스크에 한 블록씩 요청해서 메모리 캐시에 적재

        Multiblock I/O : 디스크에 여러 블록을해서 메모리 캐시에 적재(수십~수백)

        인덱스를 이용할 때는 기본적으로 Single Block I/O로 동작한다.

        테이블 전체 스캔할 때는 Multiblock I/O를 사용한다.

 

 (7) Table Full Scan vs Index Range Scan

     > Index Range Scan : 랜덤 액세스와 Single Block I/O 방식. 소량데이터에 적합

        Table Full Scan : 테이블 전체를 스캔. 시퀀셜 액세스와 Multiblock I/O 방식이다. 대량 데이터에 적합

        Storage 스캔 성능이 좋아져도 Single Block I/O 는 성능이 조금밖에 좋아지지 않는다. 한개씩 읽기때문 

        이에 반해 Muliblock I/O는 Storage 스캔 성능에 비례해 성능이 향상된다.

        많은 데이터 검색을 위해 Index Range Scan을 하는 경우 비효율적이다.

        Ex) 전체 데이터가 10000개 인데  where key > 100 처럼 9900개를 조회할 경우

        그러므로 이 경우 Table Full Scan으로 유도한다. 조인을 사용할 경우 조인메소드로 해시 조인으로 선택해준다.

    

 (8) 캐시 탐색 메커니즘

     > 하나의 버퍼캐시 블록에 두 개 이상 프로세스가 동시에 접근 할때 문제가 생길 수 있다.

        그래서 순차적으로 접근하도록 직렬화 메커니즘이 필요하다.(줄세우기 메커니즘^^)

        이런 매커니즘을 래치이다. 캐시마다 별도의 래치가 존재한다.

        버퍼캐시에는 캐시버퍼 체인 래치, 캐시버퍼 LRU 체인 래치 등 다양한 래치 등이 작동한다.

        래치에 의한 경합 때문에 캐시 I/O도 생각만큼 빠르지 않을 수 있다.

        이런 직렬화 메커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O) 자체를 줄여야 한다.