MariaDB InnoDB表执行OPTIMIZE TABLE的三类技术疑问

架构老林

2026-8-22

MariaDB 10.6.7 中 OPTIMIZE TABLE 的相关问题

背景

我阅读了OPTIMIZE TABLE的相关文章，但仍有疑问。针对MariaDB v10.6.7运行mysqltuner.pl工具后，得到如下优化建议：

Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `DB`.`TableA`; -- can free 426 MB
    Total freed space after theses OPTIMIZE TABLE : 426 Mb

技术疑问

对InnoDB表执行OPTIMIZE TABLE TableA``是否可行，能否提升性能？我知道该操作会清理磁盘未使用空间，但不确定对性能的实际帮助。
执行OPTIMIZE TABLE时，InnoDB表提示Table does not support optimize, doing recreate + analyze instead，是否需要改用ALTER TABLE ... OPTIMIZE替代？我猜测二者有关联。
执行OPTIMIZE TABLE后，原本426MB的可释放空间仅减少至384MB，无法完全释放，原因是什么？

执行的SQL及结果

> select * from information_schema.TABLES where TABLE_NAME = "TableA"\G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: DB
     TABLE_NAME: TableA
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 1600474
 AVG_ROW_LENGTH: 207
    DATA_LENGTH: 332136448
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 446693376 (426MB)
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2022-08-09 16:01:05
    UPDATE_TIME: 2022-08-09 16:04:47
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: partitioned
  TABLE_COMMENT:
1 row in set (0.01 sec)

ERROR: No query specified


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

127.0.0.1:3307> select * from information_schema.TABLES where TABLE_NAME = "TableA"\G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: DB
     TABLE_NAME: TableA
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 1600474
 AVG_ROW_LENGTH: 193
    DATA_LENGTH: 310116352
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 402653184 (384MB)
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2022-08-09 16:47:00
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: partitioned
  TABLE_COMMENT:
1 row in set (0.27 sec)

补充信息

mysqltuner.pl 空闲空间查询逻辑

SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),cast(DATA_FREE as signed) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND DATA_LENGTH/1024/1024>100 AND cast(DATA_FREE as signed)*100/(DATA_LENGTH+INDEX_LENGTH+cast(DATA_FREE as signed)) > 10 AND NOT ENGINE='MEMORY' $not_innodb

表状态与结构

> SHOW TABLE STATUS WHERE name LIKE "TableA"\G;
*************************** 1. row ***************************
            Name: TableA
          Engine: InnoDB
         Version: 10
      Row_format: Dynamic
            Rows: 1875385
  Avg_row_length: 3
     Data_length: 5685248
 Max_data_length: 0
    Index_length: 0
       Data_free: 1991245824
  Auto_increment: NULL
     Create_time: 2022-10-25 10:53:40
     Update_time: 2022-10-25 11:34:32
      Check_time: NULL
       Collation: utf8mb3_general_ci
        Checksum: NULL
  Create_options: partitioned
         Comment:
Max_index_length: 0
       Temporary: N
1 row in set (0.002 sec)

> Show create table TableA;

| TableA | CREATE TABLE `TableA` (
  `Col1` mediumint(8) unsigned NOT NULL,
  `Col2` tinyint(4) NOT NULL,
  `Col3` tinyint(4) NOT NULL,
  `Col4` tinyint(4) NOT NULL,
  `Col5` tinyint(4) NOT NULL,
  `Col6` smallint(4) NOT NULL,
  `timestamp` int(11) NOT NULL,
  `Col7` bigint(20) DEFAULT NULL,
  `Col8` bigint(20) DEFAULT NULL,
  `Col9` tinyint(4) DEFAULT NULL,
  :::
  `Col40` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`Col1` ,`Col2` ,`Col3` ,`Col4` ,`Col5` ,`Col6`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
 PARTITION BY RANGE (`timestamp`)
(PARTITION `p2022_10_11_02_00_00` VALUES LESS THAN (1665437400) ENGINE = InnoDB,
 PARTITION `p2022_10_11_03_00_00` VALUES LESS THAN (1665441000) ENGINE = InnoDB,
 PARTITION `p2022_10_11_04_00_00` VALUES LESS THAN (1665444600) ENGINE = InnoDB,
 ....
 PARTITION `p2022_10_25_12_00_00` VALUES LESS THAN (1666683000) ENGINE = InnoDB)
 //partitioned by timestamp. Partitioned more than 360

解答

1. InnoDB表执行OPTIMIZE TABLE是否可行，能否提升性能？

可行，但性能提升场景有限：

InnoDB不支持原生的OPTIMIZE TABLE碎片整理，实际会执行ALTER TABLE ... ENGINE=InnoDB（重建表）+ANALYZE TABLE的组合操作。
性能提升仅在表碎片率极高（比如大量删除/更新操作后，数据页碎片化严重）时才会显现：减少磁盘IO次数，提升数据扫描效率；如果表的读写模式是常规的插入/少量更新，几乎不会有性能变化。
注意：重建表会锁表（MariaDB 10.3+支持Online DDL，可减少锁表时间），且会占用额外磁盘空间，需在业务低峰期执行。

2. 是否需要改用ALTER TABLE ... OPTIMIZE替代OPTIMIZE TABLE？

不需要，二者本质是等价的：

当对InnoDB表执行OPTIMIZE TABLE时，系统自动触发的recreate + analyze，就是执行了ALTER TABLE TableA ENGINE=InnoDB; ANALYZE TABLE TableA;的逻辑。
ALTER TABLE ... OPTIMIZE是部分存储引擎的语法，InnoDB中并不会有特殊处理，最终还是会走重建表的流程。直接使用OPTIMIZE TABLE即可，无需替换。

3. 为什么无法完全释放426MB空闲空间？

核心原因是该表是分区表，每个分区会保留一定的预留空间：

InnoDB的每个分区作为独立的表空间，重建时会为每个分区分配默认的预留空间（通常是每个分区10MB左右，你有360+个分区，总预留空间刚好接近42MB的差值）。
DATA_FREE字段统计的是表空间中未被使用的页，分区表的DATA_FREE是所有分区空闲空间的总和，重建后每个分区的预留空间会被计入其中，所以无法完全清零。
另外，若执行OPTIMIZE期间有新的写入操作，也会占用部分空闲空间，但从你的数据看，主要原因还是分区的预留空间。

关于mysqltuner.pl空闲空间查询逻辑的说明

该查询的作用是筛选出需要优化的大表：

排除系统库（information_schema、performance_schema、mysql）和MEMORY引擎表。
只处理数据量超过100MB的表。
筛选空闲空间占总空间（数据+索引+空闲）比例超过10%的表，认为这类表有碎片整理的价值。
$not_innodb变量应该是用来控制是否包含InnoDB表，不过从你的场景看，脚本还是把InnoDB表纳入了建议范围。

内容的提问来源于stack exchange，提问作者ragul rangarajan

MariaDB InnoDB表执行OPTIMIZE TABLE的三类技术疑问

背景 #

技术疑问 #

执行的SQL及结果 #

补充信息 #

mysqltuner.pl 空闲空间查询逻辑 #

表状态与结构 #

解答 #

1. InnoDB表执行OPTIMIZE TABLE是否可行，能否提升性能？ #

2. 是否需要改用ALTER TABLE ... OPTIMIZE替代OPTIMIZE TABLE？ #

3. 为什么无法完全释放426MB空闲空间？ #

关于mysqltuner.pl空闲空间查询逻辑的说明 #

背景