8. SQL튜닝 요약(2) - 패스트캠퍼스 백엔드 부트캠프 3기

인덱스

인덱스 구조 및 탐색
인덱스 : 소량의 데이터를 빠르게 검색 할 수 있게 하는 데이터 구조
데이터를 효과적으로 검색하기 위한 저장방식 
인덱스의 특징 : 데이터 정렬, 위치 정보
인덱스 튜닝의 두 가지 핵심요소
인덱스 스캔 효율화 튜닝 : 정렬할 컬럼의 효율적 선택
랜덤 액세스 최소화 튜닝 : 위치 검색 최소화 
OLTP(Tx) : 소량 데이터 검색, 즉시, Online - 주문
OLAP(분석:Analysis) : 대량 데이터 검색, 배치(Batch) - 회원등급,전체이메일 발송
인덱스 구조
B(Balanced)-Tree : 제일 아래와 위의 가지의 차이가 1이상나지 않는다.
Balanced : 루트로부터 모든 리프 블록까지의 높이는 항상 같다.

인덱스 기본 사용법
인덱스 사용불가 조건 : 변형, not, null, like, or, in
인덱스의 소트 연산 생략 : 인덱스를 사용하면 order by를 써도 sort 연산이 추가되지 않는다, 인덱스가 있다고 order by가 생략되면 안된다.
select-list에서 컬럼 가공
MIN, MAX : 정렬 생략 가능
MAX(TO_NUMBER(변경순번)) -> 컬럼변형을 먼저하고 값을 찾으면 정렬을 다시 해줘야한다.
TO_NUMBER(MAX(변경순번)) -> 값을 찾고 데이터를 변형해주면 정렬(N제곱)을 다시 안해줘도된다.
자동 형변환 : 명시적으로 형변환하라, 자동 형변환은 인덱스 변형이 일어난다.

인덱스 확장기능 사용법
Index Range Scan(범위 탐색) : 필요한 범위만 스캔
Index Full Scan(전체 탐색) : 처음부터 끝까지 수평적으로 탐색하는 방식
Index Unique Scan(단일 탐색) : 수직적 탐색만으로 데이터를 찾는 스캔 방식
Index Skip Scan(조건 탐색)
Index Fast Full Scan(전체 탐색) : 순서상관없이 Multiblock I/O방식으로 읽어오는것, 순서 보장X, 병렬스캔, 세그먼트 전체 스캔
Index Range Scan Descending(범위 탐색) : Index Range Scan을 거꾸로 한 것

 

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

인덱스 튜닝

테이블 액세스 최소화 - 범위를 줄이고, 데이터 갯수 줄이기
ROWID : 각 행을 고유하게 식별하는 값
인덱스 ROWID : 논리적인 주소 정보, 한번에 데이터로 도착하지않음 - 인덱스 ROWID를 이용한 테이블 액세스는 고비용 구조
인덱스 클러스터링 팩터(CF) : 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도
정렬이 잘되어 있으면 여러번 읽지 않아도 된다.
CF가 좋다 : 정렬, 밀집도가 높다.
디스크 조각 모음 : 데이터 정렬 및 밀집도 상승 -> 디스크 성능 하락 및 오래걸림
인덱스 손익분기점 : 소량검색(5~10%)
Table Full Scan : 성능이 일정(1건, 10만건, 1,000만건 모두 동일)
온라인 프로그램 튜닝(OLTP) : 주문 입력, 소량 데이터(기본)
배치 프로그램 튜닝(OLAP) : 처리, 대량 데이터(Full Scan과 해시조인이 유리)
인덱스 컬럼 추가 : 인덱스 스캔량은 줄지 않지만, 테이블 랜덤 액세스 횟수를 줄여준다.
인덱스 구조 테이블 : 모든 컬럼이 인덱스
클러스터 테이블 : 데이터를 대충정렬

부분범위 처리 활용
부분범위 처리 : 데이터를 전송할 때 일정량씩 나누어 전송하는 것 

인덱스 스캔 효율화 - 조건이 정확할 수록 빠르다, 처음 조건이 좁아야 빠르다
액세스 조건 : 인덱스 탐색 범위를 줄이는데 도움이 되는 조건
필터 조건 : 인덱스 탐색 범위를 줄이는데 도움이 되지 않는 조건
BETWEEN을 IN-List로 전환 : 모든 컬럼을 '=' 조건으로 검색(소량)
BETWWEN과 LIKE 스캔 범위 비교
BETWEEN : 범위 정확(항상 빠름 LIKE보다)
LIKE : 범위 미정
사용자 정의 함수 - (성능 매우 안좋음)

인덱스 설계
인덱스가 많으면 발생하는 문제 : DML성능 저하(TPS 저하), DB 사이즈 증가(디스크 공간 낭비), DB 관리 및 운영 비용 상승
UPDATE - delete + insert (느림)
인덱스 선두 컬럼 선택 기준 : 
1. 조건절에 항상 사용하거나 자주 사용하는 컬럼을 선정
2. '='조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.
스캔 효율성 이외의 판단 기준 : 수행 빈도, 업무상 중요도
공식을 초월한 전략적 설계 
1. 조회 구간이 길지 않으면 인덱스 스캔 비효율이 성능에 미치는 영향이 크지 않다.
2. 인덱스 스캔 효율보다 테이블 액세스가 더 큰 부하요소가 될 수 있다.
중복 인덱스 제거 : 선두 컬럼의 평균 카디널리티가 매우 낮다면 사실상 중복
커디널리티 : 컬럼 값의 고유한 개수