OPTIMIZE TABLE 

OPTIMIZE TABLEを実行する代わりに、以下の様にALTER TABLEを実行するケースも多いと思います。
バックグラウンドでどの様に処理しているかを聞かれる事も多いのでこちらにメモしておきます。

alter table sbtest6 engine=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

OPTIMIZE TABLE中の一時テーブル処理

# stat city_sample1.ibd
  File: ‘city_sample1.ibd’
  Size: 5272240128      Blocks: 10297376   IO Block: 4096   regular file
Device: 10301h/66305d   Inode: 96469157    Links: 1
Access: (0640/-rw-r-----)  Uid: (  995/   mysql)   Gid: ( 1001/   mysql)
Access: 2023-04-21 22:33:34.264902911 +0000
Modify: 2023-04-21 23:27:00.503985634 +0000
Change: 2023-04-21 23:27:00.503985634 +0000
 Birth: -
 
# /usr/local/mysql/bin/mysql -u root -p -e "optimize table shell.city_sample1"
Enter password:
+--------------------+----------+----------+-------------------------------------------------------------------+
| Table              | Op       | Msg_type | Msg_text                                                          |
+--------------------+----------+----------+-------------------------------------------------------------------+
| shell.city_sample1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| shell.city_sample1 | optimize | status   | OK                                                                |
+--------------------+----------+----------+-------------------------------------------------------------------+

# stat city_sample1.ibd
  File: ‘city_sample1.ibd’
  Size: 5553258496      Blocks: 10846224   IO Block: 4096   regular file
Device: 10301h/66305d   Inode: 96469156    Links: 1
Access: (0640/-rw-r-----)  Uid: (  995/   mysql)   Gid: ( 1001/   mysql)
Access: 2023-06-02 04:38:23.444725102 +0000
Modify: 2023-06-02 04:48:50.106803831 +0000
Change: 2023-06-02 04:48:50.106803831 +0000
 Birth: -
#

OPTIMIZE TABLEを実行中はバックグラウンドで一時的にファイルが作成されて最終的に入れ替えられている事が分かります。

但し、実行前と実行後を比較すると、フラグメンテーションが解消されるのにファイルサイズは微妙に大きくなっているます。

3.7.3.4 OPTIMIZE TABLE ステートメント

実行開始直後

# ls -lh
total 11G
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:27 city_sample1.ibd
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:25 city_sample1_old.ibd
-rw-r----- 1 mysql mysql 276M Jun  2 04:36 city_sample.ibd
-rw-r----- 1 mysql mysql 588M Jun  2 04:38 #sql-ib1304-1434569684.ibd
#

実行完了

# ls -lh
total 16G
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:27 city_sample1.ibd
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:25 city_sample1_old.ibd
-rw-r----- 1 mysql mysql 276M Jun  2 04:36 city_sample.ibd
-rw-r----- 1 mysql mysql 5.2G Jun  2 04:48 #sql-ib1304-1434569684.ibd
# ls -lh
total 11G
-rw-r----- 1 mysql mysql 5.2G Jun  2 04:48 city_sample1.ibd
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:25 city_sample1_old.ibd
-rw-r----- 1 mysql mysql 276M Jun  2 04:36 city_sample.ibd

OPTIMIZE TABLE では、通常のパーティション化された InnoDB テーブルに online DDL が使用されるため、同時 DML 操作の停止時間が短縮されます。 OPTIMIZE TABLE によってトリガーされたテーブルの再構築が適切に完了します。 排他テーブルロックは、操作の準備フェーズおよびコミットフェーズでのみ短時間実行されます。 準備フェーズでは、メタデータが更新され、中間テーブルが作成されます。 コミットフェーズでは、テーブルメタデータの変更がコミットされます。

3.7.3.4 OPTIMIZE TABLE ステートメント

ALTER TABLEのケース

ALTER TABLE中の一時テーブル処理 (ALGORITHM=INPLACE)

# stat city_sample1.ibd
  File: ‘city_sample1.ibd’
  Size: 5553258496      Blocks: 10846224   IO Block: 4096   regular file
