Производительность REGEX в SQL Server 2025 не так уж плоха!

Автор: Brent Ozar, Update: SQL Server 2025’s REGEX Performance Isn’t So Bad!

Ещё в марте 2025 года, когда Microsoft впервые анонсировала поддержку REGEX в SQL Server 2025 и Azure SQL DB, я провёл быстрое тестирование, и производительность была ужасающей. Она была плохой в трёх разных аспектах:

1. Использование ЦП было ужасным — сжигалось 60 секунд процессорного времени для проверки нескольких миллионов строк
2. Он отказывался использовать индекс
3. Оценка кардинальности была ужасной, жёстко закодирована на уровне 30% от таблицы

После комментария Эрланда Соммарског в этом месяце я вернулся и снова запустил тесты с релизной версией SQL Server 2025. Отличные новости! Microsoft исправила 1 из проблем, и... ну, одна из них немного хитрая. Для демонстрации я собираюсь использовать большую базу данных Stack Overflow за апрель 2024 года, чтобы создать наихудший сценарий, затем начать с индекса на небольшой таблице Users и запросить её через regex, как мы делали в статье от марта 2025 года.

CREATE INDEX Location ON dbo.Users(Location);

SET STATISTICS IO, TIME ON;

SELECT TOP 100 *
FROM dbo.Users 
WHERE REGEXP_LIKE(Location, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

Фактический план выполнения:

Это заняло около 8 секунд, всё это время сжигался ЦП. Это на самом деле ОТЛИЧНО, ОГРОМНОЕ улучшение по сравнению с прошлым разом! 8 секунд процессорного времени звучит плохо, но это фантастически, учитывая количество строк, которые SQL Server должен был проверить, чтобы найти 100 совпадений:

Поскольку данные, которые я искал, были относительно редкими, SQL Server пришлось прочитать около 10 миллионов строк, чтобы найти 100 совпадений. Это означает, что SQL Server смог читать 1,2 миллиона строк в секунду и проверять их содержимое с помощью regex. Это потрясающе! Мне это нравится, и я хотел бы, чтобы история на этом закончилась.

Но давайте переключимся на изучение столбца Title таблицы Posts, одного из самых больших в базе данных. Я создал индекс на столбце Title:

CREATE INDEX Title ON dbo.Posts(Title)
    WITH (ONLINE = OFF, MAXDOP = 0);
GO
sp_BlitzIndex @TableName = 'Posts'

Таблица содержит около 60 миллионов строк, кластерный индекс занимает 163 ГБ, а индекс только по Title — 3 ГБ. Если SQL Server будет использовать индекс, это даст нам огромный прирост производительности по сравнению с необходимостью сканировать всю таблицу.

Давайте запустим тот же фильтр WHERE, но на этот раз используем SUM(1) вместо TOP 100, чтобы SQL Server был вынужден обработать все строки, и чтобы я мог продемонстрировать оценку кардинальности:

SELECT SUM(1)
FROM dbo.Posts 
WHERE REGEXP_LIKE(Title, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

Фактический план на первый взгляд выглядит не очень, но держитесь, потому что это действительно наихудший сценарий — здесь есть кое-что отличное:

Во-первых, он использовал индекс! Это фантастика. Очевидно, мы не можем выполнить поиск по нему, но по крайней мере мы читаем только 3 ГБ данных вместо 163 ГБ. Это хорошо — это та проблема, которую Microsoft полностью исправила. Мне это нравится.

Во-вторых, он автоматически распараллелился, распознав, что работы будет много. Ему пришлось прочитать 60 миллионов строк, и это заняло 7 минут, так что он обрабатывал около 145 тысяч строк в секунду. Это... не хорошо. Это огромное падение по сравнению с обработкой нашей таблицы Users, которая достигала примерно 1,2 миллиона строк в секунду. Пока запрос выполнялся, уф, наш бедный сервер:

Здесь становится жарко, так что загрузите все ваши ядра

Статистика ожиданий — это катастрофа параллелизма:

Так... является ли параллелизм проблемой? Я слышал, как люди говорят, что CXCONSUMER безвреден, но постоянные читатели здесь знают лучше. Добавьте указание OPTION (MAXDOP 1) к запросу, и он выполнится за 32 секунды:

Что возвращает нас к 1,86 миллиона строк в секунду, обрабатываемых REGEX. Это, честно говоря, фантастически. Если вам нужно найти иголку в стоге сена с помощью regex, и у вас есть индекс, чтобы сканировать меньше данных, и если планирование ЦП не мешает, это чертовски быстрый способ сделать это. Обратите внимание, что я не пробовал более сложные регулярные выражения — я не хочу придумывать синтетические вещи для тестирования, вместо этого вам следует тестировать с теми regex, которые вы действительно собираетесь использовать в своей работе.

С другой стороны, обратите внимание, что расчётное количество строк по-прежнему отвратительно — SQL Server оценил, что вернётся 5 383 710 строк, тогда как на самом деле не вернулось ни одной. Я считаю это справедливым, потому что я не знаю, как можно предсказать количество строк, которые будут соответствовать заданному regex. Чёрт возьми, я даже не могу ПРОЧИТАТЬ большинство regex и понять, что они пытаются сделать. Если в вашем запросе есть регулярное выражение в фильтре, вам, вероятно, следует сначала загрузить соответствующие строки во временную таблицу, чтобы при последующих соединениях SQL Server лучше понимал количество строк, которые будут задействованы.

Итак, в заключение, ситуация с regex в SQL Server 2025 лучше, чем была в Azure SQL DB — по крайней мере, теперь используются индексы, и ЦП лучше, чем был. Просто будьте осторожны при запуске в рабочей среде — если вы используете его только как служебный запрос для быстрого исследования, попробуйте добавить указание MAXDOP 1 по двум причинам. Он может выполниться быстрее, и будет меньше вероятность, что он захватит весь ЦП сервера.