mysql 查询碎片的方法
mysql> select table_schema,concat('alter table ',table_name, ' engine=Innodb ;'),(data_length+index_length)/1024/1024 length,engine,data_free,table_rows from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema') and data_free !=0 group by table_name order by table_rows asc; +---------------------+------------------------------------------------------------------+---------------+--------+-----------+------------+ | table_schema | concat('alter table ',table_name, ' engine=Innodb ;') | length | engine | data_free | table_rows | +---------------------+------------------------------------------------------------------+---------------+--------+-----------+------------+ | testdbs_students001 | alter table student_period_history_201705 engine=Innodb ; | 2.03125000 | InnoDB | 4194304 | 7407 | | testdbs_students001 | alter table student_period_history_201706 engine=Innodb ; | 1.51562500 | InnoDB | 4194304 | 7815 | | testdbs_students001 | alter table transfer_order_record engine=Innodb ; | 1.82812500 | InnoDB | 4194304 | 8001 | | testdbs_students001 | alter table teacher_account engine=Innodb ; | 2.40625000 | InnoDB | 4194304 | 8221 | | testdbs_students001 | alter table period_appeal_log engine=Innodb ; | 1.51562500 | InnoDB | 4194304 | 9412 | | testdbs_students001 | alter table student_period_history_201704 engine=Innodb ; | 2.23437500 | InnoDB | 4194304 | 9996 | | testdbs_students001 | alter table student_period_history_201709 engine=Innodb ; | 1.51562500 | InnoDB | 4194304 | 12011 | | testdbs_students001 | alter table failed_jobs engine=Innodb ; | 8.51562500 | InnoDB | 4194304 | 12595 | | testdbs_students001 | alter table student_period_history_201707 engine=Innodb ; | 2.51562500 | InnoDB | 4194304 | 16019 | | testdbs_students001 | alter table student_course_repair_log engine=Innodb ; | 50.90625000 | InnoDB | 7340032 | 16955 | | testdbs_students001 | alter table order_period_warning engine=Innodb ; | 3.84375000 | InnoDB | 4194304 | 17711 | | testdbs_students001 | alter table send_msg_log engine=Innodb ; | 3.40625000 | InnoDB | 4194304 | 19140 | | testdbs_students001 | alter table back_period_log engine=Innodb ; | 2.51562500 | InnoDB | 4194304 | 22785 | | testdbs_students001 | alter table teacher_finance engine=Innodb ; | 2.50000000 | InnoDB | 4194304 | 23018 | | testdbs_students001 | alter table student_period_history_201711 engine=Innodb ; | 2.51562500 | InnoDB | 4194304 | 23263 | | testdbs_students001 | alter table student_period_history_201710 engine=Innodb ; | 2.51562500 | InnoDB | 4194304 | 24336 | | testdbs_students001 | alter table student_period_cancel_request engine=Innodb ; | 5.03125000 | InnoDB | 4194304 | 25585 | | testdbs_students001 | alter table student_period_history_201708 engine=Innodb ; | 3.51562500 | InnoDB | 4194304 | 29830 | | testdbs_students001 | alter table student_course_error_log engine=Innodb ; | 22.06250000 | InnoDB | 4194304 | 31661 | | testdbs_students001 | alter table order_paymenttype_changed_logs engine=Innodb ; | 5.50000000 | InnoDB | 4194304 | 33330 | | testdbs_students001 | alter table student_period_history_201801 engine=Innodb ; | 4.51562500 | InnoDB | 4194304 | 40481 | | testdbs_students001 | alter table student_period_history_201712 engine=Innodb ; | 4.51562500 | InnoDB | 4194304 | 41839 | | testdbs_students001 | alter table teacher_teaching_award_list engine=Innodb ; | 6.54687500 | InnoDB | 4194304 | 45883 | | testdbs_students001 | alter table teacher_base_finance_log engine=Innodb ; | 13.57812500 | InnoDB | 4194304 | 56640 | | testdbs_students001 | alter table student_period_history_201802 engine=Innodb ; | 6.51562500 | InnoDB | 4194304 | 59748 | | testdbs_students001 | alter table withdraw engine=Innodb ; | 23.54687500 | InnoDB | 4194304 | 61167 | | testdbs_students001 | alter table student_period_history_201803 engine=Innodb ; | 7.51562500 | InnoDB | 3145728 | 69367 | | testdbs_students001 | alter table teacher_student_period engine=Innodb ; | 8.03125000 | InnoDB | 4194304 | 74849 | | testdbs_students001 | alter table student_period_history_201804 engine=Innodb ; | 8.51562500 | InnoDB | 4194304 | 80023 | | testdbs_students001 | alter table student_period_history_201805 engine=Innodb ; | 8.51562500 | InnoDB | 4194304 | 83745 | | testdbs_students001 | alter table student_course engine=Innodb ; | 11.03125000 | InnoDB | 4194304 | 90741 | | testdbs_students001 | alter table student_period_history_201809 engine=Innodb ; | 9.51562500 | InnoDB | 4194304 | 92332 | | testdbs_students001 | alter table student_period_history_201806 engine=Innodb ; | 9.51562500 | InnoDB | 4194304 | 94333 | | testdbs_students001 | alter table order_credit_info engine=Innodb ; | 45.07812500 | InnoDB | 7340032 | 97368 | | testdbs_students001 | alter table student_period_history_201810 engine=Innodb ; | 15.51562500 | InnoDB | 4194304 | 155869 | | testdbs_students001 | alter table student_period_history_201807 engine=Innodb ; | 15.51562500 | InnoDB | 4194304 | 156100 | | testdbs_students001 | alter table student_period_history_201811 engine=Innodb ; | 16.51562500 | InnoDB | 4194304 | 159868 | | testdbs_students001 | alter table student_period_history_201905 engine=Innodb ; | 17.51562500 | InnoDB | 4194304 | 170079 | | testdbs_students001 | alter table student_period_history_201902 engine=Innodb ; | 17.51562500 | InnoDB | 4194304 | 175909 | | testdbs_students001 | alter table order_period_warning_info engine=Innodb ; | 24.54687500 | InnoDB | 6291456 | 178991 | | testdbs_students001 | alter table student_period_history_201812 engine=Innodb ; | 19.54687500 | InnoDB | 4194304 | 194860 | | testdbs_students001 | alter table student_period_history_201808 engine=Innodb ; | 20.54687500 | InnoDB | 4194304 | 203676 | | testdbs_students001 | alter table student_period_history_201904 engine=Innodb ; | 20.54687500 | InnoDB | 4194304 | 204831 | | testdbs_students001 | alter table student_period_history_201903 engine=Innodb ; | 21.54687500 | InnoDB | 3145728 | 218756 | | testdbs_students001 | alter table student_period_history_201901 engine=Innodb ; | 22.54687500 | InnoDB | 4194304 | 219717 | | testdbs_students001 | alter table student_finance engine=Innodb ; | 28.54687500 | InnoDB | 7340032 | 368189 | | testdbs_students001 | alter table course_real_period_log engine=Innodb ; | 79.03125000 | InnoDB | 5242880 | 404931 | | testdbs_students001 | alter table course_real_period engine=Innodb ; | 71.04687500 | InnoDB | 5242880 | 406539 | | testdbs_students001 | alter table audition_order engine=Innodb ; | 179.03125000 | InnoDB | 7340032 | 548999 | | testdbs_students001 | alter table inform_order engine=Innodb ; | 429.85937500 | InnoDB | 7340032 | 648426 | | testdbs_students001 | alter table teacher_full_time_finance_log engine=Innodb ; | 136.79687500 | InnoDB | 7340032 | 700182 | | testdbs_students001 | alter table teacher_all_time_finance_log engine=Innodb ; | 137.79687500 | InnoDB | 4194304 | 712279 | | testdbs_students001 | alter table student_period_cancel_logs engine=Innodb ; | 90.14062500 | InnoDB | 4194304 | 841515 | | testdbs_students001 | alter table course_order engine=Innodb ; | 377.00000000 | InnoDB | 6291456 | 1113689 | | testdbs_students001 | alter table order engine=Innodb ; | 547.34375000 | InnoDB | 7340032 | 1121134 | | testdbs_students001 | alter table order_status_log engine=Innodb ; | 137.28125000 | InnoDB | 5242880 | 1182890 | | testdbs_students001 | alter table student_finance_log engine=Innodb ; | 171.21875000 | InnoDB | 5242880 | 1349596 | | testdbs_students001 | alter table uid_to_sale engine=Innodb ; | 122.98437500 | InnoDB | 6291456 | 1480265 | | testdbs_students001 | alter table period_msg_to_teacher engine=Innodb ; | 424.75000000 | InnoDB | 6291456 | 2183860 | | testdbs_students001 | alter table teacher_course_remark engine=Innodb ; | 2983.00000000 | InnoDB | 4194304 | 2226556 | | testdbs_students001 | alter table student_period engine=Innodb ; | 386.21875000 | InnoDB | 6291456 | 2344035 | | testdbs_students001 | alter table teacher_part_time_finance_log engine=Innodb ; | 645.54687500 | InnoDB | 7340032 | 3224173 | | testdbs_students001 | alter table teacher_subsidy_detail engine=Innodb ; | 827.20312500 | InnoDB | 6291456 | 3812535 | | testdbs_students001 | alter table student_course_log engine=Innodb ; | 816.54687500 | InnoDB | 4194304 | 4489196 | | testdbs_students001 | alter table teacher_finance_log engine=Innodb ; | 1449.75000000 | InnoDB | 5242880 | 6493046 | | testdbs_students001 | alter table order_update_log engine=Innodb ; | 1088.00000000 | InnoDB | 7340032 | 7501555 | +---------------------+------------------------------------------------------------------+---------------+--------+-----------+------------+ 66 rows in set (0.02 sec) mysql>