SQL 튜닝 용어 정리(2)

논리적인 SQL 개념 용어

• SQL 문 작성에 필요한 주변 오브젝트와 SQL 문의 상호관계, 연관성과 알고리즘에 관한 논리적 개념 용어를 다룬다.

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

• 서브쿼리(subquery)란 쿼리 안의 보조쿼리를 가리키는 용어이다.
• 가장 바깥쪽의 SELECT 문인 메인쿼리(main query)를 기준으로 내부에 SELECT 문을 추가로 작성해서 서브쿼리를 만든다. 작성한 서브쿼리의 위치에 따라 각각 부르는 용어가 다르다.
• SELECT 절 : 스칼라 서브쿼리
• FROM 절 : 인라인 뷰
• WHERE 절 : 중첩 서브쿼리

스칼라 서브쿼리

• 메인쿼리의 SELECT 절에 있는 또 다른 SELECT 절이 스칼라 서브쿼리라고 했지만 사실 FROM 절이나 WHERE 절 등에서도 스칼라 서브쿼리를 사용할 수 있다.
• 메인쿼리의 SELECT 절에는 최종 출력하는 열들이 나열되므로, 출력 데이터 1건과 스칼라 서브쿼리의 결과 건수가 일치해야 한다.
  • 스칼라 서브쿼리의 결괏값이 2개 이상 나온다면 에러가 발생한다.
  • 즉, 스칼라 서브쿼리의 결괏값은 1행 1열의 구조로 출력되어야 한다.

SELECT 학생2.학번, 학생2.이름
  FROM (SELECT *
          FROM 학생
         WHERE 성별='남') 학생2;

인라인 뷰

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

SELECT *
  FROM 학생
 WHERE 학번=(SELECT MAX(학번)
              FROM 학생);

중첩 서브쿼리

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

SELECT *
  FROM 학생
 WHERE 학번=(SELECT MAX(학번)
              FROM 학생)

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

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

비상관 서브쿼리

• 비상관 서브쿼리는 메인쿼리와 서브쿼리 간에 관계성이 없음을 의미한다.
• 서브쿼리가 독자적으로 실행된 뒤 메인쿼리에게 그 결과를 던져주는 형태인 것이다.
• 비상관 서브쿼리에서는 서브쿼리가 먼저 실행된 뒤에 그 결과를 메인쿼리가 활용한다.
  • 서브쿼리 실행 → 메인쿼리 실행의 순서로 실행된다.

SELECT *
  FROM 학생
 WHERE 학번 IN (SELECT 학번
                 FROM 학생
                WHERE 성별='남')

• 위의 비상관 서브쿼리는 성별='남' 조건으로 학생 테이블에서 데이터를 가져온 뒤 그 결과를 메인쿼리의 학생 테이블로 전달하여 최종 데이터를 출력한다.
• 이때 DB 버전 및 옵티마이저에 따라 서브쿼리가 제거되고 하나의 메인쿼리로 통합되는 뷰 병합(view merging), 즉 SQL 재작성(rewrite)이 작동할 수도 있다.

상관 서브쿼리

• 상관 서브쿼리는 메인쿼리와 서브쿼리 간에 관계성이 있음을 의미한다. 서브 쿼리가 수행되려면 메인쿼리의 값을 받아야 하므로 서브쿼리와 메인쿼리는 서로 끈끈한 관계를 유지한다.
• 이러한 상관 서브쿼리는 SELECT 절에 작성하는 스칼라 서브쿼리와 WHERE 절에 작성하는 중첩 서브쿼리일 때 발생한다.

SELECT ...
  FROM 학생
 WHERE <subquery> IN

<subquery>
SELECT ...
  FROM 지도교수
 WHERE 학생.학번 = ...

• 메인 쿼리 실행(학생.학번 데이터 가져오기) → 서브쿼리 실행(지도교수.학번 = 학생.학번) → 다시 메인쿼리 실행한 뒤 결과 출력(SELECT * FROM 학생~)
• DB 버전 및 옵티마이저에 따라 서브쿼리가 제거되고 하나의 메인쿼리로 통합되는 뷰 병합, 즉 SQL 재작성으로 작동할 수 있다.

