소프트파싱 vs. 하드파싱
- SQL 파싱(SQL문을 컴퓨터가 이해할수있도록 바꾸는 DB 내부 작업 )
- 최적화(Optimize = SQL를 실행할때 가장 적은 비용이 소모되도록 실행계획을 생성 및 선택하는 과정)
- 로우 소스 생성
과정을 거쳐 내부 프로시저가 생성된다. ( 옵티마이저와 로우 소스 생성기가 만든다.) (사용자가 직접만드는 stored procedure랑 다른것이다.)
이렇게 생성된 내부 프로시저는 반복 재사용할 수 있도록 라이브러리 캐시라는곳에 저장된다.
사용자가 SQL문을 전달하면 DBMS는 SQL를 파싱한후 해당 SQL이 라이브러리 캐시에 존재하는지 확인한다.
찾으면 바로 실행단계로 넘어가지만 찾지못하면 최적화 단계를 거친다.
SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 ‘소프트 파싱(Soft Parsing)’이라 하고,
찾는 데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 ‘하드 파싱(Hard Parsing)’이라고 한다.
로우 소스 생성:
옵티마이저가 선택한 실행계획(Execution Plan)을 실제로 실행할 수 있는 하위 연산 단위의 트리(로우 소스 트리) 구조
로 만드는 과정 ( = SQL 실행을 위해 row 단위 연산을 트리 구조로 만드는 과정 )
sql 최적화 과정은 왜 하드 할까?
옵티마이저가 SQL을 최적화할 때 많은 경우의 수를 고려해야한다.
예를들어 다섯개의 테이블을 조인하는 쿼리문 하나를 최적화하는데도 무수히 많은 경우의 수가 존재한다.
조인순서만 고려해도 5!(=120)가지이고 여기다가 NL 조인, 소트머지 조인 등등 다양한 조인방식을 고려해야하며
테이블을 전체스캔할지 인덱스를 이용할지도 결정해야하고 인덱스 스캔에도 Index Range Scan, Index Unique Scan, Index Full Scan 등 다양한 방식이 제공된다.
게다가 사용할 수 있는 인덱스가 테이블당 여러개이니까 수십만 가지 경우의 수가 존재하게 된다.
수십만가지의 실행경로를 도출하고 각각에 대한 효율성을 판단하는 과정은 결코 가벼울 수 없기에 SQL를 최적화하는 과정하고
로우소스를 생성하는 과정은 하드파싱이 된다.
이것이 라이브러리 캐시가 필요한 이유이다.
바인드 변수의 중요성
이름없는 SQL 문제
사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖는다.
컴파일한 상태로 딕셔너리에 저장되며. 사용자가 삭제하지 않는 한 영구적으로 보관된다.
실행할 때 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다.
반면, SQL은 이름이 따로 없다. 전체 SQL 텍스트가 이름 역할을 한다. 딕셔너리에 저장하지도 않는다.
처음 실행할 때 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브 러리 캐시에 적재함으로써
여러 사용자가 공유하면서 재사용한다.
캐시 공간이 부족하면 버 려졌다가 다음에 다시 실행할 때 똑같은 최적화 과정을 거쳐 캐시에 적재된다.
사용자 정의 함수/프로시저는 내용을 수정해도 이름이 변하지 않으므로 같은 프로그램이 무 한 생성되지 않는다.
그런데 SQL은 이름이 따로 없다. SQL 자체가 이름이기 때문에 텍스트 중 작은 부분이라도 수정되면
그 순간 다른 객체가 새로 탄생하는 구조다.
DBMS에서 수행되는 SQL이 모두 완성된 SQL은 아니며. 특히 개발 과정에는 수시로 변 이 일어난다.
일회성(ad hoc) SQL도 많다.
일회성 또는 무효화된 SQL까지 모두 저장하려 면 많은 공간이 필요하고, 그만큼 SQL을 찾는 속도도 느려진다.
오라클, SQL Server 같은 DBMS가 SQL을 영구 저장하지 않는 쪽을 선택한 이유다.
Data Dictionary(데이터 딕셔너리)
:DB 객체와 관련된 모든 정보(메타데이터)를 저장하는 시스템 테이블들의 집합
즉, 테이블, 컬럼, 인덱스, 뷰, 프로시저, 사용자, 권한 등 DB 구조와 객체 정보를 관리하는 곳이다.
공식적으로는 SYS 스키마에 있는 테이블과 뷰사용자에게는 ALL_XXX, USER_XXX, DBA_XXX 뷰로 제공
이름 있는 객체(테이블, 프로시저 등) → 여기에 기록됨
이름 없는 SQL → 여기에 기록되지 않고, 필요할 때마다 캐시에 적재됨
공유가능 SQL
라이브러리 캐시에서 SQL을 찾기 위해 사용하는 키 값이 ‘SQL 문 그 자체’이므로 아래는 모두 다른 SQL이다.
의미적으로는 모두 같지만, 실행할 때 각각 최적화를 진행하고 라이브러리 캐시에서 별도 공간을 사용한다.
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；
500만 고객을 보유한 어떤 쇼핑몰에서 로그인 모듈 담당 개발자가 프로그램을 아래와 같이 작성했다고 하자.
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();
}
이 쇼핑몰에서 어느 날 12시 정각부터 딱 30분간 대대적인 할인 이벤트를 하기로 했다.
500만명 중 20%에 해당하는 100만 고객이 이벤트 당일 12시를 전후해 동시에 시스템 접속을 시도할 경우 어떤 일이 발생할까?
DBMS에 발생하는 부하는 대개 과도한 I/O가 원인인데,
이날은 I/O가 거의 발생하지 않음에도 불구하고 ( LOGIN_ID로 인덱스가 만들어져있다고 가정 )
CPU 사용률은 급격히 올라가고,
라이브러리 캐시에 발생하는 여러 종류의 경합 때문에 로그인이 제대로 처리되지 않을 것이다.
각 고객에 대해 동시다발적으로 발생하는 SQL 하드파싱 때문이다.
그 순간 라이브러리 캐시(V$SQL)를 조회해 보면, 아래와 같은 SQL로 가득 차 있다.
요LECT * FROM CUSTOMER WHERE LOGINJD = 'oraking'
SELECT * FROM CUSTOMER WHERE LOGINJD = 'javaking'
SELECT * FROM CUSTOMER WHERE LOGINJD = 'tommy'
SELECT * FROM CUSTOMER WHERE LOGINJD = 'karajan'
...
...
...
로그인 프로그램을 이렇게 작성하면,
고객이 로그인할 때마다 아래와 같이 DBMS 내부 프로 시저를 하나씩 만들어서 라이브러리 캐시에 적재하는 셈이다.
내부 프로시저 만드는 역할을 SQL 옵티마이저와 로우 소스 생성기가 담당한다고 앞서 설명했다.
create procedure LOGIN_ORAKING( ) { ... }
create procedure LOGINJAVAKING( ) { ... }
create procedure LOGIN_TOMMY( ) { ... }
create procedure LOGIN_KARA3AN( ) {...}
....
....
....
위 프로시저의 내부 처리 루틴은 모두 같다.
그렇다면 프로시저를 여러 개 생성할 것이 아니라
아래처럼 로그인ID를 파라미터로 받는 프로시저 하나를 공유하면서 재사용하는 것이 마땅하다.
create procedure LOGIN (login_id in varchar2 ) { … }
이처럼 파라미터 Driven 방식으로 SQL을 작성하는 방법이 제공되는데, 바인드 변수가 바로 그것이다.
앞서 예를 든 쇼핑몰에서 로그인 프로그램을 아래와 같이 수정하고, 이튿날 이벤트를 다시 실시했다. 그날은 어떤 일이 발생할까?
public void login(String login_id) throws Exception {
String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?";
PreparedStatement st = con.prepareStatement(SQLStmt);
st.setString(1, login_id);
ResultSet rs = st.executeQuery();
if(rs.next()){
// do anything
}
rs.close();
st.close();
}
하드파싱 이외에 다른 문제가 숨어 있었다면 모르겠지만, 그렇지 않다면 할인 이벤트는 순조롭게 진행될 것이다.
그 순간 라이브러리 캐시를 조회해 보면, 로그인과 관련해서 아래 SQL 하나만 발견된다.
SELECT * FROM CUSTOMER WHERE LOGIN_ID = ：1이 SQL에 대한 하드파싱은 최초 한번만 일어나고, 캐싱된 SQL을 100만 고객이 공유하면서 재사용한다.
I/O:
디스크에 데이터를 읽고 쓰는 물리적 IO를 대부분 의미한다.
디스크에서 읽은 데이터가 RAM에 캐싱되어있고 그걸 읽는 논리적 IO라는 개념도 있다. ( = 실제 디스크를 건드리지 않아도 메모리에서 데이터를 읽는 작업)
데이터베이스 저장구조
Oracle 저장 구조
논리적 구조 (Logical Structure)
테이블스페이스
└── 세그먼트 (Segment) - 테이블, 인덱스 등
└── 익스텐트 (Extent) - 연속된 블록 묶음
└── 데이터 블록 (Block) - 최소 I/O 단위
물리적 구조 (Physical Structure)
테이블스페이스
└── 데이터파일 (Datafile) - OS 상의 실제 파일들
예: /oracle/data/users01.dbf
/oracle/data/users02.dbf
테이블스페이스는:
- 논리적으로: 여러 세그먼트를 담는 컨테이너
- 물리적으로: 여러 데이터파일로 구성됨
예시)
[USERS 테이블스페이스]
논리적 관점:
- CUSTOMER 테이블 (세그먼트)
- ORDERS 테이블 (세그먼트)
- IDX_LOGIN (인덱스 세그먼트)
물리적 관점:
- users01.dbf (100MB)
- users02.dbf (100MB)
세그먼트 공간이 부족해지면 테이블스페이스로부터 익스텐트를 추가로 할당받는다
세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 수 있다.
아니, 서로 다른 데이터파일에 위치할 가능성이 더 높다.
하나의 테이블스페이스를 여러 데이터파일 로 구성하면, 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한
여러 데이터파일로 분산해서 저장하기 때문이다
(하나의 세그먼트(논리적 객체)가 물리적으로 여러 데이터파일에 걸쳐 저장되는 모습)
익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아니라는 사실을 그림을 통해 알 수 있다.
핵심 포인트
- 세그먼트는 논리적 개념 (테이블이나 인덱스 같은)
- 데이터파일은 물리적 파일 (OS 상의 실제 파일)
- 하나의 세그먼트는 여러 데이터파일에 분산 저장될 수 있음
예를 들어:
- CUSTOMER 테이블(세그먼트)의 데이터가
- users01.dbf, users02.dbf, users03.dbf 등 여러 파일에
- 익스텐트 단위로 분산되어 저장됨
블록, 익스텐트, 세그먼트, 테이블스페이스, 데이터파일을 간단히 정의하면, 다음과 같다.
• 블록 : DBMS가 데이터를 읽고 쓰는 단위 ( = 데이터 I/O 단위 )
• 익스텐트 : 공간을 확장하는 단위. 연속된 블록 집합
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트（테이블, 인덱스, 파티션, LOB 등）
• 테이블스페이스 : 세그먼트를 담는 콘테이너
• 데이터파일 : 디스크 상의 물리적인 OS 파일
데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다.
심지어 lByte짜리 컬럼 하나만 읽고 싶어도 블록을 통째로 읽는다.
EMP 테이블에 데이터가 저장된 모습을 표현하고 있다.
테이블에 네 개 블록이 할당됐는데, 블록 4에는 현재 데이터가 하나도 입력되지 않은 싱태다.
블록 1〜3까지는 데이터가 입력돼 있고, 아래쪽에 여유 공간이 조금 있다.
오라클은 기본적으로 8KB5 크기의 블록을 사용하므로 IByte를 읽기 위해 8KB를 읽는 셈이다
테이블뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다.
Table Full Scan vs. Index Range Scan
테이블에 저장된 데이터를 읽는 방식은 두 가지다.
테이블 전체를 스캔해서 읽는 방식과 인덱스를 이용해서 읽는 방식이다.
Table Full Scan은 말 그대로 테이블에 속한 블록 ‘전체’를 읽어서 사용자가 원하는 데이터를 찾는 방식이다.
인덱스를 이용한 테이블 액세스는 인덱스에서 ‘일정량’을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식이다.
ROWID는 테이블 레코드가 디스크 상에 어디 저장됐는지를 가리키는 위치 정보다.
인덱스를 통한 조회 (Index Range Scan + Table Access)
1. 인덱스 블록 읽기 (루트 → 브랜치 → 리프)
↓
2. 리프 블록에서 ROWID 찾기
↓
3. ROWID로 테이블 블록 읽기
↓
4. 다음 행도 1~3 반복...
문제점: 테이블 블록 접근이 랜덤 I/O
- 각 행마다 흩어진 테이블 블록을 읽어야 함
- 디스크 헤드가 여기저기 이동 ( 조회해와야할 데이터가 많은 경우 비효율적 )
Full Table Scan
테이블을 처음부터 끝까지 순차적으로 읽음
블록1 → 블록2 → 블록3 → 블록4 → ...장점: 순차 I/O (Sequential I/O)
- 디스크 헤드 이동 최소화
- 멀티 블록 I/O 가능 (한 번에 여러 블록)
- 읽기 속도가 훨씬 빠름 ( 조회해와야할 데이터가 많은 경우 )
Table Full Scan 찾아내기식 실행계획 분석은 실제로 SQL 성능을 향상하는데 큰 도움이 되지 않는다.
인덱스를 사용해야 하는 상황인데 Table Full Scan 하는 경우도 있으므로 전혀 의미가 없다고 할 수 없지만,
Table Full Scan은 피해야 한다는 많은 개발자의 인식과 달리 인덱스가 SQL 성능을 떨어뜨리는 경우도 상당히 많기 때문이다.
한 번에 많은 데이터를 처리하는 집계용 SQL과 배치 프로그램이 특히 그렇다.
Table Full Scan은 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다.
한 블록에 속한 모든 레코드를 한 번에 읽어 들이고, 캐시에서 못 찾으면 ‘한 번의 수면(I/O Call) 을 통해
인접한 수십〜수백 개 블록을 한꺼번에 I/O하는 메커니즘’이다.
이 방식을 사용하는 SQL은 스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다.
시퀀셜 액세스와 Multiblock I/O가 아무리 좋아도 수십〜수백 건의 소량 데이터 찾을 때
수 백만〜수천만 건 데이터를 스캔하는 건 비효율적이다.
큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스를 이용해야 한다
Index Range Scan을 통한 테이블 액세스는 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽는다.
캐시에서 블록을 못 찾으면, ‘레코드 하나를 읽기 위해 매번 잠을 자는 I/O 메커니즘’이다.
따라서 많은 데이터를 읽을 때는 Table Full Scan보다 불리하다.
게다가 이 방식은 읽었던 블록을 반복해서 읽는 비효율이 있다.
많은 데이터를 읽을 때 물리적인 블록 I/O 뿐만 아니라 논리적인 블록 I/O 측면에서도 불리하다는 얘기다.
한 블록에 평균 500개 레코드가 있으면, 같은 블록을 최대 500번 읽는다.
만약 인덱스를 이용해 ‘전체 레코드’를 액세스한다면, ‘모든 블록’을 평균 500번씩 읽게 되는 셈이다.
각 블록을 단 한 번 읽는 Table Full Scan보다 훨씬 불리하다.
데이터베이스를 효과적으로 이용하는데 있어 인덱스의 중요성은 아무리 강조해도 지나치지 않다.
하지만, 인덱스에 대한 맹신은 금물이다. 인덱스가 항상 옳은 것은 아니며, 바꿔 말해 Table Full Scan이 항상 나쁜 것도 아니다.
인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구일 뿐이므로
모든 성능 문제를 인덱스로 해결하려 해선 안 된다.
읽을 데이터가 일정량을 넘으면 인덱스보다 Table Full Scan이 유리하다.
예시) 100만 건 테이블, 10만 건 조회에서 Table Full Scan Vs Index Range Scan
인덱스 엔트리(로우)란
인덱스 테이블 (idx_login):
┌──────────────┬─────────────┐
│ LOGIN_ID │ ROWID │ ← 인덱스 엔트리 (로우)
├──────────────┼─────────────┤
│ 'alice' │ AAA...001 │ ← 인덱스 엔트리 (로우)
│ 'bob' │ AAA...002 │ ← 인덱스 엔트리 (로우)
│ 'charlie' │ AAA...003 │ ← 인덱스 엔트리 (로우)
└──────────────┴─────────────┘
인덱스 스캔(=Index Range Scan)인 경우
인덱스 트리 구성
-> 루트 블록 , 브랜치 블록 , 리프 블록
블록 크기: 8KB (Oracle 기본값) ( = 8,192 bytes ) ( = 대략 하나의 블록 크기 8,000 bytes )
인덱스 엔트리(로우) 크기:
- 인덱스 키: 10 bytes로 가정 (인덱싱 한 컬럼 크기 예시) LOGIN_ID 같은 VARCHAR )
- ROWID: 10 bytes ( 테이블 블록을 찾아가기 위한 주소같은 값 )
- 오버헤드: 약 6 bytes
- 총: 약 26 bytes
리프 블록당 엔트리(로우) 수:
8,000 / 26 = 약 300 엔트리 => 하나의 블록에 들어갈 수 있는 인덱스 엔트리(로우) 갯수
10만 건 조회를 위한 거쳐가야할 리프 블록 수:
100,000 / 300 = 약 333 리프 블록
=> 하나의 인덱스 엔트리는 1건만 조회해올 수 있으니까 ( single block I/O )
10만건 조회를 위해 들려야할 리프 블록의 수는 대략 333개가 된다.
브랜치 엔트리 크기:
- 인덱스 키 값: 10 bytes
- 블록 포인터: 6 bytes
- 오버헤드: 약 4 bytes
- 총: 약 20 bytes
브랜치 블록당 포인터 수:
8,000(한개 블록 대략적 크기) / 20 = 400개
333개 리프를 가리키는데 필요한 브랜치:
333 / 400 = 0.83개 → 1개면 충분!
전체 인덱스 구조(테이블 데이터가 100만 건이고 LOGIN_ID 같은 VARCHAR를 인덱스 컬럼으로 가지는 인덱스):
[루트 1개]
↓
[브랜치 약 10개] -> 브랜치 블록 1개당 대략 400개의 리프 블록을 가리킬 수 있음
↓
[리프 약 3,300개] -> 리프 블록 한개당 300개의 인덱스 엔트리(로우)를 가질 수 있음
3300 * 300 -> 대략 100만건
10만건을 조회하기 위해 거쳐야하는 블록 갯수
브랜치/루트 블록:
- 리프 333개를 가리키는 브랜치: 약 1개
- 루트: 1개
- 총: 333(리프) + 1(브랜치) + 1(루트) ≈ 약 335 블록
인덱스를 이용하여 10만건의 데이터를 조회할때
대략 335개의 인덱스 블록에 접근해야하고 ( 첫 접근만 Physical I/O 그 이후로는 logical I/O )
최악의 경우 10만건의 테이블 블록에 접근해야한다.(= 데이터 각각이 각각 다른 블록에 있는경우)
( Physical I/O )
Clustering Factor 영향으로 이미 들렸던 테이블 블록에 대한 데이터는 캐싱되어있기때문에
이미 들린 테이블 블록에 대해서는 Logical I/O가 발생한다.
10만건 조회인경우
Clustering Factor가 보통이라면 절반인 5만건에 대한 Pysical I/O가 발생한다고 보면된다.
( DB는 블록단위 I/O를 진행한다. 행 하나가 필요해도 그 행이 포함된 블록을 읽어온다.
그래서 Clustering Factor이라는게 있다, 찾아야하는 데이터가 같은 블록에 모여있을수록 Clustering
factor의 이점이 있다.)
총 블록 읽기: 335(인덱스 블록) + 50,000~70,000(테이블 블록) = 50,335~70,335 블록
대략 5만~ 7만회 Physical I/O가 발생한다.
( 인덱스 루트,브랜치 블록은 자주 접근되므로 한번 들린후 항상 버퍼 캐시에 상주한다. )
Clustering Factor 나쁜 경우:
- 10만 개 행이 각각 다른 블록에 흩어짐
- 10만 번의 랜덤 Physical I/O 발생
- 랜덤 I/O는 매우 느림 (디스크 헤드 이동)
Clustering Factor 예시)
10만 건을 조회할 때
CF 좋음: 같은 블록에 여러 행이 모여있음
블록 #100: [a001, a002, a003, ..., a080] ← 80건
블록 #101: [a081, a082, a083, ..., a160] ← 80건
...
10만 건이 1,250개 블록에 밀집되어 있다면
→ 1,250 Physical I/O
CF 나쁨: 각 행이 다른 블록에 흩어짐
블록 #100: [a001, z999, x888, ...] ← 1건만 필요
블록 #555: [k777, a002, j666, ...] ← 1건만 필요
블록 #892: [a003, ...] ← 1건만 필요
...
10만 건이 거의 10만개 블록에 분산
→ 10만 Physical I/O
CF 보통: 평균 2건 정도가 같은 블록에
블록 #100: [a001, a002, z999, ...] ← 2건 필요
블록 #200: [x888, a003, a004, ...] ← 2건 필요
블록 #300: [a005, a006, k777, ...] ← 2건 필요
...
10만 건이 5만개 블록에 분산 (평균 2건씩)
→ 5만 Physical I/O
----
일반적으로 Primary Key Index가 CF가 좋고,
나머지는 보통~나쁨이라고 한다.
추가로 고려해야할 점
HDD (하드디스크):
- 랜덤 I/O와 순차 I/O 차이가 매우 큼 (10~100배)
- Full Scan이 더 유리해지는 시점이 빠름
SSD (솔리드 스테이트):
- 랜덤 I/O와 순차 I/O 차이가 작음 (2~3배)
- Index Scan이 유리한 범위가 더 넓음
Full Table Scan인 경우
테이블: 100만 행
블록당 80행 저장 ( 위의 계산 참고 )
전체 블록 수: 1,000,000 / 80 = 12,500 블록 ( 테이블 블록 )
처음부터 끝까지 순차적으로 스캔:
블록 #1 → 80행 중 조건 만족하는 행만 선택
블록 #2 → 80행 중 조건 만족하는 행만 선택
블록 #3 → 80행 중 조건 만족하는 행만 선택
...
블록 #12,500 → 마지막까지
특징:
- 순차 I/O (Sequential I/O)
- 멀티 블록 I/O (한 번에 여러 블록)
- Clustering Factor 영향 없음 (어차피 전체 읽음)
- Physical I/O: 12,500 블록 / 멀티블록 계수
예: db_file_multiblock_read_count = 16
→ 실제 I/O 횟수: 12,500 / 16 = 약 781회
총 블록 읽기: 12,500 블록 (고정)
하지만 Multi Block I/O 방식이므로 Pysical I/O는 대략 781회 발생한다.
위의 예시에서 Index Range Scan은 CF가 좋다고해도
335(인덱스 블록) + 1250 (테이블 블록) = 1500번쯤의 Physical I/O가 발생한다.
거기다가 Random Access는 Sequential Access (순차) 보다 디스크헤드가 움직이는 시간이 10배~100배정도 더 발생한다.
( = HDD인 경우에 10배~100배 , SSD인 경우에는 전자적접근으로 더 빨라져서 2배~3배 정도 차이가 난다. )
하드디스크가 HDD이고 헤드 움직이는 시간을 1ms 로 가정하고 Primary Key Index라서 CF가 좋다고 했을때
Index Range Scan의 경우 1500번의 물리적 I/O * 1ms * 10 해서 15000ms가 소요되고
Full scan의 경우 781회의 물리적 I/O * 1ms 해서 781ms가 소요된다.
즉 하나의 테이블의 전체 데이터가 100만건이고 그 10%를 조회하려고할때는 full scan이 더 빠르다.
하지만 이건 단일테이블인 경우 예시이고 Join이 들어가는 순간 더 복잡해진다.
그러나 실무에서는 데이터를 조회하려고 할때는 index가 대부분 몇배는 더 빠르다.
정리
┌─────────────────────────────────────────────┐
│ 실무 인덱스 판단 기준 (단일 테이블) │
├─────────────────────────────────────────────┤
│ │
│ 조회 건수 < 1,000건 │
│ → 무조건 인덱스 사용 ✅ │
│ │
│ 조회 건수 1,000 ~ 10,000건 │
│ → 상황 봐야 함 (실행 계획 확인) ⚠️ │
│ │
│ 조회 건수 > 10,000건 │
│ → Full Scan 고려 🤔 │
│ │
└─────────────────────────────────────────────┘
비율로 보면:
- 데이터 갯수가 작은 테이블(< 10만): 1% 미만 → Index Range Scan
- 데이터 갯수가 큰 테이블(> 100만): 0.5% 미만 → Index Range Scan
SQL에 Join이 들어가는 순간 Join 방식이 Index Range Scan / Table Full Scan을 결정한다.
위의 정리는 단일테이블 기준이니까 참고만 할것
댓글