[DB][MySQL][개념] 📚 DB 초고수의 길! 인덱스부터 VIEW까지 한 방에 마스터하기! 💥

김상욱·2024년 9월 12일

💾 DDL (Data Definition Language)

DDL은 데이터베이스에서 객체의 구조를 정의하는 명령어입니다. 이를 통해 테이블, 뷰, 인덱스 등 데이터베이스 객체의 생성, 수정, 삭제 작업을 수행할 수 있습니다. 🛠️

🔑 주요 명령어

CREATE: 데이터베이스 객체 생성
ALTER: 기존 데이터베이스 객체 수정
DROP: 데이터베이스 객체 삭제
RENAME: 데이터베이스 객체의 이름 변경
TRUNCATE: 테이블의 데이터 삭제 및 초기화

💡 예시 코드

-- 테이블 생성
CREATE TABLE table_name (
   column_name datatype constraints
);

-- 테이블 수정
ALTER TABLE table_name
ADD column_name datatype;

-- 테이블 삭제
DROP TABLE table_name;

-- 테이블 이름 변경
RENAME TABLE old_table_name TO new_table_name;

-- 테이블 데이터 초기화
TRUNCATE TABLE table_name;

🔤 Character Set & Collation

Character set: 문자가 컴퓨터에 저장될 때 어떤 '코드'로 저장될지를 정하는 규칙입니다. 예를 들어 UTF-8, ASCII 등이 있습니다.
Collation: 저장된 문자를 비교하거나 정렬할 때 적용되는 규칙들의 집합입니다. 예로 utf8_general_ci, utf8_bin이 있습니다.

🔢 데이터 타입 정리

1. 문자열 데이터 타입

CHAR(M): 고정 길이 문자열. M은 1~255 사이의 값이며, 짧은 문자열도 지정한 길이만큼 메모리를 차지합니다.
- 예: CHAR(20)에 10자를 저장해도 20바이트 사용 📝
VARCHAR(M): 가변 길이 문자열. M은 1~65535 사이의 값이며, 실제 저장한 길이만큼만 메모리 사용 + 길이 정보 저장용 1바이트 추가.
- 예: VARCHAR(20)에 10자를 저장하면 11바이트 사용 📝
TEXT 타입들:
- TINYTEXT: 최대 255바이트
- TEXT: 최대 65535바이트
- MEDIUMTEXT: 최대 16777215바이트
- LONGTEXT: 최대 4294967295바이트
ENUM: 미리 정의된 값 중 하나를 저장하며, 최대 65535개의 값을 가질 수 있습니다. 내부적으로는 숫자로 저장됩니다.
SET: 미리 정의된 값들 중 여러 개를 저장하며, 최대 64개의 요소로 구성됩니다.

2. 숫자 데이터 타입

BIT(M): 비트 값을 저장하며, M은 1~64 사이의 비트 수입니다.
BOOL/BOOLEAN: TINYINT(1)의 동의어로, 0은 false, 1은 true로 간주됩니다.
정수 타입:
- TINYINT: 1바이트, signed: -128 ~ 127, unsigned: 0 ~ 255
- SMALLINT: 2바이트, signed: -32768 ~ 32767, unsigned: 0 ~ 65535
- MEDIUMINT: 3바이트, signed: -8388608 ~ 8388607, unsigned: 0 ~ 16777215
- INT: 4바이트, signed: -2147483648 ~ 2147483647, unsigned: 0 ~ 4294967295
- BIGINT: 8바이트, signed: -9223372036854775808 ~ 9223372036854775807, unsigned: 0 ~ 18446744073709551615
부동 소수점 타입:
- FLOAT(M, D): 부동 소수점 숫자 (M: 총 자릿수, D: 소수점 이하 자릿수)
- DOUBLE(M, D): FLOAT보다 더 높은 정밀도 제공
고정 소수점 타입:
- DECIMAL(M, D): 고정 소수점 숫자 (금융 계산에 적합)
- NUMERIC(M, D): DECIMAL의 동의어

3. 날짜/시간 데이터 타입

DATE: 날짜를 YYYY-MM-DD 형식으로 저장 (범위: 1001-01-01 ~ 9999-12-31)
TIME: 시간을 HH:MM:SS 형식으로 저장 (범위: -838:59:59 ~ 838:59:59)
DATETIME: 날짜와 시간을 YYYY-MM-DD HH:MM:SS 형식으로 저장 (범위: 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59)
TIMESTAMP: 타임스탬프 값을 저장하며, 데이터 삽입 또는 수정 시 자동 업데이트 (범위: 1970-01-01 ~ 2038-01-19)
YEAR(2|4): 2자리 또는 4자리의 연도 저장 가능 (2자리: 70-69, 4자리: 1901-2155)

4. 이진 데이터 타입

BINARY(M): 고정 길이 이진 문자열 저장
VARBINARY(M): 가변 길이 이진 문자열 저장
BLOB 타입들:
- TINYBLOB: 최대 255바이트
- BLOB: 최대 65535바이트
- MEDIUMBLOB: 최대 16777215바이트
- LONGBLOB: 최대 4294967295바이트

🛠 CREATE 구문에서의 옵션 속성 및 제약 조건

MySQL에서 테이블을 생성할 때 사용할 수 있는 다양한 옵션 속성과 제약 조건에 대해 설명하고, 각각의 예시를 제공하겠습니다.

1. 🔧 옵션 속성

