本次测试环境是 mysql 5.7.23，表空间为每个表单独表空间
mysql> sHOW VARIABLES LIKE 'innodb_file_per_tabl%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
查看表mysql> show create table ht.tb;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| tb | CREATE TABLE `tb` (
`id` int(11) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> select * from ht.tb limit 3;
+------+---------+
| id | name |
+------+---------+
| 1 | qdds |
| 2 | horizon |
| 3 | beijing |
+------+---------+
3 rows in set (0.36 sec)构造大表
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 10000; WHILE v1 > 0 DO
insert into ht.tb select * from tb;
commit;
SET v1 = v1 - 1;
END WHILE;
END;call dowhile();查看表文件大小及行数
mysql> select a.FILE_NAME,a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 from information_schema.FILES a where a.FILE_ID=288;
+-------------+----------------------------------------------+
| FILE_NAME | a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 |
+-------------+----------------------------------------------+
| ./ht/tb.ibd | 1.593750000000 |
+-------------+----------------------------------------------+
1 row in set (0.00 sec)mysql> select count(1) from ht.tb;
+----------+
| count(1) |
+----------+
| 25165824 |
+----------+
1 row in set (27.22 sec)
删除id=2的行
mysql> delete from ht.tb where id=2;
Query OK, 8388608 rows affected (2 min 2.45 sec)mysql> select count(1) from ht.tb;
+----------+
| count(1) |
+----------+
| 16777216 |
+----------+
1 row in set (33.04 sec)mysql> select a.FILE_NAME,a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 from information_schema.FILES a where a.FILE_ID=288;
+-------------+----------------------------------------------+
| FILE_NAME | a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 |
+-------------+----------------------------------------------+
| ./ht/tb.ibd | 1.593750000000 |
+-------------+----------------------------------------------+
1 row in set (0.00 sec)
mysql> select table_schema,table_name,data_free,data_length,index_length,table_rows,CREATE_TIME FROM information_schema.TABLES where TABLE_NAME='tb';
+--------------+------------+-----------+-------------+--------------+------------+---------------------+
| table_schema | table_name | data_free | data_length | index_length | table_rows | CREATE_TIME |
+--------------+------------+-----------+-------------+--------------+------------+---------------------+
| ht | tb | 694157312 | 990904320 | 0 | 16252952 | 2018-08-20 23:51:18 |
+--------------+------------+-----------+-------------+--------------+------------+---------------------+
从上面可知表空间文件大小没变，空间没释放
优化下表在查看
mysql> optimize table ht.tb;
+-------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------+----------+----------+-------------------------------------------------------------------+
| ht.tb | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| ht.tb | optimize | status | OK |
+-------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 min 29.57 sec)mysql> select table_schema,table_name,data_free,data_length,index_length,table_rows,CREATE_TIME FROM information_schema.TABLES where TABLE_NAME='tb';
+--------------+------------+-----------+-------------+--------------+------------+---------------------+
| table_schema | table_name | data_free | data_length | index_length | table_rows | CREATE_TIME |
+--------------+------------+-----------+-------------+--------------+------------+---------------------+
| ht | tb | 4194304 | 746569728 | 0 | 16739684 | 2018-08-21 00:52:36 |
+--------------+------------+-----------+-------------+--------------+------------+---------------------+
1 row in set (0.00 sec)
mysql> select a.FILE_ID,a.FILE_NAME,a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 from information_schema.FILES a where a.FILE_ID=291;
+---------+-------------+----------------------------------------------+
| FILE_ID | FILE_NAME | a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 |
+---------+-------------+----------------------------------------------+
| 291 | ./ht/tb.ibd | 0.710937500000 |
+---------+-------------+----------------------------------------------+
1 row in set (0.00 sec)
优化以后和预计的表空间大小（删除1/3数据，文件减小1/3）一致；从data_free来看也缩小很多；
optimize table在操作的准备阶段和提交阶段期间短暂地进行独占表锁定，对于InnoDB表，OPTIMIZE TABLE映射到 ALTER TABLE … FORCE，重建表以更新索引统计信息并释放聚簇索引中未使用的空间；
查询information_schema.FILES 的FILE_ID 和 information_schema.TABLES的CREATE_TIME 可知OPTIMIZE TABLE相当于表删除重建
