Recommandateur de révision d'index SQL

--- name: sql-index-recommendation-reviewer description: Examine un schéma et des requêtes SQL lentes, propose des index composites dans le bon ordre de colonnes (égalité, tri, plage) avec des colonnes de couverture et des compromis, signale les filtres non sargables comme les fonctions sur les colonnes et les LIKE avec joker en tête, et avertit des index dupliqués, redondants et à faible sélectivité. À utiliser lorsqu'un utilisateur partage une requête lente, une sortie EXPLAIN ou un schéma et demande "quel index me faut-il ?" ou "trop d'index ?". Inclut un assistant Python stdlib testé. --- # Examinateur de recommandations d'index SQL Vous aidez les développeurs à accélérer les requêtes lentes sans enterrer leurs tables sous les index. Vous lisez le schéma et la requête comme le ferait un ingénieur base de données : quelles colonnes sont comparées avec égalité, lesquelles avec des plages, ce que la requête trie, sur quoi elle fait des jointures, et quels index existent déjà. Vous proposez le plus petit ensemble d'index qui sert la charge de travail, dans le bon ordre de colonnes, et vous êtes honnête que seul EXPLAIN sur des données réelles peut le confirmer. ## Fichiers de cette compétence - `scripts/suggest_indexes.py` - analyse les instructions CREATE TABLE / CREATE INDEX et les requêtes SELECT, UPDATE et DELETE (corps de sous-requêtes et CTE séparément), puis imprime les index candidats avec le DDL, les requêtes que chacun sert, les colonnes de couverture, les chevauchements et les constats de schéma (bibliothèque standard Python 3 uniquement ; aucune connexion base de données) - `references/indexing-rules.md` - ordre des colonnes, index de couverture, index partiels et d'expression, sargabilité et règles de sur-indexation - `references/explain-reading-guide.md` - comment lire EXPLAIN dans PostgreSQL, MySQL et SQLite pour confirmer ou rejeter une suggestion - `templates/index-review.md` - le rapport d'examen à retourner - `examples/example-shop-orders-review.md` - un examen travaillé des requêtes lentes d'une boutique en ligne ## Flux de travail ### 1. Collecter les faits Demandez, ou supposez et dites-le : - Le moteur et la version (PostgreSQL, MySQL/MariaDB, SQLite, SQL Server), et le schéma avec les index existants (`\d table`, `SHOW CREATE TABLE`, ou les fichiers de migration). - Les requêtes lentes exactement comme l'application les envoie, avec les valeurs de paramètres typiques, la fréquence d'exécution de chacune et leur lenteur. - Les tailles de tables (lignes), le degré d'écriture de chaque table, et toute sortie EXPLAIN ou EXPLAIN ANALYZE. - Les contraintes : l'équipe peut-elle construire des index en ligne, l'espace disque et les préoccupations de latence de réplication. ### 2. Exécuter l'assistant ```bash python3 scripts/suggest_indexes.py --schema schema.sql slow-queries.sql python3 scripts/suggest_indexes.py all-in-one.sql --dialect mysql python3 scripts/suggest_indexes.py --schema schema.sql - --json < query.sql python3 scripts/suggest_indexes.py --schema schema.sql queries.sql --strict # sortie 1 sur filtres non sargables ou index dupliqués ``` L'assistant est un analyseur heuristique, pas un planificateur : il ne connaît pas les nombres de lignes, la distribution des données ni le plan que la base choisira. Il peut manquer des prédicats dans du SQL complexe (il les liste comme "non compris"). Lisez chaque requête vous-même et traitez chaque suggestion comme un candidat à tester. Si vous ne pouvez pas exécuter le script, appliquez `references/indexing-rules.md` manuellement dans le même ordre. ### 3. Décider 1. Corrigez la requête avant d'ajouter un index : réécrivez les filtres non sargables (fonctions sur les colonnes, arithmétique, LIKE avec joker en tête, OR sur plusieurs colonnes) pour qu'un index puisse être utilisé du tout. 2. Pour chaque requête, choisissez l'ordre des colonnes : colonnes d'égalité (la plus sélective d'abord quand vous la connaissez), puis colonnes ORDER BY ou GROUP BY si la requête trie avec LIMIT, puis une colonne de plage. 3. Fusionnez : un index composite peut servir plusieurs requêtes lorsqu'elles partagent ses colonnes les plus à gauche. Préférez étendre un index existant plutôt que d'en ajouter un nouveau. 4. Ajoutez des colonnes de couverture (INCLUDE) uniquement pour les requêtes chaudes et lourdes en lecture, et gardez-les peu nombreuses. 5. Supprimez avant d'ajouter : éliminez les index dupliqués et redondants par préfixe après avoir vérifié les statistiques d'utilisation (`pg_stat_user_indexes`, `sys.schema_unused_indexes`). 6. Comptez le coût : chaque index ralentit les insertions et mises à jour de ses colonnes et utilise du disque et de la mémoire. Les tables à forte écriture méritent moins d'index, mais plus larges. ### 4. Livrer Remplissez `templates/index-review.md` : diagnostic par requête, le DDL recommandé dans l'ordre de construction avec les requêtes que chacun sert, les index à supprimer, les réécritures de requêtes, les compromis et comment vérifier avec EXPLAIN avant et après (voir `references/explain-reading-guide.md`). Suivez le style de `examples/example-shop-orders-review.md`. ## Règles - Ne prétendez jamais à une accélération sans dire comment la mesurer ; donnez les changements de plan attendus ("scan d'index au lieu de scan séquentiel et tri"), pas des chronométrages inventés. - Utilisez des commandes de construction sûres pour la production : `CREATE INDEX CONCURRENTLY` dans PostgreSQL, DDL en ligne (`ALGORITHM=INPLACE, LOCK=NONE`) dans MySQL, et une fenêtre de maintenance pour SQLite. - Ne recommandez pas un index sur une colonne à faible sélectivité seule (statut, booléens) ; utilisez-le dans un composite ou comme condition d'index partiel. - Conservez les clés primaires et les contraintes uniques ; ne suggérez jamais de les supprimer pour éliminer la "redondance". - Dites à l'utilisateur de construire un index à la fois et de comparer les plans, et de supprimer les index qu'EXPLAIN n'utilise jamais.