Как оптимизировать SQL в большом списке?

Содержание

Вопрос или проблема
Ответ или решение
1. Основные рекомендации по оптимизации
2. Альтернативные методы реализации
3. Дополнительные методы оптимизации
Заключение

Вопрос или проблема

У меня есть SQL-запрос, как показано ниже.

SELECT * FROM payment
WHERE is_deleted = 0
    AND is_privacy = 0
    AND ou_code IN ('A1', 'A2', ..., 'W1', 'W2'...)
LIMIT 0, 20

После in есть много элементов, около 3,000. Таблица payment содержит 50 миллионов данных, и ou_code, is_deleted, is_privacy все имеют индексы. Я попробовал следующие методы:

1、использовать временную таблицу: создать новую временную таблицу, вставить список ou_code в таблицу и создать индекс, а затем использовать внутреннее соединение. Но моему Java-приложению запрещено использовать DDL-операторы в программе, поэтому я не могу попробовать этот метод.

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

SELECT * FROM 
(VALUES ROW('A1'), ROW('A2')... ROW('W1'), ROW('W2')...) tem(`code`)
inner join 
payment
on payment.`ou_code` =  tem.`code`
WHERE is_deleted = 0
    AND is_privacy = 0
LIMIT 0, 20

Но эта таблица, похоже, не может создать индекс, так что эффективность не улучшается, а становится медленнее.

3、использовать exists: Я пробовал это, но этот метод тоже не может создать индекс и оказывается еще медленнее.

SELECT *
FROM 
payment
WHERE is_deleted = 0
    AND is_privacy = 0
    AND exists (select 1 from (SELECT 'WA1' AS `code`
UNION ALL  
SELECT 'WA2'  
UNION ALL  
SELECT 'WA3') tem where tem.code = payment.ou_code)
LIMIT 0, 20

4、полная таблица ou code: У меня есть еще одна идея, похожая на эту, но она еще не реализована. Можете помочь мне проанализировать, является ли она теоретически осуществимой?
Смысл ou code – это код компании, так что полное количество ou_code известно, вышеуказанный список в SQL также является частью полного количества.
Могу ли я сначала создать таблицу с индексом заранее, вставить полное количество данных ou_code, а затем использовать SQL, аналогичный следующему?

SELECT payment.* FROM payment
inner join full_ou_code on payment.ou_code = full_ou_code.ou_code
WHERE is_deleted = 0
    AND is_privacy = 0
    AND full_ou_code.ou_code IN ('A1', 'A2', ..., 'W1', 'W2'...)
LIMIT 0, 20

Любые идеи будут оценены.

Прежде всего, я согласен с @Jonas Metzler, если у вас нет прав DDL, вам следует поговорить с кем-то, кто может исправить структуру базы данных.

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

Под разбивкой я имею в виду что-то вроде этого

    SELECT * FROM payment
WHERE is_deleted = 0
    AND is_privacy = 0
    AND ou_code IN ('A1', 'A2', ..., 'A500')
LIMIT 0, 20;

-- Следующая партия...
SELECT * FROM payment
WHERE is_deleted = 0
    AND is_privacy = 0
    AND ou_code IN ('A501', 'A502', ..., 'A1000')
LIMIT 0, 20;

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

Ответ или решение

Оптимизация SQL-запросов в контексте работы с большими массивами данных является сложной задачей, особенно когда требуется обработка списков, содержащих тысячу и более значений. Ваш SQL-запрос, как видно из описания, пытается выбрать данные из таблицы payment на основе значений ou_code, что усложняется большим объемом данных и ограничениями по использованию DDL в Java-приложении.

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

Использование индексирования:

Убедитесь, что индексы на ou_code, is_deleted и is_privacy созданы корректно. Использование комбинированного индекса на этих трех полях может значительно улучшить производительность вашего запроса. Индексирование по выражению, которое объединяет эти поля, может уменьшить время выборки.

Переосмысление подхода к IN-оператору:

Если у вас есть 3000 значений в списке IN, это может вызвать значительное снижение производительности. Рассмотрите возможность разбивки этого списка на батчи, как было предложено, чтобы выполнять несколько запросов с меньшими наборами значений.

2. Альтернативные методы реализации

Использование временной таблицы:

Хотя вы столкнулись с ограничениями в использовании DDL, возможно, существует возможность создания временных таблиц через хранимые процедуры. Хранимые процедуры могут использовать DDL, и вы можете обернуть свою логику в такую процедуру, чтобы избежать ограничений вашего Java-приложения.

Создание полной таблицы ou_code:

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

SELECT payment.* 
FROM payment
INNER JOIN full_ou_code ON payment.ou_code = full_ou_code.ou_code
WHERE payment.is_deleted = 0
    AND payment.is_privacy = 0
    AND full_ou_code.ou_code IN ('A1', 'A2', ..., 'W2')
LIMIT 0, 20;

3. Дополнительные методы оптимизации

Фильтрация в подзапросе:

Вместо использования IN, рассмотрение JOIN с фильтрацией исходя из списков ou_code может быть более эффективным.
Создайте представление с необходимыми значениями ou_code. Это представление затем может быть использовано в ваших запросах.

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

Проведение анализа выполнения запрета (EXPLAIN) может помочь понять, где возникают узкие места. Это позволит вам получить представление о том, как запросы выполняются и какую часть данных они обрабатывают.

Заключение

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