Hyowon's Archive

친절한 SQLP 튜닝

2장

• 인덱스 탐색 과정은 수직적 탐색과 수평적 탐색 두 단계로 이루어진다.
• 인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용한다.
• 인덱스 튜닝의 두 가지 핵심요소
  • 인덱스 스캔 과정에서 발생하는 비효율 줄이는 것 = 인덱스 스캔 효율화 튜닝
  • 테이블 엑세스 횟수를 줄이는 것 = 랜덤 엑세스 최소화 튜닝
• IOT, 클러스터, 파티션, Prefetch, Batch I/O 등의 기능들의 본질은 랜덤 I/O의 최소화이다.
• NL 조인이 대량 조인할 때 느린 이유도 랜덤I/O 때문이다. 이를 위해 소트머지 조인과 해시 조인이 개발되었다.
• DBMS는 일반적으로 B*Tree 인덱스를 사용한다.
• Root가 위쪽에 있고, Branch를 거쳐 맨 아래 Leaf가 있다.
• Root와 Branch 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 갖는다.
• Root와 Branch 블록에는 주소값을 갖지 않는 LMC(Leftmost Child)라는 특별한 레코드가 있다.
• Leaf 블록에 저장된 각 레코드는 키값 순으로 정렬되어 있을 뿐만 아니라 테이블 레코드를 가리키는 주소값, 즉 ROWID를 갖는다.
  • ROWID = 데이터 블록 주소 + 로우 번호
  • 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
  • 블록 번호 : 데이터 파일 내에서 부여한 상대적 순번
  • 로우 번호 : 블록 내 순번
• 인덱스 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정 = 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정이다. 즉, 인덱스 스캔 시작지점을 찾는 과정이다.
• 인덱스 수평적 탐색 : 데이터를  찾는 과정 = 찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프 블록을 수평적으로 스캔한다.
  
  • 조건절에 만족하는 데이터를 모두 찾기 위해서, ROWID를 얻기 위해서 수평적 탐색을 수행한다.
• 인덱스 선두 컬럼을 모두 등치(=) 조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 두든 블록 I/O 개수가 같으므로 성능도 똑같다. 즉 , 선두 컬럼이 모두 등치 조건이면 수직적 탐색 과정의 비용이 같다는 것이다.
• B*Tree 인덱스에서 어떤 값으로 탐색하더라도 인덱스 루트에서 리프 블록에 도달하기까지 읽는 블록 수가 같다. 

