Ch07. 인덱스 - 인덱스와 정렬

데이터베이스에서 정렬(ORDER BY) 작업은 생각보다 비용이 많이 드는 무거운 작업 중 하나다. 왜냐하면 조건에 맞는 데이터를 모두 찾은 후에, 그 결과를 서로 비교하면서 순서에 맞게 다시 정렬해야 하기 때문이다. 찾은 데이터가 수십, 수백만 건이라면 이 데이터를 정렬 알고리즘을 사용해서 정렬해야 하는데, 이 정렬 과정에서 엄청난 부하가 발생할 수 있다.

 

하지만 우리에게는 인덱스가 있다. 인덱스는 이미 데이터가 특정 순서로 정렬된 자료구조다. 그렇다면 이 정렬된 인덱스를 활용해서 "ORDER BY" 작업의 성능을 획기적으로 개선할 수 있지 않을까?

 

"ORDER BY"가 인덱스를 잘 활용하면, 별도의 정렬 과정 없이 이미 정렬된 인덱스를 순서대로 읽기만 하면 되므로 매우 빠르게 동작한다. 데이터베이스는 이 과정에서 "filesort" 라는 별도의 정렬 작업을 생략할 수 있게 된다. 여기서 "filesort"라는 이름만 보고 파일 시스템을 사용한다고 오해하면 안 된다. 실제로는 메모리나 디스크를 사용해 정렬하는 내부 프로세스를 의미한다. 우리의 목표는 바로 이 비효율적인 "filesort"를 피하는 것이다.

 

explain select * from items order by stock_quantity;

쿼리 결과

• Extra 항목에 "Using filesort"를 확인할 수 있다. 
• 데이터를 모두 찾은 후에 "stock_quantity"를 기준으로 정렬 작업이 추가된다.

인덱스를 사용해 정렬까지 한 번에 처리하는 경우

가장 이상적인 상황은 "WHERE"절의 조건과 "ORDER BY" 절의 정렬 기준이 같아서, 인덱스 하나로 검색과 정렬을 모두 해결하는 경우다. 앞서 "price"로 범위 검색을 했던 쿼리에 "ORDER BY price"를 추가해서 실행 계획을 확인해 보자.

explain select * from items where price between 50000 and 100000
order by price;

쿼리 결과

• type(range): "idx_items_price" 인덱스를 사용해 특정 범위(50000 ~ 100000)를 효율적으로 스캔했다.
• key(idx_items_price): "idx_items_price" 인덱스가 사용되었다.
• Extra(Using index condition): "WHERE" 조건 필터링에 인덱스를 사용했다

쿼리 동작 과정

• 데이터베이스 옵티마이저는 "idx_items_price" 인덱스가 이미 "price" 순서로 정렬되어 있다는 사실을 알고 있다.
• 따라서 "WHERE" 조건에 맞는 데이터를 찾기 위해 인덱스를 스캔하는 것만으로도 자연스럽게 "price" 순서로 정렬된 결과를 얻을 수 있다. 즉, 별도의 정렬 작업을 할 필요가 전혀 없는 것이다.

쿼리 실행 결과

select * from items where price between 50000 and 100000
order by price;

 

쿼리 결과

• 결과가 "price" 순서로 정렬되어 있는 것을 확인할 수 있다.
• 가장 오른쪽 "price" 컬럼을 확인해 보라. "ORDER BY price"를 명시하지 않았음에도 인덱스를 스캔한 순서 덕분에 이미 결과가 정렬된 것이다

인덱스를 역방향으로 조회하는 경우

"ORDER BY"를 사용할 때 항상 오름차순(ASC )으로만 정렬하는 것은 아니다. 쇼핑몰에서는 최신 상품이나 가격이 높은 상품을 먼저 보여주는 것처럼, 내림차순(DESC) 정렬도 매우 흔하게 사용된다.

 

그렇다면 "ORDER BY price DESC"처럼내림차순 정렬을 사용하면 "filesort"가 발생할까? 결론부터 말하면, 단일 컬럼 인덱스에서는 "filesort" 없이 효율적인 처리가 가능하다. 데이터베이스 옵티마이저는 인덱스를 거꾸로 읽는, 즉 역방향 스캔(Backward Index Scan)을 할 수 있기 때문이다.

 

"price"가 비싼 순서대로 상품을 조회하는 쿼리의 실행 계획을 살펴보자

explain select * from items where price between 50000 and 100000
order by price desc;

쿼리 결과

• Backward index scan: 옵티마이저가 "idx_items_price" 인덱스를 끝에서부터 앞으로, 즉 역순으로 스캔했음을 의미한다.
• 인덱스는 양방향 탐색이 가능하다. 따라서  "price"가 높은 값부터 낮은 값 순서로 인덱스를 탐색하여 "WHERE" 조건에 맞는 데이터를 찾는다.
• 이 과정에서 이미 정렬 순서가 만족되므로 별도의 "filesort" 작업이 필요 없다.

정방향 스캔이 역방향 스캔보다 미세하게 더 빠르다.
정방향 인덱스 스캔이 미세하게 더 빠른 이유는 컴퓨터 하드웨어의 "미리 읽기(Prefetching)" 기능 때문이다.

컴퓨터는 데이터를 정방향(1, 2, 3...)으로 읽을 것을 예측하고, 다음 데이터를 미리 준비해 둔다. 이 방식으로 하드웨어가 최적화되어 있어 효율이 가장 높다.

하지만 이 성능 차이는 미미해서 실무에서는 거의 무시해도 된다. "ORDER BY"에서 "filesort"를 피하는 것이 수백 배 더 중요하다.

 

내림차순 인덱스 (Descending Index)

역방향 스캔은 효율적이지만, 여기서 한 걸음 더 나아가 정렬 방향과 일치하는 인덱스를 직접 만들어 줄 수도 있다. MySQL 8.0 버전부터는 "내림차순 인덱스(Descending Index)" 생성을 정식으로 지원한다. 내림차순 인덱스는 데이터 자체를 처음부터 내림차순으로 정렬하여 저장하는 인덱스다.

 

기존 "idx_items_price" 인덱스를 삭제하고, "price"에 대한 내림차순 인덱스를 새로 만들어보자.

drop index idx_items_price on items; -- 기존 오름차순 인덱스 삭제
create index idx_items_price on items(price desc); -- price 컬럼에 내림차순 인덱스 생성

 

실행 계획 실행

explain select * from items where price between 50000 and 100000
order by price desc;

쿼리 결과

• "key" 컬럼에서 우리가 새로 만든 "idx_items_price_desc" 인덱스가 사용된 것을 볼 수 있다.
• 가장 주목할 점은 "Extra" 컬럼에서 "Backward index scan"이 사라지고 "Using index condition"만 남았다는 것이다.
• 이는 옵티마이저가 더 이상 인덱스를 "거꾸로" 읽는 수고를 할 필요가 없어졌음을 의미한다.
  • 쿼리가 요구하는 정렬 순서 (DESC)와 인덱스의 정렬 순서(DESC)가 완벽하게 일치하므로, 인덱스를 자연스러운 순서(정방향)로 스캔하기만 하면 된다.
  • 이것이 "ORDER BY" 절을 최적화하는 가장 이상적인 방법이다.

단일 컬럼 인덱스에서는 역방향 스캔과 내림차순 인덱스 간의 성능 차이가 크지 않을 수 있다. 하지만 "ORDER BY category ASC, registered_date DESC"처럼 여러 컬럼에 대해 서로 다른 정렬 순서(오름차순과 내림차순의 혼합)가 필요한 복잡한 쿼리에서는 내림차순 인덱스의 진가가 발휘된다.

이런 경우, 정렬 순서에 맞춰 정확하게 생성된 다중 컬럼 인덱스(복합 인덱스)는 쿼리 성능을 극적으로 향상시킬 수 있다. 다중 컬럼 인덱스(복합 인덱스)는 뒤에서 알아본다.