榛樿鏄疢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涓洪浂
濡傛灉闇瑕佹鏌ュ苟淇鎵鏈夌殑鏁版嵁搴撶殑鏁版嵁琛紝閭ｄ箞鍙互浣跨敤锛
/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%鈥;
鍙戣〃鍥炲