Discussion :

[GyZmoO]

Bonjour à tous !

Nous avons un petit soucis que nous ne comprenons pas forcément.

Je vous explique :

Voici une table de notre base MySQL, contenant 70 000 000 de lignes dont la structure ressemble à ça :

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
 
MA_TABLE
{
   PRI_ID         INTEGER NOT NULL,
   MY_DATE     TIMESTAMP NOT NULL,
   d'autres champs
};
 
 
ALTER TABLE MA_TABLE
       ADD  ( PRIMARY KEY (PRI_ID) ) ;
 
ALTER TABLE MA_TABLE MODIFY COLUMN PRI_ID INTEGER NOT NULL AUTO_INCREMENT;
 
CREATE INDEX MY_INDEX ON MA_TABLE
(
       MY_DATE                      ASC
);

Bref, nous faisons une requête du style

Code :

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

1
2
 
SELECT tous_mes_champs FROM MA_TABLE WHERE MY_DATE BETWEEN DATE1 AND DATE2

Cette requête répond en 0,5 secondes au maximum. Ils y'a à peu près 800 000 lignes dans la réponse. (avec un explain, on voit qu'il utilise bien l'index.)

Maintenant, on fait un DELETE sur cette table dans le même intervalle de temps : ça fait 2600 secondes que le delete est parti, il n'est toujours pas terminé...

Pensez vous que cela soit normal??

Je précise :

- moteur de la table innodb
- cette table référence 4 tables au moyen de foreign keys, mais n'est référencée par personne.

D'avance merci pour vos tuyaux

[SQLpro]

1) donnez la liste intégrale des "autres colonnes" et non pas champs !
2) donnez la liste intégrale des contraintes et index

Sans cela il est impossible de vous aider !

A +

[CinePhil]

Je pense qu'au cours du DELETE, il met à jour l'index en même temps, ce qui est lourd avec 70 millions de lignes.

Essaie de désactiver les index avant de faire le DELETE puis de reconstruire l'index après :

Code :

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

1
2
3
4
5
6
7
8
ALTER TABLE MaTable
DISABLE KEYS;
 
DELETE FROM MaTable
WHERE ... ;
 
ALTER TABLE MaTable
ENABLE KEYS;

Je crois que SQLPro a parlé d'une chose similaire dans un article sur l'indexation à propos d'INSERT massifs.

[GyZmoO]

Bonjour et merci pour vos réponses.

Voici la structure totale :

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
CREATE TABLE MA_TABLE (
       PRI_ID          INTEGER NOT NULL,
       MY_DATE TIMESTAMP NOT NULL,
       MY_VALUE       DOUBLE NOT NULL,
       MY_STATUS      TINYINT NOT NULL,
       MONPAR_ID          INTEGER NOT NULL,
       CHKCOND_ID         INTEGER NOT NULL,
       SITE_NAME          VARCHAR(40) NULL,
       CHAIN_NAME         VARCHAR(40) NULL,
       MEAS_ANT_NAME      VARCHAR(40) NULL,
       RF_POLAR         TINYINT NULL,
       RF_TYPE			  TINYINT NULL,
       MY_RESTORATION_DATE		TIMESTAMP NULL,
       MY_RESTORATION_USE		BOOLEAN NOT NULL DEFAULT FALSE
) TYPE=INNODB;
 
--
-- Foreign keys
--
 
ALTER TABLE MY_TABLE
       ADD CONSTRAINT fk_my1 FOREIGN KEY (SITE_NAME, MEAS_ANT_NAME, RF_POLAR, RF_TYPE)
                             REFERENCES T_PATH(SITE_NAME, MEAS_ANT_NAME, RF_POLAR, RF_TYPE) ;
 
ALTER TABLE MY_TABLE
       ADD CONSTRAINT fk_my2 FOREIGN KEY (SITE_NAME, CHAIN_NAME)
                             REFERENCES T_CHAIN(SITE_NAME, CHAIN_NAME) ;
 
 
ALTER TABLE MY_TABLE
       ADD CONSTRAINT fk_my3 FOREIGN KEY (CHKCOND_ID)
                             REFERENCES T_CONDITION(CHKCOND_ID) ;
 
 
ALTER TABLE MY_TABLE
       ADD CONSTRAINT fk_my4 FOREIGN KEY (MONPAR_ID)
                             REFERENCES T_PARAMETER(MONPAR_ID) ;
 
--
-- End foreign keys
--
 
--
-- Primary key auto increment
--
ALTER TABLE MY_TABLE MODIFY COLUMN PRI_ID INTEGER NOT NULL AUTO_INCREMENT;
 
--
-- End primary key auto increment
--
 
--
-- Index on MY_TABLE
--
 
CREATE INDEX IND_MY_TABLE_1 ON MY_TABLE
(
       MY_DATE                      ASC
);
 
CREATE INDEX IND_MY_TABLE_2 ON MY_TABLE
(
       MONPAR_ID                      ASC,
       MY_DATE            ASC
);
 
--
-- End index on MY_TABLE
--

@SQLPro : voila pour la totalité des contraintes/index sur ma table .

@CinePhil : On pensait cela également. Cependant, le fait de désactiver les contraintes puis de les reconstruire sera plus rapide?? Merci pour l'article, je vais le lire pour voir si je peux en tirer qq chose !

[ionesco]

Supprimer les index sur une table pour faire un delete puis les remettre après n'est pas forcément une bonne chose dans le sens où pendant ce temps des requêtes de type SELECT peuvent s'effectuer sur la dite table et ne pourrons jouir des indexes, ce qui peut les rendre très longues.

Ou bien me trompe-je?

[CinePhil]

Code :

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

1
2
3
       SITE_NAME          VARCHAR(40) NULL,
       CHAIN_NAME         VARCHAR(40) NULL,
       MEAS_ANT_NAME      VARCHAR(40) NULL,

Code :

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

1
2
3
4
5
6
7
ALTER TABLE MY_TABLE
       ADD CONSTRAINT fk_my1 FOREIGN KEY (SITE_NAME, MEAS_ANT_NAME, RF_POLAR, RF_TYPE)
                             REFERENCES T_PATH(SITE_NAME, MEAS_ANT_NAME, RF_POLAR, RF_TYPE) ;
 
ALTER TABLE MY_TABLE
       ADD CONSTRAINT fk_my2 FOREIGN KEY (SITE_NAME, CHAIN_NAME)
                             REFERENCES T_CHAIN(SITE_NAME, CHAIN_NAME) ;

C'est quoi ces clés étrangères multiples sur des VARCHAR ?
Pas du tout optimisé pour une table de 70 millions de lignes !

Et les colonnes clé étrangères ne sont pas indexées !

Je crois que la lecture des articles de SQLPro sur l'indexation et le reste de son blog seront fort utiles !

Supprimer les index sur une table pour faire un delete puis les remettre après n'est pas forcément une bonne chose dans le sens où pendant ce temps des requêtes de type SELECT peuvent s'effectuer sur la dite table et ne pourrons jouir des indexes, ce qui peut les rendre très longues.

Ou bien me trompe-je?

Vu l'opération de DELETE qui est indiquée au premier message, j'ai plutôt l'impression qu'il s'agit d'une opération de maintenance à faire une fois, de préférence hors période d'exploitation de la table, préférenciellement en bloquant son utilisation durant l'opération, ce qui est je crois implicite pendant le DELETE mais qui doit je pense être explicitement programmé avec l'enchaînement des trois requêtes que j'ai proposées.

[ionesco]

En effet dans le cadre d'une maintenance qui n'a pas lieu trop souvent, cela est fort recommander de droper les index puis les régénérer à la fin de notre opération.

[GyZmoO]

Re.

- Quand on déclare une FK, les colonnes correspondantes ne sont pas forcément indexées??!! !! ??

- Sinon, en fait, la structure de la BD est vieille (c'est un vieux projet qui est malheureusement en exploitation), nous avons prévu de faire une refonte globale de la structure, mais là on essaye de faire des petites modifs pour optimiser un peu ... (Le client n'est pas content...)

- De plus, encore malheureusement, cette requête DELETE doit pouvoir se faire en exploitation sans gêner les opérateurs... Du coup supprimer les index puis les remettre serait acceptable si le temps du DELETE était vraiment faible (de l'ordre de qqs minutes par exemple...)

- Enfin, pourquoi est ce une mauvaise chose d'avoir des foreign key multiples sur des colonnes varchar?? Je veux bien comprendre que pour un index c'est pas terrible(si j'ai bien saisi, plus un index est petit, mieux c'est) mais pour une clé étrangère (si elles ne sont pas indexées...) ?? Le problème, c'est que pour le moment nous n'avons pas la possibilité de changer la structure des foreign key (et donc des primary keys associées) pour les convertir en "Integer auto increment"... C'est un peu la folie !

Encore merci pour vos réponses.

[laraki.fissel]

Bonjour

je rebondis sur le sujet, j'ai le même problème avec une table de 1 500 000 lignes.
un index est utilisé bien pour la requete SELECT mais pas pour le DELETE.

explain select * from facture where annee='2016' and mois='07';

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------------+------------+------+---------------+-----------+---------+-------------+--------+----------+-------+
| 1 | SIMPLE | facture | NULL | ref | annMois | annMois | 8 | const,const | 278218 | 100.00 | NULL |

et

explain delete from facture where annee='2016' and mois='07';
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | DELETE | facture | NULL | ALL | annMois | NULL | NULL | NULL | 1456213 | 100.00 | Using where |

Merci pour votre aide

[Artemus24]

Salut SQLPRO.

Je ne sais pas si vous avez remarqué, mais vous répondez à Oishiiii, sur un message datant du 11 novembre 2009 ???

Pour MySQL cela dépend de la version puisque, il n'y a pas si longtemps les FK n'était pas active (on pouvait les créer, mais cela ne servait a rien !!!)

Si MySql a créé une foreign Key et qu'elle n'est pas active, comment accéder à la table mère ? Je ne comprends pas votre réponse.
Pourquoi MySql aurait mis en oeuvre cette fonctionnalité si on ne peut pas l'utiliser ?

Et de quelle version MySql parlez-vous ? Bien sûr, je parle bien sûr de la version où vous avez rencontré ce problème.

Depuis une certaine version c'est effectivement ce qu'il fait mais c'est parfaitement imbécile dans un certain nombre de cas de figure...

MySql fait quoi ? Pouvez-vous détailler votre affirmation ?
Vous dites que c'est imbécile, je veux bien, mais il serait bien de détailler votre réponse afin de mieux vous comprendre.

Pour SQL Server cela n'a jamais été vrai

Qu'est-ce qui n'est pas vrai ? Je ne comprends votre affirmation. Essayez d'être plus clair dans vos propos.

Selon vous, MySql crée un index sur une foreign key ou pas ? Et sur Microsoft SQL Server est-ce aussi le cas ?

Un index sur une foreign key sert que dans un seul cas, celui où l'on accès à la table fille en partant de la table mère.
Or le cas général, consiste plutôt à partir de la table fille pour accéder à la table mère.
Et donc la foreign key est largement suffisante pour ce type d'accès, et l'index sur celle-ci ne sert strictement à rien.
Dois-je comprendre que votre NON signifie l'inutilité de l'index sur une foreign key ? Est-ce bien cela ?

Par contre, je ne comprends pas trop pourquoi MySql crée un index sur une foreign key ?
Je tiens tout de même à préciser que je n'ai pas créé cet index !
Voici un cas réel sur la version MySql 5.7.18. J'ai créé une foreign key sur la table fils qui pointe sur la table père. Et voici la suppression de la foreign key :

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
--------------
SHOW INDEX FROM   `fils`
--------------
 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fils  |          0 | PRIMARY  |            1 | fils_id     | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| fils  |          1 | FK_01    |            1 | pere_id     | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
--------------
DROP INDEX `FK_01` on `fils`
--------------
 
ERROR 1553 (HY000) at line 5: Cannot drop index 'FK_01': needed in a foreign key constraint
--------------
commit
--------------
 
--------------
SHOW INDEX FROM   `fils`
--------------
 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fils  |          0 | PRIMARY  |            1 | fils_id     | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| fils  |          1 | FK_01    |            1 | pere_id     | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
--------------
ALTER TABLE `fils` DROP FOREIGN KEY `FK_01`
--------------
 
--------------
commit
--------------
 
--------------
SHOW INDEX FROM   `fils`
--------------
 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fils  |          0 | PRIMARY  |            1 | fils_id     | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| fils  |          1 | FK_01    |            1 | pere_id     | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
--------------
DROP INDEX `FK_01` on `fils`
--------------
 
--------------
commit
--------------
 
--------------
SHOW INDEX FROM   `fils`
--------------
 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fils  |          0 | PRIMARY  |            1 | fils_id     | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
Appuyez sur une touche pour continuer...

On remarque que supprimer en premier l'index ne fonctionne pas.
Il faut en premier supprimer la foreign key pour pourvoir par la suite supprimer l'index.

Donc Oishiiii avait bien raison et ne mérite pas ce -1.

@ laraki.fissel : Quel est l'intérêt de déterrer un sujet datant de 2009 ???

@+