先看这里：WordPress数据类型选MyISAM还是InnoDB。

如果你需要转换成innodb，请执行：

ALTER TABLE wp_downloads ENGINE=InnoDB;

如果出现：#1067 – Invalid default value…错误

修改下数据库配置文件，在[mysqld]下面添加：

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

mysql8.0添加上面的就行了，如果是5.7添加：

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

这个1067错误表示，之前的版本不兼容InnoDB，因为InnoDB不支持时间为0的，所以改下参数。

好了，现在开始优化Wordpress数据库，记得根据自己的数据库前缀改。

同时数据库默认类型改成：(也是修改数据库配置文件)

default_storage_engine = InnoDB

1：删除已被删掉的评论记录

DELETE  FROM wp_comments WHERE comment_approved = 'trash';
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

2：删除wp_postmeta表里面不存在文章的元信息

DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT post_id FROM wp_posts);

3：删除wp_postmeta表里面不用的_edit_lock、_edit_last字段

DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock';
DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';

4：删除wp_postmeta表里面旧的别名信息

DELETE FROM wp_postmeta WHERE meta_key = '_wp_old_slug';

5：删除wp_postmeta表其它冗余信息

DELETE FROM wp_postmeta WHERE meta_key = '_revision-control';
DELETE FROM wp_postmeta WHERE meta_value = '{{unknown}}';

有的人会删掉wp_postmeta表里面附件的信息，不过我不建议删掉这个，因为删掉这个，后台媒体管理就不完整了。

6：删除文章修订

DELETE FROM wp_postmeta WHERE post_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'

限制修订查看：WordPress速度优化总结，里面有提到。

7：删除之前的pingback记录和引用(如果网站一开始就关闭了，就用不到)

DELETE FROM wp_comments WHERE comment_type = 'pingback';
DELETE FROM wp_comments WHERE comment_type = 'trackback';

8：删除与文章无关的标签

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

优化全部多余记录：

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;

当然也可以使用数据库优化插件WP Optimize，以上代码部分参考：

https://wpengine.com/support/database-optimization-best-practices/

https://premium.wpmudev.org/blog/mysql-databases/

CMS, wordpress

Tagged in: