innodb表磁碟重組方法

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 Sep28 16:06 transfe.frm
-rw-rw---- 1 mysql mysql 2.9G Sep28 16:11 transfe.ibd

表檔案從5.6G下降到2.9G，整理完成
註：MySQL磁碟重組在5.6的時候，注意觀察鎖，建議在業務低峰期進行整理。