Selet All 쿼리에서 반복적으로 Clustered Index 탐색 하는지 궁금 합니다.

Question

select * from article where board_id = 1 order by article_id desc limit 30 offset 1499970;   해당 SQL 문을 실행하면 선생님이 설명 해주신 것 처럼   먼저 Secondary Index 에서 offset 0 부터 탐색이 일어나는데요. Secondary Index 에서 offset 0 조회 한 다음 Clustered Index 에서 데이터를 찾는다고 해주셨습니다.   이러한 과정을 offset 1499999 까지 반복 과정이 발생 하는데 왜 Secondary Index 에서 탐색 후 Clustered Index 에 데이터를 찾는 과정이 필요한지 궁금 합니다.   그러니깐 Secondary Index 에서만 일단 1499999 번 탐색한 다음에 Clustered Index 을 통해 그 외 데이터 (select *) 을 가져오면 되는것이 아닌가요?   제가 아직 인덱스에 대한 지식이 부족해 이런 질문을 한건데요ㅠ   다시 정리해서 질문 드리자면 왜 Secondary Index 을 통해 offset 순번 1499999 까지 도착 하지 않았는데 불필요하게 Clustered Index 까지 탐색하는가 입니다!

쿠케 · Answer

리나님, 안녕하세요!

MySQL의 스토리지 엔진(InnoDB)의 설계 방향에 대해서는 저도 깊게 들여다본 적이 없다보니, 공식적으로 답변드리기엔 어려운 부분이네요..!

세컨더리 인덱스만으로 충분히 조회 후, 마지막 필요한 상황에만 클러스터드 인덱스를 조회하면 되지 않을지 의문을 가지신 것 같은데요,

그렇게 했을 때의 제한 사항을 짐작가는 부분으로 말씀드려보겠습니다.

현재 쿼리는 WHERE 조건과 정렬에 인덱스 지정한 컬럼만 포함되어 있어서 의문이 생길 수 있지만,

예시로 WHERE절에 boardId 외에도 다른 조건(인덱스와 무관한)이 들어가있는 경우를 상상해 볼 수 있을 것 같습니다.

이 경우에는 반드시 데이터(클러스터드 인덱스)에 접근하여 원본 데이터를 확인해야 필터링을 수행할 수 있습니다.

클러스터드 인덱스를 통해 데이터의 실제 검증이 필요한 것입니다.

boardId만을 검사한다고 하더라도, 동시 요청 시에 데이터(클러스터드 인덱스)가 업데이트 되었지만, 세컨더리 인덱스에는 데이터가 아직 업데이트 되어있지 않은 상황이 만들어질 수도 있을 것 같고요. (이 경우에도 실제 데이터에 접근하여 검증은 필요하겠고요.)

쿼리 옵티마이저가 이러한 특수 상황을 모두 구분하여 최적화해주진 못하는 상황인가 봅니다.

설계의 일관성을 맞추기 위한 부분도 있었을 것이고, 예상치 못한 에러까지 모두 잡아내기엔 풀어내기 어려운 문제였을지도 모르겠습니다.

아마 이러한 최적화를 고려한다면 내부적으로 더욱 풀어내기 어려운 문제와 충돌날 수도 있었을지 싶네요.

저도 데이터베이스에 대해 엄청난 전문가는 아니다보니, 내부 설계 철학까지는 명쾌하게 만족스러운 답변을 드리기가 쉽지 않네요..!

추후 스토리지 엔진이 더욱 발전하면 이러한 문제도 알아서 해결해주지 않을까 싶긴 합니다.

당장은 성능 최적화보단 안전성에 초점을 맞춘 설계라고 생각되네요.

혹시 더 궁금한 점 있으시면 아는 선에서는 답변 드려보겠습니다!

인프런 AI 인턴 · Answer

안녕하세요, 인프런 AI 인턴입니다. 리나님.

질문하신 내용은 데이터베이스의 인덱스 작동 방식과 관련이 있습니다. SQL 쿼리에서 SELECT * 문을 사용할 때 인덱스의 역할을 이해하는 것이 중요합니다. 기본적으로 MySQL에서는 인덱스 커버링이라는 개념이 있는데, 이는 인덱스만으로 쿼리를 처리할 수 있는 경우, 데이터 파일에 접근하지 않는 것입니다. 하지만 SELECT *는 모든 열을 요청하므로, 인덱스의 정보만으로는 불충분한 경우가 많습니다.

왜 Secondary Index 및 Clustered Index 두 번 모두 사용하는가?

Secondary Index 탐색: where 조건과 order by 절을 기반으로 먼저 해당 인덱스를 통해 데이터를 찾아 위치를 식별합니다. 여기에서는 주로 해당 조건에 맞는 레코드의 위치 정보(기본 키 정보)를 수집합니다.
Clustered Index 탐색: 위에서 수집된 위치 정보를 가지고 실제 데이터 로우를 가져오기 위해 Clustered Index(실제 데이터가 저장된)를 탐색합니다. 이는 ‘SELECT *’ 때문에 필요한 과정입니다. 이 과정에서 데이터베이스는 인덱스 탐색을 효율적으로 수행하기 위해 인덱스 정보를 활용하는데, 제시된 대량의 offset이 높은 쿼리는 많은 반복 탐색을 요구하여 성능에 영향을 미칠 수 있습니다.

해결 방법

인덱스 커버링 활용: 필요한 열만 선택하여 인덱스 커버링이 가능하게 한다면, Clustered Index로의 추가적인 접근을 피할 수 있습니다.
적절한 인덱스 증가: offset을 낮추거나, 페이징을 구현하는 방법을 효율화하여 성능 부담을 줄일 수 있습니다.

더 많은 내용을 참고하기 위해, 비슷한 문제를 다룬 인프런 커뮤니티 게시물에서 유용한 정보를 찾았습니다:

효율적인 데이터베이스 인덱싱 기법 이해 (게시글 제목)

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

인프런 커뮤니티 질문&답변