[REAL MYSQL 8.0] 옵티마이저 와 실행계획 (2) - 정렬

MySQL 의 정렬 처리 특징

 

MySQL 은 정렬을 위한 별도의 메모리 공간을 쿼리 수행시 할당 받는다. 이를 Sort Buffer 소트 버퍼 라고 하며

버퍼의 크기는 정렬 대상의 크기에 따라 최대 sort_buffer_size 변수 만큼 가변적으로 할당 받는다.

소트버퍼는 쿼리 수행 완료후, 바로 반납된다.

 

그렇다면 소트 버퍼보다 예상 레코드 수가 크다면 어떻게 처리될까?

소팅을 버퍼의 크기만큼 여러 조각으로 나누어, 매 처리 결과를 디스크에 임시저장을 한 후 병합한다. 이를 멀티 머지라고 한다.

디스크 I/O 가 계속 발생하는 것이므로, 성능에 영향이 있다. 하지만 이를 해결하기 위해 sort_buffer_size 를 늘리는 것은 옳지 않다고 한다. 소트 버퍼를 늘려도 속도에 대한 효과는 미미하고, 오히려 사용자간 공유 공간이 아닌 메모리 공간이기 때문에 OOM 을 유발할 가능성이 크다. sort_buffer_size 는 속도에 대한 최적화 보다는 Disk 성능이 좋지 않은 서버를 사용할 때 Disk I/O 에 대한 최적화를 할 수 있다고 보면 좋겠다.

 

 

MySQL 옵티마이저의 정렬 처리 분류

정렬 방식에 대한 분류

 

옵티마이저가 정렬(sorting)을 처리하는 방법은 크게 file sort 와 인덱스를 이용하는 방법 두가지로 분류될 수 있다.

 

인덱스를 이용하는 방법은 사실 인덱스가 삽입,변경,삭제 시 정렬된 상태를 유지하기 때문에 실제 쿼리 수행시 정렬을 하는 방법은 아니다.

반면에 file sort 란 쿼리를 수행할 때마다, 결과 값에 대한 정렬을 수행하여 반환하는 방법을 뜻한다.

 

물론 인덱스를 활용하는 편이 이점이 크지만, 아래와 같은 이유로 모든 정렬을 인덱스로 처리하기는 쉽지 않다.

• 정렬 기준이 너무 많아, 최적화된 인덱스를 모두 만들 수 없는 경우
• GROUP BY, DISTINCT 결과에 대한 정렬을 처리하는 경우
• UNION 같은 임시 테이블의 결과를 정렬하는 경우
• 랜덤하게 레코드를 가져와야 하는 경우

 

정렬 알고리즘에 대한 분류

MySQL 의 Sort Buffer 를 이용하는 정렬 알고리즘 방식은 두가지로 나뉜다. 

 

• 조회 대상 모든 칼럼을 버퍼에 저장 했다가 바로 반환
• 정렬 대상과 PK 만 저장 후, 정렬 순서대로 다시 PK 로 테이블을 조회하는 방식 

각각의 방식을 책에서는 싱글패스, 투패스 방식이라고 명명한다.

 

싱글패스 방식이 합리적이긴 하지만 버퍼의 크기에 따라 한번에 처리 가능한 수에 한계가 있기 때문에

레코드의 크기가 클 수록, 또 조회 건수가 상당히 많은 경우에는 투 패스가 유리한 경우도 있다.

 

최신 버전에서는 싱글패스 방식이 주로 채택되지만, 아래의 경우에는 투 패스 방식이 채택된다.

• 레코드의 크기가 max_length_for_sort_data 보다 큰경우
• BLOB 이나 TEXT 컬럼이 조회 대상에 포함된 경우.

 

MySQL 옵티마이저의 정렬 처리 

각 정렬 처리 방식의 분류를 살펴보았으니 실제로 MySQL 에서 정렬이 어떻게 동작하는지 살펴보자.

아까 인덱스를 이용하는 방식을 file sort 와 인덱스를 이용하는 방식으로 나누었는데, 조금 더 자세히 나누면 3가지 방법으로 분류할 수 있다.

 

• 인덱스를 사용하는 방법 (extra column = "")
• 조인에서 드라이빙 테이블만 정렬해서 조인하는 방법 (extra column = "using filesort;")
• 조인에서 조인 결과를 임시테이블로 저장한 후 정렬하는 방법 (extra column = "using temporary; using filesort)

대충 봐도 알겠지만, 속도는 위에서 아래로 갈 수록 느리다. 

하나씩 동작하는 방식을 살펴보자.

 

인덱스를 이용한 정렬

인덱스를 이용한 정렬은 다음 두가지 조건을 만족하여야 한다.

• ORDER BY 절에 지정된 순서대로 생성된 인덱스 존재
• WHERE 절 첫번째로 읽는 테이블(드라이빙 테이블)의 칼럼에 대한 조건이 있다면, 해당 조건과 ORDER BY 가 같은 인덱스를 사용할 수 있어야 함.

당연하게도 Hash 나 전문검색 인덱스는 인덱스를 이용한 정렬이 불가능하며 R-TREE 의 경우 NESTED 방식의 조인에서만 정렬에 활용할 수 있다. 또한 인덱스를 이용한 정렬은 실제로 쿼리 수행시 정렬을 하는 것이 아니라 정렬된 데이터를 순서대로 읽어오는 것이기 때문에

ORDER BY 절을 명시한것과 명시하지 않은 것이 같은 결과를 뱉는다. 

 

SELECT * 
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
	AND e.emp_no BETWEEN 10002 AND 100020 -- 해당 조건으로 인해 employees 가 드라이빙
ORDER BY e.emp_no; -- optional 하지만 명시적으로 표시해주는것이 바람직하다.

 

조인의 드라이빙 테이블만 정렬

조인을 수행하면, 결과 레코드 수는 몇배로 불어난다. 드라이빙 테이블에서 미리 정렬 후, 조인하는편이 성능적으로 유리하다.

 

SELECT * 
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
	AND e.emp_no BETWEEN 10002 AND 100020
ORDER BY e.last_name;

인덱스를 이용한 정렬에서 사용했던 예제와 거의 유사한 쿼리지만 정렬 대상 칼럼이 인덱스 컬럼이 아니다.

위 쿼리는 조건절에 의해서 employees 테이블이 드라이빙 테이블로 선정되었고, 드라이빙 테이블의 컬럼이 정렬 대상 칼럼이다.

이런 경우 드라이빙 테이블 (employees) 의 조건에 맞는 결과 (BETWEEN 10002 ~ 100020 18건) 을 가져와 정렬 한 후에

조인을 수행한다. 즉 정렬 작업은 조인 후 결과 레코드가 얼마가 되었건 18건에 대해서만 일어나면 된다는 것이다.

 

 

임시 테이블을 이용한 정렬

이 방법은 모든 쿼리를 처리 한 후, 최종 결과를 정렬하는 방식이다.

당연하게도 위의 두 방식이 훨씬 속도가 빠르지만,  위의 두 방식의 사용이 불가능한 경우에 불가피하게 채택된다.

 

SELECT * 
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
	AND e.emp_no BETWEEN 10002 AND 100020
ORDER BY s.salary;

2번의 예제와 거의 유사하지만 정렬 대상 컬럼이 드라이빙 테이블의 컬럼이 아니라, 드리븐 테이블의 컬럼이다.

이런 경우는 정렬 수행을 위해 salary 테이블을 읽을 수 밖에 없으므로 조인이 완료된 후 정렬을 수행한다.

 

 

.... 스트리밍방식 버퍼링방식.. 추가 정리