[데이터베이스] 트랜잭션과 무결성/인덱스/조인

2023년 5월 1일 206p~228p

 

4.3 트랜잭션과 무결성

4.3.1 트랜잭션

트렌잭션이란?

데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위

상태를 변화시킨다는 것 → SQL 질의어를 통해 DB에 접근하는 것

- SELECT
- INSERT
- DELETE
- UPDATE

작업 단위 → 많은 SQL 명령문들을 사람이 정하는 기준에 따라 정하는 것

예시) 사용자 A가 사용자 B에게 만원을 송금한다.

* 이때 DB 작업
- 1. 사용자 A의 계좌에서 만원을 차감한다 : UPDATE 문을 사용해 사용자 A의 잔고를 변경
- 2. 사용자 B의 계좌에 만원을 추가한다 : UPDATE 문을 사용해 사용자 B의 잔고를 변경

현재 작업 단위 : 출금 UPDATE문 + 입금 UPDATE문
→ 이를 통틀어 하나의 트랜잭션이라고 한다.
- 위 두 쿼리문 모두 성공적으로 완료되어야만 "하나의 작업(트랜잭션)"이 완료되는 것이다 = Commit 
- 작업 단위에 속하는 쿼리 중 하나라도 실패하면 모든 쿼리문을 취소하고 이전 상태로 돌려놓아야한다 = Rollback

트랜잭션의 특징은 원자성, 일관성, 독립성, 지속성이 있으며 이를 한꺼번에 ACID 특징이라고 한다.

1. 원자성

트랜잭션과 관련된 일이 모두 수행되거나 되지 않았거나를 보장한다. 트랜잭션을 커밋했는데, 문제가 발생하여 롤백하는 경우 그 이후에는 모두 수행되지 않음을 보장한다.

ex) 1000만원을 가진 홍철이가 0원을 가진 규영이에게 500만원 이체

1. 홍철의 잔고 조회
2. 홍철 돈 -500만원
3. 규영 돈 +500만원

이 작업을 취소하면 홍철이는 1000만원, 규영이는 0원 가져야한다. 홍철이는 500만원, 규영이는 0원이 되지 않는다.

트랜잭션 단위로 여러 로직들을 묶을 때 외부 API를 호출하는 것이 있으면 안되며, 만약 있다고 하면 롤백이 일어날 경우 어떻게 대처를 해야할 지에 대한 해결방법이 있어야 하는 트랜잭션 전파를 신경써서 관리할 필요가 있다.
커밋과 롤백 덕분에 데이터의 무결성이 보장되며, 데이터 변경 전에 변경사항들을 쉽게 확인할 수 있고 해당작업을 그룹화 할 수 있다.

* 커밋: 여러 쿼리가 성공적으로 처리가 되었다고 확정하는 명령어이다. 커밋은 트랜잭션 단위로 수행되며 변경된 내용이 모두 영구적으로 저장되는 것을 이야기 한다.

* 롤백: 트랜잭션으로 처리한 하나의 묶음 과정을 일어나기 전으로 돌리는 것을 말한다.

* 트랜잭션 전파: 트랜잭션을 수행할때 커넥션 단위로 수행하기 때문에 커넥션 객체를 넘겨서 수행해야한다. 하지만 이를 매번 넘겨주기가 어렵고 번거롭기 때문에, 이를 넘겨서 수행하기 보다 여러 트랜잭션 관련 메서드의 호출을 하나의 트랜잭션에 묶이도록 하는 것을 트랜잭션 전파라고 한다.

스프링에서 @Transactional을 이용한 트랜잭션 전파 :  여러 쿼리 관련 코드들을 하나의 트랜잭션으로 처리

2. 일관성

데이터를 허용된 방식으로만 변경해야 하는 것을 의미한다. 즉 데이터베이스에 기록된 모든 데이터는 여러가지의 조건, 규칙에 따라 유효함을 가져야한다.

3. 격리성

트랜잭션 수행 시 서로 끼어들지 못하는 것을 말한다. 복수의 병렬 트랜잭션은 서로 격리되어 마치 순차적으로 실행되는 것처럼 작동되어야하고, 데이터베이스는 여러 사용자가 같은 데이터에 접근할 수 있어야한다.

격리 수준에 따라 발생하는 현상은 다음과 같다.

• Phantom Read (팬텀 리드) : 한 트랜잭션 내에서 동일한 쿼리를 보냈을 때 해당 조회 결과가 다른 경우
  ex) 사용자 A가 회원 테이블에서 age가 12 이상인 회원들을 조회하는 쿼리를 보냈을 때 결과로 세 개의 테이블이 조회되었다. 그 다음 사용자 B가 age가 15인 회원 레코드를 삽입하였다. 그러면 그 다음 네 개의 테이블이 조회된다.
• Non-repeatable Read (반복 가능하지 않은 조회) : 한 트랜잭션 내의 같은 행에 두 번 이상 조회가 발생하였는데, 그 행의 값이 다른 경우
  * 팬텀리드와 다른 점 ? 팬텀 리드는 다른 행이 선택될 수도 있는 것이고, 반복 가능하지 않은 조회에서는 행 값이 달라질 수 있음
  ex) 사용자 A가 100이라는 값을 가지는 데이터였는데, B가 1로 변경해서 커밋하면 사용자는 1을 읽게 된다.
• Dirty Read (더티 리드) : 한 트랜잭션이 실행 중일 때 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 행의 데이터를 읽는 것
  ex) 사용자 A가 100이라는 값을 가지는 데이터를 1로 변경한 내용이 커밋되지 않은 상태더라도 사용자 B가 조회한 결과가 1이 나오게 된다.

격리 수준의 종류는 다음과 같다.

• Serializable : 트랜잭션을 순차적으로 진행시키는 것이다. 여러 트랜잭션이 동시에 같은 행에 접근할 수 없다. 따라서 교착 상태가 일어날 확률도 많고, 가장 성능이 떨어지는 격리 수준이다.
• Repeatable Read : 하나의 트랜잭션이 수정한 행을 다른 트랜잭션이 수정할 수 없도록 막아주지만, 새로운 행을 추가하는 것은 막지 않는다.
  => 팬텀리드 발생
• Read Committed : 가장 많이 사용되는 격리 수준이다. MySQL, PostgreSQL, SQL Server, Oracle에서 기본값으로 설정되어 있다. 다른 트랜잭션이 커밋하지 않은 정보는 읽을 수 없다. 즉, 커밋 완료된 데이터에 대해서만 조회를 허용한다. 하지만 어떤 트랜잭션이 접근한 행을 다른 트랜잭션이 수정할 수 있다. 이 때문에 트랜잭션이 같은 행을 다시 읽을 때 다른 내용이 발견될 수도 있다.
  => 팬텀리드, 반복 가능하지 않은 조회 발생
• Read Uncommitted : 가장 낮은 격리 수준이다. 하나의 트랜잭션이 커밋되기 이전에 다른 트랜잭션에 노출되는 문제가 있지만 가장 빠르다. 데이터 무결성을 위해 되도록 사용하지 않는 것이 이상적이나, 대량의 데이터를 어림잡아 집계하는 데에는 사용하면 좋다.
  => 팬텀리드, 반복 가능하지 않은 조회, 더티 리드 발생

4. 지속성

성공적으로 수행된 트랜잭션은 영원히 반영되어야 하는 것을 의미한다. 이는 데이터베이스에 시스템 장애가 발생해도 원래 상태로 복구하는 회복 기능이 있어야 함을 뜻하며, 데이터베이스는 이를 위해 다양한 기능을 제공한다.

* 체크섬: 중복검사의 한 형태로, 오류 정정을 통해 송신된 자료의 무결성을 보호하는 단순한 방법
* 저널링: 파일 시스템 또는 데이터베이스 시스템에 변경사항을 반영하기 전에 로깅하는 것을 의미

4.3.2 무결성

