Discussion :

[Artemus24]

Salut à tous.

j’ai attendu la V1R2 (1986) pour enfin faire du tuning autrement qu’en aveugle

A cette époque là, j'étais encore ingénieur d'étude et je faisais beaucoup de développement cobol / jcl.

voyez l’exemple de la banque d'une ville méridionale où il fait bon vivre...

Je l'ai lu. Très intéressant comme expérience ! Et c'est pourquoi, il ne faut pas laisser n'importe qui faire n'importe quoi.
J'ai déjà été confronté à des problèmes similaire, comme la fois où un pseudo DBA croyait bien faire et dégradait les performances de l'ensemble d'un projet, juste pour améliorer la performance d'une lourde requête.

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

Je ne confronte pas théorie d'un coté et technique de l'autre. Ce sont juste des aides pour parvenir au but recherché.
Quand je dis que je suis pragmatique, je veux signifier que je ne prends jamais rien pour argent comptant.
De ce fait, je vérifie et j'expérimente toujours avant de faire une modification. Et même après la modification, je vérifie l'impacte que cela a sur le reste du projet.
Mon credo reste toujours le juste équilibre entre d'une part le respect de la normalisation et d'autre part le cout en volumétrie et en temps d'exécution, autrement dit en performance.

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

Oui, manque de précision de ma part. Je ne parle pas des formes normales que je respecte, mais des normes SQL.
Quand on travaille avec un SGBD, la norme SQL n'est pas toujours respectée et seul le fonctionnement du SGBD fait preuve de foi. Donc on fait avec !
Ce qui signifie que d'un SGBD (DB2) à l'autre (MySql), on peut se retrouver avec des normes différentes, dues à des évolutions différentes dans le temps.
Et je n'aime pas trop que l'on dise du mal de MySql car certains membres ayant fait l'usage d'un SGBD plus respectueux des normes SQL se retrouvent en panne d'imagination pour trouver la solution sous MySql.

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.

A vrai dire, je ne connais pas la norme SQL, mais juste la façon dont elle est appliquée dans DB2. C'est DB2 que je connais et non la norme SQL.
Je recherchais à l'identique ce que je connaissais de DB2 mais dans MySql. Voilà pour les explications.

Non ! il aurait pû. ça fait toute la différence.

Je comprends ce que vous me dites, mais quand on fait un "select * from test" par exemple, je m'attends à obtenir un 'full scan' de ma table triée selon l'ordre de ma "primary key' et non sur un autre indexe. C'est pas logique !
Mais quand je compare le résultat produit par cette requête, juste en changeant "InnoDB' par "Memory' ou encore 'MyIsam', je suis surpris de ne pas obtenir la même chose.
C'est en cela que je dis qu'il y a un bug de fonctionnement.

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

J'ai bien compris que la solution est de faire "select * from test order by clef", juste pour influencer l'optimiseur à faire l'usage de la 'primary key'.
J'ai testé autre chose (les hints) afin de voir ce que je pouvais obtenir, mais ce n'est pas la solution à envisager, enfin dans cet exemple là.

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.

J'en conviens que cela ne m'est jamais encore arrivé et cela peut surprendre d'avoir de l'aléatoire dans le fonctionnement d'un SGBD.
Il se peut que l'on rencontre des problèmes forts différents entre le gros système et la micro.

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.

