MySQL проти Elasticsearch для пошуку в e‑commerce: чому SQL ламається під фільтрами

Чому пошук на SQL ламається під фільтрами

Пошук у e‑commerce — це не один запит. Це розмова між людиною та вашим каталогом:
ввів текст, наклав обмеження, знову обмежив, відсортував, перейшов на сторінку й хочеш, щоб інтерфейс оновився
менше ніж за кілька сотень мілісекунд, показавши ще й кількість по фасетах. Кожен клік додає обмеження,
і кожне обмеження множить способи, якими база даних може це зробити неправильно.

Модель відмови: комбінаторний біль, а не один повільний запит

У MySQL типова схема така:

• Один запит для отримання результатів (часто з JOIN для ціни, інвентарю, категорії, атрибутів).
• Кілька запитів для обчислення фасетів (кількість по бренду, розміру, кольору тощо).
• Ще один запит для «мабуть, мали на увазі» або синонімів чи хитрощів рейтингу.

Якщо робити все це в реальному часі проти нормалізованих таблиць, ви просите транзакційну базу
поводитись як пошукова система. Вона постарається. Але також розігріє ваші CPU до червоного.

Що роблять «фільтри» з SQL: вони руйнують припущення про селективність

B-tree індекси (типові для InnoDB в MySQL) чудові для звуження набору даних, коли предикати селективні й
узгоджені з префіксом індексу. Фільтри в фасетованому пошуку не підлаштовуються ввічливо.
Вони виглядають як:

• WHERE category IN (...) (кілька значень, часто не селективні на верхньому рівні)
• AND brand_id IN (...) (мульти-вибір, керований користувачем)
• AND price BETWEEN ... (предикати діапазону руйнують композитні індекси)
• AND (title LIKE '%term%' OR description LIKE '%term%') (ведучий підстановник = індекс декоративний)
• AND attr_id=... AND attr_value IN (...) повторювано N разів (EAV-експлозія JOIN)
• ORDER BY popularity DESC з глибокою пагінацією (filesort + сканування)

Оптимізатор MySQL має обрати план. Для складних предикатів він вгадує, базуючись на статистиці,
яка може бути застарілою, грубою або хибною для даних зі збоями (а продуктові каталоги саме такі).
Коли він помиляється, результат — не «на 10% повільніше». Це «тайм-аути й наради».

Пастка «кількостей по фасетах»: ви запускаєте аналітику на OLTP

Кількості по фасетах — це по суті агрегації GROUP BY на відфільтрованому наборі. Це аналітична робота.
У SQL ви це реалізуєте через GROUP BY по joined таблицях, і ви або:

• Запускаєте велику агрегацію для кожної фасети (N запитів на запит), або
• Намагаєтеся зробити мегазапит з умовними агрегатами й плачете, коли він стає немислимим.

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

Суха операційна істина: MySQL передбачуваний; фільтри користувачів — ні

MySQL блискуче працює, коли ви можете передбачити шаблони доступу й індексувати під них. Фасетований пошук — це
антагоністична задача: користувачі фактично генерують ad‑hoc запити з дивними поєднаннями. MySQL може впоратися з частиною,
але межа продуктивності швидко руйнується, коли ви додаєте:

• кілька JOIN для атрибутів
• фільтри діапазону + сортування
• глибоку пагінацію
• фасетні агрегації
• мультимовну стемінг/синоніми/тонке налаштування релевантності

Жарт №1: Запуск фасетованого пошуку на повністю нормалізованій схемі — це як тягнути човен на велосипеді: можливo,
але всі навколо хвилюються.

Як MySQL фактично виконує ваш «простий» пошук

Щоб зрозуміти, чому пошук на SQL ламається, потрібно засвоїти, для чого MySQL оптимізований:
точкові звернення та вузькі сканування діапазону індексу з транзакційною консистентністю, у row‑store з B‑tree індексами.
Пошукові движки будують інвертовані індекси, колонкоподібні doc values, кешовані бітсети фільтрів і модель скорингу.
Інша фізика.

B-tree індекси: чудові в «знайти такі рядки», посередні в «обчислити релевантність»

B‑tree індекс блискучий, коли предикат швидко зменшує множину кандидатів. Він страждає, коли:

• Потрібно знайти токени в тексті без попередньої токенізації (LIKE '%term%').
• Потрібно ранжувати за релевантністю (вимагає скорингу, частоти термінів, підсилення полів).
• Потрібна логіка з багатьма OR, що розширює скан (brand IN (...) OR category IN (...)).
• Потрібні підрахунки по багатьох вимірах (фасети).

FULLTEXT в MySQL допомагає, але не вирішує фасетування

FULLTEXT індекси MySQL дають можливість покрокового збігу токенів для певних навантажень,
і в InnoDB вони реальні й корисні. Але e‑commerce пошук хоче більше:

• Префікс, нечіткість, толерантність до опечаток
• Синоніми і «мабуть, ви мали на увазі»
• Налаштування релевантності по полях (title vs description)
• Структуровані фільтри, що мають бути швидкими
• Кількості по фасетах і «покажи, скільки елементів буде, якщо я натисну це»

Навіть якщо FULLTEXT правильно знаходить текст, робота з JOIN і фільтрами після цього може залишитись вузьким місцем.
Ви отримуєте двоетапний запит: текстовий матч дає ID, потім ви JOIN фільтри, потім сортуєте. Кожен етап має свої
скелясті місця продуктивності.

