배치 I/O와 인덱스를 믿고 쿼리에 ORDER BY를 생략하면 안되는 이유

조시형님의 친절한 SQL 튜닝을 읽고 개인적으로 공부한 내용을 정리한 글입니다.

인덱스는 큰 테이블에서 소량 데이터를 추출할때 유용하다고 배웠다.

인덱스로 레코드에 접근하는 경우, 인덱스와 연결된 해당 레코드가 존재하는 블록 정보를 통해서 레코드에 접근해야하기 때문에 랜덤 I/O 로 접근해야하고

이 랜덤 I/O가 많아지면, 전체 테이블을 풀 스캔해서 시퀀셜 엑세스로 접근하는게 비용이 더 적어질 수 있기 때문이다.

💡 이러한 성능 비효율을 어느정도 감소시키기 위해, 이전 인덱스를 통해 접근했던 테이블 블록 정보를 저장해두었다가

일치하면 바로 접근하는 캐싱 방법을 사용하긴하지만

인덱스로 정렬된 레코드마다 다른 블록을 가리키면 효과를 보기 힘든 방법이다.

그래서 고안해낸 방법이 💡 배치 I/O 이다.

인덱스로 읽는 레코드마다 건건이 I/O를 발생시키는 비효율을 줄이기 위한 방법이다.

🚨 인덱스를 이용해 찾은 접근해야하는 테이블 정보를 버퍼 캐시에서 찾지 못하면 디스크에 접근하는게 일반적인 방법인데,

💡 부득이하게 디스크로 접근해야할때, 테이블 블록에 대한 디스크 I/O를 미뤘다가 일정량 쌓이면 한꺼번에 처리하는 기능이다.

조회 시, 인덱스 칼럼이 조건절에 사용되면 인덱스 테이블에 이미 레코드가 정렬되어있어

옵티마이저는 Order By 를 생략한다고 이전에 배웠었다.

따라서, 인덱스를 사용하는 쿼리의 경우 의도적으로 order by 문을 생략해서 사용하는 경우가 있을 수 있는데,

🚨 만약 배치 I/O 기능을 사용할 때, Order By 구문을 생략하면 원하는 결과를 얻지 못할 수 있다.

옵티마이저는 배치 I/O를 사용하는 경우 인덱스 순서대로 I/O를 진행하지 않기 때문에,

조건절에 인덱스만 존재하더라도 기존에는 Sort 과정을 생략했지만

실행계획을 살펴보면 Sort 과정을 생략하지 않는다. (쿼리에 명시한 Order by를 성실히 수행한다.)

따라서, 배치 I/O 기능을 사용하는 경우,

🚨 '인덱스 조건이 알아서 정렬해주겠지' 라는 생각으로 쿼리에 Order by 를 생략하면

정렬되지 않은 결과가 나올 수 있으므로 주의가 필요하다.

티스토리툴바