Principales vérifications de schéma ySQL pour améliorer les performances de la base de données

Un schéma de base de données définit la structure logique de votre base de données, y compris les tables, les colonnes, les relations, les index et les contraintes qui façonnent la manière dont les données sont organisées et accessibles. Il ne s'agit pas seulement de la manière dont les données sont stockées, mais également de la manière dont elles interagissent avec les requêtes, les transactions et autres opérations.

Ces vérifications peuvent vous aider à rester au courant de tout problème nouveau ou persistant avant qu'il ne se transforme en problèmes plus importants. Vous pouvez approfondir ces vérifications de schéma ci-dessous et découvrir exactement comment résoudre les problèmes si votre base de données échoue. N'oubliez pas qu'avant d'apporter des modifications au schéma, sauvegardez toujours vos données pour vous protéger contre les risques potentiels qui pourraient survenir lors des modifications.

1. Vérification de la clé primaire (clés primaires manquantes)

La clé primaire est un élément essentiel de toute table, identifiant de manière unique chaque ligne et permettant des requêtes efficaces. Sans clé primaire, les tables peuvent rencontrer des problèmes de performances et certains outils tels que les utilitaires de réplication et de modification de schéma peuvent ne pas fonctionner correctement.

Il existe plusieurs problèmes que vous pouvez éviter en définissant une clé primaire lors de la conception des schémas :

1. Si aucune clé primaire ou unique n'est spécifiée, MySQL en crée une interne, inaccessible à l'utilisation.
2. L'absence de clé primaire pourrait ralentir les performances de réplication, en particulier avec une réplication basée sur les lignes ou mixte.
3. Les clés primaires permettent un archivage et une purge évolutive des données. Des outils comme pt-online-schema-change nécessitent une clé primaire ou unique.
4. Les clés primaires identifient de manière unique les lignes, ce qui est crucial du point de vue de l'application.

Exemple

Pour créer une contrainte PRIMARY KEY sur la colonne "ID" lorsque la table est déjà créée, utilisez le SQL suivant :

ALTER TABLE Persons ADD PRIMARY KEY (ID);

Pour définir une clé primaire sur plusieurs colonnes :

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Remarque : Si vous utilisez la commande ALTER TABLE, la ou les colonnes de clé primaire doivent avoir été déclarées comme ne contenant pas de valeurs NULL lors de la création initiale de la table.

2. Vérification du moteur de table (moteur de table obsolète)

Le moteur de stockage MyISAM est obsolète et les tables qui l'utilisent encore doivent être migrées vers InnoDB. InnoDB est le moteur par défaut et recommandé pour la plupart des cas d'utilisation en raison de ses performances supérieures, de ses capacités de récupération de données et de sa prise en charge des transactions. La migration de MyISAM vers InnoDB peut améliorer considérablement les performances des applications gourmandes en écriture, offrir une meilleure tolérance aux pannes et permettre des fonctionnalités MySQL plus avancées telles que la recherche en texte intégral et les clés étrangères.

Pourquoi InnoDB est préféré :

• Les capacités de récupération après incident lui permettent de récupérer automatiquement après une panne du serveur de base de données ou de l'hôte sans corruption des données.
• Verrouille uniquement les lignes affectées par une requête, ce qui permet de bien meilleures performances dans les environnements à haute concurrence.
• Mette en cache les données et les index en mémoire, ce qui est préférable pour les charges de travail lourdes en lecture.
• Entièrement conforme à ACID, garantissant l'intégrité des données et prenant en charge les transactions.
• Le moteur InnoDB reçoit la majorité de l'attention de la communauté de développement MySQL, ce qui en fait le moteur le plus à jour et le mieux pris en charge.

Comment migrer vers InnoDB

ALTER TABLE Persons ADD PRIMARY KEY (ID);

3. Vérification du classement des tables (classements mixtes)

L'utilisation de classements différents dans les tables ou même au sein d'une table peut entraîner des problèmes de performances, en particulier lors des comparaisons de chaînes et des jointures. Si les classements de deux colonnes de chaînes diffèrent, MySQL devra peut-être convertir les chaînes au moment de l'exécution, ce qui peut empêcher l'utilisation des index et ralentir vos requêtes.

Lorsque vous apportez des modifications aux tables de classements mixtes, quelques problèmes peuvent apparaître :

• Les classements peuvent différer au niveau des colonnes, donc les incohérences au niveau de la table ne poseront pas de problèmes si les colonnes pertinentes d'une jointure ont des classements correspondants.
• Changer le classement d'une table, notamment avec un changement de jeu de caractères, n'est pas toujours simple. Une conversion de données peut être nécessaire et les caractères non pris en charge peuvent se transformer en données corrompues.
• Si vous ne spécifiez pas de classement ou de jeu de caractères lors de la création d'une table, celle-ci hérite des valeurs par défaut de la base de données. Si aucun n’est défini au niveau de la base de données, les valeurs par défaut du serveur s’appliqueront. Pour éviter ces problèmes, il est important de standardiser le classement sur l'ensemble de votre ensemble de données, en particulier pour les colonnes fréquemment utilisées dans les opérations de jointure.

Comment modifier les paramètres de classement

Avant d'apporter des modifications aux paramètres de classement de votre base de données, testez votre approche dans un environnement hors production pour éviter des conséquences inattendues. Si vous n'êtes pas sûr de quoi que ce soit, il est préférable de consulter un administrateur de base de données.

Récupérez le jeu de caractères et le classement par défaut pour toutes les bases de données :

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Vérifiez le classement de tables spécifiques :

ALTER TABLE <table_name> ENGINE=InnoDB;

Trouver le jeu de caractères par défaut du serveur :

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

Trouver le classement par défaut du serveur :

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

Mettre à jour le classement pour une base de données spécifique :

SELECT @@GLOBAL.character_set_server;

Mettre à jour le classement d'une table spécifique :

SELECT @@GLOBAL.collation_server;

4. Vérification du jeu de caractères du tableau (jeu de caractères mixte)

Les jeux de caractères mixtes sont similaires aux classements mixtes dans le sens où ils peuvent entraîner des problèmes de performances et de compatibilité. Un jeu de caractères mixte se produit lorsque différentes colonnes ou tables utilisent différents formats de codage pour stocker les données.

• Les jeux de caractères mixtes peuvent nuire aux performances de jointure sur les colonnes de chaîne en empêchant l'utilisation d'index ou en exigeant des conversions de valeurs.
• Les jeux de caractères peuvent être définis au niveau des colonnes, et tant que les colonnes impliquées dans une jointure ont des jeux de caractères correspondants, les performances ne seront pas affectées par des incohérences au niveau de la table.
• La modification du jeu de caractères d'une table peut impliquer une conversion de données, ce qui peut entraîner des données corrompues si des caractères non pris en charge sont rencontrés.
• Si aucun jeu de caractères ou classement n'est spécifié, les tables héritent des valeurs par défaut de la base de données et les bases de données héritent du jeu de caractères et du classement par défaut du serveur.

Comment modifier les paramètres des personnages

Avant d'ajuster les paramètres de caractères de votre base de données, assurez-vous de tester les modifications dans un environnement intermédiaire pour éviter tout problème inattendu. Si vous n'êtes pas sûr des étapes à suivre, consultez un administrateur de base de données pour obtenir des conseils.

Récupérez le jeu de caractères et le classement par défaut pour toutes les bases de données :

ALTER TABLE Persons ADD PRIMARY KEY (ID);

Récupérer le jeu de caractères d'une colonne :

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Trouver le jeu de caractères par défaut du serveur :

ALTER TABLE <table_name> ENGINE=InnoDB;

Trouver le classement par défaut du serveur :

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

Pour visualiser la structure d'un tableau :

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

Exemple de résultat :

SELECT @@GLOBAL.character_set_server;

Pour modifier un jeu de caractères de colonne :

SELECT @@GLOBAL.collation_server;

5. Vérification de l'incrémentation automatique des colonnes (type de colonnes d'incrémentation automatique)

