1. 首頁 > 生活百科 > >

Mysql單表訪問方法，索引合并，多表連接原理，基于規則的優化，子查詢優化( 二 )

生活百科

那么是無法進行交集索引合并的,因為a>1得到的主鍵并不是有序的 ， 

  • 同樣聯合索引q,w,e 普通索引r 執行select * from table where q=1 and w=2 and r=3也不可以使用交集索引合并，因為聯合索引是依次根據q,w,e排序的，滿足q=1 and w=2的數據主鍵并不是有序的 。
  • 普通索引a，主鍵為id，select * from a=1 and id>100這樣的查詢理論上是主鍵有序可與使用的，但是mysql會找到滿足a=1且id>100的第一條記錄，然后向右直到不符合條件的數據出現，這種情況也不需要使用交集索引合并
    • 2.并集索引合并select * from table where a>1 or b>2 a,b均為普通索引，無法只使用a或者b索引進行查詢，但是可分別從a和b中獲取滿足條件的主鍵，然后取二者并集后回表即可 。這稱之為并集索引合并，同樣也是要求從單個索引獲取到的主鍵值是有序的，
    3.排序并集索引合并并集索引合并要求根據單個索引獲取到的主鍵是有序的，然后取并集回表 ， 條件比較苛刻 。mysql支持分別從各個索引中掃描得到記錄的主鍵讓排序 ， 再取并集進行回標查詢，這種稱為排序并集索引合并
    四丶連表查詢的原理上面我們研究了單表查詢，下面我們學習一下多表連接查詢
    1.連接的本質連接的本質就是將各個表中的數據都取出來進行依次匹配，并且將匹配后的結果發送回客戶端 。無論那個表作為驅動表產生的笛卡爾積肯定是一樣的，而對于內連接來說凡是不符合on和where的查詢都不會加入到結果集，內連接的驅動表和被驅動表可以交換，但是對于外連接來說，驅動表中的記錄即使在被驅動表中找不到符合on的記錄，也會被加入到結果集，驅動表和被驅動表不可隨意交換
    Mysql單表訪問方法，索引合并，多表連接原理，基于規則的優化，子查詢優化

    文章插圖
    2.連接中的過濾條件在連接時過濾掉特定的記錄是非常必要的（如果不進行過濾那么就是多表的笛卡爾積，條數是多個表條數的乘積）多表連接查詢中的過濾條件分為一下兩種
    • 單表查詢的搜索條件
      比如select * from a left join b where a>1 ,這里的單表查詢搜索條件就是a>1,如果a是索引那么將使用單表查詢的range訪問方法
    • 涉及兩表的條件
      比如select * from a , b where a.k1 = b.k2 and a.k3>b.k4，這里涉及的條件便是a.k1 = b.k2 and a.k3>b.k4，對于這種擦好像執行過程如下
      1. 首先確定第一個需要查詢的表,稱為驅動表
        mysql會選擇代價最小的訪問方法對驅動表進行查詢
      2. 1中的驅動表獲取到的每一條記錄，都需要去另外一張表中查詢匹配的記錄
        比如在a作為驅動表獲取到了a.k1=1,a.k3=2 那么接下來需要去表b中查詢滿足1=b.k2 and 2>b.k4的記錄（并不是將在驅動表中獲取到的記錄緩存起來，再一起到被驅動表中進行查詢，而是每在驅動表中獲取到一條記錄都會到被驅動表中查詢滿足的數據）
    3.內連接和外連接
    • 對于內連接的兩個表，如果驅動表中的記錄在被驅動表中找不到匹配的記錄，那么該記錄不會加入到最后的結果集 。
      如select * from user_detail d ,user u where u.id = d.id查詢結果如下
      Mysql單表訪問方法，索引合并，多表連接原理，基于規則的優化，子查詢優化

      文章插圖
      user_detail 中具備非常多的記錄，但是和user 中id匹配的記錄只有id=3，這里也只能查出一條記錄
    • 對于外連接的表，即使驅動表中的記錄在被驅動表中找不到匹配的記錄，那么也會加入到結果集中
      如select * from user_detail d left join user u onu.id = d.id查詢結果如下
      Mysql單表訪問方法，索引合并，多表連接原理，基于規則的優化，子查詢優化

      文章插圖
      即使在onu.id = d.id的限制下，只有id=3滿足，其他id不滿足 ， 也會將其他記錄加入到結果集，被驅動的字段使用null填充
    4.外連接的分類外連接分為左外連接 即左連接，右外連接 即右連接，二者的區別在于左連接是左邊的表作為驅動表，右連接使用右邊的表作為驅動表 。
    5.where 和 on在外連接中存在where 和 on兩種過濾條件 。
    1. where
      無論是內連接，還是外連接，只要不符合where條件的記錄都不會加入到最后的結果集中

      2. 推薦閱讀