Discussion :

[aieeeuuuuu]

Je cherche à comprendre pourquoi MySql préfère utiliser l'index de ma seconde colonne, plutôt que d'utiliser la 'primary key' ?

C'est bien le moteur qui fait ce choix. Comme vous avez pu le remarquer, le choix n'est pas le même avec InnoDB qu'avec MyISAM.

Cependant, je vous ai déjà expliqué que le moteur avez deux possibilités équivalentes (la clef primaire ou votre index sur col2), il en choisit une. Pour comprendre précisément pourquoi ce choix plutôt qu'un autre, il faudrait connaitre parfaitement le moteur, ce qui est loin d'être mon cas.
Mais ce n'est pas un bogue. Je dirai même que le choix de l'index idx2 est plutôt pertinent.

Et comme vous reclamez des exemples, reprennons votre requete et remplaçons dans votre l'étoile par le nom des deux colonnes :

Code :

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

SELECT col1,col2 from test

Comparons le nombre de lectures engendrées par la requêtes dans 4 situation différents (en suivant l'évolution du compteur show session status like 'Innodb_buffer_pool_read_requests';). Avec/sans index sur la colonne2. Avec/Sans colonne supplémentaires contenant de longs textes

reprenons votre jeu de test :

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
 
DROP TABLE IF EXISTS `test`;
 
CREATE TABLE `test`
(
  `col1`  int unsigned NOT NULL,
  `col2`  int unsigned NOT NULL,
 
  PRIMARY KEY (`col1`)
) ENGINE= InnoDB 
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ;
 
insert into `test` (`col1`,`col2`) values
(1, 9),(2, 8),(3, 7),(4, 6),(5, 5),(6, 4),(7, 3),(8, 2),(9, 1);

1/ sans index
-> 7 lectures

2/ avec un index sur la colonne 2

Code :

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

create UNIQUE INDEX `idx2` USING BTREE ON `test` (`col2`);

--> 7 lectures

3/ Avec une colonne supplémentaire :

Code :

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

1
2
3
 
ALTER TABLE test ADD dummy VARCHAR(8000) ;
UPDATE test SET dummy = REPEAT(uuid(),200 ) ;

--> 7 lectures

4/ toujours avec la colonne supplémentaire, mais en supprimant l'index :

Code :

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

drop index `idx2`  ON `test` ;

--> 12 lectures

Si on synthétise :

	Avec index	Sans index
Sans colonne suppl.	7	7
Avec colonne supplémentaire	7	12

On voit que dans le cas de test initial (sans colonne supplémentaire), le nombre de pages lues est le même, que l'on utilise l'index ou pas.
On voit également qu'avec la colonne supplémentaire, l'utilisation de l'index pour répondre à la requête est préférable. En effet, celui-ci ne contient que les colonnes nécessaires pour la requête et n'est pas surchargé par la troisième colonne.

[escartefigue]

Et cet argumentaire fort bien détaillé par aieeuuuu a déja été présenté par les différents contributeurs, de façon certes moins détaillée mais avec les mêmes arguments

Reprenez par exemple mon poste #9 page 1 (on est déjà a la page 3, comme le temps passe) et vous aurez tout en bas la même explication

Ce principe de l'index only n'est pas une particularité de MySQL mais existe bel et bien pour d'autres bases de données - probablement toutes mais je préfère rester prudent -
et à commencer par DB2 for Z/OS que vous pensez connaitre pour l'avoir l'administré

[Artemus24]

Salut escartefigue.

Ce comportement n'est pas spécifique à MySql, on a le même avec d'autres SGBD, a commencer par DB2 que vous citez en comparaison

Je tiens à préciser que c'est DB2 gros système sur IBM z/os. Et non la version que l'on peut installer sur un micro ordinateur comme MySql.
Et je peux t'assurer que je n'ai pas le comportement que je rencontre avec MySql.

Quelque soit le SGBD, l'absence d'une clause order by, rend la séquence des enregistrements restitués par le select aléatoire

Pourquoi aléatoire ? Donc à bien te comprendre, tu fais un "select *" et cinq minutes après, tu en refais un autre, et tu n'as pas le même tri.
Allons allons, ne dit pas n'importe quoi.

Le seul index qui ait un lien avec la séquence physique des données est l'index cluster, donc la seule chance d'avoir un select sans order by qui donne un résultat trié est de faire un select dont toutes les colonnes sont dans l'index cluster et sous réserve que le cluster ratio soit de 100% et que l'optimiseur choisisse cet index comme chemin d'accès si d'autres index contiennent aussi toutes les colonnes, rien n'interdit que l'optimiseur choisisse de faire un index scan de ceux là

Si je prends ce lien : https://dev.mysql.com/doc/refman/5.6...dex-types.html
il est expliqué que la 'primary key' est un 'clustered index'.

When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

Or si je lis ta phrase, j'ai l'impression que tu me parles d'autre chose. Or pour moi, la 'primary key' est un cluster.
Donc il y a bien une incompréhension entre nous, d'une part sur le test que j'ai fait, et d'autre part, sur l'indexe cluster.

Qu'est-ce que pour toi l'index cluster ?

Un indexe cluster consiste à faire l'association entre la ligne physiquement écrite sur le disque et d'une part un numéro d'identification que l'on nomme 'rowid' et d'autre part d'indiquer où cette ligne se trouve sur le disque dur.

Un indexe (secondary indexes) fait toujours référence à l'indexe cluster afin d'obtenir une autre façon d'accéder à tes lignes.
Après il existe plusieurs possibilités pour faire ce lien. MySql a choisit de prendre la valeur la valeur contenu dans la colonne 'primary key' pour faire le lien.
Il aurait tout aussi bien fait le lien avec le 'rowid'.

@+

[escartefigue]

Salut escartefigue.
Je tiens à préciser que c'est DB2 gros système sur IBM z/os. Et non la version que l'on peut installer sur un micro ordinateur comme MySql.
Et je peux t'assurer que je n'ai pas le comportement que je rencontre avec MySql.

Oui DB2 for Z/OS, et bien c'est que vos tests ne sont pas fait de façon rigoureuse ! car c'est pourtant le cas

Pourquoi aléatoire ? Donc à bien te comprendre, tu fais un "select *" et cinq minutes après, tu en refais un autre, et tu n'as pas le même tri.
Allons allons, ne dit pas n'importe quoi.

J'ai indiqué plus haut qu'une réorganisation suffisait à produire ce comportement sans pour autant que le contenu de la table ait changé
De la même façon, l'ajout de données dans la table, et même la mise à jour de lignes existantes qui peut provoquer le déplacement physique des données, peut suffire à modifier le résultat d'un select sans order by
(sur ce dernier point, je vous laisse chercher pourquoi, un peu de lecture ne nuit pas )

Si je prends ce lien : https://dev.mysql.com/doc/refman/5.6...dex-types.html
il est expliqué que la 'primary key' est un 'clustered index'.

Ce n'est pas vrai pour tous les types de bases de données
Comme vous mentionnez régulièrement DB2 for Z/OS, vous devriez le savoir : l'index cluster est créé par le mot clef cluster et il est facultatif
Et comme justement l'écart ici se produit avec la base innodb ca devrait vous mettre sur la voie

[Artemus24]

salut aieeeuuuuu.

Merci pour ton test.

C'est bien le moteur qui fait ce choix. Comme vous avez pu le remarquer, le choix n'est pas le même avec InnoDB qu'avec MyISAM.

J'ai bien compris que c'est l'optimiseur qui fait ce choix.
La différence de comportement entre 'InnoDB' et 'MyIsam', je ne me l'explique pas non plus.

Cependant, je vous ai déjà expliqué que le moteur avez deux possibilités équivalentes (la clef primaire ou votre index sur col2), il en choisit une. Pour comprendre précisément pourquoi ce choix plutôt qu'un autre, il faudrait connaitre parfaitement le moteur, ce qui est loin d'être mon cas.

C'est ce que je demande depuis le début de mon sujet. Tant pis si tu ne peux pas me répondre.

Est-ce qu'il y a une autre façon d'influencer ce choix, autrement qu'en faisant un "select * from test order by col1" ?

J'ai refait ton test et je trouve exactement la même chose dans les quatre cas. Est-ce que j'aurai mal fait mon test ?

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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
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` (
        `col1`  int UNSIGNED NOT NULL,
        `col2`  int UNSIGNED NOT NULL,
        PRIMARY KEY (`col1`)
)       ENGINE=InnoDB
        DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
        ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`col1`,`col2`) values (1, 9),(2, 8),(3, 7),(4, 6),(5, 5),(6, 4),(7, 3),(8, 2),(9, 1)
--------------
 
--------------
show session status like 'Innodb_buffer_pool_read_requests'
--------------
 
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_read_requests | 68009 |
+----------------------------------+-------+
--------------
select VARIABLE_VALUE into @a1 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select col1, col2 from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    1 |    9 |
|    2 |    8 |
|    3 |    7 |
|    4 |    6 |
|    5 |    5 |
|    6 |    4 |
|    7 |    3 |
|    8 |    2 |
|    9 |    1 |
+------+------+
--------------
select VARIABLE_VALUE into @b1 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select @b1 - @a1 AS 'nbre' into @z1
--------------
 
--------------
create UNIQUE INDEX `idx2` USING BTREE ON `test` (`col2`)
--------------
 
--------------
select VARIABLE_VALUE into @a2 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select col1, col2 from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    9 |    1 |
|    8 |    2 |
|    7 |    3 |
|    6 |    4 |
|    5 |    5 |
|    4 |    6 |
|    3 |    7 |
|    2 |    8 |
|    1 |    9 |
+------+------+
--------------
select VARIABLE_VALUE into @b2 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select @b2 - @a2 AS 'nbre' into @z2
--------------
 
--------------
ALTER TABLE test ADD dummy VARCHAR(65524)
--------------
 
--------------
UPDATE `test` SET dummy = REPEAT(uuid(),2000)
--------------
 
--------------
select VARIABLE_VALUE into @a3 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select col1, col2 from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    9 |    1 |
|    8 |    2 |
|    7 |    3 |
|    6 |    4 |
|    5 |    5 |
|    4 |    6 |
|    3 |    7 |
|    2 |    8 |
|    1 |    9 |
+------+------+
--------------
select VARIABLE_VALUE into @b3 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select @b3 - @a3 AS 'nbre' into @z3
--------------
 
--------------
ALTER TABLE `test` drop index `idx2`
--------------
 
--------------
select VARIABLE_VALUE into @a4 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select col1, col2 from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    1 |    9 |
|    2 |    8 |
|    3 |    7 |
|    4 |    6 |
|    5 |    5 |
|    6 |    4 |
|    7 |    3 |
|    8 |    2 |
|    9 |    1 |
+------+------+
--------------
select VARIABLE_VALUE into @b4 from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------
 
--------------
select @b4 - @a4 AS 'nbre' into @z4
--------------
 
--------------
select @z1 as 'sans index, sans col',
       @z2 as 'avec index, sans col',
       @z3 as 'avec index, avec col',
       @z4 as 'sans index, avec col'
--------------
 
+----------------------+----------------------+----------------------+----------------------+
| sans index, sans col | avec index, sans col | avec index, avec col | sans index, avec col |
+----------------------+----------------------+----------------------+----------------------+
|                   11 |                   11 |                   11 |                   11 |
+----------------------+----------------------+----------------------+----------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

[aieeeuuuuu]

'ai refait ton test et je trouve exactement la même chose dans les quatre cas. Est-ce que j'aurai mal fait mon test ?

La colonne que vous avez ajoutée contient trop de données... du coup, son contenu est déplacé hors des pages de la table :

If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage

baissez la taille pour la colonne "dummy" et relancez le test.

[pier.antoine]

Bonjour

De ce que j'ai compris de votre demande, c'est :
"Comment Mysql utilise les index pour afficher l'ordre des données, car selon le moteur utilisé, les résultats sont différents?"

Pour moi on vous a répondu:
Les index ne servent pas à afficher l'odre des données, il ne faut donc pas aller dans cette voie.

Ai-je bien résumé?

Pierre

[Artemus24]

Salut escartefigue.

Désolé de le dire mais quand je lis vos réponses, j'ai l'impression que l'on ne parle pas de la même chose.

J'ai indiqué plus haut qu'une réorganisation suffisait à produire ce comportement sans pour autant que le contenu de la table ait changé

On gros, ce que vous dites c'est qu'une réorganisation désorganise votre table. Ça n'a pas de sens.

De la même façon, l'ajout de données dans la table, et même la mise à jour de lignes existantes qui peut provoquer le déplacement physique des données, peut suffire à modifier le résultat d'un select sans order by

La aussi çan'a pas de sens. Une ligne est toujours lié à une 'primary key'
Donc comment pouvez-vous obtenir un ordre différent, juste par une nouvelle insertion ou une mise-à-jour ?
A moins de désactiver les indexes.

J'aimerai avoir un exemple qui illustre vos propos, svp ?

@+

[escartefigue]

Salut escartefigue.

Désolé de le dire mais quand je lis vos réponses, j'ai l'impression que l'on ne parle pas de la même chose.

On gros, ce que vous dites c'est qu'une réorganisation désorganise votre table. Ça n'a pas de sens.

La aussi çan'a pas de sens. Une ligne est toujours lié à une 'primary key'
Donc comment pouvez-vous obtenir un ordre différent, juste par une nouvelle insertion ou une mise-à-jour ?
A moins de désactiver les indexes.

J'aimerai avoir un exemple qui illustre vos propos, svp ?

@+

Ce n'est pas parceque vous avez un index cluster, que les enregistrements sont rangés dans cet ordre, il le sont au départ quand la table est vide et que vous faites des insertions, mais ce n'est plus le cas quand la table vit, qu'on y fait des delete, insert et update. Et c'est là qu'une réorg permet de réagencer les lignes selon l'index cluster.

Ceci est le B.A. BA du fonctionnement des bases de données

Il y a plein de doc disponible sur ce forum et ailleurs sur le WEB vous auriez pu y trouver toutes ces explications, et bien d'autres encore
Lisez ces documents, ce qui n'a pas de sens pour vous aujourd'hui, deviendra limpide

Intéressez vous aussi à ce qui se passe quand on met à jour des lignes contenant des colonnes varchar et l'impact sur le rangement physique des données

[Artemus24]

Salut aieeeuuuuu.

J'ai mis :

Code :

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

1
2
3
4
5
6
--------------
ALTER TABLE test ADD dummy VARCHAR(8000)
--------------
 
--------------
UPDATE `test` SET dummy = REPEAT(uuid(),200)

Et j'ai refais le test. Voici le nouveau résultat :

Code :

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

1
2
3
4
5
+----------------------+----------------------+----------------------+----------------------+
| sans index, sans col | avec index, sans col | avec index, avec col | sans index, avec col |
+----------------------+----------------------+----------------------+----------------------+
|                   11 |                   11 |                   11 |                   16 |
+----------------------+----------------------+----------------------+----------------------+

Comment se fait-il que vous trouvez '7' alors que dans mon exemple, j'ai '11' ?
Par contre dans nos deux exemples, l'augmentation est la même : 5.

La colonne que vous avez ajoutée contient trop de données... du coup, son contenu est déplacé hors des pages de la table

Et comment puis-je voir ce comportement dans mon environnement ?

@+

[Artemus24]

Salut escartefigue.

Ce n'est pas parce que vous avez un index cluster, que les enregistrements sont rangés dans cet ordre

Je vais vous surprendre mais la réponse est OUI !

Si ce n'est pas le cas, alors soit :
--> votre requête est mal foutue
--> ou il y a quelque chose qui vient perturber le bon fonctionnement de votre requête.

Je fais les tests suivant sans et avec une 'primary key' pour vous démontrer que vous vous trompez.

1) il n'y a pas de 'primary key', ni d'indexe dans votre table (cas d'école).
J'insère les lignes dans l'ordre '3, 1, 2'.
Je fais un 'select *', je retrouve cet ordre. En gros, c'est l'ordre d'insertion et c'est tout à fait normal.
Je supprime la ligne numéroté '1', celle du milieu. Puis je viens l'insérer à nouveau dans la table.

Question : où cette nouvelle ligne va se trouver maintenant ?
Elle ne va pas se trouver "au hasard" comme cela vous semble être le cas, mais physiquement elle se trouve à la fin de votre table.
Car une insertion se fait toujours là où il y a de la place disponible.
On ne vient jamais réécrire physiquement sur une ligne qui a été supprimé logiquement. Même le 'rowid' change avec cette nouvelle insertion !

2) il y a juste une 'primary key', pas d'autres indexes dans votre table.
Je refais le même test.

Question : où cette nouvelle ligne va se trouver maintenant ?
Au même endroit physiquement, que dans le cas précédent.
Car c'est le comportement normal de l'insertion physique d'une ligne dans une table, quelque soit le SGBD relationnel.

Sauf que maintenant, il y a une 'primary key' qui va conditionner l'ordre apparent (c'est-à-dire l'affichage) de vos lignes par un "select *".
Ce qui signifie que MySql va chercher les lignes et les mettre dans l'ordre selon la 'primary key'.

Si vous ne me croyez pas, faites par vous-même le test. D'ailleurs voici le mien qui confirme ce que je dis.
Et désolé de le dire, mais c'est aussi ce comportement que j'ai sous DB2.

Que va faire une réorganisation d'une table ?
Elle va comprimer le 'table space'.
C'est-à-dire supprimer physiquement les lignes qui ont été supprimées logiquement afin de gagner de l'espace innutilisé.
Réorganiser les lignes dans l'ordre du 'rowid'.
Et faire en sorte que toutes ces lignes se trouve dans le même espace contigüe sur le disque afin de gagner du temps lors d'une lecture physique.

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test1`
--------------
 
--------------
CREATE TABLE `test1` (
        `col1`  int UNSIGNED NOT NULL
)       ENGINE=InnoDB
        DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
        ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test1` (`col1`) value (2),(1),(3)
--------------
 
--------------
explain select * from test1
--------------
 
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
--------------
select * from test1
--------------
 
+------+
| col1 |
+------+
|    2 |
|    1 |
|    3 |
+------+
--------------
describe `test1`
--------------
 
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1  | int(10) unsigned | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
--------------
DELETE from `test1` WHERE col1=1
--------------
 
--------------
INSERT INTO `test1` (`col1`) value (1)
--------------
 
--------------
select * from test1
--------------
 
+------+
| col1 |
+------+
|    2 |
|    3 |
|    1 |
+------+
--------------
DROP TABLE IF EXISTS `test2`
--------------
 
--------------
CREATE TABLE `test2` (
        `col1`  int UNSIGNED NOT NULL PRIMARY KEY
)       ENGINE=InnoDB
        DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
        ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test2` (`col1`) value (2),(1),(3)
--------------
 
--------------
explain select * from test2
--------------
 
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test2 | index | NULL          | PRIMARY | 4       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
--------------
select * from test2
--------------
 
+------+
| col1 |
+------+
|    1 |
|    2 |
|    3 |
+------+
--------------
describe `test2`
--------------
 
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1  | int(10) unsigned | NO   | PRI | NULL    |       |
+-------+------------------+------+-----+---------+-------+
--------------
DELETE from `test2` WHERE col1=1
--------------
 
--------------
INSERT INTO `test2` (`col1`) value (1)
--------------
 
--------------
select * from test2
--------------
 
+------+
| col1 |
+------+
|    1 |
|    2 |
|    3 |
+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

mais ce n'est plus le cas quand la table vit, qu'on y fait des delete, insert et update.

Un delete, c'est une suppression logique dans la table. Cela ne libère pas la place occupée par la ligne.
Un insert se fait toujours en fin de table.
Un update, cela dépend si la taille de la ligne augmente ou pas.
Si sa taille ne change pas, la ligne reste au même endroit.
Si sa taille change alors l'ancienne ligne est supprimée logiquement, et la nouvelle ligne, donc celle qui occupe plus de place, est déplacé en fin de table.

A oui, quand je dis en fin de table, cela signifie après la dernière ligne physique stockée dans le 'table space'.
Ce n'est pas en fin du 'table space', sauf si votre 'table space' est occupé à 100%.
Dans ce cas, il y a un extension de votre 'table space' et l'on peut se retrouver en 'full disk'.

Ceci est le B.A. BA du fonctionnement des bases de données

On n'a pas le même abcédaire !

@+

[fsmrel]

Bonsoir,

Je crois que vous confondez le rôle joué par la 'primary key' avec le rôle de l'indexe.

En l’occurrence, il faut séparer soigneusement le niveau relationnel et le niveau physique.


1) Le niveau relationnel ressortit aux mathématiques appliquées (voyez l’algèbre relationnelle), et à la logique des prédicats (voyez le calcul relationnel de tuples et le calcul relationnel de domaines).

Le niveau relationnel n’est pas concerné par les aspects spatio-temporels bassement matériels (coût de stockage, durée des opérations), il s’occupe du QUOI, en l’occurrence des ensembles que sont les relations (au sens du modèle relationnel de données défini par Ted Codd en 1970, cf. A Relational Model of Data for Large Shared Data Banks). Qu’une opération relationnelle consomme zéro ou un temps et un espace un infini n’est pas du ressort de ce niveau.

Par ailleurs, l’ordre de rangement des éléments dans un ensemble n’ayant pas de sens, cela vaut évidemment pour une relation et ses n-uplets.

Dans ce contexte, la clé primaire est un concept relationnel qui a été défini par Ted Codd dans son article fondateur de 1970, et dont le rôle est de garantir l’intégrité d’entité. En réalité, il n’y a plus qu’en SQL qu’on parle de clé primaire, cette clé qui serait « plus égale » que les autres, ravalées au rang de clés alternatives : il y a belle lurette que le qualificatif de « primaire » a disparu de la théorie relationnelle, où l’on ne trouve que des clés candidates (ou plus simplement clés), qui continuent à jouer implicitement leur rôle quant à l’intégrité d’entité.


2) Le niveau physique ressortit notamment à la gestion de l’espace et du temps, à avoir le coût de stockage, la durée des opérations, et c’est seulement là que le COMMENT et le COMBIEN ÇA COÛTE ont un sens.

