大型項目MySQL性能優(yōu)化實例

1、Sunmicrosystems大型移動項目MySQL數(shù)據(jù)庫性能優(yōu)化上海愛可生信息技術有限公司MySQL高級技術顧問應用環(huán)境：注冊用戶2億，同時在線2000多萬，活躍用戶在230萬連續(xù)高壓力下單項業(yè)務操作的所有數(shù)據(jù)庫響應時間和vO.ls高訪問量壓力時的故障快速恢復少于5分鐘MB MB |每日大量用戶LOG IN與LOG OUT （伴隨龐大的信息查詢）每日大量的互聯(lián)網(wǎng)消息通信（自然人與機器人并存）MSSQL與MySQL同等壓力下的極限性能對比數(shù)據(jù)庫環(huán)境:單機MySQL應用環(huán)境（目前僅Master提供對外數(shù)據(jù)庫服務） 普通PC64位服務器，共享存儲，32G內(nèi)存，2路4核2.50HZ cpu 150G

2、B的在線應用數(shù)據(jù)量，上TB的歷史數(shù)據(jù)（統(tǒng)計經(jīng)分等）單表最大7億條記錄，最大表容量55G 平均1000個并發(fā)的Acitve連接每秒處理5000個Active的數(shù)據(jù)庫R, 2000個Active的數(shù)據(jù)庫寫W每秒1030次磁盤讀，19+M磁盤數(shù)據(jù)讀取量殘SunMySQL體系架構MySQL設計優(yōu)化MySQL系統(tǒng)優(yōu)化MySQL配置優(yōu)化MySQL語句優(yōu)化第一層MySQL體系架構MySQL體系結構P Admin & ToolsAdministratorQuery Browser1WorkbenchMigration Toolkit INFORMATION_SCH01AKX Enterprise Fe

3、atures4ReplicationOusterBackup & RecoveryInstanee ManagerSecurityPartitioningMySQL NetworkCo 口 nNative C API. JDBC, ODBC. .NET, PHP, Python, Perl, Ruby, VBaArchiveCustomMylSAMInnoDBMemoryMergeMySQL ServerConnection PoolAuthentication Thread Reuse - Connect!on Limits - Check Memory CachesFile Sys

