[MySQL] B-Tree 인덱스 구조와 인덱스 스캔

인덱스란?

☁️ 디스크 읽기 방식

CPU와 메모리는 전기적 특성을 띄고 있는 장치이기 때문에, 속도가 빠르다.
하지만 이에 반해 디스크와 같은 기계적 장치는 여전히 느리므로, 데이터베이스 성능 튜닝은 디스크 I/O를 얼마나 줄이느가 관건이라 할 수 있다.

HDD VS SSD

하드 디스크 드라이브는 기계식 장치이므로, 이를 대체하기 위해 나온 것이 전자식 저장 매체인 SSD(Solid State Drive)이다. SSD는 HDD에서 데이터 저장용 플래터인 원판을 제거하고, 플래시 메모리를 장착하고 있다.

🔖 플래시 메모리란?
전기적으로 데이터를 지우고, 다시 기록할 수 있는 비휘발성 컴퓨터 기억 장치

플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다. 여전히 속도는 메모리(D-Ram) 보다는 느리지만, 기계식 HDD 보다는 훨씬 빠르다(1000배 정도)는 장점을 가진다.

☁️ 랜덤 I/O VS 순차 I/O

사실 데이터를 읽는 속도는, 데이터의 위치를 찾고 접근하기까지의 시간에 비례한다. 즉, 디스크의 성능은 디스크 헤더의 위치 이동 없이 한번에 얼마나 많은 데이터를 읽고 기록하느냐에 결정되는 것이다. 이는 원판이 없는 SSD에서도 마찬가지이다.

만약 3개의 페이지를 기록하는 상황에서 각각 어떻게 작동하는지 살펴보자.

1. 랜덤 I/O

랜덤 I/O 에서는, 3개의 페이지 각각에 대해 시스템 콜을 요청한다. 따라서 디스크 헤더가 총 3번이나 움직이게 되어 속도가 매우 느리다.

인덱스 레인지 스캔이 주로 데이터를 읽기 위해 랜덤 I/O를 사용한다.

2. 순차 I/O

순차 I/O 에서는, 3개의 페이지를 하나의 시스템 콜 요청으로 처리한다. 따라서 디스크 헤더가 한 번만 움직여서 속도가 빠르다.

풀 테이블 스캔이 주로 데이터를 읽기 위해 순차 I/O를 사용한다. 따라서 큰 테이블의 레코드 대부분을 읽는 작업에서는, 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 최적화 될 때도 있다. (주로 서비스 트랜잭션 보다는 데이터 웨어하우스나 통계 작업에서 사용된다)

데이터베이스 대부분의 작업은, 이러한 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋, 바이너리 로그 버퍼, InnoDB 로그 버퍼 등의 기능이 내장되어 있다.

쿼리를 튜닝한다는 것은, 이러한 랜덤 I/O 발생 자체를 줄이는 것에 목적이 있다.
즉, 쿼리 요청에 대해 꼭 필요한 데이터만 읽도록 개선하는 것이다. (불필요한 데이터를 읽는 것을 막으면 데이터 양이 줄어들고, 그만큼 DB I/O 요청도 감소한다.)

☁️ 인덱스란?

인덱스란, 테이블의 모든 데이터를 검색하지 않고 빠르게 조회하기 위한 별도의 자료구조를 의미한다. 어떤 방식으로 구현되어 있길래 빠르게 조회할 수 있을까?

1. 테이블 칼럼의 값과 해당 레코드가 저장된 주소가 key -value 형태로 담겨있으므로 바로 접근할 수 있다.
2. 인덱스는 자바의 SortedList 와 같이 항상 정렬된 상태로 유지되므로 검색 속도가 빠르다. 하지만 새로운 인덱스 추가, 수정, 삭제 작업에 대해서는 오버헤드가 발생한다.

앞서서 말했듯이 InnoDB 스토리지 엔진에서는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후, 테이블의 레코드를 잠그는 방식으로 구현되어 있다. 따라서 UPDATE 와 DELETE 에서 사용할 수 있는 인덱스가 없다면 최악의 경우 테이블의 모든 레코드가 잠금이 걸리기 때문에 특히나 인덱스를 잘 설계하는 것이 매우 중요하다.

인덱스 종류

인덱스는 역할별로 크게 두가지로 구분할 수 있다.

1. 프라이머리 키
   레코드를 대표하는 칼럼의 값으로 만들어진 인덱스이다. 식별자라고도 하며, NULL 과 중복을 허용하지 않는다.

2. 세컨더리 인덱스
   프라이머리 키를 제외한 나머지 모든 인덱스를 의미한다.

인덱스를 검색하는 작업은, B-Tree 루트 노드부터 시작해 최종 리프 노드까지 이동하면서 작업을 수행하는데 SELECT 뿐만 아니라 UPDATE나 DELETE 를 처리하기 위해 해당 레코드를 먼저 검색해야 할 경우에도 사용된다.

인덱스 구조

인덱스 자료구조는, B-Tree 알고리즘이나 Hash 인덱스 알고리즘으로 구성되어 있다. B-Tree 인덱스는 칼럼의 값을 변형하지 않고 원본을 이용해 인덱싱 하며, Hash 인덱스는 칼럼의 값으로 해시값을 계산해서 인덱싱한다.

속도 측면에서는 당연히 트리 탐색 시간이 소요되지 않는 Hash 가 빠르지만, 값이 변형되는 단점으로 인해 100% 동등 일치가 아닌 prefix 일치나, 값의 일부만 검색하거나, 범위를 검색할 때에는 사용할 수 없다.

따라서 가장 범용적으로 사용되는 알고리즘은 B-Tree 이다.

☁️ B-Tree 인덱스

가장 범용적으로 사용되는 인덱스 알고리즘으로, B-Tree와 B+Tree 두 가지 종류가 있다. 주의할 점은, B 가 바이너리의 약자가 아닌 Balanced , 즉 균형 트리의 약자라는 것이다.

자식 노드의 개수가 이진 트리처럼 2개로 고정되어 있지 않고, 가변적이다.

🫧 Balanced Tree란?

기본 이진 탐색 트리는 데이터 양이 많아지면 아래와 같이 한 쪽으로 편향이 되어, 최악의 경우 시간 복잡도가 O(N) 이 될 수도 있다는 단점을 가진다.

이러한 단점을 극복하고자 나온 것이 균형 이진 탐색 트리 로, 최악의 경우에도 항상 O(logN) 의 성능을 보장한다. 노드의 삽입과 삭제가 일어나는 경우에, 자동으로 높이를 작게 유지하려고 조정하는 과정이 일어나기 때문이다.

균형 이진 탐색트리의 종류로는 AVL 트리 , 레드-블랙트리(Red-Black Tree) , B 트리, B+ 트리, B* 트리 등이 존재한다.

B-Tree 구조

최상위에 루트 노드가 존재하고, 하위에 자식 노드가 붙어있다.
가장 하위 노드를 리프 노드라고 하며, 리프 노드에는 실제 데이터 레코드를 찾아가기 위한 주소값을 가지고 있다다.

아래에서 볼 수 있듯이 인덱스 데이터와 실제 데이터 파일은 다른 영역에서 관리된다. 단, 프라이머리 키 인덱스는 같은 데이터 파일에 존재한다.

주의할점은 인덱스 키 값은 항상 정렬된 상태로 유지되지만, 데이터 파일 레코드는 순서대로 저장이 되지 않는다. 만약 순서대로 삽입되다가, 레코드가 삭제되어 빈 공간이 생기면 그다음 삽입은 삭제된 공간을 재활용하기 때문에 뒤죽박죽 될 수 있기 때문이다.

참고로 InnoDB 는 클러스터되어 디스크에 저장되는 것이 디폴트이기 때문에 기본적으로 PK 순서로 정렬되어 저장된다.

