MySQLにて、複数Tableを結合して集計するSQL

Question

MySQL(5.5)にて、「商品」「売上」「仕入れ」の3テーブルについて、商品ごとの売上合計、仕入れ合計等を算出したいと考えています。
実現自体は出来ているのですが、処理に時間がかかっているため、もっとよい(高速で正しい値が取れる)方法がないかお知恵を貸していただきたく質問しました。

テーブル構造は以下の通りです。

product(商品)テーブル

id	name	price
1	商品1	100
2	商品2	200
3	商品3	300

sell(売上)テーブル (※is_back=1の場合、返品扱い)
|id|product_id|qty|is_back|datetime|
|:--|:--:|:--:|--:|
|1|1|2|0|2018-10-10 00:00:00|
|2|2|1|0|2018-10-10 00:00:00|
|3|3|3|0|2018-10-10 00:00:00|
|4|1|1|1|2018-10-10 00:00:00|
|5|2|1|1|2018-10-10 00:00:00|
|6|3|1|1|2018-10-10 00:00:00|

buy(仕入れ)テーブル(※is_back=1の場合、返品扱い)
|id|product_id|qty|is_back|datetime|
|:--|:--:|:--:|--:|
|1|1|5|0|2018-10-10 00:00:00|
|2|2|3|0|2018-10-10 00:00:00|
|3|3|10|0|2018-10-10 00:00:00|
|4|1|2|1|2018-10-10 00:00:00|
|5|2|1|1|2018-10-10 00:00:00|
|6|3|8|1|2018-10-10 00:00:00|
|7|1|1|0|2018-10-10 00:00:00|

最初に書いた(間違った)SQL

まず最初に、各TableをproductについてLeft Joinし、合算しました。

select 
    product.id,
    product.price,
    ifnull(sum(sell_out.qty), 0) as summed_sell,
    ifnull(sum(sell_in.qty), 0) as summed_back,
    ifnull(sum(buy_out.qty), 0) as summed_buy,
    ifnull(sum(buy_in.qty), 0) as summed_return,

    /** 実際にはこれらの値も取得しているが、説明の為割愛
    ifnull(sum(sell_out.qty * product.price), 0) as summed_sell_price,
    ifnull(sum(sell_in.qty * product.price), 0) as summed_back_price,
    ifnull(sum(buy_out.qty * product.price), 0) as summed_buy_price,
    ifnull(sum(buy_in.qty * product.price), 0) as summed_return_price
    **/
from
	product
	left join
		sell as sell_out
		on
				sell_out.product_id = product.id
			and	sell_out.is_back = 0
			and	sell_out.`datetime` > '2018-10-01 00:00:00'

	left join
		sell as sell_in
		on
				sell_in.product_id = product.id
			and	sell_in.is_back = 1
			and	sell_in.`datetime` > '2018-10-01 00:00:00'

	left join
		buy as buy_out
		on
				buy_out.product_id = product.id
			and	buy_out.is_back = 1
			and	buy_out.`datetime` > '2018-10-01 00:00:00'

	left join
		buy as buy_in
		on
				buy_in.product_id = product.id
			and	buy_in.is_back = 0
			and	buy_in.`datetime` > '2018-10-01 00:00:00'
group by
	product.id;

このSQLでは、全てLEFT JOINで結合されるため、商品1(product.id=1)は仕入れ(buy)が2件あるため、JOINされたrow数が2つずつになり、結果として売上、返品等の件数がすべて倍になっています。

product.id	product.price	summed_sell	summed_back	summed_buy	summed_return
1	100	4	2	4	6
2	200	1	1	1	3
3	300	3	1	8	10

そこで、JOINする前にGroup Byで集計し、その値を出すようにしまして、正しい値が取れるようになりました。

