04.데이터베이스

4.1 데이터베이스의 기본

데이터베이스(DB, DataBase)
일정한 규칙 혹은 규약을 통해 구조화되어 저장되는 데이터의 모음집

 

4.1.1 엔티티(entity)

사람, 장소, 물건, 사건, 개념 등 여러 개의 속성을 지닌 명사

추상적인 정보 대상이라고 이해하면 될것같음(예: 회원)

 

약한 엔티티와 강한 엔티티

엔티티는 두개로 나뉜다. 

예) A가 혼자서는 존재하지 못하고 B의 존재 여부에 따라 종속적이라면 A는 약한 엔티티 B는 강한엔티티다.

더 쉬운 예) 방은 건물안에만 존재하기 때문에 방은 약한 엔티티 건물은 강한엔티티다

 

4.1.2 릴레이션(relation)

정보를 구분하여 저장하는 기본 단위

엔티티에 관한 데이터를 데이터베이스는 릴레이션 하나에 담아 관리한다.

 

릴레이션은 관계형 데이터베이스에서는 테이블이라고 부른다.

NoSQL에서는 컬렉션이라고 부름

 

릴레이션은 데이터를 수학적으로 표현한 관계형 모델 상의 개념이다.

근데 DBMS에서는 릴레이션을 테이블로 구현한다는 말썸.

 

테이블과 컬렉션

데이터베이스 종류는 관계형 데이터베이스와 NoSQL 데이터베이스로 나뉜다.

관계형 데이터베이스 대표는 MySQL NoSQL 대표는 MongoDB가 있음

• MySQL 구조: 레코드-테이블-데이터베이스
• MongoDB: 도큐먼트-컬렉션-데이터베이스

 

4.1.3 속성(attribute)

릴레이션에서 관리하는 구체적이며 고유한 이름을 갖는 정보

서비스의 요구사항을 기반으로 관리해야 할 필요가 있는 속성들만 엔티티 속성이 된다.

 

4.1.4 도메인(domain)

릴레이션에 포함된 각각의 속성들이 가질 수 있는 값의 집합

 

예) 성별이라는 속성이 있을 때 이 속성이 가질 수 있는 값은 {남,여}라는 집합이 됨

즉, 회원이라는 릴레이션에 이름, 아이디, 주소, 전화번호, 성별이라는 속성이 있고 성별은 {남,여}라는 도메인을 가진다.

 

4.1.5 필드와 레코드

회원이란 엔티티는 member라는 테이블로 속성인 이름, 아이디 등을 가지고 있으며 name, ID, address 등의 필드를 가집니다.
그리고 이 테이블에 쌓이는 행(row) 단위의 데이터를 레코드라고 합니다.
또한, 레코드를 튜플이라고도 합니다

 

긍꼐.

1. 엔티티: 현실세계의 무언가.
   -> 추상적인 개념. 실제 데이터는 없다. 정보를 저장해야할 대상이라는 뜻
2. 테이블: 엔티티를 실제 DB에 표현한거
   -> 회원이라는 엔티티를 담는 테이블이 member이라는 뜻
3. 필드 = 속성 = 테이블의 열(Column)
   ->이름,아이디,주소 같은거
4. 레코드 = 튜플 = 테이블의 행(Row)
   -> 한 명의 회원 정보 한 줄. 예) 큰돌 | kundol | 서울 | 112

엔티티는 테이블로 표현되는것이다!!!!!!!!

앤나.

필드 = 속성 = 열

레코드 = 튜플 = 행

릴레이션 = 테이블 그자체

이라는 뜻.

외이렇게 지멋대로들 부르는지 모르겟다

하나로 통일하면 얼마나 참 좋을까

 

용어	현실 개념	DB 구현	다른 이름들
엔티티(entity)	정보의 대상(예: 회원)	테이블 이름	없음
속성(attribute)	정보의 항목(예: 이름, 주소)	필드, 컬럼	열(column),필드
릴레이션(Relation)	속성들 간의 수학적 관계	테이블 전체	테이블
튜플(tuple)	속성들의 값 묶음	한 행	레코드, 행(row)

 

필드타입

필드는 타입을 가진다.

 

숫자타입

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT가 있음

타입	용량(바이트)	최솟값(부호 있음)	최솟값(부호 없음)	최댓값(부호 있음(	최댓값(부호 없음)
TINYINT	1	-128	0	127	255
SMALLINT	2	-32768	0	32767	65535
MEDIUMINT	3	-8388608	0	8388607	16777215
INT	4	-2147483648	0	2147483647	4294967295
BIGINT	8	-263	0	263-1	264-1

 

날짜 타입

DATE, DATETIME, TIMESTEMP

DATE

날짜 부분은 있지만 시간 부분은 없는 값에 사용

3바이트의 용량을 가진다

 

DATETIME

날짜 및 시간 부분을 모두 포함하는 값에 사용

8바이트의 용량을 가진다

1000-01-01 00:00:00 ~ 9999-12-31 23:59:59지원

 

TIMESTAMP

날짜 및 시간 부분을 모두 포함하는 값에 사용

4바이트의 용량을 가진다.

1970-01-01 00:00:00 ~ 2038-10-19 03:14:07 지원

 

요기서 잠깐.

왜 타임스탬프는 지원 기간이 짧은가?!

저장 방식이 다르기 때문이다.

 

타임스탬프는 초를 저장한다.

1970년 1월1일 00:00:00 (UTC)를 기준으로해서 그로부터 몇 초가 지났는지 정수(int)로 저장한다.

이거를 Epoch time 또는 UNIX time이라고함

 

4바이트 = 32비트 정수

TIMESTAMP는 4바이트 = 32비트 정수로 저장

부호 있는 정수로 저장할 경우, 표현할 수 있는 범위는: –2,147,483,648 ~ +2,147,483,647 (약 21억 초) 임

• +2,147,483,647초 = 1970년 1월 1일부터 약 68년 후
  → 그래서 2038년 1월 ~ 10월까지만 가능

그러면 왜 데이트타임은 넉넉함?

데이트타임은 8바이트를 써서 연도,월,일,시,분,초를 따로따로 저장해버림

그래서 1000년부터 9999년까지 넉넉하다.

 

타임스탬프가 짧은건 시대도 잘못타고났긴하다고 한다.

옛날 시스템 메모리 아낄려고 4바이트로 만든게 아직까지 남아있는거임

 

문자타입

CHAR. VARCHAR, TEXT, BLOB, ENUM, SET

 

CHAR와 VARCHAR

수를 입력해서 몇 자까지 입력할지 정한다.

CHAR(30)이라면 최대 30글자까지 가넝함

 

• CHAR: 고정 길이 문자열이다. 길이는 0에서 255 사이의 값을 가진다. 레코드를 저장할 때 무조건 선언한 길이 값으로
  고정해서 저장된다. 
  예) CHAR(100)으로 선언 후 10글자를 저장해도 100바이트로 저장함
• VARCHAR: 가변 길이 문자열이다. 길이는 0에서 65.525 사이의 값으로 지정할 수 있다.
  입력된 데이터에 따라 용량을 가변시켜 저장함
  예)10글자 이메일을 저장할 경우 10글자에 해당하는 바이트 + 길이기록용 1바이트로 저장하게 된다. VARCHAR(1000)여도 입력한 길이만큼 저장한다는 뜻

유동적인 길이를 가진데이터는 VARCHAR를 쓸것

 

TEXT와 BLOB

모두 큰 데이터를 저장할 때 사용함

• TEXT: 큰 문자열 저장할 때 사용 주로 게시판의 본문 저장시 사용한다
• BLOB: 이미지, 동영상등 큰 데이터 저장에 쓴다.
  보통 아마존의 이미지 호스팅 서비스인 S3를 이용해 서버에 파일을 올리고 파일에 관한 경로를 VARCHAR로 저장함

ENUM과 SET

모두 문자열을 열거한 타입

• ENUM: ENUM('x-small, small, medinum, large, x-large) 형태로 쓰이며 단일 선택만 가능하다. 
  ENUM 리스트에 없는 잘못된 값을 삽입하면 빈 문자열이 삽입된다.
  ENUM을 이용하몀 x-small등이 0,1 등으로 매핑되어 메모리를 적게 사용하는 이점이 있음
  최대 65535개의 요소들을 넣을 수 있다.
• SET:ENUM이랑 비슷하지반 여러개의 데이터를 선택할 수 있고 비트 단위의 연산을 할 수 있다. 최대 64개의 요소를 집어넣을 수 있음

애플리케이션 수정에 따라 데이터베이스 ENUM이나 SET에서 정의한 목록을 수정해야한다는 단점이 있다.

 

4.1.6 관계

테이블은 서로의 관계가 정의 되어 있다.

이러한 관계를 관계 화살표로 나타낸다

 

1:1 관계

예) 유저당 유저 이메일

1:1 관계는 테이블을 두 개의 테이블로 나눠 테이블 구조를 더 이해하기 쉽게 만들어준다

 

1:N 관계

예) 한 유저당 여러개의 상품을 장바구니에 넣을때 이 경우 1:N 관계가 됨.
하나도 안넣을 경우도 있으니까 0도 포함되는 화살표를 통해 표현
한 개체가 다른 많은 개체를 포함하는 관계

 

 

N:M 관계

예) 학생도 강의를 많이 들을 수 있고 강의도 여러 명의 학생을 포함할 수 있다.

학생_강의 테이블이 끼어있는데 N:M은 테이블 두 개를 직접적으로 연결해서 구축하지 않고 1:N, 1:M이라는 관계를 갖는 테이블 두개로 설정한다.

 

 

4.1.7 키

테이블 간의 관계를 조금 더 명확히 하고 테이블 자체의 인덱스를 위해 설정되 장치

슈퍼키는 유일성이 있다. 그 안에 포함된 후보키는 최소성까지 갖춘 키다.

후보키에서 기본키로 선택되지 못한 키는 대체키가 된다.

• 유일성: 중복된 값이 없다
• 최소성: 필드를 조합하지 않고 최소 필드만 써서 키를 형성할 수 있는 것

기본키(PK, Primary Key)

유일성과 최소성을 만족하는 키

테이블의 데이터 중 고유하게 존재하는 속성

기본키에 해당하는 데이터는 중복되어서는 안된다.

 

 

사진 처럼 ID는 기본키로 설정 가능함

{ID,name}이라는 복합키를 기본키로 설정할 수 있지만 이러면 최소성만족이 안됨

기본키는 자연키, 인조키 중에 골라 형성한다.

 

자연키

• 중복된 값들을 제외하며 중복되지 않는 것을 자연스레 뽑다가 나오는 키를 자연키라고한다.
• 자연키는 언젠가는 변하는 속성을 가짐

인조키

• 주민번호, 이름, 성별 등의 속성이 있는 테이블에 인위적으로 유저 아이디를 부여한다.
• 이를 통해 고유 식별자가 생겨남.
• 인위적으로 생성한 키를 인조키라한다.
• 자연키와는 대조적으로 변하지 않는다. 보통 기본키는 인조키로 설정한다.

외래키(FK, Foreign Key)

다른 테이블의 기본키를 그대로 참조하는 값.

개체와 관계를 식별하는 데 사용

 

 

외래키는 중복되어도 된다.

 

후보키(candidate key)

기본키가 될 수 있는 후보들

유일성과 최소성을 동시에 만족한다

 

대체키(alternate eky)

후보키가 두 개 이상일 경우 하나를 기본키로 지정하고 남은 후보키

 

슈퍼키(super key)

레코드를 유일하게 식별할 수 있는 유일성을 갖춘 키

4.2 ERD와 정규화 과정

ERD(Entity Relationship Diagram) 
데이터베이스를 구축할 때 가장 기초적인 뼈대 역할을 하며, 릴레이션 간의 관계들을 정의한것.

 

 

4.2.1 ERD의 중요성

ERD는 시스템의 요구 사항을 기반으로 작성되며 ERD를 기반으로 데이터베이스를 구축한다.

비정형 데이터를 충분히 표현할 수 없는 단점이 있음

 

• 비정형 데이터: 비구조화 데이터를 말하며, 미리 정의 된 데이터 모델이 없거나 미리 정의된 방식으로 정리되지 않은 정보

4.2.2 예제로 배우는 ERD

책보세용

 

4.2.3 정규화 과정

정규화과정
릴레이션 간의 잘못된 종속 관계로 인해 데이터베이스 이상 현상이 일어나서 이를 해결하거나, 저장 공간을 효율적으로 사용하기 위해 릴레이션을 여러 개로 분리하는 과정

 

정규형 원칙

• 좀 더 좋은 구조로 만들어야하고
• 자료의 중복성을 감소해야하고
• 독립적인 관계는 별개의 릴레이션으로 표현해야하고
• 각각의 릴레이션은 독립적인 표현이 가능해야하는 것

제1정규형

릴레이션의 모든 도메인이 더 이상 분해될 수 ㅇ벗는 원자값만으로 구정되어야한다.

릴레이션의 속성 값 중에서 한 개의 기본키에 대해 두 개 이상의 값을 가지는 반복 집합이 있어선 안된다.

반복 집합이 있으면 제거.

제2정규형

릴레이션이 제1정규형이며 부분 함수의 종속성을 제거한 형태

• 부분함수의 종속성 제거: 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속적인것

릴레이션을 분해할 때 동등한 릴레이션으로 분해해야하고, 정보 손실이 발생하지 않는 무손실 분해로 분해되어야 한다.

 

제3정규형

제2정규형이고 기본키가 아닌 모든 속성이 이행적 함수(transitive FD)을 만족하지 않는 상태

 

이행적 함수 종속

A -> B와 B->C가 존재하면 논리적으로 A->C가 성립하는데 

이때 집합 C가 집합 A에 이행적으로 함수 종속이 되었다고한다.

 

예) 유저 ID와 등급, 할인율이 정해져 있는 테이블을 다음과 같이 분해하는 것

 

보이스/코드 정규형(BCNF)

제3정규형이고 , 결정자가 후보키가 아닌 함수 종속 관계를 제거하여 릴레이션의 함수 종속 관계에서 모든 결정자가 후보키인 상태

 

• 결정자: 함수 종속 관계에서 특정 종속자를 결정짓는 요수 x->y일때 x는 결정자 y는 종속자

4.3 트랜잭션과 무결성

4.3.1 트랙잭션

트랜잭션
데이터베이스에서 하나의 논리적 기능을 수행하기 위한 작업의 단위를 말한다.
데이터베이스에 접근하는 방법은 쿼리이므로, 여러 개의 쿼리들을 하나로 묶는 단위를 말함
원자성, 일관성, 독립성, 지속성이 특징이며 이를 ACID 특징이라고 한다

 

원자성(atomicity)

all or nothing

트랜잭션과 관련된 일이 모두 수행되었거나 되지 않았거나를 보장하는 특징

 

예) 트랜잭션을 커밋했는데 문제가 발생하여 롤백하는 경우 그 이후에 모두 수행되지 않음을 보장하는 것

1000만원을 가진 홍철이가 0원을 가지 ㄴ규영이한테 500만원 이체 

-> 홍철이는 500만원 규영이 500만원 가지게 됨

-> 해당 결과들은 operation 단위들로 이루어진 과정을 거친다

1. 홍철의 잔고를 조회
2. 홍철에게 500만원 뺀다
3. 규영에게 500만원 넣기

1-3의 operation중 데이터베이스 사용자는 세가지 과정을 볼 수도 참여할 수도 없음. 

끝난 상황만 보는겨.

 

작업을 취소한다고 했을때 홍철이는 다시 1000만원 규영이는 0원임. 

일부 operation만 적용되지 않은것을 의미한다. 그래서 all or nothing인것

 

커밋과 롤백

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

update, insert, delete의 쿼리가 하나의 트랜잭션 단위로 수행되고 데이터베이스에 영구 저장된다.

 

하지만 에러나 여러 이슈 때문에 트랜잭션 전으로 돌려야 될때 사용하는 것이 롤백임

• 롤백: 트랜잭션으로 처리한 하나의 묶음 과정을 일어나기 전으로 돌리는 일(취소)

트랜잭션 전파

여러 트랜잭션 관련 메서드의 호출을 하나의 트랜잭션에 묶이도록 하는 것

 

일관성(consistency)

허용된 방식으로만 데이터를 변경해야하는 것

데이터베이스에 기록된 모든 데이터는 여러 가지 조건, 규칙에 따라 유효함을 가져야함

 

격리성(isolation)

트랜잭션 수행 시 서로 끼어들지 못하는 것

격리성은 여러 개의 격리 수준으로 나뉘어 격리성을 보장한다.

위로 갈수록 동시성이 강해지지만 격리성은 약해지며 아래로 갈수록 동시성은 약해지고 격리성은 강해진다.

REPEATABLE_READ: 팬텀 리드 발생

READ_COMMITTED: 팬텀 리드, 반복 가능하지 않은 조회 발생

READ_UNCOMMITTED: 팬텀 리드, 반복 가능하지 않은 조회, 더티 리드가 발생

 

격리 주순에 따라 발생하는 현상

팬텀리드(phantom read)

한 트랜잭션 내에서 동일한 쿼리를 보냈을 때 해당 조회 결과가 다른 경우

 

반복 가능하지 않은 조회(non-repeatable read)

한 트랜잭션 내의 같은 행에 두 번 이상 조회가 발생했는데, 그 값이 다른 경우

팬텀 리드와 다른 점은 반복 가능하지 않은 조회는 행 값이 달라질 수도 있는데, 팬텀 리드는 다른 행이 선택될 수도 있다는 것을 의미함

 

더티 리드(dirty read)

반복 가능하지 않은 조회와 유사하며 한 트랜잭션이 실행 중일 때 다른 트랜잭션에 의해 수행되었지만 아직 커밋 되지않은 행의 데이터를
읽을수 있을 때 발생

 

격리 수준

SERIALIZABLE

말 그대로 트랜잭션을 순차적으로 진행시키는 것을 말한다.

• 여러 트랜잭션이 동시에 같은 행에 접근할 수 없음
• 매우 엄격한 수준으로 해당 행에 대해 격리함
• 이후 트랜잭션이 이 행에 대해 일어난다면 기다려야함
• 교착 상태가 일어날 확률도 높고 가장 성능이 떨어지는 격리수준이다.

 

REPEATABLE_READ

하나의 트랜잭션이 수정한 행을 다른 트랜잭션이 수정할 수 없도록 막아주지만 새로운 행을 추가하는 것을 막지 않는다.

이후에 추가된 행을 발견할 수도 있음.

 

 

READ_COMMITTED

가장 많이 사용되는 격리수준

• PostgreSQL, SQL Server, 오라클에서 기본값으로 설정되어 있음
• 다른 트랜잭션이 커미하지 않은 정보는 읽을 수 없다.
• 커밋 완료된 데이터에 대해서만 조회를 허용
• 어떤 트랜잭션이 접근한 행을 다른 트랜잭션이 수정할 수 있음

READ_UNCOMMITTED

가장 낮은 격리수준

• 하나의 트랜잭션이 커밋되기 이전에 다른 트랜잭션에 노출되는 문제가 있지만 가장 빠르다.
• 데이터 무결성을 위해 되도록이면 사용하지 않는게 좋음
• 거대한 양의 데이터를 어림잡아 집계할 때 사용하면 좋음

