MySQL: クエリチューニングできますか？

追記あり

2013-02-25 innodb って Permissible Index Types が BTREE だったっていう

あらすじ

流れのフリーランスやってるとよく面接行くはめになるんですが、そうすると大体「クエリチューニングの経験ありますか？」「これまでどういったチューニングしましたか？」とか聞かれます。

いつも思い出すのに苦労するので忘れないように問題にしておきました。

問題

以下のクエリが遅いので何かしらの対処を考えるように、とインフラチームから連絡がありました。何かしらの対処を考えなさい。

なお、type と delete_fg は常に等価であるかどうかのクエリしか発行しないものとする。

SEELECT count(*) FROM product WHERE type = 'N' AND delete_fg = 'N';

現状ではプロダクトテーブルは以下のスキーマとなっています。

mysql> show create table product\G
*************************** 1. row ***************************
       Table: product
Create Table: 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='製品テーブル'
1 row in set (0.00 sec)

まあ状況にもよるんですが、できるだけ簡単な方法でそこそこいい方法を考えましょう。

テスト環境の作成

とりあえず私はSSDのAir Mac を使って、MySQLのバージョンはこれです。

mysql --version
mysql  Ver 14.14 Distrib 5.5.10, for osx10.6 (i386) using  EditLine wrapper

table 作成

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='製品テーブル';

とりあえず適当にテストデータを作成

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;

テストデータ作成完了

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

何やっているか知りたい人は下記を参照してください

http://t100life.blog121.fc2.com/blog-entry-205.html

実行計画を調べる

とりあえず explian してみる

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

とりあえずdelete_fg があやしいのでwhere句からはずす

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

Extra: Using where から Using index に変わりました。

delete_fg はインデックスが貼られていないので、その値が0か1かはインデックス探索だけでは判定できないからです。

ちょっとクエリを投げて検証します。

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

mysql> SELECT SQL_NO_CACHE count(*) FROM product WHERE type = '1'\G
*************************** 1. row ***************************
count(*): 524880
1 row in set (0.37 sec)

という事で delete_fg を何とかすればそこそこいい感じになりそうです。

それでどうする。

こうしてみる

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
         type: ref
possible_keys: idx_type_delete_fg
          key: idx_type_delete_fg
      key_len: 8
          ref: const,const
         rows: 524479
        Extra: Using index
1 row in set (0.00 sec)

お、Extra: Using index となっている。ちょっとcountをとってみましょう。

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

OKェーいい感じ〜♩

USING HASH

そういえば等価比較しかしないんだっけ。USING HASH のほうが速いのかしら

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

どうだろう

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

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

うーん。変わらないなあ。SSDだからかなあ。とりあえず書き込み速度のベンチも必要になるだろうけどBTREE INDEX から HASH INDEX に変更して遅くなるっていう事はないだろうからたぶんこれでいいんじゃないかな。

追記: innodb って Permissible Index Types が BTREE だったっていう

show index from product したら Index_type が BTREE でした。マニュアルよんだら Perlmissible ではなかったっていう。うそつきましたごめんなさい。

まとめ

ということで delete_fg をインデックスに含めるように複合インデックスに貼り直して一件落着。おしまい。

あいつの日誌β

働きながら旅しています。