mysql 정렬

https://weicomes.tistory.com/155

1. 인덱스를 이용한 정렬

• insert, update, delete 처리될때 이미 인덱스가 정렬되어 있어 순서대로 읽기만 하면 됨
• 단점: 부가적인 인덱스 추가/삭제 작업 필요, 인덱스 때문에 디스크 공간이 많이 필요함, 인덱스 개수가 늘어날 수 록 InnoDB의 버퍼 풀이나 MyISAM 키 캐시용 메모리가 많이 필요함

2. filesort 이용

• 장점: 인덱스를 생성하지 않아도 되므로, 인덱스 정렬의 단점을 커버 가능함
• 정렬해야하는 레코드가 많지 않으면 메모리에서 filesort가 처리됨
• 단점: 속도가 느림

3. 인덱스 정렬로 튜닝하기 어려운 상황

• 정렬기준이 너무 많은 경우
• GROUP BY의 결과 또는 DISTINCT와 같은 처리의 결과를 정렬해야하는 경우
• UNION 과 같이 임시테이블을 다시 정렬해야하는 경우
• 랜덤하게 레코드를 가져와야하는 경우

4. 소트 버퍼

• mysql은 정렬을 수행하기 위해 메모리 공간을 할당 받아서 사용
• 이 공간을 소트 버퍼라고 함
• 버퍼의 크기는 정렬할 레코드의 크기에 따라 가변적으로 증가하지만, 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있음
• 실행 완료후 반납

5. 정렬이 문제가 되는 경우

• 레코드의 양이 소량인 경우 메모리의 소트 버퍼 만으로 정렬 가능=> 정렬이 빠르게 처리됨

• 레코드 수가 소트 버퍼로 할당된 공간보다 크다면, MySQL은 레코드를 여러 조각으로 처리하는데 이 과정에서 임시 저장을 위해 디스크를 사용

  1. 소트 버퍼에서 정렬 수행
  2. 결과를 임시로 디스크에 저장
  3. 다음 레코드와서 다시 정렬해서 반복적으로 디스크에 임시저장

• 병합 작업을 멀티 머지라고 표현하며, 수행된 멀티 머지 횟수는 sort_merge_passes라는 상태 변수에 누적

• 레코드 건수가 많을 수록 반복작업의 횟수가 많아지는데, 소트 버퍼를 크게 설정하면 디스크를 사용하지 않아, 빨라질 것으로 예상되지만 실제 벤치마크 결과는 거의 차이가 없음