• 인덱스 기본 사용법은 인덱스를 Range Scan 하는 방법을 의미한다.
• 인덱스 컬럼(정확히는 선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다. '인덱스를 정상적으로 사용한다'는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미한다. 즉, 리프 블록 일부만 스캔하는 Index Range Scan을 의미한다.
• 인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고 멈출 수도 없어 리프 블록 전체를 스캔해야만 한다. 즉, 일부가 아닌 전체를 스캔하는 Index Full Scan 방식으로 작동한다.
• Index Range Scan은 인덱스에서 일정 범위를 스캔한다는 뜻이다. 일정 범위를 스캔하려면 시작지점과 끝지점이 있어야 한다. 인덱스 컬럼을 가공했을 때 인덱스 스캔 시작점을 찾을 수 없기 때문에 정상적으로 사용할 수 없다.
• OR 조건식을 SQL 옵티마이저가 UNION ALL 형태로 변환할 수 있는데 이를 'OR Expansion'이라고 한다.
• use_concat 힌트를 통해 'OR Expansion'를 유도할 수 있다.
• IN 조건은 OR 조건을 표현하는 다른 방식일 뿐이다. IN 조건절에 대해서는 SQL 옵티마이저가 IN-List Iterator 방식을 사용한다. IN-List 개수만큼 Index Range Scan을 반복하는 것이다. UNION ALL 방식으로 변환한 것과 같은 효과를 얻을 수 있다.
• 인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다는 사실이다. 가공하지 않은 상태로 말이다.
• 문제는, 인덱스를 Range Scan 한다고 해서 항상 성능이 좋은 것은 아니라는 것이다.
• '인덱스를 탄다'라는 표현은 '인덱스를 Range Scan한다'와 같은 의미인 셈이다. 그런데 인덱스를 정말 잘 타는지는 인덱스 리프 블록에서 스캔하는 양을 따져봐야 알 수 있다.
• 인덱스 컬럼을 가공해도 인덱스를 사용할 순 있지만, 찾고자 하는 데이터가 전체 구간에 흩어져 있기 때문에 Range Scan이 불가능하거나 비효율이 발생한다.
• 옵티마이저는 SQL에 ORDER BY가 있어도 정렬 연산을 따로 수행하지 않는다. 인덱스를 스캔하면서 출력한 결과 집합은 어차피 인덱스 컬럼 순으로 정렬되기 때문이다. 따라서 실행계획에 SORT 연산이 없다.
• 조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 종종 있다.
• 인덱스를 이용해 정렬 연산 없이 최소 또는 최대값을 빠르게 찾을 때 인덱스 리프블록의 왼쪽(MIN) 또는 오른쪽(MAX)에서 레코드 하나(FIRST ROW)만 읽고 멈춘다.
• 인덱스는 문자열 기준으로 정렬돼 있는데 이를 숫자값으로 바꾼 값 기준으로 최소 또는 최대값, ORDER BY를 요구하면 정렬 연산을 생략할 수 없다.
• 오라클은 자동으로 형변환 처리해준다.
• 오라클에서 숫자형과 문자형이 만나면 숫자형이 이긴다. 숫자형 컬럼 기준으로 문자형 컬럼을 변환한다는 뜻이다.연산자가 LIKE 일 때는 다르다. LIKE 자체가 문자열 비교 연산자이므로 이때는 문자형 기준으로 숫자형 컬럼이 변환된다. 숫자형 컬럼을 LIKE 조건으로 검색하면 자동 형변환이 발생해 인덱스 엑세스 조건으로 사용되지 못한다.
• 날짜형과 문자형이 만나면 날짜형이 이긴다.
• DECODE(A, B, C, D)를 처리 할 때 'A = B' 이면 C를 반환하고, 아니면 D를 반환한다. 이때 반환값의 데이터 타입은 세 번째 인자 C에 의해 결정된다. 세 번째 인자가 NULL 값이면 네 번 째 인자가 숫자형이어도 문자열로 변환하고, 문자열 기준으로 MAX() 를 수행하게 된다. 950보다 5000이 크다고 판단하게 된다.
• SQL 성능 원리를 잘 모르는 개발자는 TO_CHAR, TO_DATE, TO_NUMBER 같은 형변환 함수를 의도적으로 생략하곤 한다. 이들 함수를 생략하면 연산횟수가 줄어 성능이 더 좋지 않을까 생각하기 때문이다. SQL 성능은 그런 데서 결정되는 게 아니라 블록 I/O를 줄일 수 있느냐 없느냐에서 결정된다. 형변환 함수를 생략한다고 연산 횟수가 주는 것도 아니다. 개발자가 형변환 함수를 생략해도 옵티마이저가 자동으로 생성한다.
• Index Range Scan 이외에도 Index Full Scan, Index Uinque Scan, Index Skip Scan, Index Fast Full Scan 등이 있다.
• Index Range Scan은 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 필요한 범위만 스캔한다.
• 선두 컬럼을 가공하지 않은 상태로 조건절에 사용하면 Index Range Scan이 무조건 가능하다. 그러므로 성능은 인덱스 스캔 범위, 테이블  엑세스 횟수를 얼마나 줄일 수 있느냐로 결정된다.
• Index Full Scan은 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.
• Index Full Scan은 대개 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.
• 대용량 테이블이어서 옵티마이저는 Table Full Scan을 고려하지만, 대용량 테이블의 경우 Table Full Scan이 부담스러워서 옵티마이저는 인덱스 활용을 고려하지 않을 수 없다.
• /*+ first_rows */ 힌트를 사용하면 옵티마이저가 모드를 바꿔 인덱스 Full Scan을 고려한다. 이는 인덱스를 활용하므로 Sort 연산 생략 효과를 가져온다.
• Index Unique Scan은 수직적 탐색만으로 데이터를 찾는 스캔 방식으로서 Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동한다.
•  Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성을 관리한다. 따라서 해당 인덱스 키 컬럼을 모두 '=' 조건으로 검색할 때는 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없다.
• Unique 결합 인덱스에 대해 일부 컬럼으로만 검색할 때도 Index Range Scan이 나타난다.
• 옵티마이저는 Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면, Index Full Scan을 사용하기도 한다.
• 오라클은 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식이 Index Skip Scan이다. 이 스캔 방식을 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다. 예를 들어 고객 테이블에서 Distinct Value가 적은 컬럼을 '성별'이라고 할 수 있다.
• Index Skip Scan은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성이 있는' 리프 블록만 골라서 엑세스하는 스캔 방식이다.
• 인덱스 선두 컬럼이 없을 때만 Index Skip Scan이 작동하는 것은 아니다.
• 선두 컬럼에 대한 조건절은 있고, 중간 컬럼에 대한 조건절이 없는 경우에도 Skip Scan을 사용할 수 있다.
• /*+ INDEX_SS(A 일별업종별거래_PK) */
• Index Skip Scan을 사용한다면, 업종유형코드 = '01'인 구간에서 기준일자가 '20080501'보다 크거나 같고 '20080531'보다 작거나 같은 레코드를 '포함할 가능성이 있는 리프 블록만' 골라서 엑세스할 수 있다.
• 선두컬럼이 부등호, BETWEEN, LIKE 같은 범위검색 조건일 때도 Index Skip Scan을 사용할 수 있다.
• Index Range Scan을 사용한다면, 기준일자 BETWEEN 조건을 만족하는 인덱스 구간을 모두 스캔해야 한다. Index Skip Scan을 사용한다면, 기준일자 BETWEEN 조건을 만족하는 인덱스 구간에서도 업종유형코드 = '01'인 레코드를 포함할 가능성이 있는 리프 블록만 골라서 엑세스할 수 있다. 이처럼 Index Range Scan이 불가능하거나 효율적이지 못한 상황에서 Index Skip Scan이 종종 빛을 발한다.
• 인덱스는 기본적으로 최적의 Index Range Scan을 목표로 설계해야 하며, 수행 횟수가 적은 SQL을 위해 인덱스를 추가하는 것이 비효율적일 때 이들 스캔 방식을 차선책으로 활용하는 전략이 바람직하다.
• Index Fast Full Scan이 Index Full Scan 보다 빠른 이유는, 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하기 때문이다. 관련 힌트 : index_ffs, no_index_ffs 
• Index Fast Full Scan은 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 읽어들인다.
• 결과집합이 인덱스 키 순서대로 정렬되지 않는다. 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때만 사용할 수 있다는 점도 기억할 필요가 있다.
• Index Range Scan 또는 Index Full Scan과 달리, 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능한 것도 중요한 특징 중 하나이다. 병렬 쿼리 시에는 Direct Path I/O 방식을 사용하기 때문에 속도가 더 빨라진다.
• Table Full Scan은 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻는다. Index Fast Full Scan도 똑같은 방식을 사용한다.
• Index Fast Full Scan 특징
  • 세그먼트 전체를 스캔
  • 결과집합 순서 보장 안 됨
  • Multiblock I/O
  • 병렬스캔 가능
  • 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능
• Index Full Scan 특징
  • 인덱스 (논리적) 구조를 따라 스캔
  • 결과집합 순서 보장
  • Single Block I/O
  • (파티션 돼 있지 않다면) 병렬스캔 불가
  • 인덱스에 포함되지 않은 컬럼 조회시에도 사용 가능
• Index Range Scan Descending은 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.
• 내림차순 Order by나 MAX 값을 구하고자 하는 경우 등에 사용된다.

SHOW DATABASES;

CREATE DATABASE company;

USE <DATABSENAME>

SELECT DATABASE();

• MySQL 에서는 databse와 schema가 같은 뜻
  
  • 즉 CREATE DATABSE company = CREATE SCHEMA comapny
  • i.g.) PostgreSQL에서는 SCHEMA가 DATABASE의 namespace를 의미
  • 오라클에서는 유저가 소유한 논리적 데이터 구조,즉, 스키마 오브젝트(테이블,인덱스, 뷰 등)의 집합, 유저=SCHEMA라고 할 수 있다. 서로 다른 유저가 같은 스키마 오브젝트들을 공유할 수 있음.

DATA TYPE

• SQL 표준에서 DECIMAL과 NUMERIC의 차이
  • DECIMAL : 5자리 넘어서도 저장
  • NUMERIC : 5자리 넘어서면 저장하지 않음
  • 하지만 MYSQL은 둘 다 엄격하게 그 자릿수만 저장(표준의 NUMERIC 방식)

Constraints(제약 조건)

• PRIMARY KEY : table의 tuple을 식별하기 위해 사용, 하나 이상의 attribute로 구성
  • Primary key는 중복된 값을 가질 수 없으며, NULL도 값으로 가질 수 없다.(PK가 가지는 속성 하나라도 NULL이면 안됨)
• UNIQUE : 중복된 값을 가질 수 없다.
  • 단, NULL은 중복을 허용할 수도 있다(RDBMS 마다 다름)
• NOT NULL
  • 해당 속성은 NULL 값을 가질 수 없다.
• DEAFULT : 기본값을 정의할 때 사용
  • 새로운 TUPLE 저장 시 해당 attribute 의 값이 없다면 이 기본값을 저장
• CHECK 
  • attribute 의 값을 제한할 때 사용
• FOREIGN KEY
  • attribute가 다른 table의 primary key나 unique key를 참조할 때 사용
  • Options
    • CASCADE : 참조값의 삭제/변경을 그대로 반영
    • SET NULL : 참조값이 삭제/변경 시 NULL로 변경
    • RESTRICT : 참조값이 삭제/변경되는 것을 금지
    • NO ACTION : RESTRICT와 유사 (MySQL 미지원)
    • SET DEFAULT : 참조값이 삭제/변경 시 DEFAULT값으로 변경 (MySQL 미지원)