Je ne parle pas du tri, dans le sens de l'usage du 'filesort', mais de l'ordre apparent à l'affichage qu'il faut distinguer de l'ordre réel dans le 'table space'.
Si vous faites un vidage de votre 'table space' par un utilitaire, vous devriez observer que les lignes ne sont pas stockées dans l'ordre en fonction de votre 'primary key' mais en fonction du 'rowid'. Je nomme cela l'ordre réel.
Si maintenant vous faites un 'select * from test', ce n'est pas l'ordre réel que vous obtenez, mais l'ordre apparent selon l'indexe qui est en usage (voir l'explain) à cet instant.
La 'primary key' est un indexe comme les autre. Alors à quoi sert la primary key et pourquoi faire une distinction avec les autre indexes ?
Elle sert à faire le lien entre l'emplacement réel de la ligne dans le 'table space', et l'ordre apparent selon cette 'primary key'.
Et c'est pourquoi, quand on crée un indexe, celui-ci fait toujours référence à la primary key.

Si à chaque insertion, modification ou suppression, MySql se réorganiserait alors l'ordre réel serait toujours en conformité avec l'ordre apparent de la 'primary key'.
Et de ce fait, une réorganisation de la table et de surcroit du 'table space' ne servirait à rien ! Or il n'y a pas de réorganisation automatique.
Il faut comprendre que les lignes dans un 'table space' sont dans un ordre quelconque, disons aléatoire.
Mais quand on fait un 'select', l'ordre apparent est toujours selon l'ordre soit de la 'primary key', soit d'un l'indexe.
S'il n'y a pas de 'primary key' ou d'indexe alors l'ordre apparent est en conformité avec l'ordre réel.

Je pense que vous ne m'avez pas compris sur ce sujet, où d'une part je ne parle pas de tri, mais d'ordre (réel et apparent) et d'autre part la façon dont le SGBD s'organise d'une manière aléatoire pour stocker les lignes dans le 'table space'.

Si je me suis trompé dans mes explications, donnez moi un exemple qui prouve que j'ai tort.
D'ailleurs, connaissez vous un utilitaire qui fasse le vidage "physique" d'un 'table space' ? Je ne parle pas, bien sûr, de mysqldump.

@+

[SQLpro]

Il faut comprendre que les lignes dans un 'table space' sont dans un ordre quelconque, disons aléatoire.
[...]
Je pense que vous ne m'avez pas compris sur ce sujet, où d'une part je ne parle pas de tri, mais d'ordre (réel et apparent) et d'autre part la façon dont le SGBD s'organise d'une manière aléatoire pour stocker les lignes dans le 'table space'.

En fait il n'y a rien d'aléatoire... Un processus aléatoire est, selon le Larousse : "soumis au hasard, dont le résultat est incertain". En matière de SGBDR le résultat est certain et déterministe. Il est néanmoins donné dans un ordre ARBITRAIRE !

A +

[Artemus24]

Salut à tous.

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.

Je vais détailler ma pensé à ce sujet.

1) comme je l'ai dit dans un message précédent, quand je fais un "select * from test", je m'attendais à l'ordre apparent selon la 'primary key'.
D'où le test que j'ai fait en passant de 'innodb' à 'memory' ou à 'myisam'. et je constate que 'innodb' ne fonctionne pas comment les deux autres moteurs.
Pourquoi cette différence de fonctionnement ?

2) oui, j'ai bien compris que la solution donnée par escartefigue (que je remercie au passage), consiste à mettre 'order by clef' sur la requête 'select * from test', non pas pour trier le résultat, mais juste demander à mysql de choisir le bon indexe, entre autre la 'primary key'.
Et ma question qui demeure sans réponse actuellement, est la suivante : est-ce la seule solution possible à mon problème ?
N'y a-t-il pas un quelconque paramétrage à mettre en place dans le fichier 'my.ini' pour ne pas avoir continuellement ce problème, à savoir écrire ma requête ainsi "select * from test order by clef" pour obtenir l'ordre apparent sur la "primary key" ?

3) pourquoi mysql prend-il la décision de sélectionner le premier indexe, autre que la 'primary key' ?
C'est ça qui me pose problème ! Bon, si MySql procède ainsi, je ferai avec.

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

4) J'ai fait des tas de tests et le 'hint' ne fonctionne pas toujours.

a) Quand j'ai juste une 'primary key' dans ma table, j'obtiens le bon résultat :

Code :

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

1
2
3
4
5
6
7
8
9
--------------
explain select * from test1
--------------
 
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test1 | index | NULL          | PRIMARY | 4       | NULL |    9 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

b) Toujours sur la même table, si je lui demande de ne pas faire l'usage de la 'primary key', voici ce que j'obtiens :

Code :

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

1
2
3
4
5
6
7
8
9
--------------
explain select * from test1 IGNORE 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  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

A priori, l'optimiseur n'indique aucun indexe. Je ne sais quoi penser de ce résultat.

c) Toujours dans ma table, j'insère des lignes, puis ensuite, j'en supprime quelques unes et je les ré-insère.
C'est juste pour avoir un jeu d'essai un peu plus réel.

d) je fais le vidage de ma table selon le cas a) et le vidage de ma table selon le cas b), et j'obtiens le même résultat. C'est normal !
Mais alors pourquoi par l'explain, j'obtiens deux résultats différents, dont l'un voudrait signer que je passe par la 'primary key' et l'autre pas aucun indexe ?
Dans le cas b), j'aurai dû (???) obtenir l'ordre réel et l'ordre apparent.

Alors je conclu que le résultat de l'explain, pour le cas a) et le cas b) sont totalement similaire.

Est-ce que je fais une erreur d'interprétation ?

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

A bien comprendre votre exemple, jadis, on avait encore l'ordre réel de stockage, si on ne précisait pas la clause 'order by'.
Alors que maintenant, on n'a plus du tout l'ordre réel, mais juste l'ordre apparent.

Je comprends ! Mais c'est quand même différent de la sélection de tel ou tel indexe par l'optimiseur de MySql, non ?

@+

[SQLpro]

A bien comprendre votre exemple, jadis, on avait encore l'ordre réel de stockage, si on ne précisait pas la clause 'order by'.
Alors que maintenant, on n'a plus du tout l'ordre réel, mais juste l'ordre apparent.

Encore une fois vous supposez des choses qui n'existent pas; Ni maintenant ni avant les SGBDR restituait les données dans un ordre quelconque prédéfini, par exemple l'ordre de stockage.
Deux exemple pour vous démontrer le contraire :
1) une partie des données de la table est en mémoire : le système va sans doute restituer en première les données en RAM puis en parallèle monter les données du disque en RAM, pour enfin restituer la queue.
2) pour les SGBDR faisant du parallélisme au niveau des IO comme du cache, l'ordre de présentation des lignes sera arbitraire, car ce sera le premier thread qui présentera ses données en premier pour l’utilisateur..

Bref, vous avez encore bien du chemin à faire pour comprendre ce que vous n'avez pas compris.
Votre faiblesse vient du fait que vous ne maîtrisez pas la théorie, comme vous l'a fait remarquer FMSREL, alors vous inventez votre propre logique...

A +

[Artemus24]

Salut sqlpro.

A chaque fois que vous intervenez, c'est juste pour critiquer !

il n'existe JAMAIS sur aucun SGBDR d'ordre par défaut

Et qu'est-ce que vous entendez par "ordre par défaut" ? Votre phrase ne veut rien dire du tout !
Dois-je comprendre qu'à chaque fois que je fais un 'select', je n'obtiens jamais le même ordre apparent des lignes ?

Selon moi, vous avez une conception théorique des bases de données et vous ne vous intéressez jamais au contrainte physique, qui nécessite justement l'optimisation de la performance.

Il y a quelques années j'ai montré les méfaits de cette croyance avec SQL Server

Qu'est-ce que j'ai à foutre de SQL Server. Nous sommes dans le forum consacré à MySql alors essayez de ne pas être hors sujet.

Donnez-moi un exemple concret sous mysql démontrant vos affirmations !
Si vous ne vous pliez pas à ces exigences, vos paroles sont vaines et reflètes juste votre arrogance !

@+

[SQLpro]

Dois-je comprendre qu'à chaque fois que je fais un 'select', je n'obtiens jamais le même ordre apparent des lignes ?

ENFIN !!! Hourra !!! vous commencez à percevoir les choses... Il serait temps !

A +

[escartefigue]

2) oui, j'ai bien compris que la solution donnée par escartefigue (que je remercie au passage), consiste à mettre 'order by clef' sur la requête 'select * from test', non pas pour trier le résultat, mais juste demander à mysql de choisir le bon indexe, entre autre la 'primary key'.

C'est gentil de me remercier, mais relisez tous mes posts ci-dessus : je n'ai jamais écrit une énormité pareille !
(j'ai moi même relu mes posts car j'ai failli faire un arrêt cardiaque )
C'est tout le contraire : mettre un order by c'est pour trier, rien d'autre

[aieeeuuuuu]

En effet, vous devez confondre avec mon post #23.

Mais je parlais bien de mettre un ORDER BY pour avoir un tri et vous faisais remarquer que de fait il utilise un autre index. je disais donc que c'était bien mieux qu'un HINT.
En aucun cas je ne vous proposais cette solution pour le forcer à utiliser un index quelconque.

[fsmrel]

Bonsoir,

La 'primary key' est un indexe comme les autres

Hum... J’ai rappelé ici que le concept de clé primaire a été inventé par un mathématicien, Ted Cod, dans le cadre de la théorie relationnelle, laquelle ressortit aux mathématiques appliquées.

J’ai aussi rappelé que l’index intervient dans la soute, au niveau de la tuyauterie et n’a donc rien à voir avec la clé primaire, ça n’est qu’un fichier dont les plombiers ont eu l’idée de se servir pour héberger les valeurs prises par les attributs d’une table, notamment ceux qui composent une clé primaire et, physiquement, en garantir l’unicité.

A vrai dire, je ne connais pas la norme SQL

C’est l’occasion d’en prendre connaissance. Pour moins de 10 euros (port compris) vous pouvez acquérir l’ouvrage de Peter Gulutzan, SQL-99 Complete, Really, mais n’y cherchez pas l’instruction CREATE INDEX ! La plomberie ne fait évidemment pas partie de la norme.





