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

 

 

들어가며

 

DBMS는 인덱스 구성시 NULL 값은 저장하지 않는다고 알고있지만 이는 Oracle에 해당되는 내용이며

MyISAM, InnoDB 등 에서는 Oracle과 다르게 인덱스 구성시 NULL 값을 저장한다. NULL 값을 저장하므로써 인덱스 수행에 NULL 조건을 사용할 수 있는 이야기가 된다.

 

따라서 Oracle과 InnoDB를 사용하는 MySQL이 NULL값을 Index에 저장하는지 여부에 따른 IS NULL, IS NOT NULL 수행 방식에 대해 정리해보고자 한다.

 

테스트는 MySQL 5.6.50 버전에서 진행됐다.

 

 

 

 

Oracle

 

1. IS NOT NULL

 

SELECT * FROM table1 WHERE col_1 IS NOT NULL;

 

col_1으로 단일 컬럼 인덱스 구성 후 위의 쿼리를 수행할 경우 Oracle은 인덱스에 NULL 값을 저장 하지 않기 때문에
옵티마이저가 NULL이 아닌 row에 대해 Full table scan을 하거나 대용량 테이블일경우 최적화에 따라 Index full scan을 수행한다.

 

 

2. IS NULL

 

IS NULL도 마찬가지로 Oracle은 인덱스에 NULL 값을 저장 하지 않기 때문에 IS NOT NULL과 동일하게 옵티마이저에 의해 Full table scan 또는 Index full scan이 수행된다.

 

(Oracle 환경도 추후에 구축해서 테스트를 진행해봐야겠다..)

 

 

 

 

MySQL

 

1. IS NOT NULL

 

MySQL은 인덱스에 NULL 값을 저장하며 NULL 값을 통해 인덱스 사용이 가능하다.

 

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

EXPLAIN SELECT * FROM table1 WHERE col_1 IS NOT NULL;

 

위의 쿼리는 인덱스에 저장된 NULL값을 사용하여 쿼리가 수행 됐기 때문에 실행계획 내용중 type 컬럼값이 range임으로 Index range scan이 수행됨을 확인할 수 있다.

 

 

2. IS NULL

 

EXPLAIN SELECT * FROM table1 WHERE col_1 IS NULL;

 

IS NULL 조건으로 수행시 IS NOT NULL 조건과 동일하게 Index range scan이 수행될것이라고 예상했지만 type 컬럼값을 확인해보면 ref 접근 방식으로 수행한것을 확인할 수 있다. 즉, ref 컬럼 값이 const임으로 옵티마이저가 col_1 컬럼이 NULL인 row들을 range scan한것이 아닌 NULL값을 상수값으로 취급하여 조회하도록 실행계획을 구성하지 않았나 싶다.

 

그러나 Index range scan 이라 하면 const, ref, range 이 세 가지 접근 방법을 묶어서 지칭 한다. "인덱스를 효율적으로 사용한다" 또는 "범위 제한 조건으로 인덱스를 사용한다"는 표현은 모두 이 세가지 접근 방법을 의미하며 일반적으로 '인덱스 레인지 스캔' 또는 '레인지 스캔'으로 언급할 때가 많다.

출처: Real Mysql