Как я могу создать индекс по подстроке столбца?

#mysql #sql #sql-server #oracle #indexing

#mysql #sql #sql-сервер #Oracle #индексирование

Вопрос:

У меня есть таблица, содержащая пары ключ-значение, по которым я хотел бы иметь возможность эффективного поиска:

 SELECT * WHERE meta_key = "User ID" AND meta_value = "123userId";

Однако из-за устаревшего требования размер хранилища ключей и значений NVARCHAR может достигать 255 и 1000 символов соответственно. Индексация на таких больших столбцах не только дорогостоящая, но и прямо ограничена для некоторых типов БД.

Я полагаю, что в MySQL есть система, позволяющая индексировать по LEFT подстроке в стиле a следующим образом:

 CREATE INDEX ix_metadata_indexing_key_value ON metadata_indexing(meta_key, meta_value(255));

… однако наша система должна поддерживать все три MySQL, MSSQL и Oracle. Правильно ли это, и если да, то как мне создать аналогичные индексы в MSSQL и Oracle?

1. Если вам нужно решение здесь, вам, вероятно, понадобятся разные решения для каждого поставщика. Все 3 используют очень разные диалекты. Для SQL Server я бы предложил добавить PERSISTED вычисляемый столбец и проиндексировать его; затем вы можете запросить это в WHERE вместо. Хотя, учитывая, что вам нужна только LEFT сторона, a LIKE , вероятно, sargable тоже WHERE YourColumn LIKE '123userid%'; будет использовать индекс для вашего столбца YourColumn .

2. Также стоит отметить, что в стандарте ANSI SQL вообще ничего не говорится об индексации. Каждый поставщик изобретает свой собственный проприетарный синтаксис и функции для индексации. Вам нужно подумать о шаблоне адаптера , потому что нет синтаксиса, который будет работать во всех реализациях поставщиков.

3. Это звучит как преждевременная оптимизация, так и проблема XY . Считаете ли вы такой ограниченный индекс полезным?

4. «звучит как проблема x-y». Также звучит как дизайн EAV, одна из худших концепций, когда-либо навязанных миру СУБД.

5. Как говорили другие, решения, не зависящего от базы данных, не существует. Для Oracle я бы посмотрел на индексы на основе функций.

Ответ №1:

Если вы говорите о WordPress, этот индекс является только частью решения. При достижении into postmeta вам нужен составной ключ, начинающийся с. post_id Больше рекомендаций здесь .

Кроме того, если данные достаточно предсказуемы, вы могли бы заменить LONGTEXT will чем-то более цивилизованным, например VARCHAR(150) . Сделайте SELECT MAX(LENGTH(meta_key)), MAX(LENGTH(meta_value)) FROM post_meta; .

(Я тоже просматриваю EAV. И особенно плохая реализация WP.)

Ответ №2:

Как и предполагал @Larnu, в итоге мы получили новый вычисляемый meta_value_short столбец, который нужно было создавать отдельно для каждого типа БД. При запросе…

Если длина равна 255 или меньше: просто сравните с meta_value_short .

В противном случае: сначала сравните LEFT(value, 255) с meta_value_short , затем дополнительно сравните с meta_value .

Для справки определения вычисляемых столбцов приведены ниже:

MSSQL:
ALTER TABLE [metadata_indexing] ADD meta_value_short AS (SUBSTRING(meta_value, 1, 255))

MySQL:
ALTER TABLE metadata_indexing ADD meta_value_short VARCHAR(255) AS (SUBSTRING(meta_value, 1, 255));

Oracle:
ALTER TABLE metadata_indexing ADD meta_value_short AS (SUBSTR(meta_value, 1, 255));