개발 학습 실습실

이 사이트의 실습은 자바스크립트를 켜야 움직입니다. 아래는 같은 내용을 글로 적은 것입니다.

공부 › DB › 인덱스와 실행 계획

조인 알고리즘

조인 컬럼에 인덱스가 없으면 DB는 어떻게 짝을 찾나

조인은 한 테이블을 먼저 읽고, 그 행마다 다른 테이블에서 짝이 되는 행을 찾는 일입니다. 짝을 찾을 때 인덱스를 쓸 수 있으면 필요한 행만 읽지만, 없으면 전체를 읽어야 합니다. MySQL 8은 그런 경우에 해시 조인이라는 다른 방법을 씁니다.

실행으로 대조 · MySQL 8.4에서 일곱 가지 조합을 EXPLAIN, EXPLAIN FORMAT=TREE, EXPLAIN ANALYZE로 실행해 조인 방식과 읽은 횟수를 대조

이 실습에 나오는 개념

중첩 루프 조인
한 테이블의 행을 하나씩 읽으면서, 그 행마다 다른 테이블에서 짝이 되는 행을 찾는 조인 방식이다. 프로그램의 이중 반복문과 같은 모양이라 이런 이름이 붙었으며, 영어로는 Nested Loop Join이다. 읽는 양은 먼저 읽는 테이블의 행 수에 한 번 찾을 때 읽는 행 수를 곱한 만큼이다. 나중에 읽는 테이블의 조인 컬럼에 인덱스가 있으면 필요한 행만 읽지만, 없으면 찾을 때마다 테이블 전체를 읽어야 한다.
해시 조인
한 테이블의 행으로 조인 컬럼 값을 열쇠로 하는 해시 테이블을 메모리에 만든 뒤, 다른 테이블을 한 번 읽으면서 그 해시 테이블에서 짝을 찾는 조인 방식이다. 두 테이블을 한 번씩만 읽으므로, 인덱스 없이 중첩 루프로 조인할 때보다 읽는 양이 훨씬 적다. MySQL은 8.0.18부터 지원하며, 조인 컬럼에 쓸 인덱스가 없을 때 선택한다. 해시 테이블이 메모리를 차지하고, 일부 행만 필요할 때에도 테이블을 통째로 읽는다는 점은 인덱스를 쓰는 조인보다 불리하다.
구동 테이블
조인에서 먼저 읽는 테이블이다. 영어로는 driving table이라고 하며, 중첩 루프 조인에서는 이 테이블의 행 하나마다 나머지 테이블을 찾아간다. 어느 테이블을 구동 테이블로 삼을지는 FROM에 적은 순서가 아니라 옵티마이저가 비용을 계산해 정한다. 보통 조건으로 걸러낸 뒤의 행이 적고, 상대 테이블을 인덱스로 찾아갈 수 있게 되는 쪽이 선택된다. MySQL에서는 EXPLAIN 결과의 윗줄에 나오는 테이블이 구동 테이블이다.
옵티마이저
SQL을 실행할 수 있는 여러 방법 가운데 비용이 가장 적을 것으로 예상되는 방법을 고르는 데이터베이스의 구성 요소다. 테이블의 행 수와 값의 분포 같은 통계 정보를 바탕으로 판단한다. 통계가 실제와 다르면 비효율적인 방법을 고를 수 있다.
실행 계획(EXPLAIN)
데이터베이스가 쿼리를 어떤 방법으로 실행할지 정한 계획이다. 어떤 인덱스를 쓰는지, 테이블을 어떤 순서로 조인하는지, 몇 행을 읽을 것으로 예상하는지를 보여 준다. 쿼리 앞에 EXPLAIN을 붙여 확인하며, 쿼리가 느릴 때 원인을 찾는 출발점이다.

이렇게 바꿔 보세요

