Compréhension des types de données MySQL

Compréhension des types de données MySql :

En raison des caractéristiques uniques et des détails d'implémentation de Mysql, L’impact sur les performances est évident, car il est essentiel de bien concevoir la base de données Mysql. Pour la conception de la base de données, nous devons mentionner la sélection du type de champs de table. Puisque Mysql prend en charge de nombreux types de données, il est crucial de choisir le bon type de données pour obtenir des performances élevées. Quel que soit le type de données que nous souhaitons stocker, nous devons les considérer selon certains principes de conception de bases de données.

Réflexions sur le choix des types de données

Plus petit est généralement mieux (en général, le plus petit type de données qui stocke correctement les données doit être utilisé autant que possible.)

Pourquoi ?

(1) Parce que les types de données plus petits sont généralement plus rapides car ils occupent moins de disque, de mémoire et de cache CPU, et nécessitent moins de cycles CPU pour être traités.
(2) Assurez-vous de ne pas sous-estimer la plage de valeurs qui doivent être stockées. La valeur la plus petite est relative à la plage de valeurs maximale du type de données.
(3) Si vous ne parvenez pas à décider quel type de données est le meilleur, choisissez le type le plus petit qui, selon vous, ne dépassera pas la plage.

La simplicité est une bonne chose (les opérations sur des types de données simples nécessitent généralement moins de cycles CPU.)

Pourquoi ? Voici quelques exemples pour illustrer pourquoi.

(1) Les opérations sur les entiers sont moins chères que les opérations sur les chaînes car les ensembles de chaînes et les règles de classement (règles de classement) rendent les comparaisons de caractères plus complexes que les comparaisons d'entiers.
(2) Les types intégrés de MySQL (date, heure, datatime) doivent être utilisés pour stocker les dates et les heures.
(3) L'adresse IP doit être stockée sous forme de type entier (int).

Essayez d'éviter NULL (valeur vide)

Pourquoi ?

(1) De nombreuses tables contiennent des colonnes qui peuvent être NULL, même si le programme n'a pas besoin de sauvegarder NULL, car l'attribut par défaut de la colonne est qu'elle peut être NULL. Il est généralement préférable de spécifier NOT NULL pour une colonne, sauf si vous avez vraiment besoin de stocker NULL.
(2) Si la requête contient des colonnes qui peuvent être NULL, il est difficile pour Mysql de l'optimiser car les colonnes NULL rendent les index, les statistiques d'index et les comparaisons de valeurs plus complexes. Les colonnes qui peuvent être NULL utilisent plus d'espace de stockage et nécessitent une gestion particulière dans Mysql. Lorsque les colonnes NULLable sont indexées, chaque enregistrement d'index nécessite un octet supplémentaire, ce qui peut même faire qu'un index de taille fixe devienne un index de taille variable dans MyISAM.
(3) Habituellement, changer la colonne compatible NULL en NOTNULL n'apportera que peu d'amélioration des performances. Si vous envisagez de créer un index sur la colonne, vous devriez essayer d'éviter de la concevoir comme une colonne compatible NULL. (Il existe une exception, c'est-à-dire que dans InnoDB, un bit séparé est utilisé pour stocker les valeurs NULL, il a donc une bonne efficacité spatiale pour les données clairsemées.)

Résumé

Lors du choix d'un type de données pour une colonne, la première étape consiste à déterminer le grand type approprié (nombre, chaîne, heure, etc.). C'est généralement très simple, puis l'étape suivante consiste à choisir le type spécifique.

De nombreux types de données Mysql peuvent stocker le même type de données, mais la longueur et la plage de stockage sont différentes, la précision autorisée est différente ou l'espace physique requis (espace disque et mémoire) est différent. Différents sous-types de données du même type volumineux ont parfois des comportements et des propriétés particulières. Par exemple : DATATIME et Les colonnes TIMESAMP peuvent stocker le même type de données (heure et date) et sont précises à la seconde près. Cependant, TIMESTAMP n'utilise que la moitié de l'espace de stockage de DATATIME et dispose de fonctionnalités de mise à jour automatique spéciales basées sur les changements de fuseau horaire. De plus, la plage de temps autorisée par TIMESTAMP est beaucoup plus petite et parfois ses capacités spéciales peuvent devenir des obstacles dont nous, les développeurs, devons tenir compte.

Type entier

Il existe deux types de nombres : le nombre entier et le nombre réel.

Si vous stockez des entiers, vous pouvez utiliser ces types d'entiers : TINNYINT (8), SMALLINT (16), MEDIUMINT (24), INT (32), BIGINT (64).

Le type entier a un attribut facultatif UNSIGNED, ce qui signifie que les valeurs négatives ne sont pas autorisées. Cela peut environ doubler la limite supérieure des nombres positifs.

Par exemple : TINYINT UNSIGNED peut stocker la plage 0~255, tandis que la plage de stockage de TINYINT est de -127~128.

Les types signés et non signés utilisent le même espace de stockage et ont les mêmes fonctions .

Vous pouvez ainsi choisir le type approprié en fonction de la situation réelle.

Votre choix détermine la manière dont Mysql enregistre les données en mémoire et sur le disque.

Les entiers choisissent généralement des entiers BIGINT 64 bits, même dans des environnements 32 bits. (Mais certaines fonctions d'agrégation sont des exceptions, elles sont calculées en utilisant DECIMAL ou DOUBLE)

Mysql peut spécifier la largeur des types entiers.

Par exemple : INT (11), qui n'a aucun sens pour la plupart des applications : il ne limite pas la plage légale de valeurs, mais stipule seulement que certains outils interactifs de Mysql (comme le client en ligne de commande Mysql) sont utilisé Le nombre de caractères affichés. À des fins de stockage et de calcul, INT(1) et INT(20) sont identiques.

Certains moteurs de stockage tiers (tels qu'Infobright) ont parfois des formats de stockage et des schémas de compression personnalisés, qui n'utilisent pas nécessairement le moteur intégré Mysql commun.

Type réel

Un nombre réel est un nombre avec une partie décimale.

Ils stockent non seulement la partie décimale dans le futur, mais peuvent également utiliser DECIMAL pour stocker des entiers plus grands que BIGINT. Mysql prend en charge les types précis et imprécis. Le type DECIMAL est utilisé pour stocker des décimales exactes.

Les opérations de précision sont prises en charge dans Mysql5.0 ou version ultérieure, mais des exceptions se produiront lors de l'utilisation d'opérations à virgule flottante dans Mysql4.1 et versions antérieures (principalement causées par une perte de précision). Les types FLOAT et DECIMAL peuvent être utilisés. . Précisez la progression.

Pour les colonnes DECIMAL, vous pouvez spécifier le nombre maximum de chiffres autorisés avant et après la virgule décimale, ce qui affectera la consommation d'espace de la colonne. Il existe de nombreuses façons de spécifier la précision requise pour les colonnes FLOAT (virgule flottante). Cela amènera Mysql à sélectionner discrètement un type de données différent ou à arrondir la valeur lors du stockage. Cependant, ces précisions sont souvent non standard, c'est pourquoi. est généralement recommandé uniquement. Le type de données spécifié ne spécifie pas la précision.

En raison de l'espace supplémentaire et de la surcharge de calcul, vous devriez essayer de n'utiliser que DECIMAL lorsque vous effectuez des calculs précis sur des décimales. Par exemple, lors du stockage de données financières, si la quantité de données est relativement importante, vous pouvez envisager d'utiliser BIGINT au lieu de DECIMAL et multiplier l'unité monétaire à stocker par le multiple correspondant en fonction du nombre de décimales. Les types FLOAT et DOUBLE prennent en charge les calculs approximatifs à l'aide de l'arithmétique standard à virgule flottante.

Type de chaîne

Mysql prend en charge plusieurs types de chaînes, et il existe de nombreuses variantes de chaque type. Parmi eux, VARCHAR et CHAR sont les deux types de chaînes les plus importants.

注意：Mysql存储引擎存储CHAR或者VARCHAR值的方式在内存中和在磁盘上可能不一样，所以Mysql服务器从存储引擎读取的值可能需要转换为另外一种存储格式。

Le type VARCHAR est utilisé pour stocker des chaînes de longueur variable et est le type de données de chaîne le plus courant.

VARCHAR est plus économe en espace que les types de longueur fixe car il n'utilise que l'espace nécessaire (les chaînes plus courtes utilisent moins d'espace).

VARCHAR nécessite 1 ou 2 octets supplémentaires pour enregistrer la longueur de la chaîne.

VARCHAR économise de l'espace de stockage, il est donc utile pour les performances.

Voici quelques scénarios dans lesquels VARCHAR peut être utilisé :
(1) La longueur maximale de la colonne de chaîne est beaucoup plus grande que la longueur moyenne.
(2) Les colonnes sont rarement mises à jour, la fragmentation n'est donc pas un problème.
(3) utilise un jeu de caractères complexe comme UTF-8, et chaque caractère utilise un nombre différent d'octets pour le stockage.

Le type CHAR est de longueur fixe. (Mysql alloue toujours suffisamment d'espace en fonction de la longueur de chaîne définie)

CHAR convient pour stocker des chaînes très courtes, ou toutes les valeurs sont proches de la même longueur.

Les types similaires à VARCHAR et CHAR sont BINARY et VARBINARY, qui stockent des chaînes binaires.

注意：使用VARCAHR（5）和VARCHAR（200）存储“hello”的空间开销都是一样的，那么使用更短的列有什么优势呢？（事实证明有很大的优势）

Les colonnes plus longues consomment plus de mémoire car Mysql alloue généralement des blocs de mémoire de taille fixe pour contenir les valeurs internes. Ceci est particulièrement problématique lors de l'utilisation de tables temporaires en mémoire pour le tri ou les opérations. C'est tout aussi mauvais lors du tri à l'aide de tables temporaires de disque.

注意：归根到底，最好的策略是只分配真正需要的空间。

Types BLOB et TEXT

BLOB et TEXT sont des types de données de chaîne conçus pour stocker des données volumineuses, en utilisant respectivement les modes binaire et caractère.

En fait, ils appartiennent à deux groupes différents de familles de types de données : les types de chaînes incluent TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT ;

les types binaires incluent TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB;

Type ENUM

Vous pouvez utiliser l'énumération (ENUM) au lieu du type chaîne. Il est souvent recommandé d’utiliser des colonnes d’énumération au lieu des types de chaînes couramment utilisés.

(1) La colonne d'énumération peut stocker des chaînes uniques dans une collection prédéfinie.
(2) Mysql est très compact lors du stockage des énumérations et sera compressé en un ou deux octets en fonction du nombre de valeurs de la liste.
(3) Mysql enregistrera en interne la position de chaque valeur dans la liste sous forme d'entier et enregistrera la "table de recherche" de la relation de mappage "numéro-chaîne" dans le fichier .frm de la table.

注意：有一个令人吃惊的地方是，枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。

注意：枚举最不好的地方是：字符串列表是固定的，添加或者删除字符串必须使用ALTER TABLE，因此对于一系列未来可能会改变的字符串，使用枚举并不是一个好主意，除非接受只能在列表末尾添加元素。

注意：由于Mysql把每个枚举值保存为整数，并且必须进行查找才能转换为字符串，所以枚举列有一些开销。

Type de date et d'heure

Mysql a de nombreux types qui peuvent enregistrer des valeurs de date et d'heure, telles que ANNÉE et DATE.

La granularité temporelle minimale que Mysql peut stocker est de quelques secondes (MariaDB prend en charge les types d'événements au niveau de la microseconde). Mais MySQL peut également utiliser une granularité au niveau de la microseconde pour effectuer des opérations ad hoc.

La plupart du temps, il n'existe pas d'alternative au type, il n'est donc pas question de savoir quel est le meilleur choix.

La seule question est alors de savoir ce qu'il faut faire lors de l'enregistrement de la date et de l'heure.

DATETIME

(1) Ce type peut enregistrer une large plage de valeurs, de 1001 à 9999, avec une précision en secondes.
(2) DATETIME encapsule l'heure et la date dans des entiers au format AAAAMMJJHHMMSS, quel que soit le fuseau horaire.
(3) DATETIME utilise 8 octets d'espace de stockage.

TIMESTAMP

(1) Le type TIMESTAMP stocke le nombre de secondes depuis minuit le 1er janvier 1970, qui est le même que l'horodatage UNIX.
(2) TIMESTAMP n'utilise que 4 octets d'espace de stockage, sa plage est donc beaucoup plus petite que DATETIME.
(3) La valeur affichée par TIMESTAMP dépend du fuseau horaire.

Comparaison entre DATETIME et TIMESTAMP :

(1) Par défaut, si la valeur de la première colonne TIMESTAMP n'est pas spécifiée lors de l'insertion, Mysql définit la valeur de cette colonne à l'heure actuelle. (C'est une fonctionnalité que DATETIME n'a pas)
(2) Lors de l'insertion d'une ligne d'enregistrements, Mysql mettra également à jour la valeur de la première colonne TIMESTAMP par défaut.
(3) La colonne TIMESTAMP est par défaut NOT NULL, ce qui est différent des autres types de données.

Résumé

(1) En plus d'un comportement spécial, TIMESTAMP doit généralement être utilisé autant que possible car il est plus économe en espace que DATETIME.
(2) De manière générale, il n'est pas recommandé d'enregistrer les horodatages UNIX sous forme de valeurs entières. Cela n'apportera aucun avantage. L'enregistrement des horodatages au format entier est généralement peu pratique à traiter.
(3) Si vous devez stocker des valeurs de date et d'heure avec une granularité inférieure à celle des secondes, vous pouvez utiliser le type BIGINT pour stocker des horodatages au niveau de la microseconde, ou utiliser DOUBLE pour stocker la partie décimale après les secondes. utilisez également MariaDB au lieu de Mysql.

Type de données bit

BIT définit un champ contenant un seul bit, BIT(2) stocke 2 bits et la longueur maximale est de 64 bits.

注意：一般建议谨慎使用BIT类型，对于大部分应用来讲最好避免使用这种类型。

Choisissez l'identifiant

Il est très important de choisir le type de données approprié pour l'identifiant (colonne identité).

De manière générale, il est plus probable d'utiliser la colonne d'identité pour comparer avec d'autres valeurs, ou pour trouver d'autres colonnes via la colonne d'identité.

Lors du choix du type de colonne d'identité, vous devez prendre en compte non seulement le type de stockage, mais également la manière dont Mysql effectue des calculs et des comparaisons sur ce type.

Une fois que vous avez sélectionné un type, assurez-vous d'utiliser le même type dans toutes les tables associées.

En partant du principe que les exigences de plage de la valeur peuvent être satisfaites et qu'une marge de croissance future est réservée, le plus petit type de données doit être sélectionné.

注意：整数通常是标识列最好的选择，因为它们很快而且可以使用AUTO_INCREMENT。注意：ENUM和SET是最糟糕的选择了；如果可能也尽可能避免使用字符串作为标识列，因为它们很消耗空间并且通常比数字类慢。

Résumé en texte intégral

Pour la conception d'une base de données, vous devez réfléchir à deux fois avant de choisir le type de colonne de données le plus approprié et de décider du La taille des colonnes de données est une étape critique. En fait, il n'y a pas lieu de paniquer. Quelle que soit la conception de la table de données pour tout type d'exigences, il vous suffit de vous rappeler un principe, un principe très important, très important, très important : utiliser le plus petit type de données pouvant être stocké correctement. données autant que possible.

Ce qui précède concerne la compréhension des types de données MySQL.

Références associéesSite Web PHP chinois

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!