【explain】MySQL連結テーブルクエリのドライバテーブル

前に書く
1、連結テーブルクエリー(join/left join/inner joinなど)のmysql演算プロセスを必ず理解する必要はありません.
2、オンライン(現在または未来)のどのテーブルのデータ量が大きいか、どのテーブルのデータ量が小さいか、誰もが必ず知っていることを要求しない
3、しかしmysqlクライアント(例えばSQLyog、例えばHeidiSQL)をデスクトップの上に置いて、時々explainを出して、これは1種の美徳です!
インスタンスの説明に先立ち、次の連結テーブル・クエリーの基礎知識をレビューします.
結合テーブルクエリの基礎知識
引用子:なぜ最初のクエリーはusing temporaryで、2番目のクエリーはテンポラリ・テーブルを使用しないのですか.
次の2つのクエリは、order byが1つしか残っていませんが、効果は大きく異なります.
最初のクエリ:

EXPLAIN extended
SELECT ads.id FROM ads, city 
WHERE
   city.city_id = 8005
   AND ads.status = 'online'
   AND city.ads_id=ads.id
ORDER BY ads.id desc

実行計画は次のとおりです.

id  select_type table  type    possible_keys  key          key_len  ref             rows  filtered  Extra
1   SIMPLE        city    ref      ads_id,city_id   city_id     4            const          2838  100.00   Using temporary; Using filesort
1   SIMPLE        ads    eq_ref PRIMARY          PRIMARY 4            city.ads_id   1        100.00   Using where

2番目のクエリ:

EXPLAIN extended
SELECT ads.id FROM ads,city 
WHERE
   city.city_id =8005
   AND ads.status = 'online'
   AND city.ads_id=ads.id
ORDER BY city.ads_id desc

実行計画にはusing temporaryがありません.

id  select_type table type    possible_keys  key           key_len  ref              rows  filtered  Extra
1   SIMPLE        city   ref      ads_id,city_id   city_id      4           const          2838  100.00   Using where; Using filesort
1   SIMPLE        ads   eq_ref  PRIMARY         PRIMARY  4           city.ads_id   1       100.00    Using where

どうして?DBAは次のように教えてくれます.

MySQL         Nest Loop Join，                  ，                                 ，      。

EXPLAINの結果、1行目に出てくるテーブルが駆動テーブル(Important!)
以上の2つのクエリー文は、上記の実行計画に示すように、ドライバテーブルがcityです.
ドライバテーブルは直接ソートできます.非ドライバテーブル(のフィールドソート)は、循環クエリーの集計結果(テンポラリテーブル)をソートする必要があります(Important!)
だからorder by ads.id descの場合、まずusing temporary!
ドライバテーブルの定義
wwh 999は2006年に、マルチテーブル接続クエリーを行う場合、[ドライバテーブル]の定義は以下の通りであるとまとめた.
1)結合条件が指定されている場合、クエリ条件を満たすレコード行数が少ないテーブルは[ドライバテーブル]
2)結合条件が指定されていない場合、行数の少ないテーブルは[ドライバテーブル](Important!)
アドバイス:誰にドライバテーブルをやらせるべきか、誰がjoinをやらせるべきか分からない場合は、MySQLの実行時に自分で判断させてください.
「接続条件が指定されていない場合、行数の少ないテーブルは[ドライバテーブル]です.また、自分が書いた複雑なNested Loop Joinについてもあまり自信がないので(以下の例に示すように)、誰のleft/right joinを指定するのではなく、MySQLオプティマイザの実行時にお任せください.
自分に特に自信があれば、火丁のように最適化することができます.
小結果セット駆動大結果セット
de.celは2012年に、あなたもMySQLも、JOINのNested Loopのサイクル数をできるだけ減らすことを最適化したとまとめた.
これにより、大きな結果セット(Important!)を常に小さな結果セットで駆動することが保証されます.
実例説明
Nested Loop Join SQL文をゆっくり調べる
まずmbテーブルに千万級の記録があることを理解して、mbeiテーブルはずっと少ないです.スローチェックの例は次のとおりです.

explain
SELECT mb.id, ……
FROMmb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid  
WHERE 1=1  
ORDER BY mbei.apply_time DESC
limit 0,10

