[MySQL] Order By 사용 전략

💡 July님의 200억건의 데이터를 MySQL로 마이그레이션 할 때 고려했던 개념과 튜닝 방법 강의를 듣고 정리한 내용입니다.

 

목차

Order By

이번에는 실무에서 큰 데이터를 다루는데 Order By를 사용하는 전략에 대해서 알아본다. 대용량 데이터를 다루는 경우 Order By는 골칫덩어리 같은 존재가 된다. 그 이유는 Order By는 데이터를 정렬하면서 추가적인 리소스가 요구되기 때문이다.

따라서 이 경우 인덱스 설정에 유의해야한다. 또한 쿼리에 Order By 절이 포함되거나 정렬해야하는 필드에 대해 IN, OR 연산을 사용하는 것은 지양한다. 정렬하는 필드에 대해 OR 연산을 사용하는 경우 연산이 2배로 들고, IN 연산을 사용하는 경우 추가된 필드만큼 연산이 n배 더 든다고 생각하면 된다.

SELECT * FROM test1 WHERE first_name = "abc" OR second_name = "abc" ORDER BY time DESC

예를 들어 다음과 같은 쿼리에서 WHERE 절에 first_name 조건만 있다면 한 로우에 한 컬럼만 확인하면 된다. 하지만 OR 연산으로 first_name과 second_name 컬럼 값을 보고 있으므로 한 로우를 조회할 때 두개의 컬럼을 확인해야한다. 결과적으로 2배의 리소스가 요구된다. 

File Sort

인덱스가 설정되지 않은 컬럼에 대해 정렬하는 방식을 File Sort 라고 한다. 당연히 이경우 풀 스캔이 일어나므로 효율적으로 동작하지는 않는다. 하지만 기본적으로 메모리로 동작하기때문에 하드 디스크에서 정렬을 수행하는 것보다는 빠르게 동작한다. 그러나 정해진 sort_buffer_size 만큼의 메모리만을 사용하며 만약 이 값을 넘어갈 정도로 데이터가 많은 테이블에 대해 Order By 사용한다면 디스크 I/O가 발생하면서 리소스를 더 많이 사용하고 성능이 저하된다.

예제

예제 확인을 위해 다음과 같이 a, b 컬럼에 대해 인덱스 a_b가 설정된 users 테이블을 생성한다.

CREATE TABLE users (
	id INT AUTO_INCREMENT PRIMARY KEY,
    a VARCHAR(255),
    b VARCHAR(255),
    c VARCHAR(255),
    INDEX a_b(a, b)
);

예시 1) Index를 사용한 Order By

다음 쿼리를 실행해보면 a_b 인덱스를 사용하여 정렬하고 조회한 것을 볼 수 있다.

EXPLAIN SELECT a, b FROM users ORDER BY a, b;

결과는 다음과 같다. type이 index인 것을 볼 수 있다. type의 관한 설명은 [MySQL] Multi Column Index(복합 인덱스)#정리를 참고한다.

 

예시 2) File sort

이 경우는 인덱스에 존재하지 않는 컬럼에 대해 Order By를 수행하므로 Full scan이 발생한다. 인덱스를 사용하지 않았으므로 Filse Sort 방식으로 정렬한다.

EXPLAIN SELECT * FROM users ORDER BY c, a;

이 쿼리의 실행 결과는 다음과 같다. type이 ALL인 것을 볼 수 있다.

 

 

Order By 사용 전략

Order By를 잘 사용하는 전략을 결국 인덱스를 잘 설계하고 이를 고려하여 쿼리를 사용하는 것이다. 또한 대용량 데이터를 다루는 경우 sort_buffer_size를 잘 조정하면 디스크 I/O를 줄여 성능을 향상할 수 있다. 하지만 서버의 메모리 자원은 굉장히 한정적이며, MySQL에서 서버의 메모리를 많이 할당하는 부분이 있다.

 

이로인해 실무에서는 종종 메모리가 부족해서 sort_buffer_size을 사용할 수 없는 경우가 있다. 따라서 DBA와 함께 모니터링하면서 적절한 값으로 설정하는 것이 중요하다.

 

또한 가능한 데이터 타입을 한정하는 것이 중요하다. (물론 DBA와 잘 협의하는 것이 중요하다.) 예를 들어서 위 예시에서 a 필드는 항상 10자리 String이 들어온다면, 타입을 VARCHAR(255)이 아닌 VARCHAR(10)으로 지정하는 식이다. 데이터베이스에서 데이터를 조회할때 일반적으로 정의된 타입의 크기만큼 메모리를 차지한다. 따라서 10자리 String만 들어오는데 타입을 VARCHAR(255)으로 선언하면 불필요한 공간을 더 차지하는 것이다.

 

이처럼 아껴둔 메모리 자원은 sort_buffer_size에서 사용할 수도 있다. 물론 DBA와 함께 소통하면서 설정해야한다. 실무에서는 이렇게 데이터 타입을 한정하면서 최적화하기는 어려울 수 있다.