Andy's Blog » » WordPress执行效率问题

WordPress执行效率问题

实在受不了Wordpress的速度,今天非要找出个所以然来.
开始!
wp-config.php中加入

><?php define('SAVEQUERIES', true); ?>

在模板文件footer.php中加入

><?php var_dump($wpdb->queries); ?>

修改模板,并卸了所有的PLUGIN,将wp恢复到最简洁状态,然后运行测试.

一共6万文章,首页执行时间在16-30秒左右,分类的列表页执行效率居然高了快7倍,在4秒左右.

来看看执行首页都有哪些查询

19 queries. 16.817 seconds.

array(19) {
[0]=>
array(2) {
[0]=>
string(65) "SELECT option_value FROM wp_options WHERE option_name = 'siteurl'"
[1]=>
float(0.000766038894653)
}
[1]=>
array(2) {
[0]=>
string(71) "SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'"
[1]=>
float(0.00127911567688)
}
[2]=>
array(2) {
[0]=>
string(79) "SELECT option_value FROM wp_options WHERE option_name = 'rewrite_rules' LIMIT 1"
[1]=>
float(0.000645875930786)
}
[3]=>
array(2) {
[0]=>
string(79) "SELECT option_value FROM wp_options WHERE option_name = 'rewrite_rules' LIMIT 1"
[1]=>
float(0.000285863876343)
}
[4]=>
array(2) {
[0]=>
string(207) " SELECT DISTINCT * FROM wp_posts WHERE 1=1 AND post_date_gmt <= '2006-08-13 06:41:59' AND (post_status = "publish") AND post_status != "attachment" GROUP BY wp_posts.ID ORDER BY post_date DESC LIMIT 0, 50" [1]=>
float(12.4344360828)
}

[5]=>
array(2) {
[0]=>
string(356) "SELECT post_id, category_id FROM wp_post2cat WHERE post_id IN (29271, 550, 33318, 23145, 55913, 15376, 48144, 9332, 42100, 2081, 34849, 21539, 54307, 31042, 20684, 53452, 3023, 35791, 18805, 51573, 2775, 35543, 14502, 47270, 17286, 50054, 20112, 52880, 22078, 54846, 27809, 19048, 51816, 30658, 6829, 39597, 11084, 43852, 29775, 29859, 14854, 47622, 23742, 56510, 11496, 44264, 10043, 42811, 30274, 18221)"
[1]=>
float(0.0330619812012)
}
[6]=>
array(2) {
[0]=>
string(27) "SELECT * FROM wp_categories"
[1]=>
float(0.000616073608398)
}
[7]=>
array(2) {
[0]=>
string(391) "SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN(29271, 550, 33318, 23145, 55913, 15376, 48144, 9332, 42100, 2081, 34849, 21539, 54307, 31042, 20684, 53452, 3023, 35791, 18805, 51573, 2775, 35543, 14502, 47270, 17286, 50054, 20112, 52880, 22078, 54846, 27809, 19048, 51816, 30658, 6829, 39597, 11084, 43852, 29775, 29859, 14854, 47622, 23742, 56510, 11496, 44264, 10043, 42811, 30274, 18221) ORDER BY post_id, meta_key"
[1]=>
float(0.000411033630371)
}
[8]=>
array(2) {
[0]=>
string(278) "SELECT DISTINCT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts WHERE post_date < '2006-08-13 14:41:21' AND post_date != '0000-00-00 00:00:00' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC" [1]=>
float(1.06055593491)
}

[9]=>
array(2) {
[0]=>
string(86) "SELECT option_value FROM wp_options WHERE option_name = 'kubrick_header_image' LIMIT 1"
[1]=>
float(0.000422954559326)
}
[10]=>
array(2) {
[0]=>
string(86) "SELECT option_value FROM wp_options WHERE option_name = 'kubrick_header_color' LIMIT 1"
[1]=>
float(0.000302076339722)
}
[11]=>
array(2) {
[0]=>
string(88) "SELECT option_value FROM wp_options WHERE option_name = 'kubrick_header_display' LIMIT 1"
[1]=>
float(0.000371932983398)
}
[12]=>
array(2) {
[0]=>
string(45) "SELECT * FROM wp_users WHERE ID = '1' LIMIT 1"
[1]=>
float(0.000383138656616)
}
[13]=>
array(2) {
[0]=>
string(64) "SELECT meta_key, meta_value FROM wp_usermeta WHERE user_id = '1'"
[1]=>
float(0.000432968139648)
}
[14]=>
array(2) {
[0]=>
string(155) "SELECT COUNT(DISTINCT ID) FROM wp_posts WHERE 1=1 AND post_date_gmt <= '2006-08-13 06:41:59' AND (post_status = "publish") AND post_status != "attachment"" [1]=>
float(1.02000594139)
}

[15]=>
array(2) {
[0]=>
string(169) "
SELECT cat_ID, cat_name, category_nicename, category_description, category_parent, category_count
FROM wp_categories
WHERE cat_ID > 0
ORDER BY cat_name asc"
[1]=>
float(0.00116395950317)
}
[16]=>
array(2) {
[0]=>
string(76) "SELECT * FROM wp_posts WHERE post_status = 'static' ORDER BY post_title ASC"
[1]=>
float(0.935003995895)
}
[17]=>
array(2) {
[0]=>
string(278) "SELECT DISTINCT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts WHERE post_date < '2006-08-13 14:41:24' AND post_date != '0000-00-00 00:00:00' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC" [1]=>
float(0.997811079025)
}
[18]=>
array(2) {
[0]=>
string(295) "
SELECT DISTINCT link_category, cat_name, show_images,
show_description, show_rating, show_updated, sort_order,
sort_desc, list_limit
FROM `wp_links`
LEFT JOIN `wp_linkcategories` ON (link_category = cat_id)
WHERE link_visible = 'Y'
AND list_limit <> 0
ORDER BY cat_name "
[1]=>
float(0.00177693367004)
}
}

很明显,第一个加粗部份的sql语句,居然执行了12秒!我汗!!!!

再来看看分类列表页都执行了些啥样的sql

19 queries. 3.430 seconds.

array(19) {
[0]=>
array(2) {
[0]=>
string(65) "SELECT option_value FROM wp_options WHERE option_name = 'siteurl'"
[1]=>
float(0.000646114349365)
}
[1]=>
array(2) {
[0]=>
string(71) "SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'"
[1]=>
float(0.00108289718628)
}
[2]=>
array(2) {
[0]=>
string(79) "SELECT option_value FROM wp_options WHERE option_name = 'rewrite_rules' LIMIT 1"
[1]=>
float(0.000606060028076)
}
[3]=>
array(2) {
[0]=>
string(79) "SELECT option_value FROM wp_options WHERE option_name = 'rewrite_rules' LIMIT 1"
[1]=>
float(0.000309944152832)
}
[4]=>
array(2) {
[0]=>
string(27) "SELECT * FROM wp_categories"
[1]=>
float(0.000545978546143)
}
[5]=>
array(2) {
[0]=>
string(32) "SELECT cat_ID FROM wp_categories"
[1]=>
float(0.000543117523193)
}
[6]=>
array(2) {
[0]=>
string(292) " SELECT DISTINCT * FROM wp_posts LEFT JOIN wp_post2cat ON (wp_posts.ID = wp_post2cat.post_id) WHERE 1=1 AND (category_id = 35) AND post_date_gmt <= '2006-08-13 06:43:59' AND (post_status = "publish") AND post_status != "attachment" GROUP BY wp_posts.ID ORDER BY post_date DESC LIMIT 0, 50" [1]=>
float(0.0346350669861)
}
[7]=>
array(2) {
[0]=>
string(363) "SELECT post_id, category_id FROM wp_post2cat WHERE post_id IN (41073, 41074, 41058, 41053, 41066, 40988, 41070, 40992, 41034, 41056, 41020, 40996, 41051, 40989, 41023, 41017, 40991, 41039, 41071, 40993, 41077, 41031, 41030, 41062, 41002, 41025, 41028, 41021, 41047, 40990, 41008, 41061, 41042, 41033, 41052, 41065, 41078, 41076, 41004, 41049, 41043, 41014, 41027, 40998, 41036, 41059, 41050, 41072, 41011, 41069)"
[1]=>
float(0.00174999237061)
}
[8]=>
array(2) {
[0]=>
string(398) "SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN(41073, 41074, 41058, 41053, 41066, 40988, 41070, 40992, 41034, 41056, 41020, 40996, 41051, 40989, 41023, 41017, 40991, 41039, 41071, 40993, 41077, 41031, 41030, 41062, 41002, 41025, 41028, 41021, 41047, 40990, 41008, 41061, 41042, 41033, 41052, 41065, 41078, 41076, 41004, 41049, 41043, 41014, 41027, 40998, 41036, 41059, 41050, 41072, 41011, 41069) ORDER BY post_id, meta_key"
[1]=>
float(0.000437021255493)
}
[9]=>
array(2) {
[0]=>
string(278) "SELECT DISTINCT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts WHERE post_date < '2006-08-13 14:43:36' AND post_date != '0000-00-00 00:00:00' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC" [1]=>
float(1.01349496841)
}

[10]=>
array(2) {
[0]=>
string(86) "SELECT option_value FROM wp_options WHERE option_name = 'kubrick_header_image' LIMIT 1"
[1]=>
float(0.000421047210693)
}
[11]=>
array(2) {
[0]=>
string(86) "SELECT option_value FROM wp_options WHERE option_name = 'kubrick_header_color' LIMIT 1"
[1]=>
float(0.00031304359436)
}
[12]=>
array(2) {
[0]=>
string(88) "SELECT option_value FROM wp_options WHERE option_name = 'kubrick_header_display' LIMIT 1"
[1]=>
float(0.000300168991089)
}
[13]=>
array(2) {
[0]=>
string(45) "SELECT * FROM wp_users WHERE ID = '1' LIMIT 1"
[1]=>
float(0.000428915023804)
}
[14]=>
array(2) {
[0]=>
string(64) "SELECT meta_key, meta_value FROM wp_usermeta WHERE user_id = '1'"
[1]=>
float(0.000426054000854)
}
[15]=>
array(2) {
[0]=>
string(240) "SELECT COUNT(DISTINCT ID) FROM wp_posts LEFT JOIN wp_post2cat ON (wp_posts.ID = wp_post2cat.post_id) WHERE 1=1 AND (category_id = 35) AND post_date_gmt <= '2006-08-13 06:43:59' AND (post_status = "publish") AND post_status != "attachment"" [1]=>
float(0.039783000946)
}
[16]=>
array(2) {
[0]=>
string(169) "
SELECT cat_ID, cat_name, category_nicename, category_description, category_parent, category_count
FROM wp_categories
WHERE cat_ID > 0
ORDER BY cat_name asc"
[1]=>
float(0.001296043396)
}
[17]=>
array(2) {
[0]=>
string(76) "SELECT * FROM wp_posts WHERE post_status = 'static' ORDER BY post_title ASC"
[1]=>
float(0.955458164215)
}
[18]=>
array(2) {
[0]=>
string(278) "SELECT DISTINCT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts WHERE post_date < '2006-08-13 14:43:38' AND post_date != '0000-00-00 00:00:00' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC" [1]=>
float(1.03362488747)
}

}

显然,罪魁祸首就是
SELECT DISTINCT * FROM wp_posts WHERE 1=1

Incoming search terms:

Tags: WordPress专题

本文地址: http://www.21andy.com/new/20060813/374.html

17 评论 to “PHP开源CMS之MODx”

  1. Alang 于 2006-08-13 19:36:55 发表:

    怎么解决呢?

  2. taozey 于 2006-08-15 02:22:38 发表:

    怎么解决的呢?

  3. Andy 于 2006-08-15 03:03:06 发表:

    我只是找出问题,暂没有解决.
    还望高人指点.

  4. Robot5 于 2006-08-18 16:25:44 发表:

    不知道你的测试环境是什么?MySQL及PHP的版本是多少?

  5. Andy 于 2006-08-18 17:14:14 发表:

    本机测试
    硬件 p4 930d 2GDDR
    环境
    Apache Web Server Version 2.2.2
    PHP Script Language Version 5.1.4
    MySQL Database Version 5.0.22

    使用了WP的classic模板,执行时间也是超慢.偶尔快也要4秒左右

  6. ZhuZhe 于 2006-11-24 11:44:46 发表:

    你试试看一页不要放50页文章 -,-

  7. BloggingPro China » WordPress执行效率问题 于 2006-11-29 00:27:26 发表:

    [...]   WordPress执行效率问题;查看WordPress执行过程中提交的SQL语句,这两篇文章推荐各位阅读一下,能更好的完善自己的WP Blog系统。 [...]

  8. 撞……撞研究! → 泊客Myheimu 于 2006-12-10 09:40:35 发表:

    [...] 哥哥在“查看WordPress页面执行过程中提交的SQL查询语句”中提到文章《WordPress执行效率问题》,恰巧昨天我也看到了这篇文章和相关的文章。不过我承认我没大看懂,不过之前yskin一直说我不需要用WordPress的Cache功能,但自己一直用,但是现在看来,我在DH上的heymu.com虽然运行速度快但是美国服务器链接速度慢,yskin.net国内传输超快,似乎我更需要这个功能。我抛开yskin的文章,准备自己上网查Cache的方法,但是在google里查“WordPress cache”却发现找到的第一个有用信息居然是来自可恶的yskin.net!又撞了! [...]

  9. 十方界 » 博客文章 » 查看WordPress页面执行过程中提交的SQL查询语句 于 2006-12-17 17:34:11 发表:

    [...] 今天照着《WordPress执行效率问题》一文检查了一下我的Blog首页执行的SQL语句。具体方法那篇文章里讲了,就是在wp-config.php文件里添加define('SAVEQUERIES', true);,再在footer.php文件的尾部加上一句 [...]

  10. Victor’s Blog » WordPress执行效率问题 于 2006-12-24 01:14:32 发表:

    [...] 今天照着《WordPress执行效率问题》一文检查了一下我的Blog首页执行的SQL语句。刷新Blog首页,就可以在页面源代码的尾部找到整个页面执行过程中所提交的所有SQL查询语句了。数组里每一项都包含一个string和一个float,string存储查询语句,float存储查询时间。先看看查询时间,看看哪个占用的时间很长;再看看查询语句,看看哪个可以省略,哪个可以cache起来的。 [...]

  11. youcan 于 2007-01-04 01:02:20 发表:

    "在wp-config.php中加入

    在模板文件footer.php中加入
    queries); ?>"

    照着步骤做了,怎么我在网页的源代码里没有显示那些sql查询语句和那些array,string,float的?我的是在本地作测试的。
    :(

    请Andy帮忙!

  12. WordPress执行效率问题 于 2007-04-26 10:28:45 发表:

    [...] 今天照着《WordPress执行效率问题》一文检查了一下我的Blog首页执行的SQL语句。刷新Blog首页,就可以在页面源代码的尾部找到整个页面执行过程中所提交的所有SQL查询语句了。数组里每一项都包含一个string和一个float,string存储查询语句,float存储查询时间。先看看查询时间,看看哪个占用的时间很长;再看看查询语句,看看哪个可以省略,哪个可以cache起来的。 [...]

  13. 加速blog:监测和优化WordPress数据库 » Allen blog--路爽のblog 于 2008-06-02 10:48:49 发表:

    [...] WordPress执行效率问题作者作了一个测试,大家可以看一下具体有什么问题,与自己的做一个对照。我提一下我遇到过的比较慢的查询语句。 [...]

  14. 天津广告制作 于 2009-08-10 08:33:22 发表:

    全看不懂!

  15. 加速blog:监测和优化WordPress数据库 | 网赚博客 于 2010-05-14 14:34:51 发表:

    [...] WordPress执行效率问题作者作了一个测试,大家可以看一下具体有什么问题,与自己的做一个对照。我提一下我遇到过的比较慢的查询语句。 [...]

  16. 位置圈 于 2011-06-26 11:10:15 发表:

    不知道,这个与 get_num_queries();有没有区别,结果是出了了但是还需要高人给出解决方案

  17. 袁源 于 2011-07-25 08:19:15 发表:

    我表示学习了,有空试验下