나도개발자

쿼리 성능 개선

DB에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업. SQL 성능 개선을 통해 데이터 조작 프로시저의 성능 개선 가능.

쿼리 성능 개선 절차

문제 있는 SQL 식별 -> 옵티마이저 통계 확인 -> SQL문 재구성 -> 인덱스 재구성 -> 실행계획 유지관리

1) 문제 있는 SQL 식별 : APM 활용.

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

2) 옵티마이저 통계 확인 : SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DB 핵심 모듈

3) SQL문 재구성 : 범위가 아닌 특정 값 지정으로 범위를 줄여 처리속도를 빠르게 함. 옵티마이저가 비정상적인 실행계획 수립 시, 힌트로서 옵티마이저의 접근 경로 및 조인 순서를 제어함.

4) 인덱스 재구성 : 성능에 중요한 액세스 경로를 고려해 인덱스 생성. 실행 계획 검토하여 기존 인덱스의 열 순서 변경 및 추가.

5) 실행계획 유지관리 : DB 버전 업그레이드, 데이터 전환 등 시스템 환경의 변경 사항 발생 시에도 실행 계획이 유지되고 있는지 관리함.

옵티마이저

SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부 핵심엔진.

SQL 처리경로 = 실행 계획

옵티마이저 유형

SQL 수행과정 내 옵티마이저 역할

- 쿼리 변환(Query Transformer) : SQL을 좀 더 일반적이고 표준화된 형태로 변환.

- 비용 산정(Estimator) : 쿼리 명령어 각 단계의 선택도, 카디널리티, 비용 계산. 실행계획 전체에 대한 총비용을 계산.

- 계획 생성(Plan Generator) : 하나의 쿼리를 수행 시 후보군이 될 만한 실행계획들을 생성해내는 역할.

힌트 사용

SQL 성능 개선의 핵심 부분으로 옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 함.

옵티마이저가 항상 최선의 실행계획을 수립할 순 없기에 명시적 힌트를 통해 실행계획을 변경함.

SQL문 재구성

특정 값 지정 / 별도 SQL 사용 / 힌트 사용 / HAVING 미사용 / 인덱스만 질의 사용

인덱스 재구성

자주쓰는 컬럼 선정 / SORT 명령어 생략 / 분포도 고려 / 변경 적은 컬럼 선정 / 결합 인덱스 사용

DDL (Data Definition Language) _ 데이터 정의어

데이터를 담는 그릇을 정의하는 언어.

테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어들로 특정 구조를 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어들.

DDL 대상

도메인 / 스키마 / 테이블 / 뷰 / 인덱스

** 스키마

- 외부 스키마 : 사용자 및 개발자의 관점. 사용자 뷰. 서브 스키마 

- 개념 스키마 : DB 전체적인 논리적 구조. 전체적인 뷰. 개체간의 관계, 제약 조건, 접근 권한, 무결성, 보안에 대한 정의.

- 내부 스키마 : 물리적 저장장치의 관점. 실제 DB에 저장될 레코드 형식 정의. 저장 데이터 항목의 표현 방법 및 내부 레코드의 물리적 순서 등 표현.

DDL 명령어

- 생성 : CREATE

- 수정 : ALTER

- 삭제 : DROP / TRUNCATE

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

DB에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어.

DML 명령어

- 조회 : SELECT

- 삽입 : INSERT

- 갱신 : UPDATE

- 삭제 : DELETE

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

DBA가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 사용하는 제어용 언어.

DCL 명령어

- 사용 권한 부여 : GRANT (GRANT 권한 ON 테이블 TO 사용자;)

- 사용 권한 취소 : REVOKE (REVOKE 권한 ON 테이블 FROM 사용자;)

도메인

하나의 속성이 가질 수 있는 원자값들의 집합. 속성의 데이터 타입과 크기, 제약조건 등의 정보.

스키마

DB의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조

테이블

데이터를 저장하는 항목인 필드들로 구성된 데이터의 집합체.

뷰

논리 테이블로서 사용자에게 테이블과 동일.

- 논리적 데이터 독립성 제공 : DB에 영향을 주지 않고 애플리케이션이 원하는 형태로 데이터에 접근 가능.

- 데이터 조작 연산 간소화 : 애플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산 간소화.

- 보안 기능(접근제어) 제공

- 뷰 변경 불가

인덱스

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

