[MySQL] Indexes et jointures : optimiser les perfs

Indexes et jointures : optimiser les perfs [MySQL] - SQL/NoSQL - Programmation

Marsh Posté le 28-12-2011 à 14:59:18    

Bonjour,
 
Je me casse la tête avec des requêtes de ce genre  :pt1cable:  
 

SELECT T2.zone_name,T1.t_hostname, sum(T1.summary_count) as nb FROM KB.equipment_list T3 JOIN REPORTING.fw_event_agg_hourly_summary T1 USE INDEX (idx_epoch,
idx_ghostname) on T3.equipment_db_name = T1.g_hostname JOIN sdw.net_assets_zones T2 on T1.t_zone_id = T2.zone_id WHERE T3.equipment_perimeter = "STC Admin"
AND T3.equipment_meta_type like '%fw%'
AND T1.summarization_id = '50263'
AND T1.epoch_hour <  unix_timestamp(NOW()) AND T1.epoch_hour >= unix_timestamp( DATE_SUB(NOW(),INTERVAL 24 HOUR) ) AND T1.nsm_type = "fw.auth.grant"
GROUP BY T2.zone_name, T1.t_hostname
ORDER BY nb desc


 
l'index idx_epoch regroupe les champs summarization, epoch_hour sur la table fw_event_agg_hourly_summary et l'indexe idx_ghostname ne contient que g_hostname
 
Si je prend l'indexe idx_ghostname, alors la jointure entre equipment_list et fw_event_agg_hourly_summary va se faire rapidement car il ne va selectionner que les bonnes lignes du bon équipement, mais par contre il devra executer les clauses "where" lentement car pas le bon index ...
 
par contre l'index idx_epoch va au contraire bien réduire le nombre de lignes de la seconde table grace à la clause where ... mais la jointure perdra du temps ensuite  :sweat:  
 
il est possible d'utiliser deux index ? quelqu'un a une idée pour optimiser cela ????  :jap:  

Reply

Marsh Posté le 28-12-2011 à 14:59:18   

Reply

Marsh Posté le 29-12-2011 à 07:56:00    

Aucun des deux indexes n'est suffisant, soit tu en crées un nouveau, soit tu utilises le moins pire des deux.
Il n'est pas possible d'en utiliser deux a la fois.
 
En fonction des statistiques (et donc du nombre d'enregistrement qui sont retourné par chaque condition) il est meme probable qu'il n'utilise aucun des deux indexes (si tu lui en donnes le choix).
 
A vue de nez il te faudrai un index sur epoch_hour, summarization_id, nsm_type. L'ordre depend des données, est-ce qu'il y a beaucoup de record par jour, est que le nsm_type ne retourne que quelques lignes, meme chose pour symmarization_id? L'ordre doit aller de la colonne la plus restrictive a la moins restrictive.
 
Il faut aussi que ton index soit couvrant, il font donc aussi inclure les colonnes g_hostname, t_zone_id, t_hostname et summary_count.


Message édité par Oliiii le 29-12-2011 à 07:59:41
Reply

Marsh Posté le 29-12-2011 à 09:06:54    

j'ai plusieurs millions de record par jours dans la base (entre 5 et 10 millions).
 
j'ai aussi un index qui utilise summarization_id, epoch_hour et nsm_type, cependant quand je fait un explain je voit qu'il utilise l'index sur summarization_id et epoch_hour. Je doit le forcer à utiliser l'autre ? :)
 
il n'y a aucun index qui ne me retourne que quelques records ... summarization_id et nsm_type ne sont pas très restrictifs ...  
 
quel est l'interet de couvrir toutes mes colonnes ? au final je vais me retrouver avec un index qui prend toutes les colonnes de ma table non ? summary_count est tres variable et si j'inclus toutes les colonnes cela reviens à créer un index identique à ma table ...  
 
De plus théoriquement je ne voit pas vraiment l'interet de mettre l'index sur t_zone_id et t_hostname non ? vu qu'il ne sont pas utilisés ni dans la jointure ni dans la clause where, ils ne seront pas utilisés par l'index ?

Reply

Marsh Posté le 29-12-2011 à 12:42:04    

Le principe d'un index couvrant est d'avoir les données supplementaire directement dans l'index et donc d'eviter des lookups.
 
La query en elle meme retourne +- combien de records?
 
Le truc c'est de trouver ce qui est tres restrictif et se concentrer la dessus.
Ca peut aussi etre une colonne d'une autre table.
 
Idéalement il faut eviter de forcer l'utilisation d'un index.
Si le query optimiser decide de ne pas l'utiliser c'est qu'il a une bonne raison. Forcer un index permet de voir ce qui manque quand on fait du debug.

Reply

Marsh Posté le 30-12-2011 à 13:09:12    

tu veut que je donne le nombre de record par sous-requetes ou le nombre de record retourné par la requête ?

Reply

Marsh Posté le 02-01-2012 à 07:54:05    

Par la requête :)

Reply

Marsh Posté le 02-01-2012 à 10:55:21    

T'as regardé avec la commande EXPLAIN de que faisait Mysql sur ta requête?


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 24-01-2012 à 12:00:35    

gugusg a écrit :

tu veut que je donne le nombre de record par sous-requetes ou le nombre de record retourné par la requête ?


Il fait un order by d'un champs calculé via le group by.
Il n'a aucune chance que sa requête speed s'il a plusieurs millions de lignes qui sont concernée par ce calcul (surtout si par malheur la mémoire allouée à MySQL est trop petite)
 
Le group by va de toutes façons imposé un index sur ce champs là (en principe, ça dépends de la taille de la table).
En tout cas c'est le genre de requête où il ne faut rien imposé à l'optimiseur, et où il faut s'assurer d'avoir des statistiques frais sur l'ensemble des tables/index/colonnes concernés.
 
Bref c'est pas évident comme problème.


---------------
| AMD Ryzen 7 7700X 8C/16T @ 4.5-5.4GHz - 64GB DDR5-6000 30-40-40 1T - AMD Radeon RX 7900 XTX 24GB @ 2680MHz/20Gbps |
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed