관계형 데이터베이스 인덱스와 조인 정리

묵시적 JOIN과 명시적 JOIN은 무엇인가요?

묵시적(Implicit) JOIN과 명시적(Explicit) JOIN은 결과적으로 동일한 데이터를 반환하며, 현대의 관계형 데이터베이스 옵티마이저(Optimizer)는 내부적으로 이 둘을 동일한 실행 계획으로 처리합니다. 하지만 가독성과 유지보수성 측면에서 ANSI 표준 문법인 명시적 JOIN을 사용하는 것이 권장됩니다.

SELECT C.CourseName, T.TeacherName
FROM Courses C
INNER JOIN Teachers T 
  ON C.TeacherID = T.TeacherID
WHERE C.IsActive = 1; -- 조인과 필터링 조건이 명확히 분리됨

명시적 JOIN (ANSI SQL 표준): JOIN 키워드를 사용하여 조인 대상을 지정하고, ON 절에 조인 조건을 명시합니다. 조인 조건과 데이터를 필터링하는 WHERE 조건이 분리되어 쿼리의 목적이 명확해집니다.

SELECT C.CourseName, T.TeacherName
FROM Courses C, Teachers T
WHERE C.TeacherID = T.TeacherID 
  AND C.IsActive = 1; -- 조인 조건과 필터링 조건이 섞여 있음

묵시적 JOIN (과거 방식): FROM 절에 콤마(,)로 테이블을 나열하고, WHERE 절에 조인 조건을 기술합니다. 조인 조건과 일반 필터링 조건이 혼재되어 쿼리가 복잡해질 경우 오류를 범하기 쉽습니다. 그렇기 때문에 JOIN 와 필터링이 구분이 되는 명시적인 JOIN 을 사용하는 것이 일반적인 경우입니다.

관계형 데이터베이스에서 Join이 필요한 이유에 대해 설명해 보세요

관계형 데이터베이스(RDBMS)의 핵심 설계 원칙은 정규화(Normalization) 입니다. 정규화는 데이터의 중복을 최소화하고 삽입, 수정, 삭제 시 발생할 수 있는 이상 현상(Anomaly)을 방지하여 데이터의 무결성을 유지하기 위해 테이블을 여러 개로 분리하는 과정입니다.

결과적으로 하나의 논리적인 엔티티 정보가 여러 테이블에 분산 저장되므로, 사용자가 의미 있는 완성된 데이터를 조회하기 위해서는 분리된 테이블들을 다시 연결해야 합니다. JOIN은 이처럼 정규화로 인해 분리된 테이블들을 공통된 기준(주로 기본 키와 외래 키)으로 결합하여 원하는 데이터를 조합하고 추출하는 필수적인 연산입니다.

관계형 데이터베이스에서 논리적 조인이 무엇인지 설명해 보세요

논리적 조인은 INNER, OUTER와 같이 쿼리 수준에서 테이블간의 연결 관계를 정의하고 그 관계를 기반으로 데이터를 가져오는 방식을 이야기 합니다. 사용자가 작성하는 SQL 쿼리 수준에서 테이블 간의 연결 관계와 결과 집합을 정의하는 개념입니다. 스키마에 정의된 키(주로 PK, FK)를 기준으로 데이터를 어떻게 가져올지 결정합니다.

• INNER JOIN: 두 테이블 간에 조건이 일치하는 데이터만 반환합니다.
• OUTER JOIN (LEFT, RIGHT, FULL): 조건이 일치하지 않더라도 기준이 되는 테이블의 데이터를 모두 보존하여 반환합니다.
• CROSS JOIN: 두 테이블 간의 카테시안 곱(Cartesian Product)을 반환합니다.

이제 위에서 이야기한 JOIN에 대해서 구체적인 예시와 함께 설명해보겠습니다. 우선 아래와 같은 기준 테이블 데이터를 예시로 설명해보겠습니다.

1. Employees (직원 테이블)

2. Departments (부서 테이블)

INNER JOIN

SELECT E.EmpName, D.DeptName
FROM Employees E
INNER JOIN Departments D ON E.DeptID = D.DeptID;

두 테이블에서 조인 조건(ON)을 정확히 만족하는(일치하는) 교집합 데이터만 반환합니다. 이 쿼리문을 수행한 결과는 아래와 같습니다.

OUTER JOIN

SELECT E.EmpName, D.DeptName
FROM Employees E
LEFT OUTER JOIN Departments D ON E.DeptID = D.DeptID;

조인 조건에 일치하지 않는 데이터도 결과에 포함시키며, 기준이 되는 테이블의 방향에 따라 나뉩니다. 일치하는 데이터가 없는 컬럼은 NULL로 채워집니다. 이 쿼리에 대한 결과는 아래와 같습니다.

CROSS JOIN

SELECT E.EmpName, D.DeptName
FROM Employees E
CROSS JOIN Departments D;

조인 조건 없이 두 테이블의 모든 행을 결합하여 카테시안 곱(Cartesian Product) 을 생성합니다. 여기서 카테시안 곱이란 수학의 집합론에서 유래한 용어를 의미합니다. 쉽게 요약하면 한 테이블의 모든 행과 다른 테이블의 모든 행을 하나씩 빠짐없이 전부 연결하는 방식 을 의미합니다.

관계형 데이터베이스에서 물리적 조인이 무엇인지 설명해 보세요

논리적 조인은 사용자가 작성하는 SQL 쿼리 수준에서 테이블 간의 연결 관계와 결과 집합을 정의하는 개념입니다. 스키마에 정의된 키(주로 PK, FK)를 기준으로 데이터를 어떻게 가져올지 결정합니다. 논리적 조인의 예시는 다음과 같습니다.

• INNER JOIN: 두 테이블 간에 조건이 일치하는 데이터만 반환합니다.
• OUTER JOIN (LEFT, RIGHT, FULL): 조건이 일치하지 않더라도 기준이 되는 테이블의 데이터를 모두 보존하여 반환합니다.
• CROSS JOIN: 두 테이블 간의 카테시안 곱(Cartesian Product)을 반환합니다.

물리적 조인은 논리적 조인으로 작성된 쿼리를 데이터베이스 엔진(옵티마이저)이 실제로 디스크나 메모리에서 데이터를 읽어와 결합하는 내부적인 실행 알고리즘을 의미합니다. 인덱스 유무, 데이터의 크기, 메모리 상태 등에 따라 옵티마이저가 가장 효율적인 방식을 선택합니다. 이 방식의 예시는 다음과 같습니다.

• Hash Join: 조인 컬럼을 기준으로 해시 테이블을 메모리에 생성하여 조인하는 방식입니다. 대용량 데이터를 처리하거나 인덱스가 없을 때 주로 사용됩니다.
• Sort Merge Join: 조인 대상 컬럼을 기준으로 두 테이블을 정렬한 후, 병합하면서 조인하는 방식입니다. 데이터가 이미 정렬되어 있거나 등가(=) 조인이 아닐 때 사용됩니다.

논리적 조인은 실제 데이터를 조회하고 결합하는 방식을 의미하지만 물리적 조인은 쿼리가 실제고 실행되는 알고리즘, 디스크를 어떻게 일고 메모리를 불러서 사용할 것인지와 같은 쿼리를 실행하는 구체적인 방법을 의미합니다.

관계형 데이터베이스에서 Primary Index와 Secondary Index에 대해 설명해 보세요

-- Primary Index는 PK 제약조건 추가 시 자동 생성
CREATE TABLE Users (
    UserID INT PRIMARY KEY, -- Primary Index 생성
    UserName VARCHAR(50),
    Email VARCHAR(100)
);

-- Secondary Index 명시적 생성
CREATE INDEX idx_users_email ON Users(Email);

Primary Index는 테이블의 기본 키에 대해 생성되는 인덱스에 해당합니다. 이는 테이블의 순서를 결정하는 클러스터드 인덱스로 구현이 되는 경우가 많습니다. Primary Index는 항상 고유한 값을 가져야 하고 대부분의 데이터베이스에서는 자동 생성됩니다. 데이터의 물리적인 정렬 순서를 결정합니다. 즉, 인덱스의 리프 노드가 곧 실제 데이터 페이지입니다.

Secondary Index 는 Primary Index 외에 추가로 생성되는 인덱스인데 이는 데이터의 물리적인 순서와는 독립적으로 생성이 됩니다. 데이터의 물리적 순서에 영향을 주지 않으며, 별도의 인덱스 구조를 생성합니다. 인덱스의 리프 노드는 실제 데이터가 있는 위치를 가리키는 포인터(또는 PK 값)를 가집니다.

Secondary Index 는 다중 생성이 허용이 되고 또한 중복 또한 허용이 됩니다. 이러한 특성 때문에 데이터가 변경될 때마다 인덱스도 같이 업데이트해야 하는 비용이 발생하지만 다양한 검색 조건에 대한 성능을 향상 시킬 수 있기 때문에 적절히 사용하는 것이 좋습니다.

Covering 인덱스란?

Covering 인덱스란 쿼리에서 필요로 하는 모든 컬럼을 포함하는 인덱스를 말합니다. 이를 통해 데이터베이스는 테이블의 실제 데이터 페이지에 접근하지 않고도 인덱스만으로 쿼리를 처리할 수 있습니다.

-- 인덱스 생성 (UserID와 UserName을 모두 포함)
CREATE INDEX idx_users_id_name ON Users(UserID, UserName);

-- 커버링 인덱스 활용 쿼리: 실제 레코드에 접근하지 않고 인덱스 트리의 정보만으로 결과 반환
SELECT UserID, UserName 
FROM Users 
WHERE UserID = 100;

일반적인 세컨더리 인덱스 검색은 인덱스 트리를 탐색하여 찾은 포인터(혹은 PK)를 가지고 다시 실제 데이터 블록에 접근(Table Access)하여 나머지 컬럼 값을 가져와야 합니다. 하지만 쿼리의 SELECT, WHERE, GROUP BY 등에 사용된 모든 컬럼이 인덱스에 포함되어 있다면, 디스크의 실제 데이터 페이지에 접근할 필요 없이 인덱스 스캔만으로 결과를 반환할 수 있어 성능이 비약적으로 상승합니다.

Covering 인덱스는 디스크 I/O 를 줄여 쿼리 실행 속도를 높이고 메모리 사용을 최적화 하여 CPU 사용량을 줄일 수 있습니다. 하지만 Covering 인덱스는 특히 대용량 데이터를 다루는 환경에서 유용하지만, 인덱스 크기 증가와 쓰기 작업의 오버헤드 증가라는 트레이드오프가 있습니다. 따라서 쿼리 패턴과 데이터 특성을 고려하여 적절히 사용해야 합니다.

세컨더리 인덱스를 늘릴 경우 쓰기 작업의 오버헤드가 증가하는 이유

세컨더리 인덱스는 PK 인덱스와 달리 자동으로 생성되지 않고 개발자가 자주 조회되는 컬럼을 대상으로 인덱싱을 함으로 생성되는 인덱스가 세컨더리 인덱스입니다.

인덱스는 원본 테이블과 논리적으로 연결되어 있지만, 물리적으로는 완전히 독립된 별도의 자료구조(일반적으로 B-Tree 또는 B+Tree)로 디스크에 저장됩니다. 원본 테이블의 데이터가 변경되면 연결된 모든 인덱스 트리도 동기화되어야 합니다. 구체적인 오버헤드 발생 이유는 다음과 같습니다.

1. 추가적인 디스크 I/O (데이터 중복 쓰기)

테이블에 새로운 레코드를 1건 INSERT 할 때, 인덱스가 없다면 데이터 페이지에 1번만 기록하면 됩니다. 하지만 세컨더리 인덱스가 N개 있다면, 데이터 페이지 1번 외에도 N개의 인덱스 트리에 각각 새로운 엔트리를 추가하는 쓰기 작업이 발생합니다. 커버링 인덱스는 포함하는 컬럼 수가 많아 인덱스 레코드의 크기 자체도 크기 때문에 기록해야 할 데이터양이 늘어납니다.

2. UPDATE 연산의 비용 증가

원본 테이블에서 UPDATE가 발생할 때, 변경되는 컬럼이 세컨더리 인덱스에 포함되어 있다면 인덱스 트리 내에서도 변경이 일어나야 합니다. 인덱스는 정렬되어 있으므로, 단순히 값을 덮어쓰는 것이 아닙니다.

데이터베이스는 인덱스에서 기존 값의 위치를 찾아 삭제(또는 삭제 마킹) 처리하고, 변경된 새 값에 맞는 정렬 위치를 다시 찾아 새로운 엔트리를 삽입해야 합니다. 커버링 인덱스를 위해 인덱스에 여러 컬럼을 묶어둘 경우, 그 중 하나의 컬럼 값만 변경되어도 해당 인덱스 전체를 재배치하는 비용이 발생합니다.

3. B-Tree 구조 유지를 위한 '페이지 분할 (Page Split)'

인덱스는 항상 설정된 키 컬럼을 기준으로 정렬된 상태를 유지해야 합니다.

새로운 데이터가 삽입되거나 업데이트될 때, 해당 값이 들어가야 할 인덱스의 특정 데이터 블록(페이지)에 빈 공간이 없다면 데이터베이스는 새로운 페이지를 할당받아 기존 데이터의 절반을 새 페이지로 이동시킵니다.

이를 페이지 분할(Page Split) 이라고 합니다. 페이지 분할은 디스크의 새로운 공간을 할당하고, 데이터를 복사하고, 상위 브랜치 노드의 포인터를 수정하는 등 막대한 디스크 I/O와 CPU 자원을 소모하는 무거운 작업입니다. 인덱스의 개수가 많을수록 이 현상이 발생할 확률이 높아집니다.

B+Tree와 B-Tree의 차이를 설명해 보세요

B-Tree 의 경우 노드에 키와 데이터가 모두 저장이 되어 있기 때문에 빠른 포인터 검색이 가능합니다. 특정 키에 대해서 검색 시에 데이터와 같이 저장이 되어 있기 때문에 효율적입니다.

하지만 범위 검색에 불리하다는 단점이 있습니다. 이러한 이유는 구간이 나뉘어 있지 않고 노드 간의 수평적 연결이 없어 트리를 상하로 반복해서 이동해야 하기 때문에 특정 값을 찾기 위해서는 모든 노드를 찾아 키를 찾아야 하는 단점이 있습니다. 또한 키와 데이터가 담겨 있기 때문에 노드의 크기가 커질 수 있다는 단점이 존재합니다.

B+트리의 경우 키와 데이터를 분리하여 관리합니다. 내부 노드에는 키 값을 저장하고 리프 노드에는 데이터를 저장합니다. 또한 각 노드들은 Linked List로 연결이 되어 있기 때문에 순차적 접근이 가능하고 범위 탐색 또한 유리합니다.

하지만 업데이트 시 오버헤드가 발생할 수 있습니다. 데이터 변경이 리프 노드에 집중되면서, 리프 노드의 구조화가 발생할 수 있습니다.

B+Tree와 B-Tree의 주요 차이점은 데이터 저장 방식과 노드 구조에 있습니다.

                   [ 30 | 70 ]
                  /      |      \
       ┌─────────┘       |       └─────────┐
       |                 |                 |
[노드]              [노드]              [노드]
[10, 20]         [30, 40, 50, 60]      [70, 80, 90, 100]

B-Tree에서는 모든 노드(내부 노드와 리프 노드)가 키와 데이터를 저장할 수 있습니다. 노드의 구성은 키, 자식 포인터, 데이터 로 구성이 되어 있습니다. B-Tree의 키들은 정렬되어 저장이 되며, 이 키들을 기준으로 자식 노드로 검색이 분기가 됩니다. 자식 포인터는 한 노드에 있는 키의 수가 n개라면, 그 노드는 최대 n+1개의 자식 포인터를 가질 수 있습니다.

반면 B+Tree의 경우 내부 노드들은 키 값만을 저장하고 있고 실제 데이터는 리프 노드에만 저장이 됩니다. 또한 B+ Tree의 리프 노드들은 linked list처럼 서로 연결되어 있어 순차적으로 접근이 용이하며 이는 범위 검색에 더 효율적이고 이러한 특징으로 인해서 데이터베이스의 인덱싱등에서 자주 이용이 됩니다.

                   [ 내부 노드 ]
                  [ 30 | 70 ]
                 /      |      \
       ┌─────────┘      |       └─────────┐
       |                |                 |
[리프 노드]       [리프 노드]       [리프 노드]
[10, 20, 25]    [30, 40, 50, 60]    [70, 80, 90, 100]
       |                |                 |
       └─────→─────→────┘ (연결 리스트로 연결됨)

내부 노드, 루트에는 하위 리프 노드에 저장된 값들을 구분하기 위한 키 값을 저장하는 용도로 사용이 됩니다.

내부 노드는 데이터 검색 경로를 결정하는 인덱스 역할을 주로 수행하며, 실제 데이터는 리프 노드에 저장되지만(B 트리에서는 내부 노드에도 데이터가 저장될 수 있으나, 주된 목적은 하위 노드로의 분기 안내입니다), 전체적인 트리 구조는 데이터를 효율적으로 접근할 수 있도록 설계되어 있습니다.

