Discussion :

[Artemus24]

Salut à tous.

@ Escartefigue : Il s'agit du message #2 qui date du 11 octobre 2019 à 13H38 et non 11H38.
Je n'ai rien à dire de plus que vous avez déjà dit dans ce message.

L'index cluster est une notion physique, c'est l'index de rangement : ...

Oui, je suis d'accord.

... après réorganisation, les lignes sont agencées selon cet index.

Une nouvelle insertion peut très bien s'insérer entre deux lignes déjà existante, et provoquer, en cas de débordement, un split de la page.
Je suis désolé de le dire, mais ce n'est pas une réorganisation de la table !

Selon vous, on réorganise quoi ? l'ordre des lignes ?
Et bien non, car c'est juste une insertion entre deux lignes qui sont déjà triées selon l'index clusterisé.

Une réorganisation serait de faire un tri sur la totalité de la table, ce qui n'est pas ce qui est fait lors de l'insertion.
Et dans le cas d'une réorganisation, il n'y a pas de débordement, puisque chaque page va contenir exactement le taux de remplissage désiré par l'administrateur.

Là ou les colonnes de l'index cluster peuvent tout à fait être différentes de celles de la PK.

Je ne suis pas d'accord, non plus. L'index cluster ne peut pas être dissocié de la primary key dans MySql.
--> https://dev.mysql.com/doc/refman/8.0...dex-types.html

Un index clusterisé applique physiquement l'ordre de rangement des ligne dans la table.
Dire que l'on peut les dissocier, cela revient à dire que l'index clusterisé pointe sur des lignes qui sont rangées différemment de l'ordre du cluster.
Cela n'a pas de sens du point de vue de la performance, puisque l'index cluster sert à l'optimisation !

C'est même souvent un bon choix, tout dépend des types de requêtes effectuées sur la BDD

C'est la bonne façon de faire, en cas de balayage de la table selon un critère bien précis.
Il n'est pas toujours opportun de considérer qu'une primary key doit être une clef technique.

À rectifier cette assertion qui est totalement fausse

Elle n'est pas fausse. Encore une fois, tu parles des SGBDR en général et non en particulier de MySql.
J'insiste sur le fait que nous sommes dans le forum MySql et non dans un forum général sur les SGBDR.

L'identifiant primaire ou clef primaire ne saurait dépendre du SGBDR dans la mesure où, comme expliqué plus haut cette notion apparaît dès la modélisation conceptuelle et qu'on on la retrouve bien sur dans le modèle logique.

C'est toute la différence entre une modélisation théorique (ou logique et donc indépndant de toute notion technique), et une base de données pratique où l'on est confronté à des problèmes de savoir faire et de performance.

Vin d'iou le débat est parti loin

C'est la gueguerre entre les théoriciens et les pragmatiques, ce dont je suis.
Je ne suis pas trop regardant sur les normes à la condition de ne pas faire n'importe quoi.
Inversement, je m'intéresse à tout ce qui concerne l'optimisation et aux performances.
Et là, il est difficile de faire comprendre à certains qu'une base de données bien faite ne rime pas nécessairement avec une base de données performante.

ce n'est qu'ensuite que le sujet s'est élargi pour expliquer qu'il ne fallait aucunement confondre index cluster (considération technique et liée au SGBD) et identifiant primaire (considération qui apparaît dès le modèle conceptuel et sans lien avec le choix de tel ou tel SGBD)

C'est toi qui croit que l'on peut dissocier l'index cluster et la primary key. Ce n'est pas mon cas.
Je pense même que l'on peut faire abstraction de cela, en considérant que toute "primary key" est en faite un index cluster.

Ce sont sur des nuances que nous n'arrivons pas à nous mettre d'accord !

@+

[escartefigue]

C'est agaçant de devoir encore polluer un sujet avec ces discussions que je devine déjà sans fin, je vais donc faire court

Il suffit de relire ce que j'ai écrit plus haut sans déformer ni tronquer , ça suffira (je l'espère) à faire disparaitre ce genre de remarque :

Une nouvelle insertion peut très bien s'insérer entre deux lignes déjà existante, et provoquer, en cas de débordement, un split de la page.
Je suis désolé de le dire, mais ce n'est pas une réorganisation de la table !

Complètement sans rapport avec mes propos, et c'est même limite insultant de faire comme si j'avais pu proférer de telles sornettes !

[StringBuilder]

