索引常見模型:
哈希表:適用於等值查詢,區間查詢速度很慢
有序數組:等值,區間查詢性能優秀,適用與靜態搜索引擎。 (更新成本高)
搜索樹:N叉樹應用為主(減少樹高和磁盤交互次數),InnoDB B+樹。
其他:跳錶,LSM樹等
哈希表結構
哈希表是一種以鍵 - 值(key-value)存儲數據的結構,我們只要輸入待查找的值即 key,就可以找到其對應的值即 Value。哈希的思路很
簡單,把值放在數組裡,用一個哈希函數把 key 換算成一個確定的位置,然後把 value 放在數組的這個位置。
不可避免地,多個 key 值經過哈希函數的換算,會出現同一個值的情況。處理這種情況的一種方法是,拉出一個鏈表
InnoDB 的索引模型
每一個索引在 InnoDB 裡面對應一棵 B+ 樹。
主鍵索引的葉子節點存的是整行數據,也叫聚簇索引(InnoDB中)
非主鍵索引的葉子節點內容是主鍵的值。
基於非主鍵索引的查詢需要多掃描一棵索引樹。因此,我們在應用中應該儘量使用主鍵查詢。
自增主鍵的插入數據模式,正符合了我們前面提到的遞增插入的場景。每次插入一條新記錄,都是追加操作,都不涉及到挪動其他記錄,也不會觸發葉子節點的分裂。
主鍵長度越小,普通索引的葉子節點就越小,普通索引佔用的空間也就越小
所以自增主鍵往往是合理的選擇。
重建索引的過程會創建一個新的索引,把數據按順序插入,這樣頁面的利用率最高,索引更緊湊,更省空間。
主鍵索引只能重新建表才能重建索引。(錢的教訓)刪除數據,索引還在,索引樹還是會很大。
覆蓋索引
由於覆蓋索引可以減少樹的搜索次數,顯著提升查詢性能,所以使用覆蓋索引是一個常用的性能優化手段。
如果現在有一個高頻請求,要根據市民的身份證號查詢他的姓名,這個聯合索引就有意義了。它可以在這個高頻請求上用到覆蓋索引,不再需要回表查整行記錄,減少語句的執行時間。
當然,索引字段的維護總是有代價的。因此,在建立冗餘索引來支持覆蓋索引時就需要權衡考慮了。這正是業務 DBA,或者稱為業務數據架構師的工作。
最左前綴原則
這個最左前綴可以是聯合索引的最左 N 個字段,也可以是字符串索引的最左 M 個字符。
第一原則是,如果通過調整順序,可以少維護一個索引,那麼這個順序往往就是需要優先考慮採用的。
索引下推
而 MySQL 5.6 引入的索引下推優化(index condition pushdown), 可以在索引遍歷過程中,對索引中包含的字段先做判斷,直接過濾掉不滿足條件的記錄,減少回表次數
\tmysql> select * from tuser where name like '張 %' and age=10 and ismale=1;
閱讀更多 GT七妖妖 的文章