Back to writing

업무에 바로쓰는 SQL 튜닝

업무에 바로쓰는 SQL 튜닝

조인 알고리즘 용어

드라이빙 테이블과 드리븐 테이블

  • 드라이빙 테이블은 시작 기준 테이블, 드라이빙 테이블을 무엇으로 선정할지가 중요한 문제.
  • 가능하면 적은 결과가 반환될 것으로 예상되는 드라이빙 테이블을 선정
  • 조인 조건절의 열이 인덱스로 설정되도록 구성

NL 조인 (nested loop join)

  • 인덱스는 정의된 열 기준으로 순차 정렬되지만, 인덱스를 이용해 테이블의 데이터를 찾아가는 과정에서 랜덤 엑세스가 발생함
  • 랜덤 엑세스를 줄일 수 있도록 데이터의 엑세스 범위를 좁히는 방향으로 인덱스를 설계하고 조건 절을 작성해야함
  • 랜덤 액세스를 유발하는 인덱스는 기본 키가 아닌 비고유 인덱스일 경우에 해당

BNL 조인 (block nested loop join)

  • 드라이빙 테이블에서 인덱스가 없는 드리븐 테이블에 접근할 때, 매번 full scan이 일어나지 않도록 조인 버퍼 라는 개념을 도입함
  • 조인 버퍼를 두고 채워가며 조인하는 방식을 사용하여 1번의 full scan으로 완료

BKA 조인 (batched key access join)

  • NL 조인 방식은 필연적으로 데이터 접근 시 인덱스에 의한 랜덤 엑세스가 발생하므로, 엑세스할 데이터의 범위가 넓다면 비효율적인 조인 방식
  • 이러한 단점을 해결하고자 접근할 데이터를 미리 예상하고 가져오는 데 착안한 조인 알고리즘
  • BNL에서 활용한 드라이빙 테이블의 조인 버퍼 개념을 그대로 사용
  • 드리븐 테이블에 필요한 데이터를 미리 예측하고 정렬된 상태로 버퍼에 적재 (Multi range read)
  • 미리 예측된 데이터를 가져와 정렬된 상태에서 랜덤 버퍼에 담기 때문에, 드리븐 테이블에 대해 랜덤 액세스가 아닌 시퀀셜 엑세스를 수행

개념적인 튜닝 용어

오브젝트 스캔 유형

  • 테이블 풀 스캔: 테이블의 모든 데이터를 읽어옴, 인덱스 없이 사용하는 유일한 방식
  • 인덱스 범위 스캔: between 조건절이나 범위 조건절을 사용하는 경우, 좁은 범위를 스캔할 때는 효율적이지만 넓은 범위를 스캔할 때는 비효율적임
  • 인덱스 풀 스캔: 인덱스로 구성된 열 정보만 요구하는 SQL문에서 수행, 테이블 풀스캔 보단 유리함.
  • 인덱스 고유 스캔: 기본 키나 고유 인덱스로 테이블에 접근하는 방식, 인덱스를 사용하는 스캔 방식 중 가장 효율적임 where 절에 = 조건
  • 인덱스 루스 스캔: 인덱스의 필요한 부분들만 골라 스캔, 보통 group by나 max, min 함수가 포함되면 동작함, 불필요한 인덱스 키를 무시하고 접근
  • 인덱스 병합 스캔: 테이블 내에 생성된 인덱스들을 통합해서 스캔하는 방식, where 절 열들이 서로 다른 인덱스로 존재하면 옵티마이저가 해당 인덱스를 가져와서 모두 활용

디스크 접근 방식

원하는 데이터를 찾으려고 데이터가 저장된 스토리지의 페이지에 접근.

페이지 는 데이터를 검색하는 최소 단위로, 페이지 단위로 데이터를 읽고 쓰기를 수행 할 수 있음.

  • sequential access: 디스크 블록을 순차적으로 읽어오는 방식, 보통 테이블 풀 스캔에서 활용하며, 데이터를 찾고자 이동하는 디스크 헤더의 움직임을 최소화 하여 작업시간과 리소스 점유 비용을 줄일 수 있음.
  • random access: 물리적으로 떨어진 페이지들에 임의로 접근하는 방식. 물리적인 위치를 고려하지 않고 접근하기 때문에 데이터의 접근 수행 시간이 오래 걸림.

조건 유형

