auto_increment index 꼭 필요한가 ?

의문점

프로젝트를 진행하면서 게시글에는 해시태그를 넣을 수 있다.

인스타그램처럼 내가 원하는 해시태그를 마음대로 넣을 수 있는 건 아니고 정해진 해시태그들을 선택할 수 있도록 했는데, 여기서 데이터베이스의 인덱스에 의문점을 가지게 됐다.

해시태그는 다음과 같이 사용된다.

해시 태그를 이용해서 게시글을 검색(조회)할 때
어떤 게시글에 달린 해시 태그를 조회할 때 (어떤 게시글을 조회하면, 그 게시글에 달린 해시 태그들도 같이 가져와야 함)
게시글을 작성할 때 (게시글에 해시 태그를 넣음)

내가 궁금했던 것은 테이블의 클러스터 인덱스인 board_hashtag_id가 필요할까? 였다.

해시태그가 사용될 때의 쿼리는 다음과 같다.

where hashtag_name = ?
where board_id = ?
insert ,,,

쿼리문을 보면 board_hashtag_id가 쓰이지 않는다.

그래서 테이블의 인덱스에 따른 쓰기와 조회 성능의 차이를 테스트하기로 했다.

테스트 조건

board_hashtag_id 기본키, (hashtag_name, board_id) 복합 유니크키
board_hashtag_id 기본키, (hashtag_name, board_id) 유니크키 X
(hashtag_name, board_id) 복합 기본키
board_hashtag_id 기본키, (board_id, hashtag_name) 복합 유니크키
board_hashtag_id 기본키, (board_id, hashtag_name ) 유니크키 X
(board_id, hashtag_name) 복합 기본키

테스트 환경 : AWS RDS 프리티어, 로컬에서 Datagrip 사용
데이터 : 20만 개
board_id로 조회했을 때
hashtag_name으로 조회했을 때
insert 할 때

예상 결과

AUTO_INCREMENT 기본키와 복합 기본키의 경우 클러스터 인덱스가 생성되고, 복합 유니크키의 경우 넌클러스터 인덱스가 생성될 것이다.

일반적으로 클러스터 인덱스가 조회 성능이 더 좋고 넌클러스터 인덱스는 쓰기 성능이 덜 느리다.

따라서

1번과 4번은 클러스터 인덱스와 넌클러스터 인덱스 둘 다 있기 때문에 쓰기 성능에서 가장 느릴 것이고, board_id와 hashtag_name은 넌클러스터 인덱스이기 때문에 3번과 6번보다 조회 성능이 더 느릴 것이다.

2번과 5번은 클러스터 인덱스밖에 없기 때문에 쓰기에서 가장 빠를 것이고, board_id나 hashtag_name으로 조회할 때는 가장 느릴 것이다.

3번과 6번은 클러스터 인덱스만 있고 board_id와 hashtag_name이 클러스터 인덱스이기 때문에, 쓰기와 조회 모두 1번과 4번 보다는 빠를 것이다. 또한 쓰기는 2번과 5번보다는 느릴 것이다.

예상 읽기 순위 : 3, 6 > 1, 4 > 2, 5

예상 쓰기 순위 : 2, 5 > 3, 6 > 1, 4

테스트 코드

CREATE TABLE case1 (
    idx BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    hashtag_name VARCHAR(20) NOT NULL ,
    board_id BIGINT NOT NULL
);
ALTER TABLE case1 ADD UNIQUE (hashtag_name, board_id);

DROP TABLE case1;
###########################
create table case2 (
    idx BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    hashtag_name VARCHAR(20) NOT NULL ,
    board_id BIGINT NOT NULL
);

DROP TABLE case2;
##########################
CREATE TABLE case3 (
    hashtag_name VARCHAR(20) NOT NULL ,
    board_id BIGINT NOT NULL ,
    PRIMARY KEY(hashtag_name, board_id)
);

DROP TABLE case3;
##########################


DELIMITER $$
CREATE PROCEDURE case1_func()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= 40000) DO
        INSERT INTO case1(hashtag_name, board_id) VALUES ('abcd', i);
        INSERT INTO case1(hashtag_name, board_id) VALUES ('efgh', i);
        INSERT INTO case1(hashtag_name, board_id) VALUES ('ijkl', i);
        INSERT INTO case1(hashtag_name, board_id) VALUES ('mnop', i);
        INSERT INTO case1(hashtag_name, board_id) VALUES ('qrst', i);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL case1_func();
##########################

DELIMITER $$
CREATE PROCEDURE case2_func()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= 40000) DO
        INSERT INTO case2(hashtag_name, board_id) VALUES ('abcd', i);
        INSERT INTO case2(hashtag_name, board_id) VALUES ('efgh', i);
        INSERT INTO case2(hashtag_name, board_id) VALUES ('ijkl', i);
        INSERT INTO case2(hashtag_name, board_id) VALUES ('mnop', i);
        INSERT INTO case2(hashtag_name, board_id) VALUES ('qrst', i);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL case2_func();
