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

PHP 测试数据库加速

wen PHP项目 87

本文目录导读：

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. 监控告警

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

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

版权声明：NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播

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

下一篇PHP CI中并行测试

相关文章

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

友情链接攻略窝 · 礼包窝 · 配置窝 · 换算窝 · 算法窝 · 查文窝 · 手册窝 · 问答窝 · 全文搜 · 手册库
相关站点礼包窝 · 查文窝