[MySQL] DDL 종류와 명령어, DB Index 개념 및 문법

---

DDL(Data Definition Language)

데이터를 정의할 수 있는 SQL의 하위 집합, 테이블 정의어로 데이터를 어떻게 저장할 지 전체적인 골격을 결정하는 역할을 한다. 테이블 생성뿐 아니라 테이블 구조를 바꾸는 역할도 한다.

• DDL : 테이블을 생성
• DML : 생성된 테이블에 데이터를 조회, 삽입, 수정 삭제

DML을 사용하기 위해서는 반드시 DDL을 사용해서 기본틀을 먼저 생성해주어야 한다.

[DDL문]

CREATE	새로운 테이블을 생성
ALTER	기존 테이블 구조 변경
DROP	테이블 삭제
RENAME	기존 테이블 이름 변경
TRUNCATE	기존 테이블 초기화

---

CREATE

데이터베이스

DBMS 저장소는 위와 같은 구조를 가지고 있다. 특정 DB안에 여러 개의 테이블을 만들 수 있는 구조로 되어 있다. 데이터베이스 하위에 여러 개의 테이블을 만들 수 있는 계층 구조인 것이다.
CREATE DATABASE test_db 특정 데이터베이스를 생성하고 CREATE TABLE test_db.students() 그 안에 테이블을 만들 수 있다. 실무에서는 데이터베이스를 도메인 단위로 나눠서 관리를 하기도 하고, 서비스 단위로 테이블을 생성하기도 한다.

데이터베이스 생성

CREATE DATABASE [데이터베이스명]

CREATE DATABASE test_db

테이블 생성

CREATE TABLE [데이터베이스이름].[테이블이름] (
	[열] [데이터타입] [제약조건],
	...
)

CREATE TABLE test_db.students (
  name VARCHAR(255) NOT NULL,
  age INT NOT NULL,
  address VARCHAR(255) NOT NULL
)

