提高 IN 語句中 Select 語句的查詢性能

我的查詢是：

(3) UPDATE wp_postmeta set meta_value = 'outofstock' where meta_key = '_stock_status' and 
   post_id in  
       ( 
        (2)SELECT post_id FROM 
           ( 
           (1) SELECT A.post_id from wp_postmeta A 
               JOIN wp_postmeta B ON A.post_id = B.post_id 
               AND A.meta_key = 'attribute_pa_beden' 
               and A.meta_value in ('12yas','34yas','56yas','78yas','910yas','1112yas') 
               and B.meta_key = 'attribute_pa_renk' 
               and ((B.meta_value = 'bordo') OR ('bordo' = 'hepsi')) 
               JOIN wp_posts ON A.post_id = wp_posts.id 
               JOIN wp_term_relationships ON wp_posts.post_parent = wp_term_relationships.object_id 
               and term_taxonomy_id in ('2643','2304') 
           ) AS DerivedTable
       )

為了提高此查詢的速度，我使用“解釋”語句對其進行了分析。以下是結果：

當我explain在上述查詢中添加到 (1) 位置並執行子查詢時。結果如下：

當我添加explain到 (2) 位置並執行該 subqyery 時，結果如下。

當我添加explain到 (3) 位置並執行整個查詢時，結果如下：

我的分析是（1）子查詢沒有速度問題，但是在我從這個子查詢中選擇數據到派生表（2）之後，不知何故有一個55.277.640“行”來了，這似乎是我的查詢這麼慢的原因。我該如何優化它？這裡有什麼問題？

編輯：這些表格是 Wordpress WooCommerce 模組標準表格。我沒有修改它們。這裡的SHOW CREATE TABLE結果：

wp_postmeta

CREATE TABLE `wp_postmeta` (
 `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `meta_value` longtext COLLATE utf8mb4_unicode_ci,
 PRIMARY KEY (`meta_id`),
 KEY `post_id` (`post_id`),
 KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM AUTO_INCREMENT=11119572 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

wp_posts

CREATE TABLE `wp_posts` (
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
 `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
 `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
 `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
 `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
 `post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
 `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
 `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `menu_order` int(11) NOT NULL DEFAULT '0',
 `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
 `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `comment_count` bigint(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`),
 KEY `post_name` (`post_name`(191)),
 KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
 KEY `post_parent` (`post_parent`),
 KEY `post_author` (`post_author`)
) ENGINE=MyISAM AUTO_INCREMENT=352598 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

wp_term_relationships

CREATE TABLE `wp_term_relationships` (
 `object_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 `term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 `term_order` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`object_id`,`term_taxonomy_id`),
 KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Edit2：手動更改為 innoDB 後，解釋更改如下。

在位置 1 中解釋：

在位置 2 中解釋：

在位置 3 中解釋：

這裡有一些關於我想要做什麼的資訊。在我的網站上有很多產品變化。wp_postmeta 表包含所有變體的元資訊。（例如，價格、顏色、尺寸、庫存等）。

我的查詢用於更新股票資訊。例如，我有一個有 9 種顏色和 9 種尺寸的產品。這意味著 81 種變化。如果我需要更新所有尺寸的顏色“bordo”庫存。這意味著必須在 wp_postmeta 中更新 9 行。（顏色的所有尺寸：bordo）。

如果我將上述查詢更新為，這裡有一些行號select count(*)：

wp_postmeta 有 9.929.761

我表示為 (1) 的第一個子查詢返回 3612 行。

我表示為 (2) 的第一個子查詢也返回 3612 行。

我表示為 (3) 的第一個子查詢也返回 3612 行。

所以基本上我的網站上有 3612 種“bordo”彩色產品變體，我需要更新所有這些變體的庫存資訊。

當我檢查我的數據庫時，有 227K 行帶有 meta_key “_stock_status”。（所有顏色和所有產品尺寸）。我知道更新所有數據的成本有點高。但我並沒有一直使用這個功能。

當我添加 20 個新產品並從生產團隊獲得有關產品和顏色的最新庫存狀態的資訊時，我點擊此“更新庫存”按鈕將網站中的所有庫存資訊更新到最後狀態。

編輯2

我從 Rick 閱讀了 wp_postmeta 表的修改要求。它們似乎都有幫助，但我不想更改 wp_postmeta 表的預設結構，因為我不是數據庫專家，並且可能不會恢復未來可能由修改核心 wordpress 表引起的問題。

因此，為了安全起見，我閱讀了ypercubeᵀᴹ和Old Pro的另外兩個答案。

他們建議為 wp_postmeta 表添加一個新索引，這是我對這個核心 wordpress 表的唯一更改。在我測試了他們的兩個查詢之後，他們給出了與explain下面相同的語句結果。

所以基本上他們的查詢是用不同的結構做同樣的事情。現在查詢的性能提高了很多。我心中唯一的問題是，將手動索引添加到 wordpress 核心表有多安全？

postmeta效率低下。 有關如何處理它的討論，請參閱https://stackoverflow.com/questions/43859351/why-are-references-to-wp-postmeta-so-slow 。或：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta或 https://wordpress.stackexchange.com/questions/248207/simple-sql-query-on-wp-postmeta-very-slow

首先，正如 Rick James 在他的回答和連結中指出的那樣，Wordpress 數據庫設計有幾個缺陷。這wp_postmeta尤其是許多 WP 安裝中性能問題的常見原因，一旦它們增長到足夠多並且有超過幾千行。

在解決這個問題的艱難而漫長的道路之前，我將提出一些可能有助於在短時間內改進特定查詢的內容：

---

重寫IN (complex subquery)為 a JOIN：

UPDATE
   ( 
       SELECT A.post_id from wp_postmeta A 
       JOIN wp_postmeta B ON A.post_id = B.post_id 
       AND A.meta_key = 'attribute_pa_beden' 
       and A.meta_value in ('12yas','34yas','56yas','78yas','910yas','1112yas') 
       and B.meta_key = 'attribute_pa_renk' 
       and ((B.meta_value = 'bordo') OR ('bordo' = 'hepsi')) 
       JOIN wp_posts ON A.post_id = wp_posts.id 
       JOIN wp_term_relationships ON wp_posts.post_parent = wp_term_relationships.object_id 
       and term_taxonomy_id in ('2643','2304') 
   ) AS DerivedTable
   JOIN
       wp_postmeta AS upd
   ON
       upd.post_id = DerivedTable.post_id
SET
   upd.meta_value = 'outofstock'
WHERE
   upd.meta_key = '_stock_status' ;

並添加一個索引wp_postmeta (meta_key(191), post_id)

引用自：https://dba.stackexchange.com/questions/229348