위의 그림에서 레코드 주소는 DBMS 종류나 MySQL의 스토리지 엔진에 따라 의미가 달라진다. MyISAM 에서는 리프 노드에서 바로 레코드 주소(실제 물리적인 주소)를 참조하고 있지만, InnoDB 에서는 프라이머리 키(PK)를 참조하고 있다.

따라서 인덱스를 통해 레코드를 읽을 때는 데이터 파일을 바로 찾아갈 수 없다. 먼저 1차적으로 세컨더리 인덱스 트리를 탐색하고, 얻은 PK 값을 바탕으로 프라이머리 키 인덱스를 한번 더 검색한다. 그리고 마지막으로 프라이머리 키 인덱스 리프 노드에 저장되어 있는 레코드를 읽는다.

간단히 생각하면 성능이 떨어질 것 같지만, 더 자세한거는 클러스터링 인덱스에서 살펴보자.

☁️ B-Tree 인덱스 사용에 영향을 미치는 요소

1. 인덱스를 구성하는 칼럼의 크기

디스크에 데이터를 저장하는 가장 기본 단위는 페이지(Block)이다.

• 디스크 읽기 및 쓰기 작업의 최소 단위
• InnoDB 스토리지 엔진 버퍼 풀에서 데이터 버퍼링의 기본 단위
• 인덱스가 관리되는 단위(루프 / 브랜치 / 리프)

인덱스 키 값이 길어지면 어떤 일이 발생할까?

1. 인덱스 칼럼의 크기가 크면, 그만큼 하나의 인덱스 페이지에 저장할 수 있는 키의 개수가 줄어든다. 이는 곧 줄어든 만큼 DB에 접근해 읽어야 하는 횟수가 늘어나 성능이 느려진다.

2. 인덱스를 캐시해두는 InnoDB 버퍼 풀 영역은 제한적이기 때문에, 캐시해둘 수 있는 인덱스 수가 줄어들어 메모리 효율이 떨어진다.

3. 하나의 인덱스 페이지가 담을 수 있는 개수가 적어져 B-Tree 깊이가 깊어져 디스크 읽기가 더 많이 필요해진다.

예를 들어, 주로 페이지는 16KB가 기본값이다. 이때 키가 16바이트, 자식 노드 주소가 12바이트라 하면 16 * 1024 / (16+12) 해서 총 585개를 저장할 수 있다.

2. 카디널리티: 선택도(기수성(Cardinality))

인덱스 선택도란, 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 즉 인덱스로 선정된 컬럼에 중복된 값이 많아질수록 선택도 값은 감소한다.

mysql> SELECT *
		FROM tb_test
        WHERE country='KOREA' AND city='SEOUL'

총 데이터가 만개라고 가정해보자.

첫 번째 경우는 칼럼의 유니크한 값의 개수가 10 개이다.
즉 인덱스를 통해 1000 건(10000 / 10)을 읽었는데, 실제 데이터는 1건일 때 나머지 999는 불필요가 데이터를 읽어 낭비가 존재하게 되는 셈이다.(누누히 말하지만 불필요한 데이터 읽기를 줄이는게 성능 최적화 핵심이다.)

따라서 카디널리티가 낮은 컬럼에는 인덱스를 걸지 않는 것이 좋다.

korea A -> 유니크가 1000건 데이터 중에 10개만 유니크(나머지 다 중복)
korea B
korea C
korea D
korea E
korea SEOUL
... * 1000

두 번째 경우는 유니크한 값의 개수가 1000 개이다.
즉, 인덱스를 통해 10 건(10000 / 1000) 을 읽고, 그 중 실제 데이터는 1건이기 때문에 9 건만 불필요하게 읽어 앞에 경우보다 성능이 더 좋다고 볼 수 있다.

korea SEOUL -> 1000건 모두 유니크
korea BUSAN
... * 10

3. 읽어야 하는 레코드 건수