Un index est un concept physique, un fichier (VSAM LDS par exemple en DB2) dont l’objet est surtout de réduire le plus possible le temps d’accès aux données (sans préjuger du ralentissement provoqué dans les opérations de mise à jour). Accessoirement, un index peut être qualifié de UNIQUE, de PRIMAIRE, ou tout ce que vous voulez, et sous le capot les éditeurs de SGBD, qui sont pragmatiques, s’en servent pour garantir l’unicité des valeurs des clés primaires, mais c’est un moyen (efficace), on est dans la série des « comment, sous le capot, implémenter au mieux une fonctionnalité, au moindre coût, avec un bon fer à souder ».

[Artemus24]

Salut pier.antoine.

Merci de ta participation.

Non, vous aussi, vous n'avez rien compris de mon problème.
Je cherche à comprendre pourquoi MySql utilise un index, alors que je demande juste un vidage de ma table (un full scan), sans autre critère.
Je m'attendais à obtenir les lignes selon l'ordre de la 'primary key', mais en fait MySql utilise un index (et ça, je ne sais pas pourquoi) et me donne un autre ordre.

Je répète encore une fois, je ne cherche pas à faire quelque chose de particulier, je cherche juste à comprendre sur cet exemple.

La seule réponse qui sort du lot est de forcer MySql à sélectionner la 'primary key' par l'ajout de 'order by clef'. J'ai compris cette raison.
SQLPRO me l'a assez répété que l'ordre n'est pas garantie et que c'est la seule façon de procéder pour garantir l'ordre.
Mais je ne me trouve pas dans ce contexte où l'ordre serait arbitraire, si je dois reprendre les propos de SQLPRO.
J'ai un exemple extrêmement simple où l'ordre est toujours le même car la table est petite en volumétrie.
Et rien ne viendrait perturber l'ordre de ma table: partitionnement, thread multiple, ou buffer trop petit ou je ne sais quoi d'autre.

