2025-09-24

오늘 배운 것

9.2 기본 데이터 처리

9.2.4 GROUP BY 처리

• GROUP BY도 ORDER BY와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리
• GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요 없음
• 하지만 GROUP BY에서도 인덱스를 사용할 수도 안할 수도 있음
  • 할 때
    • 인덱스 스캔 : 인덱스를 차례대로 읽음
    • 루스 인덱스 스캔 : 인덱스를 건너뛰면서 읽음
  • 안할 때
    • 임시 테이블 사용

9.2.4.1 인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)

• 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑 할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인 처리함
• 하지만 그룹 함수 등의 그룹값을 처리해야 할 때는 임시 테이블이 필요할 수도 있음

9.2.4.2 루스 인덱스 스캔을 이용하는 GROUP BY

• 예시
  • (emp_no, from_date)로 인덱스 설정, emp_no로 GROUP BY from_date로 WHERE 조건, emp_no SELECT
    • emp_no의 유일한 값(그룹키) 찾고
    • 해당 값인 것 중 from_date가 같은 레코드만 가져옴
    • 그다음 emp_no의 유일한 값(그룹키) 찾고 더이상 그룹키 없을 때까지 해당 과정 반복
• 특징
  • 단일 테이블에 대해 수행되는 GROUP BY 처리에서만 사용 가능
  • 인덱스의 유니크한 값의 수가 적을 수록 성능이 향상됨
  • 루스 인덱스 스캔으로만 처리되는 쿼리에서는 별도의 임시 테이블 필요하지 않음
• 사용 불가한 경우
  • 프리픽스 인덱스(칼럼 값의 앞쪽 일부만으로 생성된 인덱스)에서는 사용 불가
  • MIN(), MAX() 이외의 집합 함수 사용했을 때
  • GROUP BY에 사용된 칼럼이 인덱스 구성 칼럼의 왼쪽 부터 일치하지 않을 때
  • SELECT 절의 칼럼이 GROUP BY와 일치하지 않을 때

9.2.4.3 임시테이블을 사용하는 GROUP BY

• 인덱스를 전혀 사용하지 못할 때 사용
• MySQL 8.0 부터는 GROUP BY가 필요한 경우 내부적으로 GROUP BY 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복제거와 집합 함수 연산을 수행
  • 묵시적 정렬을 더이상 하지 않고 오직 ORDER BY가 명시되었을 때만 정렬함
    • 이전 버전에서는 묵시적 정렬 수행해서 정렬이 필요없다면 ORDER BY null 해서 성능을 높이곤 했음

9.2.5 DISTINCT 처리

• 특정 칼럼의 유니크한 값만 조회 할 때 SELECT 쿼리에 DISTINCT를 사용

9.2.5.1 SELECT DISTINCT …

• 단순히 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 할 때 GROUP BY 와 동일한 방식으로 처리
• 단순 SELECT DISTINCT 주의점
  • SELECT 하는 레코드를 유니크하게 SELECT 하는 것이지, 특정 칼럼만 유니크하게 조회하는 것이 아님
    • SELECT DISTINCT(first_name), last_name FROM employee;
      • 이렇게 해도 distinct의 괄호를 의미 없는 것이라 생각하고 first와 last의 조합이 유니크 한것들을 조회하게 됌

9.2.5.2 집합 함수와 함께 사용된 DISTINCT

• 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수 인자로 전달된 칼럼값이 유니크한 것들을 가져옴
• 인덱스를 이용할 수 없을 때는 DISTINCT도 임시 테이블 사용
• 인덱스 이용가능할 때는 인덱스 풀스캔 또는 레인지 스캔 함

9.2.6 내부 임시 테이블 활용

• MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블을 사용함
• MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨짐
• 특정 예외 케이스에는 메모리를 거치지 않고 바로 디스크에 임시 테이블이 만들어지기도 함
• 내부적인 가공을 위해 생성하는 임시테이블은 다른 세션이나 다른 쿼리에서는 볼수 없으며 사용하는 것도 불가능
• 쿼리의 처리가 완료되면 자동으로 삭제됨

9.2.6.1 메모리 임시 테이블과 디스크 임시 테이블

• MySQL 8.0 버전 부터는 메모리는 TempTable이라는 스토리지 엔진을 사용하고, 디스크에 저장되는 임시테이블을 InnoDB 스토리지 엔진을 사용
• TempTable 스토리지 엔진은 가변길이 타입을 지원
• InnoDB 스토리지 엔진은 트랜잭션을 지원함
• temptable_max_ram(default → 1GB) 보다 임시테이블의 크기가 커지면 메모리의 임시테이블을 디스크에 기록
  • MMAP 파일로 디스크에 기록
    • default
      • InnoDB 테이블보다 오버헤드가 적음
  • InnoDB 테이블로 기록

9.2.6.2 임시 테이블이 필요한 쿼리

• 인덱스를 사용하지 못할 때
• ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
• ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
• DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
• UNION이나 UNION DISTINCT가 사용된 쿼리
• 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

9.2.6.3 임시 테이블이 디스크에 생성되는 경우

• InnoDB 테이블이 디폴트 값
• 경우
  • UNION이나 UNION ALL에서 SELECT 되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
  • GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
  • 메모리 임시테이블의 크기가 temptable_max_ram 시스템 변수 값보다 큰 경우

