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

• Используйте LIMIT
• Используйте LIKE с % в правой части
• UNION vs UNION ALL
• Используйте STRAIGHT_JOIN
• EXIST vs IN
• CHAR vs VARCHAR
• Использование функций в WHERE
• Использование подзапросов/CTE для вычислений в SQL-запросах
• Используйте VIEW для хранения агрегированных стабильных данных.
• Прочее методы оптимизации запросов
• Итог

Используйте LIMIT

-- Запрос 1 （контрпример）
SELECT id, movie_name, rating FROM movie LIMIT 100000, 30;

-- Запрос 2 (пример)
SELECT id, movie_name, rating FROM movie WHERE id > 100000 LIMIT 30;

• Запрос 1 должен просмотреть и отбросить первые 100 000 строк, прежде чем получить последующие 30 строк, что может быть неэффективным и более медленным, особенно если нет соответствующего индекса для поддержки этой операции.
• В Запросе 2 используется специальное условие, которое может использовать индекс на столбце id, позволяя MySQL напрямую находить и извлекать первые 30 строк, удовлетворяющих условию, что потенциально быстрее, чем перебор большого количества строк.

Используйте LIKE с % в правой части

-- Запрос 1
SELECT id, movie_name, rating FROM movie where movie_name like "%Spiderman";

-- Запрос 2
SELECT id, movie_name, rating FROM movie where movie_name like "%Spiderman%";

-- Query 3
SELECT id, movie_name, rating FROM movie where movie_name like "The Amazing%";

При использовании подстановочных знаков в начале (Запрос 1) или с обеих сторон (Запрос 2) поискового шаблона может потребоваться просмотр всего набора данных, что может негативно сказаться на производительности запроса, особенно при работе с большими наборами данных.

UNION vs UNION ALL

-- Запрос 1
SELECT "Movie Title" FROM action_movies
UNION
SELECT "Movie Title" FROM scifi_movies;

-- Запрос 2
SELECT "Movie Title" FROM action_movies
UNION ALL
SELECT "Movie Title" FROM scifi_movies;

• UNION без ключевого слова ALL автоматически удаляет дубликаты строк, так как выполняет операцию DISTINCT, а также по умолчанию сортирует результаты,
• Включает все строки из обеих таблиц, включая дубликаты, и по умолчанию не сортирует результат

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

Используйте STRAIGHT_JOIN

-- Запрос
SELECT
    orders.order_id,
    customers.customer_name
FROM
    orders STRAIGHT_JOIN customers
ON orders.customer_id = customers.customer_id;

STRAIGHT_JOIN может указать базе данных сначала обработать левую таблицу (orders), а затем правую таблицу (customers), независимо от выбора оптимизатора. Используйте STRAIGHT_JOIN, когда убедитесь, что размер данных в левой таблице меньше, чем в правой. Обычно оптимизаторы выбирают оптимальные способы объединения таблиц автоматически. Поэтому использовать STRAIGHT_JOIN следует осторожно и только в том случае, если это явно ускорит выполнение запроса.

EXIST vs IN

-- Запрос 1
SELECT *
FROM orders
WHERE customer_id IN (
    SELECT id
    FROM customers
    WHERE country = "MALAYSIA"
);

-- Запрос 2
SELECT *
FROM orders o
WHERE EXISTS (
    SELECT 1
    FROM customers c
    WHERE c.id = o.customer_id
    AND c.country = 'MALAYSIA'
);

• Используйте IN, если результаты подзапроса меньше результатов основного запроса, потому что база данных сначала выполнит подзапрос, а затем только основной запрос.
• Используйте EXISTS, если результаты подзапроса больше результатов основного запроса, потому что база данных сначала выполнит основной запрос, а затем только подзапрос.

CHAR vs VARCHAR

Тип данных влияет на производительность запроса, поэтому мы должны выбирать правильный тип данных в зависимости от требований.

Ключевые различия:

1. Хранение: CHAR использует для хранения максимальную длину, определённую независимо от фактической длины данных, в то время как VARCHAR использует для хранения только длину фактических данных плюс некоторые накладные расходы на хранение длины.
2. Заполнение: CHAR заполняет данные пробелами до заданной длины, в то время как VARCHAR не заполняет данные.
3. Эффективность: При работе со строками различной длины VARCHAR обычно занимает больше места, чем CHAR.
4. Извлечение: Получение данных из столбцов CHAR может быть немного быстрее, поскольку их длина фиксирована, в то время как для VARCHAR может потребоваться дополнительная проверка длины.

Использование функций в WHERE

-- Запрос 1
SELECT * FROM users WHERE POW(age, 2) > 100;

-- Запрос 2
SELECT * FROM users WHERE age > SQRT(100);

• Прямое использование функции POW в столбце age может привести к неэффективности индекса.

Использование функций непосредственно к индексированным столбцам в предложении WHERE может потенциально препятствовать использованию индексов.

Использование подзапросов/CTE для вычислений в SQL-запросах

-- Запрос 1
SELECT
    SUM(failed) AS total_failed,
    SUM(success) AS total_success,
    (SUM(success) / (SUM(failed) + SUM(success))) * 100 AS success_rate_percentage
FROM
    results;

-- Запрос 2 CTE подход
WITH Summary AS (
    SELECT
        SUM(failed) AS total_failed,
        SUM(success) AS total_success
    FROM
        results
)
SELECT
    total_failed,
    total_success,
    (total_success / (total_failed + total_success)) * 100 AS success_rate_percentage
FROM
    Summary;

-- Запрос 3
SELECT
    total_failed,
    total_success,
    (total_success / (total_failed + total_success)) * 100 AS success_rate_percentage
FROM (
    SELECT
        SUM(failed) AS total_failed,
        SUM(success) AS total_success
    FROM
        results
) AS subquery_alias;

Запрос 2 работает быстрее, потому что он вычисляет итоговые значения (failed и success) только один раз в CTE, а затем использует эти предварительно вычисленные значения для определения коэффициента успешности в основном запросе. Это сокращает количество вычислений SUM (в 3 раза) по сравнению с Запросом 1, выполняющим функцию SUM большее количество раз (5 раз) в рамках одного и того же запроса.

Используйте VIEW для хранения агрегированных стабильных данных.

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

Прочее методы оптимизации запросов

• По возможности используйте наименьший тип данных. Меньшие типы данных обычно требуют меньше места для хранения, а это повышает производительность, особенно при работе с большими наборами данных.
• FORCE INDEX предлагает оптимизатору запросов MySQL использовать определённый индекс при выполнении оператора SELECT.
• Фильтрация всегда должна быть на первом месте, потому что фильтрация позволяет сузить набор результатов, используя индексы и уменьшая количество строк, которые необходимо обработать для достижения оптимизации MySQL.
• Извлекайте из таблицы только определённые столбцы. Избегайте получения всех столбцов из таблицы с помощью запроса SELECT *.
• Сокращение количества объединений таблиц (JOIN) может повысить производительность запросов.
• Используйте TRUNCATE TABLE вместо DELETE FROM, если требуется удалить все строки из таблицы. TRUNCATE TABLE работает быстрее DELETE FROM, поскольку удаляет данные путём высвобождения страниц данных, используемых для хранения данных таблицы. Но при этом данной операции нельзя сделать откат. Поэтому будьте осторожны перед выполнением TRUNCATE TABLE.
• Индекс играет решающую роль в определении эффективности ваших запросов. Но индекс — это обширная тема, поэтому в этой статье не будем её рассматривать.

Итог

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