Mais l’ouvrage de Gulutzan fait plus de 1000 pages... Comme introduction à la norme, l’ouvrage de Date et Darwen (qui a longtemps représenté le Royaume uni dans le comité ISO SQL) est le plus indiqué (500 pages quand même) : A Guide to SQL Standard (4th Edition), pour environ 5 euros ! (via AbeBooks.com).

[Artemus24]

Salut sqlpro.

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

Au lieu de me faire de la rhétorique de bas étage, Monsieur SQLPRO, c'est plutôt à escartefigue que vous auriez dû vous adressez, car c'est lui qui a introduit le mot aléatoire dans ce sujet.

Définition du mot "arbitraire" :

Qui résulte d'un libre choix et ne répond à aucune nécessité logique

Vous vous trompez même dans l'usage du mot dont vous désirez illustrer vos propos.
Ce n'est pas un "libre choix", mais une contrainte par la nécessite de l'organisation physique sur laquelle repose votre base de données.
Arrêter de faire de la masturbation intellectuelle à tout bout de champs et essayez de répondre, sans condescendance, aux questions posées si vous en êtes capable. Mais ça, j'en doute.

@+

[SQLpro]

Vous vous trompez même dans l'usage du mot dont vous désirez illustrer vos propos.
Ce n'est pas un "libre choix", mais une contrainte par la nécessite de l'organisation physique sur laquelle repose votre base de données.

Encore une fois non ! Vous n'avez rien compris ! C'est bien le libre choix qu'effectue l'optimiseur ! Libre choix d'utiliser tel ou tel index, ou la table. Libre choix encore d'effectuer une lecture série ou en parallèle. Vous, vous n'avez aucun choix possible que de lancer des requêtes qui sont de simples demandes et non une suite d'instruction à réaliser dans le strict respect de l'ordre des éléments !!!

A +

[Artemus24]

Salut à tous.

On s'est bien écarté du sujet de base.

Encore une fois vous supposez des choses qui n'existent pas; Ni maintenant ni avant les SGBDR restituait les données dans un ordre quelconque prédéfini, par exemple l'ordre de stockage.

Cette question ne s'adressait pas à vous, mais à fsmrel. Et ce n'est pas parce que vous êtes encore très jeune qu'il faut croire que les bases de données sont nées en même temps que vous.

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

Ma question était de savoir si à un moment donné, dans DB2, un 'select *' se faisait dans l'ordre de stockage et non selon la clef primaire ?
Ou bien, je n'ai pas compris l'exemple que vous avez voulu illustrer par l'introduction du 'order by'.
Hormis que ce soit à la demande des utilisateurs, et non un choix issue de la théorie, je ne comprends même pas pourquoi la théorie a fait une impasse sur cet clause.

1) il faut verrouiller la table au niveau d'isolation SERIALIZABLE pour chaque insertion

Je n'ai pas attendu sur vous pour le faire.

Deux exemple pour vous démontrer le contraire :
1) une partie des données de la table est en mémoire : le système va sans doute restituer en première les données en RAM puis en parallèle monter les données du disque en RAM, pour enfin restituer la queue.
2) pour les SGBDR faisant du parallélisme au niveau des IO comme du cache, l'ordre de présentation des lignes sera arbitraire, car ce sera le premier thread qui présentera ses données en premier pour l’utilisateur..

Je ne me retrouve ni dans le cas 1) ni dans le cas 2) ! Pourquoi ?
Pour le cas 1) je n'ai pas une volumétrie qui fasse que je puisse à un moment donné me retrouver dans ce cas de figure.
Ensuite, je suis en ce moment dans des tests d'optimisations et de ce fait, j'ai désactivé (temporairement) l'accès aux buffers afin d'avoir le vrai temps d'exécution de mes requêtes.
Pour le cas 2), je n'ai rien vu dans le paramétrage de 'MY.INI' quelque chose qui m'indiquerait que je fais du parallélisme.
De plus, je travaille sur un ordinateur portable HP Compas 6830s qui est dual core.

Code :

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

Processeur	Intel(R) Core(TM)2 Duo CPU     P8600  @ 2.40GHz, 2401*MHz, 2 cœur(s), 2 processeur(s) logique(s)

Avant de généraliser comme vous le faites, renseignez vous sur la configuration du matériel et sur le travail que j'effectue actuellement.

Et pour votre gouverne, l'ordre par défaut est celui de l'insertion. Et c'est pourquoi il est vivement conseillé de faire en sorte que le fichier (ou tout autre chose) soit triés selon la clef primaire avant d'effectuer le chargement dans la table.

J’ai aussi rappelé que l’index intervient dans la soute, au niveau de la tuyauterie et n’a donc rien à voir avec la clé primaire, ça n’est qu’un fichier dont les plombiers ont eu l’idée de se servir pour héberger les valeurs prises par les attributs d’une table, notamment ceux qui composent une clé primaire et, physiquement, en garantir l’unicité.

