NL (Nested Loop Join)

 

인덱스 사용자체를 기반으로 하는 조인 방식이다.

 

 

조인 수행 절차

하나의 테이블을 기준으로 각 row를 추출할 때마다 순차적으로 상대 테이블의 연관된 모든 row들을 조인에 의해 추출한다.

 

 

수행절차로부터 알 수 있는 튜닝 포인트에는 2가지가 있다.

 

1) 테이블사이에 조인이 발생하니 조인의 수를 최소화 하는것이 중요하다.

    그걸 위한 차원에서 조인순서(driving)가 중요하다.

2) 연결되는 테이블은 반드시 연결고리 칼럼에 대한 인덱스가 사용되어야 한다.

    조인조건에 대한 인덱스가 구성되어 있어야 하고, 그것이 사용되어야 하는 것이 중요하다.

 

 

 

특징

1. 인덱스에 의한 랜덤 액세스에 기반을 두며 인덱스 사용을 하는 이상 대용량 데이터를 처리하게 될때는 I/O로 인해서 문제가 발생될 가능성이 있다는 것이 특징이다.

2. Driving이 중요하다. Driving Table 이란 데이터를 먼저 읽어들일 테이블을 의미하는데, 이 테이블은 테이블 자체의 데이터가 적게 있거나, where절을 통해서 그 어떤 테이블보다도 데이터를 적게 읽어들일 수 있는 테이블이 Driving table로 선택되어야 한다. 

3. Driven Table(연결되는 테이블)은 반드시 조인조건에 대해서 사용가능한 인덱스가 구성되어야 하고, 그 인덱스가 반드시 사용되도록 하는것이 중요하다고 할 수 있다.

 

 

---

데이터베이스 I/O 

 

Database는 데이터를 블록(Block) 단위로 읽고 저장한다. 오라클의 경우는 기본 블록 사이즈가 8kb이다. 즉, database가 아주 작은 데이터를 가져온다고 하더라도 최소한 8kb의 블록을 읽는다. Database의 튜닝에서 가장 중요한 것은 바로 이 블록 단위 I/O를 줄이는 것이다.

 

디스크 I/O : 디스크의 액세스 암이 움직이면서 헤드를 통해 데이터를 읽고 쓴다.
메모리 I/O : 전기적 신호

 

디스크 I/O를 통한 입출력은 메모리를 통한 입출력보다 평균적으로 10,000배 이상 느리다.

 

메모리는 물리적으로 한정된 자원이므로, 디스크 I/O를 최소화하고 버퍼 캐시 효율을 높이는 것이 데이터베이스 I/O 튜닝의 목표가 된다.

 

 

버퍼 캐시

DB 버퍼 캐시는 '데이터 캐시'라고 할 수 있다. 디스크에서 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄일 수 있다.

 

 

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

논리적 I/O는 SQL을 처리하는 과정에서 Buffer Cache에서 발생한 총 블록 I/O를 말한다. 물리적 I/O는 디스크에서 발생한 I/O를 말한다. SQL을 처리하면서, 찾고자 하는 데이터가 없을 경우, 디스크를 액세스하는 경우가 물리적 I/O이다. 

 

 

버퍼캐시 히트율

버퍼 캐시 히트율은 읽은 블록 중에서 얼마만큼을 메모리 버퍼 캐시에서 찾았는지를 나타내는 것 이다.

 

논리적 I/O는 데이터의 변경이 없다면 같은 SQL을 몇 번 수행해도 같지만 물리적 I/O는 같은 SQL을 여러 번 수행할수록 줄어든다. 실행할수록 DB 버퍼 캐시에서 해당 테이블 블록의 점유율이 높아지기 때문이다.

논리적인 블록 요청 횟수를 줄이고, 물리적으로 디스크에서 읽어야 할 블록 수를 줄이는 것이 I/O 효율화 튜닝의 핵심 원리이다.

1.물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다.

2. 버퍼캐시 히트 비율이 낮으면 SQL 성능이 떨어진다.
3. BCHR가 100%라고 하더라도 논리적으로 읽어야 할 블록 수의 절대량이 많다면 반드시 SQL 튜닝을 실시해야 한다.

 

애플리케이션 특성에 따라 다르지만, 온라인 트랜잭션을 주로 처리하는 애플리케이션이라면 시스템 레벨에서 평균 99% 히트율을 달성해야 한다. 

 

 

I/O 효율화 원리

논리적인 I/O 요청 횟수를 최소화하는 것이 I/O 효율화 튜닝의 핵심 원리다.

 

- 필요한 최소 블록만 읽도록 SQL 작성 (필요한 컬럼만 SELECT)

- 최적의 옵티마이징 팩터 제공

  1) 전략적인 인덱스 구성
  2) DBMS가 제공하는 기능 활용(인덱스, 파티션, 클러스터, 윈도우 함수 등)
  3) 옵티마이저 모드(전체 처리속도 최적화, 최초 응답속도 최적화) 설정
  4) 통계정보

- 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도 (힌트 사용)

 

 

참조

https://insight-bgh.tistory.com/501

[Database] 데이터베이스 I/O 원리 및 최적화