[SQLP 막간정리 1] [3] 인덱스 튜닝

(1) 인덱스 기본 원리

[SQLP필기풀이]3장 인덱스 튜닝(1)-인덱스 기본 원리1
[SQLP필기풀이]3장 인덱스 튜닝(1)-인덱스 기본 원리2
[SQLP필기풀이]3장 인덱스 튜닝(1)-인덱스 기본 원리3

5번 : SQL Server에서 원하는 조건으로 검색(SEEK)하도록 힌트 지정

FROM 고객 WITH(INDEX(1))
FROM 고객 WITH(INDEX(고객_PK))
FROM 고객 WITH(INDEX=고객_PK)
FROM 고객 WITH FORCESEEK (고객_PK(고객번호)))

OPTION (INDEX(고객_PK)) >>> 틀림
OPTION (TABLE HINT(고객, INDEX(고객_PK))) >>> 맞음

풀스캔

FROM 고객 WITH (FORCESCAN)

클러스터형 인덱스 있을 때

FROM 고객 WITH(INDEX(1)) >>> 고객번호로 클러스터형 인덱스를 검색
FROM 고객 WITH(INDEX(0)) >>> 클러스터형 인덱스를 scan하면서 고객번호를 필터링

클러스터형 인덱스 없을 때

FROM 고객 WITH(INDEX(1)) >>> 구문오류 발생
FROM 고객 WITH(INDEX(0)) >>> 테이블을 Full Scan 하면서 조건절을 필터링

15번 : B*Tree 인덱스에 생길 수 있는 현상

• 불균형은 생길 수 없지만 Index Fragmentation에 의한 Index Skew 또는 Sparse 현상이 생기는 경우는 종종 있고, 이는 인덱스 스캔 효율에 나쁜 영향을 미칠 수 있다.
• Index Skew는 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상을 말한다.예를 들어, 시계열적으로 증가하는 인덱스에서 과거 데이터를 일괄 삭제하고 나면 왼쪽 리프 블록들은 텅비는 반면, 오른쪽 블록들은 꽉 찬 상태가 된다.
• Index Sparse는 인덱스 블록 전반에 걸쳐 밀도(density)가 떨어지는 현상을 말한다.

23번 : Index Range Scan이 불가능한 SQL

• 구성 컬럼 중 하나라도 NULL이 아닌 레코드는 인덱스에 저장하기 때문에 2개 이상 컬럼으로 구성된 결합 인덱스에 대해서는 IS NULL 조건에 대한 Index Range Scan이 가능할 수 있다.

24번,25번 : Index Range Scan이 가능한 SQL

1. 숫자형과 문자형을 비교할 때는 숫자형 기준으로 문자형이 자동 변환되지만, 연산자가
   LIKE일 때는 다르다. LIKE 자체가 문자열 비교 연산자이므로 이때는 아래와 같이 문자형
   기준으로 숫자형 컬럼이 변환된다.
2. 숫자형과 문자형을 비교할 때는 숫자형 기준으로 문자형이 자동 변환된다.
3. 두 컬럼 값이 모두 Null인 데이터는 인덱스에 저장되지않으므로 '지점코드 IS NULL'인 계좌를 검색하기 위해 인덱스를 사용하면 결과집합에 누락이 발생한다.
4. 인덱스를 Range Scan 을 하려면 고객번호를 선두로 갖는 인덱스가 있어야 하는데, 현
   재 그런 인덱스는 생성돼 있지 않다. 계좌_X02 인덱스를 Full Scan 해서 '고객번호 =
   123456'인 계좌를 찾을 수는 있다.
5. 하지만, NOT BETWEEN 조건인 경우, CONCATENATION(옵티마이저에 의한 UNION ALL 분기)이 일어나면 Index Range Scan이 가능하다.그런데 ④번 SQL에는 CONCATENATION을 방지하도록 NO_EXPAND 힌트를 사용하였으므로 Index Range Scan이 불가능하다. 참고로, CONCATENATION을 유도하려면 USE_CONCAT 힌트를 사용하면 된다.

26번 : CONCATENATION(옵티마이저에 의한 UNION ALL 분기,힌트 미사용)

