[MySQL] ORDER BY Optimization

MySQL

ORDER BY Optimization

MySQL Order by 사용시 인덱스를 사용 조건에 충족하다면 인덱스를 이용해서 정렬을 하겠지만
인덱스를 사용할 수 없는 경우에는 filesort를 사용을 한다.

ORDER BY 인덱스 사용 충족 조건

Order by는 꼭 인덱스와 매치가 안되더라도 인덱스를 사용할 수도 있다. 각설하고 실제 예시를 통해 알아보자.

SELECT *
FROM t1
ORDER BY col_a, col_b;

테이블 t1에 복합 인덱스로 index (col_a, col_b) 가 생성되어 있다고 가정을 하자.
MySQL의 Optimizer는 실행시 인덱스를 읽는게 효율적이라고 생각 한다면 인덱스를 사용할 것이고 그것을 통해서 추가적인 정렬하는 자원을 방지 할 수도 있다.

SELECT * 은 col_a, col_b 에서 두개 컬럼 외에 다른 컬럼을 추가적 조회 하기 때문에 이런 경우 데이터를 찾는것과 정렬에서 조금 더 자원을 필요로 한다. 이런 경우에서 Optimizer는 인덱스를 사용을 안할 수도 있다. 하지만 인덱스 컬럼인 col_a, col_b 만 조회를 한다면 인덱스를 사용할 것이다.

만약 테이블이 InnoDB라고 한다면 Primary Key가 암묵적으로 인덱스에 포함 됨으로 다음처럼 조회할 수 있다.

SELECT pk, col_a, col_b
FROM t1
ORDER BY col_a, col_b;

아래 경우들은 인덱스 사용 조건에 충족하는 케이스들이다.

SELECT *
FROM t1
WHERE col_a = value // (col_a > value) (col_a < value) 두개 경우도 포함
ORDER BY col_b;

SELECT *
FROM t1
ORDER BY col_a DESC, col_b DESC;

SELECT *
FROM t1
ORDER BY col_a DESC, col_b ASC;

SELECT *
FROM t1
WHERE col_a = value ADN col_b = value
ORDER BY col_b;

다음은 인덱스를 사용할 수 없는 경우이다.

SELECT *
FROM t1
ORDER BY col_c, col_d;

SELECT *
FROM t1
WHERE col_c = value
ORDER BY col_a, col_b;

ORDER BY를 수행하기 위한 filesort

MySQL에서는 쿼리 호출에서 Order By 절에서 인덱스만으로 만족이 힘들다면 filesort라는 쿼리 실행에서 추가적인 정렬를 구성한다.
filesort 명령은 기본적으로 메모리를 사용하며 sort_buffer_size로 사이즈 설정이 가능하며 메모리에 담기에는 크기가 너무 크다면 임시로 Disk도 사용할 수 있다.

sort_buffer_size 설정값 증가는 이상적으로는 디스크 사용을 줄여 모두 메모리를 사용할 수 있도록 할 수 있다.

sort_buffer_size 설정은 max_sort_length와 read_rnd_buffer_size에 영향을 받는데 각각 sort buffer에 컬럼 값 저장의 크기, 한번에 읽는 row 개수 이다.

Sort_merge_passes 상태 값을 이용하여 임시 디스크 사용한 것들에 개수들을 모니터링 할 수 있다.

 

---

LIMIT Query Optimization

특정 몇개 Row를 가져오기 위해서는 LIMIT을 사용할 수 있는데 이는 필요하지 않는 데이터를 안가져오는 효과와 최적화 효과를 가져올 수 있다.

• 인덱스를 사용할 수 있도록 해주며, ORDER BY가 잇다면 MySQL은 필요한 만큼의 Row만 정렬을 한다. 만약 정렬 과정에서 filesort가 아니라 인덱스를 사용한다면 매우 효과적 일 것. 또한 filesort로 동작을 해도 최대한 인-메모리 작동을 하려고 할 것이다. 
• DISTINCT와 함께 사용 필요한 유니크 Row 만큼 찾는다.
• GROUP BY와 함께 사용시 필요하지 않은 계산을 생략할 수 있다.

---

 

Ref

https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html#order-by-index-use

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization

 

https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.19 LIMIT Query Optimization