innodb表碎片整理方法

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5752985.html

1、相关表信息：
+------------------------+----------------+-----------+------------+------------+
| Table Name             | Number of Rows | Data Size | Index Size | Total      |
+------------------------+----------------+-----------+------------+------------+
| emoprod.transfe |        3542930 | 881.5781M | 2029.9688M | 2911.5469M |
+------------------------+----------------+-----------+------------+------------+
1 row in set (0.00 sec)
发现索引比表还大2倍多呢？transfe表是业务的主要表之一，其特点是：DML频繁，和索引比较多。
mysql> 
mysql> show table status from emoprod like 'transfe';
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| transfe | InnoDB |      10 | Compact    | 3543033 |            260 |   924401664 |               0 |   2128576512 |   7340032 |           NULL | 2016-09-25 17:43:44 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

表实际占用的空间：
[root@emo-mysql-02 emoprod]# ls -lh |grep transfe
-rw-r----- 1 mysql mysql  14K Sep 25 17:39 transfe.frm
-rw-r----- 1 mysql mysql 5.6G Sep 28 16:03 transfe.ibd

2、开始碎片整理：
mysql> show table status from emoprod like 'transfe'\G
*************************** 1. row ***************************
           Name: transfe
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3543053
 Avg_row_length: 260
    Data_length: 924401664
Max_data_length: 0
   Index_length: 2128576512
      Data_free: 7340032
 Auto_increment: NULL
    Create_time: 2016-09-25 17:43:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

进行碎片整理：
mysql> optimize table transfe;
+------------------------+----------+----------+-------------------------------------------------------------------+
| Table                  | Op       | Msg_type | Msg_text                                                          |
+------------------------+----------+----------+-------------------------------------------------------------------+
| emoprod.transfe | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| emoprod.transfe | optimize | status   | OK                                                                |
+------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (5 min 15.06 sec)


观察数据库
+------+-------------+-----------+---------------+---------+--------+-----------------------------------------------------------------------------+-------------------------+
| Id   | User            | Host      | db            | Command | Time   | State                                                                       | Info                    |
+------+-------------+-----------+---------------+---------+--------+-----------------------------------------------------------------------------+-------------------------+
|    2 | system user |           | NULL          | Connect | 253445 | Waiting for master to send event                                            | NULL                    |
|    3 | system user |           | NULL          | Connect |      0 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                    |
| 1180 | root        | localhost | emoprod | Query   |    281 | altering table                                                              | optimize table transfe |
| 1271 | root        | localhost | NULL          | Query   |      0 | init                                                                        | show processlist        |
+------+-------------+-----------+---------------+---------+--------+-----------------------------------------------------------------------------+-------------------------+
4 rows in set (0.00 sec)


mysql> select * from  information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1918481819
                 trx_state: RUNNING
               trx_started: 2016-09-28 16:06:22
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 1180
                 trx_query: optimize table transfe
       trx_operation_state: NULL
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 0
     trx_lock_memory_bytes: 360
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)


mysql>  show table status from emoprod like 'transfe'\G
*************************** 1. row ***************************
           Name: transfe
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 5312336
 Avg_row_length: 200
    Data_length: 1066401792
Max_data_length: 0
   Index_length: 2961178624
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2016-09-28 16:11:38
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

整理后：
[root@emo-mysql-02 emoprod]# ls -lh |grep transfe
-rw-rw---- 1 mysql mysql  14K Sep 28 16:06 transfe.frm
-rw-rw---- 1 mysql mysql 2.9G Sep 28 16:11 transfe.ibd

表文件从5.6G下降到2.9G，整理完成
注：MySQL碎片整理在5.6的时候，注意观察锁，建议在业务低峰期进行整理。

---the end