鏈€杩戝湪鍋歁YSQL澶ф壒閲忔暟鎹殑娴嬭瘯锛屽氨绠€鍗曟€荤粨涓€涓嬮亣鍒扮殑闂锛?
棣栧厛鎴戞槸绠€鍗曠殑鍐欎簡涓€涓狹YSQL鐨勫惊鐜彃鍏ユ暟鎹殑SP锛屽叿浣撳涓嬶細
杩欐槸鎻掑叆100W鏁版嵁鐨勮繃绋嬪拰缁撴灉锛屽彲浠ョ湅鍒版槸鎹簡55min +20S绾?320绉掞紙绾?00rows/s锛?鐪嬪埌涔嬪悗鎴戞槸鍙穿婧冿紝灏卞湪缃戜笂鏌ヤ簡浜涙彁閫熺殑鏂规硶锛?
0. 鏈€蹇殑褰撶劧鏄洿鎺?copy 鏁版嵁搴撹〃鐨勬暟鎹枃浠讹紙鐗堟湰鍜屽钩鍙版渶濂借鐩稿悓鎴栫浉浼硷級锛? 1. 璁剧疆 innodb_flush_log_at_trx_commit聽=聽0 锛岀浉瀵逛簬 innodb_flush_log_at_trx_commit聽= 1 鍙互鍗佸垎鏄庢樉鐨勬彁鍗囧鍏ラ€熷害锛? 2. 浣跨敤 load data local infile 鎻愰€熸槑鏄撅紱
3. 淇敼鍙傛暟聽bulk_insert_buffer_size锛?璋冨ぇ鎵归噺鎻掑叆鐨勭紦瀛橈紱
4. 鍚堝苟澶氭潯 insert 涓轰竴鏉? insert into t values(a,b,c),聽 (d,e,f) ,,,
5. 鎵嬪姩浣跨敤浜嬬墿锛?
褰撴暟鎹噺杈冨ぇ鏃讹紝濡備笂鐧句竾鐢氳嚦涓婂崈涓囪褰曟椂锛屽悜MySQL鏁版嵁搴撲腑瀵煎叆鏁版嵁閫氬父鏄竴涓瘮杈冭垂鏃剁殑杩囩▼銆傞€氬父鍙互閲囧彇浠ヤ笅鏂规硶鏉ュ姞閫熻繖涓€杩囩▼锛?
涓€銆佸浜嶮yisam绫诲瀷鐨勮〃锛屽彲浠ラ€氳繃浠ヤ笅鏂瑰紡蹇€熺殑瀵煎叆澶ч噺鐨勬暟鎹€偮燗LTER TABLE tblname DISABLE KEYS; loading the data ALTER TABLE tblname ENABLE KEYS; 杩欎袱涓懡浠ょ敤鏉ユ墦寮€鎴栬€呭叧闂璏yisam琛ㄩ潪鍞竴绱㈠紩鐨勬洿鏂般€傚湪瀵煎叆澶ч噺鐨勬暟鎹埌涓€涓潪绌虹殑Myisam琛ㄦ椂锛岄€氳繃璁剧疆杩欎袱涓懡浠わ紝鍙互鎻愰珮瀵煎叆鐨勬晥鐜囥€傚浜庡鍏ュぇ閲忔暟鎹埌涓€涓┖鐨凪yisam琛紝榛樿灏辨槸鍏堝鍏ユ暟鎹劧鍚庢墠鍒涘缓绱㈠紩鐨勶紝鎵€浠ヤ笉鐢ㄨ繘琛岃缃€?
浜屻€佸浜嶪nnodb绫诲瀷鐨勮〃锛屾湁浠ヤ笅鍑犵鏂瑰紡鍙互鎻愰珮瀵煎叆鐨勬晥鐜囷細聽鈶犲洜涓篒nnodb绫诲瀷鐨勮〃鏄寜鐓т富閿殑椤哄簭淇濆瓨鐨勶紝鎵€浠ュ皢瀵煎叆鐨勬暟鎹寜鐓т富閿殑椤哄簭鎺掑垪锛屽彲浠ユ湁鏁堢殑鎻愰珮瀵煎叆鏁版嵁鐨勬晥鐜囥€傚鏋淚nnodb琛ㄦ病鏈変富閿紝閭ｄ箞绯荤粺浼氶粯璁ゅ垱寤轰竴涓唴閮ㄥ垪浣滀负涓婚敭锛屾墍浠ュ鏋滃彲浠ョ粰琛ㄥ垱寤轰竴涓富閿紝灏嗗彲浠ュ埄鐢ㄨ繖涓紭鍔挎彁楂樺鍏ユ暟鎹殑鏁堢巼銆?
鈶″湪瀵煎叆鏁版嵁鍓嶆墽琛孲ET UNIQUE_CHECKS=0锛屽叧闂敮涓€鎬ф牎楠岋紝鍦ㄥ鍏ョ粨鏉熷悗鎵цSET UNIQUE_CHECKS=1锛屾仮澶嶅敮涓€鎬ф牎楠岋紝鍙互鎻愰珮瀵煎叆鐨勬晥鐜囥€?
鈶㈠鏋滃簲鐢ㄤ娇鐢ㄨ嚜鍔ㄦ彁浜ょ殑鏂瑰紡锛屽缓璁湪瀵煎叆鍓嶆墽琛孲ET AUTOCOMMIT=0锛屽叧闂嚜鍔ㄦ彁浜わ紝瀵煎叆缁撴潫鍚庡啀鎵цSET AUTOCOMMIT=1锛屾墦寮€鑷姩鎻愪氦锛屼篃鍙互鎻愰珮瀵煎叆鐨勬晥鐜囥€?
鑰屾垜鍒涘缓鐨勬槸Innodb绫诲瀷鐨勮〃锛屽垎浜?28涓垎鍖恒€傝€屾垜渚濈収浠ヤ笂鐨勬柟娉曪紝璁剧疆濡備笅锛?
鎻掑叆鐧句竾鏁版嵁鐨凷P濡備笅锛?
鍙互鏄庢樉鐨勭湅鍒版彃鍏ョ櫨涓囨暟鎹槸100S宸﹀彸锛岄€熷害鎻愬崌浜?3鍊嶄箣澶氥€?
閫熷害鏄彁鍗囦簡涓嶅皯锛岄偅灏卞姞澶ф彃鍏ョ殑鏁版嵁閲忥紝鎻愬崌10鍊嶏紝鍗虫彃鍏ュ崈涓囩殑鏁版嵁閲忥紝鍏蜂綋鐨凷P濡備笅锛?
鍙互鐪嬪埌鏃堕棿宸笉澶氭槸1200s宸﹀彸锛屽洜涓哄瓧娈靛姞闀夸簡锛屽彲鑳戒篃鏈夊奖鍝嶆彃鍏ョ殑閫熷害銆?
涓轰簡鍏蜂綋楠岃瘉锛屽氨鎸夊崈涓囪鎻掑叆锛屽瓧娈电殑闀垮害涓?000瀛楄妭锛屾潵鏌ョ湅缁撴灉锛屽叿浣撶殑SP鍜岀粨鏋滃涓嬶細
鍙互鐪嬪埌鐢ㄦ椂33min 51s鏈堬紙绾?031绉掞級锛屽嵆锛?900row/s锛夛紝閫熷害涓嬮檷寰堝锛屽瓧绗﹂暱搴︾湅鏉ユ槸鐢ㄥ奖鍝嶇殑銆?
varchar瀛楁
瀛楁鐨勯檺鍒跺湪瀛楁瀹氫箟鐨勬椂鍊欐湁浠ヤ笅瑙勫垯锛毬? a) 瀛樺偍闄愬埗聽
varchar 瀛楁鏄皢瀹為檯鍐呭鍗曠嫭瀛樺偍鍦ㄨ仛绨囩储寮曚箣澶栵紝鍐呭寮€澶寸敤1鍒?涓瓧鑺傝〃绀哄疄闄呴暱搴︼紙闀垮害瓒呰繃255鏃堕渶瑕?涓瓧鑺傦級锛屽洜姝ゆ渶澶ч暱搴︿笉鑳借秴杩?5535銆偮? b) 缂栫爜闀垮害闄愬埗聽
瀛楃绫诲瀷鑻ヤ负gbk锛屾瘡涓瓧绗︽渶澶氬崰2涓瓧鑺傦紝鏈€澶ч暱搴︿笉鑳借秴杩?2766;聽
銆€銆€瀛楃绫诲瀷鑻ヤ负utf8锛屾瘡涓瓧绗︽渶澶氬崰3涓瓧鑺傦紝鏈€澶ч暱搴︿笉鑳借秴杩?1845銆偮? 銆€銆€瀵逛簬鑻辨枃姣旇緝澶氱殑璁哄潧 锛屼娇鐢℅BK鍒欐瘡涓瓧绗﹀崰鐢?涓瓧鑺傦紝鑰屼娇鐢║TF锛?鑻辨枃鍗村彧鍗犱竴涓瓧鑺傘€偮? 銆€銆€鑻ュ畾涔夌殑鏃跺€欒秴杩囦笂杩伴檺鍒讹紝鍒檝archar瀛楁浼氳寮鸿杞负text绫诲瀷锛屽苟浜х敓warning銆偮? c) 琛岄暱搴﹂檺鍒堵? 銆€銆€瀵艰嚧瀹為檯搴旂敤涓璿archar闀垮害闄愬埗鐨勬槸涓€涓瀹氫箟鐨勯暱搴︺€?MySQL瑕佹眰涓€涓鐨勫畾涔夐暱搴︿笉鑳借秴杩?5535銆傝嫢瀹氫箟鐨勮〃闀垮害瓒呰繃杩欎釜鍊硷紝鍒欐彁绀郝? 銆€銆€ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to 銆€銆€銆€change some columns to TEXT or BLOBs銆偮? 2銆佽绠椾緥瀛惵? 銆€銆€涓句袱涓緥璇存槑涓€涓嬪疄闄呴暱搴︾殑璁＄畻銆偮? a) 鑻ヤ竴涓〃鍙湁涓€涓獀archar绫诲瀷锛屽瀹氫箟涓郝? create table t4(c varchar(N)) charset=gbk;聽
鍒欐澶凬鐨勬渶澶у€间负(65535-1-2)/2= 32766銆偮? 鍑?鐨勫師鍥犳槸瀹為檯琛屽瓨鍌ㄤ粠绗簩涓瓧鑺傚紑濮?;聽
鍑?鐨勫師鍥犳槸varchar澶撮儴鐨?涓瓧鑺傝〃绀洪暱搴?聽
闄?鐨勫師鍥犳槸瀛楃缂栫爜鏄痝bk銆偮?
b) 鑻ヤ竴涓〃瀹氫箟涓郝? create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;聽
鍒欐澶凬鐨勬渶澶у€间负 (65535-1-2-4-30*3)/3=21812聽
鍑?鍜屽噺2涓庝笂渚嬬浉鍚?聽
鍑?鐨勫師鍥犳槸int绫诲瀷鐨刢鍗?涓瓧鑺?聽
鍑?0*3鐨勫師鍥犳槸char(30)鍗犵敤90涓瓧鑺傦紝缂栫爜鏄痷tf8銆偮? 濡傛灉琚玽archar瓒呰繃涓婅堪鐨刡瑙勫垯锛岃寮鸿浆鎴恡ext绫诲瀷锛屽垯姣忎釜瀛楁鍗犵敤瀹氫箟闀垮害涓?1瀛楄妭锛屽綋鐒惰繖宸茬粡涓嶆槸鈥渧archar鈥濅簡銆?
鍦╩ysql 涓敤"SHOW VARIABLES LIKE '%CHAR%'"鏌ョ湅瀛楃闆嗭細
鍐嶆鍗囩骇鎻掑叆鐨勬暟鎹噺锛屾彁鍗?0鍊嶏紝鐪嬫彃鍏ョ殑鏃堕棿鍙婂崰鐢ㄧ殑鍐呭瓨锛屽瓧娈电殑瀛楄妭鍚屾牱涓?000锛屽叿浣撶殑SP鍜岀粨鏋滃涓嬶細
浠庝笂鍥惧彲浠ユ竻妤氱殑鐪嬪埌锛屾彃鍏?浜挎潯鏁版嵁鐨勬椂闂翠负5hours +20 min+ 56s=19256s锛屽钩鍧囨彃鍏ョ殑鏉℃暟涓猴紙5193 rows/s锛夈€傛牴涓婃鎻掑叆1鍗冧竾鏉＄殑鏃堕棿宸笉澶氾紝鍐嶇湅鎵€鑰楃鐩樼┖闂达紝鐢ㄤ簡98G鐨勭┖闂达紝璺熶笂娆℃彃鍏ュ崈涓囨潯鏁版嵁鏃剁殑锛?6G-17G=9G锛変篃鏄垚绾挎€у叧绯荤殑銆傛寜鐓ф湰鏈?00G鐨勭鐩樼┖闂达紝瀛樺偍1琛?K瀛楄妭澶у皬鐨勬暟鎹紝鏈満鍙互瀛樺偍鐞嗘兂鏋侀檺鎯呭喌涓嬩负5浜挎潯鏁版嵁锛屼繚瀹堜负4~4.5浜垮乏鍙冲悎閫傦紝浠ラ槻鍏朵粬鐨勫簲鐢ㄦ垨鑰呮暟鎹簱鐨刄NDO锛岀储寮曠┖闂村崰鐢ㄣ€?
鏈€鍚庡啀鐪嬩竴娆℃煡璇㈢殑鏃堕棿锛屼笂娆℃彃鍏ョ櫨涓囨暟锛屾煡璇㈡暟鎹噺鐨勬椂闂?
鍥犱负鍒涘缓浜嗙储寮曪紝鍦ㄦ煡鐧句竾绾х殑鏁版嵁閲忔椂锛屾椂闂存槸1绉掑乏鍙筹紝鍦ㄦ暟鎹噺涓婂崌鍒板崈涓囨椂锛屾煡璇?浜?鐧句竾鏃讹紝鏃堕棿涓?Min 30S,鍐嶆彃鍏?浜挎暟鎹紝鏌ヨ鏁版嵁閲忥紝鏃堕棿杈惧埌27min 43s,鍙锛屼笉鏄嚎鎬у叧绯伙紝鏄嚑浣曠骇澧炲姞鐨勩€?
=====================================================================================================
鐜板湪鎻忚堪闆嗙兢鐜鐨勬祴璇?
闆嗙兢锛?2G鍐呭瓨 锛?00G纭洏锛?鍙拌櫄鎷熸満涔熷氨鏄?涓妭鐐癸細188.188.2.181锛堜富鑺傜偣锛屾暟鎹妭鐐瑰拰SQL鑺傜偣锛夈€?88.188.2.182锛堟暟鎹妭鐐瑰拰SQL鑺傜偣锛夊拰188.188.2.183锛堟暟鎹妭鐐瑰拰SQL鑺傜偣锛夈€?root鐩綍鍒嗗尯纾佺洏绌洪棿200G锛堝師鍏堥粯璁ょ殑鏄?0G锛夈€佹彃鍏ョ殑鏁版嵁閲忎负8000KW锛屾墍鍗犵鐩樼┖闂翠负涓嬪浘
鎻掑叆鍓嶇殑鍐呭瓨锛?
鎻掑叆鍚庡唴瀛橈細
鏁版嵁鍐呭瓨鎵€鍗犵┖闂达細(910051-5)*32K=27.77G 聽-----300W鏉?G
绱㈠紩鍐呭瓨鎵€鍗犵┖闂达細54980*8K=430M
鎻掑叆鍓嶇殑纾佺洏绌洪棿锛?
鎻掑叆鍚庣殑纾佺洏绌洪棿锛?
纾佺洏绌洪棿锛?00G*锛?4%-5%锛?58G 聽 聽-----143W鏉?G
鎻掑叆鍓嶇殑鏉℃暟锛?
鎻掑叆鍚庣殑鏉℃暟锛?
鏉℃暟鐨勶細82551267鏉?
=================================================================================================================
2016-06-03,鍦ㄤ笂涓婅堪閰嶇疆鐨勫熀纭€涓婏紝澧炲姞涓€涓暟鎹妭鐐癸紝184锛宒atamemory:28000M indexmemory=1024M NoOfFragmentLogFiles=64
==================================================================================================================
鍐呭瓨濡備笂
纾佺洏鎯呭喌锛?
鍒拌揪涓€涓嘲鍊硷紝鎻掍笉杩涘幓锛岄渶瑕佽皟鏁村弬鏁?
------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
======================================================================================================
鐜板湪寮€濮婦ataMemory =28000M
NoOfFragmentLogFiles=128
鎻掑叆8400W鏉℃暟鎹?
===================================================================================================================
=======================================================涓婅堪涓変釜鎴浘鏄?鍙版暟鎹妭鐐圭殑鏋侀檺鎯呭喌锛侊紒锛?
涓轰簡瀵规瘮鐨勪弗璋ㄦ€у拰绉戝鎬э紝鐜板湪閲嶆柊姣旇緝3鍙版暟鎹妭鐐瑰拰4鍙版暟鎹妭鐐圭殑妯悜姣旇緝锛屼负浜嗛伩鍏嶇鐞嗚妭鐐瑰拰鏁版嵁鑺傜偣SQL鑺傜偣鍦ㄤ竴鍙版満瀛愮珯鍗犵敤绌洪棿銆傜幇鍦ㄦ妸绠＄悊鑺傜偣鍗曟嫀鍑烘潵锛?85鍋氱鐞嗚妭鐐癸紝181銆?82銆?83鍋氭暟鎹妭鐐瑰拰SQL鑺傜偣锛?
绠＄悊鑺傜偣鐨勯厤缃細Datamemory=30000M Indexmemory=1024M MaxNoOfConcurrentTransactions=100000 MaxNoOfConcurrentOperations=200000 NoOfFragmentLogFiles=128
-----------------------------------------------------------------------------------------------------------------
聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 涓ゅ彴鏁版嵁鑺傜偣 聽聽聽聽聽聽聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 涓夊彴鏁版嵁鑺傜偣聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 鍥涘彴鏁版嵁鑺傜偣
-----------------------------------------------------------------------------------------------------------------
data useage memory聽聽聽 4*32K---->849052*32K锛?5.9G锛?*32K---->910541*32K(27.787G) 4*32K----->881480*32K(26.9G)
index usage memory聽聽聽 10*8K-->45180 *8K锛?52.9M锛?聽聽 9*8K---->51753*8k(404.3M) 8*8k---->47572*8K(371.66M)
max record number聽聽聽聽聽聽聽 533300000聽 聽 聽 聽 聽 聽 聽聽 76200000聽聽 聽聽聽聽聽 110500000
initial used disk size聽聽聽聽聽聽聽 8.8G 聽 聽 聽 聽 聽 聽 聽聽聽聽聽 8.8G 聽聽聽聽 聽 聽 聽聽 8.9G
final used disk size聽聽聽聽聽聽聽聽 67G 聽聽聽聽聽聽聽聽聽聽聽聽聽 68G聽聽聽聽聽聽聽聽聽聽 69G
鍐呭瓨鍗犵敤鐜囬兘涓?4%鏃讹紝琛╥s full
===================================================================================================================
鍦ㄨ祫婧愭湁闄愮殑鎯呭喌涓嬶紝鍚堢悊鐨勯厤缃槸涓夊彴鏁版嵁鑺傜偣锛屾瘡鍙拌櫄鎷熸満鐨勯厤缃槸32G 鍐呭瓨锛?00G纭洏锛岀洰鏍囨槸瀛樺偍杩?0T鐨勬暟鎹紝濡傛灉鍗曠函鐨勬寜杩欑闆嗙兢鍐呭瓨琛ㄦ潵瀛樺偍锛岃400涓暟鎹妭鐐癸紝杩欎釜鏈夌偣澶稿紶浜嗭紝鐜板疄鐨勭敓浜х幆澧冧腑涔熶笉鍙兘鏄繖鏍风殑锛屾墍浠ワ紝鎺ヤ笅鏉ラ渶瑕佺敤纾佺洏琛ㄦ潵鐧诲満浜嗭紝澶ц嚧鐨勮璁℃柟妗堢殑娴佺▼鍥惧涓嬶細
=======================================================================================
妯℃嫙鏁版嵁娴佺殑鏉戣繃绋嬪涓嬶細prod_dt
create procedure prod_dt()
聽begin
聽declare i,maxid int;
set i=0;
聽select max(id) into maxid from loadtest;
聽if maxid<10000000 then
聽while i<100000 do
聽insert into loadtest(data,record_date) values('1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111','2016-06-06');
聽set i=i+1;
聽end while;
聽commit;
聽elseif maxid>10000000 and maxid<20000000 then
聽set i=0;
while i<100000 do
insert into loadtest(data,record_date) values('2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222','2016-06-07');
set i=i+1;
聽end while;
聽commit;
聽elseif maxid>20000000 and maxid<30000000 then
set i=0;
while i<100000 do
聽insert into loadtest(data,record_date) values('3333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333','2016-06-08');
聽set i=i+1;
聽end while;
聽commit;
elseif maxid>30000000 and maxid<40000000 then
聽set i=0;
while i<100000 do
insert into loadtest(data,record_date) values('4444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444','2016-06-09');
聽set i=i+1;
聽end while;
聽commit;
聽elseif maxid>40000000 and maxid<50000000 then
聽set i=0;
while i<100000 do
聽insert into loadtest(data,record_date) values('55555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555','2016-06-10');
聽set i=i+1;
聽end while;
聽commit;
elseif maxid>50000000 and maxid<60000000 then
聽set i=0;
while i<100000 do
insert into loadtest(data,record_date) values('666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666','2016-06-11');
set i=i+1;
聽end while;
聽commit;
聽elseif maxid>60000000 and maxid<70000000 then
聽set i=0;
while i<100000 do
聽insert into loadtest(data,record_date) values('7777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777','2016-06-12');
set i=i+1;
聽end while;
聽commit;
聽elseif maxid>70000000 and maxid<80000000 then
聽set i=0;
聽while i<100000 do
insert into loadtest(data,record_date) values('888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888','2016-06-13');
聽set i=i+1;
end while;
commit;
聽elseif maxid>80000000 and maxid<90000000 then
聽set i=0;
聽while i<100000 do
insert into loadtest(data,record_date) values('999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999','2016-06-14');
聽set i=i+1;
聽end while;
聽commit;
聽else
聽alter event e_load disable;
end if;
end
================================================================================================
妯℃嫙瀛樺偍瓒呰繃7澶╃殑鏁版嵁锛岃鏀惧埌鏂囦欢涓腑杞紝鍐峫oad data infile鎻掑叆鍒扮鐩樿〃閲岋紝鍥犱负MYSQL鐨勫瓨鍌ㄨ繃绋嬩笉鍏佽鏈塋OAD DATA infile 璇彞锛屾晠鑰岋紝闇€瑕佸啓涓€涓猄HELL鑴氭湰瀹氭椂璋冪敤銆傚叿浣撶殑杞Щ鏁版嵁鑴氭湰transfer_dt锛?
create procedure transfer_dt()
begin
declare maxdate,currdate varchar(20);
select max(record_date) into maxdate from loadtest;
set currdate=curdate();
start transaction;
if maxdate=currdate then
select * from loadtest where record_date<date_sub(maxdate,interval 7 day) into outfile '/tmp/loadtest.txt';
delete from loadtest where record_date<date_sub(maxdate,interval 7 day);
else
select * from loadtest where record_date<date_sub(currdate,interval 7 day) into outfile '/tmp/loadtest.txt';
delete from loadtest where record_date<date_sub(currdate,interval 7 day);聽聽聽聽 聽
end if;
end
============================鍏蜂綋鐨刲oad data shell scripts ,z闇€瑕佺敤linux鐨勫畾鏃朵换鍔″幓璋冪敤shell 鑴氭湰:
#!/bin/bash
mysql -u root -vv -pholystar << EOF
use cluster;
load data infile '/tmp/loadtest.txt' into table load_dsk;
exit
EOF
======================================================================================================
澶ц嚧鐨勬暟鎹璁℃柟妗堝拰鍏蜂綋妯℃嫙璁捐宸笉澶氬埌杩欏効锛屼笉杩囬渶瑕佽繘琛屽帇鍔涙祴璇曪紝鏈夐棶棰樼殑璇濓紝鍐嶆洿鏂板惂锛侊紒锛?