4、temNTFS - NFSSAN - NASDMLf DDLStored ProceduresFiles & LogsError. Query, and SlowSQL InterfaceVfParser、fOptimizer許( Caches & Buffers 、Query Translation. Object PrivilegeAccess Paths, StatisticsGlobal and Engine Specific Caches & Buffers1卞Views. Tnggers. etc.Pluggable Storage EnginesMemor

5、y. Index & Storage ManagementRedo. Undo. Data. Index. Binary,MySQL存儲特性通過選擇存儲引擎來更好的適應應用的特殊性能要求對你來說最重要的是什么?密集讀操作復制OLTP （聯(lián)機事務處理）-在線備份事務處理數(shù)據(jù)倉庫性能-外鍵可伸縮性-占用空間小并發(fā)級別-行級別鎖索引類型-嵌入式存儲利用率-表級別鎖高可靠性-集群規(guī)劃設計引擎選擇MylSAM引擎適用場景： 數(shù)據(jù)庫端的并發(fā)數(shù)量不多（20%寫80%讀） 讀操作比較多，而且都能很好的用到索引 SQL語句比較簡單的應用 輕松達到TB級數(shù)據(jù)量存儲的數(shù)據(jù)倉庫規(guī)劃設計引擎選擇InnoDB引擎

6、適用場景： 數(shù)據(jù)庫端的讀寫并發(fā)數(shù)量非常多 寫操作比較多，TB級數(shù)據(jù)量應用 數(shù)據(jù)較小、索引不好利用的應用比較多（報表）有外鍵、事務等需求的應用第二層MySQL設計優(yōu)化MySQL數(shù)據(jù)庫結構一規(guī)劃設計命名規(guī)則字段類型MySQL Workbench 5.1Visual Database DesignWindows, Linux, Mac OS編碼選擇其他注意的問題按照多數(shù)開發(fā)語言的命名規(guī)則。比(myCustomer)按照多數(shù)開源思想命名規(guī)則。比女口(my_customer)按照咱們中國人的思想。比如(我的客戶) 隨便的命名。比如(my customer)整型TINYINT、INT、BIGINT浮點類型

7、時間日期類型FLOAT、 DOUBLEDECIMAL > NUMERICDATETIME> DATE、TIMESTAMPVARCHAR、CHARBLOB、TEXT、ENUM規(guī)劃設計編碼選擇單字節(jié)？西歐文字中歐、南歐、中東、等等 latinl多字節(jié)？中國、韓國、日本 utf-8只考慮漢字？中國 gbk、gb2312AC默認值索引反范式CXLA'CW加 3TBCHMUCffK glZVfnrrxijir.ooiiATrMAtrOUOtTTKMMumtHUTLaariftti4欣 TAfir h»utz«.ri <r< .-4 ir*z-»

8、;1.- -Hh>tFmrncM tuvr 気mnw ng»c6 4" t9_«»cexCOVVJUC vwr“CA*A.ttJ KK*««1 KfAiV.CHVUtTUI ai MM P WT心.C8SCW KM«rAkfijWAKCUL I <'1：了g r rcvwrxTwmr r< r/Ay 0 心Aj匚 0Q,TR»Xr HKMCrAur :dAl'卜八V u.rriti>YM mavth£KMbM>.Q MUU W*(7Xl*TKf«_

9、»/!/<! - CCUAPCM.lUiCAll8 «XL*r*> ccrvwv 5 LB MC pwn«»> ««»TcoefcMtVUIKN1FK1FK1FK1FUg» nrrrtrwn e i vm M>vr03031 D3U33W kW枳 d3(X3m for |h& W,5QL r/anAarjcbcnia dais bate如4g k) LVSQL «<£k)n b l ./twiaCh卯妳M>Hry “>4h<杓 nVMHi(

10、!KnUifwj 切 R Utr! R «n才The 咖尸m mbefrrt<y 如ngH Eb rr。聞 For srff tomrerls. sLggesInrs cr txjqs. en«l R_P_%*葉尹於亦Ailf c mSW W_K6MF：MA MAMa|sw淋merienRV.rrwE YAH,"，”Mtece.TYwr八""K.8lic.aun<iixC_OW(TA14Xxxuvwjr.r« rin/ «»ewA 的3. MMflr x)u 337aVwWETGrxaC-CCTAlCC

11、CMUntE rxi TAtt£_5C«n»A PM TM&I MU&A4MIM TV*M 7 A.丄i£4K£ UKVuoa q.cmMTAai：I MU- ymu.&<« QaA w»v.-»wr r<u me >、G KU .kHiO- OTA 辭 Tb 磁皿去* g._«rwe "vwr fcnjKfMr crcvr -»x 1EVT -XFUxrp>RTTr>w_*r«4<- FAfllKOSEGL imp

12、art t>-». swy, “Mrvwcyg"侃Ikt. uOMeO > mhuxw(»» »i»n(w rABL£ 云 毗 WH “MGTM Wl W 3 CAT* ICIGfMMM I IM； IM C4U RW“a rv» <»*cx Iw CttXPAA* ARTTSOCMMCXT uxroAoo unpiH «»v*k- CA1«4<> »mv tvwi.nrr IXECUTE AT illX/AL，人qMX BKCX $T

13、£*T EWX HATUI au_cowi£i)» OtTATTO LAir UTDVD LASI.CiXOJTlt* ev©», <OMM0krr,3ex，垃 MMV ,L£>H? UBMSlMCtMMltt IV.f.CATMfX W；r.5：»TMAUDU MM.6ta9i» kwr t cxyt 心 bx twx*e rjnwxr.trra U1£KD HW5 irtATi cam rvr_rxTTKT3 khm urtNia««» MTUI Wf vao

14、mJm «a MJKJtXTrw.aa CfKATOl TWC LAST LfH： TME IA3T MXCM X »fX6«4R Wown. 0!OMUiWJg E E” &*TA IfcHilH30 血、l£H g上35T0 msgw HCAT5_1>；CMO. W*STATU wm<規(guī)劃設計命名規(guī)則殘Sun第三層MySQL系統(tǒng)優(yōu)化MySQL數(shù)據(jù)庫系統(tǒng)選型優(yōu)化3購后 HX445Qjunfi«!X4l5flMySQC谿QK越好的機器性能越好？ 文件系統(tǒng)越好性能越好? 網(wǎng)絡越好性能越好？ 版本越新性能越好？MySQ L數(shù)據(jù)庫

15、系統(tǒng)一網(wǎng)絡環(huán)境MySQ L數(shù)據(jù)庫系統(tǒng)一網(wǎng)絡環(huán)境多核的CPU，主頻高的CPU 更大的內(nèi)存，MySQL是個很喜歡內(nèi)存的數(shù)據(jù)庫共享磁盤，RAID陣列,ISCSI, NAS,本地磁盤,SSD使用合適的文件系統(tǒng)> XFS> ZFS> NTFS> EXT3盡量將數(shù)據(jù)庫整體系統(tǒng)部署在局域網(wǎng)內(nèi)reci使用專有的網(wǎng)絡協(xié)議> SCI 光纜保證網(wǎng)絡的安全冗余雙網(wǎng)線，提供安全冗余L V Transaction Performanee Comparison MySQL?SCIGigabit Ethernet 多端口綁定監(jiān)聽MySQL數(shù)據(jù)庫系統(tǒng)一軟件環(huán)境開啟MySQL復制，實現(xiàn)