select 
	product.id,
    product.price,
	sell_out.summed_sell as summed_sell,
	sell_in.summed_back as summed_back,
	buy_out.summed_buy as summed_buy,
	buy_in.summed_return as summed_return,

    /** 実際にはこれらの値も取得しているが、説明の為割愛
	ifnull(sum(sell_out.summed_sell * product.price), 0) as summed_sell_price,
    ifnull(sum(sell_in.summed_back * product.price), 0) as summed_back_price,
	ifnull(sum(buy_out.summed_buy * product.price), 0) as summed_buy_price,
    ifnull(sum(buy_in.summed_return * product.price), 0) as summed_return_price
    **/

from
	product
	left join
		(select
			ifnull(sum(sell.qty), 0) as summed_sell,
			sell.product_id
		from
			sell
		where
				sell.is_back = 0
			and	sell.`datetime` > '2018-10-01 00:00:00'
		group by
			sell.product_id
		) as sell_out
		on
			sell_out.product_id = product.id

	left join
		(select
			ifnull(sum(sell.qty), 0) as summed_back,
			sell.product_id
		from
			sell
		where
				sell.is_back = 1
			and	sell.`datetime` > '2018-10-01 00:00:00'
		group by
			sell.product_id
		) as sell_in
		on
			sell_in.product_id = product.id

	left join
		(select
			ifnull(sum(buy.qty), 0) as summed_buy,
			buy.product_id
		from
			buy
		where
				buy.is_back = 1
			and	buy.`datetime` > '2018-10-01 00:00:00'
		group by
			buy.product_id
		) as buy_out
		on
			buy_out.product_id = product.id

	left join
		(select
			ifnull(sum(buy.qty), 0) as summed_return,
			buy.product_id
		from
			buy
		where
				buy.is_back = 0
			and	buy.`datetime` > '2018-10-01 00:00:00'
		group by
			buy.product_id
		) as buy_in
		on
			buy_in.product_id = product.id
group by
	product.id;

↓結果

product.id	product.price	summed_sell	summed_back	summed_buy	summed_return
1	100	2	1	2	6
2	200	1	1	1	3
3	300	3	1	8	10

ただ、こちらの場合は処理にかなり時間がかかってしまい、とても残念な感じです。
なんとか高速に正しい値が処理出来るようにならないでしょうか。

ちなみに、上記に記載したテーブル構造・データは下記のSQLで構築出来ますので、是非ご検証いただけると助かります。
※実際には、件数が万件単位になっている場合に処理時間が体感出来るのですが。。

--
-- Table structure for table `buy`
--

DROP TABLE IF EXISTS `buy`;
CREATE TABLE `buy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `qty` int(11) DEFAULT NULL,
  `is_back` tinyint(4) DEFAULT '0',
  `datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `buy`
--

LOCK TABLES `buy` WRITE;
/*!40000 ALTER TABLE `buy` DISABLE KEYS */;
INSERT INTO `buy` VALUES (1,1,5,0,'2018-10-10 00:00:00'),(2,2,3,0,'2018-10-10 00:00:00'),(3,3,10,0,'2018-10-10 00:00:00'),(4,1,2,1,'2018-10-10 00:00:00'),(5,2,1,1,'2018-10-10 00:00:00'),(6,3,8,1,'2018-10-10 00:00:00'),(7,1,1,0,'2018-10-10 00:00:00');
/*!40000 ALTER TABLE `buy` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `product`
--

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `product`
--

LOCK TABLES `product` WRITE;
/*!40000 ALTER TABLE `product` DISABLE KEYS */;
INSERT INTO `product` VALUES (1,'商品1',100),(2,'商品2',200),(3,'商品3',300);
/*!40000 ALTER TABLE `product` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `sell`
--

DROP TABLE IF EXISTS `sell`;
CREATE TABLE `sell` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `qty` int(11) DEFAULT NULL,
  `is_back` tinyint(4) DEFAULT '0',
  `datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `sell`
--

LOCK TABLES `sell` WRITE;
/*!40000 ALTER TABLE `sell` DISABLE KEYS */;
INSERT INTO `sell` VALUES (1,1,2,0,'2018-10-10 00:00:00'),(2,2,1,0,'2018-10-10 00:00:00'),(3,3,3,0,'2018-10-10 00:00:00'),(4,1,1,1,'2018-10-10 00:00:00'),(5,2,1,1,'2018-10-10 00:00:00'),(6,3,1,1,'2018-10-10 00:00:00');
/*!40000 ALTER TABLE `sell` ENABLE KEYS */;
UNLOCK TABLES;

Answer 1

SQLの作りより、インデックスが無いのが根本的に低速な理由かと思われます。
sell,buy共に、
(datetime, is_back, qty, product_id)
または
(product_id, datetime, is_back, qty)
のインデックスを追加してみて下さい。
※多分効果を生むのは前者で、その場合product_idは無くても変わらない気がしますが。

SQLはyamabejpさんのご提示分で良いとして、売値と仕入値は実際には分かれているんですよねきっと。

Answer 2

こんな感じですね

• 元データ

create table product(id int primary key,name varchar(20),price int);
insert into product values
(1,'商品1',100),
(2,'商品2',200),
(3,'商品3',300);

create table sell(id int primary key,product_id int,qty int,is_back tinyint,`datetime` datetime);
insert into sell values
(1,1,2,0,'2018-10-10 00:00:00'),
(2,2,1,0,'2018-10-10 00:00:00'),
(3,3,3,0,'2018-10-10 00:00:00'),
(4,1,1,1,'2018-10-10 00:00:00'),
(5,2,1,1,'2018-10-10 00:00:00'),
(6,3,1,1,'2018-10-10 00:00:00');

create table buy(id int primary key,product_id int,qty int,is_back tinyint,`datetime` datetime);
insert into buy values
(1,1,5 ,0,'2018-10-10 00:00:00'),
(2,2,3 ,0,'2018-10-10 00:00:00'),
(3,3,10,0,'2018-10-10 00:00:00'),
(4,1,2 ,1,'2018-10-10 00:00:00'),
(5,2,1 ,1,'2018-10-10 00:00:00'),
(6,3,8 ,1,'2018-10-10 00:00:00'),
(7,1,1 ,0,'2018-10-10 00:00:00');

• 集計

select * from product as t1
left join 
(select product_id,
sum(qty*(is_back=0)) summed_sell,
sum(qty*(is_back=1)) summed_back
from sell
where `datetime` > '2018-10-01 00:00:00'
group by product_id
) as t2 on t1.id=t2.product_id
left join 
(select product_id,
sum(qty*(is_back=0)) summed_buy,
sum(qty*(is_back=1)) summed_return
from buy
where `datetime` > '2018-10-01 00:00:00'
group by product_id
) as t3 on t1.id=t3.product_id

Comments

[kunai]

あぁ、すみません。検証用データ構築用SQLの提供が遅れました。。
わざわざありがとうございます。
なんかカッコいい感じのSQLですが、WHERE句に書かれるような条件がSELECT句内で表現されているのを初めて見ましたので、何者なのか調べてきます。

[kunai]

これ(select句内での条件記述)は、値を抽出しているのではなく、単純にIF文のように
is_back=0 を満たすレコードの場合は1となり、つまりqty実数が加算され、
is_back=0 でなかったレコードの場合は0となり、つまりqty*0=0が加算されるため、
結果的に表に出る値は正しい値になる、という理屈なのだと理解して良いのでしょうか。

[yambejp]

sellとbuyはほぼ同じ構造なので一つにまとめてもいいかもしれませんね。
また、戻しについてはis_back属性は必要だとして、戻しのqtyに-をつければ処理は楽になります

[yambejp]

> sum(qty*(is_back=0))

これはMySQLの方言のようなもので
(is_back=0)を評価しtrueかfalseを返し、trueは１falseは0で計算されるため
上記プラスの処理のqtyの合計を計算します

[kunai]

方言ですか。なるほど。
テーブル構造については、何年も前から運用されているシステムで、既に膨大なデータが登録されているので今更どうにも、というところなのです。
正確には、仰る通りマイナスの値が入っているレコードもあったりするので、実際にはABS(qty)的な処理も入っています。
実際のデータにて検証してみます！ありがとうございます。

[yambejp]

念の為ちゃんとしたSQLだと

