책너두 6기 28일차
백은빈, 이성욱의 Real MySQL8.0 1권 p.287 ~ p.298
내용정리
09 옵티마이저와 힌트
9.2.2 병럴 처리
병렬 처리는 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것을 의미한다. innodb_parallel_read_threads라는 시스템 변수로 스레드 갯수를 설정할 수 있다. where 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수있다. CPU 갯수를 넘어서면 오히려 성능이 떨어질 수 있다.
9.2.3 ORDER BY 처리(Using filesort)
인덱스 이용
- 장점 :
insert,update,delete쿼리가 실행될 때 이미 인덱스가 정렬돼 있어서 빠르다 - 단점 :
insert,update,delete작업 시 부가적인 인덱스 추가/삭제 작업이 필요해 느리다. 디스크 공간이 더 필요하다.
FIlesort 이용
- 장점 : 인덱스를 사용하지 않아 인덱스 이용시 단점이 장점이 된다. 레코드가 적을땐 충분히 빠르다.
- 단점 : 레코드가 많을수록 응답 속도가 느리다.
모든 정렬을 인덱스를 이용하도록 튜닝하는 것은 힘들다.
- 정렬 기준이 너무 많은 경우
- group by, distinct 같은 처리의 결과를 정렬하는 경우
- 임시 테이블의 결과를 다시 정렬하는 경우
- 랜덤하게 결과를 가져와야 하는 경우
9.2.3.1 소트 버퍼
정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메몰 공간을 sort buffer라고 한다.
레코드의 건수가 소트 버퍼로 할당된 공간보다 크면 정렬을 수행하고 그 결과를 임시로 디스크에 기록해 둔다. 그다음 레코드를 가져와 정렬하고 저장 하는 과정을 반복한다. 이처럼 각 버퍼 크기 만큼 정렬된 레코드를 병합하면서 정렬하는데, 이 것을 멀티 머지(Multi-merge)라고 한다.
sort_buffer_Size 가 크다고 무조건 빨리지는 것은 절대 아니다.
9.2.3.2 정렬 알고리즘
레코드 정렬시 레코드 전체를 소트 버퍼에 담을지 정렬 기준 칼럼만 담을지를 싱글 패스(Single-pass)와 투 패스(Two-pass)라고 한다.
9.2.3.2.1 싱글 패스 정렬 방식
정렬 키와 레코드 전체를 가져와서 정렬하는 방식.
레코드의 칼럼들을 고정사이즈 혹은 가변 사이즈로 메모리 저장한다.
9.2.3.2.2 투 패스 정렬 방식
정렬 키와 레코드의 로우 아이디(Row ID)만 가져와서 정렬하는 방식
- 레코드의 크기가
max_length_for_sort_data시스템 변수에 설정된 값보다 클 떄 - BLOB이나 TEXT 타입의 칼럼이 select 대상에 포함될 때
를 제외하곤 보통 싱글 패스 정렬 방식을 기본으로 사용한다.
9.2.3.3 정렬 처리 방법
쿼리에 order by가 사용되면 다음 3가지 중 한 가지로 처리된다. 아래 방법일 수록 느리다.
- 인덱스를 사용
- 조인에서 드라이빙 테이블만 정렬 : Extra 칼럼에 “using filesort” 메시지가 표시됨
- 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 : Extra 칼럼에 “Using temporary; Using filesort” 메시지가 표시됨
9.2.3.3.1 인덱스를 이용한 정렬
order by에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, order by의 순서대로 생성된 인덱스가 있어야 한다.
인덱스를 이용해 정렬이 처리되는 경우에는 싷 인덱스의 값이 정렬돼 있기 때문에 인덱스의 순서대로 읽기만 하면 된다.
9.2.3.3.2 조인의 드라이빙 테이블만 정렬
조인이 실행되면 레코드 건수도 늘어나고 크기도 커지기 때문에, 실행 전 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는것이 정렬의 차선책이 될 것이다.
