Created
February 19, 2011 18:19
-
-
Save julp/835239 to your computer and use it in GitHub Desktop.
Profiling MySQL/PDO queries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace Julp; | |
if (isset($_SERVER['PHP_ENV']) && $_SERVER['PHP_ENV'] == 'development' && function_exists('ob_tidyhandler')) { | |
/* | |
tidy.default_config is: | |
indent: true | |
indent-spaces: 4 | |
output-xhtml: yes | |
wrap: 0 | |
tidy-mark: no | |
input-encoding: utf8 | |
output-bom: no | |
output-encoding: utf8 | |
*/ | |
ob_start('ob_tidyhandler'); | |
} | |
//use \PDO; | |
use \Exception; | |
require_once('pdo_constantes.php'); | |
function h($text) { | |
return htmlspecialchars($text, ENT_QUOTES, 'UTF-8'); | |
} | |
class Timer | |
{ | |
protected $_precision; | |
protected $_start_time; | |
protected $_stop_time; | |
public function __construct($precision = 5) { | |
$this->_precision = $precision; | |
} | |
public function start() { | |
$this->_start_time = microtime(TRUE); | |
return $this; | |
} | |
public function stop() { | |
$this->_stop_time = microtime(TRUE); | |
return $this; | |
} | |
public function getTime() { | |
return round($this->_stop_time - $this->_start_time, $this->_precision); | |
} | |
public function getPrecision() { | |
return $this->_precision; | |
} | |
} | |
class Query_Execution | |
{ | |
protected $_query; | |
protected $_id; | |
public function __construct(Query $query, $id) { | |
$this->_query = $query; | |
$this->_id = $id; | |
} | |
public function getQuery() { | |
return $this->_query; | |
} | |
public function getId() { | |
return $this->_id; | |
} | |
} | |
class Query implements \Countable, \IteratorAggregate | |
{ | |
protected $_query_string; | |
protected $_executions = array(); | |
public function __construct($query_string) { | |
$this->_query_string = $query_string; | |
} | |
public function addQueryExecution($id) { | |
$this->_executions[] = new Query_Execution($this, $id); | |
} | |
public function count() { | |
return count($this->_executions); | |
} | |
public function getIterator() { | |
return new \ArrayIterator($this->_executions); | |
} | |
public function getQueryString() { | |
return $this->_query_string; | |
} | |
} | |
class PDO/*_MySQL_Profiler*/ extends \PDO implements \Countable, \IteratorAggregate | |
{ | |
protected $_counter = 0; | |
protected $_queries = array(); | |
public function __construct($dsn, $username = NULL, $password = NULL, $driver_options = array()) { | |
parent::__construct($dsn, $username, $password, $driver_options); | |
parent::setAttribute(self::ATTR_STATEMENT_CLASS, array(__NAMESPACE__ . '\PDOStatement', array($this))); | |
parent::exec('SET SESSION query_cache_type = OFF, PROFILING = 1'); | |
} | |
public function setAttribute($attribute, $value) { | |
/*if (defined('__NAMESPACE__') && $attribute == self::ATTR_STATEMENT_CLASS) { | |
if ($value[0][0] != '\\') { | |
$value[0] = __NAMESPACE__ . '\\' . $value[0]; | |
} | |
}*/ | |
if ($attribute == self::ATTR_STATEMENT_CLASS) { | |
throw new LogicException('PDO::ATTR_STATEMENT_CLASS overwrite forbidden: a specific class is already in place.'); | |
} | |
return parent::setAttribute($attribute, $value); | |
} | |
public function _query($statement) { | |
if (!isset($this->_queries[$statement])) { | |
$this->_queries[$statement] = new Query($statement); | |
} | |
return $this->_queries[$statement]; | |
} | |
public function _register_query_execution($statement) { | |
$this->_query($statement)->addQueryExecution(++$this->_counter); | |
} | |
public function exec($statement) { | |
$affected = parent::exec($statement); | |
$this->_register_query_execution($statement); | |
return $affected; | |
} | |
public function query($statement) { | |
$args = func_get_args(); | |
if (func_num_args() >= 3) { | |
if ($args[1] == self::FETCH_CLASS) { | |
if ($args[2][0] != '\\') { | |
$args[2] = __NAMESPACE__ . '\\' . $args[2]; | |
} | |
} | |
} | |
$stmt = call_user_func_array(array(get_parent_class(), __FUNCTION__), $args); | |
$this->_register_query_execution($statement); | |
return $stmt; | |
} | |
public function prepare($statement, $driver_options = array()) { | |
$this->_query($statement); | |
return parent::prepare($statement, $driver_options); | |
} | |
public function count() { | |
return $this->_counter; | |
} | |
public function getIterator() { | |
return new \ArrayIterator($this->_queries); | |
} | |
public function __destruct() { | |
parent::exec('SET PROFILING = 0'); | |
$prof = parent::query('SHOW PROFILES', self::FETCH_OBJ); | |
if ($prof->rowCount()) { | |
echo '<ul>'; | |
$queries = $prof->fetchAll(PDO::FETCH_UNIQUE); | |
foreach ($this->_queries as $q) { | |
echo '<li>'; | |
$duration = 0; | |
foreach ($q as $qe) { | |
$duration += $queries[$qe->getId()]->Duration; | |
} | |
echo '<p>', h($q->getQueryString()), ' Executed : ', count($q), ' times', ' Total duration: ', $duration, ' Average duration: ', $duration / count($q), '</p>'; | |
echo '<ul>'; | |
foreach ($q as $qe) { | |
$qp = $queries[$qe->getId()]; | |
echo '<li>', $qe->getId(), ' : ', h($qp->Query), ' ', $qp->Duration, '</li>'; | |
} | |
echo '</ul>'; | |
echo '</li>'; | |
} | |
echo '</ul>'; | |
} | |
/* | |
$prof = parent::query('SHOW PROFILES', self::FETCH_OBJ); | |
if ($prof->rowCount()) { | |
$queryprof = parent::prepare('SHOW PROFILE SOURCE FOR QUERY :id'); | |
echo '<ul>'; | |
foreach ($prof as $q) { | |
//printf('<li>%s : %d exécution(s)</li>', $q->getQueryString(), count($q)); | |
printf('<li>%d : %s (%f)</li>', $q->Query_ID, $q->Query, $q->Duration); | |
//echo '<ul>'; | |
//foreach ($q as $qe) { | |
//printf('<li>ID = %d, time = %f</li>', $qe->getId(), $qe->getTime()); | |
//$queryprof->bindValue('id', $qe->getId(), self::PARAM_INT); | |
$queryprof->bindValue('id', intval($q->Query_ID), self::PARAM_INT); | |
$queryprof->execute(); | |
//$queryprof->setFetchMode(PDO::FETCH_OBJ); | |
printf('<table class="profile" id="P%d">', $q->Query_ID); | |
echo '<thead>'; | |
echo '<tr>'; | |
echo '<th>Status</th>'; | |
echo '<th>Duration</th>'; | |
echo '<th>Source function</th>'; | |
echo '<th>Source file</th>'; | |
echo '<th>Source line</th>'; | |
echo '</tr>'; | |
echo '</thead>'; | |
echo '<tbody>'; | |
foreach ($queryprof as $p) { | |
echo '<tr>'; | |
echo '<td>', $p->Status, '</td>'; | |
echo '<td>', $p->Duration, '</td>'; | |
echo '<td>', $p->Source_function, '</td>'; | |
echo '<td>', $p->Source_file, '</td>'; | |
echo '<td>', $p->Source_line, '</td>'; | |
echo '</tr>'; | |
} | |
echo '</tbody>'; | |
echo '</table>'; | |
//} | |
//echo '</ul>'; | |
} | |
echo '</ul>'; | |
}*/ | |
} | |
} | |
class PDOStatement/*_MySQL_Profiler*/ extends \PDOStatement | |
{ | |
protected $_pdo; | |
protected function __construct(PDO $pdo) { | |
$this->_pdo = $pdo; | |
} | |
public function execute($input_parameters = NULL) { | |
$bool = parent::execute($input_parameters); | |
$this->_pdo->_register_query_execution($this->queryString); | |
return $bool; | |
} | |
public function fetchObject($class_name = '\stdClass', $ctor_args = array()) { | |
if ($class_name[0] != '\\') { | |
$class_name = __NAMESPACE__ . '\\' . $class_name; | |
} | |
return parent::fetchObject($class_name, $ctor_args); | |
} | |
public function setFetchMode($mode, $class_name = NULL) { | |
if ($mode == \PDO::FETCH_CLASS) { | |
$ctor_args = func_num_args() >= 3 ? func_get_arg(2) : array(); | |
if ($class_name[0] != '\\') { | |
$class_name = __NAMESPACE__ . '\\' . $class_name; | |
} | |
return parent::setFetchMode($mode, $class_name, $ctor_args); | |
} else { | |
return call_user_func_array(array(get_parent_class(), __FUNCTION__), func_get_args()); | |
} | |
} | |
} | |
/* TESTING */ | |
//function var_dump() {} | |
try { | |
$dbh = new PDO(DSN, LOGIN, MOT_DE_PASSE); | |
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); | |
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ); | |
/*$dbh->exec('SET SESSION query_cache_type = OFF'); | |
$dbh->exec('SET PROFILING = 1');*/ | |
$utils = $dbh->prepare('SELECT COUNT(*) FROM utilisateurs WHERE id = :id'); | |
$utils->bindParam('id', $id, PDO::PARAM_INT); | |
$id = 3; | |
$utils->execute(); | |
$utils->fetch(); | |
$dbh->exec('UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto"'); | |
$id = 6; | |
$utils->execute(); | |
$utils->fetch(); | |
$dbh->exec('UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto"'); | |
$id = 1; | |
$utils->execute(); | |
$utils->fetch(); | |
} catch (Exception $e) { | |
die(sprintf("%s dans %s à la ligne %d : %s", get_class($e), $e->getFile(), $e->getLine(), $e->getMessage())); | |
} | |
/* | |
Output: | |
* SELECT COUNT(*) FROM utilisateurs WHERE id = :id Executed : 3 times Total duration: 0.0004075 Average duration: 0.00013583333333333 | |
o 1 : SELECT COUNT(*) FROM utilisateurs WHERE id = 3 0.00019925 | |
o 3 : SELECT COUNT(*) FROM utilisateurs WHERE id = 6 0.00010900 | |
o 5 : SELECT COUNT(*) FROM utilisateurs WHERE id = 1 0.00009925 | |
* UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto" Executed : 2 times Total duration: 0.0002645 Average duration: 0.00013225 | |
o 2 : UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto" 0.00015250 | |
o 4 : UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto" 0.00011200 | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment