MySQL
Mysql Performance Tips

Sélectionner l'optimisation de la déclaration

Vous trouverez ci-dessous quelques astuces à retenir lorsque vous écrivez une requête select dans MySQL qui peut nous aider à réduire le temps de requête: -

1. Chaque fois que nous utilisons où dans une grande table, nous devons nous assurer que la colonne dans la clause where est indexée ou non. Ex: - Sélectionnez * from employee où user_id> 2000. user_id si indexé accélère alors l'évaluation de la requête atlot. Les index sont également très importants lors des jointures et des clés étrangères.

2. Lorsque vous avez besoin de la plus petite section de contenu plutôt que d’en extraire des données entières, essayez d’utiliser limit. Plutôt que d'écrire Ex: - Sélectionnez * de l'employé. Si vous n'avez besoin que de 20 premiers employés de lakhs, utilisez simplement la limite Ex: - Sélectionnez * de l'employé LIMIT 20.

3. Vous pouvez également optimiser votre requête en fournissant le nom de colonne souhaité dans le jeu de résultats. Plutôt que d'écrire Ex: - Sélectionnez * de l'employé. Il suffit de mentionner le nom de la colonne à partir de laquelle vous avez besoin de données si votre table contient beaucoup de colonnes et que vous souhaitez en avoir quelques-unes. Ex: - Sélectionnez id, nom de l'employé.

4. Colonne d'index si vous utilisez pour vérifier la valeur NULL dans la clause where. Si vous avez une déclaration en tant que SELECT * FROM nom_de_table WHERE key_col IS NULL; alors si key_col est indexé alors la requête sera évaluée plus rapidement.

Optimisation de la disposition de stockage pour les tables InnoDB

1. Dans InnoDB, avoir une longue PRIMARY KEY (soit une seule colonne avec une longue valeur, soit plusieurs colonnes qui forment une longue valeur composite) gaspille beaucoup d'espace disque. La valeur de clé primaire pour une ligne est dupliquée dans tous les enregistrements d'index secondaires qui pointent vers la même ligne. Créez une colonne AUTO_INCREMENT comme clé primaire si votre clé primaire est longue.
2. Utilisez le type de données VARCHAR au lieu de CHAR pour stocker des chaînes de longueur variable ou des colonnes avec plusieurs valeurs NULL. Une colonne CHAR (N) prend toujours N caractères pour stocker des données, même si la chaîne est plus courte ou sa valeur est NULL. Les tables plus petites s'intègrent mieux dans le pool de mémoire tampon et réduisent les E / S disque.

Lorsque vous utilisez un format de ligne COMPACT (format InnoDB par défaut) et des jeux de caractères de longueur variable, tels que utf8 ou sjis, les colonnes CHAR (N) occupent un espace variable, mais contiennent au moins N octets.

3. Pour les tables volumineuses ou contenant beaucoup de données textuelles ou numériques répétitives, envisagez d'utiliser le format de ligne COMPRESSED. Moins d’E / S de disque sont nécessaires pour importer des données dans le pool de mémoire tampon ou pour effectuer des analyses de table complètes. Avant de prendre une décision définitive, mesurez la quantité de compression que vous pouvez obtenir en utilisant le format de ligne COMPRESSED versus COMPACT. Avertissement: Les tests de performances montrent rarement une compression supérieure à 2: 1 et il y a beaucoup de surcharge dans buffer_pool pour COMPRESSED.
4. Une fois que vos données atteignent une taille stable ou qu'une table croissante a augmenté de dizaines ou de centaines de mégaoctets, envisagez d'utiliser la commande OPTIMIZE TABLE pour réorganiser la table et compacter tout espace perdu. Les tables réorganisées nécessitent moins d'E / S disque pour effectuer des analyses de table complètes. Il s'agit d'une technique simple qui peut améliorer les performances lorsque d'autres techniques, telles que l'amélioration de l'utilisation des index ou le réglage du code d'application, ne sont pas pratiques. Avertissement : Indépendamment de la taille de la table, OPTIMIZE TABLE doit être rarement exécuté. C'est parce que c'est coûteux et améliore rarement la table suffisamment pour en valoir la peine. InnoDB est raisonnablement bon pour garder ses arbres B + exempts de beaucoup d'espace perdu.

OPTIMIZE TABLE copie la partie données de la table et reconstruit les index. Les avantages proviennent de l'amélioration de l'emballage des données au sein des index et de la fragmentation réduite au sein des espaces de table et sur le disque. Les avantages varient en fonction des données de chaque tableau. Vous pouvez constater qu'il y a des gains significatifs pour certains et pas pour d'autres, ou que les gains diminuent avec le temps jusqu'à ce que vous optimisiez ensuite le tableau. Cette opération peut être lente si la table est volumineuse ou si les index en cours de reconstruction ne rentrent pas dans le pool de mémoire tampon. La première exécution après avoir ajouté beaucoup de données à une table est souvent beaucoup plus lente que les exécutions ultérieures.

Construire un index composite

Dans de nombreuses situations, un index composite est plus performant qu'un index avec une seule colonne. Pour créer un index composite optimal, remplissez-le avec des colonnes dans cet ordre.

• = colonne (s) de la clause WHERE premier. (par exemple, INDEX(a,b,...) pour WHERE a=12 AND b='xyz' ... )
• Colonne (s) IN ; l'optimiseur peut être en mesure de sauter l'index.
• Une "plage" (par exemple x BETWEEN 3 AND 9 , name LIKE 'J%' ) Elle n'utilisera rien après la première colonne de la plage.
• Toutes les colonnes de GROUP BY , dans l'ordre
• Toutes les colonnes dans ORDER BY , dans l'ordre. Fonctionne uniquement si tous sont en ASC ou si tous sont DESC ou si vous utilisez 8.0.

Notes et exceptions:

• Ne dupliquez aucune colonne.
• Ignorez tous les cas qui ne s'appliquent pas.
• Si vous n'utilisez pas toutes les colonnes de WHERE , il n'est pas nécessaire de passer à GROUP BY , etc.
• Il y a des cas où il est utile d'indexer uniquement la ou les colonnes ORDER BY , en ignorant WHERE .
• Ne "cachez" pas une colonne dans une fonction (par exemple, DATE(x) = ... ne peut pas utiliser x dans l'index).
• L'indexation du préfixe (par exemple, text_col(99) ) ne sera probablement pas utile. peut faire mal

Plus de détails et de conseils .