webew
Войти » Регистрация
 
PHP
MySQL/MariaDB :: оптимизация

Архитектурная концепция программирования при работе со списками в веб-приложениях на основе PHP и MySQL

28 февраля 2013, 15:04

В статье излагается подход, позволяющий оптимизировать производительность запросов MySQL, а также облегчить написание кода на PHP с использованием специально разработанной для этого библиотеки.

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

Один из подходов к оптимизации запросов, используемых для получения данных списков, заключается в разбиении таких запросов на две части:

  1. Получение списка идентификаторов.
  2. Получение собственно данных.

Отчего так получается быстрее? Рассмотрим этот вопрос подробнее.

Как правило, списки составляются на основании каких-то условий (к примеру, показываются не все новости, а лишь несколько последних; не все товары, а только лежащие в определенном ценовом диапазоне и т.п.), при этом часто задействуются связи с другими сущностями (например, товары принадлежат к определенной категории, у новостей или статей может быть автор и др.). С точки зрения СУБД это означает, что требуется запрос типа JOIN с WHERE, ORDER BY (а часто - также GROUP BY) и LIMIT. Обычно такие запросы выполняются медленно и нередко становятся серьезной проблемой1. В качестве примера возьмем запрос, используемый в CMS Wordpress:

SELECT *
FROM wp_posts
LEFT JOIN wp_post2cat ON (wp_posts.ID = wp_post2cat.post_id)
LEFT JOIN wp_categories ON (wp_post2cat.category_id = wp_categories.cat_ID)
WHERE 1=1
  AND (category_id = '1')
  AND post_date_gmt <= '2008-09-12 00:15:59'
  AND (post_status = 'publish')
  AND post_status != 'attachment'
GROUP BY wp_posts.ID
ORDER BY post_date DESC
LIMIT 9, 3;

Причина медленного его выполнения лежит в особенностях механизма работы MySQL и заключается в следующем. Сложность условий выборки не позволяет использовать для сортировки индексы, поэтому MySQL потребуется создать временную таблицу, содержащую все перечисленные в запросе колонки, которую затем отсортировать и выбрать указанное число записей. Поскольку такая временная таблица будет велика, эта операция будет происходить медленно (это усугубится еще сильнее, если временная таблица не поместится в оперативную память). Решающее значение здесь имеет именно общая длина колонок: чем длиннее строка таблицы, тем больше ресурсов потребуется на то, чтобы такие длинные строки переставить в нужном порядке.

Следует отметить также, что, по сравнению с общим количеством обрабатываемых записей конечное результирующее их число невелико. Получается, что б́ольшая часть работы по выборке содержимого ячеек и сортировке получившихся в результате длинных записей фактически делается впустую.

Если в части SELECT запроса оставить только идентификаторы записей, а среди таблиц оставить только те, что необходимы для проверки условий (что тоже немаловажно, т.к. позвляет избавиться от лишних JOIN), запрос значительно ускорится.

Вот как это будет выглядеть на практике при использовании языка PHP:

// 1.1. Уберем из запроса все колонки, кроме ID, а также лишние таблицы:

$sql = "
    SELECT wp_posts.ID
    FROM wp_posts
    LEFT JOIN wp_post2cat ON (wp_posts.ID = wp_post2cat.post_id)
    WHERE (category_id = '1')
      AND post_date_gmt <= '2008-09-12 00:15:59'
      AND (post_status = 'publish')
      AND post_status != 'attachment'
    ORDER BY post_date DESC
    LIMIT 9, 3;
    "
;

// 1.2. Теперь получим список идентификаторов:

$ids = array();
$result = mysql_query($sql);
while ( ($row = mysql_fetch_assoc($result) !== FALSE)
    $ids[] = $row['ID'];

Теперь нужно получить непосредственно данные для этих трех записей, сохранив при этом порядок сортировки:

// 2.1. Получаем данные в неотсортированном виде:

$sql = "
    SELECT *
    FROM wp_posts
    LEFT JOIN wp_post2cat
    LEFT JOIN wp_categories
    WHERE wp_posts.ID IN ("
. implode(",", $ids) . ")
    "
;

$data_raw = array();
$result = mysql_query($sql);
while ( ($row = mysql_fetch_assoc($result) !== FALSE)
    $data_raw[$row['ID']] = $row;

// 2.2. Восстанавливаем порядок сортировки:

$data_final = array();
foreach ($ids as $id)
    $data_final[$id] = $data_raw[$id];
 

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

Библиотека для работы со списками

Описанный подход является одним из основных принципов, лежащих в основе универсальной библиотеки для работы со списками, которая реализует объектно-реляционное отображение. Библиотеке посвящена отдельная статья.

1. По данным курса по оптимизации производительности MySQL, запросы такого типа создают б́ольшую часть нагрузки на сервера MySQL в Рунете, а их оптимизация решает около 50% всех проблем производительности. См. также "Ботанический определитель" Григория Рубцова.


© Все права на данную статью принадлежат порталу webew.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.
Статьи, часто просматриваемые вместе с этой:
Легкий ORM для PHP и MySQL
Кэширование запросов в MySQL
Профилирование запросов
Добавить комментарий
© 2008—2017 webew.ru, связаться: x собака webew.ru
Сайт использует Flede и соответствует стандартам WAI-WCAG 1.0 на уровне A.
Rambler's Top100