데이터에 엑세스 하는 조건문으로 데이터를 가져오고, 가져온 데이터에서 다시 한번 출력할 데이터만 추출.

  • access condition: 디스크에 있는 데이터에 어떻게 접근할 것인지, where 절의 특정 조건문을 이용해 소량의 데이터를 가져오고, 인덱스를 통해 시간낭비를 줄이는 조건절 선택.
  • filter condition: 데이터를 가져온 후 불필요한 데이터를 제거하거나 가공하는 조건, 스토리지 엔진에서 MySQL 엔진으로 데이터를 전달하는 오버헤드가 있으므로 필터 조건으로 제거될 데이터라면 스토리지 엔진의 데이터에 접근하는 과정에서 같이 제외되는 편이 성능적으로 좋음.

응용 용어

  • selectivity: 테이블의 특정 열을 기준으로 해당 열의 조건절에 따라 선택되는 데이터의 비율, 해당 열에 중복되는 데이터가 많다면 선택도가 높다.
  • cardinality: 하나의 데이터 유형으로 정의되는 데이터 행의 개수, 전체 데이터 건수에 해당 열 * 해당열의 선택도

캐릭터셋 vs 콜레이션

캐릭터셋(character set)

데이터 저장을 어떻게 할 것인가?

  • 영문 + 숫자
  • 중국어
  • 다국어 ..
  • ex) utf8(다국어), utf8mb4(다국어 + 이모지)

콜레이션(collation)

데이터 정렬을 어떻게 할 것인가?

  • a와 A간의 대소관계 정의
  • a와 b간의 대소관계 정의
  • ex) utf8_general_ci, utf8_bin(다국어 + 이모지)

악성 SQL 튜닝

동등 조건으로 인덱스를 사용하는 나쁜 SQL문

튜닝전

SELECT *
FROM 사원출입기록
WHERE 출입문 = 'B'
  • 출입문 인덱스를 사용하여 데이터에 접근
  • 명확한 상수화 조건으로 데이터 접근 범위를 줄였으므로 ref 가 const 출력
  • 출입문 B는 66만 건의 전체 데이터 중 33만 건을 차지, 전체 데이터의 약 50%
  • 인덱스에 접근한 뒤 랜덤 엑세스 하므로 인덱스를 사용하는게 과연 효율적일지

튜닝후

SELECT *
FROM 사원출입기록 IGNORE INDEX (출입문)
WHERE 출입문 = 'B'
  • 테이블 풀 스캔 방식 (type: ALL)으로 수행
  • 66만건의 전체 데이터를 읽어와서 출입문이 B인 데이터만 필터링
  • 랜덤 엑세스가 발생하지 않고 한 번에 다수에 페이지에 접근

범위 조건으로 인덱스를 사용하는 나쁜 SQL문

튜닝전

SELECT 이름, 성
FROM 사원
WHERE 입사일자 BETWEEN STR_TO_DATE('2023-01-01', '%Y-%m-%d') 
              AND STR_TO_DATE('2023-12-31', '%Y-%m-%d')
  • 입사일자 index로 인덱스 범위 스캔 수행
  • extra에 Using index condition 출력
  • Using MRR (Multi Range Read)로 인덱스 범위 스캔을 수행
  • 사원 테이블의 데이터를 총 300,024건 조회 결과는 48,875(16.3%)건
  • 인덱스를 사용하는게 효율적일지 확인필요

튜닝후

SELECT 이름, 성
FROM 사원
WHERE YEAR(입사일자) BETWEEN '1994' AND '2000'
  • 튜닝 후 1초 감소
  • 테이블 풀스캔(type: ALL)으로 수행
  • 인덱스 없이 테이블에 직접 접근하여 한 번에 다수에 페이지에 접근

테이블 조인 설정 변경으로 착한 쿼리 만들기

  • MySQL과 MaridaDB에서 두 개 테이블의 데이터를 결합하는 조인 알고리즘은 대부분 중첩 루프 조인
  • 성능 최적화를 위해 다른 조인 알고리즘을 제공하긴 하지만 상당수의 조인 방식은 여전히 중첩 루프 조인에 기반을 둠

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

튜닝전

SELECT 매핑.사원번호,
       부서.부서번호
FROM 부서사원_매핑 매핑,
     부서
