Пожалуй, самое важное, при оптимизации, контролировать свои результаты.
То есть, наблюдать за выполнением SQL-запросов движка.
Мне удалось найти несколько способов, как это сделать.
При возможности, я их все, перечислю.
Хочу сразу заметить, что одна из проблем, в том, что EXPLAIN выдает разные результаты, при одном и том же запросе.
Это зависит от многих факторов, не буду вдаваться в подробности, скажу только, что, для уменьшения погрешности значений времени выполнения, желательно делать больше замеров.
Предлагаю, для отладки запросов в базу, использовать небольшую модернизацию функции sql_query, которая позволит смотреть все SQL-запросы, время выполнения, использование индексов, и тд.
Естественно, делать это нужно не на работающем сайте, а на копии.
Открываем файл db/mysql4.php
Находим:
function sql_query($query = "", $transaction = FALSE)
{
//
// Remove any pre-existing queries
//
unset($this->query_result);
if( $query != "" )
{
$this->num_queries++;
if( $transaction == BEGIN_TRANSACTION && !$this->in_transaction )
{
$result = mysql_query("BEGIN", $this->db_connect_id);
if(!$result)
{
return false;
}
$this->in_transaction = TRUE;
}
$this->query_result = mysql_query($query, $this->db_connect_id);
}
else
{
if( $transaction == END_TRANSACTION && $this->in_transaction )
{
$result = mysql_query("COMMIT", $this->db_connect_id);
}
}
if( $this->query_result )
{
unset($this->row[$this->query_result]);
unset($this->rowset[$this->query_result]);
if( $transaction == END_TRANSACTION && $this->in_transaction )
{
$this->in_transaction = FALSE;
if ( !mysql_query("COMMIT", $this->db_connect_id) )
{
mysql_query("ROLLBACK", $this->db_connect_id);
return false;
}
}
return $this->query_result;
}
else
{
if( $this->in_transaction )
{
mysql_query("ROLLBACK", $this->db_connect_id);
$this->in_transaction = FALSE;
}
return false;
}
}
Меняем на:
function sql_query($query = "", $transaction = FALSE)
{
//
// Remove any pre-existing queries
//
unset($this->query_result);
if( $query != "" )
{
$this->num_queries++;
$this->evil .= "Start Query #" . $this->num_queries . "<br />\n" . htmlentities($query) . "<br />\n";
if(strtoupper(substr($query, 0, 6)) == "SELECT")
{
$x = mysql_query("EXPLAIN $query", $this->db_connect_id);
$z = array();
$this->evil .= "<table><tr><th>Table</th><th>Type</th> <th>Possible</th><th>Used</th> <th>Len</th><th>Ref</th> <th>Rows</th><th>Extra</th></tr>";
while($y = mysql_fetch_array($x, MYSQL_ASSOC))
{
$this->evil .= "<tr><td>" . $y['table'] . "</td><td>" . $y['type'] . "</td><td>" . $y['possible_keys'] . "</td><td>" . $y['key'] . "</td><td>" . $y['key_len'] . "</td><td>" . $y['ref'] . "</td><td>" . $y['rows'] . "</td><td>" . $y['Extra'] . "</td></tr>";
}
$this->evil .= "</table>";
$this->evil .= print_r($z, true);
mysql_free_result($x);
}
$start = microtime();
if( $transaction == BEGIN_TRANSACTION && !$this->in_transaction )
{
$result = mysql_query("BEGIN", $this->db_connect_id);
if(!$result)
{
return false;
}
$this->in_transaction = TRUE;
}
$this->query_result = mysql_query($query, $this->db_connect_id);
$end = microtime();
$this->evil .= "Time Taken = " . ($end - $start) . "<br /><br />\n";
}
else
{
if( $transaction == END_TRANSACTION && $this->in_transaction )
{
$result = mysql_query("COMMIT", $this->db_connect_id);
}
}
if( $this->query_result )
{
unset($this->row[$this->query_result]);
unset($this->rowset[$this->query_result]);
if( $transaction == END_TRANSACTION && $this->in_transaction )
{
$this->in_transaction = FALSE;
if ( !mysql_query("COMMIT", $this->db_connect_id) )
{
mysql_query("ROLLBACK", $this->db_connect_id);
return false;
}
}
return $this->query_result;
}
else
{
if( $this->in_transaction )
{
mysql_query("ROLLBACK", $this->db_connect_id);
$this->in_transaction = FALSE;
}
return false;
}
}
Открываем includes/page_tail.php
Находим:
'ADMIN_LINK' => $admin_link)
Меняем на:
'ADMIN_LINK' => $admin_link,
'DB_SPEED' => $db->evil)
Открываем overall_footer.tpl
Находим:
Меняем на:
Все.
Запускаем.
Теперь мы видим на странице, все выполненные запросы, и можем попытаться облегчить наиболее сложные.
Приведу пример, как с помощью вышеуказанного "отладчика" можно выловить неудачные запросы.
Возьмем к примеру, Attachment Mod от Acyd Burn.
Есть один странный запрос при просмотре темы.
Вот EXPLAIN в запросе темы с одним атачем:
SELECT a.post_id, d.* FROM phpbb_attachments a, phpbb_attachments_desc d WHERE a.post_id IN (256383) AND a.attach_id = d.attach_id ORDER BY d.filetime ASC
Возвращает:
Rows_sent: 1
Rows_examined: 4163
Possible: attach_id_post_id,attach_id_privmsgs_id
Used:
attach_id_post_id
Extra: where used; Using index; Using temporary; Using filesort
Time Taken = 0.018864
Самый медленный у меня запрос при просмотре темы !
Сканирует все записи из таблицы атачей
Причем, есть индекс attach_id, post_id
Но в запросе, последовательность post_id > attach_id
Получается, индекс не используется? Хотя пишет, что используется ...
Проверяю.
Удаляю индекс attach_id_post_id
Создаю новый индекс:
CREATE INDEX post_id_attach_id ON phpbb_attachments (post_id, attach_id);
То есть, с другой последовательноcтью.
Делаю EXPLAIN:
Rows_sent: 1
Rows_examined: 1
Possible: attach_id_privmsgs_id,post_id_attach_id
Used:
post_id_attach_id
Extra: where used; Using index; Using temporary; Using filesort
Time Taken = 0.002231
Вот так.
Теперь, видно, что индекс используется.
Скорость выборки увеличена почти в 10 раз.
Думаю, что при большом количестве атачей, разница в скорости была бы еще больше.
Всегда есть решение ...
).
