Запросы и их анализ

Работа запросов

Подготовленный оператор

Пример запроса

Если у нас есть запрос, например, с несколькими таблицами и условиями, подготовленный оператор будет полезен:

SELECT o.id, o.order_date, c.name, SUM(oi.quantity * oi.price) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.customer_id = ? AND o.order_date BETWEEN ? AND ?
GROUP BY o.id, o.order_date, c.name

Пример создания подготовленного оператора

PREPARE get_order_summary (INT, DATE, DATE) AS
SELECT 
    o.id,
    o.order_date,
    c.name,
    SUM(oi.quantity * oi.price) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.customer_id = $1 AND o.order_date BETWEEN $2 AND $3
GROUP BY o.id, o.order_date, c.name;

В Java это будет выглядеть так:

String query = "SELECT o.id, o.order_date, c.name, SUM(oi.quantity * oi.price) AS total_amount " +
               "FROM orders o " +
               "JOIN customers c ON o.customer_id = c.id " +
               "JOIN order_items oi ON o.id = oi.order_id " +
               "WHERE o.customer_id = ? AND o.order_date BETWEEN ? AND ? " +
               "GROUP BY o.id, o.order_date, c.name";

PreparedStatement pstmt = conn.prepareStatement(query);

int customerId = 123;
Date startDate = new Date(2022, 1, 1);
Date endDate = new Date(2022, 12, 31);

pstmt.setInt(1, customerId);
pstmt.setDate(2, startDate);
pstmt.setDate(3, endDate);

ResultSet rs = pstmt.executeQuery();

Преимущества подготовленных операторов

Производительность: Подготовленные операторы выполняются быстрее, так как сервер базы данных компилирует запрос один раз и может повторно использовать план выполнения.
Безопасность: Защищают от SQL-инъекций, поскольку параметры передаются отдельно от SQL-кода.
Удобство: Легко использовать переменные для передачи данных в запрос.

Недостатки подготовленных операторов

Сложность: Может быть сложнее в реализации для очень простых запросов.
Ограничения: Не все типы запросов могут быть подготовлены (например, запросы с динамическими таблицами или столбцами).

Когда использовать подготовленные операторы?

Повторное выполнение запросов: Если один и тот же запрос выполняется многократно с разными параметрами.
Сложные запросы: Для запросов с несколькими таблицами или условиями.
Безопасность: Для защиты от SQL-инъекций.

Когда не стоит использовать подготовленные операторы?

Простые запросы: Если запрос очень простой и выполняется только один раз.
Динамические запросы: Если структура запроса меняется динамически.

Курсоры в SQL

Курсор — это механизм, который позволяет обрабатывать результаты запроса по одной строке за раз. Это особенно полезно, когда необходимо выполнить сложные операции над каждой строкой или когда приложение не может эффективно обрабатывать весь результирующий набор сразу.

Основные операции с курсорами

Объявление курсора: Определяется запрос, который будет использоваться для получения данных.
DECLARE my_cursor CURSOR FOR SELECT * FROM my_table;
Открытие курсора: Курсор открывается, и выполняется запрос для получения данных.
OPEN my_cursor;
Извлечение данных: Используется оператор FETCH для получения данных по одной строке за раз.
FETCH FROM my_cursor INTO @variable;
Закрытие курсора: После обработки всех данных курсор закрывается.
CLOSE my_cursor;
Освобождение ресурсов: Курсор удаляется, чтобы освободить ресурсы.
DEALLOCATE my_cursor;

Преимущества курсоров

Последовательная обработка: Позволяет обрабатывать данные по одной строке за раз, что полезно для сложных вычислений или обновлений.
Эффективность: Снижает нагрузку на систему, так как не требует загрузки всего результирующего набора в память.
Удобство: Легко интегрируется с языками программирования, позволяя использовать данные в циклах и других структурах.

Типы курсоров

Статические: Создаются в виде временной таблицы и не отражают изменения данных после открытия.
Динамические: Отражают все изменения в данных во время работы.
Последовательные: Позволяют перемещаться только в прямом направлении.
Прокручиваемые: Позволяют перемещаться в обоих направлениях.

Пример использования курсора

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CursorExample {
    public static void main(String[] args) {
        // Установка соединения с базой данных
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "myuser";
        String password = "mypassword";

        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            // Создание оператора
            Statement stmt = conn.createStatement();

            // Выполнение запроса
            String query = "SELECT id, name FROM users";
            ResultSet rs = stmt.executeQuery(query);

            // Обработка результатов
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");

                // Обработка данных
                System.out.println("ID: " + id + ", Name: " + name);
            }
        } catch (SQLException e) {
            System.out.println("Ошибка: " + e.getMessage());
        }
    }
}

Когда использовать курсоры?

Сложные вычисления: Когда необходимо выполнить сложные операции над каждой строкой.
Обновления данных: При точном обновлении данных, когда необходимо обрабатывать каждую строку индивидуально.
Ограниченные ресурсы: Когда приложение не может эффективно обрабатывать весь результирующий набор сразу.

Когда не стоит использовать курсоры?

Простые запросы: Для простых запросов, где можно использовать обычные SQL-операторы.
Большие объемы данных: Если необходимо обработать очень большой объем данных, может быть более эффективно использовать другие методы, такие как параллельная обработка или обработка в пакетах.

