내맘대로 정리하는 Real MySQL #6장) 실행계획 - MySQL 쿼리 동작 방식 편

<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^

원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??

'풀 테이블 스캔'을 제외한 나머지는 모두 스토리지 엔진이 아니라 MySQL 엔진에서 처리되는 내용이다. 또한 MySQL 엔진에서 부가적으로 처리하는 작업은 대부분 성능에 미치는 영향력이 큰데, 안타깝께도 모두 쿼리의 성능을 저하시키는 데 한몫하는 작업이다. 스토리지 엔진에서 읽은 레코드를 MySQL 엔진이 아무런 가공 작업도 하지 않고 사용자에게 반환한다면 최상의 성능을 보장하는 쿼리가 되겠지만, 우리가 필요로 하는 대부분의 쿼리는 그렇지 않다. MySQL 엔진에서 처리하는데 시간이 오래 걸리는 작업의 원리를 알아둔다면 쿼리를 튜닝하는데 상당히 많은 도움이 될 것이다.

풀 테이블 스캔

인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어 요청된 작업을 처리하는 작업

풀 테이블 스캔을 사용하는 경우

• 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
• WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 인덱스 레인지 스캔을 사용할 수 있는 쿼리라 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우
• max_seekds_for_key 변수를 특정 값(N)으로 설정하면 MySQL 옵티마이저는 인덱스의 기수성이나 선택도를 무시하고 최대 N건만 읽으면 된다고 판단하게 된다.

InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 background thread에 의해 read ahead 작업이 자동으로 시작된다. 필요한 데이터를 예측해 요청이 오기 전 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 것

ORDER BY

정렬을 처리하기 위해서는 1) 인덱스를 이용하는 방법과 2) 쿼리가 실행될 때 'Filesort'라는 별도의 처리를 이용하는 방법이 있다.

인덱스를 사용하지 않았다면 실행계획 Extra 칼럼에 'Using filesort'가 표시된다.

소트 버퍼

정렬을 수행하기 위해 별도로 할당받는 메모리 공간

정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다면, 임시 저장을 위해 디스크를 사용하게 된다. 각 버퍼 크기 만큼씩 정렬된 레코드를 다시 병합하며 정렬을 수행하게 되고, 이 병합 작업을 multi merge라 한다.
수행된 multi merge 횟수는 Sort_merge_passes라는 세션 변수에 누적된다.

정렬 알고리즘

1. Single PASS : SELECT 되는 칼럼을 전부 담아 정렬을 수행하는 방법
2. Two PASS : 정렬 대상 칼럼과 PK를 담아 정렬하고, 정렬된 순서대로 PK를 이용해 SELECT을 다시 하는 알고리즘

최근 MySQL 버전은 일반적으로 Single PASS 알고리즘을 사용하며 다음 경우에는 Two PASS 알고리즘을 사용한다

• 레코드의 크기가 max_length_fot_sort_data 파라미터로 설정된 값보다 클 때
• BLOB이나 TEXT 타입의 칼럼이 SELECT 대상에 포함될 때

정렬 처리 방식

ORDER BY는 3가지 처리 방식이 있다.

1. 인덱스를 이용한 정렬

   • 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY 순서대로 생성된 인덱스가 있어야 한다.
   • WHERE 절에 첫 번째 읽는 테이블의 칼럼에 대한 조건이 있다면, 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.

2. 드라이빙 테이블만 정렬

   • 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것

   • 예를 들어보면 아래 쿼리에서는 WHERE 검색 조건이 employees 테이블이므로, employees 테이블을 먼저 가져와 last_name으로 정렬하고 salaries의 인덱스를 이용해 salaries와 합칠 수 있다.

     SELECT * FROM employees e, salaries s
     WHERE s.emp_no = e.emp_no
       AND e.emp_no BETWEEN 100002 AND 100010
     ORDER BY e.last_name;

3. 임시 테이블을 이용한 정렬

   • 조인한 결과를 임시테이블에 놓고 정렬하는 작업을 수행하는 경우

     SELECT * FROM employees e, salaries s
     WHERE s.emp_no = e.emp_no
       AND e.emp_no BETWEEN 100002 AND 100010
     ORDER BY s.salary; // 여기가 바꼈다

정렬 방식 성능 비교

• 스트리밍 방식 : MySQL에서 처리해야 할 데이터가 얼마나 될지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식. 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받을 것이다. (나머지 레코드들은 순차적으로 받게된다)
• 버퍼링 방식 : 결과를 모아 MySQL 서버에서 일괄 가공하고 한 번에 돌려주는 방식
• 참고 : JDBC 라이브러리가 버퍼링을 하기 때문에 애플리케이션에서 쿼리를 실행하게 되면 버퍼링 방식처럼 느껴진다.

정렬 관련 상태 변수

SHOW SESSION STATUST LIKE 'Sort%';

• Sort_merge_passes : multi merge 처리 횟수
• Sort_range : 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
• Sort_scan : 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
• Sort_rows : 지금까지 정렬한 전체 레코드 건수

정렬 쿼리 결론

가능하다면 인덱스를 사용한 정렬로 유도하고 그렇지 못한다면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는 것도 좋은 튜닝 방법이다.

GROUP BY

GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.

GROUP BY 처리 방식

GROUP BY 작업도 인덱스를 사용하는 경우와 그렇지 못한 경우로 나눠 볼 수 있다. 인덱스를 이용할 때는 인덱스를 차례대로 이용하는 인덱스 스캔 방법과 인덱스를 건너 뛰는 루스 인덱스 스캔이라는 방법으로 나뉜다. 그리고 인덱스를 사용하지 못하는 쿼리에서 GROUP BY작업은 임시 테이블을 사용한다.

1. 인덱스 스캔을 이용 (타이트 인덱스 스캔)
   • ORDER BY와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그룹핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그룹핑 작업을 수행하고 그 결과로 조인을 처리한다.
2. 루스 인덱스 스캔을 이용
3. 임시 테이블을 이용
   • GROUP BY의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못하는 경우
   • GROUP BY가 임시테이블로 처리되는 경우 사용되는 임시테이블은 항상 유니크 키를 가진다.

DISTINCT

SLECT DISTINCT

gorup by와 거의 같은 방식으로 처리된다.

SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 칼럼에 영향을 미친다. 절대로 SELECT 하는 여러 칼럼 중 일부 칼럼만 유니크하게 조회하는 방법은 없다.

SELECT DISTINCT first_name, last_name FROM employees;

집합 함수와 함께 사용된 DISTINCT

집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼 값들 중에서 중복을 제거하고 남은 값만을 가져온다.

임시 테이블

일반적으로 MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다. 물론 특정 예외 케이스에는 메모리를 거치지 않고 바로 디스크에 임시 테이블이 만들어지기도 한다. 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 사용한다.

특정 조건이 성립하면 사이즈와 관계없이 디스크에 임시 메모리를 만든다

• 임시 테이블에 저장해야 하는 내용 중 BLOB이나 TEXT와 같은 대용량 칼럼이 있는 경우
• 임시 테이블에 저장해야 하는 레코드의 전체 크기나 UNION이나 UNION ALL에서 SELECT 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
• GROUP BY 혹은 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
• 임시 테이블에 저장할 데이터의 전체 크기(데이터의 바이트 크기)가 tmp_table_size 또는 max_heap_table_size 시스템 설정 값보다 큰 경우

임시 테이블이 디스크에 생성됐는지 메모리에 생성됐는지 파악하려면 MySQL 서버의 상태 변수(SHOW SESSION STATUTS LIKE 'Created_tmp%'; 를 확인해보면 된다.

임시테이블이 디스크에 생성되면 성능상 문제가 될 수 있다.

임시테이블이 MEMORY(HEAP) 테이블로 물리 메모리에 생성되는 경우에도 주의해야 할 사항이 있다. MEMORY(HEAP) 테이블의 모든 칼럼은 고정 크기 칼럼이라는 점이다. 만약 칼럼의 타입이 VARCHAR(512)라면 이 칼럼이 차지하는 공간은 512 * 3(utf8인 경우) 바이트가 된다. 임시 테이블의 저장 방식 때문에 SELECT 하는 칼럼은 최소화하고 칼럼의 데이터 타입 선정도 가능한 작게 해주는 것이 좋다.

테이블 조인

조인의 종류는 크게 INNER JOIN과 OUTER JOIN으로 구분할 수 있다.

조인의 처리에서 어느 테이블을 먼저 읽을지 결정하는 것은 상당히 중요하며, 그에 따라 처리할 작업량이 상당히 달라진다. INNER JOIN은 어느 테이블을 먼저 읽어도 결과가 달라지지 않으므로 MySQL 옵티마이저가 조인의 순서를 조절해서 다양한 방법으로 최적화를 수행할 수 있다. 하지만 OUTER JOIN은 반드시 OUTER가 되는 테이블을 먼저 읽어야 하기 때문에 조인 순서를 옵티마이저가 선택할 수 없다.

MySQL에서 조인은 nested loop 방식만 지원한다. nested loop란 일반적으로 프로그램을 작성할 때 FOR나 WHILE과 같은 반복 루프 문장을 실행하는 형태로 조인이 처리되는 것을 의미한다.

LEFT OUTER JOIN과 RIGHT OUTER JOIN은 결국 처리 내용이 같으므로 혼동을 막기 위해 LEFT OUTER JOIN으로 통일해서 사용하는 것이 바람직하다.

어떤 방식으로도 드린 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리한다. 이때 사용되는 메모리의 캐시를 조인 버퍼라고 한다. 조인 버퍼는 join_buffer_size라는 시스템 설정 변수로 크기를 제한할 수 있으며, 조인이 완료되면 조인 버퍼는 바로 해제된다.

일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 흐트러질 수 있음을 기억해야 한다. SQL 쿼리에서 결과의 정렬을 보장하는 방법은 ORDER BY 절을 사용하는 것밖에는 없다는 사실을 잊지 말자.

사실 OUTER JOIN과 INNER JOIN은 실제 가져와야 하는 레코드가 같다면 쿼리의 성능은 거의 발생하지 않는다.
INNER JOIN과 OUTER JOIN으 성능을 고려해서 선택할 것이 아니라 업무 요건에 따라 선택하는 것이 바람직하다.