Ce qui distingue une PK d'une clef candidate unique et "not null", c'est que la PK se propage dans d'autres tables via les contraintes d'intégrité.

Là pour le coup c'est pas vrai.

Avec SQL Server on peut parfaitement utiliser une clé alternative (ici une simple contrainte unique) comme clé étrangère :

Code sql :

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
 
create table personne
(
	id int not null primary key clustered identity,
	matricule char(10) null unique,
	nom varchar(20)
);
 
create table pointage
(
	id int not null primary key identity,
	matricule char(10) not null references personne (matricule),
	horaire datetime2 not null default CURRENT_TIMESTAMP,
	unique clustered (matricule, horaire)
);
 
insert into personne (matricule, nom) values ('P1', 'Tom'), ('P2', 'Bob'), ('P3', 'Rob');
 
-- Attendre quelques millisecondes entre chaque requêtes sinon ça fait des doubles ;)
insert into pointage (matricule) values ('P1');
insert into pointage (matricule) values ('P3');
insert into pointage (matricule) values ('P2');
insert into pointage (matricule) values ('P1');
insert into pointage (matricule) values ('P3');

[StringBuilder]

Je ne suis pas d'accord, non plus. L'index cluster ne peut pas être dissocié de la primary key dans MySql.
--> https://dev.mysql.com/doc/refman/8.0...dex-types.html

Ca revient à dire que MySQL est en toujours au stade du fichier séquentiel indexé...

Un index clusterisé applique physiquement l'ordre de rangement des ligne dans la table.
Dire que l'on peut les dissocier, cela revient à dire que l'index clusterisé pointe sur des lignes qui sont rangées différemment de l'ordre du cluster.
Cela n'a pas de sens du point de vue de la performance, puisque l'index cluster sert à l'optimisation !