Donc pourquoi MySql utilise un index dans le contexte de cet exemple ?
si je change de contexte, MySql va avoir un autre comportement.
Sauf que les autres comportement, je les comprends, sauf celui-ci !

@+

[SQLpro]

Salut pier.antoine.

Merci de ta participation.

Non, vous aussi, vous n'avez rien compris de mon problème.
Je cherche à comprendre pourquoi MySql utilise un index, alors que je demande juste un vidage de ma table (un full scan), sans autre critère.
Je m'attendais à obtenir les lignes selon l'ordre de la 'primary key', mais en fait MySql utilise un index (et ça, je ne sais pas pourquoi) et me donne un autre ordre.
...

Relisez votre premier post dont j'ai recopié les principaux extraits ici :

Salut à tous.

J'ai un problème de compréhension au sujets des indexes.

J'ai fait le même test, et je n'obtiens pas le même résultat. Voici le test :
...
Rien de bien compliqué ! Et voici les différences :

Avec 'engine=MyIsam' ou 'engine=Memory', j'obtiens le bon résultat. Ma table est tri selon la 'PRIMARY KEY' (col1).
...
Et avec 'engine=InnoDB', j'obtiens le mauvais résultat. Ma table est tri sur l'indexe (col2).
...
L'ordre du tri n'est pas le même ! Tout ce passe comme si l'indexe 'idx2' dans 'engine=InnoDB' venait influencer l'ordre des lignes.