NOT NULL: 각 행에서 해당 열은 반드시 값을 가져야 하며, NULL 값은 허용되지 않습니다.
- 예시:
```
CREATE TABLE users (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL
);
```
DEFAULT: 값이 명시되지 않을 경우 자동으로 설정되는 기본값을 지정합니다.
- 예시:
```
CREATE TABLE products (
  id INT AUTO_INCREMENT,
  price DECIMAL(10, 2) DEFAULT 0.00
);
```
UNSIGNED: 음수 값을 허용하지 않고 양수만 허용하는 숫자 타입에 사용됩니다.
- 예시:
```
CREATE TABLE scores (
  id INT UNSIGNED,
  points INT UNSIGNED
);
```
AUTO INCREMENT: 새 레코드가 추가될 때마다 필드 값이 자동으로 1씩 증가합니다.
- 예시:
```
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_number VARCHAR(20)
);
```
PRIMARY KEY: 테이블의 각 행을 고유하게 식별하기 위한 열로 설정됩니다. AUTO INCREMENT와 자주 함께 사용됩니다.
- 예시:
```
CREATE TABLE employees (
  employee_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);
```

2. 🧩 제약 조건

NOT NULL: 열에 NULL 값을 저장할 수 없으며, 반드시 값을 지정해야 합니다.
- 예시:
```
ALTER TABLE students
MODIFY COLUMN name VARCHAR(50) NOT NULL;
```

UNIQUE: 열의 값이 중복되지 않도록 보장합니다.

예시:

CREATE TABLE accounts (
  id INT AUTO_INCREMENT,
  email VARCHAR(100) UNIQUE,
  PRIMARY KEY (id)
);

PRIMARY KEY: 고유한 값을 가지는 열로 각 행을 식별합니다.

예시:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50)
);

FOREIGN KEY: 다른 테이블의 PRIMARY KEY를 참조하는 외래키입니다.

예시:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

DEFAULT: 값이 없을 경우 자동으로 설정되는 기본값을 지정합니다.

예시:

CREATE TABLE payments (
  payment_id INT AUTO_INCREMENT PRIMARY KEY,
  amount DECIMAL(10, 2) DEFAULT 0.00
);

CHECK: 열의 값이 특정 조건을 만족해야 하도록 제약을 설정합니다. (MySQL 8.0 이상에서 사용 가능)
- 예시:
```
CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  salary DECIMAL(10, 2),
  CHECK (salary > 0)
);
```

3. 💡 제약 조건 설명

제약 조건을 통해 컬럼에 저장될 데이터를 관리하고, 잘못된 값이 저장되는 것을 방지할 수 있습니다. 제약 조건은 테이블을 생성할 때나 ALTER 명령어로 나중에 추가할 수 있습니다.

4. 📚 스키마

스키마는 데이터베이스의 테이블에 저장될 데이터의 구조와 형식을 정의하는 중요한 개념입니다. 이를 통해 데이터베이스의 조직과 관계를 명확히 할 수 있습니다.

5. 📊 ER 다이어그램 (ERD)

ER 다이어그램은 현실 세계의 개체와 그들 간의 관계를 시각적으로 표현하는 도구입니다. 이를 통해 개체 간의 관계를 한눈에 파악할 수 있습니다. 📐

🔄 ALTER 명령어로 테이블 수정하기

이 섹션에서는 MySQL에서 테이블을 수정하는 다양한 방법을 설명하고, 각각의 명령어와 예시를 함께 제공합니다.

1. 🆕 컬럼 추가

명령어:

ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입 제약조건;

설명: 테이블에 새 컬럼을 추가할 때 사용하는 명령어입니다.
예시:
```
ALTER TABLE students ADD COLUMN age INT NOT NULL;
```
위 예시는 students 테이블에 age라는 새로운 컬럼을 추가하는 코드입니다.

2. 🔄 컬럼 변경

명령어:

ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 데이터타입 제약조건;

설명: 기존 컬럼의 데이터 타입이나 제약 조건을 변경할 때 사용됩니다.
예시:
```
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10, 2) NOT NULL;
```
위 코드는 employees 테이블에서 salary 컬럼의 데이터 타입을 DECIMAL(10, 2)로 변경하고, NOT NULL 제약을 추가하는 명령어입니다.

3. ✏️ 컬럼 이름 변경

명령어:

ALTER TABLE 테이블명 CHANGE COLUMN 이전컬럼명 변경컬럼명 데이터타입 제약조건;

설명: 컬럼의 이름을 변경할 때 사용하는 명령어로, 데이터 타입이나 제약 조건도 동시에 변경 가능합니다.
예시:
```
ALTER TABLE orders CHANGE COLUMN order_date order_created_at DATETIME NOT NULL;
```
위 예시는 orders 테이블의 order_date 컬럼을 order_created_at으로 이름을 변경하는 동시에, DATETIME 타입과 NOT NULL 제약 조건을 설정하는 코드입니다.

4. ❌ 컬럼 삭제

명령어:

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

설명: 테이블에서 특정 컬럼을 삭제할 때 사용되는 명령어입니다.
예시:
```
ALTER TABLE students DROP COLUMN address;
```
이 코드는 students 테이블에서 address 컬럼을 삭제하는 명령어입니다.

5. 📝 테이블 이름 변경

명령어:

ALTER TABLE 이전테이블명 RENAME 변경테이블명;

설명: 테이블의 이름을 변경할 때 사용하는 명령어입니다.
예시:
```
ALTER TABLE customers RENAME TO clients;
```
위 코드는 customers 테이블의 이름을 clients로 변경하는 예시입니다.

📚 Index (인덱스)

🔍 인덱스란?

정의: 인덱스는 데이터베이스에서 테이블의 검색 및 조작 속도를 향상시키는 자료 구조입니다. 하나 또는 여러 개의 컬럼을 기반으로 생성되며, 데이터를 보다 효율적으로 조회할 수 있도록 도와줍니다. 📖

