인덱스 컬럼 결정과 가공

조시형님의 친절한 SQL 튜닝을 읽고 개인적으로 공부한 내용을 정리한 글입니다.

---

인덱스 스캔

 

데이터를 찾는 방법은 크게 2 가지로 나눌 수 있다.

 

1. 테이블 전체 스캔

2. 인덱스 스캔

 

테이블 전체 스캔은 말 그대로, 테이블 데이터가 저장되어 있는 블록 전체를 스캔한다.

 

인덱스 스캔은 인덱스로 설정한 기준에 따라 일정량을 스캔한다.

 

❓ 인덱스 스캔 방식이 항상 유리할까?

 

그렇지 않다.

 

💡 인덱스 스캔 방식은 큰 테이블에서 소량의 데이터를 찾고자 할때 유용하다.

 

예로, 전체 테이블 스캔을 진행하면 버퍼 캐시에 데이터가 존재하지 않을 시, 모든 레코드를 탐색하므로 오랜 시간이 소요될 수 있다.

 

하지만, 한번 가져오고 난 뒤에는 해당 테이블과 관련한 데이터는 물리적 I/O 없이 탐색할 수 있다.

 

인덱스 스캔 방식은 단발적인 쿼리를 수행하기에는 좋지만, 이전에 사용했던 인덱스 스캔이 아닌 다른 구조의 블록이 필요해지는 경우 비효율적일 수 있다.

 

 

❓ 인덱스는 소량의 데이터를 찾을 때 유용하다는 것은 알겠는데, 그러면 어떤 식으로 결정하는게 효율적인 방식일까?

 

만약, 시력이 1.0에서 1.5 사이 이면서 이름이 '홍길동' 인 데이터를 찾아야 한다고 가정해보자.

 

시력이 1.0~1.5인 학생은 50명이고 이름이 홍길동인 학생은 2명이라고 가정했을때,

 

1. 시력 순으로 정렬 -> 이름 순으로 정렬 

2. 이름 순으로 정렬 -> 시력 순으로 정렬

 

위와 같은 조건으로 정렬된 테이블 중, 당연히 2번 테이블이 효과적으로 탐색할 수 있다.

 

이름이 홍길동인 레코드의 시작점을 찾으면 2번만 비교하면 되지만,

 

1번의 경우 시력이 1.0인 레코드를 발견하고 50명 중 이름이 홍길동인 레코드를 또 탐색해야하기 때문이다.

 

이처럼, 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것이 중요하다.

 

또한, 학생 정보 테이블이 이름순으로 정렬되어 있다면, 시퀀셜 엑세스가 가능하므로 효율적으로 탐색할 수 있게 된다.

 

 

인덱스의 수직적 탐색과 수평적 탐색

 

수직적 탐색

조건을 만족하는 첫번째 레코드의 주소값을 찾는 과정이다.

 

즉, 인덱스 스캔 시작 지점을 찾는 과정이다.

 

수평적 탐색

수직적 탐색을 통해 인덱스 스캔을 시작할 위치를 정했으면,

 

조건을 만족하지 않는 데이터가 나올때까지 데이터를 탐색하는 것을 수평적 탐색이라고 한다.

 

 

🚨 자주하는 오해가 있다.

성별이 '여자' 이고 이름이 '유관순' 인 탐색한다고 가정했을 때,

[성별 + 이름] 로 인덱스를 구성한 경우

'여자'인 레코드 25개를 찾고 거기서 '유관순' 이름을 찾으므로 총 25번의 검사를 진행하고

[이름 + 성별] 로 인덱스를 구성한 경우,

'유관순'인 레코드 2개를 찾고, 거기서  '여자' 성별을 찾으므로 총 2번의 검사를 진행한다는 것이다.

따라서, 선택도(중복도)가 낮은 이름을 앞쪽에 두고 결합 인덱스를 생성해야 효율적이다 라는 것이다.

하지만, 실제로 B Tree 구조로 관리되는 인덱스는 위와 같은 단계를 거쳐서 탐색이 이루어지는 것이 아니라

💡 다단계 구조로, '여자' 들 중에서 '유관순' 을 찾는 것이 아니라 '여자' 이면서 '유관순' 인 레코드를 먼저 찾는다.

 

 

