Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Як індексувати поля JSON у MySQL, зокрема масиви?

Колонку JSON у MySQL неможливо проіндексувати напряму. Індексують значення, витягнуті з документа.

1. Скалярне поле - функціональний індекс чи згенерована колонка:

-- функціональний індекс (MySQL 8.0.13+)
CREATE INDEX products_brand_idx ON products ((CAST(attributes ->> '$.brand' AS CHAR(100)) COLLATE utf8mb4_bin));

-- або віртуальна колонка з індексом - простіше в запитах
ALTER TABLE products
    ADD COLUMN brand VARCHAR(100) AS (attributes ->> '$.brand') VIRTUAL,
    ADD INDEX (brand);

->> повертає текст з типом LONGTEXT, тож у функціональному індексі потрібне явне CAST до типу з обмеженою довжиною. А вираз у запиті має дослівно збігатися з виразом індексу - тому віртуальна колонка надійніша: у запиті пишуть просто WHERE brand = ?.

2. Масив - багатозначний індекс (multi-valued index, MySQL 8.0.17+). Один рядок дає кілька записів в індексі - по одному на кожен елемент масиву:

-- products.attributes = {"tags": ["laravel", "php", "api"]}
CREATE INDEX products_tags_idx ON products ((CAST(attributes -> '$.tags' AS CHAR(50) ARRAY)));

SELECT * FROM products WHERE 'php' MEMBER OF (attributes -> '$.tags');
SELECT * FROM products WHERE JSON_CONTAINS(attributes -> '$.tags', '["php", "api"]');
SELECT * FROM products WHERE JSON_OVERLAPS(attributes -> '$.tags', '["vue", "react"]');

Індекс використовується лише цими трьома конструкціями: MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS.

Обмеження багатозначних індексів:

  • один багатозначний компонент на індекс;
  • лише для простих масивів скалярних значень;
  • не підтримуються ORDER BY за індексом і деякі типи;
  • оновлення масиву оновлює всі його записи в індексі.

Порівняння з PostgreSQL: там GIN-індекс на весь jsonb прискорює пошук за будь-якими ключами без попереднього вибору полів. У MySQL треба заздалегідь знати, за якими полями шукатимуть.

Сигнал до зміни схеми: якщо за полем JSON постійно фільтрують, сортують і з'єднують, - йому краще бути звичайною колонкою (а масиву - таблицею зв'язку).

Докладніше в документації: CREATE INDEX: багатозначні індекси

1

Перевір себе

20 випадкових питань за спробу, після завершення - розбір кожної помилки

Схожі питання