優化執行需要 5 秒鐘的 WordPress 查詢

問題描述

我有一個大的 wordpress 數據庫：

關鍵表中的行：

730K wp_posts
404K wp_terms
752K wp_term_relationships
27K wp_term_taxonomy
1.8 Million wp_postmeta

問題是我有一個查詢需要 5 秒鐘才能完成，我想在添加任何緩存之前優化查詢。

mysql> SELECT wp_posts.ID
 FROM wp_posts
 INNER JOIN wp_term_relationships
 ON (wp_posts.ID = wp_term_relationships.object_id)
 LEFT JOIN wp_postmeta
 ON (wp_posts.ID = wp_postmeta.post_id
 AND wp_postmeta.meta_key = '_Original Post ID' )
 LEFT JOIN wp_postmeta AS mt1
 ON ( wp_posts.ID = mt1.post_id )
 WHERE 1=1
 AND wp_posts.ID NOT IN (731467)
 AND ( wp_term_relationships.term_taxonomy_id IN (5) )
 AND wp_posts.post_type = 'post'
 AND (wp_posts.post_status = 'publish'
 OR wp_posts.post_status = 'private')
 AND ( wp_postmeta.post_id IS NULL
 OR ( mt1.meta_key = '_Original Post ID'
 AND CAST(mt1.meta_value AS CHAR) = 'deleted' ) )
 GROUP BY wp_posts.ID
 ORDER BY  wp_posts.ID DESC
 LIMIT 0, 20;

這是結果：

+--------+
| ID     |
+--------+
| 731451 |
| 731405 |
| 731403 |
| 731397 |
| 731391 |
| 731385 |
| 731375 |
| 731363 |
| 731361 |
| 731353 |
| 731347 |
| 731345 |
| 731335 |
| 731331 |
| 731304 |
| 731300 |
| 731284 |
| 731273 |
| 731258 |
| 731254 |
+--------+

對查詢進行解釋會產生以下信息

+----+-------------+-----------------------+--------+------------------------------------------------------------+------------------+---------+----------------------------------------+--------+-----------------------------------------------------------+
| id | select_type | table                 | type   | possible_keys                                                 | key              | key_len | ref                                    | rows   |   Extra                                                     |
+----+-------------+-----------------------+--------+------------------------------------------------------------+------------------+---------+----------------------------------------+--------+-----------------------------------------------------------+
|  1 | SIMPLE      | wp_term_relationships | range  | PRIMARY,term_taxonomy_id                                      | term_taxonomy_id | 16      | NULL                                   | 130445 |   Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | wp_posts              | eq_ref | PRIMARY,post_name,type_status_date,post_parent,post_author | PRIMARY          | 8       | mydatabase.wp_term_relationships.object_id |      1 | Using where                                               |
|  1 | SIMPLE      | wp_postmeta           | ref    | post_id,meta_key                                           | post_id          | 8       | mydatabase.wp_term_relationships.object_id |      1 | Using where                                               |
|  1 | SIMPLE      | mt1                   | ref    | post_id                                                    | post_id          | 8       | mydatabase.wp_term_relationships.object_id |      1 | Using where                                               |
+----+-------------+-----------------------+--------+------------------------------------------------------------+------------------+---------+----------------------------------------+--------+-----------------------------------------------------------+

如何優化此查詢以加載更快？我認為一個自定義索引將是去的方式，但不知道在哪些領域。另外我試圖訂購結果 wp_posts.ID DESC，但是得到同樣的時間來執行查詢。

最佳解決方案

我有完全相同的問題。這個問題不是可以修改的，而是可以修改一些你可能不應該使用的代碼 (或者寫一個過濾器或’drop-in’) 。問題是 SQL 語句中的 CAST 指令。它會在整個表完成任何事情之前，記錄的數量，它需要一段時間。

捕獲查詢，刪除以下"AND CAST(mt1.meta_value AS CHAR) = 'deleted'"並運行它，現在應該要快很多。

編輯：(更正) 將查詢更改為"AND mt1.meta_value = 'deleted'"

我不知道開發人員在添加無用的 CAST 時是怎麼想的，MySQL 沒有它可以正常工作 (除了大小之外，TEXT 與 CHAR 沒有任何區別) 。我相信有一些邊的情況下，刪除它不會給出所需的結果，但我還沒有找到一個。

長活 Wordpress SQL X)

次佳解決方案

