本文目录导读：
我来为您详细介绍PHP数据库追踪的几种实现方法：
使用SQL日志记录
简单SQL查询日志
class DatabaseLogger {
private $logFile;
public function __construct($logFile = 'sql_log.txt') {
$this->logFile = $logFile;
}
public function logQuery($sql, $params = [], $executionTime = 0) {
$logEntry = sprintf(
"[%s] SQL: %s | Params: %s | Time: %.4fs\n",
date('Y-m-d H:i:s'),
$sql,
json_encode($params),
$executionTime
);
file_put_contents($this->logFile, $logEntry, FILE_APPEND);
}
}
// 使用示例
$logger = new DatabaseLogger();
$start = microtime(true);
$result = $db->query("SELECT * FROM users WHERE id = ?", [1]);
$executionTime = microtime(true) - $start;
$logger->logQuery("SELECT * FROM users WHERE id = ?", [1], $executionTime);
PDO扩展监控
自定义PDO类实现追踪
class MonitoredPDO extends PDO {
private $queries = [];
public function query($query, $fetchMode = null, ...$fetchModeArgs) {
$start = microtime(true);
$result = parent::query($query, $fetchMode, ...$fetchModeArgs);
$executionTime = microtime(true) - $start;
$this->queries[] = [
'sql' => $query,
'time' => $executionTime,
'backtrace' => debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS)
];
return $result;
}
public function getQueries() {
return $this->queries;
}
public function getQueryCount() {
return count($this->queries);
}
public function getTotalExecutionTime() {
return array_sum(array_column($this->queries, 'time'));
}
}
// 使用示例
$db = new MonitoredPDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$db->query("SELECT * FROM users");
$db->query("INSERT INTO logs SET action='test'");
print_r($db->getQueries());
echo "Total queries: " . $db->getQueryCount();
echo "Total time: " . $db->getTotalExecutionTime();
数据库查询分析器
完整的查询追踪系统
class QueryAnalyzer {
private $queries = [];
private $slowQueryThreshold = 1.0; // 1秒
public function startQuery($sql, $params = []) {
$queryId = uniqid('query_');
$this->queries[$queryId] = [
'sql' => $sql,
'params' => $params,
'start_time' => microtime(true),
'backtrace' => debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 5)
];
return $queryId;
}
public function endQuery($queryId) {
if (!isset($this->queries[$queryId])) {
return;
}
$this->queries[$queryId]['end_time'] = microtime(true);
$this->queries[$queryId]['duration'] =
$this->queries[$queryId]['end_time'] - $this->queries[$queryId]['start_time'];
// 检测慢查询
if ($this->queries[$queryId]['duration'] > $this->slowQueryThreshold) {
$this->reportSlowQuery($this->queries[$queryId]);
}
}
private function reportSlowQuery($queryInfo) {
$message = sprintf(
"慢查询警告: %.4f秒\nSQL: %s\nParams: %s\n调用栈: %s",
$queryInfo['duration'],
$queryInfo['sql'],
json_encode($queryInfo['params']),
json_encode($queryInfo['backtrace'])
);
error_log($message);
// 可以发送邮件或记录到专门的慢查询日志
}
public function getStatistics() {
$stats = [
'total_queries' => count($this->queries),
'total_time' => 0,
'avg_time' => 0,
'slow_queries' => 0,
'fastest' => PHP_FLOAT_MAX,
'slowest' => 0
];
foreach ($this->queries as $query) {
if (isset($query['duration'])) {
$stats['total_time'] += $query['duration'];
$stats['fastest'] = min($stats['fastest'], $query['duration']);
$stats['slowest'] = max($stats['slowest'], $query['duration']);
if ($query['duration'] > $this->slowQueryThreshold) {
$stats['slow_queries']++;
}
}
}
if ($stats['total_queries'] > 0) {
$stats['avg_time'] = $stats['total_time'] / $stats['total_queries'];
}
return $stats;
}
}
// 使用示例
$analyzer = new QueryAnalyzer();
$queryId = $analyzer->startQuery("SELECT * FROM users WHERE id = ?", [1]);
// 执行查询...
$analyzer->endQuery($queryId);
print_r($analyzer->getStatistics());
数据库连接池监控
class ConnectionPoolMonitor {
private $connections = [];
private $maxConnections = 10;
public function createConnection($config) {
if (count($this->connections) >= $this->maxConnections) {
throw new Exception("达到最大连接数限制");
}
$connectionId = uniqid('conn_');
$this->connections[$connectionId] = [
'created_at' => microtime(true),
'config' => $config,
'queries' => 0,
'last_activity' => time()
];
return $connectionId;
}
public function trackQuery($connectionId) {
if (isset($this->connections[$connectionId])) {
$this->connections[$connectionId]['queries']++;
$this->connections[$connectionId]['last_activity'] = time();
}
}
public function closeConnection($connectionId) {
if (isset($this->connections[$connectionId])) {
$info = $this->connections[$connectionId];
$lifetime = microtime(true) - $info['created_at'];
// 记录连接使用情况
error_log(sprintf(
"连接 %s 关闭: %d 查询, 存活 %.2f 秒",
$connectionId,
$info['queries'],
$lifetime
));
unset($this->connections[$connectionId]);
}
}
public function getStatus() {
return [
'active_connections' => count($this->connections),
'total_queries' => array_sum(array_column($this->connections, 'queries')),
'max_connections' => $this->maxConnections
];
}
}
使用第三方工具
集成 Xdebug 追踪
// php.ini 配置
/*
xdebug.mode = trace
xdebug.trace_output_dir = /var/log/xdebug
xdebug.trace_options = 1
*/
// 代码中启用
xdebug_start_trace('/var/log/xdebug/app_trace.xt');
// 执行数据库操作...
xdebug_stop_trace();
使用 Query Monitor (WordPress)
// 在WordPress开发环境中使用
if (defined('WP_DEBUG') && WP_DEBUG) {
// Query Monitor 插件会自动追踪所有SQL查询
// 并通过工具栏显示
}
完整的追踪中间件示例
class DatabaseTracingMiddleware {
private $db;
private $logHandler;
private $monitor;
public function __construct($db, $logHandler = null) {
$this->db = $db;
$this->logHandler = $logHandler ?: new FileLogger();
$this->monitor = new PerformanceMonitor();
}
public function query($sql, $params = []) {
$queryId = $this->monitor->startTracking();
try {
$start = microtime(true);
$result = $this->db->query($sql, $params);
$duration = microtime(true) - $start;
// 记录查询
$this->logHandler->log([
'sql' => $sql,
'params' => $params,
'duration' => $duration,
'memory' => memory_get_usage(),
'timestamp' => date('Y-m-d H:i:s')
]);
// 追踪性能
$this->monitor->endTracking($queryId, $duration);
// 慢查询告警
if ($duration > 1.0) {
$this->sendSlowQueryAlert($sql, $duration);
}
return $result;
} catch (Exception $e) {
$this->logHandler->error([
'sql' => $sql,
'params' => $params,
'error' => $e->getMessage(),
'timestamp' => date('Y-m-d H:i:s')
]);
throw $e;
}
}
private function sendSlowQueryAlert($sql, $duration) {
// 发送邮件、Slack通知等
$message = "慢查询警告: {$sql} " . "耗时: {$duration}秒";
// 实现具体通知逻辑
}
}
// 使用
$tracker = new DatabaseTracingMiddleware($db);
$users = $tracker->query("SELECT * FROM users WHERE status = ?", ['active']);
最佳实践建议
- 生产环境谨慎使用：过多的日志可能影响性能
- 定期清理日志：设置日志轮转策略
- 分级监控：开发环境全量记录，生产环境只记录慢查询和错误
- 使用索引：确保被追踪的查询使用合适的索引
- 异步记录：考虑使用消息队列异步处理日志
- 隐私保护：不要在日志中记录敏感数据
这些方法可以根据您的具体需求组合使用,实现对数据库操作的全面追踪和监控。