인덱스의 최적화

인덱스 설계시 실제로 실행되는 커리들을 분석해야 합니다. 자주 사용되는 WHERE절 JOIN절, ORDER BY절 등이 무엇인지 파악하여 인덱스를 설계해야 합니다.

선택도는 전체 레코드 중에서 특정 조건을 만족하는 레코드의 비율을 의미합니다. 선택도가 높은(고유한 값이 많은) 열에 인덱스를 생성하는 것이 효율적입니다. 예를 들어, 성별과 같이 값의 종류가 적은 열보다 주민등록번호와 같이 고유한 값이 많은 열에 인덱스를 생성하는 것이 성능 향상에 더 효과적입니다.

이렇듯 인덱스 설계가 중요한 이유는 인덱스는 디스크 공간을 사용하고 데이터 변경 시 추가적인 작업을 필요로 하기 때문입니다. 여기서 주의해야할 것은 update, delete의 행위가 느린것이지, update, delete를 하기 위해 해당 데이터를 조회하는 것은 인덱스가 있으면 빠르게 조회가 됩니다.

인덱스가 너무 많으면 옵티마이저가 잘못된 인덱스를 선택할 확률이 높아지기 때문에 인덱스의 개수는 3~4개가 적절합니다.

인덱스는 SELECT 쿼리에서 성능을 오히려 저하 시킬 수 있습니다. 작은 테이블이나 쿼리가 테이블의 대부분의 데이터를 반환하는 경우, 인덱스를 사용하는 것보다 테이블 전체를 스캔하는 것이 더 효율적일 수 있습니다.

-- 성(last_name)과 이름(first_name)으로 복합 인덱스 생성
CREATE INDEX idx_user_name ON users(last_name, first_name);

-- [인덱스 사용 가능] 첫 번째 컬럼(last_name)을 조건으로 사용
SELECT * FROM users WHERE last_name = 'Kim';
SELECT * FROM users WHERE last_name = 'Kim' AND first_name = 'Chulsoo';

-- [인덱스 사용 불가 혹은 비효율적] 첫 번째 컬럼을 건너뛰고 두 번째 컬럼만 조건으로 사용
SELECT * FROM users WHERE first_name = 'Chulsoo';

복합 인덱스는 두 개 이상의 열을 결합한 인덱스입니다. 위의 예시에서는 last_name, first_name 으로 생성한 인덱스가 복합 인덱스에 해당합니다.

복합 인덱스를 생성할 때는 열의 순서가 매우 중요합니다. 이러한 이유는 복합키의 경우 왼쪽 접두사 규칙을 따르기 때문입니다. 왼쪽 접두사 규칙은 인덱스의 왼쪽부터 순서대로 열을 사용해야 인덱스가 효과적으로 작동한다는 의미입니다.

효율적인 복합 인덱스 설계를 위한 일반적인 원칙은 다음과 같습니다:

1. 동등 조건('=')에 사용되는 열을 먼저 배치
2. 높은 선택도(고유한 값이 많은)를 가진 열을 앞쪽에 배치
3. 범위 조건(>, <, BETWEEN)에 사용되는 열은 마지막에 배치
4. 쿼리의 WHERE, ORDER BY, GROUP BY 절에서 함께 사용되는 열들을 고려

먼저 말씀드릴 것은 1개의 컬럼만 인덱스를 걸어야 한다면, 해당 컬럼은 카디널리티(Cardinality)가 가장 높은 것을 잡아야 한다는 점입니다.

카디널리티(Cardinality)란 해당 컬럼이 가지는 고유한 값의 개수(Distinct Value) 를 나타냅니다. 예를 들어 성별, 학년 등은 카디널리티가 낮다고 얘기합니다.
반대로 주민등록번호, 계좌번호 등은 카디널리티가 높다고 얘기합니다.

인덱스로 최대한 효율을 뽑아내려면, 해당 인덱스로 많은 부분을 걸러내야 하기 때문입니다. 만약 성별을 인덱스로 잡는다면, 남/녀 중 하나를 선택하기 때문에 인덱스를 통해 50%밖에 걸러내지 못합니다.

하지만 주민등록번호나 계좌번호 같은 경우엔 인덱스를 통해 데이터의 대부분을 걸러내기 때문에 빠르게 검색이 가능합니다.