Question req perf

Version imprimable

07/10/2022, 16h52
dari68

Question req perf

Bonjour,

Avec MySQL, si j'ai une requête par exemple:

select * from matab where COL='ppp' et si y a un idex sur COL: est ce qu'il sera pris en compte avec select * ?

MERCI.

A+
07/10/2022, 17h18
escartefigue

Bonjour,

Ca dépend :

Si la table contient un grand nombre de lignes et que la colonne COL contient un grand nombre de valeurs distinctes, alors il est probable que l'index sera utilisé
Si à l'inverse, la table ne contient que quelques lignes ou bien que la colonne COL ne contient que 4 ou 5 valeurs distinctes, alors le ou les index existants ne seront probablement pas utilisés.

En effet, un index n'est utilisé que s'il est jugé pertinent par l'optimiseur.
Dans le premier cas, l'optimiseur choisira sans doute d'utiliser l'index pour retrouver les lignes car la valeur 'ppp' fait partie des milliers de valeurs possibles et seules quelques lignes satisfont ce critère
Dans le deuxième cas, soit balayer toute la table ne prend que quelques nanosecondes (cas d'une table d'une dizaine de lignes), en ce cas il est inutile de passer par un index, soit la valeur 'ppp' représente au moins 10% de l'effectif de la table, auquel cas faire des allers-retours entre index et data prendrait plus de temps que de balayer toute la table et de ne retenir que les lignes qui nous intéressent.

D'autre éléments peuvent entrer en jeu, tels que la désorganisation plus ou moins forte de la table. Une forte désorganisation défavorise l'usage des index.

Par contre, l'usage de SELECT * plutôt que de citer les colonnes utiles au traitement est dans tous les cas déconseillé : ça compromet l'usage d'un éventuel index couvrant, ça rend le résultat instable si la table évolue au risque de faire planter le traitement ou de produire des résultats erronés, et les performances sont dégradées.
10/10/2022, 20h20
dari68

Merci bcp escartefigue.

A+

Salut à tous

Citation:

Envoyé par Escartefigue

Ca dépend :

Ben non. :? Quand on désire savoir si un index est utilisé ou pas, on fait un "explain" devant la requête.
Mais on peut aussi forcer l'usage d'un index ou le contraire, ignorer un index. Ci-après un exemple fort simple.

Code:

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
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`   integer  unsigned NOT NULL auto_increment primary key,
  `val`  smallint unsigned NOT NULL,
  `lib`  varchar(255)      NOT NULL,
  INDEX `idx1` USING BTREE (`val`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`lib`,`val`) values
  ('un',   1),('deux', 2),('trois', 3),('quatre', 4),('cinq', 5),
  ('eins', 1),('zwei', 2),('drei',  3),('vier',   4),('funf', 5),
  ('one',  1),('two',  2),('three', 3),('four',   4),('five', 5)
--------------
 
--------------
select * from `test`
--------------
 
+----+-----+--------+
| id | val | lib    |
+----+-----+--------+
|  1 |   1 | un     |
|  2 |   2 | deux   |
|  3 |   3 | trois  |
|  4 |   4 | quatre |
|  5 |   5 | cinq   |
|  6 |   1 | eins   |
|  7 |   2 | zwei   |
|  8 |   3 | drei   |
|  9 |   4 | vier   |
| 10 |   5 | funf   |
| 11 |   1 | one    |
| 12 |   2 | two    |
| 13 |   3 | three  |
| 14 |   4 | four   |
| 15 |   5 | five   |
+----+-----+--------+
--------------
explain select * from `test`                       where val = 2
--------------
 
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx1          | idx1 | 2       | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
--------------
explain select * from `test` ignore index (`idx1`) where val = 2
--------------
 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |     6.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
--------------
explain select * from `test`                       where val > 1
--------------
 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | idx1          | NULL | NULL    | NULL |   15 |    80.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
--------------
explain select * from `test` force  index (`idx1`) where val > 1
--------------
 
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | idx1          | idx1 | 2       | NULL |   12 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

La première et la quatrième requête font l'usage d'un index, pas la deuxième et troisième requête.

Cordialement.
Artemus24.
@+

11/10/2022, 13h28
escartefigue
Citation:

Envoyé par Artemus24

Quand on désire savoir si un index est utilisé ou pas, on fait un "explain" devant la requête.
Mais on peut aussi forcer l'usage d'un index ou le contraire, ignorer un index. Ci-après un exemple fort simple.

En effet, quelques remarques en complément :
- un explain n'a de valeur que pour une plateforme d'exécution donnée. Par exemple, entre la prod et la recette, si les données sont différentes (nombre de lignes dans les tables, valeurs des colonnes indexées...) ou que certains index sont différents (la version de recette n'étant pas forcément encore en prod), ou encore tout simplement que la date de dernière réorg ou des dernières statistiques est différente, alors l'explain fait en recette n'a pas de valeur sur la prod et inversement.
  En effet, tout optimiseur s'appuie sur les index présents et sur les statistiques pour choisir sa stratégie.
  ==> Il faut faire l'explain sur la plateforme qui nous intéresse
- forcer ou ignorer des index (ce qu'on appelle faire un "hint") n'est à faire que dans des situations très particulières, quand l'optimiseur ne choisit pas de lui même le chemin d'accès le plus efficient. C'est relativement rare, l'optimiseur faisant le plus souvent le meilleur choix. C'est dangereux, car si les statistiques changent, le choix du hint lui subsiste et risque donc de ne plus être pertinent.
  ==> C'est la raison pour laquelle en général, le hint est une solution provisoire, mise en place par le DBA et sous surveillance de celui-ci.
Quoi qu'il en soit, l'explain ne fait que traduire dans les faits les principes que j'ai évoqués plus haut et qui restent vrais.
À savoir que ceci reste le principe de choix ou pas de tel ou tel index :

Citation:

Envoyé par escartefigue

Bonjour,

Ca dépend :

Si la table contient un grand nombre de lignes et que la colonne COL contient un grand nombre de valeurs distinctes, alors il est probable que l'index sera utilisé
Si à l'inverse, la table ne contient que quelques lignes ou bien que la colonne COL ne contient que 4 ou 5 valeurs distinctes, alors le ou les index existants ne seront probablement pas utilisés.

En effet, un index n'est utilisé que s'il est jugé pertinent par l'optimiseur. [...]

Bien sûr, qui doit choix d'index, dit prédicat "SArgAble", un prédicat non sargable n'étant de toute façon jamais indexé (ex : <>, like '%toto'...)
Mais ça c'est un autre débat ;)