パラメータ調整で多々あるみたいですが効率の良いSQLについて検証してみます。
前回の検証の続きです。
検証内容は前回に似ています。
もっとより実務的なSQLのチューニングをはかります。
検証1
サイト毎の合計view数,conversion数を計算
| SQL1 | SELECT * FROM (SELECT site_id, site_name, SUM(view) AS view, SUM(conversion) AS conversion FROM site AS T1 INNER JOIN site_view AS T2 USING(site_id) WHERE date BETWEEN '2013/11/01' AND '2013/11/30' GROUP BY site_id, site_name ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0; |
| SQL2 | SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view WHERE date BETWEEN '2013/11/01' AND '2013/11/30' GROUP BY site_id) AS T2 USING(site_id) ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0; |
前回とほぼ同一なのでシェルの実行等は省きます.
以下が検証結果です。
| 実行ファイル | 1回目 | 2回目 | 3回目 | 4回目 | 5回目 | 6回目 | 7回目 | 8回目 | 9回目 | 10回目 | 最小 | 最大 | 平均 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| sql1.sql | 417.611 | 376.637 | 420.722 | 350.816 | 316.651 | 322.067 | 335.734 | 307.371 | 385.674 | 370.905 | 307.371 | 420.722 | 360.418 |
| sql2.sql | 321.847 | 280.912 | 303.027 | 263.667 | 258.189 | 276.157 | 310.888 | 274.68 | 277.269 | 265.227 | 258.189 | 321.847 | 283.186 |
と検証結果はこのようになりました。
先ほどよりやや差は縮まりましたがやはりSQL２の方が早いです。
では、SQL1の記述は遅いのかを親のデータsiteにWHERE句をかけて試します
| SQL1 | SELECT * FROM (SELECT site_id, site_name, SUM(view) AS view, SUM(conversion) AS conversion FROM site AS T1 INNER JOIN site_view AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' GROUP BY site_id, site_name ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0; |
| SQL2 | SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id) AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0; |
このようなSQLです、あえて遅くするために正規表現で記載
| 実行ファイル | 1回目 | 2回目 | 3回目 | 4回目 | 5回目 | 6回目 | 7回目 | 8回目 | 9回目 | 10回目 | 最小 | 最大 | 平均 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| sql1.sql | 1709.609 | 1729.138 | 1714.069 | 1862.493 | 1838.969 | 1752.116 | 1668.097 | 1701.067 | 1749.651 | 1714.535 | 1668.097 | 1862.493 | 1743.974 |
| sql2.sql | 1074.498 | 1056.323 | 1087.329 | 1060.741 | 1051.747 | 1133.521 | 1133.265 | 1143.876 | 1087.005 | 1065.277 | 1051.747 | 1143.876 | 1089.358 |
極端とまでは行かないが半分近くSQL2が早い良い書き方はこういったように
効率化できそうである。
と後もう１個検証したい・・・
HAVINGを使うかWHEREを使うかである
検証で早いであろうSQL２をベースに以下のないようで検証します
| SQL1 | SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id HAVING SUM(conversion) > 90000) AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0; |
| SQL2 | SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id) AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' AND T2.conversion > 90000 ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0; |
HAVINGを利用したSQLがSQL1でWHERE句を利用したのがSQL2である
何方もどっちな気がしなくはないが検証
| 実行ファイル | 1回目 | 2回目 | 3回目 | 4回目 | 5回目 | 6回目 | 7回目 | 8回目 | 9回目 | 10回目 | 最小 | 最大 | 平均 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| sql1.sql | 1180.76 | 1383.684 | 1326.127 | 1376.931 | 1359.841 | 1411.559 | 1368.94 | 1324.281 | 1383.121 | 1211.007 | 1180.76 | 1411.559 | 1332.625 |
| sql2.sql | 1359.779 | 1306.331 | 1312.314 | 1350.094 | 1327.714 | 1377.518 | 1405.109 | 1362.456 | 1313.428 | 1146.364 | 1146.364 | 1405.109 | 1326.111 |
仮にPHPやJavaでWHERE句やHAVINGを動的生成するのならSQL2の方がやりやすそうな気がするのでSQL2に軍配あり？ とま〜長々と検証していきました。
結論としては、GROUP BYを先に行いJOINしましょうに限る
今度は、月別レポートの作成で検証したいと思います。
