[MySQL]LEFT JOIN에서 기간 검색과 조인 조건 인덱스 설정

LEFT JOIN을 사용해 기간 검색이 필요한 JOIN 쿼리에서 인덱스를 설정하기 위해 기간 컬럼과 ON 절에 사용하는 컬럼을 복합 인덱스로 만들지, 아니면 기간 검색을 위한 컬럼만 인덱스로 설정할지 고민한 내용을 정리하고자 합니다.

Nested-Loop Join의 동작 방식

1. 드라이빙 테이블에서 각 행을 선택합니다.
2. 드리븐 테이블에서 선택된 행과 일치하는 행을 찾습니다.
3. 일치하는 행이 있으면 결과 집합에 추가합니다.
4. 드라이빙 테이블의 모든 행에 대해 반복합니다.

https://velog.io/@yoonee1126

INNER JOIN

INNER JOIN에서는 두 테이블 간의 일치하는 레코드만 반환합니다.

1. 드라이빙 테이블: 첫 번째 테이블의 각 행을 선택합니다.
2. 드리븐 테이블: 두 번째 테이블에서 인덱스를 사용하여 일치하는 행을 찾습니다.

INNER JOIN에서 드라이빙 테이블과 드리븐 테이블을 선택하는 과정은 쿼리 옵티마이저가 최적의 실행 계획을 결정하는 중요한 단계입니다. 이 과정은 여러 요인에 따라 달라질 수 있습니다.

어떤 테이블이 드라이빙 테이블이 될지 미리 알 수 없는 경우, 양쪽 테이블 모두에 인덱스를 설정하는 것이 좋습니다. 이렇게 하면 쿼리 옵티마이저가 최적의 실행 계획을 선택할 수 있습니다.

1. 테이블 크기
쿼리 옵티마이저는 일반적으로 작은 테이블을 드라이빙 테이블로 선택합니다. 작은 테이블을 먼저 스캔하고, 그 결과를 기반으로 큰 테이블에서 일치하는 행을 찾는 것이 효율적이기 때문입니다.
2. 인덱스 사용 가능성
인덱스가 있는 테이블은 드리븐 테이블로 선택될 가능성이 높습니다. 인덱스를 사용하면 검색 속도가 빨라지기 때문에, 옵티마이저는 인덱스를 활용할 수 있는 테이블을 드리븐 테이블로 선택합니다.
3. 필터 조건의 선택성
필터 조건이 매우 선택적인 경우, 해당 조건이 적용된 테이블을 드라이빙 테이블로 선택합니다. 선택성이 높은 조건은 결과 집합의 크기를 크게 줄일 수 있기 때문에, 이를 먼저 적용하는 것이 효율적입니다.
4. 조인 조건
조인 조건에 따라 드라이빙 테이블과 드리븐 테이블이 결정될 수 있습니다. 예를 들어, 조인 조건이 특정 테이블의 기본 키(PK)와 일치하는 경우, 해당 테이블이 드리븐 테이블로 선택될 수 있습니다.

##inner join
SELECT * FROM A
INNER JOIN B ON A.id = B.id;

## create index
CREATE INDEX idx_a_id ON A(id);
CREATE INDEX idx_b_id ON B(id);

쿼리 옵티마이저는 테이블 크기, 인덱스 사용 가능성, 필터 조건의 선택성, 조인 조건 등을 종합적으로 고려하여 드라이빙 테이블과 드리븐 테이블을 선택합니다. 이를 통해 최적의 실행 계획을 수립하고, 쿼리 성능을 최적화합니다.

LEFT JOIN

LEFT JOIN에서는 왼쪽 테이블의 모든 레코드와 오른쪽 테이블의 일치하는 레코드를 반환합니다. 일치하지 않는 오른쪽 테이블의 값은 NULL로 표시됩니다. 인덱스 사용 방식은 다음과 같습니다:

1. 드라이빙 테이블: 왼쪽 테이블의 각 행을 선택합니다.
2. 드리븐 테이블: 오른쪽 테이블에서 인덱스를 사용하여 일치하는 행을 찾습니다. 일치하는 행이 없으면 NULL을 반환합니다.

비유를 해서 설명하자면, 왼쪽 테이블은 항상 먼저 출발하는 기차 라고 생각하면 이해가 쉬울 것 같습니다.

• LEFT OUTER JOIN은 왼쪽 테이블을 기준으로 시작합니다.
• 기차역에서 왼쪽 테이블의 모든 승객(데이터)을 확인하고,
• 오른쪽 테이블(드리븐 테이블)에서 해당 승객이 있는지 찾아봅니다.
  • 성능을 위해 오른쪽 테이블의 인덱스가 필요합니다.
• 승객이 없으면, 그 자리는 NULL로 비워둡니다.

LEFT OUTER JOIN은 왼쪽 테이블(기준)이 먼저 출발하고, 오른쪽 테이블이 따라오는 구조입니다. 기차 출발 순서는 왼쪽을 기준으로 강제로 고정 되어 있습니다.

SELECT * FROM A
LEFT JOIN B ON A.id = B.id
WHERE A.date BETWEEN '2024-01-01' AND '2024-12-31';

 

 

 

참고 :  https://velog.io/@yoonee1126/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EB%85%BC%EB%A6%AC%EC%A0%81%EB%AC%BC%EB%A6%AC%EC%A0%81-%EC%A1%B0%EC%9D%B8#outer-join

https://dev.mysql.com/doc/refman/8.4/en/nested-loop-joins.html