MySQL 8.0.32之optimize table

MySQL版本：8.0.32

最新使用到了optimize table ，不清楚是做什么用的，因此在8.0.32版本上测试了下该SQL语句。

该语句的功能是：重新组织表数据和相关索引数据的物理存储，整理碎片，减少存储空间。

关于表碎片，先看下MySQL中存储表数据时的方式，参考：MySQL表碎片是如何产生的_一缕阳光a的博客-CSDN博客

optimize table语句的功能类似于PostgreSQL中的auto vaccum。

先用一个普通表做下测试：

mysql> drop table if exists t11;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> create table t11(i int primary key, j varchar(200));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t11 values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create index idx1 on t11(j);

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from t11;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into t11 values(3,'c'),(4,'d');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select count(*) from t11;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
mysql> show table status like 't11';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| t11  | InnoDB |      10 | Dynamic    |    2 |           8192 |       16384 |               0 |            0 |         0 |           NULL | 2023-04-19 15:44:32 | 2023-04-19 15:44:32 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

从该表的统计信息中可以看到Rows为2，接下来执行 optimize table t11;

mysql> optimize table t11;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.t11 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t11 | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.12 sec)

mysql> select count(*) from t11;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> show table status like 't11'\G;
*************************** 1. row ***************************
           Name: t11
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2023-04-19 16:11:19
    Update_time: 2023-04-19 15:44:32
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

执行 optimize table之后，查看表的统计信息显示还是ROW还是2，Index_length也是0，因此统计信息并没有修改。

进行gdb调试源码时，发现在 执行optimize table时没有 执行更新 table_stats 和 index_stats。是否更新统计信息，是由下面的ignore_grl_on_analyze变量决定的：

bool ignore_grl_on_analyze = operator_func == &handler::ha_analyze;

因此只有在执行 analyze table table_name;之后才会更新统计信息。SQL语句执行如下：

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

mysql> select count(*) from t11;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> show table status like 't11'\G;
*************************** 1. row ***************************
           Name: t11
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2023-04-19 16:11:19
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

从结果可以看到，Rows: 4，Index_length: 16384，统计信息已经更到最新了。

堆栈信息如下：

mysql_execute_command
|> Sql_cmd_optimize_table::execute
||> mysql_admin_table
|||> handler::ha_optimize
||||> ha_innobase::optimize
|||> mysql_recreate_table
|||> handler::ha_analyze
||||> ha_innobase::analyze

在执行 optimize table table_name; 语句时，optimize table 执行的是 recreate (mysql_recreate_table(...)) + analyze (handler::ha_analyze)，不会阻塞其他的SQL的，并且该语句是online ddl。