2024/08/27

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

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

2024/08/21

CREATE DATABASE/CREATE TABLEでCHARSETを指定してCOLLATEを指定しない

TL;DR

@mita2さんのブログを見て、そういえばこんなのMySQL Server側にもあったなと思ったので書いてみた
- MySQL go-sql-driver/mysql ドライバで意図せず utf8mb4_0900_ai_ci が使われるケース - mita2 database life
CREATE {DATABASE|TABLE} で CHARSET だけ指定して COLLATE を明示しないと collation_server ではなくて default_collation_for_utf8mb4 の方が使われる

CREATE DATABASE の時にCHARSETやCOLLATEを明示したりしなかったりする場合(指定するCOLLATEとCHARSETはちゃんと対応している前提で)、

CHARSET指定	COLLATE指定	実際に使われるCOLLATE
していない	していない	(その時点の)collation_server
していない	している	COLLATEで指定した値
している	していない	(その時点の)default_collation_for_utf8mb4
している	している	COLLATEで指定した値

同じく CREATE TABLE の場合は

CHARSET指定	COLLATE指定	実際に使われるCOLLATE
していない	していない	(その時点の)スキーマのCOLLATE
していない	している	COLLATEで指定した値
している	していない	(その時点の)default_collation_for_utf8mb4
している	している	COLLATEで指定した値

【2024/08/22 09:49】更にカラムは

CHARSET指定	COLLATE指定	実際に使われるCOLLATE
していない	していない	(その時点の)テーブルののCOLLATE
していない	している	COLLATEで指定した値
している	していない	(その時点の)default_collation_for_utf8mb4
している	している	COLLATEで指定した値

というわけで、「そもそもCOLLATEまで完全指定した方が間違いがない」「どうしても省略したいならスキーマのCOLLATEに注意する」「少なくともCHARSETだけ書いてCOLLATEだけ省略すると意図しないことになる可能性がある」のでした。

mysql80 17> SELECT @@session.default_collation_for_utf8mb4, @@session.character_set_server, @@session.collation_server, @@session.collation_connection;
+-----------------------------------------+--------------------------------+----------------------------+--------------------------------+
| @@session.default_collation_for_utf8mb4 | @@session.character_set_server | @@session.collation_server | @@session.collation_connection |
+-----------------------------------------+--------------------------------+----------------------------+--------------------------------+
| utf8mb4_0900_ai_ci                      | utf8mb4                        | utf8mb4_general_ci         | latin1_swedish_ci              |
+-----------------------------------------+--------------------------------+----------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql80 17> CREATE DATABASE no_specified;
Query OK, 1 row affected (0.01 sec)

mysql80 17> SHOW CREATE DATABASE no_specified;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
| Database     | Create Database                                                                                                                        |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
| no_specified | CREATE DATABASE `no_specified` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 17> CREATE DATABASE only_charset_specified CHARSET utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql80 17> SHOW CREATE DATABASE only_charset_specified;
+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Database               | Create Database                                                                                                                                  |
+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| only_charset_specified | CREATE DATABASE `only_charset_specified` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 17> CREATE DATABASE fully_specified CHARSET utf8mb4 COLLATE utf8mb4_0900_bin;
Query OK, 1 row affected (0.01 sec)

mysql80 17> SHOW CREATE DATABASE fully_specified;
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Database        | Create Database                                                                                                                         |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| fully_specified | CREATE DATABASE `fully_specified` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 17> use fully_specified
Database changed

mysql80 17> CREATE TABLE no_specified_table_and_column (val varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql80 17> SHOW CREATE TABLE no_specified_table_and_column\G
*************************** 1. row ***************************
       Table: no_specified_table_and_column
Create Table: CREATE TABLE `no_specified_table_and_column` (
  `val` varchar(32) COLLATE utf8mb4_0900_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
1 row in set (0.00 sec)

mysql80 17> CREATE TABLE specified_table_and_no_specified_column (val varchar(32)) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)

mysql80 17> SHOW CREATE TABLE specified_table_and_no_specified_column\G
*************************** 1. row ***************************
       Table: specified_table_and_no_specified_column
Create Table: CREATE TABLE `specified_table_and_no_specified_column` (
  `val` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql80 17> CREATE TABLE specified_only_charset_table_and_no_specified_column (val varchar(32)) CHARSET utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql80 17> SHOW CREATE TABLE specified_only_charset_table_and_no_specified_column\G
*************************** 1. row ***************************
       Table: specified_only_charset_table_and_no_specified_column
Create Table: CREATE TABLE `specified_only_charset_table_and_no_specified_column` (
  `val` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 17> CREATE TABLE specified_table_and_specified_column (val varchar(32) CHARSET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)

mysql80 17> SHOW CREATE TABLE specified_table_and_specified_column\G
*************************** 1. row ***************************
       Table: specified_table_and_specified_column
Create Table: CREATE TABLE `specified_table_and_specified_column` (
  `val` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql80 17> CREATE TABLE specified_table_and_specified_only_charset_column (val varchar(32) CHARSET utf8mb4) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)

mysql80 17> SHOW CREATE TABLE specified_table_and_specified_only_charset_column\G
*************************** 1. row ***************************
       Table: specified_table_and_specified_only_charset_column
Create Table: CREATE TABLE `specified_table_and_specified_only_charset_column` (
  `val` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql80 17> CREATE TABLE specified_only_charset_table_and_specified_only_charset_column (val varchar(32) CHARSET utf8mb4) CHARSET utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql80 17> SHOW CREATE TABLE specified_only_charset_table_and_specified_only_charset_column\G
*************************** 1. row ***************************
       Table: specified_only_charset_table_and_specified_only_charset_column
Create Table: CREATE TABLE `specified_only_charset_table_and_specified_only_charset_column` (
  `val` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

登録: 投稿 ( Atom )

日々の覚書

GA

2024/08/27

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

TL;DR

2024/08/21

CREATE DATABASE/CREATE TABLEでCHARSETを指定してCOLLATEを指定しない

TL;DR