[MySQL]MySQL 벼락치기(11) - 쿼리최적화(2)

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다. 

MySQL 역시 내용이 많기 때문에 시리즈로 나눠서 정리할 예정이다.

이번에는 쿼리최적화에 대해서 알아볼 예정이고 오늘은 그중에서도 WHERE 조건과 GROUP BY, ORDER BY를 이용하는 SELECT 쿼리를 사용할때 인덱스를 사용하려면 알아야 하는 정보에 대해서 알아볼 예정이다. 

SELECT 각 절의 처리 순서

아래와 같은 쿼리를 보자.

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 (100001, 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 AVG(s.salary)
• LIMIT 절 : LIMIT 10

위의 예제 쿼리는 SELECT 쿼리에 지정할 수 있는 대부분의 절이 포함돼 있다. 가끔 이런 쿼리에서 어느절이 먼저 실행될지 예측하지 못할 때가 자주 있는데, 어느 절이 먼저 실행되는지를 모르면 처리 내용이나 처리 결과를 예측 할 수 없다. 각 구분의 처리순서를 예측해보면 아래와 같다. 

WHERE 절과 GROUP BY 절, ORDER BY 절의 인덱스 사용

이번에는 각 절에서 어떤 요건을 갖췄을 때 인덱스를 이용할 수 있는지 좀더 자세히 살펴보자.

인덱스를 사용하기 위한 기본 규칙

인덱스된 컬럼의 값 자체를 변환하지 않고 그대로 사용해야 한다. 
아래와 같은 쿼리는 인덱스를 적절히 이용하지못하게 된다. 

비교 조건 연산자 양쪽의 두 비교 대상 값은 데이터 타입이 일치해야 한다. 
아래와 같은 쿼리를 실행하면 인덱스 레인지 스캔이 아닌 인덱스 풀 스캔을 사용한다. 

SELECT * FROM tab_test WHERE number_column = '10001'
SELECT * FROM tab_test WHERE string_column = 10001;

다만 이전 포스팅에서 설명한 경우처럼 number 컬럼에 string 숫자 값으로 비교할경우에는 성능상 영향을 주지 않는다. ( https://idea-sketch.tistory.com/51 )

WHERE 절의 인덱스 사용

WHERE 조건이 인덱스를 사용할 수 있는 기준은 이미 지난 포스팅에서 살펴봤다.( https://idea-sketch.tistory.com/43 )
WHERE 조건이 인덱스를 사용하는 방법은 크게 범위 제한 조건과 체크조건 두가지 방식으로 구분해 볼 수 있는데, 둘중에서 범위 제한 조건은 동등비교 조건이나 IN으로 구성된 조건이 인덱스를 구성하는 컬럼과 얼마나 좌측으로 부터 일치하는가에 따라 달라진다. 

WHERE 조건에서의 각 조건이 명시된 순서는 중요하지 않고, 그 컬럼에 대한 조건이 있는지 없는지가 중요하다.

GROUP BY 절의 인덱스 사용

GROUP BY 절의 각 컬럼은 비교 연산자를 가지지 않으므로 범위 제한 조건이나 체크조건과 같이 구분해서 생각할 필요는 없다. GROUP BY 절에 명시된 컬럼의 순서가 인덱스를 구성하는 컬럼의 순서와 같으면 GROUP BY 절은 일단 인덱스를 이용할 수 있다. 이를 정리해보면 아래와 같다. 

• GROUP BY 절에 명시된 컬럼이 인덱스 컬럼의 순서와 위치가 같아야 한다. 
• 인덱스를 구성하는 컬럼 중에서 뒷쪽에 있는 컬럼은 GROUP BY 절에 명시되지 않아도 인덱스를 사용할 수 있지만 인덱스 앞쪽에 있는 컬럼이 GROUP BY 절에 명시되지 않으면 인덱스를 사용할 수 없다. 
• WHERE 조건절과는 달리 GROUP BY 절에 명시된 컬럼이 하나라도 인덱스에 없으면 GROUP BY 절은 전혀 인덱스를 이용하지 못한다. 

인덱스(COL1, COL2, COl3, COL4) 가 있을때 다음에 예시된 GROUP BY 절은 모두 인덱스를 이용하지 못하는 경우이다. 

... GROUP BY COL2, COL1
... GROUP BY COL1, COL3, COL2
... GROUP BY COL1, COL3
... GROUP BY COL1. COL2, COL3, COL4, COL5

위 예제가 인덱스를 사용하지 못하는 원인을 살펴보자. 

• 첫번째와 두번째 예제는 GROUP BY 컬럼이 인덱스를 구성하는 컬럼의 순서와 일치하지 않기 때문에 사용하지 못하는 것이다. 
• 세번째 예제는 GROUP BY 절에 COL3가 명시됐지만 COL2가 그 앞에 명시되지 않았기 때문이다.
• 네번째 예제는 GROUP BY 절의 마지막에 있는 COL5가 인덱스에는 없어서 인덱스를 사용하지 못하는 것이다. 

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 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 절로 옮겨보면 된다. 

위의 예제에서 COL1 은 상수 값이 비교되므로 GROUP BY COL2, COL3 는 GROUP BY COL1, COL2, COL3 와 동일한 결과를 만들어 낸다. 이 처럼 GROUP BY 절을 고쳐도 똑같은 결과가 조회된다면 WHERE 절과 GROUP BY 절이 모두 인덱스를 사용할 수 있는 쿼리로 판단하면 된다. 

ORDER BY 절의 인덱스 사용

GROUP BY 와 ORDER BY 는 처리 방법이 상당히 비슷하다. 그래서 ORDER BY 절의 인덱스 사용 여부는 GROUP BY 의 요건과 거의 흡사하다. 하지만 추가적으로 ORDER BY 는 정렬되는 각 컬럼의 오름차순(ASC) 및 내림차순(DESC) 옵션이 인덱스와 같거나 또는 정반대의 경우에만 사용할 수 있다. 

인덱스(COL1, COL2, COl3, COL4) 가 있을때 다음에 예시된 ORDER BY 절은 모두 인덱스를 이용하지 못하는 경우이다. 

... ORDER BY COL2, COL3
... ORDER BY COL1, COL3, COL2
... ORDER BY COL1, COL2 DESC, COL3
... ORDER BY COL1, COL3
... ORDER BY COL1, COL2, COL3, COL4, COL5

위 예제가 인덱스를 사용하지 못하는 원인을 살펴보자. 

• 첫번째 예제는 인덱스의 제일 앞쪽 컬럼인 COL1 이 ORDER BY 절에 명시되지 않았기 때문에 인덱스를 사용할 수 없다.
• 두번째 예제는 인덱스와 ORDER BY 절의 컬럼 순서가 일치 하지 않기 때문에 인덱스를 사용할 수 없다. 
• 세번째 예제는 ORDER BY 절의 다른 컬럼은 모두 오름차순(ASC) 인데 두번째 컬럼인 COL2 의 정렬 순서가 내림차순(DESC)이라서 인덱스를 사용할 수 없다. 
• 네번째 예제는 인덱스에는 COL1, COL3 사이에 COL2 컬럼이 있지만 ORDER BY 절에는 COL2 컬럼이 명시되지 않았기 때문에 인덱스를 사용할 수 없다. 
• 다섯번째 예제는 인덱스에 존재하지 않는 COL5 가 ORDER BY 절에 명시됐기 때문에 인덱스를 사용하지 못한다. 

WHERE 조건과 ORDER BY(또는 GROUP BY) 절의 인덱스 사용

일반적으로 우리가 사용하는 쿼리는 WHERE 절을 가지고 있으며, 선택적으로 ORDER BY 나 GROUP BY 절을 포함할 것이다. 쿼리에 WHERE 절만 또는 GROUP BY 나 ORDER BY 절만 포함돼 있다면 사용된 절 하나에만 초점을 맞춰서 인덱스를 사용할 수 있게 튜닝하면 된다. 하지만 일반적으로 사용되는 쿼리는 그렇게 단순하지 않다. SQL 문장이 WHERE 절과 ORDER BY 절을 가지고 있다고 가정했을 때 WHERE 조건은 A 인덱스를 사용하고 ORDER BY 는 B 인덱스를 사용하는 것은 불가능하다. 이는 WHERE 절과 GROUP BY 절이 같이 사용된 경우와 GROUP BY 와 ORDER BY 가 같이 사용된 쿼리에서도 마찬가지다. 

WHERE 절에서 동등비교(Equal) 조건으로 비교된 컬럼과 ORDER BY 절에서 명시된 컬럼이 순서대로 빠짐없이 인덱스 컬럼의 왼쪽부터 일치해야 한다. 

다음의 예제를 한번보자. 

SELECT * FROM tb_test WHERE col1 > 10 ORDER BY col1, col2, col3
SELECT * FROM tb_test WHERE col1 > 10 ORDER BY col2, col3

위의 예제에서 첫번째는 col1 이 동등비교(Equal) 을 사용하지 않았지만 ORDER BY 절에서 col1~col3 까지 순서대로 모두 명시 됐기 때문에  WHERE 조건과 ORDER BY 절이 모두 인덱스를 사용할 수 있다. 하지만 두번째 에서는 WHERE 절에서 col1이 범위 조건으로 사용되고 ORDER BY 절에서 col1 이 없기 때문에 WHERE 절을 처리할때만 인덱스를 사용하고 정렬할때는 인덱스를 사용하지 못한다. 

WHERE 절과 GROUP BY 절 그리고 ORDER BY 절이 모두 포함된 쿼리가 인덱스를 사용하는지 판단하는 방법을 알아보자. 
다음과 같이 3개의 질문을 기본으로 해서 흐름을 적용해 보면 된다. 

1. WHERE 절이 인덱스를 사용할 수 있는가?
2. GROUP BY 절이 인덱스를 사용할 수 있는가?
3. GROUP BY 절과 ORDER BY 절이 동시에 인덱스를 사용할 수 있는가?

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~