この記事は裏freee developers Advent Calendar 2018の21日目の記事です。

freee株式会社でプロダクト基盤本部本部長をしています浅羽と申します。プロダクト基盤は文字通りプロダクトの基盤を作っており、SRE、分析基盤、アカウントアグリゲーション基盤を作っているチームになっています。コードを書く時間を減らして組織づくりにフォーカスしていますが、とはいえサービスを良くするためには技術も素振りする必要があると思っています。ということで、組織的な話ではなく技術的な話を書こうと思います。

サービスを運営しているとデータベースがだんだん大きくなってきて、RDBMSの性能がスローダウンするような場面があると思います。その引き金としてクエリが遅くなったり、大きめのテーブルに対してADD COLUMNをしてしまうなどがありえそうでしょうか。freeeではRDMBSはMySQLをメインに使っています。ADD COLUMNに関してはMySQLの場合はONLINE DDLも存在していますが、それでも結構気を使います。

2017年にAmazon AuroraでFast DDLなる機能がリリースされ、それとは全然実装が違うと思いますがMySQLでもInstant ADD COLUMNという機能が2018年にリリースされました。

これは文字通りに解釈すれば最高な機能なのですが、長くソフトウェアエンジニアをやっているとこういうgood newsを疑ってしまってなにか罠があるんじゃないか？と思ったので、ちゃんと調べてみようと思いました。ただ、実際にちゃんと調べてみると結構大変でまとまっているのかは怪しいですが、誰かが更に深掘りしてくれると信じて書いたものを出してみます。

Instant ADD COLUMNとは？
Instant ADD COLUMNに関する資料
MySQLのデバッグビルド
- ソースコードのダウンロード
- デバッグビルド
metadataを確認する
- ibd2sdi
- デバッグビルドしているのであればmysql.tables等から参照
gdbで追いかける
実験
まとめ？
宣伝

Instant ADD COLUMNとは？

MySQLのALTER TABLEは5.7ではCOPY方式とINPLACE方式の2つありました。詳細は以下のドキュメントを読んでいただきたいのですが、ざっくり書くとこんな感じです。

MySQL :: MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations

ALGORITHM=INPLACE
- secondary indexに関してはテーブルデータを作り直さないのと、exclusive lockも取らない
- ADD COLUMNについてはexclusive lockを取らないので並列して参照や更新ができるが、裏でテーブルを作り直すので結構重い。
ALGORITHM=COPY
- exclusive lockを取ってデータを作り直すので、その間は対象のテーブルへの読み書きができなくなる

MySQL5.7でALGORITHMを指定せずにADD COLUMNをした場合は大抵の場合はInplaceの挙動になります（詳しくはドキュメントをご覧ください）。ただ、以下の処理が裏で走るため

テーブルを作り直す
secondary indexも作り直す
テーブル作り直し中の更新操作をログに記録
テーブル作り直し中の更新操作を作り直したテーブルへapplyする *COMMITの並列処理が抑えられる可能性があるため、ここの時間帯がおそらく一番重い

ちなみに、↑はMySQL5.6のONLINE DDLの処理を社内qiitaにまとめたときにコードを読んだ仕組みなので、もしかしたら5.7では挙動が変わっているかもしれません。観測している感じだと5.6と5.7でそんなに挙動は変わっていない気もします。

さて、やっと本題になりますが、MySQL 8.0.12からInstant ADD COLUMNという機能が追加されました。ALTER TABLEではALGORITHM=INSTANTです。

MySQL :: MySQL 8.0 Reference Manual :: 13.1.9 ALTER TABLE Syntax

INSTANT: Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous. Concurrent DML is permitted. (Introduced in MySQL 8.0.12)

データディクショナリに格納されているメタデータのみを書き換えて、テーブルデータを全く変更しないモードです。普通に考えて非常に速そうですね。

Instant ADD COLUMNに関する資料

MySQLのデバッグビルド

マニュアル以上の挙動を調べるためにはソースコードを読むしかないと思います。ということで読む環境を整えます。Linuxのほうがデバッグする環境を作るのが楽なので、Linux(Ubuntu 18.04)を前提に書いていきます。

ソースコードのダウンロード

MySQL :: Download MySQL Community Server からソースコードをダウンロードできます。この記事では8.0.13を想定しています。

Select Operating System: Source Code
Select OS Version: Generic Linux (Architecture Independent)
- Boost ありを選ぶ

デバッグビルド

ビルドに必要なツールをインストールしてから、デバッグビルドのオプションを指定した上でビルドします。当たり前な話ですが、本番環境等のデバッグ目的でない場合はデバッグビルドしないでください。

% sudo apt-get update
% sudo apt-get install build-essential cmake wget curl libncurses5-dev libssl-dev gdb gdbserver -y 
% tar zxvf mysql-boost-8.0.13.tar.gz
% cd mysql-8.0.13
% CFLAGS=-O0 cmake -DWITH_DEBUG=1 -DWITH_INNODB_EXTRA_DEBUG=1 -DCMAKE_INSTALL_PREFIX=$HOME/mysql-debug -DDOWNLOAD_BOOST=1 -DWITH_BOOST=./boost
% make -j$(getconf _NPROCESSORS_ONLN) install

$HOME/mysql-debug ディレクトリ以下にバイナリをインストールしたので、データの初期化を行います。

% cd $HOME/mysql-debug
% ./bin/mysqld  --no-defaults --datadir=./data --basedir=./ --initialize-insecure

metadataを確認する

metadataはMySQL 8.0からData Dictionaryと呼ばれる場所に保存されるようになりました。

https://mysqlserverteam.com/the-unified-data-dictionary-lab-release/

調べてみた感じだと、このdata dictionaryの中身をdumpするには2通りありそうです。

ibd2sdi

ibdファイルからSDI(serialized dictionary information)を抽出してくれる便利コマンドです。出力はJSON形式です。

MySQL :: MySQL 8.0 Reference Manual :: 4.6.1 ibd2sdi — InnoDB Tablespace SDI Extraction Utility

デバッグビルドしているのであればmysql.tables等から参照

ドキュメントに書いてあるやり方で参照することが可能です。

mysql> SET SESSION debug='+d,skip_dd_table_access_check';
mysql> SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';
mysql> show create table mysql.columns \G;
*************************** 1. row ***************************
       Table: columns
Create Table: CREATE TABLE `columns` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `table_id` bigint(20) unsigned NOT NULL,
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci NOT NULL,
  `ordinal_position` int(10) unsigned NOT NULL,
  `type` enum('MYSQL_TYPE_DECIMAL','MYSQL_TYPE_TINY','MYSQL_TYPE_SHORT','MYSQL_TYPE_LONG','MYSQL_TYPE_FLOAT','MYSQL_TYPE_DOUBLE','MYSQL_TYPE_NULL','MYSQL_TYPE_TIMESTAM
P','MYSQL_TYPE_LONGLONG','MYSQL_TYPE_INT24','MYSQL_TYPE_DATE','MYSQL_TYPE_TIME','MYSQL_TYPE_DATETIME','MYSQL_TYPE_YEAR','MYSQL_TYPE_NEWDATE','MYSQL_TYPE_VARCHAR','MYSQ
L_TYPE_BIT','MYSQL_TYPE_TIMESTAMP2','MYSQL_TYPE_DATETIME2','MYSQL_TYPE_TIME2','MYSQL_TYPE_NEWDECIMAL','MYSQL_TYPE_ENUM','MYSQL_TYPE_SET','MYSQL_TYPE_TINY_BLOB','MYSQL_
TYPE_MEDIUM_BLOB','MYSQL_TYPE_LONG_BLOB','MYSQL_TYPE_BLOB','MYSQL_TYPE_VAR_STRING','MYSQL_TYPE_STRING','MYSQL_TYPE_GEOMETRY','MYSQL_TYPE_JSON') COLLATE utf8_bin NOT NU
LL,
  `is_nullable` tinyint(1) NOT NULL,
  `is_zerofill` tinyint(1) DEFAULT NULL,
  `is_unsigned` tinyint(1) DEFAULT NULL,
  `char_length` int(10) unsigned DEFAULT NULL,
  `numeric_precision` int(10) unsigned DEFAULT NULL,
  `numeric_scale` int(10) unsigned DEFAULT NULL,
  `datetime_precision` int(10) unsigned DEFAULT NULL,
  `collation_id` bigint(20) unsigned DEFAULT NULL,
  `has_no_default` tinyint(1) DEFAULT NULL,
  `default_value` blob,
  `default_value_utf8` text COLLATE utf8_bin,
  `default_option` blob,
  `update_option` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `is_auto_increment` tinyint(1) DEFAULT NULL,
  `is_virtual` tinyint(1) DEFAULT NULL,
  `generation_expression` longblob,
  `generation_expression_utf8` longtext COLLATE utf8_bin,
  `comment` varchar(2048) COLLATE utf8_bin NOT NULL,
  `hidden` enum('Visible','SE','SQL') COLLATE utf8_bin NOT NULL,
  `options` mediumtext COLLATE utf8_bin,
  `se_private_data` mediumtext COLLATE utf8_bin,
  `column_key` enum('','PRI','UNI','MUL') COLLATE utf8_bin NOT NULL,
  `column_type_utf8` mediumtext COLLATE utf8_bin NOT NULL,
  `srs_id` int(10) unsigned DEFAULT NULL,
  `is_explicit_collation` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `table_id` (`table_id`,`name`),
  UNIQUE KEY `table_id_2` (`table_id`,`ordinal_position`),
  KEY `collation_id` (`collation_id`),
  KEY `srs_id` (`srs_id`),
  CONSTRAINT `columns_ibfk_1` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`),
  CONSTRAINT `columns_ibfk_2` FOREIGN KEY (`collation_id`) REFERENCES `collations` (`id`),
  CONSTRAINT `columns_ibfk_3` FOREIGN KEY (`srs_id`) REFERENCES `st_spatial_reference_systems` (`id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB AUTO_INCREMENT=4062 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

gdbで追いかける

事前準備

準備としてテーブルを作ってデータを投入します。

mysql> create database test;
mysql> create table hoge4(a int auto_increment primary key);
mysql> insert into hoge4(a) values (1), (2);

この時点でibd2sdiでメタデータを確認します。どこかにメモっておきましょう。

% ./bin/ibd2sdi data/test/hoge4.ibd

ALTER TABLE ADD COLUMN

以下の場所にbreakpointを貼ります。

(gdb) b ha_innobase::inplace_alter_table_impl<dd::Table>(TABLE*, Alter_inplace_info*, dd::Table const*, dd::Table*)

(gdb) b dd_commit_inplace_instant<dd::Table>(Alter_inplace_info*, THD*, trx_t*, dict_table_t*, TABLE const*, TABLE const*, dd::Table const*, dd::Table*, uint64_t*)

mysqlコマンドでデバッグビルドしたmysqldに接続し、alter tableを適当に流します。

mysql> alter table hoge4 add column c varchar(10) default 'aaa';

そうすると以下のようにbreakpointに引っかかってmysqldが一時停止します。

(gdb) bt
#0  ha_innobase::inplace_alter_table_impl<dd::Table> (this=0x7fd400addb98, altered_table=0x7fd400b150e0, ha_alter_info=0x7fd438f92980, old_dd_tab=0x7fd4007d2f28,
    new_dd_tab=0x7fd4000be328) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/handler/handler0alter.cc:5723
#1  0x000000000475f1a7 in ha_innobase::inplace_alter_table (this=0x7fd400addb98, altered_table=0x7fd400b150e0, ha_alter_info=0x7fd438f92980,
    old_dd_tab=0x7fd4007d2f28, new_dd_tab=0x7fd4000be328) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/handler/handler0alter.cc:1168
#2  0x00000000030c658c in handler::ha_inplace_alter_table (this=0x7fd400addb98, altered_table=0x7fd400b150e0, ha_alter_info=0x7fd438f92980,
    old_table_def=0x7fd4007d2f28, new_table_def=0x7fd4000be328) at /home/y-asaba/src/mysql-8.0.13/sql/handler.h:5041
#3  0x00000000030b2108 in mysql_inplace_alter_table (thd=0x7fd4006d6da0, schema=..., new_schema=..., table_def=0x7fd4007d2f28, altered_table_def=0x7fd4000be328,
    table_list=0x7fd400d4fce8, table=0x7fd400f29600, altered_table=0x7fd400b150e0, ha_alter_info=0x7fd438f92980, inplace_supported=HA_ALTER_INPLACE_INSTANT,
    alter_ctx=0x7fd438f93570, columns=std::set with 0 elements, fk_key_info=0x7fd400f5da40, fk_key_count=0, fk_invalidator=0x7fd438f928c0)
    at /home/y-asaba/src/mysql-8.0.13/sql/sql_table.cc:11621
