SQL Flashcards
데이터베이스 언어
[정의] 데이터 정의,조작,제어를 통한 데이터베이스 구축 및 관리와 사용자와 데이터베이스 간 통신수단
[종류]
1. DDL(정의)
- 개념 : DB의 논리적 구조와, 물리적 구조를 정의하는 데이터 정의 언어
- 유형 : Create, Alter, Drop, TRUNCATE
- 목적 : 데이터 베이스 정의
2. DML(조작)
- 개념 : DB에 들어있는 데이터를 조회,삽입,갱신,삭제하는 데이터 조작 언어
- 유형 : Select, Insert, Delete, Update
- 목적 : 데이터 처리
3. DCL(제어)
- 개념 : DB에 접근하거나 객체에 권한을 주는등 역활을 하는 데이터 제어 언어
- 유형 : Commit, Rollback, Grant, Revoke
- 목적 : 보안, 무결성, 데이터 회복, 병행수행
관계대수
[정의] 관계형 데이터베이스에서 정보 수집을 위해 릴레이션간 질의를 명시하는 절차적 언어
[관계대수 연산자]
1. 일반 집합 연산자 (합교차카)
- 수학적 집합 이론을 릴레이션에 적용
- 1.합집합 : R ∪ S, 2. 교집합 : R ∩ S 3. 차집합 : R - S 4. 카디션 프로덕트 : R × S (Join 개념)
2. 순수 관계 연산자 (셀프조디)
- 관계 데이터베이스에 적용할 수 있도록 정의
- Select, Project, Join, Division
* 관계대수로 표현한 식은 관계해석으로 표현 가능
[비교] 관계대수 vs 관계해석
- 개념 : 집합과 관계 연산에 기초 / 수학적 논리에 기초
- 관점 : How / What
- 종류 : 일반집합,순수관계 / 튜플 관계 , 도메인 형식
- 언어분류 : 절차적 언어(프로그래밍언어와 유사) / 비절차적 언어(자연어와 유사), 프레디킷 해석 기반
- 연산 : 릴레이션을 구하는 연산 집합 제공 / 형식화를 위한 표기법 제공
관계해석
[정의] 프레디킷 해석 기반을 두고 원하는 데이터가 무엇인지 선언하는 비절차적 언어
[관계해석 유형]
1. 튜플 관계해석
- 원하는 릴레이션을 튜플 해석식으로 표현
- 정의 : {t1.A1, t1.A2 … tn.An | F(t1, …tn+1, tn+m}
- 사례 : ‘주문’ 릴레이션 고객번호가 200인 고객이 주문한 주문수량과 주문액수 → {a.주문수량, a.주문액수 | a(주문) ^ a.고객번호=200}
2. 도메인 관계해석
- 원하는 릴레이션을 도메인 해석식으로 표현
- 정의 : {x1,x2…,xn | F(x1,…xn, xn+1, xn+m)}
- 사례 : ‘주문’ 릴레이션 고객번호가 200인 고객이 주문한 주문수량과 주문액수 → {주문수량, 주문액수 | 주문(고객번호,주문수량,주문액수)^고객번호=200}
[연산자]
- 연산자 : OR 연산(V), AND 연산(∧), NOT 연산(ㄱ)
- 정량자 : ∃(존재한다. There exist), ∈(속함, t∈r), ∀(모든 것에 대하여, For All), ∪(합집합)
조인
[정의] 한 데이터베이스 내 여러 테이블을 조합하여 하나의 테이블로써 사용하기 위한 조합 방법
[종류]
- 논리적 조인 방식 : Inner, Outer(Left,Right,Full), Natural, Equal, Cross, Semi
- 물리적 조인 방식 : Nested loops, Sort Merge, Hash, Cartesian, Index
[성능향상 기법]
- 일반적 기법 : Index 전략, Outer Join제거, 연결고리 상태, 조인 순서 조정
- 알고리즘 기법 : Nested, Sort Merge, Hash 조인
논리적 조인방식
[유형]
- Inner Join : 조건일치 행만 검색
- Outer(Left,Right,Full) Join : 단일 혹은 양 집합을 기준으로 조건 일치 행 값 표시, 없으면 Null
- Natural Join : 동일 컬럼명을 가진 두 테이블 모든 컬럼 비교, 동일 속성 비교
→ SELECT * FROM emp1 NATURAL JOIN emp2;
- Cross Join : 모든 조합 결과
- Semi Join : 메인 쿼리 수행전 필요 속성 추출
- Equal Join : 두 테이블의 공통 정보, 단순 or 내부조인
물리적 조인 방식
[유형]
- Nested loops : 선행 테이블(R)의 처리 범위를 하나씩 엑세스후 추출된 겂으로 후행 테이블(S)에 조인
- Sort Merge : 양쪽 테이블 처리범위 각자 정렬후 순차 스캔 연결 조건 기반 머지
- Hash : 해시 함수와 해시 테이블이용
- Cartesian : 전체 조인
- Index : 테이블 엑세스 없이 인덱스로만 처리
중첩반복조인 nested loop
[정의] 다수 테이블에서 하나의 집합 기준 순차적 상대방 Row 결합하여 필요 결과 추출하는 조인 기법
[특징] (속도) 선행 테이블 사이즈 * 후행 테이블 접근 횟수, 후행 테이블 인덱스 존재
[절차]
1. R 테이블을 Full SCAN
2. R 테이블의 코드 이용 S 테이블 인덱스 Unique SCAN
3. 인덱스 결과 이용하여 S 테이블 읽기(랜덤 액세스)
4. R 테이블과 S 테이블 조인 수행
[고려사항]
- 결과 집합 최소화, 부분 범위 처리 유리
정렬합병조인 sort merge
[정의] 두 테이블이 모두 정렬 시 조인 애트리뷰트 순서에 따라 동시 스캔하며 A, B값이 동일 레코드 검색
* 랜덤 엑세스를 줄이기 위한 경우나 연결 고리에 인덱스가 존재하지 않는 경우 수행
[절차]
1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
2. 선행 테이블의 조인 키를 기준으로 정렬 작업을 수행
3. 후행 테이블에서 주어진 조건을 만족하는 행을 찾음
4. 후행 테이블의 조인 키를 기준으로 정렬 작업을 수행
5. 정렬된 결과를 이용하여 조인을 수행하며 조인에 성공하면 추출버퍼에 넣음
해시조인
[정의] 해시함수를 적용한 선행 해시 테이블과 후행테이블에 해시 함수를 적용해 조인하는 기법
[필요성] 대용량 처리의 랜덤과 정렬에 대한 부담을 해결
[제한] CBO 옵티마이저, ‘=’ 비교를 통한 조인에서만 사용 가능
[절차]
1. 작은 릴레이션을 선행테이블로 선택
2. 선행테이블에 해시함수를 적용, 해시 Area에 해시테이블 생성
3. 후행테이블을 읽어 해시함수 적용한 값으로 해시테이블을 탐색하면서 조인
[고려사항] (자원)CPU, PGA 메모리, (이상)키 컬럼 중복, 해시 충돌 가능성
[활용]
- 배치 처리시간 최소화
- Sort Merge Join을 하기에는 두 테이블이 너무 커 Sort 부하가 심한 경우
- Random access 부하 개선
옵티마이저
[리드] 최적의 SQL 수행
[정의] SQL 실행에 대한 처리 비용을 추정 최적의 실행 계획을 수립하는 DBMS 성능 튜닝 엔진
[목표] 처리속도 및 응답속도 최적화
[제약요소] SQL연산자, 옵티마이저 Factor, HINT, 통계정보, DBMS종류/버전
[종류]
- RBO (Rule Based Optimizer) : 사전 정의된 규칙
- CBO (Cost Based Optimizer) : 통계정보 활용
- 옵티마이저 HINT - 옵티마이져의 파싱을 원하는 쪽으로 유도
[수행과정]
1. SQL 파싱 (Syntax, Semantic)
2. Soft 파싱, Hard 파싱 (캐싱 정보) - Hard 파싱인 경우 다음 절차 수행
3. 최적화 과정 : CBO, RBO
4. 실행 계획 : Row Source Generator
5. 실행 : SQL 엔진
[고려사항] 옵티마이저 적용시 고려사항
1. 실행 계획 : 통계, 제약, 옵티마이저 팩터
2. 가용자원 : 힌트, I/O, Buffer Pool
3. SQL 문 : Query 우선 순위, 문법
소프트파싱, 하드파싱
[소프트 파싱] Library Cache활용 기 저장된 SQL 및 실행계획을 재활용하는 SQL 처리 과정
[하드 파싱] Optimizer 활용 최적화 실행 계획을 수립후 수행하는 SQL 처리 과정
* 공유메모리의 Library Cache 에 동일 SQL 존재 여부에 따라 Soft Parsing 과 Hard Parsing 결정
[비교] 소프트 파싱 vs 하드 파싱
- 처리속도 : 속도 빠름(실행계획 수립과정 생략) / 상대적 느림(신규 실행계획 수립)
- 성능요소 : Library Cache의 캐싱 정보 / Optimizer 성능, 통계 자료
- 처리방식 : QEP 과정 생략 / QEP 생성과정 필요
- 성능 향상 방안 : 리터럴 SQL 바인드 변수 사용 / Application 커서 캐싱 사용
* Hard Parsing 과정의 최적 실행계획 수립을 위해 Optimizer 를 활용하며, RBO(Rule-Based Optimizer)와
CBO(Cost-Based Optimizer) 유형 존재. 현재 대부분의 상용 RDBMS 는 CBO 방식을 사용
rbo
[정의] 사전 정의된 인덱스 구조나 비교 연산자의 우선순위를 기준으로 최적의 실행 계획을 수립하는 옵티마이져
* 1순위 Single row by rowed ~ 15순위 Full table scan우선 순위에 따라서 실행 계획을 수립
[장점] 실행 계획 예측 및 제어 가능, Rule 기반의 규칙적인 수행
[단점] 통계요소 현실 요소가 무시되어 판단 오차 증가
cbo
[정의] 처리방법들에 대한 비용을 산정해보고 그중 가장 적은 비용이 들어가는 실행 계획을 수립하는 옵티마이저
[장점] 현실 정보를 감안한 실행계획 수립 가능, [단점] 실행 계획를 미리 예측 및 제어 어려움
[수행과정] Query 변환 → 비용 예측 → 실행 계획 생성
[수행과정 상세]
1. SQL 파싱 (Syntax, Semantic)
2. 최적화 과정(QEP) : Query 변환기, Estimator(비용예측), Plan Generator(실행계획) <– Dictionary
3. 실행 계획 : Row Source Generator
4. 실행 : SQL 엔진
* 일반적인 RDBMS에서 사용하며, Oracle11이후부터 CBO 채택
[CBO 성능한계 요인 및 개선 방안] (한계 요인/ 개선방안)
- 지속적 통계 정보 업데이트 필요 : 자동(스케줄러)/수동(관리자) 통계수집 혼용 정책
- 비현실적 가정 사용(바인드 변수 균등 분포 가정, 동시성 배제) : DW, OLAP 경우 바인드 변수보다 상수 사용
- 자동화 ≠ 최적화(부분 규칙 의존, 기술수준 불완전) : 사용자 Hint 통한 Heuristic 쿼리 변환 방지
cbo 참조 통계정보 유형
선카히비,선택도,카디널리티,히스토그램,비용
1/distinct value 개수,총 로우 수*선택도,값 별 빈도,버킷 수,io call횟수,cpu시간
NewSQL
[리드] RDBMS의 AICD 준수 및 NoSQL의 확장성 결합
[정의] 무결성 지원 위해 RDBMS의 ACID를 보장하면서 NoSQL수준의 성능과 확장성 제공하는 DBMS
[특징] Full ACID 지원, FT(수평확장 가능, 스토리지 서버 독립적 확장)
[핵심기술]
<RDBMS측면>
- Indexing : 데이터베이스 검색 속도 향상
- MVCC : 트랜잭션의 다중 버전 동시성 제어로 트랜잭션 직렬화
- Sharding : 동일 테이블 스키마의 데이터를 다수 데이터베이스에 분산 저장
<NoSQL>
- Schemaless : 테이블과 컬럼 스키마없이 Key-Value 기반 단순 검색
- In-Memory : 고성능, 저지연 서비스, 버퍼 관리 불필요
- DB scaling : 샤드 구성 통한 부분 집합으로 분리 설계
* 유사 DBMS대비 SQL의 복잡성등의 단점 존재, DBMS별 특성 확인 필요
[차별화] NewSQL vs RDBMS vs NoSQL
- (스키마) : 제약 없음 / 연관 테이블 / 스키마 없음
- (특성) : ACID&BASE / ACID /BASE
- (고가용성) : 고가용성 내장 / 별도 구성 필요 / 고가용성 내장
- (적용제품) : Goolge Spanner, VoltDB / Oracle, MySQL / MongoDB,HBase
* 제공기능 및 특성의 차이 존재하므로 적용 환경에 맞는 DBMS 선택이 필요
</NoSQL></RDBMS측면>