🛠️ 인덱스의 역할

검색 속도 향상: 인덱스는 데이터를 찾는 과정을 빠르게 해주는 구조로, 책의 찾아보기처럼 데이터를 빠르게 찾아줍니다. 이로 인해 데이터 조회 시 성능이 향상됩니다.
순서 매김: 인덱스는 정렬된 상태로 데이터를 저장하여, 레코드 접근을 효율적으로 할 수 있도록 돕습니다.
데이터의 위치 찾기: 인덱스는 컬럼의 값과 해당 레코드의 저장 위치를 키-값 쌍으로 저장하여, 데이터의 위치를 빠르게 찾아냅니다. 🔑
고유성 보장: 고유 인덱스는 테이블의 컬럼에 중복된 값이 들어가지 않도록 보장합니다. 이를 통해 중복 데이터를 방지할 수 있습니다.

💾 인덱스의 저장

인덱스는 테이블을 저장하는 데 필요한 디스크 공간보다 적은 공간을 차지하며, 보통 키 필드만 포함합니다.
MySQL에서는 인덱스를 MYI (MySQL Index) 파일에 저장합니다. 🗂️

⚠️ 인덱스의 문제점

📏 인덱스 크기 문제:
- 인덱스를 과도하게 만들거나 불필요한 인덱스를 생성하면 인덱스 자체의 크기가 커져 성능에 악영향을 미칠 수 있습니다. 이는 마치 책의 모든 페이지에 단어를 찾아본 것처럼, 인덱스가 본문보다 커지는 상황과 비슷합니다.
⏳ 성능 저하:
- 불필요한 인덱스가 있으면 전체 테이블을 검색하는 것이 오히려 더 빠를 수 있습니다. 이는 인덱스가 비효율적으로 동작하기 때문에 발생합니다.
💽 추가적인 디스크 공간 필요:
- 인덱스는 테이블 외에 별도의 저장 공간을 차지합니다. 보통 인덱스는 데이터베이스 크기의 약 10% 정도를 추가로 차지할 수 있습니다.
⏱️ 인덱스 생성 시간 소요:
- 인덱스를 처음 생성할 때 특히 데이터가 많은 테이블에서 시간이 많이 소요될 수 있습니다. 많은 양의 데이터를 가진 테이블에 인덱스를 생성하는 작업은 긴 시간이 걸릴 수 있습니다.
🔄 데이터 변경 작업에서의 성능 저하:
- 테이블에서 INSERT, UPDATE, DELETE와 같은 데이터 변경 작업이 자주 발생하면 인덱스가 계속 갱신되어야 합니다. 이로 인해 추가적인 작업 부하가 발생해 성능이 저하될 수 있습니다.
- 예를 들어, 데이터가 삽입되거나 수정될 때마다 인덱스를 재정렬하거나 업데이트해야 하므로 작업 부하가 증가합니다.

🗂️ Index의 종류

🔗 클러스터형 인덱스 (Clustered Index)

클러스터형 인덱스는 데이터를 특정 기준에 맞춰 물리적으로 정렬하여 저장하는 인덱스입니다. 이 인덱스는 테이블의 레코드 순서를 기준으로 데이터가 재정렬되기 때문에, 일반적인 인덱스와는 다른 특징을 가집니다.

특징 및 설명

📐 정렬된 데이터 저장:
- 클러스터형 인덱스는 특정 열을 기준으로 테이블의 모든 데이터를 정렬합니다. 예를 들어, 영어 사전처럼 데이터를 물리적으로 정렬해 검색을 빠르게 할 수 있습니다.
- 예시: 학생 테이블에서 학번 컬럼에 클러스터형 인덱스를 생성하면, 테이블의 모든 레코드가 학번 순으로 정렬됩니다.
⚙️ 대용량 데이터의 성능 이슈:
- 대용량 데이터에서 클러스터형 인덱스를 생성하면, 데이터를 다시 정렬해야 하기 때문에 성능 부하가 발생할 수 있습니다. 따라서 주의가 필요합니다.
🔒 테이블당 하나의 클러스터형 인덱스만 가능:
- 클러스터형 인덱스는 테이블당 하나만 생성할 수 있습니다. 왜냐하면 데이터 자체가 인덱스 기준으로 물리적으로 정렬되기 때문입니다.
⏩ 검색 속도:
- 클러스터형 인덱스는 데이터를 정렬된 상태로 유지하기 때문에 검색 속도가 빠릅니다. 하지만, 데이터 입력, 수정, 삭제 작업에서는 추가적인 작업이 필요해 성능이 상대적으로 느릴 수 있습니다.
🛠️ MySQL의 클러스터형 인덱스:
- MySQL에서는 기본적으로 Primary Key가 클러스터형 인덱스로 지정됩니다. 만약 Primary Key가 없다면, 고유하고 NOT NULL 조건을 가진 컬럼이 클러스터형 인덱스로 사용됩니다.

📌 Tip: 클러스터형 인덱스는 자주 조회되는 데이터와 변경 작업이 적은 테이블에 적합합니다.

🔧 보조 인덱스 (Secondary Index)

보조 인덱스는 클러스터형 인덱스와 달리, 데이터를 물리적으로 정렬하지 않고 별도의 인덱스 페이지에 정렬 정보를 저장해 검색을 빠르게 하는 인덱스입니다. 이는 후보키(Candidate Key)에 부여할 수 있으며, 기본키 외에 유일성을 가지는 키들에 적용할 수 있습니다.

보조 인덱스의 특징

