MySQL 入門クエリ計画編 #MySQL

これは何

ありがたいことに以前書いたMySQL 入門にいいねしてくれた方々がいるようです。たぶん彼らはいずれ「日を追うごとにクエリが遅くなる問題」について悩むと思うので何となく書き記しておきます。

対象読者

MySQL が遅いと顧客になじられている人
MySQL が遅くて早く家に帰れない人

MySQL が遅い原因がクエリだった場合、この記事を読めばなんとかなるかもしれません。もしかすると。

あらすじ

昔とある現場の新人プログラマが日を追うごとに MySQL が遅くなってしまい困っていた。
原因を調べたいがどこから手をつければいいのか分からない。

とりあえずクエリを疑ってみる

MySQL が遅い理由というのはいくつかあると思いますが、データ量が増えると MySQL からのレスポンスが遅くなるのは自然なことです。

例えばデータ量に対してハードウェアのスペックが足りないのか、無駄なレコードも走査していて遅くなっているのか、など。

私の経験だとボトルネックがクエリになっている可能性が高いと思います。というわけでまずはクエリが問題なのかそうでないのかを確認したいと思います。

テストデータを用意して調べる

昔は HDD だったので少しでもデータが増えるとインデックスの効果が絶大だったのですが最近の PC はだいたい SSD を使っていると思います。

そうすると手元で演習するために大量のデータを用意する必要があります。というわけで以下のようすると大量のデータを用意する事ができます。

テーブルを用意

mysql> use test;

mysql> CREATE TABLE `product` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) NOT NULL COMMENT '製品名',
  `type` int(1) unsigned NOT NULL COMMENT 'type_id',
  `delete_fg` int(1) unsigned NOT NULL COMMENT '0:表示, 1:削除',
  PRIMARY KEY (`id`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='製品テーブル';

テストデータを用意します。以下の例ではテストデータを作るために(unionする)一時的に適当なテーブルを作成しています。

mysql> create table a select 1 union select 2 union select 3 union select 4;
mysql> SET @id := 0;

mysql> insert into product select @id := @id + 1 id , 'xxx' name, type, round(rand() * 0.51) delete_flag from (select floor(rand() * 2 +1) type from a a1, a a2, a a3, a a4, a a5, a a6, a a7, a a8, a a9, a a10) dat;

というわけで 100万件のテストデータが入りました。

mysql> select count(*) from product;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.51 sec)

今回 union 使っていますが正直簡単なスクリプト書いてデータを用意したほうが
簡単かも。 union で何やっているか知りたい人は下記を参照してください。

いきなり本番環境のクエリを変えるわけにはいかないのでとりあえず手元で実際に試せるようにしましょう。

explain 句を使って調べる

先ほどの方法だと table が一個だけなので再現しやすいのですが実務だとテーブルが激しくリレーションしていて再現するのも大変だったりすると思います。

そのような場合もあると思いますので今度は explain 句を使いましょう。そうすると MySQL がクエリが求めている結果を理解して、どのようにすれば低コストで実行できるのか、その計画を見る事ができます。

mysql> EXPLAIN SELECT count(*) FROM product WHERE type = '1' AND delete_fg = '0'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: product
   partitions: NULL
         type: ref
possible_keys: type
          key: type
      key_len: 4
          ref: const
         rows: 523380
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

たとえあなたがこれ見て分からなくても show create table product; の結果と上記のクエリ計画を見た他のエンジニアが問題点に気づくかもしれません。

解決に挑む

とりあえずdelete_fg があやしいのでwhere句からはずしてみましょう。そうすると計画が少し変わります。

mysql> EXPLAIN SELECT count(*) FROM product WHERE type = '1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: product
   partitions: NULL
         type: ref
possible_keys: type
          key: type
      key_len: 4
          ref: const
         rows: 523380
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Extra: Using where から Using index に変わりました。実際に実行すると高速化しています。結果が出るまで2.71秒かかっていたのが0.12秒に短縮されました。

mysql> SELECT SQL_NO_CACHE count(*) FROM product WHERE type = '1' AND delete_fg = '0'\G
*************************** 1. row ***************************
count(*): 513419
1 row in set, 1 warning (2.71 sec)

mysql> SELECT SQL_NO_CACHE count(*) FROM product WHERE type = '1'\G
*************************** 1. row ***************************
count(*): 523807
1 row in set, 1 warning (0.12 sec)

ということでインデックスを作成しなおします。

mysql> alter table product drop index type;
mysql> alter table product add index idx_type_delete_fg(type, delete_fg);

計画がいい感じになりました。

mysql> EXPLAIN SELECT count(*) FROM product WHERE type = '1' AND delete_fg = '0'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: product
   partitions: NULL
         type: ref
possible_keys: idx_type_delete_fg
          key: idx_type_delete_fg
      key_len: 8
          ref: const,const
         rows: 523380
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

実行結果も早くなりました。成功です。

mysql> SELECT SQL_NO_CACHE count(*) FROM product WHERE type = '1' AND delete_fg = '0'\G
*************************** 1. row ***************************
count(*): 513419
1 row in set, 1 warning (0.12 sec)

まとめ

というわけで MySQL を使っていて月日が流れると「クエリが遅くなる」という問題は割とよく起きます。開発していた時はデータ量が少なかったので問題が表面化しないのですが、データが増えるとよくこういう事がおきます。

インデックスがどうなっていて、どうして早くなったのか、という事を理解するのも大事だと思いますが、とりあえずはこのようにして問題を解決する事ができる可能性がある、という点を頭の片隅に残しておけばいいと思います。

MySQL 入門 クエリ計画編