반환 결과에 따른 SQL 용어

• 서브쿼리의 결과 유형은 수치적 기준으로 구분할 수 있다.
• 단순히 1건의 행 데이터만 반환하는 경우와 2개 이상의 행 데이터를 반환하는 경우, 그리고 2개 이상의 행과 열 데이터를 반환하는 경우로 정리한다.
• 단일행 서브쿼리(single-row subquery)
• 다중행 서브쿼리(multiple-row subquery)
• 다중열 서브쿼리(multiple-column subquery)

단일행 서브쿼리

• 단일행 서브쿼리는 서브쿼리 결과가 1건의 행으로 반환되는 쿼리이다. 그에 따라 메인쿼리의 조건절에서는 =, <, > 등의 연산자와 비교한다. 스칼라 서브쿼리와 동일하다고 볼 수 있다.

SELECT ...
  FROM ...
 WHERE 학번 = 서브쿼리
<서브쿼리>
SELECT MAX(학번)
  FROM 학생

다중행 서브쿼리

• 다중행 서브쿼리는 서브쿼리 결과가 여러 건의 행으로 반환되는 쿼리이다. 그에 따라 메인쿼리의 조건절에서는 IN 구문으로 서브쿼리에서 반환되는 값들을 받는다.

SELECT ...
  FROM ...
 WHERE 학번 IN 서브쿼리
<서브쿼리>
SELECT MAX(학번)
  FROM 학생
 GROUP BY 전공코드

다중열 서브쿼리

• 다중열 서브쿼리에서는 서브쿼리 결과가 여러 개의 열과 행으로 반환된다. 그에 따라 메인쿼리의 조건절에서는 IN 구문과 함께 서브쿼리에서 반환될 열들을 동일하게 나열해 서브쿼리 결과를 받는다.

SELECT ...
  FROM ...
 WHERE (이름,전공코드)
        IN 서브쿼리
<서브쿼리>
SELECT 이름,전공코드
  FROM 학생
 WHERE 이름 LIKE '김%'

조인 연산방식 용어

• 필요한 데이터끼리 결합할 때 조인(join)이라는 방식을 사용한다. 분리된 데이터 간의 공통된 정보, 즉 동일한 열값 또는 키값 기준으로 데이터를 논리적으로 연결할 수 있다.
• 내부 조인(inner join)
• 왼쪽 외부 조인(left outer join)
• 오른쪽 외부 조인(right outer join)
• 전체 외부 조인(full outer join)
  • 왼쪽 외부 조인과 오른쪽 외부 조인이 통합된 방식.
  • MySQL과 MariaDB는 지원하지 않는다.
• 교차 조인(cross join)
  • 데카르트 곱(cartesian product)이라고 하는 곱집합 개념
• 자연 조인(natural join)
  • 2개 테이블에 동일한 컬럼명이 있을 때 조인 조건절을 따로 작성하지 않아도 자동으로 조인을 수행해주는 방식
  • 동일한 열명이 있다면 내부 조인, 없다면 발생 가능한 경우의 수를 모두 조합하는 교차 조인이 수행된다.

SELECT 학생.*, 지도교수.*
  FROM 학생
NATURAL JOIN 지도교수
# ON 학생.학변 = 지도교수.학번 과 같은 구문을 입력하면 에러 발생

조인 알고리즘 용어

• 다수의 테이블에서 조인을 수행할 때는 동시에 여러 개의 테이블에 접근할 수 없는 만큼 접근하는 우선순위를 정하게 된다. 다수의 테이블에서 첫 번재로 접근할 테이블, 두 번째로 접근할 테이블, 세 번째로 접근할 테이블 등 내부적으로 순번을 정하고, 차례대로 테이블에 접근한 결과를 다음 순번의 테이블로 전달한다.
• 테이블에 접근하는 선후 관계에 따라 드라이빙 테이블과 드리븐 테이블이라는 용어로 구분한다. 그리고 각 테이블에 접근해 조인을 수행하는 알고리즘에도 여러 가지 방식이 있다.

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

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

• 먼저 접근하는 테이블인 드라이빙 테이블(outer table)은 학생 테이블
• 학생 테이블의 검색 결과를 통해 뒤늦게 데이터를 검색하는 테이블인 드리븐 테이블(inner table)은 비상연락망 테이블
• 가능하면 적은 결과가 반환될 것으로 예상되는 드라이빙 테이블을 선정하고, 조인 조건절의 열이 인덱스로 설정되도록 구성해야 한다.

인덱스 랜덤 액세스

• 보조 인덱스인 Non-clustered index에서 range scan을 사용하여 데이터 로우를 읽는 경우 랜덤 디스크 액세스가 발생할 수 있다.

MRR(Multi Range Read)

• 인덱스를 통한 랜덤 디스크 엑세스를 줄이기 위해 도입한 기능으로 이러한 Random I/O를 Sequential I/O로 처리할 수 있도록 도와준다. 바로 데이터를 조회하지 않고, 버퍼에 primary key 값들을 채운다음 정렬하여 최대한 PK 순서대로 데이터를 접근할 수 있도록 한다.

중첩 루프 조인

Nested Loop Join (NL 조인)

• 드라이빙 테이블의 데이터 1건당 드리븐 테이블을 반복해 검색하며 최종적으로는 양쪽 테이블에 공통된 데이터를 출력한다.
• 위의 예제에서 인덱스 없이 극단적인 경우에는 학번 1 데이터를 위하여 학생 테이블 100건, 비상연락망 테이블 1000건으로 100 + 1000, 학번 100 데이터를 위하여 학생 테이블 100건, 비상연락망 테이블 1000건 접근으로 총 2200번의 접근이 발생하게 된다.
• 인덱스가 있을 시, 학생 테이블에 한 번의 접근 후 비상연락망 테이블에 인덱스를 통해 바로 접근하여 훨씬 적은 수로 접근이 가능하다.

블록 중첩 루프 조인

Block Nested Loop Join (BNL 조인)

• 만약, 드리븐 테이블에 인덱스가 없다면 드라이빙 테이블에서 검색된 데이터마다 드리븐 테이블에 대해서 테이블 풀 스캔을 진행해야 한다. 이를 해결하기 위해 테이블 사이에 조인 버퍼를 두어서 단 한 번의 테이블 풀 스캔만 하도록 해서 성능 저하를 개선시킨 방식이다.
• 드라이빙 테이블에서 검색된 데이터들을 조인 버퍼에 가득찰 때까지 모두 넣고 드리븐 테이블의 각 데이터와 비교하면서 한 번의 테이블 풀 스캔으로 조인이 가능하다.
• 블록 해시 조인(block hash join)이란 것도 있음. 블록 중첩 루프 조인과 유사함.

 

배치 키 액세스 조인

Batched Key Access Join (BKA 조인)

• 중첩 루프 조인 방식은 필연적으로 데이터 접근 시 인덱스에 의한 랜덤 액세스가 발생하므로, 액세스할 데이터의 범위가 넓다면 분명 비효율적인 조인 방식이다. 이러한 랜덤 액세스의 단점을 해결하고자 접근할 데이터를 미리 예상하고 가져오는 데 착안한 조인 알고리즘이 배치 키 엑세스 조인이다.
• 블록 중첩 루프 조인 방식처럼 조인 버퍼를 두고 드리븐 테이블을 위한 랜덤 버퍼를 둔 후 드라이빙 테이블의 검색된 데이터를 조인 버퍼에 적재합니다. 그리고 드리븐 테이블에서 MRR를 통해 예측된 데이터를 랜덤 버퍼에 적재하고 조인 버퍼와 랜덤 버퍼를 조인 조건절로 비교하며 순차적으로 조인하는 방식이다.

해시 조인

Hash Join

• MySQL 8.0.18 버전부터 지원되는 조인 방식이다.
• MariaDB 5.3 이후 버전부터 블록 중첩 루프 해시(block nested loop hash)라는 이름으로 해시 조인 기능을 제공한다.
• 조인에 참여하는 각 테이블의 데이터를 내부적으로 해시값으로 만들어 내부 조인을 수행한다. 해시값으로 내부 조인을 수행한 결과는 조인 버퍼에 저장되므로 조인열의 인덱스를 필수로 요구하지 않아도 된다.