• CONTRAINT 이름 명시
  • 이름을 붙이면 어떤 CONTRAINT를 위반했는 지 쉽게 파악 가능
  • CONSTRAINT를 삭제하고 싶을 때 해당 이름으로 삭제 가능
• ALTER TABLE로 스키마 변경하기
  • ADD, RENAME COLUMN, MODIFY COLUMN, RENAME TO, ADD PRIMARY KEY.. 등 여러 옵션있음
  • 이미 서비스 중인 TABLE의 SCHEMA를 변경하는 것이라면 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요.

CREATE TABLE
    DEPARTMENT (
        ID INT PRIMARY KEY,
        NAME VARCHAR(20) NOT NULL UNIQUE,
        LEADER_ID INT
    );
    
    
    CREATE TABLE EMPLOYEE (
    ID         INT PRIMARY KEY,
    NAME       VARCHAR(30) NOT NULL,
    BIRTH_DATE DATE,
    SEX        CHAR(1) CHECK(SEX IN ('M','F')),
    POSITION   VARCHAR(10),
    SALARY     INT DEFAULT 50000000,
    DEPT_ID    INT,
    
    FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(ID)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CHECK (SALARY >= 50000000)
);


CREATE TABLE PROJECT (
    ID         INT PRIMARY KEY,
    NAME       VARCHAR(20) NOT NULL UNIQUE,
    LEADER_ID  INT,
    START_DATE DATE,
    END_DATE   DATE,
    
    FOREIGN KEY (LEADER_ID) REFERENCES EMPLOYEE(ID)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CHECK (START_DATE < END_DATE)
);

CREATE TABLE WORKS_ON (
    EMPL_ID    INT,
    PROJ_ID    INT,
    PRIMARY KEY(EMPL_ID, PROJ_ID),
    FOREIGN KEY(EMPL_ID) REFERENCES EMPLOYEE(ID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(PROJ_ID) REFERENCES PROJECT(ID)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- DEPARTMENT의 LEADER_ID FOREIGN KEY 추가
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (LEADER_ID)
    REFERENCES EMPLOYEE(ID)
        ON UPDATE CASCADE
        ON DELETE SET NULL;

* 데이터베이스 구조를 정의할 때는 구축하는 서비스의 스펙과 데이터 일관성, 편의성, 확장성 등을 종합적으로 고려하여 DB 스키마를 적절하게 정의해야 한다.

• Demultiplexing : Internet Layer로부터 받은 segment나 datagram에 있는 payload를 적절한 socket으로 전달하는 것
• Multiplexing : Internet Layer로 socket들로부터 데이터를 수집해서 각각 segment나 datagram으로 만든 후 보내는 것
• Internet Layer의 TCP/UDP 정보가 있어 해당 정보를 확인하여 각 Transport Layer 양식에 맞춰 Transport Layer에 보내줌
• Transport가 어떤 프로세스(Socket)으로 데이터를 보내야 하는지 어떻게 판단할까?
  • port로 구분
    • UDP : Destination Port로 보내면 끝
    • TCP
      • syn flag가 1이면 listening socket으로 보내고
      • 이미 연결된 Connection이면 TCP, IP Address, Port 다 같은 곳으로 보냄

*Layer는 

• 데이터베이스 시스템의 데이터 모델 중 가장 많이 사용되는 것이 관계 데이터 모델이다.
• 관계 데이터 모델은 테이블 형태로 데이터 간의 관계를 표현한다.

Relation

• relation은 row와 column으로 구성된 table을 말한다.

• 위 테이블은 고객의 주문 내역 정보를 담고 있는 릴레이션이다.
• 이 릴레이션은 주문번호, 고객코드, 제품코드, 수량, 가격이라는 5개의 집합으로 구성되어 있다.
• 각 집합의 값은 각각 다음과 같이 원소를 가지고 있다.
  • 주문번호 = { ABCDE00001, ABCDE00002 }
  • 고객코드 = { 123456, 654321 }
  • 제품코드 = { 1002, 1004 }
  • 수량 = { 10, 4 }
  • 가격 = { 500000, 40000 }
• 첫 번째 행은 5개의 집합에서 각각 원소 1 개씩 선택하여 만들어졌는데, 이것이 원소들이 맺은 관계(relationship)이다.
• 릴레이션 스키마는 릴레이션의 데이터 구조를 뜻하며 저장된 데이터는 인스턴스라고 표현한다.

• 릴레이션 스키마의 요소
  • 속성(attribute) ; 릴레이션 스키마의 열
  • 도메인(domain) : 속성이 가질 수 있는 값의 집합, 더 나눌 수 없는 값(atomic)
    • 수량, 가격, 단가는 원화 표시이기 때문에 둘다 ‘자연수’라는 도메인을 같는다.
    • 즉, 이 릴레이션에서 수량, 가격, 단가는 ‘자연수’를 도메인으로 하는 attribute이다.
    • 속성은 그 column의 원소들이 나타내는 값의 의미를 나타내고 도메인은 어떤 값들이 그 원소가 될 수 있는 지를 정의한다.
  • 차수(degree) : 속성의 개수
  • 투플(tuple) : 릴레이션의 행
  • 카디날리티(cardinality) : 투플의 수
  • 릴레이션(relation) : 투플의 집합(set of tuples)

• 릴레이션 용어는 일반적으로 잘 쓰이지 않는다. 같은 의미로 통용되는 다른 단어가 주로 쓰인다.
  • table, row, column, header, data, record, field 정도의 용어가 자주 쓰인다.
  • 위 표에 나온 정의대로 쓰인다기 보다는 table의 header, table의 field, table의 record 처럼 파일시스템 용어를 섞어 쓰기도 한다.

릴레이션의 특징

• 속성은 단일 값을 가진다.
• 속성은 서로 다른 이름을 가진다.(하지만 도메인은 같을 수 있다.)
• 한 속성의 값은 모두 같은 도메인 값을 가진다. (= 도메인이라는 집합에 있는 값을 가져야 한다)
• 속성의 순서는 상관없다. 속성의 순서가 달라도 릴레이션 스키마는 같은 것으로 본다. (주문번호, 고객번호) 와 (고객번호, 주문번호)는 같은 스키마이다.
• 릴레이션 내의 중복된 투플은 허용하지 않는다.
  • 유니크한 키 값이 없으면 테이블 내에 같은 데이터를 가진 행이 존재할 수 있다.
  • 하지만 이 경우에도 저장된 데이터들은 사용자가 입력하지 않았더라도 시스템 내부에선 각각 다른 index 번호를 가지기 때문에 사실은 다른 투플이라고 볼 수 있다.
• 투플의 순서가 달라도 같은 릴레이션이다. (즉, 정렬과 상관없다.)

Super Key

• relation에서 tuples를 unique하게 식별할 수 있는 attributes set(즉, 속성 하나여도 되고, 여러 개의 속성의 집합이어도 된다는 것)
• 위 테이블에선 ‘주문번호’가 super key가 될 수 있다.
• 마찬가지로 (주문번호, 고객코드) 또한 super key가 될 수 있다.
• 다만 키를 구성하는 속성이 많으면 그만큼 관계 표현이 복잡해지므로 최소한의 속성 집합에 관심을 갖는다.

Candidate Key

• 후보키(candidate key)는 투플을 유일하게 식별할 수 있는 속성의 최소 집합. 즉 위 테이블에선 ‘주문번호’만 해당된다. (주문번호, 고객코드)는 고객코드가 없어도 되기 때문에 ‘주문번호’가 유일하게 하나의 속성으로 투플을 식별할 수 있는 속성이므로 후보키가 될 수 있다.

Primary Key

• 투플들을 unique하게 식별하기 위해 채택된 candidate key
• 위에서는 ‘주문번호’만 될 수 있다.

Unique Key = Alternate key

• Primary Key가 아닌 candidate key

Foreign Key

• 다른 relation의 PK(primary key)를 참조하는 attributes set
• 위 주문정보 테이블에서 ‘고객코드’는 고객 정보 테이블이 있고 고객코드가 그 테이블의 PK라면 foreign key에 해당한다.

Constraints(제약조건)

• domain constraints : attribute의 value는 해당 attribute의 domain에 속한 value여야 한다.
  • ‘가격’의 도메인은 자연수이기 때문에 ‘10.25’와 같은 값이 올 수 없다.
• key constraints : 서로 다른 tuples은 같은 value의 key를 가질 수 없다.
  • 즉 주문정보 테이블에서 ‘주문번호’가 같을 수 없다.
• NULL value constraint : attribute가 NOT NULL로 명시되어 있다면 NULL을 가질 수 없다.
• Entity integriry contraint : PK는 value에 NULL을 가질 수 없다.
  • NULL이 가능하다면 투플을 식별할 수가 없게 되기 때문이다.
• Referential integrity contraint : FK와 PK와 도메인이 같아야 하고 PK에 없는 values를 FK가 값으로 가질 수 없다.
  • 즉, ‘주문정보’ 테이블의 ‘고객코드’는 ‘고객’ 테이블의 ‘고객코드’와 도메인이 같아야하고 ‘고객’ 테이블에 있는 ‘고객코드‘값만을 참조할 수 있다.

*데이터베이스 기초 시리즈는 유튜버 '쉬운 코드'님의 데이터베이스 강의 및 책 '오라클로 배우는 데이터베이스 개론과 실습'을 공부하며 정리함.

https://www.youtube.com/watch?v=aL0XXc1yGPs&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&ab_channel=%EC%89%AC%EC%9A%B4%EC%BD%94%EB%93%9C

• 데이터베이스 : 관련있는 데이터들을 조직화된 집합으로 정리

• Metadata
  • database를 정의하거나 기술하는 data
  • catalog라고도 부름
  • ex) 데이터 유형, 구조, 제약조건, 보안, 저장, 인덱스, 사용자 등의 데이터
• Database System = Database + DBMS + 연관된 application
  • 줄여서 데이터베이스라고도 부름
  • 문맥에 따라 데이터베이스는 시스템(DB서버)를 뜻하기도 하고 데이터의 집합인 데이터베이스를 뜻하기도 함 
• Data model : DB의 구조를 기술하는데 사용될 수 있는 개념들이 모인 집합
  • DB 구조를 추상화해서 표현할 수 있는 수단 제공
  • DB에서 읽고 쓰기 위한 기본적인 동작들(operations)도 포함한다.
  • DB구조 : 데이터 유형, 데이터 관계(relationship), 제약 사항(constraints) 등

데이터 모델링

• Conceptual data model (개념적 모델)
  • 일반 사용자들이 쉽게 이해할 수 있는 개념들로 이뤄진 모델
  • 추상화 수준이 가장 높음
  • 비즈니스 요구 사항을 추상화하여 기술할 때 사용
  • Entity-Relationship model(ER 모델) 이 대표적
• Logical data model (논리적 모델)
  • 이해하기 어렵지 않으면서 디테일하게 DB를 구조화 할 수 있는 개념들 제공
  • 데이터가 컴퓨터에 저장될 때의 구조와 크게 다르지 않게 DB 구조화를 가능하게 함
  • 특정 DBMS나 storage에 종속되지 않는 수준에서 DB를 구조화할 수 있는 모델
  • Relational data model(가장 많이 사용), object data model, object-relational data model 등
• Physical data models (물리적 모델)
  • 컴퓨터에 데이터가 어떻게 파일 형태로 저장되는지를 기술할 수 있는 수단 제공
  • data format, data orderings, access path 등
  • access path : 데이터 검색을 빠르게 하기 위한 구조체 (ex) Index)

