SQL튜닝 - Chapter01. SQL처리과정과 I/O

친절한 SQL 튜닝 챕터1 정리

들어가기 전 간단 정리 ( 생각나는 대로 정리하고 급하게 적은 거라 정확하지 않을 수 있다..)

 

SQL 튜닝 정의

    -- 성능 테스트를 하고 성능이 안좋은 애들(?) 분석하고 고치는 것이 SQL 튜닝

    진정한 튜닝은 모델리우터 다시해야한다ㅋㅋ

 

튜닝자체는 기술적으로 쉽다. 

1. 튜닝이 어렵다고 느끼는 이유는 업무를 정확히 모르기 때문이다.

   업무가 80%에 해당하고 기술은 20%에 해당한다

2. 추가는 쉽고, 변경은 어렵다.

 

Optimizer가 하는 일

    1. 문법체크

    2. 구문분석

    3. 최적화 : 자동

    4. 번역

 

다만 자동은 만능이 아니다

 

최적화는 자동과 수동으로 나뉜다

 

자동은 2가지로 나뉘는데 Rule Base / Cost Base 로 나뉜다

 

Rule Base : 쉽게 설명하면 자바 연산자에서 우선순위가 지정되어 있는 것처럼 우선순위가 지정되어 있는 것

                    그러나 지금은 사용하지 않음.

 

Cost Base : 통계기반 , 통계정보가 쌓일 수록 정교해진다 ( Ex. 네비게이션 )

                    다시말하면 많은 사람이 써야 데이터가 많이 쌓이고 더욱 더 정교해진다.

소프트 파싱 : 캐시된 SQL을 사용   /  하드파싱 :  SQL 사용

 

"select" where id = "asdf"

"SELECT " where id = "asdf"

이 둘이 같은 Query문일까? 

 

그렇지 않다. 공백 , 대 소문자 구분이 굉장히 중요하다.

 

---

SQL은 기본적으로 구조적 ( structured ) , 집합적 ( set -based ) , 선언적 ( declarative )인 질의 언어

원하는 결과집합을 구조적 , 집합적으로 선언하지만 그 결과집합을 만드는 과정은 절차적일 수 밖에 없다.

프로시저가 필요한데, 프로시저를 만들어내는 DBMS 내부 엔진이 SQL 옵티마이저다.

사용자 -------> 옵티마이저 -------> 프로시저

              SQL                    실행계획

  SQL을 실행하기 전 최적화 과정을 세분화

 

1 - 1. SQL 최적화

 

1. SQL 파싱

 

   1-1. 파싱트리생성

   1-2. Syntax 체크 ( 문법체크 ) : 사용할 수 없는 키워드를 사용했거나 순서가 바르지 않거나 누락된 키워드가 있는지 확인

   1-3. Semantic 체크 ( 구문체크 ) : 존재하지 않는 테이블 또는 컬럼을 사용했거나 오브젝트에 대한 권한이 있는지 확인

 

2. SQL 최적화

 

   옵티마이저가 역할을 한다.

   오브젝트 통계정보를 바탕으로 다양한 실행경로를 비교해서 가장 효율적인 하나를 선택한다.

   데이터베이스 성능을 결정하는 가장 핵심적인 엔진

 

3. 로우 소스 생성

 

 SQL 옵티마이저

사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 엑세스 경로를 선택해 주는 DBMS의 핵심 엔진.

 

쿼리를 수행해서 예측비용을 따진 다음 최저비용을 나타내는 실행계획을 선택한다.

 

 

자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지를 확인 할 수 있어야 하고 인덱스가 무엇인지 알아야 한다.

 

옵티마이저 힌트

자동차 내비게이션이 보편적으로 좋은 선택을 하지만 그 선택이 항상 최선은 아니다.

운전자 자신만 아는 정보나 경험을 활용해서 더 빨리 목적지에 도착할 수 있는 것처럼 통계정보에 담을 수 없는 데이터

또는 업무 특성을 활용해 개발자가 직접 더 효율적인 액세스 경로를 찾아낼 수 있다

 

- 힌트 사용법 -

SELECT /*+ INDEX(A 고객 PK) */

               고객명, 연락처, 주소, 가입일시

   FROM 고객 A

WHERE 고객 ID = '000000008'

 

힌트 작성 시 주의사항

 

  1. 콤마 사용금지

  2. 스키마명 명시 금지

  3. FROM 절 테이블명 옆에 ALIAS를 지정했다면 반드시 ALIAS 사용

 

 

1 - 2 . SQL 공유 및 재사용

 

소프트 파싱과 하드파싱의 차이점

들어가기 전에 앞서 SGA를 알아야 한다.

 

SGA ( System Global Area )

서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간.

SGA ( 그림을 내가 너무 못그린다...)

 

SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 소프트파싱 ( Soft Pasing )

 

찾는 데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 하드 파싱 ( Hard Parsing )

 

실행까지 바로가는것이 소프트파싱 / 로우소스까지 거쳐서 실행하는 것이 하드파싱

 

SELECT * FROM emp WHERE empno = 7900;