데이터의 정확성, 일관성, 유효성을 유지하는 것을 이야기하며, 무결성이 유지되어야 데이터베이스에 저장된 데이터 값과 그 값에 해당하는 현실 세계의 실제값이 일치하는지에 대한 신뢰가 생긴다.

이름	설명
개체 무결성	기본키로 선택된 필드는 빈 값을 허용하지 않음
참조 무결성	서로 참조 관계에 있는 두 테이블의 데이터는 항상 일관된 값을 유지해야함
고유 무결성	특정 속성에 대해 고유한 값을 가지도록 조건이 주어진 경우 그 속성 값은 모두 고유한 값을 가짐
NULL 무결성	특정 속성 값에 NULL이 나올 수 없다는 조건이 주어진 경우 그 속성 값은 NULL이 될 수 없다는 제약 조건

* 트랜잭션의 상태

Active
트랜잭션의 활동 상태. 트랜잭션이 실행중이며 동작중인 상태를 말한다.
Failed
트랜잭션 실패 상태. 트랜잭션이 더 이상 정상적으로 진행 할 수 없는 상태를 말한다.
Partially Committed
트랜잭션의 Commit 명령이 도착한 상태. 트랜잭션의 commit이전 sql문이 수행되고 commit만 남은 상태를 말한다.
Committed
트랜잭션 완료 상태. 트랜잭션이 정상적으로 완료된 상태를 말한다.
Aborted
트랜잭션이 취소 상태. 트랜잭션이 취소되고 트랜잭션 실행 이전 데이터로 돌아간 상태를 말한다.

* Partially Committed 와 Committed 의 차이점
Commit 요청이 들어오면 상태는 Partial Commited 상태가 된다. 이후 Commit을 문제없이 수행할 수 있으면 Committed 상태로 전이되고, 만약 오류가 발생하면 Failed 상태가 된다. 즉, Partial Commited는 Commit 요청이 들어왔을때를 말하며, Commited는 Commit을 정상적으로 완료한 상태를 말한다.

* 트랜잭션을 사용할 때 주의할 점

트랜잭션은 꼭 필요한 최소의 코드에만 적용하는 것이 좋다. 즉 트랜잭션의 범위를 최소화하라는 의미다. 일반적으로 데이터베이스 커넥션은 개수가 제한적이다. 그런데 각 단위 프로그램이 커넥션을 소유하는 시간이 길어진다면 사용 가능한 여유 커넥션의 개수는 줄어들게 된다. 그러다 어느 순간에는 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수도 있는 것이다.

*교착상태

복수의 트랜잭션을 사용하다보면 교착상태가 일어날수 있다. 교착상태란 두 개 이상의 트랜잭션이 특정 자원(테이블 또는 행)의 잠금(Lock)을 획득한 채 다른 트랜잭션이 소유하고 있는 잠금을 요구하면 아무리 기다려도 상황이 바뀌지 않는 상태를 말한다.

---

4.4. 데이터베이스의 종류

1. 관계형 데이터베이스

• 행과 열을 가지는 표 형식 데이터를 저장하는 형태의 DB를 가리킨다.
• SQL이라는 언어를 써서 조작한다.
• 대표적으로 MySQL, PostgreSQL 등이 있다.

2. NoSQL 데이터베이스

• Not only SQL 이라는 슬로건에서 생겨난 DB이다.
• SQL을 사용하지 않는 데이터베이스를 말한다.
• 대표적으로 몽고DB, redis 등이 있다.

참고) SQL vs NOSQL 깊이 알아보기

https://gyoogle.dev/blog/computer-science/data-base/SQL%20&%20NOSQL.html

---

4.5 인덱스

인덱스는 데이터를 데이터베이스에서 빠르게 찾을 수 있는 하나의 장치로, 인덱스를 설정하면 테이블 안에 내가 찾고자 하는 데이터를 빠르게 찾을 수 있다. 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어 둔다.

