MySQL 테이블 디자인: 14가지 황금 규칙

1. 테이블 설계 시 포함해야 할 일반적인 필드

일반적으로 테이블에는 다음 필드가 포함되어야 합니다.

• id: 기본 키. 모든 테이블은 기본 키를 가져야 합니다. 예외는 없습니다.
• create_time: 생성 시간 - 필수.
• modified_time: 마지막 수정 시간 - 필수. 레코드가 업데이트될 때마다 업데이트되어야 합니다.
• version: 레코드의 버전 번호로, 일반적으로 낙관적 잠금에 사용됩니다. - 선택 사항.
• modifier: 레코드를 마지막으로 수정한 사람 - 선택 사항.
• creator: 레코드를 만든 사람 - 선택 사항.

2. 모든 필드에 주석을 달아야 하며, 특히 열거형의 경우

테이블을 설계할 때 모든 필드에는 주석이 포함되어야 합니다. 이는 열거형을 사용하는 필드에 특히 중요하며, 모든 enum 값은 주석에 나열되어야 합니다. 나중에 변경 사항이 발생하면 주석에도 반영되어야 합니다.

잘못된 예:

CREATE TABLE order_tab (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNIQUE,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    payment_status VARCHAR(20) DEFAULT 'not_paid',
    version INT DEFAULT 0,
    created_time DATETIME,
    updated_time DATETIME,
    creator VARCHAR(255),
    modifier VARCHAR(255)
);

좋은 예:

CREATE TABLE order_tab (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '주문 항목의 고유 식별자, 자동 증가 기본 키',
    order_id BIGINT UNIQUE COMMENT '주문의 전역적으로 고유한 식별자',
    user_id BIGINT NOT NULL COMMENT '사용자의 고유 식별자, 사용자 테이블에 연결됨',
    total_amount DECIMAL(10, 2) NOT NULL COMMENT '총 주문 금액, 소수점 두 자리까지 정확함',
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '주문 상태, 예: PENDING, COMPLETED 등',
    payment_status VARCHAR(20) DEFAULT 'not_paid' COMMENT '결제 상태, 예: not_paid, paid 등',
    version INT DEFAULT 0 COMMENT '동시성 제어를 위한 낙관적 잠금 버전 번호',
    created_time DATETIME COMMENT '주문 생성 타임스탬프',
    updated_time DATETIME COMMENT '마지막 업데이트 타임스탬프',
    creator VARCHAR(255) COMMENT '주문 생성자 - 일반적으로 사용자 또는 시스템의 사용자 이름',
    modifier VARCHAR(255) COMMENT '주문을 마지막으로 수정한 사람 또는 시스템'
);

3. 명명 규칙

테이블 이름, 필드 이름, 인덱스 이름 등에 대해 명확하고 일관된 명명 규칙을 사용합니다. 좋은 명명은 가독성과 이해도를 높입니다.

예를 들어 다음과 같은 이름을 피하십시오.

• acc_no, 1_acc_no (나쁜 예)

대신 다음을 사용하십시오.

• account_no, account_number (좋은 예)

추가 규칙:

• 테이블 및 필드 이름은 소문자 또는 숫자만 사용해야 합니다.
• 숫자로 시작하지 마십시오.
• 약어를 피하십시오.
• 인덱스 명명 규칙:
  • 기본 키: pk_<field_name>
  • 고유 인덱스: uk_<field_name>
  • 일반 인덱스: idx_<field_name>

4. 적절한 필드 유형 선택

테이블을 설계할 때 가장 적절한 필드 유형을 선택하십시오.

• 저장 공간을 절약하기 위해 가장 작은 적합한 데이터 유형을 사용하십시오(예: tinyint, smallint, int, bigint 순으로 선호).
• 통화 값의 경우 float 또는 double 대신 decimal을 사용하십시오.
• 문자열 길이가 고정되거나 거의 고정된 경우 char를 사용하십시오.
• 가변 길이 문자열의 경우 varchar를 사용하되 길이를 5000 미만으로 유지하십시오.
• 매우 큰 값의 경우 text를 사용하는 것을 고려하고 기본 키로 연결된 별도의 테이블에 저장하십시오.
• 테이블의 모든 varchar 필드의 결합된 길이는 65535바이트를 초과할 수 없습니다. 필요한 경우 TEXT 또는 LONGTEXT 유형을 사용하십시오.

