본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
SELECT 각 절의 처리순서
포스팅에서 사용된 SELECT 문장이라는 용어는 SQL 전체를 의미한다. SELECT 키워드와 실제 가져올 칼럼을 명시한 부분만 언급할 때는 SELECT 절 이라 표현하겠다. 여기서 절이란 주로 알고 있는 키워드 (SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT)와 그 뒤에 기술된 표현식을 묶어서 말한다.
SELECT s.emp_no COUNT(DISTINCT e.first_name) AS cnt
FROM salaries s
INNER JOIN employees e ON e.emp_no=s.emp_no
WHERE s.emp_no IN (10001, 100002)
GROUP BY s.emp_no
HAVING AVG(s.salary) > 1000
ORDER BY AVG(s.salary)
LIMIT 10;
위 쿼리 예제를 각 절로 나누면 다음과 같다.
- SELECT 절 : SELECT s.emp_no COUNT(DISTINCT e.first_name) AS cnt
- FROM 절 : FROM salaries s INNER JOIN employees e ON e.emp_no=s.emp_no
- WHERE 절 : WHERE s.emp_no IN(100001, 100002)
- GROUP BY 절 : GROUP BY s.emp_no
- HAVING 절 : HAVING AVG(s.salary) > 1000
- ORDER BY 절 : ORDER BY AVB(s.salary)
- LIMIT 절 : LIMIT 10
위의 쿼리같이 SELECT 쿼리에 지정할 수 있는 대부분의 절이 포함되어 있는 경우 어느 절이 먼저 실행될지 예측하지 못할 때가 자주 있다.
위 그림의 쿼리 절의 실행순서가 바뀌어 실행되는 형태의 쿼리는 아래의 예제를 제외하고는 거의 없다.
위의 그림에서 ORDER BY 가 사용된 쿼리에서 예외적인 순서로 실행되는 경우를 보여준다. 첫 번째 테이블만 읽어서 정렬을 수행하고 나머지 테이블을 읽는데, 주로 GROUP BY 절이 없이 ORDER BY 만 사용된 쿼리에서 사용될 수 있는 순서다.
WHERE 절과 GROUP BY 절, 그리고 ORDER BY 절의 인덱스 사용
WHERE 절의 조건뿐만 아니라 GROUP BY나 ORDER BY 절도 인덱스를 이용해 빠르게 처리할 수 있다. 아래의 내용들은 각 절에서 어떤 요건을 갖추었을 때 인덱스를 이용할 수 있는지 자세히 살펴본다.
인덱스를 사용하기 위한 기본 규칙
WHERE 절이나 ORDER BY 또는 GROUP BY가 인덱스를 사용하려면 기본적으로 인덱스된 칼럼의 값 자체가 변환하지 않고 그대로 사용한다는 조건을 만족해야 한다. 인덱스는 칼럼의 값을 아무런 변환 없이 B-Tree에 정렬해서 저장한다. WHERE 조건이나 GROUP BY 또는 ORDER BY 에서도 원본값을 검색하거나 정렬할 때만 B-Tree 에 정렬된 인덱스를 이용한다. 다음 예제와 같이 칼럼의 값을 가공한 후 다른 상수 값과 비교한다면 이 쿼리는 인덱스를 적절하게 이용하지 못하게 된다.
SELECT * FROM salaries WHERE salary*10 > 150000;
이 쿼리는 다음과 같이 변경해 salary 칼럼의 값을 변경하지 않고 검색하도록 유도할 수 있지만, 옵티마이저에서는 인덱스를 최적으로 이용할 수 있게 표현식을 변환하지는 못한다.
SELECT * FROM salaries WHERE salary > 15000/10;
칼럼의 값을 여러 개를 곱하거나 더해서 비교해야 하는 복잡한 연산이 필요할 때는 미리 계산된 값을 저장할 별도의 칼럼을 추가하고 그 칼럼에 인덱스를 생성하는 것이 유일한 해결책이다. 결론적으로 인덱스의 칼럼을 변형해서 비교하는 경우에는 인덱스를 이용할 수 없게 된다는 점에 주의하자.
WHERE 절에 사용되는 비교 조건에서 연산자 양쪽의 두 비교 대상 값은 데이터 타입이 일치해야 한다.
CREATE TABLE tb_test (age VARCHAR(10), INDEX ix_age (age));
INSERT INTO tb_test VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7');
SELECT * FROM tb_test WHERE age=2;
age라는 VARCHAR 타입의 칼럼이 지정된 테이블을 생성하고, 테스트용 레코드를 INSERT하자. 그리고 SELECT 쿼리의 실행 계획을 한번 확인해 보자. 이 쿼리는 age라는 칼럼에 인덱스가 준비돼 있어서 type 칼럼에 "ref"나 "range"가 표시되어야 할 것으로 기대되지만 사실 "index"라고 표시도니다.
왜 이 쿼리가 인덱스 레인지 스캔을 사용하지 못하고, 인덱스 풀 스캔을 사용한 이유는 바로 age 칼럼의 데이터 타입이 비교되는 값의 데이터 타입과 다르기 때문이다(VARCHAR, INTEGER). MySQL 옵티마이저가 문자열을 숫자 타입으로 변환한 후 비교 작업을 처리하게 된다. age 칼럼이 숫자 타입으로 변환된 후 비교돼야 하므로 인덱스 레인지 스캔이 불가능한 것이다. 이 쿼리를 다음과 같이 변경하면 인덱스 레인지 스캔을 사용하도록 유도할 수 있다.
SELECT * FROM tb_test WHERE age='2';
저장하고자 하는 값의 타입에 맞춰서 칼럼의 타입을 선정하고, SQL을 작성할 때는 데이터의 타입에 맞춰서 비교 조건을 맞춰서 비교 조건을 사용하는 것이 좋다.
WHERE 절의 인덱스 사용
WHERE 조건이 인덱스를 사용하는 방법은 크게 범위 제한 조건과 체크 조건 두 방식으로 구분할 수 있다. 둘 중에서 범위 제한 조건은 동등 비교 조건이나 IN으로 구성된 조건이 인덱스를 구성하는 칼럼과 얼마나 좌측부터 일치하는 가에 따라 달라진다.
그림 왼쪽은 4개의 칼럼이 순서대로 결합 인덱스로 생성돼 있는 것을 의미하며, 오른쪽은 SQL의 WHERE 절에 존재하는 조건을 의미한다. COL3의 조건이 동등 조건이 아닌 비교이므로 뒤 칼럼인 COL4의 조건은 범위 제한 조건으로 사용되지 못하고 체크 조건으로 사용된다. WHERE 절에서 각 조건이 명시된 순서는 중요치 않고, 칼럼에 대한 조건이 있는지 없는지가 중요하다. 보여준 예는 WHERE 조건이 AND 연산자로 연결되는 경우를 가정한 것이며 OR 연산자가 있으면 처리 방법이 완전히 바뀐다.
SELECT *
FROM employees
WHERE first_name='Kebin' OR last_name='Poly';
위 쿼리에서 first_name='Kebin' 조건은 인덱스를 이용할 수 있지만 last_name='Poly'는 인덱스를 사용할 수 없다. 두조건이 AND 연산자로 연결되었다면 first_name의 인덱스를 이요할수 있지만 OR 연산자로 연결됐기 때문에 옵티마이저는 풀 테이블 스캔을 선택할 수 밖에 없다. ( 풀테이블 스캔+인덱스 레인지 스캔) 작업량보다 (풀 테이블 스캔) 한번이 빠르기 때문이다.
WHER의 절에서 각 조건이 AND 로 연결되면 읽어와야 할 레코드의 건수를 줄이는 역할을 하지만 각 조건이 OR로 연결되면 읽어서 비교해야 할 레코드가 더 늘어나기 때문에 WHERE 조건에 OR 연산자가 있다면 주의해야 한다.
GROUP BY 절의 인덱스 사용
GROUP BY 절의 각 칼럼은 비교 연산자를 가지지 않으므로 범위 제한 조건이나 체크 조건과 같이 구분해서 생각할 필요는 없다. GROUP BY 절에 명시된 칼럼이 순서가 인덱스를 구성하는 칼럼 순서와 같으면 GROUP BY 절은 일단 인덱스를 이용할 수 있다. 여기서 설명하는 내용은 여러 개의 칼럼으로 구성된 다중 칼럼 인덱스를 기준으로 한다.
- GROUP BY 절에 명시된 칼럼이 인덱스 칼럼의 순서와 위치가 같아야 한다.
- 인덱스를 구성하는 칼럼 중에서 뒷쪽에 있는 칼럼은 GROUP BY 절에 명시되지 않아도 인덱스를 사용할 수 있지만 인덱스의 앞쪽에 있는 칼럼이 GROUP BY 절에 명시되지 않으면 인덱스를 사용할 수 없다.
- WHERE 조건절과는 달리, GROUP BY 절에 명시된 칼럼이 하나라도 인덱스에 없으면 GROUP BY 절은 전혀 인덱스를 이용하지 못한다.
위의 그림은 GROUP BY 절이 인덱스를 사용하기 위한 조건을 보여준다. 왼쪽은 COL1+COL2+COL3+COL4로 만들어진 인덱스를 의미하며, 오른쪽은 COL1부터 COL3을 순서대로 GROUP BY 절에 사용한 것을 의미한다. GROUP BY 절과 인덱스를 칼럼의 순서가 중요하므로 순서대로 표현되었다.
아래의 쿼리 예시는 인덱스를 이용하지 못하는 경우다.
... GROUP BY COL2, COL1
... GROUP BY COL1, COL3, COL2
... GROUP BY COL1, COL3
... GROUP BY COL1, COL2, COL3, COL4, COL5
- 첫 번째와 두번째는 GROUP BY 칼럼이 인덱스를 구성하는 칼럼의 순서와 일치하지 않기 때문에 사용하지 못한다.
- 세 번째 예제는 COL3 가 명시되어 있지만 COL2 가 그 앞에 명시되지 않았기 때문이다.
- 네 번째 예제는 GROUP BY 절 마지막 COL5가 인덱스에는 없어서 인덱스를 사용하지 못한다.
다음 예시는 GROUP BY 절이 인덱스를 사용할 수 있는 패턴이다. 다음의 예제는 WHERE 조건 없이 단순히 GROUP BY 만 사용된 형태의 쿼리다.
... GROUP BY COL1
... GROUP BY COL1, COL2
... GROUP BY COL1, COL2, COL3
... GROUP BY COL1, COL2, COL3, COL4
WHERE 조건절에 COL1이나 COL2가 동등 비교 조건으로 사용된다면, GROUP BY 절에 COL1이나 COL2가 빠져도 인덱스를 이용한 GROUP BY가 가능할 때도 있다. 인덱스의 앞쪽에 있는 칼럼을 WHERE 절에서 상수로 비교하기 때문에 GROUP BY 절에 해당 칼럼이 명시되지 않아도 인덱스를 이용한 그룹핑이 가능한 예제다.
... WHERE COL1='상수' ... GROUP BY COL2, COL3
... WHERE COL1='상수' AND COL2='상수' ... GROUP BY COL3, COL4
... WHERE COL1='상수' AND COL2='상수' AND COL3='상수' ... GROUP BY COL4
위의 예제는 WHERE 절과 GROUP BY 절이 혼용된 쿼리가 인덱스를 이용해 WHERE 절과 GROUP BY 저링 모두 처리될수 있는지는 WHERE 절에서 동등 비교 조건으로 사용된 칼럼을 GROUP BY 절로 옮겨보면 된다.
