정보처리기사 실기 스터디[2022-10-05] - SQL 응용(데이터베이스 기본)

📌 Study

🔸 2022-10-05

[SQL 활용]

1. 데이터베이스 기본

• 트랜잭션 : DB에서 하나의 논리적 기능을 위한 작업 기본 단위

 

• 트랜잭션 특성
  • Atomicity(원자성) : 연산 전체가 모두 실행하거나 모두 취소, 모두 성공 또는 실패되어야 하는 성질, (커밋, 롤백, 회복성)
  • Consistency(일관성) : 시스템 구성요소가 트랜잭션 수행 전과 후 상태가 같아야 하는 성질, (무결성 제약조건, 동시성 제어)
  • Isolation(격리성) : 동시 실행하는 트랜잭션들이 서로 영향을 미치지 않아야 하는 성질
  • Durability(영속성) : 성공 후 완료된 트랜잭션 결과가 DB에 저장되어야 하는 성질

 

• 트랜잭션 상태변화
  • 활동 상태(Active) : 초기 상태, 트랜잭션이 실행 중일 때 가지는 상태
  • 부분 완료 상태(Partially Committed) : 마지막 명령문이 실행된 후에 가지는 상태
  • 완료 상태(Committed) : 트랜잭션이 성공적으로 완료된 후 가지는 상태
  • 실패 상태(Failed) : 정상적인 실행이 더 이상 진행될 수 없을 때 가지는 상태
  • 철회 상태(Aborted) : 트랜잭션이 취소되고, 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태

 

• 트랜잭션 제어어(TCL) : 트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어
  • 커밋 : 트랜잭션 확정, 트랜잭션을 메모리에 영구적으로 저장하는 명령어
  • 롤백 : 트랜잭션 취소, 트랜잭션 내역을 저장 무효화시키는 명령어
  • 체크포인트 : 저장 시기 설정, 롤백을 위한 시점을 지정하는 명령어

 

• 병행제어 : 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, DB 일관성 유지를 위해 상호작용 제어

 

• 병행제어 미 보장 문제점
  • 갱신 손실 : 먼저 실행된 트랙잭션 결과를 나중 실행된 트랜잭션이 덮을 때 오류
  • 현황 파악 오류 : 중간 수행 결과를 다른 트랜잭션이 참조하여 오류
  • 모순성 : 두 트랜잭션이 동시 실행되어 DB 일관성 결여
  • 연쇄 복귀 : 두 트랜잭션이 공유하다가 한ㅈ 쪽이 취소되어 다른 트랜잭션이 부분 취소하지 못하는 오류

 

• 병행제어 기법
  • 로킹 : 다중 트랜잭션 환경에서 DB 일관성, 무결성 유지를 위해 트랜잭션의 순차적 진행을 보장하는 기법, 로킹 단위가 작아지면 DB 공유도 증가, 로킹 단위가 작아지면 로킹 오버헤드 증가
  • 낙관적 검증 : 트랜잭션 검증을 수행하지 않고, 일단 수행 후 종료 시 검증하여 DB 반영
  • 타임 스탬프 순서 : 트랜잭션 실행 전 타임 스탬프를 부여하여 부여 시간에 따라 트랜잭션 작업 수행
  • 다중버전 동시성 제어 : 트랜잭션 타임 스탬프와 데이터 타임 스탬프를 비교하여 직렬 가능성이 보장되는 적절한 버전 선택하여 접근하도록 함

 

• 고립화 수준 : 다른 트랜잭션이 현재 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도
  • Read Uncommitted: 연산 중인 데이터를 트랜잭션이 읽기 허용
  • Read Committed : 연산이 완료될 때까지 읽기 제한
  • Repeatable Read : 선행 트랜잭션이 종료 시 까지 갱신, 삭제를 제한
  • Serializable Read : 선행 트랜잭션이 특정 데이터를 읽을 때, 해당 데이터 전체 접근 제한

 

• 회복 기법 : 트랜잭션의 장애로 인해 DB 손상 이전 상태로 복구

 

