Sweet Baby

这次Blog的恢复，采用的是3年前备份的SQL文件。WordPress是个很贴心的系统，在后台的许多操作都会留下残留数据。数据库中的冗余数据就会很多。所以决定来清理和优化一下Wordpress的数据库，让Wordpress能够快速工作。百度了一下，发现一个很好用的插件——WP Clean Up。

WP Clean Up 的插件作者网址：http://boliquan.com/wp-clean-up/

WP Clean Up 的插件WP官方网址：http://wordpress.org/plugins/wp-clean-up/

WP Clean Up 包含2个功能，分别是数据库清理和数据表优化。我使用后感觉这个插件很棒，在因此推荐给大家。

/*wo version*/

set sql_safe_updates=0;

use wordpress;

delete from wp_posts where post_type=’post’ && post_status!=’publish’;

delete from wp_posts where post_type=’revision’ && post_status!=’publish’;

如果你不方便安装插件，且对SQL语句较熟悉，也可以尝试直接用SQL语句来清理和优化数据库。初次使用的话，建议先备份一下数据库。

清理数据库的残留无效数据，可以用以下的语句（语句来源于WP Clean Up插件源代码）：

DELETE FROM wp_posts WHERE post_type = ‘revision’;

DELETE FROM wp_posts WHERE post_status = ‘draft’;

DELETE FROM wp_posts WHERE post_status = ‘auto-draft’;

DELETE FROM wp_comments WHERE comment_approved = ‘0’;

DELETE FROM wp_comments WHERE comment_approved = ‘spam’;

DELETE FROM wp_comments WHERE comment_approved = ‘trash’;

DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

DELETE FROM wp_term_relationships WHERE term_taxonomy_id=1 AND object_id NOT IN (SELECT id FROM wp_posts);

DELETE FROM wp_options WHERE option_name LIKE ‘_site_transient_browser_%’ OR option_name LIKE ‘_site_transient_timeout_browser_%’ OR option_name LIKE ‘_transient_feed_%’ OR option_name LIKE ‘_transient_timeout_feed_%’;

/*wo version*/

DELETE tr FROM wp_term_relationships tr

INNER JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)

WHERE tt.taxonomy != ‘link_category’

AND tr.object_id NOT IN (SELECT ID FROM wp_posts);

优化数据表，可以用以下的语句：

OPTIMIZE TABLE wp_commentmeta;

OPTIMIZE TABLE wp_comments;

OPTIMIZE TABLE wp_links;

OPTIMIZE TABLE wp_options;

OPTIMIZE TABLE wp_postmeta;

OPTIMIZE TABLE wp_posts;

OPTIMIZE TABLE wp_terms;

OPTIMIZE TABLE wp_term_relationships;

OPTIMIZE TABLE wp_term_taxonomy;

OPTIMIZE TABLE wp_usermeta;

OPTIMIZE TABLE wp_users;