6. 데이터베이스 객체 작성과 삭제

25. 데이터베이스 객체

 

데이터베이스 객체

테이블이나 뷰, 인덱스 등 데이터베이스 내에 정의하는 모든 것을 일컫는 말

이때, 객체는 데이터베이스 내에서 실체와 이름을 가지며 데이터베이스 내에서 객체를 작성할 때는 이름이 겹치지 않도록 해야 함

 

데이터베이스 객체의 명명규칙 및 제약 사항

- 기존 이름이나 예약어와 중복하지 않는다.

- 숫자로 시작할 수 있다.

- 언더스코어 외 기호는 사용 불가하다

- 한글을 사용할 때는 더블쿼트로 둘러싼다.

- 시스템이 허용하는 길이를 초과하지 않는다.

 

스키마

객체의 이름이 같아도 스키마가 서로 다르다면 상관없으며 데이터베이스 객체는 스키마 객체라 불리기도 함

SQL 명령의 DDL을 이용하여 정의하며 어떤 것이 스키마가 되는지는 데이터베이스 제품에 따라 다름

 

- 스키마 설계: 실제로 데이터베이스에 테이블을 작성해서 구축해나가는 작업

- MySQL: CREATE DATABASE 명령으로 작성한 데이터베이스가 스키마

- Oracle: 데이터베이스와 데이터베이스 사용자가 계층적 스키마

 

이때, 테이블 안에서는 열을 정의할 수 있고 스키마 안에서는 테이블 정의 가능

네임스페이스: 각각의 그릇 안에서 중복하지 않도록 이름을 지정할 때, 이름이 충돌하지 않도록 기능하는 그릇

 

26. 테이블 작성·삭제·변경

 

테이블 작성

CREATE TABLE에 이어 작성하고 싶은 테이블 명 지정, 테이블명 뒤에는 괄호로 묶어 열 정의, 열을 정의할 때는 데티블에 필요한 열을 콤마로 구분하여 연속해 지정

 

열 정의

열명 자료형 [DEFAULT 기본값] [NULL | NOT NULL]

 

| 열명: 열에 붙일 이름 지정

 

| 자료형: INTEGER나 VARCHAR 등을 지정, 특히 CHAR나 VARCHAR와 같은 문자열형으로 지정할 때는 최대길이를 괄호로 묶어줘야함

* 기본값을 설정할 때는 DEFAULT로 지정하되 자료형에 맞는 리터럴로 기술하며 생략 가능

mysql> CREATE TABLE t1(no INTEGER NOT NULL, a VARCHAR(30), b DATE) DESC t1;

* NULL값을 허용할 것인지 지정, NULL 명시적으로 지정하거나 생략하는 경우 NULL을 혀용하며 NOT NULL 지정 시 NULL 허용 불가

 

테이블 삭제

테이블 삭제 명령: DROP TABLE [삭제하려는 테이블명];

테이블 정의는 그대로 두고 데이터만 삭제하는 경우 DELETE 명령을 사용하며 WHERE 조건을 지정하지 않는 경우 테이블의 모든 행을 삭제

 

DELETE

테이블 삭제 명령 중 하나지만 행 단위로 내부처리가 발생하므로 삭제 행이 많을 때 처리속도가 느려짐

이때, TRUNCATE TABLE 명령을 사용해 모든 행을 삭제 가능하지만 삭제할 행 지정이나 WHERE 구문 지정 불가

 

테이블 변경

테이블 변경 명령: ALTER TABLE [변경하려는 테이블 열];

 

| 열 추가: ALTER TABLE [테이블 명] ADD [열 정의];

mysql> ALTER TABLE t1 ADD newcol INT;

 

| 열 속성 변경: ALTER TABLE [테이블 명] MODIFY [열 정의];

mysql> ALTER TABLEL t1 MODIFY newcol VARCHAR(10);

 

| 열 이름 변경: ALTER TABLE [테이블 명] CHANGE [기존 열 이름] [신규 열 정의];

mysql> ALTER TABLE t1 CHANGE newcol c VARCHAR(20);

 

| 열 삭제: ALTER TABLE [테이블 명] DROP [명령];

ALTER TABLE t1 DROP c;

 

| 최대 길이 연장: ALTER TABLE [테이블 명] MODIFY [열 이름] [최대 길이 지정];

mysql> ALTER TABLE t1 MODIFY col VARCHAR(30);

 

27. 제약

 

테이블 작성 제약