Qu'est-ce qui vient perturber cet ordre ?
Autrement dit, est-ce que j'ai dans 'my.ini' un mauvais paramétrage sur 'engine=InnoDb' ?

Merci.
@+

Vous parlez bien du tri du résultat, de l'ordre des lignes et non pas des index. Si vous aviez voulu parlé de l'utilisation des index il aurait fallu commencer par étudier les plans de requête. L'utilisation d'un index ne restitue pas forcément les données dans l'ordre de tri de l'index, notamment lorsqu'il existe un partitionnement ou que le moteur fait du parallélisme ce qu nous vous avons déjà mentionné !

Bref, quand on pose mal les questions, il est normal que l'on obtienne des réponses vraies qui ne vous satisfassent pas !

A +

[Artemus24]

Salut fsmrel.

Merci de votre participation.

Ma connaissance des bases de données de type DB2, est d'abord avec le profile "ingénieur d'étude" avant de devenir "administrateur.
Ma connaissance de MySql est bien moindre car je suis en apprentissage et mes connaissances DB2 m'aide beaucoup.
Mais un apprentissage personnel, disons pour le plaisir, car je suis à la retraite et l'informatique ma toujours passionné.
Je constate qu'il existe beaucoup plus de possibilités sous mysql que j'ai pu rencontré avec DB2.
J'ai tendance à croire que MySql fonctionne presque à l'identique de DB2, et c'est un tort.