#4  0x00000000030bcc72 in mysql_alter_table (thd=0x7fd4006d6da0, new_db=0x7fd400d50280 "test", new_name=0x0, create_info=0x7fd438f948a0, table_list=0x7fd400d4fce8,
    alter_info=0x7fd438f949a0) at /home/y-asaba/src/mysql-8.0.13/sql/sql_table.cc:14963
#5  0x00000000035a93ca in Sql_cmd_alter_table::execute (this=0x7fd400d50380, thd=0x7fd4006d6da0) at /home/y-asaba/src/mysql-8.0.13/sql/sql_alter.cc:345
#6  0x0000000002ff9f78 in mysql_execute_command (thd=0x7fd4006d6da0, first_level=true) at /home/y-asaba/src/mysql-8.0.13/sql/sql_parse.cc:4307
#7  0x0000000002ffc644 in mysql_parse (thd=0x7fd4006d6da0, parser_state=0x7fd438f96380, force_primary_storage_engine=false)
    at /home/y-asaba/src/mysql-8.0.13/sql/sql_parse.cc:5041
#8  0x0000000002ff1f8c in dispatch_command (thd=0x7fd4006d6da0, com_data=0x7fd438f96cf0, command=COM_QUERY) at /home/y-asaba/src/mysql-8.0.13/sql/sql_parse.cc:1687
#9  0x0000000002ff05ee in do_command (thd=0x7fd4006d6da0) at /home/y-asaba/src/mysql-8.0.13/sql/sql_parse.cc:1260
#10 0x000000000319dfd9 in handle_connection (arg=0x7d5cea0) at /home/y-asaba/src/mysql-8.0.13/sql/conn_handler/connection_handler_per_thread.cc:308
#11 0x0000000004625004 in pfs_spawn_thread (arg=0x7d430e0) at /home/y-asaba/src/mysql-8.0.13/storage/perfschema/pfs.cc:2836
#12 0x00007fd44d9186ba in start_thread (arg=0x7fd438f97700) at pthread_create.c:333
#13 0x00007fd44c2f441d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

実際にdebuggerでstep実行していくと、すぐに以下のようにinstant ddlかどうかを判断し、instantの場合はこの関数を抜けるようになっています。

