VII. SQL 응용

01. 데이터베이스 기본

트랜잭션

인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야하는 특성이자, 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

 

트랜잭션의 특성

(1) Automatically(원자성) : 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질

(2) Consistency(일관성) : 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야하는 성질 

(3) Isolation(고립성) : 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야한다는 성질

(4) Durability(영속성) : 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터 베이스에 저장되어야 하는 성질

 

트랜잭션의 상태변화

(1) Active(활동 상태) : 초기 상태, 트랜잭션이 실행 중일 떄 가지는 상태

(2) Partially Commited(부분 완료 상태) : 마지막 명령문이 실행된 후에 가지는 상태

(3) Commited(완료 상태) : 트랜잭션이 성공적으로 완료된 후 가지는 상태

(4) Failed(실패 상태) : 정상적인 실행이 더 이상 진행될 수 없을 때 가지는 상태

(5) Aborted(철회 상태) : 트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태

 

트랜잭션 제어 언어 = TCL(Transaction Control Language)

(1) 커밋(COMMIT) : 트랜잭션을 메모리에 영구적으로 저장하는 명령어

(2) 롤백(ROLLBACK) : 트랜잭션 내역을 저장 무효화시키는 명령어

(3) 체크포인트(CHECKPOINT) : ROLLBACK을 위한 시점을 지정하는 명령어

 

병행제어 (일관성 주요기법)

다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호 작용을 제어하는 기법

 

병행제어의 목적은 무엇일까?

- 데이터베이스의 공유를 최대화

- 시스템의 활용도를 최대화

- 데이터베이스의 일관성을 유지

- 사용자에 대한 응답시간 최소화

 

병행제어를 보장하지 않는 다면 발생하는 문제점은?

Lost Update(갱신 손실) : 먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어쓸 때 발생하는 오류

Dirty Read(현황 파악 오류) : 트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류

Inconsistency(모순성) : 두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류

Cascading Rollback(연쇄복귀) : 복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류

 

병행제어 기법의 종류

(1) Locking(로킹) : DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법

(2) Optimistic Validation(낙관적 검증) : 트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 DB에 반영하는 기법

(3) Time Stamp Ordering(타임 스탬프 순서) : 트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전에 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법

(4) MVCC; Mutli Version Concurrency Control(다중버전 동시성 제어) : 트랜잭션의 타임 스탬프와 접근하려는 데이터의 타임 스탬프를 비교하여 직렬 가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법

 

데이터 베이스 고립화 수준 (격리성 주요 기법)

다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도

 

고립화 수준의 종류

Read Uncommitted

- 한 트랜잭션에서 아직 커밋되지 않은 연산 중인 데이터를 다른 트랙잭션이 읽는 것을 허용하는 수준

- 연산 중인 데이터에 대한 연산은 불허

Read Committed 

- 한 트랜잭션에서 연산을 수행할 때, 연산이 완료될 때까지 연산 대상 데이터 읽기를 제한하는 수준

- 연산이 완료되어 커밋된 데이터는 다른 트랜잭션이 읽는 것을 허용

Repeatable Read

- 선행 트랜잭션이 특정 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터에 대한 갱신 · 삭제를 제한하는 수준

Serializable Read

- 선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근 제한하는 수준

 

*연산 = 갱신

 

회복 기법 (영속성 주요 기법)

트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업

 

회복 기법 종류

로그 기반 회복 기법 

- Deferred Update(지연 갱신 회복 기법) : 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법

- Immediate Update(즉각 갱신 회복 기법) : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법

Checkpoint Recovery(체크 포인트 회복 기법) : 장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전의 상태로 복원시키는 회복 기법

Shadow Paging Recovery(그림자 페이징 회복 기법) : 데이터베이스 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시 이를 이용해 복구하는 기법

 

DDL ; Data Definition Langaguge (데이터 정의어)

데이터를 정의하는 언어로서 `데이터를 담는 그릇을 정의하는 언어`이다.

 

도메인 : 하나의 속성이 가질 수 있는 원자값들의 집합

스키마(Schema) : 데이터베이스의 구조, 제약조건 등의 정보를 담고있는 기본적인 구조

(1) External Schema(외부 스키마) : 사용자나 개발자의 관점에서 필요로하는 데이터베이스의 논리적 구조 (= 서브스키마), 사용자 뷰

(2) Conceptual Schema(개념 스키마) : 데이터베이스의 전체적인 논리적 구조, 전체적인 뷰

(3) Internal Schema(내부 스키마) : 물리적 저장장치의 관점에서 보는 데이터베이스 구조

 

테이블(Table) 

데이터를 저장하는 항목인 필드(Field)들로 구성된 데이터의 집합체, 릴레이션 혹은 엔터티라고도 부른다.

 

뷰(View)

논리테이블로서 사용관점에서 테이블과 같은 역할을 한다.

 

뷰의 특징 

(1) 논리적 데이터 독립성 제공 : 데이터베이스에 영향으 주지 않고 어플리케이션이 원하는 형태로 데이터에 접근 가능

(2) 데이터 조작 연산 간소화 : 어플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산을 간소화