Je suis désolé si je ne suis pas dans la mouvance des administrateurs issus de la micro informatique ou de la mini informatique.
Je suis issu du monde gros système IBM et accessoirement jadis de BULL, et j'ai fait toute ma carrière dans les SSII.
Je constate aussi qu'il y a beaucoup de choses en DB2, voir aussi en IDS2, que nous ne pouvions pas nous permettre et qui se fait couramment dans le monde de la micro.
Entre autre, des jointures à plus de seize tables par exemple, des procédures stockées ...

La théorie, c'est bien, mais pour les théoriciens. J'ai toujours eu une démarche pragmatique, avec la volonté de produire des résultats.
Ce n'est pas un reproche que je fais, mais depuis que je suis arrivé sur ce forum, on n'arrête pas de me parler de la norme.
Quand on est face à un problème, la norme on s'en fout, mais en essaye par les moyens que l'on a à sa disposition de faire des tentatives de résolutions.
La documentation mysql, par exemple, mais aussi les sujets qui ont déjà été traités ici, dans ce forum ou ailleurs, sont d'une grande aide.
Mais dès que cela devient un tantinet trop technique, je n'ai plus aucune réponse !

En ce moment, je lis le livre "Audit et Optimisation - MySql 5" de Pascal Borghino, Olivier Dasino et Arbaud Gadal, que je trouve très bien.
C'est pourquoi, je fais des exercices pour mieux comprendre le fonctionnement de MySql.

