MySQL索引碎片整理

查看碎片信息：

show table status like 'table_name';

Index_length 代表索引的总量
Data_free 代表碎片数量

从information_schema中获取信息：

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  

ORDER BY data_free DESC;

碎片整理：

optimize table table_name;

过程时间长短取决于表大小和碎片多少，
返回结果optimize status OK则整理完成；

碎片整理过程会添加表级排他锁，需要找非繁忙期进行操作。