英文:

WooCommerce cpu is 50%-100% cause of db queries?

问题

抱歉，您提供的内容中有一些代码，我将忽略这些代码并为您翻译文本部分。

很不幸，我的网站经常出现资源短缺问题，CPU 使用率有时会达到 100%。通常情况下，它在 30% 到 50% 之间波动，但它可能会突然增加并导致网站崩溃。主机说资源消耗的原因如下：

问题代码1：

SELECT t.*, tt.*, tr.object_id, tm.meta_value FROM ptd_terms AS t INNER JOIN ptd_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN ptd_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id LEFT JOIN ptd_termmeta AS tm ON (t.term_id = tm.term_id AND tm.meta_key = 'order') WHERE tt.taxonomy IN ('product_type', 'product_visibility', 'product_cat', 'product_tag', 'product_shipping_class') AND tr.object_id IN (43606, 43609, 43612, 43615) GROUP BY t.term_id, tr.object_id ORDER BY tm.meta_value+0 ASC, t.name ASC

问题代码2：

taxonomy IN ('product_type', 'product_visibility', 'product_cat', 'product_tag', 'product_shipping_class')

问题在于我也在使用缓存插件，但似乎没有任何效果。这些代码是否可能导致 CPU 使用率上升？这些代码是什么，如果它们引起问题该怎么办？

WP 查询插件还显示了两个慢查询：0.1367 秒

慢查询1：

SELECT SQL_CALC_FOUND_ROWS ptd_posts.ID
FROM ptd_posts
LEFT JOIN ptd_term_relationships
ON (ptd_posts.ID = ptd_term_relationships.object_id)
WHERE 1=1
AND ( ptd_term_relationships.term_taxonomy_id IN (18,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,496,497,498,499,500,501,502,503,504,2601,2665,3114,3737,4735,4847,5093,5257,5491,5492,5493,5499,5610,5738,6166,6989,7153,7370,7445,8438,8787,9442,10032,12527,13110,16684,19154,20214,20373,20633,21697,22238,22239,23280,23353,23482,23560,23781,23821,23980,25108,25393,26345,26424,26748,27097,27207,27210,27387,27619,27972,29504,30006,30538,31194,31375,31565,31792,32130,32216,33149,33313,33571,34215,34852,34983,35801,37147,37244,37563,39735,39946,40544,40627,41175,41461) )
AND ptd_posts.post_type = 'product'
AND (ptd_posts.post_status = 'publish'
OR ptd_posts.post_status = 'acf-disabled'
OR ptd_posts.post_status = 'private')
GROUP BY ptd_posts.ID
ORDER BY ptd_posts.post_date DESC
LIMIT 0, 4

慢查询2：

SELECT SQL_CALC_FOUND_ROWS ptd_posts.ID
FROM ptd_posts
LEFT JOIN ptd_term_relationships
ON (ptd_posts.ID = ptd_term_relationships.object_id)
WHERE 1=1
AND ( ptd_term_relationships.term_taxonomy_id IN (16,17,18,21,22,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,

<details>
<summary>英文:</summary>

Unfortunately, my website often experiences resource shortages and CPU usage can go up to 100% at times. Usually, it ranges between 30% to 50%, but it can suddenly increase and cause the site to go down. The host says that resources are being consumed due to the following reasons:

SELECT t., tt., tr.object_id, tm.meta_value FROM ptd_terms AS t INNER JOIN ptd_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN ptd_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id LEFT JOIN ptd_termmeta AS tm ON (t.term_id = tm.term_id AND tm.meta_key = 'order') WHERE tt.taxonomy IN ('product_type', 'product_visibility', 'product_cat', 'product_tag', 'product_shipping_class') AND tr.object_id IN (43606, 43609, 43612, 43615) GROUP BY t.term_id, tr.object_id ORDER BY tm.meta_value+0 ASC, t.name ASC

and :

taxonomy IN ('product_type', 'product_visibility', 'product_cat', 'product_tag', 'product_shipping_class')

The point is that I am also using a caching plugin, but it does not seem to have any effect. Could these codes be causing an increase in CPU usage? What are these codes, and what should I do if they are causing a problem?

The WP Query plugin also shows me two queries that are slow : 0٫1367 time

SELECT SQL_CALC_FOUND_ROWS ptd_posts.ID
FROM ptd_posts
LEFT JOIN ptd_term_relationships
ON (ptd_posts.ID = ptd_term_relationships.object_id)
WHERE 1=1
AND ( ptd_term_relationships.term_taxonomy_id IN (18,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,496,497,498,499,500,501,502,503,504,2601,2665,3114,3737,4735,4847,5093,5257,5491,5492,5493,5499,5610,5738,6166,6989,7153,7370,7445,8438,8787,9442,10032,12527,13110,16684,19154,20214,20373,20633,21697,22238,22239,23280,23353,23482,23560,23781,23821,23980,25108,25393,26345,26424,26748,27097,27207,27210,27387,27619,27972,29504,30006,30538,31194,31375,31565,31792,32130,32216,33149,33313,33571,34215,34852,34983,35801,37147,37244,37563,39735,39946,40544,40627,41175,41461) )
AND ptd_posts.post_type = 'product'
AND (ptd_posts.post_status = 'publish'
OR ptd_posts.post_status = 'acf-disabled'
OR ptd_posts.post_status = 'private')
GROUP BY ptd_posts.ID
ORDER BY ptd_posts.post_date DESC
LIMIT 0, 4

WP_Query->get_posts

0٫1367

SELECT SQL_CALC_FOUND_ROWS ptd_posts.ID
FROM ptd_posts
LEFT JOIN ptd_term_relationships
ON (ptd_posts.ID = ptd_term_relationships.object_id)
WHERE 1=1
AND ( ptd_term_relationships.term_taxonomy_id IN (16,17,18,21,22,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,198,197,196,195,194,193,192,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,541,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,1120,1121,1122,1130,1131,1132,1133,1143,1169,1198,1199,1211,1212,1213,1214,1257,1777,1778,1779,1780,1781,1782,1783,1784,1785,1786,1787,1788,1789,1790,1791,1792,1793,1794,1795,1796,1797,1798,1799,1800,1801,1802,1803,1804,1805,1806,1807,1808,1809,1810,1811,1812,1813,1814,1815,1816,1817,1818,1819,1820,1821,1822,1823,1824,1825,1826,1827,1828,1829,1830,1831,1832,1833,1834,1835,1836,1837,1838,1839,1840,1841,1842,1843,1844,1845,1846,1847,1848,1849,1850,1851,1852,1853,1854,1855,1856,1857,1858,1859,1860,1861,1862,1863,1864,1865,1866,1867,1868,1869,1870,2003,2447,2601,2665,2875,2909,3114,3737,3773,3774,3775,3776,3777,3778,3779,3780,3781,3782,3783,3784,3785,3786,3787,3788,3789,3790,3791,3792,3793,3794,3795,3796,3797,3798,3799,3800,3801,3802,3803,3804,3805,3806,3807,3808,3809,3810,3811,3812,3813,3814,3815,3816,3817,3818,3819,3820,3821,3822,3824,3825,3826,3827,3828,3829,3830,4174,4220,4233,4245,4259,4297,4735,4847,4968,5093,5257,5429,5491,5492,5493,5499,5551,5552,5610,5738,6166,6571,6678,6765,6821,6989,7044,7051,7153,7370,7397,7413,7445,7515,7740,8438,8787,8911,9442,9716,9919,10032,10056,11002,12372,12527,12559,12613,12664,12738,12770,12826,12868,12889,12910,12927,13005,13110,13201,13225,14757,15075,15126,16113,16164,16684,17797,18762,18854,19154,19550,19675,19676,19741,19956,20214,20373,20479,20593,20633,20812,20813,20814,20841,20842,20843,20844,20869,20870,20871,20872,20873,20914,20915,20916,20917,21219,21220,21273,21274,21294,21295,21490,21491,21492,21504,21505,21540,21541,21580,21581,21582,21583,21697,21723,21724,21725,21726,21742,21743,21744,21745,21746,21931,22075,22135,22136,22187,22238,22239,22802,22803,22861,23075,23258,23280,23353,23462,23482,23560,23667,23739,23781,23821,23843,23912,23934,23979,23980,24400,24401,24559,24962,25042,25108,25171,25393,25467,26345,26346,26424,26425,26528,26646,26684,26748,26749,26750,27097,27140,27209,27207,27208,27210,27387,27619,27642,27911,27972,27973,28203,28434,29041,29147,29275,29276,29277,29278,29304,29305,29340,29405,29406,29483,29504,29541,29571,29598,29817,29948,30006,30171,30316,30317,30458,30538,30539,31017,31062,31090,31155,31184,31194,31253,31375,31376,31504,31565,31566,31764,31792,31793,32105,32130,32131,32216,32599,32779,32780,33138,33149,33150,33313,33314,33456,33571,33572,33751,34214,34215,34852,34983,34984,35801,35802,36786,36962,37014,37030,37031,37126,37147,37244,37245,37246,37247,37422,37563,37564,37565,38038,38445,38912,39029,39097,39281,39370,39735,39736,39946,39947,39948,40544,40627,40826,41101,41175,41176,41177,41317,41461) )
AND ptd_posts.post_type = 'product'
AND (ptd_posts.post_status = 'publish'
OR ptd_posts.post_status = 'acf-disabled'
OR ptd_posts.post_status = 'private')
GROUP BY ptd_posts.ID
ORDER BY ptd_posts.post_date DESC
LIMIT 0, 18

can anyone tell me what is these codes and how reduce cu usage ?

</details>


# 答案1
**得分**: 1

这些查询是由WordPress中专门构建的ORM合成的，所以反对它们的丑陋是没有用的（除非你想尝试说服WordPress.org的人重构部署在数百万台服务器上的大量内容）。

这些性能问题可以通过持久对象缓存来解决。有了对象缓存，就不太需要那些`IN(huge, list, of, numbers)`子句了。正如你已经发现的，页面缓存帮助不大。

你可以尝试[重新索引][1]你的数据库表。这可能会帮助解决帖子查询的问题，但对分类查询可能没有太大帮助。

使用一个清理插件进行数据库清理可能会有所帮助，特别是如果你的网站已经存在一段时间。

此外，你可能已经超出了预算托管服务的范围。我知道这既是好消息又是坏消息。

[1]: https://wordpress.org/plugins/index-wp-mysql-for-speed/

<details>
<summary>英文:</summary>

These queries are synthesized by the purpose-built ORM in WordPress, so it&#39;s no use railing against their ugliness (unless you want to try to persuade WordPress.org people to refactor a whole lot of stuff deployed in millions of servers etc). 

These performance issues can be helped by a persistent object cache. Those `IN(huge, list, of, numbers)` clauses aren&#39;t needed as much with an object cache in place. As you have discovered page caches don&#39;t help much.

You can try [reindexing][1] your database tables. That will probably help with the posts queries but not the taxonomy queries. 

A database cleanup with a cleaner plugin may help, especially if your site has been around for a while. 

And, you may have outgrown your budget hosting service. I know, good news and bad news.


  [1]: https://wordpress.org/plugins/index-wp-mysql-for-speed/

</details>