Le rôle de la clef primaire ne se cantonne pas uniquement à obtenir d'une part l'unicité de la valeur introduite et d'autre part de ne pas avoir de valeur 'NULL'. Ça, c'est la définition mathématique ou théorique.
Une base de données doit obligatoirement reposer sur un support physique, au cas où cela vous aurait totalement échappé.
Les choix qui sont faits sont des contraintes que la théorie ignore totalement. Mais elles ont leur raison d'être, même si cela vous dérange.
Et une de ces raison est la performance. Tout l'intérêt d'un DBA est justement de savoir maintenir cette performance à une valeur presque optimale.

Le rôle de la 'primary key' est de faire le lien entre la ligne insérée et la valeur de la clef. C'est-à-dire d'indiquer son emplacement sur le disque dur.
Et comme cette clef primaire est aussi un indexe, l'indexe est trié selon l'ordre de la clef.
Donc il ne faut pas mélanger l'ordre des valeurs de la clef dite primaire, et l'ordre de l'insertion des lignes, qui se fait selon la 'rowid'.
De plus, s'il n'y avait pas de 'Primary key', je ne sais pas comment on pourrait retrouver la ligne, juste à partir de sa clef.

Ensuite, chaque indexe fait toujours référence à la 'Primary Key' pour savoir où se trouve la ligne dont la valeur qui est indexée, y fait référence.
ET oui, ne pensez pas que la tuyauterie soit moins importante que l'aspect théorique du sql dont vous semblez en faire l'éloge.
Sans cette tuyauterie, les performances seraient désastreuses !

C'est gentil de me remercier, mais relisez tous mes posts ci-dessus : je n'ai jamais écrit une énormité pareille !
(j'ai moi même relu mes posts car j'ai failli faire un arrêt cardiaque )
C'est tout le contraire : mettre un order by c'est pour trier, rien d'autre

Voilà une énormité auquel je ne m'attendais pas ! Franchement, avez-vous un tant soit peu de bon sens, pour écrire une telle absurdité ?

Si je n'ai pas d'indexe et que je demande par la clause 'order by' un résultat qui n'est pas celui de la clef primaire alors OUI, il y a aura un tri.
Voir l'exemple ci-après :

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
--------------
describe test
--------------
 
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1  | int(10) unsigned | NO   | PRI | NULL    |       |
| col2  | int(10) unsigned | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
--------------
explain select * from test order by col2
--------------
 
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
--------------
select * from test order by col2
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    2 |    1 |
|    3 |    2 |
|    1 |    3 |
+------+------+

Et comment je sais qu'il y a un tri ? Tout simplement en regardant la colonne 'extra' du explain. Il est indiqué 'using filesort'.

Or l'exemple que j'avais donné était celui où j'avait un indexe sur la colonne 'col2' alors que je désire obtenir l'ordre selon la 'primary key'.
Au cas où vous auriez oublié, c'est le problème que je rencontre et l'origine de mon sujet.

L'exemple que je donne ci-après est celui où je demande à ce que mon 'select *' soit trié selon l'ordre de la colonne 'col2'.
Or ici, j'ai un indexe sur cette colonne. En faisant 'select * from test order by col2', mon résultat sera bien ordonné, mais il n'y aura pas de tri !
Ci-après l'exemple qui démontre mon affirmation :

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
--------------
describe test
--------------
 
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1  | int(10) unsigned | NO   | PRI | NULL    |       |
| col2  | int(10) unsigned | NO   | UNI | NULL    |       |
+-------+------------------+------+-----+---------+-------+
--------------
explain select * from test order by col2
--------------
 
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | jdx_col2 | 4       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
--------------
select * from test order by col2
--------------
 
+------+------+
| col1 | col2 |
+------+------+
|    2 |    1 |
|    3 |    2 |
|    1 |    3 |
+------+------+

Et comment je sais qu'il n'y a pas eu de tri ? En regardant la colonne 'extra' de l'explain. Il est indiqué 'using index'.
Ce qui signifie qu'il n'y a pas de tri mais que le 'full scan' de la table sera lu dans l'ordre de l'indexe 'idx_col2'.

Alors je vais vous apprendre quelque chose, un 'order by' peut aussi forcer l'usage d'un indexe quand celui-ci existe !

ENFIN !!! Hourra !!! vous commencez à percevoir les choses... Il serait temps !

Mon interrogation était une forme d'ironie ! J'ai répondu à cette question ci-dessus dans ce message.

Bon. Je termine ce sujet où je n'ai pas eu les réponses que j'attendais.

@+

[SQLpro]

Votre dernier post est un tissus d'énormités qui me sidère et laisse voir l'état d’inculture dans lequel vous vous trouvez !
À peu près tout ce que vous affirmez est faux.
Devant tant de bêtise je renonce à vous contredire, ne serait-ce que parce que de toutes façons vous n'entendrez pas des choses que l'on vous a déjà dites et que je ne perdrais pas encore mon temps avec vous, il y a des gens plus intelligents et plus ouverts...
Mais si je fais ce post, c'est pour indiquer aux lecteurs qui risquent hélas vous lire que ce tissus d’âneries constitue la plus belle collection de choses à ne pas faire !
Visiblement vous vous complaisez dans votre ignorance et refusez les éclaircissements de tout ceux qui ont participer à vous aider...

C'est navrant et calamiteux !

[fsmrel]

Bonsoir,

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

Hormis que ce soit à la demande des utilisateurs, et non un choix issue de la théorie, je ne comprends même pas pourquoi la théorie a fait une impasse sur cet clause.

Heu... Vous attribuez à SQLpro des propos qui sont les miens... Je répondrai donc :

Si la théorie a fait une impasse sur la clause ORDER BY c’est parce celle-ci n’a rien à voir avec celle-là. Comme je l’ai déjà précisé, Ted Codd, père de la théorie relationnelle, s’est situé dans le contexte de la théorie des ensembles et de la logique des prédicats. Or, le père de la théorie des ensembles, Georg Cantor, et celui de la logique moderne (avec notamment, en 1879, la théorie de la quantification, sans laquelle Ted Codd serait resté sec), Gottlob Frege, ont fait l’impasse puisque qu’ils n’en avaient strictement rien à faire : en fait, en remontant aux origines, c’est au grand ancêtre, Aristote soi-même qu’il faudrait poser la question, mais c'est un peu tard...

Peut-être un début de réponse :

En se référant à Frege, Quine propose a son tour (cf. méthodes de logique, page 259) la formule qui sous-tend l'induction mathématique (« Nx » se lit « x est un nombre ») :

Nx ←→ (∀α) {(0 ∈ α) . (∀z) [(z ∈ α) → (1+z ∈ α)] ➙ (x ∈ α)}

C'est-à-dire : Pour toute classe α, si 0 appartient à α et si la proposition « si tout nombre z appartient à α alors son successeur appartient aussi à α » alors tout nombre appartient à α.

Autrement dit, être un nombre c'est appartenir à chaque classe à laquelle appartient 0 ainsi que le successeur de chaque membre de la classe.

On a là un certain ordre, puisqu’on parle de successeur. Le problème est quand même qu’on ne s’intéresse ici qu’à 0, à un certain z et son successeur, mais pour les autres nombres, libre à chacun d’ordonner comme il l’entend...

Mais attention, on entre désormais dans la logique du 2e ordre, sur laquelle Codd s’est du reste appuyé dans son article de 1969 (Derivability, Redundancy and Consistency of Relations Stored in Large Data Banks), mais en 1970 il est prudemment revenu à la logique du 1er ordre (A Relational Model of Data for Large Shared Data Banks)...


Je reviendrai un peu plus tard sur le concept de clé primaire...

[fsmrel]

Bonsoir,

Le rôle de la clef primaire ne se cantonne pas uniquement à obtenir d'une part l'unicité de la valeur introduite et d'autre part de ne pas avoir de valeur 'NULL'. Ça, c'est la définition mathématique ou théorique.

Ne varietur ! Le concept de clé primaire est né il y a 45 ans le 6 juin 1970 (cf. A Relational Model of Data for Large Shared Data Banks). Toute autre interprétation de la définition qu’en a donné Codd relève de l’homonymie et de la récupération. Si vous remettez le nez dans la documentation VSAM d’il y a 40 ans (par exemple, le Programmer’s Guide), vous verrez que, dans le cas d’un KSDS de base, on n’y causait que de clés (de type UNIQUE par construction), sans le qualificatif « primaire ». Dans le cas d’un index alternatif (doublons autorisés), on utilise le terme d’« alternate key » pour un ensemble de champs constituant une clé alternative. Cet index alternatif contient l’ensemble des pointeurs (« prime key pointer » ou en abrégé « prime key ») vers le KSDS de base.

Comme le terme « primary key » est devenu populaire, grâce à SQL, il était tentant de rebaptiser ainsi la clé d’un KSDS, et c’est ce qui s’est passé, ça fait à la page, mais même pour un KSDS d’aujourd’hui, le rôle de la clé primaire se limite à garantir l’unicité des valeurs qu’elle contient. Je vous renvoie à la documentation de référence DFSMStvs Administration Guide.

