软件开发培训班 >> 编程开发 >> MySQL


mysql的数据压缩性能对比详情

鏁版嵁榄旀柟闇�瑕佺殑鏁版嵁锛屼竴鏃﹀啓鍏ュ氨寰堝皯鎴栬�呮牴鏈笉浼氭洿鏂般�傝繖绉嶆暟鎹潪甯搁�傚悎鍘嬬缉浠ラ檷浣庣鐩樺崰鐢ㄣ�侻ySQL鏈韩鎻愪緵浜嗕袱绉嶅帇缂╂柟寮忊�曗�?code>archive寮曟搸浠ュ強閽堝MyISAM寮曟搸鐨?code>myisampack鏂瑰紡銆備粖澶╁杩欎袱绉嶆柟寮忓垎鍒繘琛屼簡娴嬭瘯锛屽姣斾簡浜岃�呭湪纾佺洏鍗犵敤浠ュ強鏌ヨ鎬ц兘鏂归潰鍚勮嚜鐨勪紭鍔ｃ�傝嚦浜庝负浠�涔堝仛杩欎釜锛屼綘浠簲璇ユ噦鐨勶紝鎴戝悗鏂囪繕浼氫粙缁嶃�備笖鐪嬫鏂囷細

1. 娴嬭瘯鐜

1.1 杞‖浠?br />

涓�鍙?64浣?code> 2.6.18-92 鍐呮牳Linux寮�鍙戞満锛?G鍐呭瓨锛?涓?code>2800Mhz Dual-Core AMD Opteron(tm) Processor 2220 CPU銆?/p>

MySQL鏀惧湪涓�鍧?200杞琒AT纭洏锛屾湭鍋?code>raid锛?/p>

MySQL鏈仛浠讳綍浼樺寲锛?鍏抽棴浜?code>query cache 锛岀洰鐨勫湪浜庨伩鍏?code>query cache瀵规祴璇曠粨鏋滈�犳垚骞叉壈銆?/p>

1.2 琛ㄧ粨鏋?/h3>

2424753鏉¤褰曪紝鐢熶骇鐜鏌愪竴涓垎鐗囩殑瀹為檯鏁版嵁锛?/p>

鍒嗗埆寤虹珛浜?partition_by1,idx_rank) 鍜?(partition_by1,chg_idx)鐨勮仈鍚堢储寮曪紝鍏朵腑 partition_by1涓?2闀垮害鐨剉archar绫诲瀷 锛岀敤浜庢绱紱鍏朵綑涓や釜瀛楁鍧囦负娴偣鏁帮紝澶氱敤浜庢帓搴忥紱

autokid浣滀负瀛愬鍒楋紝鍏呭綋PRIMARY KEY锛屼粎浣滀负鏁版嵁瑁呰浇鏃跺師瀛愭�т繚璇佺敤锛屾棤瀹為檯鎰忎箟銆?/p>

2. 娴嬭瘯鐩殑

2.1 鍘嬬缉绌洪棿瀵规瘮

鍘嬬缉鐜囪秺澶э紝鍗犵敤鐨勭鐩樼┖闂磋秺灏忥紝鐩存帴闄嶄綆鏁版嵁鐨勫瓨鍌ㄦ垚鏈紱

2.2 鏌ヨ鎬ц兘瀵规瘮

鍘嬬缉鍚庢煡璇㈡�ц兘涓嶅簲璇ユ湁鏄捐憲闄嶄綆銆?code>Archive鏄笉鏀寔绱㈠紩鐨勶紝鍥犳鎬ц兘闄嶄綆鏄繀鐒剁殑锛岄偅涔堟垜浠篃搴旇蹇冮噷鏈変釜璋憋紝鍒板簳闄嶄綆浜嗗灏戯紝鑳戒笉鑳芥帴鍙椼�?/p>

3. 娴嬭瘯宸ュ叿

3.1 mysqlslap

