Оптимізація SQL в 23 рази

Надзвичайно цікава розповідь від Navneet Kumar Singh на dev.to про те, як йому довелося покроково розв'язувати проблему пов'язану з неоптимізованим запитом до БД під управлінням TypeORM.

Дано

Таблицю з більше ніж 700k записів та поганий запит до неї, час віддачі даних на фронт може сягати більш ніж 6 секунд. Безпосередньо сам запит не складний: 4 join, близько 4 where, sorting, limit та skip.

Аналіз причини

• Index'es ‒ неправильні або взагалі відсутні індекси для стовпців, які використовувались для join
• Необов'язкові jois ‒ джоіни яких можна було позбутися
• Баг з порожньою строкою ‒ якщо юзер не передавав дані для фільтрації даних, то відбувався пошук по колонці де шукався порожній рядок
• ORM ‒ генерував дуже поганий запит до БД

Тестове середовище

Для експериментів була взята тестова БД з 100k записами. Без всіляких оптимізацій запит з фронта в середньому виконувався за 2.3 секунд. 

Рішення

Index'es 

Додавання індексу до колонок дозволило скоротити запит до 2-х секунд, це не багато, але вже щось.

Баг з порожньою строкою

Ну тут все очевидно. Якщо юзер нічого не шукав, то замість порожньої строки передавався null і пошук не відбувався. Час виконання запиту скоротився з 2.3 до 1.3 секунди, якщо юзер щось фільтрує ‒ це +500мс, що цілком прийнятно.

Необов'язкові jois

Були видалені непотрібно джоіни які просто ніде не використовувалися.

ORM

TypeORM використовує active record pattern, який створює JSON-подібний об'єкт для роботи з БД. ORM-ки дуже сильно економлять час, але іноді генерують погані запити. Наприклад де можна було бахнути простий SQL запит, то TypeORM зробив два запити які зовсім не потрібні. Причина цього не відома, але автор стверджує, що це відома проблема. Ще до всього TypeORM також об'єднував чотири таблиці двічі по одному разу в обох запитах, а потім також сортував двічі по одному разу в обох запитах.

Саме ці два згенеровані запити були основною причиною проблеми, вони були замінені простим, а щонайголовніше ‒ одним SQL запитом:

SELECT
  Submission.id,
  Submission.language,
  Submission.verdictCode,
...
FROM
  submission AS Submission
  LEFT JOIN problem ...
  LEFT JOIN user ...
ORDER BY
  Submission.created_at DESC
LIMIT 10

Коли був запущений новий запит, він виконувався всього за 100мс. Таким чином, ми скоротили запит з 1,3 секунди до 100мс, а в цілому з 2.3 секунди до 100мс. Що означає приріст продуктивності більш ніж в 23 рази!

Наступного дня...

Далі автор розповідає про інші способи оптимізації та дає рекомендації.

• Наприклад використання full text indexing замість звичайного індексу, якщо LIKE-запити передбачають пошук слів або фраз всередині колонки.
• Переконайтеся, що стовпчик не чутливий до регістру. Це дозволяє ефективніше використовувати індекс, оскільки пошук стає нечутливим до регістру
• Пошук за префіксом: якщо LIKE-запит використовує підстановку в кінці (наприклад, LIKE префікс%), індекс все ще може бути ефективно використаний. Однак, якщо шаблон знаходиться на початку (наприклад, LIKE %suffix), індекс не буде використано. У таких випадках варто розглянути можливість використання альтернативних методів, таких як повнотекстовий пошук.
• Кешування (memcached чи Redis)

Отже! У фінальному результаті автору статті вдалося зменшити час очікування з 7 секунд до <=150 мс, що надзвичайно круто.

🌏 Оригінал: dev.to