25강. 데이터베이스 객체
1. 데이터베이스 객체
데이터베이스 객체 : 테이블이나 뷰, 인덱스 등 데이터베이스 내에 정의하는 모든 것을 일컫는 말
데이터베이스 내에 실체를 가지는 어떤 것
객체의 종류에 따라 데이터베이스에 저장되는 내용도 달라짐
객체의 ex) 테이블 → 행과 열이 저장됨
SELECT나 INSERT 같은 SQL 명령은 실체를 가지지 않으므로 객체X
객체는 이름을 가짐 (이름은 겹치지 않도록 함)
열이나 별명은 객체가 아님
- 이름을 붙일 때의 제약 사항(명명규칙)
- 기존 이름이나 예약어와 중복X
- 숫자로 시작할 수 없음
- 언더스코어(_) 이외의 기호 사용X
- 한글을 사용할 때는 더블쿼트(MySQL에서는 백쿼트) 사용
- 시스템이 허용하는 길이 초과X
어떤 데이터가 저장되어 있는지 파악할 수 있도록 의미있는 이름으로 지정
이름은 객체의 종류와는 무관
2. 스키마
데이터베이스 객체는 스키마라는 그릇 안에 만들어짐
객체의 이름이 같아도 스키마가 서로 다르면 상관X
→ '스키마 객체' 라 부르기도 함
데이터베이스에 테이블을 작성해서 구축해나가는 작업을 '스키마 설계' 라고 부름
테이블 안에 열 정의, 스키마 안에 테이블 정의
각각의 안에서 이름 충돌하지 않도록 기능 → '네임스페이스(namespace)'
26강. 테이블 작성•삭제•변경
DML : 데이터를 조작하는 명령 ex) SELECT, INSERT, DELETE, UPDATE
DDL : 데이터를 정의하는 명령
1. 테이블 작성
CREATE로 작성, DROP으로 삭제, ALTER로 변경
각각의 뒤에 객체 지정
CREATE TABLE 테이블명 (
열 정의1,
열 정의2,
...
)
| <열 정의> 열명 자료형 [DEFAULT 기본값] [NULL|NOT NULL] |
2. 테이블 삭제
DROP TABLE 명령을 사용하여 필요없는 테이블을 삭제
DROP TABLE 테이블명
- 데이터 행 삭제
데이터만 삭제할 때는 DELETE 명령 사용
DELETE 명령은 삭제할 행이 많으면 처리속도가 늦어짐
→ TRUNCATE TABLE 명령 (DDL) 사용
TRUNCATE TABLE 명령을 통해 모든 행을 삭제해야 할 때 빠른 속도로 삭제할 수 있음
TRUNCATE TABLE 테이블명3. 테이블 변경
ALTER TABLE 명령을 통해 테이블에 데이터가 이미 존재하는 경우 저장되어 있는 데이터는 그대로 남긴 채 구성만 변경할 수 있음
ALTER TABLE 테이블명 변경명령
< ALTER TABLE >
- 열 추가, 삭제, 변경
- 제약 추가, 삭제
- 열 추가
ALTER TABLE로 열을 추가할 때는 ADD 하부명령을 통해 실행
ALTER TABLE 테이블명 ADD 열 정의NOT NULL 제약이 걸린 열을 추가할 때는 기본값을 지정해야 함
- 열 속성 변경
ALTER TABLE로 열 속성을 변경할 때는 MODIFY 하부명령을 통해 실행
ALTER TABLE 테이블명 MODIFY 열 정의열 이름은 변경X, 자료형이나 기본값, NOT NULL 제약 등의 속성 변경
- 열 이름 변경
ALTER TABLE로 열 이름을 변경할 때는 CHANGE 하부명령을 통해 실행
ALTER TABLE 테이블명 CHANGE [기존 열 이름] [신규 열 정의]
- 열 삭제
ALTER TABLE로 열을 삭제할 때는 DROP 하부명령을 통해 실행
ALTER TABLE 테이블명 DROP 열명DROP 뒤에 삭제하고 싶은 열명 지정
4. ALTER TABLE로 관리
- 최대길이 연장
문자열형의 경우 최대길이를 지정하는데, ALTER TABLE로 최대길이를 늘릴 수 있음
ALTER TABLE sample MODIFY col VARCHAR(30)
저장공간을 늘리기 위해 최대길이를 줄이고 싶은 경우가 있을 수 있음
다만, 여러 가지 문제 발생 가능
기존의 행에 존재하는 데이터의 길이보다 작게 지정 불가
열의 최대길이를 줄였다고 해서 실제 저장공간이 늘어나는 경우 적음
- 열 추가
테이블에 열을 추가할 수 있음
ALTER TABLE sample ADD new_col INTEGER
27강. 제약
테이블에 제약을 설정함으로써 저장될 데이터를 제한할 수 있음
1. 테이블 작성시 제약 정의
제약은 테이블에 설정하는 것
CREATE TABLE로 테이블을 작성할 때 제약을 같이 정의함
ALTER TABLE로 제약을 지정하거나 변경할 수 있음
하나의 열에 대해 설정하는 제약은 열을 정의할 대 지정
열에 대해 정의하는 제약을 '열 제약'이라 부름
ex)
| CREATE TABLE sample631 ( a INTEGER NOT NULL, b INTEGER NOT NULL UNIQUE, c VARCHAR(30) ); a열에는 NOT NULL 제약, b열에는 NOT NULL 제약과 UNIQUE 제약이 걸려있음, c열은 x |
한 개의 제약으로 복수의 열에 제약을 설명하는 경우를 '테이블 제약'이라 부름
ex)
| CREATE TABLE sample631 ( no INTEGER NOT NULL, sub_no INTEGER NOT NULL, name VARCHAR(30), PRIMARYKEY (no, sub_no) ); |
제약에는 관리하기 쉽도록 이름을 붙일 수 있음
제약 이름은 CONSTRAINT 키워드를 사용
ex)
| CREATE TABLE sample632 ( no INTEGER NOT NULL, sub_no INTEGER NOT NULL, name VARCHAR(30), CONSTRAINT pkey_sample PRIMARY KEY(no, sub_no) ); |
2. 제약 추가
기존 테이블에도 나중에 체약을 추가할 수 있음
열 제약과 테이블 제약은 다른 방법으로 추가함
- 열 제약 추가
열 제약을 추가할 경우 ALTER TABLE로 열 정의를 변경할 수 있음
ex)
ALTER TABLE sample631 MODIFY c VARCHAR(30) NOT NULL;
- 테이블 제약 추가
테이블 제약은 ALTER TABLE의 ADD 하부명령으로 추가
ex)
ALTER TABLE sample631 ADD CONSTRAINT pkey_sample631 PRIMARY KEY(a);
3. 제약 삭제
테이블 제약은 나중에 삭제할 수 있음
열 제약의 경우, 제약을 추가할 때와 동일하게 열 정의를 변경
ex)
< c열의 NOT NULL 제약 없애기 >
ALTER TABLE sample631 MODIFY c VARCHAR(30);
테이블 제약의 경우, ALTER TABLE의 DROP 하부명령으로 삭제
삭제할 때는 제약명을 지정하여 삭제
ex)
< pkey_sample631 제약 삭제하기 >
ALTER TABLE sample631 DROP CONSTRAINT pkey_sample631;
< 기본키 제약 삭제하기 >
ALTER TABLE sample631 DROP PRIMARY KEY;기본키는 테이블당 하나만 설정할 수 있기때문에 제약명을 지정하지 않고도 삭제할 수 있음
4. 기본키
CREATE TABLE sample634(
p INTEGER NOT NULL,
a VARCHAR(30),
CONSTRAINT pkey_sample634 PRIMARY KEY(p)
);열 p가 sample634 테이블의 기본키
기본키로 지정할 열은 NOT NULL 제약이 설정되어 있어야 함
기본키는 테이블의 행 한개를 특정할 수 있는 검색키
기본키로 설정된 열이 중복하는 데이터 값을 가지면 제약에 위반됨
INSERT INTO sample634 VALUES (1, '첫째줄');
INSERT INTO sample634 VALUES (2, '둘째줄');
INSERT INTO sample634 VALUES (3, '셋째줄');INSERT를 사용해 세 개의 행을 추가하였음
이때 p 열의 값이 1, 2, 3으로 중복하지 않음
이미 존재하는 값인 2로 행을 추가할 시,
p열의 값이 중복되기때문에 '기본키 제약에 위반된다'는 내용의 에러가 표시됨
UPDATE 명령을 실행할 때도 제약을 위반하는 값이 없는지 검사함
p가 3인 행을 2로 갱신하게 되면 p=2인 열이 두 개 존재하므로 오류 발생
기본키 제약 : 열을 기본키로 지정해 유일한 값을 가지도록 하는 구조, '유일성 제약' 이라고도 부름
- 복수의 열로 기본키 구성하기
기본키 제약에는 이를 구성할 열 지정 필요
지정된 열은 NOT NULL 제약이 설정되어 있어야 함 (기본키로는 NULL 값 허용X)
기본키를 구성하는 열이 복수라도 상관X
복수의 열을 기본키로 지정했을 시, 키를 구성하는 모든 열을 사용해서 중복 여부 check
ex)
SELECT a, b FROM sample635;| a | b |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
a 열과 b 열로 기본키를 지정했을 경우,
a 열만을 봤을 때는 중복하는 값이 있지만, b 열이 다르면 키 전체로서는 중복하지 않는다고 간주
→ 기본키 제약에 위반되지 않음
28강. 인덱스 구조
인덱스(색인)은 데이터베이스 객체에 해당
테이블에는 인덱스를 작성할 수 있음
1. 인덱스
인덱스는 테이블에 붙여진 색인
역할 : 검색(SELECT 명령에 WHERE 구조로 조건을 지정하고 그에 일치하는 행을 찾는 일련의 과정) 속도의 향상
데이터베이스의 인덱스에는 검색 시에 쓰이는 키워드와 대응하는 데이터 행의 장소가 저장되어 있음
인덱스는 테이블과는 별개로 독립된 데이터베이스 객체로 작성됨
인덱스는 테이블에 의존하는 객체로, 테이블을 삭제하면 인덱스도 같이 삭제됨
2. 검색에 사용하는 알고리즘
인덱스에 쓰이는 대표적인 검색 알고리즘의 예 : '이진 트리(binary tree)', '해시'
이진트리는 탐색 방법이라기보다 데이터 구조에 가까움
'이진탐색(binary search)' 에서 검색하기 쉬운 구조로 한 것이 이진 트리
- 풀 테이블 스캔(full table scan)
인덱스가 지정되지 않은 테이블을 검색할 때는 테이블에 저장된 모든 값을 처음부터 차례로 조사함
- 이진 탐색(binary search)
차례대로 나열된 집합에서 유효한 검색 방법
집합을 반으로 나누어 조사하는 검색방법
집합의 가운데에서부터 조사하기 시작하여 검색하려고 하는 값과 비교
가운데 값 < 찾고자 하는 값 → 가운데 값의 오른쪽 조사
가운데 값 > 찾고자 하는 값 → 가운데 값의 왼쪽 조사
위의 과정을 반복하여 원하는 수치가 나올 때까지 찾음
데이터 수가 많을 수록 풀 테이블 스캔에 비해 훨씬 빠름
- 이진 트리(binary tree)
테이블에 인덱스를 작성하면 테이블 데이터와 별개로 인덱스용 데이터가 저장장치에 만들어짐
→ 이진 트리라는 데이터 구조로 작성됨
트리는 노드(node)라는 요소로 구성됨
각 노드는 두 개의 가지로 나뉨
- 노드의 왼쪽 가지 → 작은 값
- 노드의 오른쪽 가지 → 큰 값
검색의 진행 방법은 이진탐색과 거의 비슷
원하는 수치와 비교해서 더 크면 오른쪽 가지, 작으면 왼쪽 가지를 따라감
3. 유일성
이진 트리에서는 집합 내에 중복하는 값을 가질 수 없음
29강. 인덱스 작성과 삭제
인덱스는 데이터베이스 객체의 하나로 DDL을 사용해서 작성하거나 삭제
테이블 내에 이름이 중복되지 않도록 지정해 관리
인덱스를 작성할 때는 해당 인덱스가 어느 테이블의 어느 열에 관한 것인지 지정해야 함
복수의 열 지정 가능
1. 인덱스 작성
CREATE INDEX 명령으로 만듦
CREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ... )인덱스를 작성할 때는 저장장치에 색인용 데이터가 만들어짐
테이블 크기에 따라 작성시간, 저장공간 달라짐
2. 인덱스 삭제
DROP INDEX 명령으로 삭제 (인덱스만 삭제하는 경우)
DROP 할 때는 인덱스 이름만 지정하면 됨 (테이블 내 객체로서 작성하는 경우에는 테이블 이름도 지정)
DROP INDEX 인덱스명 # 스키마 객체의 경우
DROP INDEX 인덱스명 ON 테이블명 # 테이블 내 객체의 경우
작성한 인덱스의 열을 WHERE 구로 조건을 지정하여 SELECT 명령으로 검색하면 처리속도 향상
ex)
CREATE INDEX isample65 ON sample62(a);
SELECT * FROM sample62 WHERE a = 'a';
3. EXPLAIN
인덱스를 사용해 검색하는지를 확인하려면 EXPLAIN 명령 사용
EXPLAIN SQL명령EXPLAIN에 뒤이어 확인하고 싶은 SQL 명령 지정
SQL 명령이 실제로 실행되는 것은 X, 어떤 상태로 실행되는지 설명해줌
ex)
EXPLAIN SELECT * FROM sample62 WHERE a = 'a';| id | select_type | table | type | posssible_keys | key |
| 1 | SIMPLE | sample62 | ref | isample65 | isample65 |
sample62의 a 열에는 isample65 라는 인덱스가 작성되어 있음
possible_keys에 사용될 수 있는 인덱스가 표시됨, key는 사용된 인덱스가 표시됨
인덱스를 사용할 수 없게 WHERE 조건을 바꾼 경우
EXPLAIN SELECT * FROM sample62 WHERE no > 10;| id | select_type | table | type | posssible_keys | key |
| 1 | SIMPLE | sample62 | ref | NULL | NULL |
possible_keys와 key가 NULL됨
4. 최적화
내부처리에서는 명령 실행에 앞서 실행계획을 세움
인덱스의 유무뿐만 아니라 인덱스를 사용할 것인지 여부에 대해서도 데이터베이스 내부의 최적화 처리를 통해 판단
판단 기준으로 인덱스의 품질 고려
30강. 뷰 작성과 삭제
1. 뷰
뷰 : 본래 데이터베이스 객체로 등록할 수 없는 SELECT 명령을, 객체로서 이름을 붙여 관리할 수 있도록 한 것
뷰를 참조하면 그에 정의된 SELECT 명령의 실행결과를 테이블처럼 사용할 수 있음
서브쿼리 부분을 뷰로 대체하여 SELECT 명령을 간략하게 표현 가능
자주 사용하거나 복잡한 SELECT 명령을 뷰로 만들어 편리하게 사용 가능
- 가상테이블
뷰는 실체가 존재하지 않음, '가상 테이블' 이라고 부르기도 함
저장공간을 가지지 X
SELECT 명령에서만 사용하는 것을 권장
2. 뷰 작성과 삭제
DDL로 작성하거나 삭제
작성할 때는 CREATE VIEW, 삭제할 때는 DROP VIEW 사용
- 뷰의 작성
CREATE VIEW 뷰명 (열명1, 열명2, ...) AS SELECT 명령CREATE VIEW 다음에 뷰 이름 지정, AS로 SELECT 명령 지정 (AS 생략 불가)
필요에 따라 이름 뒤에 괄호로 묶어 열을 나열하여 열 지정 가능
자료형이나 제약 지정X
- 뷰 삭제
뷰를 삭제하면 더 이상 뷰를 참조하여 사용할 수 없음
DROP VIEW 뷰명
3. 뷰의 약점
저장공간을 소비하지 않는 대신 CPU 자원 사용
- 머티리얼라이즈드 뷰(Materialized View)
뷰의 약점 ①
뷰의 근원이 되는 테이블에 보관하는 데이터양이 많은 경우, 처리속도가 많이 떨어짐
뷰를 중첩해서 사용하는 경우에도 속도가 떨어지기 쉬움
①의 해결방법
→ '머티리얼라이즈드 뷰'
머티리얼라이즈드 뷰는 처음 참조되었을 때 데이터를 저장해 두었다가 다시 참조할 때 데이터를 그대로 사용
매번 SELECT 명령 사용할 필요X
데이터가 변경된 경우에는 SELECT 명령을 재실행하여 데이터를 다시 저장
변경유무를 확인하여 재실행하는 것은 RDBMS가 자동으로 실행
데이터가 자주 변경되지 않는 경우에 뷰의 약점을 어느정도 보완 O
- 함수 테이블
뷰의 약점 ②
뷰를 구성하는 SELECT 명령은 단독으로도 실행할 수 있어야 함
②의 해결방법
→ '함수 테이블'
함수 테이블 : 테이블을 결과값으로 반환해주는 사용자정의 함수
함수에는 인수를 지정할 수 있기때문에 인수의 값에 따라 WHERE 조건을 붙여 결과값을 바꿀 수 있음
그에 따라 상관 서브쿼리처럼 동작 O
'백엔드 > SQL 첫걸음' 카테고리의 다른 글
| 8장 데이터베이스 설계 (0) | 2024.11.02 |
|---|---|
| 7장 복수의 테이블 다루기 (0) | 2024.11.02 |
| 5장 집계와 서브쿼리 (0) | 2024.10.11 |
| 4장 데이터의 추가, 삭제, 갱신 (0) | 2024.10.05 |
| 3장 정렬과 연산 (0) | 2024.10.05 |