Explication détaillée des méthodes d'optimisation des types de données dans MySQL

Cet article présente principalement les méthodes d'optimisation liées au type de données dans MySQL, y compris l'utilisation d'index multi-colonnes et d'autres méthodes d'optimisation associées. Les amis dans le besoin peuvent se référer à

. Sélections de types qui contribuent à l'efficacité

1. Gardez vos données aussi petites que possible

L'une des optimisations les plus élémentaires consiste à conserver vos données (et index) sur le disque. (et occupent le moins d'espace possible en mémoire). Cela peut apporter une énorme amélioration car les lectures sur disque sont plus rapides et utilisent généralement moins de mémoire principale. Si vous indexez sur des colonnes plus petites, l'index consomme également moins de ressources.

Vous pouvez utiliser les techniques suivantes pour améliorer les performances des tables et minimiser l'espace de stockage :

·Utilisez le type le plus efficace (le plus petit) possible. MySQL possède de nombreuses spécialisations qui permettent d'économiser de l'espace disque et de la mémoire.

·Si possible pour réduire la taille du tableau, utilisez un type entier plus petit. Par exemple, MEDIUMINT est souvent meilleur que INT.

· Si possible, déclarez la colonne NON NULL. Cela rend tout plus rapide et vous économisez un bit par colonne. Notez que si vous avez vraiment besoin de NULL dans votre application, vous devez l'utiliser sans aucun doute, évitez simplement de l'avoir sur toutes les colonnes par défaut.

2. Utilisez des colonnes de longueur fixe au lieu de colonnes de longueur variable

Cette directive est particulièrement importante pour les tableaux fréquemment modifiés et sujets à la fragmentation. Par exemple, les colonnes CHAR doivent être sélectionnées au lieu des colonnes VARCHAR. Le compromis est que le tableau prend plus de place lorsque vous utilisez des colonnes de longueur fixe, mais si vous pouvez vous permettre le coût de l'espace, l'utilisation de lignes de longueur fixe sera beaucoup plus rapide que l'utilisation de lignes de longueur variable.

3. Définissez la colonne comme NOT NULL

Cela traitera plus rapidement et nécessitera moins d'espace. Cela simplifie aussi parfois les requêtes car il n'est pas nécessaire de vérifier le cas particulier NULL.

4. Pensez à utiliser une colonne ENUM

Si vous avez une colonne qui ne contient qu'un nombre limité de valeurs spécifiques, vous devriez envisager de la convertir en colonne ENUM. Les valeurs des colonnes ENUM peuvent être traitées plus rapidement car elles sont représentées en interne sous forme de valeurs numériques.

À propos des types BLOB et TEXT
1 Avantages de l'utilisation des types BLOB et TEXT

Il est possible d'utiliser BLOB pour stocker des données packagées ou non dans les applications Data. une récupération qui nécessitait initialement plusieurs opérations de récupération peut être réalisée en une seule opération de récupération. Il est également utile pour stocker des données qui ne sont pas facilement représentées par des structures de tableaux standard ou des données qui changent au fil du temps.

2. Inconvénients possibles de l'utilisation des types BLOB et TEXT

D'un autre côté, les valeurs BLOB ont également leurs propres problèmes inhérents, en particulier lors de l'exécution d'un grand nombre d'opérations DELETE ou UPDATE. La suppression d'un BLOB laisse un grand vide dans le tableau qui devra ensuite être rempli avec un enregistrement ou éventuellement plusieurs enregistrements de tailles différentes.

Évitez de récupérer de grandes valeurs BLOB ou TEXT sauf si cela est nécessaire. Par exemple, une requête SELECT * n'est pas une bonne idée à moins que vous soyez sûr que la clause WHERE limitera les résultats exactement aux lignes souhaitées. Cela pourrait faire glisser de très grandes valeurs BLOB sur le réseau sans destination. Il s'agit d'une autre situation dans laquelle les informations d'identification BLOB stockées dans une autre colonne sont utiles. Vous pouvez rechercher la colonne pour déterminer la ligne souhaitée, puis récupérer la valeur BLOB de la ligne qualifiée.

