PHP 预加载关联防N 1

wen PHP项目 2026-08-11 161

本文目录导读：

PHP 预加载关联防N 1

原生 SQL JOIN 预加载
使用 IN 子查询预加载
使用 ORM 的预加载功能
通用预加载工厂类
延迟预加载（Lazy Loading）
批量优化技巧
性能比较和选择建议
选择建议

在 PHP 中处理关联数据的 N+1 问题，主要有以下几种预加载策略,我来详细介绍各种方案：

原生 SQL JOIN 预加载

// 问题代码（N+1）
$users = User::findAll();
foreach ($users as $user) {
    $orders = $user->orders(); // 每次循环都查询数据库
}
// 解决方案：JOIN 查询
$sql = "SELECT u.*, o.* 
        FROM users u 
        INNER JOIN orders o ON o.user_id = u.id 
        WHERE u.status = 'active'";
$result = $db->query($sql)->fetchAll();

使用 IN 子查询预加载

// 第一步：获取所有用户
$users = $db->query("SELECT * FROM users WHERE status = 'active'")->fetchAll();
// 第二步：收集所有用户ID
$userIds = array_column($users, 'id');
// 第三步：一次性查询所有订单
if (!empty($userIds)) {
    $placeholders = implode(',', array_fill(0, count($userIds), '?'));
    $orders = $db->prepare("SELECT * FROM orders WHERE user_id IN ($placeholders)")
                  ->execute($userIds)
                  ->fetchAll();
    // 第四步：按用户ID分组
    $ordersByUser = [];
    foreach ($orders as $order) {
        $ordersByUser[$order['user_id']][] = $order;
    }
    // 第五步：关联到用户
    foreach ($users as &$user) {
        $user['orders'] = $ordersByUser[$user['id']] ?? [];
    }
}

使用 ORM 的预加载功能

Laravel 示例

// Laravel Eloquent 预加载
$users = User::with('orders', 'profile', 'posts.comments')
              ->where('active', true)
              ->get();
// 条件预加载
$users = User::with(['orders' => function($query) {
    $query->where('status', 'completed');
}])->get();

Doctrine 示例

// Annotation 方式
#[Entity]
class User {
    /**
     * @OneToMany(targetEntity="Order", mappedBy="user", fetch="EAGER")
     */
    private $orders;
}
// 或者查询时指定
$query = $em->createQuery('SELECT u, o FROM User u JOIN u.orders o');
$users = $query->getResult();

通用预加载工厂类

class Preloader {
    private $db;
    private $relations = [];
    public function __construct($db) {
        $this->db = $db;
    }
    // 添加预加载关系
    public function with($name, $foreignKey, $localKey, $table, $select = '*') {
        $this->relations[] = [
            'name' => $name,
            'foreignKey' => $foreignKey,
            'localKey' => $localKey,
            'table' => $table,
            'select' => $select
        ];
        return $this;
    }
    // 执行预加载
    public function load(&$items) {
        if (empty($items)) return;
        $ids = array_map(function($item) {
            return $item['id'];
        }, $items);
        foreach ($this->relations as $relation) {
            $idsString = implode(',', $ids);
            $sql = "SELECT * FROM {$relation['table']} 
                    WHERE {$relation['foreignKey']} IN ($idsString)";
            $relatedItems = $this->db->query($sql)->fetchAll();
            $groupedItems = [];
            foreach ($relatedItems as $item) {
                $key = $item[$relation['foreignKey']];
                $groupedItems[$key][] = $item;
            }
            foreach ($items as &$item) {
                $item[$relation['localKey']] = 
                    $groupedItems[$item['id']] ?? null;
            }
        }
    }
}
// 使用示例
$users = $db->query("SELECT * FROM users LIMIT 100")->fetchAll();
$preloader = new Preloader($db);
$preloader->with('orders', 'user_id', 'id', 'orders')
          ->with('profile', 'user_id', 'id', 'profiles')
          ->load($users);

延迟预加载（Lazy Loading）

class LazyLoader {
    private $db;
    private $loaded = [];
    public function __construct($db) {
        $this->db = $db;
    }
    // 使用魔术方法处理属性访问
    public function __get($name) {
        if ($name === 'orders' && !isset($this->loaded['orders'])) {
            $this->loadOrders();
        }
        return $this->loaded[$name] ?? null;
    }
    private function loadOrders() {
        $ids = $this->getAllIds();
        // 批量加载
        $query = "SELECT * FROM orders WHERE user_id IN (" . 
                 implode(',', $ids) . ")";
        $result = $this->db->query($query);
        // 缓存结果
        foreach ($result as $row) {
            $this->loaded['orders'][$row['user_id']][] = $row;
        }
    }
}

批量优化技巧

class QueryOptimizer {
    // 分批预加载（避免 IN 子句过长）
    public function chunkLoad($items, $chunkSize = 500) {
        $chunks = array_chunk($items, $chunkSize);
        $results = [];
        foreach ($chunks as $chunk) {
            $ids = array_column($chunk, 'id');
            $results = array_merge($results, $this->queryByBatch($ids));
        }
        return $results;
    }
    // 缓存查询结果
    public function cachedPreload($key, $callback, $ttl = 3600) {
        // 使用 Redis/Memcached 缓存
        global $cache;
        if ($data = $cache->get($key)) {
            return unserialize($data);
        }
        $data = $callback();
        $cache->set($key, serialize($data), $ttl);
        return $data;
    }
}

性能比较和选择建议

// 性能测试基准
class PerformanceTest {
    public static function benchmark() {
        $tests = [
            'N+1' => function() {
                // 1000次查询
                $users = getUsers();
                foreach ($users as $user) {
                    getOrdersByUser($user['id']);
                }
            },
            'JOIN' => function() {
                // 1次查询
                $joinQuery = "SELECT * FROM users u 
                              JOIN orders o ON o.user_id = u.id";
            },
            'IN_预加载' => function() {
                // 2次查询
                $users = getUsers();
                $userIds = array_column($users, 'id');
                getOrdersByUserIds($userIds);
            },
            'ORM预加载' => function() {
                // 2次查询
                $users = User::with('orders')->get();
            }
        ];
        foreach ($tests as $name => $test) {
            $start = microtime(true);
            $test();
            echo "$name: " . (microtime(true) - $start) * 1000 . "ms\n";
        }
    }
}