태그: #정보처리기사
03. 인덱스와 뷰
01. 인덱스(Index)
1) 인덱스의 개념
- 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주는 자료 구조
- 데이터베이스 성능에 많은 영향을 주는 DBMS의 구성 요소로 테이블과 클러스터에 연관되어 독립적인 저장 공간을 보유함
- 인덱스는 기본 테이블의 특정 레코드 위치를 알려주는 용도로 사용함
- 인덱스는 자동으로 생성되지 않기 때문에 데이터베이스 사용자가 지정해 주어야 하지만, 데이터베이스 사용자가 특정 컬럼을 기본키로 지정할 경우에 인덱스는 자동 생성됨
- 기본 테이블에서 번호를 기본키로 하는 경우, 번호에 대한 인덱스는 자동으로 생성되지만, 기본키가 아닌 전화번호나 출생시를 기준으로 하는 인덱스는 자동으로 생성되지 않음
- 기본 테이블의 성명 컬럼에 인덱스가 없는 경우, 테이블의 전체 내용을 검색하게 됨
- 만약, 인덱스가 생성되어 있다면 테이블 일부분을 검색하여 데이터를 빠르게 찾을 수 있음
- 인덱스는 조건절에 '='로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있음
2) 인덱스 대상 테이블 선정 기준
- MULTI BLOCK READ 수에 의해 판단함
- MULTI BLOCK READ가 16일 때 테이블의 크기가 16블록 이상일 경우 인덱스를 설정함
- MULTI_BLOCK_READ_COUNT는 8인 경우(BLOCK SIZE 8K) 테이블의 크기가 64K 이하라면 인덱스가 불필요하지만 참조 관계인 경우 인덱스를 생성함
02. 인덱스 설계
1) 인덱스 설계 과정
- 접근 경로 수집
- 접근 경로는 테이블에서 데이터를 검색하는 방법을 의미함
- 접근 경로 유형은 테이블 스캔과 인덱스 스캔이 있음
- 접근 경로 수집이란 SQL이 최적화되었을 때 인덱스 스캔을 해야 하는 검색 조건들을 수집하는 것
- 경로 수집 방법은 사용하고 있는 애플리케이션 소스 안에 있는 SQL 문장을 수집하거나 DBMS의 트레이스(Trace) 도구를 사용하여 SQL 문장을 수집하는 것
- 분포도 조사를 통한 후보 컬럼 선정
- 분포도 = 데이터 평균 행(Row) 수 / 테이블의 행(Row) 수
- 수집된 접근 경로에 대해 분포도를 조사함
- 일반적으로 분포도가 10 ~ 15% 정도이면 인덱스 컬럼(열) 후보로 사용함
- 분포도가 좋은 열은 단독적으로 생성하여 활용도를 향상시킴
- 자주 조합되어 사용되는 컬럼은 결합 인덱스로 생성하여 활용함
- 결합 인덱스는 구성되는 컬럼 순서 선정에 유의해야 함
- 가능한 한 수정이 빈번하지 않은 컬럼을 선정함
- 접근 경로 결정
- 인덱스 후보 목록을 이용하여 접근 유형에 따라 어떤 인덱스 후보를 사용할 것인가를 결정함
- 만약 누락된 접근 경로가 있다면 분포도 조사를 실시하고 인덱스 후보 목록에 추가 작업을 반복함
- 컬럼 조합 및 순서 결정
- 단일 컬럼의 분포도가 양호하면 단일 컬럼 인덱스로 확정함
- 하나 이상의 컬럼 조합이 필요한 경우에는 추가 고려하여 인덱스 컬럼 순서를 결정함
- 적용 시험
- 설계된 인덱스를 적용하고 접근 경로별로 인덱스가 사용되는지 시험해야 함
- 여러 개의 접근 경로가 존재하는 테이블은 여러 개의 인덱스가 만들어지므로 의도한 실행 계획으로 동작하는지 확인해야 함
2) 인덱스 접근 경로 유형
- 반복 수행되는 접근 경로
- 대표적으로 조인 컬럼을 후보로 선택함
- 만약, 주문 1건당 평균 50개의 주문 내역을 가진다면 주문 테이블과 주문 내역 테이블을 이용하여 주문서를 작성하는 SQL은 조인을 위해 평균 50번의 주문 내역 테이블을 반복 액세스하기 때문
- 분포도가 양호한 컬럼
- 주문번호, 청구번호, 주민등록번호 등은 단일 컬럼 인덱스로도 충분한 수행 속도를 보장받을 수 있는 후보
- 조회 조건에 사용되는 컬럼
- 성명, 상품명, 고객명 등 명칭이나 주문 일자, 판매일, 입고일 등 일자와 같은 컬럼은 조회 조건으로 많이 이용되는 컬럼
- 자주 결합되어 사용되는 컬럼
- 판매일+판매부서, 급여일+급여부서와 같이 조합에 의해 사용되는 컬럼
- 데이터 정렬 순서와 그룹핑 컬럼
- 조건뿐만 아니라 순방향, 역방향 등의 정렬 순서를 고려함
- 인덱스는 구성 컬럼 값들이 정렬되어 있어 인덱스를 이용하면 별도의 ORDER BY, 정렬 작업이 불필요함
- 동일한 원리로 그룹핑 단위로 사용된 컬럼도 조사함
- 일련번호를 부여한 컬럼
- 이력을 관리하기 위해서 일련번호를 부여한 컬럼에 대해서 조사함
- 마지막 일련번호를 찾는 경우가 빈번히 발생하므로 효과적인 액세스를 위해 필요함
- 통계 자료 추출 조건
- 통계 자료는 결과를 추출하기 위해서 넓은 범위의 데이터가 필요함
- 다양한 추출 조건을 사전에 확보하여 인덱스 생성에 반영함
- 조회 조건이나 조인 조건 연산자
- =, BETWEEN, LIKE 등의 비교 연산자를 병행 조사하여 인덱스 결합 순서를 결정할 때 중요한 정보로 사용하도록 함
3) 결합 인덱스의 선두 컬럼 결정 요건
- 항상 사용되는 컬럼
- 컬럼 A, B가 인덱스로 사용될 때 컬럼 A에는 항상 값이 있어야 함
- 인덱스로 사용될 때 선행되는 컬럼 값이 NULL인 경우 인덱스를 이용하지 못함
- 등호 조건으로 사용되는 컬럼
- 부등호나 범위 연산보다는 등호 연산을 사용하는 컬럼을 선두에 배치하는 것이 좋음
- 분포도가 좋은 컬럼
- 분포도가 좋다는 의미는 데이터 값의 중복이 적은 것을 의미함
- 값의 중복이 적은 컬럼을 선두에 사용하는 것이 좋음
- ORDER BY, GROUP BY 순서
- ORDER BY나 GROUP BY 절에 사용되는 컬럼 순으로 인덱스를 생성하는 것이 좋음
4) 인덱스 설계 시 고려사항
- 새로 추가되는 인덱스가 기존 접근 경로에 영향을 미칠 수 있음에 유의함
- 너무 많은 인덱스는 오버헤드로 적용함
- 인덱스는 추가적인 저장 공간이 필요함
- 넓은 범위의 인덱스를 처리할 경우 전체 처리보다 많은 오버헤드가 발생할 수 있음
- 인덱스와 테이블 데이터의 저장 공간은 적절히 분리되어야 함
03. 인덱스 구현
1) 인덱스 생성 명령
CREATE [UNIQUE] INDEX <인덱스명> ON
<테이블명> (<필드명들>);- UNIQUE : 인덱스 컬럼에 중복 값을 허용하지 않는 것으로, CREATE 테이블에서 사용하는 UNIQUE 제약 조건과 동일한 의미로 생략이 가능함
- 인덱스명 : 생성하고자 하는 인덱스 테이블의 이름
- 테이블명 : 인덱스 대상 테이블 이름
- 필드명들 : 복수 컬럼 지정이 가능함
2) 인덱스 삭제 명령
ALTER TABLE <테이블명> DROP INDEX <인덱스명>;- 테이블명 : 인덱스된 테이블 이름
- 인덱스명 : 생성된 인덱스 이름을 의미
- 인덱스 관련 명령어에 대한 SQL 표준이 없기에 제품별 DROP 명령문의 사용법은 약간씩 다름
- 보통 인덱스를 테이블의 종속 구조로 생각하기 때문에 인덱스를 삭제하기 위해 테이블에 변경을 가하는 형식의 명령을 사용함
- ALTER TABLE 명령 뒤에 DROP INDEX 명령이 추가되는 형태로 사용됨
3) 인덱스 변경 명령
- 사용자가 지정한 인덱스는 언제든 다시 지정하여 변경할 수 있음
- 한 번 생성된 인덱스에 대해 변경이 필요한 경우는 드묾
- 인덱스 관련 변경 SQL문은 표준이 없고, 일부 제품은 인덱스에 대한 변경 SQL문이 없음
- 인덱스를 변경하려면 기존 인덱스를 삭제하고 신규 인덱스를 생성하는 방식으로 사용하는 것이 좋음
4) 인덱스 조회 명령
SHOW INDEX FROM <테이블명>;04. 뷰(View)
1) 뷰의 개념
- 하나 이상의 뷰 테이블은 기본 테이블로부터 유도되어 정의되는 가상 테이블이며 뷰 테이블로 정의한 정보는 시스템 카탈로그에 저장됨
- 기본 테이블은 실제 테이블이지만 뷰 테이블은 물리적으로 존재하지 않고 또한 물리적으로 분리될 수도 없음
- 데이터베이스의 응용 프로그램이나 사용자가 접근하는 외부 스키마를 구성하는 데 사용
- 데이터베이스에 논리적 데이터 독립성을 제공할 수 있음
- 같은 데이터를 동시에 여러 사용자에게 상이한 방법으로 제공할 수 있음
- 뷰는 여러 사용자의 상이한 응용이나 요구를 지원할 수 있어서 데이터 관리를 단순하게 함
- 사용자의 관점에서 바라볼 수 있게 만들 수 있기 때문에 사용자의 인식을 복잡성에서 탈피하여 단순화시킬 수 있음
- 제공되지 않은 데이터에 대해서는 자동적으로 보안을 유지할 수 있음
- 뷰 위에 또 다른 뷰를 정의할 수 있음
- 뷰가 정의된 기본 테이블이 제거되면 뷰도 자동적으로 제거됨
- 뷰 테이블을 변경하려면 뷰 테이블을 DROP으로 삭제하고 다시 생성해야 함
2) 뷰의 장단점
- 뷰의 장점
- 논리적 독립성 제공 : 뷰는 논리 테이블이므로 테이블의 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됨
- 사용자 데이터 관리 용이 : 복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용이 가능함
- 데이터 보안 용이 : 중요 보안 데이터를 저장 중인 테이블에는 접근 불허하며, 해당 테이블의 일부 정보만을 볼 수 있는 뷰에는 접근을 허용하는 방식
- 뷰의 단점
- 뷰 자체 인덱스 불가 : 인덱스는 물리적으로 저장된 데이터를 대상으로 하기에 논리적 구성인 뷰 자체는 인덱스를 가지지 못함
- 뷰 정의 변경 불가 : 뷰의 정의를 변경하려면 뷰를 삭제하고 재생성해야 함
- 데이터 변경 제약 존재 : 뷰의 내용에 대한 삽입, 삭제, 변경 제약이 있음
05. 뷰의 설계
1) 뷰의 설계
- 테이블 구조의 단순화를 추구함
- 다양한 관점에서의 데이터를 제시함
- 데이터의 보안 유지를 고려함
- 논리적인 데이터의 독립성 유지를 고려함
2) 뷰 설계 시 고려사항
- 뷰를 통해 최종적으로 테이블에 접근하는 것이므로 사용에 따라 처리 속도에 문제가 발생할 수 있음
- 뷰 내의 SELECT문의 조건은 가능한 한 최적의 접근 경로를 사용할 수 있도록 해야 함
- 뷰를 사용한 SQL의 WHERE절에서는 반드시 양호한 접근 경로가 되도록 해야 함
06. 뷰의 구현
1) 뷰의 생성
- 뷰 생성 명령의 일반 형태
CREATE VIEW <뷰_이름>(컬럼_목록) AS <SELECT문> [옵션];뷰의 옵션
- REPLACE : 뷰가 이미 존재하는 경우 재생성함
- FORCE : 기본 테이블의 존재 여부에 관계없이 뷰를 생성함
- NOFORCE : 기본 테이블이 존재할 때만 뷰를 생성함
- WITH CHECK OPTION : 서브 쿼리 내의 조건을 만족하는 튜플만을 변경함
- WITH READ ONLY : DML 작업이 불가함
원본 테이블 그대로 뷰 테이블 생성
CREATE VIEW 뷰_테이블 AS SELECT * FROM 원본_테이블;- 원본 테이블의 일부분으로 뷰 테이블 생성
CREATE VIEW 뷰_테이블 AS SELECT 컬럼1, 컬럼2, ... FROM 원본_테이블;- 원본 테이블 A와 원본 테이블 B를 조인하여 생성
CREATE VIEW 뷰_테이블 AS SELECT * FROM 원본_테이블A a, 원본_테이블B b
WHERE a.컬럼1=b.컬럼1;2) 뷰의 삭제
DROP VIEW <뷰_이름>;- 뷰 정의 자체를 변경하는 것은 불가능함
- 뷰를 정의하면, 뷰의 물리적 내용은 뷰의 이름과 데이터를 조회하기만 함
- 뷰는 변경하는 수단이 제공되지 않기 때문에 뷰의 삭제와 재생성을 통해 뷰에 대한 정의를 변경하여 사용함
3) 뷰의 조회
SELECT * FROM <뷰_이름>;