처음 설정으로 실행하면

  1. 쿼리: member와 orders를 회원 번호로 짝지어 회원 이름과 주문 금액을 조회합니다. member는 1,000행, orders는 10,000행이고 회원 한 명에 주문이 10건씩 있습니다. 결과는 10,000행입니다.
  2. 먼저 읽을 테이블 정하기: STRAIGHT_JOIN으로 FROM에 쓴 순서대로 읽게 했으므로 member를 먼저 읽습니다. 먼저 읽는 테이블을 구동 테이블이라고 부릅니다.
  3. 짝을 찾는 방법 정하기: 나중에 읽는 orders의 조인 컬럼 member_id에 인덱스가 없습니다. 중첩 루프로 하면 member의 행마다 orders 10,000행을 모두 읽어야 합니다. MySQL 8은 이런 경우에 해시 조인을 고릅니다. EXPLAIN FORMAT=TREE의 첫 줄이 Inner hash join으로 나옵니다.
  4. 해시 조인 1단계, 해시 테이블 만들기: member 1,000행을 한 번 읽어, id를 열쇠로 하는 해시 테이블을 메모리에 만듭니다. 해시 테이블은 열쇠 값으로 계산한 자리에 바로 넣고 바로 찾는 구조입니다.
  5. 해시 조인 2단계, 짝 찾기: orders 10,000행을 처음부터 끝까지 한 번 읽으면서, 행마다 member_id를 해시 테이블에서 찾습니다. 찾을 때마다 테이블을 훑지 않고 계산한 자리만 확인합니다. orders를 읽은 횟수는 1번입니다.
  6. 결과 10,000행이 나옵니다. 두 테이블에서 읽은 행은 member 1,000행과 orders 10,000행, 합쳐서 11,000행입니다.

실행 결과

코드

-- member: 1,000행 (기본 키 id)
-- orders: 10,000행 (회원 한 명에 주문 10건)
-- orders.member_id에 인덱스 없음

EXPLAIN FORMAT=TREE
SELECT m.name, o.amount
  FROM member m
  STRAIGHT_JOIN orders o ON o.member_id = m.id;   -- FROM에 쓴 순서대로 읽게 한다

이 모형의 전제

화면에서 고를 수 있는 일곱 가지 조합 모두, 같은 테이블(member 1,000행, orders 10,000행)을 이 PC의 MySQL 8.4에 만들고 ANALYZE TABLE을 한 뒤 대조했습니다. 먼저 읽는 테이블과 나중 테이블의 type, key, Extra는 EXPLAIN으로, 조인 방식은 EXPLAIN FORMAT=TREE의 첫 줄로, 나중 테이블을 읽은 횟수와 한 번에 읽은 행 수는 실제로 실행하며 세어 주는 EXPLAIN ANALYZE의 loops와 rows로 확인했습니다. 해시 조인 설정이 숨겨지는 다섯 가지 경우에는 NO_BNL 힌트를 붙여도 계획이 바뀌지 않는 것도 확인했습니다. 옵티마이저에게 맡겼을 때의 순서는 비용 계산의 결과라서 데이터의 양과 분포에 따라 달라질 수 있습니다. "읽은 행"은 테이블이나 인덱스에서 꺼낸 행의 수이며, 인덱스를 타고 내려가는 비용, 해시 테이블을 만들고 찾는 비용, 걸린 시간은 넣지 않았습니다. 해시 조인은 MySQL 8.0.18에 들어왔고 8.0.20부터 인덱스 없는 조인에 쓰이던 블록 중첩 루프를 대신합니다. 해시 테이블이 메모리(join_buffer_size)에 다 들어가지 않으면 디스크를 함께 씁니다. 이 두 가지는 실행으로 확인한 것이 아니라 MySQL 문서의 해시 조인 최적화 설명을 따른 것입니다. 그림은 회원 3명, 주문 6건으로 줄인 것입니다.

앞 실습: 실행 계획 읽기 · 다음 실습: 트랜잭션 기본

이 실습에 나오는 개념

이 실습에서 보는 것

설정

코드


      
    


    

      
그림

      

      

    


    

      

      

      

    


  


  

    

      
실행 비교

      

      

    

    

      
이렇게 바꿔 보세요