2. 데이터 입출력 구현
논리 데이터 저장소 확인
논리 데이터 모델 검증
논리 데이터 모델링 개념
데이터베이스 설계 프로세스의 기초 설계 단계로 비즈니스 정보의 구조와 규칙을 명확하게 표현할 수 있는 기법. 개념 모델로부터 업무 영역의 업무 데이터 및 규칙을 구체적으로 표현한 모델
논리 데이터 모델링 특성
- 포용성
- 정규화
- 완전성
- 독립성
논리 데이터 모델링 속성
- 개체
- 속성
- 관계
개체-관계 모델
현실 세계에 존재하는 데이터와 그들 간의 관계를 사람이 이해할 수 있는 형태로 명확하게 표현하기 위해서 가장 널리 사용되고 있는 모델. 개체, 속성, 관계로 기술한 모델.
개체-관계 다이어그램 기호
- 개체 : ㅁ
- 관계 : ◇
- 속성 : Ο
- 다중 값 속성 : ◎
- 관계 속성 연결 : -
정규화
정규화의 개념
관계형 데이터베이스의 설계에서 중복을 최소화하여 데이터를 구조화하는 프로세스
이상 현상
데이터의 중복성으로 인해 릴레이션을 조작할 때 발생하는 비합리적 현상
데이터베이스 이상 현상
- 삽입이상
- 삭제이상
- 갱신이상
정규화의 단계
- 1정규형 : 원자값으로 구성
- 2정규형 : 부분 함수 종속 제거
- 3정규형 : 이행 함수 종속 제거
- 보이스-코드 정규형(BCNF) : 결정자 함수이면서 후보키가 아닌 것 제거
- 4정규형 : 다치 종속성 제거
- 5정규형 : 조인 종속성 제거
물리 데이터 저장소 설계
물리 데이터 모델 설계
물리 데이터 모델링 개념
논리 모델을 적용하고자 하는 기술에 맞도록 상세화해가는 과정
물리 데이터 모델링 변환 절차
- 개체를 테이블로 변환
- 속성을 컬럼으로 변환
- UID를 기본키로 변환
- 관계를 외래키로 변환
- 칼럼 유형과 길이 정의
데이터 유형
- CHAR : 최대 2000바이트의 고정길이 문자열 저장
- VACHAR2 : 최대 4000 바이트의 가변 길이 문자열 저장
- NUMBER : 38 자릿수의 숫자 저장 가능
- BLOB, CLOB : 바이너리, 텍스트 데이터 최대 4GB 저장
- 반 정규화 진행
물리 데이터 저장소 구성
DBMS를 선정한 이후의 활동
테이블 제약조건 설계
참조무결성 제약조건
릴레이션과 릴레이션 사이에 대해 참조의 일관성을 보장하기 위한 조건. 참조하는 외래키가 참조되는 릴레이션에 기본키로 존재.
- 제한 : 참조무결성 원칙을 위배하는 연산을 거절하는 옵션
- 연쇄 : 참조되는 릴레이션에서 튜플을 삭제하고, 참조되는 릴레이션에서 이 튜플을 참조하는 튜플들도 함께 삭제하는 옵션
- 널 값 : 참조 되는 릴레이션에서 튜플을 삭제하고, 참조하는 릴레이션에서 해당 튜플을 참조하는 튜플들의 외래 키에 NULL 값을 넣는 옵션
인덱스 설계
인덱스 개념
검색 연산의 최적화를 위해 데이터베이스 내 열에 대한 정보를 구성한 데이터구조
인덱스 적용 기준
- 분포도 10~15% 이내
- 부분처리를 목적으로 하는 경우
- 조회 및 출력 조건으로 사용되는 컬럼
- 인덱스 자동생성 기본키와 Unique 키의 제약조건을 사용할 경우
인덱스 컬럼 선정
- 분포도가 좋은 컬럼은 단독적 생성
- 자주 조합되어 사용되는 컬럼은 결합 인덱스로 생성
- 가능한 한 수정이 빈번하지 않은 컬럼을 선정
인덱스 설계 시 고려 사항
- 지나치게 많은 인덱스는 오버헤드로 작용
- 추가적인 저장 공간 필요
- 인덱스와 테이블의 저장 공간을 적절히 분리
뷰 설계
뷰 개념
뷰는 다른 테이블을 기반으로 만들어진 가상의 테이블을 의미. 뷰는 실제로 데이터를 저장하고 있지 않으며, 논리적으로만 존재. 그러나 데이터베이스 사용자는 실제로 데이터가 존재하는 테이블과 동일하게 뷰를 조작할 수 있음.
뷰 속성
- REPLACE : 뷰가 이미 존재하는 경우 재생성
- FORCE : 본 테이블의 존재 여부에 관게없이 뷰 생성
- NOFORCE : 기본 테이블 존재할 때 뷰 생성
클러스터 설계
클러스터
디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장시키는 방법
적용 기준
- 인덱스의 단점을 해결한 기법. 분포도가 넓을수록 유리
- 액세스 기법이 아니라 액세스 효율 향상을 위한 물리적 저장 방법
- 분포도가 넓은 테이블의 클러스터링은 저장 공간의 절약이 가능
- 대량의 범위를 자주 액세스하는 경우 적용
- 넓은 분포도에 활용
- 여러 개의 테이블이 빈번하게 조인을 일으킬 때
클러스터 설계 시 고려 사항
- 검색 효율은 높여주나 입력, 수정, 삭제 시는 부하가 증가
- UNION, DISTINCT, ORDER BY, GROUP BY가 빈번한 컬럼이면 검토 대상
- 수정이 자주 발생하지 않는 컬럼은 검토 대상
- 처리 범위가 넓어 문제가 발생하는 경우 단일 테이블 클러스터링 고려
- 조인이 많아 문제가 발생되는 경우는 다중 테이블 클러스터링을 고려
파티션 설계
파티션 개념
논리적인 데이터 element들을 다수의 entity로 쪼개는 행위를 뜻하는 일반적인 용어. 즉 큰 table이나 index를 관리하기 쉬운 partition이라는 작은 단위로 물리적으로 분할하는것을 의미. 물리적인 데이터 분할이 있더라도, DB에 접근하는 application의 입장에서는 이를 인식 못함.
파티션의 종류
- 레인지 파티셔닝 : 연속적인 숫자나 날짜를 기준으로 하는 파티셔닝 기법
- 해시 파티셔닝 : 파티션 키의 해시 함수 값에 의한 파티셔닝 기법
- 리스트 파티셔닝 : 특정 파티션에 저장 될 데이터에 대한 명시적 제어가 가능한 파티셔닝 기법
- 컴포지트 파티셔닝 : 범위분할 이후 해시 함수를 적용하여 재분할 하는 파티셔닝 기법
파티션의 장점
- 성능 향상
- 가용성 향성
- 백업 가능
- 경합 감소
데이터 조작 프로시저 작성
데이터 조작 프로세저 개발
프로시저의 개념
SQL를 이용해 생성된 데이터를 조작하는 프로그램. 데이터베이스 내부에 저장되고 일정한 조건이 되면 자동으로 수행.
PL/SQL의 개념
표준 SQL을 기본으로 Oracle에서 개발한 데이터 조작 언어.
PL/SQL 작성 절차
- JDBC를 통해 연결
- 드라이버 로딩
- 데이터베이스 연결 : conn = DriverManager.getConnection(url, "아이디", "비번");
- 쿼리 전달 : pstmt = conn.prepareStatment(sql);, 쿼리를 DB로 전달하기 위해 Statement, PreparedStatement 객체를 생성.
- 결과 수신 : ResultSet rs = pstmet.executeQurey();
- 생성, 수정, 삭제하는 방법을 사전에 정의
- 데이터 조작 프로시저 작성
CREATE OR REPLACE PROCEDURE 프로시저이름()
IS
BEGIN
COMMIT;
END;
EXEC 프로시저이름(); - 데이터 검색 프로시저 작성
데이터 조작 프로시저 테스트
PL/SQL 테스트
DBMS_OUTPUT 패키지 활용
메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스 패키지(END; 전에 DBMS_OUTPUT.PUT_LINE()로 변수를 찍어서 확인 가능).
DBMS_OUTPUT 패키지 실행 방법 : SET SERVEROUTPUT ON
PL/SQL의 처리결과를 화면에 출력하기 위한 SERVEROUTPUT을 ON시키고, 실행하고자 하는 PL/SQL 블록 또는 저장객체 명을 호출. PL/SQL 오류발생 시 'SHOW ERRORS' 명령어를 통해 오류내용 확인.
데이터 조작 프로시저 최적화
데이터 조작 프로시저 성능개선
쿼리 성능 개선(튜닝)의 개념
데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업
SQL 성능 개선 절차
- 문제 있는 SQL 식별 : APM 등을 활용
- 옵티마이저(최적의 처리 경로를 생성해주는 핵심 모듈) 통계 확인
- SQL문 재구성
- 인덱스 재구성
- 실행계획 유지관리
옵티마이저 통계 확인
옵티마이저의 개념
SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진. 옵티마이저가 생성한 SQL 처리경로를 실행계획이라고 부름.
옵티마이저 유형
- RBO(규칙기반 옵티마이저) : 통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의 실행 계획을 선택하는 옵티마이저
- CBO(비용기반 옵티마이저) : 통계 정보로부터 모든 접근 경로를 고려한 질의실행 계획을 선택하는 옵티마이저
SQL 수행 시 옵티마이저 역할
- 쿼리 변환
- 비용 산정
- 계획 생성
힌트 사용
SQL 성능 개선의 핵심 부분으로 옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 함. 옵티마이저가 항상 최선의 실행 계획을 수립할 수 없어 명시적인 힌트를 통해 실행계획을 변경.
주요 옵티마이저 힌트
/*+ RULE/: 규칙 기반 접근 방식을 사용하도록 지정/*+ CHOOSE*/: 오라클 옵티마이저 디폴트 값에 따름/*+ INDEX(테이블명 인덱스명)*/: 지정된 인덱스를 강제적으로 사용/*+ USE_HASH(테이블명)*/: 지정된 테이블들의 조인이 Hash Join 형식으로 일어나도록 유도/*+ USE_MERGE(테이블명)*/: Sort Merge 형식으로/*+ USE_NL(테이블명)*/: Nested Loop 형식으로