Choix d'index par l'optimiseur

**laraki.fissel** · 21/01/2017, 23h27

Bonjour tout le monde
Je vous explique le contexte, j'ai deux tables volumineuses, indiv avec 417811 lignes et pap avec 1387661
j'ai ajouté un index sur les deux tables pour couvrir la jointure, idx_MATRICULE_CLIENT (NUM_MATRICULE,CODE_CLIENT),(les deux champs ne sont pas des clefs primaires), leur type est varchar(20), et sur la table pap, ces deux champs contiennent parfois la valeur vide et pas NULL.
J’exécute la requête ci-dessous (39s pour avoir tous les résultats) et je ne comprends pas pourquoi l'optimiseur Mysql ne prend pas en compte mon index idx_MATRICULE_CLIENT:

voici la requete :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT 
  pap.NOM_SALARIE 
FROM
  pap 
  LEFT JOIN  indiv 
  ON (
    indiv.MATRICULE = pap.NUM_MATRICULE
    AND indiv.CODE_CLIENT = pap.CODE_CLIENT
  ) 
WHERE     
      pap.CODE_DOCUMENT IN ('G350010')

voici le resultat de mon explain :

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
 
   id: 1
  select_type: SIMPLE
        TABLE: indexfiche_paperless
         TYPE: ref
possible_keys: ID_CODE_DOCUMENT,idx_indexfiche_paperless_search
          KEY: ID_CODE_DOCUMENT
      key_len: 12
          ref: const
         ROWS: 662175
        Extra: USING INDEX CONDITION; USING TEMPORARY
*************************** 2. ROW ***************************
           id: 1
  select_type: SIMPLE
        TABLE: idx_indiv
         TYPE: ref
possible_keys: ID_CODE_CLIENT,ID_NUM_MATRICULE_2,ID_NUM_MATRICULE
          KEY: ID_NUM_MATRICULE_2
      key_len: 12
          ref: bvrh_000041.indexfiche_paperless.ID_NUM_MATRICULE
         ROWS: 1
        Extra: USING WHERE; DISTINCT
2 ROWS IN SET (0.00 sec)

status des handler :

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
 
+----------------------------+---------+
| Variable_name              | VALUE   |
+----------------------------+---------+
| Handler_commit             | 1       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_external_lock      | 0       |
| Handler_icp_attempts       | 1385120 |
| Handler_icp_match          | 1385120 |
| Handler_mrr_init           | 0       |
| Handler_mrr_key_refills    | 0       |
| Handler_mrr_rowid_refills  | 0       |
| Handler_prepare            | 0       |
| Handler_read_first         | 0       |
| Handler_read_key           | 1385121 |
| Handler_read_last          | 0       |
| Handler_read_next          | 1419014 |
| Handler_read_prev          | 0       |
| Handler_read_retry         | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_deleted   | 0       |
| Handler_read_rnd_next      | 129458  |
| Handler_rollback           | 0       |
| Handler_savepoint          | 0       |
| Handler_savepoint_rollback | 0       |
| Handler_tmp_update         | 0       |
| Handler_tmp_write          | 1443803 |
| Handler_update             | 0       |
| Handler_write              | 0       |
+----------------------------+---------+

Merci bcp pour votre aide.

**Artemus24** · 22/01/2017, 01h16

Salut laraki.fissel.

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `indiv`
--------------
 
--------------
CREATE TABLE `indiv`
( `id`             integer unsigned  not null auto_increment primary key,
  `matricule`         char(05)       not null,
  `code_client`       char(07)       not null,
  index `IDX_1` (`matricule`,`code_client`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `indiv` (`matricule`,`code_client`) values
  ('12345','zulu'),('23456','tango'),('34567','charlie'),('45678','bravo'),('56789','golf'),('67890','sierra')
--------------
 
--------------
select * from indiv
--------------
 
+----+-----------+-------------+
| id | matricule | code_client |
+----+-----------+-------------+
|  1 | 12345     | zulu        |
|  2 | 23456     | tango       |
|  3 | 34567     | charlie     |
|  4 | 45678     | bravo       |
|  5 | 56789     | golf        |
|  6 | 67890     | sierra      |
+----+-----------+-------------+
--------------
DROP TABLE IF EXISTS `pap`
--------------
 
--------------
CREATE TABLE `pap`
( `id`             integer unsigned  not null auto_increment primary key,
  `num_matricule`     char(05)       not null,
  `code_client`       char(07)       not null,
  `code_document`     char(07)       not null,
  `nom_salarie`    varchar(255)      not null,
  index `IDX_2` (`num_matricule`,`code_client`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `pap` (`num_matricule`,`code_client`,`nom_salarie`,`code_document`) values
  ('12345', 'zulu',    'alphonse daudet',           'G270033'),
  ('23456', 'tango',   'marcel amont',              'G270033'),
  ('34567', 'charlie', 'gaston leroux',             'G350010'),
  ('45678', 'bravo',   'Maurice Leblanc',           'G350010'),
  ('56789', 'golf',    ' Pierre Ponson du Terrail', 'G450077'),
  ('67890', 'sierra',  'jules verne',               'G450077')
--------------
 
--------------
select * from pap
--------------
 
+----+---------------+-------------+---------------+---------------------------+
| id | num_matricule | code_client | code_document | nom_salarie               |
+----+---------------+-------------+---------------+---------------------------+
|  1 | 12345         | zulu        | G270033       | alphonse daudet           |
|  2 | 23456         | tango       | G270033       | marcel amont              |
|  3 | 34567         | charlie     | G350010       | gaston leroux             |
|  4 | 45678         | bravo       | G350010       | Maurice Leblanc           |
|  5 | 56789         | golf        | G450077       |  Pierre Ponson du Terrail |
|  6 | 67890         | sierra      | G450077       | jules verne               |
+----+---------------+-------------+---------------+---------------------------+
--------------
SELECT DISTINCT  *
           FROM  pap
LEFT outer JOIN  indiv
             ON  indiv.MATRICULE     = pap.NUM_MATRICULE
            AND  indiv.CODE_CLIENT   = pap.CODE_CLIENT
          WHERE    pap.CODE_DOCUMENT = 'G350010'
--------------
 
+----+---------------+-------------+---------------+-----------------+------+-----------+-------------+
| id | num_matricule | code_client | code_document | nom_salarie     | id   | matricule | code_client |
+----+---------------+-------------+---------------+-----------------+------+-----------+-------------+
|  3 | 34567         | charlie     | G350010       | gaston leroux   |    3 | 34567     | charlie     |
|  4 | 45678         | bravo       | G350010       | Maurice Leblanc |    4 | 45678     | bravo       |
+----+---------------+-------------+---------------+-----------------+------+-----------+-------------+
--------------
explain
SELECT DISTINCT  *
           FROM  pap
LEFT outer JOIN  indiv
             ON  indiv.MATRICULE     = pap.NUM_MATRICULE
            AND  indiv.CODE_CLIENT   = pap.CODE_CLIENT
          WHERE    pap.CODE_DOCUMENT = 'G350010'
--------------
 
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------------------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref                                         | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------------------------------+------+----------+------------------------------+
|  1 | SIMPLE      | pap   | NULL       | ALL  | NULL          | NULL  | NULL    | NULL                                        |    6 |    16.67 | Using where; Using temporary |
|  1 | SIMPLE      | indiv | NULL       | ref  | IDX_1         | IDX_1 | 12      | base.pap.num_matricule,base.pap.code_client |    1 |   100.00 | Using index                  |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------------------------------+------+----------+------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**laraki.fissel** · 22/01/2017, 01h35

Merci Artemus24 d'avoir réagi au sujet,
Je vois que vous avez décrit le contexte mieux que moi,

J’ai deux question :
Pourquoi l'optimiseur n'a pas choisi l'index pour la première ligne d'Explain et a préféré de scanner toute la table (6lignes)

En cas de plusieurs millions de lignes :
Vous croyez que l'optimiseur réagira de la même façon, sachant que l'optimiseur réagit en fonction de nombre d'enregistrement, d'index et d’autres informations.

Merci encore pour votre aide.

**Artemus24** · 22/01/2017, 19h26

Salut laraki.fissel.

Je me suis concentré que sur votre problème d'index (NUM_MATRICULE,CODE_CLIENT).
L'autre index (CODE_DOCUMENT) est traité par ce que j'ai ajouté ci-après (IDX_3) :

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `indiv`
--------------
 
--------------
CREATE TABLE `indiv`
( `id`             integer unsigned  not null auto_increment primary key,
  `matricule`         char(05)       not null,
  `code_client`       char(07)       not null,
  index `IDX_1` (`matricule`,`code_client`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `indiv` (`matricule`,`code_client`) values
  ('12345','zulu'),('23456','tango'),('34567','charlie'),('45678','bravo'),('56789','golf'),('67890','sierra')
--------------
 
--------------
select * from indiv
--------------
 
+----+-----------+-------------+
| id | matricule | code_client |
+----+-----------+-------------+
|  1 | 12345     | zulu        |
|  2 | 23456     | tango       |
|  3 | 34567     | charlie     |
|  4 | 45678     | bravo       |
|  5 | 56789     | golf        |
|  6 | 67890     | sierra      |
+----+-----------+-------------+
--------------
DROP TABLE IF EXISTS `pap`
--------------
 
--------------
CREATE TABLE `pap`
( `id`             integer unsigned  not null auto_increment primary key,
  `num_matricule`     char(05)       not null,
  `code_client`       char(07)       not null,
  `code_document`     char(07)       not null,
  `nom_salarie`    varchar(255)      not null,
  index `IDX_2` (`num_matricule`,`code_client`),
  index `IDX_3` (`code_document`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `pap` (`num_matricule`,`code_client`,`nom_salarie`,`code_document`) values
  ('12345', 'zulu',    'alphonse daudet',           'G270033'),
  ('23456', 'tango',   'marcel amont',              'G270033'),
  ('34567', 'charlie', 'gaston leroux',             'G350010'),
  ('45678', 'bravo',   'Maurice Leblanc',           'G350010'),
  ('56789', 'golf',    ' Pierre Ponson du Terrail', 'G450077'),
  ('67890', 'sierra',  'jules verne',               'G450077')
--------------
 
--------------
select * from pap
--------------
 
+----+---------------+-------------+---------------+---------------------------+
| id | num_matricule | code_client | code_document | nom_salarie               |
+----+---------------+-------------+---------------+---------------------------+
|  1 | 12345         | zulu        | G270033       | alphonse daudet           |
|  2 | 23456         | tango       | G270033       | marcel amont              |
|  3 | 34567         | charlie     | G350010       | gaston leroux             |
|  4 | 45678         | bravo       | G350010       | Maurice Leblanc           |
|  5 | 56789         | golf        | G450077       |  Pierre Ponson du Terrail |
|  6 | 67890         | sierra      | G450077       | jules verne               |
+----+---------------+-------------+---------------+---------------------------+
--------------
SELECT DISTINCT  *
           FROM  pap
LEFT outer JOIN  indiv
             ON  indiv.MATRICULE     = pap.NUM_MATRICULE
            AND  indiv.CODE_CLIENT   = pap.CODE_CLIENT
          WHERE    pap.CODE_DOCUMENT = 'G350010'
--------------
 
+----+---------------+-------------+---------------+-----------------+------+-----------+-------------+
| id | num_matricule | code_client | code_document | nom_salarie     | id   | matricule | code_client |
+----+---------------+-------------+---------------+-----------------+------+-----------+-------------+
|  3 | 34567         | charlie     | G350010       | gaston leroux   |    3 | 34567     | charlie     |
|  4 | 45678         | bravo       | G350010       | Maurice Leblanc |    4 | 45678     | bravo       |
+----+---------------+-------------+---------------+-----------------+------+-----------+-------------+
--------------
explain
SELECT DISTINCT  *
           FROM  pap
LEFT outer JOIN  indiv
             ON  indiv.MATRICULE     = pap.NUM_MATRICULE
            AND  indiv.CODE_CLIENT   = pap.CODE_CLIENT
          WHERE    pap.CODE_DOCUMENT = 'G350010'
--------------
 
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------------------------------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref                                         | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------------------------------+------+----------+-----------------+
|  1 | SIMPLE      | pap   | NULL       | ref  | IDX_3         | IDX_3 | 7       | const                                       |    2 |   100.00 | Using temporary |
|  1 | SIMPLE      | indiv | NULL       | ref  | IDX_1         | IDX_1 | 12      | base.pap.num_matricule,base.pap.code_client |    1 |   100.00 | Using index     |
+----+-------------+-------+------------+------+---------------+-------+---------+---------------------------------------------+------+----------+-----------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Sinon, il serait bon de revoir la modélisation de votre base de donnée, ainsi que les types utilisés.

@+

Choix d'index par l'optimiseur

Requêtes MySQL

Discussions similaires

Partager

Partager