Discussion :

[Artemus24]

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 :

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
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;
 
create UNIQUE INDEX `idx2` USING BTREE ON `test` (`col2`);
 
SHOW WARNINGS;
 
insert into `test` (`col1`,`col2`) values
(1, 9),(2, 8),(3, 7),(4, 6),(5, 5),(6, 4),(7, 3),(8, 2),(9, 1);
 
select * from 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).

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
+------+------+
| col1 | col2 |
+------+------+
|    1 |    9 |
|    2 |    8 |
|    3 |    7 |
|    4 |    6 |
|    5 |    5 |
|    6 |    4 |
|    7 |    3 |
|    8 |    2 |
|    9 |    1 |
+------+------+

Et avec 'engine=InnoDB', j'obtiens le mauvais résultat. Ma table est tri sur l'indexe (col2).

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
+------+------+
| col1 | col2 |
+------+------+
|    9 |    1 |
|    8 |    2 |
|    7 |    3 |
|    6 |    4 |
|    5 |    5 |
|    4 |    6 |
|    3 |    7 |
|    2 |    8 |
|    1 |    9 |
+------+------+

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.
@+

[aieeeuuuuu]

Bonjour,

Seule une clause ORDER BY peut vous garantir un ordre spécifique.
Il est très dangereux de compter sur un quelconque index ou une quelconque clef pour obtenir un ordre voulu. Ils ne servent d'ailleurs pas à cela !

[escartefigue]

Bonjour,

Seule une clause ORDER BY peut vous garantir un ordre spécifique.
Il est très dangereux de compter sur un quelconque index ou une quelconque clef pour obtenir un ordre voulu. Ils ne servent d'ailleurs pas à cela !

Et ce quelque soit la base de données

D'ailleurs si vous passez une fois la requete, puis lancez une réorg, puis ré-exécutez exactement la meme requete, sans order by, il est très probable que la séquence change, avec un order by non !

[Artemus24]

Salut à tous.

Mon but est d'éviter de faire un tri quand cela n'est pas nécessaire.
Alors j'ai imaginé un test basique, afin de vérifier une optimisation, avec deux traitements :

1) une lecture séquentielle dans l'ordre de la clef primaire.
2) un accès directe à une ligne particulière en fonction un critère du genre 'where col2 = 5', par exemple.

Pour réaliser cela, en plus de la PRIMARY KEY, je dois ajouter un indexe sur 'COL2'.

Je fais mes deux tests et voici ce que j'obtiens :

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
--------------
show index from test
--------------
 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY  |            1 | col1        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | clef2    |            1 | col2        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
--------------
explain select * from test
--------------
 
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | clef2 | 4       | NULL |    9 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
--------------
explain select * from test where col2 = 5
--------------
 
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | const | clef2         | clef2 | 4       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------------+
--------------
select * from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    9 |    1 |
|    8 |    2 |
|    7 |    3 |
|    6 |    4 |
|    5 |    5 |
|    4 |    6 |
|    3 |    7 |
|    2 |    8 |
|    1 |    9 |
+------+------+
 
Appuyez sur une touche pour continuer...

Désolé de le dire, mais ce résultat me parait vraiment bizarre.
Alors, je refais le même test et j'ajoute une troisième colonne 'col3' avec aussi un index sur cette colonne.
Et voici ce que j'obtiens :

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
--------------
show index from test
--------------
 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY  |            1 | col1        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | clef2    |            1 | col2        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | clef3    |            1 | col3        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
--------------
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 |    9 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
--------------
explain select * from test where col2 = 5
--------------
 
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | clef2         | clef2 | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+
--------------
explain select * from test where col3 = 7
--------------
 
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | clef3         | clef3 | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+
--------------
select * from test
--------------
 
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    9 |    6 |
|    2 |    8 |    4 |
|    3 |    7 |    7 |
|    4 |    6 |    3 |
|    5 |    5 |    8 |
|    6 |    4 |    2 |
|    7 |    3 |    9 |
|    8 |    2 |    1 |
|    9 |    1 |    5 |
+------+------+------+
 
Appuyez sur une touche pour continuer...

Et là, au miracle, ça fonctionne parfaitement !

