ORDER BY와 인덱스의 관계 + sort_buffer_size

데이터베이스에서 ORDER BY 절은 결과를 특정 컬럼 기준으로 정렬할 때 사용된다. 그리고 해당 작업이 효율적으로 이루어지려면, 적절한 인덱스를 사용하는 것이 중요하다.

 

ORDER BY & 인덱스

---

인덱스의 역할

인덱스는 데이터베이스에서 검색과 정렬 성능을 향상시키는 데 중요한 역할을 한다. 특히 ORDER BY 절에서 인덱스를 활용하면 불필요한 정렬 작업을 피하고 효율적으로 결과를 반환할 수 있다.

 

주의점

인덱스를 ORDER BY 절에서 사용해야 할 경우 주의할 점이 존재한다.

인덱스가 (a,b,c) 인 경우

order by b, c

• 인덱스 첫번째 컬럼인 a가 누락되어 사용 불가

order by a, c

• 인덱스에 포함된 b 컬럼이 a, c 사이에 미포함되어 사용 불가

order by a, c, b

• 인덱스 컬럼과 order by 컬럼간 순서 불일치로 사용 불가

order by a, b desc, c

• b 컬럼의 desc 로 인해서 사용 불가

order by a, b, c, d

• 인덱스에 존재하지 않는 컬럼 d로 인해 사용 불가

 

다음과 같은 ORDER BY  쿼리는 인덱스 적용이 가능하다.

WHERE a = 1 
ORDER BY b, c

WHERE a = 1 and b = 'b'
ORDER BY c

가능한 이유는 위 쿼리가 실제로 WHERE a = 1 ORDRE BY a, b, c 와 동일한 쿼리이기 떄문이다.

옵티마이저가 적절하게 실행 계획을 결정해준다고 이해하면 된다.

 

 

ORDER BY 쿼리

예시

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT, -- 주문 ID (기본 키)
    customer_id BIGINT NOT NULL,             -- 고객 ID
    order_date BIGINT NOT NULL,            -- 주문 날짜
    total_amount DECIMAL(10, 2),             -- 주문 총액
	sts VARCHAR(20),                      -- 주문 상태
    hash_email VARCHAR(500),
    INDEX idx_hash_email (hash_email),       -- 해시 인덱스
    INDEX idx_customer_sts_date (customer_id, order_date) -- 복합 인덱스
);

 

EXPLAIN SELECT customer_id, order_date 
FROM orders 
ORDER BY customer_id, order_date;

• idx_customer_sts_date 인덱스에 의해 정렬이 수행된다.

 

EXPLAIN SELECT * 
FROM orders 
ORDER BY sts, order_date;

• 인덱스를 사용하지 않고 filesort 방식으로 정렬이 이루어진다.

 

---

정렬과 sort_buffer_size

---

인덱스를 사용할 수 없는 경우, 데이터베이스는 정렬 작업을 메모리에서 수행한다. 이 과정에서 중요한 설정이 sort_buffer_size 이다.

 

sort_buffer_size란?

sort_buffer_size는 MySQL에서 정렬 작업에 할당되는 메모리 크기를 결정하는 설정이다.

 

sort_buffer_size를 사용한 정렬 과정

1. 정렬 버퍼 할당: 데이터베이스는 정렬 작업을 위해 메모리에 정렬 버퍼를 생성한다.
2. 정렬 수행: 데이터가 메모리에 적재되고 정렬된다.
3. 디스크 사용 여부 결정: 데이터 크기가 sort_buffer_size를 초과하면 임시 파일을 생성해 디스크에서 정렬 작업을 수행한다.

 

정렬 성능과 메모리

sort_buffer_size 값이 충분히 크다면 정렬 작업을 메모리 내에서 완료할 수 있어 성능이 향상된다. 그러나 너무 큰 값을 설정하면 서버 자원이 과도하게 사용될 수 있다. 적절한 크기를 설정하기 위해 다음과 같은 사항을 고려해야 한다.

• 평균 정렬 데이터 크기
• 서버의 가용 메모리
• 동시에 실행되는 쿼리 수

 

sort_buffer_size와 인덱스 비교

인덱스를 활용할 수 있다면 정렬 버퍼를 사용할 필요가 없다. 따라서 쿼리를 최적화할 때 가장 먼저 인덱스를 고려하는 것이 중요하다. sort_buffer_size는 인덱스를 사용할 수 없는 경우를 대비한 보조적인 설정이다.

 

---

정리

---

• ORDER BY 절은 인덱스를 활용할 때 가장 효율적이다.
• 단일 컬럼 정렬은 해당 컬럼의 인덱스만으로 최적화가 가능하며, 다중 컬럼 정렬은 다중 컬럼 인덱스를 활용해야 한다.
• 인덱스를 사용할 수 없는 경우, 데이터베이스는 메모리와 디스크를 사용해 정렬을 수행하며, 이 과정에서 sort_buffer_size가 중요한 역할을 한다.
• sort_buffer_size를 적절히 설정해 메모리 사용을 최적화하는 동시에, 정렬 작업이 디스크로 넘어가지 않도록 해야 한다.

 

참고

https://jojoldu.tistory.com/481

2. 커버링 인덱스 (WHERE + ORDER BY / GROUP BY + ORDER BY )