DBMS 의 인덱스는 항상 정렬된 상태를 유지하기 때문에 원하는 값을 탐색하는데는 빠르지만 새로운 값을 추가하거나 삭제, 수정하는 경우에는 쿼리문 실행 속도가 느려진다. 결론적으로 DBMS 에서 인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져서 오히려 역효과만 불러올 수 있다.

- B+-Tree 인덱스 알고리즘
일반적으로 사용되는 인덱스 알고리즘은 B+-Tree 알고리즘이다. B+-Tree 인덱스는 칼럼의 값을 변형하지 않고(사실 값의 앞부분만 잘라서 관리한다), 원래의 값을 이용해 인덱싱하는 알고리즘이다.

- Hash 인덱스 알고리즘
칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로, 특정 문자로 시작하는 값으로 검색을 하는 전방 일치와 같이 값의 일부만으로 검색하고자 할 때는 해시 인덱스를 사용할 수 없다. 주로 메모리 기반의 데이터베이스에서 많이 사용한다.

- 왜 index 를 생성하는데 b-tree 를 사용하지? 데이터에 접근하는 시간복잡도가 O(1)인 hash table 이 더 효율적일 것 같은데? 
SELECT 질의의 조건에는 부등호(<>) 연산도 포함이 된다. hash table 을 사용하게 된다면 등호(=) 연산이 아닌 부등호 연산의 경우에 문제가 발생한다. 동등 연산(=)에 특화된 hashtable은 데이터베이스의 자료구조로 적합하지 않다.

4.5.2 B 트리

인덱스는 보통 B 트리라는 자료 구조로 이루어져 있다. 이는 루트 노트, 리프 노트 그리고 루트 노드와 리프 노드 사이에 있는 브랜치 노드로 나뉜다.

B 트리는 탐색 성능을 높이기 위해 균형 있게 높이를 유지하는 Balanced Tree 의 일종이다.

자식 node의 개수가 2개 이상이며, node 내의 key가 1개 이상일 수도 있다. node의 자식 수 중 최댓값이 K(만약 3)라고 하면, 해당 B-Tree를 K(3)차 B-트리이다.

ex) 키 14에 해당하는 데이터를 찾을 때

트리의 탐색은 루프 노드부터 이루어지며 브랜치 노드를 거쳐서 리프 노드까지 내려오게 된다. 이러한 인덱스가 효율적인 이유는 효율적인 단계를 거쳐서 모든 요소에 접근할 수 있는 균형잡힌 트리 구조와 트리길이의 대수확장성 때문이다. 대수확장성이란 트리 깊이가 리프 노드 수에 비해 매우 느리게 성장하는 것을 의미하며, 기본적으로 인덱스가 한 깊이씩 증가할 때마다 최대 인덱스의 항목의 수는 4배씩 증가한다.

4.5.3 인덱스 만드는 방법

1) MySQL

• 인덱스는 테이블의 특정 컬럼(Column) 을 기준으로 적용되어진다.
• MySQL 의 인덱스 종류에는 클러스터드 인덱스(Clustered Index) 와 보조 인덱스(Secondary Index or Non-Clustered Index) 가 있다.

클러스터형 인덱스

클러스터드 인덱스는 '영어 사전' 과 같다. 영어사전은 일반적인 책과 같이 '찾아보기' 페이지가 존재하지 않는다.
다만 목차가 존재하며 A~Z 까지 단어가 순서대로 정렬되어 있어 목차가 곧 해당 단어를 나타내게 된다.

목차 자체가 책의 내용과 같다 = 내용 자체가 인덱스

클러스터드 인덱스는 이와 같이 동작한다.
데이터베이스 테이블의 데이터(행 데이터)는 클러스터드 인덱스를 기준으로 자동 정렬되어진다. (MySQL 의 경우 오름 차순 정렬)
그리고 클러스터드 인덱스는 테이블당 1개만 생성할 수 있으며 중복된 값을 가질 수 없다.
테이블의 특정 컬럼에 PK 를 생성하는 순간 자동으로 해당 컬럼에 클러스터드 인덱스가 생성된다.
즉, "특정 컬럼을 PK 로 지정한다." 라는 것은 곧 "해당 컬럼에 클러스터드 인덱스가 생성된다." 는 것과 같다.