5. 합리적인 기본 키 설계

기본 키를 비즈니스 로직에 바인딩하지 마십시오. 예를 들어 사용자 ID(고유하더라도)를 기본 키로 사용하는 것은 권장되지 않습니다. 대신 다음과 같은 의미 없는 고유 식별자를 사용하십시오.

• UUID,
• 자동 증가 기본 키,
• Snowflake 알고리즘으로 생성된 기본 키.

6. 적절한 필드 길이 선택

먼저 질문을 드리겠습니다. 데이터베이스에서 필드 길이는 문자 길이를 나타냅니까, 바이트 길이를 나타냅니까?

MySQL에서:

• varchar 및 char는 문자 길이를 지정합니다.
• 다른 유형은 일반적으로 바이트 길이를 지정합니다.

예를 들어:

• char(10)은 10자를 의미합니다.
• bigint(4)는 표시 너비(저장 크기 아님)를 나타내지만 bigint는 항상 8바이트를 사용합니다.

테이블을 설계할 때 필드 길이를 신중하게 고려하십시오. 예를 들어 사용자 이름 필드가 5~20자 사이일 것으로 예상되는 경우 username varchar(32)로 정의할 수 있습니다.

팁: 필드 길이는 일반적으로 2의 거듭제곱(즉, 2ⁿ)으로 설정하는 것이 가장 좋습니다.

7. 물리적 삭제보다 논리적 삭제 선호

물리적 삭제: 데이터가 디스크에서 영구적으로 제거되어 저장 공간이 확보됩니다.

논리적 삭제: 데이터를 삭제된 것으로 표시하기 위해 is_deleted와 같은 필드를 추가합니다.

물리적 삭제의 예:

DELETE FROM account_info_tab WHERE account_no = '666';

논리적 삭제의 예:

UPDATE account_info_tab SET is_deleted = 1 WHERE account_no = '666';

논리적 삭제를 선호하는 이유?

• 물리적 삭제로는 데이터 복구가 어렵습니다.
• 자동 증가 기본 키가 비연속적이 됩니다.
• 핵심 비즈니스 테이블의 경우 레코드를 물리적으로 삭제하는 대신 상태 필드를 업데이트하는 것이 좋습니다.

8. 단일 테이블에 너무 많은 필드 피하기

테이블을 설계할 때 필드 수를 제한하려고 노력하십시오. 일반적으로 20개 이하로 유지하십시오.

너무 많은 필드는 다음과 같은 문제가 발생할 수 있습니다.

• 큰 행 크기,
• 낮은 쿼리 성능.

비즈니스 로직에 많은 필드가 필요한 경우 동일한 기본 키를 가진 더 작은 테이블로 큰 테이블을 분할하는 것을 고려하십시오.

테이블에 많은 수의 필드가 있는 경우 다음으로 분리하는 것을 고려하십시오.

• 빠른 필터링을 위한 "쿼리 조건" 테이블,
• 전체 콘텐츠를 위한 "세부 정보" 테이블, 성능을 향상시킵니다.

9. 가능한 한 NOT NULL 사용

특정 이유가 없는 한 필드를 NOT NULL로 정의하는 것이 좋습니다.

왜?

• null 포인터 문제를 방지합니다.
• NULL은 추가 저장 공간을 차지합니다.
• NULL과의 비교는 더 복잡하고 쿼리 최적화를 방해합니다.
• NULL 값은 인덱스 실패를 유발할 수 있습니다.
• 필드를 안전하게 빈 문자열 또는 상수로 기본 설정할 수 있는 경우 NOT NULL로 정의해야 합니다.

10. 인덱스가 필요한 필드 평가

먼저 데이터 세트의 크기를 평가하십시오. 테이블에 몇 백 개의 행만 있는 경우 인덱싱이 필요하지 않을 수 있습니다.

일반적으로 필드가 쿼리 조건에 자주 사용되는 경우 인덱스가 있어야 합니다. 그러나 인덱싱을 과도하게 사용해서는 안 됩니다.

• 너무 많은 인덱스를 만들지 마십시오. 테이블당 5개 이하로 유지하십시오.
  • 너무 많은 인덱스는 삽입 및 업데이트 작업을 느리게 합니다.