Схеми EAV: мовчазний вбивця

Системи атрибутів часто реалізують як EAV (entity‑attribute‑value): у продукту багато атрибутів,
кожен зберігається як рядок. Це гнучко, але жахливо для фільтрів пошуку. Запит «size=10 and color=black»
перетворюється на кілька self‑join або підзапитів до таблиці атрибутів. На великому каталозі це — експлозія JOIN
з низькою селективністю предикатів. Оптимізатор не завжди може вигідно перегрупувати JOIN, і навіть коли може,
проміжні результати швидко зростають.

Сортування та пагінація: «ORDER BY popularity» — це не безкоштовно

Сортування — друге вузьке місце, яке проявляється після того, як «виправили» фільтрацію. Якщо ваш запит вибирає багато кандидатів
й потім сортує за полем, яке не покривається індексом, MySQL може робити filesort. Це не обов’язково означає «сортування на диску»
кожного разу, але може висипатися у spill і спалювати CPU і пам’ять.

Глибока пагінація (LIMIT 20 OFFSET 2000) особливо підла: MySQL все одно мусить знайти і відкинути перші
2000 рядків після застосування фільтрів і сортування. Це марна робота на запит, і вона масштабується лінійно з офсетом.

Стабільність плану запитів: у production «іноді» стає «завжди»

Найбільший операційний головний біль — не в одному повільному запиті. Це те, що одна й та сама форма запиту стає повільною в залежності від:

• яку категорію вибрали
• які бренди вибрані
• часу доби (буфер‑пул теплий чи холодний)
• скосу даних (один бренд має 40% продуктів)
• свіжості (нові продукти не в статистиці)

Ви бачите стрибок p95‑латентності, потім звинувачуєте мережу, потім застосунок, потім виявляєте, що один користувач
натиснув фільтр «Всі кольори», реалізований як IN (...hundreds of ids...). Це завжди непомітна функція UI.

Одна цитата, що набула болісної надійності в операціях: «Надія — не стратегія.» — Gene Kranz.

Як Elasticsearch робить фільтри дешевими (і чому це не магія)

Elasticsearch побудований на Lucene, який створений для пошуку. Це звучить очікувано, але має практичні наслідки:
структура індексу — інвертований індекс для тексту плюс колонкоподібні doc values для агрегацій і сортування. Фільтри
стають операціями над бітсетами, а не головоломками JOIN.

Інвертований індекс: основна перевага

В інвертованому індексі кожен термін вказує на список документів, що його містять. Перетинаєте списки розміщень,
застосовуєте фільтри як попередньо обчислені набори, скорите — і готово. Суть у тому, що робота масштабується залежно
від кількості збігів, а не кількості рядків, що скануються в JOIN‑ах.

Фільтри кешуються й компонуются

У Elasticsearch фільтр (наприклад, brand:Acme) часто перетворюється на bitset: бітову маску документів, що підходять.
Комбінувати фільтри через AND/OR швидко. Багато фільтрів можна ефективно кешувати, бо вони повторюються між запитами,
особливо загальні фасети як «в наявності» чи «безкоштовна доставка».

Кількості по фасетах (агрегації) — це першокласні громадяни. Elasticsearch може обчислювати підрахунки за брендом, розміром, кольором тощо,
використовуючи doc values і оптимізації на рівні сегментів. Ви не пишете N різних GROUP BY запитів і не молитесь,
щоб оптимізатор не розцінив вашу схему як особистий виклик.

Денормалізація — плата за продуктивність

Elasticsearch працює найкраще, коли кожен товар — це один документ, що містить поля, за якими фільтрують і сортують.
Це означає денормалізацію: включити назву бренду, шлях категорії, атрибути, стан наявності, можливо обчислений ранг.
OLTP‑інженери часто проти денормалізації з принципу. Пошук змушує це робити все одно, але в окремій системі.

Операційна реальність: Elasticsearch дає продуктивність, а не простоту

Elasticsearch не безкоштовний:

• Пайплайни індексації можуть падати. Перезаповнення індексу може займати години.
• Помилки в mapping часто незворотні (або принаймні поки не зробиш reindex).
• Розмір кластера — це інженерна робота, а не vibes.
• Консистентність за замовчуванням — eventual; треба проектувати під це.
• Ви тепер експлуатуєте дві системи: MySQL — джерело істини, Elasticsearch — для пошуку.

Але якщо ваш продукт потребує швидкого фасетування і релевантності, Elasticsearch — це правильний вид складності.
Він відповідає формі проблеми.

Жарт №2: Elasticsearch — це друг, який допомагає швидко рухатися, а потім просить допомогти йому переїхати кластер наступного вікенду.

Цікаві факти та історичний контекст (те, що пояснює сучасні компроміси)

1. Lucene з’явився майже за десятиліття до Elasticsearch. Lucene стартував у кінці 1990‑х; Elasticsearch прийшов пізніше як розподілена обгортка з API.
2. InnoDB став движком за замовчуванням у MySQL 5.5. Це змінило багато: транзакції, відновлення після падіння і поведінка buffer pool формують уявлення про «швидко».
3. FULLTEXT в InnoDB з’явився після того, як MyISAM мав його роками. Функція є, але екосистема аналізаторів і скорингу в Lucene глибша.
4. Ранні e‑commerce «пошуки» були часто переглядом категорій плюс LIKE. Ця спадщина досі переслідує схеми: EAV‑таблиці, таблиці ключових слів і вручну підтримувані колонки релевантності.
5. Фасетна навігація стала мейнстрімом для великих каталогів у 2000‑х. Коли «фільтрувати за розміром/кольором/брендом» стало стандартом, OLTP‑БД почали зловживати.
6. Elasticsearch популяризував «schema on write» для пошуку. Ваші рішення мапінгу під час індексації сильно впливають на продуктивність і коректність під час запитів.
7. Колонкоподібні doc values були переломним моментом для агрегацій. Без doc values підрахунки по фасетах на масштабах були б набагато дорожчими.
8. Розподілені пошукові кластери зробили релевантність виробничою проблемою. Ви не просто робите запит; ви налаштовуєте аналізатори, синоніми, стемінг і скоринг по шардах.

Три короткі корпоративні історії з практики

1) Інцидент через неправильне припущення: «воно проіндексовано, отже швидко»

Один середньорозмірний ритейлер мав endpoint пошуку на MySQL, що «працював у стенді». У продакшені було інше.
Endpoint приєднував продукти до інвентарю, цін, категорій і атрибутів, потім сортував за популярністю. Вони
додали індекси скрізь. Хтось навіть мав таблицю в Excel.

Трафік різко виріс під час сезонної кампанії, і p95‑латентність пошуку підскочила від «прийнятно» до «можна оновити сторінку й все ще чекати».
CPU бази був високий, але не завантажений повністю. Справжній біль був у I/O і деревах buffer pool:
запит торкався занадто багатьох сторінок, надто непередбачувано.

Неправильне припущення було тонким: вони вірили, що оскільки кожна таблиця мала індекс на ключах JOIN, JOIN
залишатиметься дешевим під фільтрами. Але фільтри були на атрибутах у EAV‑рядках, тому порядок JOIN змінювався
з різними фільтрами. Оптимізатор обирав план, що виглядав добре для загальних фільтрів і катастрофічно для певної категорії
з великою різноманітністю атрибутів.

Інцидентний захист не був гламурним. Вони тимчасово відключили мульти‑вибір фасетів для тієї категорії і примусили
звузити набір фільтрів. Латентність одразу відновилася. Корінне рішення — перенести фасетування і ранжування в Elasticsearch,
а MySQL використовувати лише як каталог істини.

Примітка постмортему, що має значення: «Наявність індексу — не значить, що індекс корисний». Якщо предикат не селективний,
індекс — це порада, а не рішення.

2) Оптимізація, що відбилася бумерангом: кешування SQL‑результатів

Інша компанія зробила класичний крок: «Закешуємо відповіді пошуку в Redis». Хешували параметри запиту і кешували весь JSON на 10 хвилин.
На папері це зменшило навантаження. На практиці це створило нові режими відмов.

Каталог змінювався часто: ціни оновлювалися, інвентар коливався, промоції вмикалися й вимикалися. Кеш повертав
застарілі результати, що порушували бізнес‑правила (товари вийшли з наявності, знижки відсутні). Підтримка клієнтів помітила.
Маркетинг помітив. Фінансовий директор помітив.

Вони звузили TTL. Навантаження повернулося. Додали хуки інвалідизації з pipeline каталогу. Це стало складною,
крихкою подією‑орієнтованою системою, і вони все одно не могли інвалідовувати всі комбінації фільтрів.
Комбінаторика їх перемогла: неможливо ефективно інвалідовувати «усі запити, що включають бренд X і будь‑який колір і ціну між A та B».

Остаточне рішення було нудним: припинити кешувати повні відповіді і натомість кешувати стабільні підрезультати (наприклад, битсети фільтрів)
в пошуковому інструменті, де ці примітиви призначені для цього. Вони впровадили Elasticsearch для виконання запитів і використовували MySQL для записів і істини.
Кешування відповідей лишилося, але тільки для верхніх сторінок категорій з передбачуваними параметрами.

3) Банально правильна практика, що врятувала день: dual‑write і відтворюваний індекс

Велика організація зробила непопулярне: вони поставили індексування пошуку як виробничий pipeline.
Оновлення товарів надходили в надійну чергу. Один consumer оновлював Elasticsearch. Інший consumer оновлював MySQL.
Кожна подія була відтворювана з сховища.

Одного дня вкралася помилка мапінгу: числове поле проіндексували як текст. Кількості по фасетах почали давати дивні значення, сортування зламалось,
і хтось намагався «гаряче» виправити мапінг. Elasticsearch ввічливо відмовився, бо мапінги не такі гнучкі.
Команда не панікувала. Саме для цього потрібен нудний процес.

Вони створили новий індекс з правильною мапою, відтворили події з надійного логу, валідували підрахунки з MySQL
і перемкнули alias. Простілих простоїв не було; «радіус ураження» обмежився тимчасовими аномаліями релевантності під час реіндексування.

Нічого тут не було химерного. Це була дисципліна: незмінний журнал подій, ідемпотентне індексування, cutover через alias та дашборди,
що порівнюють документи індексу з даними джерела істини. Коли пошук — це продуктова функція, так виглядає дорослість.

Швидкий план діагностики

Коли пошук сповільнюється, ваше завдання — не сперечатися, яка система «краща». Ваше завдання — знайти вузьке місце за хвилини,
а не дні, і прийняти безпечне рішення під тиском.

По‑перше: класифікуйте джерело повільності (аплікація vs база vs пошуковий кластер)

• Перевірте p95/p99 на краю: весь endpoint повільний чи лише певні форми запитів?
• Порівняйте «без фільтрів» vs «багато фільтрів»: якщо фільтри викликають сверхлінійний стрибок — це план/агрегації.
• Перевірте error budget: тайм‑аути, 5xx, спрацьовування circuit breaker.

По‑друге: відокремте час запиту від роботи зі свіжістю даних

• MySQL: повільно через скани/сорти, чи ви блокуєтеся (lock‑и)?
• Elasticsearch: повільно через важкі агрегації, чи кластер зайнятий мерджами сегментів / GC / прив’язкою до диска?

По‑третє: шукати класичні обриви

• MySQL‑обриви: filesort, тимчасові таблиці, «rows examined» експлозія, EAV‑JOIN, глибокий OFFSET, застаріла статистика.
• Elasticsearch‑обриви: агрегації високої кардинальності, занадто багато шардів, fielddata‑вибух, повільні диски, тиск heap, конфлікт refresh/merge.

По‑четверте: застосуйте найменш ризиковану міру

• Тимчасово відключіть найгірший фасет або опцію сортування.
• Обмежте OFFSET (або вимагайте «search after»).
• Зменшіть розмір агрегацій.
• Поверніться до keyword‑пошуку без підрахунків.

По‑п’яте: вирішіть архітектуру, а не героїчні тимчасові рішення

Якщо ваш продукт потребує багатого фільтрування + підрахунків фасетів + релевантності, перенесіть навантаження запитів до пошукового движка.
Якщо ваш продукт — «адмінський пошук» з декількома фільтрами, MySQL підходить — але робіть це свідомо.

Практичні завдання з командами, виводами й рішеннями

Це завдання, які я запускаю в продакшені, коли хтось каже «пошук повільний». Кожне завдання містить:
виконувану команду, реалістичний приклад виводу, що це означає, і яке рішення прийняти.

Завдання 1: Визначити повільні MySQL‑запити (за загальним часом)

cr0x@server:~$ sudo pt-query-digest /var/log/mysql/mysql-slow.log | head -n 25
# 120s user time, 2s system time, 33.12M rss, 101.02M vsz
# Current date: Mon Dec 30 12:10:07 2025
# Hostname: db1
# Files: /var/log/mysql/mysql-slow.log
# Overall: 1.2k total, 37 unique, 0 QPS, 0x concurrency ____________
# Time range: 2025-12-30T10:00:00 to 2025-12-30T12:00:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          3500s   150ms     12s      3s      9s     2s   1.5s
# Rows examine        3.1G       0    6.0M   2.6M   5.9M   1.1M   2.1M
# Query 1: 45% 0.12 QPS, 0.40x concurrency, ID 0xA1B2C3D4 at byte 12345
# Scores: V/M = 0.98
# Time range: 2025-12-30T10:10:00 to 2025-12-30T11:55:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         22     270
# Exec time     45   1580s   800ms     12s      6s     10s     2s      5s
# Rows examine  62   1.9G   400k    6.0M   7.1M   5.9M   1.1M   6.8M

Що це означає: Ваш «запит пошуку» не просто повільний; він сканує мільйони рядків на запит і домінує за часом.

Рішення: Витягніть цю форму запиту, запустіть EXPLAIN ANALYZE і або переробіть індекси/схему, або перенесіть навантаження з MySQL.

Завдання 2: Знайти поточні виконувані MySQL‑запити та їхній час

cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST\G" | sed -n '1,35p'
*************************** 1. row ***************************
     Id: 91234
   User: app
   Host: 10.0.2.15:53912
     db: catalog
Command: Query
   Time: 18
  State: Sending data
   Info: SELECT p.id, p.title FROM products p
         JOIN product_attr pa ON pa.product_id=p.id
         WHERE p.category_id IN (12,13) AND pa.attr_id=7 AND pa.value IN ('black','navy')
         ORDER BY p.popularity DESC LIMIT 20 OFFSET 2000

Що це означає: «Sending data» протягом 18 секунд часто означає скан+join+сортування, а не мережевий I/O.

Рішення: Пом’якшити глибоку пагінацію і перевірити, чи селективний JOIN, щоб використовувати індекси.

Завдання 3: Проінспектувати план запиту з runtime‑метриками (MySQL 8)

cr0x@server:~$ mysql -e "EXPLAIN ANALYZE SELECT p.id FROM products p JOIN product_attr pa ON pa.product_id=p.id WHERE p.category_id IN (12,13) AND pa.attr_id=7 AND pa.value IN ('black','navy') ORDER BY p.popularity DESC LIMIT 20 OFFSET 2000;"
+----------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------+
| -> Limit/Offset: 20/2000  (cost=84512.23 rows=2020) (actual time=15123.412..15123.900 rows=20 loops=1)
    -> Sort: p.popularity DESC  (cost=84512.23 rows=40123) (actual time=15123.410..15123.650 rows=2020 loops=1)
        -> Nested loop inner join  (cost=40211.11 rows=40123) (actual time=112.310..15012.000 rows=420000 loops=1)
            -> Index range scan on p using idx_category (category_id in (12,13))  (cost=1123.00 rows=70000) (actual time=2.100..50.000 rows=68000 loops=1)
            -> Index lookup on pa using idx_attr (attr_id=7, value in ('black','navy')) (cost=0.50 rows=6) (actual time=0.200..0.210 rows=6 loops=68000) |
+----------------------------------------------------------------------------------------------------------------------------------+

Що це означає: 68k зовнішніх рядків викликають 68k повторних пошуків; проміжні результати експоненційно ростуть; сортування 420k кандидатів, щоб відкинути 2000 — марна робота.

Рішення: Уникати OFFSET‑пагінації, попередньо обчислити/денормалізувати атрибути для популярних фільтрів або перенести фільтрацію/сортування в Elasticsearch.

Завдання 4: Перевірити визначення індексів і корисність префіксів

cr0x@server:~$ mysql -e "SHOW INDEX FROM product_attr\G" | sed -n '1,35p'
*************************** 1. row ***************************
        Table: product_attr
   Non_unique: 1
     Key_name: idx_attr
 Seq_in_index: 1
  Column_name: attr_id
    Collation: A
  Cardinality: 32
*************************** 2. row ***************************
        Table: product_attr
   Non_unique: 1
     Key_name: idx_attr
 Seq_in_index: 2
  Column_name: value
    Collation: A
  Cardinality: 2100
*************************** 3. row ***************************
        Table: product_attr
   Non_unique: 1
     Key_name: idx_attr
 Seq_in_index: 3
  Column_name: product_id
    Collation: A
  Cardinality: 800000

Що це означає: Низька кардинальність для attr_id вказує на багато рядків на атрибут; індекс менш корисний, ніж здається під широкими категоріями.

Рішення: Розглянути композитні індекси під найпоширеніші запити або переробити атрибути (сплющити гарячі атрибути в колонки або денормалізовану таблицю).

Завдання 5: Перевірити, чи MySQL не викидає на диск через тимчасові таблиці

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 184203   |
| Created_tmp_files       | 9132     |
| Created_tmp_tables      | 512004   |
+-------------------------+----------+

Що це означає: Високе співвідношення дискових тимчасових таблиць вказує на сорти/GROUP BY, що не вміщуються в пам’ять або не можуть використати індекси.

Рішення: Зменшити складність GROUP BY, забезпечити індекси для групувань або припинити робити підрахунки фасетів у MySQL.

Завдання 6: Підтвердити тиск на buffer pool (InnoDB)

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_requests      | 982341223 |
| Innodb_buffer_pool_reads              | 18233411  |
+---------------------------------------+-----------+

Що це означає: Buffer pool reads (фізичні) значні. Запити пошуку перемішують сторінки і пропускають кеш.

Рішення: Або виділіть більше пам’яті (тимчасове полегшення), або зменшіть робочий набір, переносячи пошук з MySQL / денормалізуючи / іншим кешуванням.

Завдання 7: Перевірити очікування блокувань у MySQL, що впливають на пошук

cr0x@server:~$ mysql -e "SELECT * FROM performance_schema.data_lock_waits\G" | sed -n '1,40p'
*************************** 1. row ***************************
REQUESTING_ENGINE_LOCK_ID: 139941888:106:4:5:12345
REQUESTING_ENGINE_TRANSACTION_ID: 812345
BLOCKING_ENGINE_LOCK_ID: 139941888:106:4:5:12345
BLOCKING_ENGINE_TRANSACTION_ID: 812100
REQUESTING_THREAD_ID: 2221
BLOCKING_THREAD_ID: 2199

Що це означає: Латентність пошуку може бути спричинена контенцією блокувань, часто через записи або довгі транзакції, що блокують читання (або metadata‑lock).

Рішення: Виправити шлях запису, скоротити транзакції та відокремити read‑репліки для пошуку, якщо ви залишаєте SQL‑пошук.

Завдання 8: Виміряти латентність пошуку в Elasticsearch і стан шардів

cr0x@server:~$ curl -s http://localhost:9200/_cat/health?v
epoch      timestamp cluster status node.total node.data shards pri relo init unassign pending_tasks max_task_wait_time active_shards_percent
1735560300 12:05:00  es-prod yellow          6         6   420 210    0    0       12             0                  -                 97.1%

Що це означає: Yellow із неназначеними шардами може додавати латентність і ризик, особливо якщо репліки відсутні і вузли під тиском.

Рішення: Відновити розподіл реплік, виправити disk watermarks або зменшити кількість шардів перед тим, як ганятися за налаштуванням запитів.

Завдання 9: Виявити повільні Elasticsearch‑запити через profile