16、讀、寫分離，負載均衡獲得推薦的最新GA版本，禾憂BUG修復提升性能MMySQL數(shù)據(jù)庫配置全局參數(shù)設置MySQL數(shù)據(jù)庫配置全局參數(shù)設置規(guī)劃設計命名規(guī)則殘Sun第四層MySQL配置優(yōu)化key buffer sizeMylSAM索引緩沖，根據(jù)(key reads / Key_read_requests )判斷innodbbuffer_pool_sizeInnoDB數(shù)據(jù)、索引、日志緩沖最重耍的引擎參數(shù)，根據(jù)（hitriatos和FILE I/O）判斷wait_time_out線程連接的超時時間，盡量不要設置的很大maxconnections允許服務器最大連接數(shù)，盡量不要設置很大threadconcur

17、rency線程弄發(fā)利用數(shù)量（cpu+disk）大2,根據(jù)（OS中顯示的請求隊列和tickets）判斷注意：全局參數(shù)設置一經(jīng)設置，隨服務器啟動預占用資源sort_buffer_size獲得更快的-ORDER BY, GROUP BYf SELECT DISTINCT r UNION DISTINCTread_rnd_buffer_size當根據(jù)鍵進行分類操作時獲得更快的ORDER BYj oin_buffer_sizeJoin連接使用全表掃描連接的緩沖大小，根據(jù)(Select_full_join )判斷read_buffer_size全表掃描時為查詢預留的緩沖大小，根據(jù)(Select_scan

18、)判斷tno) table size臨時內(nèi)存表超出設置，轉化為磁盤表，根據(jù)(Created_tmp_disk_tab：Les )判斷注意：線程參數(shù)設置的小影響性能，設置的大會導致服務器swapInnoDB -專有優(yōu)化參數(shù)innodb_log_file_size （默認 5M） 記錄InnoDB引擎redo log的文件 較大的值意味著較長的故障崩潰恢復時間Innodbflush_method （默認 fdatasync）Linux系統(tǒng)可以使用O_DIRECT處理數(shù)據(jù)文件，避免OS級別的Cache O_DIRECT模式提高數(shù)坯文件和口志文件的工0提交性能innodb_flush_log_at_t

19、rx_comm.it （默認 1）0爲每秒還行邊齊匚如希E操作。1表示在每次事務提交后執(zhí)行一次A和B操作。2表示在每次事務提交后，執(zhí)行一次B操作。A-L 0 G數(shù)據(jù)寫到CACHEBFLUSH LOG數(shù)據(jù)刷新到磁盤規(guī)劃設計-SQL優(yōu)化殘Sun第五層MySQL語句優(yōu)化語句優(yōu)化讀語句語句優(yōu)化讀語句性能差的讀語句CREATE TABLE vUserStatus_Logv (LogTime' datetime NOT NULL,Userid、int(ll) NOT NULL,MobileNo、bigint(20) DEFAULT NULL,Sid' int(ll) DEFAULT NUL

20、L,OpType、tinyint(3) unsigned DEFAULT NULL,K Requestsource" smallint(6) DEFAULT NULL,KEYv IX_PS_UserStatusLog_UserId_LogTimeK C UserID ； LogTimes),KEY IX_PS_UserStatusLog_Sids ( Sid )ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql> select count() as total from UserStatus_Log where 1; +I count(*)l+In

21、noDB引擎隨記錄越大執(zhí)行越慢I 524288 I+1 row in set (1.68 sec) mysql>優(yōu)化替代方法：mysql> create table table_count-> (table_name varchar(64) not null default M primary key，-> total bigint unsigned not null default 0>)engine myisam;Query OK, 0 rows affected (0.01 sec)mysql> select count(*) as total fro

22、m UserStatus_Log where 1 into total;Query OK, 1 rows affected (0.36 sec)mysql>insert into table_count (table_name, total) values (4UserStatus_Log total); Query OK, 1 rows affected (0.00 sec)mysql>select * from table_count where table_name = 4UserStatusog9;+1 table_name1 total1+-+1 UserStatus_L

23、og1 5242881_1_+1 row in set (0.00 sec)執(zhí)行性能差的SQL分析結果mysql> explain-> select count() as total from UserStatus_Log where 1;+-+I id I select_type I tableI type I possible_keys I key I key_len I ref rows I ExtraI+-+ +I 1 I SIMPLE I UserStatus_Log I index I NULLI idxjd I 4 I NULL 5242881 Using index

24、 I 1 row in set (0.01 sec)雖然使用了索引，但是還是進行了全表掃描優(yōu)化后的SQL執(zhí)行性能分析mysql> explain-> select table name, total from table count where table name = 4UserStatus_Log+ lid1 select_type 1 table4W WW Wtype+ possible_keys 1 key1 key_len 1 ref|rows 1£xtra+4d+1 11 SIMPLE 1 table_countsystemprimary1 NULL 1 NULL 1 NULL1 1+4“+1 row in set (0>00 sec)為僅有一行，這行的列值被優(yōu)化器認為是常數(shù)。最多一行匹配，如const表執(zhí)行，速度非?？霱ySQL Query Analyzer利用GUIX具去捕捉有性能問題的SQL語句，提高優(yōu)化效率MyWeM:1JM6 Browse QueriesSearch lypeQuery SeschMabaseIm Dis