Портал за которым я слежу на работе посещают в день десятки тысяч человек. Сайт специфичный, и требует серьезной работы с базой данных. Нагрузки на сервер соответствующие. Бывает, что проворачивая сложные sql запросы, mysql на долго вешал сервер и сайт был недоступен. особенно это ощущалось в пятницу вечером и в выходные, когда посещения сайта были максимальными. Мой администратор сказал, что все дело в кривых SQL запросах, и он со своей стороны все, что мог уже оптимизировал.
Я начал искать, и нашел. Расскажу, как я замерял скорость и опишу пару оптимизаций.
Для начала класс при помощи которого я замерял скорость выполнения SQL запросов.
class Timer{
private $start_time;
private function get_time(){
list($usec, $seconds) = explode(" ", microtime());
return ((float)$usec + (float)$seconds);
}
function start(){
return $this->start_time = $this->get_time();
}
function end($startTime=false){
return $this->get_time() - ((!$startTime)?$this->start_time:$startTime);
}
}
пользоваться им просто
$timer = new Timer();
$timer->start();
mysql_query('select ...');
echo 'времени ушло - '.$timer->end().'<br>';
Время выводиться в долях секунды. Я использую удобную надстройку над mysql, благодаря которой мне лишь в одном месте потребовалось разместить этот код. В методе query. Кроме того, встраиваем в класс метод, для логирования всех запросов.
private function logger($msg,$time){
$this->_logid and @file_put_contents(dirname(__FILE__).'/.mlog',$time.'-'.$msg."\n",FILE_APPEND);
}
и флаг
private $_logid = false;
для того, чтобы всегда можно было отключить логирование. Первым параметром подаем sql запрос, вторым время его выполнения. Потом будет наглядно видно, какие запросы занимают больше всего времени.
Теперь, расскажу немного об узких местах, которые я обнаружил в своем коде. Не запомнил по значительности, просто перечислю все по порядку.
select *. - если Вам из БД не требуется брать все поля, а нужно только одно, то его и нужно указывать место звездочки. Идеально во всез запросах перечислять все поля, которые Вам необходимы. Это добавит некий контроль.
запросы без limit - пожалуй самая серьезная оптимизация: добавить в конце всех запросов ограничение на количество полученных данных. Это касается select, update и delete запросов, в которых используется оператор where. К примеру если Вы удаляете запись по уникальному ключу, и она будет только одна, то целесообразно добавить в конце запроса limit 0,1;
Тоже и в select запросах, если таблица большая, то стоит ввести максимально необходимое число записей, и добавить в конце запроса limit 0,1000. Уверяю Вас, что в таблицах с несколькими тысячами строк, такой запрос отработает гораздо быстрее чем без ограничения.
Последняя оптимизация, которая мне попалась весьма специфичная.
INSERT INTO :last_sms: SET phone='484848484', region='1' on duplicate KEY UPDATE ldate='345345345'
Если записи с таким номером в БД нет, то она вставляется заново. Если же она уже существует, то у нее обновляется только дата обновления.
Оказалось, что два отдельных запроса
$data = $db->query("select id from sms where phone='484484484' limit 0,1");
if($data['id'])
$db->query("update sms set ldate='345345345' where phone='484484484' limit 0,1");
else
$db->query("INSERT INTO sms SET phone='484848484', region='1'");
работают в разы быстрее. Вот такие дела. Оптимизируем сайт, лечим от вирусов на Пролетарской, чистим, включаем всевозможные кешеры, и сайт начинает летать.
Комментарии
$t = microtime(true);
//Измеряемый код
echo 'времени ушло - '.round(microtime(true) - $t, 3);
А у меня именно эта инструкция в классе решает...
Пошёл скорость мерить...
Спасибо, коллега!
мелкий фикс, при delete нельзя указать офсет, поэтому должно быть просто LIMIT 1
>> Насчёт замера скорости
Учитывая, что mysql кэширует всё и везде, где только можно, то результаты будут весьма относительные, особенно по селектам + а на лайв сервере будут зависеть от нагрузки на сам сервер