MySQL Performance Boosting avec les index et expliquez

Points clés

• Utilisez la commande EXPLAIN de MySQL pour analyser et optimiser les plans d'exécution des requêtes, garantissant des opérations de base de données plus efficaces en révélant des informations clés telles que le type de connexion et l'utilisation d'index.
• Implémentez l'analyse des requêtes pour mesurer le temps d'exécution réel d'une requête, effectuant ainsi des optimisations ciblées pour réduire le temps d'exécution et améliorer les performances globales.
• Ajoutez des index appropriés en fonction des commentaires de la commande EXPLAIN, en nous concentrant sur les colonnes utilisées dans la clause WHERE pour accélérer la récupération des données et améliorer les performances de la requête.
• Pour les colonnes impliquées dans les opérations de recherche, envisagez d'utiliser l'indexation de texte complet pour optimiser les performances, en particulier lorsque vous utilisez l'opérateur LIKE dans les requêtes.
• Notez l'utilisation de ORDER BY en combinaison avec LIMIT, car elle peut compenser les avantages de performance de la limitation des résultats, surtout si l'indice n'est pas utilisé efficacement.

L'optimisation de la base de données est généralement l'objectif principal de l'amélioration des performances des applications et du goulot d'étranglement le plus courant. Comment mesurer et comprendre ce qui doit être amélioré?

Un outil simple et efficace est l'analyse des requêtes. L'analyse activée permet des estimations plus précises du temps d'exécution d'une requête. Il s'agit d'un processus en deux étapes: d'abord, activez l'analyse; alors, appelez show profiles pour obtenir le temps d'exécution de la requête.