📄 데이터 페이지와 별도의 인덱스 페이지:
- 보조 인덱스는 데이터를 변경하지 않고 별도의 인덱스 페이지에 저장합니다. 즉, 본문의 내용을 그대로 두고 색인(찾아보기)만 추가하는 것과 유사합니다.
📍 데이터 위치의 주소값 (RID):
- 보조 인덱스는 데이터의 실제 값 대신 RID(Record Identifier, 레코드 식별자)를 저장하여, 데이터를 빠르게 찾을 수 있습니다.
⚡ 성능:
- 보조 인덱스는 검색 속도가 클러스터형 인덱스보다 느리지만, 데이터 입력, 수정, 삭제 시 부하가 적습니다. 이는 데이터가 물리적으로 재정렬되지 않기 때문입니다.
🌀 여러 개의 보조 인덱스 생성 가능:
- 여러 개의 보조 인덱스를 생성할 수 있어 다양한 컬럼에서 검색 성능을 향상시킬 수 있습니다. 하지만, 너무 많은 보조 인덱스를 생성하면 성능 저하가 발생할 수 있으므로 균형을 유지하는 것이 중요합니다.

예시

-- 기본 테이블 생성
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

-- 클러스터형 인덱스는 Primary Key로 생성됨 (employee_id)
-- 보조 인덱스 생성 (이름으로 검색할 때 빠르게 검색되도록 인덱스 추가)
CREATE INDEX idx_employee_name ON employees (name);

-- 보조 인덱스 생성 (부서 ID로 검색할 때 빠르게 검색되도록 인덱스 추가)
CREATE INDEX idx_department ON employees (department_id);

이 예시에서는 name과 department_id에 각각 보조 인덱스를 추가하여, 해당 컬럼을 기준으로 검색할 때 검색 성능을 향상시킬 수 있습니다. 🛠️

이 내용을 좀 더 다듬고 가독성을 높여서 정리해 드릴게요! 😊

🏷️ 제약 조건: Index 결정

1. Primary Key와 Unique Key가 모두 있는 경우

Primary Key와 Unique Key가 함께 존재하는 테이블에서 인덱스는 다음과 같이 설정됩니다:
- Primary Key로 지정된 컬럼은 클러스터형 인덱스 (Clustered Index)로 설정됩니다.
  - 클러스터형 인덱스는 테이블의 데이터를 물리적으로 정렬하여 빠르게 검색할 수 있도록 도와줍니다. 📐
- Unique Key로 지정된 컬럼은 보조 인덱스 (Secondary Index)로 설정됩니다.
  - 보조 인덱스는 데이터를 물리적으로 정렬하지 않고 별도의 인덱스 페이지에 저장해 관리합니다. 🔍
- Unique Key는 NULL 값의 허용 여부와 상관없이 보조 인덱스로 설정됩니다.
  - Unique Key는 고유한 값을 보장하지만, 클러스터형 인덱스처럼 물리적인 데이터 정렬이 필요하지 않기 때문에 보조 인덱스가 됩니다.

2. Primary Key가 없는 경우

Primary Key가 없는 경우, Unique Key와 NOT NULL 조건에 따라 인덱스가 결정됩니다:
- Unique Key + NOT NULL 조건이 있을 경우:
  - 이 컬럼은 클러스터형 인덱스가 됩니다. Primary Key가 없는 테이블에서 유일성과 NOT NULL 조건을 충족하는 컬럼이 데이터 정렬의 기준이 됩니다.
- Unique Key만 있는 경우 (NOT NULL 조건이 없음):
  - 이 경우 보조 인덱스로 설정됩니다. NOT NULL 조건이 없으면 데이터의 물리적 정렬이 필요하지 않아 보조 인덱스로 처리됩니다.

💡 예시

Primary Key와 Unique Key가 모두 있는 테이블:

CREATE TABLE users (
    user_id INT PRIMARY KEY,         -- Primary Key로 클러스터형 인덱스
    email VARCHAR(100) UNIQUE,       -- Unique Key로 보조 인덱스
    username VARCHAR(50)
);

Primary Key가 없는 테이블에서 UNIQUE + NOT NULL 조건:

CREATE TABLE products (
    product_code VARCHAR(100) UNIQUE NOT NULL,  -- 클러스터형 인덱스
    product_name VARCHAR(255),
    price DECIMAL(10, 2)
);

Primary Key가 없고 UNIQUE만 있는 경우:

CREATE TABLE departments (
    department_code VARCHAR(50) UNIQUE,  -- 보조 인덱스 (NOT NULL 조건 없음)
    department_name VARCHAR(100)
);

🛠️ 인덱스를 생성할 때 고려해야 할 사항

데이터베이스에서 인덱스 생성과 관리는 성능을 좌우하는 중요한 요소입니다. 다음은 인덱스를 생성할 때 꼭 고려해야 할 사항들입니다.

1. 인덱스는 특정 열(Column)에 생성

인덱스는 테이블 전체가 아닌, 특정 열에 대해 생성됩니다. 이를 통해 해당 열에 대해 데이터를 더 빠르게 검색할 수 있습니다.
예시: 학생 테이블에서 학번이나 이름에 인덱스를 생성하면, 이 컬럼을 기준으로 데이터를 빠르게 검색할 수 있습니다.

2. WHERE 절에서 자주 사용되는 열에 인덱스 생성

WHERE 절에서 자주 사용되는 열에 인덱스를 생성하면 성능이 향상됩니다. 이는 데이터베이스가 WHERE 조건에 따라 데이터를 자주 검색하기 때문입니다.
예시:
```
SELECT * FROM students WHERE student_id = 123;
```
이 쿼리에서 student_id에 인덱스를 생성하면 검색 속도가 크게 향상됩니다.

