1. 풀 테이블 스캔과 풀 인덱스 스캔
풀 테이블 스캔 : 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 작업을 처리
풀 테이블 스캔을 선택하는 조건
- 레코드 건수가
너무 작아, 인덱스를 읽는 것 보다 풀 테이블 스캔이 빠른 경우- 일반적으로 페이지 1개로 구성
- 인덱스 레인지 스캔을 사용할 수 있는 쿼리라도, 일치 레코드 건수가
너무 많은경우- 인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준
풀 테이블 스캔은 많은 디스크 읽기가 필요하므로, 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다.
다만, 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 없다.
→ InnoDB는 한 페이지씩 읽어 오는게 아님.
InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작됨.
리드 어헤드 : 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 미리 예측해 미리 읽어 버퍼 풀에 가져다 두는 것
풀 테이블 스캔 실행
→ 몇 개의 페이지는 포그라운드 스레드
→ 특정 시점 이후에는 백그라운드 스레드에 의해 가져옴.
한 번에 4개 또는 8개, 최대 64개의 데이터 페이지
다음 쿼리는 풀 테이블 스캔을 할 것 같지만, 풀 인덱스 스캔을 하게 될 가능성이 높다.
단순히 레코드의 건수만 필요로 하는 쿼리라면 용량이 작은 인덱스를 선택하는 것이 I/O 작업을 줄일 수 있기 때문
> SELECT COUNT(*) FROM employess;2. 병렬 처리
병렬 처리 : 하나의 쿼리를 여러 스레드가 작업을 동시에 나누어 처리
→ 여러 스레드가 동시에 각각의 쿼리를 처리하는 것은 MySQL 서버가 처음 만들어질 때 부터 가능.
MySQL 8.0에서는 innodb_parallel_read_threads 라는 시스템 변수로 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지 변경할 수 있다.
병렬 처리용 스레드 개수를 아무리 늘리더라도 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수 있으니 주의하자.
→ CONTEXT SWITCHING
ORDER BY 처리
- Index 사용
- Filesort 사용
1. Index
장점
- INSERT, UPDATE, DELETE 쿼리가 실행될 때
이미 인덱스가 정렬돼 있어 순서대로 읽기만 하면 되므로매우 빠름. → 너 오타 아니냐 ? SELECT 아니냐 ?
단점
- INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다.
- 인덱스 때문에 디스크 공간이 더 많이 필요하다.
- 인덱스의 개수가 늘어날수록 InnoDB 버퍼풀을 위한 메모리가 많이 필요하다.
2. Filesort
장점
- Index를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀐다.
- 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다.
단점
- 정렬 작업이 쿼리 실행 시 처리되므로 레코드가 많을수록 쿼리의 응답 속도가 느리다.
모든 정렬을 인덱스를 이요하도록 튜닝하기란 거의 불가능하다.
- 정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
- GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
- UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
- 랜덤하게 결과 레코드를 가져와야 하는 경우
Filesort를 이용했을 경우, 실행 계획에 Using filesort 라고 나옴.
소트 버퍼
- 정렬을 수행하기 위해 할당받은 별도의 메모리 공간
- 정렬이 필요한 경우메나 할당되며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가한다.
- 최대 사용 가능한 소트 버퍼의 공간은
sort_buffer_size라는 시스템 변수로 설정 - 소트 버퍼 크기가 256KB에서 8MB 사이에서 최적의 성능을 보였으며, 그 외에는 성능 효과가 보이지 않았다.
→ 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록하고, 다시 가져오기 때문에 많은 I/O 작업이 필요하다.
💡 소트 버퍼를 너무 크게 설정하면 서버의 메모리가 부족해져서 MySQL 서버가 종료될 수 있다.
대량 데이터의 정렬이 필요한 경우 해당 세션의 소트 버퍼만 일시적으로 늘려서 쿼리를 실행하고 다시 줄이는 것도 굿 !
정렬 알고리즘
- 싱글 패스(Single-pass)
- 투 패스(Two-pass)
std::stable_sort
→ Quick-sort, Heap-sort를 복합적으로 사용
싱글 패스 정렬 방식
- 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행
mysql > SELECT emp_no, first_name, last_name
FROM employees
ORDER BY first_name;정렬에 필요 없는 last_name도 소트 버퍼에 넣고, 멀티 머지 후에 결과를 반환한다.
투 패스 정렬 방식
- 정렬 대상 칼럼과 PK 값만 소트 버퍼에 담아서 정렬을 수행하고,
정렬된 순서대로 다시 PK로 테이블을 읽어SELECT할 칼럼을 가져온다. 싱글 패스 정렬보다 오래된 기법임.
→ 투 패스 방식은 테이블을 두 번 읽어야 하기 때문에 상당히 불합리하지만, 새로운 정렬 방식인 싱글패스는 한 번만 읽어도 된다.
투 패스 정렬 방식을 사용하는 경우
- 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
- BLOB이나 TEXT 타입의 칼럼이 SELECT 대상에 포함될 때
💡 SELECT 쿼리에서 꼭 필요한 칼럼만 조회하지 않고, 모든 칼럼(*)을 가져오도록 개발할 때가 많다.
하지만 이는 정렬 버퍼를 몇 배에서 몇십 배까지 비효율적으로 사용할 가능성이 크다.
불필요한 칼럼을 SELECT하지 않게 쿼리를 작성하는 것이 효율적이다.
인덱스를 이용한 정렬
- ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(JOIN인 경우 드라이빙 테이블)
- ORDER BY의 순서대로 생성된 인덱스가 있어야 함.
- 해시 인덱스나, 전문 검색 인덱스는 인덱스 X
조인의 드라이빙 테이블만 정렬
- 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이다.
- 조인에서 첫 번쨰로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY 절을 작성해야 한다.
mysql > SELECT * FROM employees e, salaries s where s.emp_no=ememp_no and e.emp_no between ~ order by e.last_name;- WHERE 절의 검색 조건은 employees 테이블의 PK를 이용해 검색하면 작업량을 줄일 수 있다.
- 드리븐 테이블(salaries)의 조인 칼럼인 emp_no 칼럼에 인덱스가 이다.
last_name은 employees 테이블의 PK와 전혀 연관이 없으므로 인덱스를 이용한 정렬은 불가능하다.
→ ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블에 포함된 칼럼임을 알 수 있다.
→ 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고, 그 결과와 salaries 테이블을 조인한 것이다.
드리븐 테이블의 order by 조건은, 조인된 데이터를 가지고 정렬할 수 밖에 없다.
정렬 처리 방법의 성능 비교
- 주로 웹 서비스용 쿼리는 ORDER BY와 함께 LIMIT이 거의 필수로 사용되는 경향이 있다.
- ORDER BY나 GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수 만큼만 가져와서는 처리할 수 없다.
- 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그루핑 작업을 실행해야만 비로소 LIMIT으로 건수를 제한할 수 있다.
- WHERE 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생한다.
쿼리가 처리되는 방법
1. 스트리밍 방식
- 서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식을 의미한다.
- 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받는다.
- 가장 마지막 레코드는 언제 받을지 알 수 없지만, 이는 그다지 중요하지 않다.
- 웹 서비스 같은 OLTP 환경에서는 쿼리의 요청에서부터 첫 번째 레코드를 전달받게 되기까지의 응답 시간이 중요하다.
쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장- LIMIT처럼 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄여줄 수 있다.
2. 버퍼링 방식
- 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려진다.
- LIMIT처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않는다.
JDBC 라이브러리가 버퍼링하는 것.
JDBC를 사용하지 않는 SQL 클라이언트 도구는 이러한 버퍼링을 하지 않기 때문에 아무리 큰 테이블이라고 하더라도 첫 번째 레코드는 매우 빨리 가져온다.
→ 불필요한 네트워크 요청을 최소화되기 때문에 전체 처리량이 뛰어나서 이렇게 작업한다.
→ 아주 대량의 데이터를 가져와야 할 때는 스트리밍 방식으로 변경할 수 있다.
인덱스를 사용하지 못하고 Filesort 작업을 거쳐야 하는 쿼리에서 LIMIT 조건이 아무런 도움이 되지 못하는 것은 아니다.
LIMIT 10이 있다면, 1000건의 레코드를 모두 정렬하는 것이 아니라 필요한 순서대로 정렬해서 상위 10건만 정렬이 채워지면 정렬을 멈추고 결과를 반환한다.
MySQL 서버는 정렬을 위해 퀵 소트와 힙 소트 알고리즘을 사용한다.
→ 이는 LIMIT 10을 만족하는 상위 10건을 정렬하기 위해 더 많은 작업이 필요할 수도 있음을 의미한다.
GROUP BY 처리
- ORDER BY와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리 중 하나다.
- GROUP BY에 사용된 조건은 인덱스를 사용할 수 없으므로, HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.
인덱스를 사용할 때
- 인덱스를 차례대로 읽는 인덱스 스캔 방법
- 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔 방법
인덱스를 사용하지 못할 때
- 임시 테이블을 사용
인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)
- 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 인덱스를 차례대로 읽으면서 그루핑 작업 수행하고 그 결과로 조인
- GROUP BY가 인덱스를 사용해서 처리된다 해도, 그룹 함수 등의
그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다.
루스 인덱스 스캔을 이용하는 GROUP BY
- 루스 인덱스 스캔 : 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것
- Using index for group-by 코멘트 표시
프리픽스 인덱스는 루스 인덱스 스캔을 사용할 수 없다.
인덱스 레인지 스캔은 유니크한 값의 수가 많을수록 성능이 향상되지만, 루스 인덱스 스캔은 유니크한 값의 수가 적을수록 성능이 향상된다.
DISTINCT 처리
- 집합 함수와 같이 DISTINCT가 사용되는 쿼리의 실행 계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요하다.
- 하지만 실행 계획에서는 Using temporary 메시지가 출력되지 않는다.
다음과 같이 인덱스가 없는 컬럼에 COUNT를 하면 임시 테이블이 2개가 필요하다.
mysql > 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그러나, 인덱스가 있는 칼럼에 대해 DISTINCT 처리를 수행할 때는 인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블 없이 최적화된 처리를 수행할 수 있다.
내부 임시 테이블 활용
- MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블을 사용한다.
- 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다.
- 내부적인 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제된다.
RealMySQL 8.0 - Ch.9
