GROUP BY sur une vue très lent [Résolu]

[johnalias110]

Bonjour à tous,

Une petite description de mes tables/vues avant d'exposer mon problème :

Une table principale, avec exactement 17 colonnes :

Code :

1
2
3
4
5
6
7
8
9
 
TABLE annuel_ann
======================
ann_annee        int(11) PK
ann_code     char(10) PK
...
ann_imp int(11)
ann_niveau       char(1)
ann_type         int(11)

Avec des index, entre autres, sur les trois derniers champs.

Il y a également 2 vues permettant des accès différents aux données. La première permettant un accès total alors que la seconde limite l'accès à certaines données :

Code :

1
2
3
4
5
6
7
 
CREATE OR REPLACE VIEW vue_annuel_ann_total AS 
SELECT * FROM annuel_ann;
 
CREATE OR REPLACE VIEW vue_annuel_ann_part AS 
SELECT * FROM annuel_ann, vue_imp_zone1
WHERE ann_imp = imp_id;

Il faut également savoir que vue_imp_zone1 ne sélectionne qu'une colonne qui sont des identifiants. Ce qui fait que vue_annuel_ann_part possède, ici, une colonne de plus que vue_annuel_ann_total. Dans ma base de données, je ne sélectionne en fait que les 17 colonnes de la table annuel_ann mais pour simplifier, j'ai mis "*" à la place des champs sélectionnés.

Il y a +/- 800.000 enregistrements pour vue_annuel_ann_total et 120.000 pour vue_annuel_ann_part.

Jusque là, pas de souci :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT count(*) FROM vue_annuel_ann_total;
=> 0.214 sec
 
SELECT count(*) FROM vue_annuel_ann_part;
=> 0.110 sec
 
SELECT * FROM vue_annuel_ann_total LIMIT 0, 1000;
=> 0.007 sec
 
SELECT * FROM vue_annuel_ann_part LIMIT 0, 1000;
=> 0.013 sec

J'ai ensuite voulu commencer à effectuer des count() sur les vues en groupant par différentes colonnes :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT ann_annee, count(*) FROM vue_cmpt_annuel_ann_cf GROUP BY ann_annee;
=> 0.280 sec
 
SELECT ann_annee, count(*) FROM vue_annuel_ann_part GROUP BY ann_annee;
=> 0.089 sec
 
SELECT ann_niveau, count(*) FROM vue_cmpt_annuel_ann_cf GROUP BY ann_niveau;
=> 0.356 sec
 
SELECT ann_niveau, count(*) FROM vue_annuel_ann_part GROUP BY ann_niveau;
=> 89.086 sec !!!!!!!

Et voici donc mon problème. Alors que le GROUP BY marche sans problème pour la vue vue_cmpt_annuel_ann_cf, le même pour vue_annuel_ann_part est EXTREMEMENT lent alors qu'il y a moins de données.

Je me suis dit que cela devait venir du fait que la vue est créée à partir de plusieurs tables/vues et j'ai recréé une vue comme ceci :

Code :

1
2
3
4
5
6
7
8
9
10
 
CREATE OR REPLACE VIEW vue_annuel_ann_part2 AS
SELECT * FROM cmpt_annuel_ann2
WHERE ann_imp IN (SELECT imp_fase FROM vue_imp_zone1) ;
 
SELECT count(*) FROM vue_annuel_ann_part2
=> 4.446 sec
 
SELECT ann_niveau, count(*) FROM vue_annuel_ann_part2 GROUP BY ann_niveau
=> 18.045 sec

Cela fait bien diminuer le temps de la requête avec le GROUP BY (mais cela reste tout de même assez élevé) mais fait augmenter le temps d'un simple count.

Je ne comprends absolument pas d'où cela peut venir...
Si vous avez une idée ou une piste de solution, cela m'aiderait grandement !

Merci d'avance.

[CinePhil]

Il faudrait faire un EXPLAIN de la requête qui pose problème pour essayer de comprendre ce qu'il se passe mais il est possible que l'index sur ann_niveau ne soit pas utilisé car il ne contient pas assez de valeurs différentes, étant un CHAR(1).

Sinon, un petit OPTIMIZE pourrait peut-être permettre de réorganiser un peu les données et accélérer la requête ?

[johnalias110]

Merci de ta réponse. En effet, j'étais justement en train de faire quelques EXPLAIN pour tenter de comprendre. Voici les résultats :

(1) Pour vue_annuel_ann_total :

Code :

1
2
3
4
5
6
 
EXPLAIN SELECT ann_niveau, count(*) FROM vue_annuel_ann_total GROUP BY ann_niveau
 