3. WHERE 절에 사용되더라도 자주 사용해야 인덱스가 유용

WHERE 절에서 사용된다고 해서 모두 인덱스를 생성하는 것은 비효율적입니다. 자주 사용되는 열에만 인덱스를 생성해야 인덱스 생성의 효과를 볼 수 있습니다.

4. 데이터 중복도가 높은 열에 인덱스 생성은 효과가 적음

중복도가 높은 열(즉, 값이 많이 중복되는 열)에 인덱스를 생성해도 성능 향상 효과가 적습니다. 중복도가 낮은 열에 인덱스를 생성하는 것이 좋습니다.
중복도가 낮은 열의 예: 주민등록번호, 학번 등 고유한 값들이 많은 열.

5. 외래키(Foreign Key)에 자동으로 인덱스 생성

외래키로 설정된 열에는 데이터베이스가 자동으로 인덱스를 생성합니다. 이는 외래키가 자주 조인 연산에 사용되기 때문에 성능을 향상시키기 위한 조치입니다.

예시:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

이 예시에서 customer_id에 인덱스가 자동으로 생성됩니다.

6. 조인에 자주 사용되는 열에 인덱스 생성

테이블 간 조인 연산에 자주 사용되는 열에 인덱스를 생성하면, 조인 시 성능이 크게 향상됩니다.
예시:
```
SELECT * FROM students s
JOIN courses c ON s.student_id = c.student_id;
```
이 쿼리에서 student_id에 인덱스를 생성하면 조인 성능이 향상됩니다.

7. 데이터 변경 작업(삽입, 수정, 삭제)이 자주 발생하는 경우 주의

데이터 변경 작업(삽입, 수정, 삭제)이 자주 발생하면, 인덱스가 계속 갱신되어야 하기 때문에 성능 저하가 발생할 수 있습니다. 인덱스를 너무 많이 생성하면, 이러한 작업이 잦을 때 성능이 저하될 수 있습니다.
변경 작업이 자주 일어나는 테이블에서는 신중하게 인덱스를 설계해야 합니다.

8. 클러스터형 인덱스는 테이블당 하나만 생성 가능

클러스터형 인덱스는 테이블 데이터를 물리적으로 정렬하는 인덱스이므로, 한 테이블에 하나의 기준으로만 생성할 수 있습니다.

예시:

CREATE TABLE students (
    student_id INT PRIMARY KEY,  -- 클러스터형 인덱스
    name VARCHAR(100)
);

이 경우, 테이블의 모든 데이터는 student_id 기준으로 물리적으로 정렬됩니다.

9. 사용하지 않는 인덱스는 제거

사용되지 않거나 자주 사용되지 않는 인덱스는 불필요하게 성능을 저하시킬 수 있습니다. 이런 인덱스는 인덱스 관리 비용이 크므로, 사용하지 않는 인덱스는 주기적으로 제거하는 것이 좋습니다.
예시: 테이블을 설계할 때 잘못 생성된 인덱스나, 현재 사용되지 않는 인덱스는 성능에 불리하므로 주기적으로 확인하여 제거해야 합니다.

⚙️ 자동 INDEX

MySQL에서는 Primary Key나 Unique Key와 같은 제약 조건에 따라 인덱스가 자동으로 생성됩니다. 여기서는 다양한 상황에서 인덱스가 자동으로 생성되는 예시들을 보여드리겠습니다.

예시 1: Primary Key가 있는 테이블

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    hire_date DATE
);

클러스터형 인덱스: emp_id는 Primary Key로 지정되어 있으며, MySQL에서는 Primary Key에 대해 자동으로 클러스터형 인덱스가 생성됩니다. 테이블의 모든 데이터는 emp_id를 기준으로 물리적으로 정렬됩니다.
보조 인덱스: name, department, hire_date 같은 다른 컬럼들에는 현재 인덱스가 없지만, 필요에 따라 추가적인 인덱스를 설정하면 보조 인덱스로 생성됩니다.

예시 2: UNIQUE Key가 있는 테이블

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_code VARCHAR(50) UNIQUE,
    price DECIMAL(10, 2)
);

클러스터형 인덱스: product_id가 Primary Key이기 때문에 클러스터형 인덱스가 자동 생성됩니다. 테이블의 데이터는 product_id를 기준으로 정렬됩니다.
보조 인덱스: product_code에 UNIQUE 제약 조건이 붙어 있어 자동으로 보조 인덱스가 생성됩니다. 이는 고유성을 보장하며, 검색 속도를 높이는 역할을 합니다. 단, 보조 인덱스는 데이터의 물리적 정렬에는 영향을 주지 않습니다.

예시 3: Primary Key 없이 UNIQUE Key만 있는 경우

CREATE TABLE users (
    email VARCHAR(100) UNIQUE NOT NULL,
    username VARCHAR(50),
    join_date DATE
);

클러스터형 인덱스: 이 테이블에는 Primary Key가 없으며, email이 UNIQUE와 NOT NULL 조건이 설정되어 있어, email 컬럼에 대해 클러스터형 인덱스가 자동으로 생성됩니다. 테이블의 데이터는 email 기준으로 물리적으로 정렬됩니다.
보조 인덱스: username이나 join_date에는 인덱스가 생성되지 않지만, 필요에 따라 보조 인덱스를 추가할 수 있습니다.

예시 4: Primary Key와 여러 보조 인덱스가 있는 경우

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    UNIQUE (product_id)
);

