本日は実際に困って調べた話にしたいと思います。
タイトルですが、InnoDBだとDBを再起動した際にauto_incrementが最適化されてしまうとのこと。
すでに多くの知見があるようですが、今回は実際に試してみました。
5.5.9のリリースにて、最適化にてリセットされる問題は解消されたようですが、今回は試しにやってみたいと思います。
Changes in MySQL 5.5.9 (2011-02-07, General Availability)
準備
MySQLのバージョンは5.7.14になります。
$ mysql --version
mysql Ver 14.14 Distrib 5.7.14, for osx10.10 (x86_64) using EditLine wrappermysql> create database test;現在はInnoDBがデフォルトですね。（というより、こんなにたくさんあるんですね。。。）
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+InnoDBとMyISAMの簡単な特徴は下記になります。
- InnoDBはレコード単位でロックされるが、MyISAMはテーブル単位。
- トランザクション機能がMyISAMにはない。
- InnoDBはMyISAMに比べてデータサイズが大きくなる。
- InnoDBは更新系、MyISAMは参照系が得意。
それでは実際にそれぞれのエンジンのテーブルを作成します。
mysql> create table innodb_test (id int primary key auto_increment) engine=InnoDB;
mysql> create table myisam_test (id int primary key auto_increment) engine=MyISAM;mysql> use information_schema;
mysql> select table_name, engine from tables where table_schema = "test";
+-------------+--------+
| table_name | engine |
+-------------+--------+
| innodb_test | InnoDB |
| myisam_test | MyISAM |
+-------------+--------+レコード作成
それぞれのテーブルにレコードを作成します。
mysql> insert into innodb_test (id) values (0), (0), (0), (0), (0);
mysql> insert into myisam_test (id) values (0), (0), (0), (0), (0);mysql> select * from innodb_test;
mysql> select * from myisam_test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+mysql> delete from innodb_test where id in (4, 5);
mysql> delete from myisam_test where id in (4, 5);テーブル最適化
それでは最適化してみます。
まずはInnoDBから。
mysql> optimize table innodb_test;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+----------+----------+-------------------------------------------------------------------+
| test.innodb_test | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.innodb_test | optimize | status | OK |
+------------------+----------+----------+-------------------------------------------------------------------+どうやらInnoDBではoptimizeコマンドはALTER TABLEとして実行されるそうです。（その通知メッセージのようです。）
14.7.2.4 OPTIMIZE TABLE Syntax
続きましてMyISAMです。
mysql> optimize table myisam_test;
+------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+----------+----------+----------+
| test.myisam_test | optimize | status | OK |
+------------------+----------+----------+----------+mysql> insert into innodb_test (id) values (0), (0);
mysql> insert into myisam_test (id) values (0), (0);mysql> select * from innodb_test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 6 |
| 7 |
+----+
mysql> select * from myisam_test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 6 |
| 7 |
+----+次は再起動を試してみます。
MySQL再起動
それでは再起動後にインサートしてみます。（再起動前はauto_incrementの値が13でした。）
mysql> select * from innodb_test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
mysql> select * from myisam_test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 13 |
| 14 |
+----+実際、テーブル情報を見ると、戻っていました。
mysql> show table status like 'innodb_test' \G;
*************************** 1. row ***************************
Name: innodb_test
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 4
Create_time: 2016-12-15 23:38:15
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
mysql> show table status like 'myisam_test' \G;
*************************** 1. row ***************************
Name: myisam_test
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 3
Avg_row_length: 7
Data_length: 56
Max_data_length: 1970324836974591
Index_length: 2048
Data_free: 35
Auto_increment: 13
Create_time: 2016-12-15 19:30:36
Update_time: 2016-12-15 23:52:41
Check_time: 2016-12-15 23:38:22
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: 最適化では確認できませんでしたが、再起動ではInnoDBの方はauto_incrementの値が戻っておりました。
InnoDBではauto incrementのカラムをメモリ上のみに持ち、再起動のタイミングで以下で値を取得しているようです。
SELECT MAX(ai_col) FROM table_name FOR UPDATE;参考資料
15.8.6 AUTO_INCREMENT Handling in InnoDB
【MySQL】AUTO_INCREMENTの値が戻る＠InnoDBエンジンのテーブル
Changes in MySQL 5.5.9 (2011-02-07, General Availability)
MySQLの「InnoDB」と「MyISAM」についての易しめな違い
14.7.2.4 OPTIMIZE TABLE Syntax
投稿
0 件のコメント:
コメントを投稿