合并MySQL查询提升性能：整合3个查询为含额外字段的查询

问题背景 #

现有三个关联查询：主查询获取service_material相关数据，遍历主查询结果时，额外执行两个查询分别获取lastExchanged（同物料、同位置的最新更换记录）和lastExchangedGroup（同物料组、同位置的最新更换记录）的exchange字段。需将三个查询合并为一个，直接返回包含这两个额外字段的结果集，避免N+1查询的性能问题。

纯SQL合并方案 #

通过关联子查询直接在主查询中嵌入两个子查询，分别获取所需的exchange值：

SELECT 
    sm.*,
    -- 获取lastExchanged：同charge_number/typ/year、同service_article_id、同install_location的最新exchange（排除当前operation）
    (SELECT sm2.exchange
     FROM service_material sm2
     JOIN service_operation so2 ON sm2.service_operation_id = so2.id
     WHERE so2.charge_number = so.charge_number
       AND so2.typ = so.typ
       AND so2.year = so.year
       AND sm2.service_article_id = sm.service_article_id
       AND sm2.install_location = sm.install_location
       AND sm2.exchange IS NOT NULL
       AND so2.id != so.id
     ORDER BY sm2.exchange DESC
     LIMIT 1) AS lastExchanged,
    -- 获取lastExchangedGroup：同charge_number/typ/year、同service_article.group、同install_location的最新exchange（排除当前operation和当前物料）
    (SELECT sm3.exchange
     FROM service_material sm3
     JOIN service_operation so3 ON sm3.service_operation_id = so3.id
     JOIN service_article sa3 ON sm3.service_article_id = sa3.number
     WHERE so3.charge_number = so.charge_number
       AND so3.typ = so.typ
       AND so3.year = so.year
       AND sa3.group = sa.group
       AND sm3.install_location = sm.install_location
       AND sm3.exchange IS NOT NULL
       AND so3.id != so.id
       AND sm3.service_article_id != sm.service_article_id
     ORDER BY sm3.exchange DESC
     LIMIT 1) AS lastExchangedGroup
FROM service_material sm
LEFT JOIN service_operation so ON sm.service_operation_id = so.id
LEFT JOIN service_article sa ON sm.service_article_id = sa.number
LEFT JOIN service_operation_patterns sop ON so.pattern_id = sop.id
LEFT JOIN products p ON so.typ = p.typ
     AND so.year = p.year
     AND so.charge_number = p.charge_number
     AND so.producttypeid = p.producttype_id
LEFT JOIN `order` o ON so.order_id = o.id
LEFT JOIN invoice inv ON sm.id = inv.service_material_id
WHERE so.order_id = 3998
  AND (sop.id = 54 OR (sop.active = 1 AND sop.is_inspection = 1))
  AND sm.destroyed IS NULL
ORDER BY so.typ DESC,
         so.charge_number DESC,
         so.year DESC,
         sm.service_article_id DESC

Yii2 ActiveRecord实现方式 #

在原有查询基础上，通过select()方法添加两个子查询字段，对应SQL中的关联子查询逻辑：

$serviceMaterialsQuery = ServiceMaterial::find()
    ->select([
        'service_material.*',
        // 子查询获取lastExchanged
        'lastExchanged' => ServiceMaterial::find()
            ->select('service_material.exchange')
            ->joinWith('serviceOperation')
            ->where([
                'service_operation.charge_number' => new \yii\db\Expression('service_operation.charge_number'),
                'service_operation.typ' => new \yii\db\Expression('service_operation.typ'),
                'service_operation.year' => new \yii\db\Expression('service_operation.year'),
                'service_material.service_article_id' => new \yii\db\Expression('service_material.service_article_id'),
                'service_material.install_location' => new \yii\db\Expression('service_material.install_location'),
            ])
            ->andWhere('service_material.exchange IS NOT NULL')
            ->andWhere(['!=', 'service_operation.id', new \yii\db\Expression('service_operation.id')])
            ->orderBy(['service_material.exchange' => SORT_DESC])
            ->limit(1),
        // 子查询获取lastExchangedGroup
        'lastExchangedGroup' => ServiceMaterial::find()
            ->select('service_material.exchange')
            ->joinWith(['serviceOperation', 'serviceArticle'])
            ->where([
                'service_operation.charge_number' => new \yii\db\Expression('service_operation.charge_number'),
                'service_operation.typ' => new \yii\db\Expression('service_operation.typ'),
                'service_operation.year' => new \yii\db\Expression('service_operation.year'),
                'service_article.group' => new \yii\db\Expression('service_article.group'),
                'service_material.install_location' => new \yii\db\Expression('service_material.install_location'),
            ])
            ->andWhere(['!=', 'service_operation.id', new \yii\db\Expression('service_operation.id')])
            ->andWhere(['!=', 'service_material.service_article_id', new \yii\db\Expression('service_material.service_article_id')])
            ->andWhere('service_material.exchange IS NOT NULL')
            ->orderBy(['service_material.exchange' => SORT_DESC])
            ->limit(1),
    ])
    ->joinWith(['serviceOperation', 'serviceArticle', 'pattern', 'serviceOperation.product', 'serviceOperation.order', 'invoice'])
    ->orderBy([
        "service_operation.typ" => SORT_DESC,
        "service_operation.charge_number" => SORT_DESC,
        "service_operation.year" => SORT_DESC,
        "service_material.service_article_id" => SORT_DESC,
    ])
    ->andWhere(['service_operation.order_id' => 3998])
    ->andWhere('service_operation_patterns.id = 54 OR (service_operation_patterns.active = 1 AND service_operation_patterns.is_inspection = 1)');

$serviceMaterials = $serviceMaterialsQuery->all();

关键注意事项 #

• 使用\yii\db\Expression引用主查询中的字段，确保子查询能正确关联主查询的当前行数据
• 子查询添加LIMIT 1来获取最新的exchange记录（已按exchange降序排序）
• 为提升性能，需确保以下字段存在索引：
  • service_operation(charge_number, typ, year, id)
  • service_material(service_article_id, install_location, exchange)
  • service_article(number, group)

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