Supposons que l'opération d'insertion suivante existe dans la base de données (et en supposant que l'utilisateur 1 et la galerie 1 ont été créés):

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Une petite quantité de données ne causera pas de problèmes, mais nous pouvons l'utiliser pour une analyse simple. Considérez la requête suivante:

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

S'il existe de nombreuses entrées de photos, cette requête peut devenir un problème à l'avenir.

Pour obtenir le temps d'exécution exact de cette requête, vous pouvez utiliser le SQL suivant:

set profiling = 1;
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';
show profiles;

Les résultats sont les suivants:

Query_Id	Duration	Query
1	0.00016950	SHOW WARNINGS
2	0.00039200	SELECT * FROM homestead.images AS i WHERE i.description LIKE '%street%' LIMIT 0, 1000
3	0.00037600	SHOW KEYS FROM homestead.images
4	0.00034625	SHOW DATABASES LIKE 'homestead'
5	0.00027600	SHOW TABLES FROM homestead LIKE 'images'
6	0.00024950	SELECT * FROM homestead.images WHERE 0=1
7	0.00104300	SHOW FULL COLUMNS FROM homestead.images LIKE 'id'

La commande

show profiles; affiche non seulement le temps de la requête d'origine, mais aussi le temps de toutes les autres requêtes, afin que la requête puisse être analysée avec précision.

Comment améliorer la requête?

Vous pouvez compter sur les connaissances SQL pour améliorer, ou compter sur la commande EXPLAIN de MySQL et améliorer les performances de la requête en fonction des informations réelles.

EXPLAIN est utilisé pour obtenir le plan d'exécution de la requête, c'est-à-dire comment MySQL exécute la requête. Il convient aux instructions SELECT, DELETE, INSERT, REPLACE et UPDATE et affiche des informations sur le plan d'exécution de l'instruction par l'Optimiseur. La documentation officielle décrit bien comment EXPLAIN comment

peut nous aider:

EXPLAIN Avec EXPLAIN, vous pouvez voir quelles tables vous devez ajouter des index afin que les instructions puissent exécuter plus rapidement en utilisant des index pour trouver des lignes. Vous pouvez également utiliser

pour vérifier si l'Optimiseur rejoint les tables dans le meilleur ordre.

EXPLAIN Pour donner un exemple pour illustrer l'utilisation de UserManager.php, nous utiliserons la requête pour trouver des e-mails utilisateur dans

:

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

EXPLAIN pour utiliser la commande SELECT, ajoutez-le simplement avant la requête de type

:

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Le résultat est le suivant (faites défiler droit pour tout voir):

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	const	UNIQ_1483A5E9E7927C74	UNIQ_1483A5E9E7927C74	182	const	1	100.00	NULL

Ces résultats ne sont pas faciles à comprendre au début, examinons de plus près chacun:

• id: Il s'agit de l'identifiant séquentiel pour chaque requête dans SELECT.
• select_type: SELECT Type de requête. Ce champ peut prendre plusieurs valeurs différentes, nous nous concentrerons donc sur les plus importants:
  • SIMPLE: requête simple sans sous-questionnaires ni syndicats
  • PRIMARY: select Situé dans la requête la plus externe de la connexion
  • DERIVED: select fait partie de la requête à neutrons from
  • : Le premier de la sous-requête SUBQUERY select
  • : UNION est la deuxième déclaration ou ultérieure de l'Union. La liste complète des valeurs de champ select peut être trouvée ici. select_type
• : Le tableau référencé par les lignes. table
• : Ce champ représente le type de table utilisé par la connexion MySQL. Il s'agit probablement du champ le plus important de la sortie type. Il peut indiquer l'index manquant, ou il peut montrer comment la requête est remplacée. Les valeurs possibles pour ce champ sont les suivantes (triées du meilleur au pire type): EXPLAIN
  • : La table a zéro lignes ou une ligne. system
  • : Il n'y a qu'une seule ligne dans le tableau qui correspond à la ligne et la ligne a été indexée. Il s'agit du type de connexion le plus rapide. const
  • : Toutes les parties de l'index sont utilisées par la jointure, et l'index est eq_ref ou PRIMARY_KEY. UNIQUE NOT NULL
  • : Pour chaque combinaison de lignes de la table précédente, toutes les lignes correspondantes de la colonne d'index sont lues. Ce type de jointure se produit généralement dans des colonnes indexées comparées à l'aide de ref ou des opérateurs. =
  • : Rejoignez l'index de texte intégral du tableau. fulltext
  • : Identique à ref_or_null, mais contient également les lignes de la valeur ref de la colonne. NULL
  • : Les connexions utilisent les listes d'index pour générer des ensembles de résultats. La colonne index_merge de EXPLAIN contiendra les touches utilisées. KEY
  • : unique_subquery La sous-requête ne renvoie qu'un seul résultat de la table et utilise la clé primaire. IN
  • : Utilisez des index pour trouver des lignes correspondantes dans une plage spécifique. range
  • : Scannez l'ensemble de l'arborescence d'index pour trouver des lignes correspondantes. index
  • : Scannez la table entière pour trouver les lignes correspondantes pour la jointure. Il s'agit du pire type de jointure, indiquant généralement un index approprié manquant sur la table. ALL
• : montre les clés que MySQL peut utiliser pour trouver des lignes à partir d'une table. Ces clés peuvent ou non être utilisées dans la pratique. possible_keys
• : Indique l'index que MySQL utilise réellement. MySQL trouve toujours les meilleures clés qui peuvent être utilisées pour les requêtes. Lorsque vous rejoignez plusieurs tables, il peut trouver des clés qui ne sont pas répertoriées dans keys mais qui sont meilleures. possible_keys
• : indique la longueur de l'index à utiliser par l'optimiseur de requête. key_len
• : Affiche une colonne ou une constante qui se compare à l'index nommé dans la colonne ref.
• rows: répertorie le nombre d'enregistrements vérifiés pour générer une sortie. Il s'agit d'un indicateur très important; moins les enregistrements sont vérifiés, mieux c'est.
• Extra: contient d'autres informations. L'équivalent Using filesort ou Using temporary dans cette colonne peut indiquer une requête en question.

EXPLAIN La documentation complète du format de sortie peut être trouvée sur la page MySQL officielle.

Retour à notre requête simple: c'est un SIMPLE type select avec une connexion de const type. C'est le meilleur cas de requête que nous puissions avoir. Mais que se passe-t-il lorsque nous avons besoin de requêtes plus grandes et plus complexes?

Retour à notre mode d'application, nous voulons peut-être obtenir toutes les images de la galerie. Nous pouvons également vouloir n'inclure que des photos avec le mot «chat» dans la description. C'est certainement une situation que nous pouvons trouver dans les exigences du projet. Regardons la requête:

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Dans cette situation plus complexe, nous devrions obtenir plus d'informations dans EXPLAIN pour analyser:

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Cela donnera les résultats suivants (faites défiler le droit de voir toutes les cellules):

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	index	PRIMARY,UNIQ_1483A5E9BF396750	UNIQ_1483A5E9BF396750	108	NULL	1	100.00	Using index
1	SIMPLE	gal	NULL	ref	PRIMARY,UNIQ_F70E6EB7BF396750,IDX_F70E6EB7A76ED395	UNIQ_1483A5E9BF396750	108	homestead.users.id	1	100.00	NULL
1	SIMPLE	img	NULL	ref	IDX_E01FBE6A4E7AF8F	IDX_E01FBE6A4E7AF8F	109	homestead.gal.id	1	25.00	Using where

Regardons de plus près et voyons ce que nous pouvons améliorer dans la requête.

Comme mentionné précédemment, les colonnes principales qui doivent être visualisées en premier sont les colonnes type et les colonnes rows. L'objectif devrait être d'obtenir de meilleures valeurs dans la colonne type et de minimiser les valeurs de la colonne rows.

Le résultat de la première requête est index, ce qui n'est pas du tout un bon résultat. Cela signifie que nous pouvons être en mesure de l'améliorer.

Affichez notre requête, il existe deux façons de la résoudre. Tout d'abord, le tableau Users n'est pas utilisé. Nous étendons la requête pour nous assurer que nous ciblons l'utilisateur, soit nous devons supprimer complètement la partie utilisateur de la requête. Cela n'augmente que la complexité et le temps de nos performances globales.

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Alors maintenant, nous obtenons exactement le même résultat. Voyons EXPLAIN:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	PRIMARY,UNIQ_1483A5E9BF396750	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	img	NULL	ref	IDX_E01FBE6A4E7AF8F	IDX_E01FBE6A4E7AF8F	109	homestead.gal.id	1	25.00	Using where

Ce qui nous reste est le type ALL. Bien que ALL soit probablement le pire type de connexion, il y a certains cas où c'est la seule option. Selon nos exigences, nous voulons toutes les images de la galerie, nous devons donc rechercher l'ensemble du tableau galleries. Lorsque nous avons besoin de toutes les informations dans le tableau, les index sont excellents lorsque vous essayez de trouver des informations spécifiques dans le tableau, mais elles ne nous aident pas. Lorsque nous rencontrons cette situation, nous devons recourir à d'autres méthodes, telles que la mise en cache.

Puisque nous travaillons sur LIKE, la dernière amélioration que nous pouvons apporter est d'ajouter un index complet à notre champ description. De cette façon, nous pouvons changer LIKE en match() et améliorer les performances. Plus d'informations sur l'indexation du texte intégral peuvent être trouvées ici.

Nous devons également consulter deux situations très intéressantes: les fonctionnalités les plus récentes et connexes de l'application. Ceux-ci s'appliquent à la galerie et impliquent des situations extrêmes auxquelles nous devons prêter attention:

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Ce qui précède sont des galeries liées.

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Ce qui précède est la dernière galerie.

À première vue, ces requêtes devraient être très rapides car elles utilisent LIMIT. C'est le cas dans la plupart des requêtes qui utilisent LIMIT. Malheureusement pour nous et nos applications, ces requêtes utilisent également ORDER BY. Parce que nous devons trier tous les résultats avant de limiter la requête, nous perdons l'avantage d'utiliser LIMIT.

Puisque nous savons que ORDER BY peut être délicat, appliquons notre fiable EXPLAIN.

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	IDX_F70E6EB7A76ED395	NULL	NULL	NULL	1	100.00	Using where; Using filesort
1	SIMPLE	u	NULL	eq_ref	PRIMARY,UNIQ_1483A5E9BF396750	PRIMARY	108	homestead.gal.id	1	100.00	NULL

et,

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using filesort

Nous pouvons voir que pour nos deux requêtes, nous avons le pire type de connexion: ALL.

Historiquement, la mise en œuvre de MySQL, en particulier lorsqu'elle est utilisée avec ORDER BY, était souvent la source des problèmes de performance MySQL. Cette combinaison est également utilisée dans la plupart des applications interactives avec de grands ensembles de données. Des fonctionnalités telles que les nouveaux utilisateurs enregistrés et les balises populaires utilisent souvent cette combinaison. LIMIT

Parce qu'il s'agit d'un problème courant, nous devons également appliquer certaines solutions courantes pour résoudre les problèmes de performance.

Assurez-vous que nous utilisons des index. Dans notre cas,
• est un bon candidat parce que c'est le domaine que nous trions. De cette façon, nous pouvons faire created_at et ORDER BY sans numériser et trier l'ensemble de résultats complet. LIMIT
Trier par colonnes dans la table principale. Généralement, si
• est trié par des champs qui ne sont pas le premier tableau de l'ordre de jointure, l'index ne peut pas être utilisé. ORDER BY
Ne triez pas par expression. Les expressions et les fonctions ne permettent pas à
• d'utiliser des index. ORDER BY
Faites attention à la grande valeur de
• . De grandes valeurs LIMIT obligeront LIMIT à tri plus de lignes. Cela affectera les performances. ORDER BY

Lorsque nous utilisons à la fois

et LIMIT, ce sont quelques-unes des mesures que nous devrions prendre pour minimiser les problèmes de performance. ORDER BY

Conclusion

Comme nous l'avons vu,

est très utile pour identifier les problèmes dans les requêtes le plus tôt possible. Il y a de nombreux problèmes qui ne sont remarqués que lorsque notre application est en production et qu'il y a beaucoup de données ou beaucoup de visiteurs pour accéder à la base de données. Si vous pouvez utiliser EXPLAIN pour détecter ces problèmes le plus tôt possible, la possibilité de problèmes de performance à l'avenir est beaucoup plus petite. EXPLAIN

Notre application a tous les index nécessaires et est rapide, mais nous savons maintenant que chaque fois que nous avons besoin de vérifier les gains de performances, nous pouvons toujours nous tourner vers

et les index. EXPLAIN

FAQ sur l'indexation des performances MySQL (FAQ)

Quelle est l'importance de l'indexation des performances MySQL?

L'indexation des performances MySQL est cruciale pour optimiser les performances de la base de données. Ils accélèrent considérablement les opérations de récupération de données en accédant rapidement aux lignes dans le tableau de données en fonction des valeurs des colonnes indexées. Sans index, MySQL devra traverser chaque ligne du tableau pour trouver les lignes pertinentes, qui peuvent prendre beaucoup de temps, en particulier pour les grandes bases de données.

Comment la commande EXPLIQUE Aide-t-elle améliore-t-elle les performances MySQL?

La commande

dans MySQL est un outil puissant qui fournit des informations sur la façon dont MySQL effectue des requêtes. Il montre l'ordre des tables de lecture, le type d'opérations de lecture effectuées, l'index qui peut être sélectionné et le nombre estimé de lignes à vérifier. Ces informations peuvent aider les développeurs à optimiser les requêtes et à améliorer les performances de la base de données. EXPLAIN

Pourquoi MySQL n'utilise-t-il pas de clés possibles?

MySQL n'utilise aucune clé possible pour plusieurs raisons. Une des raisons peut être que l'optimiseur estime que l'utilisation d'index nécessite la numérisation de la majeure partie du tableau et décide que la numérisation du tableau sera plus rapide. Une autre raison pourrait être que les colonnes de la clause WHERE ne correspondent pas aux colonnes de l'index.

Comment optimiser ma requête MySQL?

Il existe plusieurs façons d'optimiser les requêtes MySQL. Une façon consiste à utiliser efficacement les indices. Les index peuvent accélérer considérablement la récupération des données. Cependant, ils ralentissent les opérations de modification des données telles que INSERT, UPDATE et DELETE. Par conséquent, il est très important de trouver un point d'équilibre. Une autre façon consiste à utiliser la commande EXPLAIN pour comprendre comment MySQL exécute des requêtes et trouve des goulots d'étranglement potentiels.

Quelle est la différence entre les clés primaires et les index dans MySQL?

La clé principale de MySQL est un index. La clé principale est un identifiant unique pour la ligne dans le tableau. Il applique l'unicité d'une combinaison colonne ou colonne et garantit que la combinaison colonne ou colonne ne contient pas de valeurs NULL. D'un autre côté, un index est une structure de données qui peut augmenter la vitesse des opérations de récupération des données. Il peut être appliqué à n'importe quelle colonne ou combinaison de colonnes.

Comment créer un index dans MySQL?

Vous pouvez utiliser l'instruction CREATE INDEX pour créer un index dans MySQL. La syntaxe est la suivante: CREATE INDEX index_name ON table_name (column1, column2, …);. Cela crée un index sur la colonne spécifiée de la table spécifiée.

Quel est l'index composite dans MySQL?

Index composite, également appelé index multi-colonne, est un index contenant plusieurs colonnes. Dans MySQL, un index composite peut contenir jusqu'à 16 colonnes, mais la taille totale des colonnes indexées ne peut pas dépasser 767 octets.

Comment supprimer l'index dans MySQL?

Vous pouvez utiliser l'instruction DROP INDEX pour supprimer l'index dans MySQL. La syntaxe est la suivante: DROP INDEX index_name ON table_name;. Cela supprimera l'index spécifié de la table spécifiée.

Quelle est la différence entre l'index cluster et l'index non cluster dans MySQL?

L'index cluster détermine l'ordre physique des données dans le tableau. Chaque table ne peut avoir qu'un seul index en cluster. D'un autre côté, les index non cluster ne modifient pas l'ordre physique des données dans le tableau. Au lieu de cela, il maintient une structure de données distincte (index) pointant vers la ligne de données, permettant une récupération de données plus rapide.

Comment sélectionner l'index à utiliser dans MySQL?

MySQL utilise un optimiseur basé sur les coûts pour sélectionner l'index à utiliser. L'optimiseur estime le coût d'exécution des plans pour différentes requêtes et sélectionne le plan le moins cher. Le coût est estimé en fonction de facteurs tels que le nombre de lignes à lire, le nombre de recherches de disque, le coût du processeur et l'utilisation de la mémoire.

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!