欢迎光临!
登录
  • About me
  • 文章归档
  • 标签云集
  • 友情链接
  • 随机文章
  • 试试手气
  • 站点地图
  • 给我留言

岂止手帐岂止手帐

×
MENU
  • About me
  • 文章归档
  • 标签云集
  • 友情链接
  • 随机文章
  • 试试手气
  • 站点地图
  • 给我留言
首页资源分享经验分享wp_postmeta索引优化

wp_postmeta索引优化

  • wp_postmeta索引优化已关闭评论
  • 928 views
  • A+
所属分类:经验分享
摘要

主要影响性能的,是两张表:wp_postmeta、wp_term_relationships、wp_posts,下面主要来讲wp_postmeta的优化

文章目录[隐藏]

  • 优化过程
    • 业务和表的关系
    • 表之间的关系
    • 数据清理完毕,那么可以开始建立分区表了

基于 WordPress 搭建的,因为数据越来越多,变得慢,我从 PHP slow log 里面看出是 WordPress 有些查询总是很慢,即使已经安装了页面缓存插件,但是由于页面众多,命中率不高,所以加速效果也不明显,而且由于界面经常改版,页面缓存需要清空重新生成,进一步降低了缓存的效果。反正就是不流畅,有点慢。

看了下服务器配置虽然不高,但是也不至于打开一个一面要 4 秒钟吧,而且 CPU 占用率奇高,虽然说升级硬件可以缓解,但根源还是程序效率的问题,所以不妨先趁性能出现问题的情况下,优化程序,解决程序的性能问题后,再升级服务器硬件,这样效果才持久。

于是乎打算从表结构上作些优化。主要影响性能的,是两张表:wp_postmeta、wp_term_relationships、wp_posts

先看一下最终结果:

wp_postmeta索引优化

可以看到 CPU 明显下降了不少(那两个剧烈波动的折线请忽略,跟本文无关)。

优化过程

先介绍一下本次优化涉及到的数据库表结构:

业务和表的关系

内容类型数据表
文章wp_posts
页面wp_posts
自定义文章类型wp_posts
附件wp_posts
导航菜单wp_posts
文章元数据wp_post_meta
分类目录wp_terms
标签wp_terms
自定义分类法wp_term_taxonomy

表之间的关系

数据表存储的数据关联到
wp_posts文章、页面、附件、版本、导航菜单项目wp_postmeta (通过post_id关联)
wp_postmeta每个文章的元数据wp_posts (通过 post_id关联)
wp_term_relationships文章和自定义分类法之间的关系wp_posts (通过 post_id 关联)wp_term_taxonomy (通过term_taxonomy_id 关联)
wp_term_taxonomy自定义分类法(包括默认的分类目录和标签)wp_term_relationships(通过 term_taxonomy_id关联)
wp_terms关联到分类法中的分类目录,标签和自定义分类项目wp_term_taxonomy (通过term_id 关联)

wp_postmeta 是查询最慢的一张表,它存放文章/页面/自定义内容(wp_posts)的元数据信息,所谓元数据,也包括如文章查看数、封面图片,还有你自定义的字段。

按理说,一篇文章(wp_posts),对应 wp_postmeta 一行记录,为啥会慢呢?原因是,WordPress 把 wp_postmeta 设计成了一张纵表,而且没有恰当的索引。

关于横表和纵表,横表是我们做项目最常用的,不清楚这个概念的朋友,看下面的的小实验就明白了:

普通横表 STUDENT_SCORE 有语文成绩、英语成绩等7个KPI指标,三个学生的三条记录:

SQL> SELECT * FROM STUDENT_SCORE;

Id CHINESE_SCORE ENGLISH_SCORE MATH_SOCRE PHYSICAL_SCORE SPORTS_SCORE CHEMICAL_SCORE BIOLOGICAL_SCORE

----------- ------------- ------------- ---------- -------------- ------------ -------------- ----------------

10001 87.4 63 92 86 75 85 89

10002 91 89 98 62 76 82 73

10006 74 63 57 42 76 59 67

对应于纵表/竖表,这三个学生的7个KPI指标需要21条记录才能描述清楚:

SQL> SELECT * FROM STUDENT_SCORE;

Id FieldName Value

----------- --------------------- ----------

10001 CHINESE_SCORE 87.4

10001 ENGLISH_SCORE 63

10001 MATH_SOCRE 92

10001 PHYSICAL_SCORE 86

10001 SPORTS_SCORE 75

10001 CHEMICAL_SCORE 85

10001 BIOLOGICAL_SCORE 89

10002 CHINESE_SCORE 91

10002 ENGLISH_SCORE 89

10002 MATH_SOCRE 98

10002 PHYSICAL_SCORE 62

10002 SPORTS_SCORE 76

10002 CHEMICAL_SCORE 82

10002 BIOLOGICAL_SCORE 73

10006 CHINESE_SCORE 74

10006 ENGLISH_SCORE 63

10006 MATH_SOCRE 57

10006 PHYSICAL_SCORE 42

10006 SPORTS_SCORE 76

10006 CHEMICAL_SCORE 59

10006 BIOLOGICAL_SCORE 67

所以我们从这个小实验中可以看到,横表转成纵表/竖表,对应的记录会翻倍增长,这对应于数据量大的表或宽表,都是一件不好的消息。很多时候,数据量上去了,性能问题就出来了。

分析得到 WordPress 从来是不会根据 meta_id 去查 postmeta 表的,都是根据 post_id 去查 post 的单个 meta 信息或者所有 meta key 和 value,所以原本的主键 meta_id 仍然保持自增(因为 的,它就仅仅是一个自增 ID)

提升性能的办法是把 post_id 和 meta_key 改为主键,然后根据 post_id 做分区表,这样,这样有两个好处,一是查询时,可以根据 post_id 去读区分区表的数据了,不用再全表查找了,另外是这俩字段组成唯一约束和索引了,查询速度自然会加快,而原本的主键 meta_id 仍然保持自增,不会影响到原本的业务逻辑。

WordPress 默认没有为 wp_postmeta 的表没有设定 post_id 和 meta_key 的唯一约束,也就是说,是存在一个 post 再 postmeta 表有多个同样的的 meta key 和 value 的情况的,我验证了一下:

wp_postmeta索引优化

1
2
3
4
5
6
7
8
9
10
SELECT *
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
    )
SELECT distinct meta_key From wp_postmeta Group By post_id,meta_key Having Count(*)>1
 

wp_postmeta索引优化

返回内容大致如下:

/*

'_wp_old_slug'

'_thumbnail_id'

'_edit_lock'

*/

确实是这样,但是看了下都是 WordPress 运行过程中产生的垃圾数据,是可以无副作用删除的,那么此路是可行的。

好,那么,先先清理下垃圾数据:

wp_postmeta索引优化

1
2
3
4
5
6
7
8
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 = '_revision-control';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT post_id FROM wp_posts);
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}}’;
 

wp_postmeta索引优化

然后,删除掉重复的 meta key 和 value 记录,仅保留最新的一个

wp_postmeta索引优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
)
 

wp_postmeta索引优化

这里存在一个问题,就是 WordPress 在开启了文章的版本控制情况下,是存在插入重复 post 和 meta key 的情况的,数据库改成唯一约束后会报错,或者其它插件会这么做,解决办法是,WordPress 里面 Hook 一下 add metadata 函数,insert 前先 check 是否已经 exists,另外就是数据库里面加个 Trigger 做判断,如果已存在,就更新。

数据清理完毕,那么可以开始建立分区表了

必须先 ADD UNIQUE(meta_id),才能 DROP meta_id 的 PRIMARY KEY。

1
2
3
4
5
ALTER TABLE `wp_postmeta`
ADD UNIQUE INDEX `UNQ_meta_id` (`meta_id` ASC);
ALTER TABLE `wp_postmeta`
DROP PRIMARY KEY (`meta_id`);
 

再 DROP 掉 meta_id 的 UNIQUE,这是因为后面分区,要求 RANGE 分区列的UNIQUE INDEX 必须包含所有 primary key ,即任意 UNIQUE INDEX 都要包含 post_id,meta_key 分区函数列,否则分区函数是无法创建,会报错误:Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function。

wp_postmeta索引优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ALTER TABLE `wp_postmeta`
DROP UNIQUE INDEX `UNQ_meta_id` (`meta_id` ASC);
ALTER TABLE `wp_postmeta`
ADD PRIMARY KEY (`post_id`, `meta_key`);
ALTER TABLE `wp_postmeta`
CHANGE COLUMN `meta_key` `meta_key` VARCHAR(255) NOT NULL ,
CHANGE COLUMN `post_id` `post_id` BIGINT(20) UNSIGNED NOT NULL ;
ALTER TABLE `wp_postmeta`
ADD UNIQUE INDEX `UNQ_post_id_meta_key` (`post_id` ASC, `meta_key` ASC),/* 这句可以加可以不加,因为已经是 PRIMARY KEY */
ADD UNIQUE INDEX `UNQ_meta_id_post_id_meta_key` (`meta_id` ASC, `post_id` ASC, `meta_key` ASC);
 

wp_postmeta索引优化

好了,先看下 post 表 id 的分布,我的是从 id 从 5万到11万,先给 posts 表分好区:

SELECT id FROM wp_posts order by id asc;

wp_postmeta索引优化

1
2
3
4
5
6
7
8
9
10
ALTER TABLE wp_posts PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN (60000),
    PARTITION p1 VALUES LESS THAN (70000),
    PARTITION p2 VALUES LESS THAN (80000),
    PARTITION p3 VALUES LESS THAN (90000),
    PARTITION p4 VALUES LESS THAN (100000),
    PARTITION p5 VALUES LESS THAN (110000),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);
 

wp_postmeta索引优化

wp_postmeta 表,也如法炮制,这样再查询 post 的 meta,不但不用全表扫描,只用扫分区内的数据了,而且还可以走索引 :

wp_postmeta索引优化

1
2
3
4
5
6
7
8
9
10
ALTER TABLE wp_postmeta PARTITION BY RANGE COLUMNS(post_id,meta_key) (
    PARTITION p0 VALUES LESS THAN (60000,MAXVALUE),
    PARTITION p1 VALUES LESS THAN (70000,MAXVALUE),
    PARTITION p2 VALUES LESS THAN (80000,MAXVALUE),
    PARTITION p3 VALUES LESS THAN (90000,MAXVALUE),
    PARTITION p4 VALUES LESS THAN (100000,MAXVALUE),
    PARTITION p5 VALUES LESS THAN (110000,MAXVALUE),
    PARTITION p6 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
 

wp_postmeta索引优化

另外, 这个表的查询也比较耗时,把 object_id,term_taxonomy_id 改为主键后,也分下区:

ALTER TABLE wp_term_relationships PARTITION BY RANGE COLUMNS(object_id,term_taxonomy_id) (

PARTITION p0 VALUES LESS THAN (60000,MAXVALUE),

PARTITION p1 VALUES LESS THAN (70000,MAXVALUE),

PARTITION p2 VALUES LESS THAN (80000,MAXVALUE),

PARTITION p3 VALUES LESS THAN (90000,MAXVALUE),

PARTITION p4 VALUES LESS THAN (100000,MAXVALUE),

PARTITION p5 VALUES LESS THAN (110000,MAXVALUE),

PARTITION p6 VALUES LESS THAN (MAXVALUE,MAXVALUE)

);

最后,顺便根据 MySQL 的统计信息,对 MySQL 的性能参数做了适当的调整:

wp_postmeta索引优化

性能调整对应的参数表格:

wp_postmeta索引优化

增大了 sort_buffer_size ,使得原本【创建临时表到磁盘】有 51%,增加 tmp_table_size 调整后降低到 29.36% 。

分区后,原本未缓存的页面打开要 4s-5s,现在 2-3s 就可以打开啦。观察一段时间再升级下服务器。

查看效果( 这不是干货 ):https://www.888meinv.com

CPU 的使用率也下降了不少(那两个剧烈波动的折线请忽略,那个是之前别的进程hang了,跟本次无关)。

wp_postmeta索引优化

weinxin
我的微信公众号
我的微信公众号扫一扫
赞 0
赏
分享
  • DBA
  • wp_postmeta
  • 优化
  • 数据库优化
  • 版权声明:本站原创文章,于2018年4月13日09:54:43,由 音速灰行的起子 发表,共 4097 字。
  • 转载请注明:wp_postmeta索引优化 | 岂止手帐
mysqldump的全备及分库分表备份记录
mysqldump的全备及分库分表备份记录
linux N卡 steam启动失败解决方案 (32位 库缺失)
linux N卡 steam启动失败解决方案 (32位 库缺失)
【分享发现】2018最新免费申请9位QQ号码通道
【分享发现】2018最新免费申请9位QQ号码通道
[攻略] 无法语音或语音只能30秒的解决办法(更新贴图和找不到在那关的解决办法)
[攻略] 无法语音或语音只能30秒的解决办法(更新贴图和找不到在那关的解决办法)

最近更新过的文章

  • 编译安装php7 编译参数
  • openldap
  • jumpserver mysql1071问题(和其他python mysql问题类似)mysql Specified key was too long
  • RAISR:Google的图像压缩黑科技
  • ZooKeeper 配置与安装

本站推荐

  • 宝刀未老,犹有钝时-WordPress合并优化压缩神器Better WordPress Minify宝刀未老,犹有钝时-WordPress合并优化压缩神器Better WordPress Minify 1,212 views 0
  • 知更鸟主题begin各版本主题使用指南[精细版]知更鸟主题begin各版本主题使用指南[精细版] 1,957 views 0
  • nginx web应用全方位优化手册nginx web应用全方位优化手册 2,969 views 0
上一篇
知更鸟主题 begin-lts主题 登录用户无法评论解决
下一篇
[攻略] 无法语音或语音只能30秒的解决办法(更新贴图和找不到在那关的解决办法)

文章导航

经验分享

  • 如何关闭浏览器的HSTS功能如何关闭浏览器的HSTS功能09/29 2,985 views
  • 自学编程之思想自学编程之思想10/31 561 views
  • javascripts实现调皮的标题javascripts实现调皮的标题02/23 942 views
  • 页面底部实现本页共执行n次查询操作耗时y秒的格式页面底部实现本页共执行n次查询操作耗时y秒的格式03/01 1,135 views
  • WordPress禁止版本修订历史、自动保存和自动草稿WordPress禁止版本修订历史、自动保存和自动草稿03/07 819 views
  • 实用技能批量替换标前缀实用技能批量替换标前缀04/03 671 views
  • 知更鸟主题 begin-lts主题 登录用户无法评论解决知更鸟主题 begin-lts主题 登录用户无法评论解决04/11 2,439 views
  • wp_postmeta索引优化04/13 928 views
  • [攻略] 无法语音或语音只能30秒的解决办法(更新贴图和找不到在那关的解决办法)[攻略] 无法语音或语音只能30秒的解决办法(更新贴图和找不到在那关的解决办法)04/23 674 views
  • 【分享发现】2018最新免费申请9位QQ号码通道【分享发现】2018最新免费申请9位QQ号码通道04/28 2,738 views

同标签文章

  • linux N卡 steam启动失败解决方案 (32位 库缺失)linux N卡 steam启动失败解决方案 (32位 库缺失)07/19 1,922 views
  • 什么是kms? KMS 服务 a to z什么是kms? KMS 服务 a to z05/02 1,202 views
  • 五个免费通配符域名证书在线申请网站(4+1)Lets Encrypt trustAisa五个免费通配符域名证书在线申请网站(4+1)Lets Encrypt trustAisa04/23 3,604 views
  • 酷看神器——没有VIP照样看视频的追剧追影神器酷看神器——没有VIP照样看视频的追剧追影神器04/23 2,293 views
  • 054-老男孩教育每日一题-使用find命令的时候 |xargs(管道xargs)与-exec有什么区别?054-老男孩教育每日一题-使用find命令的时候 |xargs(管道xargs)与-exec有什么区别?04/22 675 views

分类目录

  • 前端
  • 后端
  • 应用
  • 应用运维
  • 技术生活
  • 数据库
  • 模板主题
  • 测试
  • 监控运维
  • 知识搬砖
  • 科技资讯
  • 精品插件
  • 系统运维
  • 经验分享
  • 网站源码
  • 网络建站
  • 萌界推荐
  • 虚拟化
  • 视频教程
  • 资源分享
  • 软件分享
  • 运维
  • 运维每日一题

关于本站

一个以个人网络搬砖生活为主题,分享个人所见、所得及所闻的有趣博客,专注于分享各种技术资源,wordpress技巧,美化代码,电影音乐,热点信息,实用软件等。既不是大神,也不NB,只是在生活不断折腾中找到乐趣。

玩转本站

  • About me
  • 文章归档
  • 标签云集
  • 友情链接
  • 随机文章
  • 试试手气
  • 站点地图
  • 给我留言

更多精彩

  • 博客112收录
  • 中国博客联盟

本页共执行97次查询操作耗时1.718秒 使用内存1.21MB

Copyright ©
的蓝色飞船 版权所有
本站由
提供图片和动态cdn支持
浙ICP备17009889号网站运行:0年348天22时26分48秒

登录找回密码

输入用户名或电子邮箱地址,您会收到一封新密码链接的电子邮件。

  • 文章目录
  • 繁
  • 微信

    微信
    在线咨询
  • icon
蕾姆
拉姆
×
订阅图标按钮
  • 随机推荐:《233,笑死我了,记录真假b站的css实现》( 阅读1,185 views次 |坐等沙发)
  • 随机推荐:《nginx 多条件联合判断的变通之法》( 阅读1,183 views次 |坐等沙发)
  • 随机推荐:《真·“轻轻松松破假象”官网》( 阅读987 views次 |真·“轻轻松松破假象”官网已关闭评论)
  • 随机推荐:《h5.udrig.com的api是什么鬼?》( 阅读2,097 views次 |坐等沙发)
  • 随机推荐:《phpstorm+WebStorm 2017版激活和汉化方法》( 阅读1,989 views次 |1条评论)
//360收录