回覆列表
  • 1 # 愛可生雲資料庫

    在滿足語句需求的情況下,儘量少的訪問資源是資料庫設計的重要原則,這和執行的 SQL 有直接的關係,索引問題又是 SQL 問題中出現頻率最高的,常見的索引問題包括:無索引(失效)、隱式轉換。1. SQL 執行流程看一個問題,在下面這個表 T 中,如果我要執行 需要執行幾次樹的搜尋操作,會掃描多少行?

    這分別是 ID 欄位索引樹、k 欄位索引樹。

    這條 SQL 語句的執行流程:

    1. 在 k 索引樹上找到 k=3,獲得 ID=3002. 回表到 ID 索引樹查詢 ID=300 的記錄,對應 R33. 在 k 索引樹找到下一個值 k=5,ID=5004. 再回到 ID 索引樹找到對應 ID=500 的 R4

    5. 在 k 索引樹去下一個值 k=6,不符合條件,迴圈結束

    這個過程讀取了 k 索引樹的三條記錄,回表了兩次。因為查詢結果所需要的資料只在主鍵索引上有,所以必須得回表。所以,我們該如何透過最佳化索引,來避免回表呢?2. 常見索引最佳化2.1 覆蓋索引覆蓋索引,換言之就是索引要覆蓋我們的查詢請求,無需回表。

    如果執行的語句是 ,這樣的話因為 ID 的值在 k 索引樹上,就不需要回表了。

    覆蓋索引可以減少樹的搜尋次數,顯著提升查詢效能,是常用的效能最佳化手段。

    但是,維護索引是有代價的,所以在建立冗餘索引來支援覆蓋索引時要權衡利弊。

    2.2 最左字首原則

    B+ 樹的資料項是複合的資料結構,比如 的時候,B+ 樹是按照從左到右的順序來建立搜尋樹的,當 這樣的資料來檢索的時候,B+ 樹會優先比較 name 來確定下一步的檢索方向,如果 name 相同再依次比較 sex 和 age,最後得到檢索的資料。

    可以清楚的看到,A1 使用 tl 索引,A2 進行了全表掃描,雖然 A2 的兩個條件都在 tl 索引中出現,但是沒有使用到 name 列,不符合最左字首原則,無法使用索引。所以在建立聯合索引的時候,如何安排索引內的欄位排序是關鍵。評估標準是索引的複用能力,因為支援最左字首,所以當建立(a,b)這個聯合索引之後,就不需要給 a 單獨建立索引。原則上,如果透過調整順序,可以少維護一個索引,那麼這個順序往往就是需要優先考慮採用的。上面這個例子中,如果查詢條件裡只有 b,就是沒法利用(a,b)這個聯合索引的,這時候就不得不維護另一個索引,也就是說要同時維護(a,b)、(b)兩個索引。這樣的話,就需要考慮空間佔用了,比如,name 和 age 的聯合索引,name 欄位比 age 欄位佔用空間大,所以建立(name,age)聯合索引和(age)索引佔用空間是要小於(age,name)、(name)索引的。

    2.3 索引下推

    以人員表的聯合索引(name, age)為例。如果現在有一個需求:檢索出表中“名字第一個字是張,而且年齡是26歲的所有男性”。那麼,SQL 語句是這麼寫的

    透過最左字首索引規則,會找到 ID1,然後需要判斷其他條件是否滿足在 MySQL 5.6 之前,只能從 ID1 開始一個個回表。到主鍵索引上找出資料行,再對比欄位值。而 MySQL 5.6 引入的索引下推最佳化(index condition pushdown),可以在索引遍歷過程中,對索引中包含的欄位先做判斷,直接過濾掉不滿足條件的記錄,減少回表次數。這樣,減少了回表次數和之後再次過濾的工作量,明顯提高檢索速度。

    2.4 隱式型別轉化

    隱式型別轉化主要原因是,表結構中指定的資料型別與傳入的資料型別不同,導致索引無法使用。所以有兩種方案:

    修改表結構,修改欄位資料型別。

    修改應用,將應用中傳入的字元型別改為與表結構相同型別。

    3. 為什麼會選錯索引3.1 最佳化器選擇索引是最佳化器的工作,其目的是找到一個最優的執行方案,用最小的代價去執行語句。在資料庫中,掃描行數是影響執行代價的因素之一。掃描的行數越少,意味著訪問磁碟資料的次數越少,消耗的 CPU 資源越少。當然,掃描行數並不是唯一的判斷標準,最佳化器還會結合是否使用臨時表、是否排序等因素進行綜合判斷。

    3.2 掃描行數

    MySQL 在真正開始執行語句之前,並不能精確的知道滿足這個條件的記錄有多少條,只能透過索引的區分度來判斷。顯然,一個索引上不同的值越多,索引的區分度就越好,而一個索引上不同值的個數我們稱為“基數”,也就是說,這個基數越大,索引的區分度越好。

    MySQL 使用取樣統計方法來估算基數:取樣統計的時候,InnoDB 預設會選擇 N 個數據頁,統計這些頁面上的不同值,得到一個平均值,然後乘以這個索引的頁面數,就得到了這個索引的基數。而資料表是會持續更新的,索引統計資訊也不會固定不變。所以,當變更的資料行數超過 1/M 的時候,會自動觸發重新做一次索引統計。

    在 MySQL 中,有兩種儲存索引統計的方式,可以透過設定引數 innodb_stats_persistent 的值來選擇:

    on 表示統計資訊會持久化儲存。預設 N = 20,M = 10。

    off 表示統計資訊只儲存在記憶體中。預設 N = 8,M = 16。

    由於是取樣統計,所以不管 N 是 20 還是 8,這個基數都很容易不準確。所以,冤有頭債有主,MySQL 選錯索引,還得歸咎到沒能準確地判斷出掃描行數。

    可以用 來重新統計索引資訊,進行修正。

    3.3 索引選擇異常和處理1. 採用 force index 強行選擇一個索引。2. 可以考慮修改語句,引導 MySQL 使用我們期望的索引。3. 有些場景下,可以新建一個更合適的索引,來提供給最佳化器做選擇,或刪掉誤用的索引。

  • 中秋節和大豐收的關聯?
  • 白色衣服的紐扣生鏽了,那個鏽粘在衣服上了,怎麼洗都洗不掉,有什麼方法可以洗掉?