Je n'ai pas encore abordé la question système du paramétrage du serveur MySql, au travers de son fichier "my.ini".
Ce que j'essaye de faire, c'est d'une part comprendre les différences avec DB2 et d'autre part de faire de l'optimisation.

@+

[fsmrel]

Bonsoir Artemus24,

La théorie, c'est bien, mais pour les théoriciens.

Et c’est très bien aussi pour les gens qui comme moi ont beaucoup crapahuté dans les bases de données. Rassurez-vous, j’ai longtemps fait le DBA DB2 avec le « 3R » pour devise (Reorg, Runstats, Rebind) sans oublier le précieux EXPLAIN qui manquait cruellement dans DB2 V1 (1984) et sans lequel il n’était pas bien prudent de faire de la production (j’ai attendu la V1R2 (1986) pour enfin faire du tuning autrement qu’en aveugle). Cela dit, la maîtrise de la théorie relationnelle m’a permis de rattraper des gros projets qui partaient en quenouille, d’empêcher d’autres DBA de faire des bêtises (voyez l’exemple de la banque d'une ville méridionale où il fait bon vivre...)

Outre le « 3R », je pense qu’il faut avoir à l’esprit ce que chantait le poète (merci, Georges !) :

G. Brassens :

« Sans technique, un don n’est rien qu’un’ sal’ manie... »

Bref, théorie et technique ne sont pas forcément synonymes, mais en tout cas complémentaires et d'une grande aide...

Quand on est face à un problème, la norme on s'en fout.

Parlez-vous de la norme SQL ? de la normalisation en nième forme normale ? d’autre chose ?

[aieeeuuuuu]

Si j'ai créé ce sujet, c'est que je pensais, à tort, que le fonctionnement de MySql se faisait à l'identique de ce que je connaissais de DB2.
Je dis cela en pensant que MySql suivait la norme SQL. Mea culpa !

Il ne la suit en effet pas, mais ça n'a rien a voir avec le sujet de la discussion qui parle des index. Or, les index n'ont rien à voir avec la norme SQL qui ne se préoccupe pas de l'aspect physique, mais qui reste au niveau logique. Vous ne trouverez pas un mot au sujet des index dans la norme, et chaque éditeur est libre de les implémenter ou non, et surtout comme il l'entend.

Ici, il utilise l'indexe 'clef2' alors qu'il aurait dû utiliser la 'primary key'.

Non ! il aurait pû. ça fait toute la différence.
Vous indiquez au SGBDR (par une requête) ce que vous voulez, libre à lui de trouver comme il l'entend la façon la plus efficace d'y répondre.
Les hint ne sont pas fait pour obtenir un ordre précis, mais pour tirer occasionnellement le moteur d'un mauvais pas lorsqu'il fait de mauvaises estimations (au sens performances, pas au sens du tri que vous voulez obtenir).

Pourquoi aléatoire ? Donc à bien te comprendre, tu fais un "select *" et cinq minutes après, tu en refais un autre, et tu n'as pas le même tri.
Allons allons, ne dit pas n'importe quoi.

Ce n'est pas n'importe quoi !!
Je l'ai déjà observé sous SQL Server : une requête qui renvoyait une fois sur deux un ordre puis un autre.
J'en conviens, ça n'arrive pas tout le temps (surtout avec MySQL qui ne connait pas le parallélisme), mais ça peut arriver.

Ce qui signifie que MySql va chercher les lignes et les mettre dans l'ordre selon la 'primary key'.

Là encore, vous n'avez pas compris : MySQL va chercher bien les lignes mais n'effectuera aucun tri : il les récupérere naturellement (le plus souvent, mais pas forcément) selon l'ordre de la PK dans votre exemple.

[aieeeuuuuu]

a) j'ai quatre possibilités pour influence le choix de mysql sur le 'full scan'. Est-ce qu'il existe d'autres possibilités d'écritures ?

Comme déjà dit, dans votre cas, puisque vous voulez un tri, utilisez ORDER BY.

Faites donc le test suivant sur la requête qui vous pose souci en utilisant l'index sur col2 : ajouter un ORDER BY col1.
Vous remarquerez que MySQL n'utilisera plus cet index idx2, mais passera sur l'index de la PK.

En effet, sans ORDER BY, les deux index couvrent la requêtes, et ils seront tous les deux aussi efficaces.
En ajoutant la clause de tri, l'index sur la clef primaire prend un avantage, car en plus de couvrir la requête, il répond également au tri demandé. MySQL va donc l'utiliser plutôt que idx2, ce qui lui évitera un tri.
En l'occurence, la clause ORDER BY n'aura donc aucun surcout (puisqu'en fait, aucun tri ne sera effectué). Il est donc bien mieux qu'un hint,puisqu'en plus d'avoir l'effet escompté sans surcout, il inclus clairement dans votre requête ce que vous attendez d'elle, ce qui sera bien mieux en maintenance (pensez a ceux qui pourraient reprendre votre requête... comment interpréteront-ils le hint ?).

[fsmrel]

Bonsoir,


A propos de ORDER BY...

Si vous vous vous plongez dans le 1er article [Boyce1974], publié en 1974 sur le langage SQL (SEQUEL à l’époque, qui a perdu ses voyelles pour éviter de possibles problèmes de copyright), vous noterez qu’ORDER BY en est absent. C’est normal, fruit des cogitations de deux chercheurs du laboratoire de recherche d’IBM (San Jose), Chamberlin et Boyce(RIP), SEQUEL n’était qu’un prototype visant à montrer qu’en deux lignes et une minute, on en faisait autant qu’avec un programme de 500 lignes et une journée d’effort (au moins ! j’ai donné...) de programmation de l’époque (avec IMS DL/1, IDMS, IDS2 et autres poids lourds). Manipuler des ensembles, par définition non ordonnés, quoi de plus naturel pour des mathématiciens ? ils n’auraient pas été émus outre mesure d’entendre Monsieur Jourdain déclamer :

« Me font vos yeux beaux mourir, belle Marquise, d’amour ! »

Peu leur importait l’ordre des éléments affichés à l’écran ou imprimés. Mais en 1976, dans la version 2 de SEQUEL [Chamberlin1976], ORDER BY entre en scène. Je cite (le soulignement des mots et autres fioritures sont de mon fait) :

The result of a query is returned in system-determined order unless the user requests an ordering, as shown in Q5.

Q5. List the employee number, name, and salary of employees in Dept. 50, in order of employee number.

SELECT EMPNO,NAME,SAL
FROM EMP
WHERE DNO = 50
ORDER BY EMPNO

Une première !

Visiblement, on sort du cadre de la recherche pure, on commence à s’intéresser aux souhaits des utilisateurs ayant joué les cobayes et dont on a étudié les réactions, en conséquence de quoi des éléments n’ayant rien à voir avec la théorie relationnelle viennent « compléter » le langage, et ça n'est pas fini...


Cela dit, cette requête Q5 a 40 ans, mais en l’état demeure opérationnelle...

_____________________________________

Références

[Boyce1974] R. F. Boyce, D. D. Chamberlin. SEQUEL: A structured English query language.

[Chamberlin1976] D. D. Chamberlin, M.M. Astrahan, et al. SEQUEL 2: A Unified Approach to Data Definition, Manipulation, and Control. IBM Journal of Research and Development, vol. 20, n° 6, novembre 1976.

[escartefigue]

Ce que j'essaye de faire, c'est d'une part comprendre les différences avec DB2 et d'autre part de faire de l'optimisation.
@+

Du point de vue de la séquence selon laquelle les enregistrements sont restitués en l'absence d'order by, vous aurez donc compris qu'il n'y en a pas
Que ce soit DB2 for Z/OS ou ses pseudo-clones micro ou AS/400