[Real MySQL] 6장(2) 실행 계획 - MySQL의 주요 처리 방식

들어가기 앞서서

MySQL 엔진에서 부가적으로 처리하는 작업은 대부분 성능에 미치는 영향력이 큰데, 안타깝게도 모두 쿼리의 성능을 저하시키는 데 한몫하는 작업이다.

---

풀 테이블 스캔

실행 조건

MySQL 옵티마이저는 다음과 같은 조건일 때 주로 풀 테이블 스캔을 선택한다.

• 테이블의 레코드 건수가 너무 작은 경우 (일반적으로 테이블이 페이지 1개로 구성된 경우)
• WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 인덱스 레인지 스캔을 사용할 수 있는 쿼리라도, 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우.

 

리드 어헤드

• InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다.
• 리드 어헤드란, 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미.
• 즉, 풀 테이블 스캔이 실행되면 처음 몇개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다.

 

ORDER BY 처리

인덱스를 이용하는 방법

• 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠름
• INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요.
• 인덱스 때문에 디스크 공간이 더 많이 필요.

 

Filesort를 이용하는 방법

• 레코드 대상 건수가 많아질 수록 쿼리의 응답 속도가 느려진다.
• 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠름.

정렬을 인덱스를 이용하도록 튜닝하는 것이 불가능한 경우

• 정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
• GROUP BY의 결과 또는 DISTINCT와 같은 처리의 결과를 정렬해야 하는 경우
• UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우

---

소트 버퍼

• MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 의미.

 

소트 버퍼의 문제점

• 정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다면, 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용한다.
• 이 경우 각 버퍼 크기 만큼씩 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 하는데, 이 병합 작업을 멀티 머지라고 표현한다.

 

정렬 알고리즘

싱글 패스 알고리즘

• 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 되는 칼럼 전부를 담아서 정렬을 수행하는 방법.
• MySQL 5.0 이후 최근 버전에서 도입된 정렬 방법.

 

투 패스 알고리즘

• 정렬 대상 칼럼과 PK만을 소트 버퍼에 담아서 정렬을 수행하고 정렬된 순서대로 다시 PK로 테이블을 읽어서 SELECT 할 칼럼을 가져오는 알고리즘.

 

싱글 패스 vs 투패스 알고리즘

• 투 패스 알고리즘은 테이블을 두 번 읽어야 하기 때문에 상당히 불합리하다.
• 하지만 싱글 패스 알고리즘은 더 많은 소트 버퍼 공간이 필요하다.
• 싱글 패스 알고리즘은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보이며, 투 패스 알고리즘은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적임.
• 일반적으로 싱글 패스 방식을 사용한다. (예외 존재)

 

싱글 패스 방식을 사용하지 못하고 투 패스 정렬 알고리즘을 사용하는 경우

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

 

Tip

SELECT 쿼리에서 꼭 필요한 칼럼만 조회하지 않고 모든 칼럼(*)을 가져오도록 개발할 때가 많다. 하지만, 이는 정렬 버퍼를 몇 배에서 몇 십배까지 비효율적으로 사용하게 만들 가능성이 크다.

따라서 SELECT 쿼리에서 꼭 필요한 칼럼만 조회하도록 쿼리를 작성하는 것이 좋다고 권장.

---

정렬의 처리 방식

• 쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 방식 중 하나로 정렬이 처리됨.
• 일반적으로 아래로 갈수록 처리가 느려짐

 

1. 인덱스를 사용한 정렬

• 실행 계획 Extra 코멘트에 별도의 내용 표기 X
• 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 함.
• 또한 WHERE 절에 첫 번째 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 함.

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

• 실행 계획 Extra 코멘트에 using filesort로 표시
• 조인이 수행되면 레코드의 건수가 몇 배로 불어난다. 그래서 조인을 수행하기 전에, 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이 될 것.
• 조인에서 처음 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY 절이 작성되어야 함.
• 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. 조인 결과를 임시 테이블로 저장한 후, 임시 테이블에서 저장.

