资源描述：
《Efficient Pagination Using MySQL》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。
1、EfficientPaginationUsingMySQLSuratSinghBhati(surat@yahoo-inc.com)RickJames(rjames@yahoo-inc.com)YahooIncPerconaPerformanceConference2009Outline1.Overview–CommonpaginationUIpattern–SampletableandtypicalsolutionusingOFFSET–TechniquestoavoidlargeOFFSET–Performancecomparison–Conce
2、rns-2-CommonPatterns-3-BasicsFirststeptowardhavingefficientpaginationoverlargedataset–Useindextofilterrows(resolveWHERE)–Usesameindextoreturnrowsinsortedorder(resolveORDER)Stepzero–http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html–http://dev.mysql.com/doc/refman/5.1/en
3、/order-by-optimization.html–http://dev.mysql.com/doc/refman/5.1/en/limit-optimization.html-4-UsingIndexKEYa_b_c(a,b,c)ORDERmaygetresolvedusingIndex–ORDERBYa–ORDERBYa,b–ORDERBYa,b,c–ORDERBYaDESC,bDESC,cDESCWHEREandORDERbothresolvedusingindex:–WHEREa=constORDERBYb,c–WHEREa=const
4、ANDb=constORDERBYc–WHEREa=constORDERBYb,c–WHEREa=constANDb>constORDERBYb,cORDERwillnotgetresolveduisngindex(filesort)–ORDERBYaASC,bDESC,cDESC/*mixedsortdirection*/–WHEREg=constORDERBYb,c/*aprefixismissing*/–WHEREa=constORDERBYc/*bismissing*/–WHEREa=constORDERBYa,d/*disnotparto
5、findex*/-5-SampleSchemaCREATETABLE`message`(`id`int(11)NOTNULLAUTO_INCREMENT,`title`varchar(255)COLLATEutf8_unicode_ciNOTNULL,`user_id`int(11)NOTNULL,`content`textCOLLATEutf8_unicode_ciNOTNULL,`create_time`int(11)NOTNULL,`thumbs_up`int(11)NOTNULLDEFAULT'0',/*VoteCount*/PRIMARY
6、KEY(`id`),KEY`thumbs_up_key`(`thumbs_up`,`id`))ENGINE=InnoDBmysql>showtablestatuslike'message'GEngine:InnoDBVersion:10Row_format:CompactRows:50000040/*50Million*/Avg_row_length:565Data_length:28273803264/*26GB*/Index_length:789577728/*753MB*/Data_free:6291456Create_time:2009-
7、04-2013:30:45Twousecase:•Paginatebytime,recentmessageonepageone•Paginatebythumps_up,largestvalueonpageone-6-TypicalQuery1.GetthetotalrecordsSELECTcount(*)FROMmessage2.GetcurrentpageSELECT*FROMmessageORDERBYidDESCLIMIT0,20•http://domain.com/message?page=1•ORDERBYidDESCLIMIT0,20
8、•http://domain.com/message?page=2•ORDERBYidDESCLIMIT20,20•htt
