MySQL에서 테이블을 생성할 때 알아야 할 핵심 사항

백엔드 개발자에게 데이터베이스 액세스는 매우 중요합니다.

핵심 사용자 데이터는 일반적으로 MySQL이나 Oracle과 같은 데이터베이스에 안전하게 저장됩니다.

일상 작업에는 비즈니스 요구 사항을 충족하기 위해 데이터베이스와 테이블을 만드는 것이 포함되는 경우가 많지만 테이블이 훨씬 더 자주 생성됩니다.

주요 세부 정보를 무시하면 배포 후 유지 관리에 비용이 많이 드는 문제가 발생할 수 있으므로 이 문서에서는 테이블 생성에 중점을 둡니다.

그런데, 데이터베이스 설계 관행이 좋지 않으면 동시성이 높을 때 API가 느리게 응답할 수도 있습니다. 다음 이미지는 EchoAPI 도구를 사용한 API의 성능 테스트 결과를 보여줍니다.

오늘은 데이터베이스에서 테이블을 생성하기 위한 18가지 팁에 대해 논의해 보겠습니다.

이 글에 언급된 내용 중 상당수는 제가 업무를 하면서 겪은 경험과 어려움에서 비롯된 것이며, 여러분께 도움이 되기를 바랍니다.

1. 명명

테이블, 필드, 인덱스를 생성할 때 좋은 이름을 지정하는 것은 매우 중요합니다.

1.1 의미 있는 이름

이름은 테이블, 필드, 인덱스의 얼굴 역할을 하며 첫인상을 남깁니다.

좋은 이름은 간결하고 자기 설명적이므로 의사소통과 유지 관리가 더 쉽습니다.

가난한 이름은 모호하고 혼란스러워서 혼란과 좌절을 낳습니다.

나쁜 예:

abc, abc_name, name, user_name_123456789와 같은 필드 이름은 당황스러울 것입니다.

좋은 예:

필드 이름은 user_name입니다.

주의사항: 이름은 너무 길어서는 안 되며 30자 이내로 유지하는 것이 이상적입니다.

1.2 대소문자 구분

이름은 소문자를 사용하는 것이 시각적으로 읽기 쉽기 때문에 가장 좋습니다.

나쁜 예:

PRODUCT_NAME, PRODUCT_name과 같은 필드 이름은 직관적이지 않습니다. 대문자와 소문자를 혼합하면 읽기가 덜 즐겁습니다.

좋은 예:

product_name과 같은 필드 이름이 더 편안해 보입니다.

1.3 구분 기호

더 나은 이해를 위해 이름에 여러 단어가 포함될 수 있는 경우가 많습니다.

여러 단어 사이에는 어떤 구분 기호를 사용해야 합니까?

나쁜 예:

productname, productName, product name, product@name과 같은 필드 이름은 권장되지 않습니다.

좋은 예:

필드 이름은 product_name입니다.

단어 사이에 밑줄_을 사용하는 것이 좋습니다.

1.4 테이블 이름

테이블 이름에는 비즈니스 접두사와 함께 의미 있고 간결한 이름을 사용하는 것이 좋습니다.

주문 관련 테이블의 경우 테이블 이름 앞에 order_를 붙입니다(예: order_pay, order_pay_detail).

제품 관련 테이블의 경우 product_spu, product_sku처럼 product_를 앞에 붙입니다.

이 방법은 동일한 비즈니스와 관련된 테이블을 빠르게 함께 분류하는 데 도움이 됩니다.

또한, 비주문 업체가 pay라는 테이블을 생성해야 하는 경우 Finance_pay로 쉽게 구분할 수 있어 이름 충돌을 방지할 수 있습니다.

1.5 필드 이름

필드 이름은 최대한의 유연성을 허용하지만 쉽게 혼란을 초래할 수 있습니다.

예를 들어, 한 테이블에서는 상태를 표시하기 위해 플래그를 사용하고 다른 테이블에서는 상태를 사용하면 불일치가 발생할 수 있습니다.

국가를 대표하는 지위로 표준화하는 것이 바람직합니다.

테이블이 다른 테이블의 기본 키를 사용하는 경우 필드 이름 끝에 _id 또는 _sys_no를 추가하세요(예: product_spu_id 또는 product_spu_sys_no).

추가로 생성 시간을 create_time, 수정 시간을 update_time으로 표준화하고 삭제 상태는 delete_status로 고정합니다.

다른 공통 필드도 명확성을 높이기 위해 여러 테이블에서 통일된 명명 규칙을 유지해야 합니다.

1.6 인덱스 이름

데이터베이스에는 기본키, 일반 인덱스, 고유 인덱스, 복합 인덱스 등 다양한 종류의 인덱스가 있습니다.

테이블에는 일반적으로 id 또는 sys_no라는 이름의 단일 기본 키가 있습니다.

정규 및 복합 인덱스는 ix_ 접두사(예: ix_product_status)를 사용할 수 있습니다.

고유 인덱스는 ux_product_code와 같은 ux_ 접두사를 사용할 수 있습니다.

2. 필드 유형

테이블을 디자인할 때 필드 유형을 자유롭게 선택할 수 있습니다.

시간 형식 필드는 날짜, 날짜/시간, 타임스탬프 등이 될 수 있습니다.

문자 데이터 유형에는 varchar, char, text 등이 포함됩니다.

숫자 유형은 int, bigint, smallint 및tinyint로 구성됩니다.

적절한 필드 유형을 선택하는 것이 중요합니다.

유형을 과대평가하면(예: 1에서 10 사이의 값만 저장하는 필드에 bigint 사용) 공간이 낭비됩니다. Tinyint이면 충분합니다.

반대로 과소평가(예: 18자리 ID에 int 사용)하면 데이터 저장 오류가 발생합니다.

다음은 필드 유형 선택에 대한 몇 가지 원칙입니다.

• 소형에서 대형까지 선택하여 일반적인 비즈니스 요구 사항을 충족하면서 작은 저장 공간을 선호하세요.
• 고정되거나 유사한 문자열 길이에는 char를 사용하고 다양한 길이에는 varchar를 사용하세요.
• 부울 필드에는 비트를 사용하세요.
• 열거 필드에는tinyint를 사용하세요.
• 기본 키 필드에 대해 bigint를 선택하세요.
• 통화 필드에는 소수를 사용하세요.
• 시간 필드에는 타임스탬프 또는 날짜/시간을 사용하세요.

3. 필드 길이

필드 이름을 정의하고 적절한 필드 유형을 선택한 후에는 varchar(20) 또는 bigint(20)와 같은 필드 길이로 초점이 이동해야 합니다.

varchar는 길이(바이트 또는 문자) 측면에서 무엇을 나타냅니까?

답변: MySQL에서 varchar와 char은 문자 길이를 나타내는 반면, 대부분의 다른 유형은 바이트 길이를 나타냅니다.

예를 들어 bigint(4)는 저장 길이가 아닌 표시 길이를 지정하며 8바이트로 유지됩니다.

zerofill 속성을 설정하면 4바이트 미만의 숫자가 채워지지만, 채워져도 기본 데이터 저장소는 8바이트로 유지됩니다.

4. 분야 수

테이블을 디자인할 때 필드 수를 제한하는 것이 중요합니다.

테이블에 수십, 심지어 수백 개의 필드가 있어 데이터 볼륨이 커지고 쿼리 효율성이 떨어지는 것을 본 적이 있습니다.

이러한 상황이 발생하면 공통 기본 키를 유지하면서 큰 테이블을 작은 테이블로 분할하는 것이 좋습니다.

일반적으로 테이블당 필드 수는 20개 미만으로 유지하세요.

5. 기본 키

테이블을 설정할 때 기본 키를 생성하세요.

