조회 쿼리 sort 성능 개선

회사에서 DB 모니터링을 진행하고 있어 개선 해야하는 쿼리들을 공유 받았다.

그 중 조회 쿼리 개선을 요청 받은 것이 있어 몰랐던 부분을 알게되어 정리한다.

CREATE TABLE `tb_test` (
	`seq` INT(11) NOT NULL AUTO_INCREMENT,
	`string_data1` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
	`string_data2` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
	`number_data3` BIGINT(20) NOT NULL,
	`date` DATETIME NOT NULL,
	INDEX 'seq' (`seq`) USING BTREE,
	INDEX `date` (`date`) USING BTREE,
	INDEX `string_data1` (`string_data1`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

위와 같이 테이블이 있고 인덱스가 잡혀있을 때 기존에 조회 쿼리를 사용하고 있었다.

SELECT * FROM tb_test WHERE `date` BETWEEN '2023-01-01' AND '2023-02-01' ORDER BY seq DESC;

나는 seq, date 각각 인덱스가 잡혀 있기 때문에 seq로 정렬을 해도 문제가 없다고 생각했는데 전혀 아니었다.

date 인덱스를 통한 결과값 검색 후, 별개의 인덱스인 Seq 컬럼 Sort 로 인해 비용 발생

그렇기 때문에 인덱스를 통해 검색을 했으면 해당 인덱스로 정렬되어 있기 때문에 동일한 인덱스로 정렬하는 비용적 측면에서 유리하다.

 

공식문서에서 아래 내용 확인할 수 있었다.

기존 쿼리 문제

1. where에서 date 컬럼으로 조회

2. db는 date로 조회된 데이터를 정렬해서 캐시에 갖고있음

3. date로 정렬된 데이터를 seq 컬럼으로 새로 정렬함

  3-1. 이 때 정렬 비용이 발생(filesort)

 

개선 방향

where에서 사용한 컬럼으로 정렬 처리 (filesort 미발생)

SELECT * FROM tb_test WHERE `date` BETWEEN '2023-01-01' AND '2023-02-01' ORDER BY `date` DESC;

 

참고 : https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html#order-by-index-use