옵티마이저, 힌트는 저번에 한 번 다뤄본적이 있다.
기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요한데 이 기능을 옵티마이저가 담당한다.
옵티마이저는 DB 서버에서 두뇌와 같은데, 현재 DBMS 는 비용 기반 최적화를 사용하고 과거에는 규칙 기반 최적화 방법을 사용했다
데이터 읽기
결과물은 동일하더라고 RDBMS 별로 그 결과를 만들어 내는 과정은 다 다르다.
일반적으로 테이블의 전체 크기는 인덱스 보다 훨씬 크기 때문에 테이블을 처음부터 읽는 작업은 상당히 많은 디스크 읽기가 필요하다.
그래서 대부분 DBMS는 풀 테이블 스캔을 실행할 때 여러개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다.
즉 하나씩 읽어오는 것이 아니다. (InnoDB 에서는 틀린말)
InnoDB 엔진은 특정 테이블의 연속된 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다.
리드 어헤드란 어떤 영역의 데이터가 앞으로 필요할 것 이다 라고 예측해서 미리 버퍼 풀에 가져다 두는것을 의미한다.
즉 풀 테이블 스캔이 실행되면 처음 몇개는 포그라운드 스레드(클라이언트 스레드) 읽다가 특정 시점에 백그라운드 스레드로 읽기 작업을 넘긴다. 백그라운드 스레드가 읽기를 넘겨받으면 한 번에 4~8개 페이지를 읽고, 계속 그 수가 증가돼 최대 64개의 페이지를 읽어 버퍼풀에 저장해 둔다.
포그라운드 스레드는 미리 버퍼풀에 저장된 데이터를 가져다 사용하므로 쿼리가 상당히 빨라진다.
소트버퍼
정렬을 수행하기 위해 별도의 메모리 공간을 할당해 사용하는데 이 공간을 소트버퍼 라고한다. 정렬이 필요한 경우에만 할당되고, 소트 버퍼를 위한 공간은 쿼리가 완료되면 즉시 시스템으로 반납된다.
근데 정렬해야 할 레코드 건수가, 소트버퍼로 할당된 공간보다 크다면 어떨까 ??
정렬해야 할 레코드를 여러조각으로 나누어 처리하는데 이때 임시 저장을 위해 디스크를 사용한다. 메모리의 소트 버퍼에서 정렬을 수행하고 , 그결과를 임시로 디스크에 기록한다. 그리고 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장한다.
그럼 소트 버퍼 사이즈 설정값이 무조건 크면 메모리에서 모두 처리되니 빠른가 ??
정답은 아니이다.
큰 메모리 공간 할당때문에 성능이 떨어질 가능성이 있다.
소트 버퍼는 여러 클라이언트가 공유할 수 있는 영역이 아니다. 연결이 많을수록, 정렬이 많을수록,
소트버퍼로 소비되는 메모리 공간이 커진다.
- 정렬 알고리즘
싱글-패스 : 데이터를 한 번만 읽고 쓰면서 정렬 작업을 완료.주로 작은 데이터 세트나 메모리에 모두 적재할 수 있는 경우에 사용
투-패스 : 데이터를 두 번 이상 읽고 쓰면서 정렬 작업을 완료. 주로 대규모 데이터 세트나 메모리에 모두 적재할 수 없는 경우에 사용
예시
투 패스 : <sort_key, id> : 정렬키와 레코드의 아이디만 가져와서 정렬하는 방식
싱글패스 : <sort_key, additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식
레코드의 칼럼들을 고정 사이즈로 메모리의 저장한다
싱글 패스 정렬 방식
처음에 읽을때 필요 없는것 까지 다 읽어 버리고, 소트 버퍼에 담고 정렬을 수행한다.
- 투 패스 정렬 방식
투 패스 의 경우, pk 값만 소트버퍼에 담아 정렬하고, 정렬된 순서대로 다시 pk로 테이블을 읽어서 select할 칼럼을 가져오는 방식이다.
싱글 패스의 경우 소트버퍼 공간이 많이 필요하다. 불합리 하지는 않다.
투 패스 의 경우 두 번 읽어야 하기 때문에 불합리하다.
최신 버전에서는 주로 싱글 패스 정렬 방식을 사용하고, 특수한 경우 투 패스 정렬 방식을 사용한다.
1. 레코드 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클때
2. BLOB 나 TEXT 타입의 칼럼이 SELECT 대상에 포함할 때
3. 인덱스 부재: 정렬할 컬럼에 인덱스가 없을 때.
4. 데이터 크기: 정렬에 필요한 데이터의 크기가 메모리 한계를 초과할 때.
5. 쿼리 복잡성: 여러 테이블 간의 조인 또는 복잡한 조건이 있을 때.
6. SELECT 대상 컬럼: SELECT 대상에 대용량 컬럼이 포함되어 있을 때.
과거 Harbor 프로젝트에서 id에 pk가 걸려있는 상황에서
explain select * from hr_employee ORDER BY employee_id;
extra 의 별도 포기가 없다. 인덱스를 사용할 수 있따면 별도의 Filesort 과정없이 인덱스를 순서대로 읽어서 반환한다.
인덱싱 안 걸린 다른 것을 할때
explain select * from hr_employee ORDER BY birth_date;
인덱스를 이용해 정렬이 처리되는 경우, 실제 인덱스의 값이 정렬돼 있기 때문에 인덱스 순서대로 읽기만 하면 된다.
인덱스를 사용하면 자동으로 정렬이 된다고, 일부러 order by 를 제거하는 것은 좋지 않은 선택이다
select *
from hr_employee
where ~~
;
정렬 처리에서 성능 을 올리기 위해 ORDER BY, LIMIT 는 필수로 사용되는 경향이 있다. LIMIT 의 경우에는 테이블이나, 처리 결과의 일부만 가져오기 때문에 서버가 처리해야 할 작업량을 줄이는 역할을 한다.
그러나 LIMIT 를 걸려면 애초에 우선 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그루핑 작업을 실행해야지 LIMIT 로 건수를 제한할 수 있다. WHERE 조건이 아무리 인덱스를 잘 활용하게 해도 ORDER BY, GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생한다.
그럼 왜 인덱스를 사용하지 못하는 정렬이나, 그루핑 작업이 느리게 할 수밖에 없을까 ??
쿼리가 처리되는 방법에는 스트리밍 처리와 버퍼링 처리 2가지 방식이 있다.
스트리밍 방식 : 서버 쪽에서 데이터가 얼마인지 관계없이 조건에 일치하는 레코드 검색될 때 마다 바로 클라이언트 전송해주는 방식.
스트리밍 방식을 사용하면
- 일치하는 레코드 찾는 즉시 전달 받아 동시에 데이터의 가공 작업을 시작할 수 있다.
- 쿼리가얼마나 많은지에 상관없이 빠른 응답 시간을 보장한다.
- 엄청 큰 테이블을 조건 없이 Select 하면 첫번 째 레코드는 아주 빨리 가져온다. 서버는 아직 돌아가고 있는 중에도
버퍼링 방식: 서버 에서 모든 레코드를 검색하고 정렬하는 동안 클라이언트는 아무것도 하지 않고 기다려야한다.일차하는 모든 레코드를 가져온 후, 정렬하거나 그루핑 해서 차례대로 보내주는 형식
쿼리의 결과가 스트리밍 되는 것은 불가능하다. where 절로 가져온 후 그루핑, 정렬해서 차례대로 내보내야하기 때문이다.
Limit 를 걸어도 성능 향상에 별로 도움이 되지 않는다.
네스티드 루프 조인 : MySQL 서버에서 지원하는 조인 방식, 드라이빙 테이블 의 레코드를 한 건 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서 조인을 수행하는 것
- 마치 중첩된 반복 명령을 사용하는 것처럼 작동한다.
블록 네스티드 루프 조인 : 조인의 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용되는 조인 방식
차이점
- 조인 버퍼(조인을 위한 버퍼)가 사용되는지 여부와 조인에서 드라이빙 테이블과 드리븐 테이블의 어느 순서로 조인되느냐.
- 조인버퍼란 : 데이터베이스에서 조인을 수행할 때 사용하는 메모리 공간 주로 조인 연산을 최적화하고 성능을 향상시키기 위해 사용왼다. 조인 버퍼는 데이터베이스 시스템에서 조인을 수행할 때 필요한 중간 데이터를 일시적으로 저장하는 역할을 한다
댓글