Database Schema

• data model을 바탕으로 database의 구조를 기술한 것
• schema는 database를 설계할 때 정해지며 한 번 정해진 후에는 자주 바뀌지 않는다.

• database state
  • database에 있는 실제 데이터는 꽤 자주 바뀔 수 있다.
  • 특정 시점에 database에 있는 데이터를 database state 혹은 snapshot이라고 한다
  • 혹은 database에 있는 현재 instances의 집합이라고도 한다.
• 3-schema architecture
  • database system을 구축하는 Architecture
  • user application(사용자와 DBMS 사이의 인터페이스 시스템)으로부터 물리적인 database를 분리시키는 목적
  • 세 가지 level이 존재하며 각각의 level마다 schema가 정의되어 있다.
  • 어느 한 레벨의 변화가 상위 레벨에 영향을 주지 않기 위함. 즉, Physical Schema가 변한다고 데이터의 논리적 구조가 크게 변하지 않게 하기 위함(Oracle 에서 MySQL로 DBMS를 변경한다든지, 저장장치(디스크)를 변경한다든지)
  • 실무에선 external과 conceptual schema 사이에 영향을 주지 않도록 정의하는 것이 복잡하고 애매해서 그런지 이 3레벨 구조를 엄격하게 따르지는 않는다.

1. Internal schema (Physical Schema) 
   1. 물리적으로 데이터가 어떻게 저장되는지 표현
   2. physical data model을 통해 표현
   3. data storage, data structure, access path 등등 실체가 있는 내용 기술
