PHP项目怎样优化数据库查询性能

PHP项目数据库查询性能优化指南

索引优化

-- 为常用查询字段添加索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_user_id ON orders(user_id, created_at);
-- 复合索引要注意最左前缀原则
-- 下面索引适用于 WHERE user_id = ? AND status = ?
CREATE INDEX idx_order_user_status ON orders(user_id, status);

查询优化技巧

// 1. 选择性字段，避免 SELECT *
$sql = "SELECT id, name, email FROM users WHERE status = 1";
// 2. 使用 LIMIT 限制返回数量
$sql = "SELECT * FROM articles WHERE category_id = 5 LIMIT 10";
// 3. 避免在 WHERE 中使用函数
// 不推荐
$sql = "SELECT * FROM users WHERE DATE(created_at) = '2025-01-01'";
// 推荐
$sql = "SELECT * FROM users WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02'";
// 4. 使用 EXISTS 代替 IN（子查询较大时）
$sql = "SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
)";

使用查询缓存

// Redis 缓存示例
class QueryCache {
    private $redis;
    private $ttl = 300;
    public function get($key, $callback) {
        $cacheKey = 'query:' . $key;
        $data = $this->redis->get($cacheKey);
        if ($data === false) {
            $data = $callback();
            $this->redis->setex($cacheKey, $this->ttl, serialize($data));
        }
        return unserialize($data);
    }
}
// 使用示例
$users = $cache->get('users:active', function() {
    return DB::select("SELECT * FROM users WHERE status = 1");
});

数据库连接优化

// 1. 使用连接池（PDO 持久连接）
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 
    $user, $pass, [
        PDO::ATTR_PERSISTENT => true,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_STRINGIFY_FETCHES => false
    ]
);
// 2. 使用读写分离
class DatabaseManager {
    public function getConnection($isWrite = false) {
        if ($isWrite) {
            return $this->writeConnection;
        }
        return $this->readConnection; // 连接从库
    }
}

分页优化

// 使用游标分页替代 OFFSET 分页
// 不推荐
$sql = "SELECT * FROM orders ORDER BY id DESC LIMIT 10 OFFSET 100000";
// 推荐 - 记住最后一条记录的 ID
$sql = "SELECT * FROM orders 
        WHERE id < $lastId 
        ORDER BY id DESC 
        LIMIT 10";

避免N+1查询问题

// 不推荐 - N+1 查询
$users = User::all();
foreach ($users as $user) {
    $posts = $user->posts; // 每个用户都执行一次查询
}
// 推荐 - 使用 JOIN 或预加载
$users = DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->select('users.*', 'posts.title as post_title')
    ->get();

分表分库策略

// 根据用户ID进行分表
class UserRepository {
    public function getTableName($userId) {
        $tableIndex = $userId % 10;
        return "users_{$tableIndex}";
    }
    public function getById($userId) {
        $table = $this->getTableName($userId);
        $sql = "SELECT * FROM {$table} WHERE id = ?";
        return DB::select($sql, [$userId]);
    }
}

监控和日志

// 慢查询日志配置（MySQL）
// my.cnf 配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
// PHP 端监控
class QueryLogger {
    public static function logSlowQuery($sql, $params, $time) {
        if ($time > 1) { // 超过1秒的查询
            Log::warning('Slow query', [
                'sql' => $sql,
                'params' => $params,
                'time' => $time
            ]);
        }
    }
}

使用批量操作

// 批量插入
DB::table('users')->insert([
    ['name' => '张三', 'email' => 'zhangsan@example.com'],
    ['name' => '李四', 'email' => 'lisi@example.com'],
    // ...
]);
// 批量更新
DB::table('users')
    ->whereIn('id', [1, 2, 3])
    ->update(['status' => 1]);

优化数据库配置

# MySQL 配置优化
[mysqld]
innodb_buffer_pool_size = 4G
innodb_log_file_size = 256M
max_connections = 500
thread_cache_size = 50
query_cache_type = 1
query_cache_size = 128M

代码优化建议

// 1. 使用预处理语句避免 SQL 注入
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);
// 2. 延迟加载不需要的数据
class User {
    private $orders = null;
    public function getOrders() {
        if ($this->orders === null) {
            $this->orders = DB::select("SELECT * FROM orders WHERE user_id = ?");
        }
        return $this->orders;
    }
}

使用文件缓存策略

class DataCache {
    public static function remember($key, $ttl, $callback) {
        $file = storage_path("cache/{$key}.json");
        if (file_exists($file) && (time() - filemtime($file) < $ttl)) {
            return json_decode(file_get_contents($file), true);
        }
        $data = $callback();
        file_put_contents($file, json_encode($data));
        return $data;
    }
}

通过这些优化策略，可以显著提升 PHP 项目的数据库查询性能，关键是找到合适的优化方案,并持续监控和调优。