瀹樻柟鐨勫伐鍏峰綋鐒舵槸涓嶄簩涔嬮�夈�傚叧浜?code>mysqlslap鐨勪粙缁嶈鍙傝�?瀹樻柟鏂囨 。 銆?/p>

3.2 娴嬭瘯query

鎴彇鐢熶骇鐜璁块棶topranks_v3琛ㄧ殑瀹為檯SQL鍏?973鏉★紝浠庝腑鎶藉彇璁块棶閲忚緝澶х殑7鏉★紝骞跺彂50锛岄噸澶嶆墽琛?0娆°�傚懡浠ゅ涓嬶細

./mysqlslap --defaults-file=../etc/my.cnf -u**** -p**** -c50 -i10 -q ../t.sql --debug-info

4.娴嬭瘯缁撹

姣旇緝椤?/td> 纾佺洏绌洪棿 鑰楁椂锛堢锛?/td> CPU Idle LOAD 骞跺彂
鍩哄噯琛紙MyISAM锛?/td> 403956004 2.308 30 15 50
ARCHIVE 75630745 >300 75 4 1
PACK 99302109 2.596 30 22 50

鏍规嵁涓婇潰鐨勮〃鏍肩粰鍑虹殑娴嬭瘯鏁版嵁锛屾垜浠畝鍗曞緱鍑轰互涓嬬粨璁猴細

  • 閽堝娴嬭瘯琛紝Archive琛ㄥ崰鐢ㄧ┖闂寸害涓轰箣鍓嶇殑18.7%锛?code>myisampack鍚庣┖闂村崰鐢ㄧ害涓轰箣鍓嶇殑24.6%锛涗簩鑰呯浉宸笉澶氾紝鍗曠函浠庣┖闂村埄鐢ㄦ儏鍐垫潵鐪嬶紝鎴戜滑浼间箮闇�瑕侀�夋嫨archive琛紱
  • 鎴戜滑鍐嶇湅鏌ヨ鎬ц兘锛屼笌鍩哄噯琛ㄨ繘琛屽姣斻�傛棤璁哄湪鎬昏�楁椂杩樻槸绯荤粺璐熻浇鏂归潰锛?0骞跺彂涓嬬殑pack琛ㄦ煡璇㈡�ц兘涓庡熀鍑嗚〃鐩稿綋锛?鑰?code>archive琛ㄥ湪鍗曞苟鍙戞儏鍐典笅鑰楁椂瓒呰繃浜?鍒嗛挓 锛堝疄鍦ㄧ瓑涓嶄簡浜嗭紝kill涔嬶級锛?/li>

閭ｄ箞锛屾垜浠技涔庡彲浠ュ緱鍑虹粨璁猴紝閽堝闇�瑕佸湪绾挎煡璇㈢殑琛紝ARCHIVE寮曟搸鍩烘湰涓婂彲浠ヤ笉鑰冭檻浜嗐�?/p>

涓轰粈涔堣繖涓祴璇曡繃绋嬩腑ARCHIVE寮曟搸濡傛鍦版參鍛紵

鎴戜滑鐭ラ亾锛?code>mysql鎻愪緵archive杩欑瀛樺偍寮曟搸鏄负浜嗛檷浣庣鐩樺紑閿�锛屼絾杩樻湁涓�涓墠鎻愶紝閭ｅ氨鏄褰掓 。鐨勬暟鎹笉闇�瑕佹垨鑰呭緢灏戣鍦ㄧ嚎鏌ヨ锛屽伓灏旂殑鏌ヨ鎱竴浜涗篃鏄病鍏崇郴鐨勩�傞壌浜庝笂杩板師鍥狅紝archive琛ㄦ槸涓嶅厑璁稿缓绔嬭嚜澧炲垪涔嬪鐨勭储寮曠殑銆?/p>

鏈変簡杩欎釜鍏辫瘑锛屾垜浠嬁涓�鏉℃祴璇昐QL鏉ュ垎鏋愪竴涓嬩笉鐢ㄧ储寮曞墠鍚庣殑鏌ヨ鎬ц兘宸埆涓轰粈涔堣繖涔堝ぇ銆?/p>

鍦ㄦ垜浠殑娴嬭瘯SQL涓湁杩欎箞涓�鏉★細

SELECT c1,c2,...,cn FROM  mysqlslap.rpt_topranks_v3
WHERE ... AND partition_by1 = '50008090'
ORDER BY added_quantity3 DESC
LIMIT 500

鎴戜滑鍓嶈竟璇磋繃锛屾祴璇曠殑杩欎釜琛ㄥ湪partition_by1杩欎釜瀛楁涓婂缓绔嬩簡绱㈠紩锛岄偅涔堬紝鎴戜滑鍒濇鍒ゆ柇鍦ㄥ熀鍑嗚〃鍜?code>myisampack琛ㄤ笂锛岃繖涓煡璇㈠簲璇ョ敤鍒颁簡partition_by1鐨勭储寮曪紱 EXPLAIN 涓�涓嬶細

mysql> EXPLAIN
    -> SELECT ... FROM  mysqlslap.rpt_topranks_v3
    -> WHERE ... AND partition_by1 = '50008090'
    -> ORDER BY added_quantity3 DESC
    -> LIMIT 500G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        TABLE: rpt_topranks_v3
         type: ref
possible_keys: idx_toprank_pid,idx_toprank_chg
          KEY: idx_toprank_pid
      key_len: 99
          ref: const
         rows: 2477
        Extra: USING WHERE; USING filesort
1 row IN SET (0.00 sec)

姝ｅ鎴戜滑鎵�鏂欙紝杩欎釜鏌ヨ鐢ㄥ埌浜嗗缓绔嬪湪partition_by1杩欎釜瀛楁涓婄殑绱㈠紩锛屽尮閰嶇殑鐩爣琛屾暟涓?477锛岀劧鍚庤繕鏈変竴涓湪added_quantity3瀛楁涓婄殑鎺掑簭銆傜敱浜?code>added_quantity3娌℃湁绱㈠紩锛屾墍浠ョ敤鍒颁簡filesort銆?/p>

鎴戜滑鍐嶇湅涓�涓嬭繖鏉QL鍦ㄥ綊妗ｈ〃涓婄殑 EXPLAIN 缁撴灉锛?/strong>

mysql> EXPLAIN
    -> SELECT ... FROM  mysqlslap.rpt_topranks_v3_<strong>archive</strong>
    -> WHERE ... AND partition_by1 = '50008090'
    -> ORDER BY added_quantity3 DESC
    -> LIMIT 500G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        TABLE: rpt_topranks_v3_archive
         type: ALL
possible_keys: NULL
          KEY: NULL
      key_len: NULL
          ref: NULL
         rows: 2424753
        Extra: USING WHERE; USING filesort
1 row IN SET (0.00 sec)

EXPLAIN 璇达細鈥?/strong>鎴戞病鏈夌储寮曞彲鐢紝鎵�浠ュ彧鑳藉叏琛ㄦ壂鎻?424753琛岃褰曪紝鐒跺悗鍐嶆潵涓?code>filesort銆傗�濅綘瑕佽拷姹傛�ц兘锛岄偅鏄剧劧鏄灞?code>MySQL浜嗐�?/p>

鍒版杩欑瘒鍏充簬mysql鐨勬暟鎹帇缂╂�ц兘瀵规瘮璇︽儏鐨勬枃绔犲氨浠嬬粛鍒拌繖浜?鏇村鐩稿叧mysql鐨勬暟鎹帇缂╂�ц兘瀵规瘮鍐呭璇锋悳绱㈣剼鏈箣瀹朵互鍓嶇殑鏂囩珷鎴栫户缁祻瑙堜笅闈㈢殑鐩稿叧鏂囩珷甯屾湜澶у浠ュ悗澶氬鏀寔鑴氭湰涔嬪锛?/p>