mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.89 sec)

mysql> create table `t` (
    ->  `a` varchar(100) not null default '',
    ->  `b` varchar(50) not null default '',
    ->  `c` varchar(5000) default null,
    ->  `d` timestamp null default current_timestamp on update current_timestamp,
    ->  `e` timestamp null default current_timestamp,
    ->  `f` bigint(25) not null auto_increment,
    ->  primary key (`f`)
    -> ) engine=innodb default charset=latin1 ;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> -- we insert some random data
mysql> insert into t(a,b,c) values (uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid());
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t(a,b,c) select concat(rand(),uuid()),concat(rand(),uuid()),concat(rand(),uuid()) from t a,t b,t c,t d,t e,t f,t g,t h,t i,t j;
Query OK, 9765625 rows affected, 65535 warnings (4 min 12.36 sec)
Records: 9765625  Duplicates: 0  Warnings: 9764876

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.07 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 9638852
 Avg_row_length: 215
    Data_length: 2081423360
Max_data_length: 0
   Index_length: 0
      Data_free: 5242880
 Auto_increment: 9830256
    Create_time: 2018-01-09 13:41:19
    Update_time: 2018-01-09 13:53:06
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  9765630 |
+----------+
1 row in set (2.42 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | data_length | index_length |
+--------------------------+-------------+--------------+
|               2081423360 |  2081423360 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> -- now we create some fragmentation by updating and deleting rows.
mysql> update t set c = repeat('a',floor(100*rand())) where b like '0.1%';
Query OK, 975722 rows affected (40.49 sec)
Rows matched: 975722  Changed: 975722  Warnings: 0

mysql> update t set c = repeat('b',floor(500*rand())) where b like '0.5%';
Query OK, 976602 rows affected (4 min 7.30 sec)
Rows matched: 976602  Changed: 976602  Warnings: 0

mysql> delete from t where b like '0.7%';
Query OK, 975635 rows affected (26.86 sec)

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 9762250
 Avg_row_length: 414
    Data_length: 4050632704
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 9830256
    Create_time: 2018-01-09 13:41:19
    Update_time: 2018-01-09 13:58:23
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8789995 |
+----------+
1 row in set (2.72 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | data_length | index_length |
+--------------------------+-------------+--------------+
|               4050632704 |  4050632704 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> -- now we rebuild the table in an attempt to defragment (I).
mysql> alter table t engine=innodb, algorithm=inplace, lock=none;
Query OK, 0 rows affected (10 min 13.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.11 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8588647
 Avg_row_length: 277
    Data_length: 2383396864
Max_data_length: 0
   Index_length: 0
      Data_free: 5242880
 Auto_increment: 9830256
    Create_time: 2018-01-09 13:58:26
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8789995 |
+----------+
1 row in set (1.89 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | data_length | index_length |
+--------------------------+-------------+--------------+
|               2383396864 |  2383396864 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> -- now we rebuild the table in an attempt to defragment (II).
mysql> alter table t engine=innodb, algorithm=copy, lock=shared;
Query OK, 8789995 rows affected (2 min 9.64 sec)
Records: 8789995  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.03 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8601352
 Avg_row_length: 242
    Data_length: 2084569088
Max_data_length: 0
   Index_length: 0
      Data_free: 6291456
 Auto_increment: 9830256
    Create_time: 2018-01-09 13:58:26
    Update_time: 2018-01-09 14:10:51
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8789995 |
+----------+
1 row in set (1.79 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | data_length | index_length |
+--------------------------+-------------+--------------+
|               2084569088 |  2084569088 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql>