Перейти к содержимому


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


  • Вы не можете ответить в тему
Сообщений в теме: 65

#61 badisoft

    Продвинутый пользователь

  • Assistent vsupport.club
  • PipPipPip
  • 5075 сообщений
Репутация: 786
Мастер

Отправлено 16 June 2017 - 01:34 PM

как Вам такой запрос из home.php?

Я же уже писал где-то про него. Скорее всего, даже в этой же теме (у меня сохранился текст):
====================
1. как-то я в первом описании совсем забыл, а это весьма важно.
В home.php есть как минимум две (для new_products и popular_products) конструкции вида

SELECT бла-бла-бла FROM таблица_товаров LEFT JOIN таблица_картинок ... LIMIT 0,".$cifra

где $cifra это что-то типа восьми. И получается, что надо объединить по JOIN всю таблицу товаров (десятки тысяч)
с таблицей картинок, чтобы потом оттуда выбрать восемь нужных строк. Результат будет получен НАМНОГО быстрее,
если убрать JOIN из запроса, т.е. оставить запрос только по таблице товаров, а нужные картинки подгрузить отдельными запросами.
Да-да, еще восемь запросов, но суммарно эти девять запросов выполнятся быстрее (у меня - на порядок),
чем одиночный запрос с JOIN.

А вот special_offers так исправлять вовсе не обязательно, т.к. там запрос по небольшой таблице спецпредложений и JOIN вполне уместен.

2. Все (!) запросы в home.php выполняются ВСЕГДА, вне зависимости от текущей страницы.
Хотя спецпредложения выводятся только на главной, а new_products и popular_products - если включены соответствующие блоки.
Имеет смысл перенести эти куски PHP-кода в соответствующие tpl.html-файлы, чтобы код с запросами выполнялся только при
использовании этого tpl.html-файла. Код переносится практически один-в-один, надо только $smarty-> заменить на $this-> и
обрамить код тэгами {php}{/php}
home.php после этого осталнется пустым и можно его будет удалить.


PS. Думаю, что если копнуть, то конструкция "товары JOIN картинки LIMIT около десятка" найдется еще много где.
-----------------------------
  • 0
http://cpu.badisoft.ru (тестовый сайт), http://badisoft.ru (модули)

#62 Salp

    Продвинутый пользователь

  • Assistent vsupport.club
  • PipPipPip
  • 218 сообщений
Репутация: 54
Продвинутый

Отправлено 16 June 2017 - 04:48 PM

Да-да, еще восемь запросов, но суммарно эти девять запросов выполнятся быстрее (у меня - на порядок), чем одиночный запрос с JOIN.

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

Изначально обернул код в home.php в такое условие:
if ((strtok($_SERVER['REQUEST_URI'],"?")=="/")||(strtok($_SERVER['REQUEST_URI'],"index.php")=="/"))
{
/*******код модуля********/
}

Но теперь, пожалуй, перенесу его в шаблон. Он там, действительно, уместнее.

Перенес код в шаблон, разобрал запрос с LIMIT, но разницы не заметил:
   $q = db_query("select productID, name, Price, default_picture, noindex, cpu FROM ".PRODUCTS_TABLE." WHERE categoryID!=1 AND enabled=1 ORDER BY date_added DESC LIMIT ".$cifra);
while ($row = db_fetch_row($q)) {

$picture = db_query("select filename from ".PRODUCT_PICTURES." where photoID=".$row["default_picture"] );
$picture_row = db_fetch_row( $picture );
$row["filename"]=$picture_row["filename"];

if (strlen($row["filename"])>0 && file_exists( "data/small/".$row["filename"])){
$row["filename"] = "small/".$row["filename"];
} else {
$row["filename"] = "empty.gif";
}
$row["cena"] = $row["Price"];
$row["Price"] = show_price($row["Price"]);
$result[] = $row;
}
$this->assign("new_products", $result);

  • 0

#63 badisoft

    Продвинутый пользователь

  • Assistent vsupport.club
  • PipPipPip
  • 5075 сообщений
Репутация: 786
Мастер

Отправлено 16 June 2017 - 07:05 PM

Подозреваю, что данный составной запрос является довольно затратным не только по времени выполнения, но и по расходу памяти.

Не знаю. Не специалист по MySQL. Даже что такое "shared хостинг" не знаю.
Если SQL-сервер на другом ресурсе, то затраты на запрос не Ваша забота. Вы получаете результат, который довольно мал по объему. В минусе только скорость выполнения запроса.
Если SQL-сервер на том же ресурсе, что и сайт (VPS, VDS), тогда имеет смысл озаботиться изучением средств оптимизации работы SQL-сервиса. Всяких там мониторингов и отчетов о. Я так и не сподобился, т.к. смертельной необходимости пока не было.

Перенес код в шаблон, разобрал запрос с LIMIT, но разницы не заметил:

Возможно, Ваш SQL-сервер достаточно хорошо оптимизирован к таким запросам.
У меня (скорее всего, таки не у меня, а у какого-то моего клиента с большой базой товаров) разделение запросов дало эффект "стало на порядок быстрее", но это не значит, что такой результат будет вообще у всех.
  • 0
http://cpu.badisoft.ru (тестовый сайт), http://badisoft.ru (модули)

#64 Salp

    Продвинутый пользователь

  • Assistent vsupport.club
  • PipPipPip
  • 218 сообщений
Репутация: 54
Продвинутый

Отправлено 17 June 2017 - 10:34 AM

Оптимизация, приведенная в product_detailed.php модуля кеширования запросов заставила меня потратить двое суток на то, чтобы найти баг, который проявлялся в виде 100% загрузки процессора хостинга. При чем, баг проявлялся периодически иногда один раз в два часа, а иногда буквально каждые две минуты. Хостер уже начал слать гневные письма о том, что ограничит ресурсы для моих сайтов. Подвисал процесс index.php и бесконечно грузил процессор, но это и понятно, т.к. скрипт product_detailed.php выполняется не самостоятельно, а инклудится в index.php. Очень неприятный баг, на выяснение и устранение которого ушло много времени.
  if ($cnt[0] > 0)
{
$q = db_query("select productID FROM ".RELATED_PRODUCTS_TABLE." WHERE Owner=".$productID);
// BEGIN Patch Optimize 01 (by http://trickywebs.org.ua/ soulmare@gmail.com)
$relatedIdList = Array();
while ($row = db_fetch_row($q))
$relatedIdList[] = $row[0];
$relatedIdList_ = implode(',', $relatedIdList);
$p = db_query("SELECT p.productID, p.name, p.Price, pp.filename, pp.thumbnail, pp.enlarged
FROM ".PRODUCTS_TABLE." p
LEFT JOIN ".DB_PRFX."product_pictures pp ON p.default_picture = pp.photoID
WHERE p.productID IN ( $relatedIdList_ )
AND p.enabled=1
GROUP BY productID");
while($r = db_fetch_row($p)) {
$r["Price"] = show_price($r["Price"]);
if(!file_exists('data/small/' . $r['filename']))
$r['filename'] = null;
$related[] = $r;
}
// END Patch Optimize 01
}

  • 0

#65 badisoft

    Продвинутый пользователь

  • Assistent vsupport.club
  • PipPipPip
  • 5075 сообщений
Репутация: 786
Мастер

Отправлено 17 June 2017 - 11:50 AM

Очень неприятный баг, на выяснение и устранение которого ушло много времени.

А баг-то в чем? Я не вижу ничего критичного во втором запросе.
Я не понял, зачем там GROUP BY productID, если каждого productID и так по одному, нечего группировать.
Да и вообще JOIN с таблицей картинок нужен только если выводятся картинки рекомендуемых товаров.
Главная идея этой правки - замена цикла запросов c WHERE productID= на один запрос с WHERE productID IN() и это вполне правильная идея.

PS. Кстати, еще одна мелкая ошибка в ShopCMS.
$product_related_number это ВСЕ рекомендуемые товары, а $product_related - только те, которые enabled=1.
т.е. если рекомендуемый товар один и он выключен, то будет показан

{if $product_related_number > 0}
file="header.tpl.html" header=$smarty.const.STRING_RELATED_ITEMS}

Хотя никто не мешал сделать

{if $product_related|@count}


Вообще странная идея одним SQL-запросом получать COUNT(*), а вторым список ProductID. Почему не получить список и посчитать количество элементов в массиве? Массив-то с гулькин нос.
  • 0
http://cpu.badisoft.ru (тестовый сайт), http://badisoft.ru (модули)

#66 Salp

    Продвинутый пользователь

  • Assistent vsupport.club
  • PipPipPip
  • 218 сообщений
Репутация: 54
Продвинутый

Отправлено 17 June 2017 - 02:36 PM

А баг-то в чем? Я не вижу ничего критичного во втором запросе.

Пока что я это подробно не выяснил, но исключением оптимизации, проблема была решена.
  • 0