기본 키는 본질적으로 기본 키 인덱스와 함께 제공되므로 추가 조회가 필요하지 않으므로 쿼리가 더 효율적입니다.

단일 데이터베이스에서 기본 키는 자동 증가를 위해 AUTO_INCREMENT를 사용할 수 있습니다.

분산 데이터베이스, 특히 샤딩된 아키텍처의 경우 외부 알고리즘(예: Snowflake)을 사용하여 전역적으로 고유한 ID를 보장하는 것이 가장 좋습니다.

또한 기본 키를 비즈니스 가치와 독립적으로 유지하여 결합을 줄이고 향후 확장을 촉진하세요.

단, 사용자 테이블, 사용자 확장 테이블 등의 일대일 관계에서는 사용자 테이블의 기본 키를 직접 사용해도 됩니다.

6. 스토리지 엔진

MySQL 8 이전에는 기본 스토리지 엔진이 MyISAM이었습니다. MySQL 8부터는 InnoDB입니다.

역사적으로 어떤 스토리지 엔진을 선택할지에 대해 많은 논쟁이 있었습니다.

MyISAM은 인덱스와 데이터 저장소를 분리하여 쿼리 성능을 향상시키지만 트랜잭션 및 외래 키에 대한 지원이 부족합니다.

InnoDB는 쿼리 속도가 약간 느리지만 트랜잭션과 외래 키를 지원하므로 더욱 강력해집니다.

이전에는 읽기가 많은 시나리오에는 MyISAM을 사용하고 쓰기가 많은 시나리오에는 InnoDB를 사용하는 것이 권장되었습니다.

그러나 MySQL의 최적화를 통해 성능 차이가 줄어들었으므로 추가 수정 없이 MySQL 8 이상에서는 기본 InnoDB 스토리지 엔진을 사용하는 것이 좋습니다.

7. NULL이 아니다

필드를 생성할 때 NULL이 될 수 있는지 여부를 결정하세요.

가능한 경우 필드를 NOT NULL로 정의하는 것이 좋습니다.

왜요?

InnoDB에서 NULL 값을 저장하려면 추가 공간이 필요하며 인덱스 오류가 발생할 수도 있습니다.

=를 사용하면 항상 false가 반환되므로 NULL 값은 IS NULL 또는 IS NOT NULL을 통해서만 쿼리할 수 있습니다.

따라서 가능한 경우 필드를 NOT NULL로 정의하세요.

단, 필드를 NOT NULL로 직접 정의한 경우 입력 중 값을 잊어버린 경우 데이터 삽입이 불가능합니다.

새 코드를 배포하기 전에 새 필드가 추가되고 스크립트가 실행되어 기본값이 없는 오류가 발생하는 경우 이는 허용 가능한 상황일 수 있습니다.

새로 추가된 NOT NULL 필드의 경우 기본값을 설정하는 것이 중요합니다.

ALTER TABLE product_sku ADD COLUMN brand_id INT(10) NOT NULL DEFAULT 0;

8. 외래 키

MySQL의 외래 키는 데이터 일관성과 무결성을 보장하는 역할을 합니다.

예:

CREATE TABLE class (
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(15)
);

이렇게 하면 수업 테이블이 생성됩니다.

그런 다음 이를 참조하는 학생 테이블을 구성할 수 있습니다.

CREATE TABLE student(
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(15) NOT NULL,
  gender VARCHAR(10) NOT NULL,
  cid INT,
  FOREIGN KEY (cid) REFERENCES class(id)
);

여기에서 학생 테이블의 cid는 클래스 테이블의 id를 참조합니다.

수업에서 해당 CID 레코드를 제거하지 않고 학생의 레코드를 삭제하려고 하면 외래 키 제약 조건 오류가 발생합니다.

외래 키 제약 조건이 실패했습니다.

따라서 일관성과 무결성이 유지됩니다.

외래 키는 InnoDB 스토리지 엔진에서만 사용할 수 있습니다.

