옵티마이저
현재 대부분의 DBMS는 비용 기반 최적화(Cost-based optimizer, CBO)
풀 테이블 스캔
테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요하다. 백그라운드 스레드를 의해 리드 어헤드(Read ahead) 빨리 읽을 수 있다.
리드 어헤드 : 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측하고 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것
풀 인덱스 스캔
1
mysql> SELECT count(*) FROM employees;
단순히 레코드 건수만 필요로 하는 쿼리라면 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있기 때문
2~3개 컬럼만 구성되기 때문에 테이블 자체 보다는 용량이 작아서 훨씬 빠른 처리 가능
병렬 처리
MySQL 8.0 에서는 innodb_parallel_read_threads 라는 시스템 변수를 이용해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지 변경할 수 있다.
병렬 처리용 스레드 개수를 아무리 늘리더라도 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수 있다.
ORDER BY 처리 (Using filesort)
| - | 장점 | 단점 |
| 인덱스 이용 | INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠르다. | INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 인덱스 때문에 디스크 공간이 더 많이 필요하다. 인덱스의 개수가 늘어날수록 InnoDB의 버퍼풀을 위한 메모리가 많이 필요하다. |
| Filesort 이용 | 인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때 의 단점이 장점으로 바뀐다. 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort 가 처리되므로 충분히 빠르다. | 정렬작업이 쿼리 실행시 처리되므로 레코드 대상 건수가 많아질수도록 쿼리 응답속도가 느리다. |
MySQL 서버에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 Extra 칼럼에 “Using filesort” 메시지가 표시되는지 여부로 판단할 수 있다.
소트버퍼
MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데 이 메모리 공간을 소트 버퍼(Sort buffer) 라고 한다. 쿼리 실행이 완료되면 즉시 시스템으로 반납된다.
정렬해야 할 레코드의 건수가 소트버퍼로 할당된 공간보다 크다면 여러조각으로 나눠서 처리하는데 임시 저장을 위해 디스크를 사용
소트버퍼 크게 설정해서 빠른 성능을 얻을 수도 있지만 시스템 메모리 부족으로 MySQL 서버가 종료될 위험이 있다.
정렬 알고리즘
레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 컬럼만 소프 버터에 담을지에 따라 **“싱글 패스(Single-pass)”와 “투 패스(Two-pass)” 2가지 정렬 모드로 나눌 수 있음
싱글 패스 정렬 방식
소트 버퍼에 정렬 기준 컬럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식
투 패스 정렬 방식
정렬 대상 컬럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT 할 컬럼을 가져오는 정렬 방식
일반적으로 싱글 패스 정렬 방식을 주로 사용
투 패스 정렬 방식을 사용하는 경우
- 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
- BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함할 때
정렬 처리 방법
ORDER BY 가 사용되면 반드시 3가지 처리 방법 중 하나로 정렬 처리
- 인덱스를 사용한 정렬 (별도 표기 없음)
- 조인에서 드라이빙 테이블만 정렬 (Using filesort 메시지가 표시됨)
- 조인에서 조인 결과를 임시 테이블로 저장후 정렬 (Using temporary Using filesort 메시지가 표시됨)
인덱스를 이용한 정렬
ORDER BY 에 명시된 컬럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블) 에 속하고, ORDER BY 의 순서대로 생성된 인덱스가 있어야 한다.
조인의 드라이빙 테이블만 정렬
드라이빙 테이블의 컬럼(PK, 인덱스 아님)만으로 ORDER BY 가 작성되는 경우
- 인덱스를 이용해 where 절 검색
- 검색 결과를 가지고 컬럼 정렬을 수행
- 정렬 결과를 가지고 다른테이블과 조인 수행
임시 테이블을 이용한 정렬
드리븐 테이블에 있는 컬럼을 정렬하는 경우 , 조인된 결과를 가지고 정렬을 수행한다.
정렬 처리 방법의 성능 비교
스트리밍 방식
레코드가 검색될때마다 바로바로 클라이언트로 전송해주는 방식을 의미
버퍼링 방식
먼저 결과를 모아서 MySQL 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야 한다.
GROUP BY 처리
- GROUP BY 인덱스 이용 : 인덱스 스캔 방법 또는 루스 인덱스 스캔 사용
- GROUP BY 인덱스 미사용 : 임시 테이블을 사용
정렬 관련 코멘트 Using temporary, Using filesort
루스 인덱스 스캔을 이용한 GROUP BY
실행 계획의 Extra 컬럼에 Using index for group-by 코멘트가 표시됨 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있음
임시테이블을 사용하는 GROUP BY
MySQL 8.0 버전 부터는 묵시적인 정렬은 더 이상 실행되지 않음
DISTINCT 처리
- 아래의 두 쿼리는 동일하게 내부적으로 처리됨.
1 2
select distinct emp_no from salaries; select emp_no from salaries group by emp_no;
- 레코드를 유니크하게 조회하는 것이지 특정 컬럼만 유니크하게 조회하는 것이 아니다.
- 아래의 쿼리를 괄호가 제거되고 실행된다.
1
select distinct (first_name), last_name from salaries;
- 집합 함수와 함께 사용된 DISTINCT
- count(), min(), max()와 같은 집합 함수 내에서 DISTINCT 키워드가 사용 가능
- 집합 함수가 없는 select 쿼리의 distinct는 조회하는 모든 컬럼이 유니크한 것들만 가져온다.
- 하지만 집합 함수 내에서 사용된 distinct는 그 집합 함수 인자로 전달된 컬럼값만 유니크한 것들을 가져온다.
- 인덱스 컬럼에 대해 distinct 처리를 수행할 때는 인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블 없이 최적화된 쿼리를 수행할 수 있다.
내부 임시 테이블 활용
- 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다.
- 메모리 임시 테이블 & 디스크 임시테이블 > page 314