[Real MariaDB] 최적화 - 풀 테이블 스캔, ORDER BY, 소트 버퍼

풀 테이블 스캔

 테이블의 데이터를 인덱스를 사용하지 않고 처음부터 끝까지 읽어서 작업을 처리한다.

 

MariaDB의 옵티마이저는 다음과 같은 조건이 일치할 때 주로 풀 테이블 스캔을 선택한다.

• 테이블의 레코드 건수가 너무 작아서 인덱스를 통해서 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우(일반적으로 테이블이 페이지 1개로 구성된 경우)
• WHERE 절이나 ON 절에서 인덱스를 이용할 수 없는 경우
• 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우 (인덱스의 B-Tree 를 샘플링해서 조사한 통계 정보 기준)
  
  • max_seeks_for_key 변수를 작게 설정하여 풀 테이블 스캔보다 인덱스 레인지 스캔을 더 선호하도록 유도할 수 있다.

 

 InnoDB나 XtraDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 Read ahead 작업이 자동으로 시작된다. (Read ahead는 어떤 영역의 데이터가 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어서 XtraDB의 버퍼 풀에 저장해두는 것이다.)

 

 풀 테이블 스캔이 실행되면 처음 몇개의 데이터 페이지는 Foreground thread가 페이지 읽기를 실행하지만 특정 시점부터는 Background thread가 읽기 작업을 수행한다. Background thread가 작업을 수행하는 시점부터 페이지를 읽으면서 계속 그 수를 증가시킨다.

 

ORDER BY 처리 (Using filesort)

 실행 계획의 Extra 컬럼에 "Using filesort" 라는 코멘트가 있다면 인덱스를 이용하지 않고 별도의 정렬 처리를 한 것임을 알 수 있다.

 

정렬을 처리하기 위한 방법

인덱스를 이용하는 방법

장점

• 인덱스는 이미 정렬되어 있기 때문에 순서대로 읽기만 하면 되므로 매우 빠르다.

단점

• INSERT, UPDATE, DELETE 수행 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다.
• 인덱스를 위한 추가적인 디스크 공간이 더 필요하다.
• 인덱스 개수가 늘어날 수록 InnoDB의 버퍼 풀이나 MyISAM의 키 캐시용 메모리가 많이 필요하다.

 

Filesort를 이용하는 방법

장점

• 인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀐다.
• 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다.

단점

• 정렬 작업이 쿼리 실행 시에 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느려진다.

 

소트 버퍼 (Sort Buffer)

 소트 버퍼는 정렬을 수행하기 위해 할당받은 별도의 메모리 공간이다. 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size 라는 시스템 변수로 설정할 수 있다.

 

 소트 버퍼는 쿼리의 실행이 완료되면 즉시 시스템으로 반납된다.

 

 MyISAM이나 XtraDB와 같은 스토리지 엔진에서는 정렬을 위해서 sort_buffer_size 시스템 설정 변수로 할당된 메모리를 사용하지만 Aria 스토리지 엔진을 사용하는 테이블은 aria_sort_buffer_size 라는 시스템 변수로 할당되는 메모리 공간을 활용한다.

 

정렬해야 할 레코드 건수가 소트 버퍼보다 큰 경우

 MariaDB는 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용한다. 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록한다. 그리고 그 다음 레코드를 가져와서 다시 정렬하고 디스크에 임시 저장하는 과정을 반복한다.

 

 각 버퍼 크기 만큼씩 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 하는데, 이 병합 작업을 멀티 머지(Multi-merge)라고 한다. 수행된 멀티 머지 횟수는 sort_merge_passes 라는 상태 변수에 누적된다. (show status variables; 명령 참조)

 

 소트 버퍼의 크기는 256KB ~ 512KB 사이에서 최적의 성능을 보였다.

 

 MariaDB가 사용하는 메모리는 글로벌 메모리와 세션(로컬) 메모리 영역으로 나뉜다. 이때 정렬을 위해 사용하는 소트 버퍼는 세션 메모리 영역으로, 여러 클라이언트가 공유해서 사용할 수 없는 영역이다. 따라서 커넥션이 많을수록, 정렬 작업이 많을수록 소트 버퍼로 소비되는 메모리 공간이 커진다.

 

정렬 알고리즘

싱글 패스(Single pass) 알고리즘

 소트 버퍼에 정렬 기준 컬럼을 포함해 select 되는 컬럼을 전부 담아서 정렬을 수행하는 방법이다. MySQL 5.0 이후 도입된 정렬 방법이다.

 

투 패스(Two pass) 알고리즘

 정렬 대상 컬럼과 primary key 값만을 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 primary key로 테이블을 읽어서 select 할 컬럼을 읽어오는 알고리즘이다. MySQL 5.0 이전에서 사용하던 방법이지만 정렬하려는 레코드의 특성에 따라 MariaDB 10.0 버전에서도 여전히 사용되는 경우가 있다.

 

* 싱글 패스 알고리즘은 투 패스 알고리즘에 비해 사용하는 메모리 공간이 더 크다.

 

투 패스 알고리즘을 사용하는 경우

• 레코드의 크기가 max_length_for_sort_data 파라미터로 설정된 값보다 클 때
• BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때

 

 일반적으로 새로운 정렬 알고리즘인 싱글 패스 방식을 사용하지만 아래의 경우엔 투 패스 알고리즘을 사용한다.

• 레코드의 크기가 max_length_for_sort_data 파라미터로 설정된 값보다 클 때
• BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때

 

---

참고자료

http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9788998139537&orderClick=LAG&Kc= 

Real MariaDB - 교보문고