Индексация и поиск по элементам массива в PostgreSQL, MongoDB и MySQL: практическое руководство

Ускорить поиск по элементам массива в PostgreSQL помогает GIN-индекс и оператор @>, в MongoDB достаточно обычного multikey-индекса с условием вида { tags: "value" }, а в MySQL 8.0.17 и новее работает multi-valued индекс по JSON-массиву вместе с JSON_CONTAINS, JSON_OVERLAPS или MEMBER OF. Планировщик обращается к индексу только тогда, когда условие записано через поддерживаемый оператор, поэтому переписанный запрос даёт больше выигрыша, чем сам факт наличия индекса.

Индекс по массиву хранит отдельную запись для каждого элемента, поэтому поиск по одному значению в таблице на 1 млн строк превращается из перебора (150-900 мс) в чтение нескольких страниц (доли миллисекунды). Цена ускорения: рост размера базы и замедление вставки. Ниже разобраны три СУБД с примерами EXPLAIN и фактическими замерами, а также случаи, где индекс не поможет и нужна другая модель данных.

Почему поиск по массивам тормозит и как индексы это исправляют

Без индекса СУБД читает каждую строку или документ, распаковывает массив и сравнивает элементы по очереди. На таблице в 1 млн строк с двумя тегами такой перебор укладывается в 200 мс на прогретом кеше. Если добавить широкие текстовые колонки, холодный кеш и десяток параллельных запросов, время вырастает до секунд, а узким местом становится дисковый ввод-вывод.

Индекс по массиву хранит отдельную запись для каждого элемента. Значение tag-7 из строки 42 попадает в индекс как самостоятельный ключ со ссылкой на эту строку. Поиск по одному значению превращается в спуск по дереву (B-tree), чтение списка ключей (GIN) или выборку по диапазону (multi-valued), а не в перебор миллиона строк.

Механика различается по СУБД. PostgreSQL требует явно создать GIN или GiST по колонке типа text[] или int[]. MongoDB строит multikey-индекс автоматически, как только видит массив в поле. MySQL не имеет типа массива: данные лежат в JSON, а индекс называется multi-valued и доступен с версии 8.0.17.

Общее правило одинаково для всех трёх систем: индекс применяется только тем оператором, который умеет с ним работать. Условие 'value' = ANY(tags) в PostgreSQL заставляет читать таблицу, а tags @> ARRAY['value'] читает GIN. Индекс не бесплатный: каждый элемент массива добавляет запись, поэтому размер индекса растёт вместе с длиной массивов, а вставка и обновление становятся дороже. Компромисс оценивают на копии базы до выкатки в продакшен.

PostgreSQL: GIN и GiST для поиска по массивам

Массивы в PostgreSQL: штатный тип text[], int[], uuid[]. Для поиска по элементам подходят два класса индексов. GIN разбирает массив на отдельные ключи и хранит их сжатым списком: поиск получается точным, обновление дорогим. GiST хранит сигнатуру элементов массива, индекс компактнее и дешевле в обновлении, но чтение может давать ложные срабатывания с последующей проверкой строки (recheck).

Выбор простой. GIN берут для полей, которые читают часто и меняют редко: теги, метки, роли, идентификаторы групп. GiST берут для колонок с частыми UPDATE или когда важен размер индекса. Для int[] расширение intarray добавляет классы операторов gist__int_ops и gin__int_ops, они работают быстрее стандартных на целочисленных массивах.

Создание GIN-индекса и переписывание запросов

Стенд: таблица items на 1 млн строк, в каждой строке массив из двух тегов. Первый тег задаёт остаток от деления на 50, второй от деления на 1000, поэтому пара tag-7 и tag-3 встречается примерно 20 раз.

CREATE TABLE items (
    id    bigserial PRIMARY KEY,
    title text NOT NULL,
    tags  text[] NOT NULL DEFAULT '{}'
);

INSERT INTO items (title, tags)
SELECT 'item-' || g,
       ARRAY['tag-' || (g % 50), 'tag-' || ((g / 50) % 1000)]
FROM generate_series(1, 1000000) AS g;

