[DBMS] ALTER/DB Index/DCL/정규화

SQL의 ALTER TABLE 구문은 기존 테이블의 구조를 변경하기 위해서 사용함.

 

ALTER

새로운 열 추가

ALTER TABLE students ADD grade VARCHAR(20)

- students 테이블에 grade 열을 추가함. grade 열은 VARCHAR(20) 데이터 타입을 가짐.

 

기존 열 이름 변경

ALTER TABLE students RENAME COLUMN grade TO great

- students 테이블의 grade 열의 이름을 great로 수정함.

 

기존 열 데이터 타입 변경

ALTER TABLE students ALTER COLUMN address TYPE VARCHAR(100) 

- students 테이블의 address 열의 데이터 타입을 VARCHAR(100) 으로 수정함.

데이터 타입을 변경할 때는 기존 열에 저장된 데이터가 새 데이터 타입에 맞는지 확인해야 함. 만약 기존 데이터가 새 데이터 타입에 맞지 않으면 에러가 발생할 수 있음!!

 

열 삭제

ALTER TABLE students DROP COLUMN grade

- grade라는 열을 삭제함.

 

DROP

테이블 삭제

DROP TABLE test_db.students

- students 테이블을 삭제함.

 

TRUNCATE

TRUNCATE TABLE 은 테이블의 모든 데이터를 삭제할 때 사용함. 테이블을 TRUNCATE 하면 최초 생성된 테이블 초기 상태로 만들어줌.

TRUNCATE TABLE students

 

TRUNCATE vs DELETE

둘은 데이터를 삭제한다는 점에서 같지만 전체 데이터를 삭제할 때는 DELETE 보다 TRUNCATE 의 속도가 더 빠름. TRUNCATE의 경우 테이블을 스캔하는 과정을 거치지 않기 때문임. 하지만 DELETE는 로그를 남기고 데이터 복구가 가능하기 때문에 둘 중 적절한 것을 선택해서 쓰면됨.

 

---

DB Index

Index란?

데이터베이스에서 인덱스란, 테이블의 특정 열에 대한 검색 및 조회 성능을 향상 시키기 위해 사용되는 자료구조임.

인덱스가 있다면 전테를 Full Scan 하지 않고도 필요한 행에 빠르게 접근할 수 있음.

 

Index 생성/삭제 문법

<생성/추가>

-- 단일 컬럼 인덱스
CREATE INDEX [인덱스명] ON [테이블명] ([컬럼명]); 

-- 다중 컬럼 인덱스
CREATE INDEX [인덱스명] ON [테이블명] ([컬럼명, ...]); 

<삭제>

ALTER TABLE [테이블명] DROP INDEX [인덱스이름];

 

Index 자료구조

Hash Table

해시 테이블은 Key-Value 형태로 이루어진 데이터를 저장하는데 특화된 자료구조임. 해시 테이블 기반의 DB Index는 특정 컬럼의 값과 데이터의 위치를 Key-Value 로 사용함.

hash함수로 key를 고유한 hash 값으로 변환시켜 저장

해시 테이블을 사용하는 Index의 경우 WHERE 조건의 등호(=) 연산에는 효율이 좋지만 부등호 연산(>, <)은 부적합함.

해시 테이블의 내부 데이터들이 정렬되어 있지 않기 때문.

 

B-Tree

자식 노드가 2개 이상한 트리를 의미함. 각 Key의 왼쪽 자식은 key보다 작은값을, 오른쪽 자식은 key보다 큰 값을 가짐. 

B-Tree의 Key-Value값들은 항상 Key를 기준으로 오름차순 정렬임. 그렇기 때문에 해시 테이블보다 효율적이 데이터 탐색이 가능함.

Index 고려사항

DBMS 내부적으로 관리하는 인덱스 테이블은 이진트리 검색을 사용하기 때문에 기본적으로 정렬되어 있음. 그래서 만약 인덱스 테이블이 참조하는 테이블에서 삽입, 삭제, 수정이 자주 일어난다면 인덱스 테이블은 그때마다 데이터를 정렬하기 때문에 전체적인 성능 저하를 초래할 수 있음.

 

따라서 데이터 갱신보다는 조회가 사용되는 컬럼에 Index를 생성하는 것이 유리함.

 

대상 컬럼 선정 방법

인덱스 컬럼을 설정하는 방법은 일반적으로 Cardinality가 높은 컬럼을 우선적으로 인덱싱하는 것이 검색 성능에 유리함.

- Cardinality : 특정 데이터 집합의 유니크한 값의 개수

ex) 개인마다 고유한 값이 존재하는 주민등록번호 컬럼은 중복도가 낮고 Cardinality가 높음.

 

따라서 Index를 통해 데이터를 더 많이 필터링할 수 있는 Index 대상 컬럼으로 선정해야함.

---

DDL 

Data Control Language의 약자. 데이터베이스 권한 관리, 트랜잭션 관리.

 

GRANT, REVOKE는 데이터 접근 권한을 제어하기 위한 SQL문임.

 

GRANT

GRANT [권한] ON [테이블명] TO [권한을 받을 사용자]

- 사용자에게 권한 부여

 

REVOKE