인덱스 컬럼을 가공하면 정상적으로 사용할 수 없는 이유?

 

인덱스로 지정한 컬럼을 그대로 사용하는 것이 아니라, 가공해서 사용하면 인덱스 스캔을 사용할 수 없고 풀 스캔을 사용해야한다.

 

이유는, 예시를 생각하면 간단하다.

 

인덱스가 생일 컬럼이고, 이를 기준으로 테이블 데이터가 정렬되어 있다고 가정했을 때,

 

1995년생 데이터를 탐색하기 위한 시작점을 찾는 것은 쉽지만

 

만약 12월생을 탐색하고자 할때에는, 1993년생에도 12월생이 있고 1994년생에도 12월생이 있기 때문에 인덱스 스캔 시작점을 찾을 수 없다.

 

like 문법을 사용하거나 or로 여러개의 칼럼 조건을 추가하는 경우도 마찬가지로 인덱스 스캔 시작점을 찾을 수 없어 인덱스 스캔을 활용할 수 없게된다.

 

💡 단,

SELECT * FROM 고객 WHERE 전화번호 in (:tel_no1, :tel_no2)​

위와 같이 조건절에 in 을 사용하면, 이전에 설명했던 것과 같은 이유로 인덱스 스캔이 불가능하지만

SELECT * FROM 고객 WHERE 전화번호 = :tel_no1
UNION ALL
SELECT * FROM 고객 WHERE 전화번호 = :tel_no2

위와 같은 방식이면 인덱스 스캔 2번을 반복한 다음에 합친 결과이므로 인덱스 스캔을 사용할 수 있다.

따라서, in 문법을 사용하면 옵티마이저는 바인드 조건 개수 번 만큼 인덱스 스캔을 반복하는 방식을 사용한다.

 

그리고, 인덱스 조건을 여러개로 설정한 경우, 인덱스 선두 컬럼이 조건절에 있어야 한다.

 

인덱스를 [이름 + 나이] 로 지정해두었다면, 나이를 기준으로는 인덱스 스캔이 불가능하다는 의미이다.

 

 

 

💡 또한, 정렬 연산을 생략할 수 있게 인덱스가 구성되어 있따면, ORDER BY 절은 생략하도록 옵티마이저는 계획한다.

 

예로, [ 장비 번호 + 변경일자 + 변경순번 ] 으로 인덱스를 구성한 경우

 

SELECT
	*
FROM
	장비
WHERE
	장비번호 = 'C'
ORDER BY
	변경일자, 변경순번

위와 같은 SQL을 전달 받을시,

 

인덱스의 선두 칼럼인 장비번호를 바탕으로 인덱스 스캔을 하게되면 변경일자와 변경순번은 알아서 정렬되어있으므로 

 

실행 계획 상, 소트 연산을 생략한다.

 

 

칼럼의 자동 형변환

문자형 데이터인 생년월일을 인덱스로 지정했을때,

 

SELECT
	*
FROM
	고객
WHERE
	생년월일 = 19981206

 

위 쿼리를 실행하면, 테이블 전체 스캔이 수행된다.

 

그 이유는

 

🚨 WHERE 조건절에서 생년월일은 문자형 칼럼인데 19981206 이라는 숫자형과 비교가 발생했고

 

문자형 데이터가 TO_NUMBER(생년월일) 함수를 통해 숫자형으로 변환이 되어 인덱스가 가공이 되었기 때문이다.

 

문자형 데이터인 생년월일은 운이 좋게 숫자로만 이루어져 자동 형변환이 되긴했지만, 숫자형으로 자동 형변환이 일어나지 않는 데이터의 경우는 에러도 발생할 수 있다.

 

따라서, 조건절에서 인덱스 데이터가 가공되지 않도록 주의해야한다.

 

💡 참고로 LIKE 문법의 경우 숫자형 데이터를 LIKE 문법을 사용하면 자동으로 문자형 데이터로 형변환이 이루어진다.

따라서, 숫자형 칼럼이 인덱스일때 WHERE 조건절에서 LIKE 문법을 사용하면 가공이 이루어지므로 인덱스 스캔 효율을 얻을 수 없다.