複雑でしょう.Nested Loop Joinは、テーブルの結果セットをループの基礎データとして駆動し、結果セットのデータをフィルタ条件として次のテーブルに1つずつクエリーし、結果をマージします.
このとき3番目のテーブルがある場合は、前の2つのテーブルのJoin結果セットをループベースデータとし、3番目のテーブルにもう一度ループクエリー条件を介してデータをクエリーすることを繰り返す.
この文の実行計画は次のとおりです.

id select_type table   type    possible_keys   key           key_len  ref       rows       Extra
1  SIMPLE        mb      index   userid              userid       4           (NULL)  6060455 Using index; Using temporary; Using filesort
1  SIMPLE        mbei   eq_ref  mb_id  mb_id   4               mb.id     1
1  SIMPLE        u        eq_ref  PRIMARY          PRIMARY  4            mb.uid   1          Using index

「LEFT JOIN」が動員されたため、攻城ライオンはすでに駆動表を指定したが、この駆動表の結果セット記録数は百万級に達した!
最適化方法
最適化の第一歩:LEFT JOINをJOINに変更して、どうしてleft joinを要しますか?直接join!

explain
SELECT mb.id…… 
FROM mb JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid  
WHERE 1=1  
ORDER BY mbei.apply_time DESC
limit 0,10

すぐに現れて、駆動表はすぐに小表mbeiになって、Using temporaryは消えて、影響行数は少なくなりました:

id select_type table  type     possible_keys     key         key_len  ref                rows    Extra
1  SIMPLE        mbei  ALL       mb_id                 (NULL)      (NULL)   (NULL)          13383  Using filesort
1  SIMPLE        mb     eq_ref  PRIMARY,userid  PRIMARY  4          mbei.mb_id  1
1  SIMPLE        u        eq_ref  PRIMARY            PRIMARY  4          mb.uid          1  Using index

最適化の最初のステップのブランチ1:ドライバテーブルのフィールドに基づいてソートしてもいいですか?
left joinは変わらない.なぜ非駆動テーブルのフィールドに基づいてソートするのですか?前述したように、「ドライバ・テーブルを直接ソートできます.非ドライバ・テーブル(のフィールド・ソート)をソートするには、循環クエリーの集計結果(テンポラリ・テーブル)をソートする必要があります.」のです.

explain
SELECT mb.id…… 
FROM mb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid  
WHERE 1=1  
ORDER BY mb.id DESC
limit 0,10

ビジネスシーンも満たし、rowsを最小限に抑えることができます.

id select_type table  type     possible_keys  key           key_len  ref        rows  Extra
1  SIMPLE        mb     index   userid              PRIMARY  4            (NULL)   10
1  SIMPLE        mbei  eq_ref  mb_id  mb_id   		     4            mb.id     1       Using index
1  SIMPLE        u        eq_ref  PRIMARY         PRIMARY  4            mb.uid   1       Using index

最適化の第2歩:すべてのJOINを除去して、MySQLに自分で決定させます!こんなにびっしり書かれたleft join/inner joinは楽しかったですか?

explain
SELECT mb.id…… 
FROM mb,mbei,u   
WHERE 
    mb.id=mbei.mb_id
    and mb.uid=u.user_id
order by mbei.apply_time desc
limit 0,10

すぐに現れ、駆動表は同じ小表mbeiです.

id select_type table   type    possible_keys      key          key_len  ref                rows    Extra
1  SIMPLE        mbei   ALL      mb_id  (NULL)      (NULL)      (NULL)                         13388 Using filesort
1  SIMPLE        mb      eq_ref  PRIMARY,userid  PRIMARY 4            mbei.mb_id   1
1  SIMPLE        u         eq_ref  PRIMARY            PRIMARY 4            mb.uid           1  Using index

最後のまとめ:
強調して強調する.
1、運を信じすぎないように!
2、あなたの開発環境のSQLの実行速度を信じないでください!
3、explain武器を取ってください.以下の現象を見たら、最適化してください.
1)Using temporaryが出現
2)rowsが多すぎたり、ほぼ全テーブルのレコード数
3)key Yes(NULL)
4)possible_keysインデックスが多すぎる(選択される)
覚えておいて、explainは美徳です!