28-30강 ) 인덱스, 뷰

Reference : SQL 첫걸음

 

28강) 인덱스 구조

 

1. 인덱스

• 테이블에 붙여진 색인.
• 인덱스의 역할은 검색 속도의 향상
• 검색 시에 쓰이는 키워드와 대응하는 데이터 행의 장소가 저장되어 있다.
• 테이블과는 별개로 독립된 데이터베이스 객체로 작성.
  • 하지만 테이블에 의존하는 객체.

 

 

2. 검색에 사용하는 알고리즘

• 데이터베이스에 쓰이는 대표적인 검색 알고리즘 : 이진트리(Binary tree), 해시

 

풀 테이블 스캔(full table scan)

• 인덱스가 지정되지 않은 테이블을 검색.
• 테이블에 저장된 모든 값을 처음부터 차례로 조사

 

이진 탐색 (binary search)

• 차례로 나열된 집합에 대해 유효한 검색
• 집합을 반으로 나누어 조사
• 대량의 데이터를 검색할 때는 이진 탐색이 빠르다.
• 이진탐색은 데이터가 미리 정렬되어 있어야함.

 

이진 트리 (binary tree)

• 테이블에 인덱스를 작성하면 테이블 데이터와 별개로 인덱스용 데이터가 저장장치에 생성.
• 이 때 이진트리 데이터구조로 작성됨.
• 노드의 왼쪽 가지는 작은 값으로, 오른쪽 가지는 큰 값으로 나뉘어져있음.
• 검색은 이진트리의 가지에서부터 진행.
• 원하는 수치와 비교해서 더 크면 오른쪽 가지를, 작으면 왼쪽 가지를 조사.

 

 

3. 유일성

• 이진 트리에서는 집합 내에 중복하는 값을 가질 수 없다.
• 위의 특성은 키에 대하여 유일성을 가지게 할 경우에만 유용하다.

 

 

 

29강) 인덱스 작성과 삭제

인덱스는 데이터베이스 객체의 하나로 DDL을 사용해서 작성하거나 삭제.

인덱서 자체가 데이터베이스 제품에 의존하는 선택적인 항목으로 취급.

 

 

1. 인덱스 작성

CREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ...)

• CREATE INDEX 명령으로 만든다.
• Oracle이나 D2에서는 인덱스가 스키마 객체가 된다. 따라서 스키마 내에 이름이 중복하지 않도록 지정해 관리.
• SQL Server나 MySQL에서는 테이블 내의 객체. 따라서 테이블 내에 이름이 중복되지 않도록 지정해 관리.
• 행이 대량으로 존재하면 시간도 많이 걸리고 저장 공간도 많이 소비한다.

 

 

2. 인덱스 삭제

DROP INDEX 인덱스명

DROP INDEX 인덱스명 ON 테이블명

• DROP INDEX 명령으로 삭제
• 테이블 내 객체로서 작성하는 경우에는 테이블 이름도 지정한다.
• DROP TABLE로 테이블을 삭제하면 테이블에 작성된 인덱스도 자동으로 삭제된다.

 

 

 

3. EXPLAIN

EXPLAIN SQL 명령

• SQL 명령은 실제로 실행되지는 않는다.
• 어떤 상태로 실행되는지 데이터베이스가 설명해줌.
• EXPLAIN은 표준 SQL에는 존재하지 않는, 데이터베이스 제품 의존형 명령.

 

 

4. 최적화

• 데이터베이스 내부의 최적화에 의해 처리
• SELECT 명령을 실행하기에 앞서 실행계획을 세운다.
• EXPLAIN 명령은 이 실행계획을 확인하는 명령.
• 인덱스의 유무 뿐만 아니라 인덱스를 사용할 것인지 여부에 대해 데이터베이스 내부의 최적화 처리를 통해 판단.

 

 

 

30강) 뷰 작성과 삭제

FROM 구에 기술된 서브쿼리에 이름을 붙이고 데이터베이스 객체화하여 쓰기 쉽게 한 것을 뷰라고 한다.

 

 

