MySQLでbulk insert + on duplicate key updateしたい

MySQLでINSERT INTO hoge VALUES (...), (...), (...)のbulk insertでon duplicate key update(すでにレコードがあったらUPDATEで上書きする)って併用できるのかな？っていうのが気になったので調べてみたらできるみたい。VALUES関数というその目的のためだけに存在するような関数を使う。

こんな感じでテーブル作って

CREATE TABLE users (
    name VARCHAR(255) NOT NULL,
    age TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (name)
) ENGINE=InnoDB;

データをINSERTして

INSERT INTO users VALUES
  ('akuwano', 25), ('oinume', 24), ('oranie', 23);

SELECT * FROM users;
+---------+-----+
| name    | age |
+---------+-----+
| akuwano |  25 |
| oinume  |  24 |
| oranie  |  23 |
+---------+-----+

INSERT ... ON DUPLICATE KEY UPDATEで重複したレコードがあった場合に全てアップデートされるかな？

INSERT INTO users VALUES
  ('akuwano', 15), ('oinume', 14), ('oranie', 13)
  ON DUPLICATE KEY UPDATE age = VALUES(age);

mysql> SELECT * FROM users;
+---------+-----+
| name    | age |
+---------+-----+
| akuwano |  15 |
| oinume  |  14 |
| oranie  |  13 |
+---------+-----+

された！！

実践ハイパフォーマンスMySQL 第3版

Get auto increment values from MySQL information schema

Information schema is metadata about MySQL. We can obtain useful information from it. For example, I get current auto increment values by following SQL.

SELECT
  t.table_name, t.auto_increment
FROM
  information_schema.tables AS t
WHERE
  t.table_schema = 'your_db'
ORDER BY t.table_name;

+-------------------------------------+----------------+
| table_name                          | auto_increment |
+-------------------------------------+----------------+
| blog_categories                     |              4 |
| blog_posts                          |              4 |
| blog_post_categories                |              4 |
| blog_post_comments                  |              1 |
| images                              |              7 |
+-------------------------------------+----------------+

INFORMATION_SCHEMA database has much more useful tables like COLUMNS, GLOBAL_VARIABLES and so on. It's good to use it when you want to know much about MySQL.

Duplicating a MySQL table schema and data

Duplicate foo table into foo_20150904

/* Create a new table from foo's schema */
> CREATE TABLE foo_20150904 LIKE foo;

/* Insert whole data into a new table */
> INSERT INTO foo_20150904 SELECT * FROM foo;

RDS(MySQL)のバイナリログはすぐ消えるから注意

これ知らないとハマるかもしれないのでメモ。

docs.aws.amazon.com

に"Amazon RDS normally purges a binary log as soon as possible"とある通り、RDSではバイナリログはすぐ消えてしまう。もし自前でスレーブを立てたりするためにバイナリログをある程度とっておきたい場合は、

call mysql.rds_set_configuration('binlog retention hours', 24);

のプロシージャを実行してバイナリログの保持期間を伸ばすことができる。

Amazon Web Services パターン別構築・運用ガイド

MySQL 5.6で本当にオンラインでDDLが実行できるか検証してみた

MySQL 5.6での機能強化点（その1） - パフォーマンスと使い勝手を大きく向上 | Think ITに書いてあるようにMySQL 5.6からオンラインでDDLを実行してもレコードのINSERT, UPDATEはできるようになったとあるので、これが本当なのか検証してみた。MySQLにおいてレコード数の多いテーブルに対するALTER TABLE文の発行は以前から問題視されていて、pt-online-schema-changeみたいなものを駆使するのが常套手段だった。

検証環境

• ConoHa VPS 2GB
• Ubuntu 14.04
• MySQL 5.6.17-0ubuntu0.14.04.1-log
• my.cnf
• データはWikipediaのダンプデータのenwiki-20140502-redirect.sql.gz というテーブルを使用。テーブル定義はこんな感じ。

CREATE TABLE `redirect` (
  `rd_from` int(8) unsigned NOT NULL DEFAULT '0',
  `rd_namespace` int(11) NOT NULL DEFAULT '0',
  `rd_title` varbinary(255) NOT NULL DEFAULT '',
  `rd_interwiki` varbinary(32) DEFAULT NULL,
  `rd_fragment` varbinary(255) DEFAULT NULL,
  PRIMARY KEY (`rd_from`),
  KEY `rd_ns_title` (`rd_namespace`,`rd_title`,`rd_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

• このredirectというテーブルにカラムを追加するDDLを流すと大体1分ぐらいかかる(のでそのすきにINSERTとかを実行してみた)

$ mysql -uroot -e "create database wikipedia"
$ mysql -uroot wikipedia < /tmp/enwiki-20140502-redirect.sql
$ time mysql -uroot wikipedia -e "alter table redirect add column comment varbinary(255) not null default '' "

real    1m5.789s
user    0m0.003s
sys 0m0.002s

カラム追加中にINSERT/UPDATE/DELETE

DDL

$ mysql -uroot wikipedia -e "alter table redirect add column comment varbinary(255) not null default '' "

INSERT

mysql> insert into redirect values (43000000, 1, 'alter test', '', '');
Query OK, 1 row affected (0.04 sec)

UPDATE

mysql> update redirect set rd_namespace=2 where rd_from=43000000;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

DELETE

mysql> delete from redirect where rd_from=43000000;
Query OK, 1 row affected (0.16 sec)

問題なし。ってか最初に紹介したページにはINSERTとUPDATEについてしか記述がなかったけどDELETEもいけた。

カラム削除中にINSERT/UPDATE/DELETE

DDL

$ mysql -uroot wikipedia -e "alter table redirect drop column comment"

INSERT

mysql> insert into redirect values (43000001, 1, 'alter test', '', '', 'test');
Query OK, 1 row affected (0.04 sec)

UPDATE

mysql> update redirect set rd_namespace=2 where rd_from=43000001;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

DELETE

mysql> delete from redirect where rd_from=43000001;
Query OK, 1 row affected (0.10 sec)

問題なし。

インデックス追加中にINSERT

もう面倒なのでINSERTだけ検証。

DDL

$ mysql -uroot wikipedia -e "alter table redirect add index rd_title (rd_title);"

INSERT

mysql> insert into redirect values (43000002, 1, 'alter test', '', '');
Query OK, 1 row affected (0.07 sec)

問題なし

インデックス削除中にINSERT

インデックスの削除が一瞬で終わってしまうため検証できず。

カラムの型変更中にINSERT

これは5.6でもブロックされるはずなのだが気になったのでやってみたところ、やっぱりちゃんとブロックされた。

DDL

$ mysql -uroot wikipedia -e "alter table redirect modify column rd_namespace bigint NOT NULL DEFAULT '0'"

INSERT

insert into redirect values (43000000, 1, 'alter test', '', '');

結論

カラム型の変更以外ではもうpt-online-schema-changeいらないんじゃないかなーと思った。MySQL 5.6はGTIDやパフォーマンスが改善されていないなどでいいイメージがなくあまり使われていない印象なんだけど、こういうところは積極的にアップデートする理由になるのではないかと思った次第。仕事でも4万qpsぐらいクエリが来てるサーバにMySQL 5.6使っていて特に大きな問題は起きてないっす。

実践ハイパフォーマンスMySQL 第3版