Indexation et optimisation des requêtes

1) Objectifs d'indexation et d'optimisation

Latence : réduction des P50/P95/P99.
Bande passante : croissance QPS sans mise à l'échelle horizontale.
Prévisibilité : plans stables et pas de « sauts » de temps de réponse.
Économies : moins d'IO/CPU, moins de facture cloud.
Fiabilité : réduire les blocages et les débris grâce à des accès corrects.

• Toute optimisation doit rester correcte et cohérente.
• Suivre l'effet dans les métriques et les logs des plans.

2) Structures d'index de base et quand les appliquer

2. 1 B-Tree (défaut)

Égal/plage, tri, 'ORDER BY'.
Bon pour la plupart des filtres temps/ID/statut.

2. 2 Hash

Les égales pures ('=') sont moins chères en mémoire, mais sans ordre (PG : restrictions enlevées, mais choix encore de niche).

2. 3 GIN / GiST (PostgreSQL)

GIN : tableaux/clés JSONB, texte intégral (tsvector), containment ('@>').
GiST : géo, gammes, kNN.

2. 4 BRIN (PostgreSQL)

Indice super bon marché par tables « naturellement triées » (append-only par temps). Bon pour les séries time avec de grandes tables.

2. 5 Bitmap (MySQL/InnoDB : non natif ; DW-BASE DE DONNÉES/OLAP)

Efficace pour la faible cardinalité et les facettes, plus souvent dans les coffres à colonnes.

2. 6 Indices de colonnes (ClickHouse)

Primary key + data skipping (minmax), secondary через `skip indexes` (bloom, set).
Demandes OLAP avec agrégations et gammes.

2. 7 Indices inversés (Elasticsearch/OpenSearch)

Texte intégral, facettes, recherche hybride. Pour des filtres précis, utilisez les champs keyword et doc values.

2. 8 MongoDB

Single, composite, multikey (tableaux), partial, TTL, text, hashed (pour le sharding à clé uniforme).

3) Conception de clés et d'index composites

3. 1 Règle du préfixe de gauche

L'ordre des champs dans l'index détermine l'utilisation.
Requête 'WHERE tenant_id = ? AND created_at >=? ORDER BY created_at DESC` → индекс `(tenant_id, created_at DESC, id DESC)`.

3. 2 Tie-breaker

Ajouter une queue unique (généralement "id') pour le tri stable et la seek-pagination.

3. 3 Indices partiels/filtrés

sql
CREATE INDEX idx_orders_paid_recent
ON orders (created_at DESC, id DESC)
WHERE status = 'paid' AND created_at > now() - interval '90 days';

3. 4 Indices de couverture

sql
CREATE INDEX idx_user_lastseen_inc ON users (tenant_id, last_seen DESC) INCLUDE (email, plan);

3. 5 Fonctionnels/calculables

sql
CREATE INDEX idx_norm_email ON users (lower(email));

4) Lot et Chardonnage

4. 1 Lot (héritage natif/tabulaire ; MySQL RANGE/LIST)

La rotation temporelle des lots ('daily/weekly') simplifie 'VACUUM/DELETE'.
Les indices sont des lots locaux → moins de B-Tree, le plan est plus rapide.

sql
CREATE TABLE events (
tenant_id bigint,
ts timestamptz,
...
) PARTITION BY RANGE (ts);

4. 2 Clé de lot

Dans OLTP, par 'tenant _ id' (localisation de charge).
Dans time-series/OLAP, par 't' (requêtes de plage).
Hybride : '(tenant_id, ts)' + sous-partie.

4. 3 Chardonnages

Consistance hashing/range-shard par 'tenant _ id' ou par temps.
Une requête croisée-charde → scatter-gather et k-way merge ; gardez le per-shard cursor.

5) Statistiques, cardinalité et plans

5. 1 Statistiques à jour

Activer l'auto-analyse ('autovacuum/autoanalyze'), agrandir 'default _ statistics _ target' pour les distributions « sales ».

5. 2 Statistiques avancées (PG)

sql
CREATE STATISTICS stat_user_country_city (dependencies) ON country, city FROM users;
ANALYZE users;

5. 3 Plan d'exécution

