MySQL で採番テーブル

LAST_INSERT_ID(expr) を使う方法

公式ドキュメントで紹介されている方法.

• MySQL 5.6 Reference Manual :: 12.14 Information Functions

LAST_INSERT_ID(), LAST_INSERT_ID(expr)

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is
returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID().
This can be used to simulate sequences:

テーブル準備

• 採番テーブル

create table `seq` (
	`id` int(11) not null
) engine = InnoDB default charset = utf8;

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

予め id = 0 のレコードを入れておく.

insert into seq values (0);

• データ用テーブル (採番された id を保存するだけ)

create table `data` (
	`no` int(11) not null unique
) engine = InnoDB default charset = utf8;

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| no    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

採番処理

<?php
/**
 * LAST_INSERT_ID に渡された引数は,
 * 次回の LAST_INSERT_ID() の返り値として記憶される.
 */
$res = $pdo->query('update seq set id = LAST_INSERT_ID(id + 1)');
if (!$res) {
    error_log('failed to update seq.', 3, $error_log_file);
    die();
}

list($no) = $pdo->query('select LAST_INSERT_ID()', PDO::FETCH_NUM)->fetch();
if (!is_numeric($no)) {
    error_log('failed to select LAST_INSERT_ID', 3, $error_log_file);
    die();
}

$stmt = $pdo->prepare('insert into data values (:no)');
$stmt->bindValue(':no', (int)$no, PDO::PARAM_INT);

$res = $stmt->execute();
if (!$res) {
    error_log(json_encode($stmt->errorInfo()) . PHP_EOL, 3, $error_log_file);
}

注意点

トランザクションの影響を受けるため、ロールバックした場合に同一のIDが採番される.

<?php
var_dump(currentSeq($pdo));
// "26"

if (!$pdo->beginTransaction()) {
    error_log('failed to begin transaction.', 3, $error_log_file);
    die();
}

// ...
// ... 採番処理 ...
// ...

if (!$pdo->rollBack()) {
    error_log('failed to rollback.', 3, $error_log_file);
    die();
}
var_dump(currentSeq($pdo));
// "26"

パフォーマンス確認

$ ab -n 5000 -c 50 http://{VM}/index.php
Time taken for tests:   39.566 seconds
Requests per second:    126.37 [#/sec] (mean)
Time per request:       395.656 [ms] (mean)

AUTO_INCREMENT を使う方法

テーブル準備

• 採番テーブル

create table `seq_ai` (
	`id` int(11) not null auto_increment primary key
) engine = InnoDB default charset = utf8;

+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+

• データ用テーブルは LAST_INSERT_ID(expr) パターンと同じ.

採番処理

$res = $pdo->query('insert into seq_ai values (null)');
if (!$res) {
    error_log('failed to insert.', 3, $error_log_file);
    die();
}

list($no) = $pdo->query('select LAST_INSERT_ID()', PDO::FETCH_NUM)->fetch();

if (is_numeric($no)) {
    // 採番テーブルにたまるレコードを削除.
    $stmt = $pdo->prepare('delete from seq_ai where id < :no');
    $stmt->bindValue(':no', (int)$no, PDO::PARAM_INT);
    $stmt->execute();
} else {
    error_log('failed to select LAST_INSERT_ID', 3, $error_log_file);
    die();
}

$stmt = $pdo->prepare('insert into data values (:no)');
$stmt->bindValue(':no', (int)$no, PDO::PARAM_INT);

$res = $stmt->execute();
if (!$res) {
    error_log(json_encode($stmt->errorInfo()) . PHP_EOL, 3, $error_log_file);
}

トランザクションの影響を受けない

var_dump(currentAutoIncrement($pdo));
// "26"

if (!$pdo->beginTransaction()) {
    error_log('failed to begin transaction.', 3, $error_log_file);
    die();
}

// ...
// ... 採番処理 ...
// ...

if (!$pdo->rollBack()) {
    error_log('failed to rollback.', 3, $error_log_file);
    die();
}
var_dump(currentAutoIncrement($pdo));
// "27"

パフォーマンス確認

$ ab -n 5000 -c 50 http://{VM}/index_ai.php
Time taken for tests:   33.390 seconds
Requests per second:    149.75 [#/sec] (mean)
Time per request:       333.898 [ms] (mean)

アンチパターン(明示的なLOCK, UNLOCK)

• 採番テーブル, データ用テーブルは LAST_INSERT_ID(expr) パターンと同じ.

採番処理

$res = $pdo->query('lock tables seq_anti write');

$res = $pdo->query('update seq_anti set id = id + 1');
if (!$res) {
    error_log('failed to update seq.', 3, $error_log_file);
    die();
}

list($no) = $pdo->query('select max(id) from seq_anti', PDO::FETCH_NUM)->fetch();
if (!is_numeric($no)) {
    error_log('failed to select id', 3, $error_log_file);
    die();
}

$res = $pdo->query('unlock tables');

$stmt = $pdo->prepare('insert into data values (:no)');
$stmt->bindValue(':no', (int)$no, PDO::PARAM_INT);

$res = $stmt->execute();
if (!$res) {
    error_log(json_encode($stmt->errorInfo()) . PHP_EOL, 3, $error_log_file);
}

パフォーマンス確認

$ ab -n 5000 -c 50 http://{VM}/index_anti.php
Time taken for tests:   90.504 seconds
Requests per second:    55.25 [#/sec] (mean)
Time per request:       905.045 [ms] (mean)