쿼리문의 성능 측정
문제를 해결하는 두 가지 방법
JPA를 공부하다가 예전에 했던 프로젝트를 떠올렸고 문득 성능에 관한 궁금증이 생겼다.
데이터베이스에서 쿼리문을 실행할 때, 조건이 많은 복잡한 쿼리문을 실행하면 실행 시간이 오래걸리게 된다.
그래서 최대한 단순한 쿼리문을 사용하는 것이 좋다고 생각했는데, 쿼리문 실행이 많아지게 되면 마찬가지로 실행 시간이 늘어나게 된다는 것이다.
예전에 했던 프로젝트에서 외부 API를 이용하여 음식점 리스트를 얻으면 리스트에서 미리 만들어 놓은 프랜차이즈 매장에 대한 데이터베이스를 조회하여 일치하는 값이 있으면 해당 값을 삭제해서 대형 프랜차이즈를 제외한 음식점 리스트를 반환해야하는 기능이 있었다.
해당 프로젝트를 진행하던 당시에는 JPQL을 이용한 쿼리문 작성과 성능 문제에 대해 잘 몰랐었지만 지금 생각해보니 trade-off를 따져서 적절한 방법을 사용해야 할 것 같다고 생각했다.
내가 생각해낸 대형 프랜차이즈를 제외한 음식점을 반환하는 기능을 구현하는 방법은 두 가지가 있다.
- 외부 API를 사용하여 얻은 음식점 리스트를 순회하며 데이터베이스를 조회하고 결과에 따라 삭제하거나 다음 원소로 넘어간다
- 음식점의 이름만으로 조회하기 때문에 쿼리문이 단순해서 쿼리문 하나하나가 실행되는 시간이 오래걸리지 않는다.
- 쿼리문 호출이 많이 발생한다.
- JPQL을 통해 쿼리문을 커스터마이징하고 외부 API를 사용하여 얻은 음식점 리스트 전체 내용물에 대해 한 번에 조회한다.
- 음식점 리스트의 원소의 갯수만큼 쿼리문이 실행되지 않고 딱 한 번만 실행된다.
- 쿼리문 작성이 다소 복잡하고 실행시간이 비교적 오래 걸린다.
쿼리문 성능 측정 방법
MySQL은 쿼리문 실행에 대해 성능을 측정할 수 있는 profile기능이 존재한다.
성능을 측정할 데이터베이스를 선택하고 select @@profiling;쿼리를 실행하면 현재 데이터베이스에 profile기능이 활성화 되어있는지를 알 수 있다. 기본값은 0이며 0은 비활성화를 의미한다. profile기능을 활성화 하기 위해 set profiling=1;쿼리를 실행하면 profile기능이 활성화된다.
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
set profiling=1;쿼리를 실행한 결과, @@profiling이 1로 변경된 것을 알 수 있다.
profile기능으로 쿼리문이 실행되는 데에 걸리는 시간을 확인하고 싶으면 show profiles;쿼리를 실행하면 된다.
mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00024500 | select @@profiling |
+----------+------------+--------------------+
1 rows in set, 1 warning (0.00 sec)
set profiling=1;쿼리가 실행된 이후에 실행된 쿼리문에 대한 실행시간이 나타게 된다.
복잡한 쿼리 하나와 간단한 쿼리 여러 개의 성능 비교
성능 측정을 위해 사용할 데이터베이스는 예전에 프로젝트를 하면서 공정거래위원회 사이트를 크롤링하여 생성한 체인점이 20개 이상인 프랜차이즈 브랜드에 대한 데이터베이스를 사용할 것이다.
일단 각각의 음식점 이름을 하나씩 쿼리문으로 조회한 결과는 아래와 같다.
mysql> show profiles;
+----------+------------+----------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------+
| 6 | 0.00168700 | select * from franchise where name like '%맘스터치%' |
| 7 | 0.00150500 | select * from franchise where name like '%맥도날드%' |
| 8 | 0.00249100 | select * from franchise where name like '%써브웨이%' |
| 9 | 0.00233900 | select * from franchise where name like '%버거킹%' |
+----------+------------+----------------------------------------------------------+
9 rows in set, 1 warning (0.00 sec)
네 개의 음식점 상호명을 이용하여 각각 조회를 실행한 결과 총 8.022ms가 걸렸다.
외부 API로 받은 음식점 리스트가 정확히 데이터베이스의 상호명과 일치하지 않고 ‘버거킹 00점’과 같이 나와서 조회 쿼리에
LIKE를 사용하였다.
그리고 OR문으로 여러 개의 검색어를 한 번에 조회한 결과는 아래와 같다.
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| 10 | 0.00233900 | select * from franchise where name like '%맘스터치%' or name like '%맥도날드%' or name like '%써브웨이%' or name like '%버거킹%' |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)
네 개의 음식점 상호명을 이용하여 하나씩 쿼리문을 실행했을 때와 같은 조회결과를 보여주지만 걸린 시간은 2.339ms로 약 3.43배 가량 빠른 것을 확인할 수 있었다.
하나식 쿼리문으로 조회했을 때와 네 개의 음식점 상호명을 이용하여 하나씩 쿼리문을 실행했을 때의 쿼리를 여러번 반복하여 평균을 구해본 결과 비슷한 값이 나왔다.
또한
show profile CPU for query <index>;를 이용하여 쿼리문의 CPU사용량을 비교해본 결과도 수행 시간과 동일한 경향을 보였다.
그렇다면 조금 더 복잡한 쿼리문에 대해서도 같은 결과가 나올까?
select category GROUP_CONCAT(name order by name asc) as franchise_names,
sum(count) as total_count from franchise
where name like '%투썸%'
or name like '%메가%'
or name like '%빽다방%'
or name like '%컴포즈%'
or name like '%매머드%'
or name like '%카페베네%'
or name like '%탐앤탐스%'
or name like '%할리스%'
or name like '%요거프레소%'
or name like '%더벤티%'
group by category order by total_count desc;
위의 쿼리문은 10개의 키워드를 이용하여 키워드가 포함된 음식점브랜드를 카테고리별로 묶어서 이름을 알파벳순으로 정렬하고 가맹점 수의 합이 많은 순으로 카테고리를 정렬한 결과를 반환한다.
위의 쿼리문을 실행하고 profile을 조회한 결과는 아래와 같다.
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 11 | 0.00424200 | select category GROUP_CONCAT(name order by name asc) as franchise_names, sum(count) as total_count from franchise where name like '%투썸%' or name like '%메가%' or name like '%빽다방%' or name like '%컴포즈%' or name like '%매머드%' or name like '%카페베네%' or name like '%탐앤탐스%' or name like '%할리스%' or name like '%요거프레소%' or name like '%더벤티%' group by category order by total_count desc; |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
다음으로 10개의 키워드를 포함하는 결과를 하나씩 조회한 결과이다.
mysql> show profiles;
+----------+------------+-------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------+
| 12 | 0.00205100 | select * from franchise where name like '%투썸%' |
| 13 | 0.00201000 | select * from franchise where name like '%메가%' |
| 14 | 0.00183100 | select * from franchise where name like '%빽다방%' |
| 15 | 0.00196200 | select * from franchise where name like '%컴포즈%' |
| 16 | 0.00252900 | select * from franchise where name like '%매머드%' |
| 17 | 0.00210000 | select * from franchise where name like '%카페베네%' |
| 18 | 0.00240600 | select * from franchise where name like '%탐앤탐스%' |
| 19 | 0.00234400 | select * from franchise where name like '%할리스%' |
| 20 | 0.00183900 | select * from franchise where name like '%요거프레소%' |
| 21 | 0.00174800 | select * from franchise where name like '%더벤티%' |
+----------+------------+-------------------------------------------------------------+
SUM, GROUP BY, ORDER BY와 같은 조건이 추가된 복잡한 쿼리문은 WHRER, LIKE문을 포함하고 있는 비교적 단순한 쿼리문보다는 실행시간이 약 2배 정도로 길게 나타나지만 결과적으로 10개의 키워드를 조회하기 위한 총 실행 시간은 복잡한 쿼리문을 사용하는 방법이 약 5배 정도 빠른 것을 알 수 있다.
또한 복잡한 쿼리문은 정렬, 그룹화 등의 기능을 사용하여 원하는 결과를 더 알아보기 쉽게 반환할 수 있었다.
결론
복잡한 쿼리문 하나와 단순한 쿼리문 여러개의 실행시간을 비교해 보았을 때, 복잡한 쿼리문 하나를 사용하는 것이 더 적은 실행시간을 보여주었다. 쿼리문이 얼마나 복잡해지느냐에 따라서 다르겠지만 쿼리문 호출을 줄이는 것이 트랜잭션의 생성 횟수도 줄일 수 있기 때문에 대부분의 경우 빠른 성능을 낼 수 있을 것 같다.
물론 성능 측정에는 실행시간 이외에도 CPU사용량, 메모리 사용량 등의 다양한 지표가 존재한다. 또한 성능에 영향을 주는 요인으로 쿼리문 실행시간 이외에도 다양한 요소가 존재한다. 그리고 JPQL로 쿼리문을 전달하기 위해 문자열로 쿼리문을 생성하는 과정이 코드의 가독성을 낮출 수도 있어보인다. 따라서 정확한 성능 비교를 위해서는 더 다양항 요인들을 따져보고 알맞은 방법을 선택해야할 것 같다.