헷갈리면 안되는게, 1건 기준으로는 인덱스를 통해 데이터를 읽는 것이 더 비용이 많이 드는 작업이다. 일반적인 DBMS 옵티마이저에서는 테이블에서 직접 1건을 읽는 것보다 4-5배 정도 더 비용이 많이 들 것으로 예측한다.

즉, 인덱스를 통해 데이터를 읽어오는 작업이 더 비효율적이려면 필터링 대상 데이터가 전체 테이블 레코드의 20-25% 를 넘어서면 된다.

만약 넘는 경우에는, 테이블을 모두 읽어서 필요한 레코드만 가려내는 필터링 방식이 더 효율적이다. 왜냐하면, 1건에 5배라고 하면 100개의 데이터 중에 20개의 데이터까지는 인덱스를 타는 것과 테이블 전체 검색 효과가 똑같기 때문이다. 그 이상이 넘어가면 테이블 전체 검색보다 성능이 낮아진다.

이러한 이유 때문에 사용자가 강제로 인덱스를 사용하도록 힌트를 줘도, 옵티마이저에서 알아서 테이블을 직접 읽는 방식으로 처리된다. 역시 직접 테스트해보자.

현재 first_name 에 인덱스가 걸려있으므로 using idex 가 보여야 하지만, 보이지 않으므로 테이블을 그냥 쭉 읽었다고 볼 수 있다. 왜냐면 총 172개 데이터중에 과반수가 넘는 80 개의 데이터를 필터링 하고자 하였기 때문이다.

데이터의 범위를 좀 줄여보면, 아래와 같이 인덱스를 타는 것을 볼 수 있다.

B-Tree 인덱스를 통한 데이터 읽기

SELECT * from employees WHERE fist_name BETWEEN 'Ebbe' AND 'Gad';

☁️ 인덱스 레인지 스캔

인덱스 레인지 스캔이란, 가장 대표적인 접근방식으로 검색해야 할 Between 과 같이 인덱스 범위가 결정되었을 때 사용하는 방식이다.

인덱스 레인지 스캔 동작 과정

1. 먼저, 탐색이 필요한 레코드의 시작 지점을 찾는다. 루트 노드부터 비교를 시작해, 리프 노드까지 찾아 들어가는 과정이다.

2. 리프 노드에 도달하면 해당 시작 위치부터 필요한 만큼 순서대로 레코드를 읽는다. 스캔하다가 페이지의 마지막에 다다르면, 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔한다.

3. 쿼리가 필요한 데이터가 모두 인덱스만으로 찾을 수 있는 경우(covering index)가 아니라면, 레코드 주소를 기반으로 실제 데이터 파일에 접근하는 과정이 일어난다. 중요한 점은 이 때 실제 데이터 파일 레코드 당 한 건 단위로 랜덤 I/O 가 일어난다는 것이다.

이러한 점 때문에 인덱스를 통해 데이터 레코드를 읽는 작업은, 비용이 많이 든다.

인덱스를 얼마나 탔는지 확인하는 방법

SHOW STATUS LIKE 'Handler_%';

• Handler_read_key : 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는 과정(인덱스 탐색)이 실행된 횟수
• Handler_read_next : 탐색 위치부터 필요한 만큼 인덱스를 읽은 레코드 건수(인덱스 스캔)

☁️ 인덱스 풀 스캔

인덱스 풀 스캔은, 인덱스 레인지 스캔과 다르게 리프 노드를 연결하는 링크드 리스트를 따라 처음부터 끝까지 모두 읽는 방식을 의미한다. 테이블 풀 스캔하고 다른 점이 없어 보이겠지만, 다음과 같은 두 가지에서 장점을 가진다.

1. 인덱스 크기는 테이블 크기보다 작으므로 적은 디스크 I/O로 쿼리를 처리할 수 있다.
2. 인덱스에 포함된 컬럼만으로 쿼리를 처리할 수 있다면 테이블의 레코드를 읽을 필요가 없어 랜덤 I/O를 방지할 수 있다.