• PK 옵션으로 기본키로 만들면 생성할 수 있다.
• PK로 만들지 않고 unique not null 옵션을 붙이면 클러스터형 인덱스로 만들 수 있다.

세컨더리 인덱스

보조 인덱스는 일반적인 책의 '찾아보기' 페이지와 같다.
우리가 원하는 정보를 검색하기 위해 '찾아보기' 페이지를 살펴본 후에 해당 정보 옆에 표시된 페이지로 다시 이동해야 원하는 정보를 찾아낼 수 있다는 것이다.

보조 인덱스는 이와 같이 동작하게 된다.
그리고 '찾아보기' 페이지에는 많은 종류의 정보가 표시되어있는 것과 같이, 보조 인덱스는 여러 개 생성될 수 있다.

• create index…. 명령어를 기반으로 만들면 만들 수 있다
• 하나의 인덱스만 생성할 것이라면 성능상 클러스터형가 세컨더리 인덱스보다 낫다.
• 보조 인덱스로, 여러 개의 필드 값을 기반으로 쿼리를 많이 보낼 때 생성하는 인덱스이다.
• 예를 들어, age라는 하나의 필드만으로 쿼리를 보낸다면 클러스터형 인덱스만 필요하지만, age,name,email 등 다양한 필드를 기반으로 쿼리를 보낼 때는 세컨더리 인덱스를 사용해야 한다.

https://velog.io/@ymh92730/MySQL-Index-%EC%9D%98-%EC%A2%85%EB%A5%98

4.5.4 인덱스 최적화 기법

1) 인덱스와 비용

인덱스는 두 번 탐색하도록 강요하며, 인덱스 리스트, 컬렉션 순으로 탐색하기 때문에 관련 비용이 들게 된다. 컬렉션이 수정되었을 때는 인덱스도 수정되어야 하는데, 여기서 B-Tree의 높이를 균형있게 조절하는 비용도 들고 데이터를 효율적으로 조회할 수 있도록 분산시키는 비용또한 들게된다. 그렇기 때문에 쿼리에 있는 필드에 인덱스를 무작정 다 설정하는 것은 옳지 않은 방법이며, 컬렉션에서 가져와야 하는 양이 많을 수록 인덱스를 사용하는 것인 비효율적이다.

2) 테스팅

인덱스 최적화 기법은 서비스 특징에 따라 달라지며, 서비스에서 사용하는 객체의 깊이, 테이블의 양 등이 다르기 때문에 항상 테스팅을 해야한다. explain() 함수를 통해 인덱스를 만드고 쿼리를 보낸 이후에 테스팅을 진행하며 걸리는 시간을 최소화 해야한다.

3) 복합 인덱스의 순서

보통 여러 필드를 기반으로 조회를 할 때 복합 인덱스를 생성하는데, 이 인덱스를 생성할 때에는 순서가 있으며 그 생성 순서에 따라 인덱스의 성능이 달라지게된다.

1. 어떤 값과 같음을 비교하는 ==이나 equal이라는 쿼리가 있다면 제일 먼저 인덱스를 설정해야 한다.
2. 정렬에 쓰는 필드라면 그 다음 인덱스로 설정해야 한다.
3. 다중 값을 출력해야하는 필드나 쿼리 자체가 > 이거나 < 등 많은 값을 출력해야 하는 쿼리에 쓰는 필드라면 나중에 인덱스를 설정해야한다.
4. 유니크한 값의 정도를 카디널리티라고 하는데, 이 카디널리티가 높은 순서를 기반으로 인덱스를 생성해야 한다.

* Index 의 성능과 고려해야할 사항

SELECT 쿼리의 성능을 월등히 향상시키는 INDEX 항상 좋은 것일까? 쿼리문의 성능을 향상시킨다는데, 모든 컬럼에 INDEX 를 생성해두면 빨라지지 않을까? 결론부터 말하자면 그렇지 않다. 

