PHP面向对象开发：如何用对象映射数据库数据并减少查询次数？

问题背景 #

我正在设计首个复杂的PHP面向对象应用，对如何用对象表示数据库数据感到困惑，尤其不清楚如何组合多个对象同时避免大量数据库查询。

举个简单例子：我有两张数据库表：

products: - product_id
          - manufacturer_id
          - name
          - price

manufacturers: - manufacturer_id
               - name
               - factory_address
               - factory_zipcode
               - factory_country
               - // [50 additional columns]

用两个类来表示它们：

class Product {}
class Manufacturer {}

目前通过ProductFactory可以一次查询创建所有产品对象：

class ProductFactory {
    function allProducts() {
        $results = db_query("SELECT * FROM products");
        foreach ($results as $result) {
            $product_objects[] = new Product($result);
        }
        return $product_objects;
    }
}

但需要展示产品+制造商数据时，遇到了两难：

• 用JOIN查询一次性获取所有数据，但不够灵活，会加载无用列，且难以拆分独立对象；
• 延迟加载（比如$product->getManufacturer()->name）会导致N+1查询（1000个产品对应1000次制造商查询），性能极差，多层关联时问题更严重。

解决方案 #

你不需要在两种极端方案中二选一，以下几种设计模式和技巧可以兼顾灵活性与性能：

1. 预加载（Eager Loading）+ 批量查询 #

核心思路是先获取主数据，再批量关联关联数据，避免N+1查询。

修改ProductFactory，支持指定需要预加载的关联对象：

class ProductFactory {
    private $db;
    private $manufacturerFactory;

    public function __construct($db, ManufacturerFactory $manufacturerFactory) {
        $this->db = $db;
        $this->manufacturerFactory = $manufacturerFactory;
    }

    function allProducts(array $options = []) {
        // 1. 查询所有产品
        $results = $this->db->query("SELECT * FROM products");
        $products = [];
        foreach ($results as $result) {
            $products[] = new Product($result);
        }

        // 2. 如果需要预加载制造商，批量查询
        if (!empty($options['with']) && in_array('manufacturer', $options['with'])) {
            // 收集所有产品的manufacturer_id，去重
            $manufacturerIds = array_unique(array_map(function(Product $p) {
                return $p->getManufacturerId();
            }, $products));

            // 批量查询制造商（可指定需要的列）
            $manufacturers = $this->manufacturerFactory->getByIds($manufacturerIds, ['manufacturer_id', 'name']);
            $manufacturerMap = [];
            foreach ($manufacturers as $m) {
                $manufacturerMap[$m->getId()] = $m;
            }

            // 关联到产品对象
            foreach ($products as $product) {
                $product->setManufacturer($manufacturerMap[$product->getManufacturerId()] ?? null);
            }
        }

        return $products;
    }
}

// 对应的ManufacturerFactory
class ManufacturerFactory {
    private $db;

    public function __construct($db) {
        $this->db = $db;
    }

    function getByIds(array $ids, array $columns = ['manufacturer_id', 'name']) {
        if (empty($ids)) return [];
        $columnsStr = implode(',', $columns);
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $stmt = $this->db->prepare("SELECT $columnsStr FROM manufacturers WHERE manufacturer_id IN ($placeholders)");
        $stmt->execute($ids);
        $manufacturers = [];
        while ($row = $stmt->fetch()) {
            $manufacturers[] = new Manufacturer($row);
        }
        return $manufacturers;
    }
}

使用方式：

// 不需要制造商数据时
$products = $productFactory->allProducts();

// 需要制造商数据时
$products = $productFactory->allProducts(['with' => ['manufacturer']]);

这样只需要2次查询（产品+批量制造商），且可以按需选择是否加载关联数据，还能指定制造商需要的列（避免加载50个无用列）。

2. 延迟加载（Lazy Loading）+ 对象缓存池 #

如果确实需要按需加载关联对象，可以给关联对象的Factory添加缓存池，避免重复查询同一个对象。

class ManufacturerFactory {
    private $db;
    private $cache = [];

    public function __construct($db) {
        $this->db = $db;
    }

    function getById($id, array $columns = ['manufacturer_id', 'name']) {
        if (isset($this->cache[$id])) {
            return $this->cache[$id];
        }
        $columnsStr = implode(',', $columns);
        $stmt = $this->db->prepare("SELECT $columnsStr FROM manufacturers WHERE manufacturer_id = ?");
        $stmt->execute([$id]);
        $manufacturer = new Manufacturer($stmt->fetch());
        $this->cache[$id] = $manufacturer;
        return $manufacturer;
    }
}

// Product类中的getManufacturer方法
class Product {
    private $manufacturerId;
    private $manufacturer;
    private $manufacturerFactory;

    public function __construct($data, ManufacturerFactory $manufacturerFactory) {
        $this->manufacturerId = $data['manufacturer_id'];
        $this->manufacturerFactory = $manufacturerFactory;
    }

    public function getManufacturer() {
        if (!$this->manufacturer) {
            $this->manufacturer = $this->manufacturerFactory->getById($this->manufacturerId);
        }
        return $this->manufacturer;
    }
}

这种方式下，即使1000个产品属于同一个制造商，也只会查询1次，而不是1000次。如果有多个不同制造商，查询次数等于不同制造商的数量，远小于1000次。

3. 多层关联的预加载扩展 #

针对多层关联（比如Product -> Manufacturer -> Address），可以扩展预加载逻辑，支持嵌套关联：

// 修改ProductFactory的allProducts方法，支持嵌套预加载
function allProducts(array $options = []) {
    // ... 先查询产品 ...

    if (!empty($options['with'])) {
        foreach ($options['with'] as $relation) {
            if ($relation === 'manufacturer') {
                // ... 批量加载制造商 ...
            } elseif (strpos($relation, 'manufacturer.address') !== false) {
                // 先加载制造商
                $this->loadManufacturers($products);
                // 收集制造商的地址ID，批量加载地址
                $addressIds = array_unique(array_map(function(Manufacturer $m) {
                    return $m->getAddressId();
                }, array_column($products, 'manufacturer')));
                $addresses = $this->addressFactory->getByIds($addressIds);
                $addressMap = [];
                foreach ($addresses as $a) {
                    $addressMap[$a->getId()] = $a;
                }
                // 关联地址到制造商
                foreach ($products as $product) {
                    $manufacturer = $product->getManufacturer();
                    if ($manufacturer) {
                        $manufacturer->setAddress($addressMap[$manufacturer->getAddressId()] ?? null);
                    }
                }
            }
        }
    }

    return $products;
}

使用方式：

$products = $productFactory->allProducts(['with' => ['manufacturer.address']]);

这样只需要3次查询（产品→制造商→地址），完美解决多层嵌套的N+1问题。

内容的提问来源于stack exchange，提问作者Clanape