성능에 미치는 영향이 큰 실행 계획과 연관이 있는 단위 작업에 대해 설명하는 포스트이다.
하기 내용 중 풀 테이블 스캔을 제외한 나머지는 모두 스토리지 엔진이 아니라 MySQL 엔진에서 처리된다.
MySQL 엔진에서 부가적으로 처리한느 작업은 대부분 성능에 미치는 영향력이 크며, 모두 쿼리 성능을 저하시키는데 한몫하는 작업이다.
6.3.1 풀 테이블 스캔
풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어 요청된 작업을 처리하는 것을 말한다. 주로 아래의 조건에서 풀 테이블 스캔을 선택한다.
- 테이블의 레코드 건수가 너무 적어 인덱스를 통해 읽는 것 보다 풀 테이블 스캔을 통해 스캔하는 것이 더 빠른 경우(페이지 1개로 구성된 경우)
- WHERE절이나 ON 절에 인덱스를 사용할 수 있는 적절한 조건이 없는 경우
- 인덱스 레인지 스캔을 사용할 수 있는 쿼리라 하더라도, 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(B-Tree 샘플링 통계 정보 기준)
- 반대로, max_seeks_for_key 변수를 특정 값(N) 으로 설정하면, MySQL 옵티마이저는 커디널리티나 설렉티비티를 무시하고 최대 N건만 읽으면 된다고 판단한다, 이 값이 작을 수록 MySQL 서버가 인덱스를 사용하도록 유도할 수 있다
일반적으로 풀 테이블 스캔 실행시 한 번에 여러 개의 블록이나 페이지를 읽어올 수 있으며, 그 수를 조절할 수 있다.(MySQL에는 없다ㅎㅎ) InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어해드(Read ahead) 작업이 자동으로 시작된다.
리드 어헤드? : 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해 요청이 오기 전 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미함 풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드(Foreground thread)가 페이지 읽기를 실행하지만, 특정 시점 부터는 읽기 작업을 백그라운드 스레드로 넘긴다. 백그라운드 스레드가 읽기를 넘겨받는 시점부터는 한 번에 4개 / 8개씩의 페이지를 읽으며 그 수를 증가시킨다. 이렇게 많은 데이터 페이지를 읽어 버퍼 풀에 저장해두기 떄문에, 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다가 사용하면 되므로 쿼리가 빨라진다.
6.3.2 ORDER BY 처리 (Using filesort)
정렬을 처리하기 위해서 인덱스를 이용하는 방법과, 쿼리가 실행될 때 Filesort 라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.
인덱스를 이용하는 방법
장점 :
- INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼있어서 순서대로 읽기만 하면 되므로 처리가 빠르다. 단점 :
- INSERT, UPDATE, DELETE 작업시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 인덱스로 인해 더 많은 디스크 공간이 필요하며, 인덱스 개수가 늘어날수록 InnoDB의 버퍼 풀이나 MyISAM의 키 캐시용 메모리가 많이 필요하다.
Filesort를 이용하는 방법
장점 :
- 인덱스를 생성하지 않아도 되므로, 인덱스를 이용할 때의 단점이 장점으로 바뀐다.
- 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다. 단점 :
- 정렬이 쿼리 실행시 처리되므로, 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느려진다.
아래의 조건에서는 인덱스를 이용해 정렬하기 적합하지 않은 케이스다 :
- 정렬 기준이 너무 많아 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
- GROUP BY의 결과 또는 DISTINCT와 같은 처리의 결과를 정렬해야 하는 경우
- UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
- 랜덤하게 레코드를 가져와야 하는 경우(이 케이스는 인덱스를 이용하는 방법으로도 적용할 수 있다)
그럼 MySQL이 인덱스를 이용하지 않고 별도의 정렬처리를 진행했는지에 대한 여부는 어떻게 판단할까? 그 부분은 실행 계획의 Extra 컬럼의 Using filesort가 기재되어 있는지 확인하면 된다.
소트 버퍼(Sort buffer)
MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 공간을 소트 버퍼라고 한다. 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야 할 레코드에 따라 가변적으로 증가하지만, 최대 값은 sort_buffer_size로 설정할 수 있다. 서트 버퍼를 위한 메모리 공간은 쿼리의 실행이 완료되면 즉시 시스템으로 반납된다.
만일, 정렬해야 할 레코드가 소트 버퍼에 할당된 공간보다 크다면??
정렬해야 할 레코드를 여러 조각으로 나누어 처리하며, 이 과정에 임시 저장을 위해 디스크를 사용한다. MySQL은 메모리의 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록해둔다. 그 후, 그 다음 레코드를 가져와 다시 정렬해 반복적으로 디스크에 임시저장한다.
이 과정에서 디스크의 쓰기와 읽기를 유발하기 때문에, 레코드 건수가 많으면 많을 수록 반복 작업의 횟수가 많아진다.
소트 버퍼의 크기는 56KB~1MB 미만이 적절하다. 소트 버퍼는 세션 메모리 영역에 포함되며, 클라이언트가 공유해서 사용하는 영역이 아니기 때문에 커넥션이 많아질 수록 소트 버퍼가 반복해서 생성될 수 있기 떄문이다.
정렬 알고리즘
레코드 정렬시, 레코드 전체를 소트 버퍼에 담을지, 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 두가지로 정렬 알고리즘을 나눠볼 수 있다.
싱글 패스 (Single pass) 알고리즘
소트 버퍼에 정렬 기준 컬럼을 포함해 SELECT 되는 컬럼을 모두 담아 정렬을 수행하는 방법이다.(MySQL 5.0 이상에서만 가능) 정렬이 완료되면 소트 버퍼의 내용을 그대로 클라이언트에게 넘겨준다. 아래의 기준을 충족할 때에만 투 패스 알고리즘을 사용한다 :
- 레코드의 크기가 max_length_for_sort_data 파라미터로 설정된 값보다 클 때
- BLOB, TEXT 타입의 컬럼이 SELECT에 포함될 때
투 패스 (Two pass) 알고리즘
정렬 대상 컬럼과 PK값만 소트 버퍼에 담아 정렬을 수행하고, 정렬된 순서대로 다시 PK키를 통해 테이블에서 데이터를 읽어 SELECT할 컬럼을 가져오는 알고리즘이다.(예전 MySQL에서 사용하던 방법) 투 패스 알고리즘은 같은 레코드를 두 번 읽어야 하는 부분이 있다. 하지만 소트 버퍼의 크기를 그만큼 절약할 수 있다는 장점이 있다.
정렬의 처리 방식
쿼리 안에 ORDER BY가 있다면 아래 세가지 방식중 하나로 정렬이 처리된다.(아래로 갈수록 속도가 느리다)
- 인덱스를 사용한 정렬
- 드라이빙 테이블만 정렬 (Extra 컬럼에 “Using filesort” 기재됨)
- 조인 결과를 임시 테이블로 저장한 후, 임시 테이블에서 정렬 (Extra 컬럼에 “Using temporary; Using filesort”로 기재됨)
옵티마이저는 먼저 인덱스를 이용할 수 있는지 파악한 후, 사용가능할 경우 인덱스를 사용하고 없을 경우 WHERE 조건절에 만족하는 레코드를 정렬 버퍼에 저장하여 정렬을 처리(Filesort)한다. 이 때, 옵티마이저는 정렬 대상 레코드를 최소화 하기 위해 아래 두가지 방식중 하나를 선택한다.
- 드라이빙 테이블만 정렬한 후 조인 수행
- 조인이 끝나고 일치하는 모든 레코드를 가져온 후 정렬 수행
일반적으로는 조인이 수행되며 레코드 건수는 배로 불어나기 때문에, 드라이빙 테이블만 선정렬 하여 처리하는 방식이 더 효율적이다.
인덱스를 이용한 정렬
인덱스를 이용한 정렬을 위해서는 ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블(드라이빙 테이블)에 속하고, ORDER BY순서대로 인덱스가 있어야 한다.
WHERE 절에 컬럼에 대한 조건이 있다면, 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있도록 해야 한다. 그리고 B-Tree(인덱스가 키값으로 정렬됨)가 아닌 계열의 인덱스는 이 방법을 적용할 수 없다.
여러 테이블이 조인되는 경우 NL Join에서만 이 방식을 사용할 수 있다.(NL Join만 드라이빙 테이블의 인덱스 읽기 순서가 흐트러지지 않는다)
주의 ORDER BY를 넣지 않아도 자동으로 정렬된다고 해서 ORDER BY 절 자체를 쿼리에서 빼버리지는 말자. MySQL은 정렬을 인덱스로 처리 할 수 있다면 불필요한 정렬 작업을 수행하지 않기 때문에, 보다 명시적으로 표현하기 위해 ORDER BY를 쿼리에 기재해두자.
드라이빙 테이블만 정렬
인덱스를 이용해 정렬을 수행하지 못하고, 아래의 조건을 만족하는 경우 드라이빙 테이블만 선정렬하여 처리하는 방식으로 진행된다 :
- WHERE 절의 조건이 선행 테이블의 PK를 이용하였다.
- 드리븐 테이블의 조임컬럼이 인덱스로 설정되어 있다.
임시 테이블을 이용한 정렬
쿼리가 여러 테이블을 조인하지 않고, 하나의 테이블로부터 SELECT해 정렬하는 경우라면 임시 테이블이 필요하지 않다. 하지만, 2개 이상의 테이블을 조인해 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수도 있다. 드리븐 테이블의 컬럼 기준으로 정렬을 해야 할 경우 이 케이스가 적용될 가능성이 높다.
정렬 방식의 성능 비교
주로 웹서비스용 쿼리에서는 ORDER BY와 LIMIT이 함께 쓰이는 경우가 많다. LIMIT를 사용한다고 하더라도 결국은 ORDER BY를 먼저 거쳐야 하므로, 잘못된 쿼리의 경우 항상 느려질 수 밖에 없다.
스트리밍 방식
- 서버 쪽에서 처리해야할 데이터가 얼마나 될지 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트로 전송해주는 방식을 의미한다.
- 웹서비스와 같은 OLTP 환경에서는 쿼리의 요청에서부터 첫번째 레코드를 전달받게 되기까지의 응답시간이 중요하다.(얼마나 많은 레코드를 조회하느냐에 관계없다)
버퍼링 방식
ORDER BY, GROUP BY를 사용하면 쿼리의 결과를 스트리밍 할 수 없게 된다. 조건에 맞는 레코드를 필터링 한 후, 정렬이나 그룹핑 작업을 거쳐야 하기 때문이다. MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안, 클라이언트는 대기상태에 있기 때문에 응답속도가 느려진다. 이를 스트리밍의 반대 표현으로 버퍼링이라고 표현한다.
앞서 말한 정렬의 세가지 방식 중, 인덱스를 이용한 정렬만 스트리밍 방식으로 처리되며, 나머지는 버퍼링 방식으로 정렬된다.
정렬 관련 상태 변수
MySQL 서버는 처리하는 주요 작업에 대해 해당 작업의 실행 횟수를 상태 변수로 저장하고 있다.
SHOW SESSION STATUS LIKE 'Sort%';
위와 같이 검색하면 Sort_merge_passes, Sort_range, Sort_rows, Sort_scan과 같은 상태 변수 값을 보여주며 각각 의미하는 바는 아래와 같다 :
Sort_merge_passes: 멀티 머지 처리 횟수Sort_range:인덱스 레인지 스캔을 통해 검색된 결과의 정렬 작업 횟수Sort_rows: 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수Sort_scan: 지금까지 정렬한 전체 레코드 건수
6.3.3 GROUP BY 처리
GROUP BY 또한 ORDER BY처럼 스트리밍 처리를 할 수 없게하는 요소중 하나이다. GROUP BY 사용시 처리 결과를 임시 테이블이나 버퍼에 담고, HAVING을 이용해 필터링을 진행한다. GROUP BY는 인덱스를 이용할 수도 있고, 그렇지 않은 방식이 있다.
인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)
ORDER BY와 마찬가지로 조인의 드라이빙 테이블에 속한 컬럼만 이용해 그루핑 할 때, GROUP BY 컬럼으로 이미 인덱스가 있다면, 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 겨로가를 조인 처리한다. GROUP BY가 인덱스를 사용한다고 해도 그룹 함수등 그룹 값을 처리해야 해서 임시 테이블이 필요할 때도 있다.
루스(loose) 인덱스 스캔을 이용하는 GROUP BY
인덱스의 레코드를 건너 뛰면서 필요한 것만 가져오는 방식이다. 루스 인덱스 스캔은 인덱스의 유니크한 값의 수가 적을수록 성능이 향상된다.(분포도가 좋지 않은 인덱스)
임시 테이블을 사용하는 GROUP BY
GROUP BY 기준 컬럼이 드라이빙 테이블에 있든, 드리븐 테이블에 있던 관계없이 인덱스를 전혀 사용하지 못할 때 이 방식으로 처리된다.
6.3.4 DISTINCT 처리
특정 컬럼의 유니크한 값을 조회하려면 SELECT DISTINCT를 사용한다.
SELECT DISTINCT…
단순히 SELECT 되는 레코드 중, 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용한다. 이 경우에는 GROUP BY와 같은 방식으로 처리되며, 차이는 정렬만 보장이 되지 않는다는 것이다. (하지만 이마저도 인덱스로 정렬한다면 차이가 없다) 그리고 아래와 같은 케이스를 조심하자.
SELECT DISTINCT first_name, last_name FROM employees; -- DISTINCT (first_name + last_name)이다, first_name만 distinct 한 것이 아님을 주의하자.
SELECT DISTINCT(first_name), last_name FROM employees; -- DISTINCT는 괄호를 무시하며 제거한다, 위와 같은 쿼리이다.
집합 함수와 함께 사용된 DISTINCT
집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 컬럼 값이 유니크한 것들을 가져온다.
EXPLAIN
SELECT COUNT(DISTINCT s.salary)
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;
위의 쿼리는 내부적으로 COUNT(DISTINCT s.salary) 를 처리하기 위해 임시 테이블을 사용한다. 하지만 실행계획을 보면 Extra 컬럼에 Using temporary가 표시되지 않는다.
만일 쿼리를 아래와 같이 변경한다면?
EXPLAIN
SELECT COUNT(DISTINCT s.salary), COUNT(DISTINCT e.last_name)
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;
카운트 함수가 두 번 사용되었음에도 불구하고 쿼리 실행계획은 첫번째 쿼리와 동일하다. 하지만 실질적으로 2개의 임시테이블을 사용한다.
SELECT COUNT(DISTINCT emp_no) FROM employees;
SELECT COUNT(DISTINCT emp_no) FROM dept_emp GROUP BY dept_no;
위와 같이 인덱스된 컬럼의 DISTINCT 처리를 할 때에는 인덱스를 풀 스캔하거나, 레인지 스캔하며 임시 테이블 없이 최적화된 쿼리를 수행할 수 있다.
6.3.5 임시 테이블(Using temporary)
MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그룹핑할 때는 내부적인 임시 테이블을 사용한다. 이 임시 테이블은 다른 세션이나 다른 쿼리에서는 볼 수 없으며 사용하는 것도 불가능하다. 사용자가 생성한 임시테이블과 달리 내부적인 임시 테이블은 쿼리 처리가 완료되면 자동으로 삭제된다.
임시 테이블이 필요한 쿼리
인덱스를 사용하지 못하는 케이스나, 아래와 같은 상황에서는 내부 임시 테이블을 생성한다 :
- ORDER BY, GROUP BY에 명시된 컬럼이 다른 쿼리 (유니크 인덱스 포함)
- ORDER BY, GROUP BY에 명시된 컬럼이 조인 순서상 첫번째 테이블이 아닌 쿼리 (유니크 인덱스 포함)
- DISTINCT, ORDER BY가 동시에 존재하는 경우나 DISTINCT를 인덱스로 처리하지 못하는 쿼리 (유니크 인덱스 포함)
- UNION, UNION DISTINCT가 사용된 쿼리 (유니크 인덱스 포함)
- UNION ALL이 사용된 쿼리 (유니크 인덱스 미포함)
- 쿼리의 실행계획에서 select_type이 DERIVED인 쿼리 (유니크 인덱스 미포함)
보통은 실행계획의 Extra컬럼에서 “Using temporary”라는 키워드를 통해 임시 테이블이 사용되었는지 확인할 수 있으며, 위 3개 패턴의 경우 “Using temporary”가 표시되지 않았더라도 임시 테이블을 사용하게 된다.
임시 테이블이 디스크에 생성되는 경우(MyISAM 스토리지 엔진 사용)
내부 임시 테이블은 기본적으로 메모리에 생성되지만, 아래 조건을 만족하면 메모리에 임시 테이블을 생성할 수 없어 디스크상에 MyISAM 테이블로 만들어진다.
- 임시 테이블에 저장하는 내용 중 BLOB, TEXT와 같은 대용량 컬럼이 있는 경우
- 임시 테이블에 저장해야 하는 레코드의 전체 크기나 UNION, UNION ALL에서 SELECT 되는 컬럼 중 길이가 512바이트 이상인 경우
- GROUP BY, DISTINCT 컬럼에서 512바이트 이상인 크기의 컬럼이 있는 경우
- 임시 테이블에 저장할 데이터의 전체 크기가 tmp_table_size or max_heap_table_size 시스템 설정 값보다 큰 경우
즉, 임시 테이블에 저장되는 데이터가 과도하게 클 경우 디스크에 저장된다고 볼 수 있다.
임시 테이블 관련 상태 변수
물론 실행 계획을 통해서 임시 테이블을 사용했다는 사실을 확인할 수 있지만, 임시 테이블이 메모리에 생성되었는지 디스크에 생성되었는지는 확인할 수 없다.
이를 위해 MySQL 서버의 상태변수를 통해 임시 테이블이 어디에 생성되었는지 확인할 수 있다.
임시 테이블 관련 주의사항
레코드 건수가 많지 않으면 보통 메모리에 임시 테이블이 생성되고, MySQL 서버에 크게 영향을. 끼치지 않는다. 하지만 내부 임시 테이블이 MyISAM 테이블로 디스크에 생성될 경우 이슈가 발생할 수 있다. 아래의 케이스를 보자.
SELECT * FROM employees
GROUP BY last_name
ORDER BY first_name;
위 쿼리는 GROUP BY, ORDER BY 컬럼이 다르고, last_name 인덱스가 없어 임시 테이블과 정렬까지 수행해야 한다. 이 쿼리의 실행 프로세스는 아래와 같아.
- Employees 테이블의 모든 컬럼을 포함한 임시 테이블 생성
- Employees로부터 첫번째 레코드를 InnoDB 스토리지 엔진으로부터 가져와, 임시 테이블에 같은 last_name이 있는지 확인
- 없으면 임시 테이블에 INSERT, 없으면 업데이트 또는 무시
- 임시 테이블의 크기가 특정 크기보다 커지면 임시 테이블을 MyISAM 테이블로 디스크를 이동
- Employees 테이블에서 더이상 읽을 레코드가 없을 때까지 2~4과정 반복
- 최종 내부 임시 테이블에 저장된 결과에 대해 정렬 작업 수행
- 클라이언트에 결과 반환
물론 임시 테이블이 메모리에 있다면 크게 문제는 없겠지만, 디스크에 저장되어 있다면 몇십만건을 저장하기 위해 디스크에 접근하는 것 자체가 성능에 부하를 줄 수 있다. 가능하다면 인덱스를 통해 처리하고, 처음부터 임시 테이블이 필요하지 않게 만드는 것이 가장 좋다.
그리고 임시 테이블이 MEMORY(HEAP) 테이블로 물리 메모리에 생성되는 경우에도 주의해야 할 사항이 있다. MEMORY(HEAP) 테이블의 모든 컬럼은 고정 크기 컬럼이라는 것이다. first_name 컬럼이 VARCHAR(512)라면, 해당 컬럼의 값이 한글자던 두글자던 관계없이 모두 512*3(utf-8기준) 바이트를 차지한다. 그래서 SELECT하는 컬럼은 최소화하고(BLOB, Text 제외), 컬럼의 데이터 타입 선정도 가능한 작게 해주는 것이 좋다.
6.3.6 테이블 조인 방식
MySQL은 다른 DBMS보다 조인을 처리하는 방식이 단순하다.
조인 종류
조인의 종류는 크게 아래와 같이 나눌 수 있다 : INNER JOIN OUTER JOIN : LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
그 외에도 조건을 어떻게 명시하느냐에 따라 NATURAL JOIN, CROSS JOIN으로도 구분할 수 있다. 조인의 처리에서 어느 테이블을 먼저 읽을지를 결정하는 것은 중요하며, 그에 따라 작업량이 달라진다(INNER JOIN은 어느 테이블을 먼저 읽어도 결과가 달라지지 않아 MySQL 옵티마이저가 자동으로 최적화를 수행한다)
JOIN (INNER JOIN)
드라이빙 테이블의 조인 컬럼과 드라이븐 테이블의 조인 컬럼이 동시에 존재하는 경우(?), 맵핑이 가능한 경우에만 반환할 수 있는 케이스다.
OUTER JOIN
드라이븐 테이블에서 일치하는 레코드를 찾지 못할 경우 NULL로 반환한다. 조인 순서에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN(MySQL에서는 지원하지 않음)으로 나눌 수 있다.
