小白学习MySQL - InnoDB支持optimize table？

bisal的个人杂货铺 2021-08-17

2097

MySQL数据库中进行表空间整理，可以用的一种操作就是optimize table，

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

P.S. 参考《小白学习MySQL - 表空间碎片整理方法》。

optimize table会重组表数据和索引的物理存储，减少对存储空间使用和提升访问表时io效率。optimize table后，表的变化和存储引擎也有关。

对于MyISAM表，optimize table操作执行以下工作：
1.如果表含有删除的列、分列的列，optimize table会修复表。
2.如果索引页没有排序，optimize table会将索引页进行排序。
3.如果表的统计信息不是最新的，optimize table会更新索引信息。

对InnoDB表执行optimize table操作的时候，会报"Table does not support optimize, doing recreate + analyze instead"提示，有种观点认为optimize table不支持innodb表，其实这就看怎么理解了。

的确，官方文档提到了，对于InnoDB的表，不支持optimize table，

OPTIMIZE TABLE using online DDL is not supported for InnoDB tables that contain FULLTEXT indexes. The table copy method is used instead.

我们做个实验，t_per表存储引擎是InnoDB，

mysql> show table status like 't_per'\G
*************************** 1. row ***************************
           Name: t_per
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 998631
 Avg_row_length: 29
    Data_length: 29949952
Max_data_length: 0
   Index_length: 0
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2021-08-01 09:02:10
    Update_time: 2021-08-01 11:31:00
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

包含了100万条数据，

mysql> select count(*) from t_per;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.32 sec)

存储空间占了40M，

-rw-r-----. 1 mysql mysql  40M Aug  1 11:31 t_per.ibd

我们删除2/3的数据，

mysql> delete from t_per where id%3<>0;

表的状态信息，

mysql> show table status like 't_per'\G
*************************** 1. row ***************************
           Name: t_per
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 332690
 Avg_row_length: 39
    Data_length: 13123584
Max_data_length: 0
   Index_length: 0
      Data_free: 1049576
 Auto_increment: NULL
    Create_time: 2021-08-01 13:53:10
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

我们知道，如果不整理表，删除的空间，是无法释放的，我们对这张表执行指令optimize table，提示如下信息，

mysql> optimize table t_per;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
|bisal.t_per | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| bisal.t_per | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+

看下磁盘空间，此时数据文件，已经是17M，说明空间整理生效了，

-rw-r-----. 1 mysql mysql  17M Aug  1 13:53 t_per.ibd

因此，从严格的意义讲，说InnoDB不支持optimize table，其实不太准确，如官方文档所说，InnoDB引擎的表，optimize table会自动转成alter table ... force，相当于做了recreate和analyze，

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE … FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table, as shown here:

因此，对InnoDB，optimize table封装了其他操作，但对执行者来说，optimize table是执行成功的，因为他做了重建表和更新索引统计信息并释放空间的操作。

参考，

https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

https://www.cnblogs.com/abclife/p/9573578.html

小白学习MySQL，

《小白学习MySQL - table_open_cache的作用》

《小白学习MySQL - 表空间碎片整理方法》

《小白学习MySQL - 大小写敏感问题解惑》

《小白学习MySQL - only_full_group_by的校验规则》

《小白学习MySQL - max_allowed_packet》

《小白学习MySQL - mysqldump保证数据一致性的参数差异》

《小白学习MySQL - 查询会锁表？》