책너두 (Real MySQL 8.0 1권) 29일차 (~317p)

요약

• GROUP BY 의 동작방식을 이해하게 됨.
  • 인덱스 스캔을 이용
  • 루스 인덱스 스캔 이용
  • 임시 테이블 이용
• DISTINCT 의 동작방식을 이해하게 됨.
  • SELECT DISTINCT … 이용
  • 집합함수와 함께 이용
• MySQL 엔진에서 내부 임시 테이블을 사용하는 방법을 이해하게 됨.
  • 쿼리 처리 완료시 삭제됨.
  • 메모리 임시 테이블과 디스크 임시 테이블로 나눠짐.
  • 임시 테이블이 필요한 쿼리 패턴을 알게 됨.
  • 임시 테이블이 디스크에 생성되는 조건을 알게 됨.
  • 임시 테이블이 사용됬는지 상태변수를 통해 확인할 수 있음.

메모

GROUP BY 처리

• GROUP BY 또한 ORDER BY 와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리 중 하나임.
• HAVING 절은 GROUPBY 결과에 대해 필터링 역할을 수행함.
  • GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없음.
  • HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없음.
• GROUP BY 작업도 인덱스를 사용하는 경우와 그렇지 못한 경우로 나눠 볼 수 있음.
  • 인덱스를 이용할 때는
    • 인덱스를 차례대로 읽는 인덱스 스캔 방법을 이용
    • 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔 방법을 이용
  • 인덱스 사용 못할 시
    • GROUP BY 작업은 임시 테이블을 사용함.

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

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

루스 인덱스 스캔을 이용하는 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;

+----+----------+-------+---------+---------------------------------------+
| id | table    | type  | key     | Extra                                 |
+----+----------+-------+---------+---------------------------------------+
| 1  | salaries | range | PRIMARY | Using where; Using index for group-by |
+----+----------+-------+---------+---------------------------------------+

• salaries 테이블의 인덱스는 (emp_no, from_date)로 생성되어 있으므로 위 쿼리에서 WHERE 조건은 인덱스 레인지 스캔 접근 방식으로 이용할 수 없음.
  • 하지만, 이 쿼리의 실행 계획은 인덱스 레인지 스캔(range 타입)을 이용함.
  • Extra 칼럼의 메시지에도 GROUP BY 처리까지 인덱스를 사용했다는 것을 알 수 있음.
• MySQL 서버에서 이 쿼리는 다음과 같은 순서로 실행함.
  1. (emp_no, from_date) 인덱스를 차례대로 스캔하면서 emp_no의 첫번째 유일한 값(그룹 키) “10001”을 찾아냄.
  2. (emp_no, from_date) 인덱스에서 emp_no가 ‘10001’ 인 것 중에서 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번 과정으로 돌아가서 반복 수행함.
• MySQL의 루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있음.
• 또, 프리픽스 인덱스(Prefix, index, 칼럼값의 앞쪽 일부만으로 생성된 인덱스)는 루스 인덱스 스캔을 사용할 수 없음.
• 인덱스 레인지 스캔에서는, 유니크한 값의 수가 많을 수록 성능이 향상된다.
  • 반면, 루스 인덱스 스캔에서는 유니크한 값의 수가 적을 수록 성능이 향상됨.
  • 즉, 루스 인덱스 스캔은 분포도(카디널리티)가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어 냄.
  • 루스 인덱스 스캔으로 처리되는 쿼리에서는 별도의 임시 테이블이 필요 없음.
• 루스 인덱스 스캔이 사용될 수 있을지 판단하는건 어려움.
  • WHERE 절의 조건이나 ORDER BY 절이 인덱스를 사용할 수 있을지 판단하는 것보다..
  • 여러 패턴의 쿼리를 살펴보고 루스 인덱스 스캔을 사용할 수 있는지 판별하는 연습을 해야함. (p307에 예시가 잘 설명되어 있음.)

MySQL 8.0 버전부터 루스 인덱스 스캔과 동일한 방식으로 작동하는 인덱스 스킵 스캔(Index Skip Scan)최적화도 도입됨. 8.0 이전버전 까지는 GROUP BY 절의 처리를 위해서만 루스 인덱스 스캔이 사용되었음. 8.0 버전부터는 인덱스 스킵 스캔이 도입되면서 옵티마이저가 쿼리에 필요로 하는 레코드를 검색하는 부분까지 루스 인덱스 스캔 방식으로 최적화가 가능해짐.
인덱스 스킵 스캔 또한 루스 인덱스 스캔과 마찬가지로 조건이 누락된 인덱스의 선행 칼럼이 유니크한 값을 많이 가질 수록 쿼리 처리 성능이 떨어짐. 따라서, 인덱스 스킵 스캔에서도 선행 칼럼의 유니크한 값의 개수가 많으면 인덱스 스킵 스캔 최적화를 사용하지 않는다.

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

• 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;

+----+-------+------+---------+-----------------+
| id | table | type | key     | Extra           |
+----+-------+------+---------+-----------------+
|  1 | e     | ALL  | NULL    | Using temporary |
|  1 | s     | ref  | PRIMARY | NULL            |
+----+-------+------+---------+-----------------+

• 위 실행 계획의 Extra 칼럼에 “Using filesort”가 표시되지 않고, “Using temporary”만 표시됨.
  • 8.0 이전 버전까지는 GROUP BY가 사용된 쿼리는 그루핑되는 칼럼을 기준으로 묵시적인 정렬까지 함께 수행함.
  • 8.0 버전부터는 이 같은 묵시적인 정렬은 더이상 실행되지 않게 바뀜.
• MySQL 8.0에서는 GROUP BY가 필요한 경우, 내부적으로 GROUP BY 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행함.
  • 즉, 위 쿼리를 처리하기 위해 MySQL 서버는 아래와 같이 임시 테이블을 생성한다.
  • 그리고, 조인 결과를 한 건씩 가져와서 임시 테이블에서 중복 체크 하면서 INSERT 또는 UPDATE 를 실행함.

CREATE TEMPORARY TABLE ... (
    last_name VARCHAR(16),
    salary INT,
    UNIQUE INDEX ux_lastname (last_name)
);

• MySQL 8.0에서도 GROUP BY, ORDER 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;
                 ORDER BY e.last_name;

+----+----------+------+---------+---------------------------------+
| id | table    | type | key     | Extra                           | 
+----+----------+------+---------+---------------------------------+
|  1 | e        | ALL  | NULL    | Using temporary; Using filesort |
|  1 | s        | ref  | PRIMARY | NULL                            |
+----+----------+------+---------+---------------------------------+

DISTINCT 처리

• 특정 칼럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용함.
  • DISTINCT는 MIN(), MAX(), COUNT() 같은 집합 함수와 함께 사용되는 경우와 집합 함수가 없는 경우 2가지로 구분해서 살펴본다.
    • 이렇게 구분한 이유는 각 경우에 DISTINCT 키워드가 영향을 미치는 범위가 다르기 때문임.
    • 그리고, 집합 함수와 같이 DISTINCT가 사용되는 쿼리의 실행 계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요함.
      • 하지만, 실행 계획의 Extra 칼럼에는 “Using temporary” 메시지가 출력되지 않음.

SELECT DISTINCT … 

• 단순히 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 하면, SELECT DISTINCT 형태의 쿼리 문장을 사용함.
  • 이 경우는 GROUP BY와 동일한 방식으로 처리됨.
  • 아래의 쿼리는 내부적으로 같은 작업을 수행함.

mysql> SELECT DISTINCT emp_no FROM salaries;
mysql> SELECT emp_no FROM salaries GROUP BY emp_no;

• DISTINCT는 SELECT하는 레코드(튜플)를 유니크하게 SELECT 하는 것이지, 특정 칼럼만 유니크하게 조회하는 것이 아님.

mysql> SELECT DISTINCT first_name, last_name FROM employees;

• 위 쿼리는 first_name만 유니크한 것을 가져오는 것이 아니라 (first_name, last_name) 조합 전체가 유니크한 레코드를 가져옴.

mysql> SELECT DISTINCT(first_name), last_name FROM employees;
mysql> SELECT DISTINCT first_name, last_name FROM employees;

• SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 칼럼에 영향을 미친다.
  • 절대로 SELECT하는 여러 칼럼 중에서 일부 칼럼만 유니크하게 조회하는 것이 아님.
  • 따라서, 위 쿼리는 괄호가 있든 없든 의미가 없이 똑같은 쿼리임.

집합 함수와 함께 사용된 DISTINCT

• COUNT() 또는 MIN(), MAX() 같은 집합 함수 내에서 DISTINCT 키워드가 사용될 수 있음.
  • 이 경우는 SELECT DISTINCT와 다른 형태로 해석됨.
• 집합 함수가 없는 SELECT 쿼리에서, DISTINCT는 조회하는 모든 칼럼의 조합이 유니크한 것들만 가져옴.
  • 하지만 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼값이 유니크한 것들만 가져옴.

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;

+----+-------+-------+---------+--------------------------+
| id | table | type  | key     | Extra                    |
+----+-------+-------+---------+--------------------------+
|  1 | e     | range | PRIMARY | Using where; Using index |
|  1 | s     | ref   | PRIMARY | NULL                     |
+----+-------+-------+---------+--------------------------+

• 위 쿼리는 내부적으로 “COUNT(DISTINCT s.salary)” 처리를 위해 임시 테이블을 사용한다.
  • 하지만, 이 쿼리의 실행 계획에는 임시 테이블을 사용한다는 메시지는 표시되지 않음.
  • 이는 버그처럼 보이지만, 지금까지의 모든 MySQL 서버에서 보여주는 실행 계획에 “Using temporary”를 표시하지 않고 있음.
• employees 테이블과 salaries 테이블을 조인한 결과에서 salary 칼럼의 값만 저장하기 위한 임시 테이블을 만들어서 사용함.
  • 이때, 임시 테이블의 salary 칼럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아지면 상당히 느려질 수 있는 형태의 쿼리임.
• 만약 위 쿼리에서 “COUNT(DISTINCT e.last_name)” 하나를 더 추가한다면 e.last_name 칼럼 값을 저장하는 또 다른 임시 테이블이 필요하므로, 위 쿼리는 전체적으로 2개의 임시테이블을 사용하게 된다.

mysql> SELECT COUNT(DISTINCT emp_no) FROM employees;
mysql> SELECT COUNT(DISTINCT emp_no) FROM dept_emp GROUP BY dept_no;

• 위 쿼리와 같이 인덱스된 칼럼에 대해 DISTINCT 처리를 수행할 때는 인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블 없이 최적화된 처리를 수행할 수 있음.

내부 임시 테이블 활용

• MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때, 내부적인 임시 테이블(Internal temporary table)을 사용함.
• “내부적(Internal)” 이라는 단어가 포함된 이유는 “CREATE TEMPORARY TABLE” 명령으로 만든 임시 테이블과 다르기 때문임.
• 일반적인 MySQL 엔진이 사용하는 임시 테이블은 처음에 메모리에 생성했다가 테이블의 크기가 커지면 디스크로 옮겨짐.
  • 특정 예외 케이스는 메모리를 거치지 않고 바로 디스크에 임시 테이블이 만들어지기도 함.
• MySQL 엔진이 내부적인 가공을 위해 생성하는 임시 테이블은 다른 세션이나 다른 쿼리에서는 볼 수 없으며, 사용하는 것도 불가능함.
  • 사용자가 생성한 임시 테이블(CREATE TEMPORARY TABLE) 과는 달리, 내부적인 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제됨.

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

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

MySQL 서버는 디스크의 임시 테이블을 생성할 때, 파일 오픈 후 즉시 파일 삭제를 실행한다. (파일이 오픈된 상태에서 삭제되면 운영체제는 그 파일을 즉시 삭제하지는 않는다. 대신, 운영체제는 파일을 참조하는 프로세스가 모두 없어지면 그때 자동으로 파일을 삭제함.)
그리고 데이터를 저장하기 위해 해당 임시 테이블을 사용함. 이렇게 함으로써 MySQL 서버가 종료되거나 해당 쿼리가 종료되면 임시 테이블은 즉시 사라지게 보장하는 것임. 또, 이렇게 함으로써 MySQL 서버 내부의 다른 스레드, 또는 MySQL 서버 외부의 사용자가 해당 임시 테이블을 위한 파일을 변경 및 삭제하거나 볼 수 없게 하는 것이다.

• 내부 임시 테이블이 메모리에 생성되지 않고 처음부터 디스크 테이블로 생성되는 경우도 있음.
  • 이 경우에는 internal_tmp_disk_storage_engine 시스템 변수에 설정된 스토리지 엔진이 사용됨.
    • 기본값은 InnoDB 임.

임시 테이블이 필요한 쿼리

• 다음과 같은 패턴의 쿼리는 MySQL 엔진에서 별도의 데이터 가공 작업을 필요로 함.
  • 대표적으로 내부 임시 테이블을 생성하는 케이스임.
  • 물론, 이 밖에도 인덱스를 사용하지 못할 때는 내부 임시테이블을 생성해야 할 떄가 많음.
    • ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
    • ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번쨰 테이블이 아닌 쿼리
    • DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
    • UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)
    • 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
• 어떤 쿼리의 실행 계획에서 임시 테이블을 사용하는지는 Extra 칼럼에 “Using temporary” 메시지가 표시되는지 확인하면 됨.
  • “Using temporary” 가 표시되지 않아도 임시 테이블을 사용할 수 있음.
    • 위 예에서 마지막 3개 패턴이 그럴 수 있음.
  • 1~4번째 쿼리 패턴은 유니크 인덱스를 가지는 내부 임시테이블이 만들어짐.
    • 쿼리 처리 성능이 5번 패턴보다 느림.
  • 5번쨰 쿼리 패턴은 유니크 인덱스가 없는 내부 임시 테이블이 생성됨.

8.0 이전 버전까지는 UNION ALL 이 사용된 쿼리도 항상 내부 임시 테이블을 사용해서 결과를 모은 후 결과를 반환함. 8.0 버전부터는 UNION ALL을 사용하는 쿼리는 더이상 임시 테이블을 사용하지 않게 개선됨.
그러나, UNION (= UNION DISTINCT)을 사용하는 쿼리는 8.0에서도 여전히 내부 임시 테이블을 사용함. → 중복 제거 작업이 필요하기에 임시 테이블을 이용한 중복 제거 작업이 필수적임.

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

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

임시 테이블 관련 상태 변수

• 실행 계획상에서 “Using temporary” 가 표시되면 임시 테이블을 사용했다는 사실을 알 수 있음.
  • 하지만 임시 테이블이 메모리에서 처리됐는지 디스크에서 처리됐는지 알 수 없음.
  • 몇 개의 임시 테이블이 사용됐는지도 알 수 없음.
• 임시 테이블이 디스크에 생성됐는지 메모리에 생성됐는지 확인하려면 MySQL 서버의 상태 변수 SHOW SESSION STATUS LIKE 'Created_tmp%' 를 확인해보면 됨.
  • Created_tmp_tables : 쿼리의 처리를 위해 만들어진 내부 임시 테이블의 개수를 누적하는 상태 값임. 이 값은 내부 임시 테이블이 메모리에 만들어졌는지 디스크에 만들어졌는지 구분하지 않고 모두 누적함.
  • Created_tmp_disk_tables : 디스크에 내부 임시 테이블이 만들어진 개수만 누적해서 가지고 있는 상태 값임.