感谢bluehost的CPU限制,让我对SQL查询的效率进行无限的追求。这两天CPU老是超标,我仔细查看了日志,我的这个bloggermap也他的一份“功劳”,特别是RSS阅读那一块,一个查询就占据了6秒,再加上其他几个合租伙伴的“功劳”(已经给他们发送邮件),很容易就超过30s了,所以今天晚上回来继续优化。
首先我们来看看这段关于MySQL查询的分析
# Query_time: 4 Lock_time: 0 Rows_sent: 10 Rows_examined: 74584
use sevtiger_bloggermap;
SELECT sample_blogs.id blogid,
sample_blogs.url blogurl,
sample_blogs.feedurl feedurl,
sample_blogs.name blogname,
sample_article.id id,
sample_article.title title,
sample_article.content content,
sample_article.description description,
sample_article.link link,
sample_article.author author,
sample_article.timestamp timestamp
FROM sample_article LEFT JOIN sample_blogs ON sample_blogs.id = sample_article.blogid WHERE 1 ORDER BY sample_article.timestamp DESC LIMIT 17800,10
整个查询耗时4秒,查询涉及到的行数有74584,实际输出10行,我们可以看到,整个查询的效率并不高。虽然有LIMIT begin,per来限制查询的数量,但是行数依然没有减少,而LEFT JOIN更是增加了检索的数量,我想如果是LIMIT 17800,10,那么其实最多只需要查询17810或者两倍就可以了,LEFT JOIN将另外的一张表连接起来,导致的结果是查询影响的行数陡增至原来的4倍。
虽然LEFT JOIN查询省事,看来为了CPU时间的占用,不得不忍痛重写SQL。其实上面的那段SQL查询的目的就是从 article表中取出文章内容,取出的同时根据article中的blogid再从blogs表中取出blog的相关信息,具体效果请看这个页面。这样就能在显示BLOG RSS内容的同时显示对应RSS的blog名字和链接等信息。
我改写的思路是这样的:
- 定义一个函数A,函数的作用是根据blogid或者blog相关的信息,返回一个查询的object,可以根据object->name这样的形式获取blog信息;
- 获取RSS的时候不再连接blogs这张表,直接循环输出数据,在需要获取BLOG信息的时候时候引用函数A( 函数A同时被缓存,毕竟blogs信息改变的几率不大,所以这样在第二次查询的时候,A函数就不会再连接数据库了 ),从而提高了数据库的查询速度;
当然。上面由于在查询RSS的时候引用了函数,所以在函数没有被缓存之前其实是进行了嵌套SQL查询,这样的效率并不高,但是缓存之后的速度是相当的快的,现在已经改造超过2个小时了,再去查询sql slow queries,没有看到比较离谱的耗时查询了。
上面的是本人在bloggermap.org改版中遇到的实际问题,有些地方可能说得不准确,高手请指正

重点还是条数太多 你不妨试一下子查询,看速度如何
SELECT sb.id blogid,sb.url blogurl,sb.feedurl feedurl,sb.name blogname,sa.*
FROM sample_article sa
LEFT JOIN sample_blogs sb ON sb.id = sa.blogid
WHERE timestamp <= (
SELECT timestamp FROM sample_article ORDER BY timestamp DESC LIMIT 17800 , 1
) LIMIT 10