SQL : Indexes Flashcards

Question 1

Q

Qu’est-ce qu’un index SQL et à quoi sert-il ?

Answer

A

Un index SQL est une structure de données auxiliaire qui permet de localiser rapidement des lignes dans une table sans scanner toute la table.

Analogie : C’est comme l’index d’un livre : au lieu de lire toutes les pages, vous consultez l’index qui vous dit exactement quelle page regarder.

Exemple :
```
– Sans index : scan de 1,000,000 lignes (10 secondes)
SELECT * FROM employes WHERE nom = ‘Alice’;

– Avec index : recherche binaire (0.01 seconde)
CREATE INDEX idx_nom ON employes(nom);
SELECT * FROM employes WHERE nom = ‘Alice’;
```

Pourquoi : Les index transforment des recherches linéaires O(n) en recherches logarithmiques O(log n), accélérant les requêtes de 100x à 1000x.

Question 2

Q

Quel est le mnémonique pour savoir quand créer un index ?

Answer

A

Mnémonique : INDICES

Intensité de lecture (beaucoup de SELECT)
Nombre de valeurs distinctes (haute cardinalité)
Dans WHERE, JOIN, ORDER BY fréquemment
Ignorer les petites tables (< 1000 lignes)
Créer après analyse EXPLAIN
Exclure les colonnes rarement utilisées
Surveiller l’impact sur les écritures

Phrase : “INDICES guide vos décisions d’indexation”

Application : Avant de créer un index, vérifiez TOUS ces critères !

Question 3

Q

Quelle est la différence entre B-Tree et Hash index ?

Answer

A

Tableau comparatif :

Mnémonique : “BHFS (Beef Steak)”
- B-Tree : Par défaut, polyvalent
- Hash : Égalités exactes uniquement

Exemple B-Tree :

CREATE INDEX idx_nom ON employes(nom);
SELECT * FROM employes WHERE nom = 'Alice';  -- OK
SELECT * FROM employes WHERE nom > 'A';      -- OK
SELECT * FROM employes ORDER BY nom;         -- OK

Exemple Hash :
```
CREATE INDEX idx_email USING HASH ON employes(email);
SELECT * FROM employes WHERE email = ‘alice@ex.com’; – OK
SELECT * FROM employes WHERE email > ‘a’; – NON utilisé !
~~~

Question 4

Q

Quelle est la règle EPS pour les index composites ?

Answer

A

Règle EPS : ordre optimal des colonnes dans un index composite

Égalités d’abord (WHERE col = val)
Plages ensuite (WHERE col > val, BETWEEN)
Sort à la fin (ORDER BY col)

Exemple de requête :

SELECT * FROM employes
WHERE dept_id = 5          -- Égalité
  AND salaire > 50000      -- Plage
ORDER BY date_embauche;    -- Sort

Index optimal :

CREATE INDEX idx_optimal ON employes(
    dept_id,           -- É : Égalité d'abord
    salaire,           -- P : Plage ensuite
    date_embauche      -- S : Sort à la fin
);

Pourquoi : L’ordre des colonnes est CRITIQUE. Un index (A, B) n’est PAS équivalent à (B, A). L’index est utilisé de gauche à droite (règle du préfixe de gauche).

Question 5

Q

Qu’est-ce qu’un index couvrant (covering index) ?

Answer

A

Un index couvrant contient TOUTES les colonnes nécessaires à une requête, évitant de retourner à la table.

Sans index couvrant :

CREATE INDEX idx_dept ON employes(dept_id);
SELECT nom, salaire FROM employes WHERE dept_id = 5;
-- 1. Recherche dans idx_dept
-- 2. Retour à la table pour nom et salaire
-- Coût : 2 opérations

Avec index couvrant :

CREATE INDEX idx_covering ON employes(dept_id, nom, salaire);
SELECT nom, salaire FROM employes WHERE dept_id = 5;
-- 1. Tout dans l'index (Index Only Scan)
-- Coût : 1 opération (2x plus rapide !)

Syntaxe INCLUDE (SQL Server, PostgreSQL 11+) :

CREATE INDEX idx_dept ON employes(dept_id) INCLUDE (nom, salaire);

Pourquoi : Élimine les Table Lookups, réduisant les I/O disque. Peut diviser le temps de requête par 2-3.

Question 6

Q

Qu’est-ce qu’un index partiel et quand l’utiliser ?

Answer

A

Un index partiel (ou filtré) indexe seulement un sous-ensemble de lignes avec une condition WHERE.

Exemple :
```
– Index partiel : seulement les employés actifs (95% des cas)
CREATE INDEX idx_actifs ON employes(nom)
WHERE est_actif = TRUE;

