권순용의 실전 SQL 튜닝 4강 : 인덱스는 결합 인덱스로 선정하자
00:03 2017-01-05 목
4강 인덱스는 결합 인덱스로 선정하자
ㅁ 인트로
Q. 분포도가 좋은 컬럼을 앞에, 분포도가 나쁜 컬럼을 뒤에 둬야 하지 않나요?
A. 매우 위험한 선정이다! 결합 컬럼은 “분포도”가 아닌, “연산자”가 우선이 되어야 한다.
점 조건이 먼저, 선분 조건이 나중에 되도록 순서를 조정해야 한다.
ㅁ 용어정리
단일 컬럼 인덱스
– 하나의 컬럼으로만 생성된 인덱스
결합 컬럼 인덱스
– 하나의 컬럼이 아닌 여러 개의 컬럼으로 생성된 인덱스
점 조건 (Point Condition)
– Where 조건에서 =, IN 연산자를 사용한 조건의 컬럼
선분 조건 (Line Condition)
– Where 조건에서 =, IN을 제외한 LIKE, BETWEEN 등의 연산자를 사용한 조건의 컬럼
ㅁ 결합 컬럼 인덱스의 개념
(1) 특징
(2) 성능차이
하나의 컬럼으로 인덱스를 선정하는건 유용하지 않음.
인덱스가 col1 인덱스일 경우 -> (1) 반드시 where에 col1이 있어야 함.
인덱스를 col1+col2인덱스로 설정한다면 -> (1) where 절에 col1만 있어도 적용, (2) col1과 col2 있어도 적용. (3) 최적화 잘하면 col2 조건만 존재해도 인덱스 적용
전제 조건 : 결합 컬럼의 첫 번째 컬럼이 where 절에 조건으로 존재해야만 적용됨.
(단, 오라클 10g 이상부터는 where절의 첫 번째 컬럼이 아니어도 인덱스 적용 == 인덱스 SKIP SCAN이라 부름)
*** 인덱스는 컬럼 순서에 의해 성능차이가 있음.
ㅁ 해당 SQL에 대한 최적의 결합 컬럼 인덱스는?
SELECT 계좌번호, 입금자, 입금액
FROM 계좌
WHERE 계좌번호 LIKE ‘210%’
AND 연령 =20;
답 : 최적의 인덱스는 “계좌번호+연령”이 아니라, “연령+계좌번호” 인덱스이다.
전자는 처리범위 증가에 따라 성능 저하가 일어날 수 있다.
ㅁ 컬럼 순서에 의한 성능 차이(1)
SELECT 계좌번호, 입금자, 입금액
FROM 계좌
WHERE 개설년월 = ‘200803’
AND 연령 BETWEEN 20 AND 30;
일 경우,
==> 개설년월 + 연령 인덱스가 더 효과적이다.
연령 + 개선년월 인덱스는 성능이 떨어진다.
(점을 먼저 찾고, 선분을 나중에 찾는게 좋다)
ㅁ 컬럼 순서에 의한 성능 차이(2)
SELECT 계좌번호, 입금자, 입금액
FROM 계좌
WHERE 개설년월 BETWEEN ‘200802’ AND ‘200804’
AND 연령 = 20;
==> 연령 + 개설년월 인덱스가 더 효과적이다.
즉, 컬럼 순서에 의한 성능차이 (1), (2)를 종합해보았을 때,
연산자가 중요하다.
2. 결합 컬럼 인덱스의 선정
(1) 선분조건과 점 조건
(2) 결합 컬럼 인덱스 컬럼 순서 선정
(3) 점 조건으로 사용된 컬럼
(4) 랜덤 엑세스를 감소시키기 위핸 컬럼
(5) 정렬을 제거하기 위한 컬럼
(6) 분포도가 좋은 컬럼
ㅁ 선분 조건과 점 조건
선분조건 (Line Condition)
– Like
– Between
– <, >
점 조건 (Point Condition)
– =
– In
ㅁ 점 조건과 점 조건
SELECT 계좌번호, 입금자, 입금액
FROM 계좌
WHERE 개설년월 = ‘200803’
AND 연령 = 20;
인덱스의 형태 = 점 조건1 + 점 조건2 일때,
처리 범위를 감소시키는 조건 = 점 조건1, 점 조건2
(개설년월 + 연령 인덱스, 또는
연령 + 개설년월 인덱스 모두 동일한 처리 범위를 가지게 된다)
ㅁ 점 조건과 선분 조건의 비교
SELECT 계좌번호, 입금자, 입금액
FROM 계좌
WHERE 개설년월 = ‘200803’
AND 연령 BETWEEN 15 AND 28;
인덱스의 형태 = 점 조건 + 선분 조건
(개설년월 + 연령 인덱스 권장)
ㅁ 선분 조건과 점 조건의 비교
SELECT 계좌번호, 입금자, 입금액
FROM 계좌
WHERE 개설년월 BETWEEN ‘200802’ AND ‘200804’
AND 연령 = 25;
인덱스의 형태 = 선분 조건 + 점 조건 일 경우,
처리 범위를 감소시키는 조건 = 선분 조건만.
ㅁ 선분 조건과 선분 조건의 비교
SELECT 계좌번호, 입금자, 입금액
FROM 계좌
WHERE 개설년월 BETWEEN ‘200802’ AND ‘200804’
AND 연령 BETWEEN 15 AND 28;
인덱스의 형태 = 선분 조건1 + 선분 조건2
처리 범위를 감소시키는 조건 = 선분 조건1
ㅁ 인덱스의 형태 = 점 조건1 + 점 조건2 -> 처리 범위를 감소시키는 조건 = 점 조건1, 점 조건2 – A
인덱스의 형태 = 점 조건 + 선분 조건 -> 처리 범위를 감소시키는 조건 = 점 조건, 선분 조건 – B
인덱스의 형태 = 선분 조건 + 점 조건 -> 처리 범위를 감소시키는 조건 = 선분 조건 – C ===> *** C 는 B 로 변경하여야 한다.
인덱스의 형태 = 선분 조건1 + 선분 조건2 -> 처리 범위를 감소시키는 조건 = 선분 조건1 – D : 순서를 바꿔도 의미 없다.
※ 점 조건으로 조회되는 컬럼이 결합 컬럼 인덱스의 앞에 위치해야 함.
ㅁ 결합 컬럼 인덱스 컬럼 순서 선정
점 조건과 선분 조건
– 점 조건으로 사용된 컬럼이 결합 컬럼 인덱스에서 가장 앞에 위치해야 함
– 점 조건으로 사용되는 컬럼이 여러 개 존재한다면, 해당 조건들을 모두 앞으로 해야 함 (선분 조건은 뒤에 위치해야 함)
– 점 조건들에 의해 처리 범위가 감소함.
그 다음 1개의 선분 조건에 의해서 처리 범위가 감소함. 나머지 선분 조건들은 랜덤 엑세스를 제거하는 역할 수행
– 결합 컬럼 인덱스의 핵심은 “처리 범위를 얼마나 많이 감소시키는가”임.
랜덤 엑세스
– 랜덤 엑세스는 인덱스 엑세스 후 테이블을 엑세스하는 과정에서 발생하는 현상.
– 랜덤 엑세스의 증가는 성능 저하로 이어지므로 랜덤 엑세스를 감소시킬 수 있는 결합 컬럼 인덱스를 생성해야 함.
정렬의 제거
– 대용량의 데이터에 대한 정렬은 해당 시스템에 많은 부하 가중
– 결합 컬럼 인덱스를 이용하여 정렬을 제거할 수 있다면 이것만으로도 많은 성능을 향상시킬 수 있음
– 결합 컬럼 인덱스의 컬럼을 선정하는 단계에서, 정렬 제거도 항상 고려해야 함.
단일 컬럼의 분포도
– 결합 컬럼 인덱스의 컬럼 순서와 분포도는 해당 SQL 성능과 무관함
– 결합 컬럼 인덱스의 핵심(가장 중요한 항목)은 처리범위를 최소화 시키는 것.
– 단일 컬럼 분포도에 의해 결합 컬럼 인덱스 생성은 다른 SQL를 고려한 것임.
(== 다른 SQL을 고려한다면 분포도가 좋은 컬럼을 인덱스의 앞에 위치시키는 것이 유리함.)
ㅁ 점 조건으로 사용된 컬럼
– where 조건에서 점 조건으로 사용된 컬럼이 결합 컬럼 인덱스의 가장 앞에 위치
– 점 조건으로 사용된 컬럼이 여러 개라면 어느 컬럼이 먼저 위치해도 성능에 무관
ㅁ 랜덤 엑세스를 감소시키기 위한 컬럼
ㅁ 확인 랜덤 엑세스
– 확인 랜덤 엑세스는 WHERE 조건의 컬럼이 인덱스에 존재하지 않아
테이블을 엑세스한 후 해당 컬럼의 값을 확인하여
조건을 만족하면 결과로 추출하고
만족하지 않으면 해당 데이터를 버리는 역할을 수행함
SELECT 계좌번호, 입금자, 입금액
FROM 입금_목록
WHERE 구분 = ‘A’ ===> 확인 랜덤 엑세스
AND 계좌번호 = ‘115’;
계좌번호에만 인덱스가 걸려 있다면,
구분 컬럼에 확인 랜덤 엑세스가 발생한다.
(해결책 1 : 구분 + 계좌번호 인덱스 생성
해결책 2 : 계좌번호 + 구분 인덱스 생성)
ㅁ 정렬 랜덤 엑세스
Order By 절의 컬럼이 인덱스에 존재하지 않아 테이블을 엑세스하여 Order By 절의 컬럼 값을
확인한 후 정렬을 수행하는 경우 발생하는 랜덤 엑세스
SELECT 계좌번호, 입금액
FROM 입금_목록
WHERE 계좌번호 = ‘115’
ORDER BY 구분; ===> 정렬 랜덤 엑세스 발생
계좌번호 인덱스 일경우, 계좌번호 + 구분 인덱스로 변경하면 됨.
– Order By 의 컬럼을 인덱스에 추가하면 정렬 랜덤 엑세스는 제거됨
– 정렬 랜덤 인덱스를 발생시키는 컬럼은 처리 범위를 감소시킬 수 없는 컬럼이므로
인덱스에서의 위치는 중요하지 않음.
– 모든 정렬 랜덤 엑세스를 제거할 수는 없지만, 하나 또는 두 개의 컬럼을 인덱스에 추가함으로써
많은 정렬 랜덤 엑세스를 제거할 수 있다면 반드시 인덱스에 해당 컬럼을 추가하는 것을 고려해야 함.
ㅁ 추출 랜덤 엑세스
결과 데이터를 추출하기 위해 테이블을 엑세스하는 것. Select 절의 컬럼을 추출하기 위해
테이블을 엑세스하는 것을 의미함
SELECT 계좌번호, 입금액, 구분 FROM 입금_목록 WHERE 계좌번호 = ‘115’
셀렉트 절에 있는 모든 컬럼을 인덱스에 추가하면 되는데,
이렇게 되면 인덱스 관리에 어려움이 따를 수 있음.
*** 확인 랜덤 엑세스는 단일 블록 I/O에 대한 비효율 뿐만 아니라
추출되는 데이터를 감소시킬 수도 있으므로
비효율이 많이 발생함.
*** 확인 랜덤 엑세스를 제거하면 불필요한 랜덤 엑세스가 감소하여 성능이 향상됨
ㅁ 정렬을 제거하기 위한 컬럼
정렬 랜덤 엑세스 != 정렬의 제거 (전혀 다른 이야기)
정렬 랜덤 엑세스 : 정렬을 수행하는 컬럼이 인덱스에 존재하지 않기 때문에, 테이블을 엑세스하는 것을 의미한다.
정렬의 제거 : 정렬 랜덤 엑세스도 제거하지만, 실제 정렬이 수행되지 않게 하는 인덱스 선정.
SEELCT 입금자
FROM 입금_목록
WHERE 계좌번호 = ‘115’
ORDER BY 입금액;
ORDER BY 컬럼을 사용하지 않고, 인덱스 만으로 정렬을 해야 “정렬 제거” 된 것임.
SELECT 입금자
FROM 입금_목록
WHERE 계좌번호 = ‘115’;
해답 : 계좌번호+입금액 인덱스를 만들면 된다.
– 계좌번호 + 입금액 인덱스를 생성해보면, 동일한 계좌번호 컬럼의 값에 대해서는
입금액 컬럼의 값으로 정렬이 되어 있음.
– 위 SQL 에서 Order By 절을 생략하고 계좌번호 + 입금액 인덱스를 이용한다면
계좌번호 조건은 점 조건이므로 동일한 값만 엑세스하게 되며
추출되는 데이터는 입금액 컬럼의 값으로 자동 정렬되어 결과가 추출됨.
ㅁ 컬럼의 분포도 : 결합 인덱스를 선정하는 마지막 우선 순위임
SEELCT 계좌번호, 입금자, 입금액
FROM 임금_목록
WHERE 구분 ‘A’
AND 계좌번호 = ‘115’;
구분 컬럼 : 분포도 나쁨
계좌번호 컬럼 : 분포도 좋음 (해당 컬럼에 중복값이 적다는 의미임)
분포도가 좋은 컬럼을 앞에 위치시키는 것
– 점 조건들 사이의 우선순위임.
– 또는 선분 조건들 사이의 우선순위임.
ㅁ 해당 테이블을 엑세스하는 다른 유형의 SQL을 고려한다면,
분포도가 좋은 컬럼을 인덱스의 가장 앞에 위치시켜야 성능에 유리함..
ㅁ 보충 및 심화 학습
ㅁ 정렬 제거를 위한 인덱스
점 조건 + … + 점 조건 + Order By 절의 1번째 컬럼 + … + Order By 절의 n번째 컬럼 + … + 선분조건
Q. 다음 아래 SQL 에서 최적의 SQL은?
SEECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID =’10’
AND SALARY > 200;
(1) DEPARTMEM_ID+SALARY
(2) SALARY_DEPARTMENT_ID
(3) SALARY
($) DEPARTMENT_ID
답 : (1)
Q. 결합 컬럼 인덱스와 단일 컬럼 인덱스의 유연성은 어떨까요?
A. 단일 컬럼 인덱스를 생성한다면 인덱스는 하나의 컬럼으로 생성되므로
해당 컬럼이 WHERE 절에 조건으로 사용된다면
해당 인덱스를 이용하게 되면 WHER 조건에 맞지 않는다면 해당 인덱스를 이용하지 않게 된다.
이처럼 단일 컬럼 인덱스는 유연성이 나쁘지만
결합 컬럼 인덱스를 만든다면 여러 가지 상황이 발생할 수 있다.
인덱스의 첫 번째 컬럼이 WHERE 절에 본재하지 않아도 해당 인덱스를 최적으로 이용할 수도 있다.
이처럼 결합 컬럼 인덱스를 이용한다면 인덱스의 유연성은 높아진다.
ㅁ 퀴즈
1. 결합 컬럼 인덱스 선정 시 가장 중요하지 않은 것은?
(1) 컬럼의 분포도
(2) 랜덤 엑세스
(3) 조건들에 사용된 연산자
(4) 정렬 제거
답 : 1번
(“결합 컬럼 인덱스에서의 분포도”는 해당 SQL의 성능과 무관하다.
결합 컬럼 인덱스의 선정 순서는 점 조건과 선분 조건의 위치, 랜덤 엑세스, 정렬 제거, “단일 컬럼 “순으로 고려한다.)
03:47 2017-01-05 목