CREATE TABLE로 테이블 작성 시, 제약을 정의하거나 저장, 변경 가능하며 NOT NULL과 같은 하나의 열에 대해 설정하는 제약은 열 정의 시 지정

mysql> CREATE TABLE t1 (a INTEGER NOT NULL, b INTEGER NOT NULLL UNIQUE, c VARCHAR(30));
mysql> CREATE TABLE t1(no INTEGER NOT NULL, sub INTEGER NOT NULL, name VARCHAR(30), PRIMARY KEY (no, sub));

* 열에 대해 정의하는 제약은 열 제약, 복수의 열에 제약을 설정하는 경우 테이블 제약

mysql> CREATE TABLE t1(no INTEGER NOT NULL, sub INTEGER NOT NULL, name VARCHAR(30), CONSTRAINT pkey_sample PRIMARY KEY (no, sub));

* CONSTRAINT 키워드를 사용해 제약 이름 지정

 

열 제약 추가

ALTER TABLE 명령을 사용하여 기존 테이블에 열 제약 추가 및 열 정의 변경 가능

단, 기존 테이블 변경 시, 제약 위반 데이터를 먼저 검사

mysql> ALTER TABLE t1 MODIFY a VARCHAR(30) NOT NULL:

 

테이블 제약 추가

ALTER TABLE의 ADD 하부명령을 사용해 테이블 제약 추가 가능

단, 기본키는 테이블에 하나만 설정 가능하며 이미 설정되어 있는 테이블에 추가로 기본키 작성은 불가

mysql> ALTER TABLE t1 ADD CONSTRAINT pkey_sample PRIMARY KEY(a);

 

열 제약 삭제

제약을 추가하는 것과 동일하게 열 정의를 변경

mysql> ALTER TABLE t1 MODIFY c VARCHAR(30);

 

테이블 제약 삭제

ALTER TABLE의 DROP 하부명령을 사용해 테이블 제약 삭제 가능하며 제약명을 지정

단, 기본키는 테이블 당 하나만 서정 가능하므로 제약명을 지정하지 않고 삭제 가능

mysql> ALTER TABLE t1 DROP CONSTRAINT pkey_sample;
mysql> ALTER TABLE t1 DROP PRIMARY KEY;

 

UNIQUE 제약 조건 삭제

- MySQL: ALTER TABLE t1 DROP INDEX [row name];

- SQL, Oracle, MS Access: ALTER TABLE t1 DroP CONSTRAINT [제약 명];

 

기본키

테이블의 행 한 개를 특정할 수 있는 검색키이며 기본키로 지정할 열은 NOT NULL 제약이 설정되어 있어야 함

단, 기본키 제약이 설정된 테이블에서 기본키로 검색했을 때, 복수의 행이 일치하는 데이터 작성은 불가하므로 기본키로 설정된 열이 중복하는 데이터 값을 가지는 것은 제약에 위배

 

기본키 제약

열을 기본키로 지정해 유일한 값을 가지도록하는 구조로 행이 유일성을 필요로한다는 의미로 유일성 제약이라고도 함

복수의 열로 기본키 구성이 가능하며, 복수의 열을 기본키로 지정하는 경우 키를 구성하는  모든 열을 사용해 중복 값 유무 검사

 

28. 인덱스

 

인덱스

테이블에 붙여진 색인으로 검색속도의 향상에 기여하는 역할

테이블에 인덱스를 지정하는 경우 효율적으로 검색 가능하며 WHERE로 조건이 지정된 SELECT 명령의 처리 속도 향상

 

데이터베이스 인덱스

데이터베이스의 인덱스에는 검색 시 사용하는 키워드와 대응하는 데이터 행의 장소가 저장되어 있으며 인덱스는 테이블과 별개로 독립된 데이터베이스 객체로 작성됨

단, 인덱스만으로는 의미가 없으며 모든 인덱스는 테이블에 의존하는 객체로 대부분 데이터베이스의 테이블을 삭제하면 인덱스도 삭제

 

검색 알고리즘

- 이진 트리(Binary Tree)

- 해시(Hash)

 

이진 탐색(Binary Search)

이진 탐색에서 검색하기 쉬운 구조

 

| 풀 테이블 스캔(Full Table Scan): 인데스가 지정되지  않은 테이블을 검색할 때 사용하는 방식으로 테이블에 저장된 모든 값을 처음부터차례로 조사

 

