MySQL - query-optimization(3)¶
이번 포스팅은 사내 MySQL 관련 내용 발표를 위해 Real MySQL 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다.
MySQL 역시 내용이 방대하기 때문에 시리즈로 나누어 정리하고 있으며, 이번에는 쿼리 최적화 중에서도 JOIN을 사용할 때 성능을 극대화하기 위해 반드시 알아야 하는 핵심 원칙에 대해 알아본다.
1. JOIN의 순서와 인덱스 탐색 원리¶
조인 성능을 최적화하기 전에 먼저 인덱스 레인지 스캔(Index Range Scan)이 레코드를 읽는 3단계 과정을 되짚어보자.
- 인덱스 탐색(Index Seek): 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다.
- 인덱스 스캔(Index Scan): 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 순차적으로 읽는다.
- 데이터 읽기: 2번에서 읽어 들인 인덱스 키와 레코드 주소(RowID)를 이용해 실제 데이터가 저장된 페이지를 가져와 최종 레코드를 읽는다.
드라이빙 테이블 vs 드리븐 테이블의 부하 차이¶
- 드라이빙 테이블 (Driving Table, 외곽 테이블): 인덱스 탐색 작업을 단 한 번만 수행하고 그 이후부터는 스캔만 실행한다.
- 드리븐 테이블 (Driven Table, 내부 테이블): 드라이빙 테이블에서 읽은 레코드 건수만큼 인덱스 탐색과 스캔 작업을 반복한다.
💡 핵심: 두 테이블이 1:1로 조인되더라도 드리븐 테이블을 읽는 작업이 훨씬 큰 부하를 차지한다. 따라서 옵티마이저는 항상 드리븐 테이블을 최적으로 읽을 수 있도록 실행 계획을 수립한다.
조인 컬럼의 인덱스 유무에 따른 4가지 케이스¶
다음과 같은 기본 조인 쿼리를 살펴보자:
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no = de.emp_no;
① 두 컬럼 모두 각각 인덱스가 있는 경우¶
어느 테이블을 드라이빙으로 선택하든 인덱스를 이용해 드리븐 테이블의 검색 작업을 빠르게 처리할 수 있다. 이 경우 옵티마이저는 통계 정보를 바탕으로 전체 비용이 적은 쪽을 드라이빙 테이블로 선택한다.
② employees.emp_no에만 인덱스가 있는 경우¶
만약 dept_emp가 드리븐 테이블이 된다면, employees 레코드 수만큼 dept_emp 테이블을 매번 풀 스캔해야 한다. 따라서 옵티마이저는 인덱스가 없는 dept_emp를 드라이빙 테이블로 두고, 인덱스가 있는 employees를 드리븐 테이블로 선택한다.
③ dept_emp.emp_no에만 인덱스가 있는 경우¶
2번과 반대로 동작한다. employees의 반복된 풀 스캔을 막기 위해 employees를 드라이빙 테이블로, dept_emp를 드리븐 테이블로 선택한다.
④ 두 컬럼 모두 인덱스가 없는 경우¶
어느 쪽을 드라이빙으로 선택하든 드리븐 테이블의 풀 스캔이 불가피하다. 이때는 레코드 건수가 적은 테이블을 드리븐 테이블로 선택하며, MySQL은 성능 저하를 완화하기 위해 조인 버퍼(Join Buffer)를 사용한다. (실행 계획 Extra 컬럼에 Using join buffer 표시)
💡 결론: 조인 시 드리븐 테이블 풀 스캔은 양쪽 컬럼 모두에 인덱스가 없을 때만 발생하며, 인덱스가 한쪽에만 있다면 인덱스가 있는 테이블이 무조건 드리븐 테이블이 된다.
2. JOIN 컬럼의 데이터 타입 일치 원칙¶
WHERE 조건과 마찬가지로, 조인 조건에서도 비교되는 두 컬럼의 데이터 타입이 반드시 일치해야 인덱스를 효율적으로 활용할 수 있다.
대표적인 타입 불일치 위험 패턴¶
- 타입의 대분류가 다른 경우:
CHAR/VARCHAR타입과INT타입의 조인 - 문자셋(Charset) 또는 콜레이션(Collation)이 다른 경우:
utf8mb4_general_civsutf8mb4_unicode_ci등 - 부호(Sign) 여부가 다른 경우:
SIGNED INTvsUNSIGNED INT
3. OUTER JOIN과 COUNT(*) 최적화¶
웹 서비스에서 페이징 처리를 할 때 전체 레코드 수를 구하기 위해 OUTER JOIN과 COUNT(*)를 함께 사용하는 경우가 많다. 하지만 본문 데이터 조회 쿼리를 그대로 복사해서 SELECT COUNT(*)로만 바꾸면 불필요한 OUTER JOIN으로 인해 심각한 성능 저하가 발생한다.
불필요한 OUTER JOIN 제거 조건¶
다음 2가지 조건이 만족된다면 페이징 카운트 쿼리에서 OUTER JOIN된 테이블을 과감히 제거해도 동일한 결과가 나온다:
- 드라이빙 테이블과 드리븐 테이블의 관계가 1:1 또는 M:1인 경우 (조인으로 인해 레코드 건수가 증폭되지 않음)
- 드리븐 테이블에 조인 조건 이외의 별도
WHERE조건이 없는 경우 (조인으로 인해 레코드가 줄어들지 않음)
불필요한 조인 테이블을 제거하면 카운트 쿼리 속도를 비약적으로 향상시킬 수 있다.
4. INNER JOIN과 OUTER JOIN의 올바른 선택¶
간혹 "OUTER JOIN은 느리므로 억지로라도 INNER JOIN으로 바꿔야 한다"고 오해하는 경우가 있다.
하지만 실제 가져와야 하는 레코드 결과가 동일하다면 MySQL에서 INNER JOIN과 OUTER JOIN의 실행 속도 차이는 거의 없다. 따라서 조인 방식은 성능이 아닌 업무 요건(데이터의 누락 여부)에 따라 선택하는 것이 바람직하다.
5. 지연된 조인(Delayed Join)을 통한 페이징 최적화¶
지연된 조인(Delayed Join)이란, 조인을 수행하기 전에 GROUP BY나 ORDER BY, LIMIT 처리를 서브쿼리(인라인 뷰)로 먼저 실행하여 조인 대상 건수를 최소화한 뒤 조인을 수행하는 기법이다.
5.1 문제의 쿼리 (일반 페이징)¶
SELECT *
FROM employees e
LEFT JOIN dept_emp de ON de.emp_no = e.emp_no
AND NOW() BETWEEN de.from_date AND de.to_date
LIMIT 10, 10;
- 첫 페이지(
LIMIT 0, 10)는 빠르지만, 페이지 번호가 뒤로 갈수록 심각하게 느려진다. - 위 쿼리는
employees를 풀 스캔하면서 매번dept_emp와 조인하여 20건을 모은 뒤, 앞의 10건을 버리고 뒤의 10건만 반환한다. 만약LIMIT 10000, 10이라면 10,010건에 대해 조인을 전부 수행한 후 10,000건을 버리는 비효율이 발생한다.
5.2 지연된 조인 적용 쿼리¶
SELECT *
FROM (
SELECT *
FROM employees e
LIMIT 10, 10
) e
LEFT JOIN dept_emp de ON de.emp_no = e.emp_no
AND NOW() BETWEEN de.from_date AND de.to_date;
employees테이블에서 실제 화면에 표시할 10건의 레코드만 인라인 뷰로 먼저 추출한다.- 그 후 추출된 단 10건의 레코드에 대해서만
dept_emp와 조인을 수행하므로, 조인 횟수가 수천~수만 번에서 단 10번으로 급감한다.
5.3 지연된 조인 적용 시 필수 확인 조건¶
모든 쿼리에 지연된 조인을 적용할 수 있는 것은 아니며, 반드시 다음 조건을 검증해야 한다:
LEFT OUTER JOIN인 경우: 드라이빙 테이블과 드리븐 테이블이1:1또는M:1관계여야 한다.INNER JOIN인 경우:1:1또는M:1관계이면서, 드라이빙 테이블의 모든 레코드가 드리븐 테이블에 반드시 존재해야 한다. (서브쿼리에서 미리 걸러진 레코드가 조인 후에 누락되지 않음을 보장해야 함)
마치며¶
조인 쿼리 최적화의 핵심은 "드리븐 테이블의 검색 부하를 최소화하는 것"과 "불필요한 조인 연산 횟수 자체를 줄이는 것"이다. 데이터 타입 일치, 페이징 카운트 쿼리 조인 제거, 지연된 조인 테크닉을 활용하면 대규모 데이터 환경에서도 빠른 응답 속도를 유지할 수 있다.