mysqlネストされたクエリーと結合テーブルクエリーの最適化

ネストされたクエリの悪い最適化は、前述したように、特別な状況を考慮せずに、ネストされたクエリよりも結合テーブルクエリが有効です.2つのクエリーは同じ意味を表していますが、サーバーに何をするかを教えて、どうするかを決めさせる計画ですが、変更方法を教えなければならない場合があります.さもないとオプティマイザは馬鹿なことをするかもしれません.私は最近このような状況に遭遇した.これらのテーブルは、category、subcategory、itemの3つの階層関係です.数千件がcategoryテーブルに記録され、数百件がsubcategoryテーブルに記録され、数百万件がitemテーブルに記録されています.categoryテーブルは無視できますが、背景を説明するだけで、次のクエリ文は含まれません.テーブルを作成する文です.

create table subcategory (
    id int not null primary key,
    category int not null,
    index(category)
) engine=InnoDB;

create table item(
    id int not null auto_increment primary key,
    subcategory int not null,
    index(subcategory)
) engine=InnoDB;

また表にサンプルデータを記入します

insert into subcategory(id, category)
    select i, i/100 from number
    where i <= 300000;

insert into item(subcategory)
    select id
    from (
        select id, rand() * 20 as num_rows from subcategory
    ) as x
        cross join number
    where i <= num_rows;

create temporary table t as
    select subcategory from item
    group by subcategory
    having count(*) = 19
    limit 100;

insert into item (subcategory)
    select subcategory
    from t
        cross join number
    where i < 2000;

もう一度説明しますが、これらの文は実行に時間がかかり、製品環境で実行するのに適していません.構想はitemにランダムな行数のデータを挿入し、subcategoryには1から2018の間のitemがある.これは実際の完全なデータではありませんが、効果は同じです.
あるcategoryのitem数が2000より大きいすべてのsubcategoryを見つけたいです.まず、subcategory itemの数が2000より大きいものを見つけて、categoryを次のクエリに使用します.これは、特定のクエリ文です.

select c.id
from subcategory as c
    inner join item as i on i.subcategory = c.id
group by c.id
having count(*) > 2000;

-- choose one of the results, then
select * from subcategory where id = ????
-- result: category = 14

適切な値14を取得し、以下のクエリで使用します.これはcategory 14のすべてのitem数が2000より大きいsubcategoryをクエリーするための文です.

select c.id
from subcategory as c
    inner join item as i on i.subcategory = c.id
where c.category = 14
group by c.id
having count(*) > 2000;

私のサンプルデータでは、クエリーの結果が10行記録され、10秒以上で完了しました.EXPLAINはインデックスがよく使われていることを示しています.データの規模から見ると、かなりいいですね.クエリー・プランは、インデックス上でターゲット・レコードを遍歴して計算します.今まで、とてもよかったです.
今回はsubcategoryからすべてのフィールドを取り出すとします.私は上のクエリをネストしてJOINとかSELECT MAXとか(パケットセットに対応する値が唯一である以上)を使ってもいいのですが、下と同じように書いてあるのですが、有木有?

select * from subcategory
where id in (
    select c.id
    from subcategory as c
        inner join item as i on i.subcategory = c.id
    where c.category = 14
    group by c.id
    having count(*) > 2000
);

この検索を終えたら、夜明けから夕日が大地に沈むだろう.私はそれがどのくらい走るか分かりません.私はそれを絶え間なく走らせるつもりはありませんから.文から理解すると、a)内のクエリを計算し、その10の値を見つけ、b)その10のレコードを見つけ続け、primaryインデックスで探すのが非常に速いと思います.間違っています.これは実際のクエリー計画です.

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: subcategory
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 300783
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: c
         type: ref
possible_keys: PRIMARY,category
          key: category
      key_len: 4
          ref: const
         rows: 100
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: i
         type: ref
