Оптимизация запросов ActiveRecord без DAO

Скейтбордист

Одна из вечных тем, то и дело всплывающих в сети и касаемых Yii Framework – это спор относительно использования в своих проектах прямых SQL запросов посредством DAO с одной стороны против использования ActiveRecord с другой. Ведь при разрастании объёмов данных и связей между ними в высоконагруженных проектах многие разработчики переходят от удобной объектной модели ActiveRecord к низкоуровневой работе с прямыми SQL запросами и с простыми ассоциативными массивами. Посмотрим, как в некоторых случаях можно разогнать выборки ActiveRecord почти до скорости DAO.

Здесь я не призываю никого отказываться от DAO, а просто показываю альтернативные пути тем, кто это делать не хочет, но испытывает трудности. Не все знают, что можно использовать любые SQL запросы прямо внутри ActiveRecord.

Сравнение ActiveRecord и DAO

Сравню кратко. Реляционная ActiveRecord умна. Она сама генерирует запросы, подтягивает нужные отношения, позволяет пользоваться жадной и ленивой загрузками данных из БД. Также позволяет навешивать на себя и инкапсулировать дополнительный функционал. Это мощный и удобный объектный инструмент для управления данными.

DAO - обычная надстройка над PDO, позволяющая вбивать вручную или собирать построителем SQL запросы и получать результат в виде массивов. При отказе от ActiveRecord весь необходимый потерянный функционал нужно эмулировать самому разработчику.

Потребность в нестандартных выборках

Часто нам необходимо производить запросы на выборку строк из одной таблицы с указанием условий для полей связанных таблиц. Например, нам надо вывести топ-50 записей по введённым в поисковой форме городу и имени автора.

То есть осуществить такую выдуманную выборку с учётом отношений между таблицами:

$items = BlogPost::model()->findAll(array(
    'condition'=>'category.title LIKE :category AND profile.name LIKE :name',
    'params'=>array(':category'=>'%' . $form->category . '%', ':name'=>'%' . $form->name . '%'),
    'with'=>array(
        'author',
        'author.profile',
        'category'
    ),
    'limit'=>50,
));

Или, воспользуясь более красивым способом, аналогичную:

$criteria = new CDbCriteria();
$criteria->addSearchCondition('category.title', $form->category);
$criteria->addSearchCondition('profile.name', $form->name);
$criteria->with = array(
    'author',
    'author.profile',
    'category'
);
$criteria->limit = 50;
 
$items = BlogPost::model()->findAll($criteria);

Исследуя работу запроса в логах мы видим такой сгенерированный результат:

SELECT * 
FROM tbl_post t 
LEFT OUTER JOIN tbl_user author ON (t.author_id=author.id) 
LEFT OUTER JOIN tbl_profile profile ON (profile.id=author.id) 
LEFT OUTER JOIN tbl_category category ON (t.category_id=category.id) 
WHERE (category.title LIKE '%гра%' AND profile.name LIKE '%три%') 
LIMIT 50

Все нужные нам четыре таблицы (post, user, profile, category) склеились посредством оператора LEFT OUTER JOIN.

Проблема производительности

Как мы видим, запрос производится по связке четырёх таблиц, даже если нам нужна всего одна. Если у нас в крупном проекте очень большие объёмы данных, то запрос с кучей таблиц и оператором LIKE будет выполняться несколько секунд/минут и от души загрузит сервер.

Для оптимизации нужно, во первых, отказаться от жадной загрузки. Некоторые комментаторы отсылают к инструкции по применению атрибута together, который указывает, какие связи надо или не надо по умолчанию включать в запросы.

Попробуем его использовать:

$criteria->together = false;
$criteria->with = array(
    'author',
    'author.profile',
    'category'
);

И в логах мы увидим, что результирующий запрос не изменился совсем. Это произошло из-за того, что построитель запроса увидел посторонние поля category и profile в операторе WHERE

... WHERE category.title LIKE :category AND profile.name LIKE :name

и сам подтянул все необходимые для запроса зависимости. Это естественное поведение CActiveRecord производить жадную загрузку при необходимости.

А что будет, если убрать $criteria->with? Тогда выскочит уведомление об ошибке «Поля category и profile не найдены в таблице tbl_post.

То есть в любом случае этот запрос с LIKE будет производиться по джойну четырёх таблиц (!), а не по одной без джойна.

Оптимизация запросов

Нам бы хотелось выбрать записи из одной таблицы без джойнов.

Попробуем перестроить запрос так, чтобы он не использовал JOIN и не повторял одни и те же проверки. Для этого воспользуемся простыми вложенными подзапросами. Плюс учтём, что индексы tbl_user.id и tbl_profile.id у нас совпадают и мы можем вообще не брать таблицу tbl_user.

Мы можем преобразовать исходный однопроходный запрос в такой трёхшаговый:

SELECT * 
FROM tbl_post t 
WHERE t.category_id IN (SELECT id FROM tbl_category c WHERE c.title LIKE '%гра%') AND t.author_id IN (SELECT id FROM tbl_profile p WHERE p.name LIKE '%три%') 
LIMIT 50

То есть здесь интерпретатор SQL сначала найдёт города и пользователей в подзапросах

SELECT id FROM tbl_category c WHERE c.title LIKE '%гра%'
SELECT id FROM tbl_profile p WHERE p.name LIKE '%три%'

потом произведётся лёгкий запрос по ключам

SELECT * FROM tbl_post t WHERE t.category_id IN (...) AND t.author_id IN (...) LIMIT 50

так как подзапросы не затрагивают внешние поля и выполнятся только один раз (в подзапросах для этого не должно быть обращения к псевдониму t).

AR не достаточно умна чтобы собрать оптимальный запрос. А мы его теперь можем ввести так:

$items = BlogPost::model()->findAll(array(
    'condition'=>'t.category_id IN (SELECT id FROM {{category}} c WHERE c.title LIKE :category) AND t.author_id IN (SELECT id FROM {{profile}} p WHERE p.name LIKE :name)',
    'params'=>array(':category'=>'%' . $form->category . '%', ':name'=>'%' . $form->name . '%'),
    'limit'=>50,
));

или так:

$criteria = new CDbCriteria();
$criteria->condition = 't.category_id IN (SELECT id FROM {{category}} c WHERE c.title LIKE :category) AND t.author_id IN (SELECT id FROM {{profile}} p WHERE p.name LIKE :name)';
$criteria->params[':category'] = $form->category;
$criteria->params[':name'] = $form->name;
$criteria->limit = 50;
 
$items = BlogPost::model()->findAll($criteria);

В логе получаем запрос

SELECT * FROM tbl_post t WHERE t.category_id IN (SELECT id FROM tbl_category c WHERE c.title LIKE '%гра%') AND t.author_id IN (SELECT id FROM tbl_profile p WHERE p.name LIKE '%три%') LIMIT 50

Мы и перешли от жадной загрузки четырёх таблиц к выборке по одной, и не потеряли нужные нам поля.

Фактически, теперь мы можем навсегда избавиться от автоматической жадной загрузки, используя те же прямые запросы как с DAO (совершенно произвольно с любыми таблицами без каких-либо ограничений) и оборачивая результат в ActiveRecord:

$criteria = new CDbCriteria();
$criteria->addCondition('t.id IN (SELECT id FROM {{post}} p LEFT OUTER JOIN ... WHERE ... ORDER p.date DESC', array(':param'=>$value)));
$criteria->order = 't.date DESC';
$items = Post::model()->findAll($criteria);

Только не забудьте, что в подзапросах использовать алиас t нежелательно. Также мы можем использовать $criteria->join для примешивания посторонних таблиц к внешнему запросу.

Ну а напоследок советую просмотреть выступление по крупным проектам:

Другие статьи

Раньше мы рассматривали возможность повторного использования стандартных CRUD операций путём выноса их в отдельные классы (наследники класса CAction в Yii). У этого способа есть альтернатива – наследование общих действий от базового контроллера. Рассмотрим этот способ подробнее, а также попробуем найти и решить некоторые его проблемы.

На многих новых сайтах всё чаще встречается вывод списка новостей или других сущностей в виде бесконечно подгружающейся ленты. На некоторых сайтах подгрузка выполняется автоматически (на twitter.com или vk.com), на других – вручную, то есть в конце списка вместо стандартного переключателя страниц имеется кнопка «Показать ещё». Освежим в памяти работу с ClistView и попробуем реализовать подобный функционал на своём сайте.

Разговорились сегодня насчёт вывода списка чекбоксов в админке для выбора категорий к записи, то есть для связи MANY_MANY. Предоположим, что в нашем блоге есть записи и категории. Или товары в магазине и категории. При этом у каждой записи или у каждого товара можно выбрать несколько категорий. Как вывести этот список на странице редактирования статьи или товара?

Велика вероятность, что в новом или старом проекте на Yii появится необходимость изготовления меню с иконками. Это может понадобиться и при вёрстке уже готового шаблона. Рассмотрим несколько решений и сравним их между собой с точки зрения семантики и архитектурной чистоты.

Комментарии

 

Tarlyun

Использование конструкций вида:

SELECT FROM table WHERE field IN (SELECT FROM table2)

Очень плохо использует индексы.
На более-менее приличных таблицах запросы средней сложности могут выполнятся более секунды.

Пример: есть 2 таблицы (players - 580тыс записей, clan - 8000 записей). Все поля, участвующие в выборке - проиндексированы:

players:
	PRIMARY KEY (`pl_id`),

clan:
	INDEX `is_bk` (`is_bk`),
	INDEX `player_id` (`pl_id`)

EXPLAIN SELECT name FROM players WHERE id IN (SELECT player_id FROM clan WHERE is_bk = 1);

1;PRIMARY;players ;ALL;NULL;NULL;NULL;NULL;578927;Using where
2;DEPENDENT SUBQUERY;clan;index_subquery;is_bk,pl_id;pl_id;4;func;1;Using where

Как видно - индекс не использовался. Для вывода были просмотрены все строки таблицы players.

Перепишем запрос на JOIN;

EXPLAIN SELECT name FROM players AS p
LEFT JOIN clan AS c ON (p.id = c.player_id)
WHERE c.is_bk = 1

1;SIMPLE;c;ref;is_bk,player_id;is_bk;1;const;422;Using where
1;SIMPLE;p;eq_ref;PRIMARY;PRIMARY;4;lgndru.c.pl_id;1;Using where

Просмотрено 422 записи.

ps. Вот моя статья на эту тему: http://tarlyun.com/mysql/optimiziruem-where-in-podzaprosy/

Ответить

 

Rom

Есть вот такой вопрос:

Вот например, мы вытащили при помощи active record строку таблицы, представленную как экземпляр класса. У этого класса есть свойство - связь с другой таблицей. Это свойство - массив, в котором содержатся другие экземпляры связанный данных.

Как можно получить такое свойство при помощи DAO обернутое в ActiveRecord ?

Ответить

 

Дмитрий Елисеев

Как обычно: сделать связь и получать через ленивую загрузку.

Ответить

 

Евгений Ivanov

Frmework опечатка

Ответить

 

Дмитрий Елисеев

Спасибо! Исправил.

Ответить

 

Andrew

Я бы не согласился, что JOIN определенно хуже вложенных подзапросов.

Рассмотрим запрос из статьи

SELECT *
FROM tbl_post t
LEFT OUTER JOIN tbl_user author ON (t.author_id=author.id)
LEFT OUTER JOIN tbl_profile profile ON (profile.id=author.id)
LEFT OUTER JOIN tbl_category category ON (t.category_id=category.id)
WHERE (category.title LIKE '%гра%' AND profile.name LIKE '%три%')
LIMIT 50

Скорее всего оптимизатор применит хорошо известный способ: сделать выборку (WHERE) раньше JOIN, то есть tbl_category и tbl_profile будут соединяться не всеми данными, что в них есть, а уже соответствующие предикатам выборки.

Единственное, что вместо * лучше бы написать t.*.

Ответить

 

Александр

Доброго времени суток, может кто подскажет, из таблицы post требуется запросить записи дата публикации которых прошедшее врем, в phpmyadmin делаю запрос SELECT * FROM `post` WHERE `date`< now() получаю записи! Как данный запрос реализовать в Active Record

$data = self::find()
    ->with('category')
    ->where(['date' =>*********])
    ->all();
return $data;

что прописать вместо звездочек!?

Ответить

 

Дмитрий Елисеев
return self::find()
    ->with('category')
    ->andWhere(['<', 'date', new \yii\db\Expression('NOW()')])
    ->all();
Ответить

 

Александр

Спасибо за наводку, решил вопрос таким образом

->andwhere('date<:date', [':date' => date('Y-m-d H:i:s')])

такого рода запрос

->andwhere('date<:date', [':date' => new \yii\db\Expression('NOW()')])

почему то не отбирает записи

Ответить

Оставить комментарий

Войти | Завести аккаунт


(никто не увидит)



Можно использовать теги <p> <ul> <li> <b> <i> <a> <pre>