Говорите SQL?

Говорите SQL?

Речь пойдёт об одном проекте, с которым пришлось работать пару месяцев и о том, к чему может привести чрезмерное и необоснованное «употребление» 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-запросы действительно необходимы
  • - бенчмаркинг различных имплементаций может показаться пустой тратой времени, но на самом деле значительно сократит издержки на разработку и поддержку

 

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

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

  • - код ревью вообще отсутствует, а значит багов будет, мягко говоря, немало
  • - в команде явно проблемы с коммуникацией и взаимопониманием. Ибо о каком взаимопонимании может идти речь, если "мне по фиг как ты там пишешь и почему"
  • - если люди не думают о том, чтобы код был читабелен хотя бы для коллег, то они не будут думать и о качестве конечного продукта. А значит и руководству также всё равно, либо руководство попросту некомпетентно. Если проект "подкинули" со стороны - компания может заработать неплохие деньги, но если это собственный проект компании, либо проект который компания разрабатывает с нуля, то стоит задуматься, туда ли Вы попали.