불친절 SQL 1장 정리

 

 

 

1. SQL이 느린 이유 

 

-> 디스크 I/O 때문임! (I/O 처리하는 과정에서 프로세스가 잠을 자기 때문)

    여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있다.

    디스크에서 데이터를 읽어야 할 때 CPU를 OS에 반환하고 수면 상태에서 I/O 완료를 기다림 

---

2. 데이터베이스 저장 구조(오라클)

 

-> 테이블 스페이스 : 여러 세그먼트를 담음 

                                  세그먼트는 여러 카테고리가 있음 (테이블, 인덱스, 파티션 등)

 

*세그먼트, 익스텐트,블록은 하나의 테이블에 대한 정보임

   -> 세그먼트 <- 익스텐트 <- 블록의 포함관계가 있을 뿐 모두 하나의 테이블을 나타냄 

 

-> 세그먼트 : 여러 익스텐트를 담는 저장 공간 

                   

-> 익스텐트 : 여러 블록으로 이루어짐 

                      데이터를 저장할 블록이 꽉차면 블록 단위로 공간을 확장하는게 아니라, 

                      익스텐트 단위로 공간을 확장함 

-> 블록: 여러 레코드로 구성됨 (여러 행으로 구성) 

              여기서 레코드는 사용자가 입력한 (INSERT)한 값임 

               한 블록은 하나의 테이블이 독점함 (한 블록에 저장된 레코드는 모두 같은 테이블)

 

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

-> 즉, 위에서 설명한 저장구조는 논리적으로 같은 테이블을 가리키지만, 물리적으로 실제 저장될 때는 다른 위치에 저장될 수 있다. 

(ex 익스텐트 1과 익스텐트 2는 같은 세그먼트에 할당되었지만, 익스텐트1의 내용은 파일1.db에 익스텐트2의 내용은 파일2.db에 저장될 수 있음)

---

3. DBA

 

모든 데이터 블록은 실제 물리적으로 저장된 위치를 가리키는 고유 주소값을 갖는다. 이를 DBA라고 부른다. 

추후 살펴볼 인덱스의 ROWID가 DBA+로우번호로 구성된다. 

즉, DBA는 물리적으로 저장되어있는 블록의 위치(ex C드라이브에 파일1.db) 로우번호는 해당 블록에서 순번이다.

 

*테이블을 스캔할 때 세그먼트 헤더에 저장된 익스텐트 맵을 이용한다(세그먼트에 할당된 익스텐트에 대한 정보)

익스텐트 맵을 통해 해당 익스텐트 첫 블록에 DBA를 알 수 있다.

---

4. 블록 단위 I/O

 

DB에서 데이터를 읽고 쓰는 단위는 블록이다. 하나의 데이터만 읽고 싶어도 블록 전체를 다 읽어야한다.

인덱스 또한 블록 단위로 데이터를 읽고 쓴다.

 

4.1 시퀀셜 엑세스 vs 랜덤 엑세스

 

시퀀셜 엑세스

- 논리적 혹은 물리적으로 연결된 순서에 따라 블록을 읽는다 (블록1 -> 블록2 -> 블록3)

-DB를 순차적으로 스캔하는 방식이다. (full scan 방식)

 

랜덤 엑세스 

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

 (블록1 -> 블록 4 -> 블록 10)

- INDEX ROWID를 통해 테이블에 접근할 때 랜덤 엑세스가 발생한다. 

---

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

 

논리적 I/O

논리적 블록 I/O는 SQL을 처리하는 과정에서 발생한 총 블록 I/O를 의미한다. 

 

 

물리적 I/O

물리적 블록I/O는 디스크에서 발생한 총 블록 I/O를 의미한다. 

블록을 버퍼캐시에서 찾지 못할 때만 디스크를 엑세스하므로 논리적 블록 I/O중 일부를 물리적 I/O라 한다.

 

 

*논리적 I/O는 같은 조건절이라면 매번 결과가 같지만, 물리적 I/O는 다를 수 있다 -> 이는 물리적 I/O를 하며, 읽은 블록을 버퍼캐시에 저장하기 때문이다 -> 즉, SQL을 날리면 일단 버퍼 캐시를 먼저 확인하고, 여기에서 SQL에서 SELECT한 데이터를 담은 블록을 찾지 못하면 물리적 I/O를 진행한다.

 

 

*DB 튜닝의 핵심은 전체 I/O를 의미하는 논리적 I/O 횟수를 줄여 물리적 I/O를 줄이는 것이다. 

 

---

 6. Single Block I/O vs MultiBlock I/O

 

Single Block I/O

 