1. OR 조건은 기본적으로 Index Range Scan을 위한 액세스 조건으로 사용할 수 없다. OR 조건으로는 수직적 탐색을 통해 스캔 시작점을 찾을 수 없기 때문이다.
2. 다만,CONCATENATION(옵티마이저에 의한 UNION ALL 분기)으로 처리했을 때 각각 수직 탐색을 위한 액세스 조건으로 사용할 인덱스가 있다면, Index Range Scan이 가능하다.
3. UNION ALL 분기했을 때 한쪽 브랜치는 고객번호 조건으로, 다른 한쪽 브랜치는 휴대폰번호로 Index Range Scan 할 수 있다. opt 변수에 입력한 값에 따라 둘 중 한쪽 브랜치만 실행된다.

30번 : Index Range Scan 가능하도록 재작성

• ⭐️ 데이터 타입 VARCHAR(1) , VARCHAR(3) 반드시 확인한다.

AND (지수구분코드 , 지수업종코드) IN (('1','001'), ('2','003'))

(2) 테이블 액세스 최소화

[SQLP필기풀이]3장 인덱스 튜닝(2)-테이블액세스최소화1

39번 ,40번 : BATCH I/O

• 배치 I/O가 작동하더라도 실행계획에 SORT ORDER BY 오퍼레이션이 나타나지 않는 한,
  부분범위 처리는 가능하다.
• ⭐️ 실행계획에 SORT ORDER BY가 있는지 꼭 확인한다.

(3) 인덱스 스캔 효율화

[SQLP필기풀이]3장 인덱스 튜닝(3)-인덱스스캔효율화

42번 : 인덱스 스캔 과정에서 비효율이 가장 큰 조건절

• ⭐️ 핵심은 효율이라는 단어에 있다. 1번 선지는 원하는 결과의 값을 모두 필터링할 수 있기에 조건 검색중 낭비되는 데이터가 없겠지만 3번같은 경우는 선행컬럼이 중간에 비어 상품 유형 코드를 만족하는 데이터를 모두 스캔하게 되어 굳~이 스캔할 필요가 없는 데이터까지도 모두 체크해야하기 때문에 비효율적이라고 판단할 수 있다.
• 스캔하는 양이 많으면 비효율적 , 적으면 효율적이 절~대 아니다.

45번 : 일시,일자와 INDEX SkIP SCAN

• 승인요청일자는 값 종류가 7개이고 매일 100만 건이 존재하는 상황이므로 각 일자 구간에서 특정 승인요청자ID의 데이터를 찾을 때는 Index Skip Scan이 큰 도움을 준다.
• 하루는 86,400초이므로 7일간 승인요청일시로 가능한 값의 종류는 최대 684,888개다. 최대 684,800개 값 구간에 흩어진 특정 승인요청자ID의 데이터를 찾을 때
  Index Skip Scan은 성능 향상에 전혀 도움이 되지 않는다.

49번 : 옵션조건을 LIKE로 처리할 때의 장단점

• 거래일자 조건을 만족하는 모든 거래를 조회하고 싶을 때 :CUST_ID 변수에 NULL을 입력하므로 조건절은 「고객ID LIKE ||'%'」가 된다. 「NULL LIKE || '%’」은 공집합이다.
  따라서 고객ID가 NULL인 거래는 거래일자 조건을 만족하더라도 결과집합에서 누락된다.

50번 : NVL이나 DECODE 함수를 사용 > UNION ALL 형태로 쿼리변환이 작동

• 옵션조건에 NVL이나 DECODE 함수를 사용하면 아래와 같은 UNION ALL 형태로 쿼리변환이 작동하므로 고객ID처럼 변별력이 좋은 컬럼에 매우 효과적으로 사용할 수 있다.

(4) 인덱스 설계

[SQLP필기풀이]3장 인덱스 튜닝(4)-인덱스설계

57번 : 올바른 인덱스 후보군

• 조건절 컬럼을 인덱스가 모두 포함한다면 INDEX RANGE SCAN 후에 얻은 결과 건수(100)와 테이블 액세스 후에 얻은 결과 건수(50)가 다를 수 없다.
• SQL에 사용된 컬럼을 인덱스가 모두 포함한다면 실행계획 세 번째 라인의 TABLE ACCESS 오퍼레이션이 생략된다.

61번 : 최적 인덱스 설계

• 결론적으로, 종료일자 + 시작일자 순으로 두 컬럼 모두 인덱스에 포함하는 것이 좋다.