데이터베이스 튜닝

[친절한 SQL 튜닝] 1장 ~ 2장 2

피아마수 2024. 4. 1. 00:28

1장

SQL 실행 최적화

 

1. sQL 파싱

1) 파싱트리생성:sQL문을 이루는 개별 구성요소를 통해 트리생성

2) syntax체크:문법오류 체크

3) semantic 체크 : 의미오류 체크

 

2. sQL 옵티마이저

 사용자로 부터 전달 받은 sql문의 실행계획의 예상비용을 산정해서 가장 최저비용을 나타내는 실행계획을 선택한다. 실행계획은 plan키워드를  통해서 확인해볼수있다. 비용은 쿼리를 수행하는동안 발생할 I/O횟수와 예상소요시간을 말한다

 

3. 옵티마이저 힌트

옵티마이저가 가장 최적의 선택을 할 수있도록 개발자가 인덱스를 통해서  힌트를 줄 수있다. 

주의사항

1. 힌트를 나열할때 ,(콤마)로 구분한다.

2.테이블을 지정할때는 스키마명까지 명시하면 안된다.

3. from절에서 alias를 지정했다면 힌트에도 alias를 지정해야한다.

 

sQL공유 및 재사용

 

 

1. 소프트 파싱 과 하드 파싱

- 라이브러리캐시

내부 프로시저를 반복 재사용할수 있도록 캐싱해두는 메모리 공간을 말한다. SGA의 구성요소이다.

-SGA

서버프로세스와 백그라운드 프로세스가 공통으로 사용하는 메모리공간이다.

 

SGA

SQL 실행과정

1.DBMS가 SQL 파싱

2.해당SQL문이 라이브러리 캐시에 있는지 확인

3-1 찾으면실행단계로

3-2 찾지못하면 최적화 단계를 거친다.

 

-소프트 파싱

라이브러리 캐시에서 찾아서 바로 실행하는 것을 말한다.

 

-하드 파싱

저장되어있는 sql이 없어서 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 말한다. 네비게이션의 경로 선택과정과 비슷하게 겪는다

 

2. 바인드 변수의 중요성 

 

-이름 없는 sQL 문제

sql문을 실행하면 SQL문을 통틀어서 sql ID를 생성한다. 이는 sql문 전체가 토시하나라도 틀리면 새로 객체를 생성해서 부여한다. 따라서 여러개의 일회성 sql문을 생성해서 캐시에 저장하고 실행하면 sql찾는 속도가 느려진다

ex ) 쇼핑몰 로그인 문제 : 다른 아이디의 사용자가 자신의 비밀번호를 확인하는 sql문을 돌린다면 다 다른 아이디로 검색하므로 server는 먹통이된다.

해결책 : 프로시저를 통해서 하나의 sql문을 공유하며 재사용하는 것이 바람직 하다

 

jdbc를 사용하는 자바

 

  statement preparedstatement
캐시 미사용 사용
재사용성 X O
SQL Injection 방지 X O

 

 

- preparedstatement 

캐시를 사용해서 해당 쿼리를 저장해 뒀다(프로시저)가 반복 사용할때 다시 사용한다. ?를 통해서 파라미터를 넘기는 방식이기 때문에 SQL Injection을 방지할수있다 정의한 파라미터 형식에 대해서만 값을 넘길수 있기 때문이다.

 

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

 

1. sQL 이 느린 이유

데이터베이스는 I/O가 일어나는 순간에 프로세스가 잠을 자기 때문에  I/O량이 많다면 성능이 저하될 수밖에 없다

 

2. 데이터 베이스 저장구조

데이블 스페이스 > 세그먼트 > 익스텐트 > 블록

 

 

 

- 세그먼트

여러개의 익스텐트로 구성된다. 파티션 인덱스 LOB, 테이블이 들어갈 수있다.

- 익스텐트

공간을 확장하는단위이다. 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족하면 해당 오브젝트가 속한 테이블스페이스로부터 익스텐트를 추가로 할당받는다., 연속된블록의 집합, 한 익스텐트도 하나의 테이블이 독점한다.

- 블록

사용자가 실제로 입력한 레코드가 저장되는 데이터 블록이다. 하나의 블록은 하나의 테이블이 독점한다.

 

