Back-End/DB

Real MySQL 8.0 [데이터 처리 방식 및 정렬]

Meluu_ 2025. 6. 27. 18:50

쿼리 실행 절차

1. 사용자로부터 요청된 SQL 문장을 잘 게쪼개서 MYSQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다. 

2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을 지 선택 

3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴

 

1단계가 SQL 파서 모듈이 처리하는 SQL 파싱

SQL 문법적 체크

 

옵티마이저는 비용 기반 최적화와 규칙 기반 최적화가 있으며

현재는 비용 기반 최적화를 사용

 

기본 데이터 처리

풀 테이블 스캔과 풀인덱스 스캔

 

풀 스캔은 특정 테이블의 연속된 데이터 페이지 읽으면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작

리드 어헤드 : 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청 전 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 둠

 

MySQL 서버에서는 innodb_read_ahead_threshold 시스템 변수로 리드 어헤드 시작 임계값 설정 가능

포그라운드 스레드에 의해서 위 시스템 변수만큼 연속된 데이터 페이지가 읽히면 InnoDB 스토리지 엔진은

백그라운드 스레드를 이용해 대량으로 그 다음 페이지들을 읽어서 버퍼풀에 적재

 

 

병렬처리

단순히 테이블 전체 건수 조회 쿼리만 병렬처리 가능 (빠름)

SET SESSION innodb_parallel_read_threads=n; // n개 병렬 처리
SELECT COUNT(*) FROM member;

 

정렬 처리

 

MySQL은 정렬을 위해 메모리의 별도의 공간 소트 버퍼를 할당받음 

이 공간을 넘어서는 레코드건수가 할당되면

분할 해서 정렬후 디스크에 임시 저장하고 멀티머지 수행 (디스크 접근이 많아짐)

 

소트버퍼의 크기가 256KB~8MB 일때 최적의 성능을 보임

좋은 방법은 기존 소트버퍼를 넘어가면 해당 세션의 소트 버퍼만 임시로 늘리고 사용 후 다시 줄이는 것

 

책 작가는 일반적인 트랜잭션 처리용은 56KB~1MB 미만이 적절해보인다는 의견이 있다.

 

정렬 알고리즘

  • 투 패스
    • <sort_key, rowid> : 정렬 키와 레코드 로우 아이디만 가져와 정렬 (PK)
    • 정렬에 필요한 칼럼만 조회해서 정렬 수행후 이를 토대로 나머지 칼럼들을 조회
  • 싱글 패스
    • <sort_key, additional_fields> : 정렬 키와 레코드 전체를 가져와 정렬, 레코드 칼럼들은 고정 사이즈로 메모리 저장
    • <sort_key, packed_additional_fields> : 정렬 키와 레코드 전체를 가져와 정렬, 레코드 칼럼들은 가변 사이즈로 메모리 정렬

보통 투패스는 2번 읽어야해서 불리하여 기본적으로 싱글 패스를 사용하고 

다음의 경우 투 패스 정렬 방식을 사용

  • 레코드 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
  • BLOB, TEXT 타입의 칼럼이 SELECT 대상에 포함될 때

 

SELECT 쿼리에 꼭  필요한 칼럼만 조회해야하는 이유이다. 

정렬 버퍼를 몇 ~ 몇십 배까지 비효율적으로 사용할 가능성이 크다. (싱글 패스)


모든 정렬을 인덱스를 이용하도록 튜닝은 불가능

  • 정렬 기준이 너무 많아서 요건별로 모두 인덱스 생성 불가능한 경우
  • GROUP BY 결과 or DISTINCT 같은 처리의 결과를 정렬해야하는경우
  • UNION 결과와 같이 임시 테이블 결과를 다시 정렬해야하는 경우
  • 랜덤하게 결과 레코드를 가져와야하는 경우

 

 

 

ORDER BY

  • 인덱스 이용
    • 정렬 조건
      • ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하여야 함
      • ORDER BY 순서대로 생성된 인덱스 존재 해야함
      • WHERE 절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건과 ORDER BY는 같은 인덱스를 사용할 수  있어야함
      • B-Tree를 제외한 R-Tree, 전문 검색, 해시 인덱스는 불가
      • 여러 테이블 조인시 네스티드 루프 방식 조인에서만 가능
  • Filesort 이용
    • 조인 드라이빙 테이블만 정렬
      • 조인 시작전 첫 번째 테이블(드라이빙) 레코드를 먼저 정렬 후 다음 조인 실행 (소트 버퍼에 저장)
      • 첫번 째로 읽히는 드라이빙 테이블의 칼럼만으로 ORDER BY절을 작성
    • 임시 테이블을 이용한 정렬
      • 조인의 드라이빙 테이블만 정렬 패턴을 제외한 나머지 패턴에서는 2개 이상의 테이블 조인 후 정렬시 임시테이블 전략을 사용한다.
      • 예로 ORDER BY에 드라이빙 칼럼이 아닌 드리븐 테이블로 작성하여 정렬할 경우 적용
      • 먼저 조인한 후 임시테이블에 저장하고 임시테이블을 정렬한 후 결과를 반환한다.

 

인덱스 정렬시 ORDER BY 를 꼭 명시하자. 

인덱스 특성상 정렬된 채로 조회하지만 ORDER BY를 넣는다고 작업량이 늘지 않고

예외상황에 안전하게 정렬할 수 있다.

 

 

주로 웹서비스용 쿼리에서는 ORDER BY와 함께 LIMIT이 거의 필수로 사용되는 경향이 있음

일반적으로 LIMIT은 테이블이나 처리 결과의 일부만 가져오기에 MySQL 서버가 처리해야할 작업량을 줄이는 역할을 함

 

그런데 ORDER BY나 GROUP BY같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수 만큼만 가져와서 처리가 불가능

조건을 만족하는 레코드를 모두 가져와서 정렬 수행, 그루핑 작업을 실행해야만 비로소 LIMIT으로 건수를 제한 가능

WHERE 조건을 인덱스 튜닝해도 잘못된 ORDER BY나 GROUP BY때문에 쿼리가 느려지는 경우가 자주 발생

 

쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 왜 느리게 작동할 수 밖에 없는지 알아보자

 

쿼리 처리방법 : 스트리밍 처리, 버퍼링 처리

 

스트리밍 방식

서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트에게 전송

인덱스 정렬 방식에 사용

LIMIT 사용시 처리량이 줄어 빠름

 

버퍼링 방식

ORDER BY, GROUP BY 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 함.

WHERE 조건에 일치하는 모든 레코드를 가져온 후 정렬 혹은 그루핑해서 차례대로 전송해야 하기 때문

LIMIT 사용해도 별 의미 없음 

 

인덱스 정렬방식을 제외한 모두가 여기 해당

 

JDBC의 경우 MYSQL 서버로부터 받는 레코드를 내부 버퍼에 모아둔다. 전부 다 받으면 그때서야 클라이언트의 애플리케이션에 반환한다.

 

정렬 관련 상태 변수

FLUSH STATUS; // 여태 상태 변수 초기화
SHOW STATUS LIKE 'Sort&'; // 상태 변수 조회

 

Sort_merge_passes : 멀티 머지 처리 횟수

Sort_range : 인덱스 레인지 스캔 정렬 작업 횟수

Sort_scan : 풀 테이블 스캔 정렬 횟수

Sort_rows : 지금까지 정렬한 전체 레코드 건수

 

GROUP BY 처리

HAVING 절은 필터링 역할을 하지만 GROUP BY에 사용된 조건은 인덱스를 사용해서 처리가 불가

HAVING절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.

 

 

  • 인덱스 스캔 (타이트 인덱스 스캔 , 순차)
    • 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 이미 인덱스 존재시 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고  그결과로 조인을 처리
    • 그룹 함수는 임시테이블 
  • 루스 인덱스 스캔
    1. 인덱스를 스캔하면서 유일한 그룹 키 값을 찾음
    2. 찾은 키로 WHERE 절에 사용한 칼럼의 조건이 맞는 레코드만 가져옴
    3. 그 다음 유니크한 그룹 키 값을 가져옴
    4. 3번 단계에서 결과가 더 없으면 종료, 있다면 2번 과정으로 돌아가서 반복
  • 루스 인덱스 스캔 불가 쿼리 패턴
