Pourquoi MySQL 5.7/innoDB n'utilise pas mon bel index à partir de ?!?

**Calimero90** · 31/03/2020, 13h08

.
Bonjour,

Sur MySQL 5.7, j'ai ajouté sur une table en innoDB un index qui boost un max un select.
Il est bien utilisé sur une recherche d'une quinzaine de jours (environ) mais pas au delà !!!
Pourquoi ??! Une limitation ? de quoi ?

Comment faire pour levé cette limitation ?
Sinon comment savoir quand utiliser un autre index (moins bon) ?
(MySQL ne choisit pas le bon...)

Voir ci-dessous:
J'ai essayé avec différent intervalle de longueur et de plage de date,
c'est toujours AUX ENVIRONS de 15 jours que l'index idPlus n'est plus pris en compte.
GRRRrrr !

Merci.

PRESENTATION TABLE
------------------

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show columns from Mes;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| idx    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| rep    | int(11)          | YES  | MUL | NULL    |                |
| ind    | tinyint(4)       | YES  |     | NULL    |                |
| dtA    | datetime(3)      | YES  |     | NULL    |                |
| val    | float            | YES  |     | NULL    |                |
| infoI1 | int(11)          | YES  |     | NULL    |                |
| infoS1 | varchar(30)      | YES  |     | NULL    |                |
| dyA    | int(10) unsigned | YES  | MUL | 0       |                |
+--------+------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show index from Mes;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------
| mes   |          0 | PRIMARY  |            1 | idx         | A         |    13097815 |     NULL | NULL   |      | BTREE
| mes   |          1 | idMes    |            1 | rep         | A         |          33 |     NULL | NULL   | YES  | BTREE
| mes   |          1 | idMes    |            2 | ind         | A         |          33 |     NULL | NULL   | YES  | BTREE
| mes   |          1 | idDyA    |            1 | dyA         | A         |          85 |     NULL | NULL   | YES  | BTREE
| mes   |          1 | idPlus   |            1 | dyA         | A         |        8242 |     NULL | NULL   | YES  | BTREE
| mes   |          1 | idPlus   |            2 | rep         | A         |       15008 |     NULL | NULL   | YES  | BTREE
| mes   |          1 | idPlus   |            3 | ind         | A         |       15008 |     NULL | NULL   | YES  | BTREE
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------
7 rows in set (0.00 sec)

Code :

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

1
2
3
4
5
6
7
8
9
10
mysql> select * from Mes where dtA > '2020-02-01' limit 5;
+----------+------+------+-------------------------+----------+--------+----------------------+------------+
| idx      | rep  | ind  | dtA                     | val      | infoI1 | infoS1               | dyA        |
+----------+------+------+-------------------------+----------+--------+----------------------+------------+
| 62405160 |  681 |    1 | 2020-02-01 00:00:00.744 | 0.146489 |      0 | STM: Mesure opacité  | 1580511600 |
| 62405161 |  604 |    1 | 2020-02-01 00:00:08.195 |  1.56353 |      0 | LM: Mesure de vent   | 1580511600 |
| 62405162 |  609 |    1 | 2020-02-01 00:00:17.395 |  1.44338 |      0 | LM: Mesure de vent   | 1580511600 |
| 62405163 |  606 |    1 | 2020-02-01 00:00:19.296 |  1.64104 |      0 | LM: Mesure de vent   | 1580511600 |
| 62405164 |  602 |    1 | 2020-02-01 00:00:19.846 |  1.43067 |      0 | LM: Mesure de vent   | 1580511600 |
+----------+------+------+-------------------------+----------+--------+----------------------+------------+

Code :

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

1
2
3
4
5
6
mysql> select count(idx) from Mes;
+------------+
| count(idx) |
+------------+
|   13159102 |
+------------+

LE PROBLEME
-----------

Code :

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

1
2
3
4
5
6
7
 
mysql> explain select dtA, val FROM Mes USE INDEX(idPlus) WHERE dyA >= unix_timestamp('2020-02-01') AND dyA <= unix_timestamp('2020-02-15')  AND rep=0601 AND ind=1;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows    | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------+
|  1 | SIMPLE      | Mes   | NULL       | range | idPlus        | idPlus| 12      | NULL | 2159980 |     0.30 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------+

==> Requête super rapide... (tout est relatif...)

Code :

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

1
2
3
4
5
6
mysql> explain select dtA, val FROM Mes USE INDEX(idPlus) WHERE dyA >= unix_timestamp('2020-02-01') AND dyA <= unix_timestamp('2020-02-16')  AND rep=0601 AND ind=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | Mes   | NULL       | ALL  | idPlus        | NULL | NULL    | NULL | 13097815 |     0.05 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+

==> Requête super lente...

Merci !

**escartefigue** · 31/03/2020, 14h05

Bonjour,

J'ai du mal à lire vos explain, un peu de mise en forme aurait aidé, mais d'une façon générale, le but d'un index est de filtrer une sous partie, la plus petite possible, de l'effectif des tables, pour ne pas avoir à parcourir la totalité de celles-ci. Donc, plus vous élargissez la taille de la maille, plus au bout d'un moment l'usage d'un index perd de son intérêt et finit même par être contre-productif.

Techniquement, le SGBD recherche dans l'index les valeurs de filtrage (ou de jointure) pour récupérer l'adresse physique les lignes concernées dans les tables, mais s'il faut récupérer un très grand nombre d'adresses et faire plein d'allers retours de l'index vers les data, c'est plus lent que de parcourir séquentiellement toute la table.

C'est très certainement ce qui vous arrive

Il existe une notion très importante dans les SGBD-R qui est le facteur de filtrage si celui-ci est insuffisant, l'index n'est pas utilisé.

Exemple canonique : si vous posez un index sur le code sexe d'une table des personnes, cet index ne sera jamais utilisé comme critère de filtrage (il n'y a que deux valeurs possibles, éventuellement 3 si vous avez prévu une valeur pour "non documenté", et grosso modo chaque valeur représente la moitié de l'effectif).

**Calimero90** · 31/03/2020, 14h17

OK, je comprends mieux maintenant.
Car je viens de voir qu'avec index(rep,id, dyA) ca marche sur bien plus d'un mois !
C'est maintenant d'abord rep (moins nombreux) qui est utilisé pour le premier 'filtage'

Merci à tous

**escartefigue** · 31/03/2020, 14h43

C'est la raison pour laquelle, dans la mesure du possible, il est recommandé de créer les index multi-colonnes en positionnant en premier les colonnes les plus filtrantes

**Calimero90** · 01/04/2020, 10h30

.
Bonjour,
Bon, finalement, c'est pas résolu ! :

resumé:
dyA = int(10) unsigned -> contient un unix_timestamp de date (ie '2020-01-01')
dtA = datetime
val = float -> contient plein de valeur par jour (horodatées dans dtA)

idDya = index(dyA)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
mysql> explain select val from mes use index(idDya) where dya>=unix_timestamp(date('2020-03-12'));
+----+-------------+-------+------------+-------+---------------+-------+---------+------+---------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows    | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+---------+----------+----------------------------------+
|  1 | SIMPLE      | mes   | NULL       | range | idDyA         | idDyA | 5       | NULL | 2108706 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+---------+----------+----------------------------------+
mysql> explain select val from mes use index(idDya) where dya>=unix_timestamp(date('2020-03-11'));
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | mes   | NULL       | ALL  | idDyA         | NULL | NULL    | NULL | 13089141 |    17.88 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+

Pourquoi idDya n'est pas pris au delà de 15 jours environ ?
Vous allez me dire parce qu’il n'est plus assez filtrant (trop de row) à partir de 16 jours ?
Mais pourtant il est super rapide à 15j !! Et super lent à 16j sans l'index !!
C'est MOI qui décide non ?
comment le forcer à prendre l'index pour plus de 15 jours ? (Après c'est moi qui voit quand limiter cet usage)
(ce n'est pas innodb_buffer_pool_size ... j'ai essayé)

Dois-je ajouter un champ dmA (timestamps par mois) et faire un index dessus ??!! (comme j'ai fais par jour avec dyA...)

Merci

**SQLpro** · 01/04/2020, 12h37

Envoyé par Calimero90

...
Pourquoi idDya n'est pas pris au delà de 15 jours environ ?
Vous allez me dire parce qu’il n'est plus assez filtrant (trop de row) à partir de 16 jours ?

Plus exactement, c'est l'estimation de la cardinalité qui indique qu'il est préférable de ne plus utiliser l'index au delà d'un certain seuil...

Mais pourtant il est super rapide à 15j !! Et super lent à 16j sans l'index !!

Normal... Effet de seuil ! Mais il estime probable qu'avec l'index ce serait pire ! D'ou son choix...

C'est MOI qui décide non ?

Pas du tout et heureusement....
Extrait de mon nouveau livre sur SQL à paraître...

Cet aspect des choses rebute souvent le développeur qui veut absolument tout contrôler du code qu’il écrit et souhaite que la machine lui obéisse aveuglément… Il n’en est rien face à un langage de requête on l’on ne dit pas à la machine comment elle doit faire étape par étape (encore le côté itératif), mais ce qu’il faut qu’elle renvoie… C’est tout l’intérêt d’un langage de requête !
Autrement dit :

le SQL n’est pas un langage impératif, mais directif…

Il indique quel est l’objectif et non les moyens d’y parvenir ! Dès lors il ne sert à rien de torturer l’écriture de la requête dans tous les sens pour tenter de l’optimiser. C’est en revanche, le rôle du moteur SQL…

comment le forcer à prendre l'index pour plus de 15 jours ? (Après c'est moi qui voit quand limiter cet usage)

Voius ne pouvez pas conditionner sa prise d'index pour certaines critères...
En revanche vous pouvez lui forcer l'index de manière définitive avec USE INDEX (...)
mais c'est généralement à double tranchant...
D'autant plus que MySQL possède l'un des optimiseur les plus pauvres des SGBDR !

A +

**Calimero90** · 01/04/2020, 13h39

Mouai, merci, je comprends mais ça m’empêche d'optimiser une recherche sur 1 mois ou 2 ou 3...

>le SQL n’est pas un langage impératif ... Il indique quel est l’objectif ...
Bin c'est d'aller vite !
Et il le fait pas à cause d'un seuil trop .... incontrôler/incontrôlable ...

Et un champ timestamp Mois + un index(tsMois, tsJour) ?
Ça vaut le coup de prendre le temps d’essayer ou pas ?

Merci

**escartefigue** · 01/04/2020, 13h45

J'avais expliqué plus haut que le facteur de filtrage était essentiel.

L'explain estime pour la deuxième requête qu'il devra conserver 18% de 13 millions de lignes soit 2,34 millions de lignes, ca ferait beaucoup d'allers-retour des index vers les data
Et plus encore, si l'index sur ce time-stamp n'est pas l'index cluster (est-ce le cas ?), alors il faut cheminer de l'index choisi, vers l'index cluster puis les data...

Certains SGBD n'ont pas besoin de passer par l'index cluster et sont gagnants dans ce cas de figure, ce n'est pas le cas de MySQL

Quant au "hint", attention : ce qui peut être pertinent à un instant "t" peut devenir très mauvais peu de temps après, en général, les hint sont à utiliser en "one shot"

**Calimero90** · 01/04/2020, 14h29

Merci.
Je comprends un peu mieux ce qui se passe...
Ça ce complique quand on essaie de jouer dans la cours des grands!

>Et plus encore, si l'index sur ce time-stamp n'est pas l'index cluster (est-ce le cas ?)
Heu, oui je crois, le Primary (donc le cluster?) c'est idx : int autoincrement ...ect...

Quant au "hint", attention...
OK mais pourquoi si je le laisse faire il ne prends pas l'index qui au final va bien plus vite !
C'est un manque de savoir de ma part je pense.

D'après ce que je viens de comprendre,
plutôt que de créer un champ idx INT Primary autoincrement (qui ne me sert à rien. Est-ce une erreur de jeunesse à 53ans...),
et d'ajouter un champ (bidouille) dya INT qui contient le timestamp du jour qui sert d'index (en plus d'un champ datetime pour dater l'insertion)
j'aurai plutôt eu intérêt à faire un champs dya INT primary qui contient le timestamp(3) en miliseconds (plus éventuellement une valeur autoincrement pour forcer l'unicité)
et utiliser directement where dya>timestamp...

C'est un peut ça ?
J'ai un peu compris ?

Mais bon, je peu pas tout refaire maintenant...

Merci tout plein!

**escartefigue** · 01/04/2020, 14h59

Envoyé par Calimero90

Heu, oui je crois, le Primary (donc le cluster?) c'est idx : int autoincrement ...ect...

Oui : MySQL ne sait pas dissocier l'index primaire de l'index cluster, encore une bizarrerie de ce SGBD

Envoyé par Calimero90

OK mais pourquoi si je le laisse faire il ne prends pas l'index qui au final va bien plus vite !
C'est un manque de savoir de ma part je pense.

L'optimiseur du SGBD fonde sa stratégie sur les statistiques - nombre de lignes, nombre de valeurs distinctes pour les colonnes, répartition de ces valeurs, taux d'organisation -sur l'organisation ou désorganisation et, sur les index et les type de prédicat de filtrage et de jointure.
Le hint, lui, ne tient compte que de ce qu'on lui dit de faire sans se poser de question.
L'optimiseur peut parfois se tromper, notamment si les statistiques ne sont pas à jour.

Envoyé par Calimero90

D'après ce que je viens de comprendre,
plutôt que de créer un champ idx INT Primary autoincrement (qui ne me sert à rien. Est-ce une erreur de jeunesse à 53ans...),
et d'ajouter un champ (bidouille) dya INT qui contient le timestamp du jour qui sert d'index (en plus d'un champ datetime pour dater l'insertion)
j'aurai plutôt eu intérêt à faire un champs dya INT primary qui contient le timestamp(3) en miliseconds (plus éventuellement une valeur autoincrement pour forcer l'unicité)
et utiliser directement where dya>timestamp...

L'index cluster, c'est l'index de rangement. Son choix permet de faciliter les requêtes sur des sous-ensembles contigüs si la table est bien organisée : la première ligne qui correspond au critère de filtrage permet de pointer physiquement sur un espace disque à partir duquel on pourra récupérer en séquence la suite des lignes qui nous intéressent.
Le hic c'est que MySQL confond PK et index cluster, on doit donc choisir un compromis d'utilisation.

**SQLpro** · 01/04/2020, 16h11

Envoyé par Calimero90

Mouai, merci, je comprends mais ça m’empêche d'optimiser une recherche sur 1 mois ou 2 ou 3...

Il faut comprendre une chose très simple.... Quand c'est gratuit, c'est que ça vaut pas grand chose !
À un moment donné il faut rémunérer les gens qui ont travaillé sur un produit.

MySQL c'est de la bidouille comparé à SQL Server ou oracle. C'est fait pour les petites solutions perso, par pour les grands volumes ni pour les requêtes complexes...

Un moteur SQL digne de ce nom c'est plus de 10 000 années homme en R&D et ça se paye !

A +

**Calimero90** · 01/04/2020, 17h58

escartefigue:
Merci pour ces explications. Que j'aurai probablement trouvées à coup de longues heures de lecture de doc ou de formation.
Mais j'ai un patron...

SQLpro:
OUI ! Et choisir un bon produit pro et bien s'y former compenserai largement les problèmes et le temps passer à cherché et bidouiller...
Mais j'ai un patron...

Je marque résolu. C'est pas un problème de MySQL, c'est un problème de politique d'entreprise...

Merci à vous !

Pourquoi MySQL 5.7/innoDB n'utilise pas mon bel index à partir de ?!?

Requêtes MySQL

Discussions similaires

Partager

Partager