* 세그먼트에 할당된 모든 익스텐트가 같은 데이터 파일에 위치하지 않을 수도 있다.

 

-DBA(data block address)

데이터 블록이 디스크상의 몇번째 데이터 파일에 저장되어있는지를 나타내는 블록의 주소이다. 테이블의 레코드를 읽을 때는 ROWID를 이용한다. 이를 이용하면 DBA를 알수있다.

 

- 블록단위 I/O

블록은 데이터베이스가 읽고 쓰는단위이기때문에 특정레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다. 오라클은 기본적으로 8Kb 크기의 블록을 사용한다.

 

4. 시퀀셜 엑세스 와 랜덤 액세스

- 시퀀셜엑세스

논리적으로 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다.

- 랜덤엑세스

논리적, 물리적 순서를 따르지 않고 레코드 하나를 읽기 위해서 한 블록씩 접근하는 방식이다.

 

5. 논리적 I/O 와 물리적 I/O

- DB 버퍼 캐시

데이터를 캐싱하는 DB버퍼 캐시가 있다.

라이브러리 캐시 = 코드 캐시

DB버퍼 캐시 = 데이터 캐시

찾으려는 데이터가 DB 버퍼 캐시에 있다면 CPU는 I/O없이 데이터를불러올 수잇다.

 

- 논리적 블록 I/O

SQL 처리과정중에 발생한 총 블록 I/O를 말한다 일반적으로 버퍼캐시를 경유하므로 메모리 I/O와 논리적 I/O는 동일하다

 

- 물리적 블록 I/O

디스크에서 발생한 총 블록 I/O이다. 버퍼캐시에 존재하지 않을때 디스크에서 데이터를 찾아오는데 이때 발생하는 I/O이다. 물리적 블록 I/O는 실행할때마다 달라진다 왜냐하면 첫번째 실행햇을때 블록 대부분을 가져오므로 두번째 실행때는 캐시에서 가져오면 되기때문이다. 물론 시간이 지나서 다른 SQL문을 실행하고 캐시에 있던 블록들이 바뀌면 첫번째 실행햇을때 와 동일한 I/O가 발생한다.

 

결론 : 물리적 블록 I/O는 통제할수없다 따라서 버퍼캐시의 히트율을 높이려면 논리적 I/O를 줄여야한다.

 

 

6. Single block I/O 와 Multiblock I/O

 

- Single block I/O

한번에 한 블록씩 요청해서 메모리로 가져오는 방식

사용 경우

인덱스 루트 블록을 읽을 때

인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을읽을 때

인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때

인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을때

 

-Multiblock I/O

한번에 여러개의 블록을 요청해서 메모리로 가져오는 방식이다. 읽을때 인접한블록들을 한꺼번에 읽어 캐시에 미리 적재하는 방식이다. 읽어야하는 블록수가 많을 때 유리하다

* 인접한 블록을 읽어 오더라도 익스텐트의 경계는 넘지 못한다.

ex)익스텐트의 20개의 블록이있고 한번에 8개씩 읽어온다면 마지막 4개째에서 4개를 더 읽어오기 위해 옆에 있는 익스텐트를 읽어오진 않는다는 것이다.

 

7. Table full scan 과 Index Range Scan

 

- Table full scan

테이블 전체를 스캔해서 읽는 방식. 시퀀셜엑세스와 multiblock I/O 방식

 

- Index Range Scan

인덱스를 이용해서 읽는 방식.   큰테이블에서 소량검색할때 사용. 랜덤 액세스와 Single block I/O방식

 

- ROWID

테이블 레코드가 디스크 상에 어디 저장됐는지를 가리키는 위치정보이다. 

 

* 충격 : IndexRangeScan이 성능을 떨어뜨리고 table Full scan이 성능상 나을 때가 있다. 따라서 읽을 데이터가 일정량 이상이면 table full scan을 이용하자

 

?? 일정량 이상의 기준이 뭘까???

 

8. 캐시 탐색 메커니즘

DBMS는 버퍼캐시를 해시구조로 관리한다 아래 그림은 5로나눴을때의 나머지 값이 동일한 애들끼리 해시체인을 만들어서 데이터를 분류해서 해시구조로 만든것이다.

해시구조

엑세스 직렬화