If only two tables are linked, it might be manageable, but with several tables, deleting a parent record requires synchronously deleting many child records, which can impact performance.

Thus, for internet systems, it is generally advised to avoid using foreign keys to prioritize performance over absolute data consistency.

In addition to foreign keys, stored procedures and triggers are also discouraged due to their performance impact.

9. Indexes

When creating tables, beyond specifying primary keys, it’s essential to create additional indexes.

For example:

CREATE TABLE product_sku(
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  spu_id INT(10) NOT NULL,
  brand_id INT(10) NOT NULL,
  name VARCHAR(15) NOT NULL
);

This table includes spu_id (from the product group) and brand_id (from the brand table).

In situations that save IDs from other tables, a regular index can be added:

CREATE TABLE product_sku (
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  spu_id INT(10) NOT NULL,
  brand_id INT(10) NOT NULL,
  name VARCHAR(15) NOT NULL,
  KEY `ix_spu_id` (`spu_id`) USING BTREE,
  KEY `ix_brand_id` (`brand_id`) USING BTREE
);

Such indexes significantly enhance query efficiency.

However, do not create too many indexes as they can hinder data insertion efficiency due to additional storage requirements.

A single table should ideally have no more than five indexes.

If the number of indexes exceeds five during table creation, consider dropping some regular indexes in favor of composite indexes.

Also, when creating composite indexes, always apply the leftmost matching rule to ensure the indexes are effective.

For fields with high duplication rates (like status), avoid creating separate regular indexes. MySQL may skip the index and choose a full table scan instead if it’s more efficient.

I’ll address index inefficiency issues in a separate article later, so let’s hold off on that for now.

10. Time Fields

The range of types available for time fields in MySQL is fairly extensive: date, datetime, timestamp, and varchar.

Using varchar might be for API consistency where time data is represented as a string.

However, querying data by time ranges can be inefficient with varchar since it cannot utilize indexes.

Date is intended only for dates (e.g., 2020-08-20), while datetime and timestamp are suited for complete date and time.

There are subtle differences between them.

Timestamp: uses 4 bytes and spans from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07. It’s also timezone-sensitive.

Datetime: occupies 8 bytes with a range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59, independent of time zones.

Using datetime to save date and time is preferable for its wider range.

As a reminder, when setting default values for time fields, avoid using 0000-00-00 00:00:00, which can cause errors during queries.

11. Monetary Fields

MySQL provides several types for floating-point numbers: float, double, decimal, etc.

Given that float and double may lose precision, it’s recommended to use decimal for monetary values.

Typically, floating numbers are defined as decimal(m,n), where n represents the number of decimal places, and m is the total length of both integer and decimal portions.

For example, decimal(10,2) allows for 8 digits before the decimal point and 2 digits after it.

12. JSON Fields

During table structure design, you may encounter fields needing to store variable data values.

For example, in an asynchronous Excel export feature, a field in the async task table may need to save user-selected query conditions, which can vary per user.

Traditional database fields don’t handle this well.

Using MySQL’s json type enables structured data storage in JSON format for easy saving and querying.

MySQL also supports querying JSON data by field names or values.

13. Unique Indexes

Unique indexes are frequently used in practice.

You can apply unique indexes to individual fields, like an organization’s code, or create composite unique indexes for multiple fields, like category numbers, units, specifications, etc.

Unique indexes on individual fields are straightforward, but for composite unique indexes, if any field is NULL, the uniqueness constraint may fail.

Another common issue is having unique indexes while still producing duplicate data.

Due to its complexity, I’ll elaborate on unique index issues in a later article.

When creating unique indexes, ensure that none of the involved fields contain NULL values to maintain their uniqueness.

14. Character Set

MySQL supports various character sets, including latin1, utf-8, utf8mb4, etc.

Here’s a table summarizing MySQL character sets:

