WordPress网站提速优化之清理数据库冗余数据

三、分享下小 C 自用的清理方法

方法一

小 C 刚清理的时候先备份数据库，在执行下面的 SQL。

#查询修订版本
SELECT COUNT(*) FROM wp_posts WHERE post_type='revision';
#查询并删除其他表的子链
SELECT * FROM wp_postmeta WHERE post_id IN (SELECT id FROM wp_posts WHERE post_type='revision');
DELETE FROM wp_postmeta WHERE post_id IN(SELECT id FROM wp_posts WHERE post_type='revision');
#查询并删除其他表的子链
SELECT * FROM wp_term_relationships WHERE object_id IN (SELECT id FROM wp_posts WHERE post_type='revision');
DELETE FROM wp_term_relationships WHERE object_id IN (SELECT id FROM wp_posts WHERE post_type='revision');
#删除修订版本
#delete from wp_posts where post_type='revision';
DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision';
#其它查询
SELECT COUNT(*) FROM wp_postmeta WHERE meta_key='_edit_lock';
SELECT COUNT(*) FROM wp_postmeta WHERE meta_key='_edit_last';
SELECT * FROM wp_postmeta WHERE post_id NOT IN (SELECT post_id FROM wp_posts);
#其他删除
DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock';
DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT post_id FROM wp_posts);

小 C 执行完发现不够彻底就有了下面的方法二。

方法二

#这个删除的东西有点多，谨慎执行，建议执行前先备份，否则后果自负
SELECT * FROM wp_posts WHERE NOT(post_status = 'publish' AND post_type IN('post','nav_menu_item','page'));
DELETE FROM wp_posts WHERE NOT(post_status = 'publish' AND post_type IN('post','nav_menu_item','page'));
SELECT * FROM wp_postmeta WHERE meta_key IN ('_edit_lock', '_edit_last', '_wp_old_slug', '_revision-control', '{{unknown}}', '_wp_attached_file', '_wp_attachment_metadata');
DELETE FROM wp_postmeta WHERE meta_key IN ('_edit_lock', '_edit_last', '_wp_old_slug', '_revision-control', '{{unknown}}', '_wp_attached_file', '_wp_attachment_metadata');
SELECT * FROM wp_postmeta WHERE post_id NOT IN (SELECT id FROM wp_posts);
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT id FROM wp_posts);
SELECT * FROM wp_comments WHERE comment_approved LIKE '%trash%';
DELETE FROM wp_comments WHERE comment_approved LIKE '%trash%';
SELECT * FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
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_term_relationships;
OPTIMIZE TABLE wp_term_taxonomy;
OPTIMIZE TABLE wp_termmeta;
OPTIMIZE TABLE wp_terms;
OPTIMIZE TABLE wp_usermeta;
OPTIMIZE TABLE wp_users;

注意：执行方法二会将多媒体中的图片链接清理掉。

四、必看内容

执行前先备份，如果你不会折腾建议你放弃吧！如果你数据库垃圾数据过多又想折腾，但不会可以加群 247482771，让小 C 协助你折腾。

运行 SQL 时需先备份，对于未知错误而没备份的后果自负。

执行此文章的任何代码，都需要谨慎谨慎再谨慎，否则后果自负。

最后啰嗦下备份、备份、备份！