Extrait du glossaire :

primary key. One or more characters within a data record used to identify the data record or control its use. A primary key must be unique.

alternate key. One or more characters within a data record used to identify the data record or to control its use. Unlike the primary key, the alternate key can identify more than one data record. An alternate key is used to build an alternate index or to locate one or more base data records through an alternate index. See also generic key, key, and key field. application owning region.

alternate index. A key-sequenced data set that contains index entries organized by the alternate keys of its associated base data records. It provides an alternate means of locating records in the data component of a cluster on which the alternate index is based.

Un autre document qui est intéressant : VSAM Demystified.

En tout cas, VSAM lui aussi adhère à la définition théorique, et ce n'est qu'un gestionnaire de fichiers, on est loin de la théorie relationnelle...

[fsmrel]

Bonsoir à nouveau,

Le rôle de la 'primary key' est de faire le lien entre la ligne insérée et la valeur de la clef. C'est-à-dire d'indiquer son emplacement sur le disque dur.
Et comme cette clef primaire est aussi un indexe, l'indexe est trié selon l'ordre de la clef.
Donc il ne faut pas mélanger l'ordre des valeurs de la clef dite primaire, et l'ordre de l'insertion des lignes, qui se fait selon la 'rowid'.
De plus, s'il n'y avait pas de 'Primary key', je ne sais pas comment on pourrait retrouver la ligne, juste à partir de sa clef.
Ensuite, chaque indexe fait toujours référence à la 'Primary Key' pour savoir où se trouve la ligne dont la valeur qui est indexée, y fait référence.
ET oui, ne pensez pas que la tuyauterie soit moins importante que l'aspect théorique du sql dont vous semblez en faire l'éloge.
Sans cette tuyauterie, les performances seraient désastreuses !

En vertu de ce qui précède, non la clé primaire n’a rien à voir avec le disque dur, et d’après ce qui suit, elle n’est pas un index : sous le capot, ce sont les valeurs de la clé primaire qui sont hébergées dans un index (lui-même logeant dans un index space), nuance. Quant à la tuyauterie et les performances, ça me connaît ! Pour la partie SQL (qu’à l’instar de Hugh Darwen je surnomme le Askew Wall), je suis plutôt moyen, et je préfère Tutorial D de Date et Darwen.

Pour étayer mes dires, examinons ce qui se passe avec DB2 par exemple. Je reprends ce que j’ai déjà écrit en 2009, en réponse à une question de CinePhil.

Un index DB2 a une structure d'arbre équilibré (balanced tree), cela signifie que toutes les feuilles sont à la même distance de la racine : autrement dit, le temps d’accès est le même pour toutes les feuilles. Ceci est capital quand il s’agit de s’attaquer au réglage des performances, lesquelles seraient aléatoires si l’arbre n’était pas équilibré. Les index sont donc le plus souvent, des arbres B, et dans le cas de DB2 ce sont même des B+ (chaînage des feuilles).

N.B. J’ai écrit que le temps d’accès est le même pour toutes les feuilles : ceci est vrai quand l’index vient d’être créé ou réorganisé, mais au fil du temps il peut y avoir une dégradation perceptible des performances du fait de nombreuses mises à jour, et il est alors préférable de procéder à une réorganisation de l’index. DB2 tient à notre disposition toutes informations utiles à cet effet (stats du catalogue).

Observons maintenant comment DB2 (disons version 5) organise un index. Celui que je dépiaute (appelons-le MEMBRE_X1) est destiné à contenir les valeurs de la clé primaire de la table MEMBRE des membres de DVP : '0012' (valeur de la clé primaire du membre CinePhil), '4089' (valeur de la clé primaire du membre fsmrel), etc.

Supposons que l’index vient d’être défini et vide. Lors du premier INSERT, par exemple du membre SQLpro dans la table MEMBRE, DB2 crée un enregistrement dans le table space d’accueil des images des lignes de la table. Appelons MEMBRE_TS ce table space.





La page P1 contient un certain nombre d’informations « système », dont une petite table appelée id map contenant l’adresse dans P1 de la ligne qui vient d’être insérée. Le rôle de l’id map est capital : les index branchés sur la table ne connaîtront jamais l’adresse exacte d’une ligne dans le table space, mais seulement l’adresse de la page P1 et le numéro du poste (invariable) de cette ligne dans l’id map (1 dans l’exemple). Ainsi, si les enregistrements bougent dans la page, ou sont carrément expulsés vers d’autres pages (« Ôte-toi de là, que je m’y mette ! » dit le gros enregistrement au petit), ces phénomènes n’auront aucun impact sur les valeurs connues par les index (dans l’exemple, la seule information connue est 'P1,1', à savoir l’adresse de la page et le numéro de poste dans l’id map).
Concernant l’index MEMBRE_X1, DB2 réalise les opérations suivantes à l’occasion de ce premier insert :