// MIN, MAX를 제외함 그룹 함수 사용
SELECT col1, SUM(col2) FROM tb_test GROUP BY col1; 

// GROUP BY 에 사용된 칼럼이 인덱스 구성 칼럼 왼쪽부터 불일치
SELECT col1, col2 FROM tb_test GROUP BY col2, col3; 

// SELECT 절 칼럼이 GROUP BY와 불일치
SELECT col1, col3 FROM tb_test GROUP BY col1, col2;
  • 임시테이블 사용
    • GROUP BY 의 기준 칼럼이 드라이빙 테이블에 있든 , 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할때 사용
      • SELECT e.last_name, GROUP BY e.last_name, 조인은 e.emp_no으로 했을 경우
    • 묵시적 ORDER BY는 없어짐, 따라서 정렬이 필요하면 ORDER BY를 넣어준다.

 

루스 인덱스 스캔의 경우 MySQL8.0 부터는 인덱스 스킵 스캔이 도입됨

 

 

DISTINCT 처리

SELECT DISTINCT col1, col2 이면

(col1, col2) 유니크하게 SELECT하는 것이다. 

 

집합 함수와 함께 사용시

SELECT COUNT(DISTINCT s.salary)

 

조회하는 모든 칼럼의 조합이 유니크한 것들만 가져옴

내부적으로 집합 함수 처리를 위해 임시테이블 사용 

 

집합 함수가 2개면 임시테이블이 2개가 된다.

 

집합 함수가 적용되는 컬럼이 GROUP BY 절에 포함되어 있고, 해당 컬럼에 인덱스가 존재한다면 MySQL은 인덱스를 사용하여 그룹핑을 효율적으로 수행할 수 있다.

 

여기서 말하는 집합 함수는 (MIN, MAX, COUNT)를 말하며

나머지는 임시 테이블을 사용한다.

 

 

 

내부 임시 테이블

MySQL 엔진이 스토리지 엔진으로 부터 받아온 레코드를 정렬 or 그루핑시 내부적인 임시 테이블 사용

내부 임시 테이블은 다른 세션이나 다른 쿼리에서 볼 수 없고 쿼리 처리 완료시 자동으로 삭제됨

사용자가 만든 CREATE TABLE과는 다르다.

 

 

 

종류

  • 메모리 임시 테이블
  • 디스크 임시 테이블

8.0부터 메모리 임시 테이블은 TempTalbe 스토리지엔진 사용

디스크 임시 테이블은 InnoDB(트랜잭션 지원 가능)스토리지 엔진을 사용

 

 

 

temptable_max_ram 시스템 변수의 기본값 1GB

임시 테이블 크기가 1GB를 넘어서면 2가지 디스크 저장 방식 중 하나를 선택

  • MMAP 파일로 디스크 기록
  • InoDB 테이블로 기록

오버헤드가 적은 MMAP 이 기본값

 

임시 테이블이 필요한 쿼리

ORDER BY 나 GROUP BY 명시된 칼럼이 다른 쿼리
ORDER BY 나 GROUP BY 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 OR DISTINCT가 인덱스 처리 불가한 쿼리
UNION OR UNION DISTINCT 가 사용된 쿼리 
쿼리의 실행 계획에서 SELECT_TYPE이 DERIVED 인 쿼리

 

1~4 유니크 인덱스가 내부 임시 테이블에 생성

5는 생성 안되서 처리 성능 떨어짐

 

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

쉽게 말해서 메모리 임시테이블의 용량을 넘어설 것으로 예상될 경우다.

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

 

// 임시 테이블 생성 상태 변수 
SHOW SESSION STATUS LIKE 'Created_tmp%';

Created_tmp_disk_tables : 전체 임시 테이블 생성 개수 누적 값
Created_tmp_tables : 디스크에 내부 임시 테이블 생성 개수 누적 값

 

저작자표시 비영리 변경금지 (새창열림)