mysql:16327
From: "yoku ts." <"yoku ts." <yoku0825@xxxxxxxxxx>>
Date: Sat, 30 Apr 2016 14:10:18 +0900
Subject: [mysql 16327] Re: [mysql 16326] Re: [mysql 16325] Re: [mysql 16324] 処理速度が遅い
こんにちは、yoku0825といいます。 これ、元の式が > avg(case when B.日付 >= date_add(A.日付,interval -7 day) then B.数値1 else null end ) as "7d_1_HEIKIN" , と、A.日付で終端されてないので、4/28のレコードを固定化しても4/29以降の値を含んで、固定化難しいんじゃないかなとか思ってました(間違い？) 求められている回答とは違うんですが、InfiniDBでやったらまともな処理速度が出そうだったので、Window関数を持っている他のRDBMSが良いんじゃないかなと思います。 yoku0825, 2016/04/30 10:42 "Masaki Miyata" <catlover@xxxxxxxxxx>: > > こんにちは。 > 宮田と申します。 > > 頭の体操にちょっとやってみました。 > > その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] 処理速度が遅い