cr0x@server:~$ curl -s -H 'Content-Type: application/json' http://localhost:9200/products/_search -d '{
  "profile": true,
  "size": 20,
  "query": {
    "bool": {
      "must": [{"match": {"title": "shoes"}}],
      "filter": [
        {"terms": {"brand_id": [12, 15, 18]}},
        {"term": {"in_stock": true}},
        {"range": {"price": {"gte": 50, "lte": 120}}}
      ]
    }
  },
  "aggs": {"brands": {"terms": {"field": "brand_id"}}}
}' | sed -n '1,35p'
{
  "took" : 38,
  "timed_out" : false,
  "_shards" : { "total" : 12, "successful" : 12, "skipped" : 0, "failed" : 0 },
  "hits" : { "total" : { "value" : 18432, "relation" : "eq" }, "hits" : [ ... ] },
  "profile" : { "shards" : [ { "searches" : [ { "query" : [ { "type" : "BooleanQuery", "time_in_nanos" : 8200000 } ] } ] } ] }
}

Що це означає: took: 38ms — нормально; профілювання показує, куди йде час (query vs aggregation vs fetch).

Рішення: Якщо домінують агрегації, зменшіть кардинальність, використайте filter агрегації або попередньо обчислюйте деякі фасети.

Завдання 10: Виявити надто багато шардів (проста помилка в Elasticsearch)

cr0x@server:~$ curl -s http://localhost:9200/_cat/indices/products?v
health status index    uuid                   pri rep docs.count docs.deleted store.size pri.store.size
green  open   products Zk8p3bYfQkOaK9mD1n2x3Q  24   1   820000        12000     96gb        48gb

Що це означає: 24 первинних шарди для <1M документів часто зайве. Over‑sharding підвищує накладні витрати й латентність.

Рішення: Рейіндекс у меншу кількість шардів або використайте rollover/ILM, якщо дійсно потрібні індекси за часом (більшість каталогів такого не потребують).

Завдання 11: Перевірити індикатори тиску JVM heap в Elasticsearch

cr0x@server:~$ curl -s http://localhost:9200/_nodes/stats/jvm?filter_path=nodes.*.jvm.mem.heap_used_percent,nodes.*.name | sed -n '1,25p'
{
  "nodes" : {
    "9a1b" : { "name" : "es-data-1", "jvm" : { "mem" : { "heap_used_percent" : 78 } } },
    "9a1c" : { "name" : "es-data-2", "jvm" : { "mem" : { "heap_used_percent" : 83 } } }
  }
}

Що це означає: Високе стабільне використання heap корелює з GC‑паузами і стрибками латентності, особливо при важких агрегаціях.

Рішення: Зменшити навантаження агрегацій, виправити mapping, щоб уникнути fielddata, масштабувати data‑вузли або акуратно налаштувати розмір heap.

Завдання 12: Перевірити мапінги для полів‑фільтрів (keyword vs text)

cr0x@server:~$ curl -s http://localhost:9200/products/_mapping | sed -n '1,45p'
{
  "products" : {
    "mappings" : {
      "properties" : {
        "brand" : { "type" : "text" },
        "brand_id" : { "type" : "integer" },
        "color" : { "type" : "keyword" },
        "title" : { "type" : "text" },
        "in_stock" : { "type" : "boolean" },
        "price" : { "type" : "scaled_float", "scaling_factor" : 100 }
      }
    }
  }
}

Що це означає: Фільтрація/агрегація по brand як text — це пастка; потрібен keyword (або підполе brand.keyword).

Рішення: Виправити mapping у новому індексі і реіндексувати. Не «хакати» це runtime‑скриптами, якщо вам не до вподоби латентність.

Завдання 13: Перевірити свіжість статистик MySQL (сенс оптимізатора)

cr0x@server:~$ mysql -e "ANALYZE TABLE products, product_attr;"
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| catalog.products  | analyze | status   | OK       |
| catalog.product_attr | analyze | status | OK       |
+-------------------+---------+----------+----------+

Що це означає: Оновлення статистик може радикально змінити плани запитів, якщо вони були застарілими.

Рішення: Якщо це «фіксує» пошук, вам все одно потрібен надійний план: плануйте ANALYZE або переробіть запити для стійкості.

Завдання 14: Виявити зловживання глибокою пагінацією на рівні застосунку

cr0x@server:~$ sudo awk '{print $7}' /var/log/nginx/access.log | grep -E 'search\?' | sed 's/.*offset=\([0-9]\+\).*/\1/' | sort -n | tail -n 10
2000
2400
3000
3600
4200
5000
6200
8000
12000
20000

Що це означає: Користувачі (або боти) запитують офсети, що викликають лінійну роботу в MySQL і нетривіальну роботу в пошукових движках.

Рішення: Обмежити офсети, вимагати курсорну пагінацію і застосувати митігацію ботів. Глибока пагінація — це продуктове рішення, загорнуте в технічне.

Завдання 15: Перевірити затримку диска (прихована залежність для обох систем)

cr0x@server:~$ iostat -x 1 3
Linux 6.5.0 (db1)  12/30/2025  _x86_64_  (32 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           12.1    0.0     3.2    18.7    0.0    66.0

Device            r/s     w/s   rkB/s   wkB/s  await  svctm  %util
nvme0n1         120.0   340.0  9800.0 40200.0  18.4   1.2   92.0

Що це означає: Високе await і майже насичене %util означають, що сховище обмежує вас. Пошукові навантаження підсилюють випадкові читання.

Рішення: Якщо диск — вузьке місце, жодне налаштування запитів не врятує повністю. Зменшіть I/O (кращі індекси/денормалізація) або оновіть сховище.

Поширені помилки: симптом → корінь → виправлення

1) Симптом: «Пошук швидкий в одних категоріях, жахливий в інших»

