Select 튜닝

개요

Insert나 Update 작업은 대부분의 경우 레코드 단위로 발생하기 때문에 성능상 문제가 되는 경우는 별로 없습니다. 하지만 Select는 주어진 조건에 따라 여러개의 테이블에서 데이터를 조합해서 가져와야합니다. 즉, 여러개의 테이블을 어떻게 읽을 것인가에 대한 주의가 필요합니다.

Select의 처리 순서

기본순서

각 쿼리절의 실행순서를 도식화한 그림입니다. CTE(with 절)와 윈도우 함수를 제외하고, 순서가 바뀌어서 실행되는 형태의 쿼리는 거의 없습니다. 또한 ORDER BY나 GROUP BY가 있어도 인덱스를 이용해 처리할 때는 생략되어 실행됩니다.

예제 쿼리로 확인해보겠습니다.

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	6
FROM 절	FROM salaries s INNER JOIN employees e ON e.emp_no=s.emp_no	1(from) ,3(join)
WHERE 절	WHERE s.emp_no IN (100001, 100002)	2
GROUP BY 절	GROUP BY s.emp_no	4
HAVING 절	HAVING AVG(s.salary) > 1000	5
ORDER BY 절	ORDER BY AVG(s.salary)	7
LIMIT 절	LIMIT 10;	8

각 절의 실행 계획을 풀어서 보면 다음과 같습니다.

이름	수행 동작
FROM	`salaries`와 `employees` 테이블을 읽음
JOIN	`emp_no`를 기준으로 두 테이블을 결합
WHERE	`emp_no`가 100001 또는 100002인 행을 필터링
GROUP BY	`emp_no`를 기준으로 그룹화
HAVING	그룹화된 각 `emp_no`의 평균 `salary`가 1000 이상인 그룹을 필터링
SELECT	`emp_no`와 `DISTINCT first_name`의 개수를 선택
ORDER BY	`salary`를 기준으로 정렬
LIMIT	결과 행을 10개로 제한

조인보다 먼저 ORDER BY가 실행되는 경우

예외적으로 ORDER BY가 조인보다 먼저 실행되는 경우입니다. 첫번째 테이블만 읽어서 정렬을 수행한 뒤, 나머지 테이블을 읽습니다. 주로 GROUP BY 절 없이 ORDER BY만 사용된 쿼리에서 사용되는 순서입니다.

순서를 다르게 실행하고 싶다면? 인라인 뷰!

SELECT emp_no, cnt
FROM (
    SELECT s.emp_no, COUNT(DISTINCT e.first_name) AS cnt, MAX(s.salary) AS max_salary
    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 MAX(s.salary) > 1000
    LIMIT 10
) temp_view
ORDER BY max_salary;

위의 실행순서를 벗어나는 쿼리가 필요할 경우 서브 쿼리로 작성된 인라인 뷰를 사용해야합니다. 예시로 LIMIT를 먼저 적용하고 ORDER BY를 실행하고 싶다면 위와 같이 인라인 뷰를 적용해야합니다.

하지만 인라인뷰를 사용하면 임시 테이블을 쓰기 때문에 주의해야합니다.

CTE가 포함될 경우
WITH 절(CTE, Common Table Expression)은 항상 제일 먼저 실행되어 임시 테이블로 저장됩니다.

인덱스 사용

개요

WHERE, GROUP BY, ORDER BY 절에서 어떤 요건을 갖췄을 때 인덱스를 사용할 수 있는지 자세히 알아보겠습니다.

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

1원칙, 변형 금지

인덱스 칼럼의 값을 변형하지 말아야합니다.
인덱스는 칼럼의 값을 아무런 변환 없이 B-Tree에 정렬해서 저장합니다. WHERE, GROUP BY, ORDER BY 모두 원본값을 검색하거나 정렬할 때만 B-Tree에 저장된 인덱스를 이용합니다. 다음 예제와 같이 인덱스로 지정된 칼럼의 값을 변형한 후 다른 값과 비교하면 쿼리는 인덱스를 사용할 수 없습니다.

SELECT * FROM salaries WHERE salary*10 > 10000;

때문에 다음과 같이 변형하여 사용해야합니다.

SELECT * FROM salaries WHERE salary > 10000/10;

복잡한 연산을 수행한다거나, 해시 값을 만들어서 비교해야하는 경우
2가지 해결방법이 존재합니다.
1: 미리 계산된 값을 저장하도록 MYSQL의 가상 칼럼을 추가하고 그 칼럼에 인덱스를 생성하는 방법
2: 함수 기반의 인덱스 사용하기

비교 대상값은 같은 타입이어야합니다.

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;

select 쿼리의 실행계획을 확인해봅시다.

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tb_test	NULL	index	ix_age	ix_age	11	const	1	Using where

age라는 칼럼에 인덱스가 있어서 type칼럼에 ref나 range가 표시되어야하지만, 실제로는 Index(인덱스 풀 스캔)라고 표시합니다.

인덱스 레인지 스캔을 사용하지 못하고 인덱스를 풀 스캔한 이유는 age칼럼의 데이터 타입과 비교되는 값의 데이터 타입이 다르기 때문입니다. 비교되는 값의 타입이 서로 다를 경우 옵티마이저가 내부적으로 문자열을 숫자 타입으로 변환한 후 비교 작업을 처리합니다.

실제 인덱스에 저장된 값은 문자열이고 이를 정수로 변환하는 과정에서 인덱스를 사용하지 못하게 됩니다. 이 현상을 예방하기 위해서는 쿼리를 다음과 같이 변경하여 해결할 수 있습니다.

SELECT * FROM tb_test WHERE age='2';

저장하고자 하는 값의 타입에 맞춰 칼럼의 타입을 선정해야합니다.

WHERE 절에서 인덱스를 사용하는 방법

크게 작업 범위 결정 조건과 체크 조건의 두 가지 방식으로 구분합니다.

작업 범위 결정 조건

작업 범위 결정 조건은 WHERE 절에서 동등 비교 조건이나 IN으로 안의 조건에 사용된 칼럼들이 실제 인덱스 칼럼 구성과 비교할 때 얼마나 일치하는지에 따라 달라집니다.

위 그림과 같이 WHERE 절에 나열된 순서가 인덱스와 다르더라도 옵티마이저는 인덱스를 사용할 수 있는 조건들을 뽑아서 최적화를 수행할 수 있습니다. 점선 표기가 체크 조건, 실선 표기가 작업 범위 결정 조건입니다.
col_1과 col_2는 동등 비교 조건이며, col_3의 조건이 범위 비교 조건이기 때문에 col_4는 작업 범위 결정 조건으로 사용되지 못하고, 체크 조건으로 사용됩니다.

인덱스 순서상, col_4이 직전 칼럼인 col_3가 동등 비교 조건이 아니라, 범위 비교 조건으로 사용되기 때문입니다.