footmark

ひよっこエンジニアの足跡

MySQLの基礎を勉強してみた3

トリガ

トリガとは

テーブルに対して INSERT, UPDATE, DELETE などのコマンドが実行されたとき、それを引き金に特定のコマンドを実行できる機能。

トリガのコマンドを実行するタイミング

コマンドを実行するタイミングはそのきっかけとなるコマンドの実行直前と実行直後から選択する。

BEFORE きっかけとなるコマンドが実行される直前
AFTER きっかけとなるコマンドが実行される直後

カラムの値

また、きっかけとなるコマンドの実行直前の値と実行直後の値も任意のカラムのものを取得できる。

OLD.カラム名 きっかけとなるコマンドが実行される直前の「カラム名」の値
AFTER.カラム名 きっかけとなるコマンドが実行された直後の「カラム名」の値

メリット

例えば INSERT が実行されたとき、同じデータを別のテーブルに INSERT するよう設定しておけば、自動でバックアップを作成することができます。

トリガの作成

CREATE TRIGGER トリガ名 BEFORE(あるいはAFTER)DELETEなどのコマンド
ON テーブル名 FOR EACH ROW
BEGIN
更新前(OLD.カラム名)または更新後(NEW.カラム名)を使った処理
END

トリガのコマンドを記述する際はデリミタとして「;」を付けるので、ストアドプロシージャのときと同様に

DELIMITER //

などの処理をする必要があります。

トランザクション

ストレージエンジンとトランザクション

MySQLにおいて、検索やファイル操作などの処理を行う部分がストレージエンジンです。
MySQLにおける代表的なストレージエンジンとその特徴は以下の通りです。

ストレージエンジン 特徴
MyISAM 高速、デフォルト
InnoDB トランザクションに有効、MyISAMより低速

ストレージエンジン選択の基準として、トランザクションを使うならInnoDB、使わなければMyISAMとしても良い。

トランザクションとは

複数の機能をまとめて扱う機能をトランザクションといいます。トランザクションを始めてから、結果をデータベースに反映させることをコミット、また反映しないで元に戻すことをロールバックといいます。

トランザクションの使用

トランザクション使用の流れは次の通り。

# トランザクションの開始
mysql> START TRANSACTION;

# コマンドを実行
mysql> DELETE FROM テーブル名;

# コミット
mysql> COMMIT;

コマンド実行時にエラーが生じたなど、実行結果を反映せずにトランザクション開始前の状態に戻したい場合は ROLLBACK を使います。

# トランザクションの開始
mysql> START TRANSACTION;

# コマンドを実行
mysql> DELETE FROM テーブル名;

(エラーが生じた)

# ロールバック
mysql> ROLLBACK;

コミット前のテーブル

トランザクションを開始し、コマンドを実行してからコミットするまでの間は、トランザクションを実行したMySQLモニタでは実行結果が反映され、それ以外のMySQLモニタでは反映されていない状態になります。
先の例では

# トランザクションの開始
mysql> START TRANSACTION;

# コマンドを実行
mysql> DELETE FROM テーブル名;

# 実行結果の確認
mysql> SELECT * FROM 削除したテーブル名;
Empty set (0.00 sec)

(トランザクションを実行したMySQLモニタ以外ではテーブルが存在する)

# コミット
mysql> COMMIT;

(全てのMySQLモニタでテーブルが削除される)

まとめ

本シリーズはここまでです!
機能系は知らないの多かったなー。
勉強すればするほどシステム組むときのMySQLの裁量が大きくなりますね。

MySQLの基礎を勉強してみた

実践ハイパフォーマンスMySQL 第3版を読むに当たって、基礎を復習しといた方がいいだろう(触って覚えたので勉強らしい勉強はしてこなかった)と基礎からのMySQL [基礎からのシリーズ] (プログラマの種シリーズ)を読んでみました。

その中から自分が知らなかったことを連々と書いていきます。

続きを読む

焼肉軍団に入団して4ヶ月が経ちました