Character Set	Description	Encoding Size	Notes
latin1	Encounters encoding issues; rarely used in real projects	1 byte	Limited support for international characters
utf-8	Efficient in storage but cannot store emoji	3 bytes	Suitable for most text but lacks emoji support
utf8mb4	Supports all Unicode characters, including emoji	4 bytes	Recommended for modern applications

It’s advisable to set the character set to utf8mb4 during table creation to avoid potential issues.

15. Collation

When creating tables in MySQL, the COLLATE parameter can be configured.

For example:

CREATE TABLE `order` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(20) COLLATE utf8mb4_bin NOT NULL,
  `name` VARCHAR(30) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_code` (`code`),
  KEY `un_code_name` (`code`,`name`) USING BTREE,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

The collation determines how character sorting and comparison are conducted.

Character collation depends on the character set, which for utf8mb4 would also start with utf8mb4_. Common types include utf8mb4_general_ci and utf8mb4_bin.

The utf8mb4_general_ci collation is case-insensitive for alphabetical characters, while utf8mb4_bin is case-sensitive.

This distinction is important. For example, if the order table contains a record with the name YOYO and you query it using lowercase yoyo under utf8mb4_general_ci, it retrieves the record. Under utf8mb4_bin, it will not.

Choose collation based on the actual business needs to avoid confusion.

16. Large Fields

Special attention is warranted for fields that consume substantial storage space, such as comments.

A user comment field might require limits, like a maximum of 500 characters.

Defining large fields as text can waste storage, thus it’s often better to use varchar for better efficiency.

For much larger data types, like contracts that can take up several MB, it may be unreasonable to store directly in MySQL.

Instead, such data could be stored in MongoDB, with the MySQL business table retaining the MongoDB ID.

17. Redundant Fields

To enhance performance and query speed, some fields can be redundantly stored.

For example, an order table typically contains a userId to identify users.

However, many order query pages also need to display the user ID along with the user’s name.

If both tables are small, a join is feasible, but for large datasets, it can degrade performance.

In that case, creating a redundant userName field in the order table can resolve performance issues.

While this adjustment allows direct querying from the order table without joins, it requires additional storage and may lead to inconsistency if user names change.

Therefore, carefully evaluate if the redundant fields strategy fits your particular business scenario.

18. Comments

When designing tables, ensure to add clear comments for tables and associated fields.

For example:

CREATE TABLE `sys_dept` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` VARCHAR(30) NOT NULL COMMENT 'Name',
  `pid` BIGINT NOT NULL COMMENT 'Parent Department',
  `valid_status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Valid Status: 1=Valid, 0=Invalid',
  `create_user_id` BIGINT NOT NULL COMMENT 'Creator ID',
  `create_user_name` VARCHAR(30) NOT NULL COMMENT 'Creator Name',
  `create_time` DATETIME(3) DEFAULT NULL COMMENT 'Creation Date',
  `update_user_id` BIGINT DEFAULT NULL COMMENT 'Updater ID',
  `update_user_name` VARCHAR(30)  DEFAULT NULL COMMENT 'Updater Name',
  `update_time` DATETIME(3) DEFAULT NULL COMMENT 'Update Time',
  `is_del` TINYINT(1) DEFAULT '0' COMMENT 'Is Deleted: 1=Deleted, 0=Not Deleted',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_pid` (`pid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='Department';

Detailed comments clarify the purpose of tables and fields.

Particularly for fields representing statuses (like valid_status), it immediately conveys the intent behind the data, such as indicating valid versus invalid.

Avoid situations where numerous status fields exist without comments, leading to confusion about what values like 1, 2, or 3 signify.

Initially, one might remember, but after a year of operation, it’s easy to forget, potentially leading to significant pitfalls.

Thus, when designing tables, meticulous commenting and regular updates of these comments are essential.

That wraps up the technical section of this article,If you have a different opinion, let me know?.

위 내용은 MySQL에서 테이블을 생성할 때 알아야 할 핵심 사항의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!