• 회복 기법 종류
  • 로그기반 회복 기법
    • 지연 갱신 : 트랜잭션이 완료되기 전까지 DB에 기록되지 않는 기법
    • 즉시 갱신 : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영
  • 체크기반 회복 기법 : 장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전 상태로 복원
  • 그림자 페이징 회복 기법 : 복제본 생성하여 DB 장애 시 복구

 

• DDL(데이터 정의어) : 데이터를 정의하는 언어, 테비블 관계 구조 생성 관련된 명령어

 

• DDL 대상
  • 도메인 : 하나의 속성이 가질 수 있는 원자값 집합
  • 스키마 : DB구조, 제약조건 등을 담는 구조
  • 테이블 : 데이터 저장 공간
  • 뷰 : 하나의 물리 테이블에서 유도되는 가상 테이블
  • 인덱스 : 검색을 빠르게 하기 위한 데이터 구조

 

• 스키마

외부 스키마	사용자, 개발자 관점의 DB 논리적 구조(사용자 뷰), 서브 스키마
개념 스키마	DB 전체적인 논리적 구조(전체적인 뷰), ER, 제약조건, 보안 등 정의
내부 스키마	물리적 저장장치 관점, DB 구조

 

• 테이블 : 데이터 저장 항목인 필드들로 구성된 데이터 집합체
  • 행들이 유일해야 하고, 중복 행 x
  • 행들 간 순서 존재 x
  • 열들 간 순서 존재 x

 

• 테이블 관련 용어
  • 튜플 = 행, (= 레코드)
  • 애트리뷰트 = 열
  • 식별자 = 관계형 DB에서 구분점
  • 도메인 = 하나의 속성이 가질 수 있는 원자값들의 집합
  • 카디널리티 = 튜플 수
  • 차수 = 행 수 (= 디그리)

 

• 뷰 : 논리 테이블, 사용자에게 테이블과 동일

 

• 뷰 특징
  • 논리적 데이터 독립성
  • 데이터 조작 연산 간소화
  • 보안 기능
  • 뷰 변경 불가(ALTER 문 이용하여 변경 불가)
  • 단순 질의어 사용 가능
  • 테이블 중요 데이터만 제공

 

• 인덱스 : 검색을 빠르게 할 수 있는 구조
  • 기본 키(PK) 컬럼은 자동으로 인덱스 생성
  • 연월일이나 이름 기준하는 인덱스는 자동 생성하지 않음
  • 테이블의 컬럼에 인덱스 없는 경우 테이블의 전체 내용 검색

 

• 인덱스 종류
  • 순서 인덱스 : 데이터 정렬 순서로 생성, B-Tree 알고리즘 활용
  • 해시 인덱스 : 해시 함수에 의해 직접 데이터 키 값에 접근, 데이터 접근 비용이 균일
  • 비트맵 인덱스 : 각 컬럼에 적은 개수 값이 저장되는 경우 선택, 수정이 적을 경우 유용(생년월일, 상품번호 등)
  • 함수기반 인덱스 : 수식이나 함수 적용
  • 단일 인덱스 : 하나의 컬럼만 구성(주 사용 컬럼이 하나일 경우)
  • 결합 인덱스 : 두 개 이상의 컬럼 구성(WHERE 조건으로 사용하는 빈도 높은 경우)
  • 클러스터드 인덱스 : 기본 키(PK) 기준으로 레코드 묶어서 저장(특정 범위 검색 시 유리)

 

• 인덱스 SQL 문
  • 인덱스 생성 : CREATE[UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명);
  • 인덱스 삭제 : DROP INDEX 인덱스명;
  • 인덱스 변경 : ALTER[UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명);

 

• 인덱스 스캔 방식
  • 범위 스캔(Range) : 필요한 범위만 수직적 탐색
  • 전체 스캔(Full) : 처음부터 끝까지 수평적 탐색
  • 단일 스캔(Unique) : 수직적 탐색만으로 데이터 스캔
  • 생략 스캔(Skip) : 선두 컬럼이 조건절에 빠졌어도 인덱스 활용