[mySQL] OrderBy 인덱스 (3)

개발 이론/DB

[mySQL] OrderBy 인덱스 (3)

dal_been 2024. 3. 10. 21:42

728x90

지난 블로그에서 인덱스 종류와 커버링 인덱스에 대해서 알아보았다.

오늘은 OrderBy 인덱스에 관해서 이야기해볼예정이다.

Order BY 인덱스

Order By절에 명시된 칼럼에 인덱스가 있다면 MySQL은 이미 정렬된 데이터를 사용하기 때문에 추가적인 정렬작업이 필요없다.

그러나 만약 없다면 filesort방식으로 임시 테이블을 사용해 정렬 작업을 수행한다.

filesort 이용하고 있는지 여부는 EXPLAIN 명령어를 통해 Extra칼럼에 Using filesort가 있다면 filesort를 사용하고 있다는 것이다.

왜 Order By절에 명시된 칼럼에 인덱스를 사용하는 것이 좋을까?

-> Limit절과 사용하게 된다면 인덱스가 있을때 MySQL은 필요한 만큼만 데이터를 읽고나서 처리를 중단한다. 그러나 인덱스가 없다면 전체 데이터를 정렬한 후 원하는 행의 수만큼 추출한다.

Using filesort라면 어떻게 filesort할까??

정렬해야하는 데이터가 적으면 메모리 수준에서 sort buffer가 되지만 데이터가 많다면 쪼개서 디스크에 저장 + 정렬 후 반환한다.

1. sort buffer

정렬 데이터가 많다면 디스크 수준 정렬을 한다고 했다. 이때 sort_buffer_size를 증가시켜서 디스크 정렬을 최소화 하도록 최적화할 수 있다.

(디스크 정렬 수행 여부는 sort_merge_passes변수 통해서 알 수 있다)

2.sort buffer 방법 : single-pass, two pass

single - pass 의경우 sort-buffer에 데이터를 모두 넣어서 정렬한다.(select 되는 칼럼을 전부 담아서 정렬)

two - pass 의경우 정렬하는 칼럼과 PK만 넣어서 정렬후 나머지 데이터는 PK로 테이블을 읽어 select한다.

일반적으로 single-pass가 성능이 좋다고 하나, 데이터가 큰 경우 two-pass가 성능적으로 우위에 있다고 한다.

Order By 인덱스 예제

CREATE TABLE product (
                         id INT AUTO_INCREMENT PRIMARY KEY,
                         name VARCHAR(100),
                         price DECIMAL(10, 2),
                         created_at DATETIME
);

EXPLAIN SELECT * FROM product
ORDER BY created_at DESC, price ASC LIMIT 10;

1. 인덱스 생성 전

rows를 보면 약 10만개의 데이터를 정렬한후 10개의 데이터를 뽑아낸 것이다.

약 265ms정도 걸린다.

2. 인덱스 생성

CREATE INDEX idx_created_at_price ON product(created_at DESC, price ASC);

Using filesort가 없고 key에 인덱스가 존재한다는것은 인덱스를 이용하고 있다는 뜻이다.

약 0.572ms정도 걸린다.

확실히 인덱스를 생성하니 265 -> 0.572 로 개선되었다.

Sort Buffer Size

show variables like 'sort_buffer_size';
show status like 'sort_merge_passes';

통해서 sort_buffer_size를 확인하고 디스크 정렬했는지 알기 위해 현재 sort_merge_passes값을 확인한다.

현재 size는 262144이고 passes는 0이다

EXPLAIN  SELECT * FROM product
ORDER BY created_at DESC, price ASC LIMIT 1000;

일단 Using filesort를 사용하고 있음을 확인하고 쿼리 시간은 306ms정도 걸린다.

sort_merge_passes를 보니

디스크 정렬이 발생했음을 알수 있다.

그렇다면 sort_buffer_size를 올려보자

set session sort_buffer_size  = 2*262144;
show variables like 'sort_buffer_size';

그런후 쿼리 실행시간을 보였더니 254ms로 단축되었다.

sort_merge_passes가 똑같은걸로 보아 디스크 정렬이 발생하지 않았다.

즉 sort_buffer_size 증가로 디스크 정렬이 발생하지 않고 속도는 단축되었다.

현재 프로젝트에는 인덱스와 관련된 설정이 없다. 한번 시간나면 orderBy에 인덱스를 적용해서 쿼리 실행속도를 개선시켜봐야겠다.

https://www.inflearn.com/course/mysql-%EC%84%B1%EB%8A%A5-%EC%B5%9C%EC%A0%81%ED%99%94/dashboard