[MySQL优化案例]系列 -- 取出超大结果集的技巧

看看正序取得结果的耗时:

mysql>SELECT a.HandicapID, FROM_UNIXTIME( a.AddTime, '%y-%c-%e %H:%i' ) AS ShowAddTime, a.MatchID, a.MakerID, a.HandicapNumber ...
FROM MatchHandicap AS a
LEFT JOIN MatchInfo AS b ON ( a.MatchID = e.MatchID )
LEFT JOIN Team AS c ON ( e.HomeID = c.TeamID )
LEFT JOIN Team AS d ON ( e.AwayID = d.TeamID )
LEFT JOIN BookMaker AS e ON ( a.MakerID = e.MakerID ) ORDER BY a.HandicapID LIMIT 11910298, 20;
........
........
20 rows in set (1330 sec)

很恐怖吧,暂且不论这个SQL语句其他可以再优化的地方,把它改造成用倒序取得结果的方式试试看:

mysql>SELECT a.HandicapID, FROM_UNIXTIME( a.AddTime, '%y-%c-%e %H:%i' ) AS ShowAddTime, a.MatchID, a.MakerID, a.HandicapNumber ...
FROM MatchHandicap AS a
LEFT JOIN MatchInfo AS b ON ( a.MatchID = e.MatchID )
LEFT JOIN Team AS c ON ( e.HomeID = c.TeamID )
LEFT JOIN Team AS d ON ( e.AwayID = d.TeamID )
LEFT JOIN BookMaker AS e ON ( a.MakerID = e.MakerID ) ORDER BY a.HandicapID DESC LIMIT 20;
........
........
20 rows in set (0.05 sec)

两次查询的耗时简直是天差地别:1330s VS 0.05s,。有些程序员很懒,或者没有考虑过这个问题,经常会在取结果的时候一直按照同一种排序方式,而没有考虑到当该排序方式碰到超大结果集时会变得非常慢的问题。因此,我们可以在程序中约定,当按原来的排序方式取得结果过程中,如果LIMIT START,OFFSET中的START的值超过总记录数的一定比例(例如一半),就将排序方式倒过来,虽然这么做可能会导致一些其他小问题,但我认为这是非常值得的。

评论

把第二个 LIMIT 20 换成 LIMIT 11910298, 20 这样呢?

汗,当然是同样的原理了,也会很慢

MySQL方案、培训、支持
MySQL 用户组

如果一样慢,那不是换成倒叙的方式也没有用了吗?限制重20行开始,那前面说的倒叙不是没用了,用不用都一样了

limit 0,20 和limit 11111111,20的区别您木有整明白。

看见这个帖子的回复,我笑了

这点其实你理解错了,Mysql查询,第一次当然是慢一点,第二次查询的时候就快了。
如果要这样测试的话,应该测试完一条语句,就得重启mysql,再执行下一条语句 。

呵呵,老兄,那是缓存。
再说类似这样的查询并不是很频繁的,因此只是利用mysql的缓存的话基本上没用。

MySQL方案、培训、支持
MySQL 用户组