Opérateurs Python. Expressions et opérateurs logiques. Python. Tuples, dictionnaires, listes. Tranches
L’utilisation de bases de données facilite grandement la vie et le travail d’une personne avec des données, lui permettant d’obtenir à court terme informations nécessairesà partir de la base de données, ou écrivez-y. Cependant, travailler avec des données nécessite une approche appropriée ; le programmeur doit prendre en compte certains aspects de l'interaction avec les bases de données. En particulier nous parlons deà propos de MySQL. Examinons ensuite un résumé des conseils pour optimiser l'interaction avec les bases de données Données MySQL.
Rendre la mise en cache des requêtes MySQL conviviale
Le mécanisme de mise en cache des requêtes intégré au serveur MySQL peut améliorer considérablement les performances. La plupart des serveurs de bases de données MySQL incluent un mécanisme de mise en cache. De nombreuses requêtes identiques sur la base de données sur une courte période peuvent entraîner des pertes de performances importantes ; le mécanisme de mise en cache est capable de mettre en cache de telles requêtes, renvoyant les données du cache. Il existe des requêtes que MySQL ne peut pas mettre en cache, et il est recommandé de faire ces requêtes un peu différemment.
// ce Requête MySQL ne pourra pas mettre en cache $res = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); // vous pouvez le faire différemment $today = date("Y-m-d"); $res = mysql_query("SELECT nom d'utilisateur FROM utilisateur WHERE signup_date >= "$today"");
Le fait est que dans la première requête la fonction CURDATE() a été utilisée ; la particularité de son fonctionnement ne permet pas de placer les résultats de la requête dans le cache. La valeur de la date peut être pré-écrite dans la chaîne de requête, cela éliminera l'utilisation de la fonction CURDATE() dans la requête.
Par analogie, il existe d'autres fonctions qui ne sont pas elles-mêmes mises en cache. Serveur MySQL, parmi eux RAND(), NOW() ainsi que d'autres fonctions dont les résultats sont non déterministes.
Découvrez comment votre requête s'exécute à l'aide de la syntaxe EXPLAIN
Vous pouvez voir comment MySQL exécute votre requête en utilisant la syntaxe EXPLAIN. Son utilisation peut aider à identifier les faiblesses des performances des requêtes, ainsi que de la structure des tables. En tant que résultat de la requête, EXPLAIN renverra des données qui montreront quels index sont utilisés, comment les données sont sélectionnées dans les tables, comment elles sont triées, etc. Pour ce faire, ajoutez simplement au début de la requête SELECT mot-clé EXPLIQUER, après quoi un tableau avec des données sera affiché.
Lorsque vous avez besoin d'un enregistrement, définissez LIMIT 1
Il existe de nombreux cas où vous devez vérifier la présence d'au moins un enregistrement d'une table ; dans ce cas, il est recommandé d'ajouter le paramètre LIMIT 1 à la requête. Cela la rendra plus optimale, car. Le moteur de base de données cessera de récupérer les données après avoir trouvé le premier enregistrement au lieu de récupérer toutes les données. Vous économisez des ressources.
// interroge la ville avec le code Shymkent depuis la base de données $res = mysql_query("SELECT * FROM location WHERE city = "Shymkent""); if (mysql_num_rows($res) > 0)( ) // ajoutez LIMIT 1 pour optimiser la requête $res = mysql_query("SELECT * FROM location WHERE city = "Shymkent" LIMIT 1"); si (mysql_num_rows($res) > 0)( )
Indexez les champs que vous recherchez
Dans un cas particulier, un index signifie un index des champs dans lesquels vous recherchez, cela améliorera la vitesse de recherche. À propos, un index régulier ne peut pas fonctionner avec des conditions sous forme d'expressions régulières :
// l'index city LIKE 'shym%' fonctionnera ici // l'index ne sera pas utilisé ici city LIKE '%shymkent%'
Pour créer un index des conditions avec expressions régulières vous devriez utiliser , ou penser à votre système d'index.
Indexer les champs par lesquels les tables sont jointes
Si vous utilisez plusieurs jointures de tables, vous souhaiterez peut-être vous assurer que les champs impliqués dans la jointure sont indexés dans les deux tables. Cette question affecte la façon dont MySQL produira optimisation interne unions de champs de table. Les champs d'union doivent être du même type et du même codage. Ceux. par exemple, si un champ est de type DECIMAL et l'autre est INT, alors MySQL ne pourra pas utiliser l'index.
Trouver une alternative au lieu de ORDER BY RAND()
Utiliser le tri aléatoire est en effet très pratique, et de nombreux programmeurs débutants ont le même avis à ce sujet. Cependant, il existe ici des pièges, et des plus importants ; en utilisant une méthode d'échantillonnage similaire dans vos requêtes, vous quittez goulot en productivité. Ici, il est recommandé de recourir à code supplémentaire au lieu d'utiliser ORDER BY RAND(), comme alternative pour se débarrasser de point faible en performances, ce qui se rappellera à mesure que le volume de données augmente.
Utilisez la sélection de champs spécifiques au lieu de SELECT *
Ne soyez pas paresseux pour indiquer des détails champs obligatoires dans une requête lors de la récupération, au lieu d'utiliser « * » - pour récupérer tous les champs, le fait est que plus de données sont lues dans la table, plus votre requête devient lente.
Ajouter un champ ID pour toutes les tables
Chaque table, si elle est bien faite, devrait avoir un champ d'identification tapez INT, qui est la clé primaire (PRIMARY_KEY) et AUTO_INCREMENT. De plus, vous devez spécifier le paramètre UNSIGNED pour le champ, ce qui signifie que la valeur sera toujours positive.
MySQL a opérations internes qui peut utiliser clé primaire, cela joue un rôle pour configurations complexes bases de données telles que clusters, parallélisation, etc.
De plus, s'il existe plusieurs tables et que vous devez effectuer une requête jointe, les identifiants des tables vous seront utiles.
ENUM comme alternative à VARCHAR
Imaginons que vous souhaitiez ajouter un champ à une table qui doit contenir un ensemble spécifique de valeurs. Traditionnellement, de nombreux programmeurs définissent le type VARCHAR pour les champs. Il existe cependant un autre type de champ beaucoup plus rapide et plus compact. Valeurs dans ce type stocké de la même manière que TINYINT, mais affiché sous forme de type chaîne.
Utilisez NOT NULL au lieu de NULL
Les champs NULL occupent plus d'espace dans un enregistrement en raison de la nécessité de le marquer Valeur NULLE. Tables MyISAM, les champs avec NULL sont stockés de telle manière que chaque champ occupe 1 peu supplémentaire, qui est arrondi à l'octet le plus proche. Si l’utilisation de NULL dans un champ n’est pas importante, alors il est recommandé d’utiliser NOT NULL.
Utiliser des instructions préparées
$res = "MISE À JOUR des hôtes SET ip = INET_ATON("($_SERVER["REMOTE_ADDR"])") WHERE id = $host_id";Utiliser des tables statiques
Une table statique est une table normale dans la base de données, sauf que chaque champ de la table a une taille fixe. Si la table comporte des colonnes qui ne sont pas de longueur fixe, par exemple, celles-ci pourraient être : VARCHAR, TEXT, BLOB, elle cesse d'être statique et sera traitée par MySQL un peu différemment. Tableaux statiques, ou ils peuvent aussi être appelés tableaux taille fixe fonctionnent plus rapidement que les non statiques. Les enregistrements de ces tables seront visualisés plus rapidement. Si nécessaire, sélectionnez celle souhaitée. Chaînes MySQL calculera rapidement sa position. Si le champ n'a pas de taille fixe, alors dans ce cas la recherche s'effectue par index. L'utilisation de tables statiques présente d'autres avantages : le fait est que ces tables sont plus faciles à mettre en cache et peuvent également être restaurées après un crash de la base de données.
Utiliser la séparation verticale
Partitionnement vertical – consiste à diviser une table en colonnes afin d'augmenter les performances de la table. Par exemple, si votre table contient des champs qui sont très rarement utilisés ou si des champs avec longueur variable, ils peuvent ensuite être placés dans une table séparée, déchargeant ainsi la table, augmentant ainsi la vitesse de travail avec elle.
Séparez les grandes requêtes INSERT et DELETE
L'exécution d'un grand nombre de requêtes de ce type peut conduire à un verrouillage des tables, entraînant un mauvais fonctionnement de l'application dans son ensemble. Les requêtes parallèles au serveur Web peuvent générer appel supplémentaireà table. Si une table est bloquée par une requête précédente, les requêtes suivantes s'alignent dans une file d'attente, et par conséquent, cela se manifeste sous la forme d'un ralentissement du site, voire d'un crash du serveur.
Si vous devez faire beaucoup de requêtes, essayez de les contrôler en les envoyant par petits lots plutôt que de tout vider dans la base de données. Le traitement de votre demande peut prendre plus de temps, mais cela aura moins d'impact sur les autres utilisateurs.
Exemple:
While (1)( mysql_query("DELETE FROM logs WHERE log_date<= "2015-07-20" LIMIT 1000"); if (mysql_affected_rows() == 0){ // записи удалены успешно break; } usleep(50000); // делаем небольшую паузу }
Essayez d'utiliser de petits champs
Comme vous le savez, les données des bases de données sont stockées sur le disque dur, cela peut souvent être l'un des points faibles d'une application web. Le fait est que les disques de petite taille sont préférables, car... leur utilisation réduit la charge de travail sur le disque dur. Si vous êtes sûr qu'une table particulière stockera peu de lignes, alors une solution rationnelle serait d'utiliser des types de champs avec les valeurs minimales possibles. Par exemple, si la clé principale est de type INT et que vous ne stockerez qu'une petite quantité de données dans la table, alors il est préférable de la rendre de type MEDIUMINT, SMALLINT ou même TINYINT.
Choisissez le type de tables adapté à vos tâches
Deux types de tables largement connus aujourd'hui sont MyISAM et InnoDB, chacun d'eux a ses propres avantages et inconvénients. Par exemple, MyISAM est efficace pour lire les données de tables en grande quantité, mais il est plus lent lors de l'écriture. Il fonctionne également bien sur des requêtes telles que SELECT COUNT(*).
Le mécanisme de stockage de données d'InnoDB est plus complexe que celui de MyISAM, cependant, il prend en charge le verrouillage des lignes, ce qui est un côté positif lors de la mise à l'échelle. Par conséquent, il est impossible de dire que l’un est meilleur que l’autre, et ce n’est pas correct ; vous devez choisir un type en fonction de vos besoins.
Travailler avec une base de données est souvent le point le plus faible des performances de nombreuses applications Web. Et ce ne sont pas seulement les administrateurs de bases de données qui doivent s’en soucier. Les programmeurs doivent choisir la bonne structure de table, écrire des requêtes optimisées et écrire du bon code. Voici les méthodes permettant d'optimiser le travail avec MySQL pour les programmeurs.
1. Optimiser les requêtes pour le cache de requêtes
La plupart des serveurs MySQL ont la mise en cache des requêtes activée. L’un des meilleurs moyens d’améliorer les performances consiste simplement à fournir une mise en cache à la base de données elle-même. Lorsqu'une requête est répétée plusieurs fois, son résultat est extrait du cache, ce qui est beaucoup plus rapide que l'accès direct à la base de données. Le principal problème est que de nombreuses personnes utilisent simplement des requêtes qui ne peuvent pas être mises en cache :
// la requête ne sera pas mise en cache$r = mysql_query( "SELECT nom d'utilisateur FROM utilisateur WHERE signup_date >= CURDATE()"); // et il en sera ainsi ! $aujourd'hui = date("Y-m-d" ); $r = mysql_query();"SELECT nom d'utilisateur FROM utilisateur WHERE signup_date >= "$today"" La raison est que la première requête utilise la fonction CURDATE(). Cela s'applique à toutes les fonctions comme NOW(), RAND() et autres dont le résultat est non déterministe. Si le résultat d'une fonction peut changer, MySQL ne met pas en cache une telle requête. DANS dans cet exemple
cela peut être évité en calculant la date avant d'exécuter la requête.
2. Utilisez EXPLAIN pour vos requêtes SELECT// crée une instruction préparée si ($stmt = $mysqli ->prepare()) { "SELECT nom d'utilisateur FROM user WHERE state=?"// lier les valeurs $stmt ->bind_param("s" , $state );// exécute $stmt ->execute(); // lie le résultat$stmt ->bind_result($nom d'utilisateur );// récupère les données
$stmt ->fetch();
printf("%s vient de %s\n" , $username , $state );
$stmt ->fermer(); )
Il existe cependant certaines restrictions. Vous devrez lire tous les enregistrements ou appeler mysql_free_result() avant de pouvoir exécuter une autre requête. De plus, vous ne pouvez pas utiliser mysql_num_rows() ou mysql_data_seek() sur le résultat d'une fonction.
14. Stocker l'adresse IP dans UNSIGNED INT
De nombreux programmeurs stockent les adresses IP dans un champ de type VARCHAR(15), sans savoir qu'elles peuvent être stockées sous forme entière. INT occupe 4 octets et a une taille de champ fixe.
Assurez-vous d'utiliser UNSIGNED INT car IP peut être écrit sous la forme d'un nombre non signé de 32 bits.
Utilisez INET_ATON() dans votre requête pour convertir l'adresse IP en nombre, et INET_NTOA() pour conversion inverse. Les mêmes fonctions existent en PHP - ip2long() et long2ip() (en PHP ces fonctions peuvent également renvoyer des valeurs négatives. Note de The_Lion).
15. Les tableaux de taille fixe (statiques) sont plus rapides
Si chaque colonne d'un tableau a une taille fixe, alors le tableau est appelé « statique » ou « taille fixe ». Exemple de colonnes de longueur non fixe : VARCHAR, TEXT, BLOB. Si vous incluez un tel champ dans une table, il ne sera plus corrigé et sera traité différemment par MySQL.
L'utilisation de telles tables augmentera l'efficacité, car... MySQL peut y rechercher des enregistrements plus rapidement. Lorsque vous devez sélectionner la ligne souhaitée du tableau, MySQL peut calculer très rapidement sa position. Si la taille de l'enregistrement n'est pas fixe, elle est recherchée par index.
Ces tables sont également plus faciles à mettre en cache et à restaurer après un crash de base de données. Par exemple, si vous convertissez VARCHAR(20) en CHAR(20), l'entrée occupera 20 octets, quel que soit son contenu réel.
En utilisant la méthode de « séparation verticale », vous pouvez déplacer des colonnes avec des longueurs de lignes variables dans un tableau séparé.
16. Séparation verticale
Le partitionnement vertical fait référence à la division d'une table en colonnes pour améliorer les performances.
Exemple 1. Si les adresses sont stockées dans la table des utilisateurs, ce n'est pas un fait que vous en aurez besoin très souvent. Vous pouvez diviser la table et stocker les adresses dans tableau séparé. Ainsi, la taille de la table utilisateur sera réduite. La productivité augmentera.
Exemple 2 : Vous avez un champ "last_login" dans une table. Il est mis à jour à chaque fois que l'utilisateur se connecte au site. Mais toutes les modifications apportées à la table effacent son cache. En stockant ce champ dans une autre table, vous limiterez au minimum les modifications apportées à la table des utilisateurs.
Mais si vous utilisez constamment des jointures sur ces tables, cela entraînera de mauvaises performances.
17. Séparez les grandes requêtes DELETE et INSERT
Si vous devez faire une demande importante pour supprimer ou insérer des données, vous devez faire attention à ne pas casser l'application. Exécution grosse demande peut verrouiller la table et conduire à mauvais fonctionnement l'intégralité de la demande.
Apache peut en faire plusieurs processus parallèles simultanément. Par conséquent, cela fonctionne plus efficacement si les scripts sont exécutés le plus rapidement possible.
Si vous verrouillez les tables sur à long terme(par exemple, pendant 30 secondes ou plus), puis avec un trafic élevé sur le site, une grande file d'attente de processus et de demandes peut survenir, ce qui peut conduire à travail lent site ou même un crash de serveur.
Si vous avez des requêtes comme celle-ci, utilisez LIMIT pour les exécuter par petites rafales.
nousdormir(50000); )
18. Les petites colonnes sont plus rapides
Pour une base de données, travailler avec le disque dur est peut-être le point le plus faible. Les disques petits et compacts sont généralement meilleurs en termes de performances car... réduire le travail du disque.
La documentation MySQL contient une liste des exigences de stockage de données pour tous les types de données.
Si votre table doit stocker quelques lignes, cela n'a aucun sens de faire de la clé principale un type INT ; il serait peut-être préférable de la définir MEDIUMINT, SMALLINT ou même TINYINT. Si vous n'avez pas besoin de stocker l'heure, utilisez DATE au lieu de DATETIME.
Cependant, faites attention à ce que les choses ne se passent pas comme Slashdot.
19. Choisissez le bon type de tableau
20. Utilisez ORM
21. Soyez prudent avec les connexions persistantes
Les connexions persistantes sont conçues pour réduire le coût d'établissement de la communication avec MySQL. Une fois la connexion créée, elle reste ouverte une fois le script terminé. La prochaine fois, ce script utilisera la même connexion.
mysql_pconnect() en PHP
Mais cela ne paraît bon qu’en théorie. D'après mon expérience personnelle (et celle des autres), l'utilisation de cette fonctionnalité n'est pas justifiée. Vous rencontrerez de sérieux problèmes avec les limites de connexion, les limites de mémoire, etc.
Après avoir parcouru Internet pendant mon temps libre, j'ai été surpris qu'il n'y ait pas de guides d'articles spéciaux sur l'optimisation des requêtes SQL. Après avoir parcouru diverses informations et livres, je vais essayer de vous donner quelques conseils qui vous aideront à apprendre à rédiger les bonnes requêtes.
- Optimisation des tableaux. Cela est nécessaire lorsque de nombreuses modifications ont été apportées à la table : soit la plupart des données ont été supprimées, soit de nombreuses modifications ont été apportées aux chaînes de longueur variable - texte, varchar, blob. Le fait est que les enregistrements supprimés continuent d'être conservés dans le fichier d'index et que lorsque de nouveaux enregistrements sont insérés par la suite, les positions des anciens enregistrements sont utilisées. Pour défragmenter un fichier de données, utilisez la commande OPTIMIZE.
OPTIMISER LA TABLE `table1`, `table2`…
N'oubliez pas que lors de l'optimisation, l'accès à la table est bloqué.
- N'oubliez pas les tables temporaires HEAP. Bien que le tableau présente des limites, il est pratique d'y stocker des données intermédiaires, en particulier lorsque vous devez effectuer une autre sélection dans le tableau sans y accéder à nouveau. Le fait est que cette table est stockée en mémoire et donc son accès est très rapide.
- Rechercher par motif. Cela dépend de la taille du champ et si vous réduisez la taille de 400 octets à 300, le temps de recherche sera réduit de 25 %.
Reconstruire des données dans une table. Après des modifications fréquentes d'une table, cette commande peut améliorer les performances des données. Il les réorganise dans le tableau et les trie selon un champ spécifique.
ALTER TABLE `table1` ORDER BY `id`
Type de données. Il est préférable de ne pas indexer les champs de type chaîne, en particulier les champs TEXTE. Pour les tables dont les données changent fréquemment, il est conseillé d'éviter d'utiliser les champs VARCHAR et BLOB, car ce type crée une longueur de ligne dynamique, augmentant ainsi le temps d'accès aux données. Dans ce cas, il est recommandé d'utiliser le champ VARCHAR au lieu de TEXT, car son utilisation est plus rapide.
NOT NULL et champ par défaut. Il est préférable de marquer les champs comme NON NULL, car cela permet d'économiser de l'espace et d'éliminer les vérifications inutiles. Dans ce cas, il vaut la peine de définir la valeur par défaut des champs et d'insérer de nouvelles données uniquement si elles en diffèrent. Cela accélérera l’ajout de données et réduira le temps passé à analyser les tableaux. Et il convient de rappeler que les types de champs BLOB et TEXT ne peuvent pas contenir de valeurs par défaut.
Connexion permanente au serveur de base de données. Permet d'éviter de perdre du temps à la reconnexion. Cependant, il convient de rappeler que le serveur peut avoir une limite sur le nombre de connexions et que si le trafic du site est très élevé, une connexion constante peut jouer une blague cruelle.
Séparation des données. Il est recommandé de séparer les champs longs non clés dans une table distincte si la table source est utilisée pour récupérer constamment des données et change fréquemment. Cette méthode réduira la taille de la partie variable du tableau, ce qui entraînera une réduction de la recherche d'informations.
Cela est particulièrement vrai dans les cas où une partie des informations du tableau est destinée à la lecture seule, et l'autre partie est destinée non seulement à la lecture, mais aussi à la modification (n'oubliez pas que lors de l'écriture des informations, l'ensemble du tableau est verrouillé ). Un exemple frappant est le compteur de visites.
Il y a une table (nom en premier) avec les champs identifiant, contenu, spectacles. Le premier est une clé avec auto_increment, le second est du texte et le troisième est numérique - il compte le nombre d'impressions. Chaque fois que vous chargez la page, +1 est ajouté au dernier champ. Séparons le dernier champ dans le deuxième tableau. Ainsi, la première table (première) aura les champs id et content, et la seconde (seconde) aura les champs shows et first_id. Le premier champ est clair, je pense que le second aussi - une référence au champ d'identification de clé de la première table.
Désormais, des mises à jour constantes auront lieu dans le deuxième tableau. Dans le même temps, il est préférable de modifier le nombre de visites non pas par programmation, mais via une demande :
Et la sélection se fera à l'aide d'une requête compliquée, mais une ou deux ne sont pas nécessaires :
SELECT first.id, first.content, second.first_id, second.shows FROM second INNER JOIN first ON (first.id = second.first_id)
Il convient de rappeler que tout cela n'est pas pertinent pour les sites à faible trafic et avec une petite quantité d'informations.
Noms des champs, par lequel, par exemple, deux tables sont liées, il est souhaitable qu'elles portent le même nom. Ensuite, l'obtention simultanée d'informations de différentes tables via une seule requête sera plus rapide. Par exemple, d'après le paragraphe précédent, il est souhaitable que dans la deuxième table le champ ait un nom non pas first_id, mais simplement id, similaire à celui de la première table. Cependant, avec le même nom, il ne devient pas très clair quoi, où et comment. Ce conseil ne s’adresse donc pas à tout le monde.
Nécessite moins de données. Si possible, évitez les requêtes telles que :
SELECT * FROM `table1`
La requête n’est pas efficace car elle renvoie probablement plus de données que nécessaire pour fonctionner. Une meilleure option serait la conception :
SELECT id, nom FROM table1 ORDER BY id LIMIT 25
Je ferai immédiatement un ajout sur l'opportunité d'utiliser LIMIT. Cette commande limite le nombre de lignes renvoyées par la requête. Autrement dit, la requête devient « plus simple » et plus productive.
Si LIMIT est défini sur 10, alors après avoir reçu dix lignes, la demande est abandonnée.
Si une requête utilise le tri ORDER BY, elle ne s'effectue pas sur l'ensemble de la table, mais uniquement sur la sélection.
Si LIMIT est utilisé conjointement avec DISTINCT, la requête sera abandonnée une fois que le nombre spécifié de lignes uniques aura été trouvé.
Si vous utilisez LIMIT 0, une valeur vide sera renvoyée (parfois nécessaire pour déterminer le type d'un champ ou simplement vérifier le fonctionnement de la requête).
Restreindre l’utilisation de DISTINCT. Cette commande élimine les lignes en double dans le résultat. La commande nécessite un temps de traitement accru. Mieux combiné avec LIMIT.
Il y a une petite astuce. Si vous devez rechercher une correspondance dans deux tables, la commande ci-dessus s'arrêtera dès que la première correspondance sera trouvée.
Limiter l'utilisation de SELECT pour les tables en constante évolution.