select * from EMP where EMPNO = 7900;

select * from emp where empno = 7900;

select * from emp where empno = 7900 ;

select * from emp where empno = 7900  ;

select * from scott.emp where empno = 7900;

select /* comment */ * from emp where empno = 7900;

select /*+ first_rows */ * from emp where empno = 7900;

위에 SQL문을 모두 다른 SQL 문이다.

의미적으로 모두 같지만, 실행할 때 각각 최적화를 진행하고 라이브러리 캐시에서 별도 공간을 사용한다.

 

public void login(String login_id) throws Exception {

  String SQLstmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ' " + login_id + " ' ";

  Statement st = con.createStatement();

  ResultSet rs = st.executeQuery(SQLStmt);

  if(rs.next()){

    //  do anything

}

rs.close();

st.close();

}

라고 작성을 하면 이러한 결과값이 도출한다.

 

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'oraking'

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'javaking'

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'tommy'

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'karajan'

참고로 다 다른 쿼리이다

 

public void login(String login_id) thrwos Exception {

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

  PreapaedStatement st = con.prepareStatement(SQLStmt);

  st.setString(1, login_id);

  ResultSet rs = st.executeQuery();

  if(rs.next()){

    //  do anything

}

rs.close();

st.close();

}

여기서 말하는 "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ? 바인드변수라고 할 수 있다.

이렇게 작성할 경우 성능과 보완이 좋아진다.

 

 

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

 

SQL이 느린 이유는 I/O 때문이다. ( 디스크 I/O )

[ HDD : 속도는 느리지만 안정성이 높다 / SDD : 속도는 빠르지만 안정성이 낮다 ]

성능을 빠르게 하려면 구조를 잘 알아야 한다.

 

책에서는 I/O = 잠(SLEEP) 이라고 표현하는데 그 이유는 

OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스가 잠을 자고 있기 때문이라고 한다.

(자바에서 배운 스레드랑 구조가 비슷하다고 생각한다. 스레드는 사용자가 입력을 하지 않으면 대기를 하는 부분과 동일하다고 생각)

 

 

데이터베이스 저장구조

테이블스페이스 ( 그림을 못그려서 포토샵에서 그렸다)

 

- 블록 : 데이터를 읽고 쓰는 단위 ( I/O 최소단위)

- 익스텐트 : 공간을 확장하는 단위. 연속된 블록 집합 [ 블록 여러 개 묶은 것 ]

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

 [ 테이블 스페이스를 나눈 것 -> 오브젝트 별로 저장하기 위해 ]

- 테이블스페이스 : 세그먼트를 담는 컨테이너 ( 오브젝트를 담을 수 있는 공간 ) [ 여러개 파일을 논리적으로 묶은 것 ]

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

 

익스텐트 단위로 공간을 확장하지만, 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록 

 

블록이 꼭 꽉 차 있는 것은 아니다

 

 

SQL > show parameter block_size

 

NAME                                          TYPE                   VALUE

----------------------------------------- ------------------------- -------------

db_block_size                              integer                   8192 [ 8kb] 

64비트 기준 8kb가 기본단위이다

 

 

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

테이블 , 인덱스 블록을 액세스하는 방식으로는 시퀀셜 액세스와 랜덤액세스 두 가지가 있다.

 

시퀀셜액세스는 연결된 순서에 따라 차례대로 블록을 읽는 방식.

[테이블 전체를 읽는데 편하다]

 

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

[하나하나 읽음. 전체 데이터의 10% ~ 15% 정도에 해당하는 양을 읽을 때 용이하다]

 

 

 

논리적 I/O VS 물리적 I/O

논리적블록 I/O는 캐시에서 읽어오는 것.

물리적블록 I/O는 캐시에 없어서 디스크에서 읽어오는 것

 

메모리 I/O는 전기적 신호인데 디스크 I/O는 액세스 암을 통해 물리적 작용이 일어나므로 메모리 I/O에 비해

느린데 보통 10000배 정도 차이가 난다

 

논리적 I/O인지 물리적 I/O인지도 중요하지만 데이터를 갖고 오는 양이 중요하다.

 

 

 

Single Block I/O VS Multiblock I/O

 

Single Block : 한 번에 한 블록씩 요청해서 메모리를 적재하는 방식

Mulitiblock : 한 번에 여러 블록씩 요청해서 메모리를 적재하는 방식

 

 

Table Full Scan VS Index Range Scan

 

Table Full Scan : 테이블 전체를 스캔해서 읽는 방식

 

Index Range Scan : 인덱스를 이용해서 읽는 방식 [ 데이터를 트리식을 가져온다 ]

보통 Index Scan을 사용한다.

 

Full Scan이 나쁘다고 생각하고, Index Scan이 항상 빠르다고 생각하지만

Full Scan 이 항상 느리고 나쁜 건 아니다.

 

찾는 양에 따라서 Full Scan이 빠른지 Index Scan이 빠른지 달라진다

 

 

캐시 탐색 메커니즘

포토샵에서 마우스로 그리는데 너무 힘들다