우선, 첫번째 이유는 INDEX 를 생성하게 되면 INSERT, DELETE, UPDATE 쿼리문을 실행할 때 별도의 과정이 추가적으로 발생한다. INSERT 의 경우 INDEX 에 대한 데이터도 추가해야 하므로 그만큼 성능에 손실이 따른다. DELETE 의 경우 INDEX 에 존재하는 값은 삭제하지 않고 사용 안한다는 표시로 남게 된다. 즉 row 의 수는 그대로인 것이다. 이 작업이 반복되면 어떻게 될까? 실제 데이터는 10 만건인데 데이터가 100 만건 있는 결과를 낳을 수도 있는 것이다. 이렇게 되면 인덱스는 더 이상 제 역할을 못하게 되는 것이다. UPDATE 의 경우는 INSERT 의 경우, DELETE 의 경우의 문제점을 동시에 수반한다. 이전 데이터가 삭제되고 그 자리에 새 데이터가 들어오는 개념이기 때문이다. 즉 변경 전 데이터는 삭제되지 않고 insert 로 인한 split 도 발생하게 된다.

하지만 더 중요한 것은 컬럼을 이루고 있는 데이터의 형식에 따라서 인덱스의 성능이 악영향을 미칠 수 있다는 것이다. 즉, 데이터의 형식에 따라 인덱스를 만들면 효율적이고 만들면 비효율적은 데이터의 형식이 존재한다는 것이다. 어떤 경우에 그럴까?

이름, 나이, 성별 세 가지의 필드를 갖고 있는 테이블을 생각해보자. 이름은 온갖 경우의 수가 존재할 것이며 나이는 INT 타입을 갖을 것이고, 성별은 남, 녀 두 가지 경우에 대해서만 데이터가 존재할 것임을 쉽게 예측할 수 있다. 이 경우 어떤 컬럼에 대해서 인덱스를 생성하는 것이 효율적일까? 결론부터 말하자면 이름에 대해서만 인덱스를 생성하면 효율적이다.

왜 성별이나 나이는 인덱스를 생성하면 비효율적일까? 10000 레코드에 해당하는 테이블에 대해서 2000 단위로 성별에 인덱스를 생성했다고 가정하자. 값의 range 가 적은 성별은 인덱스를 읽고 다시 한 번 디스크 I/O 가 발생하기 때문에 그 만큼 비효율적인 것이다.

---

4.6 조인의 종류

조인이란 하나의 테이블이 아닌 두 개 이상의 테이블을 묶어서 하나의 결과물을 만드는 것을 말한다. NoSQL보다는 관계형 데이터베이스에서 많이 사용된다.

1. 내부 조인

SELECT * FROM TableA A
INNER JOIN TableB B ON
A.key = B.key

• 두 테이블 간에 교집합을 나타낸다.

2. 왼쪽 조인

SELECT * FROM TableA A
LEFT JOIN TableB B ON
A.key = B.key

• 테이블B의 일치하는 부분의 레코드와 함께 테이블A를 기준으로 완전한 레코드 집합을 생성한다.
• 만약 테이블B에 일치하는 항목이 없다면 해당 값은 null값이 된다.

3. 오른쪽 조인

SELECT * FROM TableA A
RIGHT JOIN TableB B ON
A.key = B.key

• 테이블A의 일치하는 부분의 레코드와 함께 테이블B를 기준으로 완전한 레코드 집합을 생성한다.
• 만약 테이블A에 일치하는 항목이 없다면 해당 값은 null값이 된다.

4. 합집합 조인

SELECT * FROM TableA A
FULL OUTER JOIN TableB B ON
A.key = B.key

• 양쪽 테이블에서 일치하는 레코드와 함께 테이블 A와 테이블 B의 모든 레코드 집합을 생성한다.
• 이때 일치하는 항목이 없으면 누락된 쪽에 null값이 포함되어 출력된다.

5. 크로스 조인

SELECT A.NAME, B.AGE
FROM EX_TABLE A
CROSS JOIN JOIN_TABLE B

