[복쟉복쟉] 비정규화로 6초 쿼리를 105ms로 줄이기

프로젝트의 요구사항과 전반적인 맥락은 여기에 정리해놓았습니다 : 실시간으로 적재되는 데이터와 부모 엔티티 한번에 가져오기

⚠ 발단

실시간 혼잡도 데이터가 계속해서 쌓이면서 60만개가 넘어가던 상황이었다.

 

 

 

 

앱을 켜보니 응답이 너무 오래 걸리는 상태가 지속됐고, 나중에는 아예 에러가 터졌다.

스프링의 기본 read timeout(서버의 response time에 대한 제한) 시간은 5초인데, 이를 초과한 것.

 

 

 

직접 쿼리를 날려봤더니... 무려 6초를 넘기고 있었다

 

혼잡도 데이터는 매일 몇만개씩 쌓여가고 있기 때문에, 정상적인 서비스를 위해선 빠르게 개선해야만 했다.

 

💡 해결 과정 : 기존 쿼리문 안에서 INDEX 사용

🤔문제 파악

기존의 쿼리에서 가장 문제가 됐던 부분은, 가장 최근의(실시간) 혼잡도를 가져오는 서브쿼리였다.

GROUP BY와 MAX를 이용하여 location 별로 가장 최근의 congestion을 묶어 가져오게 된다.

 

select
    c.location_id, max(c.observed_at)
from
    congestion as c
group by
    c.location_id;

 

이 쿼리 혼자서 3초를 잡아먹고 있었다.

장소 자체는 110개 정도이니까 느려질 이유가 없었다. 

그리고 데이터가 아무리 많다고 해도, 3초나 6초는 이해가 안 되는 속도였다.

AGGREGATE가 느리다는 레퍼런스는 봤지만 그래도 그 정도인가...?라고 생각했다.

 

직접 테이블을 보며 고민을 하던 중, INDEX의 존재가 머리속에 떠올랐다.

 

 

ORDER BY DESC

order by를 하면 집계 필요 없이 모두 가져올 수 있게 된다.

ORDER BY를 사용하면 내부적으로 INDEX를 만들어 처리하므로 속도가 빠르다.

 

로케이션의 개수를 먼저 구한 다음, 그 개수만큼 limit을 걸면 된다. 

 

select
    c.*
from
    congestion c
order by
    c.observed_at desc
limit 110;

 

서브쿼리를 적용할 수도 있다.

select
    c.location_id, observed_at
from
    congestion c
order by
    c.observed_at desc
limit (select count(*) from location);

 

⚠ 문제점 : 그룹 짓지를 못하기 때문에 적재 서버 상황에 따라 개수가 안 맞아 엉뚱한 혼잡도들을 가져오게 된다.

 

우리의 적재 서버에서는 일괄적으로 혼잡도 데이터를 가져오지 않는다.

서울시 공공데이터 API에서는 5분마다, SK API에서는 1시간마다 가져온다.

 

가령 서울시에서 제공하는 장소는 100개, SK의 장소는 10개라고 했을 때

LIMIT 110을 걸게 되면 1시간 중 55분 동안은 서울시 장소의 혼잡도 데이터만 나오게 된다. 

즉, 1시간 중 5분만 정상적으로 돌아가고 그 외에는 중복된 로케이션들이 발생하게 된다.

 

INDEX 적용

JPA의 @Index를 이용해  location_id, observed_at에 대한 복합 인덱스를 생성했다.

그러나... EXPLAIN으로 확인해봤더니 실제 쿼리에서는 FK인 location_id에 대해서만 인덱싱되고 있었다.

MySQL 쿼리 옵티마이저는 MAX보다는 GROUP BY, WHERE 인자를 우선적으로 처리하는 것 같다.

 

 

USE INDEX : MAX 부분 인덱스 적용

MySQL에서 인덱스를 강제로 지정할 수 있는 USE INDEX를 사용해봤다. 

 

인덱스를 적용했더니 3s 100 ms → 390 ms으로 성능 개선을 할 수 있었다.

8배나 빨라진 것이고 이 덕분에 인덱스 설정의 중요성을 알 수 있었다.

### 인덱스 observed_at : 390ms
EXPLAIN select
            max(c.observed_at)
        from
            congestion c
                use index (congestion_observed_at_index)
        group by
            c.location_id;

 