WHERE 매핑.부서번호 = 부서.부서번호
  AND 매핑.시작일자 >= '2023-01-01'
  • 드라이빙 테이블은 부서, 드리븐 테이블은 부사사원 매핑 테이블 중첩 루프 조인 수행
  • 작은 크기의 부서 테이블에서 부서명 인덱스를 활용해 인덱스 풀스캔
  • 매핑 테이블에서 인덱스 스캔을 하고 랜덤 엑세스로 테이블에 접근, 드리븐 테이블에서 대량 데이터에 대해 랜덤 엑세스하면 비효율적
  • 부서 사원 매핑 테이블은 30만 건 이상에 데이터가 있으나, MySQL 엔진으로 가져온 모든 데이터에 대해 필터조건 수행

튜닝후

SELECT STRAIGHT_JOIN -- 드라이빙 테이블을 명시적으로 지정
        매핑.사원번호,
        부서.부서번호
  FROM 부서사원_매핑 매핑, 
       부서
WHERE 매핑.부서번호 = 부서.부서번호
  AND 매핑.시작일자 >= '2023-01-01'
  • 부서사원_매핑 테이블을 테이블 풀 스캔(type: ALL)으로 수행
  • 반복하여 드리븐 테이블인 부서 테이블에 접근

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

튜닝전

SELECT 사원.사원번호,
       사원.이름,
       사원.성,
  FROM 사원
WHERE 사원.사원번호 > 450000
  AND ( SELECT MAX(연봉)
        FROM 급여
        WHERE 사원번호 = 사원.사원번호
      ) > 100000
  • 메인테이블 먼저 접근하여 기본기를 활용해서 범위 스캔(type: range) 수행
  • 다음으로 급여 테이블에 접근하는데 외부의 사원 테이블로부터 조건절을 받아 수행하는 의존성을 가진 서브쿼리(select_type: dependent subquery)
  • 사원번호가 450000보다 큰 데이터는 전체 데이터 건수의 약 15%수준
  • 보통 실행 계획에 select_type: dependent subquery가 있다면 외부 테이블에서 조건절을 받을 뒤 처리되어야 하므로 튜닝 성능 대상으로 고려할 수 있음

튜닝후

SELECT 사원.사원번호,
       사원.이름,
       사원.성,
  FROM 사원, 
       급여
WHERE 사원.사원번호 > 450000
  AND 사원.사원번호 = 급여.사원번호
GROUP BY 사원.사원번호
HAVING MAX(급여.연봉) > 100000
  • GROUP BY 절에서는 사원번호별 그루핑을 수행하고, HAVING 연봉의 최댓값으로 필터링
  • 급여 테이블에 접근하기 전 범위 축소 조건으로 WHERE 사원.사원번호 > 450000을 사용, 옵티마이저에 의해 급여.사원번호 > 450000 으로 rewrite 된 부분으로 수행

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

튜닝전

SELECT COUNT(DISTINCT 사원.사원번호) as 데이터건수
FROM 사원, 
     ( SELECT 사원번호
       FROM 사원출입기록 기록
        WHERE 출입문 = 'A'
     ) AS 기록
WHERE 사원.사원번호 = 기록.사원번호
  • 드라이빙 테이블인 사원출입기록은 출입문 인덱스를 활용하여 A 출입문에 관한 기록이 있는 사원번호를 구함
  • 드리븐 테이블인 사원 테이블은 기본 키를 사용해서 조인 조건절인 사원번호 열로 데이터를 비교 (type: eq_ref)
  • 인라인 뷰는 옵티마이저에 의해 조인 방식이 뷰 병합으로 최적화되어 다음 SQL 문처럼 수행
SELECT COUNT(DISTINCT 기록.사원번호) as 데이터건수
  FROM 사원,
        사원출입기록 기록
WHERE 사원.사원번호 = 기록.사원번호
  AND 기록.출입문 = 'A'
  • 66만여 건에 달하는 사원출입기록 테이블의 데이터 결과가 최종 결과에 어떻게 활용되는지 확인필요

튜닝후

SELECT COUNT(DISTINCT 사원.사원번호) as 데이터건수
  FROM 사원
 WHERE EXISTS (SELECT 1
                FROM 사원출입기록 기록
               WHERE 출입문 = 'A'
                 AND 기록.사원번호 = 사원.사원번호)
  • 사원 테이블은 드라이빙 테이블, subquery2가 드리븐 테이블로 수행
  • 사원출력기록 테이블은 EXISTS 연산자로 데이터 존재 여부를 파악하기 위해 임시테이블을 생성하는 MATERIALIZED 로 표기

SQL 문 재작성으로 착한 쿼리 만들기

처음부터 모든 데이터를 가져오는 나쁜 SQL문

튜닝전