버퍼 캐시에 캐싱된 버퍼 블록은 모두 공유자원이다 블록을 읽고 있을 때 누군가 같은 블록을 수정한다면 잘못된 데이터를 불러올 수있다. 따라서 자원을 공유하는 것처럼 보여도 내부에서는 한 프로세스씩 순차적으로 접근하도록 구현 해야 하며 이를 위해 직렬화 메커니즘이 필요하다 이러한 직렬화 메커니즘이 래치이다

ex) 공유경제의 맥락과 비슷하다. 누군가 사용하고 있을 때 다른 사람은 기다려야하며 사용시 실제 사용자는 물품을 혼자만 사용한다.

 

- 래치

해시체인 앞쪽으로 자물쇠가 달려서 key를 획득한 프로세스만이 체인에 접근할수있다.

 

??래치의 경합문제??

 

- 버퍼 lock

래치를 해제한 상태로 블록을 사용하면 후행 프로세스가 같은 블록에 접근해서 변경을 한다면 데이터 정합성문제가 발생할 수있다 따라서 래치를 해제하기 전에 버퍼에 lock을 걸어서 버퍼블록에 대한 직렬화 문제를 해결한다

 

버퍼캐시에서 블록을 읽어오는단계

1. 블록찾기

2. 버퍼 lock

3. 래치 해제

4. 읽고 쓰기

2장

 

인덱스 구조 및 탐색

 

1. 인덱스 튜닝

 

- 인덱스 튜닝의 두가지 핵심요소

1.인덱스 스캔 효율화 튜닝 : 인덱스 스캔과정에서 발생하는 비효율을 줄이는것

2. 랜덤 액세스 최소화 튜닝 : 테이블 엑세스 횟수를 줄이는 것

* 중요 : 랜덤액세스 최소화 튜닝, 성능에 미치는 영향이 크다

 

2. 인덱스 구조

 

- 범위 스캔

인덱스를 사용해서 데이터의 일부만 읽고 멈추는 방식

 

- B*Tree인덱스

DBMS 가 일반적으로 사용하는 인덱스 구조이다. 뿌리, 가지 ,잎사귀 구조이고 루트와 브랜치 블록에 있는 각 레코드는 하위블록에 대한 주소 값을 갖는다

 

-LMC(LeftMost Child)

루트와 브랜치블록에 키값을 갖지 않는 특별한 레코드.

??LMC가 가리키는 주소로 가면 키값을 가진 첫번째 레코드보다 작거나 같은 레코드가 저장되어있다.??

 

- 인덱스의 구성

ROWID를 찾으면 테이블 레코드를 찾아갈수있다.

 

. ROWID = 데이터 블록 주소 + 로우 번호

. 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호

. 블록번호 : 데이터 파일 내에서 부여한 상대적 순번

. 로우번호 : 블록 내 순번

 

3. 인덱스 수직적 탐색 

인덱스 스캔 시작지점을 찾는 과정이다. 루트부터 시작해서 찾으려는 값보다 크거나 같은 값으 ㄹ만나면 바로 직전 레코드가 가리키는 하위 블록(LMC)으로 이동한다. 

 

4. 인덱스 수평적 탐색

데이터를 찾는 과정이다. 인덱스 리프 블록이 양방향 연결 리스트 구조임을 이용하여 좌에서 우로 혹은 우에서 좌로 탐색한다. 인덱스가 필요한 컬럼을 모두 가지고 있다면 인덱스만 스캔하지만 그렇지 않다면 테이블도 스캔해서 데이터를 가져온다.

 

* 주의 : B*Tree인덱스는 엑셀처럼 평면구조가 아니다! 갯수가 다른 인덱스 컬럼 중 뭘 먼저 where절에 검색해도 속도는 같다는이야기.

 

- balanced의 의미

B*Tree의 balanced는 어떤 값을 탐색하더라도 인덱스 루트에서 리프 블록에 도달하기 까지 읽는 블록의 갯수가 동일 함을 의미한다 즉 루트로부터 모든 리프 블록의 높이는 항상 같다

 

 

 

인덱스 기본 사용법

 

 

 

1. 인덱스를 사용한다는것

- Index Range Scan

색인에서 가나다 순으로 정리되어잇을때 앞글자만 가지고 빠르게 원하는 단어를 찾아가는것

