PHPWIND 9 mysql大容量数据库碎片清理方法

主要是之前用火车采集高了很多内容，数据库每次备份的时候都是几十个G，后来查看数据库的时候其实内容并没有这么多，但是因为mysql高版本innoDB的原因，导致数据库有大量的碎片，所以只能手动清理，研究了很多网上的教程和方法，但是都不理想，几个方法测试后清理效果都不太理想，所以想了一个比较笨的办法：直接将数据表类型转换成myisam，具体SQL代码如下：

ALTER TABLE  `pw_acloud_apis` ENGINE = myisam;

其中`pw_acloud_apis`是数据表名称；

技巧：通过各种工具得到程序所有表名，excel拉出所有表的语法，然后一起执行即可例如：

ALTER TABLE `pw_acloud_apis` ENGINE = MyISAM;
ALTER TABLE `pw_acloud_app_configs` ENGINE = MyISAM;
ALTER TABLE `pw_acloud_apps` ENGINE = MyISAM;
ALTER TABLE `pw_acloud_extras` ENGINE = MyISAM;
ALTER TABLE `pw_acloud_keys` ENGINE = MyISAM;
ALTER TABLE `pw_acloud_sql_log` ENGINE = MyISAM;
ALTER TABLE `pw_acloud_table_settings` ENGINE = MyISAM;
ALTER TABLE `pw_admin_auth` ENGINE = MyISAM;
ALTER TABLE `pw_admin_config` ENGINE = MyISAM;
ALTER TABLE `pw_admin_custom` ENGINE = MyISAM;
ALTER TABLE `pw_admin_role` ENGINE = MyISAM;
ALTER TABLE `pw_advertisement` ENGINE = MyISAM;
ALTER TABLE `pw_announce` ENGINE = MyISAM;
ALTER TABLE `pw_app_majia` ENGINE = MyISAM;
ALTER TABLE `pw_app_mark_record` ENGINE = MyISAM;
ALTER TABLE `pw_app_poll` ENGINE = MyISAM;
ALTER TABLE `pw_app_poll_option` ENGINE = MyISAM;
ALTER TABLE `pw_app_poll_thread` ENGINE = MyISAM;
ALTER TABLE `pw_app_poll_voter` ENGINE = MyISAM;
ALTER TABLE `pw_app_search_record` ENGINE = MyISAM;
ALTER TABLE `pw_app_weibojifen_address` ENGINE = MyISAM;
ALTER TABLE `pw_app_weibojifen_order` ENGINE = MyISAM;
ALTER TABLE `pw_app_weibojifen_prize` ENGINE = MyISAM;
ALTER TABLE `pw_app_withdraw` ENGINE = MyISAM;
ALTER TABLE `pw_app_withdraw_list` ENGINE = MyISAM;
ALTER TABLE `pw_application` ENGINE = MyISAM;
ALTER TABLE `pw_application_log` ENGINE = MyISAM;
ALTER TABLE `pw_attachs` ENGINE = MyISAM;
ALTER TABLE `pw_attachs_thread` ENGINE = MyISAM;
ALTER TABLE `pw_attachs_thread_buy` ENGINE = MyISAM;
ALTER TABLE `pw_attachs_thread_download` ENGINE = MyISAM;
ALTER TABLE `pw_attention` ENGINE = MyISAM;
ALTER TABLE `pw_attention_fresh` ENGINE = MyISAM;
ALTER TABLE `pw_attention_fresh_index` ENGINE = MyISAM;
ALTER TABLE `pw_attention_fresh_relations` ENGINE = MyISAM;
ALTER TABLE `pw_attention_recommend_cron` ENGINE = MyISAM;
ALTER TABLE `pw_attention_recommend_friends` ENGINE = MyISAM;
ALTER TABLE `pw_attention_recommend_record` ENGINE = MyISAM;
ALTER TABLE `pw_attention_type` ENGINE = MyISAM;
ALTER TABLE `pw_attention_type_relations` ENGINE = MyISAM;
ALTER TABLE `pw_banner` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_forum` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_forum_extra` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_forum_life` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_forum_statistics` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_forum_user` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_posts` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_posts_place` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_posts_reply` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_posts_topped` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_specialsort` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_threads` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_threads_buy` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_threads_cate_index` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_threads_content` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_threads_digest_index` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_threads_hits` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_threads_index` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_threads_overtime` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_threads_place` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_threads_sort` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_threads_weight` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_topic_type` ENGINE = MyISAM;
ALTER TABLE `pw_bbs_topped` ENGINE = MyISAM;
ALTER TABLE `pw_bbsinfo` ENGINE = MyISAM;
ALTER TABLE `pw_cache` ENGINE = MyISAM;
ALTER TABLE `pw_collect_content` ENGINE = MyISAM;
ALTER TABLE `pw_common_config` ENGINE = MyISAM;
ALTER TABLE `pw_common_cron` ENGINE = MyISAM;
ALTER TABLE `pw_common_emotion` ENGINE = MyISAM;
ALTER TABLE `pw_common_emotion_category` ENGINE = MyISAM;
ALTER TABLE `pw_common_nav` ENGINE = MyISAM;
ALTER TABLE `pw_common_process` ENGINE = MyISAM;
ALTER TABLE `pw_credit_log` ENGINE = MyISAM;
ALTER TABLE `pw_credit_log_operate` ENGINE = MyISAM;
ALTER TABLE `pw_design_bak` ENGINE = MyISAM;
ALTER TABLE `pw_design_component` ENGINE = MyISAM;
ALTER TABLE `pw_design_cron` ENGINE = MyISAM;
ALTER TABLE `pw_design_data` ENGINE = MyISAM;
ALTER TABLE `pw_design_image` ENGINE = MyISAM;
ALTER TABLE `pw_design_module` ENGINE = MyISAM;
ALTER TABLE `pw_design_page` ENGINE = MyISAM;
ALTER TABLE `pw_design_permissions` ENGINE = MyISAM;
ALTER TABLE `pw_design_portal` ENGINE = MyISAM;
ALTER TABLE `pw_design_push` ENGINE = MyISAM;
ALTER TABLE `pw_design_script` ENGINE = MyISAM;
ALTER TABLE `pw_design_segment` ENGINE = MyISAM;
ALTER TABLE `pw_design_shield` ENGINE = MyISAM;
ALTER TABLE `pw_design_structure` ENGINE = MyISAM;
ALTER TABLE `pw_domain` ENGINE = MyISAM;
ALTER TABLE `pw_draft` ENGINE = MyISAM;
ALTER TABLE `pw_frag_template` ENGINE = MyISAM;
ALTER TABLE `pw_fresh_site` ENGINE = MyISAM;
ALTER TABLE `pw_hook` ENGINE = MyISAM;
ALTER TABLE `pw_hook_inject` ENGINE = MyISAM;
ALTER TABLE `pw_invite_code` ENGINE = MyISAM;
ALTER TABLE `pw_like_content` ENGINE = MyISAM;
ALTER TABLE `pw_like_log` ENGINE = MyISAM;
ALTER TABLE `pw_like_source` ENGINE = MyISAM;
ALTER TABLE `pw_like_statistics` ENGINE = MyISAM;
ALTER TABLE `pw_like_tag` ENGINE = MyISAM;
ALTER TABLE `pw_like_tag_relations` ENGINE = MyISAM;
ALTER TABLE `pw_link` ENGINE = MyISAM;
ALTER TABLE `pw_link_relations` ENGINE = MyISAM;
ALTER TABLE `pw_link_type` ENGINE = MyISAM;
ALTER TABLE `pw_log` ENGINE = MyISAM;
ALTER TABLE `pw_log_login` ENGINE = MyISAM;
ALTER TABLE `pw_medal_info` ENGINE = MyISAM;
ALTER TABLE `pw_medal_log` ENGINE = MyISAM;
ALTER TABLE `pw_medal_user` ENGINE = MyISAM;
ALTER TABLE `pw_message_config` ENGINE = MyISAM;
ALTER TABLE `pw_message_notices` ENGINE = MyISAM;
ALTER TABLE `pw_online_guest` ENGINE = MyISAM;
ALTER TABLE `pw_online_statistics` ENGINE = MyISAM;
ALTER TABLE `pw_online_user` ENGINE = MyISAM;
ALTER TABLE `pw_pay_order` ENGINE = MyISAM;
ALTER TABLE `pw_recycle_reply` ENGINE = MyISAM;
ALTER TABLE `pw_recycle_topic` ENGINE = MyISAM;
ALTER TABLE `pw_remind` ENGINE = MyISAM;
ALTER TABLE `pw_report` ENGINE = MyISAM;
ALTER TABLE `pw_seo` ENGINE = MyISAM;
ALTER TABLE `pw_space` ENGINE = MyISAM;
ALTER TABLE `pw_space_domain` ENGINE = MyISAM;
ALTER TABLE `pw_style` ENGINE = MyISAM;
ALTER TABLE `pw_tag` ENGINE = MyISAM;
ALTER TABLE `pw_tag_attention` ENGINE = MyISAM;
ALTER TABLE `pw_tag_category` ENGINE = MyISAM;
ALTER TABLE `pw_tag_category_relation` ENGINE = MyISAM;
ALTER TABLE `pw_tag_record` ENGINE = MyISAM;
ALTER TABLE `pw_tag_relation` ENGINE = MyISAM;
ALTER TABLE `pw_task` ENGINE = MyISAM;
ALTER TABLE `pw_task_cache` ENGINE = MyISAM;
ALTER TABLE `pw_task_group` ENGINE = MyISAM;
ALTER TABLE `pw_task_user` ENGINE = MyISAM;
ALTER TABLE `pw_upgrade_log` ENGINE = MyISAM;
ALTER TABLE `pw_user` ENGINE = MyISAM;
ALTER TABLE `pw_user_active_code` ENGINE = MyISAM;
ALTER TABLE `pw_user_ban` ENGINE = MyISAM;
ALTER TABLE `pw_user_behavior` ENGINE = MyISAM;
ALTER TABLE `pw_user_belong` ENGINE = MyISAM;
ALTER TABLE `pw_user_data` ENGINE = MyISAM;
ALTER TABLE `pw_user_education` ENGINE = MyISAM;
ALTER TABLE `pw_user_groups` ENGINE = MyISAM;
ALTER TABLE `pw_user_info` ENGINE = MyISAM;
ALTER TABLE `pw_user_login_ip_recode` ENGINE = MyISAM;
ALTER TABLE `pw_user_mobile` ENGINE = MyISAM;
ALTER TABLE `pw_user_mobile_verify` ENGINE = MyISAM;
ALTER TABLE `pw_user_open_account` ENGINE = MyISAM;
ALTER TABLE `pw_user_permission_groups` ENGINE = MyISAM;
ALTER TABLE `pw_user_register_check` ENGINE = MyISAM;
ALTER TABLE `pw_user_register_ip` ENGINE = MyISAM;
ALTER TABLE `pw_user_tag` ENGINE = MyISAM;
ALTER TABLE `pw_user_tag_relation` ENGINE = MyISAM;
ALTER TABLE `pw_user_work` ENGINE = MyISAM;
ALTER TABLE `pw_weibo` ENGINE = MyISAM;
ALTER TABLE `pw_weibo_comment` ENGINE = MyISAM;
ALTER TABLE `pw_windid_admin_auth` ENGINE = MyISAM;
ALTER TABLE `pw_windid_admin_config` ENGINE = MyISAM;
ALTER TABLE `pw_windid_admin_custom` ENGINE = MyISAM;
ALTER TABLE `pw_windid_admin_role` ENGINE = MyISAM;
ALTER TABLE `pw_windid_app` ENGINE = MyISAM;
ALTER TABLE `pw_windid_application` ENGINE = MyISAM;
ALTER TABLE `pw_windid_application_log` ENGINE = MyISAM;
ALTER TABLE `pw_windid_area` ENGINE = MyISAM;
ALTER TABLE `pw_windid_config` ENGINE = MyISAM;
ALTER TABLE `pw_windid_hook` ENGINE = MyISAM;
ALTER TABLE `pw_windid_hook_inject` ENGINE = MyISAM;
ALTER TABLE `pw_windid_message` ENGINE = MyISAM;
ALTER TABLE `pw_windid_message_dialog` ENGINE = MyISAM;
ALTER TABLE `pw_windid_message_relation` ENGINE = MyISAM;
ALTER TABLE `pw_windid_notify` ENGINE = MyISAM;
ALTER TABLE `pw_windid_notify_log` ENGINE = MyISAM;
ALTER TABLE `pw_windid_school` ENGINE = MyISAM;
ALTER TABLE `pw_windid_user` ENGINE = MyISAM;
ALTER TABLE `pw_windid_user_black` ENGINE = MyISAM;
ALTER TABLE `pw_windid_user_data` ENGINE = MyISAM;
ALTER TABLE `pw_windid_user_info` ENGINE = MyISAM;
ALTER TABLE `pw_word` ENGINE = MyISAM;

 

转载于:https://my.oschina.net/40813/blog/889821