인덱스 풀 스캔은 대표적으로, 쿼리의 조건절에 사용된 컬럼이 인덱스 첫 번째 컬럼이 아닌 경우 사용된다. 예를 들어 (A, B, C) 순서로 만들어져 있을 때 쿼리 조건절을 B 혹은 C 칼럼으로 검색하는 경우이다.

사실, 인덱스 풀 스캔 방식은 인덱스를 효율적으로, 잘 사용하지 못하는 방식이다.

☁️ 루스 인덱스 스캔

오라클 DBMS의 "인덱스 스킨 스캡" 과 작동 방식이 비슷하다.
루스 인덱스 스캔은, 느슨하게 듬성듬성 인덱스를 읽는 방식으로 처리된다. 일반적으로, GROUP BY 혹은 집합 함수 가운데 Min, Max 함수에 대해 최적화를 하는 경우에 사용된다.

mysql > SELECT dept_no, MIN(emp_no)
		FROM dept_emp
        WHERE dep_no BETWEEN 'd002' AND 'd004'
        GROUPY BY dept_no;

인덱스는 (dept_no, emp_no) 순으로 정렬되어 있기 때문에 dept_no 별로 첫 번째 레코드의 emp_no 값만 읽으면 된다. 즉 Min 값이므로 WHERE 조건의 모든 데이터를 읽지 않아도 된다는 것을 옵티마이저가 판단하여, 알아서 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동하게 된다.

☁️ 인덱스 스킵 스캔

다중 컬럼에서, 일반적으로 맨 첫번째에 있는 컬럼에 대한 비교 조건이 없는 경우는 인덱스를 타지 못한다. 예를 들어 (gender, birth_date) 순으로 인덱스를 생성했다고 하자. 아래는 어느 경우 인덱스를 탈 수 있고, 언제 탈 수 없는지를 보여준다.

SELECT gender, birth_date
FROM employees 
WHERE birth_date >= '1964-12-20';  // 인덱스를 타지 못함

첫 번째 칼럼에 대한 검색 조건이 없으므로 인덱스를 타지 못한다.

SELECT gender, birth_date
FROM employees 
WHERE gender='M' AND birth_date >= '1964-12-20';  // 인덱스를 탐

첫 번째 칼럼에 대한 검색 조건이 있으므로 인덱스를 탈 수 있다.

이렇게 인덱스 구성 컬럼의 순서는 매우 중요하다.

하지만 MySQL 8.0 버전부터는, 옵티마이저가 gender 칼럼을 건너뒤어서 birth_date 칼럼만으로 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔(Index Skip Scan) 최적화 기능이 도입되었다.

1. skip-scan 활성화

SET optimizer_switch='skip_scan=on';  // skip-scan 활성화

EXPLAIN
SELECT gender, birth_date
FROM employees 
WHERE birth_date >= '1964-12-20';

type=range 는 필요한 부분만 읽었다는 것을 의미하며, 57 개의 데이터만 스킵 스캔으로 가져온 것을 볼 수 있다. 어떻게 이게 가능한 것일까?

1. 루스 인덱스 스캔 방식으로 맨 첫번째 인덱스에 칼럼에 대해 모든 값을 추출한다.(중복 제외)
2. 추출한 값에 대해 인덱스 스킵 스캔을 실행한다.

해당 예제에서는 Gender 값이 'M' 과 'F' 만 있으므로, 아래와 같이 비슷한 형태로 최적화를 실행하게 된다.

SELECT gender, birth_date FROM employees
WHERE Gender='M' and birth_date >= '1964-12-20';  

SELECT gender, birth_date FROM employees
WHERE Gender='F' and birth_date >= '1964-12-20'; 

2. skip-scan 비활성화

SET optimizer_switch='skip_scan=off';  // skip-scan 활성화

type 값이 range 가 아니고 index 이므로 인덱스 풀 스캔이 일어나서 비효율적으로 사용이 되었다. 따라서, 172개의 모든 데이터를 읽어온 것을 볼 수 있다.

만약, 커버링 인덱스가 아니였고 SELECt * 로 테이블 모든 데이터를 가져와야 했다면 테이블 풀 스캔이 수행되었을 것이다.

🫧 주의사항

인덱스 스킵 스캔은, 아래 두 가지를 지켜야만 실행될 수 있다.

1. WHERE 조건절에, 조건이 없는 인덱스 선행 칼럼의 카디널리티가 작아야함
2. SELECT 대상이 인덱스에 존재하는 컬럼만으로 처리 가능한 경우(커버링 인덱스)
   

☁️ 다중 컬럼 인덱스

두 개 이상의 칼럼으로 구성된 인덱스를 의미하며, 복합 인덱스라고도 불린다. 인덱스의 후행 칼럼은 선행 칼럼에 의존해서 정렬되어있다는 것에 주의해야 한다. (그래서 인덱스 구성 칼럼의 순서가 중요하다)

아래 그림에서도 볼 수 있듯이, depth_no 에 의존해서 emp_no 가 정렬되어 있다. 따라서 emp_no 값의 정렬 순서가 빠르다고 하더라도, dept_no 의 정렬 순서가 늦으면 인덱스 뒤쪽에 위치하게 되는 것이다.

실제로 emp_no = 10003 이 인덱스 하단에 위치하고 있다.

☁️ B-Tree 인덱스 정렬 및 스캔 방향

인덱스 생성 시점에는 오름차순, 혹은 내림차순 한 방향으로만 실제로 정렬이 일어나지만 옵티마이저가 어떻게 최적화해서 어느 방향으로 읽느냐에 따라 또 다른 정렬 효과를 얻을 수 있다.

SELECT * from employees 
	ORDER BY first_name DESC LIMIT 5; 

예를 들어 위 쿼리는 오름차순으로 모든 데이터를 읽고 뒤 5개의 데이터를 가져오는게 아니라, 뒤에서부터 인덱스를 역순으로 읽으면서 필요한 5개의 데이터만 레코드만 가져오게 된다.

ORDER BY, MIN(), MAX() 함수 등의 최적화가 필요한 경우에도 옵티마이저는 인덱스 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어낸다.

내림차순 인덱스와 오름차순 인덱스

인덱스 역순 스캔이 인덱스 정순 스캔보다 조금 느리다. 기본 B-Tree 의 리프 노드에서, 하나의 페이지 내부서 레코드가 단방향 오름차순 형태로 만 연결이 되어 있기 때문이다.

소량의 레코드라면 둘 중 어느 것을 사용하던 상관 없지만, 많은 레코드를 조회하면서 빈번하게 실행되어야 하는 경우라면 아예 내림차순 인덱스로 구성을 역방향으로 하고, 위->아래로 정수 스캔 방식으로 읽는게 더 효율적이다.

☁️ B-Tree 인덱스는 어떤 경우에 효율적으로 잘 썼다고 소문이 날까?

WHERE, GROUP BY, ORDER BY 절이 어떤 경우에 인덱스를 어떤 방식으로 타는지 알아야, 데이터를 적게 탐색하게 하는 방향으로 쿼리를 잘 짤 수 있다.

먼저 인덱스 탐색 결정 조건은, 다음과 같이 두가지가 존재한다.

1. 작업 범위 결정 조건 : 비교 작업의 범위를 줄인다. 많을 수록 쿼리 처리 성능이 높아진다.
2. 필터링/체크 조건 : 범위를 줄이지 못하고 단순 조건에 맞는지 검사하는 용도로 사용된다.(성능이 높이지는 못한다)

1. 비교 조건

다중 칼럼 인덱스에서, 칼럼의 순서와 칼럼에 사용된 조건이 동등 비교인지 크다/작다 와 같은 범위 조건인지에 따라 효율성이 달라진다.

SELECT * FROM dept_emp WHERE dept_no='d002' AND emp_no >= 10114;

1. index 가 (dept_no, emp_no) 인 경우
   

dept_no가 d001면서 emp_no가 10144인 레코드를 찾고, dept_no가 아닐때까지 인덱스를 쭉 읽으면 된다. 즉 dept_no 와 emp_no 모두 검색 범위를 줄이는데 사용 되었다. 인덱스를 정상적으로 탔으며 총 44 개의 레코드만 검색되었다.

2. index 가 (emp_no, dept_no) 인 경우
   

emp_no가 10144 이상인 레코드를 찾고, dept_no가 d001이라는 조건에 맞는지 하나하나 검사해봐야 한다. 따라서 dept_no 는 위에서 말했던 필터링 조건으로 사용되었고, 인덱스를 못탔으며 총 53개의 레코드가 검색되었다.

2. 인덱스의 왼쪽 규칙

B-Tree 인덱스는 왼쪽 값에 기준해서 오른쪽 값이 정렬되어 있다. 컬럼이 하나일 때도, 다중 컬럼 인덱스 칼럼일때도 마찬가지이다. 그리고 이 규칙은 WHERE, GROUP BY, ORDER BY 모두 해당된다.

1. 인덱스 컬럼이 하나인 경우

SELECT * FROM employees WHERE first_name LIKE '%mer';

왼쪽 값부터 한 글자씩 비교하면서 일치하는 레코드를 찾아야 하는데, 왼쪽 부분이 고정되어있지 않으므로 전체 레코드를 탐색하게 되어 인덱스를 이용할 수 없는 쿼리가 된다.

위에 사진에서도 %mer 는 전체 테이블 스캔, A% 는 인덱스 레인지 스캔이 사용된 것을 볼 수 있다.

다시 기억해보자! 첫번째 쿼리는 찾으려고 하는 데이터가 20% 이상이므로 랜덤 I/O를 사용하는 인덱스가 아닌 순차 I/O 사용하는 테이블 스캔으로 최적화되었다.

2. 다중 컬럼인 경우

SELECT * FROM dept_emp WHERE emp_no >= 10114;

마찬가지로 인덱스가 (dept_no, emp_no) 칼럼 순서대로 생성되어 있다면 선행 칼럼인 dept_no 조건 없이는 인덱스를 효율적으로 사용할 수 없다.

3. 작업 범위 결정 조건으로 인덱스를 사용할 수 없는 경우

총 정리를 해보자. 다음과 같은 경우에서는, 범위를 줄이는 용도로 인덱스를 사용할 수 없다.

단일 컬럼

1. NOT-EQUAL 조건으로 비교될 경우
   <>, NOT IN, NOT BETWEEN, IS NOT NULL 조건이 포함된다.

WHERE column NOT IN (10, 11, 12);

2. LIKE '%??' 형태로 문자열 패턴이 비교될 경우

WHERE column LIKE '%승환';
WHERE column LIKE '_승환';
WHERE column LIKE '%승%';

3. 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우

WHERE SUBSTRING(colum, 1, 1) = 'X';
WHERE DAYOFMONTH(column) = 1;

4. 데이터 타입이 서로 다른 비교

WHERE char_column = 10;

다중 컬럼

INDEX ix_test (column1, column2, colum3 ... columnn)

다음과 같은 인덱스가 있다고 가정해보자.

1. 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우

• column1에 대한 조건이 없는 경우
• column1의 비교 조건이 인덱스 사용 불가 조건 중 하나인 경우

2. 작업 범위 결정 조건으로 인덱스를 사용할 수 있는 경우

• column1~column(i-1) 까지 동등 비교 형태('=' 혹은 "IN")
• column(i) 칼럼에 대해 다음 연산자 중 하나로 비교
  • 동등 비교
  • 크다 작다 형태(">", "<")
  • LIKE 좌측 일치 패턴(LIKE '승환 %')
• 이렇게 되면 columni 까지는 작업 범위 결정 조건, column(i+1)부터 나머지 까지 조건은 체크 조건으로 사용된다.