MYSQLçä¼åæ¯é常éè¦çãå
¶ä»æ常ç¨ä¹æéè¦ä¼åçå°±æ¯limitãmysqlçlimitç»å页带æ¥äºæ大çæ¹ä¾¿ï¼ä½æ°æ®éä¸å¤§çæ¶åï¼limitçæ§è½å°±æ¥å§ä¸éã
åæ ·æ¯å10æ¡æ°æ®
select * from yanxue8_visit limit 10000,10
å
select * from yanxue8_visit limit 0,10
å°±ä¸æ¯ä¸ä¸ªæ°é级å«çã
ç½ä¸ä¹å¾å¤å
³äºlimitçäºæ¡ä¼åååï¼é½æ¯ç¿»è¯èªmysqlæåï¼è½ç¶æ£ç¡®ä½ä¸å®ç¨ãä»å¤©åç°ä¸ç¯æç« åäºäºå
³äºlimitä¼åçï¼å¾ä¸éãåæå°åï¼
http://www.zhenhua.org/article.asp?id=200ï¼ä¸é¢éæåæï¼
æä¸ä¸æ¯ç´æ¥ä½¿ç¨limitï¼èæ¯é¦å
è·åå°offsetçidç¶åç´æ¥ä½¿ç¨limit sizeæ¥è·åæ°æ®ãæ ¹æ®ä»çæ°æ®ï¼ææ¾è¦å¥½äºç´æ¥ä½¿ç¨limitãè¿éæå
·ä½ä½¿ç¨æ°æ®å两ç§æ
åµè¿è¡æµè¯ãï¼æµè¯ç¯å¢win2033+p4åæ ¸(3GHZ) +4Gå
å mysql 5.0.19ï¼
1ãoffsetæ¯è¾å°çæ¶åã
select * from yanxue8_visit limit 10,10
å¤æ¬¡è¿è¡ï¼æ¶é´ä¿æå¨0.0004-0.0005ä¹é´
Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10,1
) limit 10
å¤æ¬¡è¿è¡ï¼æ¶é´ä¿æå¨0.0005-0.0006ä¹é´ï¼ä¸»è¦æ¯0.0006
ç»è®ºï¼å移offsetè¾å°çæ¶åï¼ç´æ¥ä½¿ç¨limitè¾ä¼ãè¿ä¸ªæ¾ç¶æ¯åæ¥è¯¢çåå ã
2ãoffset大çæ¶åã
select * from yanxue8_visit limit 10000,10
å¤æ¬¡è¿è¡ï¼æ¶é´ä¿æå¨0.0187å·¦å³
Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10000,1
) limit 10
å¤æ¬¡è¿è¡ï¼æ¶é´ä¿æå¨0.0061å·¦å³ï¼åªæåè
ç1/3ãå¯ä»¥é¢è®¡offsetè¶å¤§ï¼åè
è¶ä¼ã
éä¸åæï¼
select * from table LIMIT 5,10; #è¿å第6-15è¡æ°æ®
select * from table LIMIT 5; #è¿åå5è¡
select * from table LIMIT 0,5; #è¿åå5è¡
æ§è½ä¼åï¼
åºäº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.