[MySQL] Optimisation du type de données MySQL

Choisissez des types de données optimisés

MySQL prend en charge de nombreux types de données, et choisir le bon type de données est crucial pour atteindre des performances élevées. Quel que soit le type de données que vous stockez, voici quelques principes pour vous aider à faire de meilleurs choix.

Plus petit est généralement mieux

En général, vous devriez essayer d'utiliser le plus petit type de données capable de stocker correctement les données (par exemple, vous n'avez besoin de stocker que 0 à 200, tinyint non signé est préférable ). 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.

Simple, c'est bien

Les opérations sur des types de données simples nécessitent généralement moins de cycles CPU. Par exemple, les opérations sur les entiers sont moins coûteuses que les opérations sur les caractères, car les jeux de caractères et les classements (classements) rendent les comparaisons de chaînes plus complexes que les comparaisons d'entiers. Voici deux exemples : l'un est que les types intégrés de MySQL (tels que date, heure, datetime) doivent être utilisés à la place des chaînes pour stocker la date et l'heure, et l'autre est que des entiers doivent être utilisés pour stocker les adresses IP.

Essayez d'éviter d'utiliser NULL

De nombreuses tables contiennent des colonnes pouvant être NULL, même si l'application n'a pas besoin d'enregistrer NULL, car NULL est l'attribut par défaut de la colonne. Il est généralement préférable de spécifier les colonnes comme NOT NULL, sauf si vous avez vraiment besoin de stocker des valeurs NULL.

Il est plus difficile pour MySQL d'optimiser si la requête contient des colonnes NULLable, car les colonnes NULLable 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 un traitement spécial dans MySQL. Lorsqu'une colonne NULLable est indexée, chaque enregistrement d'index nécessite un octet supplémentaire, ce qui, dans MyISAM, peut même faire qu'un index de taille fixe (comme un index avec une seule colonne entière) devienne un index de taille variable.

Habituellement, l'amélioration des performances apportée par la modification de la valeur NULL en NOT NULL est relativement faible, donc (lors du réglage) il n'est pas nécessaire de rechercher et de modifier d'abord cette situation dans le schéma existant, à moins qu'elle ne soit déterminée. causer des problèmes. Cependant, si vous envisagez de créer un index sur une colonne, vous devez éviter de concevoir la colonne de manière à ce qu'elle soit NULLable.

Bien sûr, il existe des exceptions. Par exemple, il convient de mentionner qu'InnoDB utilise un bit séparé pour stocker les valeurs NULL, il a donc de bonnes performances pour les données clairsemées (la plupart des valeurs sont NULL, et seulement quelques-unes). les lignes sont des valeurs non NULL). Mais cela ne s'applique pas à MyISAM.

---

Types entiers

Si vous stockez des entiers, vous pouvez utiliser ces types entiers : TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Utilisez respectivement un espace de stockage de 8, 16, 24, 32 et 64 bits. Leur plage de stockage va de -2 à la puissance (N-1) à 2 à la puissance (N-1) -1, où N est le nombre de bits dans l'espace de stockage.

Le type entier a l'attribut facultatif UNSIGNED, ce qui signifie que les valeurs négatives ne sont pas autorisées, ce qui peut environ doubler la limite supérieure des nombres positifs. Par exemple, la plage que TINYINT UNSIGNED peut stocker est 0-. 255, et la plage de stockage de TINYINT est de -128~127.

MySQL peut spécifier la largeur des types entiers, tels que INT(11). Pour la plupart des applications, cela n'a aucun sens : cela ne limite pas la plage légale de valeurs. Les connaissances stipulent certains outils interactifs de MySQL (tels que). Client de ligne de commande MySQL) permet d'afficher le nombre de caractères. Pour le stockage, INT(1) et INT(20) sont identiques.

Type réel

Les nombres réels sont des nombres avec une partie décimale. Cependant, ils ne servent pas uniquement à stocker des parties décimales, DECIMAL peut également être utilisé pour stocker des entiers plus grands que BIGINT. MySQL prend en charge les types précis et imprécis.