• 한 번에 한 블록씩 요청에서 메모리에 적재하는 방식이다.
• 대량의 데이터에서 소량을 찾을 때 효율적이다.
  • 인덱스 루트 블록을 읽을 때 
  • 인덱스 루트 블록을 통해 블랜치 블록을 읽을 때 
  • 브랜치 블록을 통해 리프 블록을 읽을 때 사용된다.

MultiBlock I/O

• 한 번에 여러 블록씩 메모리에 적재하는 방식이다.
• 대량의 데이터 블록을 읽을 때는 이것이 훨씬 효율적이다 -> 테이블 전체 스캔 
• 캐시에서 찾지 못한 특정 블록을 읽기 위해 I/O Call을 수행할 대 인접 블록을 한꺼번에 적재한다.

 

*대량의 데이터를 Single Block I/O로 읽는다면 그만큼 대기 시간이 소모될 것이다.

   -> 이때는 MultiBlock가 낫다 한번에 많이 읽고 대기하면 된다.

 

---

7. Table Full Scan vs Index Range Scan

 

- Table Full Scan (시퀀셜 엑세스 + MultiBlock I/O)

   > 테이블에 속한 블록 전체를 읽어서 데이터를 찾는다.

   > 대량의 데이터를 찾을 때 유리하다

 

- Indexd Range Scan (랜덤 엑세스 + SingleBlock I/O)

   > 인덱스에서 일정량을 스캔하며 얻은 ROWID로 데이터를 찾아간다. 

 

*읽어야할 데이터가 많을 수록 혹은 테이블의 데이터가 많을 수록 Index가 느린 이유(면접 사골)

- Index를 이용한 스캔은 랜덤 엑세스 + Single Block I/O 방식을 활용하기 때문이다. 

   -> 애초에 인덱스를 사용하는 이유는 많은 데이터 중 필요한 일부를 빠르게 찾기 위함이다.

 

- 랜덤 엑세스이자 Single Block I/O 방식이므로,  논리적 혹은 물리적으로 저장된 순서대로 데이터 블록을 한꺼번에 읽어     오는게 아니라, ROWID를 통해 한 번에 한 개씩 블록을 읽어오므로, 이 과정에서 많은 I/O가 발생한다 

    ->  많은 I/O는 프로세스의 많은 대기시간을 유발한다.

 

- 따라서 인덱스를 통해 읽어야할 데이터가 많아진다면, 그만큼 속도가 떨어질 수 밖에 없다.

 

- 이럴때는 테이블을 한번에 읽어서 순차적으로 데이터를 처리하는 편이 훨씬 빠르다.

   -> Table Full Scan이 시퀀셜 엑세스에 MultiBlock I/O 방식이므로, 간혹 풀 스캔이 인덱스보다 빠른 이유는 여기에 있다고 할 수 있다.

 

---

8. 캐시 탐색 매커니즘

 

대부분의 블록 I/O는 버퍼캐시를 경유한다. (인덱스를 사용하든, Full Scan하든)

 

DBMS의 버퍼캐시는 

이 따위로 생겼다.

 

1. 모듈러(나머지연산)을 통해 블록을 해시 체인에 먼저 연결한다. (같은 블록 번호는 항상 같은 해시 체인에 연결 - 정렬은x)
2. 블록을 탐색할 때 버퍼를 모두 full scan 하는게 아니라, 해시 체인에 연결을 먼저 확인한다 (ROWID에 블록 번호가 있으니 이걸로 나머지 연산을 해서 해시체인 번호를 선택하고 이를 쭉 읽는다고 생각하자)
3. 해시 체인에 찾고자하는 블록이 없으면 물리적 I/O를 통해 이를 읽어오고 해시체인에 연결한다.
4. 해시 체인에 찾고자하는 블록이 있으면, 해당 정보로 버퍼 블록을 찾아가서 블록을 읽는다

* 같은 블록 번호는 항상 같은 해시체인에 연결되지만, 항상 같은 위치의 버퍼 블록에 연결되는 것은 아니다

> 버퍼블록은 계속 지워졌다 채워졌다함! 항상 같은 위치에 저장될 수 없음 (버퍼 블록 != 해시체인)

> 해시 체인은 버퍼블록을 더 효율적으로 탐색하기 위한 장치일 뿐!

 

---

9. 체인 래치

 

블록을 탐색할 때 해시 체인 구조에 변경이 생기면 안된다.

따라서 해시 체인에 Lock을 건다 이를 체인 래치라 부른다. (한 프로세스가 독점)

읽고자하는 블록을 찾으면 Lock를 곧 바로 해제한다. 

이때 Lock을 해제하는 순간에 같은 블록에 데이터를 읽고 쓴다면 데이터 정합성 문제가 발생할 수 있다.

따라서 오라클은 버퍼에도 Lock을 건다. 

체인 래치를 해제하기 전에 버퍼에도 Lock을 걸어 블록 버퍼에 대한 접근을 일시적으로 막는다.