SQL-Ex blog

Индексирование

Создавайте индексы. Индексы представляют собой структуры данных, которые обеспечивают механизм быстрого поиска, существенно улучшая производительность. Они работают путем создания структуры отсортированных данных, что позволяет ядру базы дынных быстро обнаруживать строки, удовлетворяющие предложению WHERE. В то время как индексы ускоряют запросы SELECT, он могут замедлить операции записи, поэтому важно соблюсти баланс между производительностью чтения и записи.

CREATE INDEX idx_username ON users(username);

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

CREATE INDEX idx_name_age ON employees(name, age);

Нормализация и денормализация

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

Фрагмент кода SQL (пример третьей нормальной формы):

CREATE TABLE customers (

    customer_id INT PRIMARY KEY,

    customer_name VARCHAR(100),

    address VARCHAR(255)

);

CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    customer_id INT,

    order_date DATE,

    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

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

CREATE TABLE denormalized_orders (

    order_id INT PRIMARY KEY,

    customer_id INT,

    customer_name VARCHAR(100),

    order_date DATE

);

Оптимизация запроса

Оптимизируйте запросы. Регулярно анализируйте и оптимизируйте часто используемые запросы. Используйте инструменты типа EXPLAIN, чтобы понять план выполнения запроса и нахождения мест, которые можно улучшить.

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

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

SELECT order_id, order_date FROM orders WHERE customer_id = 123;

Секционирование

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

CREATE TABLE sales (

    sale_id INT PRIMARY KEY,

    sale_date DATE,

    amount DECIMAL(10, 2)

) PARTITION BY RANGE (YEAR(sale_date)) (

    PARTITION p0 VALUES LESS THAN (1990),

    PARTITION p1 VALUES LESS THAN (2000),

    PARTITION p2 VALUES LESS THAN (2010),

    PARTITION p3 VALUES LESS THAN (2020),

    PARTITION p4 VALUES LESS THAN (MAXVALUE)

);

Нарезка секций. Убедитесь, что планировщик запросов удаляет ненужные секции при выполнении запроса. Это позволит избежать сканирования всего набора данных и улучшит производительность.

SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2023-01-01';

Кэширование

Кэширование запроса. Применяйте механизм кэширования для сохранения результатов часто выполняемых запросов. Это уменьшает нагрузку на базу данных, предоставляя кэшированные результаты и сокращая время отклика.

-- Псевдокод

DECLARE @cacheKey NVARCHAR(255) = 'query_cache_key';

DECLARE @cachedResult NVARCHAR(MAX);

SET @cachedResult = REDIS.GET(@cacheKey);

IF @cachedResult IS NULL

BEGIN

    -- Выполнить запрос и сохранить результаты в кэше

    SET @cachedResult = EXECUTE_QUERY('SELECT * FROM large_table');

    REDIS.SET(@cacheKey, @cachedResult, EXPIRY_TIME);

END

-- Используйте @cachedResult для дальнейшей обработки

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

from django.core.cache import cache

def get_user_data(user_id):

    # Пытаемся получить данные из кэша

    user_data = cache.get(f'user_{user_id}')

    if user_data is None:

        # Если в кэше нет, получаем из базы данных

        user_data = User.objects.get(id=user_id)

        # Сохраняем данные в кэше для последующих обращений

        cache.set(f'user_{user_id}', user_data, TIMEOUT)

    return user_data

Регулярное обслуживание

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

-- Обновление статистики для таблицы

UPDATE STATISTICS имя_таблицы;

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

-- Архивируем данные старше определенной даты

DELETE FROM historical_data WHERE date < '2020-01-01';

Оптимизация железа

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

-- Пример: увеличение размера кэша запросов

SET GLOBAL query_cache_size = 256M;

Используйте SSD. Рассмотрите возможность использования твердотельных накопителей (SSD) для хранилища данных. SSD обеспечивают более быстрый доступ к данным по сравнению с традиционными жесткими дисками (HDD), что приводит к улучшению общей производительности базы данных.

Управление параллелизмом

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

-- Установка уровня изоляции в READ COMMITTED 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Пул подключений

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

HikariConfig config = new HikariConfig();

config.setJdbcUrl("jdbc:mysql://localhost:3306/database");

config.setUsername("username");

config.setPassword("password");

config.setMaximumPoolSize(10);

HikariDataSource dataSource = new HikariDataSource(config);

Проект базы данных

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

CREATE TABLE products (

    product_id INT PRIMARY KEY,

    product_name VARCHAR(255),

    price DECIMAL(10, 2),

    -- Дополнительные столбцы, если необходимы

);

Мониторинг и профилирование

Регулярный мониторинг. Используйте инструменты мониторинга для отслеживания производительности базы данных во времени. Регулярно отслеживайте ключевые метрики, такие как использование ЦП, использование памяти и время выполнения запросов, для обнаружения потенциальных проблем.

SHOW STATUS LIKE 'cpu%';

Профилирование запросов. Профилируйте и анализируйте производительность отдельных запросов для выявления узких мест. Инструменты типа MySQL Performance Schema могут обеспечить подробные сведения о выполнении запроса.

-- Включение Performance Schema

SET GLOBAL performance_schema = ON;

-- Профилирование конкретного запроса

SELECT * FROM orders WHERE customer_id = 123;

Это были наиболее общие методы в оптимизации базы данных.

Ссылки по теме
1. Почему не следует использовать SELECT * в рабочих системах (никогда!)
2. Как секционировать таблицы MySQL
3. Как работает секционирование в PostgreSQL и почему вас это должно беспокоить?
4. Тайные советы по статистике в SQL Server
5. Уровни изоляции транзакций
6. Инструменты и методы для профилирования и отладки медленно выполняющихся SQL-запросов