基于MySQL5.0中limit的高性能,我对数据分页也重新有了新的认识.
1.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90001
) As tmp
) limit 100;
2.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90000,1
) As tmp
) limit 100;
同样是取90000条后100条记录,第1句快还是第2句快?
第1句是先取了前90001条记录,取其中最大一个ID值作为起始标识,然后利用它可以快速定位下100条记录
第2句择是仅仅取90000条记录后1条,然后取ID值作起始标识定位下100条记录
第1句执行结果.100 rows in set (0.23) sec
第2句执行结果.100 rows in set (0.19) sec
很明显第2句胜出.看来limit好像并不完全像我之前想象的那样做全表扫描返回limit offset+length条记录,这样看来limit比起MS-SQL的Top性能还是要提高不少的.
其实第2句完全可以简化成
Select * From cyclopedia Where ID>=(
Select ID From cyclopedia limit 90000,1
)limit 100;
直接利用第90000条记录的ID,不用经过Max运算,这样做理论上效率因该高一些,但在实际使用中几乎看不到效果,因为本身定位ID返回的就是1条记录,Max几乎不用运作就能得到结果,但这样写更清淅明朗,省去了画蛇那一足.
可是,既然MySQL有limit可以直接控制取出记录的位置,为什么不干脆用Select * From cyclopedia limit 90000,1呢?岂不更简洁?
这样想就错了,试了就知道,结果是:1 row in set (8.88) sec,怎么样,够吓人的吧,让我想起了昨天在4.1中比这还有过之的"高分".Select * 最好不要随便用,要本着用什么,选什么的原则, Select的字段越多,字段数据量越大,速度就越慢. 上面2种分页方式哪种都比单写这1句强多了,虽然看起来好像查询的次数更多一些,但实际上是以较小的代价换取了高效的性能,是非常值得的.
第1种方案同样可用于MS-SQL,而且可能是最好的.因为靠主键ID来定位起始段总是最快的.
Select Top 100 * From cyclopedia Where ID>=(
Select Top 90001 Max(ID) From (
Select ID From cyclopedia Order By ID
) As tmp
)
但不管是实现方式是存贮过程还是直接代码中,瓶颈始终在于MS-SQL的TOP总是要返回前N个记录,这种情况在数据量不大时感受不深,但如果成百上千万,效率肯定会低下的.相比之下MySQL的limit就有优势的多,执行:
Select ID From cyclopedia limit 90000
Select ID From cyclopedia limit 90000,1
的结果分别是:
90000 rows in set (0.36) sec
1 row in set (0.06) sec
而MS-SQL只能用Select Top 90000 ID From cyclopedia 执行时间是390ms,执行同样的操作时间也不及MySQL的360ms.
虽然第2段的分页代码现在看起来很简单,不过可是花了我很多的时间才悟出来的呀.这两天深受这些个SQL困扰,先是MS-SQL记录集一大就不能更新,然后又是MySQL4.1里limit速度慢的吓人.跑到CSDN问了N张贴子,浪费了200多分,都没几个人理我,想起来就郁闷!看来什么事情都还得靠自己才行喔!
不过想想罪魁还是4.1版的那个limit,怎么可能慢的那么离谱!要是它不出怪象很多事情自然而然的就不是问题了.哦,还有个给我脸色看的MS-SQL,气走我是你损失,我现在有点爱上MySQL的速度了.
忘了说了,那个BT的MS-SQL昨天我用方案1分页后对记录进行遍历更新,可以正常工作了,谁知在一次更新操作时不小心碰到的电源插头,重开机了它死都不给我好好干活了,又出现了先前记录不能更新的情况,一选到30000多记录后就卡在那里,即不报错,也不退出. 我实在想不出如何解决这种问题. 我猜可能是SQLServer内部处理出了一些Bug导致记录被锁死. 也有可能是它的JDBC驱动有问题,总之我快被它气炸了!但不管怎么说MS-SQL起码还是目前我用过的最好用的数据库系统,优秀的地方也很多,所有我不能凭这一点瑕疵就跟它绝裂,那样对我也没好处喔. |