(3) 보안 기능(접근 제어) 제공 : 특정 필드만을 선택해 뷰를 생성할 경우 어플리케이션은 선택되지 않은 필드의 조회 및 접근 불가

(4) 뷰 변경 불가 : 뷰 정의는 ALTER 문을 이용하여 변경할 수 없음

(CREATE 문을 사용하여 정의, 뷰를 제거할 때에는 DROP 문을 사용)

 

그렇다면 뷰를 왜 사용할까?

단순 질의어를 사용할 수 있기 떄문이다.

테이블의 중요 데이터 일부만을 제공할 수 있는 장단점이 있다.

 

인덱스

검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조

➠ 인덱스는 데이터를 빠르게 찾을 수 있는 수단으로, 테이블에 대한 조회 속도를 높여주는 자료구조

 

인덱스 종류

- Ordered Index (순서 인덱스) : 데이터가 정렬된 순서로 생성되는 인덱스

- Hash Index (해시 인덱스) : 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스

- Bitmap Index (비트맵 인덱스) : 각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스

- Functional Index (함수 기반 인덱스) : 수식이나 함수를 적용하여 만든 인덱스

- Singled Index (단일 인덱스) : 하나의 컬럼으로만 구성한 인덱스

- Concatenated Index (결합 인덱스) : 두 개 이상의 컬럼으로 구성한 인덱스

- Clutered Index (클러스터드 인덱스) : 기본 키(PK) 기준으로 레코드를 묶어서 저장하는 인덱스

인덱스 스캔 방식

- Index Range Scan (인덱스 범위 스캔) : 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식

- Index Full Scan (인덱스 전체 스캔) : 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식

- Index Unique Scan (인덱스 단일 스캔) : 수직적 탐색만으로 데이터를 찾는 스캔 방식 

- Index Skip Scan (인덱스 생략 스캔) : 선두 컬럼이 조건 절에 빠졌어도 인덱스를 활영하는 스캔 방식

 

DML; Data Manipulation Language (데이터 조작어) 

데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어 (CRUD가 존재)

 

유형	동작	설명
INSERT (C)	삽입	테이블 내 칼럼에 저장된 데이터를 조회
SELECT (R)	조회	테이블 내 칼럼에 데이터를 추가
UPDATE (U)	갱신	테이블 내 칼럼에 저장된 데이터를 수정
DELETE (D)	삭제	테이블 내 칼럼에 저장된 데이터를 삭제

 

데이터베이스 조인

Inner Join (내부 조인) : 공통 존재 컬럼의 값이 같은 경우를 추출하는 기법

Outer Join (외부 조인) : 외부 조인의 종류로는 왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인이 있음

- 왼쪽 외부 조인 : 왼쪽 테이블의 모든 데이터와 오른쪽 공통 데이터를 추출하는 기법

- 오른쪽 외부 조인 : 오른쪽 테이블의 모든 데이터와 왼쪽 공통 데이터를 추출하는 기법

- 완전 외부 조인 : 양쪽의 모든 데이터를 추출하는 기법

Cross Join (교차 조인) : 조인 조건이 없는 모든 데이터 조합을 추출하는 기법

Self Join (셀프 조인) : 자기 자신에게 별칭을 지정한 후 다시 조인하는 기법

 

서브쿼리 

SQL 문 안에 포함된 또 다른 SQL 문이다.

 

서브쿼리 유형

FROM 절 서브쿼리 : 서브쿼리가 FROM 절 안에 들어있는 형태

WHERE 절 서브쿼리 : 서브쿼리가 WHERE 절 안에 들어있는 형태

 

데이터베이스 집합 연산자

테이플을 집합 개념으로 보고, 두 테이블 연산에 집한 연산자를 사용하는 방식

 

집합 연산자 유형

UNION : 중복 행이 제거된 쿼리 결과를 반환하는 집합 연산자

UNION ALL : 중복 행이 제거되지 않은 쿼리 결과를 반환하는 집합 연산자

INTERSECT : 두 쿼리 결과에 공통적으로 존재하는 결과를 반환하는 집합 연산자

MINUS : 첫 쿼리에 있고 두 번째 쿼리에는 없는 결과를 반환하는 집합 연산자

 

DCL; Data Control Language (데이터 제어어)

데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 관리자(DBA)가 사용하는 제어용 언어

 

DCL의 유형

GRANT : 사용권한부여

REVOKE : 사용권한취소

 

데이터 분석 함수 

데이터 분석을 위해서는 복수 행 기준의 데이터를 모아서 처리하는 것을 목적으로 하는 다중 행 함수

 

데이터 분석 함수의 종류

- 집계 함수 : 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수

- 그룹 함수 : 소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수

- 윈도 함수 : 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능

 

집계함수의 종류

- COUNT : 복수 행의 줄 수를 반환하는 함수

- SUM : 복수 행의 해당 컬럼 간의 합계를 계산하는 함수

- AVG : 복수 행의 해당 컬럼 간의 평균을 계산하는 함수

- MAX : 복수 행의 해당 컬럼 중 최댓값을 계산하는 함수