Les types FLOAT et DOUBLE prennent en charge les calculs approximatifs en utilisant l'arithmétique standard à virgule flottante. Si vous avez besoin de savoir comment les opérations en virgule flottante sont calculées, vous devez étudier l'implémentation spécifique des nombres à virgule flottante sur la plateforme que vous utilisez.

Le type DECIMAL est utilisé pour stocker des décimales précises. Cependant, comme le processeur ne prend pas en charge le calcul direct de DECIMAL, dans MySQL 5.0 et les versions ultérieures, le serveur MySQL lui-même implémente le calcul de haute précision de DECIMAL. Relativement parlant, cela est plus lent que le processeur prenant directement en charge les opérations natives en virgule flottante.

Les types à virgule flottante et DECIMAL peuvent spécifier la précision. Pour les colonnes DECIMAL, vous pouvez spécifier le nombre maximum de chiffres autorisés avant et après la virgule décimale. Cela affecte la consommation d'espace de la colonne.

Les types à virgule flottante utilisent généralement moins d'espace que DECIMAL lors du stockage de la même plage de valeurs. FLOAT utilise 4 octets de stockage. DOUBLE occupe 8 octets et a une précision plus élevée et une portée plus large que FLOAT.

En raison de l'espace supplémentaire et de la charge de calcul requise, vous devriez essayer de n'utiliser DECIMAL que lorsque vous effectuez des calculs précis sur des décimales, comme le stockage de données financières. Cependant, lorsque 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 points décimaux. En supposant que vous souhaitiez stocker des données financières précises au dix millième, vous pouvez multiplier tous les montants par 1 million, puis stocker les résultats dans BIGINT. Cela peut éviter les problèmes de calculs de stockage en virgule flottante inexacts et le coût élevé des calculs DECIMAL précis.

Type de chaîne

La description suivante suppose que le moteur de stockage utilisé est InnoDB/ou MyISAM. S'il n'est pas supporté par ces deux moteurs de stockage, merci de vous référer à la documentation du moteur de stockage utilisé.

VARCHAR et CHAR

VARCHAR : il est plus peu encombrant que les types de longueur fixe car il n'utilise que l'espace nécessaire. VARCHAR économise de l'espace, ce qui améliore également les performances. Cependant, comme la ligne est de longueur variable, elle peut devenir plus longue que l'originale lors de UPDATE, ce qui nécessite un travail supplémentaire.

Il est approprié d'utiliser VARCHAR dans les situations suivantes : la longueur maximale de la chaîne est beaucoup plus grande que la longueur moyenne ; la colonne est moins mise à jour, donc la fragmentation n'est pas un problème avec un jeu de caractères complexe comme UTF ; -8 est utilisé et chaque caractère utilise un nombre d'octets différent.

Dans les versions 5.0 ou supérieures, MySQL préserve les espaces de fin lors du stockage et de la récupération. InnoDB est plus flexible. Il peut stocker des VARCHAR longs sous forme de BLOB

CHAR : longueur fixe Lors du stockage des valeurs CHAR, MySQL supprimera tous les espaces de fin. Le type CHAR de longueur fixe est moins sujet à la fragmentation et, pour les colonnes très courtes, CHAR est également plus efficace en termes d'espace de stockage que VARCHAR, qui possède un ou deux octets supplémentaires de longueur d'enregistrement. CHAR convient au stockage de chaînes très courtes ou lorsque toutes les valeurs sont proches de la même longueur. Par exemple : CHAR est très adapté pour stocker la valeur MD5 d'un mot de passe car il s'agit d'une valeur de longueur fixe. CHAR sera complété par des espaces si nécessaire pour faciliter la comparaison.

Les types similaires à CHAR et VARCHAR sont BINARY et VARBINARY, qui stockent des chaînes binaires. Les chaînes binaires stockent des bytecodes plutôt que des caractères.