모든 경우의 수를 전부 표현해주는 방식이다. A가 3개, B가 4개면 총 3*4 = 12개의 데이터가 검색된다.

6. 셀프 조인

SELECT A.NAME, B.AGE
FROM EX_TABLE A, EX_TABLE B

자기자신과 자기자신을 조인하는 것이다. 하나의 테이블을 여러번 복사해서 조인한다고 생각하면 편하다. 자신이 갖고 있는 칼럼을 다양하게 변형시켜 활용할 때 자주 사용한다.

---

4.7 조인의 원리

앞서 나온 조인들은 조인의 원리를 기반으로 조인 작업이 이루어진다.
각 조인의 방법마다 장단점이 있고, 조인의 성격과 조인의 원리의 디스크 I/O 복잡도에 따라 성능이 상이하다.

1. 중첩 루프 조인 (NLJ)

• 2중 for문과 같은 원리로 조인하는 방법이다.
• ex) A1, A2 테이블을 조인한다면, 첫 번째 테이블에서 행을 한 번에 하나씩 읽고 그 다음 테이블에서도 행을 하나씩 읽어 조건에 맞는 레코드를 찾아 결과값을 반환한다.
• 랜덤 접근에 대한 비용이 많이 증가하므로 대용량의 테이블에서 사용하지 않는다.
• 중첩 루프 조인에서 발전한 조인할 테이블을 작은 블록으로 나누어서 블록 하나씩 조인하는 블록 중첩 루프 조인 방식도 있다.

2. 정렬 병합 조인

• 각각의 테이블을 조인할 필드 기준으로 정렬하고, 정렬이 끝난 이후에 조인 작업을 수행하는 방법이다.
• 조인할 때 쓸 적절한 인덱스가 없고, 대용량의 테이블들을 조인하고, 조인 조건으로 <,>등 범위 비교 연산자가 있을 때 쓴다.

3. 해시 조인

• 해시 테이블을 기반으로 조인하는 방법이다.
• 두 개의 테이블을 조인한다고 했을 때 하나의 테이블이 메모리에 온전히 들어간다면, 보통 중첩 루프 조인(NLJ)보다 더 효율적이다.
• 동등(==)조건에서만 사용할 수 있다.

빌드 단계)

• 입력 테이블 중 하나를 기반으로, 메모리 내 해시 테이블을 빌드하는 단계
• 두 테이블 중 바이트가 더 작은 테이블을 기반으로 해서 해시 테이블 빌드
• 조인에 사용되는 필드가 해시 테이블의 키로 사용됨

'countries'가 빌드 입력으로 지정되었다고 가정했을때, 이상적으로 서버는 두 입력 중 더 작은 것을 빌드 입력으로 선택한다. (행수가 아닌 바이트로 측정) 'countries.country_id'는 빌드 입력에 속하는 조인 조건이므로 해시 테이블에서 키로 사용된다. 모든 행이 해시 테이블에 저장되면 빌드 단계가 완료된다. 

프로브 단계)

• 레코드 읽기를 시작하며, 각 레코드에서 key에 일치하는 레코드를 찾아서 결과값으로 반환함
• 이를 통해 각 테이블은 한 번씩만 읽게 되어, 중첩 루프 조인보다는 성능이 보통 더 좋음
• 사용 가능한 메모리 양 : 시스템 변수 join_buffer_size에 의해 제어됨. 런타임시 조정 가능

프로브 단계 동안 서버는 프로브 입력(이 예에서는 'persons')에서 행을 읽기 시작한다.

각 행에 대해 서버는 'persons.country_id'의 값을 조회 키로 사용하여 행과 일치하는 해시 테이블을 조사한다. 각 일치에 대해 조인된 행이 클라이언트로 전송된다. 결국 서버는 두 입력간에 일치하는 행을 찾기 위해 일정한 시간 조회를 사용하여 각 입력을 한번 만 스캔했다. 

https://iamwhat.tistory.com/42

Hash join in MySQL 8