sum(qty*(is_back=0))
↓↓↓
sum(qty*(case is_back when 0 then 1 else 0 end))

Answer 3

タイトル名は「…のチューニング」ですね。

SQLのチューニングはベテランエンジニアにお願いする一大仕事なので、
片手間でチューニングとかどんなベテランにも不可能です。
チューニングを責任もってやってくれという話になったらクラウドワークスで金払ってくれみたいな話に膨らんでしまうので、ここはチューニングを頑張って勉強してみてください。

MySQLのチューニングに関しては、MySQLの神様みたいな人間が書いた神記事があるので丸投げします。
ちょっと古いですが私もこれ見て勉強しました。
MySQLのEXPLAINを徹底解説!! - 漢のコンピュータ道

まずこれを見ながらExplain文を発行して「実行結果」を確認してください。
まぁ、質問文のSelect文の頭にexplainという枕詞を引っ付けるだけで表示されると思います。

MySQLは集計処理はC++で行っており、下手なスクリプト言語でfor文回して集計頑張るよりよっぽど速度が出るはずです。
にもかかわらず遅いのはほぼディスクアクセス、つまりテーブルに適切なインデックスが張られていない事が原因で、今回の理由もインデックスの張り忘れが原因なんじゃないの？と思ってます

商品ごとの売上合計、仕入れ合計等を算出したいと考えています。

返品という概念があり、売上合計と仕入れ合計のロジックが違うのが複雑怪奇になっている原因でしょう。
シンプルさを考慮するなら副問合せを行って最後にマージするか、
大人しくSQLを2つに分けるかの二択にすべきじゃないかなと思います。

もしかして一撃SQLを作ってるのですか？
一撃SQLを作る事は頭の体操になり、MySQL用のSQLを書く技術が上達しますから、
もし私が先輩であなたが後輩ならば、今日一日使って良いから一撃で出来ないか考えるよう指示を飛ばすかもしれません。

ただ、こういう公共の場で一撃SQLを作れません助けて下さいとなると
「お前アホだろ？素直に2個に分けろ」という回答がメインになるでしょう。
簡潔で良いSQLを2個発行出来るというのも立派な技術力ですから、SQLを分ける事も検討してみてください。

Comments

[sazi]

処理を分割せざるをえない場合には、分割した方がトータルでは高速という場合は有りますけど、
問い合わせ回数を減らすというのは、チューニングの基本です。

[miyabi-sun]

なるほど、つまり今回の質問者さんは一撃SQLを作ったが数十秒〜数百秒掛かって辛いと言ってるのに、
クエリ回数を1→2に増やすと数ms遅くなるから1個のまま頑張れと言ってるわけですね。

ボトルネックを探すために実行計画を確認し、それに対応する事を行うのがチューニングの仕事だと勘違いしていました。
saziさんの方がよくご存知みたいなのでそちらにおまかせします。

[sazi]

「数MS遅くなるから」というコメントから察するに、ご納得はされていないようですが、
一撃で行えるということは、分割した際には、その共通部分が実行計画上分割回数分実行されるので、トータルすると、遅くなるのが殆どなのです。
その際の時間差が数MSかどうかは物によります。

Answer 4

IS_BLACKの値により、SELLテーブルとBUYテーブルをそれぞれ2つに分けてJOINしているのが無駄です。
1回JOINして

select
    ...
    ifnull(sum(case when sell.is_black = 0 then sell.qty end), 0) as summed_sell,
    ifnull(sum(case when sell.is_black = 1 then sell.qty end), 0) as summed_black,
    ...

などとすれば良いです。

テーブル定義はCREATE TABLE,　インデックス定義は CREATE INDEX で提示した方が適切なコメントが付き易いです。
SQL実行計画の疑問解決には「とりあえずEXPLAIN」しよう で適切なインデックスが設定されているかも確認されては？

Comments

[kunai]

Explainは勿論してます！
Case文を使った方法も検証してみます。
ありがとうございます。

[Orlofsky]

質問に現状のEXPLAINも載せるとより適切なコメントが付くかと。