2013年09月30日

初期化も更新もまとめて

朝夕が冷えてきましたね。バッド(下向き矢印)
タイラです。

さて前回前々回に引き続き今回もデータベースのお話で、ちょっと便利な
INSERT ... ON DUPLICATE KEY UPDATE構文
をご紹介したいと思います。ひらめき

例として 実行する都度値をインクリメントするような処理 を作ります。

次の順で行うクエリを作ると以下のようになります。
1. 初期化(更新分もついでに行う)
2. 値更新


■テーブル構造 sampleテーブル
+------------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+-------------------+-------+
| id | int(11) | NO | PRI | NULL | |
| count | int(11) | NO | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | |
+------------+-----------+------+-----+-------------------+-------+

■1. 初期化クエリ
INSERT INTO `sample`
(id, count, created_at, updated_at)
VALUES
('1', '1', NOW(), NOW());

■2. 値更新クエリ
UPDATE `sample`
SET
count = count + 1
WHERE
id = 1;


実行する際に正しく順序が守られている場合はよいのですが、
・初期化クエリを2回以上実行する
・初期化クエリを実行せず、更新クエリを実行する
という事が発生した際に正しい結果が得られません。ふらふら

それらの問題を解消するクエリがこちら


INSERT INTO `sample`
(id, count, created_at, updated_at)
VALUES
('1', '1', NOW(), NOW())
ON DUPLICATE KEY UPDATE
count = count + 1;


INSERT構文とUPDATE構文を合わせた
INSERT ... ON DUPLICATE KEY UPDATE構文になります。

簡単な挙動としては
INSERTが実行される前に KEY(=プライマリキー) が重複を確認し
重複がない場合は INSERT が、
重複がある場合は UPDATE が行われます。

初期化クエリ、値更新クエリを置き換えると以下のようになります。


■1. 初期化クエリ
INSERT INTO `sample`
(id, count, created_at, updated_at)
VALUES
('1', '1', NOW(), NOW())
ON DUPLICATE KEY UPDATE
count = count + 1;

■2. 値更新クエリ
INSERT INTO `sample`
(id, count, created_at, updated_at)
VALUES
('1', '1', NOW(), NOW())
ON DUPLICATE KEY UPDATE
count = count + 1;

■結果表示
SELECT * FROM `sample`;
+----+-------+---------------------+---------------------+
| id | count | created_at | updated_at |
+----+-------+---------------------+---------------------+
| 1 | 2 | 2013-09-30 18:00:00 | 2013-09-30 18:00:00 |
+----+-------+---------------------+---------------------+


同じクエリで初期化も更新も出来ました。わーい(嬉しい顔)

今回はcountのインクリメントだけを行っていますが、
UPDATE構文で取り扱える内容は基本的にこの構文でも扱えます。

ではー手(パー)


posted by 管理人 at 18:45 | データベース

2013年08月09日

INSERTはなるべくまとめて

暑いですね。あせあせ(飛び散る汗)
毎年夏がやってくる度「日本暑い〜」とぐったりしてるタイラです。


さて、前回に引き続きデータベースに関するお話で、
 複数のレコードを挿入する際"一度にまとめて"挿入するとどれほど高速化されるのか
というお話です。


データベースを触りたての頃は
 レコード(データ)を何件も挿入する際は1回1回クエリ(要求)を投げていた
なんて事があったかもしれませんが、
1つのクエリでまとめる事で大幅に高速化出来るのでそれを紹介したいと思います。
ちなみに、レコードを1つのクエリでまとめて挿入する事を バルクINSERT といいます。

テーブル構成は前回利用した構成を利用します。


CREATE TABLE IF NOT EXISTS `sample` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` varchar(128) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

+------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| data | varchar(128) | NO | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | |
+------------+--------------+------+-----+-------------------+----------------+


まずは比較の為の1件INSERTするクエリ
10件必要な場合は以下のクエリを10回実行します。


INSERT INTO `sample`
(`data`, `created_at`)
VALUES
('サンプル', NOW());


次に10件をバルクINSERTするクエリ
カンマ(,)区切りで書いていく事で1回のクエリで
まとめて10件、100件、1000件とまとめてINSERT出来ます。
※100件1000件といったクエリは長いので割愛しますね


INSERT INTO `sample`
(`data`, `created_at`)
VALUES
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW());


それぞれのクエリを手元のサーバでテストしてみたところ
時間は以下の通りになりました。


+-------+----------+----------+
| N | 1件xN回 | N件x1回 |
+-------+----------+----------+
| 2 | 0.03sec | 0.01sec |
| 10 | 0.08sec | 0.02sec |
| 100 | 0.64sec | 0.05sec |
| 1000 | 6.21sec | 0.06sec |
| 10000 | 64.88sec | 0.56sec |
+-------+----------+----------+


10件ですら60ms程度の差が出ています。
万単位のレコードになると100倍以上の差に。。。がく〜(落胆した顔)

複数件INSERTする際には一度にまとめて行う方が効率的...と言いますか
まとめないという選択肢はないですよねexclamation&question

INSERTクエリを投げる際には気をつけてください。

それではexclamation




posted by 管理人 at 22:43 | データベース

2013年06月19日

MySQLのクエリだけでダミーデータを大量に挿入してみる

今年の梅雨はどこいったんだと思ってたら
大阪ではここ数日まとまった雨雨になりそうですね。
お久しぶりです。タイラです。

さて今回は珍しくデータベースのお話で
MySQLのクエリだけでダミーデータを大量に挿入する小技を紹介します。ひらめき

以下のようなテーブルに対して10万件のダミーデータを入れてみます。

CREATE TABLE IF NOT EXISTS `sample` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` varchar(128) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

+------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| data | varchar(128) | NO | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | |
+------------+--------------+------+-----+-------------------+----------------+


まず最終データと同じ形式のダミー用テンポラリテーブルを作成します。

CREATE TABLE IF NOT EXISTS `tmp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` varchar(128) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


テンポラリテーブルに対してダミーの元になるデータを入れます。

INSERT INTO `tmp`
(`data`, `created_at`)
VALUES
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW()),
('サンプル', NOW());


ダミーデータを対象となるテーブルへ挿入します。

INSERT INTO `sample`(
SELECT t1.id = NULL, t1.data, t1.created_at, t1.updated_at
FROM (tmp t1, tmp t2, tmp t3, tmp t4, tmp t5)
);


不要になったテンポラリテーブル削除します。

DROP TABLE `tmp`;


これで挿入された件数を確認すると

SELECT COUNT(*) FROM `sample`;

+----------+
| COUNT(*) |
+----------+
| 100000 |
+----------+


今回の場合ですと、元データが10件で5回クロス結合しているので
10万件のレコードが入ります。
挿入時の tmp t1, tmp t2 ... tmp tN と増やすことで
元データのN乗分のデータを作成することが出来ます。

ただしクエリの実行時間やメモリ消費量は一切考慮してないので
億単位のデータを挿入する場合は注意が必要です。


あまり実用性は低いですが、ネタの一つとして覚えておくと良いかもしれません。
それではーexclamation

posted by 管理人 at 16:11 | データベース