location_id와 observed_at에 대한 복합 인덱스를 사용했더니 아주 조금 더 빨라졌다. 약 8.2배 (표본이 적어서 오차가 있음)

 

### 복합 인덱스 observed_at, location_id : 375ms
EXPLAIN select
            max(c.observed_at)
        from
            congestion c
        use index (congestion_observed_at_location_id_index)
        group by
            c.location_id;

꼼수 INDEX 적용

신나서 SQL을 JPA로 옮기려고 했는데, JPA에서 use index를 지원하지 않았다...

기존에는 GROUP BY 절에 있는 location_id를 INDEX로 사용했다. (쿼리 옵티마이저가 생각보다 까다롭다.)

꼼수를 써서, WHERE 절에 observed_at을 비교하는 연산을 넣었다.

오늘로부터 24시간 전부터 가져오는 것.

 

6.5s → 1.1s로 성능을 개선할 수 있었다.

 

이 방식의 문제점은, 데이터 적재 API

 

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	l1_0	null	ALL	null	null	null	null	112	100	Using temporary; Using filesort
1	PRIMARY	c1_0	null	ALL	null	null	null	null	607813	100	Using where; Using join buffer (hash join)
1	PRIMARY	l2_0	null	eq_ref	PRIMARY	PRIMARY	8	awsbuzzing.l1_0.location_category_id	1	100	null
1	PRIMARY	l3_0	null	ref	FKcdr7befydtbo9k9miyf5rbxwd	FKcdr7befydtbo9k9miyf5rbxwd	8	awsbuzzing.l1_0.location_id	1	100	Using index
2	SUBQUERY	c2_0	null	index	congestion_observed_at_location_id_index	congestion_observed_at_location_id_index	17	null	607813	100	Using index; Using temporary

[Querydsl] 성능개선 - 3편 ( group by, 커버링 인덱스, update )

이제 Spring에 적용해보자.

❗그동안의 GROUP BY + 인덱싱되지 않은 컬럼의 문제점

 

Mysql 사용 시 index가 걸려있지 않는 컬럼을 group by 할 경우 file sort가 발생합니다.

⚠️with-clause not allowed on fetched associations; use filters

찾아보니 FETCH JOIN과 ON을 같이 사용했을 때 생기는 에러라고 한다.

⚠️ 애꿎은 FK 인덱스만 지정되는 문제

새로 생성한 복합 인덱스가 key로 쓰여야 하는데, 선택된건 FK였다.

 

마지막 시도…!

마지막 튜닝…!

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	l1_0	null	index	null	PRIMARY	8	null	1	100	null
1	PRIMARY	c1_0	null	ref	FK2urj33m34b0gqk73cqhrna200	FK2urj33m34b0gqk73cqhrna200	8	awsbuzzing.l1_0.location_id	5681	100	Using where
1	PRIMARY	l4_0	null	eq_ref	PRIMARY	PRIMARY	8	awsbuzzing.l1_0.location_category_id	1	100	null
1	PRIMARY	l5_0	null	ref	FKcdr7befydtbo9k9miyf5rbxwd	FKcdr7befydtbo9k9miyf5rbxwd	8	awsbuzzing.l1_0.location_id	1	100	Using index
2	SUBQUERY	c2_0	null	range	FK2urj33m34b0gqk73cqhrna200,congestion_observed_at_location_id_index	congestion_observed_at_location_id_index	9	null	49256	100	Using where; Using index; Using temporary

⚠️그런데,,, 혼잡도 정렬시

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	l1_0	null	ALL	null	null	null	null	112	100	Using temporary; Using filesort
1	PRIMARY	c1_0	null	ref	FK2urj33m34b0gqk73cqhrna200	FK2urj33m34b0gqk73cqhrna200	8	awsbuzzing.l1_0.location_id	5680	100	Using where
1	PRIMARY	l4_0	null	eq_ref	PRIMARY	PRIMARY	8	awsbuzzing.l1_0.location_category_id	1	100	null
1	PRIMARY	l5_0	null	ref	FKcdr7befydtbo9k9miyf5rbxwd	FKcdr7befydtbo9k9miyf5rbxwd	8	awsbuzzing.l1_0.location_id	1	100	Using index
2	SUBQUERY	c2_0	null	range	FK2urj33m34b0gqk73cqhrna200,congestion_observed_at_location_id_index	congestion_observed_at_location_id_index	9	null	48230	100	Using where; Using index; Using temporary