##########################

DELIMITER $$
CREATE PROCEDURE case3_func()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= 40000) DO
        INSERT INTO case3(hashtag_name, board_id) VALUES ('abcd', i);
        INSERT INTO case3(hashtag_name, board_id) VALUES ('efgh', i);
        INSERT INTO case3(hashtag_name, board_id) VALUES ('ijkl', i);
        INSERT INTO case3(hashtag_name, board_id) VALUES ('mnop', i);
        INSERT INTO case3(hashtag_name, board_id) VALUES ('qrst', i);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL case3_func();
##########################

select * from case1 where board_id = 5378;

select * from case2 where board_id = 5378;

select * from case3 where board_id = 5378;


select * from case1 where hashtag_name = 'efgh';

select * from case2 where hashtag_name = 'efgh';

select * from case3 where hashtag_name = 'efgh';

##########################

CREATE TABLE case4 (
    idx BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    board_id BIGINT NOT NULL,
    hashtag_name VARCHAR(20) NOT NULL
);
ALTER TABLE case4 ADD UNIQUE (board_id, hashtag_name);

DROP TABLE case4;
###########################
create table case5 (
    idx BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    board_id BIGINT NOT NULL,
    hashtag_name VARCHAR(20) NOT NULL
);

DROP TABLE case5;
##########################
CREATE TABLE case6 (
    board_id BIGINT NOT NULL ,
    hashtag_name VARCHAR(20) NOT NULL ,
    PRIMARY KEY(board_id, hashtag_name)
);

DROP TABLE case6;
##########################


DELIMITER $$
CREATE PROCEDURE case4_func()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= 40000) DO
        INSERT INTO case4(board_id, hashtag_name) VALUES (i,'abcd');
        INSERT INTO case4(board_id, hashtag_name) VALUES (i,'efgh');
        INSERT INTO case4(board_id, hashtag_name) VALUES (i,'ijkl');
        INSERT INTO case4(board_id, hashtag_name) VALUES (i,'mnop');
        INSERT INTO case4(board_id, hashtag_name) VALUES (i,'qrst');
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL case4_func();
##########################

DELIMITER $$
CREATE PROCEDURE case5_func()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= 40000) DO
        INSERT INTO case5(board_id, hashtag_name) VALUES (i,'abcd');
        INSERT INTO case5(board_id, hashtag_name) VALUES (i,'efgh');
        INSERT INTO case5(board_id, hashtag_name) VALUES (i,'ijkl');
        INSERT INTO case5(board_id, hashtag_name) VALUES (i,'mnop');
        INSERT INTO case5(board_id, hashtag_name) VALUES (i,'qrst');
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL case5_func();
##########################

DELIMITER $$
CREATE PROCEDURE case6_func()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= 40000) DO
        INSERT INTO case6(board_id, hashtag_name) VALUES (i,'abcd');
        INSERT INTO case6(board_id, hashtag_name) VALUES (i,'efgh');
        INSERT INTO case6(board_id, hashtag_name) VALUES (i,'ijkl');
        INSERT INTO case6(board_id, hashtag_name) VALUES (i,'mnop');
        INSERT INTO case6(board_id, hashtag_name) VALUES (i,'qrst');
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL case6_func();
##########################

select * from case4 where board_id = 5378;

select * from case5 where board_id = 5378;

select * from case6 where board_id = 5378;

select * from case4 where hashtag_name = 'efgh';

select * from case5 where hashtag_name = 'efgh';

select * from case6 where hashtag_name = 'efgh';
##########################

테스트 결과

case1 hashtag_name으로 조회, case1 board_id로 조회

case2 hashtag_name으로 조회, case2 board_id로 조회

case3 hashtag_name으로 조회, case3 board_id로 조회

case4 hashtag_name으로 조회, case4 board_id로 조회

case5 hashtag_name으로 조회, case5 board_id로 조회

case6 hashtag_name으로 조회, case6 board_id로 조회

case1	board_hashtag_id auto_increment 기본키 (hashtag_name, board_id) 복합 유니크
insert	3분 26.868초
hashtag_name으로 조회 10회 평균	217.3 ms
board_id로 조회 10회 평균	143.8 ms

case2	board_hashtag_id auto_increment 기본키
insert	3분 10.228초
hashtag_name으로 조회 10회 평균	219.3 ms
board_id로 조회 10회 평균	157.4 ms

case3	(hashtag_name, board_id) 복합 기본키
insert	3분 32.814초
hashtag_name으로 조회 10회 평균	193.9 ms
board_id로 조회 10회 평균	83.7 ms

case4	board_hashtag_id auto_increment 기본키 (board_id, hastag_name) 복합 유니크
insert	3분 18.353초
hashtag_name으로 조회 10회 평균	204.7 ms
board_id로 조회 10회 평균	83.9 ms