| 이진 탐색(Binary Search): 차레로 나열된 집합에 유효한 검색 방법으로 집합을 반으로 나누어 조사하는 검색 방법

 

ex) 차례로 나열된 수치 집합에서 30을 검색하는 예제

1

5

7

11

15

30

32

38

40

45

 

I) 이진 탐색으로 집합의 가운데인 15부터 조사를 시작

II) 찾고자 하는 수 30이 가운데 값 15보다 크므로 오른쪽 부분에서 다시 가운데를 기준으로 조사

III) 오른쪽 가운데 값은 38이므로 30은 38보다 작아 왼쪽 부분의 가운데를 기준으로 조사

IV) 가운데 값이 30이므로 조사 완료

 

만약, 풀 테이블 스캔을 사용하는 경우 총 6번의 비교를 해야하지만 이진 탐색의 경우 3회 조사로 숫자를 찾아내 효율적

실제 데이터베이스는 행이 더 많아 데이터 수에 비례해 비교 횟수도 증가하지만 이진 탐색은 데이터 수가 배가 되어도 비교 횟수는 1회만 증가해 대량 데이터 검색에 효율적

 

이진 트리

검색 속도가 빠르지만 데이터가 미리 정렬되어 있어야 하는 이진 탐색과 다르게 테이블 데이터와 별개로 인덱스용 데이터가 저장장치에 만들어질 때 이진 트리 데이터 구조로 작성

 

| 트리: 노드로 구성되며 각 노드의 왼쪽 가지는 작은 값, 오른쪽 가지는 큰 값으로 나눠져 두 가지로 분기하는 구조

* 검색 시, 원하는 수치와 비교해 더 큰 경우 오른쪽 가지, 작으면 왼쪽 가지를 조사해 가지를 따라 이동

 

유일성

이진 트리에서는 집합 내에 중복하는 값을 가질 수 없으며 이진 트리에서 같은 값을 가지는 노드를 여러개 만드는 것도 불가

즉, 키에 대하여 유일성을 가지게 하는 경우에만 유용하므로 기본키로 이진 트리 인덱스를 작성하는 데이터베이스가 많음

 

29. 인덱스 작성과 삭제

 

인덱스 작성

CREATE INDEX [index name] ON [table name] [row1, row2, ...];

단, 인덱스는 이름을 붙여 관리하는데, 데이터베이스 제품에 따라 객체 취급, 열 취급이 상이

mysql> CREATE INDEX t1 ON t2(no); // t2 테이블의 no열에 t1 인덱스 지정

* Oracle, DB2 등 인덱스는 스키마 객체로 스키마 내 이름이 중복하지 않도록 지정하며 SQL Server, MySQL 인덱스는 테이블 내의 객체

즉, 인덱스의 네임스페이스가 데이터베이스 제품마다 다르며 테이블 내 이름이 중복하지 않도록 지정

 

인덱스 삭제

- 스키마 객체: DROP INDEX [index name];

- 테이블 내 객체: DROP INDEX [indes name] ON [table name];

* 단, DROP에서 다른 객체와 동일하게 인덱스 이름만 지정하지만 테이블 내 객체의 경우 테이블 명도 함께 지정하며 DROP TABLE로 테이블을 삭제할 때 인덱스도  자동 삭제

mysql> CREATE TABLE t1 ON t2(a);
mysql> SELECT * FROM t2 WHERE a='a';

* 인덱스를 작성하는 경우 WHERE 구의 a열에 대한 조건을 빠르게 검색 가능하지만 WHERE 구 조건식에 a열이 사용되지 않으면 SELECT 명령의 t1 인덱스 사용 불가

 

EXPLAIN

실제로 인덱스를 사용하여 검색하는지 확인하기 위한 명령으로 데이터베이스가 어떤 상태로 실행되는지 설명

표준 SQL에는 존재하지 않는 데이터베이스 제품 의존형 명령이지만 대부분 비슷한 명령을 지원

 

possible_keys

사용될 수 인덱스가 표시되는 부분으로 key는 사용된 인덱스 표시

 

최적화

SELECT 명령을 실행할 때 인덱스의 사용 여부를 선택하며 데이터베이스 내부의 최적화에 의해 처리되는 부분

내부 처리에서는 SELECT 명령을 실행하기 앞서 실행 계획을 세우며 인덱스가 지정된ㄴ 열이 WHERE 조건으로 지정된 경우 인덱스를 사용하는 처리 진행

이때, EXPLAIN은 해당 실행계획을 확인

 

실행계획

데이터베이스 내부의 최적화 처리를 통해 인덱스의 유무, 인덱스 사용 여부 등을 판단

이때, 인덱스의 품질을 고려하는 경우 Y or N라는 2가지 값의 열에서는 이진탐색에 의한 효율화를 기대할 수 없으며 데이터의 종류가 적을수록 인덱스의 효율은 저하됨

반대로, 서로 다른 값으로 여러 종류의 데이터가 존재하는 경우 효율은 좋아지기에 인덱스의 품질을 고려해 실행계획 수립

 

30. 뷰 작성과 삭제

 

뷰

SELECT 명령을 기록하는 데이터 베이스 객체

본래 데이터베이스 객체로 등록할 수 없는 SELECT 명령을 객체로 이름 붙여 관리할 수 있도록 도와주는 역할

뷰를 참조하면 그에 정의된 SELECT 명령의 실행 결과를 테이블처럼 사용 가능

 

뷰의 정의

SELECT 명령을 지정한 후 SELECT 명령에서 뷰의 이름을 지정해 참조 가능

mysql> SELECT * FROM (SELECT * FROM t1) sq; // FROM 구문에서 서브쿼리 사용
mysql> SELECT * FROM t_sample; // 서브쿼리를 뷰의 객체로 만드는 경우,t_sample이 뷰의 이름

* 서브쿼리 부분을 뷰로 대체하여 SELECT 명령을 간략하게 표현 가능

 

가상 테이블

뷰와 같이 테이블처럼 취급 가능하지만 실체가 존재하지 않는 테이블을 의미

SELECT 명령으로 이뤄지는 뷰는 테이블처럼 데이터를 쓰거나 지우는 저장공간은 없으며 SELECT 명령 내에서만 사용을 권장

 

뷰의 작성

CREATE VIEW [view name] AS SELECT [명령];

mysql> CREATE VIEW view_sample AS SELECT * FROM t1;
mysql> SELECT * FROM view_sample;

* 뷰는 필요에 따라 열을 지정할 수 있으며  이름 뒤에 괄호로 묶어 열을 나열

 

CREATE VIEW [view name] (row1, row2, ...) AS SELECT [명령];

mysql> CREATE VIEW view_sample2(n,v,v2) AS SELECT no,a,a*2 FROM t2;
mysql> SELECT * FROM viw_sample2 WHERE n=1;

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

반대로, 열을 지정한 경우에는 SELECT 명령의 SELECT 구에  지정한 열보다 우선되며 열  이외 정의, 자료형이나 제약 지정 불가

 

뷰의 삭제

DROP VIEW [view name];

 

뷰의 약점

1) CPU 사용으로 인한 처리속도 저하

 

뷰는 데이터베이스 객체로서 저장장치에 저장되나 데이터베이스에 저장되는 것은 SELECT 명령으로 대량의 저장공간은 불ㄹ필요

다만, 저장공간 대신 CPU를 사용하며 뷰를 참조하면 뷰에 등록된 SELECT 명령 실행

실행 결과는 일시적으로 보존되며 뷰를 참조할 때마다 매 SELECT 명령 실행

 

2) 부모쿼리와 어떤 식으로 연관된 서브쿼리의 경우, 뷰의 SELECT 명령으로 사용 불가

 

뷰를 구성하는 SELECT 명령은 단독 실행이 가능해야하지만 위 경우 단독 실행이 불가

다만, 함수 테이블을 사용하는 경우 테이블을 결과 값으로 반환해줘 약점 회피 가능

특히, 함수에는 인수를 지정할 수 있어 인수의 값에 따라 WHERE 조건을 붙여 결과값을 바꿀수 있으며 상관 서브쿼리로 동작 가능

 

머티리얼라이즈드 뷰

일반적으로 뷰는 데이터를 일시적으로 저장한 후 쿼리 실행 종료 시 함께 삭제되는데, 머티리얼라이즈드 뷰를 사용하는 경우 테이블처럼 저장장치에 저장해 사용 가능

현재 Oracle, DB2에서만 사용 가능한 데이터베이스 객체로, 머티리얼라이즈드 뷰는 처음 참조되었을 때 데이터를 저장하고 이후 다시 참조하는 경우 저장한 데이터를 그대로 사용

* 데이터가 변경된 경우 SELECT 명령을 재실행하며 RDBMS가 해당 유무를 확인해 자동으로 재실행 가능