はじめに
花粉症が辛い季節になりました。
花粉症も困りますが、本日はMySQL関連で困って調べたことをまとめたいと思います。
タイトルですが、InnoDBだとDBを再起動した際にauto_incrementが最適化されてしまうとのこと。
すでに多くの知見があるようですが、今回は実際に試してみました。
5.5.9のリリースにて、最適化にてリセットされる問題は解消されたようですが、今回は試しにやってみたいと思います。
Changes in MySQL 5.5.9 (2011-02-07, General Availability)
準備
MySQLのバージョンは5.7.14になります。
| 1 2 | $ mysql --version mysql Ver 14.14 Distrib 5.7.14, for osx10.10 (x86_64) using EditLine wrapper |
とりあえず、テスト用のDBを作成します。
| 1 | mysql> create database test; |
ストレージエンジンの一覧を見てみます。
現在はInnoDBがデフォルトですね。（というより、こんなにたくさんあるんですね。。。）
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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の簡単な特徴は下記になります。
- InnoDBはレコード単位でロックされるが、MyISAMはテーブル単位。
- トランザクション機能がMyISAMにはない。
- InnoDBはMyISAMに比べてデータサイズが大きくなる。
- InnoDBは更新系、MyISAMは参照系が得意。
それでは実際にそれぞれのエンジンのテーブルを作成します。
| 1 2 | 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; |
実際に各テーブルのストレージエンジンはinformation_schemaに移動すれば見ることができます。
| 1 2 3 4 5 6 7 8 | mysql> use information_schema; mysql> select table_name, engine from tables where table_schema = "test"; +-------------+--------+ | table_name | engine | +-------------+--------+ | innodb_test | InnoDB | | myisam_test | MyISAM | +-------------+--------+ |
レコード作成
それぞれのテーブルにレコードを作成します。
| 1 2 | mysql> insert into innodb_test (id) values (0), (0), (0), (0), (0); mysql> insert into myisam_test (id) values (0), (0), (0), (0), (0); |
| 1 2 3 4 5 6 7 8 9 10 11 | mysql> select * from innodb_test; mysql> select * from myisam_test; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ |
とりあえず、2つレコードを削除しておきます。
| 1 2 | mysql> delete from innodb_test where id in (4, 5); mysql> delete from myisam_test where id in (4, 5); |
テーブル最適化
それでは最適化してみます。
まずはInnoDBから。
| 1 2 3 4 5 6 7 | 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です。
| 1 2 3 4 5 6 | mysql> optimize table myisam_test; +------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+----------+----------+----------+ | test.myisam_test | optimize | status | OK | +------------------+----------+----------+----------+ |
それでは、再度インサートします。
| 1 2 | mysql> insert into innodb_test (id) values (0), (0); mysql> insert into myisam_test (id) values (0), (0); |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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でした。）
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | mysql> select * from innodb_test; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ mysql> select * from myisam_test; +----+ | id | +----+ | 1 | | 2 | | 3 | | 13 | | 14 | +----+ |
InnoDBの方はauto incrementが戻っていました。
実際、テーブル情報を見ると、戻っていました。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | 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のカラムをメモリ上のみに持ち、再起動のタイミングで以下で値を取得しているようです。
| 1 | 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
