mysql索引原理及優化

• mysql索引結構：

  • mysql索引使用B+tree，為什麼使用B+tree呢，首先，使用索引是為了加快查詢的速度，B+tree的查詢時間複雜度為log(n).那為什麼不用o(1)的hashMap呢。mysql是有使用hashMap結構的hash索引的，但大部分情況下，我們使用的索引並不是hash索引，主要是hash索引這種結果在處理 !=, > ，< 這種範圍查詢時，需要全表掃描，時間複雜度為o(n).
  • 為什麼不使用B tree？ BTree和B+Tree的區別是，B+Tree的非葉子結點只儲存索引，不儲存資料，這樣一個節點儲存的資料更多，樹的高度更低，在讀取索引時，可以省IO(其實，這裡降低樹高度基本沒啥用，因為往往我們的Tree的每個節點的度都很大，BTree和B+Tree高度基本差不多)。另一個主要的作用是，由於資料節點都在葉子結點上，而每個葉子結點又使用雙向連結串列連結，這樣，在處理範圍查詢時，只需要查定一個下界，然後在葉子結點上遍歷即可，且天然有序。

• 主鍵索引和二級索引

  • 以Innodb為例：主鍵索引是和資料檔案放在一塊的，即資料檔案在葉子結點上。對於主鍵索引，找到了主鍵索引，主鍵索引對應的value即為資料row。而非主鍵索引被稱為二級索引的原因是，非主鍵索引的value存放的是主鍵的值，我們在使用非主鍵索引查詢時，需要先根據索引找到主鍵，然後根據主鍵去找資料row。根據主鍵再去找資料row的過程稱為回表。因為對於這樣的索引，不直接和資料關聯，所以稱為二級索引。對於二級索引，如果我們查詢的列，已經全部在索引列裡了，這時候就不需要回表了，這種索引成為覆蓋(covered)索引.
  • 主鍵索引也叫聚簇索引，二級索引也叫非聚簇索引

• explain索引優化。

  • 當我們想對一個sql語句進行優化時，可以用explain檢視當前sql的執行計劃。對於explain的輸出，幾個重要的如下。
  • Type：
    • const： 用主鍵匹配
    • eq_ref: 兩個表join的時候，join的key是兩個表的主鍵。這時候，對於前一個表的每一行，後一張表只需要掃描一行 One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.
    • ref: 通過非唯一索引掃描，通常不需要進行排序時，只要通過ref或者最左字首匹配就可以了
    • range: range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators.
    • index： 使用索引，但比如在掃描之後 還需要order by. 這時候，需要掃描整個索引樹。
    • all： 全表掃。
    • 通常情況下，我們優化的目標到ref就可以了
  • extra：
    • use index： 僅需要使用索引，不需要回表。
    • use where： 通過where子句過濾，where子句過濾儲存引擎返回的結果。
    • use filesort 需要使用排序

• 建立索引和索引匹配的原則

  • 對於聯合索引，比如(row1,row2,row3)這種，mysql按照最左字首匹配的時候，相當於給我們建了(row1),(row1,row2),(row1,row2,row3)三個索引。因此當有聯合索引的時候，不再需要單獨建立額外的單列索引。
  • 資料查詢時，where自己後面的順序無所謂，mysql會自動幫你優化。
  • where 後面在使用or 查詢的時候，大部分情況下不會走到索引。所以，對於這種查詢，可以使用union來優化In many cases, MySQL won't be able to use an index to apply an OR condition, and as a result, this query is not index-able.Therefore, we recommend to avoid such OR conditions and consider splitting the query to two parts, combined with a UNION DISTINCT (or even better, UNION ALL, in case you know there won't be any duplicate results）
  • 建索引時，範圍欄位放在聯合索引的最後，因為按照最左字首匹配原則，碰到範圍欄位就終止匹配了，後面的欄位不會去匹配。
  • 區分度大的欄位在建索引時放在前面。 區分度公式：count(distinct col)/count(*)，就是一個欄位當選擇了一個值時，要能過濾掉大部分欄位。

• mysql NULL

  • NULL is not data type
  • NULL is a value place holder for optional table fields.
  • MySQL treats the NULL value differently from other data types. The NULL values when used in a condition evaluates to the false Boolean value.
  • Performing arithmetic operations on NULL values always returns NULL results.
  • The comparison operators such as [, =, etc.] cannot be used to compare NULL values.
  • ‘+ - * / = != ’這些操作在作用於NULL時，永遠返回NULL，在返回NULL做條件判斷時返回false。
  • 對於NULL的判斷，使用is NULL 和is not NULL來判斷，使用= ， !=,都不會得到你想要的結果。
  • 建表時，儘量所有欄位都設定為非NULL，設為NULL時，mysql還需要額外使用欄位來標記是否為NULL。