Comment concevoir une table MySQL hautes performances

Une bonne conception logique et une bonne conception physique sont les pierres angulaires de hautes performances. Le schéma doit être conçu en fonction des instructions de requête que le système exécutera, ce qui nécessite souvent de peser divers facteurs.

1. Sélectionnez le type de données optimisé

MySQL prend en charge de nombreux types de données. Le choix du bon type de données est crucial pour obtenir des performances élevées.

Plus petit est généralement mieux

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.

Gardez les choses simples

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 nombres entiers sont moins coûteuses que les opérations sur les caractères, car les jeux de caractères et les classements rendent les comparaisons de caractères plus complexes que les comparaisons sur les nombres entiers.

Essayez d'éviter NULL

Si la requête contient des colonnes NULL, il est plus difficile à optimiser pour MySQL 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 un traitement spécial dans MySQL. Lorsque les colonnes NULLable sont indexées, 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.

Bien sûr, il existe des exceptions. Par exemple, InnoDB utilise un bit séparé pour stocker les valeurs NULL, il a donc une bonne efficacité spatiale pour les données clairsemées.

1.Type entier

Il existe deux types de nombres : les nombres entiers et les nombres réels. Si vous stockez des entiers, vous pouvez utiliser ces types d'entiers : TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Utilisez respectivement un espace de stockage de 8, 16, 24, 32 et 64 bits.

Les types entiers ont un attribut facultatif **UNSIGNED**, ce qui signifie que les valeurs négatives ne sont pas autorisées, ce qui double approximativement 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 -128 à 127.

Les types signés et non signés utilisent le même espace de stockage et ont les mêmes performances, vous pouvez donc choisir le type approprié en fonction de la situation réelle.

Votre choix détermine la manière dont MySQL stocke les données en mémoire et sur le disque. Cependant, les calculs d'entiers utilisent généralement des entiers BIGINT 64 bits, même dans un environnement 32 bits. (L'exception concerne certaines fonctions d'agrégation, qui utilisent DECIMAL ou DOUBLE pour les calculs).

MySQL peut spécifier la largeur des types entiers, tels que INT(11), ce qui n'a aucun sens pour la plupart des applications : cela ne limite pas la plage légale de valeurs, mais spécifie uniquement certains outils interactifs de MySQL (tels que le client de ligne de commande MySQL ) Permet d'afficher le nombre de caractères. À des fins de stockage et de calcul, INT(1) et INT(20) sont identiques.

2.Type de numéro 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.

Les types FLOAT et DOUBLE prennent en charge les calculs approximatifs à l'aide d'opérations à virgule flottante standard.

Le type DECIMAL est utilisé pour stocker des décimales précises.

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.

Il existe différentes manières de spécifier la précision requise pour les colonnes à virgule flottante, ce qui amènera MySQL à choisir un type de données différent ou à arrondir la valeur lors du stockage. Ces définitions de précision ne sont pas standard, nous vous recommandons donc de spécifier uniquement le type de données et non la précision.

Les types à virgule flottante utilisent généralement moins d'espace que DECIMAL lors du stockage de valeurs dans la même plage. 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. Comme pour les types entiers, tout ce que vous pouvez choisir est le type de stockage ; MySQL utilise DOUBLE comme type pour les calculs internes en virgule flottante.

En raison de l'espace supplémentaire et de la charge de calcul requise, vous devriez essayer d'utiliser uniquement DECIMAL lorsque vous effectuez des calculs précis sur des décimales. Mais lorsque les données sont relativement volumineuses, vous pouvez envisager d'utiliser BIGINT au lieu de DECIMAL. Il suffit de multiplier l'unité monétaire à stocker par le multiple correspondant en fonction du nombre de décimales.

3. Type de chaîne

VARCHAR

• Utilisé pour stocker des chaînes variables, la longueur est prise en charge jusqu'à 65535
• Nécessite 1 ou 2 octets supplémentaires pour enregistrer la longueur de la chaîne
• Convient pour : La longueur maximale de la chaîne est beaucoup plus grande que la longueur moyenne. Les mises à jour sont rares ;

CHAR

Longueur fixe, la plage de longueurs est de 1 à 255
Convient pour : stocker des chaînes très courtes, ou toutes les valeurs proches de la même longueur fréquemment modifiées
• ;

La générosité n'est pas sage

La surcharge d'espace liée au stockage de « bonjour » à l'aide de VARCHAR(5) et VARCHAR(200) est la même. Y a-t-il donc des avantages à utiliser des colonnes plus courtes ?

Cela présente de grands avantages. 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.

La meilleure stratégie consiste donc à allouer uniquement l’espace dont vous avez réellement besoin.

4.Types BLOB et TEXTE

BLOB et TEXT sont tous deux des types de données chaîne conçus pour stocker des données volumineuses et sont stockés respectivement en modes binaire et caractère.

Contrairement à d'autres types, MySQL traite chaque valeur BLOB et TEXT comme un objet indépendant. Les moteurs de stockage effectuent généralement un traitement spécial lors du stockage. Lorsque les valeurs BLOB et TEXT sont trop grandes, InnoDB utilisera une zone de stockage "externe" dédiée pour le stockage. À ce stade, chaque valeur nécessite 1 à 4 octets pour être stockée dans la ligne. .

La seule différence entre BLOB et TEXT est que le type BLOB stocke les données binaires et n'a pas de classement ni de jeu de caractères, tandis que le type TEXT a un jeu de caractères et un classement

5.Type de date et d'heure

