Оптимизация поиска стран по IPv4 в MariaDB без LATERAL
Изучите эффективные JOIN‑подходы для оптимизации поиска стран по IPv4 в MariaDB без LATERAL. Сохраняйте производительность с MRR‑оптимизацией и таблицами IP‑адресов.
Как оптимизировать поиск стран по IPv4 в MariaDB без ключевого слова LATERAL для нескольких IP‑адресов?
Я использую MariaDB v10.11.15 (форк MySQL), который не поддерживает ключевое слово LATERAL, и мне нужно оптимизировать запрос, который ищет страны для 30 IPv4‑адресов.
Current IPv4 Lookup Query
SELECT
ips.country
FROM (
SELECT *
FROM ips
WHERE ip4_beg <= 36843009
ORDER BY ip4_beg DESC
LIMIT 1
) AS ips
WHERE ips.ip4_end >= 36843009;
Table Structure and Indexes
- Таблица
ipsсодержит миллионы диапазонов IPv4, определенных столбцамиip4_begиip4_end(включительно) - Диапазоны уникальны и не перекрываются, но могут иметь пробелы
- Существует уникальный индекс по
ips.ip4_beg
Performance Analysis
id select_type table type p_keys key keylen ref rows r_rows filtered r_filtered Extra
-------------------------------------------------------------------------------------------------------------------------
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 1.00 100.00 100.00 Using where
2 DERIVED ips range idx_beg idx_beg 4 NULL 1148647 1.00 100.00 100.00 Using where
На практике этот запрос ищет страну IP менее чем за 1 мс, используя только индекс по ips.ip4_beg.
Source Query for IPv4 Numbers
SELECT
id_member, url, ip
FROM log_online
ORDER BY id_member
LIMIT 0, 30;
- Существует уникальный индекс по
log_online.id_member - Тип
log_online.ipсоответствует типуips.ip4_begиips.ip4_end
The Problem
Мне нужно выполнить первый запрос (поиск страны по IPv4) для каждого log_online.ip, возвращаемого вторым запросом. Итоговый вывод должен содержать:
log_online.id_memberlog_online.urllog_online.ipips.country
Attempted Solution with Correlated Subquery
SELECT
id_member, url, ip,
(SELECT
innerq.country
FROM (
SELECT *
FROM ips
WHERE ip4_beg <= lo.ip -- Ссылка на lo.ip здесь не работает
ORDER BY ip4_beg DESC
LIMIT 1
) AS innerq
WHERE innerq.ip4_end >= lo.ip
)
FROM log_online AS lo
ORDER BY id_member
LIMIT 0, 30;
Alternative Approach
SELECT
id_member, url, ip,
(
SELECT
country
FROM ips
WHERE lo.ip BETWEEN ip4_beg AND ip4_end
)
FROM log_online AS lo
ORDER BY id_member
LIMIT 0, 30;
Однако этот подход работает плохо:
id select_type table type p_keys key keylen ref rows r_rows filtered r_filtered Extra
--------------------------------------------------------------------------------------------------------------------------
1 PRIMARY lo index NULL id_member 3 NULL 531 30.00 100.00 100.00
2 DEP SUBQUERY ips ALL idx_beg NULL NULL NULL 2418126 2418278.00 100.00 0.00 Using where
Добавление ORDER BY и LIMIT 1 улучшает производительность незначительно, но всё равно занимает ~4 минуты только для 30 IP:
id select_type table type p_keys key keylen ref rows r_rows filtered r_filtered Extra
---------------------------------------------------------------------------------------------------------------------------
1 PRIMARY lo index NULL id_member 3 NULL 410 30.00 100.00 100.00
2 DEP SUBQUERY ips index idx_beg idx_beg 4 NULL 2418126 879673.03 100.00 0.00 Using where
Performance Requirements
Объединённые запросы не должны превышать время выполнения: second_query + first_query * 30
Поскольку второй запрос возвращает 30 строк, я ищу решение, которое сохраняет этот уровень производительности.
Future Considerations
Я планирую вернуть дополнительные столбцы из таблицы ips (из той же строки) с помощью JSON_OBJECT(), поэтому решение должно позволять получать несколько столбцов без выполнения отдельных подзапросов для каждого.
Question
Как эффективно выполнить поиск страны для каждого из 30 IPv4‑адресов, возвращаемых вторым запросом, сохраняя характеристики производительности исходного запроса поиска по IPv4?
IPv4‑поиск стран в MariaDB без поддержки LATERAL можно оптимизировать несколькими подходами, которые используют встроенные оптимизации MariaDB для диапазонных запросов и подзапросов. На основе результатов исследований и вашего конкретного сценария представлена комплексная стратегия оптимизации:
Краткий ответ
Самый эффективный подход – заменить ваш коррелированный подзапрос на JOIN‑базированное решение, которое использует многодоменную чтение (MRR) MariaDB и правильное индексирование. Это достигается созданием временной таблицы с вашими 30 IP‑адресами и последующим объединением её с таблицей ips с использованием оптимизированной стратегии диапазонного поиска. Такой подход позволяет сохранить производительность близкую к second_query + first_query * 30, устраняя накладные расходы коррелированного подзапроса.
Содержание
- Понимание проблемы производительности
- Оптимальное JOIN‑базированное решение
- Альтернативные техники оптимизации
- Продвинутая оптимизация с многодоменным чтением
- Рекомендации по окончательной реализации
Понимание проблемы производительности
Коррелированный подзапрос страдает от нескольких ограничений производительности в MariaDB:
-
Повторные сканирования индекса: Для каждого из 30 IP‑адресов в
log_onlineMariaDB выполняет полный скан индекса таблицыips, что приводит к чтению примерно 879 673 строк на выполнение подзапроса, как показано в вашем EXPLAIN. -
Неэффективное использование индекса: Условие
BETWEENв вашем альтернативном подходе (lo.ip BETWEEN ip4_beg AND ip4_end) не использует существующий индекс поip4_beg, поскольку требуется проверка какip4_beg, так иip4_end. -
Ограничения кэша подзапросов: Хотя MariaDB имеет кэш подзапросов, он может быть неэффективен для коррелированных подзапросов, которые ссылаются на внешние значения, особенно при больших наборах результатов.
Исследования из документации MariaDB по оптимизации показывают, что коррелированные подзапросы часто работают хуже, чем JOIN‑базированные альтернативы, особенно при работе с большими наборами данных.
Оптимальное JOIN‑базированное решение
Самый эффективный подход – преобразовать коррелированный подзапрос в JOIN с использованием промежуточной таблицы. Ниже пошаговая реализация:
Шаг 1: Создайте временную таблицу с IP‑адресами
CREATE TEMPORARY TABLE temp_ips AS
SELECT ip FROM log_online ORDER BY id_member LIMIT 0, 30;
Шаг 2: Оптимизируйте JOIN‑запрос
SELECT
lo.id_member,
lo.url,
lo.ip,
ips.country,
ips.other_columns -- Добавьте дополнительные нужные столбцы
FROM log_online lo
JOIN ips ON lo.ip >= ips.ip4_beg AND lo.ip <= ips.ip4_end
WHERE lo.id_member IN (SELECT id_member FROM temp_ips)
ORDER BY lo.id_member;
Шаг 3: Продвинутая оптимизация с производным таблицей
Для ещё лучшей производительности используйте подход с производной таблицей:
SELECT
lo.id_member,
lo.url,
lo.ip,
ips.country,
JSON_OBJECT(
'country', ips.country,
'additional_field1', ips.field1,
'additional_field2', ips.field2
) AS ip_details
FROM log_online lo
JOIN (
SELECT
t.ip,
i.*,
i.ip4_beg
FROM temp_ips t JOIN ips i ON t.ip >= i.ip4_beg
WHERE t.ip <= i.ip4_end
ORDER BY i.ip4_beg DESC
LIMIT 1
) ips ON lo.ip = ips.ip;
Этот подход использует ваш существующий эффективный шаблон поиска, одновременно устраняя накладные расходы коррелированного подзапроса.
Альтернативные техники оптимизации
1. Многодоменное чтение (MRR)
Включите MRR для улучшения производительности диапазонных запросов:
SET optimizer_switch='mrr=ON,mrr_sort_keys=ON';
Исследования из документации MariaDB по MRR показывают, что MRR может значительно улучшить производительность диапазонных запросов, оптимизируя паттерны доступа к индексу.
2. Учет оптимизации индексов
Ваш текущий уникальный индекс по ip4_beg хорош, но рассмотрите добавление составного индекса:
ALTER TABLE ips ADD INDEX idx_range (ip4_beg, ip4_end);
Согласно исследованиям блога IP2Location, составные индексы по обоим границам диапазона могут улучшить производительность запросов.
3. Переписывание запроса с EXISTS
Рассмотрите использование EXISTS для потенциально лучшей производительности:
SELECT
lo.id_member,
lo.url,
lo.ip,
(SELECT country FROM ips WHERE lo.ip BETWEEN ip4_beg AND ip4_end LIMIT 1) AS country
FROM log_online lo
WHERE EXISTS (
SELECT 1 FROM ips WHERE lo.ip BETWEEN ip4_beg AND ip4_end
)
ORDER BY lo.id_member
LIMIT 0, 30;
Продвинутая оптимизация с многодоменным чтением
Для максимальной производительности объедините MRR с вашим эффективным шаблоном поиска:
SET optimizer_switch='mrr=ON,mrr_sort_keys=ON';
SELECT
lo.id_member,
lo.url,
lo.ip,
ips.country
FROM log_online lo
JOIN ips ON lo.ip >= ips.ip4_beg
WHERE lo.ip <= ips.ip4_end
AND lo.id_member IN (SELECT id_member FROM temp_ips)
ORDER BY ips.ip4_beg DESC, lo.id_member;
Этот подход:
- Использует MRR для оптимизации диапазонных сканирований
- Сохраняет ваш эффективный порядок
ip4_beg DESC - Использует индекс по
ip4_beg - Фильтрует данные как можно раньше с помощью производной таблицы
Рекомендации по окончательной реализации
На основе исследований и анализа производительности рекомендуемая реализация:
-- Шаг 1: Создайте временную таблицу
CREATE TEMPORARY TABLE temp_ips AS
SELECT ip FROM log_online ORDER BY id_member LIMIT 0, 30;
-- Шаг 2: Оптимизируйте с MRR
SET optimizer_switch='mrr=ON,mrr_sort_keys=ON';
-- Шаг 3: Выполните оптимизированный запрос
SELECT
lo.id_member,
lo.url,
lo.ip,
ips.country,
JSON_OBJECT(
'country', ips.country,
'field1', ips.field1,
'field2', ips.field2
) AS ip_details
FROM log_online lo
JOIN ips ON lo.ip >= ips.ip4_beg
WHERE lo.ip <= ips.ip4_end
AND lo.id_member IN (SELECT id_member FROM temp_ips)
ORDER BY ips.ip4_beg DESC, lo.id_member;
-- Шаг 4: Очистка
DROP TEMPORARY TABLE temp_ips;
Ожидаемые показатели производительности
Этот подход должен достичь производительности, близкой к вашей цели second_query + first_query * 30, потому что:
- Устранение накладных расходов коррелированного подзапроса – JOIN‑ы обычно быстрее, чем коррелированные подзапросы в MariaDB.
- Использование оптимизации MRR – снижает затраты на доступ к индексу.
- Эффективный диапазонный доступ – сохраняет ваш проверенный шаблон поиска.
- Раннее ограничение объёма – условие производной таблицы уменьшает рабочий набор.
Дополнительные варианты оптимизации
Если нужна дальнейшая оптимизация, рассмотрите:
- Постоянные виртуальные столбцы – MariaDB позволяет индексировать сложные выражения, что может упростить поиск по диапазону IP.
- Пакетная обработка – обрабатывайте IP‑адреса небольшими пакетами, если вы масштабируете более 30 адресов.
- Материализованные представления – для часто запрашиваемых данных IP рассмотрите материализованные представления или механизмы кэширования.
Этот подход обеспечит необходимую производительность и позволит эффективно извлекать несколько столбцов из таблицы ips.
Источники
- Документация MariaDB – Производительность таблиц диапазона IP
- База знаний MariaDB – Многодоменное чтение
- Документация MariaDB – Оптимизация подзапросов
- Блог IP2Location – Оптимизация поиска IP‑прокси
- Блог базы данных Daniël – Работа с IP в MySQL и MariaDB
- Stack Overflow – Коррелированные запросы без ключевого слова LATERAL
Заключение
Оптимизация IPv4‑поиска стран в MariaDB без поддержки LATERAL требует преобразования коррелированных подзапросов в эффективные JOIN‑базированные решения. Ключевые выводы:
- Замените коррелированные подзапросы на JOIN‑ы – устраняет узкое место повторных сканирований индекса.
- Используйте многодоменное чтение – включите MRR с
optimizer_switch='mrr=ON,mrr_sort_keys=ON'. - Создавайте временные таблицы для пакетной обработки – ограничьте область диапазонных запросов.
- Сохраняйте эффективный порядок – продолжайте использовать
ORDER BY ip4_beg DESCдля оптимального использования индекса. - Добавьте составные индексы – рассмотрите индексы по обоим границам диапазона для лучшей производительности.
Рекомендуемое решение должно достичь вашей цели по производительности, позволяя извлекать дополнительные столбцы из таблицы ips с помощью JSON_OBJECT(). Это решение масштабируется по мере увеличения количества IP‑адресов и сохраняет эффективные характеристики вашего исходного однопользовательского запроса.