Un problème de tri !

**Artemus24** · 15/07/2015, 05h50

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** · 15/07/2015, 15h28

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** · 15/07/2015, 17h22

Envoyé par 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 !

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** · 15/07/2015, 20h28

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** · 16/07/2015, 10h12

Envoyé par Artemus24

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** · 16/07/2015, 10h34

Envoyé par Artemus24

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** · 26/07/2015, 11h34

Salut à tous.

En fait, j'en suis toujours au même point !

Vous avez polué mon sujet, en me faisant bien comprendre que j'étais nul, que je ne savais rien faire, et que je ferais bien d'aller voir ailleurs.

Il y a juste 'aieeeuuuuu', par compassion je suppose, dans son message du '24/07/2015, 10h38' qui semble avoir compris que la réponse :

Non, vous avez tort et faites fausse route

n'est en fait qu'une façon de me rabaisser !

'escartefigue' croit que tous les intervenants ont répondu à mes questions, et avec de nombreux exemples.
Il faudrait changer de paire de lunettes, car les exemples, c'est moi qui les ai donnés.

Ce qui me suprend pour des professionnels comme vous tous, c'est de ne pas capable de répondre à quelque chose d'aussi facile, enfin il me semble.

Je remercie au passage 'fsmrel' d'avoir rafraichi ma mémoire sur le sujet de l'organisation physique des données sous DB2.
Au moins lui, essaye de nous faire part de sa connaissance !

Je ne parle même pas de SQLPRO qui à chaque message qu'il m'adresse, c'est juste pour m'insulter !
A l'avenir, j'espère qu'il tiendra sa promesse de ne plus s'adresser à moi, car il me fait perte mon temps.

Je fais une dernière tentative de mon problème (voir l'exemple que je donne dans mon premier message).
J'ai une table avec une 'primary key' et un 'index'.
Pourquoi quand je fais un 'select * from test', il me donne le vidage de ma table selon l'ordre de l'indexe et non selon l'ordre de la 'primary key' ?

C'est ça ma question !

@+

**SQLpro** · 26/07/2015, 13h24

Envoyé par Artemus24

Salut à tous.

En fait, j'en suis toujours au même point !

Vous avez polué

Pollué prend 2 "l"

mon sujet, en me faisant bien comprendre que j'étais nul

C'est vous qui le dite et je suis pas loin de le penser... Mais surtout vous êtes borné !

, que je ne savais rien faire

La vous extrapolez...

, et que je ferais bien d'aller voir ailleurs.

Vous mentez, personne ne vous a dit cela et les multiples poste en réponse indique bien que l'on tente de vous aider, mais que vous refusez les vérités que l'on vous démontre... En nous cela s’appelle de l'intégrisme !

Il y a juste 'aieeeuuuuu', par compassion je suppose, dans son message du '24/07/2015, 10h38' qui semble avoir compris que la réponse :

n'est en fait qu'une façon de me rabaisser !

Ben voyons !

'escartefigue' croit que tous les intervenants ont répondu à mes questions, et avec de nombreux exemples.
Il faudrait changer de paire de lunettes, car les exemples, c'est moi qui les ai donnés.

N'importe quoi. Vous êtes à la limite de l'insulte compte tenu des énormes efforts déployés par tous et spécialement fmsrel !

Ce qui me suprend

Au passage ajoutez-y un "r" : surprend et non suprend !

pour des professionnels comme vous tous, c'est de ne pas capable

Il manque un verbe : "c'est de ne pas capable" = > "c'est de ne pas être capable"

de répondre à quelque chose d'aussi facile, enfin il me semble.

Pour la n^ieme fois, c'est la réponse qui ne vous plait pas car elle ne va pas dans votre sens. D’où le fait que vous êtes borné et à la limite de l'intégrisme. Relisez TOUTES les réponses apportées. Lisez les livres indiqués...

Je remercie au passage 'fsmrel' d'avoir rafraichi ma mémoire sur le sujet de l'organisation physique des données sous DB2.
Au moins lui, essaye de nous faire part de sa connaissance !

Je ne parle même pas de SQLPRO qui à chaque message qu'il m'adresse, c'est juste pour m'insulter !

Encore une fois, vous affirmez n'importe quoi. Je ne vous ais jamais insulté. Mais j'ai effectivement dit que vos propos était stupide. Preuve que vous ne lisez visiblement pas bien les messages que l'on vous adresse ! Si vous n'êtes même pas capable de faire la différence entre vos propos et votre personne, alors là oui, je pense que vous êtes stupide et là je m'adresse bien à la personne et non plus aux propos !

A

"A" étant à ce niveau une préposition, il prend un accent grave... donc "À"... Deux solutions :

commencez par mettre l'accent grave à l'aide de ALT GR + touche 0 et tapez la lettre A en majuscule
appuyez sur ALT et au clavier numérique faite 0192

l'avenir, j'espère qu'il tiendra sa promesse de ne plus s'adresser à moi, car il me fait perte

"perdre" et non perte

mon temps.

Perdu, car lorsque l'on m'attaque personnellement je me défend !

Je fais une dernière tentative de mon problème

Erreur de style : manque un verbe et ce qui va avec... Par exemple "Je fais une dernière tentative pour résoudre mon problème"

(voir l'exemple que je donne dans mon premier message).
J'ai une table avec une 'primary key' et un 'index'.
Pourquoi quand je fais un 'select * from test', il me donne le vidage de ma table selon l'ordre de l'indexe

Un index ne prend par de "e" en finale.

et non selon l'ordre de la 'primary key' ?

Vidage ce terme est inapproprié. On parle d'extraction lorsque l'on fait un SELECT.

Encore une fois on a répondu à de multiples reprises : il n'y a aucun ordre prédéterminé de restitution des lignes en sortie d'un SELECT, quelque soit le SGBDR, tant que vous ne mettez pas un tri explicite à l'aide de la clause ORDER BY. Comme déjà indiqué, l'ordre de sortie est arbitraire du fait de l'optimisation à l'instant t de la vie des données (indexation, règles sémantiques et statistiques de l'optimiseur, disponibilité des données dans le cache, parallélisme...).

C'est ça ma question !

Et notre réponse indiqué ci avant, sera toujours la même... mais vous ne voulez pas l'entendre !

@+

A +

PS : votre dernière remarque me fais penser aux interviews de Georges Marchais avec Elkabbach... "... vous arrivez avec vos questions, moi j'arrive avec mes réponses !"
http://www.ina.fr/video/I08014459

**fsmrel** · 26/07/2015, 21h09

Bonsoir,

A propos de DB2 :

Envoyé par Artemus24

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 ?

Créons la structure suivante pour la table PSN des personnes :


CREATE TABLE PSN
(
        PSN_ID         INT          NOT NULL
      , MATRICULE      CHAR(8)      NOT NULL
      , COLX           CHAR(32)     NOT NULL
      , COLY           CHAR(40)     NOT NULL
      , COLZ           CHAR(40)     NOT NULL
      , COLT           CHAR(40)     NOT NULL
      , COLU           CHAR(200)    NOT NULL
    , CONSTRAINT PSN_PK PRIMARY KEY (PSN_ID)
) ;

Et les index suivants


CREATE UNIQUE INDEX PSN_XP ON PSN (PSN_ID) CLUSTER ... ;

CREATE UNIQUE INDEX ALT_KEY ON PSN (MATRICULE) ... ;

L’index PSN_XP est cluster. Après avoir chargé la table en conséquence, l’état des lieux est le suivant, comme le montre DB2 Estimator :

Table PSN :

Index PSN_XP utilisé pour loger PSN_ID (colonne clé primaire) :

Index ALT_KEY utilisé pour loger la colonne MATRICULE :

Exécutons la requête suivante :


SELECT * FROM PSN ;

=>

Il y a un table scan, donc pas d’utilisation d’index.

Le cluster ratio est égal à 100, l’ordre de rangement des lignes de la table PSN dans le table space correspond pile-poil à la séquence PSN_ID de l’index cluster.

Parcourons le résultat à l’aide d’un curseur : je n’ai pas de DB2 for z/OS sous la main mais je sais que les lignes seront délivrées dans l’ordre des IDN_PSN, puisqu’elles sont rangées dans le table space selon la séquence définie par l’index cluster. Maintenant, les équipes de développement vont reprendre ma requête, qui va peut-être se retrouver dans une centaine de programmes, lesquels obtiendront des résultats identiques au mien.

Mais, après six mois de production, il est décidé que l’index cluster sera désormais l’autre index, ALT_kEY : la production effectue un UNLOAD/RELOAD et le rangement des lignes de la table dans le table space ne sera plus du tout le même (en passant, le cluster ratio de l’index « primaire » PSN_XP en aura pris un coup) :

Là encore, la requête SELECT * FROM PSN ; est traitée selon le régime du table scan :

Il y a un table scan, donc pas d’utilisation d’index. Mais cette fois-ci, les lignes seront délivrées aux programmes dans l’ordre des matricules : ce changement risque de provoquer des réactions véhémentes de la part de certains utilisateurs...

Moralité : pour s'assurer de la stabilité des résultats et de la confiance des utilisateurs il fallait dès le départ coder :


SELECT * FROM PSN ORDER BY PSN_ID ;

Envoyé par Artemus24

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 !

En vertu de ce qui précède, au moins avec DB2 c’est logique, puisque c'est « cluster dépendant ».

Il est probable que ce qui vaut pour DB2 vaut pour d’autres SGBD...

Il y a manifestement un consensus ès matière, et ce n’est pas le sage al1_24 qui nous démentira (cf. « Comment les opérateurs ensemblistes trient-ils ? »).

Envoyé par Artemus24

Un indexe (secondary indexes) fait toujours référence à l'indexe cluster afin d'obtenir une autre façon d'accéder à tes lignes

Disons que cela vaut pour les KSDS (VSAM), un index alternatif fait effectivement référence au cluster de base. Cela vaut sans doute pour certains SGBD. Mais avec DB2, que nenni ! quel que soit l’index, cluster ou pas, les feuilles adressent directement et seulement le table space hébergeant la table concernée, aucun index ne fait référence à un autre index. Ainsi en va-t-il pour les index PSN_XP et ALT_KEY. Ceci permet de réduire de façon très sensible le nombre entrées/sorties pour accéder aux données.

Envoyé par Artemus24

quand on crée un indexe, celui-ci fait toujours référence à la primary key

Cf. ce que je viens d’écrire. Avec DB2, quel que soit l’index, cluster ou pas, les feuilles n’adressent aucun autre index, mais directement et seulement le table space hébergeant la table concernée.

Envoyé par Artemus24

Pourquoi quand je fais un 'select * from test', il me donne le vidage de ma table selon l'ordre de l'indexe et non selon l'ordre de la 'primary key' ?

Au moins pour DB2 for z/OS, mes réponses devraient vous convenir. Pour les autres SGBD, je n’ai pas soulevé le capot, j’en resterai à la proposition 7 du Tractatus de Wittgenstein :

« Sur ce dont on ne peut parler, il faut garder le silence. » ( Wovon man nicht sprechen kann, darüber muß man schweigen).

**escartefigue** · 26/07/2015, 22h48

Envoyé par Artemus24

Salut à tous.

En fait, j'en suis toujours au même point !

Vous avez polué mon sujet, en me faisant bien comprendre que j'étais nul, que je ne savais rien faire, et que je ferais bien d'aller voir ailleurs.

Il y a juste 'aieeeuuuuu', par compassion je suppose, dans son message du '24/07/2015, 10h38' qui semble avoir compris que la réponse :

n'est en fait qu'une façon de me rabaisser !

'escartefigue' croit que tous les intervenants ont répondu à mes questions, et avec de nombreux exemples.
Il faudrait changer de paire de lunettes, car les exemples, c'est moi qui les ai donnés.

Ce qui me suprend pour des professionnels comme vous tous, c'est de ne pas capable de répondre à quelque chose d'aussi facile, enfin il me semble.

Je remercie au passage 'fsmrel' d'avoir rafraichi ma mémoire sur le sujet de l'organisation physique des données sous DB2.
Au moins lui, essaye de nous faire part de sa connaissance !

Je ne parle même pas de SQLPRO qui à chaque message qu'il m'adresse, c'est juste pour m'insulter !
A l'avenir, j'espère qu'il tiendra sa promesse de ne plus s'adresser à moi, car il me fait perte mon temps.

Je fais une dernière tentative de mon problème (voir l'exemple que je donne dans mon premier message).
J'ai une table avec une 'primary key' et un 'index'.
Pourquoi quand je fais un 'select * from test', il me donne le vidage de ma table selon l'ordre de l'indexe et non selon l'ordre de la 'primary key' ?

C'est ça ma question !

@+

Pour ma part, je fais également une ultime tentative pour vous convaincre :

- Non, je ne cherche nullement à rabaisser qui que ce soit, je suppose qu'il en va de même pour les autres intervenants
- reprenez le post de fsmrel que vous pourrez remercier pour son abnégation, tout y est, les principes, les exemples, les schémas
- relisez sereinement l'ensemble des réponses sans a priori, oubliez vos préjugés et analysez la documentation, puis, expérimentez ce qui vous a été proposé et analysez les résultats

**Antoun** · 26/07/2015, 23h37

Envoyé par Artemus24

Envoyé par SQLpro

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.

Encore une fois, tu supposes sans aucun élément des choses fausses

**Artemus24** · 17/07/2015, 03h13

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.

Envoyé par aieeeuuuuu

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.

Envoyé par aieeeuuuuu

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.

Envoyé par aieeeuuuuu

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.

Envoyé par aieeeuuuuu

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.

Envoyé par aieeeuuuuu

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 !

Envoyé par aieeeuuuuu

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

@+

**aieeeuuuuu** · 17/07/2015, 14h14

Envoyé par Artemus24

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

si si

Envoyé par Artemus24

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.

Envoyé par Artemus24

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.

Envoyé par Artemus24

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

Envoyé par Artemus24

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.

Envoyé par Artemus24

Envoyé par aieeeuuuuu

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 !

Envoyé par Artemus24

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** · 17/07/2015, 14h41

Envoyé par aieeeuuuuu

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** · 17/07/2015, 04h31

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** · 17/07/2015, 09h21

Envoyé par Artemus24

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

Envoyé par Artemus24

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

**Artemus24** · 18/07/2015, 11h10

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 :

Envoyé 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 ?

Envoyé par aieeeuuuuu

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.

Envoyé par aieeeuuuuu

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.

Envoyé par aieeeuuuuu

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** · 18/07/2015, 13h51

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

.

**SQLpro** · 21/07/2015, 11h07

Envoyé par Artemus24

Je dis cela en pensant que MySql suivait la norme SQL. Mea culpa !

La norme SQL se fout royalement de l'optimisation propre à chaque éditeur. En cette matière MySQL est certainement doté du plus mauvais optimiseur qui soit...

Encore une fois il n'existe JAMAIS sur aucun SGBDR d'ordre par défaut et pensez le contraire en évitant un ORDER BY est imbécile et suicidaire !

Vous ne méritez pas votre statut de membre éclairé, et je pense que je vais demander aux administrateur de developpez.com de vous rétrograder !
Passe encore que vous disiez des inepties, mais persister dans votre ignorance et repousser les conseils de gens avisés est stupide et confine à l'imbécilité !
"errare humanum est, perseverare diabolicum"
Bref, seriez vous un suppôt de Satan ?

Il y a quelques années j'ai montré les méfaits de cette croyance avec SQL Server. Bien des gens avaient fait des vues avec SELECT TOP 100 PERCENT WITH TIES ... ORDER BY ... . À partir de la version 2008 ce tri ne s'effectuait plus du tout quelques soit la manière de lancer la vue ! C'est justement le fait de l'optimiseur, car une vue ne doit jamais en aucun cas avoir un ORDER BY ce que l'imbécile clause TOP permettait !

A +

**Artemus24** · 27/07/2015, 10h17

Salut à tous.

Envoyé par fsmrel

Exécutons la requête suivante :

Code :

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

SELECT * FROM PSN ;

Il y a un table scan, donc pas d’utilisation d’index.

Oui, tout à fait car c'est le fonctionnement normal en DB2 que je connais ! Je m'attendais à la même chose sous mysql. Or ce n'est pas le cas.
Comme c'est un autre SGBDR, il se peut que les choix sont différents. Et justement, c'est ce que j'essaye d'étudier.

Envoyé par fsmrel

je sais que les lignes seront délivrées dans l’ordre des IDN_PSN, puisqu’elles sont rangées dans le table space selon la séquence définie par l’index cluster.

Comme je l'ai constaté sous MySql (voir mon exemple), il va chercher l'indexe et non la primary key. Sur ce point, ce n'est pas à l'identique de DB2.
Pire encore, quand j'ajoute une troisième colonne, aussi avec indexe, il me donne cette fois-ci l'ordre correcte. C'est un comportement plus qu'étrange de la part de MySql.

Envoyé par fsmrel

Moralité : pour s'assurer de la stabilité des résultats et de la confiance des utilisateurs il fallait dès le départ coder :

Code :

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

SELECT * FROM PSN ORDER BY PSN_ID ;

J'en conviens, mais ce n'est pas le "comment faire" qui m'intéresse ici mais plutôt le "pourquoi".
Pourquoi le 'table scan' sous MySql ne fonctionne pas comme sous DB2 ?
Pourquoi MySql fait ce choix étrange de passer par un indexe alors que cela devrait être par 'la primary key' ? Je considère qu'il y a un bug de fonctionnement.

Envoyé par fsmrel

Il y a manifestement un consensus ès matière, et ce n’est pas le sage al1_24 qui nous démentira (cf. « Comment les opérateurs ensemblistes trient-ils ? »).

Merci pour le lien, mais ce n'est pas exactement le même problème.
J'ai surtout l'impression de ne pas avoir été bien compris. Peut-être est-ce dû au titre de mon sujet qui laisse penser que j'ai un problème de tri avec ma table.
Ce n'est pas un problème de tri mais juste un problème de compréhension concernant le choix fait par MySql.

Envoyé par fsmrel

Disons que cela vaut pour les KSDS (VSAM), un index alternatif fait effectivement référence au cluster de base.

Vous avez raison, j'ai dû faire une confusion avec KSDS. Désolé pour cette méprise.

J'ai une question subsidiaire concernant DB2.
Pourquoi en DB2, quand on fait l'usage d'une 'Primary Key' ('PSN_PK'), faut-il toujours créer sur cette 'primary key' un 'unique index' ('PSN_XP') ?

En MySql, il n'est pas nécessaire de faire cette déclaration car cela se fait automatiquement.
Donc la 'primary key' est aussi associé à un 'unique index'. Et c'est cet indexe qui va ordonner les lignes dans le tablespace.

Envoyé par fsmrel

Parcourons le résultat à l’aide d’un curseur : je n’ai pas de DB2 for z/OS sous la main mais je sais que les lignes seront délivrées dans l’ordre des IDN_PSN, puisqu’elles sont rangées dans le table space selon la séquence définie par l’index cluster.

C'est bien ce que j'avais compris, en tout cas sous DB2 et vous me confirmez ce que je savais déjà. Alors pourquoi me faire croire (pas vous 'fsmrel') qu'il en serait autrement, même sous DB2 ?
Ici, je ne parle pas des utilisateurs, mais bien de l'ordre selon l'index cluster. Dois-je comprendre que cela ne fonctionne pas ainsi sous MySql ?
Et qu'il faut chercher la cause ailleurs, due au multiprocessing, aux buffers, aux partitionnements, et je ne sais quoi d'autre.

En tout cas merci 'fmsrel' d'avoir pris le temps de me répondre !

Envoyé par SQLPRO

Encore une fois on a répondu à de multiples reprises : il n'y a aucun ordre prédéterminé de restitution des lignes en sortie d'un SELECT, quelque soit le SGBDR, tant que vous ne mettez pas un tri explicite à l'aide de la clause ORDER BY. Comme déjà indiqué, l'ordre de sortie est arbitraire du fait de l'optimisation à l'instant t de la vie des données (indexation, règles sémantiques et statistiques de l'optimiseur, disponibilité des données dans le cache, parallélisme...).

Quand on donne une réponse, il faudrait un tant soi peu qu'elle corresponde à la question posée, non ? Je ne m'intéresse pas à l'ordre de restitution des lignes de mon 'select *' !
Je cherche à comprendre pourquoi MySql préfère utiliser l'index de ma seconde colonne, plutôt que d'utiliser la 'primary key' ?

En gros, vous avez répondu à 'comment il faut faire', et non à 'pourquoi il procède ainsi' !
Sinon, pourquoi aurai-je mis des 'explain' pour indiquer les résultats de MySql ? Juste pour faire joli peut-être ?

@ escartefigue : j'ai fait plusieurs tentative pour vous expliquer mon problème et à chaque fois, vous revenez sur vos idées.
Comme si la solution de mon problème était un vulgaire tri que j'avais oubli de faire. Je le répète encore une fois, mon problème n'est pas comment trier mon résultat avec un "select *".
Mon problème est de comprendre pourquoi MySql utilise l'index sur ma seconde colonne alors qu'il aurait dû utiliser la 'primary key' pour faire un 'full scan' de ma table.

Envoyé par escartefigue

relisez sereinement l'ensemble des réponses sans a priori, oubliez vos préjugés et analysez la documentation, puis, expérimentez ce qui vous a été proposé et analysez les résultats

Et qu'est-ce que vous croyez que je fasse depuis le début ? Vous n'avez rien compris de mon problème. Je le voie fort bien quand je vous remercie et que vous prenez la mouche.
Ce que je cherche à faire par la suite, c'est influencer le choix de MySql pour obtenir le bon résultat. Mais ça, c'est complètement secondaire vis-à-vis de mon problème de compréhension.
Et pour votre gouverne, les tests que j'ai entrepris sur les index sont terminés. Je n'ai aucun autre problème de compréhension à ce sujet, sauf celui qui est à l'origine de ce sujet.

Envoyé par Antoun

Encore une fois, tu supposes sans aucun élément des choses fausses

Je ne suppose rien. C'est juste une interrogation ! J'ai émis un doute quand 'fmsrel' a donné son exemple. Car cela ne correspondait pas à mes connaissances que j'ai sur DB2. Ensuite 'fmsrel' a confirmé ses propos. Et l'ambiguïté s'est dissipée.

@+

Un problème de tri ! [MySQL-5.6]

MySQL

Vue hybride

Discussions similaires

Partager

Partager