본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
DISTINCT
특정 칼럼의 유니크한 값을 조회할때 SELECT 쿼리에 DISTINCT를 사용한다. DISTINCT는 MIN(), MAX(), COUNT()와 같은 집합 함수와 함께 사용하는 경우와, 집합 함수가 없이 사용하는 경우 두 가지로 구분된다. 두가지 경우로 구분하는 이유는 DISTINCT 키워드가 영향을 미치는 범위가 달라지기 때문이다. 집합 함수와 같이 DISTINCT가 사용되는 쿼리는 실행 계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 있어야 한다.
SELECT DISTINCT
SELECT되는 레코드 중에서 유니크한 레코드만 가져오려면 SELECT DISTINCT 형태의 쿼리문장을 사용한다. 이 경우에는 GROUP BY와 거의 같은 방식으로 처리된다. 단 차이는 SELECT DISTINCT의 경우 정렬이 보장되지 않는다. 두 쿼리는 정렬 관련 부분만 빼면 내부적으로 같은 작업을 수행한다. 사실 이 두 개의 쿼리는 모두 인덱스를 이용하기 때문에 부가적인 정렬 작업이 필요하지 않으며 완전히 같은 쿼리다. 하지만 인덱스를 이용하지 못하는 DISTINCT는 정렬을 보장하지 않는다.
SELECT DISTINCT emp_no FROM salaries;
SELECT emp_no FROM salaries GROUP BY emp_no;
DISTINCT를 사용할 때 자주 실수하는 부분은, SELECT 되는 레코드를 유니크하게 SELECT 하는 것이지 칼럼을 유니크하게 조회하는 것이 아니다. 즉 아래의 쿼리에서 SELECT 되는 결과는 first_name 만 유니크한 것이 아니라 first_name+last_name 전체가 유니크한 레코드를 가져온다.
SELECT DISTINCT first_name, last_name FROM employees;
아래의 쿼리는 얼핏 보면 first_name 만 유니크하게 조회하고 last_name은 그냥 DISTINCT 없이 조회하는 쿼리처럼 보인다. 하지만 MySQL 서버는 DISTINCT 뒤의 괄호는 그냥 의미 없이 사용된 괄호로 해석하고 제거해 버린다. DISTINCT는 함수가 아니므로 그 뒤의 괄호는 의미가 없는 것이다.
SELECT DISTINCT(first_name), last_name FROM employees;
SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 칼럼에 영향을 미친다. 절대로 칼럼 일부만 유니크하게 조회하는 방법은 없다. 단, 밑에서 설명할 DISTINCT가 집함 함수 내에서 사용된 경우는 조금 다르다.
집합 함수와 함께 사용된 DISTINCT
COUNT() 또는 MIN(), MAX()와 같은 집합 함수 내에서 DISTINCT 키워드가 사용될 때는 일반적으로 SELECT DISTINCT와 다른 형태로 해석된다. 집합 함수가 없는 SELECT 쿼리에서 DISTINCT는 조회되는 모든 칼럼의 조합가운데 유일한 값만 가져온다. 하지만 집합함수 내에 사용된 DISTINCT는 집합 함수의 인자로 전달된 칼럼 값들 중에서 중복을 제거하고 남은 값만을 가져온다.
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;
이 쿼리의 실행 계획의 Extra 칼럼에는 출력되진 않지만 COUNT(DISTINCT s.salary) 의 처리에 인덱스 대신 임시테이블을 사용한다. 이 쿼리의 실행 계획에는 임시 테이블을 사용한다는 메시지는 표시되지 않는다.
위의 쿼리에서는 employees 테이블과 salaries 테이블을 조인한 결과에서 salary 칼럼의 값만 저장하기 위해 임시 테이블을 만들어 사용한다. 임시테이블 salary 칼럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아진다면 상당히 느려질 수 있는 형태의 쿼리이다.
아래의 쿼리는 s.salary 칼럼의 값을 저장하는 임시 테이블과, e.last_name 칼럼의 값을 저장하는 또 다른 임시 테이블이 필요하기 때문에 전체적으로 2개의 임시 테이블을 사용한다.
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;
위의 쿼리는 DISTINCT 처리를 위해 인덱스를 이용할수 없기 때문에 임시테이블이 필요하다. 하지만 다음 쿼리 같이 인덱스된 칼럼에 대한 DISTINCT는 인덱스를 이용해 효율적으로 처리할 수 있다.
SELECT COUNT(DISTINCT emp_no) FROM employees;
SELECT COUNT(DISTINCT emp_no) FROM dept_emp GROUP BY dept_no;
LIMIT n
LIMIT는 MySQL에만 존재하는 키워드이며, 오라클의 ROWNUM과 MS-SQL의 TOP n과 비슷하다. 하지만 오라클의 ROWNUM이나 MSSQL의 TOP n과는 작동 방식이 조금 다르다.
우선 LIMIT가 사용된 예제 쿼리를 한번 살펴보자.
SELECT * FROM employees
WHERE emp_no BETWEEN 10001 AND 10010
ORDER BY first_name
LIMIT 0, 5;
위의 쿼리는 다음과 같은 순서대로 실행된다.
1.employees 테이블에서 WHERE 절의 검색 조건에 일치하는 레코드를 전부 읽어 온다.
2.1번에서 읽어온 레코드를 first_name 칼럼 값에 따라 정렬한다.
3.정렬된 결과에서 상위 5건만 사용자에게 반환한다.
MySQL의 LIMIT는 WHERE 조건이 아니기 때문에 항상 쿼리의 가장 마지막에 실행된다. LIMIT의 중요한 특성은 LIMIT에서 필요한 레코드 건수만 준비되면 바로 쿼리를 종료시킨다는 것이다. 즉 위의 레코드의 정렬이 완료되지 않았다 하더라도 상위 5건까지만 정렬이 되면 작업을 멈춘다는 것이다.
다른 GROUP BY 절이나 DISTINCT 등과 같이 LIMIT가 사용됐을 때, 어떻게 동작하는지 다음의 쿼리로 조금 더 살펴보자.
SELECT * FROM employees LIMIT 0, 10;
- 이 쿼리는 LIMIT가 없을 때는 employees 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔을 실행할 것이다. 하지만 LIMIT 옵션이 있기 때문에 풀 테이블 스캔을 실행하면서 MySQL이 스토리지 엔진으로부터 10개의 레코드를 읽어들이는 순간 스토리지 엔진으로부터 읽기 작업을 멈춘다. 정렬이나 그룹핑 또는 DISTINCT가 없는 쿼리에서 LIMIT 조건을 사용하면 쿼리가 상당히 빨리 끝날 수 있다.
SELECT * FROM employees GROUP BY first_name LIMIT 0, 10;
- 이 쿼리는 GROUP BY가 있기 때문에, GROUP BY 처리가 완료되고 나서야 LIMIT를 수행할 수 있다. 인덱스를 사용하지 못하는 GROUP BY 때문에 LIMIT와 GROUP BY를 함께 사용하는 경우 LIMIT 절이 있더라도 실질적인 서버의 작업 내용을 크게 줄여 주지는 못한다.
SELECT DISTINCT first_name FROM employees LIMIT 0, 10;
- 이 쿼리에서 사용한 DISTINCT는 정렬에 대한 요건이 없이 유니크한 그룹만 만들어 내면 도니다. MySQL은 스토리지 엔진을 통해 풀 테이블 스캔 방식을 이용해 employee 테이블 레코드를 읽어들임과 동시에 DISTINCT를 위한 중복 제거 작업(임시 테이블 사용)을 진행한다. 이 작업을 반복적으로 처리핟가 유니크한 레코드가 LIMIT 건수만큼 채워지면 그 순간 쿼리를 멈춘다.
예를 들어 employees 테이블의 레코드를 10건 읽었는데 first_name 이 모두 달랐다면 유니크한 first_name 10개를 가져온 것이므로 employees 테이블을 더는 읽지 않고 쿼리를 완료한다는 것이다. DISTINCT와 함께 사용된 LIMIT는 실질적인 중복 제거 작업의 범위를 줄이는 역할을 한다. 이 쿼리에서는 10만 건의 레코드를 읽어야 할 작업을 10건만 읽어서 완료할 수 있게 했으므로 LIMIT 절이 작업량을 상당히 줄여준 것이다.
SELECT * FROM employees
WHERE emp_no BETWEEN 10001 AND 11000
ORDER BY first_name LIMIT 0, 10;
- 이 쿼리는 employees 테이블로부터 WHERE 조건에 일치하는 레코드를 읽은 후 first_name 칼럼의 값으로 정렬을 수행한다. 정렬을 수행하면서 필요한 10건이 완성되는 순간, 나머지 작업을 멈추고 결과를 사용자에게 반환한다. 정렬을 수행하기 전에 WHERE 조건에 일치하는 모든 레코드를 읽어 와야 하지만 읽어온 결과가 전부 정렬돼야 쿼리가 완료되는 것이 아니라 필요한 만큼만 정렬되면 된다는 것이다. 이 쿼리도 두 번째 쿼리와 같이 크게 작업량을 줄여주지는 못한다.
위 예제들에서 알수 있듯이 쿼리 문장에 GROUP BY나 ORDER BY와 같은 전체 범위 작업이 선행되더라도, LIMIT 옵션이 있다면 크진 않지만 나름의 성능 향상은 있다고 볼 수 있다. 만약 ORDER BY나 GROUP BY가 인덱스를 이용해 처리될 수 있다면 LIMIT 절은 꼭 필요한 만큼의 레코드만 읽도록 만들어주기 때문에 쿼리의 작업량을 상당히 줄여 준다.
LIMIT 옵션은 1개 또는 2개 인자를 사용할 수 있는데, 인자가 1개인 경우에는 상위 n개의 레코드를 가져오며, 인자가 2개의 인자를 지정하는 경우 첫 번째 위치부터 두번째 인자에 명시된 개수의 레코드를 가져온다. LIMIT 절에서 2개의 인자를 사용하는 경우, 첫 번째 인자(시작 위치, 오프셋)는 0부터 시작한다는 것을 주의하자. LIMIT 10과 같이 인자가 1개인 경우는 LIMIT 0, 10과 동일한 옵션이다. 다음 예제에서는 첫번째 쿼리는 상위 10개, 두번째 쿼리는 상위 11번부터 10개의 레코드를 가져온다.
SELECT * FROM employees LIMIT 10;
SELECT * FROM employees LIMIT 10, 10;
LIMIT 제한사항으로는 인자로, 표현식이나 별도의 서브 쿼리를 사용할수 없다.