데이터를 빠르게 찾을 수 있는 수단이자 테이블에 대한 조회 속도를 높여주는 자료 구조.

테이블의 특정 레코드 위치를 알려주는 용도로 사용.

PK컬럼은 자동으로 인덱스 생성.

인덱스가 없을 경우, 테이블 풀 스캔을 함.

인덱스 생성 시, 데이터를 빠르게 찾을 수 있음.

- 순서 인덱스 : 데이터가 정렬된 순서로 생성되는 인덱스. B-Tree 알고리즘 활용.

- 해시 인덱스 : 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스. 데이터 접근 비용이 균일, 튜플 양에 무관.

- 비트맵 인덱스 : 각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스. 수정 변경이 적을 경우 유용.

- 함수기반 인덱스 : 수식이나 함수를 적용하여 만든 인덱스.

- 단일 인덱스 : 하나의 컬럼으로만 구성한 인덱스. 주 사용 컬럼이 하나일 경우 사용.

- 결합 인덱스 : 두 개 이상의 컬럼으로 구성한 인덱스. Where 조건으로 사용하는 빈도가 높은 경우 사용.

- 클러스터드 인덱스 : PK 기분으로 레코드를 묶어 저장하는 인덱스. 저장 데이터의 물리적 순서에 따라 인덱스 생성. 특정 범위 검색 시 유리.

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

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

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

- 인덱스 생략 스캔 : 선두 컬럼이 조건 절에 빠졌어도 인덱스를 활용하는 스캔 방식

조인

- Inner Join

- Outer Join

 > Left Outer Join

 > Right Outer Join

 > Full Outer Join

- Cross Join

- Self Join

집합 연산자

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

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

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

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

트랜잭션

트랜잭션이란?

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

트랜잭션의 특성

- 원자성 (Atomicity) : Commit / Rollback. 트랜잭션을 구성하는 연산 전체가 모두 정상 실행되거나 모두 취소되어야 함.

- 일관성 (Consistency) : 시스템이 가지고있는 고정 요소는 트랜잭션 수행 전과 수행 완료 후의 상태가 같아야 함. 무결성. 동시성 제어.

- 격리성=고립성 (Isolation) : 동시에 실행되는 트랜잭션들은 서로 영향을 미치지 않아야 함.

- 영속성 (Durability) : 성공이 완료된 트랜잭션의 결과는 영속적으로 DB에 저장되어야 함.

트랜잭션의 상태

- 활동 상태 (Active) : 초기 상태, 트랜잭션이 실행 중.

- 부분 완료 상태 (Partially Committed) : 마지막 명령문이 실행된 후.

- 완료 상태 (Committed) : 트랜잭션이 성공적으로 완료된 후.

- 실패 상태 (Failed) : 정상적인 실행이 더 이상 진행될 수 없을 때.

- 철회 상태 (Aborted) : 트랜잭션이 취소되고 DB가 트랜잭션 시작 전 상태로 환원된 상태.

TCL (Transaction Contorl Language)이란?

트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어

- COMMIT : 트랜잭션을 메모리에 영구적으로 저장하는 제어어. 트랜잭션 확정.

- ROLLBACK : 오류 발생 시, 오류 이전의 특정 시점 상태로 되돌려주는 제어어. 트랜잭션 취소.

- CHECKPOINT : ROLLBACK을 위한 시점을 지정하는 제어어.

병행제어란?

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

병행제어 목적

- DB 공유 최대화

- 시스템 활용도 최대화

- DB 일관성 유지

- 응답시간 최소화

병행제어 미보장 시 문제점

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

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

- 모순성 (Inconsistency) : 두 트랜잭션이 동시에 실행되어 DB의 일관성 결여.

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

병행제어 기법

- 로킹 (Locking) : 같은 자원을 액세스하는 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법.로킹 단위가 작아지면 DB 공유도 및 로킹 오버헤드 증가.

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

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

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

DB 고립화 수준(격리성 주요 기법)

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

- Read Uncommitted : 한 트랜잭션에서 연산중인 데이터를 다른 트랜잭션이 읽는 것을 허용하는 수준.

- Read Committed : 연산이 완료될 때까지 연산 대상 데이터에 대한 읽기를 제한하는 수준.

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

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

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

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

- 로그 기반 회복 기법 - 지연 갱신 회복 기법(Deferred Update) : 트랜잭션이 완료되기 전까지 DB에 기록하지 않음.

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

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

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