构建高性能的MySQL系统

[e-mail]5.6.1.2.3.4.SQL5.6.l l l l l l l l SDLCl l l l l l l l 1.2.3.4.SQL5.6.l l l Scale-outl l XAl DBl l Cache&Searchl l -ACIDl —BASE&CAPl l text&blob1l l l NULL2l MyISAMcharInnoDBvarcharl decimalfloat&doublel timestampdatetimel intfrom_unixtime()unix_timestamp()3l IPintinet_aton()inet_ntoa()l enum,setl procedureanalyse()-MyISAMMyISAM:l R/W100:1&updatel l l -InnoDBInnoDB:l OLTPR/Wl 1000l l Shardingl MPSS-MPMSl HASHRANGEINDEXMAP1.2.3.4.SQL5.6.l l l l l l l SQLl SQLl slowlogl 5l l Selectivityl l keyl clusteredindexl primarykeyl primarykeyl auto_incrementl l sortcount(*)l explainl showprofilingl slowlogl SQLhints1.2.3.4.SQL5.6.SQLl l CPUl sp,triggers,join,sortl SQLl SQLl l SQL1l SQLl select*l SQL2l limitNl l orderbyrandom()SQL3l insertvaluel replace&insertonduplicatekeyupdatel insertignore,lowprioritydelayedSQL4l connectiondriverl preparedstatements1.2.3.4.SQL5.6.l CPUl SSD:SBP,Flashcache&FushionIOl Linuxkernel2.6.xl IOdeadlinel MySQL5.1+InnoDBPlugin1l query_cache_sizel table_cachel thread_cache_size2l read_buffer_sizel sort_buffer_sizel join_buffer_sizel read_rnd_buffer_sizel bulk_insert_buffer_size3l max_connectionsl max_user_connectionsMyISAMl key_buffer_size(key_reads/key_read_requests)0.03(often0.01isdesirable)l myisam_sort_buffer_sizel myisam_recover_optionsl myisam_repair_threadsInnoDBl innodb_buffer_pool_sizel innodb_flush_log_at_trx_commitl innodb_log_buffer_sizel innodb_log_file_sizel innodb_flush_methodl showglobalstatusl showinnodbstatusl mpstat,iostat,vmstat,top,innotopl 1.2.3.4.SQL5.6.l M/SSlavel MasterHAHeartbeat+DRBDM(active)/M(passive)l SlaveHADNS,LVScacheMySQL&MemecachedUDF1l Blackhole:Masterl 2l MasterMastercachel SlaveIDCl l l l binlogandonefortheroad...Last,butnotleast!l l Thankyouforcoming!Q&A