MySQL索引
資料庫的三正規化，反模式
資料庫正規化是為了解決關聯式資料庫中資料冗餘、更新異常、插入異常、刪除異常等問題而引入的。資料正規化可以避免資料冗餘、節省空間、增加維護便利性
-
第一正規化（原子性）
強調屬性的原子性，要求屬性不可再分解 -
第二正規化（唯一約束性）
強調記錄的唯一約束性，表中必須有一主鍵，且非主鍵不能只依賴主鍵的一部分 -
第三正規化（冗餘性約束）
強調屬性的冗餘性約束，非主鍵列必須直接依賴於主鍵 -
反模式
正規化設計下，資料庫業務設計的表可能增多，涉及到多表聯合查詢，導致效能變差。因此，出於效能優先的考量，在資料庫結構中使用反模式的設計，利用空間換取時間，採用資料冗餘的方式避免多表聯合查詢。資料一致性的問題，可選擇儘可能使資料達到使用者一致，保證系統一段時間的自我恢復和修正，最終達成一致。
零碎知識
- 資料型別
數值、日期、字串
- 可變長度型別資料
可變長度型別中varchar(200)與varchar(50)儲存hello所佔空間相同，但前者在排序時會佔用更多記憶體
- 自增id刪除之後的記錄
InnoDB引擎只將當前自增主鍵的最大id存到記憶體中，重啟後可能會使最大id丟失；而MyISAM會將最大id記錄到資料檔案中
- count 語句的統計
沒有where限定時，InnoDB的count(*)相對MyISM可能慢很多，因為InnoDB是實時統計。而MyISM維護了一個計數器
索引
- 優缺點
優點：
提高檢索速度，降低IO成本
事前排序，降低查詢時CPU消耗
缺點：
增加儲存空間、降低表更新速度
-
使用場景
小型表不建議使用，適用於中大型表；對特大型表索引的代價更大，可對資料庫表進行分割槽 -
索引的六種型別
普通索引、唯一索引（唯一性）、主鍵索引（不允許為空）、複合索引、外建索引、全文索引 -
索引建立原則
出現在where語句後，而不是select；索引基數越大，效果越好；有時複合索引提高效率；過多索引會佔用磁碟空間；主鍵儘量選取較短的資料型別；字串索引應建立一個字首長度，節省索引空間。 -
索引使用注意事項
避免在where後面使用邏輯或表示式操作、使用OR連線條件
索引原理
預設使用B Tree索引
B Tree索引
以M路BTree結構為例（M>=2，否則為空樹）
- 排序方式：所有節點都遞增排序
- 子節點數：1<非葉子節點數<=M
- 關鍵字數：ceil(M/2)-1 < 關鍵字數 < M-1
- 葉子節點：所有子節點均在同一層，包換關鍵字及關鍵字記錄的地址（此外，也有指向其子節點的指標，值為null）
B+Tree索引
對B Tree索引的一種優化，B+Tree中所有資料記錄節點都按照鍵值大小順序存放在同一層節點上，每個非葉子節點都只儲存key值資訊，這樣可大大增加每個節點儲存的key值數量，降低樹的高度
- 非葉子節點只記錄鍵資訊
- 所有葉子節點之間都有一個鏈指標
- 資料記錄都放在葉子節點中
B Tree 與 B+Tree的比較
- B+樹層級更少：B+樹每個非葉子節點儲存的關鍵字更多，所以層級更少，查詢速度更快
- B+樹查詢更穩定：B+樹所有關鍵字地址都儲存在葉子節點上，所以每次查詢次數相同，查詢速度更穩定
- B+樹具有天然的排序功能：B+樹所有葉子節點資料構成了一個有序連結串列，在查詢區間資料時更方便，資料緊密性高，快取命中率也高
- B+樹全域性遍歷更快：B+樹只需要遍歷所有葉子節點，而不需要像B樹一層層進行遍歷
- B數對根節點附近的資料訪問速度更快：因為B數非葉子節點本身存有關鍵字其資料地址
聚集索引和輔助索引
聚集索引（主鍵索引），其葉子節點儲存的資料是整行的具體資料；
輔助索引（二級索引），其葉子節點存的是整行資料對應的主鍵值，根據輔助索引查詢資料要經過兩步，即回表：
- InnoDB 儲存引擎會遍歷輔助索引找到主鍵
- 再通過主鍵在聚集索引中找到完整的行記錄資料
- 聚集索引的主鍵
- 定義主鍵時，InnoDB 儲存引擎會將其當做聚集索引
- 沒有定義主鍵時，InnoDB 儲存引擎會定位到第一個唯一索引，且改索引的所有列值均為非空，將其當做聚集索引
- 沒有主鍵且沒有適合的唯一索引，InnoDB 儲存引擎產生一個ID值6位元組的聚集索引
聚集索引的注意事項
- 插入速度嚴重依賴插入順序，按照主鍵的順序插入是最快的方式，否則出現頁分裂 會影響效能。（因此，一般定義一個自增的Id作為主鍵）
- 更新主鍵會導致更新的行移動，因此一般定義主鍵為不可更新
- 二級索引訪問需要兩次查詢，第一次找到主鍵值，第二次找到行資料。（但是當查詢資料只有id和索引時，可一次查詢直接返回資料，即索引覆蓋）
- 主鍵id建議使用整型。如此，主鍵索引的B+樹節點可以儲存更多主鍵id，輔佐索引的B+樹節點可以儲存更多主鍵id
索引的最左匹配特徵
當索引種類是複合索引時，B+樹通過從左往右建立搜尋樹，即索引的最左匹配特徵