인덱스 스캔

선택도란?

참고로 선택도란 인덱스 컬럼의 값이 얼마나 유일한지에 대한 지표다. 성별컬럼은 남자와 여자로 이루어져있기에 값이 2개 뿐이라 선택도가 매우 낮다. 따라서 인덱스 컬럼은 선택도가 높은 컬럼으로 지정하는게 좋다.

 

비용이란?

비용이란 시스템의 성능, 안전성, 보안, 유지보수등에 지출되는 비용을 의미한다 DB에서 비용은 하드웨어, 소프트웨어, 인력, 보안, 유지보수 비용등의 요소가 포함되어있다.

 

수직적 탐색이란?

B+tree에서 Root 노드에서 시작해 필요한 데이터가 있는 리프 노드까지 아래로 내려가는 탐색

WHERE id = 100 같이 정확한 값을 찾을 때 id 인덱스 트리를 따라 100을 찾아간다.

 

수평적 탐색이란?

수직적 탐색에서 찾은 첫 번째 리프노드에서 시작하여 인접한 리프 노드들을 좌우로 연결 리스트를 따라 스캔하는 것

범위 조건에 맞는 데이터들을 연속적으로 읽어 올 때 사용한다.

WHERE age BETWEEN 20 AND 30처럼 age = 20인 리프 노드를 찾은 후 21, 22 .... 등의 데이터를 다음 리프 노드에서 찾느다.

 

1. 테이블 풀 스캔

 

인덱스를 사용하지 않고 데이터를 검색하는 방식

이 경우 해당 테이블의 모든 레코드를 순차적으로 조회한다. 순차 I/O다.

디스크 I/O의 비용이 많이 발생하여 대량 데이터 처리시 시스템 성능에 영향을 줄 수 있다.

 

많은 데이터를 가져오거나 인덱스가 있어도 인덱스를 사용하여 데이터를 가져오는 것보다 테이블 전체를 스캔하는 게 더 빠른 경우 옵티마이저가 선택한다.

 

2. 테이블 인덱스 스캔

인덱스를 사용하여 데이터를 검색하는 방식

 

3. 인덱스 풀 스캔

인덱스를 사용하지 않고 전체 인덱스를 읽어서 검색하는 방식

수직적 탐색 없이 인덱스 리프 블록 처음부터 끝까지 수평적으로 탐색한다. 

데이터가 적을 때는 빠르게 처리 될 수 있으나 데이터가 많으면 많은 시간이 걸릴 수 있다.

인덱스 풀 스캔은 DB의 성능에 영향을 미칠 수 있어 인덱스 풀 스캔이 발생하지 않도록 제어해야한다.

 

1. 인덱스를 사용하지 않고 전체 테이블을 검색할 때 발생되며 인덱스의 선택도가 매우 낮아 일부 레코드만 가져올 때 발생된다.

2. 최적의 인덱스가 없을 때 차선으로 선택된다.

 

보통 WHERE절에 인덱스 선두 컬럼이 없으면 옵티마이저는 테이블 풀 스캔을 고려한다. 하지만 테이블이 대용량이면 차선으로 인덱스 풀 스캔을 선택한다. 인덱스의 면적은 테이블이 차지하는 면적보다 훨씬 적기에 인덱스 스캔 단계에서 대부분의 레코드를 필터링하고 일부 테이블만 사용할 수 있다면 테이블 풀 스캔보다 인덱스 풀 스캔이 더 좋다. 하지만 ROWID를 통한 접근이 추가되면 인덱스 전체를 읽고 테이블로 행을 읽으러가기때문에 느려지며 랜덤 I/O가 발생해 느리다. 이러한 경우는 테이블 풀 스캔이 더 좋다.

 

조회 컬럼이 모두 인덱스에 있는 경우를 커버링 인덱스라고 하는데 이러한 경우에는 ROWID에 접근하지 않아 인덱스 풀 스캔이 테이블 풀 스캔보다 좋고 이런경우는 순차 I/O가 발생해 빠르다.

 

4. 인덱스 범위 스캔

인덱스의 일부만 스캔하는 방법이다. B-tree의 가장 일반적인 접근 방식이며 인덱스 루트부터 리프 블록까지 수직적 탐색을 하고 필요한 번위 만큼 수평적 탐색을 한다.

 

특정 범위의 레코드만 읽어 비용이 적게든다.

 

 

인덱스 범위 스캔은

1. WHERE절에서 범위 검색을 수행할 때 발생된다.

2. ORDER BY에서 정렬할 때 발생된다.

3. JOIN할 때 발생된다.

 

그리고 여러개의 컬럼이 인덱스에 묶여있으면 선두컬럼을 가공하지 않아야한다.

예를들어 2024-01-01 이게 인덱스에 들어있으면 WHERE DATE(created_at) = '2024-01-01' 이런식으로 가공하면 안된다는 뜻 

WHERE created_at >= '2024-01-01'  AND created_at <  '2024-01-02' 이렇게해야 발생된다.

 

만약 선두 컬럼이 가공이 되지 않았지만 두번째 컬럼이 가공이 되었으면 인덱스 범위 스캔은 발생하지만 두번째 컬럼은 인덱스 탐색에 못쓰고 필터로 사용된다. 그리고 인덱스의 선택도가 매우 낮으면 인덱스 풀 스캔이 발생할 수 있다.

 

5. 인덱스 루스 스캔

인덱스 범위 스캔과 비슷하게 특정 인덱스 범위만 스캔하지만 중간중간 필요 없는 인덱스 키 값을 건너뛰고 다음으로 넘어가서 검색한다.

 

인덱스 루스 스캔은 WHERE절에 LIKE를 사용하여 특정 문자열을 검색하는 경우 발생한다.

 

 

6. 인덱스 유니크 스캔

인덱스에서 중복되지 않는 유일한 값을 검색할 때 발생된다. 하나의 값만 읽어 들여 비용이 가장 적게 든다.

수직적 탐색으로만 데이터를 찾는 방식이다.

 

유니크 인덱스는 유일성을 보장하여 인덱스 풀 스캔보다 빠르지만 단일 값을 검색할 때만 사용할 수 있어 제한적인 경우 사용된다.

 

인덱스 유니크 스캔은 PK또는 유니크 인덱스를 = 조건으로 검색할 때 발생한다.

또는 인덱스에서 단일 값을 검색할 때 발생한다.

 

유니크 인덱스라고 해도 between > < like 같은 범위 조건으로 검색하면 인덱스 범위 스캔으로 처리된다. 왜냐하면 수직적 탐색으로만 해당 레코드를 찾을 수 없기 때문이다.

 

7. 인덱스 병합 스캔

두 개 이상의 인덱스를 병합해서 검색하는 방법이다.

각 인덱스를 병합하는데 시간이 걸려 전체적인 속도가 느릴 수 있으나 각각의 인덱스를 사용하는 것보다는 효율적이다.

 

인덱스 병합 스캔은 OR 조건이 포함된 쿼리에서 각각의 조건에 해당하는 인덱스를 사용하는 경우에 발생한다.

또는 UNION ALL 연산자를 수행하는 경우에 발생한다.