9.3 고급 최적화

• MySQL 서버의 옵티마이저가 실행 계획을 수립할 때 통계정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립
• 구분
  • 조인관련된 옵티마이져 옵션
  • 옵티마이저 스위치
    • 고급 최적화 기능등을 활성화 할지 제어하는 용도

9.3.1 옵티마이저 스위치 옵션

9.3.1.1 MRR과 배치 키 액세스(mrr & batched_key_access)

• 네스티드 루프 조인
  • 드라이빙 테이블의 레코드를 한 건 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서 조인을 수행
  • 조인 처리 →MySQL 엔진
  • 레코드 찾고 읽기 → 스토리지 엔진
  • 네스티드 루프 조인으로 레코드를 검색하면 스토리지 엔진에서 최적화 할 수가 없음
• MRR(Multi-Range Read)
  • 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링
  • 조인 버퍼에 레코드가 가득차면 MySQL 엔진이 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청
  • 스토리지 엔진이 읽어야 할 레코드들을 데잍터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화 할 수 있음
• BKA(Batched Key Access) : MRR을 응용해서 실행되는 조인 방식
  • 기본값이 비활성화
    • 부가적인 정렬 작업이 필요해지면서 오히려 성능이 안좋아지는 경우가 있기 때문

9.3.1.2 블록 네스티드 루프 조인(block_nested_loop)

• 네스티드 루프 조인은 조인의 연결 조건이되는 칼럼에 모두 인덱스가 있는 경우 사용
• 블록 네스티드 루프 조인과의 차이
  • 조인 버퍼의 사용 여부
  • 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐
• 어떤 방식으로도 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없을 때
  • 옵티마이저가 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 조인하는 형태로 처리
• 블록 네스티드 루프 조인
  • 예시
    • 두 테이블을 연결하는 조인 조건이 없을 때 → 카테시안 조인 수행
      • 드라이빙 테이블을 인덱스를 활용해서 레코드 검색
      • 해당 레코드들의 나머지 칼럼을 다 읽어서 조인 버퍼에 저장
      • 드리븐 테이블의 인덱스를 활용해 레코드 검색
      • 드리븐 테이블에서 검색된 레코드들에 버퍼에 저장되어 있는 값들을 결합해서 반환
  • 드라이빙 테이블의 결과는 조인 버퍼에 담아두고, 드리븐 테이블을 먼저 읽고 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리
  • 조인 수행 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만, 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 흐트러질 수 있음
• 8.0.18 버전 부터는 블록 네스티드 루프 조인이 더이상 사용되지 않고 해시 조인 알고리즘 사용

9.3.1.3 인덱스 컨디션 푸시 다운

• 조건 칼럼이 인덱스 범위 제한 조건으로 사용되지 못하면 스토리지 엔진에 전달을 안했었음
• 따라서 innoDB로 범위 제한 조건으로 먼저 검색하고 그후에 그만큼 MySQL 엔진이 비교 조건을 수행
• 이를 해결하기 위해 MySQL 5.6부터는 인덱스를 범위 제한 조건으로 사용하지 못하더라도 인덱스에 포함된 칼럼의 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달함

9.3.1.4 인덱스 확장

• InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정
• 세컨더리 인덱스 리프 노드에는 pk가 있으므로 (세컨더리, pk1, pk2) 이렇게 새로운 인덱스 처럼 활용할 수 있는 것

9.3.1.5 인덱스 머지

• 인덱스를 이용해 쿼리를 실행할 때, 대부분 옵티마이저는 테이블 별로 하나의 인덱스만 사용하도록 실행 계획 수립
• 하지만 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리
• 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 것으로 예상 될 대 MySQL 서버는 인덱스 머지 실행 계획을 선택
• 종류
  • index_merge_intersection
  • index_merge_sort_union
  • index_merge_union
    • 공통점 : 여러개의 인덱스를 통해 결과를 가져옴
    • 차이점 : 결과를 어떤 방식으로 병합하는지가 다름

9.3.1.6 인덱스 머지 - 교집합(index_merge_intersection)

• 각각의 인덱스로 조건에 일치하는 레코드 건수를 예측 했을 때, 두 조건 모두 상대적으로 많은 레코드를 가져와야 한다면 교집합으로 옵티마이저는 실행계획을 세움

9.3.1.7 인덱스 머지 - 합집합(index_merge_union)

• WHERE 절에 사용된 2개 이상의 조건이 각가의 인덱스를 사용하되 OR 연산자로 연결된 경우 사용
• 정렬을 사용하지 않고 중복을 제거하는 방법
  • 각각의 인덱스로 조회한 값이 각각 PK로 정렬되어 있기 때문에 우선순위 큐 알고리즘을 활용하여 정렬없이 중복을 제거
• 추가 개념
  • 2개의 조건이 AND 연결
    • 하나라도 인덱스 사용할 수 있으면 인덱스 레인지 스캔
  • 2개의 조건이 OR 연결
    • 둘 중 하나라도 인덱스 사용 불가면 항상 풀 테이블 스캔

9.3.1.8 인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)

• 인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우 인덱스 머지 최적화의 Sort union 알고리즘을 사용