TIL) 데이터 조회에 대한 궁금증

오늘 방통대에서 데이터베이스를 공부하는 와중에 제대로 데이터 베이스에 대해 알고 있는지 확인해보려고 합니다.

학습 목표

대표적인 외부 조인을 하는 경우 데이터베이스는 어떻게 동작하여 사용자에게 데이터를 반환하는지 복습하기

정리

먼저 두 테이블 Member, School 테이블이 있습니다.

• Member.id = School.member_id로 외부 조인을 하려고합니다.
• Member.id 는 primary key (cluster index)
• School.member_id는 secondary index

SELECT 
  *
FROM member m
LEFT OUTER JOIN school s
on m.id = s.member_id

위 쿼리를 실행했을 때 데이터 베이스는 어떻게 동작할까

생각

클러스터링 인덱스는 b-tree+ 입니다. b-tree와 다른 점은 리프 노드끼리 노드가 연결되어 있어 한쪽 방향이나 양방향으로 이동이 가능합니다.

member 테이블의 조인 필드는 클러스터링 인덱스이고 school 테이블의 조인 필드는 세컨더리 인덱스로 리프노드에는 실제 하드디스크 주소가 아니라 클러스터링 인덱스 주소가 있습니다.

사실 내부 조인인 경우와 외부 조인의 경우 드리븐 테이블 기준을 정하는 방식이 다릅니다.

내부 조인의 경우에는 두 테이블 중 where 조건을 참고하여 제일 작은 테이블을 기준으로 드리븐 테이블로 정합니다 이유는 테이블이 작으면 메모리에 올려놓고 조인 필드를 순회하여 드라이빙 테이블을 조회하는 것이 빠르기 때문입니다.

외부 조인의 경우에는 왼쪽 테이블이 드리븐 테이블로 고정하게 됩니다.

지금은 드리븐 테이블은 member 테이블이 되며 member_id는 물리적 주소와 논리적 주소가 동일하므로 순차 io로 school테이블을 조회하게 됩니다. 이때 member 테이블의 데이터가 작으면 메모리에 올려놓고 조회를 합니다. 그런데 member 테이블의 데이터가 많아서 메모리에 올려놓을 수 없거나 메모리가 부족하다면 하드스크에서 페이지 단위로 가져와서 메모리에 올려놓고 학교테이블을 조회합니다. 그리고 조회 결과는 메모리에 누적합니다. 순회가 끝난 다시 메모리에 새로운 멤버 테이블 정보를 가져옵니다.

학교 테이블의 경우에는 랜덤 io가 동작합니다. 멤버 아이디가 세컨더리 인덱스로 페이지 단위로 메모리에 올라오기 때문에 처음 조회할 때 페이지 단위로 메모리에 올려놓습니다. 그리고 다음 조회하는데 거기에 페이지가 메모리에 올라와있으면 그것을 그대로 사용해서 학교 레코드에 접근합니다. 만약 다음 조회시 인덱스 페이지에 인덱스 정보가 없다면 다시 인덱스 페이지를 조회하여 인덱스 페이지를 메모리에 올려놓습니다. 이게 반복됩니다.

조회 결과는 메모리에 저장할때 연결리스트로 저장할거야 배열은 데이터의 크기가 동일해야하는데 그게 안되기 때문에 배열은 아닌거같아 그런데 만약 데이터 크기가 크다면 메모리가 아니라 디스크에 임시테이블을 만들어 저장하고 디스크 i/o로 데이터를 반환하게 되지

Grok 피드백

B-tree+는 리프노드가 연결 리스트로 연결되어있다. 순차적 이동(한쪽 방향 또는 양방향)이 가능하다는 점

따라서 범위 스캔(range scan)이나 전체 스캔(full scan)에 효율적이다.

차이점

B-tree 와 B-tree+의 차이는 연결리스트 뿐만 아니라 리프 노드 데이터 영역입니다.

1. B-tree+ 노드는 리프노드만 데이터를 가지고 있다. 내부 노드에는 키와 포인터만 있다.
2. B-tree 노드는 내부 노드도 데이터를 가지고 있다.

어떤 차이가 있을까?

B-tree+는 리프노드에 데이터(InnoDB 기준 실제 레코드 주소)가 있으므로 범위나 전체 스캔이 매우 효율적이다.

B-tree는 범위 스캔이나 전체 스캔시 내부 노드와 리프 노드를 모두 방문할 가능성이 있다. 노드간 비순차적 접근이 발생하여 디스크 I/O가 증가하거나 탐색 경로가 복잡해진다.

비용 낮은 테이블

내부 조인의 드리븐 테이블 선정: 조회 비용이 낮은 테이블이 드리븐 테이블로 선정됩니다.

이는 테이블 크기 뿐만 아니라 인덱스 유무, WHERE 조건의 선택도(유니크함 정도),필터링 효과 등을 종합해서 결정됩니다.