4. MySQL SQL 튜닝 2 (조인)

임쿠쿠·2022년 1월 10일
0

MySQL

목록 보기
5/7
post-thumbnail

1) 작은 테이블이 먼저 조인에 참여하는 나쁜 SQL 문

(1) 현황분석

select 매핑.사원번호, 부서.부서번호
    from 부서사원_매핑 매핑, 부서
    where 매핑.부서번호 = 부서.부서번호
    and 매핑.시작일자 >= '2002-03-01';
  • 부서 테이블에 먼저 접근 후 UI_부서명 인덱스를 활용해 인덱스 풀 스캔
  • 상대적으로 큰 크기의 부서사원매핑 테이블은 I부서번호 인데스로 인덱스 스캔 수행, 이때 rows 항목이 4만건으로 인덱스 스캔을 하고 랜덤 엑세스로 테이블에 접근
  • 부서사원_매핑 테이블에 30만 건 이상의 데이터를 MySQL 엔진으로 가져온 모든 데이터에 대해 where 절의 필터 조건(매핑.시작일자) 수행
    => 매핑.시작일자 조건절을 먼저 적용할 수 있다면 조인 시 비교 대상 줄어듬

(2) 튜닝 수행

select straight_join
       매핑.사원번호, 매핑.부서번호
    from 부서사원_매핑 매핑
    where 매핑.시작일자 >= '2002-03-01';
  • 상대적으로 대용량인 부서사원_매핑 테이블을 테이블 풀 스캔 처리하고, 부서 테이블에는 기본 키로 반복 접근하여 1개의 데이터에만 접근

2) 메인 테이블에 계속 의존하는 나쁜 SQL 문

(1) 현황분석

explain select 사원.사원번호, 사원.이름, 사원.from 사원
    where 사원번호 > 450000
    and (select max(연봉)
        from 급여
        where 사원번호 = 사원.사원번호
        ) > 100000;
  • 사원 테이블의 PK를 활용해 범위 스캔 수행
  • 서브쿼리는 급여 테이블의 PK 활용
  • 실행 계획의 select_type에 DEPENDENT라는 키워드는 외부 테이블에서 조건절을 받은 뒤 처리되므로 튜닝 대상 고려

(2) 튜닝 수행

explain select 사원.사원번호, 사원.이름, 사원.from 사원, 급여
    where 사원.사원번호 > 450000
    and 사원.사원번호 = 급여.사원번호
    group by 사원.사원번호
    having max(급여.연봉) > 100000;
  • 드라이빙 테이블은 급여 테이블, 드리븐 테이블은 사원 테이블
  • 급여 테이블에 먼저 접근하기 위한 범위 축소 조건은 사원.사원번호 > 450000 절을 통해 급여.사원번호 > 450000 조건절로 변형되어 적용

3) 불필요한 조인을 수행하는 나쁜 SQL 문

(1) 현황분석

explain select count(distinct 사원.사원번호) as 데이터건수
    from 사원,
         (select 사원번호
             from 사원출입기록 기록
             where 출입문 = 'A'
             ) 기록
    where 사원.사원번호 = 기록.사원번호;
  • 드라이빙 테이블인 사원출입기록 테이블은 I_출입문 인덱스 활용하여 A 출입문에 관한 기록이 있는 사원번호 구함
  • 값이 'A'인 상수와 직접 비교하므로 ref 항목이 const, 인덱스를 사용한 동등 비교 수행하므로 type 항목이 ref로 표시
  • 드리븐 테이블인 사원 테이블은 PK를 사용해서 조인 조건절인 사원번호 열로 데이터 비교, type 항목의 eq_ref는 드리븐 테이블에서 PK를 사용 시 표시되는 유형

(2) 튜닝 수행

explain select count(1) as 데이터건수
    from 사원
    where exists(select 1
                    from 사원출입기록 기록
                    where 출입문 = 'A'
                    and 기록.사원번호 = 사원.사원번호
        );
  • 사원번호에 대한 중복 제거를 구하기 보다는, 사원출입기록의 사원번호는 사원 테이블과 조인 과정에서 조건에 따른 값으 존재 여부만 확인 -> EXISTS 사용
  • id가 1인 table 항목에 먼저 출려된 사원 테이블은 드라이빙 테이블이고, subquery2는 드리븐 테이블로서, 사원출력기록 테이블은 EXISTS 연산자로 데이터 존재 여부를 파악하기 위해 임시 테이블을 생성하는 MATERIALIZED로 표기

참고 - SQL 튜닝 / 양바른 저

profile
Pay it forward

0개의 댓글