[Spring] 다중 칼럼 인덱스를 활용한 쿼리 속도 개선

 

 

Content 테이블에는 Category와 Location이라는 칼럼이 존재한다.

 

@Entity
public class Content extends BaseEntity {
	@OneToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "category_id")
	private Category category;
    
	@OneToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "location_id")
	private Location location;
}

 

해당 칼럼들은 외래키로 연결되어 있어 기본으로 단일 칼럼에 대한 인덱스를 가지고 있다.

 

하지만 카테고리와 지역 정보 두 개를 조건으로 조회하고자 할 때, 인덱스를 둘 다 사용할 수 없으므로 다중 칼럼 인덱스를 적용해주었다.

 

ALTER TABLE content ADD INDEX idx__category_location(category_id, location_id);

▲ 인덱스 생성

 

 

SHOW INDEX FROM content;

▲ 인덱스 조회

 

 

Spring에서 인덱스를 추가하는 방법은 해당 어노테이션을 Entity 클래스에 작성해주면 된다.

단, columnList는 실제 테이블을 기준으로 정의되므로 필드명이 아닌 JoinColumn name을 기준으로 설정

@Table(indexes = {
	@Index(name = "idx__category_location", columnList = "category_id, location_id")
})​

 

 

 

이후 약 200만 건의 데이터를 추가해서 단일 칼럼 인덱스와 다중 칼럼 인덱스의 속도 차이를 측정해보고자 했다.

 

DELIMITER $$
DROP PROCEDURE IF EXISTS insertLoop$$
 
CREATE PROCEDURE insertLoop()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 2000000 DO
        INSERT INTO testdb.Content(member_id, content_type, title, recruiting_count, work_content, price, is_premium,
				category_id, location_id, status, dead_line)
				VALUES (1, 1, concat('title', i), i, concat('content', i), i, false,
				FLOOR(RAND() * 13) + 1, FLOOR(RAND() * 25) + 1, 'RECRUITING',
				FROM_UNIXTIME(FLOOR(unix_timestamp('2023-07-01 00:00:00')+(RAND()*(unix_timestamp('2024-07-01 00:00:00')-unix_timestamp('2023-07-01 00:00:00'))))));
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER $$

CALL insertLoop;
$$

▲ 프로시저를 활용한 랜덤 데이터 삽입 (느림)

 

카테고리 ID와 지역 ID는 랜덤으로 삽입하도록 설정했다.

 

EasyRandom 라이브러리와 Stream의 parallel을 사용하면 빠르게 객체를 생성하고 저장할 수 있다.

 

 

---

 

결과

 

우선 데이터가 잘 들어갔는지 확인

100개는 이전에 프로시저 테스트한다고 넣어본 데이터이다

 

 

단일 칼럼 인덱스 조회와 다중 칼럼 인덱스 조회 비교

단일 칼럼 인덱스

 

다중 칼럼 인덱스

 

 

	단일 칼럼 인덱스	다중 칼럼 인덱스
1	0.014s	0.0044s
2	0.018s	0.0020s
3	0.019s	0.0020s
4	0.019s	0.0020s
5	0.017s	0.0020s
6	0.018s	0.0020s
7	0.019s	0.0020s
8	0.018s	0.0021s
9	0.019s	0.0020s
10	0.018s	0.0020s
평균	0.0179s	0.00225s

 

다중 칼럼 인덱스를 사용한 후, 약 8배의 성능 향상을 보여주었다.