Skip to content

Latest commit

 

History

History
155 lines (117 loc) · 7.45 KB

index(튜닝).md

File metadata and controls

155 lines (117 loc) · 7.45 KB
Raw

Index

  • 블록에 저장된다.
  • 컬럼 수와 데이터 사이즈마다 인덱스 블록에 저장되는 건수는 다르다.
  • 모든 컬럼이 NULL이 아닌 경우에만 인덱스가 만들어진다.
    • 모든 컬럼이 NULL인 경우에는 인덱스를 만들지 않는다.
    • NULL을 허용하는 컬럼이라면 이해하기 힘든 동작을 하는 경우가 있다.
  • 인덱스 컬럼을 가공하면 안된다.
  • 컬럼을 만든 데이터타입에 맞게 WHERE 절에 SQL문을 사용하는게 좋다.

🏖Index Scan 방식

Index Unique Scan

  • 인덱스를 사용한 검색 방식 중 가장 빠른 방법
  • 이 방식을 사용하기 위해서는 기본키 또는 Unique Index 가 생성되어 있어야 하며, 인덱스를 구성하고 있는 모든 컬럼이 조건절에서 '=' 로 비교되어야 한다.
  • 조인되는 Inner Table과의 조인 조건에도 Unique Index 또는 기본키 컬럼이 모두 조인에 참여했을 때에만 가능하다.
  • PK 컬럼 또는 Unique Index 컬럼이 여러 개로 구성되어 있는데 이중 일부만 이용할 때는 Index Range Scan 또는 Index Full Scan으로 변경되기도 한다.

Index Range Scan

  • 가장 일반적으로 사용되는 스캔 방식
  • 인덱스를 수직 탐색하다가 리프 블로겡 도달하여 시작 지점을 찾고, 필요한 만큼의 범위를 순차적으로 탐색
  • Unique Index를 사용하지 않거나, 비교연산자를 사용한 대다수의 경우가 이 방식으로 처리된다.
  • <, <=, >, >=, between, like 등이 사용된다.
  • 이 방식을 병렬로 처리하는 것은 불가능하다.

Index Full Scan

  • 수직적 탐색없이 첫번째 리플 블록으로 이동하여 마지막 리프 블록까지 순차적으로 인덱스를 탐색
  • 최적의 인덱스가 없을 때 차선으로 선택
  • 조건절에서 인덱스 컬럼 중 하나 이상을 사용한 경우 또는 SQL에서 사용한 컬럼들이 모두 하나의 인덱스에 존재할 경우 적용되는 방식
  • SQL에서 사용한 컬럼들이 모두 하나의 인덱스에 존재할 경우, 인덱스를 구성하는 컬럼 중 하나의 컬럼은 NOT NULL 제약 조건을 충족해야 한다.
  • 이 방식을 병렬로 처리하는 것은 불가능하다.

Index Skip Scan

  • 인덱스 스캔에 필요한 선두 컬럼(선행 컬럼)이 조건절에 없을 때 사용 가능한 스캔 방식
  • 생략된 선행 컬럼 값의 종류가 적을 때는 유용하다.

Index Fast Full Scan

  • 인덱스 스캔 중 유일하게 멀티블록 I/O 방식으로 스캔
  • 일반적인 인덱스 스캔을 했을 때 얻게되는 정렬 순서가 보장되지 않는다.
  • SQL문에 포함된 모든 컬럼이 인덱스에 포함되어 있어야 한다.
  • PK 인덱스 또는 NOT NULL 컬럼이 포함된 인덱스가 있어야 한다.

⛰Index Scan 유도 방법

  • 인덱스 힌트를 사용할 경우 오라클은 Index Unique Scan, Index Range Scan, Index Full Scan 중 오라클이 좋다고 판단하는 스캔 방식을 이용

힌트 종류

  • INDEX: 인덱스 스캔을 유도, 어떤 인덱스 스캔을 유도할지는 오라클이 선택
  • INDEX_DESC: 인덱스를 거꾸로 스캔
  • INDEX_RS: 인덱스 범위 스캔을 유도. 범위 스캔이 불가능하다 판단될 경우 힌트 무시
  • INDEX_SS: 인덱스 스킵 캔을 유도. 스킵 스캔이 불가능하다 판단될 경우 힌트 무시
  • INDEX_COMBINE: 새로운 인덱스를 추가하기 힘든 상황이나 기존 인덱스에 컬럼을 추가하기 부담스러운 상황에서 두 개의 인덱스를 사용할 수 있게함

