논리적인 SQL 개념 용어

서브쿼리 위치에 따른 SQL 용어

서브쿼리란 쿼리 안의 보조쿼리를 가르키는 용어다. 메인쿼리를 기준으로 내부에 서브쿼리가 어디에 위치하는지에 따라 부르는 용어가 달라진다.

SELECT (SELECT ... FROM ...)        -> SELECT 절 : 스칼라 서브쿼리
FROM (SELECT ... FROM ...)          -> FROM 절 : 인라인 뷰
WHERE 컬럼명 IN (SELECT ... FROM ...) -> WHERE절 : 중첩 서브쿼리

스칼라 서브쿼리

• 서브쿼리가 반환하는 결과가 단일 값을 가지는 경우이다. 즉, 결과 값이 항상 1행 1열의 구조로 출력되어야 한다.
• 메인쿼리 SELECT 절 목록이나 WHERE에 주로 사용된다.
• 출력되는 데이터 건수가 1건이어야 하므로 집계함수가 자주 쓰인다.

SELECT 이름,
       (SELECT count(*)
          FROM student AS st2
		     WHERE st2.name = st1.name) cnt
  FROM student AS st1

인라인 뷰

• 메인쿼리의 FROM 절에 있는 또다른 SELECT 절이다.
• FROM 절 내부에서 일시적으로 뷰를 생성하는 방식으로 인라인 뷰라고 불린다.
• 인라인 뷰의 결과는 내부적으로 메모리 또는 디스크에 임시 테이블을 생성해서 활용한다.

SELECT st2.number, st2.name
  FROM (SELECT *
          FROM st
         WHERE gender = '남') st2

중첩 서브쿼리

• 메인쿼리의 WHERE 절에 있는 또 다른 SELECT 절을 중첩 서브쿼리라고 한다.
• WHERE 절에서 단순한 값을 비교 연산하는 대신, 서브쿼리를 추가하여 비교 연산하기 위해 사용한다.
• 보통 연산자나 IN, EXISTS 문을 많이 사용한다.

SELECT *
  FROM student
 WHERE number = (SELECT MAX(number) FROM student)

 

---

메인쿼리와의 관계성에 따른 SQL 용어

서브쿼리는 그 자체가 독립적인 형태로 존재할 수 있고 메인쿼리와 끈끈한 관계를 유지하며 존재할 수도 있다.

비상관 서브쿼리

• 서브쿼리가 독립적으로 실행된 뒤 메인쿼리에게 그 결과를 던저주는 형태이다.
• 서브쿼리 실행 → 메인쿼리 실행의 순서로 수행된다.
• DB버전 및 옵티마이저에 따라 서브쿼리가 제거되고 하나의 메인쿼리로 통합되는 뷰 병햡, 즉 SQL 재 작성이 작동할 수 있다.

SELECT *
  FROM student
 WHERE number IN (SELECT * number
                    FROM student
                   WHERE gender = '남')

상관 서브쿼리

• 서브쿼리가 수행되려면 메인쿼리의 값을 받아야 하므로 서브쿼리와 메인쿼리는 끈끈한 관계를 유지하게 된다.
• 메인쿼리 실행(student.num 가져오기) → 서브쿼리 실행(advisor.num = student.num) → 메인쿼리 실행(SELECT * FROM student ~)

SELECT *
  FROM student
 WHERE num IN (SELECT num
                 FROM advisor
                WHERE gender = advisor.num = student.num)

 

---

반환 결과에 따른 SQL 용어

단일행 서브쿼리

서브쿼리의 결과과 1건의 행으로 반환

SELECT ...
  FROM ...
 WHERE num = (SELECT max(num) FROM student)

다중행 서브쿼리

서브쿼리의 결과가 여러 건의 행으로 반환

SELECT ...
  FROM ...
 WHERE num IN (SELECT max(num) FROM student GROUP BY code)

다중열 서브쿼리

서브쿼리의 결과가 여러개의 행과 열로 반환

SELECT ...
  FROM ...
 WHERE (name, code) IN (SELECT name, code FROM student where name like '김%')

 

---

조인 연산방식 용어

내부 조인(inner join)

• 교집합 데이터를 반환한다.

내부조인 - 명시적 조인

SELECT 학생.학번, 학생.이름, 지도교수.교수명
  FROM 학생
  JOIN 지도교수
    ON 학생.학번 = 지도교수.학번

내부조인 - 암시적조인

SELECT 학생.학번, 학생.이름, 지도교수.교수명
  FROM 학생, 지도교수
 WHERE 학생.학번 = 지도교수.학번

왼쪽 외부 조인(left outer join)

• 왼쪽 테이블 기준 오른쪽 테이블과 조인을 수행하지만, 조인 조건과 일치하지 않아도 왼쪽 테이블의 결과는 모두 반환한다.(조인조건과 일치하지 않는 행은 null

SELECT 학생.학번, 지도교수.교수명
  FROM 학생
  LEFT OUTER JOIN 지도교수
    ON 학생.학번 = 지도교수.학번

오른쪽 외부 조인(right outer join)

• 오른쪽 테이블 기준 왼쪽 테이블과 조인을 수행하지만,조인 조건과 일치하지 않아도 오른쪽 테이블의 결과는 모두 반환한다.(조인조건과 일치하지 않는 행은 null)
• 보통 left outer join을 자주 사용한다.

SELECT 학생.학번, 지도교수.교수명
  FROM 학생
  RIGHT OUTER JOIN 지도교수
    ON 학생.학번 = 지도교수.학번

교차 조인

• 수학적 관점에서 봤을 때 데카트르 곱 이라고 하는 곱집합 개념
• 조인에 참여하는 테이블에서 발생할 수 있는 모든 조합을 찾아내서 반환한다.
• 모든 경우의 수가 출력 대상이므로 조인 연산과정에서의 시간적, 공간적 리소스 점유 측면에서 오버헤드가 발생하는 만큼 주의해야한다.

SELECT 학생.학번, 지도교수.교수명
  FROM 학생
 CROSS JOIN 지도교수

자연 조인

• 2개 테이블에 동일한 열명이 있을 때 조인 조건절을 따로 작성하지 않아도 자동으로 조인을 수행하는 방식
• 조인이 제대로 성사되면 내부 조인과 동일한 결과가 출력된다.
  • 조인하는 열의 데이터 유형이 다르더라도 자연 조인이 수행됨

SELECT 학생.*, 지도교수.*
  FROM 학생
NATURAL JOIN 지도교수

• 2개 테이블에 공통으로 존재하는 열명이 없다면?
  • 발생 가능한 경우의 수를 모두 조합하는 교차조인으로 수행된다.
  • 따라서 자연 조인은 열명 변경에 따라 출력 결과가 달라지기 때문에 의도치 않은 결과가 출력될 가능성이 높아 잘 사용하지 않는다.

---

조인 알고리즘 용어

드라이빙 테이블과 드리븐 테이블

테이블을 조인할 때, 테이블에 동시 접근할 수는 없으므로 데이터에 접근하는 우선순위가 존재한다.

• 드라이빙 테이블(=outer table) : 먼저 접근하는 테이블
• 드리븐 테이블(=inner table) : 드라이빙 테이블의 검색 결과를 통해 데이터를 검색하는 테이블

드라이빙 테이블에서 많은 건수가 반환되면 해당 결과를 가지고 드리븐 테이블에 접근하게 되기 때문에 적은 결과가 반환될 것으로 예상되는 테이블을 드라이빙 테이블로 설정하고 조인하는 열을 인덱스로 설정하도록 구성해야한다.

중첩 루프 조인(NL조인)

• 드라이빙 테이블의 데이터 1건당 드리븐 테이블을 반복해 검색하며 최종적으로 양쪽 테이블에 공통된 데이터를 출력한다.

SELECT 학생.학번, 학생.이름, 비상연락망.관계, 비상연락망.연락처
  FROM 학생
  JOIN 비상연락망
    ON 학생.학번 = 비상연락망.학번
 WHERE 학생.학번 IN (1, 100)

# 학생 데이터는 100건
# 비상연락망 데이터는 1000건 이라고 가정한다.

✅ case1 ) 기본키와 인덱스가 없다면?

1. 학번이 1인 학생을 검색하기 위해 학생 테이블 데이터 100건에 모두 접근, 이후 학번 1과 동일한 데이터를 가진 비상연락망 데이터를 검색하기 위해 비상연락망 데이터 1000건에 모두 접근한다.
2. 학번이 100인 학생을 검색하기위해 학생 테이블 데이터 100건에 모두 접근, 이후 학번 100과 동일한 데이터를 가진 비상연락망 데이터를 검색하기 위해 비상연락망 데이터 1000건에 모두 접근한다.
3. 따라서 총 100 + 1000 + 100 + 1000의 데이터에 접근하게된다.

✅ case2 ) 학생 테이블의 학번, 비상연락망 테이블의 학번 컬럼이 인덱스로 설정되있다면?

