- MySQL
Dans le travail quotidien, vous rencontrez des erreurs assez similaires lors de l’écriture de requêtes.
Dans cet article, je voudrais donner des exemples sur la façon de NE PAS écrire de requêtes.
- Sélectionnez tous les champs
SELECT * FROM tableLors de l'écriture de requêtes, n'utilisez pas une sélection de tous les champs - "*". Listez uniquement les champs dont vous avez réellement besoin. Cela réduira la quantité de données récupérées et envoyées. N'oubliez pas non plus de couvrir les index. Même si vous avez réellement besoin de tous les champs du tableau, il est préférable de les lister. Premièrement, cela améliore la lisibilité du code. Lorsqu'on utilise un astérisque, il est impossible de savoir quels champs se trouvent dans le tableau sans le regarder. Deuxièmement, au fil du temps, le nombre de colonnes de votre table peut changer, et s'il y a aujourd'hui cinq colonnes INT, alors dans un mois, des champs TEXT et BLOB pourront être ajoutés, ce qui ralentira la sélection.
- Requêtes dans un cycle.
Vous devez clairement comprendre que SQL est un langage d'exploitation défini. Parfois, les programmeurs habitués à penser en termes de langages procéduraux ont du mal à déplacer leur réflexion vers le langage des ensembles. Cela peut être fait tout simplement en adoptant une règle simple : « ne jamais exécuter de requêtes en boucle ». Exemples de la façon dont cela peut être fait :1. Échantillons
$news_ids = get_list("SELECT news_id FROM Today_news ");
tandis que($news_id = get_next($news_ids))
$news = get_row("SELECT titre, corps FROM news WHERE news_id = ". $news_id);La règle est très simple : moins il y a de demandes, mieux c'est (bien qu'il y ait des exceptions à cela, comme toute règle). N'oubliez pas la construction IN(). Le code ci-dessus peut être écrit en une seule requête :
SELECT titre, corps FROM Today_news INNER JOIN news USING(news_id)2. Insertions
$log = parse_log();
tandis que($record = suivant($log))
query("INSERT INTO logs SET valeur = ". $log["value"]);!}Il est beaucoup plus efficace de concaténer et d’exécuter une seule requête :
INSÉRER DANS les journaux (valeur) VALEURS (...), (...)3. Mises à jour
Parfois, vous devez mettre à jour plusieurs lignes dans une seule table. Si la valeur mise à jour est la même, alors tout est simple :
MISE À JOUR des nouvelles SET title="test" WHERE id IN (1, 2, 3).!}Si la valeur à modifier est différente pour chaque enregistrement, cela peut être fait avec la requête suivante :
MISE À JOUR des nouvelles
titre = CAS
QUAND news_id = 1 ALORS "aa"
QUAND news_id = 2 ALORS "bb" FIN
OÙ news_id DANS (1, 2)Nos tests montrent qu'une telle requête est 2 à 3 fois plus rapide que plusieurs requêtes distinctes.
- Effectuer des opérations sur des champs indexés
SELECT user_id FROM utilisateurs WHERE blogs_count * 2 = $valueCette requête n'utilisera pas l'index, même si la colonne blogs_count est indexée. Pour qu'un index soit utilisé, aucune transformation ne doit être effectuée sur le champ indexé dans la requête. Pour de telles requêtes, déplacez les fonctions de conversion vers une autre partie :
SELECT user_id FROM utilisateurs WHERE blogs_count = $value / 2 ;Exemple similaire :
SELECT user_id FROM utilisateurs WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(registered) = DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
volonté. - Récupérer les lignes uniquement pour compter leur nombre
$result = mysql_query("SELECT * FROM table", $link);
$num_rows = mysql_num_rows($result);
Si vous devez sélectionner le nombre de lignes qui satisfont à une certaine condition, utilisez la requête de table SELECT COUNT(*) FROM plutôt que de sélectionner toutes les lignes uniquement pour compter le nombre de lignes. - Récupérer des lignes supplémentaires
$result = mysql_query("SELECT * FROM table1", $link);
while($row = mysql_fetch_assoc($result) && $i< 20) {
…
}
Si vous n'avez besoin que de n lignes de récupération, utilisez LIMIT au lieu de supprimer lignes supplémentaires dans la demande. - Utilisation de ORDER BY RAND()
SELECT * FROM table ORDER BY RAND() LIMIT 1;Si la table comporte plus de 4 à 5 000 lignes, alors ORDER BY RAND() fonctionnera très lentement. Il serait beaucoup plus efficace d'exécuter deux requêtes :
Si la table a une clé primaire auto_increment et aucun espace :
$rnd = rand(1, query("SELECT MAX(id) FROM table"));
$row = query("SELECT * FROM table WHERE id = ".$rnd);Ou:
$cnt = query("SELECT COUNT(*) FROM table");
$row = query("SELECT * FROM table LIMIT ".$cnt.", 1");
ce qui, cependant, peut également être lent s'il y a un très grand nombre de lignes dans le tableau. - Usage grande quantité REJOIGNEZ
SÉLECTIONNER
v.video_id
un.nom,
g.genre
DEPUIS
vidéos AS v
REJOINDRE À GAUCHE
link_actors_videos AS la ON la.video_id = v.video_id
REJOINDRE À GAUCHE
acteurs AS a ON a.actor_id = la.actor_id
REJOINDRE À GAUCHE
link_genre_video AS lg ON lg.video_id = v.video_id
REJOINDRE À GAUCHE
genres AS g ON g.genre_id = lg.genre_idIl ne faut pas oublier que lors de la connexion de tables une à plusieurs, le nombre de lignes dans la sélection augmentera à chaque JOIN suivante. Dans de tels cas, il est plus rapide de diviser une telle requête en plusieurs requêtes simples.
- Utilisation de LIMITE
SELECT… FROM table LIMIT $start, $per_pageBeaucoup de gens pensent qu'une telle requête renverra $per_page d'enregistrements (généralement 10 à 20) et fonctionnera donc rapidement. Cela fonctionnera rapidement pour les premières pages. Mais si le nombre d'enregistrements est important et que vous devez exécuter une requête SELECT... FROM table LIMIT 1000000, 1000020, alors pour exécuter une telle requête, MySQL sélectionnera d'abord 1000020 enregistrements, supprimera le premier million et renverra 20. Ceci peut-être pas rapide du tout. Il n’existe pas de moyen trivial de résoudre le problème. Beaucoup limitent simplement le nombre de pages disponibles à un nombre raisonnable. Vous pouvez également accélérer ces requêtes en utilisant des index de couverture ou solutions tierces(par exemple sphinx).
- Ne pas utiliser ON DUPLICATE KEY UPDATE
$row = query("SELECT * FROM table WHERE id=1");Si($ligne)
query("MISE À JOUR de la table SET colonne = colonne + 1 WHERE id=1")
autre
query("INSERT INTO table SET colonne = 1, id=1");Une construction similaire peut être remplacée par une requête, à condition qu'il existe une clé primaire ou unique pour le champ id :
INSERT INTO table SET colonne = 1, id = 1 ON DUPLICATE KEY UPDATE colonne = colonne + 1
J'ai déjà écrit sur une grande variété de requêtes SQL, mais il est temps de parler de choses plus complexes, par exemple une requête SQL pour sélectionner des enregistrements dans plusieurs tables.
Lorsque vous et moi avons fait une sélection dans une table, tout était très simple :
SELECT noms_of_required_fields FROM nom_table OÙ condition_sélection
Tout est très simple et trivial, mais lors de l'échantillonnage à partir de plusieurs tables à la fois, cela devient un peu plus compliqué. Une difficulté consiste à faire correspondre les noms de champs. Par exemple, chaque table possède un champ id.
Regardons cette requête :
SELECT * FROM table_1, table_2 WHERE table_1.id > table_2.user_id
Beaucoup de ceux qui n'ont pas traité de telles requêtes penseront que tout est très simple, pensant que seuls les noms de tables ont été ajoutés avant les noms de champs. En fait, cela évite les conflits entre noms de champs identiques. Cependant, la difficulté ne réside pas là-dedans, mais dans l'algorithme d'une telle requête SQL.
L'algorithme de travail est le suivant : le premier enregistrement est extrait de la table_1. L'identifiant de cet enregistrement est extrait de la table_1. Ensuite, le tableau table_2 apparaît complètement. Et tous les enregistrements sont ajoutés lorsque la valeur du champ user_id est inférieure à l'identifiant de l'enregistrement sélectionné dans table_1 . Ainsi, après la première itération, il peut y avoir de 0 à un nombre infini d'enregistrements résultants. A l'itération suivante, l'enregistrement suivant de la table table_1 est pris. La table entière table_2 est à nouveau analysée et la condition de sélection table_1.id > table_2.user_id est à nouveau déclenchée. Tous les enregistrements qui remplissent cette condition sont ajoutés au résultat. La sortie peut être un très grand nombre d’enregistrements, plusieurs fois plus grand que la taille totale des deux tables.
Si vous comprenez comment cela fonctionne dès la première fois, alors c'est génial, mais sinon, lisez jusqu'à ce que vous le compreniez parfaitement. Si vous comprenez cela, ce sera plus facile.
La requête SQL précédente, en tant que telle, est rarement utilisée. Il a simplement été donné pour expliquer l'algorithme d'échantillonnage multi-tables. Examinons maintenant une requête SQL plus trapue. Disons que nous avons deux tables : avec les produits (il y a un champowner_id, qui est responsable de l'identifiant du propriétaire du produit) et avec les utilisateurs (il y a un champ id). Nous souhaitons obtenir tous les enregistrements dans une seule requête SQL, et chacun contient des informations sur l'utilisateur et son produit. L'entrée suivante contenait des informations sur le même utilisateur et son prochain produit. Lorsque les produits de cet utilisateur sont épuisés, passez à l'utilisateur suivant. Ainsi, il faut joindre deux tables et obtenir un résultat dans lequel chaque enregistrement contient des informations sur l'utilisateur et l'un de ses produits.
Une requête similaire remplacera 2 requêtes SQL : pour sélectionner séparément dans la table avec les marchandises et dans la table avec les utilisateurs. De plus, une telle demande mettra immédiatement en relation l’utilisateur et son produit.
La requête en elle-même est très simple (si vous avez compris la précédente) :
SELECT * FROM utilisateurs, produits WHERE users.id = products.owner_id
L'algorithme ici est déjà simple : le premier enregistrement est extrait de la table des utilisateurs. Ensuite, son identifiant est pris et tous les enregistrements de la table products sont analysés, en ajoutant au résultat ceux dont leowner_id est égal à l'identifiant de la table users. Ainsi, lors de la première itération, toutes les marchandises du premier utilisateur sont collectées. A la deuxième itération, tous les produits du deuxième utilisateur sont collectés, et ainsi de suite.
Comme vous pouvez le constater, les requêtes SQL permettant de sélectionner parmi plusieurs tables ne sont pas les plus simples, mais leurs avantages peuvent être énormes, il est donc très souhaitable de connaître et de pouvoir utiliser de telles requêtes.
Lors de la dernière leçon, nous avons rencontré un inconvénient. Lorsque nous avons voulu savoir qui avait créé le sujet « vélos », nous avons fait une demande correspondante :
A la place du nom de l'auteur, nous avons reçu son identifiant. Cela est compréhensible, car nous avons effectué une requête sur une table - Sujets, et les noms des auteurs de sujets sont stockés dans une autre table - Utilisateurs. Par conséquent, après avoir découvert l'identifiant de l'auteur du sujet, nous devons faire une autre requête - à la table Utilisateurs pour connaître son nom :
SQL offre la possibilité de combiner ces requêtes en une seule en transformant l'une d'elles en sous-requête (requête imbriquée). Ainsi, pour savoir qui a créé le sujet « vélos », nous ferons la requête suivante :
Autrement dit, après le mot-clé OÙ, nous écrivons une autre demande dans la condition. MySQL traite d'abord la sous-requête, renvoie id_author=2 et cette valeur est transmise à la clause OÙ demande externe.
Il peut y avoir plusieurs sous-requêtes dans une requête, la syntaxe d'une telle requête est la suivante : Notez que les sous-requêtes ne peuvent sélectionner qu'une seule colonne dont elles renverront les valeurs à la requête externe. Essayer de sélectionner plusieurs colonnes entraînera une erreur.
Pour consolider cela, faisons une autre demande et découvrons quels messages l'auteur du sujet « vélos » a laissé sur le forum :
Compliquons maintenant la tâche, découvrez dans quels sujets l'auteur du sujet « vélos » a laissé des messages :
Voyons comment cela fonctionne.
- MySQL exécutera d'abord la requête la plus approfondie :
- Le résultat obtenu (id_author=2) sera transmis à une requête externe, qui prendra la forme :
- Le résultat obtenu (id_topic:4,1) sera transmis à une requête externe, qui prendra la forme :
- Et cela donnera le résultat final (topic_name : sur la pêche, sur la pêche). Ceux. l'auteur du sujet "vélos" a laissé des messages dans le sujet "À propos de la pêche" créé par Sergei (id=1) et dans le sujet "À propos de la pêche" créé par Sveta (id=4).
- Il n'est pas recommandé de créer des requêtes avec un degré d'imbrication supérieur à trois. Cela entraîne une augmentation du temps d’exécution et des difficultés de compréhension du code.
- La syntaxe donnée pour les requêtes imbriquées est probablement la plus courante, mais pas la seule. Par exemple, au lieu de demander
écrire
Ceux. nous pouvons utiliser tous les opérateurs utilisés avec mot-clé OÙ (nous les avons étudiés dans la dernière leçon).
Dans ce court article nous parlerons des bases de données notamment MySQL, de l'échantillonnage et du comptage. Lorsque vous travaillez avec des bases de données, vous devez souvent compter des quantités lignes COUNT() avec ou sans une certaine condition, cela est extrêmement simple à faire avec la requête suivante
Voir le code MYSQL
La requête renverra une valeur avec le nombre de lignes du tableau.
Compter avec condition
Voir le code MYSQL
La requête renverra une valeur avec le nombre de lignes dans la table satisfaisant cet état:var = 1
Pour obtenir plusieurs valeurs de nombre de lignes avec des conditions différentes, vous pouvez exécuter plusieurs requêtes une par une, par exemple
Voir le code MYSQL
Mais dans certains cas, cette approche n’est ni pratique ni optimale. Il devient donc pertinent d’organiser une requête avec plusieurs sous-requêtes afin d’obtenir plusieurs résultats à la fois dans une seule requête. Par exemple
Voir le code MYSQL
Ainsi, en exécutant une seule requête sur la base de données, nous obtenons un résultat avec un nombre de lignes pour plusieurs conditions, contenant plusieurs valeurs de comptage, par exemple
Voir le code TEXTE
| c1|c2|c3 --------- 1 |5 |8 |
L'inconvénient de l'utilisation de sous-requêtes, par rapport à plusieurs requêtes distinctes, est la rapidité d'exécution et la charge sur la base de données.
L'exemple suivant d'une requête contenant plusieurs COUNT en un Requête MySQL, est construit légèrement différemment, il utilise les constructions IF(condition, value1, value2), ainsi que la sommation SUM(). Vous permettant de sélectionner des données selon des critères spécifiés au sein d'une seule requête, puis de les résumer et d'afficher plusieurs valeurs en conséquence.
Voir le code MYSQL
Comme le montre la demande, elle a été construite de manière assez succincte, mais la rapidité de son exécution n'était pas non plus satisfaisante, le résultat de cette demande il y en aura un prochain,
Voir le code TEXTE
| total|c1|c2|c3 -------------- 14 |1 |5 |8 |
Ensuite, je fournirai des statistiques comparatives sur la vitesse d'exécution de trois options de requête pour sélectionner plusieurs COUNT(). Pour tester la vitesse d'exécution des requêtes, 1 000 requêtes de chaque type ont été exécutées, avec une table contenant plus de trois mille enregistrements. De plus, à chaque fois la requête contenait SQL_NO_CACHE pour désactiver la mise en cache des résultats par la base de données.
Vitesse d'exécution
Trois requêtes distinctes : 0,9 s
Une requête avec sous-requêtes : 0,95 s
Une requête avec construction IF et SUM : 1,5 s
Conclusion. Nous disposons donc de plusieurs options pour créer des requêtes sur la base de données. Données MySQL avec plusieurs COUNT(), la première option avec des requêtes séparées n'est pas très pratique, mais donne le meilleur résultat en termes de vitesse. La deuxième option avec sous-requêtes est un peu plus pratique, mais sa vitesse d'exécution est légèrement inférieure. Et enfin, la troisième version laconique de la requête avec les constructions IF et SUM, qui semble la plus pratique, a le plus basse vitesse des performances, qui sont presque deux fois inférieures aux deux premières options. Par conséquent, lors de l'optimisation du fonctionnement d'une base de données, je recommande d'utiliser la deuxième version de la requête contenant des sous-requêtes avec COUNT(), d'une part, sa vitesse d'exécution est proche du résultat le plus rapide, et d'autre part, une telle organisation au sein d'une requête est assez pratique .