Анализ запросов

Этапы анализа запросов

Синтаксический анализ: Проверка запроса на соответствие синтаксису SQL.
Разрешение представлений и верификация: Проверка доступа к таблицам и полям.
Генерация логического плана: Определение шагов, которые необходимо выполнить для получения результатов.
Оптимизация запроса: Выбор наиболее эффективного плана выполнения на основе статистики базы данных.
Генерация физического плана: Определение конкретных операций, которые будут выполнены (например, сканирование таблицы или использование индекса).

Инструменты для анализа запросов

EXPLAIN и EXPLAIN ANALYZE: Используются для получения плана выполнения запроса и анализа его производительности.
EXPLAIN SELECT * FROM my_table WHERE condition; EXPLAIN ANALYZE SELECT * FROM my_table WHERE condition;
Профилировщики запросов: Инструменты типа pt-query-digest для MySQL или pg_stat_statements для PostgreSQL помогают анализировать производительность запросов и выявлять медленные запросы.
-- PostgreSQL CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Анализ плана выполнения

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

Тип сканирования: Seq Scan (полное сканирование таблицы), Index Scan (использование индекса), Bitmap Heap Scan (использование битовой карты индекса).
Использование индексов: Index Only Scan (все данные получены из индекса) или Index Scan (дополнительное чтение таблицы).
Объединения таблиц: Nested Loop Join, Hash Join, Merge Join.
Операции сортировки и группировки: Sort, Group By.

Пример анализа плана выполнения

EXPLAIN ANALYZE
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date BETWEEN '2022-01-01' AND '2022-12-31';

Результат анализа может выглядеть примерно так:

Nested Loop  (cost=0.00..10.70 rows=100 width=268)
  -> Seq Scan on orders o  (cost=0.00..5.10 rows=50 width=136)
        Filter: (order_date >= '2022-01-01'::date) AND (order_date  Index Scan using customers_pkey on customers c  (cost=0.00..0.20 rows=1 width=132)
        Index Cond: (id = o.customer_id)

В этом примере используется полное сканирование таблицы orders и индексное сканирование таблицы customers.

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

Важно понимать, что хаотичное добавление данных в таблицу по которой настроен индекс, может быть очень дорогим по времени

Основные команды

Индекс в PostgreSQL создается с помощью команды CREATE INDEX. Основной синтаксис:

CREATE INDEX имя_индекса ON таблица (столбец);

Например:

CREATE INDEX idx_name ON users (name);

Типы индексов

PostgreSQL поддерживает несколько типов индексов:

B-Tree: Подходит для большинства случаев, особенно для равных сравнений и диапазонов.
Hash: Быстрее для равных сравнений, но требует больше места.
GiST: Используется для геопространственных данных и некоторых типов данных, таких как массивы.
GIN: Оптимально подходит для поиска элементов в массивах.
SP-GiST: Используется для данных с пространственной структурой.
BRIN: Быстрый и компактный индекс для больших таблиц с последовательными данными.

Примеры создания индексов

1. Простой индекс

CREATE INDEX idx_name ON users (name);

2. Уникальный индекс

CREATE UNIQUE INDEX idx_email ON users (email);

3. Составной индекс

CREATE INDEX idx_name_email ON users (name, email);

4. Индекс по выражению

CREATE INDEX idx_upper_name ON users (upper(name));

5. Неблокирующее создание индекса

CREATE INDEX idx_name ON users (name) CONCURRENTLY;

Этот метод позволяет создавать индекс без блокировки таблицы, что полезно для больших таблиц.

Определение необходимости индекса

Чтобы понять, какой индекс нужен для запроса, следует проанализировать план выполнения запроса с помощью команды EXPLAIN или EXPLAIN ANALYZE.

EXPLAIN SELECT * FROM users WHERE name = 'John Doe';

Если в плане выполнения используется полное сканирование таблицы (Seq Scan), это может указывать на необходимость индекса.

Селективность индекса

Селективность индекса — это мера того, насколько хорошо индекс может сузить количество строк, которые необходимо просмотреть. Хороший индекс должен иметь высокую селективность.

Как правильно создать индекс

Пример запроса:

SELECT * FROM users
    WHERE gender = 'male'
        AND country = 'USA'
        AND name = 'Lev'
        AND age > 18
        AND salary > 500000;

Грамотный индекс будет выглядеть следующим образом:

CREATE INDEX idx_users_country_name_age
    ON users (country, name, age);

Потому что gender плохо селективное поле, а между age и salary надо выбрать так, чтобы вариантов значений было как можно больше.

Факторы, влияющие на селективность:

Уникальность данных: Если столбец содержит уникальные значения, индекс будет более эффективным.
Распределение данных: Если данные распределены равномерно, индекс будет более эффективным.
Частота использования: Если запрос часто выполняется, индекс будет более оправдан.

Как оценить селективность:

Используйте команду EXPLAIN: Анализируйте план выполнения запроса, чтобы увидеть, использует ли PostgreSQL индекс.
Проверьте статистику индекса: Используйте команды типа pg_stat_user_indexes для оценки использования индекса.
Оцените распределение данных: Используйте команды типа SELECT COUNT(DISTINCT column_name) FROM table_name для оценки уникальности данных.

Last modified: 01 October 2025