DB 인덱스 생성의 사이드 이펙트(with Mysql)

실제로 DB를 다루게 되는 상황에서 빠질 수 없는 것이 바로 인덱스이다.

인덱스를 통하면 특정 조건에서 데이터의 조회 성능이 비약적으로 증가한다는 것은 누구나 알고 있는 사실이다.

그러면서도, 인덱스를 남발하면 안된다는 것 또한 다들 알고있다.

이 때 등장하는 논리가 바로 인덱스가 차지하는 공간적 비용과 데이터 삽입 시 추가적으로 발생하는 시간적 비용에 대한 비중을 무시할 수 없다는 것이다.

그런데 막상 이렇게 겁만 주고 실제로 그 정도가 어느 정도인지 알려주는 자료는 많이 없어서 이에 대해 실제로 확인해보려고 한다.

테스트 환경은 Mysql이고, 아무런 릴레이션이 존재하지 않는 독립된 테이블에서 테스트를 진행하였다.

-- 테이블 생성
create table item ( 
	id BIGINT NOT NULL AUTO_INCREMENT,
	name VARCHAR(25) NOT NULL,
	is_for_sale BOOLEAN NOT NULL,
	price BIGINT NOT NULL,
	created_at TIMESTAMP NOT NULL,
	PRIMARY KEY(id)
);

-- 더미 데이터 삽입
DELIMITER //

CREATE PROCEDURE insert_dummy_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 100000 DO
    INSERT INTO item (name, is_for_sale, price, created_at)
    VALUES (
      CONCAT('Item', FLOOR(1 + (RAND() * 99999))), -- 랜덤 아이템 이름 생성
      RAND() < 0.5, -- 랜덤으로 TRUE 또는 FALSE
      FLOOR(1 + (RAND() * 99999)), -- 랜덤 가격
      DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 730) DAY) -- 현재로부터 최대 2년 전의 랜덤 날짜
    );
    SET i = i + 1;
  END WHILE;
END //

DELIMITER ;

CALL insert_dummy_data();

 

1. 인덱스가 차지하는 공간적 비용

위 쿼리를 보면 알 수 있듯이, 현재 테이블에는 총 10만개의 데이터가 추가되어있다. 이 때 해당 테이블이 차지하는 용량은 대략 5.52MB 정도이다. 아래 쿼리를 통해 확인 가능하다.

select * from information_schema.TABLES where table_name = 'item';

이제, name 칼럼에 인덱스를 추가하는 상황을 가정한 뒤, 인덱스를 추가해주고, 

create index item_name_idx on item (name);

그 후 아래 쿼리를 통해 해당 인덱스가 차지하는 용량을 확인해보면, 대략 3.52MB 정도이다.

select 
	stat_value * @@innodb_page_size as index_size 
from mysql.innodb_index_stats 
where stat_name = 'size' and table_name = 'item' and index_name = 'item_name_idx';

하나의 칼럼을 통해 인덱스를 생성하더라도, 테이블 사이즈의 절반이 넘는 크기를 인덱스가 차지함을 알 수 있다.

그렇다면 이번에는 데이터의 분포가 True, False 뿐인 Boolean 타입에 인덱스를 생성하면 어떻게 될까?

이를 위해 이번에는 item_is_sale_idx라는 이름의 인덱스를 새롭게 추가해주었다.

create index item_is_sale_idx on item (is_for_sale_idx);

그 뒤 위에서 제시한 인덱스 크기를 확인하는 쿼리를 실행시켜 확인해보면, 이번에는 2.52MB를 차지하는 것을 알 수 있다.

name 칼럼과 is_for_sale 칼럼의 사이즈는 각각 25byte vs 1byte로 차이가 크게 나는 것과, 데이터의 분포가 0과 1뿐이라고 해도 실질적으로 인덱스가 차지하는 크기는 많이 차이가 나지는 않는다.

마지막으로 복합 인덱스에 대해 알아보기 위해 (price, created_at) 형식으로 인덱스를 생성해주었다.

create index item_price_created_at_idx on item (price, created_at);

그리고 그 결과는, 이전의 item_name_idx와 동일한 3.52MB 수준이다. 

결론적으로, 테이블에 비해 인덱스가 차지하는 크기는 결코 무시할 수 없는 정도인 것으로 드러났다. 물론 칼럼 수가 적은 테이블로 테스트하여 더 많은 테이블이었다면 차이가 더 많이 났겠지만 그럼에도 불구하고 꽤 무겁다는건 체감이 되는 수준.

 

2. 인덱스로 인해 발생하는 시간적 비용

인덱스는 기본적으로 B+Tree라는 자료구조를 사용한다. 해당 자료구조는 항상 균형잡힌 상태(모든 리프노드까지의 탐색 비용이 동일)를 유지하는 특성이 있는데, 이로 인해 DB는 일관된 탐색 성능을 발휘할 수 있다.

다만 이러한 특성을 유지하기 위해 데이터(인덱스)의 추가 및 제거, 수정에 따라 트리의 구조가 계속해서 수정되는 자료 구조이기도 하다. 이 때문에 인덱스 추가 시 DML의 성능이 저하된다. 

이번에는 이 성능이 과연 얼마만큼 저하되는지 직접 쿼리를 실행해보면서 확인해보고자 한다.

우선 테이블 베이스는 위에서 이용했던 테이블 베이스를 그대로 사용하도록 하고, 더미 데이터를 추가하는 쿼리의 소요 시간을 측정하는 식으로 확인해보겠다.

기존에 넣어뒀던 데이터를 모두 삭제하고 다시 10만개의 데이터를 생성해주었다. 이 때 인덱스는 PK를 제외하고는 설정하지 않은 상태.

1분 14초가 걸렸다

그 뒤 데이터를 전부 날리고, name 칼럼에 인덱스를 걸어 준 다음 다시 더미 데이터 생성 쿼리를 작동시켰다.

1분 13초... 음? 더 빠르다

알던 것과 달리, 성능이 비슷하다. (사실 더 빠르다)

다만 더 빠르다고 결론을 내는 것은 말이 안되고, 거의 차이가 없다고 보는게 맞는 것 같다. 추가적인 작업이 이루어지는 것은 자명하지만 현재의 테스트 환경으로는 그 것이 부각될 정도는 아닌듯.