- Index Full Scan

색인에서 중간 글자에 해당하는 단어를 찾을때 우리가 모든 단어를 전부 다읽어보고 찾는것

 

2. 인덱스를 Range Scan 할 수 없는이유

"인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수없다." 스캔해야할 데이터의 시작지점과 끝지점이 모호하면 Range Scan을 할 수없다.

 

Range Scan 할 수 없는 경우

1. 5 월의 끝은?

ex) where substr(생년월일, 5,2) = '05'

2. 100보다 작은 것은?

ex ) where nvl(주문수량, 0) <100

3.중간글자 탐색

ex ) where 업체명 like '%대한%'

4. or 연산자의 시작과 끝은?

ex) where (전화번호 = : tel_no or 고객명 =: cust_nm)

 

- OR expansion

or 연산자를 인덱스를 타게 하려면 union all을 통해서 각각 index range scan을 하면된다.

??왜 union all일까?? union은??

select*
from 고객
where 고객명 =: cust_nm 
union all
select *
from 고객
where 전화번호 =: tel_no
and (고객명 <> : cust_nm or 고객명 is null)

 

 

 

- in 조건

in 조건은 or을 다르게 표현한 방식이지만 SQL옵티마이저가 IN-List Iterator를 통해서 UNION ALL 방식으로 변경하여 수행한다. 그래서 or이나 in은 옵티마이저의 변환을 통해서 Index Range Scan으로 처리 될 수 있다.

 

3. 더 중요한 인덱스 사용조건

첫번째 . 인덱스 선두 컬럼이 조건절에 있어야한다. (" 가공되지 않은 상태로 ") 또한 sql을 적절히 구성했더라도 인덱스를 정말 잘 타는지는 인덱스 리프 블록에서 스캔하는양을 따져 봐야한다.

ex ) [소속팀+사원명+연령] 순으로 인덱스가 구성되어있을때 사원명으로 검색을 하면 range scan이 안된다 사원명이 소속팀에 따라서 널리널리 펼쳐져 있을 것이기 때문이다. 

 

4. 인덱스를 이용한 소트 연산 생략

인덱스는 테이블과 달리 정렬이 되어있기때문에 인덱스를 사용하면 정렬의 효과도 얻을 수있다 이것이 소트연산 생략 효과이다

ex) [장비번호 + 변경일자 + 변경순번]순으로 구성한 테이블에서 장비번호와 변경일자로 = 조건을 통해 검색한다면 결과 집합은 변경순번 순으로 출력된다.

정리 : 인덱스로 구성된 애들중 = 연산으로 비교하지 않은 애로 정렬된다.

 

- sort 연산 발생

정렬 연산을 생략할 수있게 인덱스가 구성되어 있지 않다면 아래와 같이 sort order by 연산단계가 추가된다.

 

- 오름차순 정렬

조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적 탐색한 후 우측으로 수평적 탐색을 한다.

- 내림차순 정렬

조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색을 한 수 좌측으로 수평적 탐색을 한다.

 

 

5. order by 절에서 컬럼 가공

order by 절 혹은 select- list 절에서 컬럼을 가공하므로 인해 인덱스를 정상적으로 사용할 수없는 경우도 종종 있다.

 

ex) order by 절을 가공햇을때

select *
from 상태변경이력
where 장비번호 = 'C'
order by 변경일자 || 변경순번

 

ex) select-List절을 가공했을때

order by 절에 기록한 주문번호는 select절에 to_char를 통해서 가공한 컬럼을 가리키기 때문에 sort order by 연산이 나타났다

select *
from (
    select to_char(A.주문번호, 'FM000000') AS 주문번호 , A.업체번호, A.주문금액
    from 주문 A
    where A.주문일자 =: dt
    and A.주문번호 > NVL(:next_ord_no,0)
    order by 주문번호
)
where rownum <=30

 

 

해결책 : 주문번호에 A. 하고 테이블을 명시해주면 정렬연산이 일어나지 않는다.

select *
from (
    select to_char(A.주문번호, 'FM000000') AS 주문번호 , A.업체번호, A.주문금액
    from 주문 A
    where A.주문일자 =: dt
    and A.주문번호 > NVL(:next_ord_no,0)
    order by A.주문번호
)
where rownum <=30