possible_keys: subcategory
          key: subcategory
      key_len: 4
          ref: c.id
         rows: 28
        Extra: Using index

mysqlの文クエリー計画を分析する方法に慣れていない方法については、mysql計画は内側から外側へではなく、外側から内側へクエリーを実行します.クエリーの各セクションを一つ一つ紹介します.
外の検索は簡単にSELECT*FROM subcategoryになりました.内部のクエリはsubcategoryに制約がありますが(WHERE category=14)、mysqlは何らかの理由で外部のクエリには機能しません.私は神馬の原因だとは知らなかった.テーブル全体をスキャンし(これがtype:ALLで表される意味)ただけで、インデックスは何も使用されていません.これは10数万行の記録の表をスキャンします.
外部のクエリーでは、各ローに対して内部のクエリーが1回実行されます.内部のクエリーは外部のクエリーを参照するために「最適化」されているため、値はありませんが、内部のクエリーに使用されます.この解析により、クエリー・プランはネストされたループになります.外部のクエリーのループごとに、内部のクエリーが実行されます.次に、オプティマイザが書き換えたクエリー・プランを示します.

select * from subcategory as s
where (
   s.id,(
   select c.id
   from subcategory as c
      join item as i
   where ((i.subcategory = c.id) and (c.category = 14))
   group by c.id
   having ((count(0) > 2000)
      and ((s.id) = (c.id))))
)

EXPLAIN EXTENDEDの後ろにSHOW WARNINGSを付けることで、最適化されたクエリーを得ることができます.HAVING句で指す外部ドメインに注意してください.
私がこの例を挙げたのはmysqlの最適化戦略を意図的に批判するわけではない.mysqlは、ネストされたクエリをうまく最適化できない場合があることはよく知られていますが、この問題は広く報告されています.開発者は、クエリ文が悪い最適化されていないことを確認する必要があることを指摘したい.多くの場合、安全のために必要でない場合は、ネストは避けます.特にWHERE...IN()とWHERE...NOT IN文.
私自身の原則は「疑問があればEXPLAINで見て」です.大きなデータテーブルに直面していると、自然と疑問が生まれます.
どのように中のクエリを強制して前のセクションの文衝突板を先に実行するかはmysqlがそれを関連する文として外から中へ実行するためであり、関連しない文として中から外へ実行するわけではない.mysqlに中のクエリーを先に実行させるのも方法があり、一時テーブルとして実現し、大きなパフォーマンスオーバーヘッドを回避します.
mysqlは、一時テーブルからネストされたクエリー(ある程度誤魔化された派生テーブル)を実現します.これはmysqlが先に中のクエリーを実行し、結果を一時テーブルに格納し、他のテーブルで使用することを意味します.これが私がこのクエリーを書くときに期待する実行方法です.クエリ文は次のように変更されます.

select * from subcategory
where id in (
    select id from (
        select c.id
        from subcategory as c
            inner join item as i on i.subcategory = c.id
        where c.category = 14
        group by c.id
        having count(*) > 2000
    ) as x
);

私がやったのは、ネストを元のネストクエリーに包むことです.mysqlは、最も内側が独立したネストされたクエリーが先に実行され、その後、外側を包んだネストしか残っていないと考えられます.それは一時的なテーブルに組み込まれており、わずかな記録しかありません.そのため、ずっと速くなります.この分析によると、これはかなり愚かな最適化方法である.むしろjoin方式に書き直したほうがいいです.それに、他人に見られないように、余計なコードとして片付けます.
mysqlがエラーを投げ出したり、ネストされたクエリーのテーブルが他の場所で変更されたりするなど、この最適化方法を使用することができます(注:別の記事MySQL SELECTと同時にUPDATEの同じテーブル).残念なことに、テンポラリ・テーブルがクエリー・ステートメントで一度しか使用できない場合、この方法はできません.
転載は出典を明記してくださいhttp://blog.csdn.net/afeiqiang/article/details/8620038
セクション:自http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/