MySQL語句的執行順序

1. 書寫順序

select->distinct->from->join->on->where->group by->having->order by->limit

必須字段：select、from

可選字段：distinct、join、on、where、group by、having、sum、count、max、avg、order by、limit

2. 執行順序

from->on->join->where->group by->sum、count、max、avg->having->select->distinct->order by->limit

# 用左連接配接的方式查詢 bumen 表和 yuangong 表。使用 LEFT JOINON 來實作左連接配接。

select bumen.d_id,d_name,function,bumen.address,id,name,birthday,sex,salary,yuangong.address

from bumen 

left join yuangong 

on yuangong.d_id=bumen.d_id;

bumen表與yuangong表進行笛卡爾積運算，根據yuangong.d_id=bumen.d_id篩選出符合條件的行，再根據這是左連接配接， left join一般以左表為驅動表，然後用此表的記錄去關聯其他表，即使其他表對應元組為空，最後執行select操作，選擇出指定的列

FORM: 對FROM的左表和右表計算笛卡爾積。産生虛表VT1

ON: 對虛表VT1進行ON篩選，隻有那些符合的行才會被記錄在虛表VT2中。

JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那麼保留表中未比對的行就會作為外部行添加到虛拟表VT2中，産生虛拟表VT3, 如果 from 子句中包含兩個以上的表的話，那麼就會對上一個join連接配接産生的結果VT3和下一個表重複執行步驟1~3這三個步驟，一直到處理完所有的表為止。

WHERE： 對虛拟表VT3進行WHERE條件過濾。隻有符合的記錄才會被插入到虛拟表VT4中。

GROUP BY: 根據group by子句中的列，對VT4中的記錄進行分組操作，産生VT5.

CUBE | ROLLUP: 對表VT5進行cube或者rollup操作，産生表VT6

HAVING： 對虛拟表VT6，即已經分組的資料進行過濾的條件，隻有符合的記錄才會被 插入到虛拟表VT7中

SELECT： 執行select操作，選擇指定的列，插入到虛拟表VT8中

DISTINCT： 對VT8中的記錄進行去重。産生虛拟表VT9

ORDER BY: 将虛拟表VT9中的記錄按照進行排序操作，産生虛拟表VT10

LIMIT：取出指定行的記錄，産生虛拟表VT11, 并将結果傳回。

3. 關鍵詞含義

• from：需要從哪個資料表檢索資料
• join：對需要關聯查詢的表進行關聯關聯查詢時，資料庫會選擇一個驅動表，然後用此表的記錄去關聯其他表 left join一般以左表為驅動表（right join一般為右表）,inner join一般以結果集少的表為驅動表,left join某些情況下會被查詢優化器優化為inner join

• • 驅動表選擇原則：在對最終結果集沒影響的前提下，優先選擇結果集最少的那張表作為驅動表
  • 在使用索引關聯的情況下，有

    Index Nested-Loop join

    和

    Batched Key Access join

    兩種算法
  • 在未使用索引關聯的情況下，有

    Simple Nested-Loop join

    Block Nested-Loop join

  • Simple Nested-Loop join

    （SNLJ，簡單嵌套循環連接配接）算法：根據on條件，從驅動表取一條資料，然後全表掃面被驅動表，将符合條件的記錄放入最終結果集中。這樣驅動表的每條記錄都伴随着被驅動表的一次全表掃描

• • • 比對次數：外層表行數*内層表行數

• • Index Nested-Loop Join

    （INLJ，索引嵌套循環連接配接）算法：索引嵌套循環連接配接是基于索引進行連接配接的算法，索引是基于内層表的，通過外層表比對條件直接與内層表索引進行比對，避免和内層表的每條記錄進行比較， 進而利用索引的查詢減少了對内層表的比對次數

• • • 比對次數：外層表行數*内層表索引高度

• • Block Nested-Loop Join

    （BNLJ，緩存塊嵌套循環連接配接）算法：緩存塊嵌套循環連接配接通過一次性緩存多條資料，把參與查詢的列緩存到Join Buffer 裡，然後拿join buffer裡的資料批量與内層表的資料進行比對，進而減少了内層循環的次數（周遊一次内層表就可以批量比對一次Join Buffer裡面的外層表資料）。當不使用

    Index Nested-Loop Join

    的時候，預設使用

    Block Nested-Loop Join

  • Batched Key Access join

    （BKAJ）算法：和SNLJ算法類似，但用于被join表上有索引可以利用，那麼在行送出給被join的表之前，對這些行按照索引字段進行排序，是以減少了随機IO，排序這才是兩者最大的差別，但是如果被join的表沒用索引呢？那就使用BNLJ了
  • 什麼是

    Join Buffer

    ?

• • • Join Buffer

      會緩存所有參與查詢的列而不是隻有Join的列。
    • 可以通過調整

      join_buffer_size

      緩存大小

    • join_buffer_size

      的預設值是256K，

      join_buffer_size

      的最大值在

      MySQL 5.1.22

      版本前是

      4G

      ，而之後的版本才能在64位作業系統下申請大于

      4G

      的

      Join Buffer

      空間。
    • 使用

      Block Nested-Loop Join

      算法需要開啟優化器管理配置的

      optimizer_switch

      的設定

      block_nested_loop

      為

      on

      ，預設為開啟。

• • 在選擇Join算法時，會有優先級，理論上會優先判斷能否使用INLJ、BNLJ：Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join
  • 注：可以使用explain查找驅動表，結果的第一張表即為驅動表，但執行計劃在真正執行時可能發生改變

• on：關聯條件
• where：過濾表中資料的條件

• • 執行順序：自下而上、從右到左
  • 注：對資料庫記錄生效，無法對聚合結果生效，可以過濾掉最大數量記錄的條件必須寫在where子句末尾，不能使用聚合函數（sum、count、max、avg）

• group by：如何将上面過濾出的資料分組

• • 執行順序：從左往右
  • 注：盡量在group by之前使用where過濾，避免之後使用having過濾

• avg：求平均值
• having：對上面已經分組的資料進行過濾的條件

• • 注：對聚合結果過濾，是以很耗資源，可以使用聚合函數

  • 例：篩選統計人口數量大于100W的地區

    select region, sum(population), sum(area) from bbc group by region having sum(population)>1000000，不能用where篩選超過100W的地區，因為不存在這樣的一條記錄

• select：檢視結果集中的哪個列或列的計算結果
• distinct：對結果集重複值去重
• order by：按照什麼樣的順序來檢視傳回的資料

• • 執行順序：從左到右
  • 注：很耗資源

• limit：截取出目标頁資料