1. 뷰

• 데이터베이스 객체 중 하나.
• 데이터베이스 객체로 등록할 수 없는 SELECT 명령을 객체로서 이름을 붙여 관리할 수 있도록 함.
• SELECT 명령 시 뷰를 참조하면 그에 정의된 SELECT 명령의 실행 결과를 테이블처럼 사용할 수 있다.
• 즉, 뷰는 SELECT 명령을 기록하는 데이터베이스 객체

 

SELECT * FROM sample_view_67;

뷰를 작성하는 것으로 복잡한 SELECT 명령을 간략하게 표현할 수 있다.

 

 

가상 테이블

• 테이블처럼 취급할 수 있지만 '실체가 존재하지 않는다'는 의미로 가상테이블이라고도 한다.
• 테이블처럼 취급할 수는 있지만, 데이터를 쓰거나 지울 수 있는 저장 공간을 가지지는 않는다.
• 따라서 SELECT 명령에서만 사용하는 것을 권장.

 

 

2. 뷰 작성과 삭제

• 데이터베이스 객체이기 때문에 DDL로 작성하거나 삭제한다.
• 작성할 때는 CREATE VIEW를, 삭제할 때는 DROP VIEW를 사용.

 

뷰의 작성

CREATE VIEW 뷰명 AS SELECT 명령

CREATE VIEW로 뷰를 작성한 뒤 SELECT 명령의 FROM 구에 지정해 사용

필요에 따라 열을 지정할 수 있다.

 

CREATE VIEW 뷰명 (열명1, 열명2, ...) AS SELECT 명령

뷰의 열 지정을 생략한 경우에는 SELECT 명령의 SELECT 구에서 지정하는 열 정보가 수집되어 자동적으로 뷰의 열로 지정된다.

 

 

뷰 삭제

DROP VIEW 뷰명

DROP VIEW를 사용. 뷰를 삭제하면 더이상 뷰를 참조하여 사용할 수 없다.

 

 

 

 

3. 뷰의 약점

• 데이터베이스 객체로서 저장장치에 저장.
• 하지만 테이블과 달리 대량의저장공간을 필요로 하지 않는다. 대신 CPU 자원을 사용한다.
• 뷰를 참조하면 등록되어 있는 SELECT 명령이 실행, SELECT 명령의 처리는 계산 능력을 필요로 하기 때문에 컴퓨터의 CPU를 사용한다.

 

머티리얼라이즈드 뷰 (Materialized View)

• 뷰의 근원이 되는 테이블에 보관하는 데이터의 양이 많은 경우, 집계처리를 할 때도 뷰가 사용된다면 처리속도가 떨어진다.
• 뷰를 중첩해서 사용하는 경우에도 처리 속도가 떨어진다.
• 머티리얼라이즈드 뷰는 데이터를 일시적으로 저장해 사용하는 것이 아니라 테이블처럼 저장장치에 저장해두고 사용한다.
• 처음 참조되었을 때 데이터를 저장, 다시 참조할 때 이전에 저장해 두었던 데이터를 그대로 사용한다.
• 지정된 테이블의 데이터가 변경된 경우에는 SELECT 명령을 재실행하여 데이터를 다시 저장해야한다.
• 뷰에 지정된 테이블의 데이터가 자주 변경되지 않는 경우라면 머티리얼라이즈드 뷰를 사용하여 뷰의 약점을 어느정도 보완할 수 있다.
• MySQL에서는 사용할 수 없음.

 

 

함수 테이블

• 뷰를 구성하는 SELECT 명령은 단독으로도 실행할 수 있어야 한다.
• 부모 쿼리와 어떤 식으로든 연관된 서브쿼리의 경우 뷰의 SELECT 명령으로 사용할 수 없다.
• 함수테이블은 테이블을 결괏값으로 반환해주는 사용자 정의 함수.
• 인수를 지정하여 인수 값에 따라 WHERE 조건을 붙여 결괏값을 바꿀 수 있다.