索引
B+ 樹索引的兩大類:
- 叢集索引 clustered index
- innodb 自動建立的;找到索引等於找到資料。
1. Primary key (PK)
2. 沒有 pk 則選擇一個非空的、有唯一性的欄位做索引
3. 如果上述兩者都沒有,innodb 會自動產生一個隱藏的欄位來建立索引 - 輔助索引 secondary index
- 自行建立的;找到索引等於找到資料。
- 預設為覆蓋索引(*前綴索引不是覆蓋索引)
搜尋流程一般是先跑輔助索引,再跑叢集索引。如果只用 PK 則只會跑叢集索引,如果只用覆蓋索引就會跑輔助索引。
查詢語句所要求的欄位如果全部命中了索引,就不需要再進行回表查詢→下對 sql,只需用輔助索引就找到資料,也就不需再去叢集索引。
索引的原理就是把無序的數據變成有序的查詢。
- 將創建了索引的列的值進行排序
- 將排序結果生成倒排表
- 在倒排表內容上拼上數據地址鏈
- 查詢時,先拿倒排表内容,再取出數據地址鏈,最後取得具體數據
以空間換取時間來加快查詢速度;
索引越多,所需的記憶體跟維護索引的cpu運算就需要更多。
- 每個索引都會建立一顆 b+ 樹。
- 資料操作(增/改/刪)時,都會改變 b+ 樹。
索引的設計流程:
- 找出索引欄位的候選
- 定義需求或預測,找出查詢頻度較高的候選欄位
- 判斷候選欄位的基數(欄位內有可能的值)大小
- 如果基數值很小(例如性別),就不建議建立
- 基數值越接近1,越適合建立索引
- 預測預測可能會有誤差,可在建立一段時間後再決定要補或移除索引
SELECT COUNT(DISTINCT {field}/COUNT(*)) -- 1000筆資料中的性別(僅男女的情況下)欄位基數:2/1000 = 0.002