Points clés à connaître lors de la création de tables dans MySQL

Pour les développeurs backend, accéder à une base de données est crucial.

Les données des utilisateurs principaux sont généralement stockées en toute sécurité dans des bases de données comme MySQL ou Oracle.

Les tâches quotidiennes impliquent souvent la création de bases de données et de tables pour répondre aux besoins de l'entreprise, mais les tables sont créées beaucoup plus fréquemment.

Cet article se concentrera sur la création de tables, car ignorer les détails clés peut entraîner des problèmes coûteux de maintenance après le déploiement.

À propos, de mauvaises pratiques de conception de base de données peuvent également entraîner une réponse lente de votre API en cas de concurrence élevée. L'image suivante montre les résultats des tests de performances d'une API à l'aide de l'outil EchoAPI.

Aujourd'hui, discutons de 18 conseils pour créer des tables dans une base de données.

De nombreux détails mentionnés dans cet article proviennent de mes propres expériences et défis rencontrés au cours de mon travail, et j'espère qu'ils vous seront utiles.

1. Nommer

Lors de la création de tables, de champs et d'index, il est extrêmement important de leur donner de bons noms.

1.1 Noms significatifs

Les noms servent de visage aux tables, champs et index, laissant une première impression.

Les bons noms sont concis et auto-descriptifs, ce qui facilite la communication et la maintenance.

Les noms pauvres sont ambigus et déroutants, conduisant au chaos et à la frustration.

Mauvais exemples :

Les noms de champs comme abc, abc_name, name, user_name_123456789 vous laisseront perplexes.

Bon exemple :

Nom du champ comme nom_utilisateur.

Un petit rappel : les noms ne doivent pas non plus être trop longs, idéalement limités à 30 caractères.

1.2 Sensibilité à la casse

Il est préférable d'utiliser des lettres minuscules pour les noms, car elles sont plus faciles à lire visuellement.

Mauvais exemples :

Les noms de champs comme PRODUCT_NAME, PRODUCT_name ne sont pas intuitifs. Un mélange de majuscules et de minuscules est moins agréable à lire.

Bon exemple :

Le nom du champ en tant que nom_produit semble plus confortable.

1.3 Séparateurs

Souvent, les noms peuvent contenir plusieurs mots pour une meilleure compréhension.

Quel séparateur doit-on utiliser entre plusieurs mots ?

Mauvais exemples :

Les noms de champs tels que productname, productName, product name ou product@name ne sont pas recommandés.

Bon exemple :

Nom du champ comme nom_produit.

L'utilisation d'un trait de soulignement _ entre les mots est fortement conseillée.

1.4 Noms des tables

Pour les noms de table, il est recommandé d'utiliser des noms significatifs et concis accompagnés d'un préfixe professionnel.

Pour les tables liées aux commandes, ajoutez le nom de la table avec order_, par exemple order_pay, order_pay_detail.

Pour les tableaux relatifs aux produits, ajoutez product_, comme product_spu, product_sku.

Cette pratique permet de catégoriser rapidement les tableaux liés à la même entreprise.

De plus, si une entreprise sans commande peut avoir besoin de créer une table nommée pay, elle peut être facilement distinguée comme finance_pay, évitant ainsi les conflits de noms.

1.5 Noms des champs

Les noms de champs permettent une flexibilité maximale mais peuvent facilement prêter à confusion.

Par exemple, utiliser un indicateur pour indiquer le statut dans une table tout en utilisant le statut dans une autre peut créer des incohérences.

Il est conseillé de normaliser le statut de représentant de l'État.

Lorsqu'une table utilise la clé primaire d'une autre table, ajoutez _id ou _sys_no à la fin du nom du champ, par exemple product_spu_id ou product_spu_sys_no.

De plus, standardisez l'heure de création comme create_time et l'heure de modification comme update_time, avec le statut de suppression fixé comme delete_status.

Les autres champs communs doivent également conserver une convention de dénomination uniforme dans les différentes tables pour une meilleure clarté.

1.6 Noms des index

Dans une base de données, il existe différents types d'index, notamment les clés primaires, les index réguliers, les index uniques et les index composites.

Une table a généralement une seule clé primaire, généralement nommée id ou sys_no.

Les index réguliers et composites peuvent utiliser le préfixe ix_, par exemple ix_product_status.

Les index uniques peuvent utiliser le préfixe ux_, tel que ux_product_code.

2. Types de champs

Lors de la conception de tableaux, il existe une grande liberté dans le choix des types de champs.

Les champs au format temporel peuvent être date, datetime ou timestamp, etc.

Les types de données de caractères incluent varchar, char, text, etc.

Les types numériques comprennent int, bigint, smallint et tinyint.

La sélection d'un type de champ approprié est cruciale.

Surestimer les types (par exemple, utiliser bigint pour un champ qui ne stockera que des valeurs comprises entre 1 et 10) gaspille de l'espace ; tinyint suffirait.

À l'inverse, sous-estimer (par exemple, utiliser int pour un identifiant à 18 chiffres) entraînera des échecs de stockage des données.

Voici quelques principes pour choisir les types de champs :

• Préférez une petite taille de stockage tout en répondant aux besoins normaux de l'entreprise, en choisissant de petit à grand.
• Utilisez char pour des longueurs de chaîne fixes ou similaires, et varchar pour des longueurs variées.
• Utilisez bit pour les champs booléens.
• Utilisez tinyint pour les champs d'énumération.
• Choisissez bigint pour les champs de clé primaire.
• Utilisez le nombre décimal pour les champs monétaires.
• Utilisez l'horodatage ou la date/heure pour les champs d'heure.

3. Longueur du champ

Après avoir défini les noms de champs et sélectionné les types de champs appropriés, l'accent doit être mis sur la longueur des champs, comme varchar(20) ou bigint(20).

Qu'est-ce que varchar indique en termes de longueur : octets ou caractères ?

La réponse : dans MySQL, varchar et char représentent la longueur des caractères, tandis que la plupart des autres types représentent la longueur en octets.

Par exemple, bigint(4) spécifie la longueur d'affichage, pas la longueur de stockage, qui reste 8 octets.

Si la propriété zerofill est définie, les nombres de moins de 4 octets seront complétés, mais même s'ils sont remplis, le stockage de données sous-jacent reste à 8 octets.

4. Nombre de champs

Lors de la conception d’un tableau, il est crucial de limiter le nombre de champs.

J'ai vu des tables contenant des dizaines, voire des centaines de champs, conduisant à de gros volumes de données et à une faible efficacité des requêtes.

Si cette situation se présente, envisagez de diviser les grandes tables en tables plus petites tout en conservant les clés primaires communes.

En règle générale, maintenez le nombre de champs par table en dessous de 20.

5. Clés primaires

Créez une clé primaire lors de la configuration d'une table.

Les clés primaires sont intrinsèquement accompagnées d'index de clés primaires, ce qui rend les requêtes plus efficaces, car elles ne nécessitent pas de recherches supplémentaires.

Dans une seule base de données, les clés primaires peuvent utiliser AUTO_INCREMENT pour une croissance automatique.

Pour les bases de données distribuées, en particulier dans les architectures fragmentées, il est préférable d'utiliser des algorithmes externes (comme Snowflake) pour garantir des identifiants uniques au monde.

De plus, gardez les clés primaires indépendantes des valeurs métier pour réduire le couplage et faciliter les expansions futures.

Cependant, pour les relations un-à-un, telles que les tables utilisateur et les tables d'extension utilisateur, il est acceptable d'utiliser directement la clé primaire de la table utilisateur.

6. Moteur de stockage

Avant MySQL 8, le moteur de stockage par défaut était MyISAM ; à partir de MySQL 8, c'est maintenant InnoDB.

Historiquement, il y a eu de nombreux débats sur le moteur de stockage à choisir.

MyISAM sépare le stockage des index et des données, améliorant ainsi les performances des requêtes, mais ne prend pas en charge les transactions et les clés étrangères.

InnoDB, bien que légèrement plus lent dans les requêtes, prend en charge les transactions et les clés étrangères, ce qui le rend plus robuste.

Il était auparavant conseillé d'utiliser MyISAM pour les scénarios gourmands en lecture et InnoDB pour les scénarios gourmands en écriture.

Cependant, les optimisations dans MySQL ont réduit les différences de performances, il est donc recommandé d'utiliser le moteur de stockage InnoDB par défaut dans MySQL 8 et versions ultérieures sans aucune modification supplémentaire.

7. PAS NULL

Lors de la création de champs, décidez s'ils peuvent être NULL.

Il est conseillé de définir les champs comme NOT NULL autant que possible.

Pourquoi ?

Dans InnoDB, le stockage des valeurs NULL nécessite de l'espace supplémentaire et peut également entraîner des échecs d'index.

Les valeurs NULL ne peuvent être interrogées qu'en utilisant IS NULL ou IS NOT NULL, car l'utilisation de = renvoie toujours false.

Ainsi, définissez les champs comme NOT NULL autant que possible.

Cependant, lorsqu'un champ est directement défini comme NOT NULL et qu'une valeur est oubliée lors de la saisie, cela empêchera l'insertion de données.

Cela peut être une situation acceptable lorsque de nouveaux champs sont ajoutés et que des scripts sont exécutés avant de déployer un nouveau code, ce qui entraîne des erreurs sans valeurs par défaut.

Pour les champs NOT NULL nouvellement ajoutés, la définition d'une valeur par défaut est cruciale :

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

8. Clés étrangères

Les clés étrangères dans MySQL servent à garantir la cohérence et l'intégrité des données.

Par exemple :

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

Cela crée une table de classe.

Ensuite, une table étudiante peut être construite qui y fait référence :

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)
);

Ici, cid dans la table des étudiants fait référence à l'identifiant dans la table des classes.

Tenter de supprimer un enregistrement chez l'étudiant sans supprimer l'enregistrement cid correspondant dans la classe générera une erreur de contrainte de clé étrangère :

une contrainte de clé étrangère échoue.

Ainsi, la cohérence et l'intégrité sont préservées.

Notez que les clés étrangères ne sont utilisables qu'avec le moteur de stockage 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:

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?.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!