- MIN : 복수 행의 해당 컬럼 중 최솟값을 계산하는 함수

- STDDEV : 복수 행의 해당 컬럼 간의 표준편차를 계산하는 함수

- VARIAN : 복수 행의 해당 컬럼 간의 분산을 계산하는 함수

 

그룹 함수

테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수

소계 집단이 아닌 경우 GROUP BY 뒤에 위치한다.

 

ROLLUP 함수

소계(소그룹의 합계) 등 중간 집계 값을 산출하기 위한 그룹 함수 (GROUP BY 뒤에 기재되어지는 함수)

 

CUBE 함수

결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹함수 (연산이 많아 시스템에 부담을 줌)

 

GROUPING SETS 함수

집계 대상 컬럼들에 대한 개별 집계를 구할 수 있으며, ROLLUP과 CUBE와 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있다.

 

윈도함수 (OLAP 함수)

데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 함수

윈도 함수에는 대표적으로 순위 함수가 있다.

 

윈도 함수 종류

- RANK :  특정 항목(컬럼)에 대한 순위를 구하는 함수 (동일 순위 레코드 존재 시, 후순위는 넘어감)

- DENSE_RANK : 레코드의 순위를 계산 (동일 순위 레코드 존재 시, 후순위 넘어가지 않음)

- ROW_NUMBER :  레코드의 순위를 계산 (동일 순위 레코드 존재 시, 무시하고 순서대로 순위를 매김)

 

03 절차형 SQL 활용하기

일반적인 개발 언어처럼 SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

 

절차형 SQL 종류

- 프로시저 (Procedure) : 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

- 사용자 정의 함수 (User-Defined Function) : 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL

- 트리거 (Trigger) : 데이터베이스 시스템에서 생성, 수정, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

 

프로시저

일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

 

프로시저 구성

DECLARE (선언부) : 프로시저의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의하는 부분

BEGIN/END (시작/종료부) : 프로시저의 시작과 종료를 표현하며, BEGIN/END가 쌍을 이룸

CONTROL (제어부) : 기본적으로는 순차적으로 처리

SQL : DML을 주로 이용

EXCEPTION(예외부) : BEGIN~END 절에서 실행되는 SQL 문이 실행될 때 예외 발생 시 예외 처리 방법을 정의하는 처리부

TRANSACTION(실행부) : 프로시저에서 수행된 DML 수행 내역의 DBMS의 적용 또는 취소 여부를 결정하는 처리부

 

프로시저는 IN, OUT, INOUT 모드로 구성

 

사용자 정의 함수

일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL이다.

 

프로시저와 기본적인 개념, 사용법, 문법등이 동일하지만, 종료 시 단일 값을 반환한다는 점에서 차이점을 가지고 있다.

 

사용자 정의 함수 구성 (프로시저 구성과 살짝 차이가 있음)

DECLARE(선언부)

BEGIN/END(시작/종료부)

CONTROL(제어부)

SQL

EXCEPTION(예외부)

RETURN(반환부)

 

사용자 정의 함수의 모드는 IN 하나로 구성이 되어있다.

 

트리거

데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

 

트리거의 목적 

일반적으로 이벤트와 관련된 테이블의 데이터 삽입, 추가, 삭제 작업을 DBMS가 자동적으로 실행시키는데 활용한다.

 

트리거의 종류

- 행 트리거 : 데이터 변화가 생길 때마다 실행

- 문장 트리거 : 트리거에 의해 단 한 번 실행

 

프로시저나 사용자 정의 함수와 기본적인 문법이 같다.

프로시저와 유사하게 반환 값이 없으며, DML을 주된 목적으로 한다는 점에서 유사하다.

트리거는 사용자 정의 함수, 프로시저와 다르게 외부의 IN/OUT이 존재하지 않는다.

 

트리거 구성

DECLARE (선언부)

EVENT (이벤트부)

BEGIN (시작/종료부)

CONTROL (제어부)

SQL
EXCEPTION (예외부)

 

트리거는 실행되는 순서의 전후 관계를 BEFORE, AFTER 키워드를 이용하여 결정

 

04. 데이터 조작 프로시저 최적화

 

쿼리 성능 개선(튜닝)

데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업

 

여기서 알아두어야하는 단어들은 APM, 옵티마이저이다.

APM : 안정적인 시스템 운영을 위해 부하량, 접속자 파악 및 장애 진단 등을 목적으로 하는 성능 모니터링 도구를 의미

옵티마이저 : SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진 (옵티마이저가 생성한 SQL 처리 경로를 `실행 계획`이라고 부름)

 

옵티마이저 유형

RBO (Rule Based Optimizer) : 통계 정보가 없는 상태에서 사전 규칙에 따라 질의 실행 계획을 선택하는 옵티마이저

- 규칙(우선 순위) 기반

- 사용자가 원하는 처리 경로로 유도하기가 쉬움

CBO (Cost Based Optimizer) : 통계 정보로부터 모든 접근 경로를 고려한 질의 실행 계획을 선택하는 옵티마이저

- 비용(수행 시간) 기반

- 옵티마이저의 이해도가 낮아도 성능 보장 가능