Pour les tables qui devraient croître indéfiniment et utiliser l'incrémentation automatique pour les clés primaires, il est recommandé de passer au type de données UNSIGNED BIGINT. Cela permet à la colonne de gérer une plage de valeurs beaucoup plus large, évitant ainsi la nécessité de modifications coûteuses de la table à l'avenir une fois la valeur maximale atteinte. En spécifiant UNSIGNED, seules les valeurs positives sont stockées, doublant ainsi la plage du type de données.

Comment modifier les paramètres des personnages

Pour modifier le type de colonne en UNSIGNED BIGINT :

ALTER DATABASE <db-name> COLLATE=<collation-name>;

6. Tableau Vérification des clés étrangères (Existence de clés étrangères)

Les clés étrangères offrent la cohérence des données en maintenant la relation entre les tables parent et enfant, mais elles ont également un impact sur les performances de la base de données. Chaque fois qu'une opération d'écriture se produit, des recherches supplémentaires sont nécessaires pour vérifier l'intégrité des données associées. Cela peut entraîner des ralentissements, notamment dans les environnements à fort trafic.

Si les performances sont un problème, vous pouvez envisager de supprimer les clés étrangères, en particulier dans les scénarios où la cohérence des données peut être gérée au niveau de l'application.

Comment supprimer les clés étrangères

Pour supprimer une contrainte de clé étrangère d'une table :

ALTER TABLE Persons ADD PRIMARY KEY (ID);

7. Vérification d'index en double

Les index en double dans MySQL consomment de l'espace disque inutile et créent une surcharge supplémentaire lors des opérations d'écriture, car chaque index doit être mis à jour. Cela peut compliquer l'optimisation des requêtes, conduisant potentiellement à des plans d'exécution inefficaces sans offrir de réel avantage.

Identifiez et supprimez les index en double pour rationaliser l'optimisation des requêtes et réduire les frais généraux. Mais assurez-vous que l'index n'est pas utilisé pour des requêtes critiques avant de le supprimer.

8. Vérification de l'index inutilisé

Les index inutilisés dans MySQL peuvent avoir un impact négatif sur les performances de la base de données en consommant de l'espace disque, en augmentant la surcharge de traitement lors des insertions, des mises à jour et des suppressions et en ralentissant les opérations globales. Bien que les index soient utiles pour accélérer les requêtes, ceux qui ne sont pas utilisés peuvent créer une pression inutile sur votre système.
Les avantages supplémentaires de la suppression des index inutilisés ou en double incluent :

• Avec moins d'index, l'optimiseur de MySQL a moins de choix à évaluer, ce qui simplifie l'exécution des requêtes et réduit l'utilisation du processeur/mémoire.
• La suppression des index inutilisés libère un espace disque précieux qui peut être utilisé pour des données plus critiques, améliorant également l'efficacité des E/S.
• Les tâches de maintenance des index, telles que la reconstruction ou la réorganisation, deviennent plus rapides et moins gourmandes en ressources lorsque le nombre d'index est minimisé. Cela conduit à des opérations plus fluides, en particulier dans les environnements nécessitant une disponibilité 24h/24 et 7j/7.

Pour identifier les indexés inutilisés dans MySQL ou MariabDB, veuillez utiliser l'instruction SQL suivante :

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Comment supprimer les index inutilisés ou dupliqués

Dans MySQL 8.0 et versions ultérieures, vous pouvez rendre les index invisibles pour tester s'ils sont nécessaires sans les supprimer complètement :

ALTER TABLE <table_name> ENGINE=InnoDB;

Si les performances ne sont pas affectées, l'index peut être supprimé en toute sécurité :

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

Vous pouvez rendre un index visible si nécessaire :

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

Les vérifications de schéma sont désormais disponibles avec Releem

Avec la dernière mise à jour, Releem inclut désormais des vérifications complètes de l'état du schéma. Ces vérifications fournissent des informations en temps réel sur l’intégrité structurelle de votre base de données, ainsi que des recommandations concrètes pour résoudre tout problème détecté.

En automatisant le processus de surveillance des schémas, Releem élimine les incertitudes liées aux vérifications manuelles, ce qui permet aux ingénieurs de bases de données d'économiser des tonnes de temps et d'efforts. Au lieu de passer des heures à travailler sur les détails du schéma, vous pouvez désormais vous concentrer sur des tâches plus urgentes.

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!