[스터디] 16. 기본 데이터 처리

목차

9.2 기본 데이터 처리

9.2.4 GROUP BY 처리

• GROUP BY 또한 ORDER BY와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리 중 하나임.
• GROUP BY 절이 있는 쿼리에서는 HAVING 절을 사용할 수 있는데, HAVING 절은 GROUP BY 결과에 대해 필터링 역할을 수행함.
• GROUP BY 작업은 인덱스를 사용하는 경우와 그렇지 못한 경우로 나뉨.
  • 인덱스를 사용할 때
    • 인덱스를 차례대로 읽는 인덱스 스캔 방법
    • 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔 방법
  • 인덱스를 사용하지 못할 때
    • 임시 테이블 사용

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

• 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때, GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리함.
• GROUP BY가 인덱스를 사용해서 처리된다 하더라고 그룹 함수(Aggregation function) 등의 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있음.
• 그루핑 방식을 사용하는 쿼리의 실행 계획에서는 Extra 칼럼에 별도로 GROUP BY 관련 메시지("Using index for group-by")나 임시 테이블 사용 또는 정렬 관련 메시지("Using temporary, Using filesort")가 표시되지 않음.

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

• 루스(Loose) 인덱스 스캔 방식: 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것
• 옵티마이저가 루스 인덱스 스캔을 사용할 때는 실행 계획의 Extra 칼럼에 "Using index, for group-by" 메시지가 표시됨.

mysql> EXPLAIN SELECT emp_no
	FROM salaries
    WHERE from_date='1985-03-01'
	GROUP BY emp_no;

•  
• 예제 쿼리 실행 순서
  1. (emp_no, from_date) 인덱스를 차례대로 스캔하면서 emp_no의 첫 번째 유일한 값(그룹 키) "10001"을 찾아냄.
  2. (emp_no, from_date) 인덱스에서 emp_no가 '10001'인 것 중에서 from_date 값이 '1985-03-01'인 레코드만 가져옴. 이 검색 방법은 1번 단계에서 알아낸 '10001' 값과 쿼리의 WHERE 절에 사용된 "from_date='1985-03-01'" 조건을 합쳐서 "emp_no=10001 AND from_date='1985-03-01'" 조건으로 (emp_ no, from_date) 인덱스를 검색하는 것과 거의 흡사함.
  3. (emp_no, from_date) 인덱스에서 emp_no의 그다음 유니크한(그룹 키) 값을 가져옴.
  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;

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

• GROUP BY의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때 이 방식으로 처리됨.
• 예제 쿼리의 실행 계획에서는 Extra 칼럼에 “Using temporary” 메시지가 표시됨.
• 이 실행 계획에서 임시 테이블이 사용된 것은 employees 테이블을 풀 스캔(ALL)하기 때문이 아니라 인덱스를 전혀 사용할수 없는 GROUP BY이기 때문임.

mysql> EXPLAIN
	SELECT e.last_name, AVG(s.salary)
    FROM employees e, salaries s
	WHERE s.emp_no=e.emp_no
    GROUP BY e.last_name;

• MySQL 5.7 버전까지는 GROUP BY가 사용되면 자동으로 그루핑 칼럼을 기준으로 정렬이 수행됨.
• MySQL 8.0 버전에서는 GROUP BY가 필요한 경우, 내부적으로 GROUP BY 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행함. 즉, 별도의 정렬 작업 없이 GROUP BY가 처리됨.
• 하지만 MySQL 8.0 버전에서도 GROUP BY와 ORDER BY가 같이 사용되면 명시적으로 정렬 작업을 실행함.
• 동일한 쿼리에 ORDER BY 절을 추가하면 아래 예제의 Extra 칼럼에 "Using temporary"와 함께 "Using filesort"가 표시됨.

mysql> EXPLAIN
	SELECT e.last_name, AVG(s.salary)
    FROM employees e, salaries s
	WHERE s.emp_no=e.emp_no
    GROUP BY e.last_name
    ORDER BY e.last_name;

9.2.5 DISTINCT 처리

• 특정 칼럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용함.
• DISTINCT 키워드가 영향을 미치는 범위에 따라 집합 함수(MIN(), MAX(), COUNT())와 함께 사용되는 경우와 집합 함수가 없는 경우의 2가지로 구분함.
• DISTINCT가 사용되는 쿼리의 실행 계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 때는 임시 테이블이 필요함. 하지만 실행 계획의 Extra 칼럼에는 "Using temporary" 메시지가 출력되지 않음.

9.2.5.1 SELECT DISTINCT ...

• SELECT되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용함.
• GROUP BY와 동일한 방식으로 처리됨.
• DISTINCT는 SELECT하는 레코드(튜플)을 유니크하게 SELECT하는 것이며, 특정 칼럼만 유니크하게 조회하는 것은 아님.

--- 두 쿼리는 내부적으로 같은 작업을 수행함.
mysql> SELECT DISTINCT emp_no FROM salaries;
mysql> SELECT emp_no FROM salaries GROUP BY emp_no;

--- SELECT 결과는 first_name만 유니크한 것을 가져오는 것이 아니라 (first_name, last_name) 조합 전체가 유니크한 레코드를 가져오는 것임.
mysql> SELECT DISTINCT first_name, last_name FROM employees;

--- MySQL 서버는 DISTINCT 뒤의 괄호를 그냥 의미 없이 사용된 괄호로 해석하고 제거함.
mysql> SELECT DISTINCT(first_name), last_name FROM employees; --- (X)
mysql> SELECT DISTINCT first_name, last_name FROM employees; --- (O)

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

• COUNT(), MIN(), MAX()와 같은 집합 함수 내에서 DISTINCT 키워드가 사용될 수 있음.
• 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼값이 유니크한 것들을 가져옴.
• 예제 쿼리는 COUNT(DISTINCT s.salary)를 처리하기 위해 임시 테이블을 사용하지만, 쿼리의 실행 계획에서 임시 테이블을 사용한다는 메시지는 표시되지 않음. MySQL 서버의 모든 버전의 실행 계획에서 "Using temporary"를 표시하지 않고 있음.

mysql> EXPLAIN SELECT COUNT(DISTINCT s.salary)
	FROM employees e, salaries s
    WHERE e.emp_no=s.emp_no
    AND e.emp_no BETWEEN 100001 AND 100100;

9.2.6 내부 임시 테이블 활용

• MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블(Internal temporary table)을 사용함.
• 이 임시 테이블은 CREATE TEMPORARY TABLE 명령으로 만든 임시 테이블과는 다르게, 다른 세션이나 다른 쿼리에서는 볼 수 없고 사용하는 것도 불가능하며 쿼리의 처리가 완료되면 자동으로 삭제됨.

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

• MySQL 8.0 이전 버전까지 원본 테이블의 스토리지 엔진과 관계 없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 사용함.
  • MEMORY 스토리지 엔진은 VARBINARY나 VARCHAR 같은 가변 길이 타입을 지원하지 못하기 때문에 최대 길이만큼 메모리를 할당해서 사용함.
  • MyISAM 스토리지 엔진은 트랜잭션을 지원하지 못함.
• MySQL 8.0 버전부터 메모리는 TempTable 스토리지 엔진을 사용하고, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용하도록 개선됨.
  • TempTable 스토리지 엔진은 가변 길이 타입을 지원함.
  • InnoDB 스토리지 엔진(또는 TempTable 스토리지 엔진의 MMAP 파일 버전)은 트랜잭션 지원이 가능함.
• MySQL 8.0 버전부터 internal_tmp_mem_storage_engine 시스템 변수를 이용해 메모리용 임시 테이블을 MEMORY와 TempTable(기본값) 중에서 선택할 수 있음.
• TempTable이 최대한 사용 가능한 메모리 공간의 크기는 temptable_max_ram 시스템 변수로 제어할 수 있음(기본값은 1GB). 임시 테이블의 크기가 1GB 보다 커지면 MySQL 서버는 메모리의 임시 테이블을 기록하는데, MMAP 파일로 디스크에 기록하는 방식과 InnoDB 테이블로 기록하는 방식 중 하나를 선택함.
• MySQL 서버가 MMAP 파일로 기록할지 InnoDB 테이블로 전환할지는 temptable_use_mmap 시스템 변수로 설정할 수 있음(기본값은 ON).
• 디스크에 생성되는 임시 테이블은 tmpdir 시스템 변수에 정의된 디렉터리에 저장됨.
• 내부 임시 테이블이 메모리에 생성되지 않고 처음부터 디스크 테이블로  생성되는 경우에는 internal_tmp_disk_storage_engine 시스템 변수에 설정된 스토리지 엔진이 사용됨(기본값은 IoonDB).

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

• 아래 패턴의 쿼리는 MySQL 엔진에서 별도의 데이터 가공 작업을 필요로 하므로 대표적으로 내부 임시 테이블을 생성하는 케이스임.
  1. ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
  2. ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
  3. DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  4. UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)
  5. 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
• 어떤 쿼리의 실행 계획에서 임시 테이블이 사용되는지는 Extra 칼럼에 "Using temporary" 메시지가 표시되는지 확인하면 되는데, 3~5번 패턴처럼 메시지가 표시되지 않아도 사용할 수 있음.

--- 1. ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
SELECT department_id, COUNT(*) 
FROM employees
GROUP BY department_id
ORDER BY salary DESC; -- GROUP BY와 ORDER BY 칼럼이 다름

--- 2. ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
SELECT e.emp_no, e.first_name, s.salary
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
GROUP BY s.salary -- 조인의 두 번째 테이블 칼럼으로 그룹화
ORDER BY e.first_name; -- 조인의 첫 번째 테이블 칼럼으로 정렬

--- 3. DISTINCT와 ORDER BY가 동시에 쿼리에 존재하거나 DISTINCT가 인덱스로 처리되지 못하는 쿼리
SELECT DISTINCT department_id
FROM employees
ORDER BY salary DESC; -- DISTINCT와 ORDER BY가 존재

--- 4. UNION이나 UNION DISTINCT가 사용된 쿼리
SELECT emp_no, first_name FROM employees
UNION
SELECT emp_no, salary FROM salaries;

--- 5. 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
SELECT * 
FROM (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
) AS derived_table
WHERE employee_count > 10;

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

• 내부 임시 테이블은 기본적으로 메모리 상에 만들어지지만, 아래 조건을 만족하면 메모리 임시 테이블을 사용할 수 없기 때문에 디스크 기반의 임시 테이블을 사용함.
  1. UNION이나 UNION ALL에서 SELECT되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
  2. GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
  3. 메모리 임시 테이블의 크기가 (MEMORY 스토리지 엔진에서) tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나, (TempTable 스토리지 엔진에서) temptable_max_ram 시스템 변수 값보다 큰 경우

--- 1. UNION 또는 UNION ALL에서 512바이트 이상의 칼럼 사용
SELECT emp_no, REPEAT('A', 600) AS large_column -- 600바이트 크기의 문자열
FROM employees
UNION ALL
SELECT emp_no, REPEAT('B', 600) AS large_column
FROM salaries;

--- 2. GROUP BY 또는 DISTINCT 칼럼이 512바이트 이상
SELECT department_id, REPEAT('C', 600) AS large_column
FROM employees
GROUP BY department_id, large_column; -- 600바이트 크기의 그룹화 칼럼

--- 3. 메모리 임시 테이블의 크기가 시스템 변수보다 클 때

-- 시스템 변수 설정 예 (MySQL 기본값 기준)
-- tmp_table_size = 16MB, max_heap_table_size = 16MB

-- 쿼리 예제
SELECT emp_no, REPEAT('D', 1024 * 1024) AS large_column -- 1MB 크기의 문자열
FROM employees
WHERE emp_no BETWEEN 10001 AND 20000; -- 대량의 데이터를 가져와 임시 테이블 생성

9.2.6.4 임시 테이블 관련 상태 변수

• 임시 테이블이 디스크에 생성됐는지 메모리에 생성됐는지 확인하려면 MySQL 서버의 상태 변수를 확인함.

--- 현재 세션의 상태 값 초기화
mysql> FLUSH STATUS;

mysql> SELECT first_name, last_name
	FROM employees
    GROUP BY first_name, last_name;

--- MySQL 서버의 상태 변수
mysql> SHOW SESSION STATUS LIKE 'Created_tmp%';

• 예제를 실행 결과의 상태 변수 값의 의미
  • Created_tmp_tables: 쿼리의 처리를 위해 만들어진 내부 임시 테이블의 개수를 누적하는 상태 값. 내부 임시 테이블이 메모리에 만들어졌는지 디스크에 만들어졌는지를 구분하지 않고 모두 누적함.
  • Created_tmp_disk_tables: 디스크에 내부 임시 테이블이 만들어진 개수만 누적해서 가지고 있는 상태 값

9.3 고급 최적화

---

• 옵티마이저 옵션은 조인 관련된 옵티마이저 옵션과 옵티마이저 스위치로 구분함.
  • 조인 관련된 옵티마이저 옵션은 MySQL 서버 초기 버전부터 제공되던 옵션이지만 많은 사람들이 신경쓰지 않는 편임.
  • 옵티마이저 스위치는 MySQL 5.5 버전부터 지원되기 시작했는데, MySQL 서버의 고급 최적화 기능들의 활성화 여부를 제어하는 용도로 사용됨.

9.3.1 옵티마이저 스위치 옵션

• 옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어함.
• optimizer_switch 시스템 변수에는 여러 개의 옵션을 세트로 묶어서 설정하는 방식으로 사용함.
• optimizer_switch 시스템 변수에 설정할 수 있는 최적화 옵션은 아래 표와 같음.

옵티마이저 스위치 이름	기본값	설명
batched_key_access	off	BKA 조인 알고리즘을 사용할지 여부 설정
block_nested_loop	on	Block Nested Loop 조인 알고리즘을 사용할지 여부 설정
engine_condition_pushdown	on	Engine Condition Pushdown 기능을 사용할지 여부 설정
index_condition_pushdown	on	Index Condition Pushdown 기능을 사용할지 여부 설정
use_index_extensions	on	Index Extension 최적화를 사용할지 여부 설정
index_merge	on	Index Merge 최적화를 사용할지 여부 설정
index_merge_intersection	on	Index Merge Intersection 최적화를 사용할지 여부 설정
index_merge_sort_union	on	Index Merge Sort Union 최적화를 사용할지 여부 설정
index_merge_union	on	Index Merge Union 최적화를 사용할지 여부 설정
mrr	on	MRR 최적화를 사용할지 여부 설정
mrr_cost_based	on	비용 기반의 MRR 최적화를 사용할지 여부 설정
semijoin	on	세미 조인 최적화를 사용할지 여부 설정
firstmatch	on	FirstMatch 세미 조인 최적화를 사용할지 여부 설정
loosescan	on	LooseScan 세미 조인 최적화를 사용할지 여부 설정
materialization	on	Materialization 최적화를 사용할지 여부 설정 (Materialization 세미 조인 최적화 포함)
subquery_materialization_cost_based	on	비용 기반의 Materialization 최적화를 사용할지 여부 설정

• 옵티마이저 스위치 옵션은 default와 on, off 중에서 하나를 설정할 수 있는데, on으로 설정되면 해당 옵션을 활성화하고, off를 설정하면 해당 옵션을 비활성화하고, default를 설정하면 기본값이 적용됨.
• 옵티마이저 스위치 옵션은 글로벌과 세션별 모두 설정할 수 있는 시스템 변수이므로 MySQL 서버 전체적으로 또는 현재 커넥션에 대해서만 예제와 같이 설정할 수 있음.

--- // MySQL 서버 전체적으로 옵티마이저 스위치 설정
mysql> SET GLOBAL optimizer_switch='index_merge=on,index_merge_union=on,...';

--- // 현재 커넥션의 옵티마이저 스위치만 설정
mysql> SET SESSION optimizer_switch='index_merge=on,index_merge_union=on,...';

• 아래 예제와 같이 SET_VAR 옵티마이저 힌트를 이용해 현재 쿼리에만 설정할 수도 있음.

mysql> SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */
	...
    FROM ...

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.