클러스터형 인덱스: order_id는 Primary Key로 지정되어 있어 클러스터형 인덱스가 자동으로 생성됩니다. 테이블의 데이터는 order_id를 기준으로 물리적으로 정렬됩니다.
보조 인덱스: product_id는 UNIQUE 제약 조건이 설정되어 있어 보조 인덱스가 자동 생성됩니다. 추가적으로 customer_id나 order_date에도 보조 인덱스를 생성할 수 있습니다.

📋 클러스터형 인덱스와 보조 인덱스의 자동 생성과 정렬

클러스터형 인덱스의 자동 생성:
- Primary Key가 설정된 테이블은 자동으로 해당 컬럼에 대해 클러스터형 인덱스가 생성됩니다. 이 인덱스는 테이블의 데이터를 물리적으로 정렬하며, 테이블당 하나만 생성할 수 있습니다.
- 또한, UNIQUE + NOT NULL이 설정된 컬럼이 있을 경우, Primary Key가 없는 테이블에서는 해당 컬럼에 대해 클러스터형 인덱스가 자동으로 생성됩니다.
보조 인덱스의 자동 생성:
- UNIQUE 제약 조건이 설정된 컬럼은 자동으로 보조 인덱스가 생성됩니다. 보조 인덱스는 고유성을 보장하면서 해당 컬럼에 대한 검색 속도를 높이는 역할을 합니다.
- 보조 인덱스는 데이터를 물리적으로 정렬하지 않으며, 별도의 인덱스 페이지를 유지하여 검색에 도움을 줍니다.
정렬과 성능:
- 클러스터형 인덱스는 데이터를 물리적으로 정렬하여 검색 성능을 향상시키지만, 데이터 삽입, 삭제, 수정 시 성능 부하가 발생할 수 있습니다.
- 보조 인덱스는 물리적 정렬을 하지 않기 때문에 데이터 변경 작업의 성능 부하가 적으나, 검색 성능은 클러스터형 인덱스보다 약간 떨어질 수 있습니다.

📊 인덱스 생성, 활용, 삭제에 대한 설명과 예시

데이터베이스에서 인덱스는 검색 성능을 향상시키기 위한 중요한 도구입니다. 여기서는 인덱스의 생성, 활용, 삭제 방법을 구체적인 예시와 함께 설명하겠습니다.

1. 인덱스 생성

🔧 기본 인덱스 생성 문법

CREATE [UNIQUE] INDEX 인덱스이름 ON 테이블이름 (열이름) [ASC|DESC];

UNIQUE 옵션: 열의 값이 고유해야 하는 경우 사용하며, 중복된 값이 있으면 오류가 발생합니다.
ASC/DESC: 데이터를 오름차순(ASC) 또는 내림차순(DESC)으로 정렬할 수 있습니다.
기본적으로 B-Tree 인덱스가 사용됩니다.

예시

기본 보조 인덱스 생성
```
CREATE INDEX idx_emp_name ON employees (name);
```
- employees 테이블의 name 열에 대해 보조 인덱스를 생성합니다. idx_emp_name이라는 이름의 인덱스가 만들어지며, name을 기준으로 빠르게 검색할 수 있게 해줍니다.
고유 인덱스 생성
```
CREATE UNIQUE INDEX idx_emp_email ON employees (email);
```
- email 열에 대해 고유 인덱스를 생성하여 중복된 이메일을 방지하고, 검색 성능을 높입니다.
여러 열을 조합한 인덱스 생성
```
CREATE INDEX idx_emp_name_dept ON employees (name, department);
```
- name과 department 두 열을 조합한 인덱스를 생성하여 두 열을 기준으로 검색 속도를 향상시킵니다.

2. 클러스터형 인덱스 생성

클러스터형 인덱스는 주로 Primary Key에 대해 자동으로 생성됩니다. 한 테이블에 하나만 생성될 수 있으며, 물리적으로 데이터를 정렬합니다.
클러스터형 인덱스를 생성하려면 ALTER TABLE을 사용해야 합니다.

예시

ALTER TABLE employees MODIFY COLUMN emp_id INT PRIMARY KEY;

emp_id를 Primary Key로 설정하여 클러스터형 인덱스를 생성합니다. 이로 인해 테이블 데이터는 emp_id 기준으로 물리적으로 정렬됩니다.

3. 인덱스 조회

생성된 인덱스는 SHOW INDEX 명령어를 통해 조회할 수 있습니다.

SHOW INDEX FROM employees;

이 명령어는 employees 테이블에 생성된 모든 인덱스(클러스터형 인덱스와 보조 인덱스 포함)를 보여줍니다.

4. 인덱스를 활용한 SELECT 쿼리

인덱스는 WHERE 절에서 데이터를 검색할 때 큰 성능 향상을 제공합니다.

예시

클러스터형 인덱스 참조
```
SELECT * FROM employees WHERE emp_id = 123;
```
- emp_id는 Primary Key로 클러스터형 인덱스를 사용하여 매우 빠르게 검색할 수 있습니다.
보조 인덱스 참조
```
SELECT * FROM employees WHERE name = 'John' AND department = 'HR';
```
- name과 department 열에 설정된 보조 인덱스를 활용해 빠르게 데이터를 검색합니다.

5. 인덱스 삭제

필요하지 않은 인덱스는 삭제하여 데이터베이스 성능을 최적화할 수 있습니다.

예시

보조 인덱스 삭제
```
DROP INDEX idx_emp_name ON employees;
```
- employees 테이블의 name 열에 대해 생성된 보조 인덱스 idx_emp_name을 삭제합니다.
클러스터형 인덱스 삭제 (Primary Key 삭제)
```
ALTER TABLE employees DROP PRIMARY KEY;
```
- employees 테이블의 Primary Key를 삭제하여 클러스터형 인덱스를 제거합니다.

📄 VIEW란 무엇인가?

VIEW는 데이터베이스에서 가상의 테이블을 말하며, 물리적으로 데이터를 저장하지 않지만, 실제 테이블처럼 사용할 수 있습니다. VIEW는 기본 테이블을 기반으로 생성되며, 여러 테이블을 조인하거나 서브쿼리를 단순화하는 데 유용합니다. 아래는 VIEW의 주요 특징과 장단점에 대한 설명입니다.

📌 VIEW의 특징

가상의 테이블:
- VIEW는 데이터베이스 내에 존재하는 가상의 테이블입니다. 기본 테이블의 데이터를 참조하지만, 데이터를 저장하지는 않습니다.
데이터 저장 없음:
- VIEW는 데이터를 물리적으로 저장하지 않고, 기본 테이블의 데이터를 참조하여 필요한 시점에 SELECT 문을 통해 데이터를 보여줍니다.
복잡한 쿼리의 단순화:
- 복잡한 SQL 쿼리(예: JOIN, 서브쿼리 등)를 VIEW로 정의해 두면, 간단한 SELECT 문으로 복잡한 작업을 반복하지 않고도 사용할 수 있습니다.
MySQL에서의 VIEW:
- MySQL에서 VIEW는 단순히 기본 테이블의 데이터를 참조하는 역할을 합니다. SELECT 문이 실행될 때마다 기본 테이블의 데이터를 보여줍니다.
VIEW와 테이블의 차이점:
- 테이블(Table)은 실제 데이터를 저장하는 반면, VIEW는 데이터 자체가 아니라, SELECT 문을 저장하는 개념입니다. 즉, VIEW는 데이터베이스 구조를 단순화하고 유지보수를 용이하게 만듭니다.

💡 VIEW의 장점

보안성 향상:
- 특정 사용자가 전체 테이블에 접근하지 않도록 하고, 필요한 정보만 SELECT할 수 있도록 VIEW를 제공하여 보안성을 강화할 수 있습니다.
- 예: 고객 테이블에서 이름과 주소만 보여주는 VIEW를 생성하여 민감한 정보를 숨길 수 있습니다.
권한 관리:
- 사용자가 기본 테이블에 직접 접근하지 않고 VIEW를 통해서만 데이터에 접근하도록 설정할 수 있습니다. 이를 통해 테이블에 대한 권한 관리를 세분화할 수 있습니다.
복잡한 쿼리의 단순화:
- 자주 사용하는 복잡한 쿼리를 VIEW로 정의하여 반복적인 SQL 작성을 피하고, 간단한 SELECT 문으로 사용할 수 있습니다.
쿼리 재사용성:
- 복잡한 쿼리를 VIEW로 저장해두면, 다른 곳에서 간단히 재사용할 수 있어 쿼리의 유지보수가 용이합니다.
데이터 조회 방식 다양화:
- VIEW를 사용하면 데이터를 다양한 방식으로 재구성하여 조회할 수 있습니다. 예를 들어, 동일한 데이터를 다양한 필터나 조건으로 조회할 수 있습니다.
기본 테이블의 데이터 구조에 영향 없음:
- VIEW는 기본 테이블의 구조를 변경하지 않고도 데이터를 다양한 방식으로 보여줄 수 있어 유연한 데이터 처리가 가능합니다.

⚠️ VIEW의 단점

삽입, 삭제, 갱신의 제한:
- VIEW는 주로 데이터 조회를 위해 사용되며, 삽입, 삭제, 갱신 작업은 제한적입니다. 특히, 복잡한 조인이나 여러 테이블을 참조하는 VIEW에서는 데이터 변경이 거의 불가능합니다.
인덱스 제한:
- VIEW는 자체적으로 인덱스를 생성할 수 없습니다. 기본 테이블의 인덱스를 사용할 수 있지만, VIEW에 인덱스를 설정할 수 없기 때문에 성능 최적화가 제한적일 수 있습니다.

🔄 VIEW 데이터 변경

VIEW의 데이터는 기본 테이블에 종속되어 있습니다. 따라서, 기본 테이블의 데이터가 변경되면 VIEW도 자동으로 갱신됩니다.
예를 들어, 기본 테이블에 새로운 데이터를 삽입하거나 수정하면 VIEW도 해당 데이터를 자동으로 최신 상태로 보여줍니다. 이는 VIEW가 SELECT 문을 실행할 때마다 최신 데이터를 조회하기 때문입니다.

📊 VIEW 생성, 대체, 수정, 삭제에 대한 설명과 예시

VIEW는 가상의 테이블로, 복잡한 쿼리를 단순화하고 특정 사용자에게 제한된 데이터를 보여주기 위해 사용됩니다. 여기서는 VIEW의 생성, 대체, 수정, 삭제 방법에 대해 설명하고 예시를 제공하겠습니다.

1. VIEW 생성

VIEW는 SELECT 문을 기반으로 생성되며, 기본 테이블의 데이터를 가상으로 조회할 수 있습니다. VIEW는 원본 테이블과 같은 이름을 사용할 수 없으며, 가상의 테이블로서 데이터를 직접 저장하지 않습니다.

예시

-- employees 테이블에서 HR 부서만 조회하는 VIEW 생성
CREATE VIEW employee_view AS 
SELECT emp_id, name, department 
FROM employees 
WHERE department = 'HR';

이 예시는 employees 테이블에서 HR 부서의 직원 정보만을 조회하는 employee_view라는 VIEW를 생성하는 것입니다.
VIEW는 테이블처럼 사용할 수 있지만, 실제 데이터를 저장하지 않고 매번 SELECT 문을 통해 데이터를 가져옵니다.