初回記事(焼肉軍団に混ぜてもらいました - footmark)で

「成長したい」

これに尽きます。

目下、夏までにどれだけ成長できるか。

って書きましたが、気付けばその夏です!
8月に入ったことですし、この4ヶ月振り返ってみます。

続きを読む

【MySQL】オンメモリで動かしてみる2

前回(【MySQL】オンメモリで動かしてみる - footmark)の結果に差が出なかったことに関して、SQL文に問題があったのではと思い、INNER JOIN を使ったSQL文を用意していろいろ弄ってみました。
話が前後しますが、前回のプログラムではwhere句を用いたselect文しか使っていないので、少し処理を重くしてみようということです。

とりあえず実行。

# MyISAM
mysql> select * from doc_word_table inner join word_table on doc_word_table.word_id = word_table.word_id limit 100000;
100000 rows in set (0.38 sec)

# MEMORY
mysql> select * from doc_word_table_memory inner join word_table_memory on doc_word_table_memory.word_id = word_table_memory.word_id limit 100000;
100000 rows in set (5.70 sec)

MEMORYストレージエンジン側がめっちゃ遅い!なんで!?
explain句を使って内部でどんな処理をしているのか見てみます。

# MyISAM
mysql> explain select * from doc_word_table inner join word_table on doc_word_table.word_id = word_table.word_id;
+----+-------------+----------------+--------+---------------+---------+---------+-------------------------------+----------+-------+
| id | select_type | table          | type   | possible_keys | key     | key_len | ref                           | rows     | Extra |
+----+-------------+----------------+--------+---------------+---------+---------+-------------------------------+----------+-------+
|  1 | SIMPLE      | doc_word_table | ALL    | NULL          | NULL    | NULL    | NULL                          | 16358934 |       |
|  1 | SIMPLE      | word_table     | eq_ref | PRIMARY       | PRIMARY | 4       | db.doc_word_table.word_id |        1 |       |
+----+-------------+----------------+--------+---------------+---------+---------+-------------------------------+----------+-------+
2 rows in set (0.00 sec)

# MEMORY
mysql> explain select * from doc_word_table_memory inner join word_table_memory on doc_word_table_memory.word_id = word_table_memory.word_id;
+----+-------------+-----------------------+------+---------------+------+---------+------+----------+--------------------------------+
| id | select_type | table                 | type | possible_keys | key  | key_len | ref  | rows     | Extra                          |
+----+-------------+-----------------------+------+---------------+------+---------+------+----------+--------------------------------+
|  1 | SIMPLE      | word_table_memory     | ALL  | NULL          | NULL | NULL    | NULL |    68058 |                                |
|  1 | SIMPLE      | doc_word_table_memory | ALL  | NULL          | NULL | NULL    | NULL | 16358934 | Using where; Using join buffer |
+----+-------------+-----------------------+------+---------------+------+---------+------+----------+--------------------------------+
2 rows in set (0.00 sec)

MEMORYストレージエンジン側は word_table に key を使っていないので毎回全検索をしていますね。
どうやらここがネックっぽいです。
というかそもそもなんで key 使ってないんだ?設定されていない?
確認してみます。

# MyISAM
mysql> show columns from doc_word_table;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| doc_word_id | int(11) | NO   | PRI | NULL    | auto_increment |
| doc_id   | int(11) | NO   | MUL | NULL    |                |
| word_id     | int(11) | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

# MEMORY
mysql> show columns from doc_word_table_memory;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| doc_word_id | int(11) | NO   |     | 0       |       |
| doc_id   | int(11) | NO   | MUL | NULL    |       |
| word_id     | int(11) | NO   |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

あれ、主キーが設定されてない。
なるほど、前回MyISAMストレージエンジンのテーブルからコピーしてくるときに設定し忘れていたようです。
というわけで、主キーを貼る。

mysql> alter table doc_word_table_memory add primary key (doc_word_id);
Query OK, 16358934 rows affected (21.18 sec)
Records: 16358934  Duplicates: 0  Warnings: 0

再びexplain句で調べてみます。

mysql> explain select * from doc_word_table_memory inner join word_table_memory on doc_word_table_memory.word_id = word_table_memory.word_id;
+----+-------------+-----------------------+--------+---------------+---------+---------+--------------------------------------+----------+-------+
| id | select_type | table                 | type   | possible_keys | key     | key_len | ref                                  | rows     | Extra |
+----+-------------+-----------------------+--------+---------------+---------+---------+--------------------------------------+----------+-------+
|  1 | SIMPLE      | doc_word_table_memory | ALL    | NULL          | NULL    | NULL    | NULL                                 | 16358934 |       |
|  1 | SIMPLE      | word_table_memory     | eq_ref | PRIMARY       | PRIMARY | 4       | db.doc_word_table_memory.word_id |        1 |       |
+----+-------------+-----------------------+--------+---------------+---------+---------+--------------------------------------+----------+-------+
2 rows in set (0.00 sec)

MyISAMストレージエンジンのそれと同じになりました。
改めてSQL文を打ってみます。

mysql> select * from doc_word_table_memory inner join word_table_memory on doc_word_table_memory.word_id = word_table_memory.word_id limit 100000;
100000 rows in set (0.07 sec)

はええ。
やっぱりMEMORYストレージエンジンさんは速かったんや。

(前回の結果が同じだったのも主キーを貼り忘れたせいじゃね?と思い、前回のプログラムを再実行してみたのですが、結果は変わらずでした。)

考察

前回のプログラム中のSQL文はもともと処理が軽く、メモリ云々では速くしようがない、というのが僕の考察です。

ちなみに、

MyISAM : インデックスはMySQLがキャッシュ管理する(key_buffer_sizeで大きさを設定)。データはOSのキャッシュ機構におまかせ

MyISAMを使っている時key_buffer_sizeは大きくし過ぎてもいけない | b.l0g.jp

なる仕様だそうで、プラス、動かした環境はメモリを128GB積んでいるサーバだったこともあり、差が出なかったのにはOSの仕事っぷりも一枚噛んでいるかもしれません。

【MySQL】オンメモリで動かしてみる

前回(【MySQL】インデックスを貼ったら32.2倍で動いた - footmark)に引き続き、MySQLのチューニング(?)ネタです。

同じく、MySQL上の単語の情報が格納されているテーブル word_table と、どの単語がどの文書に出現するかを表す情報が格納されているテーブル doc_word_table を使って、キーワードを入力するとそのキーワードが各文書にどれだけ含まれているかを計算する処理の高速化を目指します。

今回のベースラインはこちら。
(timeコマンドで計測)

real 6m41.622s
user 2m49.500s
sys 0m50.541s

real, user, sys はそれぞれ、

  • real=user + sys + その他(IOなど)
  • user=プログラム自信の処理時間
  • sys=OSの処理時間

なので、MySQLからの読み込みがネックになっていると予想できます。
ということは、MySQLをオンメモリで動かせれば高速化できるかも!

MEMORYストレージエンジンを使う

MEMORYストレージエンジンはメモリ上に情報を格納するテーブルです。

CREATE TABLE テーブル名(カラム名 型) ENGINE=MEMORY;

今回はベースラインのテーブルから作ります。

CREATE TABLE doc_word_table_memory ENGINE=MEMORY (SELECT * FROM doc_word_table);

で作成できます。
が、ここでエラー。
メモリ上に作成できるテーブルサイズの上限を超えていると言われました。
(スペックの限界ではなく設定の)

メモリ上に作成できるテーブルサイズの上限を増やす

SHOW VARIABLES コマンドで設定を確認してみます。

mysql> SHOW VARIABLES LIKE 'tmp_table%';
+----------------+------------+
| Variable_name  | Value      |
+----------------+------------+
| tmp_table_size | 16777216 |
+----------------+------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_heap%';
+---------------------+------------+
| Variable_name       | Value      |
+---------------------+------------+
| max_heap_table_size | 16777216 |
+---------------------+------------+
1 row in set (0.00 sec)

どちらも16MBになっていますね。

ベースラインのテーブルサイズを確認して、どのくらいの容量が必要なのか調べてみます。

sizeはMB表示

mysql> SELECT table_name, FLOOR((data_length+index_length)/1024/1024) AS size FROM information_schema.tables WHERE table_schema=database();
+----------------------+------+
| table_name           | size |
+----------------------+------+
| all_word_table       |  690 |
| word_table           |    5 |
(略)
+----------------------+------+

1GBくらい確保してやれば良さそうです。
SETコマンドで設定を変更します。

mysql> SET tmp_table_size=1*1024*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SET max_heap_table_size=1*1024*1024*1024;
Query OK, 0 rows affected (0.00 sec)

MEMORYストレージエンジンを使う(2回目)

気を取り直して、

mysql> CREATE TABLE doc_word_table_memory ENGINE=MEMORY (SELECT * FROM doc_word_table);
Query OK, 16358934 rows affected (4.13 sec)
Records: 16358934  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE word_table_memory ENGINE=MEMORY (SELECT * FROM word_table);
Query OK, 68058 rows affected (0.06 sec)
Records: 68058  Duplicates: 0  Warnings: 0

無事作成できました。
インデックスも貼ります。
(ベースラインにも貼ってあります。)

mysql> CREATE INDEX index_surface ON word_table_memory(surface);
Query OK, 68058 rows affected (0.05 sec)
Records: 68058  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX index_doc_id_word_id ON doc_word_table_memory(doc_id, word_id);
Query OK, 16358934 rows affected (9.76 sec)
Records: 16358934  Duplicates: 0  Warnings: 0

(実は前回と比べてかなりデータ量増えてます)

チューニング結果

なんかめっちゃ速くなりそうな気がしますね!

どん!

real 6m32.481s
user 2m51.671s
sys 0m48.149s

あれ?

変わらない?

使い方が間違っているのか、最初の仮説が間違っていたのか。
次回までに要検証です。

【MySQL】インデックスを貼ったら32.2倍で動いた

レコードを数えたり、重複を削除したりと便利なMySQLですが、思ったほど速度が出ない状況があります。
そんなときのKVSか!と思ったのですが、インデックス周りを見直すだけでもかなりチューニングできそうなので検証してみました。

テストデータ

処理

キーワードが与えられると、そのキーワードが各文書にどれだけ含まれているかを返す処理を想定します。

テーブル構造

文書と単語の中間テーブル

doc_word_id doc_id word_id
1 1 1
2 1 2
3 1 3
4 2 4
5 2 2


単語テーブル

word_id surface pos
1 名詞
2 名前 名詞
3 鈴木 名詞
4 趣味 名詞
5 ギター 名詞


中間テーブルのレコード数

count(doc_word_id)
143317


単語テーブルのレコード数

count(word_id)
4792

検証

上記の処理をするプログラムをPythonで書いて、time コマンドで実行時間を測ってみます。

結果はこんな感じになりました。
(realのみ掲載)

番号 index 実行時間
1 なし 5m21.873s
2 doc_id 2m55.429s
3 surface 2m30.906s
4 doc_id, surface 0m57.415s
5 複合(doc_id, word_id) 1m14.861s
6 複合(doc_id, word_id), surface 0m10.287s

6番めっちゃ速い!
複合キー万歳ですね。

もう1点、

MySQLでは、1つのクエリを実行するとき、1つのテーブルにつき1つのインデックスしか仕様できないのである。」

実践ハイパフォーマンスMySQL P66


とありますが、内部結合したときは結合後で1つのテーブルとして扱われるのかも気になっていました。
2番、3番と比べて4番が早くなっていることから結合前のテーブル数でカウントされている(結合後で1つのテーブルとして扱われていない)ようですね。

まとめ

  • 複合キー速い!
  • 内部結合するときはそれぞれのテーブルにインデックスを貼ると速い!