[MySQL] 인덱스 스캔 종류

 

 

들어가며

 

테스트는 MySQL 5.6.50 버전에서 진행됐으며

MySQL은 InnoDB 엔진을 사용하고 InnoDB 엔진은 인덱스 트리 구조를 B+Tree로 사용하고있으므로 B+Tree기준으로 정리하고자한다.

 

 

 

 

 

Index range scan

 

인덱스를 통해 B+Tree를 수직 탐색 후 leaf 노드레벨에서 필요한 범위까지 탐색하는 방식이다.

 

 

leaf 노드 레벨에서의 필요한 범위 탐색은 leaf 노드끼리는 연결리스트로 구성되어있기 때문에 가능하다.

 

CREATE TABLE `table1` (
  `table1_id` int(11) NOT NULL
  PRIMARY KEY (`table1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EXPLAIN SELECT * FROM table1 WHERE table1_id > 1;

 

<, <=, >, >=, between, IS NULL, IS NOT NULL 등 을 통한 범위 조건 설정시 Index range scan으로 수행되는 것을 확인할 수 있다.

 

MySQL은 Oracle과 다르게 인덱스에 NULL값을 저장할 수 있으며 IS NULL, IS NOT NULL 조건으로 인덱스를 사용할 수 있다. 옵티마이저 최적화에 따라 Index range scan을 사용할 수도 있고 다른 scan 방식을 사용할 수도 있다. 아래는 이 내용에 대한 포스팅이다.

https://hwannny.tistory.com/102

[MySQL, Oracle] IS NULL, IS NOT NULL 조건은 인덱스를 사용할까?

 

 

 

 

Index full scan

 

효율적으로 범위를 탐색하는 Index range scan과 달리 이름 그대로 인덱스에 저장된 데이터를 full scan 하는 방식이다.

 

 

첫 번째 leaf 노드를 수직 탐색 후 나머지 leaf 노드에 대해 순차적으로 전체 탐색을 진행한다.

 

EXPLAIN SELECT COUNT(*) FROM table1;

 

실행계획의 type 컬럼은 index으로 명시되어 있는 것을 볼 수 있다. WHERE 절에 조건이 없으므로 당연히 const, ref, range 등의 접근방식으로 인덱스를 사용할 수 없고 count 쿼리를 수행하기위해 전체의 데이터를 읽어야하므로 Index full scan 으로 수행된것을 확인할 수 있다.

 

type 컬럼의 값이 index라 해서 단순히 index가 사용됐구나라고 생각하면 안된다. index로 명시되어있다면 Index full scan을 수행한것이다. 추가적으로 Full table scan 보다는 Index full scan이 대용량 테이블 탐색이 더 효율적이기 때문에 옵티마이저가 알아서 어떤 scan을 할지 결정한다.

 

 

 

 

Index unique scan

 

EXPLAIN SELECT * FROM table1 WHERE table1_id = 25;

 

쿼리 수행시 하나의 값만 반환된다는 것을 보장할 때 사용되는 방식으로 Primary key나 Unique key로 설정된 컬럼 조건을 상수값, 동등조건으로 주었을 때 수행된다.

 

당연한 이야기지만 결합 인덱스일 경우 하나의 값만 반환하는 것을 보장하기 위해서는 조건에 인덱스에 포함된 중복이 허용되지 않는 모든 컬럼을 포함 및 상수 값, 동등조건으로 주었을 때 수행된다.