Ma table est triée selon la clef primaire, c'est-à-dire la colonne 'col1'.
Et dans le explain, je n'ai plus, dans la colonne 'extra' l'information 'Using index', mais à la place 'NULL'.

Je me pose la question si je n'ai pas dans mysql, un bug de fonctionnement ???

Autre question : comment influencer une requête quand le choix fait pas mysql, ne me convient pas ?
En DB2, j'avais la 'plan table' qui me permettait d'influencer l'optimisation, mais avec mysql, je n'ai rien trouvé d'équivalent.

@+

[aieeeuuuuu]

Désolé de le dire, mais ce résultat me parait vraiment bizarre

Je ne connais pas suffisamment bien MySQL pour être affirmatif mais je pense que l’explication est la suivante :

Votre index clef2 porte sur la colonne col2. Mais il embarque également la clef primaire de la table afin de pouvoir accéder aux autres colonnes pour les requêtes qu'il ne couvrirait pas.
De fait, votre index clef2 contient également col1 en plus de col2, et couvre donc votre requête select * puisque la table n'a que ces deux colonnes. (vos deux index couvrent la requête, MySQL n'a que l'embarras du choix)

Ma table est triée selon la clef primaire, c'est-à-dire la colonne 'col1'.

C'est impropre de dire ça.
Il se trouve que le résultat de votre requête vous revient de cette façon, mais rien ne vous le garantit. Pour preuve : l'ordre n'est pas le même si vous ajoutez une colonne.
Comme déjà dit, il ne faut pas compter sur les index pour garantir un ordre dans le résultat de vos requêtes. Le plan peut changer a tout moment, et l'ordre des données avec.

[escartefigue]

En DB2, j'avais la 'plan table' qui me permettait d'influencer l'optimisation, mais avec mysql, je n'ai rien trouvé d'équivalent.
@+

Absolument pas !
La plan_table n'influence en rien les résultats, elle permet uniquement de recueillir les informations sur les choix faits par l'optimiseur

Pour influencer ces choix, il faut agir sur les statistiques, c'est à dire sur certaines tables SYSIBM.SYSxxxx pour DB2, ou l'équivalent dans les autres SGBD, qui n'ont rien à voir avec la plan_table

C'est vrai aussi pour MySQL : modifiez les stats (et notamment les keycard, le cluster-ratio, et les cards) et vous verrez que la même requete ne fonctionne plus du tout de la même façon, en terme de chemin d'accès :
Par exemple, une requete qui utilise l'index primaire mais n'est pas index only, sur une table à forte volumétrie, collez lui un keycard de 1 et relancez, vous verrez de suite la différence !

[Artemus24]

Salut aieeeuuuuu.

Vous n'avez pas bien regardé mon compte-rendu.
Dans mon premier exemple, j'ai créé une table avec seulement deux colonnes, dont la 'col1' est 'primary key' et dont la colonne 'col2' est 'unique index'.
Je constate qu'il force mon 'select *' à utiliser un indexe alors que je n'en ai pas besoin. Voir colonne 'extra' donnant le résultat 'Using index'.

Dans mon second exemple, j'ai repris la même table et j'ai ajouté la colonne 'col3' qui est aussi 'unique index'.
Et là, l'ordre du tri est revenu à la normale. C'est en cela que je trouve bizarre le comportement de MySql.

Si vous ne me croyez pas, faites le test.

Votre index clef2 porte sur la colonne col2. Mais il embarque également la clef primaire de la table afin de pouvoir accéder aux autres colonnes pour les requêtes qu'il ne couvrirait pas.

Et si je n'ai pas de 'primary key' dans ma table ? Non, votre hypothèse ne fonctionne pas.
Je crois que vous confondez le rôle joué par la 'primary key' avec le rôle de l'indexe.

De fait, votre index clef2 contient également col1 en plus de col2, et couvre donc votre requête select * puisque la table n'a que ces deux colonnes. (vos deux index couvrent la requête, MySQL n'a que l'embarras du choix)

Vous me donnez des explications qui ne sont pas la réalité du fonctionnement de MySql.

C'est impropre de dire ça.

Je sais, je ne m'exprime pas toujours très bien, ni clairement, et en ça, je m'excuse.

