[SQLD] SQLD 자격검정 시험 핵심 요약 (feat. 노랭이책)

 

'SQL 자격검정 실전문제'(일명 노랭이책)을 풀며 핵심 개념을 정리한 글입니다

---

1-1 데이터 모델링의 이해

1-2 데이터 모델과 성능

2-1 SQL 기본

2-2 SQL 활용

2-3 SQL 최적화 기본 원리

---

✨ 1-1 데이터 모델링의 이해

• 데이터모델링이 필요한 이유
  1. 업무정보를 구성하는 기초가 되는 정보들에 대해 일정한 표기법에 의해 표현
  2. 분석된 모델을 가지고 실제 데이터베이스를 생성하여 개발 및 데이터 관리에 사용

• 데이터모델링 유의점
  1. 중복: 여러 장소에 같은 정보 저장 x
  2. 비유연성: 데이터의 정의를 데이터의 사용 프로세스와 분리 → 데이터 혹은 프로세스의 작은 변화가 애플리케이션과 데이터베이스에 중대한 변화를 일으킬 수 있는 가능성 줄임
  3. 비일관성: 데이터와 데이터 간의 상호 연관 관계에 대해 명확히 정의 (연계성 낮춤)

• 데이터모델링 개념
  1. 개념적 데이터 모델: 추상화 수준이 높고 업무중심적이고 포괄적인 수준, 전사적 데이터 모델링이나 EA 수립시 많이 이용
  2. 논리적 데이터 모델: 구축하고자 하는 업무에 대해 key, 속성, 관계 등을 정확하게 표현, 재사용성 높음
  3. 물리적 데이터 모델: 실제 데이터베이스에 이식할 수 있도록 성능, 저장 등 물리적 성격 고려

• 데이터베이스 스키마 구조
  1. 외부스키마: view 단계 여러 개의 사용자 관점으로 구성
  2. 개념스키마: 모든 사용자 관점을 통합한 조직 전체의 DB 기술, DB에 저장되는 데이터와 그들 간의 관계 표현
  3. 내부스키마: DB가 물리적으로 저장된 형식, 데이터가 실제로 저장되는 방법 표현

• 엔티티 특징
  1. 해당 업무에서 필요하고 관리하고자 하는 정보여야 함
  2. 유일한 식별자에 의해 식별이 가능해야 함
  3. 영속적으로 존재하는 두 개 이상의 인스턴스의 집합이어야 함
  4. 업무 프로세스에 의해 이용되어야 함
  5. 반드시 속성이 있어야 함
  6. 다른 엔티티와 최소 한 개 이상의 관계가 있어야 함

• 관계 도출
  1. 두 개의 엔티티 사이에 관심있는 연관규칙이 존재하는가?
  2. 두 개의 엔티티 사이에 정보의 조합이 발생되는가?
  3. 업무기술서, 장표에 관계 연결에 대한 규칙이 서술되어 있는가?
  4. 업무기술서, 장표에 관계 연결을 가능케 하는 동사가 있는가?

• 식별자의 종류
  1. 대표성 여부: 주식별자 vs 보조식별자
  2. 엔티티 내 스스로 생성 여부: 내부식별자 vs 외부식별자
  3. 속성의 수: 단일식별자 vs 복합식별자
  4. 대체 여부: 본질식별자 vs 인조식별자

• 식별자와 비식별자
  1. 식별자
     • 강한 연결, 자식 주식별자의 구성에 포함, 실선 표현
     • 반드시 부모 엔티티에 종속
  2. 비식별자
     • 약한 관계, 자식 일반 속성에 포함, 점선 표현
     • 자식 주식별자를 독립으로 구성

✨ 1-2 데이터 모델과 성능

• 성능 데이터 모델링 수행 절차
  1. 데이터 모델링을 할 때 정규화를 정확하게 수행
  2. 데이터베이스 용량 산정을 수행
  3. 데이터베이스에 발생되는 트랜잭션의 유형 파악
  4. 용량과 트랜잭션 유형에 따라 반정규화 수행
  5. 이력모델의 조정, PK/FK 조정, 슈퍼타입/서브타입 조정 등 수행
  6. 성능관점에서 데이터모델 검증

• 반정규화
  • 정규화된 엔티티, 속성, 관계에 대해 시스템의 성능향상과 개발, 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터모델링 기법
    (데이터를 중복, 통합, 분리하여 성능을 향상시키기 위한 기법)
  • 반정규화 수행
    1. 데이터를 조회할 때 디스크 I/O량이 많아 성능 저하 예상
    2. 경로가 너무 멀어 조인으로 인한 성능 저하 예상
    3. 칼럼을 계산하여 읽을 때 성능이 저하 예상

  💡

  ‘반정규화 판단요소’
  - 다량 데이터 탐색? 인덱스가 아닌 파티션 및 데이터 클러스트링 등의 다양한 물리 저장 기법을 활용해 성능 개선 유도 (다량의 데이터 탐색이 반복적으로 빈번하게 일어난다면 반정규화 고려)
  - 이전 또는 이후 위치의 레코드에 대한 탐색은 window function으로 접근 가능

  • 테이블 반정규화
    1. 테이블 병합: 1:1 관계 병합, 1:M 관계 병합, 슈퍼/서브타입 병합
    2. 테이블 분할: 수직분할, 수평분할
    3. 테이블 추가: 중복, 통계, 이력, 부분 테이블 추가
  • 칼럼 반정규화
    • 중복칼럼, 파생칼럼, 이력테이블 칼럼, PK에 의한 칼럼, 응용 시스템 오동작을 위한 칼럼 추가
  • 다른 방법으로 처리 (성능 향상)
    • 지나치게 많은 조인 → 뷰 사용
    • 대량의 데이터 처리나 부분 처리에 의한 성능 저하 → 클러스터링 적용 or 인덱스 조정
    • 대량의 데이터 → 파티셔닝 기법(PK 성격에 따라 부분적인 테이블로 분리)
    • 응용 애플리케이션에서 로직 구사 방법 변경

• 슈퍼/서브 타입 데이터 모델
  • 개별로 발생되는 트랜잭션 → 개별 테이블로 구성
  • 슈퍼+서브타입의 트랜잭션 → 슈퍼+서브타입 테이블로 구성
  • 전체를 하나로 묶어 트랜잭션 발생 → 하나의 테이블로 구성

• 분산 데이터베이스
  1. 장점
     • 지역 자치성, 점증적 시스템 용량 확장, 신뢰성과 가용성, 효용성과 융통성
     • 빠른 응답 속도와 통신비용 절감, 시스템 규모의 적절한 조절, 각 지역 사용자의 요구 수용 증대
     • 공통코드, 기준정보 등 마스터 데이터를 분산 환경에 복제분산
     • 실시간 업무적 특성, 백업 사이트 구성
  2. 단점
     • 소프트웨어 개발 비용, 오류의 잠재성 증대, 처리 비용의 증대, 설계, 관리의 복잡성과 비용
     • 불규칙한 응답 속도, 통제의 어려움, 데이터 무결성에 대한 위협

  💡

  GSI(Global Single Instance): 통합된 한 개의 인스턴스 (통합 데이터베이스 구조)

✨ 2-1 SQL 기본

• SQL 문장 종류
  1. DML(데이터 조작어): SELECT, INSERT, UPDATE, DELETE
     • 비절차적: 무슨(what) 데이터를 원하는 지만 명세
     • 절차적: 어떻게(how) 데이터를 접근해야 하는지 명세
  2. DDL(데이터 정의어): CREATE, ALTER, DROP, RENAME
  3. DCL(데이터 제어어): GRANT, REVOKE
  4. TCL(트랜잭션 제어어): COMMIT, ROLLBACK, SAVEPOINT
     • 트랜잭션: 데이터베이스의 논리적 연산단위, 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작

• 제약조건 종류
  1. PRIMARY KEY(기본키): UNIQUE & NOT NULL
  2. UNIQUE KEY(고유키): 중복값 없음, null 가능
  3. NOT NULL
  4. CHECK
  5. FOREIGN KEY(외래키): null 가능, 참조 무결성 제약

• 참조 동작
  1. INSERT
     • CASCADE: 부모 삭제 시 자식 같이 삭제
     • SET NULL: 부모 삭제 시 자식 해당 필드 Null
     • SET DEFAULT: 부모 삭제 시 자식 해당 필드 Default 값
     • RESTRICT: 자식 테이블에 PK 값이 없는 경우에만 부모 삭제 허용
  2. DELETE/MODIFY
     • AUTOMATIC: 부모 테이블에 PK가 없는 경우 부모 PK 생성 후 자식 입력
     • SET NULL: 부모 테이블에 PK가 없는 경우 자식 외부키를 null
     • SET DEFAULT: 부모 테이블에 PK가 없는 경우 자식 외부키를 Default 값
     • DEPENDENT: 부모 테이블에 PK가 존재할 때만 child 입력 허용

• 삭제
  1. DROP
     • DDL, Auto Commit (Rollback 불가능)
     • 테이블 정의 자체 완전히 삭제, 테이블이 사용했던. 스토리지 모두 release
  2. TRUNCATE
     • DDL(일부 DML), Auto Commit (Rollback 불가능)
     • 테이블을 최초로 생성된 초기상태로 만듦, 최초 테이블 생성시 할당된 스토리지만 남기고 release
  3. DELETE
     • DML, 사용자 Commit (Commit, Rollback 가능)
     • 데이터만 삭제, 사용했던 스토리지는 release되지 않음

• 데이터베이스 트랜잭션
  1. 원자성(atomicity): 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 2않은 상태로 남아 있어야 함
  2. 일관성(consistency): 트랜잭션이 실행되기 전 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안됨
  3. 고립성(isolation): 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
  4. 지속성(durability): 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장됨

• 트랜잭션 문제점
  1. Dirty Read: 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것
  2. Non-Repeatable Read: 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
  3. Phantom Read: 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령 레코드가 두 번째 쿼리에서 나타나는 현상

• Oracle vs SQL Server
  1. Oracle: DDL 문장 자동 commit 수행
     • null 조회: 컬럼 IS NULL
  2. SQL Server: DDL 문장 자동 commit 수행x
     • null 조회: 컬럼 = ‘’

• 단일행 문자형 함수
  • CHR/CHAR(ASCII 번호): 문자나 숫자로 바꿈
    • CHR(10) = 줄바꿈
  • ASCII(문자): 아스키 코드 번호로 바꿈

• 단일행 NULL 관련 함수
  1. NVL(식1, 식2): 식1의 결과값이 NULL이면 식2 값 출력 (식1과 식2의 데이터 타입이 같아야 함) ISNULL(식1, 식2): SQL Server 함수
  2. NULLIF(식1, 식2): 식1이 식2와 같으면 NULL, 같지 않으면 식1 리턴
  3. COALESCE(식1, 식2 ...): 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식, 모든 표현식이 NULL이면 NULL 리턴

• 집계 함수
  • COUNT(*): NULL 값 포함
  • COUNT(표현식): NULL 값 제외
  • SUM, AVG: NULL값 제외

• SELECT 문장 실행 순서
  • FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

• JOIN 문장
  1. EQUI JOIN: WHERE 절에 JOIN 조건을 넣음
  2. ANSI/ISO SQL 표준 EQUI JOIN: ON 절에 JOIN 조건을 넣음
  • DBMS 옵티마이저는 FROM 절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝을 지어 JOIN 수행

• PK 제약조건 생성

  # PK 제약조건 생성
  ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 PRIMARY KEY (컬럼명);
  
  # 테이블 생성시 PK 제약조건
  CREATE TABLE 테이블명 (
  	~~, CONSTRAINT 제약조건명 PRIMARY KEY (컬럼명)
  );

• 인덱스 생성

  CREATE INDEX 인덱스명 ON 테이블명 (컬럼명);

• 테이블 컬럼 삭제

  ALTER TABLE 테이블명
  DROP COLUMN 컬럼명

• 테이블명 변경

  RENAME 기존테이블명 TO 변경테이블명

• CASE 문장

  # SIMPLE_CASE_EXPRESSION
  CASE WHEN 컬럼명 = '값' THEN '표현식'
  
  # SHARCHED_CASE_EXPRESSION
  CASE 컬럼명 WHEN '값' THEN '표현식'

✨ 2-2 SQL 활용

• 순수 관계 연산자
  • SELECT, PROJECT, JOIN, DIVIDE

• JOIN 조건
  1. ON (A.id = B.id)
  2. USING (id)

• OUTER JOIN

  # LEFT OUTER JOIN
  SELECT X.KEY1, Y.KEY2
  FROM TBL1 X LEFT OUTER JOIN TBL2 Y
  ON (X.KEY1 = Y.KEY2);
  
  # (ANSI표준) LEFT OUTER JOIN
  SELECT X.KEY1, Y.KEY2
  FROM TBL1 X, TBL2 Y
  WHERE X.KEY1 = Y.KEY2(+);
  
  # RIGHT OUTER JOIN
  SELECT X.KEY1, Y.KEY2
  FROM TBL1 X RIGHT OUTER JOIN TBL2 Y
  ON (X.KEY1 = Y.KEY2);
  
  # FULL OUTER JOIN
  SELECT X.KEY1, Y.KEY2
  FROM TBL1 X FULL OUTER JOIN TBL2 Y
  ON (X.KEY1 = Y.KEY2);

• 집합 연산자
  1. UNION: 합집합, 중복된 행 제거
  2. UNION ALL: 합집합, 중복된 행도 출력
  3. INTERSECT: 교집합
  4. EXCEPT/MINUS: 차집합

• 계층 구조
  • START WITH: 계층 구조 전개의 시작 위치 지정 (루트 데이터 지정)
  • CONNECT BY: 현재 읽은 칼럼 지정
    • PRIOR 자식=부모: 부모 → 자식 방향으로 전개 (순방향)
    • PRIOR 부모=자식: 자식 → 부모 방향으로 전개 (역방향)
  • ORDER SIBLINGS BY: 형제 노드 사이에서 정렬 수행

• 서브쿼리 종류
  • 반환되는 데이터 형태
    1. 단일행 서브쿼리: 실행 결과가 항상 1건 이하, 단일행 비교 연산자(=, <, >, ≥, ≤, <>)와 함께 사용
    2. 다중행 서브쿼리: 실행 결과가 여러 건, 다중행 비교 연산자(IN, ALL, ANY, SOME, EXISTS)와 함께 사용(단일행 비교 연산자도 가능)
    3. 다중칼럼 서브쿼리: 실행 결과로 여러 칼럼 반환, 동시 비교, 칼럼 개수와 칼럼 위치 동일 (SQL Server에서 지원x)
  • 사용 형태
    1. SELECT 절: 스칼라 서브쿼리, 단일행 연관 서브쿼리, JOIN으로 변경 가능
    2. FROM 절: 인라인 뷰, 동적 뷰
    3. WHERE 절: 다중행 연관 서브쿼리
    4. HAVING 절
    5. ORDER BY 절
    • 연관 서브쿼리: 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태
    • 비연관 서브쿼리: 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용

• 뷰(VIEW) 장점
  1. 독립성: 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됨
  2. 편리성: 복잡한 질의를 뷰로 생성, 관련 질의를 단순하게 작성, 자주 사용하는 형태의 SQL문
  3. 보안성: 뷰 생성 시 숨기고 싶은 정보를 제외하고 생성

• 집계 그룹 함수
  1. ROLLUP: 계층 구조를 가진 SUB TOTAL을 생성하는 함수, 나열된 컬럼의 순서가 변경되면 수행 결과도 변경
  2. CUBE: 결합 가능한 모든 값에 대해 다차원 집계 생성
  3. GROUPING SETS: 계층 구조 없이 집계 출력, 순서가 바껴도 결과 같음, 평등 관계

• 순위 함수
  1. RANK: 동일 값에 대해 동일 순위 부여, 중간 순위 비워 둠
  2. DENSE_RANK: 동일 값에 대해 동일 순위 부여, 중간 순위 비우지 않음
  3. ROW_NUMBER: 동일 값에 대해 유일한 순위 부여
  • ORDER BY: 전체 데이터 정렬
  • PARTITION BY: 데이터 구분

• 함수
  • LAG: 현재 읽혀진 데이터의 이전 값을 가져옴
  • LEAD: 현재 읽혀진 데이터의 이후 값을 가져옴

• 권한 부여
  1. GRANT: 권한 부여
  2. REVOKE: 권한 회수
  3. ROLE: 다양한 권한을 하나의 그룹으로 묶어놓은 논리적인 권한의 그룹, 사용자와 권한 사이에서 중개 역할 수행

• PL/SQL
  • Block 구조로 되어 있어 기능별로 모듈화 가능
  • 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
  • 절차적 언어(IF, LOOF 등)를 사용하여 절차적 프로그램이 가능하도록 함

• 저장 모듈
  • 사용자 정의 함수: 다른 SQL문을 통해 호출되고, 그 결과를 리턴하는 보조적 역할
    • 프로시저, 사용자 정의 함수: 트랜잭션을 분할할 수 있음, 호출 프로시저 트랜잭션과 별도로 자율 트랜잭션 처리 가능
  • 프로시저: SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합
    • EXECUTE 명령어로 실행, 트랜잭션 제어 가능
  • 트리거: DML문이 수행되었을 때 데이터베이스에서 자동으로 동작하도록 작성된 프로그램
    • 무결성과 일관성을 위해 사용, 로그인 작업에도 사용 가능
    • TCL을 이용해 트랜잭션 제어 불가

✨ 2-3 SQL 최적화 기본 원리

• 옵티마이저
  • 규칙기반 옵티마이저
    • 제일 낮은 우선순위: 전체 테이블 스캔
    • 제일 높은 우선순위: ROWID(행에 대한 고유 주소)를 활용하여 테이블 액세스
    • 적절한 인덱스 존재하면 전체 테이블 스캔보다 인덱스를 사용
    • 통계 정보가 변경되면 실행 계획이 달라질 수 있음
  • 비용기반 옵티마이저(CBO)
    • SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O 자원량 등을 계산해 가장 효율적일 것으로 예상되는 실행계획을 선택
    • 항상 인덱스 스캔이 유리한 것은 아님