Корінь: Скошені дані + застаріла/неточна статистика призводять до нестабільних планів MySQL; деякі категорії «роздувають» кардинальність JOIN.

Виправлення: Запустіть EXPLAIN ANALYZE для репрезентативних категорій; оновіть статистику; додайте цільові композитні індекси; розгляньте перенесення фасетів до Elasticsearch.

2) Симптом: «Додав один фільтр — латентність зросла з 200ms до 8s»

Корінь: Предикат змінив порядок JOIN або відключив використання індексу; предикат діапазону або OR ламає префікс композитного індексу; з’явилися тимчасові таблиці/filesort.

Виправлення: Перепишіть запит, щоб зменшити OR; уникайте ведучого wildcard LIKE; сплющіть «гарячі» атрибути; перестаньте використовувати OFFSET; або зніміть навантаження у фільтровий контекст Elasticsearch.

3) Симптом: «Кількості по фасетах неправильні або відсутні під навантаженням»

Корінь: Кешування повних відповідей зі застарілими даними; або підрахунки на репліках із лагом; або змішування eventual‑пошуку з реальним станом запасів.

Виправлення: Визначте, що має бути строго консистентним (зазвичай checkout, а не пошук). Використовуйте near‑real‑time індексування і окрему логіку доступності з чіткими SLA.

4) Симптом: «CPU MySQL у нормі, але час відповіді жахливий»

Корінь: Очікування I/O від великих сканів, погана локальність або пропуски в buffer pool; сортування викидається на диск.

Виправлення: Виміряйте Innodb_buffer_pool_reads і затримку диска; зменшіть rows examined; додайте пам’яті/швидке сховище; припиніть виконувати аналітичні фасети в MySQL.

5) Симптом: «Elasticsearch повільний після додавання полів до індексу»

Корінь: Вибух мапінгів; агрегації високої кардинальності; занадто багато keyword-полів; збільшився об’єм doc values і диск/пам’ять.

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

6) Симптом: «Кластер Elasticsearch виглядає здоровим, але запити тайм‑аутяться»

Корінь: Over‑sharding, повільні мерджі, тиск heap або диски майже заповнені; фан‑аут запиту множить накладні витрати на шарди.

Виправлення: Зменшити кількість шардів через реіндекс; покращити сховище; слідкувати за heap і GC; налаштувати refresh/merge; масштабувати data‑вузли.

7) Симптом: «Ми використали MySQL FULLTEXT і все одно не отримали потрібного»

Корінь: FULLTEXT обробляє токенний матч, але структуровані фільтри + підрахунки фасетів + тонка настройка релевантності — це інша робота.

Виправлення: Розглядайте FULLTEXT як частковий інструмент для обмежених випадків. Якщо потрібні фасети й релевантність — використовуйте Elasticsearch чи інший пошуковий движок.

8) Симптом: «Порядок результатів змінюється між запитами»

Корінь: Нестабільне вторинне сортування в SQL; конкурентні оновлення; в Elasticsearch — зв’язки скорингу й мерджі сегментів можуть змінювати порядок при однакових балах.

Виправлення: Додайте стабільні вторинні сорти (наприклад, product_id), використовуйте детерміновані ключі сорту і явно вказуйте поведінку сортування.

Чеклісти / покроковий план

Коли MySQL прийнятний для «пошуку» (зробіть це, а не так)

• Використовуйте MySQL для адміністративного пошуку, інструментів служби підтримки і маленьких каталогів із простими фільтрами.
• Уникайте EAV‑JOIN на гарячих шляхах. Якщо треба фільтрувати по атрибутах, попередньо обчисліть денормалізовану таблицю з ключем product_id.
• Тримайте запити дружніми до індексів: без ведучого wildcard LIKE; обмежуйте OR‑логіку; уникайте глибокого OFFSET.
• Вимірюйте rows examined і тимчасові таблиці; не покладайтеся на «він має індекс».

Коли Elasticsearch — правильний вибір (для більшості магазинів)

• Вам потрібна фасетна навігація з підрахунками.
• Вам потрібне ранжування релевантності і налаштування по полях.
• Вам потрібна толерантність до помилок, синоніми, стемінг, мультимовність.
• Вам потрібна стабільна низька латентність при непередбачуваних комбінаціях фільтрів.

Покроковий план міграції: MySQL — джерело істини, Elasticsearch — пошук

1. Визначте контракт: які поля searchable, filterable, sortable; яку свіжість гарантуєте.
2. Спроєктуйте модель документа: один документ продукту з денормалізованими полями, плюс nested‑атрибути при потребі.
3. Створіть мапінги свідомо: keyword для точних збігів/фасетів, text для аналізованого пошуку, numeric для діапазонів, scaled_float для цін.
4. Побудуйте pipeline індексації: черги подій, ідемпотентні оновлення, логіку повторів, dead‑letter.
5. Бекфіл безпечно: створіть новий індекс, bulk‑завантажте, валідуйте підрахунки й вибіркові запити, потім переключайтесь через alias.
6. Реалізуйте dual reads під час rollout: порівнюйте MySQL і ES результати на частині трафіку, щоб виявити дрейф мапінгів/логіки.
7. Гардрейли: обмежуйте агрегації, глибину сторінок, лімітуйте боти й відстежуйте форми запитів.
8. Експлуатуйте як продакшн: дашборди для heap/disk/latency, алерти на лаг індексації, runbook для реіндексування.

Жорсткі правила, що запобігають повторним інцидентам

• Ніколи не випускайте нову фасету без навантажувального тесту на реалістичних комбінаціях фільтрів.
• Ніколи не дозволяйте UI генерувати необмежені IN (...) списки без ліміту.
• Завжди трактуйте глибоку пагінацію як продуктове рішення з явним лімітом.
• Завжди тримайте MySQL як джерело істини; пошуковий індекс — це проєкція.
• Завжди робіть реіндекс відтворюваним (alias + replayable source).

FAQ

1) Чи можу я залишити все в MySQL, додавши більше індексів?

Для простого пошуку — так. Для фасетованого e‑commerce пошуку з багатьма фільтрами і підрахунками — ні.
Індекси можуть полегшити біль, але вони не змінюють фундаментального невідповідності: JOIN + GROUP BY + ранжування
не є сильними сторонами MySQL на великих обсягах.

2) Що, якщо я використовую MySQL FULLTEXT?

FULLTEXT може допомогти з токенним матчем, але фасетування й налаштування релевантності все одно часто ведуть до пошукового движка.
FULLTEXT не врятує від EAV‑JOIN, глибокої пагінації і дорогих підрахунків фасетів.

3) Чому фільтри роблять SQL повільнішим, ніж лише текстовий пошук?

Тому що фільтри часто змушують робити JOIN (атрибути, інвентар, ціни) і вбивають селективність індексів.
Множина кандидатів залишається великою, а сортування і пагінація множать роботу. В Elasticsearch багато фільтрів
стають кешованими перетинами наборів.

4) Чи завжди Elasticsearch швидший?

Для пошукових навантажень (текст + фільтри + агрегації) — здебільшого так. Для транзакційних пошуків і строгої консистентності — ні.
Elasticsearch також може бути повільним, якщо ви over‑shard, неправильно замапили поля або запускаєте агрегації на слабкому залізі.

5) Як тримати інвентар «в реальному часі», якщо Elasticsearch eventual‑consistent?

Визначте, що означає «в реальному часі» для UX. Поширені підходи: індексувати інвентар частими оновленнями; трактувати «в наявності» як near‑real‑time;
або перевіряти доступність на етапі додавання до кошика / оформлення замовлення. Не прикидайтеся, що результати пошуку — остаточна істина.

6) Яка найгірша помилка схем у Elasticsearch для e‑commerce?

Мапити поля фасетів як text замість keyword і індексувати все «на всяк випадок».
Це надуває doc values, уповільнює агрегації і робить використання heap спайковим.

7) Як уникати проблем з глибокою пагінацією?

Віддавайте перевагу курсорній пагінації. У MySQL використовуйте «seek method» (запам’ятати останній ключ сорту + id).
В Elasticsearch використовуйте search_after для детермінованих сортувань. Також: обмежте максимальну глибину сторінки.
Ніхто не купує сторінку 500.

8) Чи можна робити гібрид: спочатку MySQL фільтр, потім Elasticsearch?

Можна, але це часто податок на складність з малою користю. Якщо MySQL дає малий набір кандидатів (дуже селективний), то так.
Але більшість фільтрів витискають мало селективності, і в результаті ви робите два дорогі запити замість одного.

9) Який мінімум для безпечної експлуатації Elasticsearch?

Надійні диски, достатньо heap, розумні кількості шардів, моніторинг heap/disk/latency і стратегія реіндексування з alias.
Також: pipeline індексації з повторними спробами і механізмом виявлення відставання.

10) Коли не варто використовувати Elasticsearch?

Якщо ваш «пошук» — це насправді невеликий адмінський UI, або каталог крихітний і стабільний, MySQL простіший.
Якщо команда не може експлуатувати розподілену систему, або інвестуйте в цю компетенцію, або обирайте керований сервіс.

Висновок: практичні наступні кроки

Якщо ваш e‑commerce пошук включає множинні фільтри, підрахунки фасетів, ранжування релевантності і часті зміни сортування,
припиніть намагатися переспьоркувати проблему індексами в MySQL. Ви витратите тижні на шліфування мілісекунд, доки нова фасета
не поверне вам секунди. Це не інженерія; це заперечення, прикрите метриками.

Зробіть замість цього:

1. Виміряйте: захопіть форми повільних запитів, rows examined, тимчасові таблиці і затримку диска.
2. Встановіть гардрейли в UI: обмежте глибоку пагінацію, лімітуйте мульти‑селект, лімітуйте бот‑трафік.
3. Визначте контракт: що має бути свіжим, що може бути near‑real‑time і що означає коректність.
4. Перенесіть пошук в Elasticsearch, коли набір фіч цього вимагає: фасети, релевантність, толерантність до помилок.
5. Експлуатуйте pipeline: відтворюване індексування, cutover через alias, дашборди на лаг індексації і стан кластера.

MySQL залишається правильним місцем для замовлень, істини по інвентарю, цінових правил і всього, про що ви звітуєте аудиторам.
Elasticsearch — там, де ви дозволяєте клієнтам досліджувати каталог швидко, не перетворюючи базу даних на обігрівач.