12.Clé primaire et Index

12.1.Clé primaire

Dans les chapitres précédents (jointure et champ auto-incrémenté) nous avons introduit une nouvelle notion sans la décrire précisement. En effet, à chaque élément d'une table nous avons associé un champ 'id', non null,qui identifie de façon unique l'enregistrement et qui nous sert de référence pour établir des liens avec d'autres tables. C'est ce que l'on appelle une clé primaire.

On pourra à la création de la table déclarer un champ (qu'il soit auto-incrémenté ou pas) comme étant une clé primaire par l'utilisation du mot clé PRIMARY KEY juste après la déclaration du type du champ. Comme dans l'exemple suivant:

CREATE TABLE matable (id INTEGER NOT NULL PRIMARY KEY, ...)

La clé primaire pourra être déclarée à la fin de la requête de création de table, notamment si elle implique plusieurs champs comme dans le schéma suivant (si l'on considère une table où les couples (nom/prenom) sont uniques)

CREATE TABLE matable (nom VARCHAR(64) NOT NULL, prenom VARCHAR(64) NOT NULL, ..., PRIMARY KEY(nom,prenom))

Chose extraordinaire la syntaxe est la même quelque soit la base de données utilisées: SQLite, MySQL, PostgreSQL ou Oracle.
Toutefois, contrairement aux autres bases SQLite autorise des valeurs NULL dans les champs des clés primaires (on peut quasiment considérer cela comme un bug)

Pourquoi préciser qu'une clé est primaire?

Comme il s'agit d'un champ qui sert de référence, la base de données est susceptible d'être souvent solicitée pour retrouver l'enregistrement de la table correspondant à l'identifiant 'id' donné. Cette recherche doit donc être la plus rapide possible. La base de données étant informée, elle peut chercher à optimiser cette recherche en introduisant ce que l'on appelle un index (quitte à devoir occuper plus d'espace sur le disque et/ou en mémoire).

12.2.Index

Comme évoqué ci dessus, un index sert à optimiser les recherches. Il peut être créé sur n'importe quel(s) champ(s) d'une table (il n'y a pas que les clés primaires).

L'idée est à peu près la suivante: si le moteur de la base de données passe en revue chaque enregistrement pour retrouver celui dont le champ (que l'on indexera) a une certaine valeur (ex: champ='sql') cela prendra enormément de temps si par malheur la requête porte sur des enregistrements se trouvant en fin de table. Et même une recherche par dichotomie peut ne pas être optimale.

La solution peut alors consister à trier et, en quelque sorte, à établir des pointeurs dans cette table. Pour, par exemple, être capable de dire que les enregistrements ayant dans le champ indexé une valeur commençant par 'A' se trouvent à partir du premier enregistrement, ceux commençant par 'B' se trouvent à partir du 20ème enregistrement, etc. ceux commençant par 'V' à partir de 250ème enregistrement, 'W' à partir du 300ème enregistrement etc. Dans ce cas, il sera très facile de savoir que la recherche ne doit porter que sur les enregistrements entre 250 et 299. Et donc la recherche sera grandement accélérée (surtout si, sur cet échantillon, on procède par dichotomie)

Toutefois, la création et l'utilisation de cet index à un coût: Cela prends du temps (lors de l'ajout/suppression d'enregistrement. Pour le tri et la mise à jour des index) et surtout en occupation disque (ou mémoire) il faut bien stocker quelque part ces "pointeurs". Il n'est donc pas concevable de l'appliquer à tous les champs, c'est pourquoi le concepteur de la base (i.e. vous) doit choisir judicieusement et préciser quels doivent être les champs devant bénéficier de cette indexation.

En fait, les index doivent être utilisés pour les champs souvent utilisés dans des clauses WHERE (à commencer par les clés primaires). Si pour les clés primaires l'index est créé automatiquement (via PRIMARY KEY), pour les autres champs il faut les déclarer de la façon suivante:

CREATE TABLE matable (id int4, nom varchar(64) NOT NULL, INDEX (nom));

Seul un champ défini comme NOT NULL peut être indéxé (c'est du moins vrai pour MySQL)
Contrairement à PRIMARY KEY, INDEX n'implique pas l'unicité du champs. Pour le rendre unique il faut utiliser UNIQUE INDEX.
L'algorithme d'indexation présenté ici, n'est qu'une vulgarisation du principe d'indexation, il ne reflète que très peu la réalité mais permet de prendre conscience de la problématique.
Comme vous pouvez le deviner à partir de l'exemple donné, l'utilisation d'INDEX ne s'applique pas aux recherches du type LIKE '%titi%' (cas d'une recherche ne portant pas sur le début de la valeur).