• 실행계획
  • SQL 처리를 위한 실행 절차와 방법 표현, 예상 정보
  • 구성요소: 조인 기법, 연산, 액세스 기법, 최적화 정보
  • 읽는 순서: 위에서 아래로, 안에서 밖으로
  • SQL 처리 흐름도: SQL 실행계획을 시각화해서 표현, 성능적인 측면 표현, 실행 시간 알 수 없음
  • 실행계획(실행방법)이 달라진다고 해서 결과가 달라지지 않음
  • JOIN 기법
    1. NL JOIN: OLTP의 목록 처리 업무에 많이 사용 (랜덤 액세스 방식)
       • 조인 컬럼에 적당한 인덱스가 있어 자연조인이 효율적일 때 유용
       • Driving Table의 조인 데이터 양이 큰 영향을 줌
       • 유니크 인덱스를 활용해 수행시간이 적게 걸리는 소량 테이블을 온라인 조회하는 경우 유용
       • 선택도가 낮은(결과 행 수가 적은) 테이블이 선행 테이블로 선택되는 것이 유리함
    2. HASH JOIN: 데이터 집계 업무에 많이 사용
       • 조인 컬럼의 인덱스를 사용하지 않음
       • Sort Merge Join 하기에 두 테이블이 너무 커서 소트 부하가 심할 때 유용
       • 한쪽 테이블이 주 메모리의 가용 메모리에 담길 정도로 충분히 작고 해시 키 속성에 중복 값이 적을 때 효과적
       • 자연조인 시 드라이빙 집합 쪽으로 조인 액세스량이 많아 랜덤 액세스 부하가 심할 때 효과적
       • 행의 수가 작은 테이블을 선행 테이블로 선택하는 것이 유리함
       • EQUI Join 조건에서만 동작(동등 조인)
    3. SORT MERGE JOIN: 데이터 집계 업무에 많이 사용 (스캔 방식)
       • 조인 조건의 인덱스 유무에 영향을 받지 않음
       • 조인 컬럼을 기준으로 데이터를 정렬하여 조인 수행
       • NL Join에서 부담이 되던 넓은 범위의 데이터를 처리할 때 이용
       • 정렬할 데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려운 경우 임시 영역을 사용하기 때문에 성능 저하
       • 대상 테이블이 Join Key 컬럼으로 정렬되어 있을 때 Hash Join보다 우수한 성능

• 인덱스
  • 목적: 조회 성능 최적화 (삽입, 삭제, 갱신의 경우 부하 가중)
    • UPDATE: 인덱스를 구성하는 컬럼 이외의 데이터가 갱신될 때는 인덱스로 인한 부하 발생 x
  • 인덱스 범위 스캔: 결과 건수만큼 반환 (없으면 반환 x)
  1. 기본 인덱스: UNIQUE & NOT NULL 제약조건
  2. 보조 인덱스: UNIQUE가 아니면 중복 데이터 입력 가능, 자주 변경되는 속성은 성능에 안좋은 영향
  • 인덱스가 존재하는 상황에서 데이터를 입력하면 매번 인덱스 정렬 발생 → 대량의 데이터 삽입 시 모든 인덱스 제거, 삽입이 끝나면 인덱스 재생성
  • 랜덤 액세스: 인덱스를 스캔하여 테이블로 데이터를 찾아가는 방식 (부하 큼) → 대량의 데이터를 읽는 경우 인덱스 스캔보다 테이블 전체 스캔이 유리할 수 있음
  • 테이블의 전체 테이블을 읽는 경우 인덱스를 사용하지 않는 FTS 사용

• 인덱스 종류
  1. B 트리 인덱스
     • 브랜치 블록(분기 목적)과 리프 블록(인덱스 구성하는 컬럼 값으로 정렬)으로 구성
     • 관계형 데이터베이스, OLTP 환경에서 가장 많이 사용
     • 테이블 내 데이터 중 10% 이하의 데이터를 검색할 때 유리
     • 일치 및 범위 검색에 적절한 구조
  2. BITMAP 인덱스
     • 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해서 설계됨
     • 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터 저장
  3. CLUSTERED 인덱스
     • 인덱스의 리프 페이지가 곧 데이터 페이지
     • 리프 페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장
     • SQL Server의 클러스터형 인덱스는 ORACLE의 IOT와 매우 유사

 

Uploaded by Notion2Tistory v1.1.0