작년에 면접 준비를 하면서 DB 인덱스에 대해서 정리한 내용이 있다. https://minnseong.tistory.com/19
[DB] Index 정리
Index 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스의 검색 속도를 향상하기 위한 자료구조 Index를 통해 검색 속도 향상과 조회 성능을 높일 수 있다. Index를 사용하지 않는 칼럼을 조
minnseong.tistory.com
제 3장. 인덱스 튜닝은 내용이 많아 두 파트로 나눠 정리를 했다.
인덱스 튜닝 (1) : 테이블 액세스 최소화, 부분범위 처리 활용
인덱스 튜닝 (2) : 인덱스 스캔 효율화, 인덱스 설계
01. 액세스 조건과 필터 조건
액세스 조건
- 인덱스 스캔 범위를 결정하는 조건절
- 인덱스 수적적 탐색을 통해 스캔 시작점을 결정하는 데 영향을 미치고, 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정한다.
필터 조건
- 테이블로 액세스를 할지를 결정하는 조건절
- 인덱스를 사용하든, Full Scan을 사용하든 테이블 액세스 단계에서는 처리되는 조건절
02. 비교 연산자에 따른 군집성
- 선행 컬럼이 모두 '=' 조건인 상태에서 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자에 상관없이 흩어진다.
- 첫 번째 나타나는 범위 조건까지가 인덱스 액세스 조건이고, 나머지는 필터조건이다.
- 가장 선두 칼럼이 범위 검색 조건이면, 그 조건이 스캔 범위를 결정하고, 나머지는 인덱스 칼럼 조건은 필터 조건이 된다.
03. BETWEEN을 IN-LIST로 전환
- 범위검색 컬럼이 맨뒤로 가도록 하면 좋겠지만, 운영 시스템에서는 인덱스 구성을 바꾸는 것이 쉽지 않다.
- 이럴 때, BETWEEN 조건을 IN-List로 바꿔주면 큰 효과를 얻을 수 있다.
SELECT *
FROM 상품
WHERE 상품코드 in ('1', '2', '3')
AND 상품타입 = 'A'- BETWEEN 조건을 IN-List로 바꾸었을 때, 인덱스 수직적 탐색이 세 번 발생한다. (실행 계획, INLIST ITERATOR)
- IN-List 개수만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 칼럼을 '=' 조건으로 검색하므로 선두 컬럼에 BETWEEN을 사용할 대와 같은 비효율은 사라진다.
SELECT *
FROM 상품
WHERE 상품코드 = '1'
AND 상품타입 = 'A'
UNION ALL
SELECT *
FROM 상품
WHERE 상품코드 = '2'
AND 상품타입 = 'A'
UNION ALL
SELECT *
FROM 상품
WHERE 상품코드 = '3'
AND 상품타입 = 'A'- 하지만, IN-List 항목 개수가 많지 않아야 한다. 항목 개수만큼 브랜 블록을 반복 탐색하는 비효율이 더 커질 수 있다. 특히 루트에서 브랜치 블록까지 Depth가 깊을 때 더 그렇다.
- 인덱스 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다.
- 추가로, BETWEEN 조건을 IN-List 조건으로 변환하는 상황에서 조건절을 바꾸지 않고도 Index Skip Scan을 사용하면 같은 효과를 낼 수 있다.
BETWEEN 조건 때문에 인덱스를 비효율적으로 스캔하더라도 블록 I/O 측면에서는 대개 소량에 그치는 경우가 많다. 만약 IN-LIST 개수가 많으면 수직적 탐색 과정에서 많은 블록을 읽게 된다. 데이터 분포나 수직적 탐색 비용을 따져보지 않고 BETWEEN을 IN-LIST로 변환하면 안 된다.
04. IN 조건은 '=' 인가?
아니다!
SELECT *
FROM 상품
WHERE 고객번호 =: cust_no
AND 상품ID IN ('A', 'B', 'C')
(1) 인덱스 [상품ID + 고객번호] 일경우,
- 같은 고객번호가 상품 ID에 따라 흩어진 상태 (연속되지 않는 리프블록에 저장) → IN-List Iterator 방식으로 푸는 것이 효과적
- 이 경우에는 IN 조건이 '=' 조건이 되었다. 수직적 탐색이 3번 일어난다.
(2) 인덱스 [고객번호 + 상품ID] 일경우,
- 상품ID 순으로 정렬된 상태로 같은 리프 블록에 저장, 상품ID 조건절은 필터로 처리한다.
- 고객번호만 액세스 조건이므로 한 블록만 스캔한다.
즉, IN 조건이 '='이기 위해서는 IN-List Iterator 방식으로 풀려야 한다. 그렇지 않으면 IN 조건은 필터 조건이다.
05. BETWEEN과 LIKE 스캔 범위 비교
(1) SELECT * FROM 월별고객판매집계 WHERE 판매월 LIKE '2024%'
(2) SELECT * FROM 월별고객판매집계 WHERE 판매월 BETWEEN '202401' and '202412'
(1)과 (2) 중 어떤 쿼리가 더 정확한 쿼리일까?
▷ BETWEEN과 LIKE 모두 범위검색 조건이지만, LIKE 보다는 BETWEEN을 사용하는 것이 더 낫다. → (2)이 더 정확한 표현
(예시) 판매월 + 판매구분 순 인덱스 구성, 판매구분 A, B 존재
1. 데이터 분포도 ( A : 90%, B : 10% )
(1)
where 판매월 BETWEEN '202401' and '202412'
and 판매구분 = 'B'
(2)
where 판매월 LIKE '2024%'
and 판매구분 = 'B'LIKE(2)를 사용할 경우, 판매월에 '201900'이 저장되어 있다면 그 값도 읽어야 하므로,
판매월 '201901' AND 판매구분 = 'B'인 지점으로 바로 읽을 수 없다.
2. 데이터 분포도 ( A : 10%, B : 90% )
(1)
where 판매월 BETWEEN '202401' and '202412'
and 판매구분 = 'A'
(2)
where 판매월 LIKE '2024%'
and 판매구분 = 'A'LIKE(2)를 사용할 경우, 판매월에 '201913'이 저장되어 있다면, 그 값도 읽어야 하므로 중간에 멈출 수 없다.
코딩을 쉽게 하려고 이처럼 인덱스 칼럼에 범위검색 조건을 남용하면 인덱스 스캔 비효율이 생긴다. 대량 테이블을 넓은 범위로 검색할 때는 그 영향이 매우 클 수도 있다.
05. 다양한 옵션 조건 처리 방식의 장단점 비교
(05.1) OR 조건 활용
- 인덱스 선두 칼럼에 대한 옵션 조건에 OR 조건을 사용해서는 안된다.
SELECT * FROM 거래
WHERE (:cust_id is null OR 고객ID = :cust_id)
AND 거래일자 BETWEEN :dt1 AND :dt2- 옵티마이저에 의한 OR Expansion 쿼리 변환이 기본적으로 작동하지 않으므로, 옵션 조건 칼럼을 선두에 두고 [고객ID+거래일자] 순으로 인덱스를 구성해도 이를 사용할 수 없다.
- 인덱스에 포함되지 않은 칼럼에 옵션 조건은 어차피 테이블에서 필터링할 수밖에 없으므로 OR 조건을 사용해도 무방하다.
- 인덱스 액세스 조건으로 사용 불가, 인덱스 필터 조건으로도 사용 불가, 테이블 필터 조건으로만 사용가능
- 단, 인덱스 구성 컬럼 중 하나 이상이 Not Null 칼럼이면, 18c부터 인덱스 필터 조건으로 사용 가능
(05.2) LIKE/BETWEEN 조건 활용
- 필수 조건 칼럼을 인덱스 선두에 두고 액세스 조건으로 사용하면, LIKE/BETWEEN이 인덱스 필터 조건이어도 충분히 좋은 성능을 낼 수 있다. 하지만, 필수조건이 변별력이 좋지 않을 때는 성능에 문제가 생긴다.
- LIKE/BETWEEN 패턴을 사용하고자 할 때 네, 가지 경우에 속하는지 반드시 점검 (BETWEEN 조건은 1번과 2번만 점검)
1. 인덱스 선두 칼럼에 대한 옵션 조건으로 LIKE와 BETWEEN을 사용하는 경우
SELECT * FROM 거래
WHERE 고객ID LIKE :cust_id || '%'
AND 거래일자 BETWEEN :dt1 AND :dt2- 고객ID가 변별력이 매우 좋기 때문에 비교적 빠르게 조회되지만, 고객ID 값을 입력하지 않으면 인덱스에서 모든 거래 데이터를 스캔하면서 거래 일자 조건을 필터링하는 문제가 생긴다.
- 이 경우 인덱스를 [거래일자 + 고객ID] 순으로 구성해야 한다. 하지만 이 경우는 고객ID 값이 입력되었을 때 생기는 비효율을 감수해야 한다.
2. NULL 허용 칼럼에 대한 옵션조건으로 LIKE와 BETWEEN을 사용하는 경우
SELECT * FROM 거래
WHERE 고객ID LIKE '%'
AND 거래일자 BETWEEN :dt1 AND :dt2- 성능을 떠나 결과 집합에 오류가 생긴다.
- 거래일자 조건에 해당하는 모든 고객의 거래를 선택해야 하는 상황인데, 고객ID가 NULL 허용컬럼이고 실제 NULL 값이 입력되어 있다면 그 데이터는 결과집합에 누락된다.
3. 숫자형이면서 인덱스 액세스 조건으로도 사용 가능한 칼럼에 대한 옵션 조건으로 LIKE을 사용하는 경우
SELECT * FROM 거래
WHERE 거래일자 = :trd_dt
AND 고객ID LIKE :cust_id || '%'
[거래일자 + 고객ID] 인덱스- :cust_id에 값을 입력했을 때, 두 칼럼 모두 인덱스 액세스 조건으로 사용된다.
- 하지만, 고객ID가 숫자형 칼럼이면 자동 형변환이 일어나 고객ID가 필터 조건으로 사용된다.
SELECT * FROM 거래
WHERE 거래일자 = :trd_dt
AND to_char(고객ID) LIKE :cust_id || '%'- [고객ID + 거래일자] 순으로 구성한 인덱스는 아예 사용할 수 없다.
4. 가변 길이 칼럼에 대한 옵션 조건으로 LIKE을 사용하는 경우
- LIKE 옵션 조건에 사용할 때는 칼럼 값 길이가 고정적이어야 한다.
- 칼럼 값 길이가 가변적일 때는 변수 값 길이가 같은 레코드만 조회되도록 아래와 같이 조건절을 추가해야 한다.
where 고객명 like :cust_nm || '%'
and length(고객명) = length(nvl(:cust_nm, 고객명))
where 고객명 like :cust_nm
(05.3) UNION ALL 활용
SELECT * FROM 거래
WHERE :cust_id IS NULL
AND 거래일자 BETWEEN :dt1 AND :dt2
UNION ALL
SELECT * FROM 거래
WHERE :cust_id IS NOT NULL
AND 고객ID = :cust_id
AND 거래일자 BETWEEN :dt1 AND :dt2- :cust_id 변수에 값을 입력했는지에 따라 위아래 SQL 중 어느 하나만 실행되게 하는 방식이다.
- :cust_id 변수에 값을 입력하지 않으면, 위쪽 브랜치에서 거래일자가 선두인 인덱스를 사용되며, 변수에 값이 입력되면 아래쪽 브랜치에서 [고객ID + 거래일자] 인덱스 사용, 최적으로 사용하고 있다.
- 하지만, 단점은 SQL 코딩량이 길어진다.
(05.4) NVL/DECODE 함수 활용
SELECT * FROM 거래
WHERE 고객ID = NVL(:cust_id, 고객ID)
AND 거래일자 BETWEEN :dt1 and :dt2
SELECT * FROM 거래
WHERE 고객ID = DECODE(:cust_id, null, 고객ID, :cust_id)
AND 거래일자 BETWEEN :dt1 and :dt2- NVL, DECODE 둘 중 어느 것을 사용하든 실행계획은 똑같다. → :cust_id가 입력되지 않으면 거래일자가 선두인 인덱스 사용, 입력되면 [고객ID + 거래일자] 인덱스 사용한다.
- 고객ID 칼럼을 함수 인자로 사용(인덱스 칼럼 가공)했는데도, 인덱스를 사용할 수 있는 것은 OR Expansion 쿼리 변환이 일어났기 때문이다. 만약 이 기능이 작동하지 않으면, 인덱스 액세스 조건으로 사용이 불가능했을 것이다.
- 장점은 UNION ALL 보다 단순하면서도 UNION ALL과 같은 성능을 낸다.
- 하지만, LIKE 패턴과 같이 NULL 허용 컬럼에 사용할 수 없다는 데에 있다. 조건절 변수에 NULL을 입력하면 NULL 인 레코드가 결과집합에서 누락되기 때문이다.
- 옵션 조건 처리용 NVL/DECODE 함수를 여러 개 사용하면 그중 변별력이 가장 좋은 컬럼 기준으로 한 번만 OR Expansion이 일어난다. 따라서 OR Expansion 기준으로 선택되지 않으면 인덱스 구성 칼럼이어도 모두 필터 조건으로 처리된다.
06. 인덱스 설계가 어려운 이유
- SQL 각각에 최적화된 인덱스를 마음껏 생성할 수 있다면, SQL 튜닝과 인덱스 설계는 매우 쉬운 일이다.
- 하지만, 인덱스를 많이 생성하면 테이블마다 인덱스가 수십 개씩 생성될 것이며, 관리 비용뿐만 아니라 시스템 부하를 증가시킨다.
ex) DML 성능 저하 (TPS 저하) , 데이터베이스 사이즈 증가 (디스크 공간 낭비) , 데이터베이스 관리 및 운영 비용 증가 - 신규 데이터가 입력할 때마다 인덱스 테이블에도 데이터가 추가되어야 하고, 인덱스는 정렬 상태도 유지해야 한다.
- 인덱스 추가는 시스템에 부하를 주고, 인덱스 변경은 운영 리스크가 크다.
- 데이터를 삭제할 때도, 여러 인덱스에서 레코드를 찾아 지워야 한다.
- 핵심 트랜잭션이 참조하는 테이블에 대한 DML 성능 저하는 TPS 저하로 이어지며, 꼭 필요하지 않은 인덱스로 인해 디스크 공간을 낭비하고 데이터베이스 사이즈가 커지는 만큼 백업, 복제, 재구성 등을 위한 운영 비용도 상승한다.
개별 쿼리 성능뿐만 아니라 인덱스 개수를 최소화함으로써 DML 부하를 줄여야 하므로 인덱스 설계가 어렵다.
07. 인덱스 선택에 중요한 기준
- 가장 정상적이고 일반적인 방식은 Index Range Scan이다.
- 인덱스 선두 칼럼을 조건절에 반드시 사용해야 한다. 따라서 인덱스를 구성할 때, 조건절에 항상 사용하거나 자주 사용한느 컬럼을 인덱스로 선정해야 한다.
- 그렇게 선정한 컬럼 중 '=' 조건으로 자주 조회하는 컬럼을 앞 쪽에 두어야 한다.
스캔 효율성 이외의 판단 기준
- 수행 빈도, 업무상 중요도, 클러스터링 팩터, 데이터양, DML 부하, 저장 공간, 인덱스 관리 비용 등
- 수행빈도, 수행 빈도가 매우 높은 SQL에는 앞서 말한 기준을 이용해 최적의 인덱스를 구성해줘야 한다.
- 데이터양, 데이터량이 적더라면 굳이 인덱스를 많이 만들 필요 없다. 데이터량이 많을 경우 INSERT 도 많기 때문에 초당 DML 발생량은 트랜잭션 성능에 직접적인 영향을 줄 수 있다.
열 개 중 최적을 달성해야 할 가장 핵심적인 액세스 경로 한두 개를 전략적으로 선택해서 최적 인덱스를 설계하고, 나머지 액세스 경로는 약간의 비효율이 있더라도 목표 성능을 만족하는 수준으로 인덱스를 구성할 수 있어야 한다.
08. 소트 연산을 생략하기 위한 컬럼 추가
- 인덱스는 항상 정렬 상태를 유지하므로 ORDER BY, GROUP BY를 위한 소트 연산을 생략할 수 있다. 따라서 조건절에 사용하지 않는 칼럼이더라도 소트 연산을 생략할 목적으로 인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수 있다.
- I/O를 최소화하면서 소트 연산을 생략하기 위해서는 아래 공식을 만족해야 한다.
- '=' 연산자로 사용한 조건절 칼럼 선정
- ORDER BY 절에 기술한 칼럼 추가
- '=' 연산자가 아닌 조건절 칼럼은 데이터 분포를고려해 추가 여부 결정
- 주의할 점으로는, '=' 조건절 컬럼은 ORDER BY절에 없더라도 인덱스 구성에 포함시킬 수 있으며 위치는 앞뒤 중간 어디에 두어도 상관없지만, '='가 아닌 조건절 컬럼은 반드시 ORDER BY 컬럼보다 뒤에 두어야 한다.
예시) [출고일자 + 상품카테고리ID + 등록자ID] 순과 같이 '=' 조건절 컬럼인 '상품카테고리ID'은 아무 위치나 인덱스를 구성해도 소트연산은 생략할 수 있지만, 조건절 컬럼인 '가격' 컬럼을 인덱스에 포함시키기 위해서는 [출고일자 + 등록자ID + 가격] 과 같이 ORDER BY 컬럼보다 뒤쪽에 뒤어야 한다.
SELECT *
FROM 상품
WHERE 상품카테고리ID = :category_id
AND 출고일자 BETWEEN :sdt AND :edt
AND 가격 >= 1000
AND 상품코드 IN ('1', '2', '3')
ORDER BY 출고일자, 등록자ID
09. 결합 인덱스 선택도
- 인덱스 생성 여부를 결정할 때 *선택도가 충분히 낮은지 판단하는 것도 중요한 기준이다.
- 그렇다면, 결합 인덱스 칼럼 간 순서를 정할 때 선택도는 중요할까? 예를 들어 성별과 고객번호 중 어떤 컬럼이 앞으로 오는지?
- No, 인덱스 스캔 효율에 전혀 차이가 없다. 둘 다 인덱스 액세스 조건이므로 어떤 컬럼이 앞으로 오든 인덱스 스캔 범위는 동일하다.
- 인덱스 생성 여부를 결정할 때는 선택도가 매우 중요하지만, 칼럼 간 순서를 결정할 때는 각 칼럼의 선택보다 필수 조건 여부, 연산자 형태가 더 중요한 판단기준이 된다.
* 선택도 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드의 비율을 말하며, 선택도에 총 레코드 수를 곱한 값은 '카디널리티' 이다. 따라서 선택도, 카디널리티가 높은 컬럼은 인덱스로 선택해도 효용가치가 없다.
10. 중복 인덱스
X01 : 계약ID + 청약일자
X02 : 계약ID + 청약일자 + 보험개시일자
X03 : 계약ID + 청약일자 + 보험개시일자 + 보험종료일자- X02의선두 칼럼이 X01 인덱스를 완전히 포함하고, X03 인덱스의 선두 칼럼은 X01, X02 인덱스를 전체 포함한다. = 완전 중복
- X01, X02 인덱스는 삭제해도 된다.
(AS-IS)
X01 : 계약ID + 청약일자
X02 : 계약ID + 보험개시일자
X03 : 계약ID + 보험종료일자
X04 : 계약ID + 데이터생성일시
(TO-BE)
X01 : 계약ID + 청약일자 + 보험개시일자 + 보험종료일자 + 데이터생성일시- AS-IS에서 선두 칼럼은 같지만, 두 번째 컬럼이 모두 다르다.
- 하지만, 계약 ID의 평균 카디널리티가 매우 낮다면, 사실상 중복이다. 그렇다면 인덱스를 4개나 만들 필요 없이 하나면 충분하다.
책 읽고 정리하는 과정
1. '친절한 SQL 튜닝' 책을 정독하며, 중요한 부분 바로바로 타이핑
2. 한 챕터가 끝나면, 타이핑한 부분을 복습하며 관련 구글링 및 재정리
운영 환경에서 가능한 일반적인 튜닝은 인덱스 칼럼 추가이다.
인덱스 탐색
-
인덱스 스캔 효율성
-
-> 중요) 인덱스 선행 컬럼이 조건절에 있어야 한다.
인덱스 스캔 효율성 측정
- SQL 트레이스를 통해 알 수 있다.
- rows , cr 값을 통해 읽은 블록 수와 레코드 수를 알 수 있다.
액세스 조건과 필터 조건
- 액세스 조건
-- 인덱스 스캔 범위를 결정하는 조건절
-- 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는 데 영향을 미치고, 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는 데 영향을 미치는 조건절
- 필터 조건 (p185 그림 참고)
-- 테이블로 액세스할지를 결정하는 조건절
-- 인덱스를 사용하든, Full Scan 을 하든 테이블 액세스 단계에서는 처리되는 조건절은 모두 필터 조건이다.
(p187 그림)
선행 칼럼이 모두 '=' 조건인 상태에서 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자에 상관없이 흩어진다.
- 인덱스 선행 컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문이다.
선행 컬럼이 모두 '=' 조건인 상태에서 첫번째 나타나는 범위검색 조건이 인덱스 스캔 범위를 결정한다.
- 첫번째 나타나는 범위검색 조건까지가 인덱스 액세스 조건이고, 나머지는 필터 조건이다.
- 가장 선두 칼럼이 범위 검색 조건이면, 그 조건이 스캔 범위를 결정한다. 따라서 이들 조건이 인덱스 액세스 조건이다. 나머지는 인덱스 칼럼 조건은 인덱스 필터 조건이다.
범위검색 조건 이후 조건절 컬럼은 스캔 범위를 줄이는 데 큰 역할을 하지 못한다.
- 좌변 칼럼이 가공된 조건절
- 왼쪽 '%' 또는 양쪽 '%' 기호를 사용한 like 조건절
- 같은 칼럼에 대한 조건절이 두 개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한 조건절
- OR Expansion 또는 INLIST ITERATOR 로 선택되지 못한 OR 또는 IN 조건절
3.3.5 인덱스 선행 칼럼이 등치(=) 조건이 아닐때 생기는 비효율
- 인덱스 스캔 효율성은 인덱스 칼럼을 조건절에 모두 등치 조건으로 사용할 때가 가장 좋다.
- 맨 마지막 조건만 범위 검색일 경우, 하나 더 많은 레코드를 조회하여 원하는 데이터를 가져올 수 있어 one-plus 스캔이 일어난다.
3.3.6 BETWEEN을 IN-List로 전환 (p 196)
- 범위 컬럼이 뒤로 갔음 좋겠지만, 운영 시스템에서는 인덱스를 재구성하기는 쉽지않다.
- 이때, BETWEEN 조건을 IN-List로 바꿔주면 큰 효과를 얻을 수 있다.
- IN 절안에 있는 갯수 만큼 인덱스 수직적 탐색을 하게된다. -> INLIST ITERATOR 오퍼레이션
- IN-LIST 개수 만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 칼럼을 '=' 조건으로 검색하므로 union all 한다.
- 하지만 IN-LIST 항목 개수가 늘어날 수 있다면, 전환하기 곤란해짐..
-- NL 방식의 조인문이나 서브 쿼리로 구현하면 된다. (물론, IN-LIST 값들을 코드 테이블로 관리하고 있을 경우만 가능)
- 주의할 점으로는 IN-LIST 개수가 많지 않아야 한다. BETWEEN 조건 때문에 리프 블록을 많이 스캔하는 비효율 보다 IN-LIST 개수 만큼 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있다. 특히 루트에서 브랜치 블록까지 Depth가 깊을 때 그렇다.
- 인덱스 스캔 과정에 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다는 사실!!!
-- p198 쿼리 확인
- BETWEEN 조건 때문에 인덱스를 비효율적으로 스캔하더라도 블록 I/O 측면에서는 대개 소량에 그치는 경우가 많다. 인덱스 리프 블록에는 테이블 블록과 달리 매우 많은 레코드가 담기기 때문이다. 만약 IN-LIST 개수가 많으면 수직적 탐색 과정에서 많은 블록을 읽게 된다. 데이터 ㅜㄴ포나 수직적 탐색 비용을 따져보지 않고 BETWEEN 을 IN-LIST로 변환하면 안된다.
3.3.7 Index Skip Scan 활용
- Between 조건을 In-list 조건으로 변환하는 상황에서 조건절을 바꾸지 않고도 같은 효과를 낼 수 있다.
- Index Skip Scan 을 사용하면 됨 !
3.3.8 IN 조건은 '=' 인가
- 아니다.
select *
from 상품
where 고객번호 =: cust_no
and 상품ID in ('A', 'B', 'C')
1. 인덱스 [상품ID + 고객번호] tns
- 같은 고객번호가 상품 ID에 따라 흩어진 상태 (연속되지 않는 리프블록에 저장) -> IN-List Iterator 방식으로 푸는 것이 효과적
- 이 경우에는 IN 조건이 = 조건이 되었다, 인덱스를 수직적으로 세 번 탐색 그 과정에서 아홉개 블록을 읽는다.
2. 인덱스 [고객번호 + 상품ID]
- 상품ID 순으로 정렬된 상태로 같은 리프 블록에 저장, 상품ID 조건절은 필터로 처리하여, 고객번호만 액세스 조건이르모 한 블로만 스캔한다. 3~4개의 블록만 읽는다.
즉, IN 조건이 '='이기 위해서는 IN-List Iterator 방식으로 풀려야한다. 그렇지 않으면 IN 조건은 필터 조건이다.
3.3.9 BETWEEN 과 LIKE 스캔 범위 비교
- SELECT * FROM 월별고객판매집계 WHERE 판매월 LIKE '2019%'
- 단순한 표현
- SELECT * FROM 월별고객판매집계 WHERE 판매월 BETWEEN '201901' and '201912'
- 더 정확한 표현
- BETWEEN과 LIKE 모두 범위검색 조건, 둘다 범위검색 조건.
- LIKE 보다는 BETWEEN 을 사용하는 것이 낫다.
ex) 판매월 + 판매구분 순 인덱스 구성, 판매구분 A, B 존재
1. A : 90%, B : 10%
where 판매월 BETWEEN '201901' and '201912'
and 판매구분 = 'B'
where 판매월 LIKE '2019%'
and 판매구분 = 'B'
- 혹시라도 '201900' 이 저장돼 있다면 그 값도 읽어야 하므로 판매구분 = 'B' 인 지점으로 바로 시작할 수 없다.
P.210 그림 참고
2. A : 10%, B : 90%
where 판매월 BETWEEN '201901' and '201912'
and 판매구분 = 'A'
where 판매월 LIKE '2019%'
and 판매구분 = 'A'
- 혹시라도 '201913'이 저장돼 있다면 그 값도 읽어야 하므로 중간에 멈출 수 없다.
3.3.10 범위검색 조건을 남용할 때 생기는 비효율
- 코딩을 쉽게 하려고 이처럼 인덱스 칼럼에 범위검색 조건을 남용하면 인덱스 스캔 비효율이 생긴다. 대량 테이블을 넓은 범위로 검색할 때는 그 영향이 매우 클 수도 있다.
3.3.11 다양한 옵션 조건 처리 방식의 장단점 비교
- OR 조건 활용
SELECT * FROM 거래
WHERE (:cust_id is null OR 고객ID = :cust_id)
AND 거래일자 BETWEEN :dt1 AND :dt2
- 옵티마이저에 의한 OR Expansion 쿼리 변환이 기본적으로 작동하지 않으므로, 옵션 조건 칼럼을 선두에 두고 [고객ID+거래일자] 스노으로 인덱스를 구성해도 이를 사용할 수 없다는 데에 있다.
- 인덱스 선두 칼럼에 대한 옵션 조건에 OR 조건을 사용해선 안된다.
- 인덱스에 포함되지 않은 칼럼에 옵션 조건은 어차피 테이블에서 필터링할 수 밖에 없으므로 그럴 떄는 OR 조건을 사용해도 무방하다.
-- 인덱스 액세스 조건으로 사용 불가
-- 인덱스 필터 조건으로도 사용 불가
-- 테이블 필터 조건으로만 사용가능
-- 단, 인덱스 구성 칼럼 중 하나 이상이 Not Null 칼럼이면, 18c 부터 인덱스 필터 조건으로 사용 가능
- LIKE/BETWEEN 조건 활용
- 필수 조건 칼럼을 인덱스 선두에 두고 액세스 조건으로 사용하면, LIKE/BETWEEN이 인덱스 필터 조건이어도 충분히 좋은 성능을 낼 수 있다.
- 문제는 필수조건이 변별력이 좋지 않을 때.
- LIKE/BETWEEN 패턴을 사용하고자 할 때 네 가지 경우에 속하는지 반드시 점검 (BETWEEN 조건은 1번과 2번만 점검)
-- 인덱스 선두 칼럼
SELECT * FROM 거래
WHERE 고객ID LIKE :cust_id || '%'
AND 거래일자 BETWEEN :dt1 AND :dt2
- 고객ID가 변별력이 매우 좋기 때문에 비교적 빠르게 조회되지만, 고객ID 값을 입력하지 않으면 인덱스에서 모든 거래 데이터를 스캔하면서 거래 일자 조건을 필터링하는 문제가 생긴다.
- 이 경우 인덱스를 [거래일자 + 고객ID] 순으로 구성해야한다. 하지만 이 경우는 고객ID 값이 입력되었을 때 생기는 비효울을 감수해야 한다.
-- NULL 허용 칼럼
SELECT * FROM 거래
WHERE 고객ID LIKE '%'
AND 거래일자 BETWEEN :dt1 AND :dt2
- 성능을 떠나 결과 집합에 오류가 생긴다.
- 거래일자 조건에 해당하는 모든 고객의 거래를 선택해야 하는 상황인데, 고객ID가 NULL 허용컬럼이고 실제 NULㅣ 값이 입력돼 있다면 그 데이터는 결과집합에 누락된다.
-- 숫자형 칼럼
- 숫자형이면서 인덱스 액세스 조건으로도 사용 가능한 칼럼에 대한 옵션 조건 처리는 LIKE 방식을 사용해서는 안된다.
[거래일자 + 고객ID] 순 인덱스
SELECT * FROM 거래
WHERE 거래일자 = :trd_dt
AND 고객ID LIKE :cust_id || '%'
-- :cust_id에 값을 입력했을 때 두 칼럼 모두 인덱스 액세스 조건으로 사용된다.
-- 하지만 고객ID가 숫자형 칼럼이면 자동 형변환이 일어나 고객ID가 필터 조건으로 사용된다, 특정 고객의 하루 치 거래를 조회하고 싶은 경우 하루 치거래를 모두 스캔하면서 고객ID 조건을 필터링해야한다.
SELECT * FROM 거래
WHERE 거래일자 = :trd_dt
AND to_char(고객ID) LIKE :cust_id || '%'
-- [고객ID + 거래일자] 순으로 구성한 인덱스는 아예 사용할 수 없다.
-- 가변 길이 칼럼
- LIKE 옵션ㅇ 조건에 사용할 때는 칼럼 값 길이가 고정적이어야 한다.
- 김훈을 입력했을 때 김훈남 고객도 같이 조회된다.
where 고객명 like :cust_nm || '%'
- 따라서 칼럼 값 길이가 가변적일 때는 변수 값 길이가 같은 레코드만 조회되도록 아래와 같이 조건절을 추가해야 한다.
where 고객명 like :cust_nm || '%'
and length(고객명) = length(nvl(:cust_nm, 고객명))
혹은
where 고객명 like :cust_nm
- UNION ALL 활용
select * from 거래
where :cust_id is null
and 거래일자 between :dt1 and :dt2
union all
select * from 거래
where :cust_id is not null
and 고객ID = :cust_id
and 거래일자 between :dt1 and :dt2
-- :cust_id 변수에 값을 입력했는지에 따라 위아래 SQL 중 어느 하나만 실행되게 하는 방식
-- :cust_ud 변수에 값을 입력하지 않으면 위쪽 브랜치에서 거래일자가 선두인 인덱스를 사용, 변수에 값이 있으면 아래쪽 브랜치에서 [고객ID + 거래일자] 인덱스 사용, 최적으로 사용하고 있다.
-- 하지만 단점은 SQL 코딩량이 길어진다.
- NVL/DECODE 함수 활용
SELECT * FROM 거래
WHERE 고객ID = NVL(:cust_id, 고객ID)
AND 거래일자 BETWEEN :dt1 and :dt2
SELECT * FROM 거래
WHERE 고객ID = DECODE(:cust_id, null, 고객ID, :cust_id)
AND 거래일자 BETWEEN :dt1 and :dt2
-- NVL, DECODE 둘 중 어느 것을 사용하든 실행계획은 똑같다. :cust_id가 입력되지 않으면 거래일자가 선두인 인덱스 사용, 입력되면 [고객ID + 거래일자] 인덱스 사용
-- 고객ID 칼럼을 함수 인자로 사용(인덱스 칼럼 가공)했는데도 인덱스를 사용할 수 있는 것은 OR Expansion 쿼리 변환이 일어났기 때문이다.
-- 만약 이 기능이 작동하지 않으면, 인덱스 액세스 조건으로 사용이 불가능했을 것이다.
-- 장점은 UNION ALL 보다 단순하면서도 UNION ALL과 같은 성능을 낸다.
-- 하지만, LIKE 패턴과 같이 NULL 허용 컬럼에 사용할 수 없다는 데에 있다. 조건절 변수에 NULL을 입력하면 NULL 인 레코드가 결과집합에서 누락되기 때문이다.
-- 옵션 조건 처리용 NVL/DECODE 함수를 여러 개 사용하면 그중 변별력이 가장 좋은 컬럼 기준으로 한 번만 OR Expansion이 일어난다 -> 따라서 OR Expansion 기준으로 선택되지 않으면 인덱스 구성 칼럼이어도 모두 필터 조건으로 처리된다. -> 따라서 모든 옵션 조건을 이 방식으로 처리할 수 없다.
Dynamic SQL
- 위 내용들을 잘 공감하지 못할 수 있다. 나야 나 .. ㅋㅋ
- iBatis 같은 SQL 매핑 프레임워에서는 옵션 조건을 잘 처리 할 수 있다. 옵션 조건에 '=' 연산자를 사용할 경우, 변별력 있는 칼럼을 액세스 조건으로 사용할 수 있게 인덱스만 잘 구성해주면 된다.
3.3.12 함수호출부하 해소를 위한 인덱스 구성 (별루)
- pL/SQL 사용자 정의함수는 일반적으로 매우 느리다.
select 회원번호, encryption(전화번호)
from 회원
where 회원번호 = :member_no
select 회원번호, encryption(전화번호)
from 회원
where 생월일 LIKE '01%'
한두번 함수를 호출할 때는 성능 차이를 잘 느끼지 못할 수 잇지만 대량의 데이터를 조회하는 경우 차이가 확연하다.
느린이유
- 가상머신 상에서 실행되는 인터프리터 언어, Native 코드로 완전이 컴파일된 내장 함수에 비해 느리다.
- 호출 시마다 컨텍스트 스위칭 발생
- 내장 SQL에 대한 Recursive Call 발생
3.4 인덱스 설계
3.4.1 인덱스 설계가 어려운 이유
- SQL 각각에 최적화된 인덱스를 마음껏 생성하면, SQL 튜닝과 인덱스 설계만큼 쉬운 일은 없겠지만, 인덱스를 많이 생성하다 보면, 테이블마다 인덱스가 수십 개씩 달리게 되고, 관리 비용뿐만 아니라 시스템 부하를 증가시킨다.
-- DML 성능 저하 (TPS 저하) , 데이터베이스 사이즈 증가 (디스크 공간 낭비) , 데이터베이스 관리 및 운영 비용 증가
- 신규 데이터가 입력할 때마다 인덱스 테이블에도 데이터가 추가되어야 하고, 인덱스는 정렬 상태도 유지해야 한다.
- 데이터를 삭제할 때도, 여섯 개 인덱스에서 레코드를 찾아 지워야 한다. 핵심 트랜잭션이 참조하는 테이블에 대한 DML 성능 저하는 TPS 저하로 이어지며, 꼭 필요하지 않는 인덱스로 인해 디스크 공간을 낭비하고 데이터베이스 사이즈가 커지는 만큼 백업, 복제, 재구성 등을 위한 운영 비용도 상승한다.
- 개별 쿼리 성능 뿐만 아니라 인덱스 개수를 최소화함으로써 DML 부하를 줄여야 하므로 인덱스 설계가 어렵다.
- 인덱스 추가는 시스템에 부하를 주고, 인덱스 변경은 운영 리스크가 크다.
3.4.2 가장 중요한 두 가지 선택 기준
- 가장 정상적이고 일반적인 방식은 Index Range Scan -> 인덱스 선두 컬럼을 조건절에 반드시 사용해야한다.
- 따라서 결합 인덱스를 구성할 때 첫번째 기준은 조건절에 항상 사용하거나 자주 사용하는 칼럼을 선정하는 것
- 두번째 기준은 그렇게 선정한 칼럼 중 '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 두어야 한다는 것.
3.4.3 스캔 효율성 이외의 판단 기준
- 수행 빈도, 업무상 중요도, 클러스터링 팩터, 데이터량, DML 부하, 저장 공간, 인덱스 관리 비용 등
- 수행 빈도, 수행 빈도가 매우 높은 SQL에는 앞서 설명한 공식을 이용해 최적의 인덱스를 구성해줘야 한다.
-- NL 조인에서 Outer (드라이빙 집합)에서 액세스하는 인덱스는 스캔 과정에서 비효율이 있더라도 큰 문제가 아닐 수 있다. 반대로 Inner 인덱스 스캔 과정에서 비효율이 있다면 큰 문제를 야기할 수 있다. ??? NL 조인 Inner 쪽 인덱스는 '=' 조건 칼럼을 선두에 두는 것이 중요하고, 될 수 있으면 테이블 액세스 없이 인덱스에서 필터링을 마치도록 구성해야 한다. (이해 안됨 ㅠ)
- 데이터량, 데이터량이 적다면 굳이 인덱스를 많이 만들 필요 없다. (Full Scan으로도 충분히 빠르기 때문에, 또한 인덱스를 많이 만들어도 저장 공간이나 트랜잭션 부하 측면에서 그다지 문제가 되지 않는다.)
-- 초대용량 테이블은 INSERT도 많다. 초당 DML 발생량은 트랜잭션 성능에 직접적인 영향을 준다.
3.4.4 공식을 초월한 전략적 설계
- 열 개 중 최적을 달성해야 할 가장 핵심적인 액세스 경로 한두 개를 전략적으로 선택해서 최적 인덱스를 설계하고, 나머지 액세스 경로는 약간의 비효율이 있더라도 목표 성능을 만족하는 수준으로 인덱스를 구성할 수 있어야 한다.
3.4.5 소트 연산을 생략하기 위한 칼럼 추가
- 인덱스는 항상 정렬 상태를 유지하므로 ORDER BY, GROUP BY를 위한 소트 연산을 생략할 수 있게 해 준다. 따라서 조건절에 사용하지 않는 칼럼이더라도 소트 연산을 생략할 목적으로 인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수 있다.
- '=' 조건절 칼럼은 ORDER BY 절에 없더라도 인덱스 구성에 포함할 수 있다. (위치는 앞뒤 중간 어디에 두어도 상관없다.), '=' 이 아닌 조건절 컬럼들은 반드시 ORDER BY 컬럼보다 뒤쪽에 두어야 한다.
- I/O를 최소화하면서도 소트 연산을 생략하려면, '=' 연산자로 사용한 조건절 칼럼 선정, ORDER BY 절에 기술한 칼럼 추가, '=' 연산자가 아닌 조건절 칼럼은 데이터 분포를고려해 추가 여부 결정
- IN 조건은 '='이 아니다. -> 소트 연산을 생략하려면 IN 조건절이 IN-List Iterator 방식으로 풀려선 안 된다. 즉 IN 조건절을 인덱스 액세스 조건으로 사용하면 안된다. 필터 조건으로 사용해야 한다.
3.4.6 결합 인덱스 선택도
- 인덱스 생성 여부를 결정할 떄는 선택도가 충분히 낮은지가 중요한 판단기준이다. (선택도란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말하며, 선택도에 총 레코드 수를 곱해서 '카디널리티'를 구한다.)
- 인덱스 선택도는 인덱스 칼럼을 모두 '=' 로 조회할 때 평균적으로 선택되는 비율을 의미하여, 선택도(카디널리티)가 높은 인덱스는 생성해봐야 효용가치가 별로 없다.
- 결합 인덱스 칼럼 간 순서를 정할 때도 선택도가 중요할까? 선택도가 낮은 칼럼을 앞에 두는 것? 성별과 고객번호 중 어떤 컬럼이 앞으로 오든 간에 인덱스 스캔 효율에 전혀 차이가 없다. 둘다 인덱스 액세스 조건이므로 어떤 컬럼이 앞으로 오든 인덱스 스캔 범위는 동일하다.
-- 중요한 것은 항상 사용하는 칼럼을 앞쪽에 두고 그 중 '=' 조건을 앞쪽에 위치하는 것. 그중 선택도가 낮은 칼럼을 앞쪽에 두려는 노력은 의미 없거나 오히려 손해일 수 있다.
- 결론적으로 인덱스 생성 여부를 결정할 대는 선택도가 매우 중요하지만, 칼럼 간 순서를 결정할 때는 각 칼럼의 선택도보다 필수 조건 여부, 연산자 현태가 더 중요한 판단 기준이다.
3.4.7 중복 인덱스 제거
X01 : 계약ID + 청약일자
X02 : 계약ID + 청약일자 + 보험개시일자
X03 : 계약ID + 청약일자 + 보험개시일자 + 보험종료일자
- X02 의 선두 칼럼이 X01 인덱스를 완전히 포함하고, X03 인덱스의 선두 칼럼은 X01, X02 인덱스를 전체 포함한다. '완전 중복' (X01, X02 인덱스는 삭제해도 된다.)
X01 : 계약ID + 청약일자
X02 : 계약ID + 보험개시일자
X03 : 계약ID + 보험종료일자
X04 : 계약ID + 데이터생성일시
- 선두 칼럼은 같지만, 두 번째 칼럼이 모두 다르다.
- 하지만, 계약 ID의 평균 카디널리티가 매우 낮다면 사실상 중복이다. 계약ID 평균 카디널리티가 5라고 가정하자. 계약ID를 '=' 조건으로 조회하면, 평균 다섯 건이 조회된다는 뜻. -> 그렇다면 이렇게 인덱스를 4개나 만들 필요 없이 하나면 충분하다. 완전중복을 대비해 이를 불완전 중복
X01 : 계약ID + 청약일자 + 보험개시일자 + 보험종료일자 + 데이터생성일시
'정리' 카테고리의 다른 글
| [친절한 SQL 튜닝] NL 조인, 소트 머지 조인, 해시 조인 (2) | 2024.06.16 |
|---|---|
| [친절한 SQL 튜닝] 인덱스 튜닝 (1) 테이블 액세스 최소화, 부분범위 처리 활용 (1) | 2024.05.05 |
| [친절한 SQL 튜닝] 인덱스 기본 (1) | 2024.04.21 |
| [친절한 SQL 튜닝] 데이터 저장 구조 및 I/O 메커니즘 (1) | 2024.02.03 |
| [Java] JVM 정리 (JVM의 개념, 실행과정, 구조) (0) | 2023.07.12 |
