本文目录导读：
在 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";
}
}
}
选择建议
- 数据量小（<100）：直接使用 JOIN 即可
- 数据量大：使用 IN 子句分批预加载
- 使用框架：优先使用 ORM 自带的预加载功能
- 缓存命中率高：使用缓存 + 预加载组合
- 嵌套关联：递归预加载关联的关联
这些方法可以根据具体场景灵活运用，组合使用效果最佳，预加载的核心思想就是将多个查询合并为尽可能少的查询,同时避免重复数据。
