- MySQL과 MariaDB에서 테이블의 데이터를 결합하는 조인 알고리즘은 대부분 중첩 루프 조인을 사용한다.
- 성능 최적화를 위해 다른 조인 알고리즘을 제공하지만, 대부분의 조인 방식이 중첩 루프 조인에 기반을 두므로 튜닝 분석 시 실행 계획을 확인해야 한다.
작은 테이블이 먼저 조인에 참여하는 SQL 문
현황 분석
- 부서사원_매핑 테이블과 부서 테이블을 조인하여 부서 시작일자가 '2002-03-01' 이후인 사원의 데이터를 조회하는 쿼리
- 드라이빙 테이블인 부서 테이블과 드리븐 테이블인 부서사원_매핑 테이블은 중첩 루프 조인을 수행한다.
- 작은 크기의 부서 테이블에서 부서.부서번호 컬럼만 SELECT 절과 WHERE 절에 필요하므로, 부서명 인덱스를 활용해 인덱스를 풀 스캔한다.
- 상대적으로 큰 크기의 부서사원_매핑 테이블은 부서번호 인덱스로 인덱스 스캔을 수행하는데, 이때 rows 항목은 SQL 문을 수행하기 위해 조사한 행의 예측 건수로, 인덱스 스캔을 하고 랜덤 액세스로 테이블에 접근하게 된다.
- 이처럼 드리븐 테이블에서 대량의 데이터에 대해 랜덤 액세스하면 비효율적
- 또한, MySQL 엔진으로 가져온 모든 데이터에 대해 WHERE 절의 필터 조건(매핑.시작일자 >= '2002-03-01')을 수행한다.
튜닝 수행
- 만약 상대적으로 규모가 큰 부서사원_매핑 테이블의 매핑.시작일자 >= '2002-03-01' 조건절을 먼저 적용할 수 있다면 조인할 때 비교 대상이 줄어들 것
- 또한, 부서사원_매핑 테이블에 대해 시작일자 컬럼이 범위 조건으로 작성되는지, 그 범위에 해당하는 데이터가 전체 데이터 대비 얼마인지를 분석해봐야 한다.
- 만약 소량인 경우 부서사원_매핑 테이블에 시작일자 컬럼 기준으로 인덱스를 생성한다면 인덱스 스캔을 통해 더 효율적인 데이터를 조회할 수 있다.
튜닝 결과
- 부서사원_매핑 테이블에 필요한 데이터 건수를 줄일 수 있는 조건절을 적절히 활용하여 드라이빙 테이블에서의 조인 비교 건수를 줄이도록 한다.
- STRAIGTH_JOIN 힌트를 사용하여 FROM 절에 작성된 테이블 순서대로 조인에 참여할 수 있도록 고정한다.
- 먼저 접근하는 드라이빙 테이블은 부서사원_매핑 테이블로, 테이블의 랜덤 액세스 없이 테이블 풀 스캔으로 한 번에 다수의 페이지에 접근한다.
- 드라이빙 테이블에서 추출된 데이터만큼 반복하여 접근하게 되는 드리븐 테이블은 부서 테이블이 된다.
- 즉, 상대적으로 대용량은 부서사원_매핑 테이블을 테이블 풀 스캔으로 처리하고, 부서 테이블에는 기본 키로 반복 접근하여 1개의 데이터에만 접근하는 식으로 수행된다.
메인 테이블에 계속 의존하는 SQL 문
현황 분석
- 사원번호가 450000보다 크고 최대 연봉이 100000보다 큰 데이터를 찾아 출력하는 쿼리
- 메인쿼리인 사원 테이블에서는 WHERE 조건절에서 사원번호 추출 대상을 정의하고, 중첩 서브쿼리의 급여 테이블에서는 메인 테이블의 사원번호를 매번 받아와 해당 사원의 최대 연봉 데이터를 확인한다.
- id가 1인 사원 테이블이 기본 키를 활용해서 범위 스캔을 수행한다.
- 다음으로 id가 2인 급여 테이블에 접근하는데, 해당 쿼리는 외부의 사원 테이블로부터 조건절을 전달받아 수행하는 의존성을 가진 서브쿼리이다.
튜닝 수행
- 보통 실행 계획의 select_type 항목에 DEPENDENT 키워드가 있으면, 외부 테이블에서 조건절을 받은 뒤 처리되어야 하므로 튜닝 대상으로 고려된다.
튜닝 수행
- 먼저 WHERE 절의 서브쿼리를 조인으로 변경하면서 GROUP BY 절과 HAVING 절을 이용하여 그룹별 최대값을 계산하도록 한다.
- 즉, GROUP BY 절에서는 사원번호별 그루핑을 수행하고 HAVING 절에서는 연봉의 최대값으로 조건을 설정하여 데이터를 조회한다.
- 먼저 접근하는 드라이빙 테이블은 급여 테이블이고, 그 다음으로 접근하는 드리븐 테이블은 사원 테이블이다.
- 사원 테이블과 급여 테이블을 단순히 조인하는 방식으로 변경되어 수행 효율이 향상된다.
불필요한 조인을 수행하는 SQL 문
현황 분석
- FROM 절에서 사원 테이블과 사원출입기록 테이블로 작성한 인라인 뷰를 사원번호 컬럼으로 내부 조인하는 쿼리
- 즉, A 출입문으로 출입한 사원이 총 몇 명인지 구하는 쿼리
- 드라이빙 테이블인 사원출입기록 테이블은 출입문 인덱스를 활용하여 A 출입문에 관한 기록이 있는 사원번호를 구한다.
- WHERE 절에서는 값이 'A'인 상수와 직접 비교하므로 ref 항목이 const로 출력되고, 인덱스를 사용한 동등 비교를 수행하므로 type 항목이 ref로 표시된다.
- 드리븐 테이블인 사원 테이블은 기본 키를 사용해서 조인 조건절인 사원번호 컬럼으로 데이터를 비교한다.
- 드리븐 테이블에서 기본 키를 사용하므로 type 항목은 eq_ref로 표시된다.
튜닝 수행
- FROM 절의 인라인 뷰는 사실상 옵티마이저에 의해 조인 방식이 뷰 병합으로 최적화되어 아래와 같이 수행된다.
SELECT COUNT(DISTINCT 기록.사원번호) as 데이터건수
FROM 사원, 사원출입기록 기록
WHERE 사원.사원번호 = 기록.사원번호 AND 출입문 = 'A';
- 사원출입기록 테이블의 사원번호는 사원 테이블과 조인을 수행하는 과정 중에 값의 존재 여부만 알면 된다.
- 따라서 실제로 사원출입기록 테이블의 데이터 결과가 최종 결과에 어떻게 활용되는지 확인해야 한다.
튜닝 결과
- 사원출입기록 테이블의 데이터는 최종 결과에 사용하지 않고 단지 존재 여부만 파악하면 되므로 EXIST 구문으로 변경한다.
- 출입문 A에 관한 기록이 있는 사원번호에 대해 조인을 수행한 뒤, 해당하는 데이터만 집계한다.
- 사원 테이블은 드라이빙 테이블이고, <subquery2>는 드리븐 테이블이다.
- <subquery2>는 id가 2인 사원출력기록 테이블로서, 사원출력기록 테이블은 EXISTS 연산자로 데이터 존재 여부를 파악하기 위해 임시 테이블을 생성하는 MATERIALIZED로 표기된다.
'Computer Science > SQL Tuning' 카테고리의 다른 글
SQL 문 튜닝 (0) | 2022.02.22 |
---|---|
SQL 프로파일링 (0) | 2022.02.21 |
MySQL과 MariaDB 실행 계획 (0) | 2022.02.21 |
인덱스와 인덱스 스캔 (0) | 2022.02.19 |