Кажете SQL?

Кажете SQL?

05.12.2018 23:06 | PHP

Мова піде про один проект, над яким довелося трудитися пару місяців і про те, до чого може привести надмірне і необґрунтоване «вживання» SQL в поєднанні з принципом «головне, щоб працювало».

Зі зрозумілих причин не буду давати назви і лінк, але відразу уточню - проект діючий, до того ж, наскільки мені відомо, на розробку і підтримку витрачено і витрачається чимало коштів. Судячи з анотацій phpStorm, з 2013 року код писали не менше 8 осіб, половина з яких європейці, з яких, власне, все й починалося. Фреймворк - CodeIgniter 2, БД - PostgreSQL, хоч якась документація - відсутня. Завдання: фікс багів, розробка нового функціоналу, оптимізація.

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

Поїхали.

Міграції

Яким би примітивним не був CodeIgniter 2, але навіть він передбачає створення класів міграцій. Однак, хтось порахував, що буде швидше, якщо прописувати міграції відразу на PostgreSQL (далі просто SQL). Зрозуміло, цьому комусь довелося додатково попрацювати над кодом, який би обслуговував такі міграції. Але суть в іншому - оскільки в разі помилки була втрачена можливість відкату, замість прискорення процесу отримали плутанину і головний біль. Один з реальних прикладів: створюється якась таблиця з якимись полями, через два дні людина вирішує, що неправильно вказала тип деяких полів. За звичайних обставин треба було б зробити rollback цієї єдиної міграції, підправити код, і запустити її знову. Але в нашому випадку ... пишеться нова міграція. Кінцевий результат: на майже 90 таблиць в базі більше 400 міграцій.

Query builder

Від нього просто відмовилися на користь чистого SQL. Ну як чистого - практично всі запити до БД виконуються ось так:

$this->db->query($query);

 

Іншими словами, ми таки підіймаємо білдер, але відмовляємося від створення ланцюжків методів, які значно скорочують код і, як на мене, роблять його більш читабельним. Просте запитання: якщо все одно доводиться інстанціювати необхідні для білдера класи, чи так уже багато заощадимо відмовляючись від методів на кшалт where(), group_by() і т.д.? Чи робив хто-небудь порівняння перфомансу обох варіантів, яке б обгрунтувало дане рішення? Ні. Що в результаті? Повна втрата гнучкості і, як наслідок, моделі в середньому на дві і більше тисячі рядків з чималою кількістю методів, які частково, а часом і повністю дублюють один одного. Наслідком вищеозвученого є ще пара «сюрпризів»:

  • - Оскільки методи схожі, за назвою складно визначити, що саме метод робить. Тому щоб зрозуміти, чи потрібно писати новий метод, або десь уже є той, який тобі потрібен, доводилося переглядати майже весь код моделі (і пощастить, якщо однієї) 
  • - Якщо бачиш, наприклад, метод з назвою get(), це абсолютно не означає, що на виході отримаєш масив об'єктів з полями відповідної таблиці. Цілком можна відхопити масив, кожен об'єкт якого буде містити вибіркові поля потрібної таблиці, до якої join-ами прикрутили ще кілька таблиць, і все це зроблено з умовами та сортуванням придатними тільки для певних ситуацій

Зауважу - я абсолютно не проти складних SQL запитів, більш того - є випадки, коли без них не обійтися, але кожне рішення повинно бути обгрунтованим. Я не вірю в «це буде працювати швидше», я вірю цифрам. Аргументуйте рішення - хоча б приблизно оцініть середні і пікові навантаження на додаток зараз і в перспективі. Проведіть тести, отримайте цифри і потім вирішуйте.

Надмірність даних

В даному випадку під надмірністю мається на увазі порушення принципу YAGNI. Пам'ятаємо тільки що описаний приклад методу get()? Бажання запхати все і відразу в один метод, вибачте, триповерховими запитами простежується в усьому проекті. І ось коли, наприклад, треба отримати список товарів для select в звичайній формі, прилітають товари з усіма ассетами (зображеннями і відео), категоріями, брендами і іншою абсолютно непотрібною інформацією. А треба було отримати тільки id і назви. Підемо далі і уявімо сторінку, на якій є товари, категорії, бренди і т.д. Це не фантазії - я про реальні речі в реальному проекті.

Ще один момент: навіть якщо дані були передані у в'ю, досить часто зустрічалися ситуації, коли ті ж самі дані (або їх похідні) на ту ж саму сторінку ... підтягувалися ajax-ом. Результат: передаються / підтягуються величезні масиви даних, як мінімум половина з яких абсолютно не потрібна.

Проблема N+1

Перший варіант - ліниве завантаження. У в'ю можна було зустріти щось подібне:

<?php if (isset($item->primary_image_path)) : ?>
    <img src="<?= $item->primary_image_path ?>">
<?php endif; ?>

 

Все б нічого, але за primary_image_path ховається запит в БД. І якщо на сторінці виводиться, наприклад, 100 товарів, то отримуємо від 100 до 200 зайвих запитів. І якщо такий код можна якось пояснити незнанням / неуважністю / небажанням заглянути під капот, то другий варіант - навмисне звернення до бази на кожній ітерації циклу:

public function get_data($ids) 
{
    return array_map(function ($id) {
        return $this->data_model->get($id);
    }, $ids);
}

 

Тут люди явно розуміли, що роблять, але чомусь не замислювалися про наслідки. Але це ще не все. Метод, який викликався на кожній ітерації циклу міг містити кілька прямих і / або опосередкованих звернень до бази. Іншими словами, проблема N + 1 перетворювалася в N * X + 1. Результат: запити до БД на окремих сторінках обчислювалися навіть не сотнями, а тисячами.

Як проводилась оптимізація

По-перше, де тільки можна, позбутися від запитів до БД всередині циклів. Тобто, наприклад попередній метод, переписувався так:

public function get_data($ids)
{
	return $this->data_model->get_data_by_ids($ids);
}

 

Іншими словами, замість N запитів до бази (по одному на кожен об'єкт), одним запитом отримували масив з N об'єктів. З точки зору перфомансу це далеко не одне і те ж.

По-друге, будувалося щось подібне до відносин між сутностями. Не дарма в багатьох гідних фреймворках функціонал relations закладений спочатку - можете подивитися на Laravel Eloquent Relationships, Symfony Associations/Relations, Yii Relational Data, Phalcon Models Relationships і т.д. У CodeIgniter 2 нічого подібного немає, і оскільки часу на добротну реалізацію особливо не було, робилося досить примітивно. І навіть не стільки з метою чітко позначити відносини, скільки домогтися однієї з переваг відносин - можливості замінити ледаче завантаження на жадібне. Виглядало приблизно так:

public function get_products_with($product_ids, $relations = [])
{
    $products = $this->product_model->get_products_by_ids($product_ids);

    foreach ($relations as $relation) {
        $method = 'get_products_' . $relation;
        if (method_exists($this, $method)) {
            $products = $this->{$method}($product_ids, $products);
        }
    }

    return $products;
}


// І потім, наприклад, так: 
public function get_products_categories($product_ids, $products)
{
    $categories = $this->product_model->get_categories_by_product_ids($product_ids);

    return array_map(function($product) use ($categories) {
        $product->categories = array_filter($categories, function ($category) use ($product) {
           return $category->product_id == $product->id;
        });
        return $product;
    }, $products);
}

 

Тобто якщо раніше при необхідності отримати продукти з категоріями ітерували масив товарів і робили запит для отримання категорій для кожного товару, тепер забираємо одним запитом товари, другим запитом забираємо категорії по масиву id товарів і потім просто співставляємо категорії з продуктами. Складність алгоритму все та ж: N^2, кількість ітерацій знову-таки не зменшилася, але кількість запитів до бази зменшилася до 2-х. При цьому абсолютно не важливо, яка кількість товарів (плюс категорій) треба отримати - 1, 100 або 1000 - при будь-якому розкладі буде все одно тільки два запити.

Зрозуміло, при необхідності можна було зробити і так:

$products = $this->get_products_with($product_ids, ['categories', 'brands', 'assets']);

 

І ще пару моментів. Брати з бази тільки ті дані, які потрібні і не більше. Для чого, до речі, довелося прописувати в моделях методи з найпростішими запитами, а часом і розбивати складні запити на кілька простих. Ну і позбутися дублювання отримання даних в php і javascript.

Результати

Цілком слушне запитання - який же профіт? Відповіддю послужить навантаження на сервер бази даних (зміни були залиті увечері 15 листопада):

Вважаю цим все сказано.

Висновки

Точніше, які висновки я зробив особисто для себе:

  • - використання sql по кожному найменшому чиху не благо, а величезна і ресурсовитратна проблема
  • - як мінімум, деякий час буду дуже обережно ставитися до вакансій, в яких йдеться про "вміння працювати зі складними запитами" і т.п. У подібних випадках мені відразу ж захочеться поглянути на код і переконатися, що грамотно побудована архітектура додатку, до коду на php претензій немає, а тому складні sql-запити дійсно необхідні 
  • - бенчмаркінг різних імплементацій може здатися марною тратою часу, але насправді значно скоротить витрати на розробку і підтримку 

 

Пост скриптум

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

  • - код рев'ю взагалі відсутній, а значить багів буде, м'яко кажучи, чимало 
  • - в команді явно проблеми з комунікацією і взаєморозумінням. Бо про яке взаєморозуміння може йти мова, якщо "мені по фіг як ти там пишеш і чому"
  • - якщо люди не думають про те, щоб код був читабельний хоча б для колег, то вони не будуть думати і про якість кінцевого продукту. А значить і керівництву також все одно, або керівництво просто некомпетентне. Якщо проект "підкинули зі сторони" - компанія може заробити непогані гроші, але якщо це власний проект компанії, або проект який компанія розробляє з нуля, то варто задуматися, чи туди Ви потрапили.