优化Yii2 MYSQL查询

Question

我需要优化我的code.it工作，但需要时间，有时还会超时。

目标从表%1和表%2中选择的列必须在另一个表中组合。新表中不允许重复。提亚

 $modelsc=Customers::find()->select('customer_id')->all();
 $modelsp = Product::find()->select('product_no')->all();
 foreach($modelsc as $modelc) {                                                                      
     $user = $connection->createCommand(
           'SELECT product_no as product_no,:cust_no as fkcustomer_id                                                                                                         
             FROM product AS p                                                                                                           
             WHERE NOT EXISTS( SELECT pc.fkproduct_no                                                                                                                         
             FROM                                                                                                                               
             productcustomer AS pc
             WHERE
            pc.fkproduct_no = p.Product_no AND fkcustomer_id = :cust_no)');
      $user->bindValue(':cust_no', $modelc->customer_id);
      $modelsx = $user->queryAll();    
      Yii::$app->db->createCommand()->batchInsert('productcustomer', [ 'fkproduct_no', 'fkcustomer_id'], $modelsx)->execute();                                                          }

Answer 1

查看您的代码，您可以避免not exists子句，并尝试在pc.fkproduct_no上对null使用左连接检查

SELECT product_no as product_no,
      :cust_no as fkcustomer_id                                           
FROM product AS p   
LEFT JOIN  productcustomer AS pc ON  pc.fkproduct_no = p.Product_no 
    AND fkcustomer_id = :cust_no  
WHERE pc.fkproduct_no  is null

无论如何，请确保您对列where of join条件具有正确的索引

对于表products，列Product_no上的索引

对于表productcustomer，(fkcustomer_id, fkproduct_no)上的复合索引