CMS wordpress

wordpress数据库优化

先看这里: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 meta_key = '_edit_lock';
DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';

3:删除文章修订

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速度优化总结,里面有提到。

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

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

5:删除与文章无关的标签

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/

我也不知道说点啥,你们自己看吧~ 联系我

你可能喜欢

没有留意

    发表评论