MySQL5.5 viewの処理速度改善について

Windows2008 Hyper-VゲストOS上にWindows2008 + MySQL5.5にてDB環境を構築しています。

そのMySQL環境にて下記２つのテーブルからviewテーブルを構築しています。

○テーブル１（1,000行）
CREATE TABLE `testdb`.`mst` (
`KBN` char(10) COLLATE cp932_bin NOT NULL,
`NM` char(48) COLLATE cp932_bin DEFAULT NULL,
`MSTDATE` int(10) DEFAULT NULL,
PRIMARY KEY (`KBN`),
KEY `idx_bookkbn_spdate` (`DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932 COLLATE=cp932_bin;

○テーブル２（145,000行）
CREATE TABLE `testdb`.`dt` (
`KEYID` int(10) NOT NULL,
`KBN` char(10) COLLATE cp932_bin NOT NULL,
`MEMO` varchar(1024) COLLATE cp932_bin DEFAULT NULL,
`SUBC01` char(128) COLLATE cp932_bin DEFAULT NULL,
`SUBC02` char(128) COLLATE cp932_bin DEFAULT NULL,
`SUBN01` int(10) DEFAULT NULL,
`SUBN02` int(10) DEFAULT NULL,
PRIMARY KEY (`KEYID`,`KBN`),
KEY `idx_dt_keyid` (`KEYID`),
) ENGINE=InnoDB DEFAULT CHARSET=cp932 COLLATE=cp932_bin;

○view
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`test`@`%` SQL SECURITY DEFINER VIEW `viewdtmst` AS
select distinct
`dt`.`KEYID` AS KEYID,
`dt`.`KBN` AS KBN,
`dt`.`MEMO` AS MEMO,
`dt`.`SUBC01` AS SUBC01,
`dt`.`SUBC02` AS SUBC02,
`dt`.`SUBN01` AS SUBN01,
`dt`.`SUBN02` AS SUBN02,
`mst`.`NM` AS NM,
`mst`.`MSTDATE` AS MSTDATE,
from (`dt` left join `kmst` on((`dt`.`KBN` = `mst`.`KBN`)));


■ここからご相談となります。
上記viewdtmstに対して下記の様なクエリーを実行すると
処理時間が12.5426sとかなり時間がかかり改善したいと考えています。

--------------------------------------------------------------
検索クエリー
--------------------------------------------------------------
SELECT * FROM viewdtmst
WHERE KEYID = 10010
ORDER BY KEYID,MSTDATE DESC,KBN;

--------------------------------------------------------------
上記クエリーのexplain結果
--------------------------------------------------------------
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'PRIMARY', '<derived2>', 'ALL', '', '', '', '', 143906, 'Using where; Using filesort'
2, 'DERIVED', 'dt', 'ALL', '', '', '', '', 150813, 'Using temporary'
2, 'DERIVED', 'mst', 'eq_ref', 'PRIMARY', 'PRIMARY', '20', 'testdb.dt.KBN', 1, 'Distinct'


viewを介せず直接下記のクエリーを実行すると
処理時間0.0024sで検索結果がもとまります。

--------------------------------------------------------------
viewを使わないクエリー
--------------------------------------------------------------
select distinct
`dt`.`KEYID` AS KEYID,
`dt`.`KBN` AS KBN,
`dt`.`MEMO` AS MEMO,
`dt`.`SUBC01` AS SUBC01,
`dt`.`SUBC02` AS SUBC02,
`dt`.`SUBN01` AS SUBN01,
`dt`.`SUBN02` AS SUBN02,
`mst`.`NM` AS NM,
`mst`.`MSTDATE` AS MSTDATE,
from (`dt` left join `kmst` on((`dt`.`KBN` = `mst`.`KBN`)))
where KEYID = 10010
order by KEYID,MSTDATE DESC,KBN;

--------------------------------------------------------------
viewを使わないクエリーのexplain結果
--------------------------------------------------------------
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'dt', 'ref', 'PRIMARY,idx_dt_keyid', 'PRIMARY', '4', 'const', 10, 'Using temporary; Using filesort'
1, 'SIMPLE', 'mst', 'eq_ref', 'PRIMARY', 'PRIMARY', '20', 'testdb.dt.KBN', 1, ''

なんとかviewを使って処理速度改善を図りたいのですが、ご意見いただけないでしょうか。
よろしくお願いいたします。

No.1 ベストアンサー 回答者： hogya 回答日時： 2012/07/30 22:25

'Using temporary'とUsing filesort'が使われているので、実表に対して大規模なソート（おそらくdt）処理が走っていることが確認できますね。

これはview内でdistinctを使用しているせいだと思いますが、distinctをview内に定義せず、検索クエリー側で指定する訳にはいきませんか？

この回答へのお礼

hogyaさん

貴重な情報ありがとうございます。

教えて頂いたようにview内でdistinctせず、viewテーブル検索側でdistinctするようにし
クエリーを実行した結果、期待するような速度になりました。

--------------------------------------------------------------
view内でdistinctしない場合のクエリーのexplain結果
--------------------------------------------------------------
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'dt', 'ref', 'PRIMARY,idx_dt_keyid', 'PRIMARY', '4', 'const', 10, 'Using temporary; Using filesort'
1, 'SIMPLE', 'mst', 'eq_ref', 'PRIMARY', 'PRIMARY', '20', 'testdb.dt.KBN', 1, ''


この方法で対応を進めたいと思います。

ありがとうございました。

お礼日時：2012/07/31 11:13