MySQL 磁盘碎片检索

检索SQL：

SELECT CONCAT(table_schema, '.', table_name)                    AS  TABLE_NAME
      ,engine                                                   AS  TABLE_ENGINE
      ,table_type                                               AS  TABLE_TYPE
      ,table_rows                                               AS  TABLE_ROWS
      ,CONCAT(ROUND(data_length  / ( 1024 * 1024), 2), 'M')     AS  TB_DATA_SIZE
      ,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M')     AS  TB_IDX_SIZE
      ,CONCAT(ROUND((data_length + index_length )
            / ( 1024 * 1024 ), 2), 'M')                         AS  TOTAL_SIZE
      ,CASE WHEN  data_length =0 THEN 0
            ELSE  ROUND(index_length / data_length, 2) END      AS  TB_INDX_RATE
    ,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB')            AS  TB_DATA_FREE
    ,CASE WHEN (data_length + index_length) = 0 THEN 0
             ELSE ROUND(data_free/(data_length + index_length),2)
     END                                                        AS  TB_FRAG_RATE
FROM information_schema.TABLES  
WHERE ROUND(DATA_FREE/1024/1024,2) 
ORDER BY data_free DESC;

如果表为innodb引擎，可使用一下命令收缩碎片:

alter table table_name engine=innodb;