par Guillaume Lelarge.

Clés primaires


Nous commençons avec la clause where la plus simple et la plus commune : la recherche d'une clé primaire. Pour les exemples de ce chapitre, nous utilisons la table EMPLOYES qui se définie ainsi :

CREATE TABLE employes (
   id_employe        NUMBER         NOT NULL,
   prenom            VARCHAR2(1000) NOT NULL,
   nom               VARCHAR2(1000) NOT NULL,
   date_de_naissance DATE           NOT NULL,
   numero_telephone  VARCHAR2(1000) NOT NULL,
   CONSTRAINT employes_pk PRIMARY KEY (id_employe)
)

La base de données crée automatiquement un index pour la clé primaire. Cela signifie qu'il existe un index sur la colonne ID_EMPLOYE, même si nous n'avons pas exécuté de commande create index.

Astuce

Annexe C« Example Schema » contient des scripts pour peupler la table EMPLOYES avec des données exemples. Vous pouvez l'utiliser pour tester les exemples dans votre propre environnement.

Pour suivre le texte, il suffit de savoir que la table contient 1000 lignes.

La requête suivante utilise la clé primaire pour récupérer le nom d'un employé :

SELECT prenom, nom
  FROM employes 
 WHERE id_employe = 123

La clause where ne peut pas correspondre à plusieurs enregistrements car la clé primaire assure l'unicité des valeurs de la colonne ID_EMPLOYE. La base de données n'a pas besoin de suivre les nœuds feuilles de l'index. Il suffit de parcourir l'arbre de l'index. Nous pouvons utiliser le plan d'exécution pour vérifier :

MySQL

+----+----------+-------+---------+---------+------+-------+
| id | table    | type  | key     | key_len | rows | Extra |
+----+----------+-------+---------+---------+------+-------+
|  1 | employes | const | PRIMARY | 5       |    1 |       |
+----+----------+-------+---------+---------+------+-------+

Oracle

--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |    1 |    2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYES    |    1 |    2 |
|*2 |  INDEX UNIQUE SCAN         | EMPLOYES_PK |    1 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID_EMPLOYE"=123)

PostgreSQL

                QUERY PLAN
-------------------------------------------
 Index Scan using employes_pk on employes
   (cost=0.00..8.27 rows=1 width=14)
   Index Cond: (id_employe = 123::numeric)

SQL Server

|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:employes_pk,
   |               SEEK:employes.id_employe=@1
   |            ORDERED FORWARD)
   |--RID Lookup(OBJECT:employes,
                   SEEK:Bmk1000=Bmk1000
                 LOOKUP ORDERED FORWARD)

Le plan d'exécution Oracle affiche un INDEX UNIQUE SCAN, ce qui corres­pond à l'opération qui ne fait que parcourir l'arbre de l'index. Il utilise complètement la complexité logarithmique de l'index pour trouver l'entrée très rapidement, pratiquement indépendamment de la taille de la table.

Astuce

Le plan d'exécution (quelque fois appelé plan explain ou plan de la requête) montre les étapes réalisées par la base pour exécuter une requête SQL. L'Annexe A explique comment récupérer et lire les plans d'exécution avec d'autres bases de données.

Après avoir accédé à l'index, la base de données doit réaliser une autre étape pour récupérer les données demandées (PRENOM, NOM) à partir du stockage de la table : il s'agit de l'opération TABLE ACCESS BY INDEX ROWID. Cette opération peut devenir un goulet d'étranglement, comme expliqué dans la « Index lents, partie I », mais ce risque est inexistant avec un INDEX UNIQUE SCAN. Cette opération ne peut pas renvoyer plus d'une entrée donc elle ne peut pas déclencher plus d'un accès à la table. Cela signifie que les ingrédients d'une requête lente ne sont pas présents avec un INDEX UNIQUE SCAN.

Clés primaires sans index unique

Une clé primaire n'a pas nécessairement besoin d'un index unique. Vous pouvez aussi utiliser un index non unique. Dans ce cas, la base de données Oracle n'utilise pas l'opération INDEX UNIQUE SCAN mais utilise à la place l'opération INDEX RANGE SCAN. Néanmoins, la contrainte maintiendra l'unicité des clés pour que la recherche dans l'index renvoie au plus une entrée.

Les contraintes différables sont une des raisons pour utiliser des index non uniques pour les clés primaires. En opposition aux contraintes standards, qui sont validées lors de l'exécution de la requête, la base de données repousse la validation des contraintes différables jusqu'au moment où la transaction est validée. Les contraintes différées sont requises pour insérer des données dans des tables ayant des dépendances circulaires.

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license