⭐ 결국 비정규화 (실시간 혼잡도 컬럼 추가)

✨컬럼 추가 및 데이터 적재시 UPDATE 쿼리 추가 : 6s → 105ms

[데이터베이스] 정규화 vs. 비정규화(반정규화)

 

해당 서비스에서 데이터는 매일 약 4만개가 적재된다. 현재는 1.1초에 쿼리가 실행되지만, 쌓여갈수록 점점 길어질게 분명하다.

그리고 비정규화를 하게 되면 쿼리가 압도적으로 단순해진다.

 

기본적으로 장소 - 실시간 혼잡도 는 1:1 매핑되는 관계다.

결국 해당 API의 관점에서 로케이션과 실시간 혼잡도는 한 테이블인 것과 마찬가지이다. (INLINE VIEW 서브 쿼리가 떠오른다.)

"실시간" 데이터는 장소별로 하나밖에 존재하지 않기 때문이다. 

그리고 저장할 데이터는 딱 하나, 혼잡도 단계이다.

 

해야 할 일은 2가지였다.

• 장소 테이블 : real_time_congestion_level 컬럼 추가
• 적재 로직 : 기존에 적재하던 혼잡도 테이블에 그대로 적재하되 (일간, 주간 통계 및 추후 활용을 위해 적재는 필수),
  장소 테이블의  real_time_congestion_level 컬럼도 함께 UPDATE
  즉, 중복된 값을 허용하는 것.

 

비정규화를 하고 난 뒤에 성능이 6s -> 105ms로 개선되었다.

 

단점이라고 한다면 무결성이 깨질 수 있다는 것이다. 만약 데이터 적재 로직에서 오류가 발생해 혼잡도 데이터를 갱신한 뒤에 장소 테이블 갱신을 하지 못한다면 사용자는 우리가 정의한 "실시간" 데이터가 아닌 "옛날" 데이터를 보게 된다.

그러나 이 가능성은 너무 낮다고 생각했기 때문에 큰 문제가 아니라고 판단했다.

 

정말 간단하게 요약하면 컬럼을 하나 추가한 것이다.

너무 허무하다고 생각되기도 하지만, 기존에 정규화에 갇혀있던 생각을 깨고 인덱스에 대해 깊은 생각을 해보게 된 계기였다.

데이터베이스 테이블은 무결성과 체계도 중요하지만 기본적으로 사용자에게 효용을 주는 것이 우선이라고 생각한다.

 

 

 

🥲 Redis를 포기한 이유

처음 생각한 Redis 방법 : 로케이션별로 실시간 혼잡도 데이터 저장 (location_id - congestion_level)

 

💡 2주간의 혼잡도 데이터들을 레디스에 저장해버리는건 어떨까? 주간 통계까지만 내고 그 다음엔 쓸 필요 없으니까.

 

❗문제는 조회

join 과 같은 복잡한 데이터베이스 쿼리를 수행해야 하는 경우 다른 대안을 고려해야 합니다.

Redis는 key-value 형태로 저장되기 때문에 key 를 통해서만 액세스할 수 있습니다.

단순히 실시간 congestion을 빨리 가져온다고 해도, 결국 location과 congestion.congestion_level은 너무 긴밀하게 연결되어있기 때문에 조회를 할 때 둘이 엮일 때가 많아 최종적으로 비효율적인 쿼리가 된다.

 

레디스 전략 진행 순서

1. 레디스에서 로케이션별 congestion 싹 다 가져오기 ⇒ congestionList
2. 혼잡도순 정렬 요청일 경우, congestionList 를 congestion_level 순 JAVA 정렬 → location_id 커서, congestion_level 커서, limit으로 자르기
3. congestionList 의 location_id을 이용해 IN 절 조회 쿼리 + FETCH JOIN location_category, location.bookmarkList ⇒ List<Location>
4. → ORDER BY 순서를 지정해야 함… IN 절은 순서를 무시!!
5. 이어 붙이기…! : iteration index 혹은 location_id 기반으로 둘을 이어 붙여야 함