Device: 10301h/66305d   Inode: 96469156    Links: 1
Access: (0640/-rw-r-----)  Uid: (  995/   mysql)   Gid: ( 1001/   mysql)
Access: 2023-06-02 04:38:23.444725102 +0000
Modify: 2023-06-02 04:48:50.106803831 +0000
Change: 2023-06-02 04:48:50.106803831 +0000
 Birth: -
# /usr/local/mysql/bin/mysql -u root -p -e "alter table shell.city_sample1 engine=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;"
Enter password:
[root@ip-172-31-102-178 shell]# stat city_sample1.ibd
  File: ‘city_sample1.ibd’
  Size: 5553258496      Blocks: 10846240   IO Block: 4096   regular file
Device: 10301h/66305d   Inode: 100658134   Links: 1
Access: (0640/-rw-r-----)  Uid: (  995/   mysql)   Gid: ( 1001/   mysql)
Access: 2023-06-02 05:45:32.681502558 +0000
Modify: 2023-06-02 05:56:00.567554890 +0000
Change: 2023-06-02 05:56:00.567554890 +0000
 Birth: -
#

実行開始直後

# ls -lh
total 11G
-rw-r----- 1 mysql mysql 5.2G Jun  2 04:48 city_sample1.ibd
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:25 city_sample1_old.ibd
-rw-r----- 1 mysql mysql 276M Jun  2 05:40 city_sample.ibd
#

実行完了

# ls -lh
total 14G
-rw-r----- 1 mysql mysql 5.2G Jun  2 04:48 city_sample1.ibd
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:25 city_sample1_old.ibd
-rw-r----- 1 mysql mysql 276M Jun  2 05:40 city_sample.ibd
-rw-r----- 1 mysql mysql 3.6G Jun  2 05:51 #sql-ib1365-1434569688.ibd
# ls -lh
total 11G
-rw-r----- 1 mysql mysql 5.2G Jun  2 05:56 city_sample1.ibd
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:25 city_sample1_old.ibd
-rw-r----- 1 mysql mysql 276M Jun  2 05:40 city_sample.ibd
#

ALTER TABLE中の一時テーブル処理 (ALGORITHM=COPY)

# ls -lh
total 11G
-rw-r----- 1 mysql mysql 5.2G Jun  2 05:56 city_sample1.ibd
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:25 city_sample1_old.ibd
-rw-r----- 1 mysql mysql 276M Jun  2 05:40 city_sample.ibd

# /usr/local/mysql/bin/mysql -u root -p -e "alter table shell.city_sample1 engine=InnoDB, ALGORITHM=COPY;"
Enter password:

# ls -lh
total 11G
-rw-r----- 1 mysql mysql 5.0G Jun  7 10:03 city_sample1.ibd
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:25 city_sample1_old.ibd
-rw-r----- 1 mysql mysql 276M Jun  2 05:40 city_sample.ibd

実行開始直後

# ls -lh
total 11G
-rw-r----- 1 mysql mysql 5.2G Jun  2 05:56 city_sample1.ibd
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:25 city_sample1_old.ibd
-rw-r----- 1 mysql mysql 276M Jun  2 05:40 city_sample.ibd
-rw-r----- 1 mysql mysql  48M Jun  7 09:55 #sql-6822_1d.ibd

実行完了

# ls -lh
total 11G
-rw-r----- 1 mysql mysql 5.0G Jun 7 10:03 city_sample1.ibd
-rw-r----- 1 mysql mysql 5.0G Apr 21 23:25 city_sample1_old.ibd
-rw-r----- 1 mysql mysql 276M Jun 2 05:40 city_sample.ibd

OptimizeされてテーブルがきれいになりOPTIMIZEやINPLACEよりもサイズが小さくなった事を確認。
PKを含むテーブルのINPLACE処理には一定の制限があるからなのかもしれない。別途深堀しておく。

どちらも、バックグラウドで一時テーブルを作成するのでディスク容量にはご注意下さい。

——————————————-

MySQLのバージョンによる動作の違い

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0035

こんな方法もありますね。実際に実測はした事ないですが参考になりそうです。

Using MySQL OPTIMIZE tables for InnoDB? Stop!