MySQL DISTINCT 처리, 임시테이블이 필요한 쿼리

특정 칼럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다. 

DISTINCT는 MIN(), MAX() 또는 COUNT() 같은 집합 함수와 함께 사용되는 경우와 집합 함수가 없는 경우의 2가지로 구분해서 살펴볼 수 있다.

 

• 위 2가지로 구분하는 이유는 각 경우에 DISTINCT 키워드가 영향을 미치는 범위가 달라지기 때문이다. 
• 그리고 집합 함수와 같이 DISTINCT가 사용되는 쿼리의 실행 계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요하다.

 

 

 

▶ SELECT DISTINCT ...

---

• 단순히 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용한다.
• 이 경우에는 GROUP BY와 동일한 방식으로 처리된다.
• 특히 MySQL 8.0 버전부터는 GROUP BY를 수행하는 쿼리에 ORDER BY 절이 없으면 정렬을 사용하지 않기 때문에 다음의 두 쿼리는 내부적으로 같은 작업을 수행한다.

 

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> SELECT DISTINCT(first_name), last_name FROM employees;

 

위 쿼리를 얼핏 보면 first_name만 유니크하게 조회하고 last_name은 그냥 DISTINCT가 없을 때와 동일하게 조회하는 쿼리처럼 보인다. 하지만 MySQL 서버는 DISTINCT 뒤의 괄호를 그냥 의미 없이 사용된 괄호로 해석하고 제거해 버린다. DISTINCT는 함수가 아니므로 그 뒤의 괄호는 의미가 없는 것이다

 

→ SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 칼럼에 영향을 미친다.

 

 

 

 

 

 

 

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

---

count() or MIN() or MAX() 같은 집합 함수 내에서 DISTINCT 키워드가 사용될 수 있는데, 이 경우 위와 다르게 처리된다.

집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼값이 유니크한 것들을 가져온다.

 

 

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

 

이 쿼리는 COUNT(DISTINCT s.salary)"를 처리하기 위해 임시 테이블을 사용한다. 

• employees 테이블과 salaries 테이블을 조인한 결과에서 salary 칼럼의 값만 저장하기 위한 임시 테이블을 만들어서 사용한다.
• 이때 임시 테이블의 salary 칼럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아진다면 상당히 느려질 수 있는 형태의 쿼리다.

 

 

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

 

위 쿼리는 위 위 쿼리에 COUNT(DISTINCT ...)를 하나 더 추가해서 변경하였다. COUNT() 함수가 두 번 사용되어 s.salary 칼럼의 값을 저장하는 임시 테이블과 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 8.0 이전 버전까지는 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 이용한다.

 

하지만 MySQL 8.0 버전부터는 메모리는 TempTable 이라는 스토리지 엔진을 사용하고, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용하도록 개선됐다.

 

• 기존 Memory 스토리지 엔진은 VARBINARY나 VARCHAR 같은 가변 길이 타입을 지원하지 못하기 때문에 임시 테이블이 메모리에 만들어지면 가변 길이 타입의 경우 최대 길이만큼 메모리를 할당해서 사용했다.
• 이는 메모리 낭비가 심해지는 문제점을 안고 있었고 디스크에 임시 테이블이 만들어질 때 사용되는 MyISAM 스토리지 엔진은 트랜잭션을 지원하지 못한다는 문제점을 안고 있었다.
• 그래서 MySQL 8.0 버전부터는 MEMORY 스토리지 엔진 대신 가변 길이 타입을 지원하는 TempTable 스토리지 엔진이 도입됐으며, MyISAM 스토리지 엔진을 대신해서 트랜잭션 지원 가능한 InnoDB 스토리지 엔진이 사용되도록 개선된 것이다.

 

 

 

 

 

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

---

다음과 같은 패턴의 쿼리는 MySQL 엔진에서 별도의 데이터 가공 작업을 필요로 하므로 대표적으로 내부 임시 테이블을 생성하는 케이스다. 물론 이 밖에도 인덱스를 사용하지 못할 때는 내부 임시 테이블을 생성해야 할 때가 많다.

 

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

 

 

 

 

 

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

---

내부 임시 테이블은 기본적으로 메모리 상에 만들어지지만 다음과 같은 조건을 만족하면 메모리 임시 테이블을 사용할 수 없게 된다. 그래서 이 경우에는 디스크 기반의 임시 테이블을 사용한다.

 

• UNION이나 UNION ALL에서 SELECT되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
• GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
• 메모리 임시 테이블의 크기가 (MEMORY 스토리지 엔진에서) tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 (TempTable 스토리지 엔진에서) temptable_max_ram 시스템 변수 값보다 큰 경우