Création de la page racine et d’une page feuille.
DB2 note dans la page feuille les coordonnées de SQLpro dans MEMBRE_TS, à savoir un record identifier (RID), composé du numéro de la page hébergeant SQLpro, et du numéro qui lui est affecté dans l’id map : 'P1,1'.
DB2 note dans la page racine la plus grande valeur de clé au niveau inférieur et l’adresse de celle-ci (symbolisée par '↑').






Effectuons un deuxième INSERT, par exemple du membre CinePhil. Il reste de la place dans la page P1 du table space MEMBRE_TS, et elle ressemblera à quelque chose comme ceci :





L’id map s’est enrichie d’un élément (poste), portant le numéro 2 et contenant l’adresse dans P1 du nouvel enregistrement.
L’index MEMBRE_X1 est mis à jour en conséquence :
La feuille est enrichie d’un élément permettant d’adresser dans MEMBRE_TS le membre dont la valeur de clé est égale à '0012'. Les éléments dans la feuille sont triés, ce qui fait que l’élément "3500 (RID = 'P1,1')" passe derrière le nouvel arrivant.
La racine ne change pas de contenu, par construction elle conserve toujours la plus grande valeur de clé de la feuille cible.






Les INSERT se suivent et se ressemblent : fsmrel (clé = '4089'), Antoun (clé = '0967'), etc.

Après un certain nombre d’inserts, le table space contiendra de plus en plus de pages et l’index suivra : à un moment, il n’y aura plus assez de place dans la feuille F1 : DB2 créera une feuille supplémentaire F2 et en notera l’adresse dans la racine. Dans la figure ci-dessous, la racine contient deux entrées, une pour F1 et une pour F2. Chaque entrée contient la plus grande valeur de clé pour chaque feuille ainsi que l’adresse de celle-ci.

La séquence des clés doit être strictement respectée, aussi les feuilles seront-elles chaînées à cet effet : ceci favorise les traitements de masse (batch) pour lesquels les requêtes SQL SELECT comportent une clause ORDER BY (attention, au sein des pages de données, P1, etc., les lignes ne sont pas triées). Ce chaînage des feuilles fait que les index DB2 ne sont pas seulement des arbres B, mais B+.





Si l’on soumet l’instruction :

Code SQL :

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

1
2
3
4
 
SELECT  NOM 
FROM    MEMBRE  
WHERE   MembreId = '3500'

Alors DB2 lira le contenu de la racine de l’index Membre_X1, et comme '3500' est supérieur à '0967' et inférieur à '4089', ce sera la feuille F2 qui sera lue à son tour. La lecture suivante sera celle de la page de données P1. La consultation de l’id map permettra de retrouver les données du membre '3500'.

Les INSERT continuant, de nouvelles feuilles vont être crées et le nombre d’entrées dans la racine croîtra en conséquence. Arrivera un moment où à son tour elle sera pleine :

C’est alors que DB2 effectue un split de la racine : DB2 répartit l’ensemble de ses éléments dans deux pages, pour moitié entre la première et la deuxième, à l’image de la scissiparité des vers de terre. Mais comme par définition la racine ne comporte qu’une page, ces deux pages deviennent des nœuds intermédiaires et une nouvelle racine est créée, n’adressant plus cette fois-ci les feuilles, mais ces nœuds intermédiaires. Je résume la situation dans le dessin ci-dessous, extrait d’un cours que j’ai monté il y a plus de vingt cinq ans (DB2 V 2) et que j’ai retouché pour les besoins de la cause :





A chaque fois que la racine sera pleine, il y aura split et création d’un nouveau niveau intermédiaire.

J’en resterai là, pour la suite reportez-vous à la discussion à laquelle j’ai fait référence.

[escartefigue]

Bon. Je termine ce sujet où je n'ai pas eu les réponses que j'attendais.

Nous en sommes tout de même à la 3ème page d'explications détaillées, de la part de 4 contributeurs différents, qui ont argumenté avec de nombreux exemples concrets ainsi que les bases théoriques étayant ces explications et la référence aux ouvrages sur le sujet . Que faut il de plus ?

[aieeeuuuuu]

Il n'a pas dit qu'il n'avait pas eu de réponse, il a dit que ce n'était pas celle qu'il attendait.

il attendait

Oui, vous avez raison, c'est un bogue de MySQL

Il a eu

Non, vous avez tort et faites fausse route