清理WordPress的 wp_postmeta 数据表全攻略[终极版]

wp_postmeta 是WordPress查询最慢的一张表，字段以一张多形式，对应存放了文章、页面、自定义内容的分类目录、文章查看数、封面图片，自定义的字段等数据。

1、 清理WordPress 运行过程中产生的垃圾数据

//规矩删除（删除文章中不存在文章的元信息）
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT post_id FROM wp_posts);

删除了 0 行。 (查询花费 0.0001 秒。)
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT post_id FROM wp_posts);

// 安全删除（删除_edit_lock和_edit_last条目是安全的）
DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock';
DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';

这两个 博主清理了 24000多行数据  博客并无任何异常

// 风险删除（除了这两条还执行了一些其他语句由于有些风险：自己酌情考虑）

DELETE FROM wp_postmeta WHERE meta_key = '_wp_old_slug';

DELETE FROM wp_postmeta WHERE meta_key = '_revision-control';

DELETE FROM wp_postmeta WHERE meta_value = '{{unknown}}'; 这一条博主不推荐删除  存在未知风险!

这五条语句执行完毕能够删除掉95%以上的数据，算的上是极限优化了。通过上面对 wp_postmeta 数据表的优化和清理，我们的数据库瘦身不少，执行效率也有所提升。

删除了 1432 行。 (查询花费 0.0155 秒。)
DELETE FROM wp_postmeta WHERE meta_key = '_wp_old_slug';

删除了 0 行。 (查询花费 0.0001 秒。)
DELETE FROM wp_postmeta WHERE meta_key = '_revision-control';

//特殊插件删除（postnav插件会记录每个文章的访问数，如果不需要，可以删除）博主不推荐删除  文章浏览量是重要的数据!
DELETE FROM wp_postmeta WHERE meta_key = 'views';

// 删除孤立的文章元信息（当文章的记录删除后，还有日志扩展表postmeta的数据还没有删除，也要人工清理下）
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL

删除了 0 行。 (查询花费 0.1775 秒。)
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

// 删除重复的 meta key 和 value 记录，仅保留最新的一个
DELETE FROM wp_postmeta WHERE meta_id IN (
select * from (select meta_id FROM wp_postmeta pm WHERE
meta_id NOT IN (SELECT max(meta_id) FROM wp_postmeta pm2 where pm2.post_id=pm.post_id and pm2.meta_key=pm.meta_key)
) as g1
)

删除了 558 行。 (查询花费 11.1984 秒。)
DELETE FROM wp_postmeta WHERE meta_id IN ( select * from (select meta_id FROM wp_postmeta pm WHERE meta_id NOT IN (SELECT max(meta_id) FROM wp_postmeta pm2 where pm2.post_id=pm.post_id and pm2.meta_key=pm.meta_key) ) as g1 );

2、删除 wordpress后台上传的图片或者附件信息

// 特殊操作删除
DELETE FROM wp_postmeta WHERE meta_key = '_wp_attached_file';
DELETE FROM wp_postmeta WHERE meta_key = '_wp_attachment_metadata';

注：WordPress 在开启了文章的版本控制情况下，存在了重复 post 和 meta key，数据表ID不是唯一约束。

3、其它删除 杂项未测试

// 可选项目
DELETE FROM wp_postmeta WHERE meta_key = 'jd_tweet_this';
DELETE FROM wp_postmeta WHERE meta_key = 'wp_jd_clig';
DELETE FROM wp_postmeta WHERE meta_key = 'wp_jd_target';
DELETE FROM wp_postmeta WHERE meta_key = 'nofollow4post';
DELETE FROM wp_postmeta WHERE meta_key = 'ratings_score';
DELETE FROM wp_postmeta WHERE meta_key = 'ratings_users';
DELETE FROM wp_postmeta WHERE meta_key = 'ratings_average';
DELETE FROM wp_postmeta WHERE meta_key = 'wp_noextrenallinks_mask_links';
DELETE FROM wp_postmeta WHERE meta_key = '_wp_page_template';
DELETE FROM wp_postmeta WHERE meta_key = '_sexybookmarks_permaHash';
DELETE FROM wp_postmeta WHERE meta_key = '_sexybookmarks_shortUrl';

注：此删除有风险，须备份SQL.。不熟悉wordpress数据表字段的话，最好采用sweep插件安全删除，虽然慢点。