使用自联接和子查询优化UPDATE SQL查询

Question

我的SQL查询更新了数据库中的所有库存，但是它的工作效率不高，有时我会得到504个超时错误。密码很好用。我怎样才能让它更好地运作。

P.S：请忽略缺少准备好的语句，我稍后再添加。

有关表的一些信息(Wordpress Woocommerce插件默认表)：

wp_posts:这个表包含了帖子。(文章可以是产品，也可以是产品变体。例如一个产品是蝴蝶T恤，一个产品变体是蝴蝶T恤红色大)。

wp_postmeta：此表包含有关posts的元信息。例如，如果一个产品变体是instock，或者它是什么颜色，或者它的大小。

  //This array gives, which products are there, and their respective categories.
  $allProducts = array("Fermuarlı Kapşonlu Sweatshirt" => "'2653','2659'","Kapşonlu Sweatshirt" => "'2646','2651'","Sweatshirt" => "'2644','2650'","Kadın Tişört" => "'2654','2656'","Atlet" => "'2655','2657'","Tişört" => "'2643','2304'");

  //Below arrays gives information about, which product variations are out of stock.
  $tisort_OutOfStock =array();
  $atlet_OutOfStock =array("all_colors"=>"'3xl','4xl','5xl'");
  $kadin_tisort_OutOfStock =array("all_colors"=>"'xxl','3xl','4xl','5xl'");
  $sweatshirt_OutOfStock =array("beyaz"=>"'xxl','3xl','4xl','5xl'","kirmizi"=>"'xxl','3xl','4xl','5xl'","bordo"=>"'5xl'","antrasit"=>"'5xl'");
  $kapsonlu_sweatshirt_OutOfStock =array("gri-kircilli"=>"'5xl'");
  $fermuarli_kapsonlu_sweatshirt_OutOfStock =array("gri-kircilli"=>"'5xl'","siyah"=>"'5xl'");

  //Reset stocks before updating.
  $resetStocks = "UPDATE wp_postmeta set meta_value = 'instock' where meta_key = '_stock_status'";
  $wpdb->query($resetStocks);
  echo "Stoklar are reseted<br>";

  //Foreach product, foreach color, update if product doesn't have stock.
  foreach( $allProducts as $key => $urun ){

    switch ($key) {
    case "Kadın Tişört": $tempArray = $kadin_tisort_OutOfStock; break;
    case "Fermuarlı Kapşonlu Sweatshirt": $tempArray = $fermuarli_kapsonlu_sweatshirt_OutOfStock; break;
    case "Kapşonlu Sweatshirt": $tempArray = $kapsonlu_sweatshirt_OutOfStock; break;
    case "Sweatshirt": $tempArray = $sweatshirt_OutOfStock; break;
    case "Atlet": $tempArray = $atlet_OutOfStock; break;
    case "Tişört": $tempArray = $tisort_OutOfStock; break;
    }

    foreach( $tempArray as $color => $size ){

      $query = "UPDATE wp_postmeta set meta_value = 'outofstock' where meta_key = '_stock_status' and post_id in
      (
      select post_id from (select * from wp_postmeta) AS X where meta_key = 'attribute_pa_beden' and meta_value in (".$size.")
      and post_id in (select post_id from (select * from wp_postmeta) AS Y where meta_key = 'attribute_pa_renk' and ((meta_value = '".$color."') OR ('".$color."' = 'all_colors')))
      and post_id in (select id from wp_posts where post_type = 'product_variation' and post_parent in (select object_id FROM wp_term_relationships where term_taxonomy_id in (".$urun.")))
      )";

      global $wpdb;
      $updatedRowCount = $wpdb->query($query);
    }
  }

Answer 1

从处理键SELECT开始

        SELECT  post_id
            from  
            (
                SELECT  *
                    from  wp_postmeta
            ) AS X
            where  meta_key = 'attribute_pa_beden'
              and  meta_value in (".$size.")
              and  post_id in (
                SELECT  post_id
                    from  
                    (
                        SELECT  *
                            from  wp_postmeta
                    ) AS Y
                    where  meta_key = 'attribute_pa_renk'
                      and  ((meta_value = '".$color."')
                              OR  ('".$color."' = 'all_colors'))
                          )
              and  post_id in (
                SELECT  id
                    from  wp_posts
                    where  post_type = 'product_variation'
                      and  post_parent in (
                        SELECT  object_id
                            FROM  wp_term_relationships
                            where  term_taxonomy_id in (".$urun."))) 
                      )";

是的，我意识到你需要“隐藏”wp_postmeta对UPDATE wp_postmeta，但我们可以重新安排事情，使它更有效率。注意如何在过滤之前获取整个wp_postmeta的两种情况？这使得不可能使用任何索引，因此是sloooow。

SELECT m1.post_id
    FROM wp_postmeta AS m1
    JOIN wp_postmeta AS m2  USING(post_id)
    JOIN wp_posts    AS p2  USING(post_id)
    JOIN wp_term_relationships AS tr  ON p2.post_parent = tr.object_id
    WHERE m1.meta_key = 'attribute_pa_beden' AND   m1.meta_value in ("$size")
      AND m2.meta_key = 'attribute_pa_renk'  AND ( m1.meta_value = '$color'
                                                   OR '$color' = 'all_colors' )
      AND p2.post_type = 'product_variation'
      AND tr.term_taxonomy_id IN ($urun)

在您调试这个UPDATE之前，不要考虑SELECT。)我可能犯了一些错误，但看起来不简单吗？它将运行得更快，特别是我推荐的索引。)

带有颜色的OR可能会被优化，所以我不会担心这一点。

我无法预测优化器将从哪一个表开始，因此需要这些索引来给它选择：

tr:  (term_taxonomy_id, object_id)  -- in this order
posts:  (post_type, post_id)        -- in this order
postmeta:  (meta_key, meta_value)   -- see note below

在Optimizer选择了要开始使用的表之后，它将依次转到其他每个表；顺序对我们来说并不重要。这些额外的索引可能有用：

posts:   (post_parent, post_id)        -- in this order
postmeta:  (post_id, meta_key, meta_value)   -- see note below

如果meta_value是LONGTEXT，那么它就不能在索引中，所以不要使用它。(不，不用费心使用“前缀”索引。)

如果您使用的是MySQL 5.5或5.6，那么meta_key太长，无法成为索引；请参阅我的http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes以获得多个解决方案。

EAV模式很糟糕，您正在找出原因。

回到UPDATE的挂起部分，添加包装器：

UPDATE wp_postmeta AS m
    JOIN  ( SELECT post_id
              FROM ( the above query )
          ) AS kludge  USING (post_id)
    SET   m.meta_value = 'outofstock'
    WHERE m.meta_key = '_stock_status'

Answer 2

        SELECT  post_id
            from  
            (
                SELECT  *
                    from  wp_postmeta
            ) AS X
            where  ...

-->

        SELECT post_id 
            FROM wp_postmeta
            WHERE ...

(子查询只会减慢速度。)

              and  post_id in (
                SELECT  post_id

而不是使用IN ( SELECT ... )，而是使用JOIN。

除了这些技巧之外，请参阅我关于改进后置模式的http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta。