{"id":3457,"date":"2009-12-29T01:09:46","date_gmt":"2009-12-28T17:09:46","guid":{"rendered":"http:\/\/ihower.tw\/blog\/?p=3457"},"modified":"2018-05-25T23:10:29","modified_gmt":"2018-05-25T15:10:29","slug":"effective-pagination","status":"publish","type":"post","link":"https:\/\/ihower.tw\/blog\/3457-effective-pagination","title":{"rendered":"\u5982\u4f55\u6709\u6548\u7387\u5730\u5206\u9801?"},"content":{"rendered":"<p><a href=\"https:\/\/www.percona.com\/files\/presentations\/ppc2009\/PPC2009_mysql_pagination.pdf\">Efficient Pagination Using MySQL<\/a> \u662f\u4e00\u4efd\u91dd\u5c0d\u5206\u9801\u9019\u4ef6\u4e8b\u60c5\u5982\u4f55\u66f4\u5feb\u7684 PDF\u3002<\/p>\n<p>\u50b3\u7d71\u7684\u5206\u9801\u65b9\u5f0f\uff0c\u6b63\u5982 <a href=\"http:\/\/wiki.github.com\/mislav\/will_paginate\">will_paginate<\/a> plugin \u6240\u505a\u7684\uff1a\u986f\u793a\u6240\u6709\u9801\u6578\u8ddf\u9023\u7d50\u3002\u6709\u500b\u4e0d scale \u7684\u554f\u984c\u662f\uff0c\u5b83\u7528\u4e86 OFFSET \u8ddf COUNT\uff0c\u7576\u8cc7\u6599\u91cf\u8d8a\u4f86\u8d8a\u591a\uff0c\u5206\u9801\u7684\u901f\u5ea6\u5c31\u6703\u8d8a\u4f86\u8d8a\u6162 :(<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/ihower.tw\/blog\/wp-content\/uploads\/2009\/12\/pagination.png\" alt=\"\" title=\"pagination\" \/><\/p>\n<pre>\n<core>\n SELECT count(*) FROM messages\n SELECT * FROM messages ORDER BY id DESC LIMIT 0, 20\n<\/code>\n<\/pre>\n<p>\u9019\u88e1\u63d0\u51fa\u7684\u89e3\u6c7a\u65b9\u6848\u5982\u540c Twitter \u6240\u63a1\u7528\u7684\uff0c\u53ea\u8b93\u4f7f\u7528\u8005\u9ede\u4e0b\u4e00\u9801\uff1a<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" data-attachment-id=\"3461\" data-permalink=\"https:\/\/ihower.tw\/blog\/3457-effective-pagination\/pagination-ajax\" data-orig-file=\"https:\/\/ihower.tw\/blog\/wp-content\/uploads\/2009\/12\/pagination-ajax.png\" data-orig-size=\"546,49\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;}\" data-image-title=\"pagination-ajax\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/ihower.tw\/blog\/wp-content\/uploads\/2009\/12\/pagination-ajax-300x26.png\" data-large-file=\"https:\/\/ihower.tw\/blog\/wp-content\/uploads\/2009\/12\/pagination-ajax.png\" src=\"http:\/\/ihower.tw\/blog\/wp-content\/uploads\/2009\/12\/pagination-ajax.png\" alt=\"\" title=\"pagination-ajax\" width=\"546\" height=\"49\" class=\"alignnone size-full wp-image-3461\" srcset=\"https:\/\/ihower.tw\/blog\/wp-content\/uploads\/2009\/12\/pagination-ajax.png 546w, https:\/\/ihower.tw\/blog\/wp-content\/uploads\/2009\/12\/pagination-ajax-300x26.png 300w\" sizes=\"auto, (max-width: 546px) 100vw, 546px\" \/><\/p>\n<p>\u4e0d\u8981\u986f\u793a\u9801\u6578\u3001\u4e0d\u8981\u986f\u793a\u7e3d\u6578\u91cf\uff0c\u53ef\u4ee5\u642d\u914d\u4f7f\u7528 Ajax \u63db\u4e0b\u4e00\u9801\u3002\u5982\u6b64\u5c31\u53ef\u907f\u514d\u4f7f\u7528\u5230 OFFSET \u53ea\u7528 LIMIT\uff0c\u4e5f\u6e1b\u5c11\u4e86\u4e00\u6b21 COUNT query\u3002<\/p>\n<pre>\n<core>\npage1 = SELECT * FROM messages LIMIT 10 WHERE id > 0 ASC id\npage2_min_id = page1.last.id\npage2 = SELECT * FROM messages LIMIT 10 WHERE id > page2_min_id ASC id\n<\/core>\n<\/pre>\n<p>\u7576\u7136\uff0c\u6709\u500b\u7f3a\u9ede\u5c31\u662f\u4f7f\u7528\u8005\u6c92\u8fa6\u6cd5\u77e5\u9053\u7e3d\u5171\u6709\u591a\u5c11\u8cc7\u6599\u4e86\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Efficient Pagination Using MySQL \u662f\u4e00\u4efd\u91dd\u5c0d\u5206\u9801\u9019\u4ef6\u4e8b\u60c5\u5982\u4f55\u66f4\u5feb\u7684 PDF\u3002  &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/ihower.tw\/blog\/3457-effective-pagination\" class=\"more-link\">\u95b1\u8b80\u5168\u6587<span class=\"screen-reader-text\">\u3008\u5982\u4f55\u6709\u6548\u7387\u5730\u5206\u9801?\u3009<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[38,50],"tags":[],"class_list":["post-3457","post","type-post","status-publish","format-standard","hentry","category-database","category-performance","entry"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p1q6tG-TL","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/posts\/3457","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/comments?post=3457"}],"version-history":[{"count":19,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/posts\/3457\/revisions"}],"predecessor-version":[{"id":8951,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/posts\/3457\/revisions\/8951"}],"wp:attachment":[{"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/media?parent=3457"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/categories?post=3457"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/tags?post=3457"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}