1. 풀 테이블 스캔과 풀 인덱스 스캔
: 풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 것,옵티마이저는 다음과 같은 조건일 때 주로 풀 테이블 스캔을 선택
•
테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
•
WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
•
인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우
: 일반적으로 테이블의 전체 크기는 인덱스보다 훨씬 크므로 풀 테이블 스캔은 많은 디스크 읽기가 필요, 그래서 대부분의 RDBMS는 한꺼번에 여러 개의 블록이나 페이지를 불러오는 기능을 내장하고 있음, 하지만
MySQL에는 이런 페이지의 수를 지정해두는 시스템 변수는 없음, 이 때문에 한 개씩 처리된다고 생각하나 백그라운드 스레드에서 앞으로 읽기에 사용될 페이지들을 미리 버퍼 풀에 적재를 해두고 포그라운드
스레드가 작업을 원활히 수행할 수 있게끔 해둠, 작업 초에는 포그라운드 스레드가 데이터를 읽어오나 어느정도 진행되면 백그라운드 스레드가 데이터를 읽어온다는 것
: MySQL서버에서는 innodb_read_ahead_treshold시스템 변수를 이용해 리드 어헤드를 언제 시작할지 설정할 수 있으며 일반적으로는 기본값으로도 충분
2. 병렬 처리
: MySQL 8.0부터는 한정되어있지만 쿼리의 병렬 처리가 지원됨, WHERE 조건 없이 단순히 전체 건수를 가져오는 쿼리에만 사용 가능
SET SESSION inoodb_parallel_read_threads=1;
SELECT COUNT(*) FROM salaries;
1 row in set(0.32 sec)
SET SESSION inoodb_parallel_read_threads=2;
SELECT COUNT(*) FROM salaries;
1 row in set(0.20 sec)
SET SESSION inoodb_parallel_read_threads=4;
SELECT COUNT(*) FROM salaries;
1 row in set(0.18 sec)
SET SESSION inoodb_parallel_read_threads=8;
SELECT COUNT(*) FROM salaries;
1 row in set(0.13 sec)
SQL
복사
: 이처럼 병렬 처리용 스레드 개수가 늘어날수록 요청을 빠르게 처리하는 것을 확인할 수 있는데 CPU의 코어 개수를 넘기는 경우 오히려 성능이 떨어질 수 있다.
3. ORDER BY 처리(Using filesort)
: 정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될때 Filesort라는 별도의 처리를 이용하는 방법으로 나눌 수 있음
장점 | ||
인덱스 이용 | INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가
정렬되어 있으므로 순서대로 읽기만 하면 되어 매우 빠름 | INSERT,UPDATE,DELETE 작업 시 부가적인 인덱스 작업이 발생하므로 느리다.
인덱스 때문에 디스크 공간이 더 많이 필요함
인덱스의 개수가 늘어날수록 버퍼풀을 유지하기 위한 메모리가 많이 필요 |
Filesort 이용 | 인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀜, 정렬해야 할 레코드가 많지 않으면 메모리에서 처리되므로 충분히 빠름 | 정렬 작업 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느려짐 |
: 다음과 같은 이유로 모든 정렬을 인덱스에서 수행하도록 튜닝하는 것이 불가능하므로 잘 알아두자
•
정렬 기준이 너무 많아 요건별로 모두 인덱스를 생성하는 것이 불가할 때
•
GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야할 때
•
UNION의 결과와 같이 임시 테이블의 결과를 다시 또 정렬해야할 때
•
랜덤하게 결과 레코드를 가져와야할 때
: MySQL의 정렬 특성을 이해하여 쿼리를 튜닝할 때 어떻게 하면 조금이라도 더 빠른 쿼리가 실행될 지 판단해보자
1.
소트 버퍼
: 정렬을 수행하기 위해 MySQL 버퍼가 임의로 할당하는 공간, 정렬이 필요한 경우에만 할당되며 정렬해야할 레코드의 크기에 따라 가변적으로 증가하지만 제한을 둘 수 있음
: 만약 정렬해야할 부분이 소트 버퍼로 할당된 공간보다 크면 이를 나눠서 진행하는데, 이때 임시 저장을 위해 디스크를 사용함, 이런 작업들은 디스크 입출력을 유발하며 레코드 건수가 많아질수록 그
작업 횟수가 매우 증가함
: 저자는 일반적으로 트랜잭션 처리용 MySQL 서버의 소트 버퍼 크기는 56KB에서 1MB 미만이 적절해보인다고 함
2.
정렬 알고리즘
: 레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 싱글 패스, 투 패스로 나뉨
: 옵티마이저 트레이스 기능을 활성하화여 어던 정렬 모드를 사용하는지 확인할 수 있음
1.
싱글 패스 정렬 방식
: 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 모든 칼럼을 담아서 정렬을 수행하는 방법
2.
투 패스 정렬 방식
: 정렬 대상 칼럼과 프라이머리 키 값만 담아서 소트 버퍼에 담아 정렬을 수행한 후, 정렬된 순서대로 다시 프라이머리 키를 읽어서 SELECT할 칼럼을 가져오는 방식
: 테이블을 두 번 읽어야 하므로 불합리하며 싱글 패스 정렬 방식이 최근 방법
: 싱글 패스 정렬 방식은 일반적으로 더 많은 소트 버퍼 공간이 필요하며 MySQL은 대부분의 방법에 싱글 패스 정렬 방식을 사용하나 다음의 경우 투 패스를 사용함
레코드의 크기가 max_length_for_sort_data 시스템 변수의 값보다 클 때
BLOB이나 TEXT타입의 칼럼이 SELECT 대상에 포함될 때
: 즉 싱글 패스 방식은 정렬 대상의 사이즈가 작거나 건수가 작은 경우, 투 패스 방식은 크기나 건수가 상당히 큰 경우 주로 사용됨
3.
정렬 처리 방법
: 쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 방법 중 하나로 처리됨, 아래로 내려갈수록 그 성능이 떨어지는 편
•
인덱스를 사용한 정렬, Extra 칼럼에 별도 표기 없음
•
조인에서 드라이빙 테이블만 정렬, Extra 칼럼 Using filesort 표시
•
조인에서 조인 결과를 임시 테이블로 저장 후 정렬, Using temporary; Using filesort 메시지가 표시
1.
인덱스를 사용한 정렬
: 인덱스를 이용한 정렬에는 반드시 ORDER BY에 명시된 칼럼이 드라이빙 테이블에 속하며 인덱스가 생성되어있어야 함
: WHERE절에 드라이빙 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야함
: 전문 검색 인덱스나, B-Tree 계열의 인덱스가 아닌 경우, 인덱스를 사용한 정렬을 사용할 수 없음, R-Tree도 B-Tree 계열이지만 특성상 사용이 불가하며 여러 테이블이 조인되는 경우에는
Nested-loop 방식의 조인에서만 사용할 수 있음
2.
조인의 드라이빙 테이블만 정렬
: 일반적으로 조인의 경우, 정렬해야할 사이즈가 커지므로 먼저 드라이빙 테이블을 정렬하는 것이 합리적인 방법, 이 방법으로 처리하려면 ORDER BY의 대상이 드라이빙 테이블의 칼럼이어야 함
3.
임시 테이블을 이용한 정렬
쿼리가 여러 테이블을 조인하고 그 결과를 정렬해야 한다면 필요한 경우, 항상 조인의 결과를 임시테이블에 저장한 후, 해당 결과를 다시 정렬하는 과정을 거침
4.
정렬 처리 방법의 성능 비교
쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 왜 느리게 작동하는 지에 대해 살펴보자
1.
스트리밍 방식
: 서버 쪽에서 처리할 데이터가 얼마건 간에 조건에 일치하는 레코드를 찾을 때마다 바로 클라이언트로 전송해주는 방식
: 쿼리가 스트리밍 방식으로 처리되는 경우 클라이언트는 레코드를 바로바로 전달받으므로 데이터 가공의 작업을 미리 시작할 수 있음, 이 덕에 빠른 응답 시간을 보장
2.
버퍼링 방식
: ORDER BY나 FROUP BY는 쿼리의 결과가 스트리밍되는 것을 불가능하게 함, 조건에 맞는 것을 가져온 후 정렬하거나 그루핑해야하기 때문, 이 동안 클라이언트는 아무것도 하지 못하고
대기하고 있어야하기 때문에 응답속도가 느려짐
4.
정렬 관련 상태 변수
: 정렬 관련 처리 작업들을 확인할 수 있는 변수들
4. GROUP BY 처리
: GROUP BY 작업도 인덱스를 사용하는 경우와 그렇지 못한 경우로 나눌 수 있으며 인덱스 스캔, 루스 인덱스 스캔, 임시 테이블을 사용하는 GROUP BY로 나눌 수 있다.
1.
타이트 인덱스 스캔을 사용하는 GROUP BY
: 조인의 드라이빙 테이블에 속하는 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례로 읽으며 그루핑 작업을 수행, 이미 정렬된 인덱스를 읽는 것이므로 쿼리 실행
시점에 추가적인 정렬 작업이나 임시테이블이 필요하지 않음
2.
루스 인덱스 스캔을 이용하는 GROUP BY
: 루스 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것, 이때 실행 계획의 Extra 칼럼에 “Using index for group-by” 코멘트가 표시됨
: MySQL의 루스 인덱스 스캔의 경우, 단일 테이블에 수행되는 GROUP BY에 대해서만 수행이 가능함, 즉 임시 테이블은 불가하다는 것, 또한 프리픽스 인덱스도 사용이 불가함
3.
임시 테이블을 사용하는 GROUP BY
: GROUP BY의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용할 수 없을땐 임시 테이블을 사용하여 처리됨
: 이 경우 Extra칼럼에 Using Temporary 메시지가 표시되며
5. DISTINCT 처리
: 특정 칼럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용하는데, 집합 함수가 사용되는 경우와 사용되지 않는 경우로 나눠서 처리함
1.
SELECT DISTINCT …
: SELECT된 레코드값 중 유니크한 레코드만 가져오고자 할 경우, SELECT DISTINCT를 통상적으로 사용함, 이는 GROUP BY와 동일하게 처리
: DISTINCT는 SELECT하려는 레코드를 유니크하게 SELECT할 뿐, 특정 칼럼만 조회하는 것이 아님, 또한 DISTINCT는 조회되는 모든 칼럼에 영향을 미치며 일부 컬럼에만
동작하지 않음
2.
집합 함수와 함께 사용된 DISTINCT
: COUNT(), MIN(), MAX()와 같은 집합 함수 내에서 DISTINCT가 사용될 경우, 일반적으로 사용되는 경우와는 다르게 해석됨
: 집합 함수가 없는 SELECT 쿼리에서의 DISTINCT는 조회되어지는 모든 칼럼의 조합이 유니크한 것들만 가져옴, 하지만 집합 함수 내에 사용된 DISTINCT는 집합 함수의 인자로 사용된 칼럼값이 유니크한
것들을 가져옴
6. 내부 임시 테이블 활용
: MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그룹핑할 땐 내부적으로 임시 테이블을 생성하여 사용, CREATE TEMPORARY TABLE를 사용하여 임시로 생성하며 처음에는 메모리에 적재되어있다가
사이즈가 커지면 디스크로 옮겨지며 쿼리의 처리가 완료되면 자동으로 삭제됨
1.
메모리 임시 테이블과 디스크 임시 테이블
: 8.0부터는 메모리는 TempTable이라는 스토리지 엔진을 사용하며 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용, 기존에는 MEMORY 엔진을 사용하여 메모리에 저장되는 임시 테이블을
생성하였음, 이는 현재도 internal_tmp_mem_storage_engine 변수를 통해 변경할 수 있음
2.
임시 테이블이 필요한 쿼리
: 주로 인덱스를 사용하지 못하는 쿼리의 경우
•
ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
•
ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 드라이빙 테이블이 아닌 경우
•
DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
•
UNION이나 UNION DISTINCT가 사용된 쿼리
•
쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
: 실행계획의 Extra 칼럼에서 Using temporary라는 메시지를 확인하면 된다.
3.
임시 테이블이 디스크에 생성되는 경우
: 기본적으로 메모리에 생성되지만 다음과 같은 조건을 만족하면 디스크에 생성됨
•
UNION이나 UNION ALL에서 SELECT되는 칼럼 중에 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
•
GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
•
메모리 임시 테이블의 크기가 temp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 temptable_max_ram 시스템 변수 값보다 큰 경우
: 음.. 메모리에 적재할 사이즈가 안되는 경우 디스크에 생성되는 거 같다.. 크게 의미는 없는듯
4. 임시 테이블 관련 상태 변수
: 실행 계획상에서 Using Temporary가 표시되면 임시 테이블을 사용했다는 사실을 확인할 수 있으나 이에 대한 구체적인 정보, 몇 개가 사용됐다던지, 메모리인지 디스크인지 등은
SHOW SESSION STATUS LIKE ‘Created_tmp%’;를 확인해보면 된다.