ANALYZE items;

Запрос до индекса и его план:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM items
WHERE tags @> ARRAY['tag-7','tag-3'];

Seq Scan on items  (cost=0.00..24325.00 rows=1 width=27)
                   (actual time=0.014..198.774 rows=20 loops=1)
  Filter: (tags @> '{tag-7,tag-3}'::text[])
  Rows Removed by Filter: 999980
  Buffers: shared hit=16668
Planning Time: 0.135 ms
Execution Time: 199.402 ms

Прочитан 1 000 000 строк, 999 980 отброшено фильтром, 199 мс. Индекс меняет картину:

CREATE INDEX idx_items_tags ON items USING GIN (tags);

-- на продакшене, без блокировки записи
CREATE INDEX CONCURRENTLY idx_items_tags ON items USING GIN (tags);

Bitmap Heap Scan on items  (cost=24.61..28.63 rows=20 width=27)
                           (actual time=0.271..0.372 rows=20 loops=1)
  Recheck Cond: (tags @> '{tag-7,tag-3}'::text[])
  Heap Blocks: exact=19
  Buffers: shared hit=25
  ->  Bitmap Index Scan on idx_items_tags  (cost=0.00..24.60 rows=20 width=0)
                                           (actual time=0.185..0.185 rows=28 loops=1)
        Index Cond: (tags @> '{tag-7,tag-3}'::text[])
Planning Time: 0.221 ms
Execution Time: 0.482 ms

Переход с Seq Scan на Bitmap Index Scan и Bitmap Heap Scan даёт 0.48 мс против 199 мс, ускорение примерно в 400 раз, прочитано 25 страниц вместо 16 668. Команда CREATE INDEX CONCURRENTLY идёт дольше и требует второго прохода по таблице, зато не держит монопольную блокировку на запись. Учтите параметр fastupdate: при включённом режиме новые ключи сначала копятся в pending list, который сбрасывается по достижении gin_pending_list_limit или во время VACUUM. Свежесозданный индекс с большим pending list показывает в EXPLAIN менее впечатляющие цифры, чем после первого VACUUM.

Поддерживаемые операторы: @> (массив содержит все перечисленные элементы), <@ (массив целиком входит в перечисленные), && (массивы пересекаются хотя бы одним элементом), = (совпадение массива целиком). Переписывать запросы нужно через эти операторы:

-- Seq Scan: 199 мс
SELECT id FROM items WHERE 'tag-7' = ANY (tags);

-- Bitmap Index Scan: 0.5 мс
SELECT id FROM items WHERE tags @> ARRAY['tag-7'];

-- пересечение массивов, тоже по индексу
SELECT id FROM items WHERE tags && ARRAY['tag-7','tag-9'];

Обе первые выборки возвращают одни и те же строки. Разница в плане: первая читает таблицу целиком, вторая использует индекс. Сравнение массивов целиком (tags = '{a,b,c}') обслуживает обычный B-tree: он хранит полное значение и находит его одним спуском, GIN в такой задаче проигрывает. Больше операторов, функций array_agg и unnest и разборов планов собрано в руководстве по массивам в PostgreSQL.

Когда GIN не помогает: ограничения и альтернативы

Поиск подстроки внутри элемента индекс не ускоряет. Запрос WHERE 'post' = ANY(tags) или WHERE EXISTS (SELECT 1 FROM unnest(tags) AS t WHERE t LIKE '%post%') выполняет Seq Scan даже при наличии GIN: индекс хранит значения целиком и не ищет по префиксу или вхождению.

Рабочие обходы:

• Хранить рядом текстовую колонку со склеенными тегами и триграммный GIN: CREATE EXTENSION pg_trgm; CREATE INDEX idx_items_tags_text ON items USING GIN (tags_text gin_trgm_ops). Такой индекс обслуживает LIKE '%post%' и ILIKE.
• Вынести теги в таблицу item_tags с B-tree по tag. Поиск по префиксу (LIKE 'post%') ускоряется индексом с классом операторов text_pattern_ops.
• Собрать tsvector из тегов и искать полнотекстовым запросом по GIN-индексу.