La plupart du temps, il n'existe pas d'alternative au type, il n'y a donc aucune question sur quel est le meilleur choix. Le seul problème est ce qu'il faut faire lors de l'enregistrement de la date et de l'heure. MySQL propose deux types de date similaires : DATE TIME et TIMESTAMP.

Mais actuellement, nous préférons la méthode de stockage des horodatages, donc DATE TIME et TIMESTAMP ne seront pas trop expliqués ici.

6.Autres types

6.1 Sélectionnez l'identifiant

Le plus petit type de données doit être sélectionné en partant du principe qu'il peut répondre aux besoins de la plage de valeurs et laisser place à une croissance future.

• Type entier

Les entiers sont généralement le meilleur choix pour les colonnes d'identité car ils sont rapides et peuvent utiliser AUTO_INCREMENT.

• Types ENUM et SET

Les types EMUM et SET sont généralement un mauvais choix pour les colonnes d'identité, bien qu'ils puissent convenir à certaines "tables de définition" statiques qui ne contiennent que des états ou des types fixes. Les colonnes ENUM et SET conviennent au stockage d'informations fixes, telles que le statut de la commande, le type de produit et le sexe de la personne.

• Type de chaîne

Si possible, les types chaîne doivent être évités en tant que colonnes d'identité, car ils consomment de l'espace et sont généralement plus lents que les types numériques.

Vous devez également prêter plus d'attention aux chaînes complètement "aléatoires", telles que les chaînes générées par MDS(), SHAl() ou UUID(). Les nouvelles valeurs générées par ces fonctions sont arbitrairement réparties sur un grand espace, ce qui peut rendre très lentes INSERT et certaines instructions SELECT. Si des valeurs UUID sont stockées, le signe "-" doit être supprimé.

6.2 Données de type spécial

Certains types de puits de données ne correspondent pas directement aux types intégrés. Les horodatages avec une faible précision en kilosecondes en sont un autre exemple : les utilisateurs utilisent souvent des colonnes 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. La représentation de l'adresse divisée en quatre segments à l'aide de points décimaux a simplement pour but de 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.

2. Conception de la structure de la table

1. Paradigme et anti-paradigme

Il existe généralement de nombreuses façons de représenter des données données, de entièrement normalisées à entièrement dénormalisées, et un compromis entre les deux. Dans une base de données normalisée, chaque fait apparaît exactement une fois. En revanche, dans une base de données dénormalisée, les informations sont redondantes et peuvent être stockées à plusieurs endroits.

Avantages et inconvénients du paradigme

Lorsque l'on envisage d'améliorer les performances, il est souvent recommandé de normaliser le schéma, en particulier dans les scénarios gourmands en écriture.

• Les opérations de mise à jour normalisées sont généralement plus rapides que les opérations dénormalisées.
• Lorsque les données sont bien normalisées, il y a peu ou pas de données en double, donc moins de données doivent être modifiées.
• Les tables normalisées sont généralement plus petites et tiennent mieux en mémoire, les opérations seront donc effectuées plus rapidement.
• Moins de données redondantes signifie que moins d'instructions DISTINCT ou GROUP BY sont nécessaires lors de la récupération des données de liste.

Avantages et inconvénients de l'anti-paradigme

Il n'est pas nécessaire de disposer de tables associées, donc le pire des cas pour la plupart des requêtes, même si la table n'utilise pas d'index, est une analyse complète de la table. Cela peut être beaucoup plus rapide qu'associatif lorsque les données sont plus volumineuses que la mémoire, car les E/S aléatoires sont évitées.

Les tables individuelles peuvent également utiliser des stratégies d'indexation plus efficaces.

Mélanger normalisation et dénormalisation

Dans les applications pratiques, ils doivent souvent être mélangés et des schémas partiellement normalisés, des tables de cache et d'autres techniques peuvent être utilisés.

Ajoutez de manière appropriée des champs redondants au tableau, tels que la priorité des performances, mais cela augmentera la complexité. Les requêtes liées aux tables peuvent être évitées.

Facile et familier avec le paradigme de la base de données

<br> Première forme normale (1NF) : les valeurs des champs sont atomiques et ne peuvent pas être divisées (tous les systèmes de bases de données relationnelles satisfont à la première forme normale) ;<br> Par exemple : le champ nom, où le nom et le prénom forment un tout. Si le nom et le prénom sont distingués, il faut mettre en place deux champs indépendants ;

.

Deuxième forme normale (2NF) : une table doit avoir une clé primaire, c'est-à-dire que chaque ligne de données peut être distinguée de manière unique ;
Remarque : la première forme normale doit d'abord être satisfaite ;

Troisième forme normale (3NF) : une table ne peut pas contenir d'informations sur des champs non clés dans d'autres tables associées, c'est-à-dire que la table de données ne peut pas avoir de champs redondants ;
Remarque : la deuxième forme normale doit d'abord être satisfaite ;

2. Les champs du tableau sont moins raffinés

• E/S efficace
• Les champs sont séparés et entretenus facilement
• Évaluation de rangée de 500W de volume de 1G de table unique
• Une seule ligne ne doit pas dépasser 200 octets
• Pas plus de 50 champs INT dans une seule table
• Pas plus de 20 champs CHAR(10) dans une seule table
• Il est recommandé que le nombre de champs dans une seule table soit contrôlé dans les 20
• Split TEXT/BLOB, les performances de traitement du type TEXT sont bien inférieures à celles de VARCHAR, ce qui oblige la génération de tables temporaires sur le disque dur à gaspiller plus d'espace.

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!