물리적 모델링은 데이터베이스 설계의 중요한 단계로, 데이터가 실제로 저장되는 방식을 정의합니다. 이 과정에서는 각 컬럼의 데이터 타입을 정의하고, 제약 조건을 설정하며, 성능 향상을 위해 인덱스를 사용합니다. 이번 글에서는 물리적 모델링의 주요 요소들을 상세히 설명하겠습니다.
데이터 타입 정리
각 컬럼에 적절한 데이터 타입을 설정하는 일은 매우 중요합니다. 데이터 타입을 잘 설정하면 저장 용량을 효율적으로 활용할 수 있고, 많은 row가 있을 때 성능에도 큰 영향을 미칩니다. 여기서는 MySQL에서 자주 사용하는 데이터 타입들을 살펴보겠습니다.
1. Numeric Types (숫자형 타입)
숫자를 저장하기 위해 사용되는 데이터 타입입니다. 숫자형 타입은 정수형 타입과 실수형 타입으로 나눌 수 있습니다.
(1) 정수형 타입
정수값을 저장하는 타입입니다. 범위에 따라 여러 가지 타입이 있습니다.
- TINYINT: 작은 범위의 정수를 저장.
-128~127(SIGNED),0~255(UNSIGNED). - SMALLINT: 중간 범위의 정수를 저장.
-32768~32767(SIGNED),0~65535(UNSIGNED). - MEDIUMINT: 큰 범위의 정수를 저장.
-8388608~8388607(SIGNED),0~16777215(UNSIGNED). - INT: 더 큰 범위의 정수를 저장.
-2147483648~2147483647(SIGNED),0~4294967295(UNSIGNED). - BIGINT: 매우 큰 범위의 정수를 저장.
-9223372036854775808~9223372036854775807(SIGNED),0~18446744073709551615(UNSIGNED).
(2) 실수형 타입
소수점을 포함한 수를 저장하는 타입입니다. 실수형 타입은 수의 범위와 소수점 이하 자릿수의 정밀도에 따라 나뉩니다.
- DECIMAL(M, D): 일반적으로 자주 사용되는 실수형 타입.
M은 전체 자릿수,D는 소수점 이하 자릿수를 나타냄. 예:DECIMAL(5, 2)는-999.99~999.99까지의 실수를 저장. - FLOAT:
-3.402823466E+38~3.402823466E+38범위의 실수. - DOUBLE:
-1.7976931348623157E+308~1.7976931348623157E+308범위의 실수. FLOAT보다 더 넓은 범위와 정밀도를 제공.
2. Date and Time Types (날짜 및 시간 타입)
날짜 및 시간 정보를 저장하는 타입입니다.
- DATE: 날짜를 저장. 형식:
YYYY-MM-DD. - DATETIME: 날짜와 시간을 저장. 형식:
YYYY-MM-DD HH:MM:SS. - TIMESTAMP: 날짜와 시간을 저장하며, 타임존 정보를 포함. 형식:
YYYY-MM-DD HH:MM:SS. 타임존에 따라 값이 자동으로 조정됨. - TIME: 시간을 저장. 형식:
HH:MM:SS.
3. 문자열 타입 (String Types)
문자열을 저장하기 위한 데이터 타입입니다.
- CHAR(n): 고정 길이 문자열. 최대 255자.
- VARCHAR(n): 가변 길이 문자열. 최대 65,535자. 저장 용량이 실제 값의 길이에 맞게 최적화됨.
- TEXT: 최대 65,535자의 문자열. 긴 문자열을 저장할 때 사용. MEDIUMTEXT, LONGTEXT도 있음.
제약 조건 (Constraints)
제약 조건은 데이터의 정확성과 일관성을 유지하기 위해 특정 조건을 만족해야 하는 규칙입니다. 예를 들어, 성별 컬럼은 'M' 또는 'F'만 허용한다거나, 이메일 컬럼에는 반드시 '@'가 포함되어야 한다는 식의 조건이 있습니다.
주요 제약 조건
- PRIMARY KEY: 유일하게 식별되는 컬럼. NULL 불가. PRIMARY KEY는 각 테이블에 하나만 존재할 수 있으며, 데이터의 고유성을 보장합니다.
- FOREIGN KEY: 다른 테이블의 PRIMARY KEY를 참조하는 컬럼. 참조 무결성을 유지. FOREIGN KEY는 두 테이블 간의 관계를 정의하며, 참조 무결성을 보장합니다. 예를 들어, 주문 테이블의
customer_id컬럼이 고객 테이블의id컬럼을 참조하는 경우입니다. - UNIQUE: 중복된 값을 허용하지 않는 컬럼. UNIQUE 제약 조건은 중복을 허용하지 않으며, 하나의 테이블에 여러 개의 UNIQUE 컬럼을 가질 수 있습니다.
- NOT NULL: NULL 값을 허용하지 않는 컬럼. NOT NULL 제약 조건은 반드시 값을 가져야 하며, NULL 값을 허용하지 않습니다.
- CHECK: 특정 조건을 만족하는 값을 허용. 예:
CHECK (age >= 18). CHECK 제약 조건은 데이터가 특정 조건을 만족하는지 확인합니다. 예를 들어, 나이 컬럼에 대해CHECK (age >= 0)를 설정하면 나이는 항상 0 이상이어야 합니다.
제약 조건은 테이블을 생성하거나 수정할 때 SQL 명령어를 통해 설정할 수 있습니다. 예를 들어, CREATE TABLE 문에서 제약 조건을 설정할 수 있습니다.
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE,
age INT CHECK (age >= 0),
gender CHAR(1) CHECK (gender IN ('M', 'F'))
);인덱스 (Indexes)
인덱스는 데이터를 빠르게 검색할 수 있도록 도와주는 구조입니다. 인덱스를 사용하면 데이터베이스가 특정 컬럼에서 원하는 데이터를 더 효율적으로 찾을 수 있습니다. 인덱스는 검색 성능을 향상시키는 데 중요한 역할을 합니다.
인덱스의 종류
(1) Clustered 인덱스
데이터를 물리적으로 정렬하여 저장하는 인덱스입니다. 하나의 테이블에 하나만 생성할 수 있습니다. PRIMARY KEY가 자동으로 Clustered 인덱스가 됩니다.
- 장점: 데이터 검색 속도가 빠름. 데이터가 물리적으로 정렬되어 있어 검색 성능이 뛰어납니다.
- 단점: 데이터 삽입, 수정, 삭제 시 성능 저하 가능. 데이터의 물리적 순서를 유지해야 하므로, 빈번한 변경 작업이 있을 경우 성능 저하가 발생할 수 있습니다.
(2) Non-Clustered 인덱스
데이터의 물리적 순서를 변경하지 않고 별도의 인덱스 구조에 정렬 정보를 저장합니다. 여러 개의 인덱스를 생성할 수 있습니다.
- 장점: 여러 컬럼에 대해 인덱스를 생성할 수 있음. Non-Clustered 인덱스는 실제 데이터와는 별도로 저장되므로, 다양한 컬럼에 대해 인덱스를 만들 수 있습니다.
- 단점: 검색 속도가 Clustered 인덱스보다 느림. 데이터가 물리적으로 정렬되어 있지 않기 때문에, 검색 속도가 상대적으로 느릴 수 있습니다.
인덱스 사용 예시
예를 들어, 이메일 컬럼에 대해 인덱스를 생성하면 다음과 같습니다.
CREATE INDEX idx_email ON users(email);이렇게 하면 이메일로 사용자를 검색할 때 더 빠르게 결과를 얻을 수 있습니다.
인덱스 중복되는 값들
지금까지 인덱스 예시에서는 중복되는 값이 없는 경우만 살펴봤습니다. 그러나 컬럼에 중복되는 값들이 있어도 인덱스는 충분히 잘 작동할 수 있습니다.
책의 인덱스를 비유로 사용하면 이해하기 쉽습니다. 책을 생각해보면 특정 개념들이 무조건 한 페이지에만 나오지는 않습니다. 예를 들면 "단풍잎"이라는 개념이 여러 페이지에 소개될 수 있습니다. 이때 책의 인덱스에서는 이 모든 페이지를 저장합니다. 독자는 이 페이지들을 하나씩 돌면서 원하는 내용을 찾을 수 있습니다.
데이터베이스의 인덱스도 마찬가지입니다. 예를 들어, 다음과 같은 브랜드가 여러 개의 로우에 존재하는 테이블이 있다고 가정해보겠습니다.
| 브랜드 | 제품 ID |
|---|---|
| Nike | 1001 |
| Nike | 1002 |
| Adidas | 1003 |
| Adidas | 1004 |
| Nike | 1005 |
이때 특정 브랜드인 제품 로우들을 찾고 싶다면, 인덱스를 통해 해당 브랜드를 찾고, 가장 위에 있는 주소부터 시작해 하나씩 아래로 가면서 해당 로우들에 접근하면 됩니다. 예를 들어 브랜드가 "Nike"인 제품들을 찾고 싶다면, 제품 ID가 1001, 1002, 1005인 제품들을 순차적으로 접근할 수 있습니다.
만약 브랜드가 "Nike"면서, 다른 조건도 만족하는 로우를 찾고 싶다면 이 주소들 중에서 원하는 로우를 찾을 수 있습니다. 이 로우 주소들은 특정 순서로 정렬된 것이 아니기 때문에 브랜드가 "Nike"인 제품들 중에서 또 다른 조건을 만족하는 데이터를 찾고 싶을 때는 해당 제품들을 일일이 확인해보는 선형 탐색을 사용해야 합니다.
Composite 인덱스
인덱스는 단순히 하나의 컬럼이 아니라, 여러 개의 컬럼을 합쳐서도 만들 수 있습니다. 무슨 말인지 볼게요.
예를 들어, 쇼핑몰 데이터베이스에서 상품을 조회할 때 하나의 기준이 아니라 여러 개의 기준을 합쳐서 많이 한다고 가정해봅시다. 예를 들어 "브랜드"와 "카테고리", 즉 "Nike" 브랜드의 "운동화" 제품을 조회한다고 할 때, 각각 브랜드와 카테고리에 대해 따로 인덱스가 있다고 가정해봅시다.
| 브랜드 | 카테고리 | 제품 ID |
|---|---|---|
| Nike | 운동화 | 1001 |
| Nike | 운동화 | 1002 |
| Nike | 티셔츠 | 1003 |
| Adidas | 운동화 | 1004 |
| Adidas | 티셔츠 | 1005 |
| Nike | 운동화 | 1006 |
이 경우, 두 가지 방법이 있습니다:
- 특정 브랜드, "Nike"에 해당하는 로우들을 인덱스와 이진 탐색으로 찾고, 그 다음에 이 로우들에서 "운동화" 제품들을 선형 탐색으로 찾거나,
- "운동화" 제품인 로우들을 인덱스와 이진 탐색으로 찾고, 그 다음에 이 로우들에서 "Nike"인 제품을 선형 탐색으로 찾아야 합니다.
결국에는 선형 탐색으로 데이터를 찾아야 하는 경우가 생깁니다. 두 개 이상의 조건을 사용해서 조회를 많이 할 때는 여러 컬럼에 대한 인덱스를 만들 수 있습니다.
예를 들어, "브랜드"와 "카테고리" 두 컬럼에 대한 인덱스는 이렇게 만들 수 있습니다. 데이터가 브랜드로 정렬되어 있고, 같은 브랜드 내에서는 또 카테고리로 정렬됩니다.
| 브랜드 | 카테고리 | 제품 ID |
|---|---|---|
| Adidas | 운동화 | 1004 |
| Adidas | 티셔츠 | 1005 |
| Nike | 운동화 | 1001 |
| Nike | 운동화 | 1002 |
| Nike | 운동화 | 1006 |
| Nike | 티셔츠 | 1003 |
이 경우, 먼저 원하는 브랜드를 이진 탐색으로 찾고, 원하는 카테고리도 그 안에서 이진 탐색으로 찾을 수 있어 데이터 조회가 훨씬 빨라집니다.
"브랜드", "카테고리", "색상"에 대한 인덱스도 만들 수 있습니다.
| 브랜드 | 카테고리 | 색상 | 제품 ID |
|---|---|---|---|
| Adidas | 운동화 | 빨강 | 1004 |
| Adidas | 티셔츠 | 파랑 | 1005 |
| Nike | 운동화 | 검정 | 1001 |
| Nike | 운동화 | 흰색 | 1002 |
| Nike | 운동화 | 검정 | 1006 |
| Nike | 티셔츠 | 파랑 | 1003 |
이 경우, 원하는 브랜드를 이진 탐색으로, 카테고리도 이진 탐색으로, 색상도 이진 탐색으로 찾아낼 수 있습니다. 이 인덱스는 데이터 조회에 함께 많이 사용하는 컬럼들의 조합에도 사용할 수 있습니다.
그러나 주의할 점이 두 가지 있습니다:
- 개별 컬럼들에 인덱스를 추가하는 것과, 여러 컬럼들에 대한 인덱스를 추가하는 것은 두 개의 다른 인덱스입니다. 아무리 브랜드, 카테고리, 색상 컬럼들에 대한 인덱스를 만들어놔도, 브랜드, 카테고리, 색상 모두에 대한 인덱스와는 다릅니다.
- 여러 컬럼들에 대한 인덱스를 만들 때, 순서가 중요합니다. 예를 들어 브랜드, 카테고리, 색상에 대한 인덱스가 있다고 할 때, 이 인덱스는 브랜드와 카테고리에 대해서는 인덱스를 사용할 수 있지만, 색상에 대해서는 사용할 수 없습니다. 따라서 조건으로 가장 많이 사용하는 컬럼을 가장 왼쪽에, 덜 사용하는 컬럼을 오른쪽에 배치하는 것이 좋습니다.
인덱스 사용하기
인덱스 관리하기 코드
MySQL로 인덱스를 어떻게 관리하고 사용하는지에 대해 간단히 알아보겠습니다.
Clustered 인덱스 만들기
MySQL에서는 자동으로 각 테이블이 primary key (주로 id 컬럼)에 대한 clustered 인덱스가 만들어집니다. 그러나 만약 primary key가 아닌 다른 컬럼을 clustered 인덱스로 사용하고 싶다면:
- 기존 인덱스를 삭제한 후,
- 해당 코드를 사용해 인덱스를 추가합니다:
CREATE CLUSTERED INDEX index_name ON table_name (column_name);Non-clustered 인덱스 만들기
Non-clustered 인덱스는 여러 개를 만들 수 있습니다. 아래와 같은 코드를 사용해 만들 수 있습니다:
CREATE INDEX index_name ON table_name (column_name);Composite 인덱스 만들기
여러 개의 컬럼에 대한 composite 인덱스를 만들 때는 다음과 같은 코드를 사용합니다:
CREATE INDEX index_name ON table_name (column_name_1, column_2, ...);인덱스 확인하기
테이블 안에 있는 모든 인덱스에 대한 정보를 확인하려면 다음 코드를 사용합니다:
SHOW INDEX FROM table_name;인덱스 삭제하기
인덱스를 삭제하려면 다음 코드를 사용합니다:
DROP INDEX index_name ON table_name;인덱스를 삭제하기 위해서는 인덱스 이름을 알고 있어야 합니다. 인덱스 이름을 까먹었다면 SHOW INDEX 문을 사용해 확인할 수 있습니다.
인덱스 사용하기
조회할 때 인덱스를 사용하려면 SELECT 문을 그대로 사용하면 됩니다. DBMS가 알아서 인덱스를 사용할 수 있는 쿼리에 대해 인덱스를 사용해 줍니다.
