取得件数を制限してもいい場合のパフォーマンスチューニング
2016/06/25
画面に出すデータ件数が100件だけでいい場合は、LIMIT 100 (OFFSET 0)を使えばいい。
ただLIMITはGROUP BYやDISTINCTを使用すると、条件にあうデータを全件取得したうえで100件だけをクライアントに返すため、パフォーマンスは向上しない。
1:Nのテーブルを結合して、集計関数等を使った上でクライアントに返す時、GROUP BYが必要になり、パフォーマンスが悪くなれば、結合をEXISTS句に変えて、GROUP BYをやめればLIMIT句を活用できる。
前提：
テーブル
注文(注文ID, 顧客ID, 注文日) PK:注文ID データ量:1000万件
注文商品接続(注文ID, 商品CD) PK:注文ID, 商品CD データ量:1000万件
商品(商品CD, 商品名, 価格, 商品種別) PK:商品CD データ量:20万件
顧客(顧客ID, 顧客名) PK:顧客ID データ量:10万件
結果セット
注文ID, 顧客ID, 顧客名, 注文合計金額
＊注文IDの昇順で上位100件のみ表示すること
＊商品種別は5～9のものに限定すること
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT 注文.注文ID , 注文.顧客ID , 顧客名 , (SELECT SUM(価格) FROM 注文商品接続 JOIN 商品 USING(商品CD) WHERE 注文商品接続.注文ID = 注文.注文ID ) AS 注文合計金額 FROM 注文 LEFT OUTER JOIN 顧客 USING (顧客ID) WHERE EXISTS (SELECT 1 FROM 注文商品接続 JOIN 商品 USING(商品CD) WHERE 注文商品接続.注文ID = 注文.注文ID AND 商品種別 BETWEEN '5' AND '9' ) ORDER BY 注文ID LIMIT 100 |
もし結合とGROUP BYを使用するのであれば次のようなSQLになる。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT 注文.注文ID , 注文.顧客ID , 顧客名 , SUM(価格) AS 注文合計金額 FROM 注文 JOIN 注文商品接続 USING(注文ID) JOIN 商品 USING(商品CD) LEFT OUTER JOIN 顧客 USING (顧客ID) WHERE 商品種別 BETWEEN '5' AND '9' GROUP BY 注文.注文ID, 注文.顧客ID, 顧客名 ORDER BY 注文.注文ID LIMIT 100 |
結論としては、「結合のGROUP BY禁止！」
ORDER BYが入るテーブルをメインとして、1:Nの関係になっているテーブルは結合ではなくEXISTS句にすることで、メインテーブルのWHERE句扱いにすることがポイントになる。