지속성(durability)

성공적으로 수행된 트랜잭션은 영원히 반영되어야 하는 것을 의미

데이터베이스에 시스템 장애가 발생해도 원래 상태로 복구하는 회복 기능이 있어야함을 뜻한다.

데이터베이스는 체크섬, 저널링, 롤백 등의 기능을 제공한다.

 

• 체크섬: 중복 검사의 한 형태로, 오류 정정을 통해 송신된 자료의 무결성을 보호하는 단순한 방법
• 저널링: 파일 시스템 또는 데이터베이스 시스템에 변경 사항을 반영(commit)하기 전에 로깅하는 것, 트랜잭션 등 변경 사항에 대한 로그를 남기는 것

4.3.2 무결성

무결성: 데이터의 정확성, 일관성, 유효성을 유지하는 것

 

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

 

무결성 종류

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

 

4.4 데이터베이스의 종류

4.4.1 관계형 데이터베이스

관계형 데이터베이스(RDBMS)
행과 열을 가지는 표 형식 데이터를 저장하는 형태의 데이터베이스를 가리킨다. SQL이라는 언어를 써서 조작함

 

MySQL

C,C++로 만들었으며 MyISAM 인덱스 압축기술, B-트리 기반의 인덱스, 스레드 기반의 메모리 할당 시스템, 매우 빠른 조인,

최대 64개의 인덱스를 제공한다.

대용량 데이터베이스를 위해 설계되어 있고 롤백, 커밋, 이중 암호 지원 보안 등의 기능을 제공함

 

PostgreSQL

MySQL 다음으로 개발자들이 선호하는 디비 기술임

최대 테이블의 크기는 32TB이며 SQL뿐만 아니라 JSON을 이용해서 데이터에 접근할 수 있음

 

4.4.2 NoSQL 데이터베이스

Not only SQL이라는 슬로건에서 생겨난 데이터베이스임

SQL을 사용하지 않는 데이터베이스를 말하며 대표적으로 MongoDB,redis 등이 있음

 

MongoDB

JSON을 통해 데이터에 접근할 수 있고 Binary JSON 형태로 데이터가 저장되며 와이어드타이거 엔진이 기본 스토리지 엔진으로 장착된

키-값 데이터 모델에서 확장된 도큐먼트 기반의 데이터베이스임

 

• 확장성이 뛰어나다
• 빅데이터를 저장할 때 성능이 좋다
• 고가용성과 샤딩, 레플리카셋을 지원한다
• 스키마를 정해 놓지 않고 데이터를 삽입할 수 있따
• 도큐먼트를 생성할 때마다 다른 컬렉션에서 중복된 값을 지니기 힘든 유니크 값인 ObjectID가 생성된다.

redis

인메모리 데이터베이스이자 키-값 데이터 모델 기반의 데이터베이스다.

기본적은 데이터 타입은 문자열이며 최대 512MB까지 저장할 수 있다.

셋,해시등을 지원한다.

 

pub/sub 기능을 통해 채팅 시스템, 다른 데이터베이스 앞단에 두어 사용하는 캐싱 켸층, 단순한 키-값이 필요한 세션정보관리, 정렬된 셋 자료구조를 이용한 실시간 순위표 서비스에 사용한다.

4.5 인덱스

4.5.1 인덱스의 필요성

• 인덱스: 데이터를 빠르게 찾을 수 있는 하나의 장치

4.5.2 B-트리

B-트리: 데이터베이스 시스템에서 인덱스를 구성할 때 자주 쓰는 자료구조임. 특히 대용량 데이터를 빠르게 검색할 수 있다.

 

인덱스는 보통 B-트리라는 자료 구조로 이루어져 있다. 

이는 루트 노드, 리프 노드, 루트 노드와 리프노드 사이에 있는 브랜치 노드로 나뉜다.

 