所以如果我在這個項目中使用 wordpress，我將要做的是創建一個反向的 post id 索引。我不認為這是”correct” 答案，有些人一定會完全不同意這種做法，但這正在為我生產中。

我從這個博客帖子中得到了這個想法：

https://www.igvita.com/2007/08/20/pseudo-reverse-indexes-in-mysql/

As I recently discovered, MySQL currently only supports storage of index values in ascending order….It took all of three weeks for AideRSS to hit the 3 million plus indexed blog posts, and in the process I could feel the site getting more sluggish: the descending order by clause was killing us. In the worst case, merging a union of several queries meant the performance hit of a filesort operation!

由於 mysql 的這個限制，下面的命令& 排序功能是查詢中的瓶頸。

GROUP BY wp_posts.ID
ORDER BY  wp_posts.ID DESC

但是，從生產中平均 5-15 秒的平均值中刪除這些條件，查詢將在 20ms 內返回。但是問題是這些帖子按照最新到最新的順序排列。我想要的是最新到最老的

SELECT wp_posts.*
FROM wp_posts
WHERE wp_posts.ID IN(
SELECT distinct(ID)
FROM wp_posts
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_postmeta
ON (wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = '_Original Post ID' )
LEFT JOIN wp_postmeta AS mt1
ON ( wp_posts.ID = mt1.post_id )
WHERE wp_posts.ID NOT IN (795025)
AND ( wp_term_relationships.term_taxonomy_id IN (1) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
AND ( wp_postmeta.post_id IS NULL
OR ( mt1.meta_key = '_Original Post ID'
AND CAST(mt1.meta_value AS CHAR) = 'deleted' ) ) ) limit 0, 20;

所以再次回到這個帖子：https://www.igvita.com/2007/08/20/pseudo-reverse-indexes-in-mysql/

Following Peter Zaitsev’s advice on faking a reverse index, I decided to sidestep our problem by creating a separate reverse timestamp for the publication time of an indexed blog post. The trick is, since all indexes are stored in ascending order, instead of storing the publication date, you need to store a ‘countdown’ value from some date in the future. A few SQL queries will do the trick:

而不是存儲帖子創建日期”reversed”，我決定在 wp_posts 表格中以 Post 格式存儲 Post ID 。

所以我在我的 wordpress 數據庫的 mysql 中添加了另外一列到 wp_posts 表。此新列存儲 int 負數。

alter table wp_posts add column reverse_post_id int;

更新當前帖子以獲取新列的相反數字：

 update wp_posts set reverse_post_id = (ID/ -1);

然後我在這個新的 reverse_post_id 上創建一個索引：

  create index reverse_post_id_index on wp_posts(post_type,post_status,reverse_post_id);

目前我通過自定義 api 界面編程插入文章，所以我插入後創建反向的 post id 。在通過界面插入帖子後，我將添加一個鈎子來在 wordpress 中創建 reverse_post_id 。

我還要添加一個 mysql 調度事件以一定的時間間隔來更新 wp_posts，其中 reverse_post_id 為空。

最終查詢看起來像這樣，在生產中的運行時間不超過 20 ms：

SELECT wp_posts.*
FROM wp_posts
WHERE wp_posts.ID IN(
SELECT distinct(ID)
FROM wp_posts **force index (reverse_post_id_index)**
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_postmeta
ON (wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = '_Original Post ID' )
LEFT JOIN wp_postmeta AS mt1
ON ( wp_posts.ID = mt1.post_id )
WHERE wp_posts.ID NOT IN (795025)
AND ( wp_term_relationships.term_taxonomy_id IN (1) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
AND ( wp_postmeta.post_id IS NULL
OR ( mt1.meta_key = '_Original Post ID'
AND CAST(mt1.meta_value AS CHAR) = 'deleted' ) ) ) limit 0, 20;

請注意，添加「強制索引 (reverse_post_id_index)」將以最新的 desc 順序返回 wp_posts，而不使用”order by” 操作。需要注意的是，reverese_post_id 不能為空。

再次，這可能不是正確的答案，但我發現使我的工作和我的情況的答案。

參考文獻

Optimize WordPress Query that take 5 seconds to execute

注：本文內容整合自 Google/Baidu/Bing 輔助翻譯的英文資料結果。如果您對結果不滿意，可以加入我們改善翻譯效果：薇曉朵技術論壇。

優化執行需要 5 秒鐘的 WordPress 查詢