Оптимизация запросов 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
для примешивания посторонних таблиц к внешнему запросу.
Ну а напоследок советую просмотреть выступление по крупным проектам:
Использование конструкций вида:
Очень плохо использует индексы.
На более-менее приличных таблицах запросы средней сложности могут выполнятся более секунды.
Пример: есть 2 таблицы (players - 580тыс записей, clan - 8000 записей). Все поля, участвующие в выборке - проиндексированы:
Как видно - индекс не использовался. Для вывода были просмотрены все строки таблицы players.
Перепишем запрос на JOIN;
Просмотрено 422 записи.
ps. Вот моя статья на эту тему: http://tarlyun.com/mysql/optimiziruem-where-in-podzaprosy/
Есть вот такой вопрос:
Вот например, мы вытащили при помощи active record строку таблицы, представленную как экземпляр класса. У этого класса есть свойство - связь с другой таблицей. Это свойство - массив, в котором содержатся другие экземпляры связанный данных.
Как можно получить такое свойство при помощи DAO обернутое в ActiveRecord ?
Как обычно: сделать связь и получать через ленивую загрузку.
Frmework опечатка
Спасибо! Исправил.
Я бы не согласился, что 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
что прописать вместо звездочек!?
Спасибо за наводку, решил вопрос таким образом
такого рода запрос
почему то не отбирает записи