🖥️ 들어가며
📌 I/O 튜닝의 핵심 원리
- Sequential Access의 선택도를 높인다.
- Random Access 발생량을 줄인다.
👉🏻 인덱스 컬럼 추가에 따른 클러스터링 팩터 변화
| 구분 | 변경 전 | 변경 후 |
| 인덱스 | 부서코드 (단일 컬럼 인덱스) |
부서코드 + 성명 (복합 인덱스) |
| 인덱스 정렬 순서 | 부서코드 + RowID | 부서코드 + 성명 + RowID |
| 테이블 정렬 순서 | 부서코드 + RowID | 부서코드 + RowID |
| 클러스터링 팩터 | 매우 낮음(좋음) (인덱스 순서와 테이블 순서가 거의 일치) |
높음(나쁨)* *높아질 가능성이 높음 (인덱스 순서와 테이블 순서 불일치 가능성 증가) |
- 인덱스는 키 값이 동일할 경우 RowID 순으로 정렬합니다.
즉, 변경 전에는 부서코드가 같은 경우 RowID 순으로 정렬되고 변경 후에는 부서코드, 성명 순으로 정렬되고 둘 다 같은 경우 RowID 순으로 정렬됩니다. - 테이블의 물리적 구조는 변경되지 않기 때문에, 변경 전후 테이블 정렬 순서는 동일합니다.
- 클러스터링 팩터 변화의 이유
- 인덱스에 성명 컬럼이 추가되면서 같은 부서코드 내에서 성명 순으로 정렬됩니다.
- 하지만 테이블의 물리적 순서는 변하지 않아, 인덱스 순서와 테이블 순서의 불일치가 발생할 수 있습니다.
- 이로 인해 인덱스를 통해 테이블에 접근할 때 더 많은 Table Random Access가 발생할 수 있어 클러스터링 팩터가 나빠질 수 있습니다.
- 클러스터링 팩터가 나빠지면 Index Scan의 효율성이 떨어질 수 있습니다.
- 따라서 인덱스에 컬럼을 추가할 때는 클러스터링 팩터의 변화를 고려해야 하며, 쿼리 성능에 영향을 줄 수 있음을 인지해야 합니다.
✏️ 1. 인덱스 컬럼 추가
⭐⭐⭐ 기존 인덱스 변경: 기존 인덱스 + 필요한 컬럼을 추가합니다.
-- [인덱스: 성별 + 연봉]
SELECT *
FROM TSTOWN.사원
WHERE 성별 = '남'
AND 근무지 = '광주';
-- 선두컬럼 O: Index Range Scan 가능
-- 다만, 근무지에 대한 인덱스는 없으므로 Table Random Access 발생- 위 쿼리문에서 근무지가 광주인 로우가 단 2건이라고 하더라도, 이를 위해 성별이 '남'인 전체 로우 9건을 살펴보아야 합니다. (R.A 9번 발생)
- 새로운 인덱스 생성: 성별 + 근무지
- 다음과 같은 인덱스를 새롭게 만든다면?
: 2건의 결과를 찾기 위해 R.A 2번만 발생하여 비효율은 없겠지만, 일반적으로 인덱스를 추가로 만들지 않습니다. - 인덱스를 추가로 만들지 않는 이유는?
1) 인덱스가 많으면 옵티마이저가 경로를 잘못 찾을 가능성이 높습니다. 실행계획이 자주 뒤집히고, 통계정보를 컨트롤하기 어려울 가능성이 높아집니다.
2) 인덱스 생성에도 자원이 들고, 인덱스가 많아질수록 DML 작업(INSERT, UPDATE, DELETE)의 부하가 증가합니다.
- 다음과 같은 인덱스를 새롭게 만든다면?
- 차선책: 기존 인덱스 + 필요한 컬럼 추가
- 인덱스: 성별 + 연봉 + 근무지
- 필요한 컬럼을 추가해도 문제가 되는 않는 이유는?
: 성별 → 연봉 → 근무지 순으로 정렬되기 때문에, 기존 정렬이 흐트러지지 않기 때문입니다. - Where 조건절에 있는 컬럼이 인덱스에 있는 경우, R.A 비효율이 낮아집니다.
-- [인덱스: 성별 + 연봉 + 근무지]
WHERE 성별 = '남'
AND 근무지 = '광주';
✏️ 2. PK 인덱스에 컬럼 추가
⭐⭐⭐ PK 인덱스에 컬럼을 추가합니다.
- PK는 기본적으로 인덱스를 자동 생성합니다.
ex: 부서코드를 PK로 지정하면 내부적으로 단일 컬럼 인덱스가 생성됩니다. - PK로 검색 성능을 최적화하려면 PK 정의에 복합 컬럼을 설정하거나 보조 인덱스를 추가해야 합니다.
-- PK_부서: 부서코드
SELECT *
FROM 사원 A, 부서 B
WHERE A.연봉 < 3000
AND B.부서코드 = A.부서코드
AND B.지역 = '대전';- 위 쿼리문을 나누어서 생각해 보면
- 사원 조회 → 부서조회
- 부서조회 시, 만약 부서코드 + 지역으로 인덱스 검색 → R.A 비효율이 낮아질 것입니다.
- 기존 PK(부서코드)만으로 검색 시
: 부서코드 기준으로 데이터 검색 → 지역 = '대전' 작업 추가 필요합니다. (R.A 증가) - 복합 PK(부서코드, 지역) 활용 시
: 부서코드 + 지역 조건으로 검색 가능 → 불필요한 테이블 접근을 줄입니다.
-- PK는 제약조건이므로 미리 인덱스를 만들고 ADD CONSTRAINT
CREATE INDEX PK_부서 ON 부서(부서코드, 지역);
ALTER TABLE 부서 ADD CONSTRAINT PK_부서
Primary Key (부서코드)
USING INDEX PK_부서; -- 복합 PK 정의
-- PK_부서: 부서코드 + 지역
SELECT *
FROM 사원 A, 부서 B
WHERE A.연봉 < 3000
AND B.부서코드 = A.부서코드
AND B.지역 = '대전';
✏️ 3. 인덱스만 읽고 처리 (커버링 인덱스)
⭐⭐⭐ 인덱스만 읽고 처리하는 방식은 테이블 액세스를 완전히 피할 수 있는 튜닝 기법입니다.
-- [인덱스: 부서코드 + 성명]
SELECT 부서코드, 성명, 퇴사여부
FROM 사원
WHERE 부서코드 = '1000'
AND 성명 = '홍길동';
-- Index Range Scan 가능, 퇴사여부에 대한 R.A 필요
-- [인덱스: 부서코드 + 성명 + 퇴사여부]
SELECT 부서코드, 성명, 퇴사여부
FROM 사원
WHERE 부서코드 = '1000'
AND 성명 = '홍길동';
-- R.A X => 인덱스 블록만 읽어서 결과 반환- 다만, 실무에서 SELECT절에 있는 컬럼을 위해 Index를 추가하는 것을 지양합니다. (커버링 인덱스 지양)
- 왜? 쿼리 변경에 따른 비효율, 인덱스 크기 증가, 유지보수 비용 증가 등...
- 앞서도 언급했듯이 Index는 최소한의 컬럼으로 구성해야 합니다.
- 3개 컬럼보다 2개 컬럼이 블록을 더 적게 차지할 것 → 디스크 I/O 횟수 절감에 도움이 될 것입니다.
- 따라서 가능하면 1개의 컬럼, 즉 최소한의 컬럼으로 인덱스를 구성합니다.
📒 정리하면
- 인덱스 컬럼 추가: 기존 인덱스에 필요한 컬럼을 추가하여 Table Random Access를 줄이고 쿼리 성능을 개선합니다.
- PK 인덱스 최적화: PK 인덱스에 관련 컬럼을 추가하여 복합 PK를 생성합니다.
- 커버링 인덱스: 인덱스만으로 쿼리를 처리하여 R.A를 피하지만, 실무에서는 신중하게 사용해야 합니다.
- 최소 컬럼 구성: 인덱스는 최소한의 필요한 컬럼으로 구성하여 디스크 I/O 횟수를 줄이고 전체적인 성능을 개선해야 합니다.
- 클러스터링 팩터 고려: 인덱스 컬럼 추가 시 클러스터링 팩터 변화를 고려하여 인덱스 효율성을 유지해야 합니다.
반응형
