WordPress優化資料庫作業

我這個是直接操作資料庫，對於 WordPress 來說，長期運行會累積許多不必要的資料，例如修訂版本、垃圾評論、孤立的 meta 資料等。在執行前 先備份資料庫，避免意外刪除重要資料。

刪除用不到的資料

DELETE FROM wp_posts WHERE post_type = 'revision';
DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_posts WHERE post_status = 'trash';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM wp_comments);
DELETE FROM wp_terms WHERE term_id IN (
    SELECT term_id FROM wp_term_taxonomy WHERE count = 0
);
DELETE FROM wp_term_taxonomy WHERE count = 0;

上面共處理了
刪除文章修訂版本 (Post Revisions)
清理垃圾評論 (Spam Comments)
刪除回收桶中的文章 (Deleted Posts)
移除未關聯的 Meta 資料
移除未使用的標籤 (Orphaned Tags)

然後是優化資料表

OPTIMIZE TABLE wp_posts, wp_comments, wp_postmeta, wp_term_relationships, wp_term_taxonomy, wp_options;

如果要全部優化資料表也可以，我用兩個方式做

方案一:

SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';') 
FROM information_schema.tables 
WHERE table_schema = DATABASE();

上面這個會顯示出所有資料表的優化指令, 基本上就是複製起來再貼上執行就可以了。

方案二:

另外一個優化替代方案，我用PROCEDURE處理

首先先弄一個

DELIMITER $$

CREATE PROCEDURE OptimizeAllTables()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE();
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET @query = CONCAT('OPTIMIZE TABLE ', tbl_name);
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END $$

DELIMITER ;

然後以後只要執行 CALL OptimizeAllTables(); 就可以對每個資料表做OPTIMIZE TABLE了。