Новое в SQL Server 2025: Кардинальность и REGEXP_LIKE

Автор: Louis Davidson, Cardinality and REGEXP_LIKE

Я читал пост Брента Озара о регулярных выражениях в SQL Server 2025 (T-SQL Has Regex in SQL Server 2025. Don’t Get Too Excited) и о том, почему они работают так, как работают. В комментариях кто-то упомянул несколько подсказок (hints), которые якобы должны улучшить ситуацию. О них написано немного, поэтому я решил сам проверить, как они себя ведут. Речь идёт о: ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP и ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP. Они работают, корректируя ожидаемое количество строк, возвращаемых условием с оператором REGEXP.

Тестовая таблица

Полный скрипт приведён в приложении (он совпадает с кодом из статьи «SQL Server REGEXP_LIKE features augment, not replace, LIKE»). Отличия: я добавил имя для ограничения PRIMARY KEY и увеличил количество строк с 1000 до 100000. Также вставим пару значений для выборки:

INSERT INTO dbo.RegExTest(Value)
VALUES ('This is my first known value'),
       ('This is my second known value');
  

Главное, что стоит отметить: у таблицы два индекса, и важен именно индекс по колонке Value:

sp_help 'RegExTest';
  

Фрагмент вывода:

index_name       index_description                                   index_keys
---------------- --------------------------------------------------- -------------
PK_RegExTest     clustered, unique, primary key located on PRIMARY   RegExTestId
Value            nonclustered located on PRIMARY                     Value
  

Индекс Value не уникальный, но посмотрим, как он работает с REGEXP_LIKE и, для сравнения, с LIKE.

Контрольные тесты

Начнём с запроса, возвращающего все строки:

SELECT Value
FROM RegExTest;
  

Оценка: 100002 строк, фактическое количество — 100002. Выполнился Clustered Index Scan. Удивительно, ожидал использование индекса Value, но строки короткие, возможно это повлияло на план.

Теперь запрос с LIKE:

SELECT Value
FROM RegExTest
WHERE Value LIKE 'This is my first known value';
  

План использовал только индекс Value. Прогноз: 1 строка, фактически: 1 строка.

REGEXP_LIKE без подсказок

SELECT Value
FROM RegExTest
WHERE REGEXP_LIKE(Value,'^This is my first known value$');
  

Выполнение заняло 6 секунд (на домашнем ПК). Прогноз — 9000.18 строк, при 100002 строках в таблице.

Хинт ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP

Используем ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP:

SELECT Value
FROM RegExTest
WHERE REGEXP_LIKE(Value,'^This is my first known value$')
OPTION (USE HINT ('ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP'));
  

Прогноз вырос до 50001 строк (половина таблицы). Реальной ускоренной работы пока нет, но алгоритм оценки держится стабильно.

Хинт ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP

SELECT Value
FROM RegExTest
WHERE REGEXP_LIKE(Value,'^This is my first known value$')
OPTION (USE HINT ('ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP'));
  

Прогноз уменьшился до 500.01 строк. Но и в этом случае план не использовал индекс Value как покрывающий, производительность не изменилась.

Важно: для маленькой таблицы это несущественно. Но в больших запросах разница между 500, 9000 и 50000 строк может радикально повлиять на выбор плана.

Принудительное использование индекса

SELECT Value
FROM RegExTest WITH (index = Value)
WHERE REGEXP_LIKE(Value,'^This is my first known value$');

SELECT Value
FROM RegExTest WITH (index = Value)
WHERE REGEXP_LIKE(Value,'^This is my first known value$')
OPTION (USE HINT ('ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP'));

SELECT Value
FROM RegExTest WITH (index = Value)
WHERE REGEXP_LIKE(Value,'^This is my first known value$')
OPTION (USE HINT ('ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP'));
  

Прогнозы кардинальности одинаковы. Отличие: теперь выполняется Index Scan (Nonclustered) вместо Clustered Index Scan.

CREATE UNIQUE INDEX UniqueValue ON RegExTest (Value);
  

С уникальным индексом запрос сам использует его для сканирования. Но оценки кардинальности остаются прежними.

Вывод

Подсказки в SQL Server — не лучший инструмент, но иногда они необходимы, особенно в сложных запросах. При использовании REGEXP_LIKE почти всегда нужно ожидать высокую стоимость выполнения. Часто придётся добавлять альтернативные фильтры, потому что LIKE справляется не со всеми задачами.

Иногда фактическое количество строк может сильно отличаться от прогноза, и запрос будет работать медленно. Подсказки помогают скорректировать ожидания. Например:

SELECT Value
FROM RegExTest
WHERE REGEXP_LIKE(Value,'.*')
OPTION (USE HINT ('ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP'));
  

Здесь прогноз совпадает с другими вариантами, хотя реально возвращаются все строки таблицы.

Приложение

Код для загрузки тестовых данных:

USE TempDb;
GO

SET NOCOUNT ON;

-- Drop the table if it exists
IF OBJECT_ID('dbo.RegExTest', 'U') IS NOT NULL
DROP TABLE dbo.RegExTest;

-- Create the table
CREATE TABLE dbo.RegExTest (
  RegExTestId INT IDENTITY(1,1) CONSTRAINT PK_RegExTest PRIMARY KEY,
  Value NVARCHAR(100) INDEX Value
);

-- Declare variables
DECLARE @i INT = 1;
DECLARE @chars NVARCHAR(62) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
DECLARE @len INT;
DECLARE @str NVARCHAR(100);
DECLARE @pos INT;

-- Loop to insert 100000 rows
WHILE @i <= 100000
BEGIN
  SET @len = 10 + ABS(CHECKSUM(NEWID())) % 41; -- Length between 10 and 50
  SET @str = '';

  WHILE LEN(@str) < @len
  BEGIN
      SET @pos = 1 + ABS(CHECKSUM(NEWID())) % LEN(@chars);
      SET @str = @str + SUBSTRING(@chars, @pos, 1);
  END

  INSERT INTO dbo.RegExTest (Value)
  VALUES (@str);

  SET @i += 1;
END;