Optimisation des index SQLite

Introduction

Dans ce labo, vous apprendrez comment optimiser les performances d'une base de données SQLite en utilisant des index (indexes). Vous créerez des index à une seule colonne (single-column indexes) pour améliorer la vitesse des requêtes (query speed), en vous concentrant sur l'application pratique et l'analyse. Vous apprendrez également à analyser les plans d'exécution de requêtes (query plans) et à supprimer les index redondants.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/sort_data("Sort With ORDER BY") sqlite/SQLiteGroup -.-> sqlite/build_index("Create Single Index") sqlite/SQLiteGroup -.-> sqlite/clear_index("Drop Single Index") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") subgraph Lab Skills sqlite/query_where -.-> lab-552552{{"Optimisation des index SQLite"}} sqlite/sort_data -.-> lab-552552{{"Optimisation des index SQLite"}} sqlite/build_index -.-> lab-552552{{"Optimisation des index SQLite"}} sqlite/clear_index -.-> lab-552552{{"Optimisation des index SQLite"}} sqlite/verify_table -.-> lab-552552{{"Optimisation des index SQLite"}} end

Créer une base de données et une table

Dans cette étape, vous allez créer une base de données SQLite et une table employees (employés). Vous insérerez ensuite des exemples de données dans la table.

Tout d'abord, ouvrez votre terminal dans la VM LabEx. Votre chemin d'accès par défaut est /home/labex/project.

Pour créer une base de données SQLite nommée my_database.db, exécutez la commande suivante :

sqlite3 my_database.db

Cette commande crée un nouveau fichier de base de données SQLite nommé my_database.db dans votre répertoire de projet et ouvre l'interpréteur (shell) SQLite.

Ensuite, créez la table employees avec la structure suivante :

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    department TEXT
);

Cette instruction SQL crée une table nommée employees avec cinq colonnes : id, first_name, last_name, email et department. La colonne id est définie comme clé primaire (primary key), ce qui signifie qu'elle doit contenir des valeurs uniques.

Maintenant, insérez des exemples de données dans la table employees :

INSERT INTO employees (first_name, last_name, email, department) VALUES
('John', 'Doe', '[email protected]', 'Sales'),
('Jane', 'Smith', '[email protected]', 'Marketing'),
('Robert', 'Jones', '[email protected]', 'Engineering'),
('Emily', 'Brown', '[email protected]', 'Sales'),
('Michael', 'Davis', '[email protected]', 'Marketing');

Cela insérera cinq lignes de données dans la table employees.

Pour vérifier que les données ont été insérées correctement, exécutez la commande suivante :

SELECT * FROM employees;

Vous devriez voir la sortie suivante :

1|John|Doe|[email protected]|Sales
2|Jane|Smith|[email protected]|Marketing
3|Robert|Jones|[email protected]|Engineering
4|Emily|Brown|[email protected]|Sales
5|Michael|Davis|[email protected]|Marketing

Créer un index

Dans cette étape, vous allez créer un index sur la colonne last_name (nom de famille) de la table employees.

Les index (indexes) sont des tables de consultation spéciales que le moteur de recherche de la base de données peut utiliser pour accélérer la récupération des données.

Pour créer un index nommé idx_lastname sur la colonne last_name, exécutez la commande suivante :

CREATE INDEX idx_lastname ON employees (last_name);

Cette instruction SQL crée un index nommé idx_lastname sur la colonne last_name de la table employees.

Pour vérifier que l'index a été créé, vous pouvez utiliser la commande suivante :

PRAGMA index_list(employees);

Cette commande affichera une liste des index sur la table employees, y compris l'index idx_lastname que vous venez de créer. Vous devriez voir une sortie similaire à celle-ci :

0|idx_lastname|0|c|0

Cette sortie confirme que l'index idx_lastname existe sur la table employees.

Analyser les requêtes avec EXPLAIN QUERY PLAN

Dans cette étape, vous apprendrez à utiliser la commande EXPLAIN QUERY PLAN pour analyser la façon dont SQLite exécute une requête. C'est un outil puissant pour comprendre la performance des requêtes et identifier les goulots d'étranglement (bottlenecks) potentiels.

Pour analyser une requête, préfixez-la avec EXPLAIN QUERY PLAN. Par exemple, pour analyser la requête suivante :

SELECT * FROM employees WHERE last_name = 'Smith';

Exécutez la commande suivante :

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE last_name = 'Smith';

La sortie ressemblera à ceci :

