T-SQL получил Regex в SQL Server 2025. Но не спешите радоваться

Автор: Brent Ozar. T-SQL Has Regex in SQL Server 2025. Don’t Get Too Excited

Регулярные выражения позволяют выполнять сложные поиски по строкам. Они действительно полезны, но у них репутация: их трудно писать, трудно читать и ещё сложнее отлаживать. Однако, освоив их, можно решать очень специфические задачи.

Этот пост не про сложность, а про производительность regex в Azure SQL DB и SQL Server 2025.

Regex как Everclear

Everclear — это марка алкоголя. Звучит заманчиво: без запаха, вкуса и цвета. Но на самом деле это 95% чистого спирта, настолько крепкий, что во многих штатах США его просто запретили. Даже в Неваде, где разрешены казино, оружие и марихуана.

Пить Everclear в чистом виде нельзя. Его используют только профессионалы, и то в очень малых дозах и в сочетании с другими ингредиентами. Чтобы получить примерное представление о том, какой он на вкус, посмотрите, как его пробует The Unemployed Wine Guy.

Попробуем Regex

Возьмём базу Stack Overflow 2010 (малую), размещённую в Azure SQL DB. В таблице Users около 9 млн строк (около 1 ГБ).

Предположим, мы ищем, не вставляют ли пользователи личные данные (например, email) в поле Location. Создадим индекс и выполним запрос:

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,}$');
  

Почему представление такое безвкусное?

Можно подумать: «Это из-за SELECT *, а в индексе только Location». Но запрос просит лишь 100 строк. SQL Server мог бы взять их из индекса и сделать key lookup. Давайте проверим:

SELECT TOP 100 *
FROM dbo.Users 
WHERE REGEXP_LIKE(Location, '...');

SELECT TOP 100 *
FROM dbo.Users WITH (INDEX = Location)
WHERE REGEXP_LIKE(Location, '...');

А сокращённая версия STATISTICS IO показывает, что индекс позволил бы значительно сократить количество обращений к страницам:

Table 'Users'. Scan count 1, logical reads 157281
   CPU time = 60500 ms,  elapsed time = 60955 ms.
 
Table 'Users'. Scan count 1, logical reads 20827
   CPU time = 58546 ms,  elapsed time = 58606 ms.

И план запроса действительно может с успехом использовать индекс. Верхний план — это план по умолчанию, нижний — с моей подсказкой по индексу:

Читатель: Почему оптимизатор не использует индекс?

Если оптимизатор считает, что предикат вашего запроса затронет большое количество строк, он может предпочесть кластерный индекс некластерным. Однако взгляните на эти планы ещё раз: они не могут выдать более 100 строк! Мой запрос специально запрашивает первые 100 строк. Сортировка отсутствует. Просто выдайте мне первые 100 подходящих строк. Я абсолютно уверен, что этот запрос не может привести к более чем 100 поискам по ключевым словам.

Тогда почему не используется индекс? Ну, когда Microsoft проектировала алгоритм оценки строк для регулярных выражений, они приняли несколько невероятно опрометчивых решений, возможно, под воздействием чистого Everclear. Давайте выполним другой запрос, чтобы понять, что происходит:Дело в том, что при оценке селективности regex Microsoft сделали «нетрезвые» допущения. Посмотрим другой запрос:

SELECT COUNT(*)
FROM dbo.Users 
WHERE REGEXP_LIKE(Location, '...');

Производительность низкая, потому что Microsoft снова поленилась

В SQL Server просто жёстко "захардкодили", что под regex подходят 30% строк . Это знакомо тем, кто изучал мой курс по оптимизации запросов. Для нашей таблицы это 2,675,250 строк. На деле совпадает лишь 68.

И это не только про маленькие таблицы. Даже при 9 млн строк (менее 1 ГБ) запрос с regex использовал 60 секунд времени CPU!

Это пугающе. Такой запрос в OLTP-системе будет просто губителен.

Обычный LIKE работает лучше

Вместо regex можно проверить утечку персданных через простое LIKE:

SELECT TOP 100 *
FROM dbo.Users
WHERE Location LIKE '%.com';

Читатель: «Ого, Брент, нельзя начинать поиск по шаблону со знака подстановки! Это плохо!» 

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

Да, результаты не полностью совпадают с regex, но для решения бизнес-задачи этого достаточно.

Regex как Everclear

Сам по себе regex — не зло. Просто применять его нужно осторожно, как Everclear. Это не инструмент для повседневных запросов. В большинстве случаев лучше обойтись другими методами.

Дополнение

Louis Davidson проверил новые подсказки ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP и ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP. Они могут помочь в отдельных случаях, но проблему в примере выше не решают.