template <typename Table>
bool ha_innobase::inplace_alter_table_impl(TABLE *altered_table,
                                           Alter_inplace_info *ha_alter_info,
                                           const Table *old_dd_tab,
                                           Table *new_dd_tab) {
  // ...
  if (!(ha_alter_info->handler_flags & INNOBASE_ALTER_DATA) ||
      is_instant(ha_alter_info)) {
  ok_exit:
    DEBUG_SYNC(m_user_thd, "innodb_after_inplace_alter_table");
    DBUG_RETURN(false);
  }

https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/handler/handler0alter.cc#L5041-L5047

そしてCOMMIT処理の中でmetadataのみを書き換える処理をやっています（おそらく）。

template <typename Table>
static void dd_commit_inplace_instant(Alter_inplace_info *ha_alter_info,
                                      THD *thd, trx_t *trx, dict_table_t *table,
                                      const TABLE *old_table,
                                      const TABLE *altered_table,
                                      const Table *old_dd_tab,
                                      Table *new_dd_tab, uint64_t *autoinc) {
//...
  switch (type) {
//...
    case Instant_Type::INSTANT_ADD_COLUMN:
      dd_copy_private(*new_dd_tab, *old_dd_tab);

      if (!dd_table_is_partitioned(new_dd_tab->table()) ||
          dd_part_is_first(reinterpret_cast<dd::Partition *>(new_dd_tab))) {
        dd_commit_instant_table(table, old_table, altered_table,
                                &old_dd_tab->table(), &new_dd_tab->table());
      }

      if (dd_table_is_partitioned(new_dd_tab->table())) {
        dd_commit_instant_part(table,
                               reinterpret_cast<dd::Partition *>(new_dd_tab));
      }

      row_mysql_lock_data_dictionary(trx);
      innobase_discard_table(thd, table);
      row_mysql_unlock_data_dictionary(trx);
      break;

https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/handler/handler0alter.cc#L3923-L3940

実際にibd2sdiでダンプすると以下のようにメタデータが追加されているのを確認できると思います。

            {
                "name": "c",
                "type": 16,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 3,
                "char_length": 40,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": false,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "A2FhYQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=",
                "default_value_utf8_null": false,
                "default_value_utf8": "aaa",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "default=616161;table_id=1066;",
                "column_key": 1,
                "column_type_utf8": "varchar(10)",
                "elements": [],
                "collation_id": 255,
                "is_explicit_collation": false
            },

SELECTの挙動

では、metadataだけを書き換えたのはなんとなくわかりましたが、実際にSELECTした際にどういう挙動になるのかを簡単に見てみました。（コードが巨大なので追いきれていない）

以下の場所にbreakpointを貼ります。

(gdb) b ha_innobase::rnd_next
(gdb) b rec_get_nth_field_instant

mysql cliで "select * from hoge4" という感じでSQLを投げます。

(gdb) bt
#0  rec_get_nth_field_instant (rec=0x7fd425950080 "\200", offsets=0x7fd438f93f50, n=0, index=0x7fd40093c868, len=0x7fd438f93328) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/include/rem0rec.ic:1238
#1  0x00000000049198de in row_sel_store_mysql_field_func (mysql_rec=0x7fd4009cfab8 "\377", prebuilt=0x7fd400e5e188, rec=0x7fd425950080 "\200", index=0x7fd40093c868, offsets=0x7fd438f93f50, field_no=0,
    templ=0x7fd400cbad38, sec_field_no=18446744073709551615, lob_undo=0x0) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/row/row0sel.cc:2867
#2  0x000000000491a2fb in row_sel_store_mysql_rec (mysql_rec=0x7fd4009cfab8 "\377", prebuilt=0x7fd400e5e188, rec=0x7fd425950080 "\200", vrow=0x0, rec_clust=0, index=0x7fd40093c868, offsets=0x7fd438f93f50,
    clust_templ_for_sec=false, lob_undo=0x0) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/row/row0sel.cc:3064
#3  0x0000000004920b14 in row_search_mvcc (buf=0x7fd4009cfab8 "\377", mode=PAGE_CUR_G, prebuilt=0x7fd400e5e188, match_mode=0, direction=0) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/row/row0sel.cc:5462
#4  0x0000000004703511 in ha_innobase::index_read (this=0x7fd400845128, buf=0x7fd4009cfab8 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY)
    at /home/y-asaba/src/mysql-8.0.13/storage/innobase/handler/ha_innodb.cc:8755
#5  0x000000000470465d in ha_innobase::index_first (this=0x7fd400845128, buf=0x7fd4009cfab8 "\377") at /home/y-asaba/src/mysql-8.0.13/storage/innobase/handler/ha_innodb.cc:9095
#6  0x00000000047048f0 in ha_innobase::rnd_next (this=0x7fd400845128, buf=0x7fd4009cfab8 "\377") at /home/y-asaba/src/mysql-8.0.13/storage/innobase/handler/ha_innodb.cc:9169
#7  0x00000000033400a8 in handler::ha_rnd_next (this=0x7fd400845128, buf=0x7fd4009cfab8 "\377") at /home/y-asaba/src/mysql-8.0.13/sql/handler.cc:2822
...

最終的にここの処理で、デフォルトの値をつけるべきであればmetadataからデータを取り出してくっつける処理を行っています。

/** Gets the value of the specified field in the record.
This is only used when there is possibility that the record comes from the
clustered index, which has some instantly add columns
@param[in]  rec record
@param[in]  offsets array returned by rec_get_offsets()
@param[in]  n   index of the field
@param[in]  index   clustered index where the record resides
@param[in,out]  len length of the field, UNIV_SQL_NULL if SQL null
@return value of the field, could be either pointer to rec or default value */
UNIV_INLINE
const byte *rec_get_nth_field_instant(const rec_t *rec, const ulint *offsets,
                                      ulint n, const dict_index_t *index,
                                      ulint *len) {
  ulint off = rec_get_nth_field_offs(offsets, n, len);

  if (*len != UNIV_SQL_ADD_COL_DEFAULT) {
    return (rec + off);
  }

  ut_a(index != nullptr);
  ut_ad(index->has_instant_cols());

  return (index->get_nth_default(n, len));
}

https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/include/rem0rec.ic#L1225-L1248

  /** Get the default value of nth field and its length if exists.
  If not exists, both the return value is nullptr and length is 0.
  @param[in]    nth nth field to get
  @param[in,out]    length  length of the default value
  @return   the default value data of nth field */
  const byte *get_nth_default(ulint nth, ulint *length) const {
    ut_ad(nth < n_fields);
    ut_ad(get_instant_fields() <= nth);
    const dict_col_t *col = get_col(nth);
    if (col->instant_default == nullptr) {
      *length = 0;
      return (nullptr);
    }

    *length = col->instant_default->len;
    ut_ad(*length == 0 || *length == UNIV_SQL_NULL ||
          col->instant_default->value != nullptr);
    return (col->instant_default->value);
  }

https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/include/dict0mem.h#L1170-L1182

innodbの物理フォーマットをちゃんと把握できていないので間違っていたらすみませんが、普通はblockに行のイメージを詰められるだけ詰めており、ブロック単位でメモリにロードしてから該当の行を取り出します。その行は通常はoffsetを計算しながらこの場所はこの列の値であると計算していますが、それの範囲外の場合にデフォルトの値をくっつけるという挙動になっているようです。

ということなので、以下のような制約がINSTANT ADD COLUMNについているのかと思います。

A column can only be added as the last column of the table. Adding a column to any other position among other columns is not supported.

実験

簡単なパフォーマンス比較

まあ当然速いよねという結果です。

ALGORITHM=INSTANT

alter table hoge add column b varchar(10) default 'aaa', ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALGORITHM=INPLACE

mysql> alter table hoge add column b varchar(10) default 'aaa', ALGORITHM = INPLACE;
Query OK, 0 rows affected (21.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALGORITHM=COPY

mysql> alter table hoge add column b varchar(10) default 'aaa', ALGORITHM = COPY;
Query OK, 1000001 rows affected (2 min 45.31 sec)
Records: 1000001  Duplicates: 0  Warnings: 0

optimize tableをした場合どうなる？

information_schema.innodb_tablesをoptimize tableの前後でINSTANT_COLSの値が変わっているのがわかるかと思います。あと、get_nth_default にbreakpointを貼ってみましたがそこを通過しなかったので、テーブルを作り直すタイミングでデフォルトの値を行に入れて作り直しているようです。

mysql> select * from information_schema.innodb_tables where name = 'test/hoge' \G;
*************************** 1. row ***************************
     TABLE_ID: 1075
         NAME: test/hoge
         FLAG: 33
       N_COLS: 5
        SPACE: 18
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
 INSTANT_COLS: 1
1 row in set (0.01 sec)

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

mysql> select * from information_schema.innodb_tables where name = 'test/hoge' \G;
*************************** 1. row ***************************
     TABLE_ID: 1076
         NAME: test/hoge
         FLAG: 33
       N_COLS: 5
        SPACE: 19
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
 INSTANT_COLS: 0
1 row in set (0.00 sec)

ALTER TABLE後にINSERTした行はどうなる？

optimize tableと同じように、INSERTした行のデフォルトの値はちゃんと入っているようです。同じところにbreakpointを貼ってみてください。

updateするとテーブルサイズはどうなる？

すみません、実験しきれませんでした。なんとなく不要領域が増えそうな気がしていてテーブルが肥大化するのかなという推測をしているのですが、実際にやっていないのでどなたか調べてほしいです。

まとめ？

身も蓋もないまとめですが、INSTANT ADD COLUMNはよくできているなあ。という感想でした。あと、MySQLのソースコードに興味ある人にこういう感じで読むこともできるよ、とお伝えできていると幸いです。

次回はKarate Masterの@krtによる分析基盤のいい話が聞けると思います！

宣伝

freeeのプロダクト基盤というチームでは以下のポジションで積極採用しております。興味ある方はぜひ弊社オフィスまで遊びに来てください！

y-asaba@hatenablog

MySQLのInstant ADD COLUMNをちゃんと調べてみる