Регистр: функции lower и upper над массивом в индекс не помещаются. Нормализуйте теги при записи (CHECK (tags = lower(tags))) или заведите вторую колонку с уже приведёнными значениями и GIN по ней.

Отрицания и OR. Запросы с NOT, != или NOT (tags && ARRAY['spam']) требуют чтения всех строк, чтобы доказать отсутствие элемента, и GIN тут не помогает. Условия OR по нескольким массивам планировщик тоже сводит к перебору. Частично решает задачу частичный индекс: CREATE INDEX idx_items_articles_tags ON items USING GIN (tags) WHERE kind = 'article' работает, если отрицание всегда сопровождается фильтром kind = 'article'.

MongoDB: multikey-индексы для массивов

MongoDB не требует объявлять поле массивом. Команда db.posts.createIndex({ tags: 1 }) создаёт обычный индекс, но если хотя бы в одном документе поле tags содержит массив, индекс становится multikey: для каждого элемента появляется отдельная запись со ссылкой на документ. В плане такой индекс виден как IXSCAN.

Условие на элемент массива записывается как обычное равенство:

db.posts.find({ tags: "mongodb" })

Поиск по нескольким элементам использует $all и тоже читает индекс:

db.posts.find({ tags: { $all: ["mongodb", "index"] } })

Пример: ускорение поиска по тегам в MongoDB

Коллекция на 100 000 документов, в каждом массив tags из пяти строк, значение mongodb встречается в 20 000 документах. План до индекса:

db.posts.find({ tags: "mongodb" }).explain("executionStats")

"winningPlan": { "stage": "COLLSCAN" },
"executionStats": {
  "executionTimeMillis": 142,
  "totalKeysExamined": 0,
  "totalDocsExamined": 100000
}

После db.posts.createIndex({ tags: 1 }) тот же запрос возвращает другой план:

"winningPlan": {
  "stage": "FETCH",
  "inputStage": { "stage": "IXSCAN", "indexName": "tags_1" }
},
"executionStats": {
  "executionTimeMillis": 3,
  "totalKeysExamined": 20000,
  "totalDocsExamined": 20000
}

COLLSCAN с totalDocsExamined 100 000 и временем 142 мс сменяется на IXSCAN по tags_1 с временем 3 мс, ускорение примерно в 45 раз. Смотреть стоит на totalDocsExamined: чем реже значение в массиве, тем сильнее падает число прочитанных документов. Замеры времени без explain дают разброс из-за кеша и параллельной нагрузки, поэтому решения принимают по explain("executionStats"). Методика разбора планов и готовые команды собраны в статье об оптимизации производительности MongoDB, а устройство операторов $elemMatch, $push, $addToSet и поведение multikey-индексов разобрано в материале про массивы в MongoDB.

Ограничения multikey-индексов и составные индексы

Сортировку по полю-массиву индекс не обслуживает. find(...).sort({ tags: 1 }) всегда выполняет сортировку в памяти, потому что у одного документа несколько значений ключа, поэтому сортируют по скалярному полю. Два массива в одном составном индексе запрещены: createIndex({ tags: 1, authors: 1 }) вернёт ошибку о параллельных массивах, если оба поля содержат массивы.

$elemMatch ограничивает индексом только одно условие внутри элемента. Для запроса specs: { $elemMatch: { name: "cpu", value: "x86" } } индекс найдёт документы по name, а value проверится уже на этапе FETCH. Покрывающий запрос по массиву невозможен: в индексе лежат отдельные значения, а не исходный массив, поэтому документ всё равно придётся прочитать.

Составной индекс с одним массивом работает: db.posts.createIndex({ status: 1, tags: 1 }) сначала сужает выборку по status, затем ищет по элементу tags. Плата за multikey заметна на записи: документ с массивом из 50 значений создаёт 50 записей в индексе. Размер оценивают заранее на копии базы через db.posts.stats().indexSizes.

MySQL: функциональные индексы для JSON-массивов

Типа массива в MySQL нет, поэтому массив обычно лежит в колонке JSON, а поиск идёт через JSON_CONTAINS, JSON_OVERLAPS или MEMBER OF. До версии 8.0.17 такие запросы читали таблицу целиком. С 8.0.17 появились multi-valued indexes: один ключ индекса разворачивается в набор значений из JSON-массива.

Создание функционального индекса по JSON-массиву

Путь с $[*] оборачивают в CAST ... AS ... ARRAY, поэтому индекс создают через ALTER TABLE:

ALTER TABLE items
  ADD INDEX idx_tags ( (CAST(tags_json->'$[*]' AS CHAR(32) ARRAY)) );

Индекс строится по каждому скалярному элементу массива. Запросы, которые его используют:

SELECT id, title FROM items
WHERE JSON_CONTAINS(tags_json, CAST('"mysql"' AS JSON));

SELECT id, title FROM items
WHERE 'mysql' MEMBER OF (tags_json->'$[*]');

EXPLAIN до индекса показывает type=ALL и rows=1000000, после индекса type=ref, key=idx_tags, rows около 20. На стенде с 1 млн строк запрос ускоряется с 410 мс до 0.8 мс. Ограничения, о которых стоит помнить:

• Нужна версия 8.0.17 или новее. На 5.7 и на 8.0.16 multi-valued индекс создать нельзя, остаётся таблица-связка.
• Индекс работает с JSON_CONTAINS, JSON_OVERLAPS и MEMBER OF. Условия вида JSON_EXTRACT(tags_json, '$') LIKE '%mysql%' и функция JSON_SEARCH индекс не обслуживают.
• В одном индексе допустим только один multi-valued ключ, два массива совместить нельзя.
• Выражение должно быть детерминированным и не зависеть от времени, идентификатора соединения и текущих настроек сессии.

Детали хранения массивов в JSON, сравнение с TEXT и генеративными колонками и порядок миграции разобраны в статье про хранение массивов в MySQL.

Когда лучше сменить модель данных

Если массив участвует в основных выборках, а не в редких отчётах, таблица-связка даёт предсказуемый план и убирает JSON-функции из горячих запросов.

CREATE TABLE item_tags (
  item_id BIGINT UNSIGNED NOT NULL,
  tag     VARCHAR(64) NOT NULL,
  PRIMARY KEY (item_id, tag),
  KEY idx_tag (tag),
  CONSTRAINT fk_item FOREIGN KEY (item_id) REFERENCES items (id) ON DELETE CASCADE
);

SELECT i.id, i.title
FROM items AS i
JOIN item_tags AS t ON t.item_id = i.id
WHERE t.tag = 'mysql';

Соединение двух проиндексированных таблиц на 1 млн товаров и 5 млн связей выполняется за доли миллисекунды, планировщик получает точную статистику по частоте тегов, а индекс остаётся обычным B-tree. Ограничение тоже есть: для трёх и более тегов в одном запросе приходится добавлять EXISTS для каждого тега или группировать через GROUP BY с HAVING COUNT(DISTINCT tag) = 3. Подробное сравнение схем и критерии выбора собраны в материале про нормализацию и денормализацию массивов.

Сравнение подходов: PostgreSQL vs MongoDB vs MySQL

Выбор под задачу. Сложные выборки с пересечением массивов и полный контроль над планами: PostgreSQL. Гибкая схема, где набор полей у документов различается, и быстрый старт без миграций: MongoDB. Простые теги в существующем MySQL-проекте: таблица-связка, а multi-valued индекс по JSON уместен, когда менять схему нельзя и версия сервера не ниже 8.0.17.

Случаи, когда поиск по массиву не индексируется

Пять ситуаций, в которых индекс по массиву бесполезен или почти бесполезен.

1. Поиск подстроки внутри элемента. В PostgreSQL 'post' = ANY(tags), в MySQL JSON_EXTRACT(tags,'$') LIKE '%post%', в MongoDB $regex без анкорного префикса. Решения: триграммный GIN по текстовой колонке, таблица-связка с B-tree для префиксного поиска, полнотекстовый индекс tsvector или внешний поисковый движок.
2. Отрицание: NOT @>, $ne, $nin, !=. Индекс не доказывает отсутствие элемента без чтения всех строк. Помогает инвертирование логики, частичный индекс или проверка на стороне приложения.
3. Проверка длины массива. $size в MongoDB и функция json_length в MySQL по индексу элементов не работают: индекс хранит значения, а не их количество. Для регулярных проверок длины держат отдельное числовое поле size, которое обновляют вместе с массивом.
4. OR по нескольким массивам или полям. Планировщик либо строит BitmapOr из двух индексов (работает не всегда), либо сваливается в Seq Scan. Помогает разбиение запроса на два с UNION, если логика это допускает.
5. $elemMatch с несколькими условиями внутри одного элемента. Индекс ограничивает только первое поле, остальные проверяются после выборки документа. Для составных условий внутри элемента помогает отдельная коллекция с нормализованными полями.

Когда перечисленные обходы не дают нужной скорости, массив выносят в отдельную таблицу или коллекцию, а поиск уводят в специализированный движок (Elasticsearch, OpenSearch, Sphinx). Внешний поиск снимает с основной базы задачи фильтрации по десяткам значений и заодно даёт морфологию и опечатки, которых нет в B-tree и GIN.

Практический чек-лист для проверки индексов в рабочей среде

Порядок действий, который снижает риск сломать продакшен:

1. Найти медленные запросы. PostgreSQL: pg_stat_statements и auto_explain, плюс log_min_duration_statement. MongoDB: db.setProfilingLevel(1, { slowms: 50 }) и коллекция system.profile. MySQL: slow_query_log и таблица performance_schema.events_statements_summary_by_digest. Если логов много, первичный разбор удобно отдать языковой модели: агрегатор AiTunnel даёт доступ к GPT, Gemini и Claude через один API с оплатой в рублях, но решения всё равно принимают по EXPLAIN.
2. Снять план. PostgreSQL: EXPLAIN (ANALYZE, BUFFERS). MongoDB: explain("executionStats") и разбор totalDocsExamined. MySQL: EXPLAIN ANALYZE, доступный с 8.0.18. Смотреть на тип сканирования, число прочитанных строк и страниц.
3. Проверить, что условие записано через поддерживаемый оператор: @>, <@, &&, $all, JSON_CONTAINS, MEMBER OF. Если в запросе ANY, unnest или LIKE по JSON, индекс не подключится.
4. Создать индекс на копии базы и повторить замер. Тестовый стенд удобно поднять в облаке: сервер или управляемая база разворачивается за минуты и не требует отдельного железа, например Timeweb Cloud.
5. Сравнить время до и после, а также число прочитанных строк. Ориентир: при редком значении в массиве Seq Scan сменяется на Index Scan или IXSCAN.
6. Оценить стоимость записи. Замерить время INSERT и UPDATE на том же стенде, посмотреть размер индекса (pg_relation_size для PostgreSQL, db.posts.stats().indexSizes для MongoDB, information_schema.TABLES для MySQL).
7. Выкатить в часы низкой нагрузки. В PostgreSQL использовать CREATE INDEX CONCURRENTLY, в MySQL проверить, что операция идёт в режиме online DDL (ALGORITHM=INPLACE). В MongoDB начиная с 4.2 сборка индекса блокирует коллекцию только на короткие интервалы в начале и конце, отдельный флаг background не нужен и игнорируется.
8. Поставить мониторинг на попадания в индекс: pg_stat_user_indexes.idx_scan, db.posts.aggregate([{ $indexStats: {} }]) для MongoDB, sys.schema_index_statistics для MySQL. Индекс с нулевым числом обращений спустя две недели нормальной нагрузки удаляют: он занимает место и замедляет запись. Статистику тоже обновляют: в PostgreSQL после массовой загрузки данных выполняют ANALYZE, иначе планировщик может выбрать Seq Scan даже при наличии индекса.

Начните с одного медленного запроса из pg_stat_statements или system.profile, снимите план, приведите условие к поддерживаемому оператору и создайте индекс на копии. Решение о выкатке принимайте по трём цифрам: время до, время после и число прочитанных строк.