utilisation des index par myisam

**cg_seb94** · 08/08/2015, 10h38

Bonjour,

Je fonctionne avec un moteur myisam.

Je dispose d'une table de 1 milliard de lignes avec un index sur 2 colonnes (disons, C1 et C2 ; C1 est un CHAR(8) et C2 est un champ DATETIME).

Si je fais un explain sur une requête dont la clause WHERE couvre C1 et C2, je vais bien utiliser l'index couvrant ces deux colonnes (300K lignes) : parfait.
Mais je cherche à améliorer les performances et à réduire la hauteur de ma table

...

J'ai donc créé une table qui ne contient que les informations filtrées sur 1 élément de C1 (C1 énumère 3000 éléments distincts) de la table précédente, cette table fait 1 million de lignes et dispose d'un index sur C2.
Si je fais un explain sur exactement la même requête (mais sans C1, puisque la valeur est fixée), je ne vais pas utiliser l'index et serait en full scan (1M lignes).

J'arriverai à utiliser l'index en réduisant l'intervalle sur C2 (ma clause WHERE fait un BETWEEN sur cette colonne), et je comprends que si l'index n'est pas utilisé c'est que l'index n'est pas très discriminant, par contre, je me demande, pourquoi il l'est dans ma table énorme.

L'analyseur va t-il regarder la sélectivité au prorata du nombre de ligne d'une table (300 000 lignes sur 1 000 000 000 - 0,03% ; c'est mieux que 300 000 lignes sur 1 000 000 - 30%) ?
Je pense que c'est de là que vient le souci...

Comment bien/mieux interpréter cela et comment mieux optimiser ma table du coup ?

Merci pour vos contributions

**Artemus24** · 10/08/2015, 02h00

Salut seb64.

Envoyé par seb64

Comment bien/mieux interpréter cela et comment mieux optimiser ma table du coup ?

Il y a trois points sur lesquels tu peux faire des améliorations :

1) Je vais admettre que le MCD de ta base de données est tout à fait adapté à ta requête.
Mais cette table est désorganisée. Il faut régulièrement faire en sorte qu'elle ne le soit plus du tout.
Pour ce faire, un "REPAIR TABLE `nom de la base de données`.`nom de la table`;" sera nécessaire.
Suivie de "OPTIMIZE TABLE `nom de la base de données`.`nom de la table`;".

Dans mon exemple, j'ai juste trié la table après les insertions.

2) le choix de la 'PRIMARY KEY' et des 'index' ne sont pas anodins pour une bonne performance.
Je suis parti du fait que tu utilises deux requêtes, soit :
--> une requête utilisant un identifiant pour accéder directement à ta ligne.
--> une requête faisant un balayage de ta table avec comme critère la date et une chaîne de caractères.

J'ai utilise la 'PRIMARY KEY' pour la seconde requête et l'index pour la première requête.
Vu que je n'ai que 24 lignes dans ma tables, j'ai fait en sorte d'obtenir le minimum en terme de lignes sélectionnées.

3) réécrire ta requête si elle nécessite une autre façon d'accéder à tes données.
Vu que l'exemple que tu donnes est fort simple, il n'a pas été nécessaire de la réécrire.

