문과생이 공대에서 살아남기

아래의 내용은 영우글로벌 러닝에서 진행된 4차 산업혁명 관련 교육과정에서 방형욱 강사님이 알려주신 내용을 기반으로 작성된 문서입니다.

 # 정규화 방법

    정규화되지 않은 상태

       ↓ 제1정규화(1 Normalization) : repeating group 제거

    제1정규형(1 Normal Form)

       ↓ 제2정규화(2 Normalization) : 복합UID에 대한 부분 종속 제거

    제2정규형(2 Normal Form)

       ↓ 제3정규화(3 Normalization) : Non-UID에 대한 종속 제거

    제3정규형(3 Normal Form)

테이블 조인을 수행할 때 조인 단계별로 다른 조인기법 사용

1. NL JOIN

프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인 수행

결과를 가능한 빨리 화면에 보여주어야 하는 온라인 프로그램에 적당한 조인 기법

반복문 외부에 있는 테이블을 선행테이블 또는 외부테이블이라 하고 반복문 내부에 있는 테이블을 후행테이블 또는 내부테이블이라 함

FOR 선행 테이블 읽음 → 외부 테이블(Outer Table)

FOR 후행 테이블 읽음 → 내부 테이블(Inner Table)

(선행 테이블과 후행 테이블 조인)

먼저 선행테이블 조건 만족하는 행 추출하고 후행을 읽으면서 조인 수행, 선행테이블 조건만족하는 모든 행의 수만큼 반복 수행

따라서 만족하는 행수가 많으면 그만큼 후행 테이블 조건 작업 반복 수행

랜덤방식으로 데이터 액세스 하기에 처리 범위가 좁은 것이 유리

작업방식

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음 → 만족하지 않으면 해당 데이터는 필터링 됨
2. 선행 테이블의 조인 키 값을 가지고 후행 테이블에서 조인 수행 → 선행테이블의 조인값이 후행에 존재하지 않으면 선행 테이블 데이터 필터링됨
3. 선행 테이블의 조건을 만족하는 모든 행에 대해서 1번 작업 반복 수행 ← 인덱스에서 추출한 레코드 식별자를 이용하여 후행 액세스

2. Sort Merge JOIN

조인 칼럼을 기준으로 데이터를 정렬하여 조인 수행 NL JOIN이 랜덤으로 읽던 것의 단점을 개선함

단, 정렬할 데이터가 많아 메모리를 넘어 임시 영역을 사용하는 경우 성능 저하 발생 그래서 대량은 HASH JOIN 사용

비동등 조인 조건에서 작업 가능한 것이 장점(HASH JOIN은 동등만 가능)

인덱스를 사용하지 않기때문에 인덱스 미존재시 사용가능

정렬 작업이 미리 수행되있는 조인은 추가 정렬 작업이 이루어지지 않음

작업방식

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
2. 선행 테이블에의 조인 키를 기준으로 정렬 작업을 수행 → 조건을 만족하는 모든 행에 대해 반복 수행
3. 후행 테이블에서 주어진 조건을 만족하는 행을 찾음
4. 후행 테이블에의 조인 키를 기준으로 정렬 작업을 수행 → 조건을 만족하는 모든 행에 대해 반복 수행
5. 정렬된 결과를 이용하여 조인을 수행, 성공하면 추출버퍼에 넣음

3. HASH JOIN

HASH 기법 이용하여 조인 수행

서로 동일한 해쉬 값을 갖는 것들 사이에서 실제 값이 같은지 비교하면서 조인 수행

NL조인의 랜덤 액세스 문제점과 Sort Merge JOIN의 정렬작업의 부담을 해결의 위해 등장

인덱스 존재 하지 않아도 사용 가능

동등 조건에서만 사용 가능

결과 행의 수가 적은 테이블을 선행테이블로 선정, 메모리 용량을 넘어서면 임시영역에 저장되기 때문에

그래서 선행 테이블을 Build Input, 후행테이블을 Prove Input으로 불림

작업방식

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
2. 선행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해쉬 테이블 생성 → 조인칼럼과 SELECT 절에서 필요로 하는 칼럼도 함께 저장됨

→ 반복수행

3. 후행 테이블에서 주어진 조건을 만족하는 행을 찾음
4. 후행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해방 버킷을 찾음 → 조인 키를 이용해서 실제 조인될 데이터를 찾음
5. 조인에 성공하면 추출버퍼에 넣음

→ 후행 테이블의 조건을 만족하는 모든 행에 대해서 반복 수행

1. 인덱스 특징과 종류

테이블 기반으로 선택적으로 생성할 수 있는 구조, 생성하지 않아도 여러 개를 생성해도 됨

목적 : 검색성능의 최적화

단점 : DML 작업은 인덱스까지 변경해야 하기 때문에 느려질 수 있는 단점(업데이트 시 부하가 없을수도 있음)

가. 트리기반 인덱스

가장 일반적인 B-트리 인덱스

동등 조건 "="과 BETWEEN, >등의 연산자로 검색하는 범위 검색 모두 가능

인덱스 동일 칼럼으로 구성된 것을 중복 생성은 불가능, 하지만 동일한 칼럼 순서를 변경하면 서로 다른 인덱스 생성 가능

이 외에도 ORACLE은 비트맵인덱스, 리버스키 인덱스, 함수기반 인덱스 존재

• 루트 블록 : 가장 상위 단계
• 브랜치 블록 : 분기를 목적으로 함, 다음단계를 가르키는 포인터를 가지고 있음
• 리프 블록 : 가장 아래 단계, 인덱스를 구성하는 칼럼의 데이터와 행의 위치를 가르키는 레코드식별자(RID)로 구성

 인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬, 양뱡향링크를 가지고 있어서 오름차순, 내림차순 검색 가능

비트맵 인덱스 : 사용될 질의 시스템 구현시 모두 알 수 없는 경우인 DW 및 AD-HOC 질의환경을 위해 설계

하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조

나. SQL SERVER의 클러스터형 인덱스

저장구조에 따라 클러스터형과 비 클러스터형으로 나뉨

1. 인덱스의 리프페이지가 곧 데이터 페이지, 탐색하면 모든 칼럼값을 곧바로 얻을 수 있음
2. 인덱스 키 칼럼 순으로 물리적으로 정렬되어 저장, 한가지 순서로만 정렬, 인덱스는 한개만 생성 가능

2. 전체 테이블 스캔과 인덱스 스캔

가. 전체 테이블

테이블에 존재하는 모든 데이터를 읽어가면서 조건에 맞으면 결과로 추출하고 맞지 않으면 버리는 방식

ORACLE : 고수위 마크(데이터가 쓰여있던 블록 최상의 위치) 아래의 모든 블록을 읽음, 오래걸릴 수 있음

재사용성이 떨어지기에 메로리에서 제거할 수 있도록 관리

왜 전체 테이블을 읽어야하나?

1. SQL 조건이 존재하지 않는 경우, 모든 데이터가 답이 될 수 있기에 무조건 결과로 반환
2. 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우, 함수를 사용하여 인덱스 칼럼을 변형해도 사용 불가
3. 조건에 만족하는 데이터가 많아서 블록을 다 읽어야 한다는 옵티마이저의 판단에 의해
4. 병렬처리 방식으로 처리하는 경우
5. 전체 테이블 스캔 방식의 힌트를 사용한 경우

나. 인덱스 스캔

인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 액세스 기법

검색을 위해 리프블럭을 통해 인덱스 구성 칼럼 값과 레코드 식별자 확인 가능

인덱스가 존재하지 않으면 레코드 식별자 통해 테이블 액세스 해야함

1. 인덱스 유일 스캔 : 유일 인덱스를 사용하여 단 하나의 데이터 추출, 중복불허, 모두 동등조건값 "="에 대해 가능한 인덱스 스캔방식
2. 인덱스 범위 스캔 : 한건 이상의 데이터 추출, "="로 값이 주어지지 않은 경우와 비유일 인덱스를 이용하는 모든 방식
3. 인덱스 역순 범위 스캔 : 양뱡항 링크를 이용해 내림차순으로 데이터 읽는 방식, 최대값 쉽게 찾음
4. 이외에도 인덱스 전체 스캑, 고속전체스캔, 스킵스캔 등이 존재

다. 전체 테이블 스캔과 인덱스 스캔 방식의 비교

인덱스 스캔은 불필요하게 다른 테이블 블록 불필요 → 한번의 입출력 요청에 한 블록씩 데이터 읽음

전체 인덱스 스캔은 한번의 입출력 요청에 여러 블록 테이블 읽음, 모두 읽을꺼라면 유용

대용량 데이터 중 극히 일부의 데이터를 찾을 때는 인덱스 스캔 방식 사용

반대로 대부분의 데이터를 찾을 때는 전체 테이블 스캔 방법 유리