REVOKE [권한] ON [테이블명] FROM [권한을 박탈할 사용자]

- 사용자의 권한 회수

 

GRANT, REVOKE에서 부여하고 박탈할 수 있는 권한의 종류

트랜잭션이란?

하나의 기능을 수행하기위한 작업 단위임.

ex) 트랜잭션 COMMIT 실습 (A 세션에서는 AUTO COMMIT이 꺼져있다.)

-- BEGIN
UPDATE accounts
SET balance = 5000
WHERE name = 'A';

INSERT INTO accounts VALUES ('B', 5000);

- 먼저 A 세션에서 하나의 입금 트랜잭션을 진행한다. 아직 COMMIT을 하지 않았을 때 B 세션에서는 아직 A 세션의 입금결과가 반영되지 않는다.

A 세션 결과

B 세션 결과

 

A 세션에서 COMMIT을 해야 비로소 B 세션에 SET, INSERT 결과가 반영된다.

 

ex) 트랜잭션 ROLLBACK 실습

 

A 세션 결과

A 세션에 C와 C의 balance 값을 추가했다. 이 작업을 COMMIT 하기전에 취소하고 싶다면 ROLLBACK을 실행하면 된다. 그럼 C INSERT 전 데이터베이스로 돌아가게 된다.

INSERT INTO accounts VALUES ('C', 4000);
ROLLBACK;

A 세션 ROLLBACK 결과

ex) 은행 송금 트랜잭션 예시

-- A, B 잔고 10000원인 상태에서 
-- A가 B에게 5000원 송금 (한 트랜잭션 단위) 
UPDATE accounts 
SET balance = 5000
WHERE name = 'A'

UPDATE accounts 
SET balance = 15000
WHERE name = 'B'

COMMIT;

---

정규화

정규화(Normalization)

데이터베이스 정규화는 데이터의 구조를 최적화하는 과정임. 데이터의 중복을 최소화하는데 목적이 있음.

 

정규화 전

- 이런 경우 제품 이름, 제품 제조사, 고객 이름, 고객 주소 모두 중복된 값이 있음... 만약 고객의 정보가 추가됐을 때 UPDATE나 INSERT를 하기 어려움. 분리해야하는 목적이 생겼음.

 

정규화 후

데이터의 중복을 없애기위해 테이블을 3개로 분리함.

 

정규화의 종류

제 1 정규화, 제 2 정규화, 제 3 정규화, ~~~ 제 5 정규화 (1에서 5로 갈수록 더 엄격한 제약 조건)

보통 실무에서의 정규화는 제 3 정규화까지를 의미함.

 

제 1 정규화 (1NF)

각 열(column)이 하나의 값을 가지게 하는 과정.

제품 구성 열을 보면 하나의 열에 CPU, RAM 등 여러 개의 값을 가지고 있음.

제 1 정규화가 완료된 테이블

이렇게 제품 구성 열은 단 하나의 값만 가지게 된다.

 

제 2 정규화 (2NF)

테이블의 비주요 속성이 기본키에 완전 함수 종속되도록 하는 것임 -> ????

그러니까 모든 열이 기본키(primary key)에 의존해야 한다는 것을 의미함. 이것은 현재 테이블의 주제와 관련없는 컬럼을 다른 테이블로 분리하는 작업임.

원본 데이터

제 2 정규화가 완료된 테이블

제 2 정규화의 핵심은 하나의 테이블이 하나의 주제를 가지도록 테이블을 분리하는 것임. 

 

제 3 정규화 (3NF)

제 2 정규화를 완료한 테이블에서 이행적 함수 종속성을 제거하는 과정임. 즉, 일반 컬럼에만 종속된 컬럼은 다른 테이블로 빼는 것임.

네?????

원본 데이터

데이터의 이행적 종속 관계를 그림으로 나타낸 것

 

제품명은 주문번호에 종속되어 있고, 제품 카테고리는 제품명에 종속되어 있음. 따라서 제품 카테고리는 주문번호에 종속되어 있음. 이러한 종속 관계를 끊어내기 위해 주문과 제품 정보는 별도의 테이블로 분리함.

제 3 정규화가 완료된 테이블

 

이렇게 각 테이블이 하나의 주제를 가지도록 분리함으로써 제 3 정규화를 수행함.

 

정규화의 장점

1. 일관성

정규화된 테이블은 각 테이블이 하나의 목적을 가지고 있기 때문에 데이터를 더 쉽게 관리할 수 있음. 데이터를 수정하거나 삭제할 때 오류가 발생할 가능성이 줄어듦.

 

2. 유연성

데이터 구조를 변경하거나 새로운 요구 사항을 쉽게 수용할 수 있음. 새로운 속성이나 테이블을 추가하는데 더 유연함.

 

3. 저장 공간 최적화

정규화로 저장 공간을 효율적으로 사용할 수 있음. 중복 데이터가 제거되므로 데이터 크기를 줄일 수 있고, 저장 공간과 관련된 비용을 절감할 수 있음.

요약하자면 데이터를 관리하기 쉬워지고, 새로운 요구 사항에 유연하게 대응할 수 있고, 저장 공간의 비용을 절감하기위해 정규화를 사용한다고 할 수 있음.