调整数据库表结构,搞定 WordPress 数据库查询缓慢问题

调整数据库表结构,搞定 WordPress 数据库查询缓慢问题

同事的基于 WordPress 搭建的网站,因为数据越来越多,变得慢,我从 PHP slow log 里面看出是 WordPress 有些查询总是很慢,即使已经安装了页面缓存插件,但是由于页面众多,命中率不高,所以加速效果也不明显,而且由于界面经常改版,页面缓存需要清空重新生成,进一步降低了缓存的效果。反正就是不流畅,有点慢。
 
看了下服务器配置虽然不高,但是也不至于打开一个一面要 4 秒钟吧,而且 CPU 占用率奇高,虽然说升级硬件可以缓解,但根源还是程序效率的问题,所以不妨先趁性能出现问题的情况下,优化程序,解决程序的性能问题后,再升级服务器硬件,这样效果才持久。
 
于是乎打算从表结构上作些优化。主要影响性能的,是两张表:wp_postmeta、wp_term_relationships、wp_posts
 
先看一下最终结果:
<span>调整数据库表结构,搞定 WordPress 数据库查询缓慢问题</span>
可以看到 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 的情况的,我验证了一下:
 
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_old_slug’
‘_thumbnail_id’
‘_edit_lock’
*/
 
确实是这样,但是看了下都是 WordPress 运行过程中产生的垃圾数据,是可以无副作用删除的,那么此路是可行的。
 
好,那么,先先清理下垃圾数据:
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}}’;

 

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

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

必须先 ADD UNIQUE(`meta_id`),才能 DROP meta_id 的 PRIMARY KEY。
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。
 
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);

 

 
好了,先看下 post 表 id 的分布,我的是从 id 从 5万到11万,先给 posts 表分好区:
SELECT id FROM wp_posts order by id asc;
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 表,也如法炮制,这样再查询 post 的 meta,不但不用全表扫描,只用扫分区内的数据了,而且还可以走索引 :
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)
);

 

 
另外, 这个表的查询也比较耗时,把 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 的性能参数做了适当的调整:
<span>调整数据库表结构,搞定 WordPress 数据库查询缓慢问题</span>
 
性能调整对应的参数表格:
 
<span>调整数据库表结构,搞定 WordPress 数据库查询缓慢问题</span>
 
 
 
增大了 sort_buffer_size ,使得原本【创建临时表到磁盘】有 51%,增加 tmp_table_size 调整后降低到 29.36% 。
 
分区后,原本未缓存的页面打开要 4s-5s,现在 2-3s 就可以打开啦。观察一段时间再升级下服务器。
 
 
 
CPU 的使用率也下降了不少(那两个剧烈波动的折线请忽略,那个是之前别的进程hang了,跟本次无关)。
 
<span>调整数据库表结构,搞定 WordPress 数据库查询缓慢问题</span>
 
然后找了个网站速度测试工具,输入网址测试一下:
 
<span>调整数据库表结构,搞定 WordPress 数据库查询缓慢问题</span>
 
另外我本来是熟 SQL Server 数据库优化的,MySQL 的数据库优化其实一直都是以过去 SQL Server 优化经验为指导的,有些地方可能存在盲区和不足,如果有还请指出,谢谢!
 
 

 

 

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/119605.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 【一步一个脚印】Tomcat+MySQL为自己的APP打造服务器(1)服务器环境搭建[通俗易懂]

    【一步一个脚印】Tomcat+MySQL为自己的APP打造服务器(1)服务器环境搭建[通俗易懂]做Android开发一年多了,虽然不敢说有多精通,但也相对熟悉。做久了就会发现Android在行外人眼中是多么高深(包括IOS也一样),但是我们自己知道其实Android和Web前端其实本质上是没有多大的区别,只不过一个是显示在浏览器中,一个是显示在手机上而已。慢慢地,你会发现移动端(Android、IOS、WinPhone、WebAPP)其实涉及到的业务逻辑是很少很少的——无关孰优孰劣,这是分工的不同,移动端毕竟硬件资源有限,适合于简单的计算和交互处理,业务逻辑什么的放在资源更富裕的服

  • maven打包时打包指定的lib文件夹

    maven打包时打包指定的lib文件夹今天在打包自己的springboot项目时遇到了问题,报找不到类和符号。因为我有些依赖是放在项目lib文件夹中,那么打包的时候要连把它一起打包。修改pom.xml,添加一下内容:<build><plugins><plugin><groupId>or…

  • java GC算法「建议收藏」

    java GC算法「建议收藏」一般来说,程序使用内存的方式遵循先向操作系统申请一块内存,使用内存,使用完毕之后释放内存归还给操作系统。然而在传统的C/C++等要求显式释放内存的编程语言中,记得在合适的时候释放内存是一个很有难度的工作,因此Java等编程语言都提供了基于垃圾回收算法的内存管理机制:垃圾内存回收算法常见的垃圾回收算法有引用计数法(ReferenceCounting)、标注并清理(MarkandSweep…

  • 计算机启动显示安装程序正在启动服务,安装程序正在启动服务需要多久

    计算机启动显示安装程序正在启动服务,安装程序正在启动服务需要多久大家好,我是时间财富网智能客服时间君,上述问题将由我为大家进行解答。安装程序正在启动服务需要多久,这个主要看软件大小,安装完成就会自动运行的,要是系统不断的提示,这个就不一定了,时间可能会很久的,需要耐心等待。程序,香港和台湾对英文procedure的中文翻译,编程语言中的procedure在大陆翻译为“过程”,在港台和其他领域则翻译为“程序”。在大陆于计算机科学、电脑工程、电子工程、电机工程、机…

  • awk linux 数组,Linux中的awk数组的基本使用方法

    awk linux 数组,Linux中的awk数组的基本使用方法1.awk数组描述在其他的编程语言中,数组的下标都是从0开始的,也就是说,如果想反向引用数组中的第一个元素,则需要引用对应的下标[0],在awk中数组也是通过引用下标的方法,但是在awk中数组的下标是从1开始的,在其他语言中,你可能会习惯于先“声明”一个数组,在awk中,则不用这样,直接为数组的元素赋值即可(其实如果自己给数组赋值,下标从1或者从0开始那就无所谓了!)2.在声明数组时,可能值很多…

  • ioszip怎么解压_苹果解压app推荐

    ioszip怎么解压_苹果解压app推荐本文介绍了iOS中如何实现gzip解压!

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号