본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
MySQL 주요 처리방식
이번 포스팅에서는 성능에 미치는 영향이 큰 실행 계획과 연관이 있는 단위 작업에 대해 살펴보고자 한다.
설명하는 내용중 풀 테이블 스캔을 제외한 나머지는 모두 스토리지 엔진이 아니라 MySQL 엔진에서 처리되는 내용이다. 또한 MySQL 엔진에서 부가적으로 처리하는 작업은 대부분 성능에 미치는 영향이 큰데, 모두 쿼리의 성능을 저하시키는 데 한몫을 하는 작업들이다.
MySQL 엔진에서 처리하는 데 시간이 오래 걸리는 작업의 원리를 알아둔다면 쿼리를 튜닝하는데 상당히 많은 도움이 될 것이다.
풀 테이블 스캔
인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 처리하는 작업을 의미한다. MySQL 옵티마이저는 다음과 같은 조건이 일치할 때 주로 풀 테이블 스캔을 선택한다.
- 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우 ( 일반적으로 테이블이 페이지 1개로 구성된 경우)
- WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
- 인덱스 레인지 스캔을 사용할 수 있는 쿼리라 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)
- max_seeks_for_key 변수를 특정 값으로 설정하면 옵티마이저는 인덱스의 선택도나 기수성(Cardinality)를 무시하고 특정 값 만큼만 읽으면 된다고 판단한다. 이 값을 적게 설정할수록 MySQL 서버가 인덱스를 더 사용하도록 유도한다.
테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 많은 디스크 읽기가 필요하다. 대부분의 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능이 있다.
InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read Ahead) 작업이 자동으로 시작된다. 리드 어헤드란. 어떤 영역의 데이터가 앞으로 필요할 것을 예측해 요청전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미한다.
즉, 풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운 스레드(클라이언트 스레드)가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다. 한번에 최대 64개 데이터 페이지까지 읽어서 버퍼 풀에 저장해 둔다. 이로인해 쿼리가 상당히 빨리 처리된다.
ORDER BY 처리(Using filesort)
정렬을 처리하기 위해서는 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesort라는 별도의 처리를 이용하는 방법으로 나뉜다.
1) 인덱스를 이용한 정렬
장점 - INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼어 있어 순서대로 읽기만 하면 된다.
단점 - INSERT, UPDATE, DELTE 작업시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 인덱스 때문에 디스크 공간이 많이 필요하다. 인덱스 개수가 늘어날 수록 InnoDB의 버퍼 풀이나 MyISAM 키 캐시용 메모리가 많이 필요하다.
2) Filesort 이용
장점 : 인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀐다. 정렬해야할 레코드가 많지 않으면 메모리에서 Filesort 가 처리되므로 충분히 빠르다.
단점 : 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리 응답 속도가 느리다.
인덱스 정렬을 튜닝하기 어려운 상황은 다음과 같다
- 정렬 기준이 너무 많아 요건별로 모두 인덱스 생성이 불가능한 경우
- GROUP BY의 결과 또는 DISTINCT와 같은 처리의 결과를 정렬해야 하는 경우
- UNION과 같이 임시 테이블을 다시 정렬해야 하는 경우
- 랜덤하게 결과 레코드를 가져와야 하는 경우
소트 버퍼(Sort buffer)
MySQL은 정렬을 수행하기 위해 메모리 공간을 할당받아서 사용한다. 이 메모리 공간을 소트 버퍼라고 한다. 소트 버퍼는 필요한 경우에만 할당되며, 버퍼의 크기는 정렬할 레코드 크기에 따라 가변적으로 증가하지만 최대 사용가능한 소트버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있다. 소트 버퍼는 실행 시간이 완료되면 시스템으로 반납된다.
정렬이 문제가 되는 상황들을 살펴보자. 정렬할 레코드 양이 소량인 경우 메모리의 소트 버퍼만으로 정렬할 수 있다면 정렬이 빠르게 처리된다. 하지만 레코드 건수가 소트 버퍼로 할당된 공간보다 크다면 MySQL은 레코드를 여러 조각으로 처리하는데 이 과정에서 임시 저장을 위해 디스크를 사용한다.
1) 메모리의 소트 버퍼에서 정렬을 수행
2) 결과를 임시로 디스크에 저장
3) 다음 레코드 와서 다시 정렬해서 반복적으로 디스크에 임시 저장
위 과정과 같이 버퍼 크기 만큼씩 정렬된 레코드를 다시 병합하면서 정렬을 수행한다. 병합 작업을 멀티 머지(Multi-merge)라고 표현하며, 수행된 멀티 머지 횟수 Sort_merge_passes라는 상태 변수(SHOW STATUS VARIABLES;) 에 누적된다.
이 작업은 모두 디스크의 쓰기와 읽기를 유발하며, 레코드 건수가 많을수록 반복 작업의 횟수가 많아진다. 소트 버퍼를 크게 설정하면 디스크를 사용하지 않아 빨라질 것으로 예상되지만 실제 벤치마크 결과는 거의 차이가 없다.
MySQL은 글로벌 메모리 영역과 세션(로컬) 메모리 영역으로 나눠서 생각할 수 있는데, 정렬을 위해 할당받는 소트 버퍼는 세션 메모리에 해당한다. 즉 소트 버퍼는 클라이언트가 공유해서 사용하는 영역이 아니다. 커넥션이 많으면 많을 수록 소트 버퍼로 소비되는 메모리 공간이 커진다.
정렬 알고리즘
레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지, 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 2가지 정렬 알고리즘으로 나눠진다.
1) 싱글 패스(Single pass) 알고리즘
소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 되는 칼럼 전부를 담아서 정렬을 수행하는 방법이다.
SELECT emp_no, first_name, last_name
FROM employees
ORDER BY first_name;
employees 테이블을 읽을 때 정렬에 필요하지 않은 last_name 칼럼까지 전부 읽어서 소트 버퍼에 담고 정렬을 수행한다. 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트에 넘겨준다.
2) 투 패스(Two pass) 알고리즘
정렬 대상 칼럼과 프라이머리 키값만 소트버퍼에 담아 정렬을 수행하고 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 알고리즘이다. 예전 버전의 MySQL에서 사용하던 방법이다. 하지만 5.5버전에서도 특정 조건이 되면 이 방법을 사용한다.
투 패스 알고리즘은 테이블을 두 번 읽어야 하기 때문에 비효율적이다. 하지만 싱글 패스 알고리즘은 더 많은 소트 버퍼 공간이 필요하다.
최근의 MySQL 5.x 버전에서는 일반적으로 싱글 패스 방식을 사용한다. 그러나 다음과 같은 상황에서는 투 패스 정렬 알고리즘을 사용한다
- 레코드의 크기가 max_length_for_sort_data 파라미터로 설정된 값보다 클 때
- BLOB이나 TEXT 타입의 칼럼이 SELECT 대상에 포함될 때
싱글 패스 알고리즘은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보이며, 투 패스 알고리즘은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이다.
정렬의 처리 방식
쿼리에 ORDER BY가 사용되면 반드시 다음의 3가지 방식 중 하나로 정렬이 처리된다. 밑으로 갈수록 처리가 느려진다.
[인덱스를 사용한 정렬] - 특별한 내용 표기 없음
[드라이빙 테이블만 정렬(조인이 없는 경우 포함)] - "Using filsort"가 표시됨
[조인 결과를 임시 테이블로 저장한 후, 임시 테이블에서 정렬] - "Using temporary; Using filesort"가 같이 표시됨
옵티마이저는 정렬을 위해 인덱스 사용이 가능한지 확인하고 가능하다면 "Filesort" 과정 없이 인덱스 순으로 결과를 반환한다. 인덱스를 사용할수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리(Filesort) 한다. MySQL 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 다음 방법중 하나를 택한다.
1. 드라이빙 테이블만 정렬한 다음 조인을 수행
2. 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행
일반적으로 조인이 수행되면서 레코드 건수는 배수로 불어나기 때문에 드라이빙 테이블만 정렬하고 조인하는 방법이 효율적이다. 3가지 정렬 방법에 대해 알아보자.
1) 인덱스를 이용한 정렬
인덱스를 이용한 정렬을 위해서는 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다. 또한 WHERE 절에 첫 번째 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다. B-Tree 계열이 아닌 해시 인덱스나 전문 검색 인덱스에서는 인덱스를 이용한 정렬을 사용할 수 없다.
인덱스를 이용해 정렬이 처리되는 경우 인덱스의 값이 정렬돼 있기 때문에 인덱스의 순서대로 읽기만 하면 된다. MySQL 엔진에서 별도의 정렬을 위한 추가 작업을 수행하지는 않는다. 아래 예제에서는 employees 테이블의 프라이머리 키를 읽고 다음으로 salaries 테이블을 조인했기 때문에 정렬이 되었다.
SELECT * FROM employees e, salaries s
WHERE s.emp_no=e.emp_no
AND e.emp_no BETWEEN 100002 AND 100020
ORDER BY e.emp_no;
인덱스를 사용한 정렬이 가능한 이유는 B-Tree 인덱스가 키값으로 정렬돼 있기 때문이다. 또한 조인이 네스티드-루프 방식으로 실행되기 때문에 드라이빙 테이블의 인덱스 읽기 순서가 흐트러지지 않는다. 하지만 조인이 사용된 쿼리의 실행 계획에 조인 버퍼(Join Buffer)가 사용되면 순서가 흐트러질 수 있다.
2) 드라이빙 테이블만 정렬
조인이 수행되면 결과 레코드 건수가 몇배로 불어난다. 그래서 조인 실행전 첫 번째 테이블(드라이빙) 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이 될 것이다.
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;
우선 WHERE 절의 조건이 다음의 두가지 조건을 가지고 있기 때문에 옵티마이저는 employees 테이블을 드라이빙 테이블로 선택할 것이다.
1.WHER절의 검색 조건 ("emp_no BETWEEN 100001 AND 100010")은 employees 테이블의 프라이머리 키를 이용해 검색하면 작업량을 줄일 수 있다.
2.드리븐 테이블(salaries)의 조인 칼럼인 emp_no 칼럼에 인덱스가 있다.
ORDER BY 절에 명시된 employees 테이블의 프라이머리 키와 연관이 없어 인덱스를 이용한 정렬이 불가능하다. ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블(employees)에 포함된 칼럼임을 알 수 있다. 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 수행하고, 그 결과와 salaries 테이블을 조인한다.
A. 인덱스를 이용해 emp_no BETWEEN 100001 AND 100010 조건을 만족하는 9건을 검색
B. 검색 결과를 last_name 칼럼으로 정렬 수행(Filesort)
C. 정렬 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행 86건의 최종결과를 가져옴.
3) 임시테이블을 이용한 정렬
쿼리가 2개 이상의 테이블을 조인해서 정렬해야 한다면 임시 테이블이 필요할 수도 있다. 드라이빙 테이블 정렬은 2개 이상의 테이블이 정렬이 실행되지만 임시 테이블은 사용하지 않는다. 하지만 그 밖의 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거친다.
이 방법은 3가지 방법 가운데 정렬해야 할 레코드 건수가 가장 많아지기 때문에 가장 느린 정렬 방법이다.
SELECT * FROM employees e, salaries s
WHERE e.emp_no=s.emp_no AND e.emp_no BETWEEN 100002 AND 100010
ORDER BY s.salary;
위의 쿼리는 ORDER BY 기준 칼럼이 드라이빙 테이블이 아니라 드리븐 테이블(salaries)에 있는 컬럼이다. 정렬 이 수행되기 전에 반드시 salaries 테이블을 읽어야 하므로 이 쿼리는 반드시 조인된 데이터를 가지고 정렬할 수밖에 없다.
쿼리 실행계획을 보면 extra 컬럼에 Using temporary; Using filesort 라는 코멘트가 표시된다. 이는 조인의 결과를 임시 테이블에 저장하고, 그 결과를 정렬 처리 했음을 의미한다.
