mysql:16326
From: Masaki Miyata <Masaki Miyata <catlover@xxxxxxxxxx>>
Date: Sat, 30 Apr 2016 10:39:59 +0900
Subject: [mysql 16326] Re: [mysql 16325] Re: [mysql 16324] 処理速度が遅い
こんにちは。 宮田と申します。 頭の体操にちょっとやってみました。 そのSQLで求めるなら、 最低限、「keycode, hizuke」の複合インデックスが必要ですね。 2013/1/1 から日次でサンプルデータを作って試してみました。 WindowsのノートPC、Core i5-4200Uメモリ 8GB、MySQLのメモリーチューニング無しの状態で、 1日分で5〜10秒くらい。 まぁ、自己結合で日付を最大1年とはいえクロス結合するので、 1日分で73万件（2000*365）処理することになるので、妥当じゃないでしょうか。 過去分が変わらないのであれば、1日分をサマリーテーブルに入れていけばよいです。 -- ■日次のサマリ作成 insert into tb_sum (hizuke, keycode, 7d_1_HEIKIN, 7d_2_HEIKIN, 31d_1_HEIKIN, 31d_2_HEIKIN, 180d_1_HEIKIN, 180d_2_HEIKIN, 365d_1_HEIKIN, 365d_2_HEIKIN) select A.hizuke, A.keycode, avg(case when B.hizuke >= date_add(A.hizuke, interval -7 day) then B.num1 else null end ) as '7d_1_HEIKIN', avg(case when B.hizuke >= date_add(A.hizuke, interval -7 day) then B.num2 else null end ) as '7d_2_HEIKIN', avg(case when B.hizuke >= date_add(A.hizuke, interval -31 day) then B.num1 else null end ) as '31d_1_HEIKIN', avg(case when B.hizuke >= date_add(A.hizuke, interval -31 day) then B.num2 else null end ) as '31d_2_HEIKIN', avg(case when B.hizuke >= date_add(A.hizuke, interval -150 day) then B.num1 else null end ) as '180d_1_HEIKIN', avg(case when B.hizuke >= date_add(A.hizuke, interval -150 day) then B.num2 else null end ) as '180d_2_HEIKIN', avg(case when B.hizuke >= date_add(A.hizuke, interval -365 day) then B.num1 else null end ) as '365d_1_HEIKIN', avg(case when B.hizuke >= date_add(A.hizuke, interval -365 day) then B.num2 else null end ) as '365d_2_HEIKIN' from tb A, tb B where A.keycode = B.keycode and A.hizuke = '2016-04-29' and B.hizuke between date_add(A.hizuke, interval -365 day) and A.hizuke group by A.keycode, A.hizuke; -- ■テーブルやテストデータ作成のプロシージャなど -- phpMyAdmin SQL Dump -- version 4.2.7.1 -- http://www.phpmyadmin.net -- -- サーバのバージョン： 5.6.20 -- PHP Version: 5.5.15 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; -- -- Database: 'test1' -- DELIMITER $$ -- -- プロシージャ -- CREATE DEFINER=`root`@`localhost` PROCEDURE `ins`() NO SQL BEGIN START TRANSACTION; SET @k = 1; SET @d = '2013-01-01'; WHILE '2016-04-29' >= @d DO WHILE 2001 > @k DO insert into tb (`keycode`,`hizuke`,`num1`,`num2`) values( @k, @d, CEIL(RAND() * 1000), CEIL(RAND() * 2000)); SET @k = @k +1; END WHILE; SET @k = 1; SET @d = date_add(@d, interval +1 day); END WHILE; COMMIT; END$$ DELIMITER ; -- -------------------------------------------------------- -- -- テーブルの構造 'tb' -- CREATE TABLE IF NOT EXISTS tb ( id int(10) unsigned NOT NULL, keycode int(10) unsigned NOT NULL, hizuke date NOT NULL, num1 int(10) unsigned NOT NULL, num2 int(10) unsigned NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2778524 ; -- -------------------------------------------------------- -- -- テーブルの構造 'tb_sum' -- CREATE TABLE IF NOT EXISTS tb_sum ( id int(10) unsigned NOT NULL, keycode int(10) unsigned NOT NULL, hizuke date NOT NULL, 7d_1_HEIKIN double unsigned NOT NULL, 7d_2_HEIKIN double unsigned NOT NULL, 31d_1_HEIKIN double unsigned NOT NULL, 31d_2_HEIKIN double unsigned NOT NULL, 180d_1_HEIKIN double unsigned NOT NULL, 180d_2_HEIKIN double unsigned NOT NULL, 365d_1_HEIKIN double unsigned NOT NULL, 365d_2_HEIKIN double unsigned NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2048 ; -- -- Indexes for dumped tables -- -- -- Indexes for table tb -- ALTER TABLE tb ADD PRIMARY KEY (keycode,hizuke), ADD UNIQUE KEY id (id), ADD UNIQUE KEY uq (hizuke,keycode); -- -- Indexes for table tb_sum -- ALTER TABLE tb_sum ADD PRIMARY KEY (keycode,hizuke), ADD UNIQUE KEY id (id), ADD UNIQUE KEY uq (hizuke,keycode); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table tb -- ALTER TABLE tb MODIFY id int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2778524; -- -- AUTO_INCREMENT for table tb_sum -- ALTER TABLE tb_sum MODIFY id int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2048; 宮田 昌紀 catlover@xxxxxxxxxx 2016年4月30日 0:13 HIRATSUKA Sadao <sh2@xxxxxxxxxx>: > ユメ吉さん > こんばんは、平塚といいます。 > > なんとなくですが、株式などの市場データでしょうか。 > 数値1が最高値、数値2が最安値、それで直近1週間、1か月、 > 半年(180日ではなくて？)、1年の移動平均を出したい、 > ということでイメージ合うでしょうか。 > > この、移動平均をかなりの力技で計算しているので、 > MySQL的につらいことになっていると思われます。 > > EXPLAINを見てインデックスを追加するというMySQLの技術力よりも、 > RDBMSで移動平均をどうやって計算すればよいのだろう、 > といったところに工夫が求められる感じです。 > > それから、 > 過去にさかのぼって数値1、数値2が変化することはありますか？ > もし市場データでしたら過去のデータは変わらないですよね。 > > 4か月分の計算に96分かかったのことですが、前日までのデータは > 1回計算すればそれを記録しておけばよい、といったことはないでしょうか。 > > 昨日までのデータがすでに計算してあるのならば、 > 今日の分を9.6秒かけて計算して、末尾に追加するだけ、 > といった運用ができるかもしれませんね。 > > とはいえMySQLの技術力でどうにかねじ伏せられるかどうかについては > 思いついたらメールしてみようと思います。 > ＃今日は思いつきませんでした(^^; > > 2016年4月29日 23:28 ユメ吉 <yume_tana@xxxxxxxxxx>: > > MySQLをはじめて1ヶ月の初心者です。以前、他のRDBを少しかじった程度ですが > > 本格的にものにしようと勉強中です。 > > ところが、MySQLの処理速度が遅いので困惑しており、メールをしたところです。 > > > > テーブルは日付、 keycode、数値1、数値2と簡単ですが、データ量は毎日取込んでおりで > > keycodeが2000種程になるので今では200万件を超えております。 > > 今回MySQLに移植しなおして、ためしにselect * from テーブル名(tb)de開けたところ3.2秒かかりました。 > > まだインデックスは付けていませんが、 keycodeだけ付けましたが2.2秒とあまり変わりません。 > > その時、EXPLAINを見ましたが type = ALL Key=NULL Extra = NULL になっていました。 > > 今回は自己結合を使って日付、 keycodeごとに1週間分、1か月分、半年分、一年分の平均を算出していきたく > > クエリーを作りました。 A.日付 >= '2016-04-29' にして直近だけで9.6秒かかりました。 > > ちなみに今年から（A.日付 >= '2016-01-01' )では 96分かかっていました。本当は2年分程を5〜6分以内に > > できないか思っております。 > > この時の条件は 日付とkeycodeを単独でインデックスを作りました。 > > explain は tb A type = range ,key=日付 ,extra =Using index condition; > Using > > MRR; Using temporary; Using filesort > > tb B type = ref ,key=keycode ,extra =Using where > > MYSQLのVer=5.7 モメリー＝4Ｇ ＯＳ=WIN7pro > > explainは初めてで良くわかりませんが、速度アップのためのご指導頂けないでしょうか。 > > *************************************************************** > > select > > A.hizuke as "HIZUKE" , > > A.keycode as "KEYCODE", > > avg(case when B.日付 >= date_add(A.日付,interval -7 day) then B.数値1 > else > > null end ) as "7d_1_HEIKIN" , > > avg(case when B.日付 >= date_add(A.日付,interval -7 day) then B.数値2 > else > > null end ) as "7d_2_HEIKIN" , > > avg(case when B.日付 >= date_add(A.日付,interval -31 day) then B.数値1 > else > > null end ) as "31d_1_HEIKIN" , > > avg(case when B.日付 >= date_add(A.日付,interval -31 day) then B.数値2 > else > > null end ) as "31d_2_HEIKIN" , > > avg(case when B.日付 >= date_add(A.日付,interval -150 day) then B.数値1 > else > > null end ) as "180d_1_HEIKIN" , > > avg(case when B.日付 >= date_add(A.日付,interval -150 day) then B.数値2 > else > > null end ) as "180d_2_HEIKIN" , > > avg(case when B.日付 >= date_add(A.日付,interval -365 day) then B.数値1 > > else null end ) as "365d_1_HEIKIN" , > > avg(case when B.日付 >= date_add(A.日付,interval -365 day) then B.数値2 > > else null end ) as "365d_2_HEIKIN" , > > from tb A , tb B > > > > where A.keycode = B.keycode > > and A.日付 >= '2016-04-29' > > and B.日付 between date_add(A.日付,interval -365 day) and A.日付 > > > > group by A.keycode ,A.日付 > > > > ****************************************************************** > > > > -- > 平塚貞夫 <sh2@xxxxxxxxxx> >
@ 16324 2016-04-29 23:28 [ユメ吉 <yume_tana@xx] 処理速度が遅い 16325 2016-04-30 00:13 ┗[HIRATSUKA Sadao <sh2] Re: [mysql 16324] 処理速度が遅い -> @ 16326 2016-04-30 10:39 ┗[Masaki Miyata <catlo] Re: [mysql 16325] Re: [mysql 16324] 処理速度が遅い @ 16327 2016-04-30 14:10 ┗["yoku ts." <yoku0825] Re: [mysql 16326] Re: [mysql 16325] Re: [mysql 16324] 処理速度が遅い @ 16328 2016-05-01 09:25 ┗[Masaki Miyata <catlo] Re: [mysql 16327] Re: [mysql 16326] Re: [mysql 16325] Re: [mysql 16324] 処理速度が遅い @ 16329 2016-05-01 11:50 ┗[ユメ吉 <yume_tana@xx] Re: [mysql 16328] Re: [mysql 16327] Re: [mysql 16326] Re: [mysql 16325] Re: [mysql 16324] 処理速度が遅い