Mysql
具有海量 MySQL 數據庫的 WordPress 速度慢。MySQL複製有幫助嗎？
數據庫管理通常不是我的強項，但一位朋友問我是否可以幫助他完成他的項目。他正在使用 WooCommerce 執行 WordPress (MySQL) 網站。postmeta 表儲存 160,000+ 條記錄，postmeta 表儲存 3,000,000+ 條記錄。尤其是後一張表那麼大，因為 WordPress/WooCommerce 儲存產品屬性的方式似乎有點低效。
歷史
不管這個龐大的數據庫（對於 WordPress 標準）如何，除了搜尋功能外，該網站在共享主機帳戶上執行得非常順利。它在內部連接兩個大表以查找所有產品標題、描述、屬性等中的匹配項。
有時載入需要 15 秒。有時它根本懶得載入，導致伺服器崩潰。
目前狀態
我故作聰明，建議把網站移到 VPS 上，我會為他設置。所以我配置了兩個 VPS droplets (DigitalOcean)，都執行 Ubuntu 14.04.3 x64。第一個 droplet 提供文件（Nginx + PHP FPM），第二個 droplet 只執行 MySQL 伺服器。
結果：網站速度更快，但搜尋速度仍然很慢。現在5-10秒。
實際問題
因此，在我說服自己陷入更多麻煩之前，我想知道加快搜尋速度的最佳選擇是什麼。請注意，WordPress 搜尋基於單個查詢。我假設我們有以下選擇，並希望聽到一些回饋，甚至可能是其他更聰明的建議：
- 提升伺服器性能：提高執行 MySQL 伺服器的 droplet 的性能（目前為 1 CPU / 1GB RAM）。我朋友的預算只允許升級到下一個級別：2 CPU / 2 GB RAM
- 數據庫複製：我們沒有提高執行 MySQL 的 droplet 的性能，而是將第三個 droplet 配置為 MySQL 從屬，以使用目前數據庫 droplet 在主從配置中執行。
- Database Raid10 System：只是一個瘋狂的猜測……（請記住，我遠不是這個主題的專家）我什至不確定 DigitalOcean 是否允許 Raid 配置。
根據評論中的要求：
查詢（11.0650 秒執行時間）
SELECT SQL_CALC_FOUND_ROWS wp_6bv56cvp82_posts.ID FROM wp_6bv56cvp82_posts INNER JOIN wp_6bv56cvp82_postmeta ON ( wp_6bv56cvp82_posts.ID = wp_6bv56cvp82_postmeta.post_id ) INNER JOIN wp_6bv56cvp82_postmeta AS mt1 ON ( wp_6bv56cvp82_posts.ID = mt1.post_id ) WHERE 1=1 AND (((wp_6bv56cvp82_posts.post_title LIKE '%tub%') OR (post_excerpt LIKE '%tub%') OR (wp_6bv56cvp82_posts.post_content LIKE '%tub%'))) AND ( wp_6bv56cvp82_postmeta.meta_key = 'total_sales' AND ( ( mt1.meta_key = '_visibility' AND CAST(mt1.meta_value AS CHAR) IN ('visible','search') ) ) ) AND wp_6bv56cvp82_posts.post_type = 'product' AND (wp_6bv56cvp82_posts.post_status = 'publish' OR wp_6bv56cvp82_posts.post_status = 'private') GROUP BY wp_6bv56cvp82_posts.ID ORDER BY wp_6bv56cvp82_postmeta.meta_value+0 DESC, wp_6bv56cvp82_posts.post_date DESC LIMIT 0, 10表定義
這兩個表都基於 InnoDB。
wp_6bv56cvp82_postmeta 結構（2,592,840 行）：
+------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+----------------+ | meta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | post_id | bigint(20) unsigned | NO | MUL | 0 | | | meta_key | varchar(255) | YES | MUL | NULL | | | meta_value | longtext | YES | | NULL | | +------------+---------------------+------+-----+---------+----------------+wp_6bv56cvp82_posts 結構（130,063 行）：
+-----------------------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+---------------------+----------------+ | ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | post_author | bigint(20) unsigned | NO | MUL | 0 | | | post_date | datetime | NO | | 0000-00-00 00:00:00 | | | post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | | | post_content | longtext | NO | | NULL | | | post_title | text | NO | | NULL | | | post_excerpt | text | NO | | NULL | | | post_status | varchar(20) | NO | | publish | | | comment_status | varchar(20) | NO | | open | | | ping_status | varchar(20) | NO | | open | | | post_password | varchar(20) | NO | | | | | post_name | varchar(200) | NO | MUL | | | | to_ping | text | NO | | NULL | | | pinged | text | NO | | NULL | | | post_modified | datetime | NO | | 0000-00-00 00:00:00 | | | post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | | | post_content_filtered | longtext | NO | | NULL | | | post_parent | bigint(20) unsigned | NO | MUL | 0 | | | guid | varchar(255) | NO | | | | | menu_order | int(11) | NO | | 0 | | | post_type | varchar(20) | NO | MUL | post | | | post_mime_type | varchar(100) | NO | | | | | comment_count | bigint(20) | NO | | 0 | | +-----------------------+---------------------+------+-----+---------------------+----------------+上述查詢的 EXPLAIN 輸出：
+----+-------------+------------------------+--------+--------------------------+----------+---------+--------------------------------------------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+--------+--------------------------+----------+---------+--------------------------------------------+-------+----------------------------------------------+ | 1 | SIMPLE | mt1 | ref | post_id,meta_key | meta_key | 767 | const | 70052 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | wp_6bv56cvp82_postmeta | ref | post_id,meta_key | post_id | 8 | globalfluid.mt1.post_id | 13 | Using where | | 1 | SIMPLE | wp_6bv56cvp82_posts | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | globalfluid.wp_6bv56cvp82_postmeta.post_id | 1 | Using where | +----+-------------+------------------------+--------+--------------------------+----------+---------+--------------------------------------------+-------+----------------------------------------------+表結構（這次使用 show create）
wp_6bv56cvp82_postmeta 結構：
+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | wp_6bv56cvp82_postmeta | CREATE TABLE `wp_6bv56cvp82_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=InnoDB AUTO_INCREMENT=2895543 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | +------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)wp_6bv56cvp82_posts 結構：
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | wp_6bv56cvp82_posts | CREATE TABLE `wp_6bv56cvp82_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(20) 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=InnoDB AUTO_INCREMENT=136320 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | +---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
WordPress
WordPress 在設計時並未考慮到縮放。（警告：我在這裡所說的內容是基於幾年前對內部結構的觀察；情況可能已經改變。）沒有“讀寫”分離，因此通過複製擴展讀取將是一項重大任務。每個“使用者”都有自己的數據庫，其中包含 13 個以上的表。對於大型安裝，這會導致作業系統在搜尋文件系統時出現問題，並導致 MySQL 出現諸如
table_open_cache.另一方面，您的問題意味著一個龐大的使用者，而不是大量的使用者。而且，我無法判斷有多少損失來自 WooCommerce 而不是 WordPress 本身。
大表效率低下
以下是使大表的搜尋實現效率低下的問題：
LIKE '%tub%'效率低下。它不能使用任何索引，所以它必須掃描整個表。OR雪上加霜。FULLTEXT是最好的選擇，但它帶有警告，並且需要同時更改架構和SELECT.- “鍵值”儲存（“EAV”）不能很好地擴展。
postmeta就是這樣一個架構。它會導致meta_value+0和CAST(mt1.meta_value AS CHAR).GROUP BY加號的複雜性ORDER BY使得不可能使用任何索引來防止在僅傳遞之前LIMIT 0,10找到所有行。然而，這意味著額外的負擔SQL_CALC_FOUND_ROWS是最小的。- 擁有
LONGTEXTformeta_value幾乎消除了與之相關的任何優化。這將有助於一些：
INDEX(post_id, meta_key)。（繼續下去meta_value會更好，但由於 .）這是不可能的LONGTEXT。）這可能是第二好的“快速修復”。其他注意事項
一個 MySQL 連接將只使用一個 CPU。因此，只要您沒有並發搜尋，就不需要多個 CPU 核心。即使您有多個同時搜尋，也有一些重疊，因為其中一些工作是 I/O。
1GB記憶體？您可能非常受 I/O 限制？您必須
innodb_buffer_pool_size設置為非常小的值？（否則你會被交換，這對 MySQL 來說是非常糟糕的。） 1GB 不是“小”，它是“微不足道的”！至少選擇 4GB。然後將buffer_pool設置為1500M。這一變化是最大的“快速修復”。在 RAID 之前處理 RAM。如果可以，獲取有關 CPU 和 I/O 使用率的指標。在 250 萬個元行中，有多少具有
meta_key = '_visibility'. 這就是EXPLAIN決定開始的原因。它估計有 70K，但這可能會嚴重偏離。架構的體積大約是所需的兩倍，主要是因為數據類型草率。更小 –> 更多可記憶體 –> 更少 I/O –> 更快。然而，由於 WordPress 和/或 WooCommerce，進行更改將是乏味的，而且可能是不可能的。
原型製作
在程式中，最好從“原型”開始，以證明各種設計概念和實現細節。原型製作通常涉及“現成的”工具。當數據量增加和訪問量增加時，是時候扔掉“原型”並根據您所學的知識設計自定義系統了。 你已經過了那個時間。