01:48 2017-01-04 수
권순용의 실전 SQL Tuning 3강
– 단일 컬럼 인덱스가 많으면 랜덤/?
– 유연성이 강한 결합 컬럼 인덱스를 생성해야 한다.
1. 인덱스의 개념
2. 랜덤 엑세스
– 인덱스의 구조
– 랜덤 엑세스란?
– 랜덤 엑세스의 영향은?
ㅁ 인덱스
데이터 베이스의 인덱스는 사전의 인덱스와 동일하다.
사전의 인덱스가 단어의 조회를 위해 존재하듯이
데이터베이스의 인덱스 또한 테이블에 저장되어 있는 데이터를 보다 빠르게
엑세스하기 위해 필요하다.
ㅁ 랜덤 엑세스
랜덤 액세스는 한 번 액세스시 한 번의 I/O를 발생시키게 된다.
이는 단일 블록 I/O 를 수행하기 때문이며 이와 같은 현상은 인덱스를
엑세스한 후 테이블을 엑세스하는 경우에 발생한다.
ㅁ 추출 랜덤 엑세스
select 절의 컬럼 값을 추출하기 위해 인덱스 엑세스 후
테이블을 엑세스하는 과정에서 발생하는 랜덤 엑세스이다.
ㅁ 확인 랜덤 엑세스
Where 절의 컬럼이 인덱스에 존재하지 않아
Where 절의 컬럼을 확인하기 위해 테이블을 엑세스하는 경우 발생하는 랜덤 엑세스이다.
1. 인덱스의 개념
ㅁ 인덱스의 장단점
장점 : 조회 속도 향상
단점 : 장점 외에는 거의 단점 밖에 없다. DML 성능 저하(Insert, Update, delete), 세그먼트 이므로 별도의 저장공간 차지 (디스크 공간 낭비)
잘못된 인덱스 지정은 공간 낭비 + 조회 성능 저하
ㅁ B* 트리 인덱스 ]비 스타 트리 인덱스]
루트 블록 : 가장 위에 위치. 인덱스의 루트. 루트를 통해서만 모든 인덱스 엑세스 가능. 인덱스의 삽입/변경은 루트를 통해 수행됨.
분기 값과 블록 주소 (DBA, Data Block Address)를 가지게 됨
브랜치 블록 : 중간에 위치. 루트 블록와 리프 블록을 연결함. 분기 값과 블록 주소 (DBA, Data Block Address)를 가지게 됨
리프 블록 : 가장 아래에 위치. 인덱스 키 값과 ROWID로 구성됨. (인덱스 키값 == 컬럼의 값, ROWID == 데이터의 주소)
– 더블 링크드리스트 : 리프 블록을 연결하는 포인터. 리프 블록끼리는 더블 링크드리스트로 통신하므로, 인덱스 범위(Range) 스캔 등에 이용하게 됨
ㅁ ROWID란?
데이터를 찾아가는 주소.
각 데이터는 서로 다른 ROWID 가짐 (유니크)
인덱스를 이용한다는 의미 == 인덱스를 엑세스하여 ROWID를 획득하고, 그것으로 테이블을 엑세스한다.
구성요소 : 오브젝트 번호(6자리, 32bit), 파일번호(3자리, 10bit), 블록번호(6자리, 22bit), 로우 번호(3자리, 16bit)
위 4개의 요소로 ROWID가 구성된다. (그러므로 유니크)
ex) AAAAAC AAB AAAAAX AAD
ㅁ 인덱스의 사용
인덱스를 사용하면 안되는 SQL도 매우 많으므로 주의 (엄청난 성능저하 발생)
SQL Tuning != 인덱스 사용 (동치가 아니다.)
– 적은 데이터 결과를 엑세스 하는 경우 반드시 인덱스 이용
– 많은 데이터 결과를 엑세스 하는 경우 FULL SCAN을 이용
ㅁ 인덱스 선정 절차
1. 테이블 아키텍처 선정 : 일반 테이블, 파티션 테이블 및 IOT(Index Organized table) 선정
2. 엑세스 Path 검토 : 해당 테이블을 엑세스하는 SQL 수집
3. 결합 컬럼 인덱스 고려 : 단일 컬럼 인덱스가 아닌 결합 컬럼 인덱스 고려
4. 인덱스 선정 : 엑세스 Path를 기준으로 여러 컬럼으로 구성된 결합 인덱스 선정
5. 선정된 인덱스 검증 : 인덱스에 대한 확인 및 테스트
2. 랜덤 엑세스
– 개념, 미발생/발생, 종류, 감소
ㅁ 랜덤엑세스의 개념
(1) 랜덤엑세스는 인덱스 스캔 후 테이블 엑세스하는 순간 랜덤 엑세스 발생
(2) 두 테이블 사이에 데이터 연결 단계에서 중첩 루프 조인을 사용할 경우 발생
Full Scan은 다중블록 I/O를 수행하지만,
인덱스는 랜덤 엑세스, 즉 단일블록 I/O를 수행하므로 많은 데이터를 엑세스하는 경우 FullScan이 더 빠르다.
데이터가 n건 연속으로 존재해도 인덱스는 ROWID로 찾아가기 때문에 그렇다.
– 인덱스 스캔 자체는 랜덤 엑세스를 발생시키지 않음.
– 인덱스 스캔 후 테이블 엑세스에서 랜덤 엑세스 발생.
ㅁ 랜덤엑세스 미발생
SELECT * FROM ~만 있고 WHERE가 없다면 Full Scan이므로 랜덤 엑세스 미발생.
ㅁ 랜덤엑세스 발생
WHERE 문에 특정 컬럼 조건을 넣었고, 특정 컬럼에 대한 인덱스가 존재할 경우.
단일 블록 I/O를 발생시키는 엑세스를 랜덤 엑세스라고 한다.
ㅁ 랜덤 엑세스 종류
(1) 추출 랜덤 엑세스 : 테이블 엑세스하는 과정에서 발생하는 랜덤 엑세스
SELECT col1, col2 FROM tab1 WHERE col3=’AAA’, col4=’BBB’
– col3+col4 인덱스를 사용시 : col1, col2때문에 인덱스 엑세스 후 테이블(tab1) 엑세스함. 이때 발생되는게 바로 추출 랜덤 엑세스.
– col3+col4+col1+col2 인덱스 생성시 추출 랜덤 엑세스 제거됨
(2) 확인 랜덤 엑세스 : WHERE 절의 컬럼이 인덱스에 존재하지 않아서 테이블을 엑세스하는 과정에서 발생
SELECT col3 FROM tab1 WHERE col3 = ‘AAA’ AND col4 = ‘BBB’ AND col5 like ‘AA%’
조건을 만족하지 않을 경우 랜덤엑세스 수행 결과를 버림 (버리는 과정이 동반되므로 다른 랜덤 엑세스 비해 더 많은 성능저하 발생될 수 있다.)
– col3+col5를 이용한다면 col4 를 확인하기 위해 테이블 엑세스를 함
– col4 확인 결과 조건에 안 맞으면 버리게 됨
– 예제에서는 col3+col4+col5 인덱스를 생성한다면 모두 존재하므로 확인 랜덤 엑세스가 모두 제거됨
(3) 정렬 랜덤 엑세스
SELECT a.col3 FROM tab1 WHERE col = ‘AAA’ and col4 = ‘bbb’ order by col5;
– Order By 절의 컬럼값을 확인하기 위해 인덱스 엑세스 후 테이블을 엑세스하는 과정에서 발생
– 추출랜덤 엑세스 처럼, 랜덤 엑세스 건수와 추출되는 데이터 건수가 동일함.
(오직 확인 랜덤 엑세스만이, 엑세스 건수에 비해 추출되는 데이터가 적을 수 있음)
– col3+col4 인덱스 이용시 col5 컬럼이 인덱스에 없으므로 col5 확인을 위해 랜덤 엑세스 발생 (정렬 랜덤 엑세스)
– 해당 SQL에 col3+col4+col5 인덱스 이용시 정렬 랜덤 엑세스 제거됨
ㅁ 랜덤 엑세스 감소 방법
(1) 클러스터 팩터 최적화 : 엑세스하고자 하는 데이터 모아서 저장 (랜덤 엑세스 발생 감소)
ex: 카드회사 솔루션이라면 카드번호 순으로 데이터를 쌓아두면 연속값이 존재해서 랜덤 엑세스 감소할 수 있음
(2)올바른 인덱스 선정 : 확인 랜덤, 추출 랜덤, 정렬 랜덤 엑세스의 감소
주로 (2)가 많이 사용되는 방법임
특히 확인랜덤 엑세스를 줄이는게 성능에 큰 도움이 됨.
ㅁ 랜덤 엑세스와 인덱스 스캔과의 관계
– 인덱스 스캔 자체는 랜덤 엑세스 발생시키지 않음 (인덱스 스캔으로 SQL 종료시 랜덤 엑세스 미발생)
– 스캔 후 테이블 엑세스할 때 랜덤 엑세스 발생됨
많은 데이터 결과를 추출하는 경우 : Full Scan이 유리
ㅁ 보충 및 심화학습
SEELCT col1, col2, col3, col4, col5
FROM test
WHERE dt BETWEEN ‘20080701’ AND ‘20080730’;
– dt는 인덱스에 존재해도, col1, col2, col3, col4, col5 컬럼이 인덱스에 존재하지 않을 경우 랜덤 엑세스 발생
– 테이블 전체가 2개월의 데이터만 존재하고 대용량 테이블일 경우
위 쿼리는 전체의 50%를 조회하고 있음(1개월치를 조회). 풀스캔이 더 빠른 상황
employees 테이블에 emp_idx1 이름으로 SALARY 컬럼 인덱스를 생성해보세요.
CREATE INDEX EMP_IDX1 ON EMPLOYEES(SALARY);
CREATE INDEX 인덱스이름 ON 테이블명(컬럼명 [desc|asc]); => 비 스타트리 인덱스가 만들어진다.
ㅁ SQL의 실행 계획에서 랜덤 엑세스를 의미하는 부분은 다음 중 어디일까요?
(1) SELECT STATEMENT
(2) TABLE ACESS BY INDEX ROWID | EMPLOYEES
(3) INDEX RANGE SACN | EMPLOYEE_IDX4
답: (2)
해설 :
SET AUTOT TRACEONLY EXP select * from 테이블명
하면 실행계획을 볼 수 있다.
인덱스를 엑세스한 후 인덱스로부터 로우 아이디를 획득하여 테이블을 엑세스하는 단계를, 랜덤 엑세스라고 한다.
ㅁ 인덱스를 이용해야 하는 SQL 과 이용하지 말아야 하는 SQL의 기준은 무엇일까요?
엑세스하는 데이터의 양에 의해 좌우된다. 해당 테이블의 많은 데이터를 엑세스한다면 인덱스를 이용해서는 안된다.
왜냐하면 인덱스를 엑세스한 후 테이블을 엑세스하는 랜덤 엑세스가 발생하게 되며
랜덤 엑세스의 양이 많을수록 엄청난 성능 저하를 발생시키기 때문이다.
ㅁ마무리 정리
1. 인덱스의 구조는 인덱스 컬럼의 값과 ROWID로 구성된다.
2. ROWID는 인덱스에서 테이블의 데이터를 찾아가는 주소의 역할을 한다.
3. 랜덤 엑세스는 인덱스 엑세스 후 테이블을 엑세스 하는 경우 주로 발생하게 된다.
4. 종류에는 확인 랜덤 엑세스, 추출 랜덤 엑세스, 정렬 랜덤 엑세스가 있다.
* 확인 랜덤 엑세스는 엑세스 건수보다 추출 건수가 적을 수 있음 == 버리는 로직 발생.
5. 랜덤 엑세스는 성능 저하의 주범이며, 랜덤 엑세스를 최소화하기 위해서는 인덱스 선정이 매우 중요한다.
퀴즈
1. 데이터베이스에서 지원하는 인덱스 중 B*Tree 구조를 지원하지 않는 인덱스는?
(1) B*Tree 인덱스
(2) Function base 인덱스
(3) Bitmap 인덱스
(4) Reverse Key 인덱스
답: 3
B*Tree 구조를 사용하는 Index는 B*Tree 인덱스, Function Base 인덱스, Reverse Key 인덱스가 존재.
Bitmap 인덱스는 B*Tree 인덱스와 유사하지만, 비트맵을 이용하게 된다.
2. 인덱스의 단점이 아닌 것은?
(1) DML 성능저하
(2) 잘못된 인덱스는 저장공간 낭비
(3) 잘못된 인덱스는 성능 저하 발생
(4) 디스크 속도 저하 발생
답 : 4
참고로 장점은, 잘 만든 인덱스는 조회 성능 향상.
3. 랜덤 엑세스 중에 가장 성능 저하를 만힝 발생시키는 랜덤 엑세스는?
-> 확인 랜덤 엑세스.
(버리는 과정이 동반될 수 있기 때문임).
02:56 2017-01-04 수