WordPress如何清理数据库冗余数据（数据表优化教程）

WordPress数据库表说明：

提示：清理优化之前请先备份数据库，以防因失误带来的意外情况！只有做好备份工作才可以有备无患。

PS：主要涉及到的几张表：wp_options，wp_posts，wp_postmeta，wp_commentmeta

定期清理 wp_options 数据表：

wp_options 这个数据表是wordpress设置的全局数据，主要存储的数据是和WordPress后台的设置对应（如博客名、博客地址、基本设置…等），平常在WordPress控制面板–设置 里进行的设置，都会对应的存储在这里。wp_options表里垃圾残留记录会随着时间慢慢积累直至臃肿庞大致使你的数据库执行效率低下拖慢你整个主机的运行。

wp_options 表数据膨胀的主要原因：

1. 在使用的过程中会经常安装、停用或者更换插件，或更换使用不同的主题，并且在使用插件和主题的过程中会在数据库 wp_options 表内增加大量的数据，包括一些冗余的垃圾数据（插件、主题在删除之后没有进行清理，造成残留的无用数据）。
2. 占用数据的大户–RSS缓存，后台的数据调用竟然会放到数据库里面。在wp_options表中发现了大量option_name包含“_transient”的数据，那就是WordPress程序中引入RSSFeed后产生的缓存。正常的WordPress使用过程中，会不断产生这些数据，久而久之，就非常之庞大了。

清理 wp_options 表的处理方法：

1）禁用RSS缓存：打开根目录 wp-config.php 配置文件里面设置。将下面代码添加上去。

// 不去调用RSS缓存。 0 则不缓存 magpie RSS(改为1则缓存)
define('MAGPIE_CACHE_ON', '0');

2）手动清理：进入phpmyadmin或者mysql命令行模式选择对应的数据库，执行下面mysql语句即可：

wp_options 表里的内容，特别是 _transient 、_site开始的都可以删除，这些是控制板订阅缓存（后台首页显示的订阅缓存，居然保存在wp_options表中，数量多体积大）

-- wordpress数据库手动清除Transients（下面两个SQL语句，任选一条执行）
DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%');
DELETE FROM wp_options WHERE option_name REGEXP '_transient_';

PS：可以使用插件 clean options 清理 wp_options 表中的垃圾数据。

清理 wp_posts 表（包括删除修订版本、自动草稿的文章数据）

wordpress的文章有好多：wp_posts表中包括文章种类：文章、修订版本、页面、文章的附件、菜单；其中每种文章又会有很多状态：继承、发布、私有、草稿、自动草稿、回收站。

wp_posts的重要字段含义

post_type（文章类型）：post 表示为文章，revision 表示为修订版本，page 为页面，attachment 是文章的附件信息，nav_menu_item 是菜单。这里我们需要的是文章、页面、和菜单，除此之外的都可以删除。

post_status（文章状态）：inherit 是继承的附件和文章的附带信息，publish 是已经发布、private 是私有的，draft 是草稿，auto-draft 是自动草稿，trash 是在回收站。这里我们需要的是publish的状态的（已经发布的文章、页面和菜单），除此之外的都可以删除。

PS：当然可以根据自己的需求选择删除哪些

造成 wp_posts 表中数据冗余原因：

1. 在写文章的时候，系统会保存很多的中间状态（如修订版本、自动保存等），在文章发布之后其很多的中间状态没有被删除。
2. 点击“写文章”或“新建页面”时产生的，每点击一次就生成一条“自动草稿(auto draft)”的冗余数据。

清理 wp_posts 表的解决办法

进入 phpmyadmin或者mysql命令行模式选择对应的数据库，执行下面mysql语句即可：

-- 删除所有非发布状态帖子信息（只保留已经发布的状态的文章、菜单、页面）
DELETE FROM wp_posts WHERE NOT(post_status = 'publish' AND post_type IN('post','nav_menu_item','page'));

-- 删除全部文章修订版本及所对应的关联数据
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';
 
-- 删除自动保存草稿以及修订版本的文章
delete from wp_posts where (post_status='auto-draft' or post_status='inherit') and post_type='post';

清理 wp_postmeta 表

