MySQL - query-optimization(2)¶
이번 포스팅은 사내 MySQL 관련 내용 발표를 위해 Real MySQL 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다.
MySQL 역시 내용이 방대하기 때문에 시리즈로 나누어 정리하고 있으며, 이번에는 쿼리 최적화 중에서도 WHERE, GROUP BY, ORDER BY를 이용하는 SELECT 쿼리에서 인덱스를 타게 만들기 위해 알아야 할 핵심 원칙에 대해 알아본다.
1. 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
가끔 이런 쿼리에서 어느 절이 먼저 실행되는지 예측하지 못할 때가 많은데, 실행 순서를 모르면 처리 내용이나 결과, 인덱스 적용 여부를 정확히 예측할 수 없다. 일반적인 처리 순서는 아래와 같다.
- 드라이빙 테이블 & 드리븐 테이블 조인 및 데이터 추출 (
FROM,JOIN) - 조건 필터링 (
WHERE) - 그룹화 (
GROUP BY) - 중복 제거 (
DISTINCT) - 그룹 필터링 (
HAVING) - 정렬 (
ORDER BY) - 개수 제한 (
LIMIT)
2. 인덱스를 사용하기 위한 기본 규칙¶
인덱스를 효율적으로 사용하기 위해서는 다음과 같은 대원칙을 반드시 지켜야 한다.
- 인덱스 컬럼의 가공 금지: 인덱스된 컬럼의 값 자체를 변환하거나 함수로 감싸지 않고 그대로 사용해야 한다.
- 비교 대상 데이터 타입 일치: 비교 조건 연산자 양쪽의 데이터 타입이 반드시 일치해야 한다. 타입이 다르면 인덱스 레인지 스캔이 아닌 풀 테이블 스캔이 발생한다.
SELECT * FROM tab_test WHERE number_column = '10001'; -- 상수만 변환되므로 정상
SELECT * FROM tab_test WHERE string_column = 10001; -- 컬럼 전체가 숫자로 형변환되어 인덱스 사용 불가
3. WHERE 절의 인덱스 사용¶
WHERE 조건이 인덱스를 사용하는 방법은 크게 범위 제한 조건과 체크 조건 두 가지 방식으로 나뉜다. 그중 범위 제한 조건은 동등 비교(=)나 IN으로 구성된 조건이 인덱스를 구성하는 컬럼과 얼마나 좌측(Leftmost)부터 일치하는가에 따라 결정된다.
💡 핵심 포인트:
WHERE조건절에서 조건이 명시된 작성 순서는 중요하지 않으며, 옵티마이저가 알아서 순서를 맞추므로 해당 인덱스 컬럼에 대한 조건이 존재하는가가 핵심이다.
4. GROUP BY 절의 인덱스 사용¶
GROUP BY 절의 각 컬럼은 비교 연산자를 가지지 않으므로 범위 제한이나 체크 조건으로 구분하지 않는다. GROUP BY 절에 명시된 컬럼의 순서가 인덱스를 구성하는 컬럼의 순서와 같으면 인덱스를 이용할 수 있다.
GROUP BY 인덱스 사용 기본 조건¶
GROUP BY절에 명시된 컬럼이 인덱스 컬럼의 순서 및 위치와 정확히 일치해야 한다.- 인덱스를 구성하는 컬럼 중 뒤쪽에 있는 컬럼은 생략 가능하지만, 앞쪽에 있는 컬럼이 누락되면 인덱스를 전혀 사용할 수 없다.
WHERE절과 달리,GROUP BY절에 명시된 컬럼 중 하나라도 인덱스에 포함되어 있지 않으면 전체GROUP BY는 인덱스를 이용하지 못한다.
예시: 인덱스가 (COL1, COL2, COL3, COL4)일 때¶
❌ 인덱스를 사용하지 못하는 경우¶
... GROUP BY COL2, COL1 -- 순서 불일치
... GROUP BY COL1, COL3, COL2 -- 순서 불일치
... GROUP BY COL1, COL3 -- 중간의 COL2 누락
... GROUP BY COL1, COL2, COL3, COL4, COL5 -- 인덱스에 없는 COL5 포함
✅ 인덱스를 정상적으로 사용하는 패턴¶
... GROUP BY COL1
... GROUP BY COL1, COL2
... GROUP BY COL1, COL2, COL3
... GROUP BY COL1, COL2, COL3, COL4
WHERE 절과 GROUP BY 절이 함께 쓰일 때의 꿀팁¶
WHERE 조건절에서 앞쪽 컬럼이 동등 비교 조건(=)으로 사용된다면, GROUP BY 절에서 해당 컬럼이 빠져도 인덱스를 활용할 수 있다.
... WHERE COL1 = '상수' GROUP BY COL2, COL3
... WHERE COL1 = '상수' AND COL2 = '상수' GROUP BY COL3, COL4
COL1이 상수와 비교되므로 GROUP BY COL2, COL3은 사실상 GROUP BY COL1, COL2, COL3과 동일한 의미를 갖기 때문이다.
5. ORDER BY 절의 인덱스 사용¶
ORDER BY의 인덱스 사용 요건은 GROUP BY와 거의 흡사하다. 단, 정렬 특성상 모든 컬럼의 정렬 방향(ASC 또는 DESC)이 인덱스와 일치하거나 정확히 정반대인 경우에만 인덱스를 탈 수 있다.
예시: 인덱스가 (COL1, COL2, COL3, COL4)일 때¶
❌ 인덱스를 사용하지 못하는 경우¶
... ORDER BY COL2, COL3 -- 선두 컬럼 COL1 누락
... ORDER BY COL1, COL3, COL2 -- 컬럼 순서 불일치
... ORDER BY COL1, COL2 DESC, COL3 -- 오름차순/내림차순 혼용
... ORDER BY COL1, COL3 -- 중간 컬럼 COL2 누락
... ORDER BY COL1, COL2, COL3, COL4, COL5 -- 인덱스에 없는 COL5 포함
6. WHERE 조건과 ORDER BY(또는 GROUP BY) 절 동시 사용¶
실무 쿼리는 대부분 WHERE 절과 ORDER BY 절을 동시에 사용한다.
중요한 점은 하나의 쿼리에서 WHERE 조건은 A 인덱스를 타고, ORDER BY는 B 인덱스를 타는 것은 불가능하다는 것이다.
인덱스를 정렬까지 온전히 활용하려면 WHERE 절에서 동등 비교(=)된 컬럼과 ORDER BY 절에 명시된 컬럼이 합쳐져서 인덱스의 왼쪽부터 연속으로 일치해야 한다.
예제 분석¶
인덱스가 (col1, col2, col3)으로 생성되어 있을 때:
-- 1. WHERE와 ORDER BY 모두 인덱스 활용 (인덱스 레인지 스캔 + 정렬 생략)
SELECT * FROM tb_test WHERE col1 > 10 ORDER BY col1, col2, col3;
-- 2. WHERE 절만 인덱스를 타고, ORDER BY는 별도의 정렬(Filesort) 발생
SELECT * FROM tb_test WHERE col1 > 10 ORDER BY col2, col3;
첫 번째 쿼리는 ORDER BY에 col1이 포함되어 인덱스 순서를 그대로 유지하지만, 두 번째 쿼리는 col1이 범위 조건(>)이면서 ORDER BY에 빠져 있어 별도의 정렬 작업이 불가피하다.
7. 인덱스 적용 여부 최종 판단 흐름도¶
WHERE, GROUP BY, ORDER BY 절이 복합된 쿼리를 작성할 때는 아래 3가지 질문을 순서대로 던져보아야 한다.
WHERE절이 인덱스를 사용할 수 있는가?GROUP BY절이 인덱스를 사용할 수 있는가?GROUP BY절과ORDER BY절이 동시에 인덱스를 사용할 수 있는가?
마치며¶
SELECT 쿼리 성능의 핵심은 불필요한 테이블 풀 스캔을 없애는 것뿐만 아니라, 인메모리 또는 디스크 정렬(Filesort) 및 임시 테이블(Using temporary) 생성을 방지하는 데 있다. 복합 인덱스의 컬럼 순서와 각 절의 문법적 특성을 고려하여 인덱스를 설계하면 쿼리 응답 속도를 극적으로 개선할 수 있다.