MySQL ‐ ORDER BY - dnwls16071/Backend_Summary GitHub Wiki

📚 ORDER BY

	장점	단점
인덱스 이용	INSERT, UPDATE, DELETE 등의 쿼리가 실행될 때, 이미 인덱스가 정렬되어 있어서 순서대로 읽기만 하면 되므로 매우 빠르다.	INSERT, UPDATE, DELETE 등의 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 인덱스 때문에 디스크 공간이 더 많이 필요하다. 인덱스 개수가 늘어날수록 InnoDB 버퍼 풀을 위한 메모리가 많이 필요하다.
filesort 이용	인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀐다. 정렬해야 할 레코드가 많지 않다면 메모리에서 filesort가 처리되므로 충분히 빠르다.	정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리 응답 속도가 느리다.

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아 사용하는데, 이 메모리 공간을 소트 버퍼라고 한다.
소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼 크기는 정렬해야 할 레코드 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있다. 소트 버퍼를 위한 메모리 공간은 쿼리 실행이 완료되면 즉시 시스템으로 반납된다.
메모리 소트 버퍼에서 정렬을 수행하고 그 결과를 디스크에 임시로 기록해 둔다. 그리고 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장한다.
이처럼 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행하는데 이 병합 작업을 멀티 머지(multi-merge)라고 표현하며 수행된 멀티 머지 횟수는 누적해서 집계가 된다.
이러한 작업들이 모두 디스크와의 읽기/쓰기를 유발하며, 레코드 건수가 많을수록 이 반복 작업의 횟수가 많아지게 된다.

❗그렇다면 소트 버퍼의 크기를 늘리면 되는 것 아닌가?

소트 버퍼를 크게 설정해서 빠른 성능을 얻을 순 없지만, 디스크 읽기/쓰기 사용량은 줄일 수 있다.
그래서 MySQL 서버 데이터가 많거나 디스크 I/O 성능이 낮은 장비라면 소트 버퍼 크기를 더 크게 설정하는 것이 도움이 된다.
하지만 소트 버퍼를 너무 크게 설정하면 서버의 메모리가 부족해져서 MySQL 서버가 메모리 부족을 겪을 수 있기 때문에 소트 버퍼 크기는 적절히 설정하는 것이 좋다.
그리고 대량 데이터의 정렬이 필요한 경우 해당 세션의 소트 버퍼만 일시적으로 늘려서 쿼리를 실행하고 다시 줄이는 것도 좋은 방법이다.

쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 처리 방법 중 하나로 정렬이 처리된다. 일반적으로 아래쪽에 있는 정렬 방법으로 갈수록 처리 속도는 저하된다.

정렬 처리 방법	실행 계획의 Extra 칼럼 내용
인덱스를 사용한 정렬	별도 표기 없음
조인에서 드라이빙 테이블만 정렬	"Using filesort"
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	"Using temporary; Using filesort"