MySQL の効率的なクエリの左結合とグループ化 (プラス インデックス)

mysql 効率的なクエリ

MySQL は、左結合の速度を上げるために group by を犠牲にします (インデックスが追加されていると仮定)。

ユーザーテーブル: 100,000 データ

例1: 約200秒

SELECT U.id, A.favorite_count FROM (SELECT id from user) U
左結合（
  -- いいねの数 SELECT favorite_by AS user_id, SUM(favorite_count) AS favorite_count
  お気に入りから
  お気に入り別にグループ化
) A ON U.id=A.user_id
左結合（
  -- コメント数 SELECT user_id, COUNT(*) AS comment_count
  photo_commentより
  ユーザーIDでグループ化
) B ON U.id=B.user_id

例2: 1秒以上

uf.user_id、uf.favorite_count、COUNT(pc.id) を comment_count として選択します (
u.id を user_id として選択し、SUM(f.favorite_count) を favorite_count として (SELECT id from user) u から選択します。 
LEFT JOIN お気に入り f on f.favorite_by = u.id  
u.id によるグループ化
） 
左結合 photo_comment pc on pc.user_id = uf.user_id
uf.user_id によるグループ化

付録: MySQL で 3 つのテーブルを効率的に結合する方法

次の3つのテーブルの結合ステートメント

*を選択 
t1から 
t2を(t1.a=t2.a)に結合する 
t3を(t2.b=t3.b)に結合する 
ただし、t1.c>=X、t2.c>=Y、t3.c>=Z です。

straight_join として書き直す場合、接続順序をどのように指定し、3 つのテーブルのインデックスをどのように作成すればよいでしょうか?

BKAアルゴリズムを使ってみる

BKA を使用する場合、「最初に 2 つのテーブルを結合した結果を計算し、次に 3 番目のテーブルと結合する」のではなく、クエリが直接ネストされます。具体的な実装: t1.c>=X、t2.c>=Y、t3.c>=Z の 3 つの条件のうち、フィルタリング後のデータが最も少ないテーブルを最初の駆動テーブルとして選択します。この時点で、次の 2 つの状況が発生する可能性があります。

テーブル t1 または t3 が選択された場合、残りは固定されます。

• 駆動テーブルがt1の場合、接続順序はt1->t2->t3であり、駆動テーブルフィールドにインデックスを作成する必要があります。つまり、t2.aとt3.bにインデックスを作成します。
• 駆動テーブルが t3 の場合、結合順序は t3->t2->t1 となり、t2.b と t1.a にインデックスを作成する必要があります。
  

同時に、最初の駆動テーブルのフィールド c にインデックスを作成する必要もあります。

2 番目のケースでは、選択された最初の駆動テーブルがテーブル t2 である場合、他の 2 つの条件のフィルタリング効果を評価する必要があります。

考え方としては、各結合に参加する駆動テーブルのデータ セットをできるだけ小さくすることです。これにより、駆動テーブルが小さくなります。

要約する

これで、MySQL の効率的なクエリの left join と group by に関するこの記事は終了です。MySQL の効率的なクエリに関する関連コンテンツをさらにご覧になりたい場合は、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続きご覧ください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。