-
INDEX
- 소수의 데이터 조회시 유리 (응답속도가 중요할 때)
- I/O 기준이 SINGLE BLOCK : 다랑의 데이터 인덱스로 접근하면 테이블 전체 조회보다 오히려 느림
- 테이블의 각 컬럼마다 인덱스가 지정되어 있으면 SELECT에는 유리할 수 있지만 데이터 삽입, 삭제, 변경시
각 컬럼마다 정렬작업이 이루어 지기 때문에 UPDATE, DELETE, INSERT 작업 시 부하가 커진다.
---> 즉, 인덱스를 하나 더 추가할 지 말지는 데이터의 저장 속도를 어디까지 희생할 수 있는지,
읽기 속도를 얼마나 더 빠르게 만들어야 하는지의 여부에 따라 결정되야한다.
- INDEX를 만들지 않아도 크게 상관없으면 안만드는게 Best 그래도 만든다면 5개정도가 적정선임
- 인덱스는 B* 트리 구조이고, Root node 부터 leaf node까지의 depth가 항상 같도록 밸런스를 유지한다.
-> 즉, 데이터 입력으로 밸런스가 무너질 경우 밸런스를 맞추는 추가 작업이 필수다.
- 시스템에서 실행되는 모든 쿼리를 분석하여 테이블의 적정 인덱스를 설계 하는 작업이 난이도가 높음
INDEX를 사용하지 않는 경우
* Function Based Index (함수 기반 인덱스)
CREATE INDEX 인덱스이름 ON 테이블(sal*2);
-> 기존 컬럼을 가공한 컬럼에 인덱스를 붙이는 것
-> EXPLAIN PLAN FOR를 사용해 실행계획을 보면 인덱스 사용이 별 의미가 없음
TABLE
- 테이블의 모든 데이터를 읽어서 처리하는 경우라면 인덱스 보다 유리
- I/O의 기준이 MULTI BLOCK : 전체 처리 속도가 더 중요할 때
-
INDEX 설계
중요한 두 가지 선택 기준
- 인덱스에 사용할 컬럼을 선택하고, 순서를 정하데 중요한 기준
1. 조건절에 항상 또는 자주 사용되는 컬럼을 선택
2. = 조건으로 자주 조회되는 컬럼들을 앞쪽에 둔다.
인덱스 설계는 공식이 아닌 전략과 선택의 문제
- 인덱스 설계에는 정답이 없다.
--> 그러므로, 인덱스 구성을 왜 그렇게 했는지에 대해 판단근거를 논리적으로 제시할 수 있으면 된다.
- 최소의 인덱스의 개수로 다수의 DML 성능을 만족시키는 것이 궁극의 목표
설계 예제
고객 수 : 100만명, 상품 : 10만개, 거래일자의 검색범위는 유동적
유형 검색조건1 검색조건2 검색조건3 검색조건4
A 고객번호 + 거래일자 상품번호 + 거래일자 상품번호 + 고객번호
+ 거래일자
거래일자 B 고객번호 + 거래일자 상품번호 + 거래일자 거래일자 C 고개번호 + 거래일자
+ 상품번호
상품번호 + 거래일자 거래일자 D 고객번호 + 거래일자 상품번호 + 거래일자
+ 고객번호
E 거래 일자 + 상품번호
+ 고객번호
거래일자 + 고객번호
+ 상품번호
F 거래일자 + 고객번호 거래일자 + 상품번호 G 거래일자 + 상품번호 고객번호 + 상품번호
+ 거래일자
H 고객번호 + 상품번호
+ 거래일자
I 고객번호 + 거래일자 거래일자 + 상품번호
+ 고객번호
J 거래일자 + 상품번호
+ 고객번호
유형 별 설명
A : 모든 조건절 경우만큼 인덱스 생성 -> DML 부하 심하고, 관리도 힘듬
B : 검색 조건 3번 검색시 검색조건 1번의 인덱스를 활용하면 테이블 필터링이 발생하지만, 고객번호가 변별력이
좋음을 고려하여 인덱스 생성 -> 그러나 거래일자를 아주 넓은 범위 검색 시 상품번호 필터링 때문에 다소 비효율적
C : B의 비효율을 극복하고자 검색조건1 인덱스에 상품번호를 추가한 복합인덱스 생성
D : B와 유사한 전략으로 검색조건 3의 비효율을 극복하려고 검색조건 2 인덱스에 고객번호를 추가 했는데
상품번호의 선택도가 고객번호보다 높아 C보다는 인덱스 스캔이 많이 발생
E : BETWEEN 조건 뒤는 거의 인덱스 필터 역할만 하기때문에 둘 중 하나만 있어도 상관 없는데 그럼 I와 유사
F : 4개의 조건에서 거래일자가 주가 되는 경우가 한 경우 밖에 없기 때문에 효율이 낮음
G : 검색조건 1, 2에서 인덱스 스캔이 비효율적
H : 검색조건 2,4에 대한 대비가 없고, 검색조건 1에서도 인덱스 스캔의 비효율이 크다
I : 변별력이 높은 고객번호가 조건에 포함되면 검색조건1을 효과적으로 이용가능, 검색조건2의 선두에 거래일자가 약간
의 비횽류이 있으나 범용적으로 사용하는 컬럼이기 때문에 긍정적으로 평가
J : 인덱스 활용성이 높으나, 근본적인 비효율이 있기 때문에 신중히 고려
효율성 분석
- A와 I 의 효율 비교
A I 검색조건 1 : 고객번호 + 거래일자
검색조건 2 : 상품번호 + 거래일자
검색 조건 3 : 상품번호 + 고객번호 + 거래일자
검색 조건 4 : 거래일자
검색조건 1 : 고객번호 + 거래일자
검색조건 2 : 거래일자 + 상품번호 + 고객번호
조건 A I 비고 1 검색 조건 1 검색 조건 1 두번의 테이블 랜덤 엑세스 2 검색 조건 2 검색 조건 2 세번의 테이블 랜덤 엑세스 3 검색 조건 3
한 건의 Random Access
검색 조건 1
두번의 테이블 엑세스
* 거래 범위가 좁으면 검색조건2를 이용해 불필요한 Random Access를 없애는게 좋을수도 있음
4 검색 조건 4 검색조건 2 동일한 테이블 랜덤 엑세스
Range 파티션 고려
- 테이블 Random Access 측면에서 보면 I가 조건3에서 조금 불리하지만 고객번호의 변별력이 워낙 좋으니 큰 차이가
나지 않을수 있다
- 인덱스 스캔 효율 측면에서 보면 I가 조건2, 조건3에서 비효율적.
하지만 인덱스 블록에는 하나당 수백개의 레코드가 담기므로 괜찮을 것이다
스캔 효율성 이외의 판단 기준
- 쿼리수행빈도
- 업무상 중요도
- 클러스터링 팩터( = 군집성 계수) : 데이터가 모여있는 정도.
오라클은 InnoDB에 의해서 클러스터 뭐더라.. 뭘 쓰기 때문에 테이블이 특정 컬럼
을 기준으로 비슷한 값을 데이터가 모여 있는데 이 모여 있는 정도를 의미한다.
""인덱스 클러스터링 팩터가 좋다" -> "인덱스 정렬 순서와 테이블 정렬순서가 비슷"
- 데이터량
- DML 부하(기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 컬럼 포함 여부 등)
- 저장공간
- 인덱스 관리비용 등
* Random Access란??
- 결합 인덱스 컬럼 순서는 4개의 우선 순위에 의해 생성해야함
1 순위 : 컬럼이 사용한 연산자에 의한 인덱스 컬럼 선정
2 순위 : Random Access를 고려한 인덱스 컬럼 선정
3 순위 : 정렬 제거를 위한 인덱스 컬럼 선정
4 순위 : 단일 컬럼의 분포도를 고려한 인덱스 컬럼 선정
- 두 번째 순위인 Random Access란?
데이터를 저장하는 블록을 한번에 여러 개 엑세스 하는 것이 아니라 한 번에 하나의 블록만을 엑세스하는 방식
한 번에 여러개 블록에 엑세스 -> 다중 블록 I/O 수행
- Random Access는 어떤 작업에서 발생??
인덱스를 엑세스하여 확인한 ROWID를 이용하여 테이블을 엑세스하는 경우 Random Access 발생
* Range 파티션
- 특정 기준에 의해서 범위를 나눌 때 사용하는 방식
'SQL' 카테고리의 다른 글
JOIN의 방식 (0) 2020.02.12 B-Tree 인덱스 (0) 2020.02.12 INDEX와 TABLE (0) 2020.02.11 시퀀스 (SESQUNE) (0) 2020.02.11 VIEW (0) 2020.02.11