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 をインデックスに含めるように複合インデックスに貼り直して一件落着。おしまい。