• 루트 노드: 트리 가장 위에 있는 노드
• 브랜치 노드: 중간에 위치한 노드들로, 검색을 빠르게 하기 위해 하위 노드를 가리키는 역할
• 리프 노드: 가장 아래에 있는 노드들로, 실제 데이터(또는 해당 위치를 가리키는 포인터)를 저장하고 있음

 

트리 탐색은 맨 위 루트 노드부터 탐색이 일어나며 브랜치 노드를 거쳐 리프 노드까지 내려온다.

 

57보다 같거나 클때까지 <= 를 기반으로 처음 루트 노드에서는 39,83 이후 아래 노드로 내려와 46,53,57등 정렬된 값을 기반으로 탐색한다.

 

이렇게 루트 노드부터 시작하여 마지막 리프 노드에 도달해서 57이라는 데이터 포인터를 통해 결괏값을 반환한다.

 

4.5.3 인덱스 만드는 방법(MySQL,MongoDB)

 

MySQL

클러스터형 인덱스와 세컨더리 인덱스가 있다.

클러스터형 인덱스

테이블당 하나를 설정할 수 있다.

• primary key 옵션으로 기본키로 만들면 클러스터형 인덱스 생성
• 기본키로 만들지 않고 unique not null 옵션을 붙이면 클러스터형 인덱스 생성

create index... 명령어를 기반으로 만들면 세컨더리 인덱스를 만들수 있다.

하나의 인덱스만 생성할 것이라면 클러스터형 인덱스를 만드는 것이 세컨더리 인덱스를 만드는 것보다 성능이 좋음

 

세컨더리 인덱스

보조 인덱스로 여러 개의 필드 값을 기반으로 쿼리를 많이 보낼때 생성해야하는 인덱스

다양한 필드를 기반으로 쿼리를 보낼 때는 세컨더리 인덱스를 사용하자.

 

MongoDB

도큐먼트를 만들면 자동으로 ObjectID가 형성 되며, 해당 키가 기본키로 설정된다.

세컨더리키도 부가적으로 설정해서 기본키와 세컨더리키를 같이 쓰는 복합 인덱스를 설정할 수 있다.

 

4.5.4 인덱스 최적화 기법(MongoDB 기반)

1. 인덱스는 비용이다.

인덱스는 두 번 탐색하도록 강요한다.  인덱스리스트, 그다음 컬렉션 순으로 탐색하기 때문이며, 관련 읽기 비용이 들게 된다.

 

또한 컬렉션이 수정되었을 때 인덱스도 수정되어야 한다. 이때 B-트리의 높이를 균형 있게 조절하는 비용도 들고, 데이터를 효율적으로 조회할 수 있도록 분산시키는 비용도 들게 된다.

 

그렇기 때문에 쿼리에 있는 필드에 인덱스를 무작정 다 설정하는 것은 답이 아님. 또한 컬렉션에서 가져와야 하는 양이 많을 수록 인덱스를 사용하는 것은 비효율적이다.

 

2. 항상 테스팅 하라

explain() 함수를 통해 인덱스를 만들고 쿼리를 보낸 이후에 테스팅을 하며 걸리는 시간을 최소화해야한다.

MySQL에서는 아래와 같은 코드로 데스팅을 수행한다.

 

EXPLAIN
SELECT * FROM t1
JOIN t2 ON t1.c1 = t2.c1

 

 

3. 복합 인덱스는 같음, 정렬, 다중 값 , 카디널리티 순이다.

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

 

같음, 정렬, 다중 값, 카디널리티 순으로 생성해야한다

 

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

4.6 조인의 종류

• 조인(join): 하나의 테이블이 아닌 두 개 이상의 테이블을 묶어서 하나의 결과물을 만드는 것

두 테이블 간의 교집합이 있다고 할 때, 다음과 같은 네 가지 조인이 있다.