id,select_type,TABLE,type,possible_keys,KEY,key_len,ref,rows,Extra
--------------------------------------------------------------------
1, SIMPLE, annuel_ann, INDEX, NULL, idx_ann_niveau, 3, NULL, 794064, USING INDEX

(2) Pour vue_annuel_ann_part :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
EXPLAIN SELECT ann_niveau, count(*) FROM vue_annuel_ann_part GROUP BY ann_niveau
OU MEME 
EXPLAIN SELECT * FROM vue_annuel_ann_part
 
id,select_type,TABLE,type,possible_keys,KEY,key_len,ref,rows,Extra
--------------------------------------------------------------------
1,SIMPLE,annuel_ann,ALL,idx_ann_fase_implantation,NULL,NULL,NULL,794064,"Using temporary; Using filesort"
1,SIMPLE,util_implantation_imp,eq_ref,"PRIMARY,idx_imp_sct_code",PRIMARY,4,db_pilotage_test.annuel_ann.ann_fase_implantation,1,
1,SIMPLE,geog_secteur_sct,eq_ref,"PRIMARY,fk_sct_cmn",PRIMARY,27,db_pilotage_test.util_implantation_imp.imp_sct_code,1,
1,SIMPLE,geog_commune_cmn,eq_ref,"PRIMARY,fk_cmn_zon",PRIMARY,15,db_pilotage_test.geog_secteur_sct.sct_cmn_code,1,"Using where"
1,SIMPLE,geog_zone_zon,eq_ref,PRIMARY,PRIMARY,8,db_pilotage_test.geog_commune_cmn.cmn_zon_code,1,"Using index"

Code :

1
2
3
4
5
6
7
8
9
10
 
EXPLAIN SELECT count(*) FROM vue_annuel_ann_part
 
id,select_type,TABLE,type,possible_keys,KEY,key_len,ref,rows,Extra
--------------------------------------------------------------------
1,SIMPLE,util_implantation_imp,INDEX,"PRIMARY,idx_imp_sct_code",idx_imp_sct_code,28,NULL,2353,"Using index"
1,SIMPLE,geog_secteur_sct,eq_ref,"PRIMARY,fk_sct_cmn",PRIMARY,27,db_pilotage_test.util_implantation_imp.imp_sct_code,1,
1,SIMPLE,geog_commune_cmn,eq_ref,"PRIMARY,fk_cmn_zon",PRIMARY,15,db_pilotage_test.geog_secteur_sct.sct_cmn_code,1,"Using where"
1,SIMPLE,geog_zone_zon,eq_ref,PRIMARY,PRIMARY,8,db_pilotage_test.geog_commune_cmn.cmn_zon_code,1,"Using index"
1,SIMPLE,annuel_ann,ref,idx_ann_fase_implantation,idx_ann_fase_implantation,4,db_pilotage_test.util_implantation_imp.imp_fase,1569,"Using index"

(3) Pour vue_annuel_ann_part2 :

Code :

1
2
3
4
5
6
7
8
9
10
 
EXPLAIN SELECT ann_niveau, count(*) FROM vue_annuel_ann_part2 GROUP BY ann_niveau
 
id,select_type,TABLE,type,possible_keys,KEY,key_len,ref,rows,Extra
--------------------------------------------------------------------
1,PRIMARY,annuel_ann,INDEX,NULL,idx_ann_niveau,3,NULL,794064,"Using where"
3,"DEPENDENT SUBQUERY",util_implantation_imp,eq_ref,"PRIMARY,idx_imp_sct_code",PRIMARY,4,func,1,"Using where"
3,"DEPENDENT SUBQUERY",geog_secteur_sct,eq_ref,"PRIMARY,fk_sct_cmn",PRIMARY,27,db_pilotage_test.util_implantation_imp.imp_sct_code,1,
3,"DEPENDENT SUBQUERY",geog_commune_cmn,eq_ref,"PRIMARY,fk_cmn_zon",PRIMARY,15,db_pilotage_test.geog_secteur_sct.sct_cmn_code,1,"Using where"
3,"DEPENDENT SUBQUERY",geog_zone_zon,eq_ref,PRIMARY,PRIMARY,8,db_pilotage_test.geog_commune_cmn.cmn_zon_code,1,"Using where; Using index"

Le (1) est logique.
Le (3) semble logique également : une sous-requête et utilisation de l'index.

C'est le (2) qui me pose problème comme je le disais.
On dirait que la vue est analysée dans un ordre différent selon la requête...

[johnalias110]

Et bien... après avoir épluché la doc et fait de nombreux tests, je viens de comprendre d'où venait mon problème.

En fait MySQL ne prend en considération qu'un seul index à la fois...

Je n'ai eu qu'à créer un index prenant l'ensemble des colonnes nécessaires.

Etant en train de migrer une base de données de PostgreSQL vers MySQL (par obligation), je ne suis pas habitué à ceci. PostgreSQL permet justement de combiner plusieurs index.

Suite à cela, plusieurs questions me viennent donc.
Tout d'abord, ai-je raison par rapport à mon affirmation précédente ?
Ensuite, à chaque fois que je crée une fonction, il va falloir que je vérifie les colonnes permettant un index. Une table dite "principale" sur laquelle des centaines de requêtes différentes peuvent être exécutées peut donc contenir un nombre d'index extrêmement important ?
De plus, il est impossible de déterminer l'ensemble des requêtes que les utilisateurs pourraient effectuer... Comment puis-je estimer cela ? En créant un grand nombre d'index directement ?

[CinePhil]

Envoyé par johnalias110

Et bien... après avoir épluché la doc et fait de nombreux tests, je viens de comprendre d'où venait mon problème.

En fait MySQL ne prend en considération qu'un seul index à la fois...

Où as-tu trouvé ça ?

Je savais que MySQL n'est pas un SGBD terrible mais là on touche le fond !

[skuatamad]

Envoyé par johnalias110

En fait MySQL ne prend en considération qu'un seul index à la fois...

Je n'ai eu qu'à créer un index prenant l'ensemble des colonnes nécessaires.

Etant en train de migrer une base de données de PostgreSQL vers MySQL (par obligation), je ne suis pas habitué à ceci. PostgreSQL permet justement de combiner plusieurs index.

Je ne suis pas très fan de mysql et je ne connais pas postgre mais ça me semble être un comportement normal...
L'index est une méthode d'accès à la table (en opposition au FULL SCAN) le SGBD choisit donc UN index pour accéder aux données... si un index disponnible sur une colonne n'est pas très pertinent alors qu'un index sur plusieurs colonnes l'est alors oui il faut créer un index sur plusieurs colonnes.

En quoi postrge proposait un meilleur plan ? (on parle bien de combiner plusieurs index d'une même table pour accéder uniquement aux données de cette table ?)

A ma connaissance seul les index bitmap sont combinables (en tout cas c'est vrai sur oracle) mais ce genre d'index n'est pas utilisable en environnment OLTP.

Envoyé par johnalias110

Une table dite "principale" sur laquelle des centaines de requêtes différentes peuvent être exécutées peut donc contenir un nombre d'index extrêmement important ?

C'est quoi extrèmement ? plusieurs bien sûr c'est normal...
Après c'est aussi un compromis avec les contraintes de l'appli en écriture (Insert, update, delete) mais plusieurs index est normal.

Envoyé par johnalias110

De plus, il est impossible de déterminer l'ensemble des requêtes que les utilisateurs pourraient effectuer... Comment puis-je estimer cela ? En créant un grand nombre d'index directement ?

Il faut avant tout optimiser les requêtes les plus fréquemment utilisées.

[johnalias110]

Envoyé par CinePhil

Où as-tu trouvé ça ?

Je savais que MySQL n'est pas un SGBD terrible mais là on touche le fond !

J'avais lu ça sur différents sites et trouvé ça dans la doc :

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
Si un index multi-colonne existe sur les colonnes col1 et col2, les lignes appropriées seront directement lues. Si des index séparés sur les colonnes col1 et col2 existent, l'optimiseur va essayer de trouver l'index le plus restrictif des deux, en décidant quel index débouche sur le moins de lignes possibles.

Mais j'en profite pour ajouter que depuis la version 5.0, MySQL a maintenant la possibilité de faire un merge entre deux index si on les utilise dans le WHERE et sous certaines conditions. Mon problème était qu'un index se trouvait dans la clause WHERE et l'autre dans la clause GROUP BY.
Du coup, ca ne marchait pas. Enfin bon...

Envoyé par skuatamad

C'est quoi extrèmement ? plusieurs bien sûr c'est normal...

Et bien, dans mon cas, la table que je présentais dans mon premier post est constitué de 17 colonnes. Sachant que les différentes requêtes présentent les informations sous forme d'indicateurs, n'importe quelle colonne peut être combinée avec n'importe quelle autre (et même plusieurs combinées ensemble), je me retrouve avec un nombre d'index possibles énorme...

Envoyé par skuatamad

Il faut avant tout optimiser les requêtes les plus fréquemment utilisées.

Maintenant, comme tu le dis, il faut que j'essaie d'optimiser les requêtes les plus fréquemment utilisées. Mais comme je ne sais pas quel indicateur intéresse les utilisateurs, je me dois d'optimiser l'ensemble des requêtes !
Enfin bon, j'ai au moins pu répondre à l'ensemble de mes questions.

Merci à vous.