Dans la logique, une clé primaire est simplement unique et non nulle.
Rien n'oblige à avoir les données triées physiquement dans le même ordre.
En revanche, il n'est pas rare d'avoir une clé clustered différente de la PK (sauf dans MySQL puisque c'est apparemment impossible) organisées selon une colonne FK, typiquement pour regrouper sur le disque toutes les lignes d'une même commande par exemple, même si des lignes sont ajoutées 6 mois après la création des premières.

[Artemus24]

Salut StringBuilder.

Ca fait longtemps que tu n'es pas venu nous rendre visite dans le forum mysql.

Ce qui distingue une PK d'une clef candidate unique et "not null", c'est que la PK se propage dans d'autres tables via les contraintes d'intégrité.

Qu'est-ce que vous entendez par propagation ? Ce n'est pas clair du tout !
Je pense que vous ne savez pas à quoi sert un moteur comme InnoDB !

Là pour le coup c'est pas vrai.

Ca fait plaisir de constater que je ne suis pas le seul à constater que ses affirmations sont fausses.

Avec SQL Server ...

Vous êtes hors sujet car nous sommes dans le forum consacré à MySql et non à Microsoft SQL Server.
Ce qui est vrai pour l'un, ne l'est pas systématiquement pour l'autre (Je parle des SGBDR).

Ca revient à dire que MySQL en est toujours au stade du fichier séquentiel indexé...

MySql propose btree ou hash. Rien à voir avec des séquentiel indexé.

Ce qui m'a toujours paru bizarre dans MySql, l'index est stockée dans la table.
Il suffit de se rendre dans le répertoire "/data", pour voir qu'une table est stockée dans un seul fichier suffixé par ".ibd".
Pour obtenir un fichier par table, il faut positionner la variable "innodb_file_per_table" à 1.

Maintenant j'ignore comment est stocké les index dans le fichier ".ibd" et comment cela est géré sous MySql.

Dans la logique, une clé primaire est simplement unique et non nulle.

Oui, mais comme je l'ai dit précédemment, c'est un aspect méthodologique.

Rien n'oblige à avoir les données triées physiquement dans le même ordre.

Le même ordre que quoi ?

En revanche, il n'est pas rare d'avoir une clé clustered différente de la PK (sauf dans MySQL puisque c'est apparemment impossible) organisées selon une colonne FK, typiquement pour regrouper sur le disque toutes les lignes d'une même commande par exemple, même si des lignes sont ajoutées 6 mois après la création des premières.

Par exemple sous DB2 gros système IBM. Oui, je le savais.

Mais comme je suis dans le forum MySql, je parlais de MySql, pas d'un autre SGBDR.
Les quiproquo que l'on a fréquemment dans ce forum provient du fait que certains parlent des SGBDR en général et non en particulier de MySql.

Sous MySql, un index clusterisé est toujours associé à la primary key.

@+

[StringBuilder]

Ca fait longtemps que tu n'es pas venu nous rendre visite dans le forum mysql.

En effet, j'ai cliqué par erreur, vu qu'il semblait y avoir un combat de troll gratuit, je suis resté

Qu'est-ce que vous entendez par propagation ? Ce n'est pas clair du tout !
Je pense que vous ne savez pas à quoi sert un moteur comme InnoDB !

[…]

Ca fait plaisir de constater que je ne suis pas le seul à constater que ses affirmations sont fausses.

[…]

Vous êtes hors sujet car nous sommes dans le forum consacré à MySql et non à Microsoft SQL Server.
Ce qui est vrai pour l'un, ne l'est pas systématiquement pour l'autre (Je parle des SGBDR).

Là on nage en pleine mauvaise fois.
MySQL ne permet pas d'utiliser pour clé étrangère autre-chose que la clé primaire d'une table.
Donc si on ne parle que pour MySQL (c'est très réducteur de se limiter à l'un des pires SGBD en ce qui concerne la norme SQL), alors Escartefigue a tout à fait raison de dire que la PK est propagée à toutes les tables faisant référence à la table, soit on parle du cas général de la norme SQL, et mon exemple est parfaitement valable : bref, si l'un de nous deux a tord, l'autre a raison... voir les deux...

MySql propose btree ou hash. Rien à voir avec des séquentiel indexé.

Ce qui m'a toujours paru bizarre dans MySql, l'index est stockée dans la table.
Il suffit de se rendre dans le répertoire "/data", pour voir qu'une table est stockée dans un seul fichier suffixé par ".ibd".
Pour obtenir un fichier par table, il faut positionner la variable "innodb_file_per_table" à 1.

Maintenant j'ignore comment est stocké les index dans le fichier ".ibd" et comment cela est géré sous MySql.

Renseigne-toi sur ce qu'est le séquentiel indexé...
Quand à l'index cluster, c'est normal, si c'est comme avec SQL Server, il contient lui-même toute les données de la table. C'est d'ailleurs une astuce sous SQL Server pour déplacer une table d'un filegroup à un autre à chaud : il suffit de reconstruire l'index cluster sur le nouveau filegroup !

Oui, mais comme je l'ai dit précédemment, c'est un aspect méthodologique.

C'est un aspect tout aussi technique : tu ne peux pas créer une clé primaire sur une colonne contenant des doublons ou des nulls. Pas plus qu'il est possible de retrouver une ligne sans que celle-ci soit identifiable par un critère unique.

Le même ordre que quoi ?

Que la PK évidement... si d'une ligne à l'autre du perds le fil on va pas s'en sortir !

Par exemple sous DB2 gros système IBM. Oui, je le savais.

Mais comme je suis dans le forum MySql, je parlais de MySql, pas d'un autre SGBDR.
Les quiproquo que l'on a fréquemment dans ce forum provient du fait que certains parlent des SGBDR en général et non en particulier de MySql.

Sauf que parler de MySQL sans savoir ce qu'est un SGBD ni SQL, c'est la garantie de faire de la merde (déjà qu'à la base on n'est pas aidé par l'outil, alors si en plus on ne sait pas où se situent ses limites, on n'est pas près de s'en sortir).

Sous MySql, un index clusterisé est toujours associé à la primary key.

Ou comment MySQL a renommé "séquentiel indexé" en "InnoDB".

Et j'ai envie d'aller plus loin, car MySQL ment sur la notion de clustered index dans sa documentation : il y a forcément un clustered index dans la base, et il est, dans cet ordre, selon la présence ou non : la clé primaire, le premier index unique trouvé, ou une clé primaire interne, générée automatiquement sur une colonne cachée à l'image du rowid d'Oracle.
Bref : on ne peut pas choisir l'index cluster, pas plus qu'on ne peut empêcher MySQL d'en créer un. Tout simplement car il n'y a pas d'index clustered, juste que le moteur a besoin de stocker les données sur le disque en suivant l'ordre d'un index, et qu'il est incapable d'utiliser un index autre que celui qu'il a choisi d'utiliser.
Reste à savoir ce que ça fait si on crée la table avec un index unique, puis qu'on crée une clé primaire par la suite sur une autre colonne : si c'est possible, est-ce une façon cachée de customiser l'index cluster ?

[SQLpro]

...
Ce qui distingue une PK d'une clef candidate unique et "not null", c'est que la PK se propage dans d'autres tables via les contraintes d'intégrité.

Non, mais ça c'est faux ! N'importe quelle clef, qu'elle soit PRIMAIRE ou UNIQUE est utilisable à titre de contrainte d'intégrité référentielle déclarative. Ce n'est donc pas LA CLEF PRIMAIRE qui se propage mais une des clefs (primaire ou alternative) qui se propage !

Cela fait partie de la norme SQL depuis l'origine et c'est même dans le papier original de Codd de 1970 :
https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

A +

[tanaka59]

Bonjour,

Non, mais ça c'est faux ! N'importe quelle clef, qu'elle soit PRIMAIRE ou UNIQUE est utilisable à titre de contrainte d'intégrité référentielle déclarative. Ce n'est donc pas LA CLEF PRIMAIRE qui se propage mais une des clefs (primaire ou alternative) qui se propage !

Cela fait partie de la norme SQL depuis l'origine et c'est même dans le papier original de Codd de 1970 :
https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

A +

Merci pour ce lien fort intéressant !

A noter pour ces histoires de primary & unique & index

En MySQL on note index ou key ... en soit avoir 2 fois la même instruction sous 2 labels différents est tout simplement incompréhensible . On a le même exemple avec le substr versus substring (quand les 2 instructions sont acceptées ... )

Savoir si l'on doit utiliser key ou index quand on change de SBGD c'est comme jouer à la roulette russe ... Celui qui commence en MySQL sera pollué par des règles et normes non respectées ! Résultat il ne va pas savoir ce qu'il utiliser ...

[Artemus24]

Salut StringBuilder.

Là on nage en pleine mauvaise fois.

Il faudrait développer un peu avant de dire que je suis de mauvaise foi.
Car pour l'instant, que ce soit Escartefigue, SQPRO ou toi-même, je ne comprends pas ce que vous entendez par propagation.

MySQL ne permet pas d'utiliser pour clé étrangère autre-chose que la clé primaire d'une table.

Oui, je le sais et en quoi cette affirmation vient me contredire ?

Donc si on ne parle que pour MySQL (c'est très réducteur de se limiter à l'un des pires SGBD en ce qui concerne la norme SQL), alors Escartefigue a tout à fait raison de dire que la PK est propagée à toutes les tables faisant référence à la table, soit on parle du cas général de la norme SQL, et mon exemple est parfaitement valable : bref, si l'un de nous deux a tord, l'autre a raison... voir les deux...

Définissez moi ce que vous entendez par "propagation" car je ne comprends pas ce en quoi j'ai tort.

Renseigne-toi sur ce qu'est le séquentiel indexé...

L'organisation des bases de données n'est pas basée sur le séquentiel indexé. Donc je ne comprends pas où vous voulez en venir.

Quand à l'index cluster, c'est normal, si c'est comme avec SQL Server, il contient lui-même toutes les données de la table.

Je n'ai pas dit que ce n'est pas normal, mais que cela me parait bizarre d'avoir au même endroit les index et les data. Pourquoi ?
Parce que le minimum de ce que l'on lit sur un disque dur est un cylindre. Que celui-ci est composé de plusieurs pistes.
Qu'une page est le minimum de stockage des index ou des data. Et qu'une page peut être à cheval sur plusieurs piste, mais doit être contenu entièrement dans un cylindre.
On retrouve cette même notion sous un nom différent dans DB2 avec les CI (control interval) et les CA (control area) ainsi que les pages.

Il me semble logique de regrouper dans un même cylindre toutes les pages index.
Ce qui fait que l'accès aux index est optimisée parce qu'un minimum de lectures physiques sera nécessaire pour les obtenir. Après, ces index sont chargés en mémoire.

Maintenant, s'il y a présence dans un même cylindre, des pages index avec des pages data, vu que le minimum de la lecture physique est le cylindre, nous ne sommes plus dans un minimum du nombre de lectures physiques pour l'obtention de tous les index.
C'est en cela que je trouve cette organisation bizarre.

Sous DB2, on parle de tablespace et d'indexspace, non ? Donc séparation physique des data d'avec les index.
--> https://www.ibm.com/support/knowledg...ndexspace.html

C'est un aspect tout aussi technique

Enfin quelqu'un qui reconnait que c'est aussi un aspect technique.
Le seul fait d'avoir une clef primaire (hormis l'unicité et l'absence de null), ne permet pas de dire sur quel critère les lignes sont triées dans une table. Pourquoi ?
Car l'index cluster ne s'applique pas de la même façon selon les SGBDR. Dans MySql, il est toujours associé à la clef primaire. Ailleurs, il peut être indépendant de la primary key.
Cet aspect est primordial pour l'optimisation des requêtes. C'est là où je voulais en venir, à l'optimisation.

Que la PK évidement... si d'une ligne à l'autre du perds le fil on va pas s'en sortir !

Ta réponse est fausse. Quel que soit le SGBDR, les lignes sont triées selon l'index cluster.
Pourquoi affirmer que c'est selon la primary Key, si à juste titre, on dissocie l'index cluster de la primary key ?

déjà qu'à la base on n'est pas aidé par l'outil, alors si en plus on ne sait pas où se situent ses limites, on n'est pas près de s'en sortir

Je ne te dirais pas le contraire au sujet de MySql, voire même de MariaDB.

Quand tu as l'habitude d'un bon SGBDR comme Microsoft SQL Server, cela devient vite un casse tête de se mettre à un SGBDR qui ne fait pas ce dont tu as l'habitude de faire.
Quand on fait du MySql, il faut raisonner MySql et non pas Microsoft SQL Server. C'est juste du bon sens !

Ou comment MySQL a renommé "séquentiel indexé" en "InnoDB".

Bon ben là, tu m'apprends quelque chose.

Et j'ai envie d'aller plus loin

Je ne demande que ça !

MySQL ment sur la notion de clustered index dans sa documentation

J'ai lu la doc et cet aspect n'est pas très clair.

Il y a fort longtemps dans ce forum, j'avais constaté un truc qui ne me semblait pas du tout logique. Le voici :

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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
drop table if exists `test`
--------------
 
--------------
CREATE TABLE `test`
( col1  integer unsigned NOT NULL primary key,
  col2  integer unsigned NOT NULL,
  unique index (`col2`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`col1`,`col2`) value (2, 7), (5, 4), (9, 1), (8, 3), (6, 6), (3, 8), (1, 9), (4, 5), (7, 2)
--------------
 
--------------
select * from `test`
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    9 |    1 |
|    7 |    2 |
|    8 |    3 |
|    5 |    4 |
|    4 |    5 |
|    6 |    6 |
|    2 |    7 |
|    3 |    8 |
|    1 |    9 |
+------+------+
--------------
drop table if exists `test`
--------------
 
--------------
CREATE TABLE `test`
( col1  integer unsigned NOT NULL primary key,
  col2  integer unsigned NOT NULL,
  col3  integer unsigned NOT NULL,
  unique index (`col2`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`col1`,`col2`,`col3`) value (2, 7, 4), (5, 4, 8), (9, 1, 3), (8, 3, 7), (6, 6, 9), (3, 8, 1), (1, 9, 2), (4, 5, 6), (7, 2, 5)
--------------
 
--------------
select * from `test`
--------------
 
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    9 |    2 |
|    2 |    7 |    4 |
|    3 |    8 |    1 |
|    4 |    5 |    6 |
|    5 |    4 |    8 |
|    6 |    6 |    9 |
|    7 |    2 |    5 |
|    8 |    3 |    7 |
|    9 |    1 |    3 |
+------+------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Dans ces deux exemples, le seule diférence est l'ajout de la colonne "col3".
Pourquoi dans le premier exemple, le select tri les lignes selon la colonne "col2".
Alors que dans le second exemple, le select tri les lignes selon la colonne "col1", c'est-à-dire la primary key.
Selon moi, c'est un bug car MySql choisit la colonne "col2" qui est l'index unique et non la primary key.
Dans le premier exemple, MySql se comporte comme si la Primary Key n'existe pas.
Dans tous les cas, quand la primary key existe, le tri doit être fait selon cette colonne.

@+

[al1_24]

Dans tous les cas, quand la primary key existe, le tri doit être fait selon cette colonne.

Faux.
Dans tous les cas le tri doit être fait selon la clause ORDER BY. Tout autre tri est aléatoire.

[Artemus24]

Salut Al1_24.

Voilà une réflexion stupide !
Je donne un exemple, où l'on constate que le comportement du select est hasardeux.
Je dis que dans ce contexte, le résultat aurait dû être l'ordre selon la clef primaire et de surcroît celui de l'index cluster, puisque MySql ne fait pas la distinction.
Et tout ce que vous trouvez à dire, c'est selon le "order by".

Mais où avez-vous vu un "order by" dans mon exemple ?

@+

[al1_24]

La seule et unique manière d'ordonner le résultat d'une requête est l'usage de la clause ORDER BY.
Il n'y a pas d'ordre naturel dans une table. Pas plus la clé primaire qu'un quelconque index cluster. S'appuyer sur cette éventualité ne peut conduire qu'à des résultats imprévisibles.

Donc votre affirmation (que je répète pour que l'on parle bien de la même chose) est erronée.

Dans tous les cas, quand la primary key existe, le tri doit être fait selon cette colonne.

[Artemus24]

Salut Al1_24.

La seule et unique manière d'ordonner le résultat d'une requête est l'usage de la clause ORDER BY.

Si l'on désire ordonner un résultat final, oui, je suis d'accord avec vous. Mais ce n'est pas ce que je cherche à faire.
Dans mes deux exemples, je m'attendais à obtenir le même critère de tri. Or je constate que ce n'est pas le cas !

Et donc :

Dans tous les cas, quand la primary key existe, le tri doit être fait selon cette colonne.

Le "dans tous les cas" se rapportent à la présence d'une clef primaire faisant office d'index cluster, et pas de "order by".

@+

[StringBuilder]

Artemus24, relis la norme SQL. Cherche pas plus loin que la première version, car ça n'a pas changé depuis.

L'ordre des lignes retournées par une clause SELECT en l'absence d'une clause ORDER BY n'est ni garanti, ni constant dans le temps

Lorsqu'on effectue un SELECT sans ORDER BY, le SGBD, quel qu'il soit, retourne les lignes "dans l'ordre qui l'arrange".

Dans 99% des cas, on aura effectivement des données dans le même ordre que l'index cluster.
Mais ce n'est en rien une garantie :
- Si des données étaient en cache mémoire, elles peuvent être retournées avant celles lues sur le disque
- Si la table est répartie sur plusieurs disques par exemple (partitionnement), tous les disques sont lus en même temps, et donc les lignes restituées par le SELECT sont mélangées au fur et à mesure qu'elles sont lues sur chaque disque
- Si une clause quelconque (WHERE, JOIN, etc.) utilise un index, les lignes retournées seront généralement retournées dans l'ordre du-dit index
- Si une clause DISTINCT ou GROUP BY (qui ne garantissent pas non plus quel qu'ordre que ce soit) est précisée, les données seront généralement retournée dans l'ordre des colonnes spécifiées dans la clause en question
- Etc.

RIEN, absolument RIEN ne permet de prévoir l'ordre des lignes dans une clause SELECT en l'absence d'une clause ORDER BY.

C'est une hérésie de croire le contraire, et ça devrait même être un délit d'affirmer le contraire.

Sur une grosse base de données en pleine charge, on constate même souvent que l'ordre change d'une interrogation à l'autre, même si elles ne sont espacées que de quelques secondes !

[Artemus24]

Salut StringBuilder.

Je suis toujours obligé de me justifier alors que vous ne cherchez pas à me comprendre. Ca devient franchement pénible !!!

Tout ce que tu me dis, je le sais déjà ! Cela m'a déjà été expliqué plusieurs fois, avec toujours les mêmes arguments. Et on revient toujours aux mêmes remarques désobligeantes !

Or je ne fais que répondre à cette interrogation :

Bref : on ne peut pas choisir l'index cluster, pas plus qu'on ne peut empêcher MySQL d'en créer un. Tout simplement car il n'y a pas d'index clustered, juste que le moteur a besoin de stocker les données sur le disque en suivant l'ordre d'un index, et qu'il est incapable d'utiliser un index autre que celui qu'il a choisi d'utiliser.
Reste à savoir ce que ça fait si on crée la table avec un index unique, puis qu'on crée une clé primaire par la suite sur une autre colonne : si c'est possible, est-ce une façon cachée de customiser l'index cluster ?

Je donne un simple exemple des plus basiques où je m'attendais au même comportement.

Voici le contexte :

a) la base de données est détruite avant d'être recréé.

b) il y a qu'une seule exécution de mon script, donc la cache mémoire était vierge avant le lancement de ce script.

c) Je suis dans un environnement de test où je suis le seul utilisateur. Personne ne vient perturber ma base de données.

d) la table est sur un seul disque et il n'y a pas de partitions.

e) il n'y a pas de distinct, de group by, de order by, encore moins une clause where ou je ne sais quoi d'autre qui pourrait perturber le comportement du select.

f) la seule différence est l'ajout d'une troisième colonne "col3" où le comportement du select est différent.

Le comportement de mon exemple, dans mon environnement est prévisible.
J'ai beau relancer 1 million de fois le même script, j'aurai toujours le même comportement.

Je ne parle pas dans un contexte de production, en pleine charge, avec des tas d'utilisateurs, de la mémoire cache en pagaille, d'une table partitionnée sur plusieurs disques, du multithreading et je ne sais quoi d'autre qui donnerait au select un comportement aléatoire lors de plusieurs interrogations.
Ok ! Est-ce que vous avez tous compris le contexte ou pas ?

Dans 99% des cas, on aura effectivement des données dans le même ordre que l'index cluster.

Je suppose que tu n'as pas pris le temps d'analyser mes deux exemples.
Tu aurais pu constater que l'ordre, dans un des exemples, n'est pas selon la clef primaire (qui est l'index cluster), mais selon l'index unique.

Et tu constateras par toi-même que dans mes deux exemples, c'est la même clef primaire, avec les mêmes données.
Il y a juste dans le second exemple, l'ajout d'une troisième colonne qui vient tout perturber.

Je confirme par cet exemple basique que le comportement de MySql est erratique vis-à-vis de la documentation.
Ou si tu préfères, je suis d'accord avec toi quand tu dis :

car MySQL ment sur la notion de clustered index dans sa documentation

MySql, pour je ne sais quelle raison, vient à privilégier l'index unique vis-à-vis de la clef primaire.

Ce sont toujours des prises têtes avec vous tous !

@+

[escartefigue]

N'importe quelle clef, qu'elle soit PRIMAIRE ou UNIQUE est utilisable à titre de contrainte d'intégrité référentielle déclarative. Ce n'est donc pas LA CLEF PRIMAIRE qui se propage mais une des clefs (primaire ou alternative) qui se propage

Tout à fait et c'est vrai pour tous les SGBD, je voulais dire que la PK est propagée par défaut

[escartefigue]

Ce sont toujours des prises têtes avec vous tous !

ajouter une colonne n'est pas neutre, ça peut modifier la notion d'index couvrant ou pas (mais ce n'est pas le cas ici puis qu’aucun index n'est couvrant) mais ça modifie aussi le nombre de lignes par page et donc potentiellement la stratégie de l'optimiseur, ce d'autant plus que les insertions n'ont pas été faites selon l'ordre de l'index cluster et qu'il n'y a pas eu de réorg.

Ces préoccupations et les explications afférentes avaient été largement documentées dans ce fil que vous aviez engagé il n'y pas si longtemps
https://www.developpez.net/forums/d1.../probleme-tri/

Comme mes collègues, je confirme que seule la clause ORDER BY permet d'obtenir un ordre stable des lignes restituées

[aieeeuuuuu]

bonjour,

Dans tous les cas, quand la primary key existe, le tri doit être fait selon cette colonne.

Non, ce serait au mieux identique, au pire contre performant !

Dans le premier exemple, le moteur dispose de deux indexes :
1/ l'index cluster, ordonné selon la clef primaire, et contenant donc la colonne col1 (clef de l'index) ET la colonne col2 (car c'est un index cluster, qui contient donc toutes les colonnes de la table)
2/ l'index non cluster, ordonné selon la colonne col2 et contenant donc la colonne col2 (clef de l'index) ET la colonne col1 (clef primaire de la table). En effet, il faut noter que les indexes non cluster "embarquent" la clef primaire de la table pour pouvoir retrouver la ligne correspondante afin d'aller chercher les éventuelles colonnes manquantes pour répondre à la requête.

Il se trouve ici que l'index sur col2 contient donc les deux colonnes col1 et col2, ce qui est suffisant pour répondre à la requête (pas besoin d’accéder à l'index cluster pour aller chercher d'autres colonnes). On parle alors d'index couvrant.
Dans le cas présent, le moteur à donc deux choix parfaitement identiques : les deux indexes sont de même taille, il contiennent les deux colonnes col1 et col2, et rien d'autre. Utiliser l'index cluster, ou l'index non cluster pour répondre à la requête sont deux choix possibles, identiques en cout, le moteur opte donc pour l'index non cluster.

Et ce choix est logique : l'index non cluster sera toujours de taille inférieure ou égale à l'index cluster, jamais plus gros. :
Admettons qu'on ajoute une colonne col5 à votre table. Cette colonne supplémentaire se retrouvera dans l'index cluster, mais pas dans l'index non cluster. celui-ci sera donc plus petit que celui-là.
Dans ce cas, pour votre première requête (il faudra alors spécifier les noms de colonnes pour qu'elle reste équivalente : SELECT col1, col2 FROM...) l'index non cluster sera plus efficace que l'index cluster car il engendrera moins de lectures.

Ce que j'ai mis en gras explique pourquoi votre affirmation est fausse : si un index secondaire couvre la requête, alors il est logique de l'utiliser en priorité sur l'index cluster, car il a de forte chances d'être moins couteux que l'index cluster, qui embarque potentiellement des informations inutiles pour répondre à la requête, avec donc un cout IO supérieur.

EDIT : en revanche pour le second exemple, l'index non cluster ne contient pas la colonne col3, il ne couvre pas la requête, et son utilisation obligerait à utiliser également l'index cluster, il ne présente donc aucun intérêt. Mais je pense qu'en ajoutant col3 à la clef de l'index non cluster (ou en en créant un nouveau contenant les deux deux colonnes), alors le tri se ferait de nouveau sur col2...

[escartefigue]

En effet, il faut noter que les indexes non cluster "embarquent" la clef primaire de la table pour pouvoir retrouver la ligne correspondante afin d'aller chercher les éventuelles colonnes manquantes pour répondre à la requête.

ce n'est pas le cas de tous les SGBD, mais ici on est dans la section "MySQL" et c'est LA raison

[StringBuilder]

Le comportement de mon exemple, dans mon environnement est prévisible.
J'ai beau relancer 1 million de fois le même script, j'aurai toujours le même comportement.

Je ne parle pas dans un contexte de production, en pleine charge, avec des tas d'utilisateurs, de la mémoire cache en pagaille, d'une table partitionnée sur plusieurs disques, du multithreading et je ne sais quoi d'autre qui donnerait au select un comportement aléatoire lors de plusieurs interrogations.

Il est prévisible car ton environnement est stable : même version, même données, même charge, même paramétrage.
Rien ne garanti que ceci soit vrai dans une version différente du SGBD, avec d'autres données, avec une charge concurrente, ou des paramétrages différents.

Donc il n'y a aucune raison de vouloir faire la moindre conclusion, ni même de chercher à comprendre pourquoi le résultat vous semble étonnant : il ne l'est pas. Et il sera probablement différent dans un contexte

Ok ! Est-ce que vous avez tous compris le contexte ou pas ?

Non, toi, est-ce que tu as compris que ton exemple ne sert à rien et que ton interrogation est dénuée de sens ?

Je suppose que tu n'as pas pris le temps d'analyser mes deux exemples.
Tu aurais pu constater que l'ordre, dans un des exemples, n'est pas selon la clef primaire (qui est l'index cluster), mais selon l'index unique.

Et tu constateras par toi-même que dans mes deux exemples, c'est la même clef primaire, avec les mêmes données.
Il y a juste dans le second exemple, l'ajout d'une troisième colonne qui vient tout perturber.

Si, je vois bien que l'ordre est différent.
Tout comme l'ordre sera encore probablement différent si tu fais un select col3 from test et que tu as un index (non unique, pas la peine) sur col3.

Le SELECT retourne les données dans l'ordre qu'il l'arrange, pas selon l'index cluster, la clé primaire, ni même l'âge du cheval blanc du capitaine !
Même les développeurs de MySQL sont probablement incapables de prévoir avec certitude l'ordre retourné par un SELECT dans un contexte donné.

Je confirme par cet exemple basique que le comportement de MySql est erratique vis-à-vis de la documentation.

Non, tu ne confirmes rien du tout, car la documentation parle de la manière dont est déterminé l'index cluster à sa création, et n'indique absolument pas qu'un SELECT sera trié selon l'ordre de l'index.


Sinon, juste un truc : dans ton exemple, tu crées une transaction, et tu ne commit pas avant d'interoger les données.

Il est fort à parier que cela a un réel impact sur les résultats que tu obtiens : les donnés n'étant pas validées, elle ne sont peut-être même pas écrites à leur place dans la table, et non inscrites dans les index...
Je ne sais pas du tout comment fonctionne MySQL, mais si ça se trouve tes lignes ne sont même pas stockées dans le fichier de la table !