wp_postmeta表是用来储存文章的元信息，多半是系统或者插件自动生成的，当然在主题使用了自定义字段是数据也是储存在这个表里的。

造成 wp_postmeta 表中数据冗余原因：

1. 文章被删除之后，其在wp_postmeta中的数据理应被删除，在系统中多数情况是系统自动删除，但是由于人为删除文章，系统不知道被删除，就不会删除wp_postmeta表中的数据，造成冗余。
2. 很多主题、插件没有做好及时清除的工作。

清理 wp_postmeta 表的解决办法

执行下列相关的MySQL指令则可以清理出无用的数据

--规矩删除（删除文章中不存在文章的元信息）
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';
 
-- 风险删除（除了这两条还执行了一些其他语句由于有些风险：自己酌情考虑）
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}}';
 
--特殊插件删除（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
 
-- 删除重复的 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
)

在WordPress的后台上传图片或者附件后会在wp_postmeta中生成_wp_attached_file和_wp_attachment_metadata两个项，wp_posts也会记录附件的信息。如果使用FTP工具上传文件，表中就不会有这些信息。

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

以下的这些可以考虑清，或者不清，其实很多人或许没这个项目，因为这些大都由插件产生的。

-- 可选项目
DELETE FROM wp_postmeta WHERE meta_key = '_wp_old_slug';
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';

以上几条语句执行完毕能够删除掉95%以上的数据，算的上是极限优化了，最后考虑到这个数据表并不是很重要，有洁净癖的人可以尝试清空这个表，当然测试清空表会让一些原本的数据丢失。

-- 洁癖删除
TRUNCATE TABLE wp_postmeta;

清理 wp_commentmeta 表

wp_commentmeta 表是WP官方推荐的 Akismet 反垃圾评论防护插件保存的垃圾信息记录（记录管理员用户对垃圾评论的处理结果以及插件自动判断某条评论是否为垃圾评论的相关记录），这个表如果长时间不清理的话，你会发现 wp_commentmeta 数据表会变得越来越大，甚至比我们文章数据库还要大。而且垃圾评论很少见的，建议还是不要用到像Akismet这类插件，或者我们用这个插件之后要定期清空数据（直接清理删除 wp_commentmeta 数据表内容就可以，不要删除这个字段。）。

造成 wp_commentmeta 表中数据冗余原因：

1. 评论被删除之后，其在wp_commentmeta中的数据理应被删除，在系统中多数情况是系统自动删除，但是由于人为删除文章，系统不知道被删除，就不会删除wp_commentmeta表中的数据，造成冗余。
2. 很多主题、插件没有做好及时清除的工作。

清理 wp_commentmeta 表的解决办法

进入phpmyadmin或者mysql命令行模式选择对应的数据库，执行下面mysql语句即可：

--删除孤立的评论元信息（评论删除后残留在wp_commentmeta表中的信息）
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
DELETE FROM wp_commentmeta WHERE meta_key REGEXP 'akismet';
-- 删除记录评论被删除的时间，这些信息用处不是很大
DELETE FROM wp_commentmeta WHERE meta_key LIKE '%trash%';
 
-- 直接全部删除wp_commentmeta数据表内容，影响不会太大，这里面不会涉及重要的数据
TRUNCATE TABLE wp_commentmeta;

清理其他数据表：

-- 删除所有垃圾留言（包括待审、垃圾评论、回收站评论）
DELETE FROM wp_comments WHERE comment_approved != '1';
-- 删除待审评论
DELETE FROM wp_comments WHERE comment_approved = '0';
-- 删除垃圾评论
DELETE FROM wp_comments WHERE comment_approved = 'spam';
-- 删除回收站评论
DELETE FROM wp_comments WHERE comment_approved = 'trash';
 
--孤立的关系信息（文章、评论等删除后残留在wp_term_relationships表中的信息）
DELETE FROM wp_term_relationships WHERE term_taxonomy_id=1 AND object_id NOT IN (SELECT id FROM wp_posts);
 
-- 删除没有的标签（有些文章删除了，但标签还在，WordPress不会自动删除的）
DELETE a,b,c FROM wp_terms AS a
LEFT JOIN wp_term_taxonomy AS c ON a.term_id = c.term_id
LEFT JOIN wp_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE (
c.taxonomy = 'post_tag' AND
c.count = 0
);

优化数据库表

原理：数据库优化不涉及数据的删除，是将数据库的表的状态调整好。在使用phpmyadmin时候，或许您会看到数据库表后面有多余 xxMB 的字样，这个指的是那些已经分配给当前表但是却没有使用的空间。这个多余是没有什么害处的，他不会占用你的空间。当删除一个表的一部分记录时,这些记录仍然保持在一个linked list 中，当插入新数据时会再次使用这些老纪录的位置。所以删除纪录会闲置一些空间造成你说的“多余”。

优化：在phpmyadmin手动 优化或者修复表即可，选择数据库后执行下面SQL语句：

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;

PS：wp_ 是默认的前缀，如果自定义更改过，请根据实际修改即可。

一键优化清理WordPress数据库：

将上面所有清理数据库的语句汇总到一个php文件（SEOClear.php），打开PHP文件后就可以直接优化清理数据了，并且保证是安全的。 粘贴以下代码执行PHP文件即可：

<?php
//wordpress数据库优化清理脚本
$hostname_blog = "localhost";//设定数据库主机，同wp-config.php
$database_blog = "wordpress";//设定数据库名，同wp-config.php
$username_blog = "root";//设定数据库用户名，同wp-config.php
$password_blog = "";//设定数据库密码，同wp-config.php
$blog = mysql_pconnect($hostname_blog, $username_blog, $password_blog) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database_blog, $blog);
mysql_query('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"');
mysql_query('DELETE FROM wp_posts WHERE NOT(post_status = "publish" AND post_type IN("post","nav_menu_item","page")');
mysql_query('DELETE FROM wp_postmeta WHERE meta_key = "_edit_lock"');
mysql_query('DELETE FROM wp_postmeta WHERE meta_key = "_edit_last"');
mysql_query('DELETE FROM wp_commentmeta WHERE meta_key LIKE "%trash%"');
mysql_query('DELETE FROM wp_comments WHERE comment_approved != "1"');
mysql_query('DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL');
mysql_query('DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)');
mysql_query('DELETE FROM wp_term_relationships WHERE term_taxonomy_id=1 AND object_id NOT IN (SELECT id FROM wp_posts)');
mysql_query('DELETE FROM wp_options WHERE option_name REGEXP "_transient_"');
mysql_query('DELETE FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’');
mysql_query('DELETE FROM wp_postmeta WHERE meta_key = ‘_wp_attachment_metadata’');
mysql_query("delete from wp_posts where (post_status='auto-draft' or post_status='inherit') and post_type='post'");
$tablelist = mysql_query("SHOW TABLES");
while($checklist = mysql_fetch_array($tablelist)) {
$optimization=mysql_query("OPTIMIZE TABLE `$checklist[0]`");
}echo 'Done';
//ps记得修改数据库前缀~
//使用时将脚本上传至网站任意目录后并且通过浏览器访问即可一键清理wordprsss数据库。
?>

执行后将会批量删除WordPress产生的冗余文件，修改好相应数据库信息后上传至网站然后使用浏览器直接访问该脚本文件（SEOClear.php）即可优化清理数据库，不过由于一次性执行多个sql查询，所以该脚本访问时响应速度可能会比较长，优化成功后会显示Done。

屏蔽垃圾评论提交到数据库，这样数据库就优化的差不多了

在当前WordPress主题的functions.php文件中加上以下代码即可：

//禁止垃圾评论提交到数据库
function wp_fuckspam($comment) {
if( is_user_logged_in()){ return $comment;} //登录用户无压力...
if( wp_blacklist_check($comment['comment_author'],$comment['comment_author_email'],$comment['comment_author_url'], $comment['comment_content'], $comment['comment_author_IP'], $comment['comment_agent'] )){
header("Content-type: text/html; charset=utf-8");
wp_die('
您评论包含辱骂，过激或者违反法律等言论，或者您的IP已被加入黑名单，如有疑问请联系管理员处理！返回文章页
');
} else {
return $comment;
}
}
add_filter('preprocess_comment', 'wp_fuckspam');