MySQL5.6
innodb_file_per_table=1 #使用独立表空间，动态参数。（5.6默认OFF，5.7默认ON）
1、drop/truncate table方式操作表空间能自动回收（磁盘空间）
1)、创建procedure，循环insert一定量数据
##use test
##drop procedure pro1;
DELIMITER //
create procedure pro1()
begin
declare i int;
set i=1;
while i<100000 do
insert into test.cc(id,name) values(i, "aa");
set i=i+1;
end while;
end;//
2)、调用procedure ：
mysql> call pro1();
3)、查看表大小、数据量：
select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows
from information_schema.tables where table_schema='test' and table_name='CC';
+------------+------------+------------+
| table_name | total_mb | table_rows |
+------------+------------+------------+
| cc | 3.51562500 | 100246 |
+------------+------------+------------+
1 row in set (0.31 sec)
4)、truncate清表：
mysql> truncate table test.cc;
Query OK, 0 rows affected (0.73 sec)
5)、再次查看表空间已经回收：
cc.ibd 由 11264KB 回收到96KB 。
mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows
-> from information_schema.tables where table_schema='test' and table_name='CC';
+------------+------------+------------+
| table_name | total_mb | table_rows |
+------------+------------+------------+
| cc | 0.01562500 | 0 |
+------------+------------+------------+
1 row in set (0.00 sec)
mysql>
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.11-log |
+------------+
1 row in set (0.08 sec)
mysql>
