RUEN
Пакет · mui-data-grid

Диалекты и индексы

Из двадцати трёх операторов двадцать записываются в SQL одинаково на любой СУБД. Оставшиеся четыре — регистронезависимые строковые — не записываются одинаково нигде. Эта страница объясняет, почему выбор написания вынесен в отдельное решение, как оно принимается и что каждый вариант стоит по производительности.

Почему это вообще проблема

contains в MUI DataGrid означает «содержит, без учёта регистра». В SQL этого понятия нет — есть три разных способа его выразить, и они не взаимозаменяемы:

СУБД Как пишется Что будет с ILIKE
PostgreSQL col ILIKE :v работает
MySQL / MariaDB col LIKE :v при коллации *_ci ERROR 1064 — синтаксическая ошибка
SQLite col LIKE :v (только ASCII) syntax error near "ILIKE"

Обратите внимание на характер отказа: ILIKE на MySQL — не «менее эффективно», а неработающий запрос целиком. Симметрично: если убрать регистронезависимость и всегда писать LIKE, сломается PostgreSQL — но молча. Поиск «winter» перестанет находить «Winter Internals», ошибки не будет, пользователь просто решит, что таких строк нет.

Отсюда вывод, определивший устройство: написание нельзя выбрать один раз за всех. Оно — свойство соединения, а грид про соединение ничего не знает.

Почему не «просто LIKE»

Соблазнительно писать LIKE всегда и объявить регистр заботой коллации. Не работает по двум причинам.

Во-первых, в PostgreSQL нет регистронезависимых коллаций в привычном смысле — стандартная ICU-коллация с deterministic = false существует, но задаётся на уровне колонки или базы и в существующих схемах почти не встречается. Для подавляющего большинства PostgreSQL-проектов LIKE останется регистрозависимым.

Во-вторых, тихая деградация хуже громкого отказа. Синтаксическая ошибка на MySQL обнаруживается на первом же запросе и в разработке, и в тестах. Молча пропавшие совпадения на PostgreSQL обнаруживаются через месяц по жалобе пользователя.

Как принимается решение

По умолчанию схема находится в режиме TextMatch::Auto, и MuiGrid::wrap() разрешает его один раз за вызов, до построения условий:

text
MuiGrid::wrap($repo, $request, $schema)
│
├─ режим схемы = Auto?
│    └─ да → TextMatch::forRepository($repo)
│              │
│              ├─ $repo->getDbConfigClassName()      имя класса конфигурации
│              ├─ PpaConnectionPool::getConfigDb()   объект конфигурации (из кэша пула)
│              ├─ $config->getDriver()               'pgsql' | 'mysql' | 'sqlite' | …
│              └─ forDriver()                        ILike | Like | Lower
│
└─ построить WHERE с уже конкретным режимом

Три свойства этой цепочки стоит отметить.

Она не открывает соединения. Драйвер известен из конфигурации, а не из живого подключения. Конфигурация к моменту построения грида уже зарегистрирована пулом и лежит в его статическом кэше, так что определение стоит одного обращения к массиву. Даже в худшем случае — конфигурация ещё не тронута — пул создаст объект конфигурации и вызовет его setUp(), но не подключится к базе.

Она не бросает исключений. Любая осечка — пула нет, класс не зарегистрирован, в тесте подставлен двойник репозитория — приводит к откату на TextMatch::Lower, который валиден везде. Страница таблицы не должна падать из-за того, что не удалось определить диалект.

Неизвестный драйвер — не ошибка. Oracle, MSSQL, свой DbConfig с экзотическим драйвером получают Lower: lower(col) LIKE lower(:v) — стандартный SQL, он выполнится где угодно.

Приоритет режимов

text
режим колонки (если не Auto)
      ↓ иначе
режим схемы (если не Auto)
      ↓ иначе
определение по драйверу репозитория
      ↓ не удалось
TextMatch::Lower

Явное всегда сильнее автоматического — определение никогда не «переубеждает» разработчика.

Что каждый режим стоит

Здесь важно разделить два случая: шаблон с ведущим % и шаблон-префикс.

contains и endsWith неиндексируемы в принципе. Шаблон '%winter%' начинается с подстановочного знака, а btree-индекс упорядочен слева направо — начало строки неизвестно, значит искать по дереву не с чего. Это верно для всех трёх СУБД и для всех трёх режимов. Разницы между ILIKE, LIKE и lower() здесь нет: любой из них — полный проход.

startsWith — единственное место, где выбор влияет:

Режим startsWith Может использовать индекс
Like col LIKE 'x%' да — btree по col (в PostgreSQL нужен text_pattern_ops или коллация C)
ILike col ILIKE 'x%' нет — никогда
Lower lower(col) LIKE 'x%' да — функциональный индекс по lower(col)
sql
-- под режим Lower
CREATE INDEX idx_articles_title_lower ON articles (lower(title) text_pattern_ops);

-- под режим Like в PostgreSQL (когда регистр не важен)
CREATE INDEX idx_articles_title_pattern ON articles (title text_pattern_ops);

Парадоксально, но самый «медленный» на вид режим Lower — единственный, который вообще позволяет проиндексировать регистронезависимый префиксный поиск в PostgreSQL. Если в вашей таблице поиск по началу строки — горячий путь, ->textMatch(TextMatch::Lower) плюс функциональный индекс дадут больше, чем любые другие правки.

Когда нужен полнотекстовый поиск

Если contains по большой таблице стал узким местом, правильный ответ — не другой режим, а другой инструмент: pg_trgm с GIN-индексом в PostgreSQL, FULLTEXT в MySQL, FTS5 в SQLite. Подключается это через filterUsing() для конкретной колонки — библиотека не мешает подставить любое условие.

Свёртка регистра в двух местах

Режим Lower сворачивает обе стороны сравнения, но разными реализациями: колонку — функцией SQL lower(), шаблон — функцией PHP mb_strtolower().

php
TextMatch::Lower->match('a.title', '%WiNTeR Ünïcode%');
// SQL:   lower(a.title) LIKE :iqb0
// байнд: '%winter ünïcode%'

В подавляющем большинстве случаев результаты совпадают. Расхождения возможны на краях: турецкая «i без точки», немецкая «ß», греческая «сигма в конце слова» — их правила свёртки зависят от локали, а PHP и СУБД берут их из разных источников. Если ваши данные это затрагивают, задайте режим явно и сворачивайте обе стороны одним способом — например, храните рядом заранее свёрнутую колонку и объявляйте её выражением колонки схемы.

Отдельно про SQLite: там LIKE регистронезависим только для ASCII, если не собран с ICU. Поэтому для не-латинских данных на SQLite Lower — не оптимизация, а необходимость: режим Like просто не найдёт Ünïcode по запросу ünïcode.

Запрос COUNT

Пагинация выполняет два запроса: страницу и общее число строк. Второй строится оборачиванием вашего SELECT с отбрасыванием ORDER BY, LIMIT, OFFSET и FOR. Для диалектов отсюда следует практическое: условие фильтра выполняется дважды.

Это не удваивает стоимость буквально — планировщик работает с одним и тем же предикатом, — но означает, что дорогое выражение в колонке (коррелированный подзапрос, вызов функции по каждой строке) оплачивается в обоих запросах. Если фильтровать по такому выражению не нужно, объявляйте колонку только sortable(): сортировка из COUNT-запроса выбрасывается.

Что дальше