• 내부 조인(inner join): 왼쪽 테이블과 오른쪽 테이블의 두 행이 모두 일치하는 행이 있는 부분만 표기
• 왼쪽 조인(left outer join): 왼쪽 테이블의 모든 행이 결과 테이블에 표기
• 오른쪽 조인(right outer join): 오른쪽 테이블의 모든 행이 결과 테이블에 표기
• 합집합 조인(full outer join): 두 개의 테이블을 기반으로 조인 조건에 만족하지 않은 행까지 모두 표기

4.6.1 내부 조인

내부 조인은 두 테이블 간에 교집합을 나타낸다.

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

 

 

4.6.2 왼쪽 조인

테이블 B의 일치하는 부분의 레코드와 함께 테이블 A를 기준으로 완전한 레코드 집합을 생성

만약 테이블 B에 일치하는 항목이 없으면 해당 값은 null 값이 된다.

 

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

 

4.6.3 오른쪽 조인

테이블 A에서 일치하는 부분의 레코드와함께 테이블 B를 기준으로 안전한 레코드 집합을 생성

만약 테이블 A에서 일치하는 항목이 없으면 해당 값은 null이 된다.

 

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

 

 

4.6.4 합집합 조인(완전 외부 조인)

양쪽 테이블에서 일치하는 레코드와 함께 테이블 A와 테이블 B의 모든 레코드 집합을 생성한다.

일치하는 항목이 없으면 누락된 쪽에 null 값이 포함되어 출력된다.

 

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

 

 

4.7 조인의 원리

4.7.1 중첩 루프 조인(NLJ, Nested Loop Join)

중첩 for문과 같은 원리로 조건에 맞는 조인을 사용하는 방법

랜덤 접근에 대한 비용이 많이 증가하므로 대용량의 테이블에서는 사용하지 않는다.

 

예) t1,t2 테이블을 조인한다 라고 했을 때 첫 번째 테이블에서 행을 한 번에 하나씩 읽고 그다음 테이블에서도 행을 하나씩 읽어 

조건에 맞는 레코드를 찾아 결괏값을 반환한다.

for each row in t1 matching reference key {
	for each row in t2 matching reference key {
    	if row satisfies join conditions, send to client
    }
}

 

중첩 루프 조인에서 발전한 조인할  테이블을 작은 블록으로 나눠서 블록 하나씩 조인하는 블록 중첩 루프 조인(BNL, Block Nested Loop)방식도 있음

 

4.7.2 정렬 병합 조인

각각의 테이블을 조인할 필드 기준으로 정렬하고 정렬이 끝난 이후에 조인 작업을 수행하는 조인

조인을 할 때 쓸 적절한 인덱스가 없고 대용량의 테이블들을 조인하고 조인 조건으로 <,>등 범위 비교 연산자가 있을때 쓴다.

 

4.7.3 해시 조인

해시 테이블을 기반으로 조인하는 방법

두 개의 테이블을 조인한다고 했을 때 하나의 테이블이 메모리에 온전히 들어간다면 보통 중첩 루프 조인보다 더 효율적임

동등(=) 조인에서만 사용할 수 잇다.

MySQL의 해시 조인 단계는 빌드 단계, 프로브 단계로 나뉜다.

 

빌드 단계

입력 테이블 중 하나를 기반으로 메모리 내 해시 테이블을 빌드하는 단계

예) persons와 countries라는 테이블을 조인한다고 햇을 때 둘 중에 바이트가 더 작은 테이블을 기반으로 해서 테이블을 빌드한다.

 

조인에 사용되는 필드가 해시 테이블의 키로 사용된다. countries.country_id가 키로 사용되는 것을 볼 수 있다.

 

프로브 단계

프로브 단계 동안 레코드 읽기를 시작하며, 각 레코드에서 persons.country_id에 일치하는 레코드를 찾아서 결괏값으로 반환한다.

이를 통해 각 테이블은 한 번씩만 읽게 되어 중첩해서 두 개의 테이블을 읽는 중첩 루프 조인보다 보통은 성능이 더 좋다.

사용 가능한 메모리양은 시스템 변수 joim_buffer_size에 의해 제어되며, 런타임 시에 조정할 수 있다.