데이터베이스 이름	어떤 데이터베이스 하위에 테이블을 생성할 지
테이블 이름	생성하려는 테이블의 이름
열(컬럼)	열의 이름
데이터 타입	해당 열의 데이터 타입 (ex. 정수, 문자열, 날짜, …)
제약조건	[NOT NULL

MySQL 에서 주로 사용하는 데이터 타입, DBMS마다 이름이 약간씩 다를 수 있기 때문에, 정확한 내용은 사용하는 DBMS의 문서를 찾아보는 것이 좋다.

데이터 타입	설명
CHAR(size)	고정된 길이의 문자열로 저장합니다. 예를 들어 데이터 타입을 “CHAR(5)”로 설정할 경우 “abcde”처럼 글자수가 5개인 문자열은 저장이 가능하고, “abc”처럼 글자수가 3개인 문자열을 저장할 때에도 “CHAR(5)” 만큼의 크기를 차지합니다.
VARCHAR(size)	가변 길이의 문자열을 저장할 수 있습니다. 예를 들어 데이터 타입을 “VARCHAR(5)”로 설정할 경우 글자수가 1~5개인 문자열을 저장할 수 있습니다. 즉, “abc”, “abcde”는 저장할 수 있지만 5자를 넘어가는 “abcdefg”는 저장할 수 없습니다.
INT	정수형 데이터를 저장할 수 있습니다. (1, 2, 3, …)
FLOAT	실수형 데이터를 저장할 수 있습니다. (1.0, 2.0, 3.123, …)
DATE	날짜를 저장할 수 있습니다. (2023-12-01)
TIME	시간을 저장할 수 있습니다. (08:38:27)
⭐ DATETIME (timezone 사용 X) ⭐ TIMESTAMP (timezone 지정 가능)	날짜와 시간을 저장할 수 있습니다. (2023-12-01 08:38:27)

TIMESTAMP 의 경우 timezone을 지정할 수 있고, 고려하여 변경된다. kst 를 사용하는 DBMS에서 utc를 사용하는 DBMS로 이주 시킬 경우 timezone에 맞춰서 저장이 된다.

DATETIME은 저장할 수 있는 날짜가 훨씬 길다 1000-01-01 ~ 9999-12-31, TIMESTAMP 는 1970-01-01 ~ 2038-01-09까지 저장이 된다. 실무에서는 TIMESTAMP 가 더 많이 사용된다.

테이블 제약조건(Constraint)

테이블에 부적절한 데이터가 입력되는 것을 방지하기 위해서, 테이블 생성 시점에 규칙을 정해놓은 것. 컬럼 단위로 정해지는 규칙이고, 해당 컬럼에 입력되는 데이터의 성격을 정의해놓는 것이다.

옵션	설명
NOT NULL	해당 열에 NULL 값을 허용하지 않는다는 의미입니다.
DEFAULT	데이터를 입력 시 해당 열에 아무런 값도 입력되지 않은 경우 기본으로 사용할 값 지정합니다.
UNIQUE	해당 테이블 내에서 유일한 속성을 갖는다는 의미로, UNIQUE로 설정된 열에는 중복된 값을 저장할 수 없습니다.
PRIMARY KEY	하나의 테이블에 있는 데이터들을 식별하기 위한 기준입니다.
FOREIGN KEY(외래키)	테이블간에 관계를 나타낼 때 사용하는 Key로, 다른 테이블의 기본키를 참조해 외래키로 사용합니다.

NOT NULL 제약조건

테이블 생성시 해당 열에 NULL값을 허용하지 않는다는 의미

CREATE TABLE 테이블명 (
		컬럼명 데이터타입 NOT NULL
		...
)

NOT NULL 제약조건이 설정되어있는 열에 NULL 값을 넣거나, 아무 값도 넣지 않을 경우 다음과 같은 에러가 발생한다.

ERROR: 실패한 자료: (신기루, 41, null)"address" 칼럼(해당 릴레이션 "students")의 null 값이 not null 제약조건을 위반했습니다.

CREATE TABLE로 테이블 생성시 컬럼 옆에 NOT NULL 이라는 제약조건이 없다면, NULL을 허용한다는 컬럼으로, default는 NULL 허용이다.

DEFAULT

컬럼 기본값 지정

CREATE TABLE 테이블명 (
		컬럼명 데이터타입 DEFAULT 기본값
		...
) 

DEFAULT 제약조건은 데이터베이스 테이블의 컬럼에 대해 값을 명시적으로 제공하지 않을 경우 사용되는 컬럼 기본 값을 지정한다. 만약 테이블에 새로운 행을 삽입할 때 해당 컬럼에 값을 지정하지 않는 경우, 그 컬럼에 DEFAULT(기본)값을 자동으로 할당한다.

-- DEFAULT 제약조건 테스트
CREATE TABLE defaulttable(
	id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    defaultcol VARCHAR(20) DEFAULT '-'
);

INSERT INTO defaulttable (id, name) VALUES(1, 'jeong'); -- defaultcol 에 null 대신에 디폴트로 지정한 값이 들어감

defaultcol 에 null 대신에 디폴트로 지정한 값이 들어감

AUTO_INCREMENT

컬럼 정보를 하나씩 자동으로 증가시킬 때 사용

CREATE TABLE autoinctable(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

INSERT INTO autoinctable (name) VALUES ('이름1'),('이름2'),('이름3'); -- id에는 아무런 값을 입력하지 않았지만 1,2,3으로 자동 증가해줌

PRIMARY KEY가 선언되어 있어야 한다.

UNIQUE 제약조건

테이블의 컬럼 그룹에 대해 유일한 값이여야 한다는 제약조건, UNIQUE 제약은 데이터 무결성을 유지하는 데 도움이 되며, 일반적으로 고유한 식별자를 갖는 열에 많이 사용된다. (예시. 사용자 ID나 주문 번호 등에 사용)

CREATE TABLE 테이블명 (
		컬럼명 데이터타입 UNIQUE 
		...
)

User 테이블의 이메일 주소 컬럼에 UNIQUE 제약조건을 설정한다면 해당 테이블에서 고유한 이메일 주소를 가져야한다는 의미이며, 중복된 이메일 주소가 INSERT 되거나 UPDATE 시도를 할 경우 데이터베이스 오류가 발생한다.

PRIMARY KEY (기본키)

하나의 테이블에 있는 데이터들을 식별하기 위한 기준, 하나의 테이블에 하나의 기본키만 생성이 가능하고, 기본키는 하나 이상의 컬럼으로 구성이 가능하다. 기본키로 지정이 되면 테이블의 데이터를 쉽고 빠르게 찾도록 도와주는 역할을 한다.

CREATE TABLE 테이블명 (
		컬럼명 데이터타입 PRIMARY KEY
		...
) 

• 변경불가
• UNIQUE
• NOT NULL

의 특징이 있다.

FOREIGN KEY (외래키)

테이블간에 관계를 나타낼 때 사용하는 Key, 다른 테이블의 기본키를 참조해 외래키로 사용한다. 즉, 한 테이블의 외래키는 연결되어 있는 다른 테이블의 기본키 중 하나이다.

원천 데이터를 삭제하려는 시도를 성공했을 때 PK를 바라보고 있던 FK들은 길을 잃기 때문에 이러한 상황을 막기 위해서 다양한 제약 조건이 있다.

CREATE TABLE orders (
		id INT PRIMARY KEY,
		customer_id INT, 
		date TIMESTAMP DEFAULT now(),
		payment VARCHAR(50),
		amount INT,
		delivery_amount INT,
		**FOREIGN KEY** (customer_id) **REFERENCES** customer(id) **[ON DELETE CASCADE|ON DELETE SET NULL]**
)

• RESTRICT(default) : 참조하고 있는 데이터가 있을 경우에 원천 데이터를 삭제하려는 시도를 한다면 삭제를 못하도록 막음
• ON DELETE CASCADE : 삭제는 할 수 있게 하되 원천 데이터를 참조 하고 있는 값을 함께 삭제
• ON DELETE SET NULL : 원천 데이터의 1번 데이터를 삭제 했을 때 해당 값을 참조 하고있던 키값을 NULL로 바꿈

RESTRICT

대부분의 DBMS 시스템에서 default 설정으로 적용되는 옵션, 데이터의 무결성을 유지하기 위해 가장 안전한 옵션이며, 외래키 제약조건이 설정된 경우 참조된 행이 삭제되는 것을 방지하여 데이터의 일관성을 유지한다.

자식 테이블의 모든 레코드를 삭제해야만 부모 테이블의 레코드를 삭제할 수 있게 되며, 자식 테이블의 행이 부모 테이블의 행을 참조하지 않는 상태가 된다면 부모 테이블의 해당 레코드를 삭제할 수 있다.

삭제 순서 : 참조하고 있던 데이터 모두 삭제 → 원천 데이터 삭제

-- 고객 테이블의 ID=1 정보 먼저 삭제 불가 (주문 테이블에 참조가 걸려있는 행 삭제 불가) 
-- ERROR 발생
DELETE FROM customer where id = 1

부모 ROW를 삭제하고 싶다면 참조하고 있는 ROW부터 먼저 삭제해야 한다.

-- 정상적인 삭제 순서 : 주문 테이블 정보 삭제 후 고객 정보 삭제
DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customer where id = 1

위와 같이 삭제 후 다시 데이터를 넣으려고 하면 customer 테이블에 1번 값이 없기 때문에 들어가지지 않는다. → 참조될 수 없는 값을 넣어주고 있기 때문

-- 외래키 제약 조건에 위배되었기 때문에 실행되지 않는다.
INSERT INTO orders VALUES 
(2, 1, '2023-11-13', '신용카드', 20000, 2500),
(3, 2, '2023-11-12', '계좌이체', 30000, 3000);

삭제, 수정, 생성 모두 제약이 걸린 상태이다. 테이블 삭제 (DROP) 에도 순서가 생긴다.

CASCADE

참조된 부모 테이블에 대한 DELETE 허용, 참조된 테이블의 레코드가 삭제될 때, 해당 레코드를 참조하는 다른 테이블의 레코드도 함께 삭제한다. 부모 테이블의 행(row)이 삭제되면 관련된 자식 테이블의 행도 자동 삭제된다. → 종속 삭제

CREATE TABLE orders (
		id INT PRIMARY KEY,
		customer_id INT, -- 외래키 설정
		date TIMESTAMP DEFAULT now(),
		payment VARCHAR(50),
		amount INT,
		delivery_amount INT,
		FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE
);

ID 1번인 PK가 3으로 수정된다면, 참조하고 있던 FK도 모두 3번으로 수정된다 → PK는 수정되는 일이 없기 때문에 실제로 일어나는 일은 없다.

-- 삭제 순서
DROP TABLE orders;
DROP TABLE customer;

SET NULL

부모 테이블의 정보가 삭제되었을 때, 이를 참고하고 있던 자식 테이블의 FK 컬럼값이 NULL로 변경된다.

CREATE TABLE orders (
		id INT PRIMARY KEY,
		customer_id INT, -- 외래키 설정
		date TIMESTAMP DEFAULT now(),
		payment VARCHAR(50),
		amount INT,
		delivery_amount INT,
		FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE SET NULL
);

원천 테이블 먼저 생성 후에 참조할 테이블 생성해야 된다.

-- 삭제 순서
DROP TABLE orders;
DROP TABLE customer;

1	NULL	2023-11-12 00:00:00	신용카드	10000	2500
2	NULL	2023-11-13 00:00:00	신용카드	20000	2500
3	2	2023-11-12 00:00:00	계좌이체	30000	3000

외래키는 데이터의 무결성을 위해 사용하지만, 테이블간의 의존도를 높여 추후 데이터를 수작업으로 다룰 경우 변경에 불리하게 작용된다. → 테이블간에 종속성이 생기고, 데이터를 수정, 삭제 할 때 순서까지 모두 고려해야 하는 번거로움이 생기게 되는데, 외래키 제약조건에 종속성 때문에 제약조건을 일부러 주지 않는 경우도 있다.

• 수작업으로 테이블 내 데이터를 변경해야할 때
• 초기 테이블 설계 이후에 추가 개발이 이루어질 경우, 데이터 변경이 필요할 때

보통은 외래키를 따로 설정하지 않고, 애플리케이션 코드 레벨에서 데이터의 일관성을 확인한다. 참조 관계에 있는 테이블을 명시만 하되, 실제 테이블간의 의존도는 낮추고 개발자의 자유도를 높이는 방법이다.

데이터에 연관성이 있으면 외래키 등의 종속성이 필요할 것 같은데, 코드를 작성하면서 종속성을 낮추려는 것이 결국 종속성을 부여하는 것 아닌가?

• 워크벤치 사용 vs 자바 코드 사용:
  • 워크벤치를 사용해서 직접 데이터 처리를 할 경우: DBMS 자체의 제약 조건이 적용되며, 이러한 제약으로 인해 자바 코드에서도 종속성이 생길 수 있음.
  • 자바 코드를 사용하여 비즈니스 로직을 처리할 경우: DBMS 자체의 제약 조건이 없으므로, 코드를 통해 종속성을 관리할 수 있음.
• 외래키 사용에 대한 예시:
  • 예를 들어, 여러 테이블이 User 테이블에 과도하게 의존할 경우, 외래키를 사용하는 것이 종속성을 강화할 수 있음.
  • 그러나 같은 관심사가 아니라면 외래키를 사용하지 않는 것이 더 적합할 수 있음. 외래키 사용이 항상 필요한 것은 아니며, 무조건적으로 사용하는 것은 바람직하지 않음.

---

ALTER

SQL의 “ALTER TABLE” 구문은 기존 테이블의 구조를 변경하기위해 사용한다. 테이블에 데이터가 쌓여있는 상태에서 구조를 변경하면 얘기치 못한 이슈가 발생할 수 있다.

새로운 열 추가(ADD)

-- students 테이블에 grade 열 추가, grade 열은 VARCHAR(20) 데이터 타입을 갖는다.
ALTER TABLE students ADD grade VARCHAR(20)

기존 열 이름 변경(RENAME COLUMN)

-- students 테이블의 grade 열의 이름을 great로 수정
ALTER TABLE students RENAME COLUMN grade TO great

기존 열 데이터 타입 변경(MODIFY COLUMN)

-- students 테이블의 address 열의 데이터 타입을 VARCHAR(100) 으로 수정
ALTER TABLE students MODIFY COLUMN address VARCHAR(100) 

데이터 타입을 변경할 때는 기존 열에 저장된 데이터가 새 데이터 타입에 맞는지 확인해야 한다. 기존 데이터가 새 데이터 타입에 맞지 않으면 에러가 발생할 수 있다. 주소(문자열)가 저장되어 있는데 이를 INT(숫자) 타입으로 바꾸려고 한다면 에러가 발생한다. 아무런 값이 들어있지 않다면 변경 가능하다.

열 삭제(DROP COLUMN)

-- grade라는 열을 삭제
ALTER TABLE students DROP COLUMN grade

---

TRUNCATE

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

테이블의 모든 데이터 삭제

-- students의 모든 데이터를 삭제
TRUNCATE TABLE students

TRUNCATE vs DELETE

“TRUNCATE”와 “DELETE”는 데이터를 삭제한다는 점에서 같지만 전체 데이터를 삭제할 때는 “DELETE”보다 “TRUNCATE”의 속도가 더 빠르다. 그 이유는 TRUNCATE의 경우 테이블을 스캔하는 과정을 거치지 않기 때문이라고 한다.

‘속도가 더 빠르면 TRUNCATE 명령어를 사용해야 하는 것인가?’ 생각할 수 있지만, DELETE 명령어는 로그를 남기고 데이터 복구가 가능하다는 특징 때문에 많이 사용한다.

DROP vs TRUNCATE vs DELETE

	DROP	TRUNCATE	DELETE
종류	DDL	DDL(일부 DML)	DML
COMMIT	AUTO COMMIT	AUTO COMMIT	사용자 COMMIT
ROLLBACK	불가	불가	가능
명령어 수행시	테이블 정의 삭제	테이블을 최초 생성한 초기 상태로 만들어줌	데이터만 삭제
로그	남기지 않음	남기지 않음	남김
속도	빠름	빠름	느림

---

DB Index

테이블의 특정 열에 대한 검색 및 조회 성능을 향상 시키기 위해 사용되는 자료구조이다. 테이블의 특정 열에 대한 검색 및 조회 성능을 향상 시키기 위해 사용되는 자료구조이며, ‘목차’를 데이터베이스에 적용해 놓은 것이다.

찾아야 할 컬럼값이 있을 경우 인덱스가 없다면 원하는 데이터의 위치를 테이블 전체를 탐색(Full Scan) 해서 찾아야겠지만, 인덱스가 있다면 전체를 탐색하지 않고 필요한 행에 빠르게 접근할 수 있다.

-- SELECT 명령문을 실행할 때 WHERE 조건절로 필터링된 특정 행을 가져오기
SELECT *
FROM students
WHERE name = '홍길동';

name 속성에 대한 값이 ‘홍길동’인 행(row)를 가져오겠지만 만약 students 테이블의 row 수가 100만개라면 Full Scan을 하기에 성능이 좋지 않을 것이다. 이럴 경우 해당 행에 인덱스를 추가한다.

Index 생성/삭제 문법

<생성/추가>

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

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

CREATE INDEX idx_students_name ON students(name);
CREATE INDEX idx_students_created_at_name ON students(created_at, name);

<삭제>

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

ALTER TABLE students DROP INDEX idx_students_name;

Index 자료구조

DB Index의 자료구조는 Hash Table, B-Tree, B+Tree 등의 자료구조가 있다.

Hash Table

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

해시 함수는 Key가 조금이라도 다르면 완전히 다른 해시 값을 생성한다. 이러한 해시 테이블을 사용하는 Index의 경우 WHERE 조건의 등호(=) 연산에는 효율이 좋지만, 부등호 연산(>, <)은 부적합하다. 해시 테이블은 내부 데이터들이 정렬되어 있지 않아 탐색이 효율적이지 않다.

B-Tree

B-Tree란 자식 노드가 2개 이상인 트리를 의미한다. 각 Key의 왼쪽 자식은 key보다 작은값을, 오른쪽 자식은 key보다 큰 값을 가진다.
B-Tree의 Key-Value값들은 항상 Key를 기준으로 오름차순 정렬임으로, 해시 테이블보다 효율적인 데이터 탐색이 가능하다.

Index 고려사항

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

1. INSERT : 새로운 데이터에 대한 인덱스가 추가
2. DELETE : 삭제하는 데이터의 인덱스를 제거
3. UPDATE : 기존 인덱스를 제거하고, 갱신된 데이터에 대한 인덱스를 추가

데이터 갱신보다는 조회가 주로 사용되는 컬럼에 INDEX를 생성하는 것이 유리합니다.
ex) 사용자명, 제품명, …

대상 컬럼 선정 방법

인덱스 컬럼을 선정하는 방법은 검색 성능에 유리한 방식을 선택하는 것이다.

인덱스 컬럼을 선정하는 방법은, 일반적으로 Cardinality가 높은 컬럼을 우선적으로 인덱싱하는 것이 검색 성능에 유리하다. Cardinality란 특정 데이터 집합의 유니크(Unique)한 값의 개수를 의미한다.

• 남-여 두가지 값만 존재하는 컬럼은 중복도가 높으며 Cardinality가 낮다.
• 개인마다 고유한 값이 존재하는 주민등록번호 컬럼은 중복도가 낮고 Cardinality가 높다.

Index를 통해 데이터를 더 많이 필터링할 수 있는 컬럼을 Index 대상 컬럼으로 선정해야 한다.

인덱스 설정 기준	정도
Cardinality	높을수록 적합
활용도	높을수록 적합
중복도	낮을수록 적합