RealMySQL 9.2.3.2 - 9.2.3.3

정렬 알고리즘

• 레코드 정렬할 때, 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 2가지 정렬 모드가 있다.

• 정렬을 수행하는 쿼리가 어떤 정렬 모드를 사용하는지는 옵티마이저 트레이스 기능으로 확인 가능

• 싱글 패스 정렬 방식
  • 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식이다.

• 투 패스 정렬 방식
  • 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 정렬 방식이다.

• 싱글 패스는 더 많은 소트 버퍼 공간이 필요하고, 투 패스는 테이블을 두번 읽어야해서 비합리적이다.
• 대상 레코드가 작은 경우 싱글 패스, 대상 레코드가 클 경우 투 패스가 효율적이다.

정렬 처리 방법

• 쿼리에 ORDER BY가 사용되면 3가지 방법 중 하나로 정렬이 처리 된다.
• 아래쪽에 있는 정렬 방법으로 갈수록 처리 속도는 떨어진다.

• 인덱스를 이용한 정렬
  • ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다. 또한 WHERE절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다. B-Tree 계열의 인덱스여야 한다.
  • B-Tree 인덱스는 키 값으로 정렬돼 있기 때문이다.
  • 드라이빙 테이블 : 조인시 먼저 액세스 되는 쪽

• 조인에서 드라이빙 테이블만 정렬
  • 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행한다.
  • 이 방법을 사용하려면 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY 절을 작성해야 한다.

• 조인에서 조인 결과를 임시 테이블로 저장 후 정렬
  • ORDER BY절의 정렬 기준 칼럼이 드라이빙 테이블이 아니라 드리븐 테이블에 있는 칼럼인 경우 이 방식이 적용된다.
    쿼리 실행 계획에서 Extra 칼럼에 Using temporary가 표시 된다.

• 정렬 처리 방법의 성능 비교 쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 느리게 작동하는 이유를 알아보기 위해, 쿼리가 처리되는 방법 2가지

• 스트리밍 방식
  • 서버 쪽에서 처리할 데이터양에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트로 전송해주는 방식이다.
  • 클라이언트는 MySQL 서버가 일치하는 레코드를 찾는 즉시 전달 받기 때문에 동시에 데이터 가공 작업을 시작할 수 있다.
  • LIMIT을 사용하는 경우, 마지막 레코드를 가져오기 까지의 시간을 줄일 수 있다.

• 버퍼링 방식

  • ORDER BY나 GROUP BY 같은 쿼리는 정렬이나 그루핑이 필요해서 스트리밍 방식을 사용할 수 없다.
  • MySQL 서버가 레코드를 검색하고 작업하는 동안 클라이언트는 대기해야하므로 응답 속도가 느려진다.

• 인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이다.
• 정렬 처리 방법에 따라 성능 차이가 크다. 가능하면 인덱스 사용 정렬로 유도하고,
  최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하자.