要整理碎片，首先要了解碎片的计算方法。
可以通过show table [from|in db_name] status like '%table_name%'命令查看：
mysql> show table from employees status like 't1'\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 1176484 Avg_row_length: 86 Data_length: 101842944 Max_data_length: 0 Index_length: 0 Data_free: 39845888 Auto_increment: NULL Create_time: 2018-08-28 13:40:19 Update_time: 2018-08-28 13:50:43 Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
碎片大小 = 数据总大小 - 实际表空间文件大小
数据总大小 = Data_length + Data_length = 101842944
实际表空间文件大小 = rows * Avg_row_length = 1176484 * 86 = 101177624
碎片大小 = (101842944 - 101177624) / 1024 /1024 = 0.63MB
通过information_schema.tables的DATA_FREE列查看表有没有碎片：
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_ROWS, t.DATA_LENGTH, t.INDEX_LENGTH, concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS datafree FROM information_schema.tables t WHERE t.TABLE_SCHEMA = 'employees' +--------------+--------------+------------+-------------+--------------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | datafree | +--------------+--------------+------------+-------------+--------------+----------+ | employees | departments | 9 | 16384 | 16384 | 0.00M | | employees | dept_emp | 331143 | 12075008 | 11567104 | 0.00M | | employees | dept_manager | 24 | 16384 | 32768 | 0.00M | | employees | employees | 299335 | 15220736 | 0 | 0.00M | | employees | salaries | 2838426 | 100270080 | 36241408 | 5.00M | | employees | t1 | 1191784 | 48824320 | 17317888 | 5.00M | | employees | titles | 442902 | 20512768 | 11059200 | 0.00M | | employees | ttt | 2 | 16384 | 0 | 0.00M | +--------------+--------------+------------+-------------+--------------+----------+ 8 rows in set (0.00 sec)
需表上有主键或唯一索引才能运行 [root@mysqldb1 14:31:16 /root] # pt-online-schema-change --alter='engine=innodb' D=employees,t=salaries --execute No slaves found. See --recursion-method if host mysqldb1 has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `employees`.`salaries`... Creating new table... Created new table employees._salaries_new OK. Altering new table... Altered `employees`.`_salaries_new` OK. 2018-08-28T14:37:01 Creating triggers... 2018-08-28T14:37:01 Created triggers OK. 2018-08-28T14:37:01 Copying approximately 2838426 rows... Copying `employees`.`salaries`: 74% 00:10 remain 2018-08-28T14:37:41 Copied rows OK. 2018-08-28T14:37:41 Analyzing new table... 2018-08-28T14:37:42 Swapping tables... 2018-08-28T14:37:42 Swapped original and new tables OK. 2018-08-28T14:37:42 Dropping old table... 2018-08-28T14:37:42 Dropped old table `employees`.`_salaries_old` OK. 2018-08-28T14:37:42 Dropping triggers... 2018-08-28T14:37:42 Dropped triggers OK. Successfully altered `employees`.`salaries`.
运行OPTIMIZE TABLE， InnoDB创建一个新的.ibd具有临时名称的文件，只使用存储的实际数据所需的空间。优化完成后，InnoDB删除旧.ibd文件并将其替换为新文件。如果先前的.ibd文件显着增长但实际数据仅占其大小的一部分，则运行OPTIMIZE TABLE可以回收未使用的空间。
mysql>optimize table account; +--------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+----------+----------+-------------------------------------------------------------------+ | test.account | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.account | optimize | status | OK | +--------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.09 sec)
# cat optimize_table.sh
#!/bin/sh socket=/tmp/mysql3306.socktime=`date +"%Y-%m-%d"` SQL="select concat(d.TABLE_SCHEMA,'.',d.TABLE_NAME) from information_schema.TABLES d where d.TABLE_SCHEMA = 'employees'"
optimize_table_name=$(/usr/local/mysql/bin/mysql -S $socket -e "$SQL"|grep -v "TABLE_NAME")
echo "Begin Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>/tmp/optimize_table_$time.log
for table_list in $optimize_table_name do
echo `date +"%Y-%m-%d %H:%M:%S"` "alter table $table_list engine=innodb ...">>/tmp/optimize_table_$time.log/usr/local/mysql/bin/mysql -S $socket -e "alter table $table_list engine=innoDB"
done echo "End Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>>/tmp/optimize_table_$time.log
输出内容# cat optimize_table_2018-08-30.log
Begin Optimize Table at: 2018-08-30 08:43:212018-08-30 08:43:21 alter table employees.departments engine=innodb ...2018-08-30 08:43:21 alter table employees.dept_emp engine=innodb ...2018-08-30 08:43:27 alter table employees.dept_manager engine=innodb ...2018-08-30 08:43:27 alter table employees.employees engine=innodb ...2018-08-30 08:43:32 alter table employees.salaries engine=innodb ...2018-08-30 08:44:02 alter table employees.t1 engine=innodb ...2018-08-30 08:44:17 alter table employees.titles engine=innodb ...2018-08-30 08:44:28 alter table employees.ttt engine=innodb ...End Optimize Table at: 2018-08-30 08:44:28
转载于:https://www.cnblogs.com/wanbin/p/9555415.html
相关资源：JAVA上百实例源码以及开源项目