MYSQL 左結合の最適化 (10 秒から 20 ミリ秒)

仕事のコンテンツを組み合わせて、Left Jon の最適化のプロセスを皆さんと共有し、新しいアイデアを提供したいと思います。

【機能背景】

ユーザーの注文番号と加盟店番号に応じて、購入された商品の数とアフターサービスの商品の数をカウントする必要があります。関係するテーブルと関係を下の図に示します。

残念ながら、エンジニアが最初にテーブル構造を設計したとき、購入アイテムの合計数をマーチャント注文テーブルに記録せず、アフターセールスアイテムの数をマーチャント注文のアフターセールスフォームに記録していませんでした。

[生のSQL]

選択 
  o.no、s_order.no、s_item.count の合計、after_sale_item.count の合計
  から 
  購入者注文 
  seller_order s_order を o.id = s_order.buyer_order_id に結合しました 
  seller_order_item s_item を s_order.id = s_item.seller_order_id に結合しました
  seller_order_after_sale after_sale を s_order.id = after_sale.seller_order_id に結合しました
  seller_order_after_sale_item after_sale_item を after_sale.id = after_sale_item.after_sale_id に結合しました
o.add_time >='2019-05-01' の場合
グループ化
  o.id、s_order.id
並び替え 
  o.id
制限 0,10

上記の SQL ステートメントは、いくつかのキー フィールドにインデックスを使用します。

【独自SQL解析】

これは非常に一般的な SQL ステートメントであり、ロジックに問題はありません。

この SQL 文には多くの接続クエリが含まれています。アフターセールスの注文数が増えると、接続されるデータの数も増えます。

すべての適格なデータをメモリにロードした後、order.id と s_order.id に従ってグループ化してカウントします。データが 100 万個ある場合はどうなるでしょうか?コードを使用してこのような統計を実装するとしたらどうしますか?

カウントされたデータを order.id で並べ替え、最初の 10 個のデータを取り出します。

上記の SQL から、条件を満たすすべてのデータをメモリにロードし、グループ化、カウント、ソートし、最後にページ分割する必要があることがわかります。読み込むデータの量を減らすことはできますか?データベースの CPU 使用率を削減することは可能ですか? 最初に少量のデータを取得してから統計を実行することは可能ですか?

上記の問題に基づいて、私たちは最適化しました

【分析手順】

観察者としては、最初は関数がどのようなデータを出力する必要があるのか​​わからないため、各テーブルにどのようなデータが格納されているか、それらの関係はどのようなものかを理解する必要があります。

元の SQL がどのようなものであったかを忘れて、必要なデータに応じて再度考え直し、元の SQL の渦に再び陥らないようにします。

上記の質問に答えて、データの読み込みを減らすにはどうすればよいでしょうか?最初にデータをページ分割し、次にページ分割されたデータに対して個別の統計を実行することは可能ですか?

では、group by を最適化する必要があるのでしょうか? まずページ分割する方法を見つける必要があります。

何か方法は考えましたか？

[最適化されたSQL]

選択 
  o.id、o.no、s_order.no、
  (seller_orderからsum(sot.count)を選択) 
    seller_order_item sot を so.id = sot.seller_order_id に結合しました 
        ここで、so.id =s_order.id )、
  (seller_order_after_sale osa から sum(osat.count) を選択) 
    seller_order_after_sale_item osat を osa.id = osat.after_sale_id に結合しました
        ここで osa.seller_order_id = s_order.id )
  から 
  購入者の注文 
  seller_order s_order を o.id = s_order.buyer_order_id に結合しました 
o.addTime >='2019-05-01' の場合
並び替え 
  o.id
制限 0,10

【最適化されたSQL分析】

1. order.id、s_order.id によるグループ化は、実際には buyer_order テーブルと seller_order テーブルのみを接続し、論理的には同じようにグループ化されるため、グループ化を削除したことは非常に直感的にわかります。
2. group by を使用しない場合、データのグループ化の CPU 処理を削減でき、メイン テーブル データのみを接続するため、メモリにロードされるデータが削減されます。
3. 上記の操作により、最初にデータをページングするという前述の内容が完了します。 10 個のデータを取得しました。
4. 次に、10個のデータ項目について、商品の販売数とアフターセールス数をカウントします。
5. この時点で、実際にはページアウトされた 10 個のデータ項目のみをカウントしていたことが全員に分かりましたが、すべてのデータをグループ化してから 10 個の項目を統計用に取得する必要があることが判明しました。この操作により、データの統計処理が大幅に削減されることがわかります。必要なデータだけを収集すればいいのです。

上記の最適化の効果は、あなたの想像をはるかに超えるものとなるかもしれません。

実際の作業では、リンクされたテーブルの数は、この例の数より多くなります。最適化されていない SQL をページングなしで実行すると、合計で 700,000 のデータがあることがわかります。10 個のデータをページングして取得するのに 10 秒以上かかりました。データ量は多くありませんが、ほとんどの時間はグループ化とデータ統計に費やされています。複雑さを理解するために、これらのデータをグループ化してカウントするコードを書いてみてください。

実際、10 個を取り出すかすべてを取り出すかに関係なく、統計が最初に実行されるため、時間は基本的に同じです (IO を考慮しない場合)。

最適化後は、約 20,000 個のデータのみがメモリにロードされ、統計は実行されません。最初に 10 個のデータが取り出され、次に 10 個のデータに対して統計が実行されます。ロジックは以前よりもはるかに単純です。最適化された SQL 実行時間は 20 ミリ秒以内です。

実際、受注テーブルとアフターサービステーブルの両方に対応する数量が記録されている場合、テーブル数はさらに少なくなり、サブクエリは必要ありません。テーブルを設計するときに、統計的なニーズを考慮する必要がある場合もあります。

MYSQL Left Join の最適化 (10 秒から 20 ミリ秒への最適化) に関するこの記事はこれで終わりです。MYSQL Left Join の最適化に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。