[MySQL] NULL은 INDEX를 탈까?

[스포일러]

결론부터 말하자면... 

MyISAM, InnoDB, BDB, MEMORY 엔진을 사용할 경우,

NULL도 INDEX를 탄다 !!!

아래는 그 테스트와 MySQL 리퍼런스 링크에 관련된 내용이다.

[문제 제기]

요즘 나의 궁금중 중 하나가, 'MySQL에서 NULL 값이 인덱스를 탈까?' 라는 것이었다.

참고로,

오라클의 경우에는 NULL 일 경우 인덱스를 타지 않고,

그로 인해 NULL일 경우를 검색해야 한다면, FULL SCAN을 해야 한다고 알고 있다.

그래서인지 주위에서는 억지로 인덱스를 태우기 위해

DEFAULT '' 를 속성의 기본값으로 사용하는 경우를 볼 수 있었다.

흠... 평소에도 쓸데 없이 NULL을 사용하지 않고 ''를 사용하던 것에 불만이었던 나...

게다가 얼마 전에 읽었던, 

The Top 20 Design Tips For MySQL Enterprise Data Architects 라는 짧은 PPT에서는 

'필요 없는 경우에 굳이 NOT NULL 을 쓰는 것은 안 좋은 경우' 라고 얘기했기에,

'그럼 한 번 NULL이 인덱스를 타느냐 안 타느냐를 가지고 테스트 해보자'

라는 생각을 하게 되었다.

[테스트]

1. 테이블 준비 및 값 넣기

 1.1. 임시 테이블 생성

-- ---------------------------------------------------------------

CREATE TABLE `nulltest` (

      `id` int(11) NOT NULL auto_increment,

      `col1` varchar(50) default NULL, 

      `col2` varchar(50) default NULL, 

      PRIMARY KEY(`id`), 

      KEY `nulltest_cols` (`col1`, `col2`) 

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

-- ---------------------------------------------------------------

 1.2. 임시 테이블에 널 값의 교차적인 값 입력

(아래 프로시저는 주석을 모두 제거해야 컴파일 됩니다. ^^;;)

-- ---------------------------------------------------------------

CREATE PROCEDURE `p_nulltest`()

BEGIN

 DECLARE number INT DEFAULT 0; --  반복 회수를 위한 변수

 SET @number = 1;  -- 1로 초기화

  WHILE @number < 100000 DO  -- 100000번 수행

   INSERT INTO nulltest (col1,col2) VALUES (

           -- 2로 나누어질 경우 NULL, 그렇지 않을 경우 랜덤숫자를 생성하여 입력

           (CASE mod(@number,2)  WHEN 0 THEN NULL ELSE CASE(ROUND(RAND()*10000) AS CHAR) END), 

           -- 3로 나누어질 경우 NULL, 그렇지 않을 경우 랜덤숫자를 생성하여 입력

           (CASE MOD(@number,3)  WHEN 0 THEN NULL ELSE CAST(ROUND(RAND()*10000) AS CHAR) END) );

   SET @number = @number+1; -- 변수 1증가

  END WHILE;

 END

-- ---------------------------------------------------------------

 1.3. 테스트 수행 

  1.3.1. col1 이 NULL인 경우 인덱스를 타는가?

EXPLAIN SELECT id FROM nulltest WHERE col1 IS NULL;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE nulltest ref nulltest_cols nulltest_cols 153 const 50278 Using where; Using index

=> 인덱스를 탄다.

 1.3.2. col1, col2 가 NULL인 경우 인덱스를 타는가?

EXPLAIN SELECT id FROM nulltest WHERE col1 IS NULL AND col2 IS NULL;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE nulltest ref nulltest_cols nulltest_cols 306 const,const 13298 Using where; Using index

=> 역시 인덱스를 탄다.

 1.4. 분석

 위의 실행계획들을 보면, 둘다 인덱스를 이용하는 것을 볼 수 있으며,

 이로 인해 NULL값도 인덱스에 들어갈 수 있다는 것을 알 수 있다.

[참고 문서]

나.. 웬지 괜히 테스트 한 듯...

MySQL 외국 리퍼런스를 보면.. (난 영어 능력이 딸려서, 한글만 검색하는뎅 ㅠ,.ㅠ)

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

In MySQL 5.0 :

 * 오직 MyISAM, InnoDB, BDB, MEMORY 에서만 인덱스에 NULL을 더 할 수 있다.

 * 오직 MyISAM, InnoDB, BDB 에서는 BLOB, TEXT를 INDEX로 사용할 수 있다. 

   (사족이지만 이것도 좋은 정보)

라고 나온다....

으흐흑~ ㅠ.ㅠ 

이넘의 저질 영어 울렁병... 언제나 고칠런지....

담부턴... 영어 리퍼런스도 항상 같이 보도록 노력하자..