QUERY PLAN
`--SEARCH employees USING INDEX idx_lastname (last_name=?)

Cette sortie vous indique que SQLite utilise l'index idx_lastname pour trouver les employés dont le nom de famille (last name) est 'Smith'. Le mot-clé SEARCH indique que SQLite utilise un index pour effectuer la recherche.

Si l'index n'était pas utilisé, la sortie serait différente. Par exemple, si vous recherchez des employés avec le prénom (first name) 'John' (et que vous n'avez pas créé d'index sur la colonne first_name), la sortie serait :

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE first_name = 'John';

La sortie ressemblera à ceci :

QUERY PLAN
`--SCAN employees

Le mot-clé SCAN indique que SQLite effectue un parcours complet de la table (full table scan), ce qui signifie qu'il doit examiner chaque ligne de la table pour trouver les employés avec le prénom 'John'. C'est moins efficace que d'utiliser un index.

Ajouter plus de données et analyser le tri

Ajoutons plus de données pour rendre l'analyse du plan de requête (query plan) plus significative. Insérez les données suivantes dans la table employees :

INSERT INTO employees (first_name, last_name, email, department) VALUES
('Alice', 'Johnson', '[email protected]', 'HR'),
('Bob', 'Williams', '[email protected]', 'Finance'),
('Charlie', 'Brown', '[email protected]', 'IT'),
('David', 'Miller', '[email protected]', 'Sales'),
('Eve', 'Wilson', '[email protected]', 'Marketing'),
('John', 'Taylor', '[email protected]', 'Engineering'),
('Jane', 'Anderson', '[email protected]', 'HR'),
('Robert', 'Thomas', '[email protected]', 'Finance'),
('Emily', 'Jackson', '[email protected]', 'IT'),
('Michael', 'White', '[email protected]', 'Sales');

Maintenant, analysons une requête plus complexe qui implique un tri. Supposons que vous souhaitiez trouver tous les employés du département 'Sales' et les trier par nom de famille (last name). Vous pouvez utiliser la requête suivante :

SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

Analysez le plan de requête :

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

La sortie pourrait ressembler à ceci :

QUERY PLAN
`--SCAN employees USING INDEX idx_lastname

Dans ce cas, SQLite effectue un parcours complet de la table (full table scan) puis trie les résultats.

Créons un index sur la colonne department :

CREATE INDEX idx_department ON employees (department);

Maintenant, analysez à nouveau le plan de requête :

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

La sortie pourrait changer pour :

QUERY PLAN
|--SEARCH employees USING INDEX idx_department (department=?)
`--USE TEMP B-TREE FOR ORDER BY

Maintenant, SQLite utilise l'index idx_department pour trouver les employés du département 'Sales', mais il doit toujours trier les résultats.

Supprimer les index redondants

Dans cette étape, vous apprendrez à identifier et à supprimer les index redondants dans SQLite. Les index redondants peuvent avoir un impact négatif sur les performances de la base de données en augmentant la surcharge (overhead) des opérations d'écriture sans apporter d'avantages aux opérations de lecture.

Créons un index sur les colonnes department et last_name :

CREATE INDEX idx_department_lastname ON employees (department, last_name);

Maintenant, listons tous les index de la table employees :

PRAGMA index_list(employees);

Vous devriez voir une sortie similaire à ceci :

0|idx_lastname|0|c|0
1|idx_department|0|c|0
2|idx_department_lastname|0|c|0

Maintenant, analysons une requête qui filtre par department et last_name :

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' AND last_name = 'Doe';

La sortie pourrait ressembler à ceci :

QUERY PLAN
`--SEARCH employees USING INDEX idx_department_lastname (department=? AND last_name=?)

Cette sortie indique que SQLite utilise l'index idx_department_lastname pour cette requête.

Maintenant, analysons une requête qui filtre uniquement par department :

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales';

La sortie pourrait ressembler à ceci :

QUERY PLAN
`--SEARCH employees USING INDEX idx_department (department=?)

Cette sortie indique que SQLite utilise l'index idx_department pour cette requête.

Dans ce scénario, l'index idx_department_lastname est redondant car l'index idx_department peut être utilisé pour les requêtes qui filtrent uniquement par department. L'index idx_department_lastname n'offre un avantage que pour les requêtes qui filtrent à la fois par department et last_name.

Pour supprimer l'index redondant idx_department, vous pouvez utiliser la commande DROP INDEX :

DROP INDEX idx_department;

Maintenant, listons à nouveau tous les index de la table employees :

PRAGMA index_list(employees);

Vous devriez constater que l'index idx_department n'est plus listé.

Résumé

Dans ce labo, vous avez appris à optimiser les performances d'une base de données SQLite en utilisant des index. Vous avez créé des index à une seule colonne pour améliorer la vitesse des requêtes, analysé les plans de requête (query plans) à l'aide de EXPLAIN QUERY PLAN, et supprimé les index redondants. Ces compétences vous aideront à créer des bases de données SQLite plus efficaces et réactives.