1. 학번이 1인 학생을 인덱스를 통해 바로 접근하고 비상 연락망 테이블의 학번이 1인 데이터에 바로 접근한다.
2. 학번이 100인 학생을 인덱스를 통해 바로 접근하고 비상 연락망 테이블의 학번이 100인 데이터에 바로 접근한다.
3. 예를 들어 비상연락망의 테이블의 학번이 1, 100인 데이터가 2건씩 있다면 총 1 + 2 + 1 + 2 데이터에 접근하게 된다.

인덱스를 이용해 테이블의 데이터를 찾아가는 과정에서 임의 접근 방식인 랜덤 엑세스가 발생

→ 랜덤 액세스를 줄일수 있도록 데이터의 액세스 범위를 좁혀나가면서 인덱스를 설계하고 조건절을 작성해야함

→ 랜덤 액세스를 유발하는 인덱스는 비고유 인덱스일 때 해당

기본키는 클러스터형 인덱스 이므로 순서대로 테이블에 적재되어있어 조회 효율이 매우 높다!

블록 중첩 루프 조인(BNL 조인)

드리븐 테이블의 풀 스캔을 줄이는게 목적으로 성능 저하를 개선하는 조인 알고리즘 방식이다.

• 탄생 배경
  • 학생 테이블이 드라이빙 테이블이고 비상연락망 테이블은 인덱스가 없다고 가정.
  • 중첩 루프 조인을 한다면 학생 테이블에서 학번 1을 바로 찾은 뒤, 비상연락처 테이블의 모든 데이터에 접근해야할 것임 → 드리븐 테이블을 항상 풀스캔해야함
  • 이러한 경우에 중첩 루프 조인의 효율성을 높이고자 탄생함.
• 드라이빙 테이블에 조인 버퍼(join buffer) 개념을 도입하여 조인 성능을 향상시킨다.
• 수행되는 절차
  1. 드라이빙 테이블인 학생 테이블에서 학번 1과 100에 해당하는 데이터를 검색한다.
  2. 검색된 데이터를 조인 버터에 가득 채워질 때 까지 적재한다.
     → 조인 버퍼는 메모리 공간으로, 드리븐 테이블과의 조인을 수행하기 위한 일종의 캐시 역할을 함.
  3. 조인 버퍼와 비상연락망 테이블 데이터를 비교한다.
     → 이를 통해 드리븐 테이블을 한 번만 풀 스캔하면서 조인을 수행
  4. 조인 버퍼에 적재된 데이터와 드리븐 테이블을 조인을 반복하면서 원하는 데이터를 찾아낸다.
• 이러한 식으로 조인 버퍼의 데이터들과 드리븐 테이블을 한 번의 풀스캔으로 원하는 데이터를 모두 찾을 수 있다. → 드리븐 테이블을 풀 스캔하는 횟수를 최소화하여 성능을 향상

배치 키 액세스 조인(BKA 조인)

중첩 루프 조인 방식의 랜덤 액세스 문제를 해결하고자 접근할 데이터를 미리 예상하고 가져오는 데 착안된 조인 알고리즘

• 조인 버퍼 활용 : 블록 중첩 루프 조인에서 활용한 드라이빙 테이블의 조인 버퍼 개념을 그대로 사용하여 드라이빙 테이블의 조인 키 값을 배치로 가져오고 메모리에 적재한다.
• 랜덤 버퍼의 도입 : 드리븐 테이블에 필요한 데이터를 미리 예측하고 정렬된 상태로 랜덤 버퍼에 담는다. 이를 통해 드리븐 테이블에 대한 랜덤 액세스가 아닌 시퀀셜 액세스를 수행한다.
  • 다중 범위 읽기(MRR) : 브리븐 테이블의 데이터를 예측하고 정렬된 상태로 버퍼에 적재하는 기능
• 시퀀셜 액세스 수행 : 랜덤 버퍼에 미리 예측된 데이터가 정렬된 상태로 담겨 있기 때문에 드리븐 테이블에 대한 시퀀셜 액세스를 수행하면서 조인을 효율적으로 진행한다.

해시 조인

• MySQL 8.0.18부터 지원
• 선후 관계를 두고 조인을 수행하는 중첩 루프 조인과 달리 조인에 참여하는 각 테이블의 데이터를 내부적으로 해시값을 만들어 내부 조인을 수행한다.
• 해시값으로 내부 조인을 수행한 결과 조인 버퍼에 저장되므로 조인 열의 인덱스를 필수로 요구하지 않는다.