Il se trouve que le résultat de votre requête vous revient de cette façon, mais rien ne vous le garantit.

Mais si, c'est le rôle de la 'primary key'.
Si on ne met pas de 'primary key', MySql utilise le premier indexe qui trouve pour ordonner les lignes de la table.
S'il n'y a pas de 'primary key', ni d'indexes, l'ordre est celui imposée lors de l'insertion dans la table.

Tenez voici un exemple où j'illustre ce que j'avance :

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
--------------
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 primary key,
        `col2`  int UNSIGNED NOT NULL
)       ENGINE=InnoDB
        DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
        ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`col1`,`col2`) value (2,1),(1,3),(3,2)
--------------
 
--------------
select * from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    1 |    3 |
|    2 |    1 |
|    3 |    2 |
+------+------+
--------------
describe `test`
--------------
 
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1  | int(10) unsigned | NO   | PRI | NULL    |       |
| col2  | int(10) unsigned | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
--------------
truncate `test`
--------------
 
--------------
alter table `test` drop primary key
--------------
 
--------------
INSERT INTO `test` (`col1`,`col2`) value (2,1),(1,3),(3,2)
--------------
 
--------------
select * from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    2 |    1 |
|    1 |    3 |
|    3 |    2 |
+------+------+
--------------
describe `test`
--------------
 
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1  | int(10) unsigned | NO   |     | NULL    |       |
| col2  | int(10) unsigned | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
--------------
truncate `test`
--------------
 
--------------
alter table `test` add unique index (`col2`)
--------------
 
--------------
INSERT INTO `test` (`col1`,`col2`) value (2,1),(1,3),(3,2)
--------------
 
--------------
select * from test
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    2 |    1 |
|    3 |    2 |
|    1 |    3 |
+------+------+
--------------
describe `test`
--------------
 
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1  | int(10) unsigned | NO   |     | NULL    |       |
| col2  | int(10) unsigned | NO   | PRI | NULL    |       |
+-------+------------------+------+-----+---------+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Dans le premier test, j'ai l'ordre selon la 'primary key'.
Dans le deuxième test, j'ai l'ordre selon l'ordre d'insertion.
Dans le troisième tests, j'ai l'ordre selon la 'unique index' sur la colonne 'col2'. Pourquoi ? Car n'ayant pas de 'primary key', Mysql se sert de cet indexe pour ordonner les lignes.

Quand l'ordre est établi dans une table, elle ne change plus du tout.

Pour preuve : l'ordre n'est pas le même si vous ajoutez une colonne.

Ce n'est pas une preuve, mais un bug de comportement de MySql !

Comme déjà dit, il ne faut pas compter sur les index pour garantir un ordre dans le résultat de vos requêtes.

Un indexe, c'est un pointeur entre d'une part une valeur d'une colonne et d'autre part l'emplacement de la ligne où se trouve cette valeur.
Mais un indexe ne doit pas changer l'ordre de stockage des lignes dans une table, quand j'ai déjà une 'primary key'.

@+

[Artemus24]

Salut escartefigue.

J'ai l'impression que c'est le mot "influencer" qui te dérange, non ? J'aurai dû plutôt utiliser le mot "choisir".
Franchement, dès que je dis un mot de travers, tu ne pardonnes pas. Désolé si je ne choisie pas les bons mots.

Car après avoir testé plusieurs stratégies d'accès aux données, qui sont les influences auxquels j'ai procédé, le résultat est rangé dans la plan_table.
Donc à l'issu de mes tests, je choisie dans la plan table, le résultat qui me convient le mieux.

Le remplissage d'une ligne dans la plan table, se fait soit par l'ordre explain, ou soit par le bind.
Il n'y a pas que la modification des statistiques sur lesquelles on peut agir :
--> Il y a la dénormalisation de la base de données, voir création de nouvelles tables.
--> partitionner les tables.
--> réécriture de la requête.
--> ajouter des indexes.
--> Réorganisation de la table, voir de la base de données ==> la règle des trois R (reorg, Runstats et Rebind).
--> utiliser le hint pour forcer le chemin d'accès.
--> ajout d'une astuce comme celle de mettre 'or 1=0' dans une requête pour influencer l'optimiseur.

On n'est pas là, pour parler de ce que l'on peut faire en DB2.

Donc ma question était de savoir s'il existe un équivalent à la plan_table pour sélectionner les choix sous mysql ? Ou autre chose ?

Après quelques recherches, j'ai trouvé une astuce que je ne connaissais pas pour remédier à la mauvaise influence de cet index, dans mon premier exemple.
C'est de modifier la requête ainsi :

Code :

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

select * from test1 ignore index (clef2)

Et voici ce que j'obtiens :

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
--------------
explain select * from test1 ignore index (clef2)
--------------
 
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL |    9 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
--------------
SET profiling = 1
--------------
 
--------------
select * from test1
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    9 |    1 |
|    8 |    2 |
|    7 |    3 |
|    6 |    4 |
|    5 |    5 |
|    4 |    6 |
|    3 |    7 |
|    2 |    8 |
|    1 |    9 |
+------+------+
--------------
select * from test1 ignore index (clef2)
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    1 |    9 |
|    2 |    8 |
|    3 |    7 |
|    4 |    6 |
|    5 |    5 |
|    6 |    4 |
|    7 |    3 |
|    8 |    2 |
|    9 |    1 |
+------+------+
--------------
SHOW PROFILES
--------------
 
+----------+------------+------------------------------------------+
| Query_ID | Duration   | Query                                    |
+----------+------------+------------------------------------------+
|        1 | 0.00014925 | select * from test1                      |
|        2 | 0.00031300 | select * from test1 ignore index (clef2) |
+----------+------------+------------------------------------------+
--------------
SET profiling = 0
--------------
 
 
Appuyez sur une touche pour continuer...

Hormis le fait de mettre un 'order by' qui ne m'intéresse pas, comment puis-je résoudre la mauvaise influence de cet indexe 'clef2' ?
Et ne me dit pas que la solution est de supprimer l'indexe en question.

Par contre, quand j'ai trois colonnes dans ma table, le problème disparaît. Qu'est-ce que tu en penses de ce problèmes ?

@+

[escartefigue]

J'ai l'impression que c'est le mot "influencer" qui te dérange, non ? J'aurai dû plutôt utiliser le mot "choisir".
Franchement, dès que je dis un mot de travers, tu ne pardonnes pas. Désolé si je ne choisie pas les bons mots.

Effectivement si l'on écrit ce que l'on ne pense pas, et que l'on écrit pas ce que l'on pense, il arrive souvent que les réponses n'aillent pas dans le sens attendu

Il n'y a pas que la modification des statistiques sur lesquelles on peut agir :
--> Il y a la dénormalisation de la base de données, voir création de nouvelles tables.
--> partitionner les tables.
--> réécriture de la requête.
--> ajouter des indexes.
--> Réorganisation de la table, voir de la base de données ==> la règle des trois R (reorg, Runstats et Rebind).
--> utiliser le hint pour forcer le chemin d'accès.
--> ajout d'une astuce comme celle de mettre 'or 1=0' dans une requête pour influencer l'optimiseur.

Bien évidement qu'on peut refaire le DDL de la bases de données mais là on ne parle plus d'optimiser une requête, et c'est sans rapport avec le propos erroné que j'ai corrigé à savoir :

"En DB2, j'avais la 'plan table' qui me permettait d'influencer l'optimisation, mais avec mysql, je n'ai rien trouvé d'équivalent"

On n'est pas là, pour parler de ce que l'on peut faire en DB2.

C'est vous qui mentionnez DB2, encore une fois je répond à ce qui est écrit !


Pour en revenir à la question initiale, les tables ne contiennent que des colonnes index, les stratégies d'accès sont donc index only (inutile d'aller dans les data pour satisfaire le select) du coup le résultat est faussé.
Refaites des tables avec des colonnes non index et le même jeu d'essai, le résultat sera probablement différent

[aieeeuuuuu]

Vous n'avez pas bien regardé mon compte-rendu.

si si

Dans mon premier exemple, j'ai créé une table avec seulement deux colonnes, dont la 'col1' est 'primary key' et dont la colonne 'col2' est 'unique index'.
Je constate qu'il force mon 'select *' à utiliser un indexe alors que je n'en ai pas besoin. Voir colonne 'extra' donnant le résultat 'Using index'.

