[Résolu] Select dans plusieurs tables MySQL

Union est réputé pour ne pas être spécialement rapide... donc je le déconseillerais si tu peux faire autrement.

En ce qui concerne les vues, il n'y a pas grand chose à en tirer côté performances en MySQL. Elles sont rapidement réécrites en interne et prennent exactement le même temps que les select qu'elles remplacent. Tu peux juste y gagner en lisibilité.
Les autres SGBD sont en général bien meilleurs avec les vues et en profitent pour faire des optimisations; pas MySQL.


Sans trop savoir à quoi ressemblent tes données, je dirais à priori que si toutes tes tables mensuelles ont exactement la même structure, c'est crétin. Mieux vaut avoir une seule table avec 100M enregistrements que 100 tables avec 1M enregistrements chacune. Si les index ont correctement été choisis, c'est pas un gros problème d'avoir une table avec 100M enregistrements, les SGBD sont faits pour ça. Sauf erreur, la taille maximum d'une table en MySQL est de 2 To.... le plus important quand on a des énormes tables comme ça est de bien placer ses index.
Par contre, comme tu le vois maintenant, avoir plusieurs tables avec exactement la même structure, ça pose problème, c'est compliqué et c'est inutilement lent quand on arrive à devoir réunir les différentes parties.

ET si tes tables mensuelles n'ont pas la même structure, par contre, là, effectivement, c'est plutôt un gros casse-tête que de vouloir les réunir. Dans ce cas, un langage de programmation classique compilant les résultats de plusieurs requêtes indépendantes sera sûrement plus efficace.
Modifié par QuentinC (16 Oct 2013 - 19:25)

Merci à tous pour vos réponses qui me confortent dans mon idée.

Ce que j'ai oublié de préciser:
- J'ai déjà fais une proposé à mon client de gérer le problème en PHP, mais c'est du boulot, donc un cout non négligeable.
- Son service informatique lui a parlé de UNION et des vues, donc il me demande de creuser ces pistes pour réduire le cout du projet.
- Ils ne peuvent/veulent pas modifier la base, ca serait a priori beaucoup trop de boulot de leur coté.
- les tables ont la même structure (c'est un historique de consommation)
- Bien sur, il y a déjà des index sur ces tables (quand même !!)

Par contre, je pensais que les VUES (que je ne maitrise pas du tout), m'apporterait plus de performances.

Merci Tournikoti pour tes précisions, c'est grosso modo ce que je pensais faire.
Par contre, je pensais utiliser "LIMIT" pour gérer le nombre de résultats retourner. C'est à dire que je met à un LIMIT à 20..si la requete sur la 1ère table me retourne moins de 20 réponses, je vais chercher sur la 2° ce qu'il me manque en faisant un LIMIT à 5 par exemple et ainsi de suite sur toutes les tables de la période que je dois traiter.

Et au fait, est ce qu'on peut appliquer un LIMIT avec UNION...je vais regarder

Encore merci pour votre aide

Marc
Modifié par pifoux (16 Oct 2013 - 22:21)

Salut,

Je suis d'accord avec QuentinC : hyper dommage que tout ne soit pas dans une même table, conceptuellement c'est assez débile. Mais bon, on ne vit pas dans un monde idéal.

Je ne suis pas d'accord avec Tournikoti : quand on gère des données sous MySQL/PHP, en termes de performances, ya pas photo : il faut faire bosser au maximum MySQL, dont c'est le métier, et non pas PHP. C'est une règle que j'ai toujours entendue, et qui en fait, est absolument logique.

UNION sur des mégatuples, c'est sûr que ça va faire méchamment turbiner le serveur, en particulier du fait de l'élimination des doublons propres au UNION. Si c'est possible, Pifoux, tu devrais essayer le UNION ALL, qui se contrefiche des doublons et concatène bestialement les tables. A mon avis, niveau perfs, ça devrait limiter la casse.

Salut,

@QuentinC : +1

@pifoux :

Ce que je ferai si j'ai 10 tables avec la même structure :
- Soit j'utiliserai une table et je ferai :

INSERT INTO une_table... AS SELECT .... FROM une_autre_table ....

soit je crée une table avec la même structure et je ferai :

INSERT INTO ma_nouvelle_table... AS SELECT .... FROM une_table ....

- En PHP, je ferai des requêtes dynamiques pour afficher le résultat escompté.

La commande UNION permet de lier des tables entres elles mais strictement avec la même structure des champs. Un INT de 4 caractères sur une table et un INT de 5 caractères sur une autre table posera des petits soucis je pense.

Les vues ne sont finalement que des alias de SELECT stockées.
Modifié par niuxe (17 Oct 2013 - 00:35)

Merci à tous,

Je vais faire des tests de performances avec UNION ALL.
Le choix de plusieurs tables est (je pense) historique, de plus comme il s'agit d'historiques de consommation, ce n'est pas totalement idiot. Chaque fin de mois, on ajoute une table et on en archive une autre (on ne garde à disposition que 6 mois).
Enfin, c'est comme ca.
Je pense aussi qu'il est préférable de faire faire le maximum de choses par MySQL (dans ce cas en tout cas), pour des raisons de performances.

Je vous tiens au courant, si je fais des tests de performances

Merci à tous

Marco

Bonsoir à toutes et à tous.

"QuentinC" a écrit :
Sans trop savoir à quoi ressemblent tes données, je dirais à priori que si toutes tes tables mensuelles ont exactement la même structure, c'est crétin.

Non, justement ce n'est pas du tout crétin comme tu sembles le croire.
En fait, tout le problème se trouve dans les performances des temps de réponses pour atteindre l'information recherchée.

Admettons que tu désires tout mettre dans une seule base de données de 2 To.
Même avec un indexe sur la clé de recherche, tu devras balayer la totalité de la base de données, alors que la recherche dans 95% des cas, se fera dans le mois courant, voire dans le mois précédent. Et cette façon de faire prend énormément de temps. Pourquoi ?
Car plus la volumétrie est importante et plus tu auras de niveaux différents dans l'indexe pour atteindre l'information recherchée.
Un indexe mal taillé peut produire un accès plus lent que sans indexe.

Admettons encore que tu te retrouves avec une base de données complètement saturées. Il faut alors créer une procédure afin d'archiver l'excédent et comme tu n'as rien prévue, en tant que traitement pour effectuer une recherche sur cette archive, tu te retrouves dans l'impossibilité de pouvoir le faire.

La solution proposée est une automatisation de l'archivage. Il existe des cas où, même avec une centaine de base de données, disons une par mois, tu peux quand même faire en sorte d'avoir accès à une seule, mais ce sera au prix d'une dégradation des performances.
J'ai connu cela avec la gestion de la numérisation des chèques. Il y avait environ 30 millions de chèques à traiter chaque jour.
Et nous devions archiver cela sur dix ans ! Et bien sûr, tout était fait pour obtenir des performances acceptables.

Il faut aussi connaitre le critère de l'archivage. Ainsi comme je le pense, le mois de la date va désigner le rang de l'archivage. Par accès à une autre table, on récupère ce rang simplement en entrant le mois de la date. Exemple. Nous sommes le 18/10/2013. l'accès à la table d'archivage donne le rang 0 (c'est la base courante, non encore archivée). Tu désires faire une recherche dont le critère se trouve dans le mois 07. Ainsi tu devras accéder à l'archivage 3.

"Petitbato" a écrit :
quand on gère des données sous MySQL/PHP, en termes de performances, y a pas photo : il faut faire bosser au maximum MySQL, dont c'est le métier, et non pas PHP. C'est une règle que j'ai toujours entendue, et qui en fait, est absolument logique.

Je suis administrateur DB2 sur gros système, et je suis fréquemment confronté à des problèmes de performances.
Nous faisons toujours en sorte de ne jamais utiliser des unions ou des vues qui ne font qu'augmenter le temps d'exécution, sauf cas exceptionnel.
Il est souvent plus rapide de faire un déchargement séquentielle de la table (un vidage) et ensuite de sélectionner les enregistrements par programme.
Pourquoi ? Passer d'une piste à une autre sur le disque est extrêmement rapide.
Inversement, si tu dois balayer la totalité de la base et que tu passes par les indexes, tu devras lire la totalité des pistes de la partie data et aussi lire la totalité des pistes de la partie indexe. Donc je ne vois pas ce qu'il y a de logique dans cette manœuvre, puisque tu augmentes le temps des accès.

Mais inversement, il est impensable de décharger une base de données de 2 To, juste pour récupérer 20 lignes.
Il n'y a pas de solutions toutes faites ! Selon le traitement désiré, on privilégie telle solution plutôt que telle autre.

@ Niuxe : tu es hors sujet.
J'ai bien compris que ta solution consiste à créer une table temporaire afin de pratiquer le critère de la sélection.
Mais comme tu ignores le critère, je ne vois pas l'intérêt de faire ainsi, puisque en admettant que ta recherche est du genre :

where date_saisie beetween 2003-03-15 AND 2003-03-18;

tu t’aperçois que ta recherche va se faire dans une seule archive ! Donc pourquoi tout récupérer ?
Et puis, les tables temporaires ont la capacité de stockage du disque où il se trouve.
Donc tu n'arriveras jamais à faire entrer la totalité de toutes les archives, qui je suppose sont largement supérieure à 2 To dans 2 To.

Un insert est très couteux en terme de performances, bien plus que le déchargement en fichier séquentiel.

"Pifou" a écrit :
Bien sur, il y a déjà des index sur ces tables (quand même !!)

Tu ne peux pas dire cela avant d'avoir consulter les indexes de ta base de données.
Imagine un seul instant que le critère soit une date et que le concepteur de la base de données n'avait pas ce critère au moment de la création de la base de données.
Donc tu vas te retrouver avec une base de données découpées en plusieurs archives, avec un critère de sélection sans indexe.
Et si en plus, ils ne veulent ou ne peuvent pas ajouter un indexe, peut-être pour la simple raison que cette base ne leur appartient pas et sert à une autre application, je ne te parle pas des performances plutôt lentes auxquelles tu vas être confronté.

Le seul conseil que je peux te donner, c'est de travailler avec l'administrateur des bases de données afin de trouver la meilleure solution en terme de performance.

@+

@tournikoti
J'avais imaginé des accès à des dates ou des périodes aléatoires. Mais effectivement tu as très probablement raison si les accès ne sont pas aléatoires et essentiellement dirigés vers le mois en cours ou les x précédents.

Par contre, il me semblais qu'il existait des commandes en MySQL pour demander une fragmentation automatique en plusieurs parties de table en se basant sur un critère. Par partie de table, comprendre, différents fichiers physiques, par opposition à un énorme fichier unique comme c'est apparament habituellement la règle en MySQL. Je n'ai aucune idée de à quoi pourraient ressembler ces commandes mais je suis presque sûr d'avoir déjà vu passer un truc comme ça en fouillant au hasard dans le manuel.

Il me semblais que c'était dit comme pouvant être utile sur des énormes tables, tout en induisant évidemment un coût au cas où plusieurs parties doivent être lues pour répondre à une requête par rapport à si on avait la table en une seule partie.

N'a-t-on pas à y gagner en utilisant cette possibilité, plutôt que d'organiser les requêtes et les résultats manuellement du côté du client ?
Modifié par QuentinC (18 Oct 2013 - 13:25)

Salut QuentinC.

MySql est un SGBD relationnel, c'est-à-dire un système de gestion de base de données de type relationnel. Pour manipuler ce SGBD, on utilise le langage de requête SQL. MySql est aussi un serveur.

PhpMyAdmin est une application web développé en Php pour gérer le SGBD MySql.

De quelle commande parles-tu ?

Si tu parles du langage SQL, on décharge une base de données avec un select sur critères et on charge l'autre base avec un insert.

Dans un batch windows, tu peux utilise 'mysql' en tant que ligne de commandes. C'est de cette façon que je procède sur mon ordi lorsque je gère mes bases de données.

Si tu parles de PhpMyAdmin, il existe les commandes import et export qui sont l'habillage de la commande mysqldump. Si je désire transférer une base de données de chez moi par exemple, vers un site comme alwaysdata ou ovh, je procède par import et export.

Là tu me parles de fragmentation automatique ? Qu'est-ce qui est automatique ?
Je ne comprends pas très bien ce que tu entends par là.

S'il s'agit de lancer un script c-shell ou k-shell, par exemple sous linux avec la commande crontab, cela n'a aucun rapport avec MySql, même si cela concerne le déchargement d'une base de données. Crontab est un utilitaire pour planifier des tâches et appartient au système d'exploitation linux.

Je suppose que tu veux parler d'une automatisation périodique, dans le cadre de l'exploitation. A ma connaissance, on ne peut pas automatiser un tel traitement (sans intervention humaine) car on n'est jamais à l'abri d'un manque de place ou d'un programme non terminé qui monopolise une base de données lorsque tu veux faire cela. D'ailleurs à quoi peut servir un administrateur de base de données selon toi ?

On peut tout imaginer dans un traitement impliquant une base de données. Le critère qui détermine le bon choix est celui de la performance. Si chaque soir, on doit mettre environ 80% à jour de la base de données, un déchargement puis un traitement et ensuite la reconstruction de cette base de données peut-être la solution. Pourquoi ?
A cause des suppression et création d'enregistrements qui peuvent désorganiser la base.
Dans la journée, seul les consultations et mises à jour ponctuelles sont autorisées. Et le temps d'accès est primordiale. La nuit, on effectue le travail de masse et le batch monopolise le traitement.

@+
Modifié par tournikoti (19 Oct 2013 - 03:57)

tournikoti a écrit :
Nous faisons toujours en sorte de ne jamais utiliser des unions ou des vues qui ne font qu'augmenter le temps d'exécution, sauf cas exceptionnel.
Il est souvent plus rapide de faire un déchargement séquentielle de la table (un vidage) et ensuite de sélectionner les enregistrements par programme.
...
Il n'y a pas de solutions toutes faites ! Selon le traitement désiré, on privilégie telle solution plutôt que telle autre.

C'est sûr que les UNION c'est un tue-la-perf. Et là où je suis 100% d'accord avec toi (et c'est pour ça que j'aime l'informatique) : il n'y pas de solutions toutes faites. Alors oui, pourquoi pas, il doit bien y avoir des cas où un script traite les données plus vite qu'un serveur MySQL - même si c'est bien dommage conceptuellement. Ma seule expérience sur des gros volumes, c'était en C++ sur serveur Oracle, et c'est clair qu'aucune ligne de C++ ne pouvait rivaliser avec l'efficacité d'Oracle.

D'ailleurs, ça pourrait presque être une conversation pour le "bar du forum", mais ça m'intéresse de voir les solutions NoSQL qui sont mises en oeuvre sur le web, je trouve que c'est un domaine passionnant à défricher.

Enfin, pour Pifoux : je crois bien qu'il va falloir benchmarker pour faire avancer la réflexion

@tournikoti:

Désolé si je n'ai pas été clair. JE parlais de commandes SQL à faire à la création de la table ou après qui permettaient que la table soit stockée en plusieurs parties plutôt qu'en une seule, pouvant éventuellement accélérer les requêtes courantes ne se faisant que sur une seule des parties à la fois.

Par contre c'est évidemment pas du SQL standard, c'était des commandes propres à MySQL.

Bonsoir à toutes et à tous.

@ petibato : Ce que je préconise, c'est de faire des "select" simples et rien de plus. Le reste peut se faire en php. Surtout ne pas faire des imbrications où tu combines plusieurs requêtes afin d'obtenir le résultat de ce que tu recherches. S'imposer la contrainte de tout faire à l'aide des requêtes SQL, au détriment du temps d'exécution. est une erreur de débutant.

L'union est une fusion entre deux requêtes. Pourquoi procéder de cette façon alors que tu peux exactement le faire en PHP. Dans ce que tu crois être une opérations basique en SQL, il y a des sous-opérations que tu ignores et qui seront mise en œuvre par MySql. Le tri sur un fichier séquentiel est bien plus rapide que le tri basé sur les indexes d'une ou plusieurs tables. Par exemple, éviter de faire un "order by" sur des colonnes qui ne sont pas indexées.

Si tu as déjà fait du développement en C++, tu comprends ce que je veux dire pour la rapidité d'exécution sur des fichiers séquentiels. Les bases de données sur micro ordinateur ou sur mini sont peu volumineuses. On peut se permettre des écarts dans la façon de procéder, et la performance sera que très légèrement impactée.

@ QuentinC : en ce qui concerne l'insertion de lignes dans une base de données répartie sur plusieurs tables physiques, on utilise les procédures stockées. Attention : la procédure stockées est couteuse en temps d’exécution et il est conseillé de les utiliser en mode transactionnel. Le terme "transactionnel" signifie d'une manière ponctuelle, à l'inverse d'un travail de masse qui est le batch. Mais la création des tables physiques restent manuelle et il faut s'adjoindre à cette procédure une autre table qui dira où ranger les lignes. C'est cette autre table qui va servir au pilotage de la procédure stockée (mois courant --> table 0, mois précédant --> table 1, ...).

Si ce n'est pas cela, je ne vois pas très bien de quoi tu parles.

@+
Modifié par tournikoti (20 Oct 2013 - 02:16)

@Tournikoti: j'ai retrouvé ce que j'avais vu passer. Ca comence un peu à dater, mais ça s'est sûrement un peu amélioré depuis:
http://krierjon.developpez.com/mysql/partitionnement/

Bonsoir QuentinC.

J'ai cru que tu me parlais d'un outil spécial à MySql qui réalisait un traitement de dispatching ou je ne sais quoi d'autre.
En fait, ce dont tu me parles est une technique de conception des bases de données.
Nous l'utilisons au travers des procédures stockées en DB2, comme je te l'ai indiqué ci-dessus.
Il n'y a rien d'automatique comme tu sembles le croire.
C'est à l'administrateur de configurer l'environnement qu'il désire obtenir.
Ceci est couteux en performance car pour reconstituer une table virtuelle, on est obligé d'utiliser des vues.
Cela m'est arrivé qu'une seule fois de le faire dans le cadre des images numérisées.

Je te rassure, ce n'est pas ancien, mais simplement peu utilisé, voire même pas du tout.
Quand on installe ceci et que ça fonctionne correctement, on n'intervient plus.
Et puis, ce genre de problème ne se rencontre pas tous les jours.
Comme je te l'avais indiqué, c'est aussi dans le cadre d'un archivage des données.

En tout cas, merci pour l'information, mais je connaissais déjà.

P.S. : tu me testais ou quoi ?

@+
Modifié par tournikoti (21 Oct 2013 - 23:11)

a écrit :
P.S. : tu me testais ou quoi ?

Non. Par contre depuis le début de cette conversation, je n'ai pas l'impression qu'on parle la même langue...

Là ce tutoriel parle de partitionnement et toi tu me parles de reconstituer des tables virtuelles avec des vues. Je comprends bien l'idée, mais je ne vois pas le rapport: là c'est un partitionnement automatique, et toi tu fais un partitionnement manuel.

Heu... à la base, c'est une question de Pifoux !

Pifoux, aurais-tu fait un benchmark par rapport à ton problème ?

Et pour le coup, voici un bout de code :

Mathieu Ricard a écrit :
Si l’ego constituait vraiment notre essence profonde, on comprendrait notre inquiétude à l’idée de s’en débarrasser. Mais s’il n’est qu’une illusion, s’en affranchir ne revient pas à extirper le cœur de notre être, mais simplement à ouvrir les yeux, à dissiper une erreur.

Salut Petibato,

J'ai fais un rapide test de performances avec UNION et UNION ALL.
C'est beaucoup mieux avec UNION ALL, mais ca reste lent (2,5 secondes pour lire 500 000 enregistrements avec une clause WHERE et un ORDER BY Indexé).
Je viens donc de prévenir mon client de la situation.
Il faudrait que je fasse des essais avec une solution en PHP, mais j'attends que le projet soit signé pour cela. Mais je pense que ca pourrait être une solution plus performante, car j'utiliserai des requetes simples avec des LIMIT. Je ne récupérerai que ce dont j'ai besoin..contrairement requêtes imbriquées dont je ne pourrai analyser le résultat qu'à la fin.
Je vous tiendrai au courant, si le projet abouti

Merci

Marco

Bonsoir à toutes et à tous.

@ QuentinC : je ne sais pas ce que tu cherches à faire, mais à bien te comprendre, sur le sujet du MySql, ou des bases de données en générales, tu n'as qu'une connaissance théorique !

Comme nous le fait remarqué Petibato, le sujet appartient à Pifoux et c'est lui qui est demandeur d'une solution pour son problème.

Donc j'arrête la conversation sur ce thème qui est hors-sujet !

@ Pifoux : as-tu demandé à ton client la structure des bases de données sur lesquelles tu vas travailler ? Et as-tu aussi un exemple de critère de ce qu'il recherche à faire ?

Il faut commencer par faire les spécifications de la demande du client. Existe-t-il plusieurs propositions séparées ou une seule qui se décline en fonction d'un paramétrage spécifique ?

A partir du moment où l'on sait ce que l'on doit faire, la solution est plus facile à trouver.

@+