case5 (case2와 같음)	board_hashtag_id auto_increment 기본키
insert	3분 25.817초
hastag_name으로 조회 10회 평균	205.8 ms
board_id로 조회 10회 평균	147.0 ms

case6	(board_id, hashtag_name) 복합 유니크
insert	3분 19.686초
hashtag_name으로 조회 10회 평균	178.7 ms
board_id로 조회 10회 평균	76.7 ms

INSERT

20만 개의 데이터를 넣으려고 하니까 여러 번 하기에는 시간이 너무 오래 걸렸다.

- 1번 : auto_increment 클러스터 인덱스로 물리적으로 정렬되고, 복합 유니크 키 논 클러스터 인덱스의 경우 hashtag_name이 먼저 기준이 되는데 board_id의 중복 횟수는 5번이고 hashtag_name은 40000번이기 때문에, 4번의 경우와 비교해서 논 클러스터 인덱스가 정렬되는데 더 빠를 것이라고 생각했다.

- 2번 : auto_increment 클러스터 인덱스로 물리적으로 정렬되는 것밖에 없기 때문에, 가장 빠를 것이라고 생각했다.

- 3번 : 복합 기본키이고 클러스터 인덱스는 물리적으로 정렬되어야 하기 때문에 다른 케이스와 비교해서 3번과 6번이 가장 느릴 것이고, 3번과 6번 중 hashtag_name의 중복 횟수가 더 크기 때문에 3번이 더 빠를 것이라고 생각했다.

결과는 테스트를 한 번밖에 하지 않았지만,

case1 ~ 3 중 2번이 제일 빨랐고 3번이 가장 느렸다.

case4 ~ 6 중 4번이 제일 빨랐고 5번이 가장 느렸다.

case 4~ 6 중에는 5번이 가장 빨라야 하는데 반대의 결과가 나온 것.

20만 개의 데이터를 생각했을 때, 테스트를 여러 번 해보진 않았지만 속도 차이가 유의미할 정도로 크게 나진 않는다고 생각.

hashtag_name으로 조회

case6 (178.7 ms) > case3 (193.9 ms) > case4 (204.7 ms) > case5 (205.8 ms) > case1 (217.3 ms) > case2 (219.3 ms)

순으로 빨랐다.

hashtag_name으로 조회하는 경우 내 생각대로 3번과 6번이 가장 빠른 것은 맞지만,

내 생각과는 반대로 6번이 더 빨랐다.

3번은 hashtag_name이 먼저 기준이 되어서 정렬되기 때문에 hashtag_name으로 검색한다면 속도가 더 빨라야 한다.

board_id로 조회

case6 (76.7 ms) > case3 (83.7 ms) > case4 (83.9 ms) > case1 (143.8 ms) > case5 (147.0 ms) > case2 (157.4 ms)

내 생각대로 6번과 3번이 가장 빠르고 그중 6번이 더 빨랐다. 그리고 5번과 2번이 가장 느리고 2번이 더 느렸다.

4번과 6번은 board_id가 먼저 기준이 되어서 정렬되기 때문에 1번 3번과 차이가 많이 나는데, 이때 유의미한 결과를 얻을 내 생각과 같은 결과가 나왔으며 유의미한 결과를 얻을 수 있었다.

결론

쓰기에서는 20만 개의 데이터로 테스트했을 때 큰 차이가 없었다.

하지만 auto_increment 인덱스를 둘 필요 없이 복합 기본키로 클러스터 인덱스를 생성한다면, 조회에서 더 좋은 성능을 낼 수 있다.

따라서 필요한 경우가 아니라면 필요하지 않은 AUTO_INCREMENT 기본키를 두지 않고,

서비스의 필요한 기능에 따라 복합 기본키에서 우선으로 정렬되는 기준을 잘 선택한다면 쓰기와 읽기에서 더 좋을 성능을 낼 수 있을 것이다.

그래서 나는 해시태그 테이블을 case 6으로 바꾸기로 했다.

'DB' 카테고리의 다른 글

(2) Mysql 실행 계획 분석 및 쿼리 튜닝 (0)	2024.05.01
(2) 스프링 Redisson 분산락 (0)	2024.04.09
(1) 스프링 동시성 문제 (1)	2024.04.04
DB Replication / Master-Slave 이중화 with Spring (1)	2024.03.29

선호의 코딩일기

auto_increment index 꼭 필요한가 ?

의문점

테스트 조건

예상 결과

테스트 코드

테스트 결과

결론

'DB' 카테고리의 다른 글

티스토리툴바

auto_increment index 꼭 필요한가 ?

의문점

테스트 조건

예상 결과

테스트 코드

테스트 결과

결론

'DB' 카테고리의 다른 글

'DB' Related Articles

티스토리툴바