L'avantage de la comparaison binaire n'est pas seulement la sensibilité à la casse. MySQL compare les chaînes BINAIRES un octet à la fois et les compare en fonction de la valeur de l'octet. Par conséquent, le binaire est beaucoup plus simple que les caractères, donc plus rapide.

Types BLOB et TEXT

Types BLOB et TEXT : BLOB et TEXT sont tous deux des types de données de chaîne conçus pour stocker de grandes quantités de données et sont stockés respectivement en modes binaire et caractère. Lorsque les valeurs BLOB et TEXT sont trop grandes, InnoDB utilisera une zone de stockage « externe » dédiée pour le stockage. Le pointeur de stockage du champ de table d'origine pointe vers la zone de stockage externe.

MySQL trie les colonnes BLOB et TEXT différemment des autres types : il trie uniquement les max_sort_length premiers octets de la colonne au lieu de la chaîne entière. Si vous n'avez besoin de trier qu'une petite partie des premiers caractères, vous pouvez réduire la configuration de max_sort_length ou utiliser ORDER BY SUSTRING(column, length).

MySQL ne peut pas indexer les chaînes complètes des colonnes BLOB et TEXT, ni utiliser ces index pour éliminer le tri.

Utilisez l'énumération (ENUM) au lieu du type chaîne

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.

Remarque : Une chose surprenante est que les champs d'énumération sont triés par entiers stockés en interne plutôt que par chaînes définies.

Remarque : le pire avec les énumérations est que la liste de chaînes est fixe. L'ajout ou la suppression de chaînes doit utiliser ALTER TABLE, donc pour une série de chaînes qui peuvent changer dans le futur, utilisez des énumérations et ce n'est pas une bonne idée. sauf si vous acceptez que vous ne puissiez ajouter des éléments qu'à la fin de la liste.

Remarque : il y a une certaine surcharge pour les colonnes d'énumération puisque MySQL enregistre chaque valeur d'énumération sous forme d'entier et doit effectuer une recherche pour la convertir en chaîne.

Type de date et d'heure

Pour plus de détails sur les types de données et leur utilisation, veuillez consulter : http://www.php.cn/

Mysql a de nombreux types qui peuvent enregistrer 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 effectuer des opérations ad hoc avec une granularité de l’ordre de la microseconde.

La plupart du temps, il n'existe pas d'alternatives 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 du 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 formats d'horodatage avec des nombres entiers 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. Vous pouvez également utiliser MariaDB. au lieu de MySQL.

Types de données binaires

MySQL propose quelques types de stockage qui utilisent des bits compacts pour stocker les données. Tous ces types de bits, quels que soient le format de stockage et le traitement sous-jacents, sont techniquement des types de chaînes.

BIT

La colonne BIT peut être utilisée pour stocker une ou plusieurs valeurs vrai/faux dans une colonne. BIT(1) définit un champ contenant un seul bit, BIT(2) stocke 2 bits, et ainsi de suite. La longueur maximale d'une colonne BIT est de 64 bits.

Si vous souhaitez stocker une valeur vrai/faux dans un peu d'espace de stockage, une autre méthode consiste à créer une colonne CHAR(0) qui peut être nulle. Cette colonne peut contenir une valeur nulle (NULL) ou une chaîne de longueur nulle (chaîne vide).

SET

Si vous devez enregistrer de nombreuses valeurs vraies/fausses, vous pouvez envisager de fusionner ces colonnes dans un type de données SET, qui est représenté en interne par MySQL comme un ensemble de bits compressés. Cela utilise efficacement l'espace de stockage et MySQL dispose de fonctions telles que FIND_IN_SET() et FIELD() pour une utilisation facile dans les requêtes. Son principal inconvénient est que la modification des définitions de colonnes est coûteuse : ALTER TABLE est nécessaire, ce qui est une opération très coûteuse pour les grandes tables. De manière générale, il n'est pas non plus possible de rechercher dans les index des colonnes SET.

Une alternative à SET consiste à envelopper une série de bits avec un entier. Par exemple, 8 bits peuvent être regroupés dans un TINYINT et utilisés dans des opérations au niveau du bit. Vous pouvez simplifier cela en définissant des constantes nommées pour chaque bit de votre application.

Par rapport à SET, le principal avantage de cette méthode est que vous pouvez modifier la valeur "énumération" représentée par le champ sans utiliser ALTER TABLE. L'inconvénient est que l'instruction de requête est plus difficile à écrire et plus difficile. comprendre (quand le 5ème bit Qu'est-ce que cela signifie lorsqu'un bit est défini ? ). Certaines personnes sont très à l’aise avec cette approche, d’autres non. L’adoption ou non de cette technique dépend donc de vos préférences personnelles.

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

• Les entiers sont généralement le meilleur choix pour les colonnes d'identité car ils sont rapides et peuvent être utilisés AUTO_INCREMENT.

• ENUM et SET sont les pires choix

• Si possible, évitez d'utiliser des chaînes comme colonnes d'identité car elles consomment beaucoup d'espace et sont généralement plus lent que les classes numériques.

Données de type spécial

Certains types de données ne sont pas directement cohérents avec les types intégrés. Les horodatages avec une précision inférieure à la seconde en sont un exemple.

Un autre exemple est que les gens utilisent souvent VARCHAR(15) pour stocker des adresses IP. Cependant, il s’agit en réalité d’entiers non signés de 32 bits, et non de chaînes. Le point décimal est utilisé pour diviser le champ en quatre segments pour faciliter la lecture. Les adresses IP doivent donc être stockées sous forme d’entiers non signés. MySQL fournit les fonctions INET_ATON() et INET_NTOA() pour convertir entre ces deux méthodes de représentation.

Choisissez des types de données optimisés

MySQL prend en charge de nombreux types de données, et le choix du bon type de données est crucial pour obtenir des performances élevées. Quel que soit le type de données que vous stockez, voici quelques principes pour vous aider à faire de meilleurs choix.

Plus petit est généralement mieux

En général, vous devriez essayer d'utiliser le plus petit type de données capable de stocker correctement les données (par exemple, vous n'avez besoin de stocker que 0 à 200, tinyint non signé est préférable ). 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.

Simple, c'est bien

Les opérations sur des types de données simples nécessitent généralement moins de cycles CPU. Par exemple, les opérations sur les entiers sont moins coûteuses que les opérations sur les caractères, car les jeux de caractères et les classements (classements) rendent les comparaisons de chaînes plus complexes que les comparaisons d'entiers. Voici deux exemples : l'un est que les types intégrés de MySQL (tels que date, heure, datetime) doivent être utilisés à la place des chaînes pour stocker la date et l'heure, et l'autre est que des entiers doivent être utilisés pour stocker les adresses IP.

Essayez d'éviter d'utiliser NULL

De nombreuses tables contiennent des colonnes pouvant être NULL, même si l'application n'a pas besoin d'enregistrer NULL, car NULL est l'attribut par défaut de la colonne. Il est généralement préférable de spécifier les colonnes comme NOT NULL, sauf si vous avez vraiment besoin de stocker des valeurs NULL.

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM中甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

通常把可为NULL的值改为NOT NULL带来的性能提升比较小，所以（调优时）没有必要首先在现有的schema中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建立索引，就应该避免设计成可为NULL的列。

当然也有例外，例如值得一提的是，InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据（大部分值为NULL，只有少数行为非NULL的值）有良好的空间效率。但这一点不适用于MyISAM。

---

整数类型

如果存储整数，可以使用这几种整数类型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。分别使用8,16,24,32,64位存储空间。它们的存储范围从 -2的(N-1)次方 到 2的(N-1)次方-1，其中N为存储空间的位数。

整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍，例如TINYINT UNSIGNED可以存储的范围是0-255，而TINYINT 的存储范围是-128~127。

MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的：他不会限制值得合法范围，知识规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储来说，INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。

FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算。如果需要知道浮点运算时怎么计算的，则需要研究所使用的平台的浮点数的具体实现。

DECIMAL 类型用于存储精确的小数。但因为CPU不支持对DECIMAL的直接计算，所以在MySQL5.0及更高版本中，MySQL服务器自身实现了DECIMAL的高精度计算。相对而言，这比CPU直接支持原生浮点数运算要慢。

浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数点的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以100W，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

下面的描述假设使用的存储引擎是InnoDB/或者MyISAM。如果不是这两种存储引擎的，请参考所使用的存储引擎的文档。

VARCHAR et CHAR

VARCHAR : il est plus peu encombrant que les types de longueur fixe car il n'utilise que l'espace nécessaire. VARCHAR économise de l'espace, ce qui améliore également les performances. Cependant, comme la ligne est de longueur variable, elle peut devenir plus longue que l'originale lors de UPDATE, ce qui nécessite un travail supplémentaire.

Il est approprié d'utiliser VARCHAR dans les situations suivantes : la longueur maximale de la chaîne est beaucoup plus grande que la longueur moyenne ; la colonne est moins mise à jour, donc la fragmentation n'est pas un problème avec un jeu de caractères complexe comme UTF ; -8 est utilisé et chaque caractère utilise un nombre d'octets différent.

Dans les versions 5.0 ou supérieures, MySQL préserve les espaces de fin lors du stockage et de la récupération. InnoDB est plus flexible. Il peut stocker des VARCHAR longs sous forme de BLOB

CHAR : longueur fixe Lors du stockage des valeurs CHAR, MySQL supprimera tous les espaces de fin. Le type CHAR de longueur fixe est moins sujet à la fragmentation et, pour les colonnes très courtes, CHAR est également plus efficace en termes d'espace de stockage que VARCHAR, qui possède un ou deux octets supplémentaires de longueur d'enregistrement. CHAR convient au stockage de chaînes très courtes ou lorsque toutes les valeurs sont proches de la même longueur. Par exemple : CHAR est très adapté pour stocker la valeur MD5 d'un mot de passe car il s'agit d'une valeur de longueur fixe. CHAR sera complété par des espaces si nécessaire pour faciliter la comparaison.

Les types similaires à CHAR et VARCHAR sont BINARY et VARBINARY, qui stockent des chaînes binaires. Les chaînes binaires stockent des bytecodes plutôt que des caractères.

L'avantage de la comparaison binaire n'est pas seulement la sensibilité à la casse. MySQL compare les chaînes BINAIRES un octet à la fois et les compare en fonction de la valeur de l'octet. Par conséquent, le binaire est beaucoup plus simple que les caractères, donc plus rapide.

Types BLOB et TEXT

Types BLOB et TEXT : BLOB et TEXT sont tous deux des types de données de chaîne conçus pour stocker de grandes quantités de données et sont stockés respectivement en modes binaire et caractère. Lorsque les valeurs BLOB et TEXT sont trop grandes, InnoDB utilisera une zone de stockage « externe » dédiée pour le stockage. Le pointeur de stockage du champ de table d'origine pointe vers la zone de stockage externe.

MySQL trie les colonnes BLOB et TEXT différemment des autres types : il trie uniquement les max_sort_length premiers octets de la colonne au lieu de la chaîne entière. Si vous n'avez besoin de trier qu'une petite partie des premiers caractères, vous pouvez réduire la configuration de max_sort_length ou utiliser ORDER BY SUSTRING(column, length).

MySQL ne peut pas indexer les chaînes complètes des colonnes BLOB et TEXT, ni utiliser ces index pour éliminer le tri.

Utilisez l'énumération (ENUM) au lieu du type chaîne

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.

Remarque : Une chose surprenante est que les champs d'énumération sont triés par entiers stockés en interne plutôt que par chaînes définies.

