モデル比較

以下は**PostgreSQL前提**の最適化です。結論から言うと、このクエリは「インデックス不足」だけでは解決しにくく、**結合で行数が増える順番**と**集計の粒度**を直すのが効果的です。---##1.主な問題点###①`reviews`を注文関連に結合した後に集計すると行数が増える現状:```sqlFROMproductspLEFTJOINorder_detailsodONod.product_id=p.idLEFTJOINordersoONo.order_id=od.order_idANDo.status='完了'LEFTJOINreviewsrONr.product_id=p.id```この場合、1つの製品について-完了注文が100件-レビューが20件だとすると、結合結果は最大**100×20=2000行**になり得ます。すると:```sqlCOUNT(o.order_id)SUM(o.amount)```がレビュー数分だけ重複して集計され、**結果が正しくなくなる場合**があります。また、行が増えるため処理時間も伸びます。---###②`order_details`を経由して`orders.amount`を合計している点現状は:```sqlSUM(o.amount)```です。ここで注意すべきは:-`orders.amount`が「受注単位の総額」である場合-1つの注文に同じ製品が複数行ある場合-1つの注文に複数製品がある場合などでは、製品別の売上として`orders.amount`を単純に合計すると過大評価になることがあります。通常、製品別売上は`order_details`の明細行にある金額を使うのが自然です。もし`order_details.amount`等の明細金額があれば、そちらを使うべきです。---###③`LEFTJOIN`に見えて実際は`INNERJOIN`になっている```sqlLEFTJOINordersoON...WHEREo.created_at>='2024-04-01'```これは`o.created_at`を`WHERE`で絞っているため、`orders`がNULLになる行は除外されます。つまり、**注文がない製品は結果に含まれない**、実質`INNERJOIN`相当です。「注文がなくても製品は返したい」のが意図なら、`WHERE`の条件の付け方が間違っています。一方で「期間内に完了注文がある製品の売上トップ50が欲しい」なら、そのまま`JOIN`に変えてよいです。---###④`GROUPBYp.product_name,c.category_name`は危険製品名・カテゴリ名でグループ化すると、以下のような場合が問題になります。-同名の製品が複数ある-カテゴリ名が重複している-本来は製品単位で集計したいのに、名称単位で集約されてしまう製品単位なら、集計は`product_id`を行い、表示だけ`product_name`,`category_name`にするのが安全です。---###⑤インデックス不足現状:-`products(id)`-`orders(order_id)`これだけでは以下の処理が効きません。-`orders.status='完了'`-`orders.created_at>='2024-04-01'`-`order_details.product_id`/`order_details.order_id`による結合-`reviews.product_id`によるレビュー平均取得---##2.推奨する書き換え方針基本戦略は以下です。1.**先に`orders`を期間とステータスで絞る**2.**`order_details`経由で製品別売上を集計する**3.**売上トップ50を先に確定する**4.**その50件だけ`categories`/`reviews`を結合・取得する**これにより、全製品×全レビューを結合することを避けます。---##3.最適化版SQL前提:-「2024-04-01以降の完了注文があるアクティブ製品」の売上トップ50が欲しい-製品単位で集計したい-売上順に並び替えたい-レビューがない製品でも`avg_rating`はNULLでよい```sqlWITHtop_productsAS(SELECTod.product_id,COUNT(DISTINCTo.order_id)ASorder_count,SUM(o.amount)AStotal_salesFROMordersoJOINorder_detailsodONod.order_id=o.order_idJOINproductspONp.id=od.product_idWHEREo.status='完了'ANDo.created_at>=DATE'2024-04-01'ANDp.is_active=trueGROUPBYod.product_idORDERBYtotal_salesDESC,product_idLIMIT50)SELECTp.product_name,c.category_name,t.order_count,t.total_sales,(SELECTAVG(r.rating)FROMreviewsrWHEREr.product_id=t.product_id)ASavg_ratingFROMtop_productstJOINproductspONp.id=t.product_idJOINcategoriescONc.id=p.category_idORDERBYt.total_salesDESCNULLSLAST;```---##4.この書き換えの改善点###①`orders`を先に絞る```sqlWHEREo.status='完了'ANDo.created_at>=DATE'2024-04-01'```これにより、対象は「期間内の完了注文」だけです。全注文や全製品を最初から結合するより対象が小さくなります。---###②レビューを最後に、しかもトップ50だけ取得する```sqlSELECTAVG(r.rating)FROMreviewsrWHEREr.product_id=t.product_id```これは50回だけ実行されるスカラーサブクエリです。`reviews`側が`product_id`で効くインデックスを持っていれば、50製品のレビューだけ取得できます。---###③集計前に結合による行膨張を防ぐレビューを結合した後に集計するのではなく、先に売上を製品別に集計しています。これにより`COUNT`と`SUM`がレビュー数倍になるのを防げます。---###④グループ化を製品単位にする```sqlGROUPBYod.product_id```としておき、最後に名称を引いています。同名製品が混ざる問題を回避できます。---##5.`orders.amount`についての重要な補足上のSQLは、元のクエリの意味をできるだけ踏襲した形です。しかし、`orders.amount`が「注文単位の総額」の場合、製品別売上としては不自然な場合があります。特に以下の場合は注意してください。###ケースA:`order_details`に明細金額があるこれが最も自然です。その場合は:```sqlSUM(od.amount)```を使うべきです。例:```sqlWITHtop_productsAS(SELECTod.product_id,COUNT(DISTINCTo.order_id)ASorder_count,SUM(od.amount)AStotal_salesFROMordersoJOINorder_detailsodONod.order_id=o.order_idJOINproductspONp.id=od.product_idWHEREo.status='完了'ANDo.created_at>=DATE'2024-04-01'ANDp.is_active=trueGROUPBYod.product_idORDERBYtotal_salesDESC,product_idLIMIT50)...```---###ケースB:`orders.amount`は注文総額だが、同じ製品が同一注文に複数行あるこの場合は、`SUM(o.amount)`をそのまま使うと同注文の金額が重複します。その場合は、一度`product_id+order_id`単位に集約してから合計する形にします。例:```sqlWITHproduct_orderAS(SELECTod.product_id,o.order_id,MIN(o.amount)ASamountFROMordersoJOINorder_detailsodONod.order_id=o.order_idJOINproductspONp.id=od.product_idWHEREo.status='完了'ANDo.created_at>=DATE'2024-04-01'ANDp.is_active=trueGROUPBYod.product_id,o.order_id),top_productsAS(SELECTproduct_id,COUNT(order_id)ASorder_count,SUM(amount)AStotal_salesFROMproduct_orderGROUPBYproduct_idORDERBYtotal_salesDESC,product_idLIMIT50)SELECTp.product_name,c.category_name,t.order_count,t.total_sales,(SELECTAVG(r.rating)FROMreviewsrWHEREr.product_id=t.product_id)ASavg_ratingFROMtop_productstJOINproductspONp.id=t.product_idJOINcategoriescONc.id=p.category_idORDERBYt.total_salesDESCNULLSLAST;```ここで`MIN(o.amount)`を使っているのは、同一`order_id`に対して`orders.amount`は原則同じ値であるためです。明細金額がある場合は、`SUM(od.amount)`による`product_order`の集計の方が自然です。---##6.おすすめインデックスPostgreSQLなら以下が有効です。```sqlANALYZEproducts;ANALYZEcategories;ANALYZEorder_details;ANALYZEorders;ANALYZEreviews;```###①`orders`の絞り込み用```sqlCREATEINDEXIFNOTEXISTSidx_orders_completed_createdONorders(created_at)INCLUDE(order_id,amount)WHEREstatus='完了';```これは部分インデックスです。`status='完了'`である行だけインデックスに入るので、小さいことが多いです。部分インデックスが使えないDBなら:```sqlCREATEINDEXIFNOTEXISTSidx_orders_status_createdONorders(status,created_at)INCLUDE(order_id,amount);```または:```sqlCREATEINDEXIFNOTEXISTSidx_orders_status_createdONorders(status,created_at,order_id,amount);```---###②`order_details`の結合用上記の最適化版SQLは`orders`から始めて`order_details`を結合しています。そのため:```sqlCREATEINDEXIFNOTEXISTSidx_order_details_order_productONorder_details(order_id,product_id);```が有効です。一方で、製品側から始める書き換えにする場合は:```sqlCREATEINDEXIFNOTEXISTSidx_order_details_product_orderONorder_details(product_id,order_id);```も検討します。`order_details`に明細金額がある場合、カバリングしたい場合は:```sqlCREATEINDEXIFNOTEXISTSidx_order_details_order_productONorder_details(order_id,product_id)INCLUDE(amount);```---###③`reviews`の平均取得用```sqlCREATEINDEXIFNOTEXISTSidx_reviews_product_ratingONreviews(product_id)INCLUDE(rating);```これでトップ50製品に対する`AVG(rating)`がインデックス中心で走ります。---###④`products`のアクティブ絞り込み用多くの製品がinactiveで、activeだけが少なければ有効です。```sqlCREATEINDEXIFNOTEXISTSidx_products_activeONproducts(is_active)INCLUDE(id,product_name,category_id);```ただし、`products`の大半が`is_active=true`の場合は、このインデックスが有効であるとは限りません。その場合はPK検索だけで十分こともあります。---##7.`COUNT(DISTINCT...)`について上記のSQLでは:```sqlCOUNT(DISTINCTo.order_id)```を使っています。これは、同一製品が同一注文に複数行ある場合に、注文数を重複して数えないための対策です。ただし、データ設計として:-1つの`order_id`に対して同一`product_id`は常に1行だけが保証されているなら:```sqlCOUNT(o.order_id)```の方が高速です。---##8.元の`LEFTJOIN`の意味を維持したい場合もし「期間内に完了注文がなくても、アクティブ製品は候補にしたい」のが意図なら、元の`WHEREo.created_at>=...`は正しくありません。その場合は以下のように、注文集計を別CTEにして`LEFTJOIN`します。```sqlWITHproduct_ordersAS(SELECTod.product_id,COUNT(DISTINCTo.order_id)ASorder_count,SUM(o.amount)AStotal_salesFROMordersoJOINorder_detailsodONod.order_id=o.order_idJOINproductspONp.id=od.product_idWHEREo.status='完了'ANDo.created_at>=DATE'2024-04-01'ANDp.is_active=trueGROUPBYod.product_id),product_ratingsAS(SELECTproduct_id,AVG(rating)ASavg_ratingFROMreviewsGROUPBYproduct_id)SELECTp.product_name,c.category_name,po.order_count,po.total_sales,pr.avg_ratingFROMproductspJOINcategoriescONc.id=p.category_idLEFTJOINproduct_orderspoONpo.product_id=p.idLEFTJOINproduct_ratingsprONpr.product_id=p.idWHEREp.is_active=trueORDERBYpo.total_salesDESCNULLSLASTLIMIT50;```ただし、この版は全製品・全レビューを扱うため、トップ50だけ先に確定する版より遅くなる場合があります。---##9.実行計画の確認変更後は必ず以下で確認してください。```sqlEXPLAIN(ANALYZE,BUFFERS,SETTINGS)SELECT...```理想は:-`orders`が`created_at`/`status`のインデックスで絞られている-`order_details`が`order_id`のインデックスで結合されている-`reviews`が50製品分だけ`product_id`のインデックスで取得されている-巨大なHashJoinで行数が爆発していない-SeqScanが複数大きなテーブルに出ていない---##10.さらに遅い場合の追加手段###①`work_mem`を増やす集計がHashAggregateを使う場合、メモリ不足だとtempfileに出て遅くなります。```sqlSETwork_mem='256MB';```ただし環境に合わせてください。---###②`orders`を`created_at`でパーティション分割するデータ量が増え続ける場合、日付パーティションが有効です。```sqlPARTITIONBYRANGE(created_at)```これにより`created_at>='2024-04-01'`のような絞り込みでパーティション剪定が行われます。---###③集計結果をマテリアライズビュー化するこのSQLを頻繁に実行するなら、集計結果を事前生成するのも手です。```sqlCREATEMATERIALIZEDVIEWmv_product_sales_2024_04ASWITHtop_productsAS(SELECTod.product_id,COUNT(DISTINCTo.order_id)ASorder_count,SUM(o.amount)AStotal_salesFROMordersoJOINorder_detailsodONod.order_id=o.order_idJOINproductspONp.id=od.product_idWHEREo.status='完了'ANDo.created_at>=DATE'2024-04-01'ANDp.is_active=trueGROUPBYod.product_id)SELECTt.product_id,p.product_name,c.category_name,t.order_count,t.total_sales,(SELECTAVG(r.rating)FROMreviewsrWHEREr.product_id=t.product_id)ASavg_ratingFROMtop_productstJOINproductspONp.id=t.product_idJOINcategoriescONc.id=p.category_id;```必要になったら:```sqlREFRESHMATERIALIZEDVIEWmv_product_sales_2024_04;```---##11.まとめ今回の最適化で重要なのは以下の5点です。1.**`orders`を期間・ステータスで先に絞る**2.**`reviews`を全結合せずに、トップ50だけ取得する**3.**結合による行重複を防ぐ**4.**製品単位は`product_id`で集計する**5.**`orders`,`order_details`,`reviews`に適切な複合インデックス・カバリングインデックスを作る**これにより、500万行規模でも30秒超から大幅な改善が期待できます。

このクエリは、500万行のテーブルに対して非効率な結合・集計・ソートを行っているため、30秒以上かかっています。以下の改善策を実施することで、処理時間を大幅に短縮できます。##1.インデックスの追加現在のインデックス（products(id)、orders(order_id)）だけでは、WHERE句や結合に必要な絞り込みができていません。以下を追加します。```sql--products:WHERE句のフィルタと結合キー用CREATEINDEXidx_products_is_active_idONproducts(is_active,id);--order_details:product_idでの結合を高速化CREATEINDEXidx_order_details_product_idONorder_details(product_id);--orders:結合とフィルタ条件（status,created_at）をカバーCREATEINDEXidx_orders_status_created_atONorders(status,created_at,order_id);--reviews:結合用CREATEINDEXidx_reviews_product_idONreviews(product_id);```##2.LEFTJOINの見直し（実質INNERJOINになっている）現在のクエリは`LEFTJOINorders...ANDo.status='完了'`にもかかわらず、`WHEREo.created_at>='2024-04-01'`でNULL行が除外され、事実上のINNERJOINになっています。もし「ordersがなくても商品を表示したい」のであれば、日付条件もONに移動すべきです。性能面では、INNERJOINにすることで不要な行の結合を減らせます。**意図を確認し、必要に応じてINNERJOINに変更してください。**##3.GROUPBYとORDERBYの最適化現在のGROUPBYは`p.product_name,c.category_name`ですが、商品名が重複する可能性があるため、**product_idでグループ化**し、商品名とカテゴリ名は集約関数（MAXなど）で取得するほうが安全かつ高速です。##4.事前集計サブクエリの活用ordersとreviewsを一度集計してからproductsに結合すると、productsの行数だけの結合に抑えられます。特にアクティブな商品数が少なければ効果的です。##5.最適化したクエリ例```sqlSELECTp.product_name,c.category_name,COALESCE(ord.order_count,0)ASorder_count,COALESCE(ord.total_sales,0)AStotal_sales,rev.avg_ratingFROMproductspLEFTJOINcategoriescONc.id=p.category_idLEFTJOIN(SELECTod.product_id,COUNT(o.order_id)ASorder_count,SUM(o.amount)AStotal_salesFROMorder_detailsodINNERJOINordersoONo.order_id=od.order_idANDo.status='完了'ANDo.created_at>='2024-04-01'GROUPBYod.product_id)ordONord.product_id=p.idLEFTJOIN(SELECTproduct_id,AVG(rating)ASavg_ratingFROMreviewsGROUPBYproduct_id)revONrev.product_id=p.idWHEREp.is_active=trueORDERBYCOALESCE(ord.total_sales,0)DESCNULLSLASTLIMIT50;```**変更点のポイント：**-`orders`のフィルタはサブクエリ内で行い、INNERJOINで絞り込む。-`reviews`も事前に集約。-`GROUPBY`を`od.product_id`に一本化。-`product_name`や`category_name`は元のテーブルから直接取得（重複があっても問題なし）。-`COALESCE`でNULLを0に変換し、ソートや表示を安全に。##6.その他の改善案（状況による）-**統計情報の更新**:大量データでは統計情報が古いと実行計画が悪化するため、`ANALYZE`を実行。-**パーティショニング**:`orders.created_at`でテーブルを月単位などに分割すると、日付範囲のスキャンが劇的に速くなる。-**マテリアライズドビュー**:集計結果を事前に保存しておく（リアルタイム性が不要な場合）。以上の対策により、応答時間を数秒以内に改善できます。まずはインデックス追加とクエリ書き換えを試し、実行計画（`EXPLAINANALYZE`）でボトルネックを確認しながら調整してください。

次の方法でクエリを最適化できます。###1.インデックスの追加現状のインデックス(products(id)とorders(order_id))は十分には言えません。特に、product_nameやcategory_name、order_id、created_atなど多くのカラムに対してインデックスを作成するのが重要です。**具体的なインデックス**:-`products(product_name,category_name)`-`orders(order_id,status,created_at)`-`order_details(product_id,order_id)`###2.WHERE条件の最適化`o.status='完了'`という条件はテーブル行の多数に影響しません。この条件を直接WHERE句から移動させると」、一意なキーやインデックスを通して影響を最適化できます。**具体的な最適化**:-WHERE条件をインデックス利用したjoin句に移動する方法。###3.クエリの構造化以下の変更を行うことで、NULLを扱いやすくなります。-計算式でのNULLの扱いは`COALESCE`や`IFNULL`などを使う###4.クエリのパフォーマンスオプティマイザの使用MySQLでは特定のオプティマイザを有効にすることで、クエリのパフォーマンスを向上させることができます。以下の例はMySQLのオプティマイザを有効にする方法です。```sqlEXPLAINEXTENDEDSELECTp.product_name,c.category_name,COUNT(o.order_id)asorder_count,SUM(o.amount)astotal_sales,AVG(r.rating)asavg_ratingFROMproductspLEFTJOINcategoriescONc.id=p.category_idLEFTJOINorder_detailsodONod.product_id=p.idLEFTJOINordersoONo.order_id=od.order_idANDo.status='完了'ANDo.created_at>='2024-04-01'LEFTJOINreviewsrONr.product_id=p.idWHEREp.is_active=trueGROUPBYp.product_name,c.category_nameORDERBYtotal_salesDESCNULLSLASTLIMIT50;```###5.クエリチューン競技値アクセスパターン(集計前)に対するプロファイリングを実施。ID数値索引の扱いが容易であることなど多くの専門的チューニングを行えるかも知れません。###最終的なクエリ(例)```sql--インデックス作成例ALTERTABLEproductsADDINDEXproduct_name_index(product_name);ALTERTABLEordersADDINDEXvalid_orders_index(order_id,status,created_at);ALTERTABLEorder_detailsADDINDEXorder_details_index(product_id,order_id);--最適化クエリSELECTp.product_name,c.category_name,COUNT(o.order_id)asorder_count,SUM(o.amount)astotal_sales,IFNULL(AVG(r.rating),0)asavg_ratingFROMproductspINNERJOINcategoriescONc.id=p.category_idINNERJOIN(SELECTod.order_id,o.amount,od.product_id,MIN(o.created_at)ascreated_at_filterFROMorder_detailsodINNERJOINordersoONo.order_id=od.order_idINNERJOIN(SELECTod2.order_idFROMorder_detailsod2INNERJOINorderso2ONo2.order_id=od2.order_idWHEREo2.status='完了'ANDo2.created_at>='2024-04-01'GROUPBYod2.order_id)subONsub.order_id=od.order_idWHEREo.status='完了'ANDo.created_at=created_at_filterGROUPBYod.order_id)valid_ordersonvalid_orders.product_id=p.idJOIN(SELECTod.order_id,r.ratingFROMorder_detailsodINNERJOINreviewsrONr.product_id=od.product_idGROUPBYod.order_id,r.rating)reviewed_ordersONreviewed_orders.order_id=od2.order_idWHEREp.is_active=trueGROUPBYp.product_name,c.category_nameORDERBYtotal_salesDESCNULLSLASTLIMIT50;```Notes:-インデックスの実際の作成は開発環境で実施し、生産環境へ適用する際は注意しましょう。-クエリチューニングツールやパフォーマンス監視ツールを使用して、実際のパフォーマンスを監視してください。これらの最適化により、クエリ実行速度は大幅に改善されるでしょう。インデックスの設置が効果的であれば、real変数の在庫などを選択静的に調整することが可能になります。