索引

Lou18
·
(修改过)
·
IPFS
·

B+ 樹索引的兩大類:

  • 叢集索引 clustered index
  • innodb 自動建立的;找到索引等於找到資料。
    1. Primary key (PK)
    2. 沒有 pk 則選擇一個非空的、有唯一性的欄位做索引
    3. 如果上述兩者都沒有,innodb 會自動產生一個隱藏的欄位來建立索引
  • 輔助索引 secondary index
  • 自行建立的;找到索引等於找到資料。
  • 預設為覆蓋索引(*前綴索引不是覆蓋索引)

搜尋流程一般是先跑輔助索引,再跑叢集索引。如果只用 PK 則只會跑叢集索引,如果只用覆蓋索引就會跑輔助索引。

查詢語句所要求的欄位如果全部命中了索引,就不需要再進行回表查詢→下對 sql,只需用輔助索引就找到資料,也就不需再去叢集索引。

使用叢集索引與輔助索引的比較表

索引的原理就是把無序的數據變成有序的查詢。

  1. 將創建了索引的列的值進行排序
  2. 將排序結果生成倒排表
  3. 倒排表內容上拼上數據地址鏈
  4. 查詢時,先拿倒排表内容,再取出數據地址鏈,最後取得具體數據

以空間換取時間來加快查詢速度;
索引越多,所需的記憶體跟維護索引的cpu運算就需要更多。

  • 每個索引都會建立一顆 b+ 樹。
  • 資料操作(增/改/刪)時,都會改變 b+ 樹。

索引的設計流程:

  • 找出索引欄位的候選
  1. 定義需求或預測,找出查詢頻度較高的候選欄位
  • 判斷候選欄位的基數(欄位內有可能的值)大小
  1. 如果基數值很小(例如性別),就不建議建立
  2. 基數值越接近1,越適合建立索引
  3. 預測預測可能會有誤差,可在建立一段時間後再決定要補或移除索引
SELECT COUNT(DISTINCT {field}/COUNT(*))
-- 1000筆資料中的性別(僅男女的情況下)欄位基數:2/1000 = 0.002
CC BY-NC-ND 2.0 授权

喜欢我的作品吗?别忘了给予支持与赞赏,让我知道在创作的路上有你陪伴,一起延续这份热忱!