优化MySQL查询获取指定活动订单关联商品及用户信息（Laravel5.2）

现有订单表（Order）和商品表（items）为一对多关联（一个订单对应多个商品），表结构如下：

Order表 #

items表 #

需求：用单条优化后的MySQL查询，获取指定活动ID（比如1765）下的所有商品数据，同时关联对应订单的姓名、邮箱、电话，预期结果如下：

当前使用Laravel 5.2，求最优查询方案。

1. 原生SQL（最直接的底层优化） #

用INNER JOIN关联两张表，同时提前通过event_id过滤订单，避免全表扫描：

SELECT 
    o.name, 
    o.email, 
    i.category, 
    i.qty, 
    i.price
FROM 
    `Order` o
INNER JOIN 
    items i ON o.id = i.order_id
WHERE 
    o.event_id = 1765;

2. Laravel 5.2 查询构建器实现 #

用Laravel的查询构建器实现上述逻辑，兼顾可读性和性能：

$eventId = 1765;

// 注意：如果你的订单表名是`orders`（Laravel默认复数），就把`Order`改成`orders`
$results = DB::table('Order')
    ->join('items', 'Order.id', '=', 'items.order_id')
    ->select('Order.name', 'Order.email', 'items.category', 'items.qty', 'items.price')
    ->where('Order.event_id', $eventId)
    ->get();

3. 模型关联方式（符合Laravel规范，推荐） #

如果已经定义了Order和Item模型，用关联查询更优雅：

定义模型

• Order模型（app/Order.php）：

namespace App;

use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    protected $table = 'Order'; // 表名如果是orders可以省略
    protected $fillable = ['name', 'email', 'phone', 'event_id'];

    public function items()
    {
        return $this->hasMany(Item::class, 'order_id');
    }
}

• Item模型（app/Item.php）：

namespace App;

use Illuminate\Database\Eloquent\Model;

class Item extends Model
{
    protected $table = 'items';
    protected $fillable = ['order_id', 'category', 'qty', 'price'];

    public function order()
    {
        return $this->belongsTo(Order::class, 'order_id');
    }
}

两种关联查询方式

• 方式一：从订单模型出发
  先筛选指定活动的订单，再关联商品，最后整理成预期格式：

$eventId = 1765;

$items = Order::where('event_id', $eventId)
    ->select('id', 'name', 'email') // 只取需要的字段，减少数据传输
    ->with(['items' => function($query) {
        $query->select('order_id', 'category', 'qty', 'price');
    }])
    ->get()
    ->flatMap(function($order) {
        // 把每个订单的商品和订单信息合并
        return $order->items->map(function($item) use ($order) {
            return [
                'name' => $order->name,
                'email' => $order->email,
                'category' => $item->category,
                'qty' => $item->qty,
                'price' => $item->price
            ];
        });
    });

• 方式二：从商品模型出发（更高效）
  直接关联订单并筛选，避免集合层面的处理：

$eventId = 1765;

$items = Item::join('Order', 'items.order_id', '=', 'Order.id')
    ->where('Order.event_id', $eventId)
    ->select('Order.name', 'Order.email', 'items.category', 'items.qty', 'items.price')
    ->get();

性能优化要点 #

• 给Order表的event_id加索引：ALTER TABLE Order ADD INDEX idx_event_id (event_id);
• 给items表的order_id加索引：ALTER TABLE items ADD INDEX idx_order_id (order_id);
• 用INNER JOIN而非LEFT JOIN，因为我们只需要有对应订单的商品数据，减少无效行。

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