3. Directives nécessaires

Utilisez OPTIMIZE TABLE pour les tables sujettes à la fragmentation
Les tables fortement modifiées, en particulier celles avec des colonnes de longueur variable, sont sujettes à la fragmentation. La fragmentation est mauvaise car elle crée de l'espace inutilisé dans les blocs de disque où la table est stockée. Au fil du temps, davantage de blocs doivent être lus pour obtenir des lignes valides, ce qui réduit les performances. Ce problème existe pour toute table comportant des lignes de longueur variable, mais il est plus problématique pour les colonnes BLOB car leurs tailles varient considérablement. L'utilisation fréquente d'OPTIMIZE TABLE aidera à empêcher les performances de se dégrader.

Utiliser des index multi-colonnes

Les colonnes d'index multi-colonnes sont parfois utiles. Une technique consiste à créer une valeur de hachage basée sur d'autres colonnes et à la stocker dans une colonne séparée, puis la ligne peut être trouvée en recherchant la valeur de hachage. Cela ne fonctionne que pour les requêtes de correspondance exacte. (Les valeurs de hachage ne sont pas utiles pour les recherches de plage avec des opérateurs comme "eb6e5ada581f33797b5ed9e5cc5236ef="). Dans MySQL version 3.23 et supérieure, les valeurs de hachage peuvent être générées à l'aide de la fonction MD5(). Les index de hachage sont particulièrement utiles sur les colonnes BLOB. Une chose à noter est que dans les versions antérieures à MySQL 3.23.2, le type BLOB ne pouvait pas être indexé. Même dans la version 3.23.2 ou ultérieure, la recherche d'une valeur BLOB à l'aide d'une valeur de hachage comme valeur d'identification est plus rapide que la recherche dans la colonne BLOB elle-même.

Isoler les valeurs BLOB dans une table séparée

Dans certains cas, déplacer les colonnes BLOB hors d'une table vers une autre table secondaire peut avoir certaines conséquences. à condition que le tableau puisse être converti au format de ligne de longueur fixe après avoir déplacé les colonnes BLOB. Cela réduit la fragmentation dans la table principale et tire parti des avantages en termes de performances des lignes de longueur fixe.

Utilisez la procédure ANALYZE pour vérifier les colonnes du tableau
Si vous utilisez MySQL 3.23 ou une version plus récente, vous devez exécuter PROCEDURE ANALYSE() pour voir les informations qu'elle fournit sur les colonnes du tableau

ANALYSE([max elements,[max memory]])

Il examine les résultats de votre requête et renvoie une analyse des résultats.

max elements (256 par défaut) est le nombre maximum de valeurs distinctes par colonne que l'analyse remarquera. Ceci est utilisé par ANALYZE pour vérifier si le type de colonne optimal doit être un type ENUM.

la mémoire maximale (par défaut 8192) est la quantité maximale de mémoire qui doit être allouée à chaque colonne lorsque l'analyse tente de trouver toutes les valeurs distinctes.

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]])

Par exemple :

mysql>SELECT * FROM student PROCEDURE ANALYSE();

mysql>SELECT * FROM student PROCEDURE ANALYSE(16,256);

La sortie correspondante comporte une colonne avec des recommandations sur le meilleur type de colonne pour chaque colonne du tableau. Le deuxième exemple demande à PROCEDURE ANALYSE( ) de ne pas suggérer de types ENUM qui ont plus de 16 valeurs ou prennent plus de 256 octets (ces valeurs peuvent être modifiées selon les besoins). Sans une telle restriction, le résultat pourrait être très long ; la définition de ENUM serait également difficile à lire.

Sur la base du résultat de PROCEDURE ANALYSE( ), vous constaterez que des modifications peuvent être apportées à la table pour tirer parti de types plus efficaces. Si vous souhaitez modifier le type de valeur, utilisez l'instruction ALTER TABLE.

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!