IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

PHP 测试数据库加速

wen PHP项目 2

本文目录导读：

PHP 测试数据库加速

  1. 基础层优化
  2. 缓存层优化
  3. 查询优化
  4. 连接复用与预编译
  5. 读写分离
  6. 测试基准脚本
  7. 监控与日志
  8. 最佳实践建议

我来为您提供多种PHP数据库加速方案，从简单到复杂,涵盖不同场景。

基础层优化

PDO 连接池优化

<?php
class DatabasePool {
    private static $connections = [];
    private static $maxConnections = 10;
    public static function getConnection($config) {
        $key = md5(json_encode($config));
        // 清理过期连接
        if (isset(self::$connections[$key])) {
            foreach (self::$connections[$key] as $index => $conn) {
                if ($conn['expire'] < time()) {
                    unset(self::$connections[$key][$index]);
                }
            }
        }
        // 获取可用连接
        if (!empty(self::$connections[$key])) {
            foreach (self::$connections[$key] as &$conn) {
                if ($conn['in_use'] === false) {
                    $conn['in_use'] = true;
                    return $conn['pdo'];
                }
            }
        }
        // 创建新连接
        $pdo = new PDO(
            "mysql:host={$config['host']};dbname={$config['dbname']};charset=utf8mb4",
            $config['username'],
            $config['password'],
            [
                PDO::ATTR_PERSISTENT => true,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_STRINGIFY_FETCHES => false
            ]
        );
        self::$connections[$key][] = [
            'pdo' => $pdo,
            'in_use' => true,
            'expire' => time() + 3600
        ];
        return $pdo;
    }
    public static function releaseConnection($pdo) {
        foreach (self::$connections as &$connections) {
            foreach ($connections as &$conn) {
                if ($conn['pdo'] === $pdo) {
                    $conn['in_use'] = false;
                    return true;
                }
            }
        }
        return false;
    }
}

缓存层优化

Redis 缓存助手

<?php
class CacheManager {
    private static $redis = null;
    private static $cacheEnabled = true;
    public static function init() {
        if (self::$redis === null && class_exists('Redis')) {
            self::$redis = new Redis();
            self::$redis->connect('127.0.0.1', 6379);
            self::$redis->setOption(Redis::OPT_SERIALIZER, Redis::SERIALIZER_PHP);
        }
    }
    public static function get($key, $callback, $ttl = 300) {
        if (!self::$cacheEnabled || !self::$redis) {
            return $callback();
        }
        $data = self::$redis->get($key);
        if ($data === false) {
            $data = $callback();
            self::$redis->setex($key, $ttl, $data);
        }
        return $data;
    }
    public static function delete($key) {
        if (self::$redis) {
            self::$redis->del($key);
        }
    }
}
// 使用示例
$users = CacheManager::get('users:top10', function() use ($pdo) {
    $stmt = $pdo->query("SELECT * FROM users ORDER BY score DESC LIMIT 10");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}, 600);

查询优化

查询构建器优化

<?php
class QueryOptimizer {
    private $pdo;
    private $queryLog = [];
    public function __construct($pdo) {
        $this->pdo = $pdo;
        $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $this->pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
    }
    // 带索引提示的查询
    public function queryWithHint($sql, $params = [], $index = null) {
        if ($index && strpos($sql, 'USE INDEX') === false) {
            preg_match('/FROM\s+(\w+)/i', $sql, $matches);
            if (isset($matches[1])) {
                $sql = str_replace(
                    "FROM {$matches[1]}",
                    "FROM {$matches[1]} USE INDEX ($index)",
                    $sql
                );
            }
        }
        $start = microtime(true);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $this->queryLog[] = [
            'sql' => $sql,
            'time' => microtime(true) - $start,
            'rows' => count($result)
        ];
        return $result;
    }
    // 分页优化
    public function paginate($table, $page = 1, $perPage = 20, $conditions = [], $orderBy = 'id DESC') {
        $offset = ($page - 1) * $perPage;
        // 优化大偏移量查询
        if ($offset > 10000) {
            $sql = "SELECT * FROM $table WHERE id > 
                    (SELECT id FROM $table ORDER BY id LIMIT $offset, 1) 
                    ORDER BY id LIMIT $perPage";
        } else {
            $sql = "SELECT * FROM $table ORDER BY $orderBy LIMIT $offset, $perPage";
        }
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }
    // 批量插入优化
    public function bulkInsert($table, $data, $batchSize = 500) {
        $totalChunks = array_chunk($data, $batchSize);
        $affectedRows = 0;
        foreach ($totalChunks as $chunk) {
            $columns = array_keys($chunk[0]);
            $placeholders = [];
            $values = [];
            foreach ($chunk as $row) {
                $rowPlaceholders = [];
                foreach ($columns as $column) {
                    $rowPlaceholders[] = '?';
                    $values[] = $row[$column];
                }
                $placeholders[] = '(' . implode(',', $rowPlaceholders) . ')';
            }
            $sql = "INSERT INTO $table (" . implode(',', $columns) . ") VALUES " .
                   implode(',', $placeholders);
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($values);
            $affectedRows += $stmt->rowCount();
        }
        return $affectedRows;
    }
    // 常用查询结果缓存
    public function cachedQuery($key, $sql, $params = [], $ttl = 300) {
        return CacheManager::get("query:$key", function() use ($sql, $params) {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }, $ttl);
    }
}

连接复用与预编译

预处理语句池

<?php
class StatementPool {
    private static $statements = [];
    public static function getStatement($pdo, $sql) {
        $hash = md5($sql);
        if (isset(self::$statements[$hash])) {
            return self::$statements[$hash];
        }
        $stmt = $pdo->prepare($sql);
        self::$statements[$hash] = $stmt;
        return $stmt;
    }
    public static function clear() {
        self::$statements = [];
    }
}
// 使用示例
$sql = "SELECT * FROM users WHERE age > ? AND status = ?";
$stmt = StatementPool::getStatement($pdo, $sql);
$stmt->execute([25, 'active']);
$users = $stmt->fetchAll();

读写分离

主从数据库配置

<?php
class DatabaseRouter {
    private $master;
    private $slaves = [];
    private static $instance = null;
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    public function setup($masterConfig, $slaveConfigs) {
        $this->master = $this->createConnection($masterConfig);
        foreach ($slaveConfigs as $slaveConfig) {
            $this->slaves[] = $this->createConnection($slaveConfig);
        }
    }
    private function createConnection($config) {
        $pdo = new PDO(
            "mysql:host={$config['host']};dbname={$config['dbname']};charset=utf8mb4",
            $config['username'],
            $config['password'],
            [
                PDO::ATTR_TIMEOUT => 3,
                PDO::ATTR_PERSISTENT => true
            ]
        );
        return $pdo;
    }
    public function getReadConnection() {
        if (empty($this->slaves)) {
            return $this->master;
        }
        // 简单的负载均衡
        $index = array_rand($this->slaves);
        return $this->slaves[$index];
    }
    public function getWriteConnection() {
        return $this->master;
    }
    public function query($sql, $params = [], $isWrite = false) {
        $connection = $isWrite ? $this->getWriteConnection() : $this->getReadConnection();
        $stmt = $connection->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
}

测试基准脚本

<?php
// benchmark.php
class DatabaseBenchmark {
    private $startTime;
    private $queries = [];
    public function start() {
        $this->startTime = microtime(true);
    }
    public function addQuery($sql, $time) {
        $this->queries[] = [
            'sql' => $sql,
            'time' => $time
        ];
    }
    public function getReport() {
        $totalTime = microtime(true) - $this->startTime;
        $avgTime = $totalTime / count($this->queries);
        return [
            'total_time' => $totalTime,
            'average_time' => $avgTime,
            'total_queries' => count($this->queries),
            'fastest' => min(array_column($this->queries, 'time')),
            'slowest' => max(array_column($this->queries, 'time'))
        ];
    }
}
// 测试示例
$benchmark = new DatabaseBenchmark();
$benchmark->start();
// 测试普通查询 vs 优化查询
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
// 普通查询
$start = microtime(true);
for ($i = 0; $i < 1000; $i++) {
    $stmt = $pdo->query("SELECT * FROM users WHERE id = " . rand(1, 10000));
    $stmt->fetch();
}
$benchmark->addQuery("直接查询", microtime(true) - $start);
// 预处理查询
$start = microtime(true);
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
for ($i = 0; $i < 1000; $i++) {
    $stmt->execute([rand(1, 10000)]);
    $stmt->fetch();
}
$benchmark->addQuery("预处理查询", microtime(true) - $start);
// 带缓存查询
$start = microtime(true);
for ($i = 0; $i < 1000; $i++) {
    $id = rand(1, 10000);
    $result = CacheManager::get("user:$id", function() use ($pdo, $id) {
        $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }, 600);
}
$benchmark->addQuery("缓存查询", microtime(true) - $start);
print_r($benchmark->getReport());

监控与日志

<?php
class QueryMonitor {
    private static $slowQueries = [];
    public static function logQuery($sql, $time, $params = []) {
        if ($time > 1.0) { // 慢查询阈值 1秒
            self::$slowQueries[] = [
                'sql' => $sql,
                'time' => $time,
                'params' => $params,
                'timestamp' => date('Y-m-d H:i:s'),
                'backtrace' => debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS)
            ];
            if (count(self::$slowQueries) > 100) {
                self::saveReport();
            }
        }
    }
    private static function saveReport() {
        $filename = __DIR__ . '/slow_queries_' . date('Y-m-d') . '.log';
        file_put_contents($filename, json_encode(self::$slowQueries, JSON_PRETTY_PRINT), FILE_APPEND);
        self::$slowQueries = [];
    }
}

最佳实践建议

  1. 索引优化

    • 为常用查询字段加索引
    • 使用复合索引

  2. 查询优化

    • 避免SELECT *
    • 使用LIMIT限制结果集
    • 避免在WHERE中使用函数

  3. 缓存策略

    • 热点数据缓存到Redis
    • 使用缓存穿透保护
    • 设置合理的过期时间

  4. 连接管理

    • 使用持久连接
    • 合理配置连接池大小

  5. 监控告警

    • 记录慢查询
    • 监控数据库负载
    • 实施自动优化

这些方案可以根据您的具体需求组合使用，从简单到复杂逐步实施，建议先用基准测试脚本评估当前性能,找出瓶颈后再针对性优化。

上一篇PHP 怎么减少测试时间

下一篇PHP CI中并行测试

相关文章

抱歉，评论功能暂时关闭!