はじめに
MySQLを使用した事がある方なら、ほとんどの方がご存知かと思いますが、MySQL には AUTO_INCREMENT 属性というものがあります。
今回は、入門的な要素が強い内容となりますが、この AUTO_INCREMENT 属性 について掘り下げて記載しようと思います。
AUTO_INCREMENT とは
テーブルのカラムに AUTO_INCREMENT 属性を定義することにより、一意の識別子（連番）を設定する事が可能となります。
MySQLで連番を設定したい場合や、サロゲートキーとして使用するようなケースでよく用いられます。
※後述しますが、必ずしも連番とならないケースもあるので活用する際は注意が必要です。
AUTO_INCREMENT の定義に関する制限
AUTO_INCREMENT 属性は、整数または浮動小数点型のカラムに対して作成する事が可能です。
但し、最新のMySQL8.0.28 で浮動小数点型のカラムに設定すると、以下のような警告メッセージが生成されました。
|
1 2 3 4 5 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 3856 | AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column 'id'. | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ |
確認したとこと、MySQL8.0.17からは浮動小数点型での定義は非推奨となったようです。
- Changes in MySQL 8.0.17 (2019-07-22, General Availability)
AUTO_INCREMENT support is deprecated for columns of type FLOAT and DOUBLE (and any synonyms) and will be removed in a future MySQL version. Consider removing the AUTO_INCREMENT attribute from such columns, or convert them to an integer type.
その他に AUTO_INCREMENT 属性を割り当てるカラムには以下の制限があります。
- テーブルごとに存在できる AUTO_INCREMENT カラムは 1 つのみ
- DEFAULT 制約を割り当てることはできない
- インデックスが割り当てられている必要がある
最後のインデックスが割り当てられている必要がある点については、MyISAMではマルチカラムインデックスで定義した先頭以外の列でも許容されるが、InnoDBでは許容されないといった違いがあります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> CREATE TABLE t1 ( -> col1 INT NOT NULL, -> col2 INT NOT NULL, -> col3 INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY(col1) -> ,INDEX i1(col2, col3) -> ) ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t2 ( -> col1 INT NOT NULL, -> col2 INT NOT NULL, -> col3 INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY(col1) -> ,INDEX i1(col2, col3) -> ) ENGINE=InnoDB; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key |
※InnoDBでもマルチカラムインデックスの先頭の定義となる col2 には AUTO_INCREMENT 属性を定義することが可能です。
AUTO_INCREMENT が最大値に達した場合
AUTO_INCREMENT の値が最大値に達した場合、以降は最大値が延々と割り当てられます。
その為、主キーもしくはユニークキーに AUTO_INCREMENT 属性を使用している場合は、最大値に達した以降は、重複キーによりエラーとなり、そのテーブルへの登録ができなくなる為、注意が必要です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> CREATE TABLE t1 ( -> id TINYINT NOT NULL -> ,col TINYINT NOT NULL AUTO_INCREMENT -> ,PRIMARY KEY(id) -> ,INDEX(col) -> ); mysql> INSERT INTO t1 (id, col) VALUES(1, 127); mysql> INSERT INTO t1 (id, col) VALUES(2, NULL); mysql> SELECT * FROM t1; +----+-----+ | id | col | +----+-----+ | 1 | 127 | | 2 | 127 | +----+-----+ |
AUTO_INCREMENT の値の確認
次に割り当てられる AUTO_INCREMENT の値を確認したい場合は、SHOW CREATE TABLE <テーブル名> で確認するか、information_schema.TABLES のレコードから確認する事が可能です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
mysql> CREATE TABLE t1 ( -> id INT NOT NULL AUTO_INCREMENT, -> col VARCHAR(10) NOT NULL -> ,PRIMARY KEY(id) -> ); mysql> INSERT INTO t1 (col) VALUES ('one'), ('two'), ('three'); mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE t1 ( id int NOT NULL AUTO_INCREMENT, col varchar(10) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci mysql> SELECT TABLE_SCHEMA, TABLE_NAME, AUTO_INCREMENT FROM information_schema.TABLES -> WHERE TABLE_SCHEMA = 'blog' AND TABLE_NAME = 't1'; +--------------+------------+----------------+ | TABLE_SCHEMA | TABLE_NAME | AUTO_INCREMENT | +--------------+------------+----------------+ | blog | t1 | 4 | +--------------+------------+----------------+ |
※ 但し、MySQL8.0 では以下のバグレポートにあるように、 information_schema.TABLES の内容がinformation_schema_stats_expiryの影響によりキャッシュされるという動作がある点、ご認識頂ければと思います。
AUTO_INCREMENT 属性が定義されているカラムの確認
「AUTO_INCREMENT 属性が付与されているテーブルとカラムを洗い出したいんだけど、いっぱいテーブルがあるし、簡単に抽出する方法がないかな？」
という場合は、 information_schema.COLUMNS から情報を抽出する事が可能です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, EXTRA FROM information_schema.COLUMNS -> WHERE TABLE_SCHEMA = 'sakila' AND EXTRA = 'auto_increment'; +--------------+------------+--------------+----------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EXTRA | +--------------+------------+--------------+----------------+ | sakila | actor | actor_id | auto_increment | | sakila | address | address_id | auto_increment | | sakila | category | category_id | auto_increment | | sakila | city | city_id | auto_increment | | sakila | country | country_id | auto_increment | | sakila | customer | customer_id | auto_increment | | sakila | film | film_id | auto_increment | | sakila | inventory | inventory_id | auto_increment | | sakila | language | language_id | auto_increment | | sakila | payment | payment_id | auto_increment | | sakila | rental | rental_id | auto_increment | | sakila | staff | staff_id | auto_increment | | sakila | store | store_id | auto_increment | +--------------+------------+--------------+----------------+ |
AUTO_INCREMENT が連番とならないケース
AUTO_INCREMENT 属性では、以下のようなケースでは連番が割り振られません。
- レコード挿入後に、ロールバックした場合
- AUTO_INCREMENT 属性に明示的に値を設定した場合
- auto_increment_increment に 1 以外を設定している場合
- innodb_autoinc_lock_mode に 2 を設定している場合
簡単に実例を確認してみましょう。
レコード挿入後に、ロールバックした場合
|
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 |
mysql> CREATE TABLE t1 ( -> id TINYINT NOT NULL AUTO_INCREMENT, -> col VARCHAR(10) NOT NULL, -> PRIMARY KEY(id) -> ) ENGINE=InnoDB; mysql> BEGIN; mysql> INSERT INTO t1 (col) VALUES ('one'), ('two'), ('three'); mysql> COMMIT; mysql> SELECT * FROM t1; +----+-------+ | id | col | +----+-------+ | 1 | one | | 2 | two | | 3 | three | +----+-------+ mysql> BEGIN; mysql> INSERT INTO t1 (col) VALUES ('four'), ('five'); mysql> ROLLBACK; mysql> BEGIN; mysql> INSERT INTO t1 (col) VALUES ('six'); mysql> COMMIT; mysql> SELECT * FROM t1; +----+-------+ | id | col | +----+-------+ | 1 | one | | 2 | two | | 3 | three | | 6 | six | +----+-------+ |
four と five の値の挿入でロールバックした為、4 と 5 の値がスキップしている事が分かります。
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 42 43 44 45 |
mysql> CREATE TABLE t2 ( -> id TINYINT NOT NULL AUTO_INCREMENT, -> col VARCHAR(10) NOT NULL, -> PRIMARY KEY(id) -> ) ENGINE=InnoDB; mysql> BEGIN; mysql> INSERT INTO t2 (col) VALUES ('one'), ('two'); mysql> COMMIT; mysql> SELECT * FROM t2; +----+-----+ | id | col | +----+-----+ | 1 | one | | 2 | two | +----+-----+ mysql> BEGIN; mysql> INSERT INTO t2 (id, col) VALUES (10, 'three'); mysql> COMMIT; mysql> SELECT * FROM t2; +----+-------+ | id | col | +----+-------+ | 1 | one | | 2 | two | | 10 | three | +----+-------+ mysql> BEGIN; mysql> INSERT INTO t2 (col) VALUES ('four'), ('five'); mysql> COMMIT; mysql> SELECT * FROM t2; +----+-------+ | id | col | +----+-------+ | 1 | one | | 2 | two | | 10 | three | | 11 | four | | 12 | five | +----+-------+ |
three のデータ挿入時に、明示的に 10 を指定しています。
AUTO_INCREMENT の値は、過去に採番した値の最大値を元に採番される為、それ以降に自動採番した場合は、10 までの値は使用されません。
※ MySQL8.0 以降と前バージョンで動作の異なる問題もありますので、以下の記事も是非、ご参照下さい。
MySQL 8.0 の AUTO_INCREMENT について
auto_increment_increment に 1 以外を設定している場合
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> CREATE TABLE t3 ( -> id TINYINT NOT NULL AUTO_INCREMENT, -> col VARCHAR(10) NOT NULL, -> PRIMARY KEY(id) -> ) ENGINE=InnoDB; mysql> SET SESSION auto_increment_increment = 10; mysql> BEGIN; mysql> INSERT INTO t3 (col) VALUES ('one'), ('two'), ('three'); mysql> COMMIT; mysql> SELECT * FROM t3; +----+-------+ | id | col | +----+-------+ | 1 | one | | 11 | two | | 21 | three | +----+-------+ |
auto_increment_increment は AUTO_INCREMENT 属性の増分値を定義するシステム変数となります。
グループレプリケーションのような複数のサーバで更新を行うような場合は、デフォルトの 1 以外を設定したりするケースもあります。
innodb_autoinc_lock_mode に 2 を設定している場合
innodb_autoinc_lock_mode を 2 に設定し、 base 、 copy の2つのテーブルを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_autoinc_lock_mode'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 2 | +--------------------------+-------+ mysql> CREATE TABLE base ( -> id INT NOT NULL AUTO_INCREMENT, -> col INT NOT NULL, -> PRIMARY KEY(id) -> ) ENGINE=InnoDB; mysql> CREATE TABLE copy ( -> id INT NOT NULL AUTO_INCREMENT, -> col INT NOT NULL, -> PRIMARY KEY(id) -> ) ENGINE=InnoDB; |
テーブル作成後、 base テーブルに1万件のレコードを登録します。
登録後は、AUTO_INCREMENT の最大値が1万となっています。
|
1 2 3 4 5 6 7 |
# for i in {1..10000} ; do mysql blog -e "INSERT INTO base (col) VALUES (${i})"; done # mysql blog -e "SELECT MAX(id) FROM base;" +---------+ | MAX(id) | +---------+ | 10000 | +---------+ |
次に copy テーブルに 2つのスレッドで、base テーブルのレコード全てを登録するというクエリを実行します。
（AUTO_INCREMENT属性の値は、コピーせずに自動採番するようにします。）
これにより、瞬間的に2つのスレッドが同時に、copy テーブルへの挿入を行うという動作が発生します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# for i in {1..2} ; do mysql blog -e "INSERT INTO copy (col) SELECT col FROM base;" & done # mysql blog -e "SELECT MAX(id) FROM copy;" +---------+ | MAX(id) | +---------+ | 26383 | +---------+ # mysql blog -e "SELECT * FROM copy WHERE id >= 18190 AND id <= 24577;" +-------+-------+ | id | col | +-------+-------+ | 18190 | 9999 | | 18191 | 10000 | | 24575 | 8192 | | 24576 | 8193 | | 24577 | 8194 | +-------+-------+ |
実行後、連番となっていないレコードが存在することが確認できるかと思います。
innodb_autoinc_lock_mode=2 （インターリーブロックモード）を設定すると、InnoDBストレージエンジンにおいて、AUTO_INCREMENT属性の値の更新時にAUTO_INCロックを使用しない為、このような空き番号が発生しますが、代わりに複数の接続からAUTO_INCREMENT属性を持つ同じテーブルへの挿入を高速に処理できるといった利点があります。
ちなみに、MySQL5.7までは、innodb_autoinc_lock_mode=1 （連続ロックモード）がデフォルトでしたが、MySQL8.0からは、デフォルトで 2 が設定されるようになりました。
AUTO_INCロックと innodb_autoinc_lock_mode については、以下のリファレンスをご確認下さい。
AUTO_INCロックによる並列挿入時間の違い
前述しましたAUTO_INCロックにおいて、インターリーブロックモードである場合に並列挿入が高速に処理できる旨を記載致しました。
それでは、連続ロックモードとインターリーブロックモードでどれ位の差がでるのか簡単に検証してみました。
検証サーバと検証手順
検証サーバは以下になります。
| 項目 | 項目値 |
|---|---|
| CPU | AMD EPYC 7J13 64-Core Processor（8core） |
| RAM | 64 GB |
| DISK | 200 GB |
| MySQL Version | MySQL 8.0.28 Community Edition |
/etc/my.cnf はデフォルトから以下の設定のみ追加しています。
|
1 2 3 4 5 |
local_infile=1 innodb_log_file_size=1G innodb_buffer_pool_size=40G innodb_lock_wait_timeout=300 ※ロックタイムアウト回避の為 innodb_autoinc_lock_mode=(1 OR 2) ※連続ロックモード:1 / インターリーブロックモード:2 |
検証手順は以下の要領で行いました。
１．sysbench を用いて1つのテーブルに 1,000万件 のレコードを登録。
登録したテーブルの定義は、以下のように AUTO_INCREMENT 属性が定義されています。
|
1 2 3 4 5 6 7 8 |
CREATE TABLE sbtest1 ( id int NOT NULL AUTO_INCREMENT, k int NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k) ) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
２．上記テーブルから、SELECT … INTO OUTFILEで TSVファイルにエクスポート。
３．エクスポートしたファイルを MySQL Shell の Parallel Table Import Utility を使用して2並列でロードする。
|
1 |
mysqlshell> util.importTable("sbtest1.tsv", {"schema": "sysbench", "table": "sbtest1", threads: 2, bytesPerChunk: '500M', showProgress: true}); |
検証結果
| 連続ロックモード | インターリーブロックモード |
|---|---|
| 2分6秒 | 1分34秒 |
今回の検証では25%程度インターリーブロックモードで高速化されるといった結果になりました。
（但し、サーバリソースや並列度によっても左右されるものですので、参考程度に見て下さい。）
また、連続ロックモードで実行の際には、SHOW ENGINE INNODB STATUS の TRANSACTIONS セクションに以下のようにAUTO_INCロックによる待ちが発生している出力を確認することができました。
|
1 2 |
------- TRX HAS BEEN WAITING 31 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table sysbench.sbtest1 trx id 105276 lock mode AUTO-INC waiting |
まとめ
MySQLからforkしたMariaDBではバージョン10.3からシーケンスが実装されていますが、MySQLには存在しない事もあり、AUTO_INCREMENT を使用するケースはまだまだあると思います。
MySQL運用・管理の課題はプロが解決します！
データベースのパフォーマンスが悪い、ダウンタイムが気になる、複雑な運用に苦戦している…そんなMySQLの運用・管理に関するお悩みはありませんか？そんな方は、ぜひ当社のオープンソースDBサポートをご検討ください。
オラクル公式パートナーである当社の経験豊富なエンジニアが、MySQL / MariaDB / CassandraをはじめとしたOSSデータベースの導入から運用まで、あらゆる課題に対してハイレベルで迅速なサポートを提供します。システムのパフォーマンス改善や安定した運用を実現します。
初回のご相談・お見積りは無料です！
MySQL運用・管理にお悩みの方は、まずはお気軽にお問い合わせください。
この記事の内容が、AUTO_INCREMENT を使用する方の疑問を解決する手助けになればいいなと思います。