Et voici un exemple sur la base des informations que tu as donné :

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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test` (
  `date` date         NOT NULL,
  `lib`  char(08)     NOT NULL,
  `id`   int unsigned NOT NULL
) ENGINE=MyIsam
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`date`,`lib`,`id`) values
  ('1995-07-05', 'alpha',   12),
  ('2007-12-17', 'beta',    17),
  ('2001-05-03', 'gamma',   24),
  ('2010-10-27', 'delta',    8),
  ('2010-10-27', 'epzilon',  5),
  ('2010-11-05', 'dzeta',    9),
  ('2008-08-08', 'eta',      3),
  ('2012-12-12', 'theta',   11),
  ('2015-04-07', 'iota',    18),
  ('2003-03-19', 'kappa',   13),
  ('2004-05-12', 'lambda',   2),
  ('2011-11-11', 'mu',       6),
  ('2007-02-24', 'nu',      20),
  ('1998-01-15', 'xi',      22),
  ('1999-09-12', 'omicron',  1),
  ('1994-08-17', 'pi',      15),
  ('2006-06-06', 'rho',      7),
  ('2011-04-21', 'signma',  16),
  ('2001-01-01', 'tau',     10),
  ('1997-12-21', 'upsilon', 21),
  ('2005-07-24', 'phi',      4),
  ('2009-07-27', 'khi',     19),
  ('2003-08-15', 'psi',     14),
  ('2005-05-05', 'omega',   23)
--------------
 
--------------
ALTER TABLE `test` order by date, lib
--------------
 
--------------
ALTER TABLE `test` ADD PRIMARY KEY  `clef` (`date`,`lib`),
                   ADD UNIQUE INDEX `idx1` (`id`)
--------------
 
--------------
describe test
--------------
 
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| date  | date             | NO   | PRI | NULL    |       |
| lib   | char(8)          | NO   | PRI | NULL    |       |
| id    | int(10) unsigned | NO   | UNI | NULL    |       |
+-------+------------------+------+-----+---------+-------+
--------------
explain select * from test
--------------
 
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |   24 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
--------------
select * from test
--------------
 
+------------+---------+----+
| date       | lib     | id |
+------------+---------+----+
| 1994-08-17 | pi      | 15 |
| 1995-07-05 | alpha   | 12 |
| 1997-12-21 | upsilon | 21 |
| 1998-01-15 | xi      | 22 |
| 1999-09-12 | omicron |  1 |
| 2001-01-01 | tau     | 10 |
| 2001-05-03 | gamma   | 24 |
| 2003-03-19 | kappa   | 13 |
| 2003-08-15 | psi     | 14 |
| 2004-05-12 | lambda  |  2 |
| 2005-05-05 | omega   | 23 |
| 2005-07-24 | phi     |  4 |
| 2006-06-06 | rho     |  7 |
| 2007-02-24 | nu      | 20 |
| 2007-12-17 | beta    | 17 |
| 2008-08-08 | eta     |  3 |
| 2009-07-27 | khi     | 19 |
| 2010-10-27 | delta   |  8 |
| 2010-10-27 | epzilon |  5 |
| 2010-11-05 | dzeta   |  9 |
| 2011-04-21 | signma  | 16 |
| 2011-11-11 | mu      |  6 |
| 2012-12-12 | theta   | 11 |
| 2015-04-07 | iota    | 18 |
+------------+---------+----+
--------------
explain select date, lib from test where lib like 'p%' and date between '2003-01-01' and '2003-12-31'
--------------
 
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 11      | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
--------------
select date, lib from test where lib like 'p%' and date between '2003-01-01' and '2003-12-31'
--------------
 
+------------+-----+
| date       | lib |
+------------+-----+
| 2003-08-15 | psi |
+------------+-----+
--------------
explain select * from test where id = 15
--------------
 
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | idx1          | idx1 | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
--------------
select * from test where id = 15
--------------
 
+------------+-----+----+
| date       | lib | id |
+------------+-----+----+
| 1994-08-17 | pi  | 15 |
+------------+-----+----+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

La première requête que je donne est juste un balayage de ta table qui est dans l'ordre du tri que j'ai effectué.

La deuxième requête est le cœur de ton problème. Cette requête utilise bien la 'PRIMARY KEY'.
Pourquoi ai-je utilisé une 'PRIMARY KEY' plutôt qu'un index ?
J'ai fait en sorte que la table soit trié sur le même critère que la 'PRIMARY KEY'.
Ainsi quand tu demandes de sélectionner des lignes selon la date, tout ce que tu vas chercher sur le second critère, c'est-à-dire la colonne 'lib' sera dans la même page.
Les temps d'accès devraient être meilleurs car tu feras qu'une seul lecture si tout ce que tu cherches se trouve dans la même page.
En utilisant des 'index', il se peut que tes informations soient réparties sur plusieurs pages et de ce fait, cela prendra plus de temps.
Dans le explain, on lit pour 'row' la valeur 2. Ceci correspond au nombre de lignes ayant le critère de la date.
En fait, je suis parti de l'hypothèse que la date avait un critère plus fin que le libelle (colonne 'lib').
Si c'est l'inverse alors tu dois inverser ces deux colonnes dans la 'PRIMARY KEY' et trier ta table sur ce même critère.

La troisième requête est juste un accès directe à ta ligne selon la colonne 'id'.
Il est nécessaire d'avoir un 'index' et il remplit bien son rôle.
On voit que dans le explain, on lit pour 'row' la valeur 1.

@+

**cg_seb94** · 12/08/2015, 10h14

Envoyé par Artemus24

Salut seb64.
La deuxième requête est le cœur de ton problème. Cette requête utilise bien la 'PRIMARY KEY'.
Pourquoi ai-je utilisé une 'PRIMARY KEY' plutôt qu'un index ?
J'ai fait en sorte que la table soit trié sur le même critère que la 'PRIMARY KEY'.
Ainsi quand tu demandes de sélectionner des lignes selon la date, tout ce que tu vas chercher sur le second critère, c'est-à-dire la colonne 'lib' sera dans la même page.
Les temps d'accès devraient être meilleurs car tu feras qu'une seul lecture si tout ce que tu cherches se trouve dans la même page.
En utilisant des 'index', il se peut que tes informations soient réparties sur plusieurs pages et de ce fait, cela prendra plus de temps.

Bonjour,

Merci beaucoup à tous les 2, j'ai suivi tes conseils Artemus24 (faire en sorte de disposer de toute l'information sur une même page), c'est beaucoup mieux déjà et je comprends mieux comment influer sur les performances.

@+

**escartefigue** · 13/08/2015, 09h41

Envoyé par Artemus24

...mais cette table est désorganisée.
Il faut régulièrement faire en sorte qu'elle ne le soit plus du tout.
Pour ce faire, un "REPAIR TABLE `nom de la base de données`.`nom de la table`;" sera nécessaire.
Suivie de "OPTIMIZE TABLE `nom de la base de données`.`nom de la table`;"

Pourquoi préconisez vous un repair préalable ?

Envoyé par Artemus24

J'ai utilise la 'PRIMARY KEY' pour la seconde requête et l'index pour la première requête.
[. . .]
Pourquoi ai-je utilisé une 'PRIMARY KEY' plutôt qu'un index ?

Non ! vous retombez toujours dans votre même erreur de confusion entre la couche logique et la couche physique !
La clef, primaire ou non, est de niveau logique
L'index est le support physique, dans lequel sont enregistrées les valeurs de clefs
Quand vous écrivez une requête, vous choisissez d'utiliser ou non des identifiants (clefs), c'est la base de données qui choisira ensuite d'utiliser ou non des index

**escartefigue** · 10/08/2015, 08h48

Envoyé par cg_seb94

L'analyseur va t-il regarder la sélectivité au prorata du nombre de ligne d'une table (300 000 lignes sur 1 000 000 000 - 0,03% ; c'est mieux que 300 000 lignes sur 1 000 000 - 30%) ?

Tout à fait, c'est ce qu'on appelle le facteur de filtrage, grosso modo, au delà de 10% de l'effectif un index n'est pas utilisé car pas suffisamment filtrant et en ce cas, les allers retours entre index et données sont plus couteux qu'un tablescan.

**Artemus24** · 13/08/2015, 03h31

Salut seb94.

Qu'est-ce que cela donne au niveau de tes performances ?

@+

**Artemus24** · 13/08/2015, 20h58

Salut escartegigue.

Envoyé par Escartefigue

Pourquoi préconisez vous un repair préalable ?

C'est juste un outil de maintenance, pour s'assurer que la table n'est pas corrompue.
Cela ne vous arrive jamais de maintenir vos bases de données ?
Je sais très bien que cela n'a aucun rapport avec la question posée, qui est celle de la performance.

Envoyé par Escartefigue

Non ! vous retombez toujours dans votre même erreur de confusion entre la couche logique et la couche physique !

Je ne confonds rien du tout ! Essayez de comprendre ce que je fais avant de venir me critiquer.
J'ai créé une table 'MyIsam' pour son exemple, sans mettre d'index ni de 'primary key'. J'ai ensuite inséré des lignes dedans.
Après j'ai trié le contenu de cette table selon la date et le libelle.
Et enfin, j'ai créé une 'primary key' sur les colonnes date et libelle, puis un index sur la colonne 'id'.
Donc en principe, l'organisation physique des lignes dans la table est ordonné selon la date et selon le libelle.

Dans la première requête, j'ai fait en sorte qu'elle utilise la 'primary key', vu que le where se fait d'une part sur la date et d'autre part sur le libelle.
Dans la seconde requête, l'accès est directe à la ligne, et cette requête utilise bien l'index et non la 'primary key'.
Je pense avoir répondu à la question posée de ce sujet.

Envoyé par Escartefigue

c'est la base de données qui choisira ensuite d'utiliser ou non des index

C'est nouveau ça ???

Je ne peux donc pas selon vous, forcer MySql à utiliser tel index plutôt qu'un autre ?

Maintenant, si vous pouvez faire mieux que mon exemple, j'attends avec impatience votre réponse.

@+

**escartefigue** · 14/08/2015, 08h53

Envoyé par Artemus24

Je sais très bien que cela n'a aucun rapport avec la question posée, qui est celle de la performance.

Question : "pourquoi préconisez vous un repair préalable ?" vous répondez : "parceque ça n'a aucun rapport avec la question posée"

Envoyé par Artemus24

Je ne confonds rien du tout ! .

Oh que si, car vous prétendez utiliser une clef plutôt qu'un index, c'est un non sens, quand comprendrez vous enfin la différence entre clef et index ?

Envoyé par Artemus24

C'est nouveau ça ???

Non, ça a toujours été, la base de donnée choisit ou non d'utiliser un index en fonction de nombreux paramètres, parmi lesquels on trouve notamment le facteur de filtrage dont j'ai parlé plus haut
Vous pouvez influencer les choix de la base de donnée mais en aucun cas choisir un index à sa place

**Artemus24** · 14/08/2015, 18h29

Salut escartefigue.

Envoyé par escartefigue

Oh que si, car vous prétendez utiliser une clef plutôt qu'un index, c'est un non sens, quand comprendrez vous enfin la différence entre clef et index ?

Donnez moi la définition ou expliquez moi la différence, puisqu'il semble que je ne comprends rien ?

Envoyé par escartefigue

Vous pouvez influencer les choix de la base de donnée mais en aucun cas choisir un index à sa place

Je suis désolé de le dire, mais ce que vous écrivez est un non sens ! Ou je peux influencer les choix fait par MySql ou je ne peux.

Si MySql me propose d'utiliser deux index, et qu'il sélectionne le premier alors je peux l'influencer pour choisir le second.
Or ce que vous dites, c'est que MySql sélectionne tels index, et que je ne peux pas changer son choix.
Ou bien votre phrase est mal foutu, ou je ne comprends pas ce que vous dites ?

On peux forcer la sélection des index sous mysql : https://dev.mysql.com/doc/refman/5.7...dex-hints.html

@+

**escartefigue** · 15/08/2015, 00h14

Envoyé par Artemus24

Salut escartefigue.
Donnez moi la définition ou expliquez moi la différence, puisqu'il semble que je ne comprends rien ?

Relisez mon post #6

Envoyé par Artemus24

Je suis désolé de le dire, mais ce que vous écrivez est un non sens ! Ou je peux influencer les choix fait par MySql ou je ne peux.

C'est exactement ce que j'ai écrit, vous pouvez influencer pas choisir

Envoyé par Artemus24

On peux forcer la sélection des index sous mysql : https://dev.mysql.com/doc/refman/5.7...dex-hints.html

En effet, mais vous n'avez pas fait de hint (j'ai bien relu le code de vos posts précédents pour m'en assurer) donc je répète vous ne pouvez pas choisir l'index, c'est le moteur de la base de données qui le fait

**Artemus24** · 14/08/2015, 21h30

Salut à tous.

Je me suis rendu compte que l'exemple que j'ai donné n'est pas suffisamment représentatif de la question posée par ce sujet.
Autrement dit, il faut faire un test grandeur nature pour se rendre compte des problèmes de performances.

Je suis parti sur 100.000 lignes, en gardant la même description (date, libelle, identifiant) de la table qui doit être optimisée.
Je ne me suis pas attardé à la durée de l'exécution de la requête qui n'est pas suffisamment significatif, mais au nombre de lignes lues.

J'ai modifié ensuite 10.000 lignes au hasard, où j'ai mis pour la date : '2015-08-10 00:00:00' et pour le libelle, j'ai mis : 'BBBBB'.
Puis enfin, sur ces 10.000 lignes, tous les 20 fois, j'ai modifié 500 ligne en modifiant la date où j'ai mis : '2015-08-12 00:00:00'

Donc la date '2015-08-12 00:00:00' est présente 500 fois, et le libelle 'BBBBB' 10.000 fois.
La requête sera basée sur ces deux critères pour la sélection des lignes.

Voici comment j'ai procédé pour créer ma table de type MyIsam.

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
SET AUTOCOMMIT = 0;
START TRANSACTION;
 
-- ======================
-- Base de Données `base`
-- ======================
 
DROP DATABASE IF EXISTS `base`;
 
CREATE DATABASE `base`
	DEFAULT CHARACTER SET `latin1`
	DEFAULT COLLATE       `latin1_general_ci`;
 
USE `base`;
 
-- ============
-- Table `test`
-- ============
 
DROP TABLE IF EXISTS `test`;
 
CREATE TABLE `test`
(
  `datime` timestamp(6) NOT NULL,
  `lib`    char(20)     NOT NULL,
  `id`     int unsigned NOT NULL
) ENGINE=MyIsam
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ===================
-- Procédure `remplir`
-- ===================
 
DROP PROCEDURE IF EXISTS remplir;
 
DELIMITER $$
CREATE PROCEDURE remplir
(
	IN In_Max INTEGER,
	IN In_Qte INTEGER,
	IN In_Dif INTEGER
)
DETERMINISTIC
NO SQL
BEGIN
 	DECLARE _nbr  INTEGER DEFAULT 1;
	DECLARE _alea INTEGER DEFAULT 0;
	DECLARE _calc INTEGER DEFAULT 0;
 
	SET _calc = In_Qte / In_Dif;
 
    WHILE _nbr <= IN_Max  DO
      INSERT INTO `test` (`datime`,`lib`,`id`) VALUE (now(6), 'AAAAA', _nbr);
 
      SET _nbr = _nbr + 1;
    END WHILE;
 
-- =====================================================================================
 
    SET _nbr = 1;
 
    WHILE _nbr <= In_Qte  DO
      SET _alea = FLOOR((In_Max*rand())+1);
 
      UPDATE `test` SET lib = 'BBBBB', datime = '2015-08-10 00:00:00' WHERE id = _alea;
 
	  IF (_nbr % _calc) = 0 THEN
	    UPDATE `test` SET datime = '2015-08-12 00:00:00' WHERE id = _alea;
      END IF;
 
      SET _nbr = _nbr + 1;
    END WHILE;
END$$
DELIMITER ;
 
-- =====================
-- Remplissage de `test`
-- =====================
 
call remplir(100000, 10000, 500);
 
-- ===
-- Fin
-- ===
 
COMMIT;
SET AUTOCOMMIT = 1;

Comme on peut le voir, je n'ai pas créé ni de Primary Key, ni d'index et je n'ai pas trié le contenu de ma table.
Le but est d'ajouter un index ou une 'primary key' et voir l'influence que cela peut procurer sur le nombre de lignes lus.

Voici la requête que j'ai testé à chaque fois :

Code :

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

explain SELECT SQL_NO_CACHE * from test where datime between '2015-08-12 00:00:00' and '2015-08-12 00:00:00' and lib like 'BBBBB';

1) création de la base de données, sans d'index, et sans 'primary key'.
Autement dit, on fait un 'full scan'.

Code :

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

1
2
3
4
5
6
7
8
9
--------------
explain SELECT SQL_NO_CACHE * from test where datime between '2015-08-12 00:00:00' and '2015-08-12 00:00:00' and lib like 'BBBBB'
--------------
 
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

2) ajout d'un index sur 'datime, lib, id'.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
--------------
alter table `test` add index `idx` (`datime`,`lib`,`id`)
--------------
 
--------------
explain SELECT SQL_NO_CACHE * from test where datime between '2015-08-12 00:00:00' and '2015-08-12 00:00:00' and lib like 'BBBBB'
--------------
 
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | idx           | idx  | 27      | NULL |  520 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

3) suppression de l'index précédent, ajout d'un nouvelle index sur 'lib, datime, id'.

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
--------------
alter table `test` drop index `idx`
--------------
 
--------------
alter table `test` add index `idx` (`lib`,`datime`,`id`)
--------------
 
--------------
explain SELECT SQL_NO_CACHE * from test where datime between '2015-08-12 00:00:00' and '2015-08-12 00:00:00' and lib like 'BBBBB'
--------------
 
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | idx           | idx  | 27      | NULL |  806 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

4) suppression de l'index précédent, et ajout de la 'primary key' sur 'datime, lib, id'. La table n'est pas trié sur ce critère.

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
--------------
alter table `test` drop index `idx`
--------------
 
--------------
alter table `test` add primary key (`datime`,`lib`,`id`)
--------------
 
--------------
explain SELECT SQL_NO_CACHE * from test where datime between '2015-08-12 00:00:00' and '2015-08-12 00:00:00' and lib like 'BBBBB'
--------------
 
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 27      | NULL |  894 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

5) suppression de la 'primary key' précédente, tri selon le critère 'datime, lib, id' et création de la 'primary key' sur 'datime, lib, id'.

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
--------------
alter table `test` drop primary key
--------------
 
--------------
alter table `test` order by datime, lib, id
--------------
 
--------------
alter table `test` add primary key (`datime`,`lib`,`id`)
--------------
 
--------------
explain SELECT SQL_NO_CACHE * from test where datime between '2015-08-12 00:00:00' and '2015-08-12 00:00:00' and lib like 'BBBBB'
--------------
 
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 27      | NULL |  528 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

6) création de la base de données et création de la primary key sur 'lib, datime, id'. La table n'est pas trié.

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
--------------
alter table `test` add primary key (`lib`,`datime`,`id`)
--------------
 
--------------
explain SELECT SQL_NO_CACHE * from test where datime between '2015-08-12 00:00:00' and '2015-08-12 00:00:00' and lib like 'BBBBB'
--------------
 
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 27      | NULL |   89 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
 
Appuyez sur une touche pour continuer...

7) suppression de la 'primary key' précédente, tri selon le critère 'lib, datime, id' et création de la 'primary key' sur 'lib, datime, id'.

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
--------------
alter table `test` order by lib, datime, id
--------------
 
--------------
alter table `test` add primary key (`lib`,`datime`,`id`)
--------------
 
--------------
explain SELECT SQL_NO_CACHE * from test where datime between '2015-08-12 00:00:00' and '2015-08-12 00:00:00' and lib like 'BBBBB'
--------------
 
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 27      | NULL |  238 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

@+

**Artemus24** · 14/08/2015, 21h33

Salut à tous.

En espérant que je n'ai pas fait de conneries durant ces tests, je suis assez surpris des résultats.
Ce qui signifie qu'il ne faut jamais avoir des idées préconçues !
Voici le récapitulatif des tests :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
+----------------+----------------------+-----------------+----------------+----------+--------------------+-----------+
| Numéro du test | Création de la table |      Index      |  Primary Key   | Order BY | Nombre de lectures |  Remarque |
+----------------+----------------------+-----------------+----------------+----------+--------------------+-----------+
|       01       |          OUI         |       Sans      |      Sans      |    NON   |       100.000      | FULL SCAN |
|       02       |          NON         |  Date, lib, id  |      Sans      |    NON   |         520        |           |
|       03       |          NON         |  lib, Date, id  |      Sans      |    NON   |         806        |           |
+----------------+----------------------+-----------------+----------------+----------+--------------------+-----------+
|       04       |          NON         |       Sans      | Date, lib, id  |    NON   |         894        |           |
|       05       |          NON         |       Sans      | Date, lib, id  |    OUI   |         528        |           |
+----------------+----------------------+-----------------+----------------+----------+--------------------+-----------+
|       06       |          OUI         |       Sans      | Lib, date, id  |    NON   |          89        |  Le Top   |
|       07       |          NON         |       Sans      | Lib, date, id  |    OUI   |         238        |           |
+----------------+----------------------+-----------------+----------------+----------+--------------------+-----------+

Nous avons deux critères, à savoir la date à '2015-08-12 00:00:00' sur 500 lignes et le libelle à 'BBBBB' sur 10.000 lignes.
Je tiens à préciser que les 500 lignes sont inclues dans les 10.000 lignes.

Voici mes constatations :

1) l'ordre des colonnes dans les critères a une énorme importance !!!

2) le bon ordre dans le critère est de la plus grande quantité vers la plus petite pour cet exemple donné.
Ainsi pour date on a 500 lignes et pour libelle 10.000 lignes. Il faut mettre libelle en premier et date en second.
Mais si nous avions changé les quantités pour date et libelle, il est fort probable que ce critère ne serait plus du tout valable.

3) l'index n'est pas toujours ce qu'il y a de mieux en terme de performance.
Comme on vient de le voir, il vaut mieux privilégier la 'primary key'.

4) le 'order by' n'a pas nécessairement un bonne influence sur la performance.
ce qui me surprend le plus, dans ce cas de figure, c'est que le tri n'est pas un critère pertient !
J'ai toujours cru qu'un table triée est plus performante qu'un table dans le désordre sur le critère de recherche.
J'ai peut-être oublié de faire quelque chose, mais je ne voie pas quoi.

5) la meilleur performance est selon l'ordre 'Lib, Date, Id' mais appliqué sur la 'Primary Key', toujours pour cet exemple.
Un autre exemple aurait donné un autre ordre, mais pas nécessairement. D'où l'importance du bonne usage de la 'primary key'.

Je rappelle que la requête est la suivante :

Code :

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

explain SELECT SQL_NO_CACHE * from test where datime between '2015-08-12 00:00:00' and '2015-08-12 00:00:00' and lib like 'BBBBB';

et qu'en changeant un des paramètres, on peut obtenir des résultats forts différents.

Autrement dit, ce qui fonctionne pour une requête donnée, peut ne plus fonctionner du tout pour une autre requête en terme de performance.
Le choix de la 'primary key' ou de l'index dépend surtout d'un juste milieu entre de bonnes performances et des performances dégradée.

6) ici, le test concernait deux requêtes dont la requête principale posait un problème de performance.
S'il existait d'autres requêtes, il faudrait faire des tests plus poussés, et trouver un juste milieux en terme de performance.
Cela peut devenir très très compliqué, voir sans solution optimal, disons juste un compromis entre ce qui est acceptable et ce qui ne l'est pas.

@+

**Artemus24** · 15/08/2015, 06h19

Salut escartefigue.

Envoyé par escartefigue

Relisez mon post #6

Je l'ai relus, ce n'est pas pour autant que je comprends mieux ce que vous dites. Soyez plus clair !
Je ne le fais pas exprès, mais il me semble depuis le départ que volontairement vous restez dans le flou, pour ne pas vous dévoiler.

Envoyé par Escartefigue

Non ! vous retombez toujours dans votre même erreur de confusion entre la couche logique et la couche physique !

Mais où voyez-vous que j'aborde cette question en faisant le distinction entre la couche logique d'avec la couche physique ?
Le fait simplement de m'en parler, montre que c'est vous qui avez un problème avec ces deux notions.

Envoyé par Escartefigue

La clef, primaire ou non, est de niveau logique

Qu'est-ce que cela veut dire, dans votre façon de voir les bases de données, que c'est du niveau logique ?
Logique pour moi, c'est comme si l'on parle de 'virtuel'. Dans ce cas là, physique serait 'réel', par opposition à 'virtuel'.

Mais votre table n'est pas dématérialisé, elle existe bien dans votre ordinateur. Alors pourquoi parles-vous de logique ?
Et un index, c'est juste un fichier de pointeurs, qui fait la relation entre des valeurs de une ou plusieurs colonnes, avec leur emplacement sur le disque.
Ce fichier, pour moi, c'est du physique, juste pour résoudre des problèmes de performances. Mais au niveau de la manipulation des données, c'est transparent.
Je me fout complètement de savoir comment est codifié ce fichier. Tout ce que je veux, c'est pouvoir l'utiliser à ma convenance.

Je ne comprends même pas le fait que vous fassiez une distinction entre niveau logique et niveau physique.
Un SGBD, c'est un ensemble d'outils pour manipuler des données, et c'est tout.

Envoyé par Escartefigue

L'index est le support physique, dans lequel sont enregistrées les valeurs de clefs

Vous voyez, vous n'êtes pas clair du tout. Un coup, l'index, c'est du niveau logique, et une autre fois, c'est un support physique. Il faudrait savoir ???

De plus, pourquoi introduisez-vous, ici, la notion de clef ??? Un index se fait sur une ou plusieurs colonnes, et c'est tout. Il n'y a pas de distinction à faire, non plus, entre clef et pas clef.

Envoyé par Escartefigue

Quand vous écrivez une requête, vous choisissez d'utiliser ou non des identifiants (clefs), c'est la base de données qui choisira ensuite d'utiliser ou non des index

La notion d'identifiant / propriétés vient du modèle entité-relation. C'est un formalisme pour détailler le rôle de ces données dans la fonctionnelle.
Il peut y avoir plusieurs clefs candidates, mais une seule sera dite 'primary key'.
Par définition, une clef est une contrainte d'unicité, ce que je ne conteste nullement. Mais c'est une définition logique, pour reprendre votre façon de vous exprimer.

Croyez-vous que votre clef primaire soit purement virtuel, c'est-à-dire sans support physique ?
Ce que vous ne voulez pas admettre, c'est que ce n'est pas uniquement une contrainte d'unicité. Et je pense que c'est ce qui vous dérange le plus.

Donc pour vous, l'ordre des lignes qui sont stockées dans le table space, n'a aucune importance. Ni pour des questions de performances, ni pour des questions de volumétries. Est-ce bien cela ce qui vous dérange ?

De même, j'ai l'impression que les notions de performances se résume pour vous, à mettre des index et à laisser MySql se démerder tout seul. Est-ce bien cela ?

Et s'il vous plait, répondez à mes questions.
Et j'aimerai plutôt parlez d'optimisation et de débattre des résultats que j'ai trouvé !

@+

utilisation des index par myisam

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager