Pb UPDATE d'une table avec une autre table

**ericd69** · 05/02/2013, 16h56

soient les colonnes a,b,c,d

a clé primaire

posons un index k1(b) et un autre k2(c) et un dernier k3(b,c)

dans un index tu as les couples de valeurs (clé primaire, valeurs des colonnes indexées) pour retrouver sans avoir à lire la table les identifiants de lignes (clé primaires) qui correspondent au n-uplet de valeurs des colonnes indexées...

pour k1 qui est sur b, tu as donc par exemple:

a1 b1
a2 b3
...

pour un index composite comme k3:

a1 b1 c2
a2 b3 c2
...

mais un index composite à un ordre:
(b,c) n'est pas le même index que (c,b)
et pour que l'index composite soit choisi, la première colonne indexée doit être choisie...

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from matable where c=machin

le seul index possible est k2

donc poser des index composites dépend des recherches que tu fais pour voir si ça vaut le coup à toi de réfléchir en fonction des différentes requêtes sur cette table, des recouvrements de plages, etc...

un index à toujours un cout pour insert, update ou delete car il est reconstruit et sert pour accélérer les recherches, jointures, tris... et en taille:
(somme des tailles des colonnes indexées + taille clé primaire) x nombre de lignes
c'est donc un compromis...
d'où:

éviter les index textes
choisir, le plus possible, des clés primaires qui soient des colonnes numériques (entiers) avec la taille la plus adaptée

**Korben-Dallas** · 05/02/2013, 17h14

HA OK OK Bah là je comprends déjà beaucoup mieux.
Et ceci pourrait donc expliquer aussi pourquoi plusieurs de mes requêtes sont longues.
Ne sachant pas vraiment comment fonctionnent les index, je n'ai créé que des index sur une colonne, là je vais donc tous les revoir afin qu'ils pointent vers 1, 2 ou 3 colonnes en fonction de ce qu'ils ont à faire.
Merci

**Korben-Dallas** · 05/02/2013, 17h24

Mais si je mets des index doubles comme ici, en intégrant "Début" et Fin" dans le même index.
Que se passe-t-il si je ne fais une recherche que sur "Début" par exemple ??
Le comportement est toujours identique ? Ce sera tous les "Début" sans se préoccuper de "Fin" ? ou bien je rencontrerai quelques pb ?
Et inversement en ne faisant des recherches que sur "Fin"

**Korben-Dallas** · 05/02/2013, 18h21

J'ai une table que je viens de modifier afin d'y mettre les doubles-index,
lorsque j'exécute les requêtes, importe du CSV plus premiers traitements ça met environ 4 à 5 min (long mais tolérable)
Par contre lorsque la requête UPDATE de ce sujet se lance alors là ce n'est plus pareil, ça fait plus de 30min que le script est lancé et ce n'est pas encore fini alors que derrière cet update je dois faire un INSERT INTO TableDefinitive

Là c'est quand même galère ce long délai et je ne vois pas comment diminuer ce délai qui est vraiment important.
Cette fois, Table1 contient 670000 lignes et Table 2 toujours 32000 lignes

Je pense vraiment que pour chacun des 67000 lignes de la table1, il va parcourir les 32000 lignes de la table2 ce qui fait bcp de traitement inutile là du coup. :-(
Et en plus ce n'est pas un BETWEEN mais un simple "="

**ericd69** · 05/02/2013, 18h54

sur un index double tant que tu cherche au moins sur la première colonne il est pris

tu parles de tests sur la version ip en caractères ou nombres?

**Korben-Dallas** · 05/02/2013, 19h00

Dans mes tables j'ai les colonnes suivantes :
- Adresses IP (caractère)
- Adresses IP Traitees (nombre)

Comme ça je fais le traitement lors de l'injection des données
Ensuite les requêtes pour l'affichage sont du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT BlaBla, BloBlo, BleBle, AdresseIP
FROM MaTable1 INNER JOIN MaTable2 ON MaTable1.AdressesIP_Traitees=MaTable2.AdresseIP_Traitees

Ceci est un exemple mais le principe est là.

**ericd69** · 05/02/2013, 19h36

le traitement de l'update s'est amélioré non?

**Korben-Dallas** · 05/02/2013, 19h37

Punaise, je viens de trouver la coquille qui me posait problème.
En parcourant toutes mes tables de ma base de données, j'ai pu remarquer une table qui avait un index mal fait, celui-ci pointait correctement vers les bonnes colonnes mais les colonnes concernées étaient en VARCHAR(20) donc effectuer des comparaisons entre un INT(11) d'une table avec un VARCHAR(20) de l'autre, il est clair que ça pose des pb.
J'ai donc tout mis en INT(11) UNSIGNED et là le script complet met moins de 5 min au lieu de l'abandon au bout d'une heure pfiou !!
Merci pour votre aide et surtout pour les explications sur les index qui m'ont été très utiles et bénéfiques.

**ericd69** · 05/02/2013, 19h50

je suis pas sur que ça vaille le coup de trainer les ip en varchar...

tu n'afficheras jamais plus d'une centaine à la fois... la conversion sur peu de lignes est donc préférable... non?

c'est comme tout... moins tu brasses de données, moins tu bufferises, plus tu traites de données ou rapidement...

tu importes ton csv comment?

où est passé l'ingénieur système/réseau? pense simple, efficace, compact!!!

**Korben-Dallas** · 05/02/2013, 20h02

LOL En fait pour le moment il faut que cet outil fonctionne, même si c'est à la warrior mais je suis sur l'étude d'une nouvelle version de cet outil (qui a énormément évolué en fonctionnalité depuis sa création au détriment des performances).
Il faut que je repense entièrement et ce n'est pas une mince à faire, surtout que c'est en plus du boulot à faire, tout ça pour que les tech sur place puisse avoir les données leurs permettant de corriger leurs serveurs.
Mais j'y travaille sur cette évolution. Et juste pour info, certes normalement je n'ai pas bcp d'IP à ressortir mais hélas actuellement je peux avoir jusqu'à plusieurs milliers d'IP qui ressortent en fonction des demandes.
Sinon pour te répondre l'import CSV se fait via script à l'aide de LOAD DATA INFILE ou bien via la console de supervision et là c'est un php qui s'occupe de l'import mais toujours avec LOAD DATA INFILE

**ericd69** · 05/02/2013, 20h30

regarde du coté des buffers qui gérent les échanges mysql-php de chaque coté
ça peut aider...

sinon active le la surveillance des requêtes lentes avec le slow query log

passes les au grill avec explain si c'est des select... ou fais un select équivalent comme j'ai fait avec ton update... pour voir...

tu peux aussi voir les différent buffers (index, données, tris) si le réglage est sensé être accessible... mais ne mets jamais des valeur au dessus de 60% de la valeur maxi sinon tu commences à reperdre...

**Korben-Dallas** · 06/02/2013, 11h23

ouh là !!
Ça commence à être super complexe pour moi là.
Faire des explain OK mais je ne sais pas les interpréter
La conf du cache ? Je vois bien où et comment on fait ça mais je ne sais pas non plus quelle valeur indiquer.
A part faire des requêtes...

**ericd69** · 06/02/2013, 17h18

les explain ce qui compte c'est de voir pour une requête lente si les index sont utilisés:

possible _keys non nul
key non nul
key_len non nul
extra doit t'en parler aussi

si c'est un truc qui sera déployé et que tu peux pas toucher à la conf ça sert à rien d'y toucher...

par contre, active le slow query log pour identifier les requêtes lentes...

**Korben-Dallas** · 06/02/2013, 17h49

J'ai plusieurs requêtes lentes et ce ne sont majoritairement que des UPDATE ou de INSERT.
Donc pas possible de faire un EXPLAIN dessus.
Par contre j'ai fait

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
EXPLAIN SELECT
     Champ1,
     Champ2,
     Champ3,
     Champ4,
     ...,
     ...,
     Champ20,
     Champ21
FROM MaTable

C'est donc une requêt extrêmement simple.
En voici le résultat

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
     id     |     select_type     |     table     |    type     |     possible_keys     |     key     |     key_len     |     ref     |     rows     |     Extra     |
     1      |     SIMPLE           |   MaTable   |    ALL      |     (NULL)              |    (NULL)  |     (NULL)      |    (NULL) |   672063    |                 |

Je ne comprends pas pourquoi un simple SELECT renvoi ceci.
Sachant que c'est le SELECT situé dans un INSERT INTO
Mais je tente de voir pour d'autres requêtes qui elles s'occupent de la récupération des infos dans la BDD et non leur traitement à l'injection des dites données dans la BDD.

**CinePhil** · 06/02/2013, 17h53

Comme il n'y a ni WHERE ni jointure ni groupage dans la requête, elle va retourner toutes les lignes de la table donc forcément la lire entièrement. Normal qu'elle n'utilise pas d'index !

**Korben-Dallas** · 06/02/2013, 18h15

OK C'est ce que je pensais aussi
Et du coup là j'ai une grosse requête gourmande

voici le résultat EXPLAIN
Que faire comme optimisation ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
|| *id* || *select_type* || *table* || *type* || *possible_keys* || *key* || *key_len* || *ref* || *rows* || *Extra* ||
|| 1 || SIMPLE || DNS || ALL ||  ||  ||  ||  || 1580 || Using temporary; Using filesort ||
|| 1 || SIMPLE || Clients || ref || AdresseIP || AdresseIP || 22 || db_MaBase.DNS.AdresseIP || 1 ||  ||
|| 1 || SIMPLE || Ref || ref || Serveur || Serveur || 22 || db_MaBase.Clients.Serveur || 1 ||  ||
|| 1 || SIMPLE || OUS || ALL ||  ||  ||  ||  || 16846 || Using where; Using join buffer ||

**ericd69** · 06/02/2013, 18h17

montre la nous... car juste l'explain....

elle n'utilise pas les index possible c'est tout ce qu'on peu en dire

**Korben-Dallas** · 06/02/2013, 18h24

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
EXPLAIN SELECT DISTINCT
    Serveur.Gestionnaire AS 'Gestionnaire',
    Serveur.Serveur AS 'Serveur',
    Serveur.AdresseDebutPlage AS 'AdresseDebutPlage_Serveur',
    Serveur.AdresseFinPlage AS 'AdresseFinPlage_Serveur',
    Serveur.AdresseDNS AS 'AdresseDNS_Serveur',
    DNS.AdresseIP AS 'AdresseIP_DNS',
    IF( DNS.AdresseIP LIKE 'MonIP', 'NATIONAL', Clients.Ordinateur ) AS 'Ordinateur_Clients',
    Clients.EtatConnexion,
    Clients.EtatClient,
    IF( DNS.AdresseIP LIKE 'MonIP', 'NATIONAL', Clients.Domaine ),
    IF( DNS.AdresseIP LIKE 'MonIP', 'NATIONAL', Ref.Gestionnaire ) AS 'Gestionnaire_Ref',
    IF( DNS.AdresseIP LIKE 'MonIP', 'NATIONAL', Ref.NomDNSServeur )
  FROM
    t_Global_Serveurs AS Serveur
    LEFT JOIN t_DNS_UA AS DNS ON Serveur.AdresseDNS LIKE CONCAT('http://', DNS.DNS, '.MonDomaine1:MonPort1/Supplement%')
    LEFT JOIN t_Global_Clients AS Clients ON DNS.AdresseIP=Clients.AdresseIP
    LEFT JOIN t_References AS Ref ON Clients.Serveur=Ref.NomServeur
  WHERE
        Serveur.AdresseDebutPlage NOT LIKE 'Update%'
    AND Serveur.AdresseDNS NOT LIKE CONCAT('http://', DNS.DNS, '.MonDomaine2:MonPort2%')
  ORDER BY
        Serveur.Gestionnaire,
        Serveur.Serveur,
        Serveur.AdresseDNS,
        Ref.Gestionnaire,
        Ref.NomDNSServeur,
        DNS.DNS;

**ericd69** · 06/02/2013, 18h28

like ou not like n'utilise pas les index il me semble...

des adresses serveur avec update dedans...

**Korben-Dallas** · 06/02/2013, 18h32

En effet, c'est une coquille qu'il faudrait que je supprime lors de l'injection des différents CSV et non ensuite lors de l'affichage, bonne remarque, je vais donc corriger ceci.
Quand au like ou not like, comment faire du coup ?

Pb UPDATE d'une table avec une autre table

Requêtes MySQL

Discussions similaires

Partager

Partager