WordPress大数据库查询优化

基于 WordPress 搭建的网站，因为数据越来越多，变得慢，怎样才能优化好呢，今天给大家分享的是由 MySQL 查询导致的 CPU 超负荷的解决方法。

一、Wordpress查询语句优化

WordPress在查询post列表时，默认会同时把文章数量也查询出来，具体语句如下：

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' ) ORDER BY wp_posts.post_date DESC LIMIT 0, 20
SELECT FOUND_ROWS()

这在网站数据量小的时候，不会引起什么问题，但是当post数量到10w+的时候，这个就是一条必现的慢查询，首页、分类、标签、搜索页面，只要用到这几个函数，就都会使用SQL_CALC_FOUND_ROWS这个方式。

解决方法一：

彻底禁用SQL_CALC_FOUND_ROWS，以下放在functions.php文件即可：

add_action('pre_get_posts', 'wndt_post_filter');
function wndt_post_filter($query) {
if (is_admin() or !$query->is_main_query()) {
return $query;
}

// 禁止查询 SQL_CALC_FOUND_ROWS
$query->set('no_found_rows', true);
}

解决方法二：

如果仍然需要查询文章数量，使用更加高效的EXPLAIN方式代替SQL_CALC_FOUND_ROWS，禁用掉SQL_CALC_FOUND_ROWS用法，用一种更加高效的方式，这里我们用EXPLAIN方式，具体代码如下，放在functions.php文件即可：

if ( ! function_exists( 'maizi_set_no_found_rows' ) ) {
/**
* 设置WP_Query的 'no_found_rows' 属性为true，禁用SQL_CALC_FOUND_ROWS
*
* @param WP_Query $wp_query WP_Query实例
* @return void
*/
function maizi_set_no_found_rows(\WP_Query $wp_query)
{
$wp_query->set('no_found_rows', true);
}
}
add_filter( 'pre_get_posts', 'maizi_set_no_found_rows', 10, 1 );

if ( ! function_exists( 'maizi_set_found_posts' ) ) {
/**
* 使用 EXPLAIN 方式重构
*/
function maizi_set_found_posts($clauses, \WP_Query $wp_query)
{
// Don't proceed if it's a singular page.
if ($wp_query->is_singular()) {
return $clauses;
}

global $wpdb;

$where = isset($clauses['where']) ? $clauses['where'] : '';
$join = isset($clauses['join']) ? $clauses['join'] : '';
$distinct = isset($clauses['distinct']) ? $clauses['distinct'] : '';

$wp_query->found_posts = (int)$wpdb->get_row("EXPLAIN SELECT $distinct * FROM {$wpdb->posts} $join WHERE 1=1 $where")->rows;

$posts_per_page = (!empty($wp_query->query_vars['posts_per_page']) ? absint($wp_query->query_vars['posts_per_page']) : absint(get_option('posts_per_page')));

$wp_query->max_num_pages = ceil($wp_query->found_posts / $posts_per_page);

return $clauses;
}
}
add_filter( 'posts_clauses', 'maizi_set_found_posts', 10, 2 );

如果用 explain 命令速度会快很多，因为 explain 用并不真正执行查询，而是查询优化器【估算】的行数。在一个1500万条记录的表中测试，用select count(*)耗时15s，而用explain耗时0.08秒，两者相差差不多有200倍之多（第一次执行会稍慢，3秒左右）。

二、服务器配置优化

MySQL开启慢查询方法一

1、在 my.ini 的 [mysqld] 添加如下语句：

log-slow-queries = E:webmysqllogmysqlslowquery.log
long_query_time = 22、修改 My.ini，将 tmp_table_size 的值赋值到 200M

3、修改 My.ini，将 key_buffer_size 的值赋值到 128M
4、修改 My.ini，将 query_cache_size 的值赋值到 32M
5、重启 MySQL

MySQL开启慢查询方法二

#取消文件系统的外部锁
skip-locking

#不进行域名反解析,注意由此带来的权限/授权问题
skip-name-resolve

#索引缓存,根据内存大小而定,如果是独立的db服务器,可以设置高达80%的内存总量
key_buffer = 512M

#连接排队列表总数
back_log = 200
max_allowed_packet = 2M

#打开表缓存总数,可以避免频繁的打开数据表产生的开销
table_cache = 512

#每个线程排序所需的缓冲
sort_buffer_size = 4M

#每个线程读取索引所需的缓冲
read_buffer_size = 4M
#MyISAM表发生变化时重新排序所需的缓冲
myisam_sort_buffer_size = 64M

#缓存可重用的线程数
thread_cache = 128

#查询结果缓存
query_cache_size = 128M

#设置超时时间,能避免长连接

set-variable = wait_timeout=60

大多数使用 WordPress 搭建的网站，其后台都是 MySQL 数据库，提升动态网站速度，减少数据库查询次数是几大重点之一，做好数据库优化对于提升网站速度是非常有必要的。

三、数据库结构优化

数据分区是一种物理数据库的设计技术，它的目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介子中，实际上还是一张表。

1.判断当前MySQL是否支持分区
5.6以下的版本可以使用如下命令：
show variables like '%partition%';
一个表最多只能有1024个分区。
5.6及以上用
show plugins;
当看到有partition并且status是active时表示支持。

在Wordpress数据库分区之前，先用SQL命令清理下Wordpress数据，数据清理完毕，那么可以开始建立分区表了。

2.查询表的主键，分区
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='表名'

ALTER TABLE 表名 PARTITION BY RANGE(主键) (
PARTITION p0 VALUES LESS THAN (100000),
PARTITION p1 VALUES LESS THAN (200000),
PARTITION p2 VALUES LESS THAN (300000),
PARTITION p3 VALUES LESS THAN (400000),
PARTITION p4 VALUES LESS THAN (500000),
PARTITION p5 VALUES LESS THAN (600000),
PARTITION p6 VALUES LESS THAN MAXVALUE
);

3.继续在原基础上增加分区
ALTER TABLE 表名 REORGANIZE PARTITION p6 INTO (
PARTITION p6 VALUES LESS THAN (700000),
PARTITION p7 VALUES LESS THAN MAXVALUE
);

ALTER TABLE 表名 DROP PARTITION p0;

4.改主键
ALTER TABLE 表名 DROP PRIMARY KEY, ADD PRIMARY KEY (主键);

5.查看分区情况
EXPLAIN PARTITIONS SELECT * FROM `表名`;

6.合并之后重新再分区
ALTER TABLE 表名
REORGANIZE PARTITION p0,p1,p2,p3,p4,p5,p6 into
(PARTITION p0 VALUES LESS THAN (100000),
PARTITION p1 VALUES LESS THAN (200000)
);

wp_posts，wp_postmeta，wp_term_relationships，wp_comments这些表，都是数据量比较大的表，可以选择分区，分区之后速度是明显改善的。

需要注意的是mysql5.6.4前只有Myisam支持，之后Myisam和innodb都支持。

通过以上三个方面的优化，大数据的Wordpress速度会有一个质的提升！