데이터베이스 기초 (데이터베이스 종류, 인덱스, B-트리, 최적화, 조인을 포함)

nuri-story 2023. 11. 16. 12:29

데이터베이스란?

- 데이터베이스(Database, DB)란?
: 데이터의 저장소입니다.

- DBMS(Database Management System, 데이터베이스 관리 시스템)란?
: 데이터베이스를 운영하고 관리하는 소프트웨어입니다.
: 계층형, 망형, 관계형 DBMS 중 대부분의 DBMS가 테이블로 구성된 관계형 DBMS(RDMBS)형태로 사용됩니다.

- SQL(Structured Query Language)란?
: 구조화된 질의 언어라는 뜻으로 관계형 데이터베이스에서 사용되는 언어. 표준 SQL을 배우면 대부분의 DBMS를 사용할 수 있습니다.

인덱스

인덱스는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료구조이다. 특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함꼐 저장됩니다. 또한, 인덱스 생성 시 오름차순으로 정렬하기 때문에 정렬된 주소체계라고 표현할 수 있습니다.

인덱스를 책에서의 목차라고 생각하면 이해하기 쉽습니다. 책에서 원하는 내용을 찾을 때 목차나 색인을 이용하면 훨씬 빠르게 찾을 수 있듯이 테이블에서 원하는 데이터를 찾기 위해 인덱스를 이용하면 빠르게 찾을 수 있습니다. 그러므로 데이터=책의 내용, 인덱스=책의 목차, 물리적 주소=책의 페이지 번호라고 생각하면 됩니다.

인덱스의 자료구조

인덱스는 여러 자료구조를 이용해서 구현하 수 있는데, 대표적인 자료구조로 해시 테이블과 B+Tree가 있습니다.

해시 테이블

해시 테이블은 컬럼의 값과 물리적 주소를 (key, value)의 한 쌍으로 저장하는 자료구조이다. 하지만 해시 테이블은 실제로 인덱스에서 잘 사용하지 않습니다.

그 이유는, 해시 테이블은 등호(=) 연산에 최적화되어있기 때문입니다. 데이터베이스에선 부등호(<,>) 연산이 자주 사용되는데, 해시 테이블 내의 데이터들은 정렬되어 있지 않으므로 특정 기준보다 크거나 작은 값을 빠른 시간 내에 찾을 수가 없습니다.

해시 테이블은 컬럼의 값과 물리적 주소를 (key, value)의 한 쌍으로 저장하는 자료구조입니다. 하지만 해시 테이블은 실제로 인덱스에서 잘 사용하지 않는다.

B-트리

B+Tree는 대부분의 DBMS 그리고 오라클에서 특히 중점적으로 사용하고 있는 가장 보편적인 인덱스입니다. 구조는 Root Node(기준)/Branch Node(중간)/Leaf Node(말단)으로 구성되며 계층적 구조를 가지고 있습니다.

Node는 데이터가 존재하는 공간입니다.
Leaf Node만 인덱스(Key)와 함께 데이터(Value)를 가지고 있고, 나머지 Root Node와 Branch Node는 데이터를 위한 인덱스(Key)만을 갖습니다.
Leaf Node에만 데이터를 저장하고 Leaf Node들끼리 LinkedList로 연결되어 있 선형 시간이 소모되어 시간 효율이 올라갑니다.
Root Node에서 경로를 확인 후, 그에 알맞는 Node들로 이동하여 최종적으로 원하는 데이터가 있는 Leaf Note에 도달합니다.

인덱스를 매 필드마다 설정하는 것은 좋은 방법인가요?

인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요합니다. 때문에 너무 많이 인덱스를 생성하면 하나의 쿼리문을 빠르게 만들 수 있지만 대신에 전체적인 데이터베이스의 성능 부하를 추래합니다. 때문에 무조건적인 인덱스 생성보다 SQL문을 효율적으로 짜고, 인덱스 생성은 마지막 수단으로 사용해야합니다.

최적화

