МДК.11.01 - 29 - Оптимизация запросов и структуры

Введение

Оптимизация баз данных — это важный процесс, направленный на повышение производительности системы и обеспечение эффективной работы с данными. С увеличением объёма информации и сложности запросов время их выполнения может возрастать, что негативно сказывается на пользовательском опыте и нагрузке на сервер. В MySQL 8 оптимизация включает в себя настройку структуры базы данных, анализ и улучшение запросов, а также корректировку параметров сервера. В этой лекции мы подробно рассмотрим ключевые методы оптимизации, такие как использование индексов, анализ с помощью EXPLAIN и настройка конфигурации сервера, с практическими примерами на тестовой базе данных, содержащей значительный объём данных. Навыки оптимизации позволят вам ускорить работу приложений и подготовить базу к высоким нагрузкам.

Зачем нужна оптимизация?

С ростом объёма данных и сложности запросов производительность базы данных может снижаться. Оптимизация помогает:

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

Основные подходы к оптимизации

Существует три ключевых направления оптимизации в MySQL:

• Оптимизация структуры: Использование правильных типов данных, создание индексов и применение нормализации или денормализации в зависимости от задачи.
• Оптимизация запросов: Анализ выполнения запросов с помощью EXPLAIN, устранение избыточных операций и использование кэширования для повторяющихся вычислений.
• Настройка сервера: Корректировка системных параметров MySQL, таких как innodb_buffer_pool_size, для улучшения работы с памятью и диском.

Эти подходы взаимосвязаны и применяются в комплексе для достижения максимального эффекта.

Подготовка СУБД для выполнения примера

Перед выполнением примера с генерацией 100,000 записей необходимо подготовить сервер базы данных и клиент, чтобы избежать ошибок, таких как Error Code: 2013. Lost connection to MySQL server during query. Следуйте этим шагам:

• Настройка глобальных переменных сервера:

  • Выполните команды от имени пользователя с правами root:

    SET GLOBAL net_write_timeout = 300;  -- Устанавливает тайм-аут записи данных в 300 секунд (5 минут)
    SET GLOBAL net_read_timeout = 300;   -- Устанавливает тайм-аут чтения данных в 300 секунд (5 минут)
    

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

• Переподключение к серверу:

  • После изменения глобальных переменных закройте текущую сессию (например, выйдите из MySQL Workbench или терминала) и переподключитесь заново. Это гарантирует, что новые настройки применятся к вашей сессии.

• Настройка клиента (MySQL Workbench):

  • Откройте MySQL Workbench, перейдите в меню Edit → Preferences → SQL Editor.
  • Установите следующие параметры:
    • DBMS connection keep-alive interval (in seconds): 600 (или оставьте 0, если не нужны keep-alive сообщения).
    • DBMS connection read timeout interval (in seconds): 300.
    • DBMS connection timeout interval (in seconds): 300.
  • Сохраните настройки и перезапустите Workbench, чтобы изменения вступили в силу. Эти параметры обеспечат, что клиент не обрывает соединение раньше сервера.

Тестовая база данных optimization_demo

Создаём базу optimization_demo с большим объёмом данных:

CREATE DATABASE optimization_demo;
USE optimization_demo;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL
);

-- Генерация 1 000 пользователей
DELIMITER //
CREATE PROCEDURE populate_users()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 1000 DO
        INSERT INTO users (user_name)
        VALUES (CONCAT('User', i));
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;
CALL populate_users();
DROP PROCEDURE populate_users;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_name VARCHAR(255),
    price DECIMAL(10,2),
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Генерация 100 000 записей с учётом, что не все пользователи имеют товары
DELIMITER //
CREATE PROCEDURE populate_orders()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 100000 DO
        INSERT INTO orders (user_id, product_name, price, order_date)
        VALUES (
            FLOOR(1 + RAND() * 1000),
            CONCAT('Product_', i),
            ROUND(10 + RAND() * 990, 2),
            DATE_SUB(CURRENT_TIMESTAMP, INTERVAL FLOOR(RAND() * 7 * 24 * 60 * 60) SECOND) -- Случайная дата за последние 7 дней
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;
CALL populate_orders();
DROP PROCEDURE populate_orders;

Оптимизация структуры базы данных

1. Выбор правильных типов данных

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

Пример: Проверяем структуру:

DESCRIBE orders;

Допустим, мы изначально указали VARCHAR(255) для поля product_name, но на самом деле все названия продуктов гарантированно не превышают 50 символов.

Если фактическая длина данных не превышает 50 символов, уменьшение размера столбца до VARCHAR(50):

• Позволяет снизить потенциальный объем памяти, занимаемый временными таблицами при сортировках и группировках, так как MySQL выделяет память по максимальной длине столбца.
• Увеличивает производительность при работе с индексами, так как для коротких строк MySQL быстрее осуществляет сравнения.

Изменяем размер product_name на VARCHAR(50):

ALTER TABLE orders MODIFY product_name VARCHAR(50);

Проверить фактический размер таблицы на диске можно так:

SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, (DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'optimization_demo' AND TABLE_NAME = 'orders';

2. Индексы

Индексы — это структуры данных (B-деревья в MySQL), которые хранят отсортированные значения столбцов, позволяя быстро находить строки без полного сканирования.

Пример: Запрос без индекса:

EXPLAIN SELECT product_name, price FROM orders WHERE product_name = 'Product_50000';

• type: ALL — полное сканирование 100,000 строк.
• rows: 100000 — проверяется вся таблица (число может быть и меньше, но в любом случае довольно велико).
• За счёт чего медленнее: MySQL читает каждую строку, сравнивая product_name.

Добавляем индекс:

CREATE INDEX idx_product_name ON orders(product_name);

После чего снова выполним тот же запрос:

EXPLAIN SELECT product_name, price FROM orders WHERE product_name = 'Product_50000';

• type: ref — поиск по индексу.
• rows: 1 — найдена одна строка.
• За счёт чего быстрее: Индекс (B-дерево) хранит отсортированные значения product_name и указатели на строки, MySQL сразу переходит к нужной записи.

3. Денормализация

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

Пример: Допустим, нам нужно получить имена пользователей и информацию о заказах. Без денормализации запрос с JOIN выглядит так:

SELECT o.product_name, u.user_name, o.price
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.user_id = 500 AND o.price > 500;

• Проблема: JOIN требует сопоставления строк между таблицами orders (100,000 строк) и users (1,000 строк). Даже с индексом на user_id это дополнительные затраты на поиск и объединение данных.
• Результат: На тестовой базе с 100,000 записей запрос может занять, например, 0.05-0.1 секунды (точное время зависит от оборудования и индексов). При росте данных до миллионов строк разница станет заметнее.

Теперь добавим поле user_name в таблицу orders для денормализации:

ALTER TABLE orders ADD COLUMN user_name VARCHAR(50);
UPDATE orders SET user_name = (SELECT user_name FROM users WHERE users.id = orders.user_id) WHERE id > 0;

Оптимизированный запрос без JOIN:

SELECT product_name, user_name, price
FROM orders
WHERE user_id = 500 AND price > 500;

• За счёт чего быстрее: Данные берутся напрямую из одной таблицы, без необходимости объединения с users. MySQL работает только с orders, что снижает количество операций.
• Результат: На той же тестовой базе время выполнения сокращается, например, до 0.02-0.05 секунд. Разница в 2-3 раза заметна уже на 100,000 строк, а на больших объёмах (миллионы записей) может достигать десятков раз.
• Выгода: Ускорение запросов для отчётов или интерфейсов, где требуется имя пользователя.
• Минус: Если имя пользователя в таблице users изменится (например, User500 станет JohnDoe), нужно обновлять все связанные строки в orders, что увеличивает затраты на запись.

Сравнение производительности:

• С JOIN: MySQL тратит время на поиск по индексу user_id в orders, затем на сопоставление с users. Чем больше записей, тем выше нагрузка.
• С денормализацией: Поиск идёт только по одной таблице, что минимизирует накладные расходы.

SELECT BENCHMARK(1000, (SELECT product_name, user_name, price FROM orders WHERE user_id = 500 AND price > 500 LIMIT 1));

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

1. Использование EXPLAIN

EXPLAIN показывает план выполнения запроса.

Пример: Фильтр с сортировкой:

EXPLAIN SELECT product_name, price
FROM orders
WHERE price > 900
ORDER BY order_date DESC;

• type: ALL — сканирование всей таблицы.
• rows: 100000 — проверка всех строк.
• Почему медленно: Нет индекса, MySQL фильтрует и сортирует вручную.

Оптимизируем:

CREATE INDEX idx_price_date ON orders(price, order_date);

Обновляем статистику после создания индекса (это не обязательно, но гарантирует что MySQL не решит использовать кэшированный результат запроса, сразу увидит индексы):

ANALYZE TABLE orders;

После чего снова выполним тот же запрос:

EXPLAIN SELECT product_name, price
FROM orders
WHERE price > 500
ORDER BY order_date DESC;

• type: range — поиск по диапазону в индексе.
• rows: ~50000 (примерно половина строк с price > 500).
• За счёт чего: Комбинированный индекс на price и order_date позволяет фильтровать по цене и сразу брать отсортированные данные по дате.

2. Устранение избыточных операций

Пример: Ненужные столбцы:

SELECT * FROM orders WHERE price > 700;

• Проблема: Возвращаются все столбцы, включая id, user_id, увеличивая объём данных.

Оптимизация:

SELECT product_name, price FROM orders WHERE price > 700 LIMIT 100;

• За счёт чего: Меньше данных передаётся клиенту, снижается нагрузка на сеть и память.

3. Кэширование запросов

Представления (VIEW) сохраняют логику запроса и могут ускорить доступ к данным, особенно если запрос сложный или часто повторяется. В MySQL представления не кэшируют результаты автоматически (в отличие от материализованных представлений в других СУБД), но они упрощают код и позволяют оптимизировать базовый запрос.

Пример: Создадим представление для заказов с высокой ценой:

CREATE VIEW high_price_orders AS
SELECT product_name, price
FROM orders
WHERE price > 800;

Теперь сравним производительность прямого запроса к таблице и запроса через представление.

• Прямой запрос к таблице:

SELECT product_name, price
FROM orders
WHERE price > 800
AND product_name LIKE 'Product_9%';

• Запрос через представление:

SELECT product_name, price
FROM high_price_orders
WHERE product_name LIKE 'Product_9%';

• За счёт чего быстрее: Представление предварительно фильтрует строки по условию price > 800, и второй фильтр (product_name LIKE 'Product_9%') применяется к уже уменьшенному набору данных. Без представления MySQL фильтрует все 100,000 строк по обоим условиям одновременно, что может быть менее эффективно, особенно без подходящего индекса.
• Результат: На тестовой базе с 100,000 строк прямой запрос может занять, например, 0.05-0.1 секунды, а запрос через представление — 0.03-0.07 секунды (время зависит от оборудования и индексов). Разница становится заметнее при сложных запросах или больших объёмах данных.

Добавим индекс для большей выгоды:

CREATE INDEX idx_price ON orders(price);

Теперь повторите оба запроса. С индексом:

• Прямой запрос проверяет все строки по price > 800, затем фильтрует по product_name.
• Запрос через high_price_orders использует индекс для базового фильтра price > 800, а затем применяет LIKE, что снижает количество проверяемых строк.

Настройка сервера

1. Параметр innodb_buffer_pool_size

Буферный пул InnoDB кэширует таблицы и индексы в памяти.

Пример:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

• По умолчанию: 128M — мало для 100,000 строк с индексами.

Настройка:

[mysqld]
innodb_buffer_pool_size = 512M

• Почему 512M: Для нашей таблицы (около 20-30 МБ данных + индексы) это избыточно, но при росте данных до сотен мегабайт 128M вызовет частое чтение с диска.
• За счёт чего: Больше данных в RAM = меньше операций ввода-вывода.

2. Параметр tmp_table_size

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

Пример:

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

• Почему нужно: При сортировке (ORDER BY) или группировке MySQL создаёт временные таблицы. Если они не помещаются в 16M (дефолт), пишутся на диск — это медленно.
• На нашем примере: Запрос SELECT user_id, AVG(price) FROM orders GROUP BY user_id создаёт временную таблицу. С 64M она останется в памяти.

Практическая часть

Мы будем работать с тестовой базой optimization_demo (таблицы users и orders). Если ранее в теоретических примерах были созданы индексы (например, idx_product_name, idx_price, idx_price_date, idx_user_price_date), их можно удалить перед началом, чтобы начать с чистого листа. Выполните следующие команды только если соответствующие индексы существуют (проверьте с помощью SHOW INDEX FROM orders):

DROP INDEX idx_product_name ON orders;
DROP INDEX idx_price ON orders;
DROP INDEX idx_price_date ON orders;

Используйте EXPLAIN для анализа планов запросов, создавайте индексы и оптимизируйте запросы. Время выполнения проверяйте в MySQL Workbench внизу окна ("Action Output" или "Query Stats") — выполните запросы несколько раз для сравнения.

1. Оптимизация фильтрации и агрегации

Базовый пример:

• Без индекса:

EXPLAIN SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE price BETWEEN 200 AND 500
GROUP BY user_id;

• С индексом:

CREATE INDEX idx_price ON orders(price);
EXPLAIN SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE price BETWEEN 200 AND 500
GROUP BY user_id;

• Результат: Без индекса — полное сканирование (type: ALL), с индексом — поиск по диапазону (type: range). Время в клиенте сокращается (например, с 0.1 сек до 0.03 сек).

Дополненный пример:
Найдём пользователей, у которых больше 50 заказов дороже 700, с подсчётом их общего количества заказов.

• Без индекса:

EXPLAIN SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE price > 700
GROUP BY user_id
HAVING order_count > 5;

• С индексом:

CREATE INDEX idx_price_user ON orders(price, user_id);
EXPLAIN SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE price > 700
GROUP BY user_id
HAVING order_count > 5;

• Результат: Индекс на (price, user_id) ускоряет фильтрацию и группировку. Время может упасть с 0.15 сек до 0.04 сек (зависит от оборудования).

2. Оптимизация сложной сортировки

Базовый пример:

• Без индекса:

EXPLAIN SELECT product_name, price, order_date
FROM orders
WHERE user_id = 420
ORDER BY price DESC, order_date ASC;

• С индексом:

CREATE INDEX idx_user_price_date ON orders(user_id, price, order_date);
EXPLAIN SELECT product_name, price, order_date
FROM orders
WHERE user_id = 420
ORDER BY price DESC, order_date ASC;

• Результат: Индекс убирает полное сканирование и ускоряет двойную сортировку.

Дополненный пример:
Найдём 5 самых дорогих заказов за последние 3 дня с сортировкой по цене и дате.

• Без индекса:

EXPLAIN SELECT product_name, price, order_date
FROM orders
WHERE order_date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
ORDER BY price DESC, order_date DESC
LIMIT 5;

• С индексом:

CREATE INDEX idx_date_price ON orders(order_date, price);
EXPLAIN SELECT product_name, price, order_date
FROM orders
WHERE order_date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
ORDER BY price DESC, order_date DESC
LIMIT 5;

• Результат: Без индекса — сканирование всей таблицы и сортировка вручную. С индексом — быстрый доступ к нужным строкам и уже отсортированные данные. Время может сократиться с 0.2 сек до 0.05 сек.

3. Тестирование производительности

Базовый пример:

• Без индекса:

EXPLAIN SELECT product_name, price
FROM orders
WHERE price > 600
ORDER BY order_date DESC
LIMIT 10;

• С индексом:

CREATE INDEX idx_price_date_opt ON orders(price, order_date);
EXPLAIN SELECT product_name, price
FROM orders
WHERE price > 600
ORDER BY order_date DESC
LIMIT 10;

• Результат: Сравните время в клиенте: без индекса — около 0.5 сек, с индексом — около 0.05 сек.

Дополненный пример:
Сравним запрос с JOIN и денормализацию. Найдём 100 заказов дороже 800 с именами пользователей.

• С JOIN (без оптимизации):

EXPLAIN SELECT o.product_name, u.user_name, o.price
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.price > 800
LIMIT 100;

• Оптимизируем с индексом:

CREATE INDEX idx_orders_user ON orders(user_id, price);
EXPLAIN SELECT o.product_name, u.user_name, o.price
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.price > 800
LIMIT 100;

• С денормализацией (добавим user_name в orders):
  Сначала добавим поле и заполним его:

ALTER TABLE orders ADD COLUMN user_name VARCHAR(50);
UPDATE orders o JOIN users u ON o.user_id = u.id SET o.user_name = u.user_name;

Затем выполним запрос:

EXPLAIN SELECT product_name, user_name, price
FROM orders
WHERE price > 800
LIMIT 100;

• Результат:
  • С JOIN без индекса: полное сканирование, время около 0.1 сек.
  • С JOIN и индексом: быстрее (около 0.06 сек).
  • С денормализацией: убирается JOIN, время около 0.03 сек. Сравните в клиенте и обсудите, стоит ли денормализация затрат на обновление данных.

Заключение

В этой лекции мы подробно изучили ключевые методы оптимизации запросов и структуры базы данных в MySQL 8. Мы рассмотрели выбор правильных типов данных, создание индексов, денормализацию, анализ запросов с помощью EXPLAIN, устранение избыточных операций, кэширование и настройку параметров сервера. Практические примеры на базе данных с 100,000 записей показали, как эти подходы сокращают время выполнения запросов и снижают нагрузку на сервер. Эти навыки позволят вам повысить производительность базы данных и обеспечить её стабильную работу даже при больших объёмах данных и высокой нагрузке.