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>

最新文章

  1. 浅析session&cookie
  2. Hammer.js
  3. 浅谈Apache性能调优
  4. java的文件流：字节流（FileInputStream、FileOutputStream）和字符流（FileReader、FileWriter）。
  5. jquery 实现层级下拉框联动效果 代码
  6. 李洪强iOS开发之XMPP
  7. [Oracle] 使用触发器实现IP限制用户登录
  8. Install Typical IIS Workloads
  9. hdu1881 毕业bg（深搜索dfs）
  10. 在Centos7 更改Docker默认镜像和容器的位置
  11. VMware的安装和使用
  12. 使用jquery获取url及url参数的方法
  13. JS中的事件&对象
  14. mysql 双机热备注意事项
  15. PHP操作redis详细讲解（转）
  16. Flutter学习笔记与整合
  17. 【转】Entity Framework教程(第二版)
  18. [转]linux最新分区方案
  19. AGC007C Pushing Balls
  20. 主窗体上按钮jig画图时，CAD得不到焦点的问题

热门文章

  1. 【css】常用的几种水平垂直居中方式与盒子模型，面试经常问到！
  2. LeetCode.1005-K次取负数组和最大(Maximize Sum Of Array After K Negations)
  3. 将dos窗口调白教程
  4. heartbeat高可用
  5. 配置java开发环境，存在多个版本JDK时，怎样让所需版本生效
  6. hbase部署
  7. Python的入门（day1）
  8. you_are_the_one(区间dp)
  9. 设置Eclipse代码自动提示
  10. adb 设置安卓连接wifi