2. VIEW 대체 (REPLACE)

기존의 VIEW를 수정할 필요가 있을 때, CREATE OR REPLACE 문을 사용하면 기존 VIEW를 덮어쓰고 새롭게 대체할 수 있습니다. 이는 기존 VIEW를 삭제하지 않고, 자동으로 업데이트하는 방식입니다.

예시

-- employee_view에 hire_date 필드를 추가하여 VIEW 대체
CREATE OR REPLACE VIEW employee_view AS 
SELECT emp_id, name, department, hire_date
FROM employees 
WHERE department = 'HR';

기존의 employee_view에서 hire_date 필드를 추가하여, 새로운 VIEW로 대체하였습니다.
이 방법은 기존 VIEW를 수정하고 싶을 때 유용하며, 덮어쓰기 방식으로 적용됩니다.

3. VIEW 수정 (ALTER)

VIEW의 SELECT 문을 변경하고 싶다면, ALTER 문을 사용하여 수정할 수 있습니다. 하지만, SELECT 문의 변경에는 주로 CREATE OR REPLACE 방식이 사용됩니다.

예시

-- employee_view를 수정하여 hire_date가 2020년 이후인 데이터만 조회
ALTER VIEW employee_view AS 
SELECT emp_id, name, department, hire_date
FROM employees 
WHERE hire_date > '2020-01-01';

employee_view를 수정하여 2020년 1월 1일 이후에 입사한 직원들만 조회하도록 변경한 예시입니다.
CREATE OR REPLACE 방식으로 VIEW를 수정하는 것이 더 일반적입니다.

4. VIEW 삭제

VIEW가 더 이상 필요하지 않을 때는 DROP 문을 사용해 삭제할 수 있습니다. VIEW를 삭제해도 기본 테이블이나 데이터에는 영향을 미치지 않습니다.

예시

-- employee_view라는 VIEW 삭제
DROP VIEW employee_view;

이 명령은 employee_view를 삭제합니다. 삭제하더라도 기본 테이블인 employees와 그 데이터에는 아무런 영향을 주지 않습니다.

📊 View의 종류 및 설명

VIEW는 데이터를 조회하는 가상의 테이블로, 사용 목적에 따라 단순 뷰, 복합 뷰, 인라인 뷰로 나뉩니다. 각 VIEW는 데이터 조회 방식과 활용 목적에 따라 다양한 특징을 가지고 있습니다. 아래는 각 뷰의 설명과 예시입니다.

1. 단순 뷰 (Simple View)

단순 뷰는 하나의 테이블을 기반으로 생성되며, 주로 단일 테이블의 데이터를 조회할 때 사용됩니다.

특징

하나의 테이블만을 참조합니다.
그룹 함수(SUM, COUNT 등)와 DISTINCT를 사용할 수 없습니다.
DML (Data Manipulation Language) 작업이 가능합니다 (예: INSERT, UPDATE, DELETE).
DML 작업 제한 사항:
- NOT NULL 제약이 있는 컬럼이 VIEW에 포함되지 않은 경우.
- 산술 표현식이 포함된 컬럼이 있는 경우.
- DISTINCT, GROUP BY 또는 그룹 함수가 사용된 경우.

예시

-- 단순 뷰 생성
CREATE VIEW emp_view AS
SELECT emp_id, name, department
FROM employees;

employees 테이블에서 emp_id, name, department 컬럼만을 조회하는 단순 뷰입니다.
이 뷰에서는 DML 작업이 가능합니다.

2. 복합 뷰 (Complex View)

복합 뷰는 여러 테이블을 조인하거나 그룹 함수를 사용하여 생성된 VIEW입니다. 복잡한 데이터 조회가 필요한 상황에서 자주 사용됩니다.

특징

여러 테이블을 참조할 수 있습니다.
그룹 함수(SUM, AVG, MAX 등)를 사용할 수 있습니다.
DISTINCT 구문을 사용할 수 있습니다.
DML 작업이 가능하지만, 일부 제한 사항이 있을 수 있습니다.

예시

-- 복합 뷰 생성
CREATE VIEW emp_dept_view AS
SELECT e.emp_id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

employees와 departments 두 테이블을 조인하여 emp_id, name, department_name을 조회하는 복합 뷰입니다.
여러 테이블에서 데이터를 조회하는 데 사용됩니다.

3. 인라인 뷰 (Inline View)

인라인 뷰는 서브쿼리가 FROM 절에 포함된 형태로, 쿼리 내에서 임시로 사용되는 가상의 테이블입니다. 복잡한 쿼리 작성 시 데이터를 효율적으로 조회할 수 있습니다.

특징

서브쿼리가 FROM 절에 포함됩니다.
테이블 대신 서브쿼리를 기반으로 데이터를 조회합니다.
한 번의 쿼리에서만 임시로 사용됩니다.

예시

-- 인라인 뷰 사용
SELECT emp_id, name, department
FROM (
    SELECT emp_id, name, department_id
    FROM employees
    WHERE salary > 5000
) AS emp_subquery
JOIN departments d ON emp_subquery.department_id = d.department_id;

employees 테이블에서 급여가 5000 이상인 직원을 조회하는 서브쿼리를 인라인 뷰로 사용하여, 그 결과를 departments 테이블과 조인하는 예시입니다.
임시로 데이터를 조회하고, 그 결과를 다른 테이블과 결합하여 활용합니다.

김상욱

이전 포스트

[코딩테스트][백준] 🔥 백준 2931번 "가스관" 문제: Python으로 완벽 해결하기! 🔥

다음 포스트