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>
mysql 查询碎片的方法
标签：inf code failed audit repair err air col ODB
