非効率なサブクエリを改善する

サブクエリを使った検索において、検索件数が数百~数千程度であれば、マシンの力で何とかなることがあるが、この検索結果が数万単位の膨大な数となる場合、動作が極端に遅くなる場合がある。

例として、historyテーブルにメールの履歴、clickテーブルにメールの履歴番号をhistory_idとしたクリックURL、およびそのクリック回数が入っていて、レコードは複数あるとする。

historyテーブルにクリック数のデータはないので、clickテーブルを結合して、履歴データと、その履歴ごとのクリック数およびクリック率（CTR）の一覧を表示したい。またCTRの大きさで一覧をソートしたい。

例えば下記のSQL。

SELECT	SQL_CALC_FOUND_ROWS
	`history`.*,
	`click`.total_clicks,
	ROUND(
		(`click`.total_clicks / `history`.users) * 100,
		 2
	) AS ctr 
FROM	`history` 
LEFT JOIN (
	SELECT
		`click`.history_id,
		SUM(click_count) AS total_clicks 
	FROM	`click`
	GROUP	BY `click`.history_id
)
ON	`history`.id = `click`.history_id
ORDER BY ctr DESC
LIMIT   0, 30

SELECT SQL_CALC_FOUND_ROWS

`history`.*,

`click`.total_clicks,

ROUND(

(`click`.total_clicks / `history`.users) * 100,

) AS ctr

FROM `history`

LEFT JOIN (

SELECT

`click`.history_id,

SUM(click_count) AS total_clicks

FROM `click`

GROUP BY `click`.history_id

)

ON `history`.id = `click`.history_id

ORDER BY ctr DESC

LIMIT 0, 30

この場合、clickテーブルのレコードが膨大になってしまうと、10~15行目のサブクエリ内のSELECTが全数検索になるため、結合後のテーブルが巨大となり、極端に動作が重くなる。history_idにINDEXを貼っていても、数万単位の量になってくると、同様だ。

いくら最後（18行目）にLIMIT句でレコード数を制限しても、内側のサブクエリから処理されるので、結合時点ではテーブルは巨大になってしまう。

「サブクエリ内でLIMIT使えばいいのでは？」と思うが、1つの履歴番号に紐付くclickテーブルのレコードが必ず存在するとは限らないし、存在したとしてもレコード数は一定ではないためこの時点でLIMITは確定できない。

つまり、「GROUP BY history.id」（15行目）したレコード数が、30になるとは限らない。仮にサブクエリ内で同じく「LIMIT 0,30」とやったとしたら、結合時にデータに不整合が生じる可能性がある。

またCTRは、historyテーブルに存在する読者数がわからないと出せないので、結合後にORDER BYするしかなく、サブクエリ内ではCTRによるORDER BYができない。この理由もありLIMITは使えない。

そこで発想の転換をして、clickテーブルにhistoryテーブルを結合してみる。

SELECT	SQL_CALC_FOUND_ROWS
	   `history`.*,
	   SUM(`click`.click_count) AS total_clicks,
	   ROUND(
              SUM(`click`.click_count) / `history`.users * 100,
              2
           ) AS ctr
FROM       `click`
RIGHT JOIN `history`
ON	   `click`.history_id = `history`.id
-- ここでwhereも使える --
GROUP BY   `history`.id
ORDER BY   SUM(`click`.click_count) / `history`.users * 100
LIMIT      0, 30

SELECT SQL_CALC_FOUND_ROWS

`history`.*,

SUM(`click`.click_count) AS total_clicks,

ROUND(

SUM(`click`.click_count) / `history`.users * 100,

) AS ctr

FROM `click`

RIGHT JOIN `history`

ON `click`.history_id = `history`.id

-- ここでwhereも使える --

GROUP BY `history`.id

ORDER BY SUM(`click`.click_count) / `history`.users * 100

LIMIT 0, 30

ポイントは、

clickテーブルを基本として、クリック数の合計とCTRの計算をする。
historyテーブルをRIGHT JOINして、historyテーブルのレコード数に、最終的な結果レコード数をあわせるようにする。
ORDER BY の対象を「click.history_id」ではなく「history.id」とし、履歴番号に紐付くclickテーブルのレコード数がない時も、対応できるようにする。

こうすることで、サブクエリを使わずに、LIMITをうまく使って膨大なクリックデータを高速に処理できるようになった。

当然ページ送りも、このLIMITの値を変更するだけで対応できる。

経験上、どうしてもサブクエリを使わなければならないってことはほとんどないんだよなあ、、、（経験不足なのか？）。

大体の場合、上記のように結合の方法を工夫するか、テンポラリテーブルを使って処理できる場合が多いと思う。（個人の感想です）

参考サイト）

http://nullnote.com/programs/mysql/join/

SQLでサブクエリを上手に使う6パターン

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル