정글에서 온 개발자
1/20 TIL 이 쿼리가 왜 빨라졌을까? 본문
계약 정보 조회를 하는 기능이 있었는데, 검색 조건으로 계약에 해당하는 상품과, 계약과 관련된 멤버를 통해 조회할 수 있는 기능을 추가했다. 그런데 기존에 다른 join을 했을 때는 빨랐던 조회가 눈에 띄게 느려진게 느껴졌다. 확인해보니 server reponse 시간이 거의 5초가 되어가고 있었다.
나중에 최적화할 수도 있었겠지만, 프론트에서 개발하는데 속도가 너무 걸리는 것 같아서 간단하게라도 성능을 개선해보고자 했다.
내가 쓰는 dbms는 mariadb10.6.4다.
문제 파악
기존의 쿼리는 크게 두 단계로 되어있었다.
- 검색 조건을 바탕으로 테이블을 한 번 만드는 서브 쿼리 (페이지네이션까지 처리)
- 페이지네이션된 테이블을 바탕으로 다시 한번 추가 join을 하는 쿼리
이번에 추가한 상품과 멤버를 검색조건으로 거는 기능은 서브 쿼리에서 계약에 포함된 엔티티들을 맵핑해주는 맵핑 table을 join했다. 이후 검색을 위해 동적으로 추가되는 SELECT 연산에서 조건을 줄이기 위해 같은 맵핑 테이블임에도 두번 join 했다.
select
a.seq,
a.name,
a.type,
a.status,
a.start_due_date,
a. end_due_date,
a.start_date,
a.end_date,
a.severity,
b.name business_name,
ca.name activity_name,
manager.name manager_name,
case
when a.product_type is null then
GROUP_CONCAT(distinct p.name separator ', ')
else
old_p.name
end as ProductNames,
COUNT(distinct af.ID) FileCount
from
(
select
act.seq,
act.name,
act.type,
act.status,
act.start_due_date,
act.end_due_date,
act.start_date,
act.end_date,
act.severity,
act.activities_type,
act.manager_seq,
act.product_type,
act.business_unit_seq
from
activities act
left join mapping_assigned_entities map_product on --두번 join하는
map_product.parent_entity_seq = act.seq
and map_product.parent_entity_code = '3'
and map_product.assigned_entity_code = '2'
left join mapping_assigned_entities map_member on -- 같은 테이블
map_member.parent_entity_seq = act.seq
and map_member.parent_entity_code = '3'
and map_member.assigned_entity_code = '1'
where
(act.DEPT like '%내부서%'
and act.del_flag = false)
and (act.type = '2')
group by
`act`.`seq`
order by
act.create_at desc
limit 15) a
left join business_units b on
b.seq = a.business_unit_seq
left join config_activities ca on
ca.seq = a.activities_type
left join member manager on
manager.seq = a.manager_seq
left join mapping_assigned_entities map on
map.parent_entity_seq = a.seq
and map.parent_entity_code = '3'
and map.assigned_entity_code = '2'
left join config_product p on
p.seq = map.assigned_entity_seq
left join config_product old_p on
old_p.seq = a.product_type
left join activities_file af on
a.seq = af.ACTIVITIES
group by
`a`.`seq`지금 생각하면 최적화할게 많은 부끄러운 쿼리이지만 공유한다.
해결 과정
사실 조회 화면에 바로 들어갔을 때 자동으로 실행되는 쿼리에서는 제품이나, 담당자 조건 없이 검색되기 때문에 저 join이 쓸데 없다. 검색 조건문은 동적으로 작성하면서 검색을 위한 join은 동적으로 작성하지 않는 점이 걸려서 이를 동적으로 작성해줬다.
sub1 := tx.Table("activities act")
if len(cond.Member) > 0 {
sub1.Joins("LEFT JOIN mapping_assigned_entities map_member ON map_member.parent_entity_seq = act.seq AND map_member.parent_entity_code = ? AND map_member.assigned_entity_code = ?", mapping.ParentCodeActivities, mapping.EntityCodeMember)
}
if len(cond.ProductType) > 0 {
sub1.Joins("LEFT JOIN mapping_assigned_entities map_product ON map_product.parent_entity_seq = act.seq AND map_product.parent_entity_code = ? AND map_product.assigned_entity_code = ?", mapping.ParentCodeActivities, mapping.EntityCodeProduct)
}이렇게 하면, 쓸데없이 같은 테이블을 두번 join 했던 문제도 오히려 장점이 되는데, 내 무의식이 이렇게 최적화 할 걸 미리 염두에 뒀는지는 잘 모르겠다.
그런데 결과가 생각 이상으로 빨랐고, 더 놀랐던 건 검색 조건으로 상품, 담당자를 줬을 때도 쿼리가 여전히 빨랐다는 것이다. 심지어 상품이 있는 쿼리가 더 빨랐다.
비교
보다 정확한 속도 비교를 위해 dbeaver에서 프로파일링을 해봤다.
SET profiling=1;
실행 쿼리
SHOW profiles;위 쿼리를 실행해 속도를 확인할 수 있다.
SHOW VARIABLES LIKE 'query_cache_type';이 쿼리를 사용해 혹시 모를 캐시의 영향도 없는 것을 확인하는 것이 좋다.
결과는 다음과 같았다.
위부터 순서대로
- 원래 쿼리 - sub_query에 무조건 join을 걸었을 때
- 수정 쿼리 - 상품 있을 때 (가장 빠름)
- 수정 쿼리 - 상품 없을 때
검색 조건을 주면 원래대로 join도 이루어질 텐데 왜 빠르지? 하는 의문이 들었다. join 당시에는 어떤 상품,멤버를 선택했는지 알 수 없어서 조건문도 못 걸고 전체 join은 여전히 똑같이 할텐데 말이다.
또 join을 걸고 where문을 통해 필터링을 한 번 더 할 텐데, 상품 있을 때가 더 빠른 것도 신기했다.
이를 조사한 과정이 포스팅을 작성하게 된 이유이다.
Explain
말로만 듣던 Explain을 돌려봤다. 쿼리 실행이 어떤 순서로 이루어지는지 알 수 있는 기능이다.
EXPLAIN 쿼리간단히 기존 쿼리 앞에 EXPLAIN만 붙여서 실행할 수 있다.
읽는 법은 자세히 다루지 않겠지만, id가 같으면 같은 수준에서 실행되는 쿼리이고, id가 높은 것부터 실행된다.
주목할 건 Derived에 있는 680001이라는 숫자인데, 계약정보 테이블의 레코드 숫자이다.
1번 쿼리는 여기에 248의 레코드가 있는 map_product, map_member를 각각 두번 join한다.
3번 쿼리는 조건이 없으므로 아예 join을 안 한다.
2번 쿼리는 특이하게 act 테이블의 레코드가 1개로 나와있다.
ref를 보면 cis_dev_last.map_product.parent_entity_seq 가 나와, record를 크게 줄여진 것을 확인할 수 있다.
Analyze(format=json)
Explain은 계획만 세우는거고, Analyze는 실제 실행 후 결과를 제공해준다.
ANALYZE FORMAT=JSON 쿼리EXPLAIN 대신 위 키워드를 사용해 조회 할 수 있는데, json 포맷이 아닐때는 시간까지 제공해주지는 않는다. 실제 시간이 어디서 많이 걸리는지 알아보기 위해 json을 뽑아봤다.
첫번째 쿼리
실행에서 총 걸린시간은 3143.355673ms 이다. 이 중 derived2를 실행하는데 3139.079168ms 가 걸려 대부분의 시간을 차지했다.
조인을 하는 block_nl_join 의 r_other_time_ms ( 조회 외의 시간. 즉) 이 693.8062251ms(상품), 2221.018229ms(멤버) 씩 걸려 확실히 join이 병목이라는 걸 알 수 있다.
멤버의 시간이 더 많이 걸리는 이유는 더 찾아봐야 할 것 같다.
"query_block": {
"select_id": 2,
"r_loops": 1,
"r_total_time_ms": 3139.079168,
"filesort": {
"sort_key": "act.create_at desc",
"r_loops": 1,
"r_total_time_ms": 33.87966682,
"r_limit": 15,
"r_used_priority_queue": true,
"r_output_rows": 16,
"r_sort_mode": "sort_key,rowid",
"temporary_table": {
"table": {
"table_name": "act",
"access_type": "ALL",
"r_loops": 1,
"rows": 68001,
"r_rows": 73139,
"r_table_time_ms": 115.2842629,
"r_other_time_ms": 61.40442955,
"filtered": 100,
"r_filtered": 87.53059243,
"attached_condition": "act.del_flag = 0 and act.dept like '%내부서%' and act.`type` = '2'"
},
"block-nl-join": {
"table": {
"table_name": "map_product",
"access_type": "ALL",
"r_loops": 43,
"rows": 248,
"r_rows": 248,
"r_table_time_ms": 5.234950885,
"r_other_time_ms": 693.8062251,
"filtered": 100,
"r_filtered": 10.88709677,
"attached_condition": "map_product.parent_entity_code = '3' and map_product.assigned_entity_code = '2'"
},
"buffer_type": "flat",
"buffer_size": "256Kb",
"join_type": "BNL",
"attached_condition": "trigcond(map_product.parent_entity_seq = act.seq and map_product.parent_entity_code = '3' and map_product.assigned_entity_code = '2')",
"r_filtered": 3.704397942
},
"block-nl-join": {
"table": {
"table_name": "map_member",
"access_type": "ALL",
"r_loops": 43,
"rows": 248,
"r_rows": 248,
"r_table_time_ms": 5.381639893,
"r_other_time_ms": 2221.018229,
"filtered": 100,
"r_filtered": 10.08064516,
"attached_condition": "map_member.parent_entity_code = '3' and map_member.assigned_entity_code = '1'"
},
"buffer_type": "incremental",
"buffer_size": "256Kb",
"join_type": "BNL",
"attached_condition": "trigcond(map_member.parent_entity_seq = act.seq and map_member.parent_entity_code = '3' and map_member.assigned_entity_code = '1')",
"r_filtered": 4.000499758
}
}
}
}두번째 쿼리
시간도 시간이지만, 실제 JOIN을 안한다! 내부적으로 map_product에 자체적으로 조건을 걸고 해당 테이블을 참조하는 방식을 사용한다.
"query_block": {
"select_id": 2,
"r_loops": 1,
"r_total_time_ms": 0.283671014,
"filesort": {
"sort_key": "act.create_at desc",
"r_loops": 1,
"r_total_time_ms": 0.010952455,
"r_limit": 15,
"r_used_priority_queue": false,
"r_output_rows": 1,
"r_buffer_size": "336",
"r_sort_mode": "sort_key,rowid",
"temporary_table": {
"table": {
"table_name": "map_product",
"access_type": "ALL",
"r_loops": 1,
"rows": 248,
"r_rows": 248,
"r_table_time_ms": 0.120472137,
"r_other_time_ms": 0.063653871,
"filtered": 100,
"r_filtered": 0.403225806,
"attached_condition": "map_product.assigned_entity_seq = 252 and map_product.parent_entity_code = '3' and map_product.assigned_entity_code = '2'"
},
"table": {
"table_name": "act",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["seq"],
"ref": ["cis_dev_last.map_product.parent_entity_seq"],
"r_loops": 1,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.024988675,
"r_other_time_ms": 0.029360808,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "act.del_flag = 0 and act.dept like '%내부서%' and act.`type` = '2' and map_product.parent_entity_seq = act.seq"
}
}
}
}
배운 것
- sql Explain, Analyze를 통해 sql 쿼리 성능 분석하기
- 엔진이 계획을 세우면서 JOIN에서 실제 JOIN을 안 할 수도 있다는 것
생각
복잡한 sql 작성할 때 Explain 한번씩 돌려보는 게 좋을 것 같다.
Explain 이 자격증 딸 때 어렴풋이 본 것 같은데, sqld나 sqlp 공부해보면 또 알게 되는 게 나올 것 같다.
'TIL' 카테고리의 다른 글
| 1/21 TIL 버전은 어떤 기준으로 올라갈까? 시맨틱 버저닝 (0) | 2025.01.22 |
|---|---|
| 1/20 TIL C++ emplace_back() (0) | 2025.01.20 |
| Go 웹 API swagger로 문서화 패키지 고르기 (0) | 2025.01.20 |
| 1/19 TIL 내가 몰랐던 Go 웹 서버 개발시 코드 패턴 (0) | 2025.01.19 |
| Go가 빌드 시간 문제를 해결한 방법 (0) | 2025.01.19 |
