NULLABLE 컬럼에 대한 인덱스 사용 ( Oracle vs MySQL)

* 요약 

오라클에서는 NULL 값을 인덱스에 저장하지 않는다.

따라서 오라클에서는 NULL 이 허용된 컬럼에 대해 인덱스를 생성해도

IS NULL 혹은 IS NOT NULL 조건으로 조회시 인덱스를 사용할 수 없고, group-by 혹은 distinct 사용시 인덱스를 사용할 수 없다.

(NULL이 있는지 없는지 FULL SCAN을 해야만 알 수 있으므로)

 

하지만 MySQL/Maria 에서는 NULL 값도 인덱스에 저장한다.

따라서 컬럼을 IS NULL 혹은 IS NOT NULL로 비교해서 인덱스를 사용할 수 있고,

group-by 및 distinct 사용시 인덱스를 사용할 수 있다.

• https://dev.mysql.com/doc/refman/8.0/en/is-null-optimization.html

 

1. Oracle

 

1.1 NULLABLE 컬럼에 인덱스 생성 시 조건 별 인덱스 사용 여부

 

Case 1. DISTINCT 조건에서 인덱스 사용 여부

 

400만건 정도 되는 테이블이 있다.

날짜별로 서버별  Oracle Segment를 수집하는 테이블이다.

 

해당 테이블에서 distinct한 서버명을 찾기 위해서 다음과 같은 쿼리를 수행한다.

select distinct(host_name) from dbacom.DB_SPACE_ORACLE_SEGMENT A

하지만 테이블의 건수가 늘어날수록 해당 쿼리의 수행 속도가 느려지고 있다.

 

따라서 host_name에 대한 인덱스를 생성하여 속도를 개선하려고 했다.

create index dbacom.IX_DB_SPACE_ORACLE_SEGMENT_01 on dbacom.DB_SPACE_ORACLE_SEGMENT (host_name)​

 

하지만 인덱스를 생성해도 TABLE FULL 스캔을 하는 실행 계획으로 표시된다.

select distinct(host_name) from dbacom.DB_SPACE_ORACLE_SEGMENT A

 

오라클은 NULL 값에 대해 인덱스를 생성하지 않기 때문에 NULL 값이 허용된 컬럼은 distinct 시 인덱스를 사용할 수 없다. (NULL이 있는지 없는지 FULL SCAN을 해야만 알 수 있으므로)

 

Case 2. GROUP BY 조건에서 인덱스 사용 여부

select host_name from dbacom.DB_SPACE_ORACLE_SEGMENT group by host_name

인덱스를 사용하지 못하는 것을 확인할 수 있다.

 

Case 3. IS NULL 조건에서 인덱스 사용 여부

select host_name from dbacom.DB_SPACE_ORACLE_SEGMENT where host_name IS NULL

인덱스를 사용하지 못하는 것을 확인할 수 있다.

 

1.2 NOT NULL 컬럼에 인덱스 생성 시 조건 별 인덱스 사용 여부

 

컬럼 값이 NOT NULL 일경우는 distinct 및 group by 조건에서 인덱스를 탈 수 있는지 확인해 보았다.

위의 예제에서 host_name은 not null값이 없는 컬럼이기 때문에 속성을 not null로 변경해주었다.

select host_name from dbacom.DB_SPACE_ORACLE_SEGMENT where host_name IS NULL -- 결과값 0
-- 컬럼 속성 변경
alter table dbacom.DB_SPACE_ORACLE_SEGMENT modify host_name not null

 

Case 1. DISTINCT 조건에서 인덱스 사용 여부

select distinct(host_name) from dbacom.DB_SPACE_ORACLE_SEGMENT A

NOT NULL 컬럼에 대해서는 DISTINCT 사용시 INDEX를 사용하는 것을 확인할 수 있다.

-> FULL SCAN 비용 : 17K -> 인덱스 사용시 3K 로 감소

왜 range스캔을 하지 않고 FULL SCAN을 하는지 모르겠다.. 아시는 분 댓글 부탁드립니다!

 

Case 2. GROUP BY 조건에서 인덱스 사용 여부

select host_name from dbacom.DB_SPACE_ORACLE_SEGMENT group by host_name

group by 에서도 인덱스를 사용하는 것을 확인할 수 있다.

 

Case 3. IS NULL 조건에서 인덱스 사용 여부

select host_name from dbacom.DB_SPACE_ORACLE_SEGMENT where host_name IS NULL

IS NULL 조건에서도 인덱스를 사용하는 것을 확인할 수 있다.

 

2. MariaDB

 

2.1 NULLABLE 컬럼에 인덱스 생성 시 조건 별 인덱스 사용 여부

먼저 MariaDB에 테스트 테이블을 생성한다

• 테스트를 위해 컬럼 하나는 NULL 허용, 하나는 NOT NULL로 생성

create table distinct_test(
host_name varchar(10),
test_data varchar(10) not null
)

그리고 20만건 정도의 테스트 데이터를 INSERT한다

DROP PROCEDURE IF EXISTS loopInsert
CREATE PROCEDURE loopInsert()
BEGIN
DECLARE i INT DEFAULT 1;

WHILE i <= 50000 DO
INSERT INTO distinct_test(host_name,test_data)
VALUES('host', 'data1');
SET i = i + 1;
END WHILE;
WHILE i <= 100000 DO
INSERT INTO distinct_test(host_name,test_data)
VALUES('host2', 'data2');
SET i = i + 1;
END WHILE;
WHILE i <= 150000 DO
INSERT INTO distinct_test(host_name,test_data)
VALUES('host3', 'data3');
SET i = i + 1;
END WHILE;
WHILE i <= 200000 DO
INSERT INTO distinct_test(host_name,test_data)
VALUES('host4', 'data4');
SET i = i + 1;
END WHILE;
end

-- 실행
CALL loopInsert;

확인을 위해 NULL 값을 가진 데이터도 INSERT한다

INSERT INTO distinct_test(test_data) VALUES('data4');

NULLABLE 컬럼에 인덱스 생성

create index IX_host_name on distinct_test(host_name)

 

Case 1. DISTINCT 조건에서 인덱스 사용 여부

analyze select distinct(host_name) from distinct_test

 

실행 계획을 보면 생성한 인덱스를 사용하여 distinct를 수행한 것을 알 수 있다. r_rows가 4인 것으로 봐서, 실제로 4건의 레코드만 읽어와서 처리한 것을 알 수 있다.

• rows : 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지에 대한 예측 값
• Using index for group-by 는 루스(Loose) 인덱스 스캔방식으로 통해 인덱스의 레코드를 건너 뛰면서 필요한 부분만 읽어서 가져왔다는 것을 의미한다.

따라서 NULL 이 가능한 컬럼이라 하더라도, DISTINCT시 인덱스를 사용할 수 있는 것을 확인할 수 있다.

 

Case 2. GROUP BY 조건에서 인덱스 사용 여부

analyze select host_name from distinct_test group by host_name

group by 에서도 동일하게 인덱스를 사용해서 처리하는 것을 확인할 수 있다.

 

Case 3. IS NULL 조건에서 인덱스 사용 여부

analyze select host_name from distinct_test where host_name is null

 

1건의 NULL 값만 INSERT 했기 때문에 인덱스를 사용해서 정확히 1건만 읽어서 처리한 것을 확인 할 수 있다.

 

 

2.2 NOT NULL 컬럼에 인덱스 생성 시 조건 별 인덱스 사용 여부

NULLABLE 컬럼에서도 인덱스 사용이 가능했으므로, NOT NULL 컬럼에 대해서도 INDEX 사용할 것으로 생각된다.