우선, 'RDBMS'의 성능이 좋다는 기준은 무엇일까요?
RDBMS의 목적이 동시성 제어로 다수의 사용자가 빠르게 원하는 데이터를 저장하고 추출하는 것이니, 얼마나 많은 사용자가 얼마나 빨리 데이터를 처리할 수 있느냐가 성능의 기준이 되겠습니다. RDBMS 최적화 전략은 다음과 같습니다.

1. 파라미터 튜닝 (Parameter tuning)
2. 조인 전략 (Join strategy)
3. 인덱스 전략 (Index strategy)
4. 데이터 아키텍처 전략 (Data architecture strategy)
5. SQL 튜닝 (SQL tuning)

조인

1. Inner join

Inner join은 교집합( A ∩ B ) 연산과 같습니다. 조인 키 컬럼 값이 양쪽 테이블 데이터 집합에서 공통적으로 존재하는 데이터만 조인해서 결과 데이터 집합으로 추출하게 됩니다.

Inner join 구문은 아래와 같습니다.

ANSI-SQL	ORACLE SQL
SELECT 컬럼명 [, 컬럼명] FROM 테이블A [INNER] JOIN 테이블B ON 테이블A.조인키컬럼 = 테이블B.조인키컬럼 ;	SELECT 컬럼명 [, 컬럼명] FROM 테이블A , 테이블B WHERE 테이블A.조인키컬럼 = 테이블B.조인키컬럼 ;

위 ANSI-SQL 구문에서 'INNER' 키워드는 생략이 가능합니다. 우리가 일반적으로 사용하는 조인문( 'JOIN' 키워드만 사용하는 구문)이 바로 Inner join 구문입니다.

아래는 조인 종류를 설명할 예제 데이터입니다.

고객주문상품 테이블의 상품ID가 '5'인 데이터는 부모 테이블인 상품 테이블에 존재하지 않는 값으로 FK 위반입니다. 하지만, OUTER JOIN의 예를 설명하기 위한 억지스러운 설정일 뿐입니다. 무결성에 위배되는 데이터지만 설명을 위해 이해 부탁드립니다.

위 [예제 1] 상품 테이블과 고객주문상품 테이블을 Inner join하는 예제를 살펴보겠습니다.

-- ANSI-SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>   FROM 상품 p
    3>    JOIN 고객주문상품 o
    4>        ON p.상품ID = o.상품ID;

Inner join의 결과는 아래와 같습니다.

상품 테이블과 고객주문상품에서 조인 키 컬럼 값이 모두 존재하는 데이터만 출력된 것을 확인하실 수 있습니다.

2. Left outer join

Left outer join은 교집합 연산 결과와 차집합 연산 결과를 합친 것( (A ∩ B) ∪ (A - B) )과 같습니다.
조인 키 컬럼 값이 양쪽 테이블 데이터 집합에서 공통적으로 존재하는 데이터와 Left outer join 키워드 왼쪽에 명시된 테이블에만 존재하는 데이터를 결과 데이터 집합으로 추출하게 됩니다.

Left outer join 구문은 아래와 같습니다.

ANSI-SQL	ORACLE SQL
SELECT 컬럼명 [, 컬럼명] FROM 테이블A LEFT OUTER JOIN 테이블B ON 테이블A.조인키컬럼 = 테이블B.조인키컬럼 ;	SELECT 컬럼명 [, 컬럼명] FROM 테이블A , 테이블B WHERE 테이블A.조인키컬럼 = 테이블B.조인키컬럼(+) ;

ANSI-SQL 구문에서는 FROM 절에 조인하고자 하는 두 테이블명 사이에 'LEFT OUTER JOIN' 키워드를 입력합니다.
ORACLE에서는 WHERE 절에서 오른쪽 테이블의 조인 키 컬럼명 뒤에 '(+)' 기호를 입력해줍니다.

위 [예제 1] 상품 테이블과 고객주문상품 테이블을 Left outer join 하는 예제를 살펴보겠습니다.

-- ANSI-SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>    FROM 상품 p
    3>     LEFT OUTER JOIN 고객주문상품 o
    4>        ON p.상품ID = o.상품ID;

조인하고자 하는 테이블명을 LEFT OUTER JOIN 키워드를 기준으로 양쪽에 입력하고, 조인 조건은 ON 키워드 다음에 작성해줍니다.

-- ORACLE SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>   FROM 상품 p
    3>             , 고객주문상품 o
    4>  WHERE p.상품ID = o.상품ID(+);

Oracle에서는 FROM 키워드 이후에 조인할 테이블명을 콤마(,)로 구분하여 나열해주고, WHERE절에 조인 조건을 작성하되, 오른쪽 테이블의 조인 키 컬럼명 뒤에 '(+)' 기호를 추가로 입력해줍니다.

Left outer join의 결과는 아래와 같습니다.

상품 테이블과 고객주문상품에서 조인 키 컬럼 값이 모두 존재하는 데이터와 왼쪽에 명시되었던 테이블의 상품 테이블에만 존재하는 데이터(상품ID가 4인 데이터)가 출력된 것을 확인하실 수 있습니다.
왼쪽 상품 테이블에만 데이터가 존재하는 로우의 오른쪽 고객주문상품 테이블 컬럼 값은 NULL로 표시됩니다.

3. Right outer join

Right outer join도 교집합 연산 결과와 차집합 연산 결과를 합친 것( (A ∩ B) ∪ (A - B) )과 같습니다. 차집합의 기준 집합이 Left outer join과 반대일 뿐입니다.
아무튼 조인 키 컬럼 값이 양쪽 테이블 데이터 집합에서 공통적으로 존재하는 데이터와 Right outer join 키워드 오른쪽에 명시된 테이블에만 존재하는 데이터를 결과 데이터 집합으로 추출하게 됩니다.

Right outer join 구문은 아래와 같습니다.

ANSI-SQL	ORACLE SQL
SELECT 컬럼명 [, 컬럼명] FROM 테이블A RIGHT OUTER JOIN 테이블B ON 테이블A.조인키컬럼 = 테이블B.조인키컬럼 ;	SELECT 컬럼명 [, 컬럼명] FROM 테이블A , 테이블B WHERE 테이블A.조인키컬럼(+) = 테이블B.조인키컬럼 ;

ANSI-SQL 구문에서는 FROM 절에 조인하고자 하는 두 테이블명 사이에 'RIGHT OUTER JOIN' 키워드를 입력합니다.
ORACLE에서는 WHERE 절에서 왼쪽 테이블의 조인 키 컬럼명 뒤에 '(+)' 기호를 입력해줍니다.

위 [예제 1] 상품 테이블과 고객주문상품 테이블을 Right outer join 하는 예제를 살펴보겠습니다.

-- ANSI-SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>    FROM 상품 p
    3>   RIGHT OUTER JOIN 고객주문상품 o
    4>        ON p.상품ID = o.상품ID;

조인하고자 하는 테이블명을 RIGHT OUTER JOIN 키워드를 기준으로 양쪽에 입력하고, 조인 조건은 ON 키워드 다음에 작성해줍니다.

-- ORACLE SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>   FROM 상품 p
    3>            , 고객주문상품 o
    4>  WHERE p.상품ID(+) = o.상품ID;

Oracle에서는 FROM 키워드 이후에 조인할 테이블명을 콤마(,)로 구분하여 나열해주고, WHERE절에 조인 조건을 작성하되, 왼쪽 테이블의 조인 키 컬럼명 뒤에 '(+)' 기호를 추가로 입력해줍니다.

Right outer join의 결과는 아래와 같습니다.

상품 테이블과 고객주문상품에서 조인 키 컬럼 값이 모두 존재하는 데이터와 오른쪽에 명시되었던 테이블의 고객주문상품 테이블에만 존재하는 데이터(상품ID가 5인 데이터)가 출력된 것을 확인하실 수 있습니다.
오른쪽 고객주문상품 테이블에만 데이터가 존재하는 로우의 왼쪽 상품 테이블 컬럼 값은 NULL로 표시됩니다.

4. Full outer join

Full outer join은 합집합 연산 결과와 같습니다.
조인 키 컬럼 값이 양쪽 테이블 데이터 집합에서 공통적으로 존재하는 데이터와 한쪽 테이블에만 존재하는 데이터도 모두 결과 데이터 집합으로 추출하게 됩니다.

Full outer join 구문은 아래와 같습니다.

ANSI-SQL	ORACLE SQL
SELECT 컬럼명 [, 컬럼명] FROM 테이블A FULL OUTER JOIN 테이블B ON 테이블A.조인키컬럼 = 테이블B.조인키컬럼 ;	SELECT 컬럼명 [, 컬럼명] FROM 테이블A FULL OUTER JOIN 테이블B ON 테이블A.조인키컬럼 = 테이블B.조인키컬럼 ;

ANSI-SQL 구문에서는 FROM 절에 조인하고자 하는 두 테이블명 사이에 'FULL OUTER JOIN' 키워드를 입력합니다.
ORACLE에서 '(+)' 기호를 이용한 Full outer join은 지원하고 있지 않습니다. Full outer join은 ANSI-SQL 구문 형식과 동일하게 사용하면 됩니다.

위 [예제 1] 상품 테이블과 고객주문상품 테이블을 Right outer join 하는 예제를 살펴보겠습니다.

-- ANSI-SQL & ORACLE SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>    FROM 상품 p
    3>     FULL OUTER JOIN 고객주문상품 o
    4>        ON p.상품ID = o.상품ID;

조인하고자 하는 테이블명을 FULL OUTER JOIN 키워드를 기준으로 양쪽에 입력하고, 조인 조건은 ON 키워드 다음에 작성해줍니다.

Full outer join의 결과는 아래와 같습니다.

상품 테이블과 고객주문상품에서 조인 키 컬럼 값이 모두 존재하는 데이터와 상품 및 고객주문상품 테이블에만 존재하는 데이터(상품ID가 4,5인 데이터)가 출력된 것을 확인하실 수 있습니다.
왼쪽 상품 테이블에만 데이터가 존재하는 로우의 오른쪽 고객주문상품 테이블 컬럼 값은 NULL로, 오른쪽 고객주문상품 테이블에만 데이터가 존재하는 로우의 왼쪽 상품 테이블 컬럼 값도 NULL로 출력됩니다.

이상으로 조인의 종류에 대해 알아봤습니다.

다음 포스트에서는 조인 방식(Methods)에 대해 설명드리도록 하겠습니다.
조인 종류는 한쪽 테이블에만 존재하는 로우의 출력 여부를 기준으로 조인을 구분한 것이라면,

조인 방식은 각 로우를 매핑을 하는 매커니즘에 따라 조인을 구분한다고 보시면 됩니다.
더 자세한 내용은 아래 포스트를 참고해주세요.

참고

https://hongong.hanbit.co.kr/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EC%9D%B4%ED%95%B4%ED%95%98%EA%B8%B0-databasedb-dbms-sql%EC%9D%98-%EA%B0%9C%EB%85%90/

[데이터베이스 이해하기] Database(DB), DBMS, SQL의 개념

데이터베이스(Database, DB)란? : 데이터의 저장소. DBMS(Database Management System, 데이터베이스 관리 시스템)란? 데이터베이스를 운영하고 관리하는 소프트웨어. 계층형, 망형, 관계형 DBMS 중 대부분의 DBM

hongong.hanbit.co.kr

https://sparkdia.tistory.com/19

RDBMS 성능 최적화 전략

데이터의 가치가 높아지고 데이터 저장/관리 기술이 발전하면서 다양한 데이터베이스 소프트웨어가 출시되고 있습니다. Hadoop 클러스터링 환경에 기반한 Hive, HBase, Spark SQL는 빅데이터 수집/분석

sparkdia.tistory.com

https://sparkdia.tistory.com/17

테이블 조인 종류(Table Join Type)

데이터베이스에서 데이터는 다수의 테이블에 나뉘어 저장되어 있습니다. 데이터의 중복을 제거하고 무결성을 보장하기 위해서 데이터 성격에 따라 분류하여 테이블에 저장을 하는 겁니다. 이

sparkdia.tistory.com