2. External schema
   1. external views, user views라고도 불림
   2. 특정 유저들이 필요로 하는 데이터만 표현
   3. 그 외 알려줄 필요가 없는 데이터는 숨김
   4. logical data model을 통해 표현
3. Conceptual schema
   1. 전체 database에 대한 구조를 기술
   2. 물리적인 저장 구조에 관한 내용은 숨김
   3. entities, data types, relationships, user operations, constraints에 집중
   4. logical data model을 통해 기술

• Data Definition Language(DDL)
  • conceptual schema를 정의하기 위해 사용되는 언어
  • internal schema까지 정의할 수 있는 경우도 있음
• Storage Definition Language(SDL)
  • internal schema를 정의하는 용도로 사용되는 언어
  • 요즘은 DBMS에 SDL이 거의 없고 파라미터 등의 설정으로 대체
• View Definition Language(VDL)
  • external schema를 정의하기 위해 사용되는 언어
  • external schema와 conceptual schema를 제대로 나누지 않기 때문에 대부분의 DBMS에서 DDL이 VDL 역할까지 수행
• 오늘날 DDL, SDL, VDL이 따로 존재하지는 않고 통합된 언어로 존재한다. ex) SQL

데이터베이스 시스템 구성

- 사용자는 인터페이스(웹어플리케이션이라면 웹브라우저를 이용해 들어간 웹페이지)를 통해 DBMS에 요청을 보낸다.

- 요청을 받은 DBMS는 요청을 분석하여 적절한 데이터를 디스크에서 꺼내와 인터페이스 시스템(웹어플리케이션 등)으로 데이터를 담은 응답을 보낸다.

- 위 그림에서는 DBMS는 데이터베이스를 관리하는 시스템, 데이터베이스는 실제 저장된 데이터를 의미한다.

Socket

• Application(process)이 시스템의 기능을 마음대로 사용하는 것은 불가능
• 대신 System은 네트워크 통신을 위한 Interface를 제공
• Application(process)이 Socket을 통해 데이터를 주고 받음(=통신)
• 개발자는 Socket Programming을 통해 네트워크 상의 다른 Process와 데이터를 주고 받을 수 있도록 구현한다.
• 대부분의 System(OS)은 Socket 형태로 네트워크 기능을 제공(Linux, Window 등)

• 개발자로서 일하면서 Socket Programming을 한 적이 없는 이유
  • 대체로 Application Layer의 프로토콜을 라이브러리로 가져와 사용하게 된다. 이 라이브러리가 보통 Socket을 사용한 네트워크 통신을 구현하고 있음. 즉, 실제로는 사용하고 있지만 잘 모르고 있는 것임.
  • 따라서 일반 Application 개발자는 Socket Programming을 직접할 일이 적다.

• Socket = < Protocol , IP address, Port number >
  • Port number는 Socket을 구분하기 위한 숫자
  • UDP는 3개(protocol, ip, port)로 식별할 수 있지만 TCP는 불가능
• TCP에서의 동작

- Connection A와 Connection B는 source의 IP, Port와 target의 IP, Port의 조합으로 연결을 식별한다.

- 그래서 서로 다른 프로세스(socket C1, socket C2)가 같은 정보의 소켓을 가지고 동일한 서버 프로세스와 연결을 생성하려고 하면 에러가 발생할 것이다. 

- 표준 문서에서는 TCP Socket이 IP address와 Port만으로 식별할 수 있다고 써있지만 실제 구현에서는 위와 같이 Connection을 구별하기 위해 양 프로세스의 Socket 정보를 모두 활용한다.

*UDP는 Connection 개념이 없이 그냥 데이터 주고 받는다.

TCP, UDP 각각의 사용처

• TCP : Connection(연결) 기반, 신뢰성 보장, 데이터 순서 보장 => 느림
  • 웹 - HTTP, HTTPS 통신
  • 파일전송 - FTP, SFTP
  • 이메일 - SMTP, IMAP, POP3
  • 원격접속 - SSH, Telnet
  • 데이터베이스와 클라이언트간 통신