• 실행 계획 Extra 코멘트에 using temporray; using filesort로 표시
• 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 하는 경우에, 드라이빙 테이블만 정렬 밖의 패턴의 쿼리에서는 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거침 (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 서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터의 가공 작업을 시작할 수 있다.
• 스트리밍 방식으로 처리되는 쿼리에서 LIMIT와 같이 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄여줄 수 있다.

 

버퍼링 방식

• MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려진다.
• 버퍼링 방식으로 처리되는 쿼리는 먼저 결과를 모아서 MySQL 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야 한다. 그래서 버퍼링 방식으로 처리되는 쿼리는 LIMIT처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않는다.

 

정렬 처리 방식

• 정렬 처리 방식 중에서 1. 인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이며, 나머지는 모두 버퍼링 된 후에 정렬된다.
• 인덱스를 사용한 정렬 방식은 LIMIT로 제한된 건수만큼만 읽으면서 바로바로 클라이언트로 결과를 전송해줄 수 있다. 하지만 인덱스를 사용하지 못하는 경우의 처리는 필요한 모든 레코드를 디스크로부터 읽어서 정렬한 후에야 비로소 LIMIT로 제한된 건수만큼 잘라서 클라이언트로 전송해줄 수 있음.

 

---

GROUP BY 처리

GROUP BY 또한 ORDER BY와 같이 쿼리가 스트리밍 된 처리를 할 수 없게 하는 요소 중 하나이다.

GROUP BY 절이 있는 쿼리에서는 HAVING 절을 사용할 수 있는데, HAVING 절은 GROUP BY 결과에 대해 필터링 역할을 수행한다.

 

방식

• 인덱스를 사용하는 경우
  • 인덱스를 차례대로 이용하는 인덱스 스캔 방법
  • 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔 방법
• 인덱스를 사용하지 못하는 경우

 

인덱스 스캔을 이용하는 GROUP BY (타이트 인덱스 스캔)

• 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그룹핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그룹핑 작업을 수행하고 그 결과를 조인으로 처리한다.
• 이런 그룹핑 방식을 사용하는 쿼리의 실행 계획에서는 Extra 칼럼에 별도로 GROUP BY와 관련된 코멘트 (Using index for group-by)이나 임시 테이블이나 정렬 관련 코멘트(Using temporaray, Using filesort)가 표시되지 않는다.

 

루스 인덱스 스캔을 이용하는 GROUP BY

• 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져오는 것을 의미.

 

임시 테이블을 사용하는 GROUP BY

• GROUP BY의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다.

 

임시 테이블

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그룹핑할 때는 내부적인 임시 테이블을 사용한다.

• 일반적으로 MySQL 에진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨간다.
• 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 이용한다.

 

임시 테이블이 필요한 쿼리

• ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
• ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서 상 첫 번째 테이블이 아닌 쿼리
• DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
• UNION 관련 사용된 쿼리

 

임시 테이블이 디스크에 생성되는 경우

• 임시 테이블에 저장해야 하는 내용 중 BLOB이나 TEXT와 같은 대용량 칼럼이 있는 경우
• 임시 테이블에 저장해야 하는 레코드의 전체 크기나 UNION이나 UNION ALL에서 SELECT 되는 칼럼 중에서 길이가 512바이트 이사의 크기의 칼럼이 있는 경우
• GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우

 

테이블 조인

조인의 종류

INNER JOIN

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

FOR (record 1 IN TABLE1) { 
	FOR (record 2 IN TABLE2) { 
    	IF (record 1.join_column == record2.join_column) { 
        	join_record_found(record1.*, record2.*); 
        } 
        ELSE { 
        	join_record_notfound(); 
        }
     } 
}

• 아우터 테이블은 이너 테이블보다 먼저 읽어야 하며, 조인에서 주도적인 역할을 한다고 해서 드라이빙 테이블이라고도 한다.
• 이너 테이블은 조인에서 끌려가는 역할을 한다고 해서 드리븐 테이블이라고 한다.
• 중첩된 반복 루프에서 최종적으로 선택될 레코드가 안쪽 반복 루프(INNER 테이블)에 의해 결정되는 경우를 INNER JOIN이라고 한다.

 

OUTER JOIN

FOR (record 1 IN TABLE1) { 
	FOR (record 2 IN TABLE2) { 
    	IF (record 1.join_column == record 2.join_column) { 
        	join_record_found(record 1.*, record2.*);
        } 
        ELSE { 
        	join_record_found(record1.*, NULL); 
        } 
    } 
}

 

• TABLE2에 일치하는 레코드가 있으면 INNER JOIN과 같은 결과를 만들어내지만, TABLE2에 조건을 만족하는 레코드가 없는 경우에는 TABLE2의 칼럼을 모두 NULL로 채워서 가져온다.

 

주의 사항

• INNER JOIN은 어느 테이블을 먼저 읽어도 결과가 달라지지 않으므로 MySQL 옵티마이저가 조인의 순서를 조절해서 다양한 방법으로 최적화를 수행할 수 있다. 하지만 OUTER JOIN은 반드시 OUTER가 되는 테이블을 먼저 읽어야 하기 때문에 조인 순서를 옵티마이저가 선택할 수 없다.
• MySQL의 실행 계획은 INNER JOIN을 사용했는지 OUTER JOIN을 사용했는지를 알려주지 않으므로 OUTER JOIN을 의도한 쿼리가 INNER JOIN으로 실행되지는 않는지 주의해야 한다.
• OUTER JOIN에서 레코드가 없을 수도 있는 쪽의 테이블에 대한 조건은 반드시 LEFT JOIN의 ON절에 모두 명시하자. 그렇지 않으면 옵티마이저는 OUTER JOIN을 내부적으로 INNER JOIN으로 변형시켜서 처리해 버릴 수도 있다.

 

INNER JOIN과 OUTER JOIN의 선택

• 성능을 고려해서 선택할 것이 아니라 업무 요건에 따라 선택하는 것이 바람직하다.

---

쿼리 종류별 잠금에 대한 내용은 다음 링크에서 이어집니다.

[Real MySQL 정리] 12장. 쿼리 종류별 잠금 (1) InnoDB의 기본 잠금 방식