MySQLチューニング:ソートやグルーピングするテーブルが大きいときはサブクエリを挟んだ方が速かった

 MySQLの便利機能でGROUP_CONCATってのがある。

SELECT primary_id, GROUP_CONCAT(item_id) AS item_id_list FROM stock GROUP BY primary_id ORDER BY primary_id ASC LIMIT 50

みたいにすると、stockテーブルのprimary_idでグループ化して、そのグループ内のitem_idをカンマ区切りで返してくれる。
 このとき、item_id_listはBLOBになったりする。
 こんな感じでもっと複雑なテーブルについてきたとき、ORDERやLIMITの関係でtmpテーブルでの作業が発生するんだけど、BLOBがMEMORYエンジンに乗らないのでボトルネックになっていた。
 さくらVPSお名前.com VPS(KVM)では、物理マシンに比べてディスクI/Oが弱いのでTmp to Diskになると著しく遅くなるので改善したい。
 ORDERやLIMITがINDEXで直ちに確定できない場合に、巨大なデータセットがtmpになるので、とりあえずこれをサブクエリにして

SELECT s.*, GROUP_CONCAT(item_id) AS item_id_list FROM (SELECT primary_id FROM stock GROUP BY primary_id ORDER BY primary_id ASC LIMIT 50) p LEFT JOIN stock s ON s.primary_id = p.primary_id GROUP BY s.primary_id ORDER BY s.primary_id ASC

みたいにした方が高速になった。
 この場合は、外のクエリは結局tmpの処理があるんだけど、LIMIT処理がサブクエリ中でかかっているから、JOINの段階でデータサイズが小さくなってCopy to Tmp Tableの時間が短縮できる。
 中間処理テーブルが大きくてLIMITが小さい場合ほど効果的。
 まぁ、根本を直した方が良いんだけど、とりあえず、簡単な修正で処理時間を短縮できるメモ。

(298)


カテゴリー: MySQL   パーマリンク

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です