日々の覚書: MySQLのCOUNTを速くする（？）SQL1本ノックその1

この記事は MySQL Casual Advent Calendar 2016 の19日目の記事です。

「MySQLのCOUNTが遅いなー」って言うと、「トリガーでサマリーテーブル作ったら？」と言われること、多くありませんか？特に @soudai1025 に言われる率が高い気がしています（個人の感想です）

個人的にはトリガーよりは同じトランザクションの中でカウンターテーブル（サマリーテーブルと違うもののつもりで言ってるけど、世の中でいうサマリーテーブルの中にはこの形式が含まれるかも知れない）をUPDATEする方が好き（だって、トリガーはブラックボックスになるんだもの）なんですが、頭の体操ということでトリガーで組んでみましょう。

サンプルテーブルはこんな感じ。

mysql57> SHOW CREATE TABLE game_score\G
*************************** 1. row ***************************
       Table: game_score
Create Table: CREATE TABLE `game_score` (
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `play_end_time` datetime NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`seq`),
  KEY `game_id` (`game_id`),
  KEY `play_end_time` (`play_end_time`)
) ENGINE=InnoDB AUTO_INCREMENT=300001 DEFAULT CHARSET=utf8mb4


mysql57> SELECT * FROM game_score LIMIT 3;
+-----+---------+---------+---------------------+--------+
| seq | game_id | user_id | play_end_time       | score  |
+-----+---------+---------+---------------------+--------+
|   1 |       9 |    2445 | 2016-12-19 11:18:32 | 981760 |
|   2 |       9 |    1440 | 2016-12-19 11:18:33 | 108172 |
|   3 |       6 |    7879 | 2016-12-19 11:18:34 | 180756 |
+-----+---------+---------+---------------------+--------+
3 rows in set (0.00 sec)

こんなテーブルの中から、「今日、各ゲームが何回プレイされたか」を抽出するクエリーは

mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/19' AND '2016/12/20' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     4610 |
|       1 |     4751 |
|       2 |     4532 |
|       3 |     4470 |
|       4 |     4581 |
|       5 |     4534 |
|       6 |     4523 |
|       7 |     4472 |
|       8 |     4583 |
|       9 |     4633 |
+---------+----------+
10 rows in set (0.37 sec)

おそらくこんなん。
死ぬほど遅いのは、GROUP BY狙いのキー(game_id)を使っちゃってるから。WHERE狙いのキー(play_end_time)に変えさせれば100msくらいにはなる。それでも遅いけど。

これをトリガーに突っ込む。日付の条件は日々変わるだろうから、DATE型までサマライズしたカラムも必要かな。あと、game_scoreはログテーブルなのでINSERT以外は走らないものとする。おそらくバッチでDELETEはするだろうけど、それは取り敢えず置いておく（一緒に消せばいいだけなので）
とするとこんな感じだろうか。

mysql57> SHOW CREATE TABLE game_score_summary\G
*************************** 1. row ***************************
       Table: game_score_summary
Create Table: CREATE TABLE `game_score_summary` (
  `game_id` int(11) NOT NULL,
  `play_date` date NOT NULL,
  `count_star` int(10) unsigned NOT NULL,
  PRIMARY KEY (`game_id`,`play_date`),
  CONSTRAINT `game_score_summary_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `game_score` (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary (game_id, play_date, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-19 11:53:52.69
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

INSERT INTO .. ON DUPLICATE KEY UPDATEで「無ければ1、あればインクリメント」にしてる。
30万行くらい（別の日付で）追加で突っ込んでみる。

mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/25' AND '2016/12/26' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     8817 |
|       1 |     8513 |
|       2 |     8655 |
|       3 |     8782 |
|       4 |     8664 |
|       5 |     8643 |
|       6 |     8525 |
|       7 |     8741 |
|       8 |     8633 |
|       9 |     8428 |
+---------+----------+
10 rows in set (1.23 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/25';
+---------+------------+------------+
| game_id | play_date  | count_star |
+---------+------------+------------+
|       0 | 2016-12-25 |       8817 |
|       1 | 2016-12-25 |       8513 |
|       2 | 2016-12-25 |       8655 |
|       3 | 2016-12-25 |       8781 |
|       4 | 2016-12-25 |       8664 |
|       5 | 2016-12-25 |       8643 |
|       6 | 2016-12-25 |       8525 |
|       7 | 2016-12-25 |       8741 |
|       8 | 2016-12-25 |       8633 |
|       9 | 2016-12-25 |       8428 |
+---------+------------+------------+
10 rows in set (0.00 sec)

おっと…play_dateにインデックス張るの忘れてたけど、追加してやればシンプルな10行フェッチで済む（PRIMARY (play_date, game_id) にする方が筋がいいかな）

このトリガーを張った瞬間より前の履歴については整合性が保証できないので、一度更新をブロックしてgame_score_summaryにマージしてやるか、諦めて「明日以降のやつはsummaryから取って、今日以前のやつはgame_scoreから取る」というロジックをアプリケーション側に入れ込むか。

mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/19' AND '2016/12/20' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     4610 |
|       1 |     4751 |
|       2 |     4532 |
|       3 |     4470 |
|       4 |     4581 |
|       5 |     4534 |
|       6 |     4523 |
|       7 |     4472 |
|       8 |     4583 |
|       9 |     4633 |
+---------+----------+
10 rows in set (0.08 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/19';
Empty set (0.00 sec)

mysql57> LOCK TABLE game_score READ, game_score_summary WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql57> INSERT INTO game_score_summary SELECT game_id, MAX(DATE(play_end_time)) AS play_date, COUNT(*) AS count_star FROM game_score WHERE play_end_time < '2016/12/20' GROUP BY game_id;
Query OK, 10 rows affected (0.08 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql57> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/19';
+---------+------------+------------+
| game_id | play_date  | count_star |
+---------+------------+------------+
|       0 | 2016-12-19 |       4610 |
|       1 | 2016-12-19 |       4751 |
|       2 | 2016-12-19 |       4532 |
|       3 | 2016-12-19 |       4470 |
|       4 | 2016-12-19 |       4581 |
|       5 | 2016-12-19 |       4534 |
|       6 | 2016-12-19 |       4522 |
|       7 | 2016-12-19 |       4472 |
|       8 | 2016-12-19 |       4583 |
|       9 | 2016-12-19 |       4633 |
+---------+------------+------------+
10 rows in set (0.00 sec)

LOCK TABLEするならこんな感じで。game_score_summaryだけロックしておけばgame_score側は要らないかも知れない（AFTER INSERTトリガーが止まるから）

実はこのテーブル構造、致命的に性能の欠陥があるので次回に続く。

明日は ogataka50 さんです！

【2016/12/19 14:39】

ちなみに、サンプルデータを作るのに使ったスクリプトはこちらです。
https://gist.github.com/yoku0825/0608014dcfd3e1cd244a51ef7e590ca0

日々の覚書

GA

2016/12/19

MySQLのCOUNTを速くする（？）SQL1本ノックその1

0 件のコメント :

コメントを投稿

GA

2016/12/19

MySQLのCOUNTを速くする（？）SQL1本ノック その1

0 件のコメント :

コメントを投稿

MySQLのCOUNTを速くする（？）SQL1本ノックその1