• `Rows`, `Loops`, `Actual time`, `Shared Read/Hit`, `Recheck Cond`.
• Типы join: Nested Loop, Hash Join, Merge Join.
• Seq Scan vs Index Scan/Only Scan/Bitmap Heap Scan.

5. 4 Stabilité des plans

Le paramétrage (statements prédéfinis) peut « bouillir » sur un mauvais plan. Utilisez plan cache guardrails (PG : 'plan _ cache _ mode = force_custom_plan' pour les requêtes problématiques) ou un « correctif » de constantes.

6) Optimisation des join-s et triage

6. 1 Stratégies

Nested Loop : petit index externe, rapide sur l'intérieur.
Hash Join : grands ensembles, assez de mémoire sous la table hash.
Merge Join : entrées triées, bénéfique dans l'ordre déjà disponible.

6. 2 Indices sous join

Pour 'A JOIN B ON B.a_id = A.id' → l'indice sur 'B (a_id)'.
Pour le filtre après join, l'index sur les colonnes du filtre de la table interne.

6. 3 Tri

Éviter 'ORDER BY' sans index approprié ; le tri sur de grands ensembles se fait par mémoire/disque.

7) Réécrire les requêtes (query rewrite)

Débarrassez-vous des « flocons de neige » des sous-demandes ; déployez-le dans JOIN.
Utilisez CTE-inline (PG ≥12 inlines CTE par défaut, mais 'MATERIALIZED' peut enregistrer un résultat intermédiaire si nécessaire).
Supprimer 'SELECT' → énumérer les champs (économie IO/réseau).
Transférez les calculs de 'WHERE' à la forme indexée (colonnes préconçues).
Agrégations : tableaux sommaires préliminaires/représentations matérialisées avec mise à jour incrémentielle.

8) Batch, limitation et pagination

Batch-insert/update : paquets 500-5000 au lieu d'une pièce.
Seek-pagination par '(sort_key, id)' au lieu de deep 'OFFSET'.
Limite le jeu avant le tri/joyau (push-down 'LIMIT').

9) Mise en cache et dénormalisation

Query-cache de niveau application (clé = SQL + bind-vars + version des droits).
Vues materialisées pour les agrégats lourds ; plan de rotation/refrain.
Dénormalisation : Stockez les champs calculables fréquemment lus (prix avec réduction), mais avec un déclencheur/tâche de fond pour la cohérence.
Redis comme L2 pour les clés « chaudes » (avec TTL et handicap par évènement).

10) Spécificité des moteurs populaires

10. 1 PostgreSQL

Индексы: B-Tree, Hash, GIN/GiST, BRIN, partial, functional, INCLUDE.

sql
CREATE INDEX idx_orders_tenant_created_desc
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (amount, status);

sql
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('russian', title          ' '          body));

10. 2 MySQL/InnoDB

sql
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; -- check risk-free plans

Statistiques par histogramme ('ANALYZE TABLE... UPDATE HISTOGRAM` в 8. 0).

10. 3 ClickHouse

Clé primaire = tri ; 'ORDER BY (tenant_id, ts, id)'.

sql
CREATE TABLE events (
tenant_id UInt64,
ts DateTime64,
id UInt64,
payload String,
INDEX idx_bloom_payload payload TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (tenant_id, ts, id);

10. 4 MongoDB

js db. orders. createIndex({ tenant_id: 1, created_at: -1, _id: -1 });
db. orders. createIndex({ status: 1 }, { partialFilterExpression: { archived: { $ne: true } } });

Utilisez 'hint ()' pour diagnostiquer, regardez 'covered query'.

10. 5 Elasticsearch/OpenSearch

Keyword vs texs du champ ; doc_values pour le tri/agrégats.
Segmentation heap : agrégations - heavy ; limitez 'size' et utilisez 'composite' de l'agrégation (échantillonnage page par page).
N'incluez pas d'analyseurs lorsque vous souhaitez une comparaison précise.

11) Concurrence, verrouillage et MVCC

Transactions courtes ; éviter les lectures « longues » sous 'REPETABLE READ' sans besoin.
Les opérations d'index prennent également des verrous (réduction de write throughput).
Prévoyez une indexation en ligne : 'CREATE INDEX CONCURRENTLY' (PG), 'ALGORITHM = INPLACE '/' ONLINE' (MySQL).
Insérez dans la queue l'heure/ID → les « pages chaudes » de l'index ; répartir la clé (UUIDv7/sel).

12) Observabilité et SLO

• "db _ query _ latency _ ms' (P50/P95/P99) par le nom de la requête.
• `rows_examined`, `rows_returned`, `buffer_hit_ratio`.
• `deadlocks`, `lock_wait_ms`, `temp_sort_disk_usage`.
• Part des plans avec 'Seq Scan'où 'Index Scan' était attendu.
• Alerts de régression lors du changement de version/paramètres de la base de données.

• Activez le slow query log avec un seuil (par exemple, 200 ms).
• Corrélation des requêtes avec les spans (trace_id).
• Retirez les plans des requêtes problématiques et enregistrez-les dans le référentiel objet pour une rétrospective.

• P95 des lectures '<= 150 ms' avec 'LIMIT <= 50' et « chaud » tenant.
• P95 les entrées '<= 200 ms'pour les battes jusqu'à 1000 lignes.

13) Sécurité et multi-ténacité

Les index par champs de contrôle d'accès ('tenant _ id', 'owner _ id') sont obligatoires.
Les politiques (RLS/ABAC) doivent être avant le filtre ; sinon, l'optimiseur planifie mal.
Ne pas indexer les champs sensibles en ouvert ; utilisez des hashs/tokens.

14) Anti-modèles

Deep 'OFFSET' sans alternative seek-cursor.
Un index par tout est la surcharge de mémoire et write-path.
'SELECT 'dans les chemins critiques.
Fonctions au-dessus de la colonne dans 'WHERE' sans index fonctionnel.
Des plans instables à cause d'anciennes statistiques.
L'absence de 'ORDER BY' en attente d'un ordre stable.
Index pour index : ROI <0 en raison de l'enregistrement/support coûteux.

15) Chèque de mise en œuvre

1. Le top N des demandes de QPS et de temps → choisir 3-5 candidats.
2. Retirer les plans 'EXPLAIN ANALYZE', vérifier la cardinalité vs réelle.
3. Concevoir des index : ordre des champs, INCLUDE/partial/functional.
4. Mettre en place un lot pour les grandes tables (clés temporelles/tenantes).
5. Réécrire les requêtes : supprimer 'SELECT', insérer des CTE simples, limiter l'ensemble.
6. Activer le batch et la seek-pagination.
7. Configurer le cache : L1/L2, invalidation par événement.
8. Introduire la surveillance des plans et slow-logs, alertes pour les régressions.
9. Effectuer des tests de charge avec une distribution réelle des données.
10. Mettre à jour les heidlines pour le développement (hints ORM, indexation, limites).

16) Exemples « avant/après »

sql
SELECT FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

sql
-- Индекс: (status, created_at DESC, id DESC) INCLUDE (amount, currency)
SELECT id, amount, currency, created_at
FROM orders
WHERE status = 'paid'
AND (created_at, id) < (:last_ts,:last_id)   -- seek
ORDER BY created_at DESC, id DESC
LIMIT 50;

17) ORM et protocoles API

Évitez N + 1 : échantillons gourmands ('includes', 'JOIN FETCH', 'preload').
Projections explicites des champs, paginate par le curseur.
gRPC/REST : limitez 'page _ size', fixez 'sort', utilisez des jetons opaques.
Plan-cache : utiliser le paramétrage ; ne générez pas de SQL « uniques » pour chaque appel.

18) Migrations et exploitation

Ajoutez les index en ligne et balancez comme INVISIBLE/CONCURRENTLY, testez les plans, puis changez.
Révisions des indices - nettoyage sanitaire régulier : doublons, non utilisés, « morts » pour les vieilles fiches.
Plan de rotation des lots (drop des anciens) et planning « VACUUM/OPTIMIZE ».

19) Résumé

L'optimisation des requêtes est l'ingénierie des systèmes : clés et index corrects, plans soignés, partitionnement et chardonnage réfléchi, discipline dans les requêtes et l'ORM, mise en cache et observabilité. En respectant les schémas décrits, vous obtiendrez un système rapide, prévisible et économique, résistant à la croissance des données et à la charge de travail.