[前][次][番号順一覧][スレッド一覧]

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  モメリー=4G OS=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] 処理速度が遅い