사용 방법

힌트종류(테이블명 인덱스명)
힌트종류(테이블명 (컬럼1, 컬럼2, ...))

SELECT /* INDEX(T T_X01) */
		*
FROM TMP T
WHERE A = :a
	AND B = :b
	AND C = :c


SELECT /* INDEX(T (A,B,C) */
		*
FROM TMP T
WHERE A = :a
	AND B = :b
	AND C = :c

🏕인덱스 활용과 튜닝

  • 인덱스를 경유하게 되더라도 테이블로 접근하는 랜덤 액세스가 복잡하면 성능이 저하된다.
  • 테이블로의 접근을 최소화할 수 있는 방법을 고안하자.

인덱스 설계

  • 등치(=) 조건으로 사용되고, 자주 사용되는 컬럼을 앞쪽으로 만든다.
  • 등치(=) 조건으로 사용되고 값의 수가 적은 컬럼을 앞쪽으로 만든다.
  • 자주 사용되는 컬름을 선정
  • 테이블 랜덤 액세스를 줄인다.
  • 정렬을 대신한다.
  • 인덱스가 가급적 여러 SQL문에서 사용할 수 있도록 만든다.
  • 인덱스의 효율을 높이려면, 등치 조건을 연속하여 배치
  • 범위 조건 이후에 등장하는 컬럼은 인덱스 필터로 동작하기 때문에 효율이 떨어진다.

인덱스로 테이블 액세스 최적화

  • 접근한 블록과 동일한 블록이라면 같은 블록을 방문하기 때문에 추가 I/O가 발생하지 않는다.
    • 이때, 인덱스 정렬 순서와 테이블에 적재된 데이터 순서까지는 일치하지 않아도 된다.
    • 같은 블록에만 있으면 버퍼피닝 효과가 나타난다.
    • 버퍼피닝(Buffer Pinning): 접근한 블록이 다음 접근해야하는 블록이 같아 추가적인 I/O 작업을 하지 않아도 되는 것

클러스터링 팩터

  • 데이터가 모여 잇는 정도를 수치로 보여주는 것
  • 클러스터링 팩터는 인덱스마다 다르며, 통계 정보를 오라클이 제공
SELECT T.TABLE_NAME, I.INDEX_NAME, T.BLOCKS, T.NUM_ROWS, I.CLUSTERING_FACTOR
FROM USER_TABLES T, USER_INDEXEX_ I
WHERE I.TABLE_NAME = T.TABLE_NAME
	AND T.TABLE_NAME IN ('테이블명1', '테이블명2')
  • 클러스터링 팩터 값이 블록 수에 가까우면 좋은 것이고, 로우 수에 가까우면 나쁜것으로 해석

기존 인덱스에 컬럼 추가

  • 인덱스를 추가하면 많은 수의 인덱스가 생겨서 추후에 더 큰 문제가 발생할 수 있다.
  • 이런 상황에서 맨 뒤에 컬럼을 추가하게 되면 인덱스의 용량이 증가함에 따라 인덱스 스캔 양은 약간 증가하지만, 테이블로의 랜덤 액세스는 최종 결과 집합에 해당 되는 데이터만큼 액세스한다.

PK 인덱스 확장하기

  • PK 인덱스를 변경할 때 주의할 사항은 기존 PK 컬럼들을 선두에 놓아야 한다.
  • 기존 PK 컬럼의 순서는 바뀌어도 무방
CREATE INDEX ITEM_PK ON (ITEM_ID, ITEM_NAME);
ALTER TABLE ITEM ADD CONSTRAINT ITEM_PK (ITEM_ID) USING INDEX ITEM_PK;
  • 컬럼을 PK 인덱스에 포함하더라도 조인을 통해 해당 컬럼만을 가져오는 SQL문에 대해서는 테이블에 접근하지 않고, 인덱스에서만 처리가 가능

인덱스에서만 처리하기

  • SQL 문에 사용하는 모든 컬럼을 포함해 테이블로 액세스하지 않도록 만드는 것
  • 결과 집합 자체가 많을 때 고려할 만하다.
    • ex) 몇 년치가 저장된 테이블에서 특정 월을 집계하라고 하려고 테이블 전체 스캔을 할 경우