Il ne force rien du tout. Il fait juste un choix.
Il a deux solutions équivalentes, il en choisit une.

Dans mon second exemple, j'ai repris la même table et j'ai ajouté la colonne 'col3' qui est aussi 'unique index'.
Et là, l'ordre du tri est revenu à la normale. C'est en cela que je trouve bizarre le comportement de MySql.

Encore une fois, il n'y a pas d'ordre "normal". sans clause ORDER BY les données vous sont restituées telle quelle.
Si un index entre en jeu dans la requête, il est fort probable que l'on puisse constater un ordre dans les données, mais je le répète : vous n'en avez aucune garantie.

Et si je n'ai pas de 'primary key' dans ma table ? Non, votre hypothèse ne fonctionne pas.

Si vous n'avez pas de clef primaire dans la table, alors MySQL sera bien perdu et pourrait même vous envoyer un message d'erreur ! (là par contre, on peut effectivement parler d'un bogue de MySQL)
Et il va faire sa tambouille, en considérant votre index unique comme clef primaire certainement, ou un truc dans ce gout là. Pour combler ses lacunes, MySQL fait parfois preuve d'une imagination débordante

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

Il est clair que nous n'avons pas la même définition de ce que sont une clef primaire et un index !
Mais vous en tirez la conclusion trop hâtive que c'est moi suis suis dans l'erreur...
N'hésitez pas à aller faire un tour du coté des cours disponible sur ce site.

Il se trouve que le résultat de votre requête vous revient de cette façon, mais rien ne vous le garantit.

Mais si, c'est le rôle de la 'primary key'.

absolument pas !

Par contre, quand j'ai trois colonnes dans ma table, le problème disparaît. Qu'est-ce que tu en penses de ce problèmes ?

Non seulement ce n'est pas un problème,mais en plus il ne disparait pas !
Avec votre table à trois colonnes, au lieu de faire un SELECT *, faites un SELECT col1, col2 FROM test. Je pense que le résultat va vous surprendre.

[escartefigue]

Non seulement ce n'est pas un problème,mais en plus il ne disparait pas !
Avec votre table à trois colonnes, au lieu de faire un SELECT *, faites un SELECT col1, col2 FROM test. Je pense que le résultat va vous surprendre.

Ce qui corrobore mon post précédent ;-)

[Artemus24]

Salut à tous.

@ escartifigue : En somme, tu me reproche de ne pas être assez précis dans ma façon de m'exprimer.

@ tous : je confirme les propos tenu par aieeeuuuuu :

Pour combler ses lacunes, MySQL fait parfois preuve d'une imagination débordante

MySql est surtout imprévisible dans ces choix.

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 !

La bonne démarche avec MySql est de toujours faire un 'explain' pour savoir comment il va se comporter.
Il y a plusieurs écritures possibles d'une requête en fonction des indexes et des performances. Je reprends les différents exemples :

1) le "select *" classique.

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
--------------
explain select * from test1
--------------
 
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | test1 | index | NULL          | clef2 | 4       | NULL |    9 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
--------------
select * from test1
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    9 |    1 |
|    8 |    2 |
|    7 |    3 |
|    6 |    4 |
|    5 |    5 |
|    4 |    6 |
|    3 |    7 |
|    2 |    8 |
|    1 |    9 |
+------+------+

Ici, il utilise l'indexe 'clef2' alors qu'il aurait dû utiliser la 'primary key'.
Et le résultat est tri selon la colonne 'col2', c'est-à-dire selon l'indexe 'clef2'.

2) on force mysql à ne pas utiliser l'indexe 'clef2'.

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
--------------
explain select * from test1 ignore index (clef2)
--------------
 
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL |    9 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
--------------
select * from test1 ignore index (clef2)
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    1 |    9 |
|    2 |    8 |
|    3 |    7 |
|    4 |    6 |
|    5 |    5 |
|    6 |    4 |
|    7 |    3 |
|    8 |    2 |
|    9 |    1 |
+------+------+

Ici, le résultat est conforme à ce que j'attends.
L'explain indique qu'aucun indexe, pas même la 'primary key' n'a été utilisé.

3) on force mysql à utiliser la 'primary key'.

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
--------------
explain select * from test1 use index (primary)
--------------
 
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL |    9 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
--------------
select * from test1 use index (primary)
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    1 |    9 |
|    2 |    8 |
|    3 |    7 |
|    4 |    6 |
|    5 |    5 |
|    6 |    4 |
|    7 |    3 |
|    8 |    2 |
|    9 |    1 |
+------+------+

Le résultat est aussi conforme à ce que j'attends. Mais par contre, je demande à MySql d'utiliser la 'primary key', ce qu'il ne fait pas.
Si je mets 'force index (primary)', j'obtiens le même explain. Il ne tiens pas compte de ce que je lui indique.

4) on trie le résultat.

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
--------------
explain select * from test1 order by col1
--------------
 
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | test1 | index | NULL          | PRIMARY | 4       | NULL |    9 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
--------------
select * from test1 order by col1
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    1 |    9 |
|    2 |    8 |
|    3 |    7 |
|    4 |    6 |
|    5 |    5 |
|    6 |    4 |
|    7 |    3 |
|    8 |    2 |
|    9 |    1 |
+------+------+

Ici, tout est conforme à ce que j'attends, aussi bien le tri sur la colonne 'col1', que l'usage de la 'primary key' dans l'explain.

Questions :

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 ?

b) je suppose que vous considérez qu'il n'y a pas de bug mais juste un choix douteux fait par mysql. Est-ce bien cela ?

Il est clair que nous n'avons pas la même définition de ce que sont une clef primaire et un index !
Mais vous en tirez la conclusion trop hâtive que c'est moi suis suis dans l'erreur...
N'hésitez pas à aller faire un tour du coté des cours disponible sur ce site.

A part le fait que j'ai environ une dizaine d'année d'expérience en tant qu'administrateur DB2 sur gros système.
Je veux bien reconnaître que je n'ai pas du tout la même expérience avec MySql.

Le rôle des indexes dans une table est le même que la sélection multicritère.
Donc oui, on peut vider une table selon l'ordre stipulé dans un indexe.
Mais ce n'est pas de cela dont je parle, mais de la façon dont les lignes sont rangées physiquement sur le disque.

absolument pas !

Je vous ai donné un exemple mysql qui confirme ce que je dis, et vous trouvez que ce n'est pas suffisant.
Le rôle de la 'primary key' n'est pas que de rendre unique et non nul les différentes valeurs constituant la clef. Ça c'est sa définition.
Elle a aussi le rôle de rangement des lignes dans la table.
Je ne sais pas pourquoi mais vous vous figurez que l'insertion dans la table se fait n'importe comment.
Enfin, je comprends ainsi votre remarque sur la "non garantie" d'avoir la table trié.

Si vous n'obtenez pas le résultat escompté du vidage ('full scan') de la table, c'est que MySql fait des choix hasardeux, je dirais plutôt n'importe quoi.
Mais cela ne vient pas contredire ce que j'ai dit à ce sujet.
De ce fait, on est obligé de surcharger la requête sql pour obtenir le bon résultat et c'est en cela que je trouve bizarre le comportement de mysql.

Maintenant MySql, ce n'est pas du DB2, il y a d'autres règles de fonctionnement que je dois apprendre.

Avec votre table à trois colonnes, au lieu de faire un SELECT *, faites un SELECT col1, col2 FROM test. Je pense que le résultat va vous surprendre.

Vous avez raison ! J'ai test et et je retrouve le même problème que j'ai avec ma table test1. Voici ce que j'obtiens :

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
--------------
explain select col1, col2 from test2
--------------
 
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | test2 | index | NULL          | clef2 | 4       | NULL |    9 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
--------------
select col1, col2 from test2
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    9 |    1 |
|    8 |    2 |
|    7 |    3 |
|    6 |    4 |
|    5 |    5 |
|    4 |    6 |
|    3 |    7 |
|    2 |    8 |
|    1 |    9 |
+------+------+

@+

[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

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

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à

Sinon, Je ne reproche rien et n'ai aucune animosité, mais comme tout un chacun, je ne peux répondre qu'en fonction de la question posée
il est effectivement important d'être clair dans la rédaction pour que les réponses correspondent aux attendus du rédacteur .

[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 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 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 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.

@+