Оптимизация индексов SQLite

Введение

В этой лабораторной работе вы узнаете, как оптимизировать производительность базы данных SQLite с помощью индексов. Вы создадите одноколоночные индексы для повышения скорости запросов, уделяя особое внимание практическому применению и анализу. Вы также научитесь анализировать планы запросов и удалять избыточные индексы.

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{{"Оптимизация индексов SQLite"}} sqlite/sort_data -.-> lab-552552{{"Оптимизация индексов SQLite"}} sqlite/build_index -.-> lab-552552{{"Оптимизация индексов SQLite"}} sqlite/clear_index -.-> lab-552552{{"Оптимизация индексов SQLite"}} sqlite/verify_table -.-> lab-552552{{"Оптимизация индексов SQLite"}} end

Создание базы данных и таблицы

На этом шаге вы создадите базу данных SQLite и таблицу employees (сотрудники). Затем вы вставите в таблицу несколько примеров данных.

Сначала откройте свой терминал в LabEx VM (виртуальной машине). Ваш путь по умолчанию — /home/labex/project.

Чтобы создать базу данных SQLite с именем my_database.db, выполните следующую команду:

sqlite3 my_database.db

Эта команда создаст новый файл базы данных SQLite с именем my_database.db в вашем каталоге проекта и откроет оболочку SQLite (SQLite shell).

Далее создайте таблицу employees со следующей структурой:

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

Этот SQL-запрос создает таблицу с именем employees с пятью столбцами: id, first_name (имя), last_name (фамилия), email и department (отдел). Столбец id установлен в качестве первичного ключа (primary key), что означает, что он должен содержать уникальные значения.

Теперь вставьте несколько примеров данных в таблицу 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');

Это вставит пять строк данных в таблицу employees.

Чтобы убедиться, что данные были вставлены правильно, выполните следующую команду:

SELECT * FROM employees;

Вы должны увидеть следующий вывод:

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

Создание индекса

На этом шаге вы создадите индекс для столбца last_name (фамилия) таблицы employees (сотрудники).

Индексы — это специальные таблицы поиска, которые поисковая система базы данных может использовать для ускорения извлечения данных.

Чтобы создать индекс с именем idx_lastname для столбца last_name, выполните следующую команду:

CREATE INDEX idx_lastname ON employees (last_name);

Этот SQL-запрос создает индекс с именем idx_lastname для столбца last_name таблицы employees.

Чтобы убедиться, что индекс был создан, вы можете использовать следующую команду:

PRAGMA index_list(employees);

Эта команда отобразит список индексов для таблицы employees, включая индекс idx_lastname, который вы только что создали. Вы должны увидеть вывод, подобный этому:

0|idx_lastname|0|c|0

Этот вывод подтверждает, что индекс idx_lastname существует в таблице employees.

Анализ запросов с помощью EXPLAIN QUERY PLAN

На этом шаге вы узнаете, как использовать команду EXPLAIN QUERY PLAN для анализа того, как SQLite выполняет запрос. Это мощный инструмент для понимания производительности запросов и выявления потенциальных узких мест (bottlenecks).

Чтобы проанализировать запрос, добавьте к нему префикс EXPLAIN QUERY PLAN. Например, чтобы проанализировать следующий запрос:

SELECT * FROM employees WHERE last_name = 'Smith';

Выполните следующую команду:

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

Вывод будет выглядеть примерно так:

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

Этот вывод сообщает вам, что SQLite использует индекс idx_lastname для поиска сотрудников с фамилией 'Smith'. Ключевое слово SEARCH указывает на то, что SQLite использует индекс для выполнения поиска.

Если индекс не использовался, вывод будет выглядеть иначе. Например, если вы запрашиваете сотрудников с именем 'John' (и вы не создали индекс для столбца first_name (имя)), вывод будет следующим:

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

Вывод будет выглядеть примерно так:

QUERY PLAN
`--SCAN employees

Ключевое слово SCAN указывает на то, что SQLite выполняет полное сканирование таблицы (full table scan), что означает, что он должен просмотреть каждую строку в таблице, чтобы найти сотрудников с именем 'John'. Это менее эффективно, чем использование индекса.

Добавление дополнительных данных и анализ сортировки

Давайте добавим больше данных, чтобы анализ плана запроса (query plan analysis) был более информативным. Вставьте следующие данные в таблицу 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');

Теперь давайте проанализируем более сложный запрос, включающий сортировку. Предположим, вы хотите найти всех сотрудников в отделе 'Sales' (продажи) и отсортировать их по фамилии. Вы можете использовать следующий запрос:

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

Проанализируйте план запроса:

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

Вывод может выглядеть так:

QUERY PLAN
`--SCAN employees USING INDEX idx_lastname

В этом случае SQLite выполняет полное сканирование таблицы (full table scan), а затем сортирует результаты.

Давайте создадим индекс для столбца department (отдел):

CREATE INDEX idx_department ON employees (department);

Теперь проанализируйте план запроса еще раз:

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

Вывод может измениться на:

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

Теперь SQLite использует индекс idx_department для поиска сотрудников в отделе 'Sales', но ему все равно нужно сортировать результаты.

Удаление избыточных индексов (Redundant Indexes)

На этом шаге вы узнаете, как идентифицировать и удалять избыточные индексы в SQLite. Избыточные индексы могут негативно влиять на производительность базы данных, увеличивая накладные расходы (overhead) на операции записи, не предоставляя при этом никаких преимуществ для операций чтения.

Давайте создадим индекс для столбцов department (отдел) и last_name (фамилия):

CREATE INDEX idx_department_lastname ON employees (department, last_name);

Теперь перечислите все индексы в таблице employees:

PRAGMA index_list(employees);

Вы должны увидеть вывод, подобный этому:

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

Теперь давайте проанализируем запрос, который фильтрует по department и last_name:

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

Вывод может выглядеть так:

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

Этот вывод указывает на то, что SQLite использует индекс idx_department_lastname для этого запроса.

Теперь давайте проанализируем запрос, который фильтрует только по department:

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

Вывод может выглядеть так:

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

Этот вывод указывает на то, что SQLite использует индекс idx_department для этого запроса.

В этом сценарии индекс idx_department_lastname является избыточным, поскольку индекс idx_department можно использовать для запросов, которые фильтруют только по department. Индекс idx_department_lastname предоставляет преимущество только для запросов, которые фильтруют по обоим полям: department и last_name.

Чтобы удалить избыточный индекс idx_department, вы можете использовать команду DROP INDEX:

DROP INDEX idx_department;

Теперь снова перечислите все индексы в таблице employees:

PRAGMA index_list(employees);

Вы должны увидеть, что индекс idx_department больше не указан.

Итог (Summary)

В этой лабораторной работе (lab) вы узнали, как оптимизировать производительность базы данных SQLite с помощью индексов. Вы создали одноколоночные индексы (single-column indexes) для повышения скорости запросов, проанализировали планы запросов (query plans) с помощью EXPLAIN QUERY PLAN и удалили избыточные индексы (redundant indexes). Эти навыки помогут вам создавать более эффективные и отзывчивые базы данных SQLite.