Почему EXISTS быстрее IN? Ответ скрыт в метриках vmstat

Паттерн который смог. EXISTS vs IN: выходим за рамки EXPLAIN.

Предисловие

Мы иногда спорим с разработчиками о том, какой паттерн написания SQL-запроса эффективнее: EXISTS или IN. Обсуждения обычно сводятся к анализу планов выполнения, но этого бывает недостаточно для полной картины. Что если заглянуть глубже — на уровень операционной системы? Эта статья предлагает сделать именно это. С помощью анализа низкоуровневых метрик vmstat — контекстных переключений, нагрузки на процессор и работы с памятью — мы не просто подтвердим, что EXISTS работает быстрее, а наглядно покажем почему. Вы увидите, как выбор SQL-паттерна напрямую влияет на нагрузку ЦП и эффективность работы с памятью, превращая теоретическое преимущество в измеримый и понятный выигрыш в производительности.

ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub

Детали эксперимента и сравнение паттернов IN и EXISTS с точки зрения метрик СУБД

Сравнительный анализ метрик vmstat в ходе нагрузочного тестирования

Чек-лист CPU (Количество ядер CPU = 8)

Чек-лист RAM (Размер RAM = 8GB)

CS - Переключения контекста в секунду

График изменения количества переключений контекста в ходе нагрузочного тестирования для использования паттерна IN и паттерна EXISTS

График изменения относительной разницы между количество переключений контекста в ходе нагрузочного тестирования при использования паттерна EXISTS по сравнению с паттерном IN

Среднее превышение количеств переключений контекста, при использовании паттерна EXISTS составило 17%

IN - Прерывания

График изменения количества прерываний в ходе нагрузочного тестирования для использования паттерна IN и паттерна EXISTS

График изменения относительной разницы между количество прерываний в ходе нагрузочного тестирования при использования паттерна EXISTS по сравнению с паттерном IN

Среднее превышение количеств прерываний, при использовании паттерна EXISTS составило 1.41%

FREE - Cвободная память

График изменения свободной памяти в ходе нагрузочного тестирования для использования паттерна IN и паттерна EXISTS

График изменения относительной разницы между свободной памяти в ходе нагрузочного тестирования при использования паттерна EXISTS по сравнению с паттерном IN

Среднее превышение размера свободной памяти, при использовании паттерна EXISTS составило 190%

Итог

Использование паттерна EXISTS позволяет снизить нагрузку на RAM и повысить эффективность использования CPU в условиях высоко нагрузки и параллельных соединений.

P.S. Анализ метрик vmstat нейросетью DeepSeek

На основе анализа данных vmstat для двух тестовых прогонов с использованием IN и EXISTS выявлены следующие ключевые различия:

Основные различия в метриках:

1. Использование памяти (memory_free)

• IN: Стабильные показатели (206-230 MB), незначительные колебания
• EXISTS: Значительное сокращение свободной памяти с 754 MB до 420 MB (-44%)

2. Кэш памяти (memory_cache)

• IN: Постепенное уменьшение с 6787 до 6508 (-4%)
• EXISTS: Рост с 6276 до 6352 (+1%) с пиками до 6787

3. Переключения контекста (system_cs)

• IN: 5043-5524 переключений/сек
• EXISTS: 5932-6204 переключений/сек
• Разница: EXISTS показывает на 15-18% больше переключений контекста

4. Прерывания (system_in)

• IN: 8462-8765 прерываний/сек
• EXISTS: 8392-8842 прерываний/сек
• EXISTS демонстрирует более высокую начальную нагрузку

5. Очередь выполнения (procs_r)

• IN: Плавный рост с 21 до 54
• EXISTS: Более резкие колебания, достигает 54 к концу теста

6. Ввод-вывод (io_bo)

• IN: Стабильные значения 49-58 блоков
• EXISTS: Рост с 53 до 65 блоков (+23%) к концу теста

Критические выводы:

Паттерн IN демонстрирует:

• Более стабильное использование памяти
• Меньшую нагрузку на подсистему ввода-вывода
• Значительно меньшее количество переключений контекста
• Более предсказуемую нагрузку на планировщик процессов

Паттерн EXISTS показывает:

• Интенсивное использование памяти с большими колебаниями
• Повышенную нагрузку на ввод-вывод
• Большее количество переключений контекста, что указывает на менее эффективную работу с процессами

Заключение: На основе анализа системных метрик, паттерн IN в данном тестировании проявляет себя как более эффективный с точки зрения нагрузки на системные ресурсы, особенно в области управления памятью и операций ввода-вывода.