TL;DR

ステートメントの権限評価が違う。 ALTER TABLE は GRANT ALTER が必要だが、 OPTIMIZE TABLE は GRANT SELECT, INSERT だけで成立する
- https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html
innodb_optimize_fulltext_only をONにしていない限りはどっちでもほぼ同じことが起こる
- ONにしている場合は違うことが起こる。後述。
OPTIMIZE TABLE には OPTIMIZE LOCAL TABLE または OPTIMIZE NO_WRITE_TO_BINLOG TABLE 構文があり、これは ALTER TABLE には存在しない機能
- 普段から OPTIMIZE LOCAL TABLE を使っている人にだけ伝われば。初めて聞いた人にはきっと関係ないので忘れて良い

同じことが起こる仕組み。

OPTIMIZE TABLE は Sql_cmd_optimize_table::execute から mysql_admin_table を通って table->table->file->*operator_func つまり ha_innobase::optimize に到達する。

ha_innobase::optimize は innodb_optimize_fulltext_only がセットされているかを判定して、ONの場合は fts_sync_table と fts_optimize_table を呼び出す。OFFの場合は何もせずに HA_ADMIN_TRY_ALTER を返す。

https://github.com/mysql/mysql-server/blob/mysql-8.4.2/storage/innobase/handler/ha_innodb.cc#L18316-L18343

返された HA_ADMIN_TRY_ALTER は mysql_admin_table でハンドルされて、おなじみの Table does not support optimize, doing recreate + analyze instead のメッセージを書き出しながら mysql_recreate_table に突入する。 mysql_recreate_table は mysql_alter_table を呼び出す。

それに対して ALTER TABLE は Sql_cmd_alter_table::execute から直接(?) mysql_alter_table に突入する。

https://github.com/mysql/mysql-server/blob/mysql-8.4.2/sql/sql_alter.cc#L219

どちらも最終的には(ALGORITHM= COPYとかしていない限り) ha_innobase::inplace_alter_table にたどり着く。

OPTIMIZE TABLE から入るとこう。

(gdb) bt
+bt
#0  ha_innobase::inplace_alter_table(TABLE*, Alter_inplace_info*, dd::Table const*, dd::Table*) () at /home/yoku0825/mysql-8.4.2/storage/innobase/handler/handler0alter.cc:1560
#1  0x0000000000f675c2 in ha_inplace_alter_table (new_table_def=0x7f9b80c2e420, old_table_def=0x7f9b80052f50, ha_alter_info=0x7f9bc0686800, altered_table=0x7f9b80dabee0, this=<optimized out>)
    at /home/yoku0825/mysql-8.4.2/sql/sql_table.cc:13740
#2  mysql_inplace_alter_table(THD*, dd::Schema const&, dd::Schema const&, dd::Table const*, dd::Table*, Table_ref*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, Alter_table_ctx*, std::set<std::string, std::less<std::string>, Stateless_allocator<std::string, histograms::Histogram_psi_key_alloc, My_free_functor> >&, FOREIGN_KEY*, unsigned int, Foreign_key_parents_invalidator*) ()
    at /home/yoku0825/mysql-8.4.2/sql/sql_table.cc:13801
#3  0x0000000000f7e07f in mysql_alter_table(THD*, char const*, char const*, HA_CREATE_INFO*, Table_ref*, Alter_info*) () at /home/yoku0825/mysql-8.4.2/sql/sql_table.cc:17675
#4  0x0000000000f83158 in mysql_recreate_table(THD*, Table_ref*, bool) () at /home/yoku0825/mysql-8.4.2/sql/sql_table.cc:18915
#5  0x00000000013df51a in mysql_admin_table(THD*, Table_ref*, HA_CHECK_OPT*, char const*, thr_lock_type, bool, bool, unsigned int, int (*)(THD*, Table_ref*, HA_CHECK_OPT*), int (handler::*)(THD*, HA_CHECK_OPT*), int, Alter_info*, bool) [clone .constprop.0] () at /home/yoku0825/mysql-8.4.2/sql/sql_admin.cc:1327
#6  0x00000000013e0ab2 in Sql_cmd_optimize_table::execute (this=0x7f9b80dae8e8, thd=0x7f9b80006bd0) at /home/yoku0825/mysql-8.4.2/sql/sql_admin.cc:1933
#7  0x0000000000ec5c01 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.4.2/sql/sql_parse.cc:4737
#8  0x0000000000ec9cd0 in dispatch_sql_command(THD*, Parser_state*) () at /home/yoku0825/mysql-8.4.2/sql/sql_parse.cc:5392
#9  0x0000000000ecc741 in dispatch_command(THD*, COM_DATA const*, enum_server_command) () at /home/yoku0825/mysql-8.4.2/sql/sql_parse.cc:2136
#10 0x0000000000ecd356 in do_command (thd=thd@entry=0x7f9b80006bd0) at /home/yoku0825/mysql-8.4.2/sql/sql_parse.cc:1465
#11 0x00000000010326d8 in handle_connection (arg=arg@entry=0x83a1150) at /home/yoku0825/mysql-8.4.2/sql/conn_handler/connection_handler_per_thread.cc:304
#12 0x00000000028ae595 in pfs_spawn_thread (arg=0x84e94a0) at /home/yoku0825/mysql-8.4.2/storage/perfschema/pfs.cc:3061
#13 0x00007f9bcf03aea5 in start_thread (arg=0x7f9bc068c700) at pthread_create.c:307
#14 0x00007f9bcd4e8b0d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

ALTER TABLE から入るとこう。

(gdb) bt
+bt
#0  ha_innobase::inplace_alter_table(TABLE*, Alter_inplace_info*, dd::Table const*, dd::Table*) () at /home/yoku0825/mysql-8.4.2/storage/innobase/handler/handler0alter.cc:1560
#1  0x0000000000f675c2 in ha_inplace_alter_table (new_table_def=0x7f9b80cf5b50, old_table_def=0x7f9b80052f50, ha_alter_info=0x7f9bc0686de0, altered_table=0x7f9b80e290e0, this=<optimized out>)
    at /home/yoku0825/mysql-8.4.2/sql/sql_table.cc:13740
#2  mysql_inplace_alter_table(THD*, dd::Schema const&, dd::Schema const&, dd::Table const*, dd::Table*, Table_ref*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, Alter_table_ctx*, std::set<std::string, std::less<std::string>, Stateless_allocator<std::string, histograms::Histogram_psi_key_alloc, My_free_functor> >&, FOREIGN_KEY*, unsigned int, Foreign_key_parents_invalidator*) ()
    at /home/yoku0825/mysql-8.4.2/sql/sql_table.cc:13801
#3  0x0000000000f7e07f in mysql_alter_table(THD*, char const*, char const*, HA_CREATE_INFO*, Table_ref*, Alter_info*) () at /home/yoku0825/mysql-8.4.2/sql/sql_table.cc:17675
#4  0x00000000013e5619 in Sql_cmd_alter_table::execute(THD*) () at /home/yoku0825/mysql-8.4.2/sql/sql_alter.cc:350
#5  0x0000000000ec5c01 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.4.2/sql/sql_parse.cc:4737
#6  0x0000000000ec9cd0 in dispatch_sql_command(THD*, Parser_state*) () at /home/yoku0825/mysql-8.4.2/sql/sql_parse.cc:5392
#7  0x0000000000ecc741 in dispatch_command(THD*, COM_DATA const*, enum_server_command) () at /home/yoku0825/mysql-8.4.2/sql/sql_parse.cc:2136
#8  0x0000000000ecd356 in do_command (thd=thd@entry=0x7f9b80006bd0) at /home/yoku0825/mysql-8.4.2/sql/sql_parse.cc:1465
#9  0x00000000010326d8 in handle_connection (arg=arg@entry=0x83a1150) at /home/yoku0825/mysql-8.4.2/sql/conn_handler/connection_handler_per_thread.cc:304
#10 0x00000000028ae595 in pfs_spawn_thread (arg=0x84e94a0) at /home/yoku0825/mysql-8.4.2/storage/perfschema/pfs.cc:3061
#11 0x00007f9bcf03aea5 in start_thread (arg=0x7f9bc068c700) at pthread_create.c:307
#12 0x00007f9bcd4e8b0d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

という訳で、

ステートメントの権限評価が違う。 ALTER TABLE は GRANT ALTER が必要だが、 OPTIMIZE TABLE は GRANT SELECT, INSERT だけで成立する
innodb_optimize_fulltext_only をONにしていない限りはどっちでもほぼ同じことが起こる
OPTIMIZE TABLE には OPTIMIZE LOCAL TABLE または OPTIMIZE NO_WRITE_TO_BINLOG TABLE 構文があり、これは ALTER TABLE には存在しない機能

この3つが気にならなければどっちを使っても一緒です。

【2024/09/02 13:00】

すっかり忘れてたんですけど OPTIMIZE TABLE から入った場合は ANALYZE TABLE もトリガーされました。

https://github.com/mysql/mysql-server/blob/mysql-8.4.2/sql/sql_admin.cc#L1367

taka_yukiさんありがとうございます！

（ANALYZE TABLE は割と勝手にバックグラウンドでトリガーされるのでやはりそこまで違いを気にしたことはないです）

ざっくりイメージだと
OPTIMIZE TABLE=
ALTER TABLE .. Engine = InnoDBと+ANALYZE TABLE
なので、
ALTER TABLE .. Engine = InnoDBの部分の作業やるのに二つの違いは？って話ですね。
— tkyk04 (@taka_yuki_04) September 2, 2024

日々の覚書

GA

2024/08/27

ALTER TABLE .. Engine = InnoDBとOPTIMIZE TABLEは結局どう違うのか

TL;DR

0 件のコメント :

コメントを投稿