MySQL-Cluster

MySQL ClusterのAUTO_INCREMENTとLAST_INSERT_ID

More than 1 year has passed since last update.

MySQLのAUTO_INCREMENTで挿入した行の番号はLAST_INSERT_ID()関数で取得できます。
MySQLでは「Multiple row insert」という複数行をまとめてINSERTすることができるのですが、この場合はLAST_INSERT_ID()関数は最初の行のみが取得できます。

InnoDBではロック機能によってMultiple row insert時に割り振られるIDは連番となるのですが、NDBではどうか気になったので試してみました。
結論から言うと、MySQL Clusterでは複数行INSERTしたときに、LAST_INSERT_IDからすべてのIDを取得することはできないようです。

テストテーブルの作成

AUTO_INCREMENTを指定したINNODBとNDBCLUSTERのTABLEを作成します。
検証用にLAST_INSERT_ID()の結果を保存するTABLEも作成しました。

DROP TABLE ndb_table;
DROP TABLE idb_table;
DROP TABLE update_table;

CREATE TABLE ndb_table (
   id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
   type int
) engine=ndbcluster;

CREATE TABLE idb_table (
   id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
   type int
) engine=innodb;

CREATE TABLE update_table (
   id int,
   type  int,
   PRIMARY KEY (id,type)
) engine=innodb;

テストプログラムの作成

テストプログラムはPythonで書きました。先日、MySQL接続テストを行ったので。
http://qiita.com/estaro/items/0e92cd25e7ae2ca69b29

ai_test1.py
import mysql.connector
cnx = mysql.connector.connect(
    host='192.168.0.10',
    user='hoge',
    password='hoge',
    database='mydb'
)
cursor = cnx.cursor();

for index in range (0,1000):
        cursor.execute("INSERT INTO ndb_table (id, type) VALUES (NULL, 1),
            (NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),
            (NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1)")
        last_incert_id = cursor.lastrowid
        cursor.execute("INSERT INTO update_table (id, type) VALUES (%s, 1)", (last_incert_id,));
        cnx.commit();

cursor.close()
cnx.close()

10件の複数行INSERTを1000回実行します。LAST_INSERT_ID()ではなく、connector提供のlastrowidを使用しています。
同様にtypeと対象テーブルを分けたプログラムを4つ用意しました。

これをまとめてシェルで実行します。

ai_test_n.sh
#!/bin/bash

python3.6 ai_test1.py &
python3.6 ai_test2.py &
python3.6 ai_test3.py &
python3.6 ai_test4.py &

実行

./ai_test_n.sh

結果の確認

挿入されたデータです。想定した件数になっています。

mysql> SELECT type, count(type) FROM ndb_table GROUP BY type;
+------+-------------+
| type | count(type) |
+------+-------------+
|    1 |       10000 |
|    2 |       10000 |
+------+-------------+
2 rows in set (0.05 sec)

mysql> SELECT type, count(type) FROM idb_table GROUP BY type;
+------+-------------+
| type | count(type) |
+------+-------------+
|    3 |       10000 |
|    4 |       10000 |
+------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT type, count(type) FROM update_table GROUP BY type;
+------+-------------+
| type | count(type) |
+------+-------------+
|    1 |        1000 |
|    2 |        1000 |
|    3 |        1000 |
|    4 |        1000 |
+------+-------------+
4 rows in set (0.01 sec)

続いて、データの検証です。
検証用のSQLは以下です。ちょっとスマートではないですが、検証用なので良しとします。

SELECT
 type, count(type)
FROM 
 ndb_table
WHERE 
 id IN (SELECT id FROM update_table WHERE type=1)
 OR id IN (SELECT id+1 FROM update_table WHERE type=1)
 OR id IN (SELECT id+2 FROM update_table WHERE type=1)
 OR id IN (SELECT id+3 FROM update_table WHERE type=1)
 OR id IN (SELECT id+4 FROM update_table WHERE type=1)
 OR id IN (SELECT id+5 FROM update_table WHERE type=1)
 OR id IN (SELECT id+6 FROM update_table WHERE type=1)
 OR id IN (SELECT id+7 FROM update_table WHERE type=1)
 OR id IN (SELECT id+8 FROM update_table WHERE type=1)
 OR id IN (SELECT id+9 FROM update_table WHERE type=1)
GROUP BY type;

実行結果(INNODB)
これが期待の結果です。LAST_INSERT_ID()から+1~+9の連番となっていることがわかります。

+------+-------------+
| type | count(type) |
+------+-------------+
|    3 |       10000 |
+------+-------------+
+------+-------------+
| type | count(type) |
+------+-------------+
|    4 |       10000 |
+------+-------------+

実行結果(NDBCLUSTER)
ズレています。

+------+-------------+
| type | count(type) |
+------+-------------+
|    1 |        9832 |
|    2 |         168 |
+------+-------------+
+------+-------------+
| type | count(type) |
+------+-------------+
|    1 |         173 |
|    2 |        9827 |
+------+-------------+

MySQLのAUTO_INCREMENTと複数INSERTはすごく便利なので困りますね。

終わりです。

参考

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id