mysql浠巑yisam杩佺Щ鍒癷nnodb鍏ㄨ繃绋

榛樿鏄疢yISAM锛岄渶瑕佷慨鏀逛负InnoDB;

璁剧疆mysql鐨勯粯璁ゅ瓨鍌ㄥ紩鎿 鍦╩y.cnf涓慨鏀:
default-storage-engine=InnoDB

璁剧疆褰撳墠浼氳瘽鐨勯粯璁ゅ瓨鍌ㄥ紩鎿:
SET storage_engine=InnoDB;

鐒跺悗鍐峴how engines鍙互鐪嬪埌榛樿寮曟搸鏄疘nnoDB浜嗭紝鐒跺悗鍐嶅皢鍘熸暟鎹簱涓殑琛ㄤ粠MyISAM搴撹浆鎹㈡垚InnoDB,鍏蜂綋鎿嶄綔濡備笅锛

1銆佷粠鍘焟ysql鏁版嵁搴揻ahao涓鍑鸿〃缁撴瀯锛屼笉甯︽暟鎹
mysqldump -uxxx -p鈥檟xx鈥 鈥搉o-data fahao > fahao.sql

2銆佸湪mysql涓垱寤烘祴璇曞簱fahao_test
create database fahao_test

3銆佸湪瀵煎嚭鐨勮〃缁撴瀯fahao.sql涓壘鍒癊NGINE=MyISAM DEFAULT CHARSET=utf8;淇敼鎴怑NGINE=InnoDB DEFAULT CHARSET=utf8;

4銆佸湪灏唂ahao.sql琛ㄧ粨鏋勫鍏ュ埌娴嬭瘯搴揻ahao_test涓紝骞舵煡鐪嬪鍏ョ殑琛ㄧ被鍨嬫槸涓嶆槸InnoDB锛
鐢╯ource瀵煎叆鍚庯紝鏌ョ湅琛ㄧ被鍨嬫柟娉曪細mysql> show table status like 鈥榝ahao_name鈥橽G

5銆佷粠鍘焟ysql鏁版嵁搴揻ahao涓鍑烘暟鎹紝涓嶉渶瑕佽〃缁撴瀯
mysqldump -uxxx -p鈥檟xxx鈥 -t fahao > fahao_data.sql

6銆乫ahao_data.sql瀵煎叆鍒版祴璇曞簱fahao_test涓

鑷虫fahao搴撶殑琛ㄤ粠MyISAM寮曟搸杞崲鎴怚nnoDB浜嗭紝浣嗘槸鏈変竴涓棶棰橈紝鏌ョ湅琛ㄧ殑璇︾粏淇℃伅鏃跺彂鐜癉ata_free涓嶄负闆讹紝璇存槑瀛樺湪鏁版嵁纰庣墖锛岄渶瑕佽繘琛屼紭鍖栵紝鍦ㄧ綉涓婃煡璇㈣祫鏂欙紝鍙戠幇鏈夊涓嬬殑鏂规硶.

鎬庝箞鏌ョ湅杩欎簺纰庣墖锛
鏄剧ず浣犳暟鎹簱涓瓨鍦ㄧ鐗囩殑鍏ㄩ儴鍒楄〃:
select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in (鈥榠nformation_schema鈥, 鈥榤ysql鈥) and data_free > 0;

鏌ョ湅fahao_test鏁版嵁搴撲腑鎵鏈夎〃鐨勮缁嗕俊鎭
mysql>show table status from fahao_test\G

鏌ョ湅鍗曚釜琛ㄧ殑璇︾粏淇℃伅锛 琛ㄧ被鍨嬫槸鍚nnoDB锛屾槸鍚︽湁鏁版嵁纰庣墖
mysql> show table status from fahao_test like 鈥榯able_name鈥橽G
mysql> show table status like 鈥榯able_name鈥橽G
mysql> show table status from fahao_test where name=鈥檛able_name鈥橽G

MySQL鎻愪緵浜嗕竴绉嶇畝渚跨殑淇鏂规硶,杩欏氨鏄墍璋撶殑浼樺寲鍒楄〃(浼樺寲琛ㄧ┖闂达紝鍑忓皯鏁版嵁纰庣墖锛岄噴鏀捐〃绌洪棿)
瀵筂yISAM銆両nnoDB寮曟搸鐨勮〃鏍兼湁鐢紝鍦↖nnoDB琛ㄤ笂闈㈡墽琛屼細鍑虹幇涓嬮潰鐨勬彁绀猴細铏界劧鎻愮ず涓嶆敮鎸侊紝optimize锛屼絾鏄凡缁忚繘琛岄噸寤哄拰鍒嗘瀽锛岀┖闂村凡缁忓洖鏀.
optimize table table_name;

淇敼琛ㄧ殑瀛樺偍寮曟搸鏃讹紝浼氶噸寤鸿〃锛岀粨鏋勬枃浠躲佹暟鎹枃浠躲佺储寮曟枃浠剁瓑鏂囦欢,杩欑鏂瑰紡浠庡師鐞嗕笂锛屾劅瑙夊彲浠ワ紝浣嗘槸娴嬭瘯骞舵病鏈夋垚鍔熴
ALTER TABLE table_name ENGINE=鈥橧nnoDB鈥;
Query OK, 2919 rows affected (5.92 sec)
Records: 2919 Duplicates: 0 Warnings: 0
Data_free: 5242880 鏈夊彉鍖
鏈変竴瀹氱殑浣滅敤锛屼絾鏃犳硶瀹屽叏閲婃斁鍑鸿〃绌洪棿锛屼娇Data_free涓洪浂

濡傛灉闇瑕佹鏌ュ苟淇鎵鏈夌殑鏁版嵁搴撶殑鏁版嵁琛紝閭d箞鍙互浣跨敤锛
/usr/local/mysql/bin/mysqlcheck -uroot -p -o -A
濡傛灉闇瑕佷慨澶嶆寚瀹氱殑鏁版嵁搴撶敤
mysqlcheck -uxx -p fahao_test
娴嬭瘯娌℃湁鏁堟灉锛孌ata_free: 4194304

浠ヤ笂涓夌鏂规硶閮芥祴璇曡繃锛屼絾閮芥棤娉曞皢鎵鏈夋暟鎹洖鏀讹紝Data_free鏃犳硶涓洪浂锛
灏濊瘯灏嗚〃鍗曠嫭mysqldump鍑哄悗锛宒rop鎺夎〃锛岀劧鍚庨噸鏂皊ource瀵煎叆dump鐨勮〃锛岀粨鏋滄棤鏁堬紝浠嶇劧鏈 Data_free: 4194304

鏈鍚庢煡璇㈣祫鏂欎负浠涔堜細鏈塂ata_free鎵嶅彂鐜帮紝璺熻〃缁撴瀯銆佸瓧娈甸暱搴︾殑璁剧疆銆佸瓧娈电被鍨嬨乨ata page閮芥湁鍏崇郴锛屾病鏈夊悎鐞嗚缃繖浜涢兘浼氬鑷存暟鎹鐗囷紝鏃犳硶鍏呭垎鍒╃敤琛ㄧ┖闂.濡傛灉涓瀹氳灏咲ata_free浼樺寲涓洪浂锛岄渶瑕佸鏁翠釜琛ㄨ繘琛屼紭鍖栨墠琛岋紝浠ヤ笅鏄熺敤鍒汉鐨勪紭鍖栨柟娉.

濡傛灉鍦ㄥ疄闄呬笟鍔′腑锛岀‘瀹為渶瑕佸湪InnoDB琛ㄤ腑瀛樺偍BLOB銆乀EXT銆侀暱VARCHAR鍒楁椂锛屾湁涓嬮潰鍑犵偣寤鸿锛
1銆佸敖鍙兘灏嗘墍鏈夋暟鎹簭鍒楀寲銆佸帇缂╀箣鍚庯紝瀛樺偍鍦ㄥ悓涓涓垪閲岋紝閬垮厤鍙戠敓澶氭off-page锛
2銆佸疄闄呮渶澶у瓨鍌ㄩ暱搴︿綆浜255鐨勫垪锛岃浆鎴怴ARCHAR鎴栬匔HAR绫诲瀷锛堝鏋滄槸鍙橀暱鏁版嵁浜岃呮病鍖哄埆锛屽鏋滄槸瀹氶暱鏁版嵁锛屽垯浣跨敤CHAR绫诲瀷锛夛紱
3銆佸鏋滄棤娉曞皢鎵鏈夊垪鏁村悎鍒颁竴涓垪锛屽彲浠ラ鑰屾眰鍏舵锛屾牴鎹瘡涓垪鏈澶ч暱搴﹁繘琛屾帓鍒楃粍鍚堝悗鎷嗗垎鎴愬涓瓙琛紝灏介噺鏄殑姣忎釜瀛愯〃鐨勬昏闀垮害灏忎簬8KB锛屽噺灏戝彂鐢無ff-page鐨勯鐜囷紱
4銆佷笂杩板缓璁槸鍦╠ata page涓洪粯璁ょ殑16KB鍓嶆彁涓嬶紝濡傛灉淇敼鎴8KB鎴栬呭叾浠栧ぇ灏忥紝璇疯嚜琛屾牴鎹笂杩扮悊璁鸿繘琛屾祴璇曪紝鎵惧埌鏈鍚堥傜殑鍊硷紱
5銆佸瓧绗﹀瀷鍒楅暱搴﹀皬浜255鏃讹紝鏃犺閲囩敤CHAR杩樻槸VARCHAR鏉ュ瓨鍌紝鎴栬呮妸VARCHAR鍒楅暱搴﹀畾涔変负255锛岄兘涓嶄細瀵艰嚧瀹為檯琛ㄧ┖闂村澶э紱
6銆佷竴鑸湪娓告垙棰嗗煙浼氱敤鍒版瘮杈冨鐨凚LOB鍒楃被鍨嬶紝娓告垙鐣屽悓琛屽彲浠ュ叧娉ㄤ笅銆

PS锛
鏇存崲鎴怚nnoDB鍚庢渶濂藉仛鎴愮嫭绔嬭〃绌洪棿锛岀紪杈憁y.cnf鍦╥nnodb娈典腑澧炲姞innodb_file_per_table = 1(1涓哄惎鐢紝0涓虹鐢)閰嶇疆鍙傛暟,杩欐牱InnoDB浼氬姣忎釜琛ㄥ垱寤轰竴涓暟鎹枃浠讹紝鐒跺悗鍙渶瑕佽繍琛孫PTIMIZE TABLE 鍛戒护灏卞彲浠ラ噴鏀炬墍鏈夊凡缁忓垹闄ょ殑纾佺洏绌洪棿銆
閫氳繃mysql璇彞鍙互鏌ョ湅璇ュ彉閲忕殑鍊硷細mysql> show variables like 鈥%per_table%鈥;

All posts

Other pages

鍙戣〃鍥炲

鎮ㄧ殑鐢靛瓙閭鍦板潃涓嶄細琚叕寮銆 蹇呭~椤瑰凡鐢*鏍囨敞