Senior: питання на співбесіді з теми «Типи й обмеження»

JSON у реляційній базі доречний, коли дані:

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

Окрема таблиця краща, коли:

• за полями потрібно фільтрувати, сортувати, групувати, з'єднувати;
• потрібні обмеження: NOT NULL, унікальність, зовнішні ключі;
• елементи масиву - самостійні сутності, які змінюють поштучно (коментарі, позиції замовлення).

PostgreSQL: json чи jsonb. jsonb зберігається в розібраному бінарному вигляді: трохи повільніший запис, зате швидкі оператори (->, ->>, @>, ?) і підтримка GIN-індексів. json зберігає текст як є (з пробілами й порядком ключів) і майже ніколи не потрібен.

CREATE INDEX products_attrs_gin ON products USING gin (attributes);
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

MySQL: тип JSON з валідацією; для індексу за полем створюють згенеровану колонку (GENERATED ALWAYS AS (attributes->>'$.color')) або функціональний індекс (8.0.13+).

Червоні прапорці: JSON-колонку, з якої постійно витягують одне поле в WHERE, часто варто перетворити на звичайну колонку. А зберігання в JSON списку ID інших записів - це втрачений зовнішній ключ.

Докладніше в документації: Типи JSON

Collation - правила, за якими база порівнює й сортує рядки: чи «а» дорівнює «А», чи «е» дорівнює «є», де в абетці «ґ». Він впливає на ORDER BY, =, LIKE, GROUP BY і унікальні індекси.

MySQL:

• Кодування має бути utf8mb4. Старий utf8 (тепер utf8mb3) зберігає лише до 3 байтів на символ і не вміщує емодзі.
• Collation за замовчуванням у MySQL 8 - utf8mb4_0900_ai_ci: нечутливий до регістру й діакритики (ai - accent-insensitive, ci - case-insensitive). Звідси сюрпризи: WHERE email = 'IVAN@x.com' знаходить ivan@x.com, а унікальний індекс не пустить і «Ivan», і «ivan».
• Для точних порівнянь (токени, хеші) - _bin або utf8mb4_0900_as_cs.

PostgreSQL:

• Порівняння за замовчуванням чутливе до регістру. Для пошуку без урахування регістру - ILIKE, lower(email) з функціональним індексом, розширення citext або недетерміністичний ICU-collation.
• Collation бази залежить від локалі ОС (glibc). Оновлення glibc може змінити порядок сортування і зіпсувати наявні індекси за текстовими колонками - їх доводиться перебудовувати (REINDEX). Тому все частіше використовують ICU-collation з контрольованою версією.

Українська абетка: щоб «ґ» стояла після «г», а «і», «ї» - на своїх місцях, потрібен collation з українською локаллю (наприклад, ICU uk-x-icu у PostgreSQL). Якщо база такого не має, сортують у застосунку через Collator з розширення intl.

Докладніше в документації: Підтримка правил сортування

Невидимі колонки (MySQL 8.0.23+) не потрапляють у SELECT * і не потребують значення в INSERT без списку колонок. Явно названі - працюють як звичайні.

ALTER TABLE orders ADD COLUMN internal_note TEXT INVISIBLE;

SELECT * FROM orders;                 -- internal_note немає
SELECT id, internal_note FROM orders; -- є

Навіщо: додати колонку до таблиці, не зламавши старий код, що робить SELECT * чи INSERT INTO t VALUES (...) без переліку колонок. Колонку можна поступово впровадити, а потім зробити видимою.

Згенерований невидимий первинний ключ (GIPK) (MySQL 8.0.30+). Якщо увімкнено sql_generate_invisible_primary_key, таблиця, створена без первинного ключа, автоматично отримує невидиму колонку:

my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY

Чому таблиця без первинного ключа - проблема в MySQL:

• InnoDB однаково потрібен кластерний ключ. Без первинного ключа він бере перший унікальний NOT NULL індекс, а якщо такого немає - створює прихований 6-байтовий ідентифікатор. Але цей ідентифікатор спільний для всіх таких таблиць сервера, і його лічильник - точка конкуренції.
• Реплікація на основі рядків: щоб застосувати UPDATE чи DELETE на репліці, потрібно знайти рядок. Без ключа репліка шукає повним переглядом таблиці для кожного рядка - велике оновлення на primary перетворюється на години затримки реплікації.
• Group Replication і InnoDB Cluster взагалі вимагають первинного ключа на кожній таблиці.
• Багато інструментів (онлайн-зміна схеми, деякі CDC-конектори) не працюють з таблицями без ключа.

GIPK вирішує це автоматично, не змінюючи видимої структури таблиці для застосунку.

Практичний висновок: кожна таблиця має мати первинний ключ, і краще явний ($table->id()). GIPK - страховка для таблиць, створених сторонніми інструментами чи без уваги. Проміжні таблиці «багато-до-багатьох» отримують складений первинний ключ з двох зовнішніх.

Докладніше в документації: Згенеровані невидимі первинні ключі

Колонку 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: багатозначні індекси

UUID. Текстове подання - 36 символів (CHAR(36)), тобто 36 байтів на значення, а в utf8mb4-колонці ще й з накладними витратами порівняння за collation. Бінарне - 16 байтів:

CREATE TABLE documents (
    id BINARY(16) PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);

INSERT INTO documents (id, title) VALUES (UUID_TO_BIN(UUID(), 1), 'Договір');
SELECT BIN_TO_UUID(id, 1) AS id, title FROM documents;

Чому розмір первинного ключа в InnoDB важливий подвійно: кожен вторинний індекс містить копію первинного ключа. 36 байтів замість 16 (чи 8 для BIGINT) множаться на всі індекси таблиці.

Прапорець 1 (swap) у UUID_TO_BIN. MySQL-функція UUID() генерує UUID версії 1, де мітка часу розкидана по рядку так, що значення не зростають. Перестановка частин робить їх впорядкованими за часом - нові рядки дописуються в кінець кластерного індексу, а не в випадкові місця. Для UUIDv4 (повністю випадкових) прапорець не допомагає - для них краще генерувати впорядковані UUIDv7 у застосунку.

Незручність бінарних UUID: у консолі вони нечитабельні, і в кожному запиті потрібні перетворення. Laravel з HasUuids за замовчуванням використовує CHAR(36); бінарне зберігання потребує власного касту.

IP-адреси:

CREATE TABLE logins (
    ip VARBINARY(16) NOT NULL,     -- IPv4 (4 байти) і IPv6 (16 байтів)
    created_at DATETIME NOT NULL
);

INSERT INTO logins (ip, created_at) VALUES (INET6_ATON('2001:db8::1'), NOW());
SELECT INET6_NTOA(ip) FROM logins;

-- пошук у підмережі - діапазоном по бінарному значенню
SELECT * FROM logins
WHERE ip BETWEEN INET6_ATON('192.168.1.0') AND INET6_ATON('192.168.1.255');

INET6_ATON працює і з IPv4, і з IPv6. Старі INET_ATON / UNSIGNED INT - лише для IPv4.

PostgreSQL має для цього рідні типи: uuid (16 байтів, з UUIDv7 у PG18) і inet/cidr з операторами підмереж - перетворення там не потрібні.

Докладніше в документації: Інші функції: UUID_TO_BIN, INET6_ATON