Remarque : le pire avec les énumérations est que la liste de chaînes est fixe. L'ajout ou la suppression de chaînes doit utiliser ALTER TABLE, donc pour une série de chaînes qui peuvent changer dans le futur, utilisez des énumérations et ce n'est pas une bonne idée. sauf si vous acceptez que vous ne puissiez ajouter des éléments qu'à la fin de la liste.

Remarque : il y a une certaine surcharge pour les colonnes d'énumération puisque MySQL enregistre chaque valeur d'énumération sous forme d'entier et doit effectuer une recherche pour la convertir en chaîne.

Type de date et d'heure

Pour plus de détails sur les types de données et leur utilisation, veuillez consulter : http://www.php.cn/

Mysql a de nombreux types qui peuvent enregistrer 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 effectuer des opérations ad hoc avec une granularité de l’ordre de la microseconde.

La plupart du temps, il n'existe pas d'alternatives 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）除了特殊行为之外，通常也应该尽可能使用TIMESTAMP，因为它比DATETIME空间效率更高。 （2）一般来讲不建议把UNIX时间戳保存为整数值，这不会带来任何收益，用整数保存时间戳格式通常不方便处理。 （3）如果需呀存储比秒更小粒度的日期和时间值，可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分，也可以用MariaDB替代Mysql。

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型的。

BIT

可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义了一个包含单个位的字段，BIT(2)存储2个位，依次类推。BIT列的最大长度是64位。

如果想在一个bit的存储空间中存储一个true/false值，另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存空值(NULL)或者长度为零的字符串(空字符串)。

SET

Si vous devez enregistrer de nombreuses valeurs vraies/fausses, envisagez de fusionner ces colonnes dans un type de données SET, qui est représenté en interne par MySQL comme un ensemble de bits compressés. Cela utilise efficacement l'espace de stockage et MySQL dispose de fonctions telles que FIND_IN_SET() et FIELD() pour une utilisation facile dans les requêtes. Son principal inconvénient est que la modification des définitions de colonnes est coûteuse : ALTER TABLE est nécessaire, ce qui est une opération très coûteuse pour les grandes tables. De manière générale, il n'est pas non plus possible de rechercher dans les index des colonnes SET.

Une alternative à SET consiste à envelopper une série de bits avec un entier. Par exemple, 8 bits peuvent être regroupés dans un TINYINT et utilisés dans des opérations au niveau du bit. Vous pouvez simplifier cela en définissant des constantes nommées pour chaque bit de votre application.

Par rapport à SET, le principal avantage de cette méthode est que vous pouvez modifier la valeur "énumération" représentée par le champ sans utiliser ALTER TABLE. L'inconvénient est que l'instruction de requête est plus difficile à écrire et plus difficile. comprendre (quand le 5ème bit Qu'est-ce que cela signifie lorsqu'un bit est défini ? ). Certaines personnes sont très à l’aise avec cette approche, d’autres non. L’adoption ou non de cette technique dépend donc de vos préférences personnelles.

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

• Les entiers sont généralement le meilleur choix pour les colonnes d'identité car ils sont rapides et peuvent être utilisés AUTO_INCREMENT.

• ENUM et SET sont les pires choix

• Si possible, évitez d'utiliser des chaînes comme colonnes d'identité car elles consomment beaucoup d'espace et sont généralement plus lent que les classes numériques.

Données de type spécial

Certains types de données ne sont pas directement cohérents avec les types intégrés. Les horodatages avec une précision inférieure à la seconde en sont un exemple.

Un autre exemple est que les gens utilisent souvent VARCHAR(15) pour stocker des adresses IP. Cependant, il s’agit en réalité d’entiers non signés de 32 bits, et non de chaînes. Le point décimal est utilisé pour diviser le champ en quatre segments pour faciliter la lecture. Les adresses IP doivent donc être stockées sous forme d’entiers non signés. MySQL fournit les fonctions INET_ATON() et INET_NTOA() pour convertir entre ces deux méthodes de représentation.

Ce qui précède est le contenu de l'optimisation du type de données MySQL [MySQL] Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !