Index basé sur l'expression de SQLite

Dans les bases de données SQLite ou SQL, les index sont utilisés pour référencer certaines colonnes d'un tableau. Ces index peuvent être composés d'une ou plusieurs colonnes et peuvent être uniques. Parallèlement à cela, nous pouvons créer des index sur différentes expressions contenant différentes colonnes de n'importe quelle table. Ces expressions pourraient être définies dans la clause de condition où l'instruction SELECT. Ainsi, nous avons décidé d'expliquer l'utilisation des expressions pour créer des indices et comment ils affectent les résultats.

Commençons par cet article en ouvrant le terminal de la coquille d'Ubuntu 20.04 rapidement. Utilisez la clé de raccourci «Ctrl + Alt + T» pour le faire. Après le lancement réussi du terminal Shell, vous devez mettre à jour votre système Linux en utilisant des commandes de mise à jour et de mise à niveau à l'aide d'un package APT. Après cela, ouvrez la base de données SQLite à l'aide de l'instruction «SQLITE3». En ce moment, nous n'avons pas de tables dans la base de données jusqu'à présent, je.e. en utilisant le .commande de tableaux.

Pour créer les index sur les expressions, nous avons besoin d'un tableau dans notre base de données. Nous avons créé un nouveau tableau nommé «Détail» dans notre base de données contenant un total de 3 colonnes de type entier, I.e. ID, vente au détail et vente. La table est vide en ce moment, je.e. Selon la commande SELECT.

Créer un détail de table (id int clé primaire non nul, vente au détail, vente int);
Sélectionner * dans le détail;

Nous devons ajouter quelques enregistrements dans le tableau de détail afin que nous puissions créer les index sur les colonnes après cela. Ainsi, nous avons utilisé l'instruction d'insertion avec le mot-clé en IN suivi par le nom du tableau «Détail» et ses noms de colonne «ID, vente au détail». Les noms de colonne sont suivis par les valeurs de mot-clé et les enregistrements à insérer dans les colonnes. Nous avons inséré un total de 10 enregistrements dans les trois colonnes de ce tableau; Chacun est unique comme indiqué:

Insérer dans les valeurs détaillées (ID, vente au détail, vente) (1, 340, 400);
Insérer dans les valeurs détaillées (ID, vente au détail, vente) (2, 380, 420);
Insérer dans les valeurs détaillées (ID, vente au détail, vente) (3, 205, 230);
Insérer dans les valeurs détaillées (ID, vente au détail, vente) (4, 98, 110);
Insérer dans les valeurs détaillées (ID, vente au détail, vente) (5, 311, 340);
Insérer dans les valeurs détaillées (ID, vente au détail, vente) (6, 100, 120);
Insérer dans les valeurs détaillées (ID, vente au détail, vente) (7, 512, 540);
Insérer dans les valeurs détaillées (ID, vente au détail, vente) (8, 634, 670);
Insérer dans les valeurs détaillées (id, vente au détail, vente) (9, 54, 67);
Insérer dans les valeurs détaillées (ID, vente au détail, vente) (10, 934, 990);

Après avoir utilisé l'instruction SELECT avec le «*» pour afficher tous les enregistrements de colonne du tableau de détail, nous avons montré les 10 enregistrements nouvellement insérés dans le tableau.

Sélectionner * dans le détail;

Avant de créer un index sur les expressions, nous examinons la question simple pour récupérer les enregistrements avec l'utilisation des expressions. De plus, nous utilisons l'option Ordre by dans la requête pour organiser les résultats en fonction de l'ordre décroissant de la durée d'une colonne. Un total de 8 enregistrements sont affichés sur notre écran jusqu'à présent.

Sélectionnez * dans le détail où la longueur (vente au détail)> 2 commande par durée (vente au détail) Desc;

Nous utilisons la même requête dans l'instruction SQLite Query Planner pour voir comment le SQLite fonctionne pour l'exécution de cette requête. Nous avons constaté qu'il doit scanner la table entière «détail» en utilisant le b-are temporaire (arbre équilibré) sur la clause Order By.

Expliquez le plan de requête Sélectionner * dans le détail où la longueur (vente au détail)> 2 commande par durée (vente au détail) Desc;

Créons un index sur la même expression «longueur» sur la colonne de vente au détail d'un détail de table. Nous utilisons l'instruction Create Index pour créer l'index «index_retail».

Créer index index_retail sur les détails (longueur (vente au détail));

Après avoir créé l'index sur la table de détail, nous avons utilisé le planificateur de requête pour voir la structure de travail de la requête. Il montre que la requête a fonctionné sur l'index basé sur l'expression pour récupérer les résultats requis au lieu de rechercher le tableau entier.

Expliquez le plan de requête Sélectionner * dans le détail où la longueur (vente au détail)> 2 commande par durée (vente au détail) Desc;

Après avoir utilisé l'instruction SELECT sur la même expression pour récupérer les enregistrements, les données s'affichent maintenant dans l'ordre décroissant de la longueur de la colonne de vente au détail.

Créons une autre requête de sélection avec une expression. Cette fois, l'expression de la classe Where affichera uniquement les enregistrements où la somme de la colonne de vente au détail et de vente est supérieure à 700.

Sélectionner * dans le détail où le commerce de détail + vente> 700;

Nous avons utilisé l'instruction SELECT précédente avec les colonnes uniques et l'expression «Retail + Sale> 700» qu'il contient, dans l'instruction du planificateur de requête. Le résultat du planificateur de requête montre que la base de données SQLite doit rechercher partout dans le tableau de détail pour récupérer les résultats requis avec cette expression assez inefficace.

Expliquez le plan de requête Sélectionnez ID, commerce de détail, vente, vente au détail + vente à partir de détails où la vente au détail + vente> 700;

Créons un nouvel index sur l'expression «vente au détail + vente» qui est utilisé dans l'instruction de sélection précédente. Nous avons donc utilisé l'instruction Create Index pour générer un nouvel index «index_detail» sur l'expression «vente au détail + vente» d'un tableau de détail. L'index nouvellement fabriqué «index_detail» peut être vu dans la liste des indices selon le «.Instruction des indices ».

Créer Index Index_Detail sur les détails (vente au détail + vente);

Maintenant, essayons l'instruction du planificateur de requête pour la même instruction sélectionnée contenant la vente au détail + vente> 700 expressions qui sont utilisées dans la clause où l'instruction sélectionnée. Après avoir utilisé le planificateur de requête, nous avons constaté que le plan SQLite ne recherchera que les entrées spécifiques d'un détail de table selon l'index «index_detail» contenant une expression.

Expliquez le plan de requête Sélectionnez ID, commerce de détail, vente, vente au détail + vente à partir de détails où la vente au détail + vente> 700;

Modifions l'expression en échangeant les noms des colonnes les uns avec les autres sans changer leur connaissance. Chaque fois que vous utilisez la même instruction SELECT avec le peu de changement dans son expression, je.e. «Vente + détail> 700», il affiche les 5 mêmes résultats car la logique est correcte et la même chose est utilisée dans l'instruction de sélection précédente.

Sélectionner * dans le détail où la vente + commerce de détail> 700;

Après avoir utilisé l'instruction SELECT précédemment modifiée dans l'instruction du planificateur de requête, nous avons constaté que la base de données SQLite doit rechercher sur l'ensemble le tableau «détail» cette requête basée sur l'expression. En effet.

Expliquez le plan de requête Sélectionnez ID, vente au détail, vente, vente + commerce de détail dans les détails où la vente + commerce de détail> 700;

Conclusion

Cet article consiste à créer un index sur différentes expressions. Nous avons essayé d'utiliser l'instruction du planificateur de requête pour expliquer la différence entre l'utilisation d'une expression simple dans une instruction sélectionnée sans aucun index et l'utilisation d'une expression dans une instruction de sélection basée sur un index.