• 카디널리티가 낮은 필드(예: 성별)에는 인덱스를 지정하지 마십시오.
• 인덱싱된 필드에서 MySQL 내장 함수를 사용하는 경우와 같이 인덱스 실패를 유발하는 경우에 주의하십시오.
• 인덱스 수를 줄이려면 복합(다중 열) 인덱스를 고려하십시오.
  • 커버링 인덱스와 같은 기술을 사용하고 최좌측 접두사 규칙을 따르십시오.

사용자 테이블의 예:

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user_id 또는 name으로 쿼리할 가능성이 높고 user_id가 고유하므로 다음과 같이 인덱스를 정의할 수 있습니다.

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  UNIQUE KEY un_user_id (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

11. MySQL 예약어 사용 피하기

데이터베이스 이름, 테이블 이름 또는 필드 이름에 예약어가 포함된 경우 SQL 문에서 백틱(`)으로 묶어야 합니다. 이렇게 하면 SQL 작성법이 복잡해지고 특히 스크립팅하거나 셸 변수로 작업할 때 오류 위험이 증가합니다.

따라서 select, interval, desc 등과 같은 MySQL 예약어는 사용하지 마십시오.

12. InnoDB 스토리지 엔진 선호

테이블을 만들 때 스토리지 엔진을 선택해야 합니다. 일반적으로 읽기-쓰기 비율이 1% 미만인 경우 MyISAM을 고려할 수 있지만 InnoDB가 선호됩니다.

13. 올바른 시간 유형 선택

테이블을 설계할 때 일반적으로 create_time, modified_time 등과 같은 공통 시간 관련 필드를 포함합니다. 그렇다면 어떤 MySQL 시간 유형을 사용해야 할까요?

주요 옵션은 다음과 같습니다.

• date: 날짜 값을 yyyy-mm-dd 형식으로 저장합니다. 범위: 1000-01-01 ~ 9999-12-31. 크기: 3바이트.
• time: 시간 값을 hh:mm:ss 형식으로 저장합니다. 범위: -838:59:59 ~ 838:59:59. 크기: 3바이트.
• datetime: 날짜 및 시간을 yyyy-mm-dd hh:mm:ss 형식으로 저장합니다. 범위: 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59. 크기: 8바이트. 표준 시간대에 종속되지 않습니다.
• timestamp: 날짜 및 시간을 타임스탬프(yyyymmddhhmmss)로 저장합니다. 범위: 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07. 크기: 4바이트. 표준 시간대에 종속됩니다.
• year: 연도 값을 yyyy 형식으로 저장합니다. 범위: 1901 ~ 2155. 크기: 1바이트.

권장 사항: 더 큰 범위가 있고 표준 시간대에 독립적이므로 날짜 및 시간을 저장하려면 datetime 유형을 사용하는 것이 좋습니다.

14. 보안 고려 사항

• 데이터 암호화: 사용자 비밀번호와 같은 민감한 정보는 암호화된 형식으로 저장해야 합니다.
• 데이터 마스킹: 전화 번호 또는 이메일 주소와 같은 개인 식별 정보(PII)의 경우 더 나은 개인 정보 보호 및 규정 준수를 위해 데이터 마스킹을 적용합니다.

---

Leapcell은 백엔드 프로젝트 호스팅을 위한 최고의 선택입니다.

Leapcell은 웹 호스팅, 비동기 작업 및 Redis를 위한 차세대 서버리스 플랫폼입니다.

다국어 지원

• Node.js, Python, Go 또는 Rust로 개발하십시오.

무료로 무제한 프로젝트 배포

• 사용량에 대해서만 지불하십시오. 요청 없음, 요금 없음.

탁월한 비용 효율성

• 유휴 요금 없이 사용한 만큼만 지불하십시오.
• 예: $25는 평균 응답 시간 60ms에서 694만 건의 요청을 지원합니다.

간소화된 개발자 경험

• 간편한 설정을 위한 직관적인 UI.
• 완전 자동화된 CI/CD 파이프라인 및 GitOps 통합.
• 실행 가능한 통찰력을 위한 실시간 지표 및 로깅.

손쉬운 확장성 및 고성능

• 쉬운 동시성 처리를 위한 자동 확장.
• 운영 오버헤드가 없습니다. 구축에만 집중하십시오.

설명서에서 자세히 알아보십시오!

X에서 팔로우하세요: @LeapcellHQ