배경
댕댕워크는 산책일지 목록을 조회할 때, [강아지 이름]과의 [n] 번째 산책이라는 형식으로 제목을 자동 생성하는 기능이 있습니다.
이를 구현하기 위해 각 산책이 이 강아지의 몇 번째 산책인지 목록 조회때마다 세어줘야 합니다.
몇 번째 산책인지 세기 위해 산책일지 - 강아지 간 관계형 테이블인 journals_dogs 테이블 전체 데이터를 검색해야 합니다.
이 부분이 부담스럽게 느껴져 쿼리 성능 개선을 시도하게 되었습니다.
EXPLAIN PLAN 분석
쿼리는 현재 이렇게 구현되어 있습니다.
EXPLAIN
SELECT
`journals`.`id` AS "journalId",
`journals`.`started_at` AS "startedAt",
distance,
calories,
duration,
(
SELECT
COUNT(*)
FROM
journals_dogs jd
WHERE
jd.dog_id = 2
AND jd.journal_id <= `journals`.`id` ) AS "journalCnt"
FROM
`journals` `journals`
INNER JOIN `journals_dogs` `journals_dogs` ON
`journals`.`id` = `journals_dogs`.`journal_id`
WHERE
`journals_dogs`.`dog_id` = 2
AND journals.user_id = 1
AND `journals`.`started_at` >= "2024-07-29T15:00:00.00"
AND `journals`.`started_at` < "2024-07-30T14:59:59.99";
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | journals | range | PRIMARY,FK_dcd8f26897887ea1ca19e9b910a,IDX_6d2772db482e23f5ad6689bfe4 | IDX_6d2772db482e23f5ad6689bfe4 | 9 | 3 | 100.0 | Using index condition | ||
| 1 | PRIMARY | journals_dogs | eq_ref | PRIMARY,FK_65d6e1349dda45ff53bc0d865af | PRIMARY | 8 | performance.journals.id,const | 1 | 100.0 | Using index | |
| 2 | DEPENDENT SUBQUERY | jd | ref | PRIMARY,FK_65d6e1349dda45ff53bc0d865af | FK_65d6e1349dda45ff53bc0d865af | 4 | const | 100 | 33.33 | Using where; Using index |
- journals, journals_dogs 간 inner JOIN
- journals 테이블을 드라이빙 테이블로 해 JOIN이 수행됩니다. 특정 유저의 특정 날짜(하루)의 산책 일지를 필터링하도록 조건이 걸려있습니다. user_id와 started_at 컬럼에 복합 인덱스가 이미 추가되어 있어 이 인덱스를 사용해 효율적으로 필터링하고 있습니다. 테스트 데이터를 하루에 3번 산책하도록 생성했기에 반환된 row는 3개입니다.
필터링 된 3개의 row마다 jouransl_dogs 테이블에 접근합니다. 총 세 번 접근하게 됩니다.
- 몇 번째 산책인지 세는 서브쿼리
- SELECT 구문 마지막에 포함되어있는 서브쿼리입니다. select_type을 보면 dependent subquery라고 되어있습니다. 이는 메인 쿼리에 종속되어 있다는 의미로, 메인 쿼리의 row 마다 이 서브쿼리가 실행됨을 뜻합니다. 총 세번 실행되게 됩니다.
이 쿼리가 신경쓰이는 부분이었는데, 메인 쿼리가 반환하는 행의 횟수만큼 실행되어 개선의 여지가 있다는 생각이 들었습니다.
EXPLAIN ANALYZE 결과는 이렇습니다.
-> Nested loop inner join (cost=7.26 rows=3) (actual time=8.38..8.63 rows=3 loops=1)
-> Index range scan on journals using IDX_6d2772db482e23f5ad6689bfe4 over (user_id = 1 AND '2024-07-14 15:00:00' <= started_at < '2024-07-15 15:00:00'), with index condition: (((journals.user_id = 1) and (journals.started_at >= TIMESTAMP'2024-07-14 15:00:00') and (journals.started_at < TIMESTAMP'2024-07-15 14:59:59.99')) and (journals.id is not null)) (cost=3.96 rows=3) (actual time=5.94..6.17 rows=3 loops=1)
-> Single-row covering index lookup on journals_dogs using PRIMARY (journal_id=journals.id, dog_id=2) (cost=1.03 rows=1) (actual time=0.455..0.455 rows=1 loops=3)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: count(0) (cost=7.81 rows=1) (actual time=0.223..0.223 rows=1 loops=3)
-> Filter: (jd.journal_id <= journals.id) (cost=4.48 rows=33.3) (actual time=0.17..0.207 rows=62 loops=3)
-> Covering index lookup on jd using FK_65d6e1349dda45ff53bc0d865af (dog_id=2) (cost=4.48 rows=100) (actual time=0.104..0.127 rows=100 loops=3)- inner join이 실행됩니다. cost는 7.26, 반환되는 row는 3입니다.
- 서브 쿼리의 cost는 7.81입니다. 메인 쿼리의 row 만큼 실행되기에 총 cost는 7.81 * 3 = 23.43입니다.
먼저 FK_65~ 인덱스를 사용해 스캔을 하는데, 이 FK는 외래키인 dog_id에 대해 자동으로 생성된 인덱스입니다.
이후 journal_id 범위 조건에 대해 필터링을 하게됩니다.
이 테이블은 journals_dogs 관계형 테이블로, journal_id와 dog_id가 컴포지트 키로 설정되어 있어 journal_id, dog_id에 대한 복합 인덱스가 존재하는데 사용되지 않았습니다.
journal_id, dog_id로 구성된 복합 인덱스는 먼저 jouranl_id로 정렬한 뒤 이후 dog_id로 정렬하게 됩니다. 그래서 dog_id를 먼저 필터링 조건으로 사용할 수 없는데
실제 데이터는 dog_id로 필터링했을 때 더 적은 행이 반환되기에 이런 선택을 한 것 같습니다.
dog_id, journal_id로 순서를 바꾼 복합 인덱스를 사용하면 어떻게 될지 궁금해 적용해보았는데, lookup과 필터링에 사용된 조건이 동일한데도 필터링 단계가 사라지지 않았고, cost도 오히려 증가했습니다.
이유는 더 공부를 해야 알 수 있을 것 같습니다.
사실 몇 번째 산책일지인지 세는 서브쿼리는 여러 번 수행할 필요가 없습니다.
해당 날짜의 첫 번째 산책일지가 몇 번째 산책일지인지 알면, 이후 산책일지의 경우 그 숫자에 1씩 더하면 되기 때문입니다.
성능 개선?
private async getJournalIdsByDogIdAndDate(userId: number, dogId: number, date: string): Promise<number[]> {
const startEndDate = getStartAndEndOfDay(new Date(date));
const result = await this.entityManager
.createQueryBuilder(Journals, 'journals')
.select([
'journals.id AS "journalId"',
'journals.started_at AS "startedAt"',
'distance',
'calories',
'duration',
])
.innerJoin(JournalsDogs, 'journals_dogs', 'journals.id = journals_dogs.journal_id')
.where('journals_dogs.dog_id = :dogId', { dogId })
.andWhere('journals.user_id = :userId', { userId })
.andWhere('journals.started_at >= :startDate', { startDate: startEndDate.startDate })
.andWhere('journals.started_at < :endDate', { endDate: startEndDate.endDate })
.orderBy('journals.id', 'ASC')
.getRawMany();
if (!result.length) {
return [];
}
const firstJournalId = result[0].journalId;
let initCount = await this.entityManager
.createQueryBuilder(JournalsDogs, 'journals_dogs')
.where('journals_dogs.dog_id = :dogId', { dogId })
.andWhere('journals_dogs.journal_id <= :firstJournalId', { firstJournalId })
.getCount();
return result.map((cur) => ({
...cur,
journalCnt: initCount++,
}));
}- 서브쿼리를 제외하고 메인쿼리만 실행합니다.
- 메인쿼리가 반환한 row 중 첫번째 행의 joruanl_id를 조건으로 해 몇 번째 산책일지인지 찾는 쿼리를 한번만 실행합니다.
- 찾은 값에 1씩 더해 나머지 행의 journalCnt도 계산합니다.
이렇게 바꾸면 서브 쿼리를 여러 번 실행하지 않아도 됩니다. 메인 쿼리에서 몇 개의 행을 반환하든, 한 번만 실행됩니다.
하지만 단점도 있습니다.
1. 쿼리를 두 번 나눠서 실행하게 되어 MySQL 서버에 요청을 두 번 보내게 됩니다.
2. 코드가 복잡해집니다.
Dependent 서브쿼리는 매우 위험하긴 하지만, 이 경우에는 메인 쿼리의 조건이 굉장히 빡빡합니다. 특정 유저가 특정 강아지와 하루라는 기간동안 산책한 산책일지만 리턴하기에 그리 많은 개수가 리턴되지 않을 것이라고 예상됩니다. 정말 많이 산책하는 유저라도 하루 세 번 이상 산책하기 어려울 것입니다.
이런 이유 때문에 실제로 적용하지는 않았습니다. 득과 실을 놓고 보았을 때 굳이..?라는 느낌이 들었습니다. 실제로 운영하면서 문제가 된다면 그 때 적용해도 괜찮을 것 같습니다.
.