9.2.0.
RDMBS는 데이터 정렬/그루핑 등 기본 데이터 가공 기능을 가진다.
하지만 결과물은 동일하더라도 결과를 만들어내는 과정을 모두 다르다.
기본적인 가공을 위해, MySQL 서버가 어떤 알고리즘을 사용하는가?
9.2.1. 풀 테이블 스캔 & 풀 인덱스 스캔
풀 테이블 스캔
•
인덱스를 사용하지 않고, 테이블의 데이터를 처음부터 끝까지 읽어서, 요청된 작업을 처리하는 방식
•
풀 테이블 스캔을 선택하는 조건
◦
테이블의 레코드 건수가 너무 작아서 풀 테이블 스캔의 속도 > 인덱스인 경우
(일반적으로 테이블이 페이지 1개로 구성된 경우)
◦
WHERE 또는 ON 절에 인덱스를 이용할 수 없는 적절한 조건이 없는 경우
◦
인덱스 레인지 스캔을 사용할 수 있는 쿼리여도, 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준으로)
•
테이블의 전체 크기 >>> 인덱스 이기 때문에, 풀 테이블 스캔은 상당히 많은 디스크 읽기가 필요
⇒ 대부분 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능 내장
⇒ 하지만 MySQL에서는 풀 테이블 스캔을 실행할 때 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수가 없음
⇒ InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면, 백그라운드 스레드에 의해, 리드 어헤드(Read Ahead) 작업이 자동으로 시작됨
◦
리드 어헤드
▪
어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측
▪
요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것
•
MySQL + InnoDB 스토리지 엔진에서 풀 테이블 스캔이 실행되면,
◦
처음 몇개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행
◦
특정 시점부터는 백그라운드 스레드로 읽기 작업을 넘김: 한 번에 4개 || 8개의 페이지를 읽으면서 계속 그 수를 증가시킴(한 번에 최대 64개까지 읽기 가능)
▪
페이지는 버퍼 풀에 저장됨
◦
포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 됨
⇒ 쿼리 처리가 상당히 빨리 됨
•
innodb_read_ahead_threshold 시스템 변수를 이용해서 언제 리드 어헤드를 시작할지 임계값 설정 가능
◦
데이터 웨어하우스 용도로 사용한다면, 해당 옵션을 더 낮은 값으로 설정해서 더 빨리 리드 어헤드가 시작되게 유도하는 것도 좋음
풀 인덱스 스캔
•
인덱스를 처음부터 끝까지 스캔하는 것을 의미
•
리드 어헤드가 동일하게 사용됨
SELECT COUNT(*) FROM ...
- 레코드 건 수 조회 → 용량 작음 → 인덱스 스캔
SELECT * FROM …
- 레코드에만 있는 칼럼이 필요한 경우 → 테이블 스캔
9.2.2. 병렬 처리
병렬 처리란
•
하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리한다는 것
◦
1 쿼리 ← n 스레드
•
스레드가 동시에 각각의 쿼리를 처리하는 것은 원래도 가능했음
◦
n 쿼리 ← n 스레드
•
innodb_parallel_read_threads 라는 시스템 변수를 이용해 하나의 쿼리를 최대의 몇 개의 스레드를 이용해서 처리할 지 변경할 수 있음.
SET SESSION innodb_parrel_read_threads = 1;
SQL
복사
•
병렬 처리용 스레드 개수가 늘어날수록 쿼리 처리에 걸리는 시간은 줄어듬
•
하지만, 병렬 처리용 스레드 개수 ≤ 서버에 장착된 CPU의 코어 개수
◦
반대가 되면 성능 떨어질 수 있음
9.2.3. ORDER BY 처리(filesort)
| 정렬 처리 방법 | 장점 | 단점 |
인덱스 이용 | INSERT, UPDATE, DELETE 쿼리 실행 시 인덱스가 정렬되어 있음
⇒ 순서대로 읽기만 하면 됨
⇒ 매우 빠름 | - INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요해서 느림
- 디스크 공간이 더 많이 필요함
- 인덱스 개수가 늘어날수록 버퍼 풀을 위한 메모리가 많이 필요함 |
Filesort 이용 | - 인덱스 생성하지 않아도 됨
→ 인덱스 이용 단점이 장점으로 바뀜
- 정렬해야 할 레코드가 적으면 메모리에서 Filesort가 처리되어 충분히 빠름 | 정렬 작업이 쿼리 실행 시 처리됨
⇒ 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느림 |
모든 정렬을 인덱스를 이용하기 어려운 이유
•
정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
•
GROUP BY 의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
•
UNION 의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
•
랜덤하게 결과 레코드를 가져와야 하는 경우
확인 방법
•
실행 계획의 Extra 칼럼에 Using filesort 메세지가 표시되는지 여부
9.2.3.1. 소트 버퍼
9.2.3.2. 정렬 알고리즘
1. 싱글 패스 정렬 방식
2. 투 패스 정렬 방식
9.2.3.3. 정렬 처리 방법
1. 인덱스 이용
2. 조인의 드라이빙 테이블만 정렬
3. 임시 테이블을 이용한 정렬
4. 성능 비교
•
스트리밍 처리
•
버퍼링 처리