search

woowacourse-study / 2022-Real-MySQL

⚡️토르⚡️의 짜릿한 Real MySQL 뽀개기 🔨
9 stars 3 forks source link

MySQL 옵티마이저의 정렬 처리 방법 #21

Open awesomeo184 opened 2 years ago

awesomeo184 commented 2 years ago

주제

정렬 처리는 옵티마이저가 판단하고 수행한다. 여기에는 크게 세 가지 방법이 있다.

어떤 상황에서 어떤 방법을 사용하는지 대략적으로 알아본다.

선정 이유

옵티마이저가 정렬을 할때 어떤 식으로 동작하는지 잘 이해한다면, 정렬을 사용할 때 칼럼 선택을 좀 더 효율적으로 선택할 수 있을 것 같다.

해당 텍스트

MySQL에서 정렬 처리 방법은 인덱스를 이용하는 방법과 “Filesort”라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.

아래의 세 가지 방법이 있고 위에서 아래로 내려갈수록 처리 속도가 느려진다.

인덱스를 이용하는 방법의 장점은 이미 인덱스가 정렬돼 있기 때문에 순서대로 읽기만하면 돼서 매우 빠르다는 점이다. 단점은 역시 INSERT, UPDATE, DELETE 작업에서 인덱스 추가/삭제 작업이 필요하므로 느리다는 것이다. Filesort를 이용하는 방법의 장단점은 정확히 인덱스의 장단점의 반대이다.

정렬을 할 때는 정렬을 위한 소트 버퍼가 따로 할당된다(당연히 인덱스를 사용하지 못하는 경우이다). 만약 정렬할 레코드가 소트 버퍼의 크기보다 크다면 임시 결과를 디스크에 저장해두기 때문에 디스크 입출력이 발생한다. 하지만 성능 상에서 큰 차이를 보이지는 않는 것으로 확인된다.

인덱스를 사용한 정렬

인덱스를 사용하는 경우에는 ORDER BY 컬럼을 명시적으로 적지 않아도 정렬된 상태로 조회가 된다. 하지만 ORDER BY로 인한 성능 차이가 거의 나지 않기도하고 ORDER BY를 명시하지 않을 경우 추후 실행 계획이 변경되었을 때 버그 발생의 원인이 될 수 있기 때문에 ORDER BY는 반드시 명시해주는 것이 좋다.

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

정렬을 할때 성능에 가장 큰 영향을 미치는 요소는, 드라이빙 테이블과 WHERE 절에 사용되는 칼럼이다.

드라이빙 테이블이란 조인을 할 때, 먼저 엑세스되는 테이블을 말한다. 드라이빙 테이블은 옵티마이저가 정해진 우선순위 규칙에 따라 결정하게 된다.

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

옵티마이저가 위 쿼리를 실행할 때 아래의 두 가지 이유로 인해 employees를 드라이빙 테이블로 선택한다.

조인 과정은 책 298페이지 그림 9.5 참고

임시 테이블을 사용한 정렬

2개 이상의 테이블을 조인해서 그 결과를 가지고 정렬을 해야하는 경우 임시 테이블이 필요할 수 있다. 이 경우 정렬할 데이터가 가장 많기 때문에 속도가 가장 느리다.

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

ORDER BY에 명시된 칼럼이 드리븐 테이블에 있는 칼럼이다. 따라서 조인된 결과를 가지고 정렬을 수행할 수밖에 없다.

정렬 알고리즘

일반적으로 싱글 패스 정렬 방식을 많이 사용하나, 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 크거나, BLOB 혹은 TEXT 타입의 칼럼이 SELECT 대상에 포함일 때 투 패스를 사용한다.

결론적으로, 정렬 대상의 레코드의 크기나 건수가 작은 경우 싱글 패스가, 정렬 대상의 레코드의 크기나 건수가 많은 경우 투 패스가 효율적이다.

관련 페이지

p.295 ~ 300

jurlring commented 2 years ago

정리 너무 잘 해주셨네용👍