– Requête qui l’utilise
SELECT * FROM employes WHERE nom = ‘Alice’ AND est_actif = TRUE;
– ✅ Utilise idx_actifs

– Requête qui ne l’utilise PAS
SELECT * FROM employes WHERE nom = ‘Alice’ AND est_actif = FALSE;
– ❌ N’utilise pas idx_actifs (condition différente)
```

Avantages :
- Index 20x plus petit (5% vs 100%)
- Plus rapide (moins de données à scanner)
- Moins d’espace disque
- Écritures plus rapides

Cas d’usage :

CREATE INDEX idx_stock_faible ON produits(nom) WHERE stock < 10;
CREATE INDEX idx_recentes ON commandes(client_id) WHERE date > '2024-01-01';

Pourquoi : Si 95% de vos données sont dans un état (actif, récent, etc.), indexer seulement cette portion est plus efficace.

Question 7

Q

Quelle est la différence entre Clustered et Non-Clustered index ?

Answer

A

Tableau comparatif :

Visualisation :
```
CLUSTERED sur ‘id’ : lignes physiquement triées par id
┌────┬───────┐
│ 1 │ Bob │
│ 2 │ David │
│ 3 │ Alice │
│ 5 │ Claire│
└────┴───────┘

NON-CLUSTERED : structure séparée avec pointeurs
Index idx_nom:
Alice → ligne 3
Bob → ligne 1
Claire → ligne 5
```

Exemple :
```
– MySQL : PRIMARY KEY est automatiquement clustered
CREATE TABLE employes (
id INT PRIMARY KEY, – Clustered automatique
nom VARCHAR(100)
);

– SQL Server : explicite
CREATE CLUSTERED INDEX idx_id ON employes(id);
```

Pourquoi : L’index clustered détermine l’ordre physique sur le disque. Choisissez une colonne fréquemment utilisée dans les plages.

Question 8

Q

Quel est l’impact des index sur les performances SELECT vs INSERT ?

Answer

A

Impact sur les lectures (SELECT) :
```
Sans index : Table Scan de 1,000,000 lignes
→ Temps : 10 secondes

Avec index : Index Seek
→ Temps : 0.01 seconde
→ Gain : 1000x plus rapide ✅
```

Impact sur les écritures (INSERT/UPDATE/DELETE) :
```
Sans index :
INSERT INTO employes VALUES (…);
→ Temps : 0.001 seconde

Avec 10 index :
INSERT INTO employes VALUES (…);
→ Écriture table + mise à jour 10 index
→ Temps : 0.010 seconde
→ Perte : 10x plus lent ❌
```

Règle d’équilibre :
- 90% lectures : 10-20 index OK
- 50% écritures : 3-5 index seulement
- 80% écritures : 1-2 index (clés primaires/uniques seulement)

Pourquoi : Chaque index accélère les lectures mais ralentit les écritures. Trouvez le bon équilibre selon votre charge de travail.

Question 9

Q

Quand créer un index et quand l’éviter ?

Answer

A

✅ Créez un index quand :
- Colonne dans WHERE, JOIN, ORDER BY fréquemment
- Table > 1000 lignes
- Haute cardinalité (ex: email, nom)
- Lectures > 80% des opérations
- EXPLAIN montre un Seq Scan lent

❌ Évitez un index quand :
- Petite table (< 1000 lignes)
- Faible cardinalité (ex: sexe M/F, booléen)
- Écritures > 50% des opérations
- Colonne rarement recherchée
- Index redondant (déjà couvert)

Exemples :
```
– ✅ BON : haute cardinalité, recherches fréquentes
CREATE INDEX idx_email ON users(email);

– ❌ MAUVAIS : faible cardinalité (2 valeurs)
CREATE INDEX idx_sexe ON users(sexe);

– ❌ MAUVAIS : redondant
CREATE INDEX idx_dept ON employes(dept_id);
CREATE INDEX idx_dept_sal ON employes(dept_id, salaire);
– idx_dept est inutile ! (idx_dept_sal suffit pour dept_id)
~~~

Question 10

Q

Quel est le problème avec les fonctions sur colonnes indexées ?

Answer

A

Problème : Les fonctions empêchent l’utilisation des index standards.

❌ Mauvais :
```
– Index créé
CREATE INDEX idx_nom ON employes(nom);

– Fonction sur colonne indexée = index NON utilisé !
SELECT * FROM employes WHERE UPPER(nom) = ‘ALICE’;
– ❌ Table Scan (index inutilisé)

SELECT * FROM employes WHERE YEAR(date_embauche) = 2025;
– ❌ Table Scan (index inutilisé)
```

✅ Solutions :
```
– Option 1 : Éviter les fonctions
SELECT * FROM employes WHERE nom = ‘Alice’;
– ✅ Index utilisé

– Option 2 : Index fonctionnel (PostgreSQL)
CREATE INDEX idx_nom_upper ON employes(UPPER(nom));
SELECT * FROM employes WHERE UPPER(nom) = ‘ALICE’;
– ✅ Index utilisé

– Option 3 : Colonne calculée (SQL Server)
ALTER TABLE employes ADD annee AS YEAR(date_embauche);
CREATE INDEX idx_annee ON employes(annee);
SELECT * FROM employes WHERE annee = 2025;
– ✅ Index utilisé
~~~

Question 11

Q

Pourquoi l’ordre des colonnes est-il critique dans un index composite ?

Answer

A

Règle du préfixe de gauche : Un index composite (A, B, C) peut être utilisé pour A, (A, B), ou (A, B, C), mais PAS pour B, C, ou (B, C).

Exemple :
```
CREATE INDEX idx_abc ON employes(dept_id, salaire, date_embauche);

– ✅ Index utilisé :
WHERE dept_id = 5
WHERE dept_id = 5 AND salaire > 50000
WHERE dept_id = 5 AND salaire > 50000 ORDER BY date_embauche

– ❌ Index PAS utilisé :
WHERE salaire > 50000
WHERE date_embauche > ‘2020-01-01’
ORDER BY salaire
```

Ordre correct vs incorrect :
```
– ❌ Mauvais ordre pour cette requête
CREATE INDEX idx_bad ON employes(salaire, dept_id);
SELECT * FROM employes WHERE dept_id = 5 AND salaire > 50000;
– N’utilise pas efficacement l’index

– ✅ Bon ordre (égalité avant plage)
CREATE INDEX idx_good ON employes(dept_id, salaire);
SELECT * FROM employes WHERE dept_id = 5 AND salaire > 50000;
– Utilise l’index optimalement
```

Pourquoi : L’index est parcouru de gauche à droite. Si la première colonne n’est pas dans WHERE, l’index est inutile.

Question 12

Q

Comment utiliser EXPLAIN pour vérifier les index ?

Answer

A

EXPLAIN montre le plan d’exécution et si les index sont utilisés.

Syntaxe de base :
```
EXPLAIN SELECT * FROM employes WHERE dept_id = 5;

– PostgreSQL / MySQL
EXPLAIN ANALYZE SELECT …; – Avec temps réel
```

Interpréter les résultats :

❌ Mauvais signe (lent) :
```
Seq Scan on employes (cost=0.00..15000.00 rows=1000000)
Filter: (dept_id = 5)

– “Seq Scan” = Table Scan = LENT (lit toute la table)
```

✅ Bon signe (rapide) :
```
Index Scan using idx_dept on employes (cost=0.42..8.44 rows=1)
Index Cond: (dept_id = 5)

– “Index Scan” = utilise l’index = RAPIDE
– “Index Only Scan” = index couvrant = TRÈS RAPIDE
```

Exemple complet :
```
– Analyser AVANT index
EXPLAIN ANALYZE SELECT * FROM employes WHERE nom = ‘Alice’;
– Seq Scan, temps : 2500 ms

– Créer l’index
CREATE INDEX idx_nom ON employes(nom);

– Analyser APRÈS index
EXPLAIN ANALYZE SELECT * FROM employes WHERE nom = ‘Alice’;
– Index Scan using idx_nom, temps : 0.5 ms (5000x plus rapide !)
~~~

Question 13

Q

Comment maintenir les index (fragmentation et statistiques) ?

Answer

A

Problèmes de maintenance :

Fragmentation : index désorganisés après beaucoup d’INSERT/UPDATE/DELETE
Statistiques obsolètes : mauvais plans d’exécution

Vérifier la fragmentation (SQL Server) :
```
SELECT OBJECT_NAME(object_id), avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘LIMITED’)
WHERE avg_fragmentation_in_percent > 10;

– < 10% : OK
– 10-30% : REORGANIZE
– > 30% : REBUILD
```

Reconstruire les index :
```
– SQL Server
ALTER INDEX idx_nom ON employes REORGANIZE; – Léger
ALTER INDEX idx_nom ON employes REBUILD; – Complet

– MySQL
OPTIMIZE TABLE employes;

– PostgreSQL
REINDEX INDEX idx_nom;
VACUUM ANALYZE employes; – + statistiques
```

Mettre à jour les statistiques :
```
– PostgreSQL
ANALYZE employes;

– MySQL
ANALYZE TABLE employes;

– SQL Server
UPDATE STATISTICS employes;
```

Automatisation : Planifier mensuel ou hebdomadaire (cron, SQL Agent, autovacuum).

Question 14

Q

Qu’est-ce qu’un index redondant et comment l’identifier ?

Answer

A

Index redondant : index déjà couvert par un autre index composite.

Règle : Un index composite (A, B) rend inutile un index (A), MAIS un index (A) ne rend PAS inutile (A, B).

Exemples redondants :
```
– ❌ REDONDANT
CREATE INDEX idx_dept ON employes(dept_id);
CREATE INDEX idx_dept_sal ON employes(dept_id, salaire);
– idx_dept est INUTILE (idx_dept_sal suffit pour dept_id seul)

– ❌ REDONDANT
CREATE INDEX idx_nom ON employes(nom);
CREATE INDEX idx_nom_prenom ON employes(nom, prenom);
– idx_nom est INUTILE
```

Solution :
```
– Supprimer les redondants
DROP INDEX idx_dept;
DROP INDEX idx_nom;

– Garder seulement les composites
```

Identifier les redondants :

-- SQL Server
SELECT i1.name, i2.name
FROM sys.indexes i1
JOIN sys.indexes i2 ON i1.object_id = i2.object_id
WHERE i1.index_id < i2.index_id
  AND [vérifier préfixe commun];

Pourquoi : Index redondants gaspillent de l’espace disque et ralentissent les écritures sans bénéfice.

Question 15

Q

Qu’est-ce que la cardinalité et pourquoi est-elle importante pour les index ?

Answer

A

Cardinalité = nombre de valeurs distinctes dans une colonne.

Haute cardinalité (bon pour index) :
- email : 1,000,000 valeurs distinctes sur 1,000,000 lignes
- nom : 50,000 valeurs distinctes sur 1,000,000 lignes
- Sélectivité : ~ 1 ligne par valeur

Faible cardinalité (mauvais pour index) :
- sexe : 2 valeurs (M/F) sur 1,000,000 lignes
- booléen : 2 valeurs (TRUE/FALSE)
- Sélectivité : 500,000 lignes par valeur

Impact :
```
– ✅ BON : haute cardinalité
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = ‘alice@ex.com’;
– Résultat : 1 ligne (très sélectif) ✅

– ❌ MAUVAIS : faible cardinalité
CREATE INDEX idx_sexe ON users(sexe);
SELECT * FROM users WHERE sexe = ‘M’;
– Résultat : 500,000 lignes (50% de la table) ❌
– L’index n’aide pas (Table Scan serait plus rapide)
```

Règle : Créez des index sur colonnes avec cardinalité > 5% du nombre de lignes. Sinon, l’index est inefficace.

SQL : Indexes Flashcards

(15 cards)