• UDP : 신뢰성보다 속도와 효율성 중시, 비연결 지향 => 빠름
  • Streaming 서비스 - Youtube, Netflix
  • 온라인 게임
  • VoIP(Voice over Internet Protocol) - Skype, Zoom 
  • DNS(Domain Name Service) <= 도메인 이름을 IP주소로 바꿔주는 건 가볍고 빠르게 처리해야 함.
  • IoT 통신 - MQTT-SN, CoAP <= IoT 장치들은 제한된 리소스를 가지고 있어 빠른 UDP를 주로 사용

- 소켓과 포트가 속하는 위치는?

- process와 port가 1대1 반드시 구조여야 하는 것은 아니다.

- Port name으로 식별한다.(OS마다 다른 방식으로 port에 name을 부여한다)

- TCP/IP의 Internet 계층의 Internet Protocol(IP)를 이용하여 데이터를 전달하게 되는 데 프로세스간 이 때, IP는 unreliable(신뢰할 수 없는)하다는 특성을 지니고 있다. 

- Data loss와 out-of-order 위험이 있으므로 그 위에서 동작하는 Application(Process)간, 즉 transport 계층의 통신에 신뢰성을 부여해야 한다. => TCP 개념 등장

• TCP; Transmission Control Protocol 
  • 프로세스 사이에서 데이터를 안정적으로 주고받도록 함.
  • TCP 에서의 Connection이란 프로세스 간 안정적이고 논리적인 통신 통로를 의미한다.
    • 3-way handshake로 초기화(initialization)을 통해 데이터를 주고 받을 수 있는 환경인 Connection을 연다.
    • 4-way handshake로 Connection을 닫는다.
    • 이런 방식을 Connection-oriented 라고 부른다.

• 그렇다면 Connection을 구축하기 위해 인터넷 상에서 어떻게 port를 식별하는가?
  • port number(port라고도 부름)를 정의 : 16bit(0~65535)
  • port number만으로는 부족 => Internet Address(IP)와 port number로 인터넷 상에서 각 포트를 식별할 수 있음 => 이를 Socket이라고 부름
  • 즉, Socket은 각 포트를 유일하게 식별하기 위해 나온 개념이므로 Socket은 인터넷 상에서 unique해야 함.
  • TCP 표준에서는 한 쌍의 Socket으로 Connection을 유일하게 식별할 수 있다.
    
    • source의 IP address, port number
    • destination의 IP address, port number
    • 이렇게 4가지로 Connection을 유일하게 식별 가능.
  • 하나의 소켓은 여러 개의 연결에 사용될 수 있다. (서버의 경우 소수의 socket으로 여러 클라이언트의 connection을 상대해야 할 것이다.

• UDP : User Datagram Protocol 
  • Connection 없이 데이터를 주고 받음. 즉, Connectionless
  • Internet Protocol을 거의 그대로 사용하여 unreliable함. 그래서 UDP 공식문서에는 Socket이란 단어는 나오지 않지만 UDP에서도 Socket의 개념을 사용함(목적지 소켓이 있어야 하므로)

• TCP, UDP 통틀어서 Socket을 구별하기 위해서는 Protocol, IP address, Port number 3가지로 구성된다.
• 위 TCP 설명에선 Socket을 IP와 Port number의 조합이라고 했지만 UDP를 포함한 개념에서는 Socket을 3가지 조건으로 식별해야 한다.

- process A, B는 프로토콜이 다르므로 서로 다른 socket에 연결되어 있는 것이다. 

- 각각의 Connection은 서로 다른 조합의 Socket 한 쌍을 가지므로 유일하게 식별 가능하다.

- 위 내용들은 모두 표준에 관한 내용이기 때문에 실제 구현의 세계에서는 소켓의 개념이 좀 다르게 쓰인다고 함.

World Wide Web : 여러 정보들을 링크를 통해 효율적으로 접근할 수 있는 인터넷 상에서 동작하는 전 지구적인 정보시스템

• Hypertext 기반 링크 방식 시스템 개발
• Link는 어떤 서버로 가야 데이터를 요청할 수 있는지를 담고 있음 => URL 개념

Web Browser 발명

• CERN Httpd 서버 발명(최초의 Web Server)
• 최초의 Website 발명
• hypertext의 마크업 언어 HTML 개발
• HTML 통신 프로토콜 HTTP 개발 - web의 토대가 되는 프로토콜
• UDI(오늘날의 URL) : 데이터를 요청할 서버의 위치를 특정할 대상
• 1993년 4월 30일 www 관련 software, source code 무료 공개 후 급성장

- 최초의 웹서버, This machine is a server. DO NOT POWER IT DOWN!! 라고 쓰여 있다.