SELECT 사원.사원번호,
       급여.평균연봉,
       급여.최고연봉,
       급여.최저연봉
  FROM 사원,
       ( SELECT 사원번호,
                ROUND(AVG(연봉), 0) AS 평균연봉,
                ROUND(MAX(연봉), 0) AS 최고연봉,
                ROUND(MIN(연봉), 0) AS 최저연봉
         FROM 급여
         GROUP BY 사원번호
       ) AS 급여
 WHERE 사원.사원번호 = 급여.사원번호
   AND 사원.사원번호 BETWEEN 10001 AND 10100

  • 크게 3개 행의 실행 계획으로 이루어지며, id가 1인 두 개의 행에 먼저 접근

  • 중첩 루프 조인을 하는 두 개 테이블은 사원 테이블과 <derived2> 테이블로, 먼저 출력된 사원 테이블이 브라이빙 테이블, <derived2> 테이블이 드리븐 테이블

  • <derived2> 테이블은 인라인뷰(drived)로 급여 테이블로 수행한 그루핑 결과를 새로 생성한 임시 테이블의 메모리나 디스크에 올려놓음

  • 이후 WHERE 절의 사원.사원번호 = 급여.사원번호 구문으로 데이터를 추출하고 조인 수행

  • type 항목의 index는 인덱스 풀 스캔을 수행하는 방식으로, FROM 절의 급여 테이블을 그루핑하면서 수행

  • 조건절 없이 그루핑을 수행하므로 지나치게 많은 데이터에 접근하지는 않는지 확인 필요

  • 사원 테이블 전체 데이터는 30만 건 수준인 데 비해 BETWEEN 구문으로 추출하는 데이터는 10건 뿐

튜닝후

SELECT 사원.사원번호,
        (SELECT ROUND(AVG(연봉), 0)
         FROM 급여
         WHERE 사원번호 = 사원.사원번호
        ) AS 평균연봉,
        (SELECT ROUND(MAX(연봉), 0)
          FROM 급여
          WHERE 사원번호 = 사원.사원번호
        ) AS 최고연봉,
        (SELECT ROUND(MIN(연봉), 0)
          FROM 급여
          WHERE 사원번호 = 사원.사원번호
        ) AS 최저연봉
  FROM 사원
 WHERE 사원.사원번호 BETWEEN 10001 AND 10100
  • 사원 테이블에서 사원번호가 10001과 10100 사이인 데이터(100건)만 추출
  • 사원 테이블의 사원 번호를 SELECT 절의 3개 스칼라 서브쿼리에서 매번 받으므로 select_type 항목은 dependent subquery로 표기
  • 이는 호출을 반복해 일으키므로 지나치게 자주 반복 호출될 경우에는 지양해야함
  • 하지만 100건의 데이터만 추출하는 경우는 비효율적인 부분은 거의 없음

비효율적인 페이징을 수행하는 나쁜 SQL문

튜닝전

SELECT 사원.사원번호, 사원.이름, 사원.성, 사원.입사일자
  FROM 사원,
       급여

 WHERE 사원.사원번호 = 급여.사원번호 
   AND 사원.사원번호 BETWEEN 10001 AND 50000
 GROUP BY 사원.사원번호
 ORDER BY SUM(급여.연봉) DESC
 LIMIT 150, 10
  • 드라이빙 테이블인 사원테이블과 드리븐 테이블인 급여 테이블을 중첩 루프 조인으로 수행
  • 사원 테이블과 급여 테이블은 각각 기본키로 데이터에 접근
  • 이때 드라입이 테이블인 사원 테이블은 그루핑 정렬 연산을 위해 임시 테이블(Extra: Using temporary)을 생성한 후 정렬 작업(extra: Using filesort)을 수행
  • LIMIT 연산으로 10건의 데이터를 가져오기 위해 수십만 건의 데이터 대상으로 조인을 수행한 뒤 그루핑과 정렬 작업 수행

튜닝후

SELECT 사원.사원번호, 사원.이름, 사원.성, 사원.입사일자
  FROM ( SELECT 사원번호
          FROM 급여
         GROUP BY 사원번호
         ORDER BY SUM(연봉) DESC
         LIMIT 150, 10
       ) AS 급여,
       사원
  WHERE 사원.사원번호 = 급여.사원번호
  • 급여 테이블에서 그루핑과 정렬 작업을 FROM 절의 인라인 뷰로 작성
  • 인라인 뷰에 필요한 데이터 건수만큼 LIMIT 절로 제약을 설정하여 사원 테이블과 조인할 수 있는 데이터 건수를 줄임