JOIN COUNT쿼리 성능 개선해보기

query timeout 파헤치기

서비스를 운영하는 도중 timeout이 자주 발생하는 쿼리를 하나 발견했습니다.
문제의 쿼리는 A테이블의 정보를 페이징 처리해 보여줄 때 필요한 count쿼리로 아래와 같은 쿼리였습니다.

SELECT 
	count(*) 
FROM 
	A LEFT JOIN B
ON A.row = B.row   

B테이블과 JOIN을 하는 이유는 B테이블의 속성값으로 조건을 활용하기 위해서 입니다. 만약 요청에 파라미터 값이 함께 들어온다면 그때는 동적으로 아래와 같은 형태의 쿼리가 됩니다.

SELECT 
	count(*) 
FROM 
	A LEFT JOIN B
ON A.val = B.val
WHERE
	B.val < 1000000
    AND B.val2 BETWEEN '20121212' AND '20240913'
    ...

WHERE조건은 JOIN과 더불어 가장 먼저 실행되는 쿼리입니다. 해당 조건을 통해 조회해야 할 데이터의 개수를 줄이면 전체 조회 쿼리의 성능을 크게 향상시킬 수 있습니다.

하지만 아무런 조건이 없는 쿼리가 종종 들어왔고 이때는 오히려 B테이블과의 JOIN이 성능에 방해가 됩니다. SELECT count(*) FROM A와 SELECT count(*) FROM A LEFT JOIN B의 결과값은 동일하기 때문에 이때는 굳이 JOIN을 실행할 이유가 없습니다.

이러한 상황에서 제가 생각한 문제 해결 방법은 다음과 같았는데요.

방법1. 요청에 반드시 조건을 포함하게 한다

방법2. 조건이 없으면 조인을 하지 않는다

방법3. 조인을 하더라도 타임아웃이 나지 않도록 쿼리를 튜닝한다

방법4. 쿼리 타임아웃 시간을 늘린다

방법3이 가장 좋아보였고 이를 시도했지만 실패했고, 실제로는 방법1을 통해 문제를 해결했습니다. 이번 글에서는 방법3을 시도하는 과정에서 어떤 식으로 쿼리를 튜닝하려 했고, 왜 해당 방식으로는 튜닝이 되지 않았는지에 대해 얘기해 보려고 합니다.

당시 상황에 대해

회사에서 사용한 테이블은 대략 다음과 같습니다. (실제 테이블과는 다소 차이가 있으므로 참고용으로만 봐주세요)

CREATE TABLE A (
	col1 VARCHAR(255),
    col2 VARCHAR(255),
    col3 VARCHAR(255),
    PRIMARY KEY (col1),
    INDEX idx (col2, col3)
);

CREATE TABLE B (
    col2 VARCHAR(255),
    col4 BIGINT,
    PRIMARY KEY (col2)
);

select count(*) from A left join B
on A.col2 = B.col2
;

• A테이블은 row1이 PK며, col2와 col3으로 만들어진 복합 인덱스가 존재합니다.
• B테이블은 row2가 PK입니다.
• A테이블과 B테이블은 col2값을 기준으로 조인합니다.
• A테이블에는 약 7_000_000개의 데이터가, B테이블에는 약 120_000개의 데이터가 들어 있습니다.

이때 다음과 같은 결과를 확인했습니다.

-- 40초
select count(*) from A left join B on A.col2 = B.col2

-- 3초
select count(*) from A

-- 12초
select count(*) from B left join A on A.col2 = B.col2

이 결과는 실제 회사 테이블로 실행했을 때 얻은 값이며, 위에서 보여드린 A,B테이블에 데이터를 넣어 실험했을 때는 다른 결과가 나왔습니다. MySQL버전부터(회사는 5.X, 테스트 환경은 8.X) 시작해 여러 요인들에 차이가 있어서 단순화한 테이블로는 비슷한 결과를 얻지 못한 것으로 생각됩니다. (추가. 실제로 회사 MySQL을 8.0으로 업그레이드 한 지금 동일한 쿼리지만 COUNT쿼리의 속도가 유의미하게 빨라진 상태입니다)

저는 SELECT count(*) from A쿼리가 가장 빠르니 JOIN에 성능을 저하시키는 원인이 있다 생각했고, 비록 동일한 결과를 얻게 되는 쿼리는 아니지만 SELECT count(*) from B left join A on A.col2 = B.col2쿼리가 빠르게 실행되는 걸 보면서 A테이블이 driving table이 됐을 때 비효율적인 작업이 발생한다고 생각했습니다.

두 JOIN쿼리의 실행 결과는 아래와 같았습니다.

explain select count(*) from A left join B on A.col2 = B.col2

select_type	table	type	key	key_len	rows
SIMPLE	A	index	idx	205	1,500,000
SIMPLE	B	eq_ref	PRIMARY	82	1

explain select count(*) from B left join A on A.col2 = B.col2

select_type	table	type	key	key_len	rows
SIMPLE	B	index	certain_idx	5	100,000
SIMPLE	A	ref	idx	82	15

이를 보면서 'B테이블은 JOIN조건인 col2가 Primary Key여서 빠른건가'라는 생각이 들었고, 'A테이블의 col2는 인덱스를 효율적으로 타고 있는건지' 궁금해 졌습니다.

알게된 사실

모든 궁금증을 다 해결하지는 못했지만 'A테이블의 col2는 인덱스를 효율적으로 타고 있는건지'에 대한 내용은 여기저기서 확인할 수 있었는데 결론만 얘기하면 인덱스를 효율적으로 타고 있었다입니다. 사실 실행 계획에서 이미 인덱스를 사용한다고 나왔기 때문에 굳이 확인해볼 필요가 없을 수 있지만, 개인적인 궁금증으로 해당 내용에 대해 조금 더 찾아봤습니다.

A테이블에 (col2, col3)으로 만들어진 idx는 복합인덱스입니다. MySQL공식문서를 찾아보면 컬럼 순서에 따라 인덱스를 사용할 수 있다고 얘기하는 걸 확인할 수 있습니다.

만약 col3으로 JOIN을 시도한다면 컬럼 순서가 적절하지 않아 idx인덱스를 사용하지 못하지만, 우리처럼 col2로 JOIN을 시도한다면 이때는 idx인덱스를 활용할 수 있습니다.

'그래도 복합 인덱스로 거는 것보다 col2에 단독으로 인덱스를 거는게 성능이 더 좋지 않을까?' 라고 생각할 수 있는데요. 실제로 간단한 테스트를 진행해보면 복합 인덱스보다 단일 인덱스가 조금 더 좋은 성능을 보이기도 합니다.

• 수행 시간
  • 
• 단독 인덱스를 사용했을 때
  • 
• 복합 인덱스를 사용했을 때
  • 

물론 간단한 테스트여서 해당 결과를 완벽히 신뢰할 수는 없는데요. 회사에서는 DB에 대한 권한이 없어서 직접 테스트할 수 없었고, DBA분에게 문의했지만 이미 인덱스를 사용하고 있기 때문에 해당 컬럼에 단독으로 인덱스를 추가하더라도 성능 향상은 없을거라는 답변과 함께 요청을 거절당했습니다.

아마도 '이미 복합인덱스를 활용하고 있기 때문에 단독 인덱스를 설정한다고 해서 엄청난 성능 개선을 가져오지는 못한다'는 의미가 아니였을까 생각됩니다. 또한 무분별하게 인덱스를 많이 만들면 Index Dive 단계에서 오히려 성능이 저하될 수 있으니 무작정 인덱스를 늘리는게 능사는 아니라고 생각합니다.