MySQL 之索引常見內容

xiaosheng發表於2021-01-20
MySql索引

索引的概念

  • 索引本質上一種資料結構
  • 索引的種類:
    • 單值索引
    • 主鍵索引
    • 唯一索引
    • 全文索引
    • 複合索引
  • 索引的優勢:
    • 提高資料的查詢效率, 降低資料庫的 IO 成本
    • 通過索引對資料進行排序, 降低資料排序成本, 降低 CPU 消耗
  • 索引的劣勢:
    • 索引也是佔磁碟空間的
    • 索引雖然加快了查詢效率, 但是卻降低了資料寫操作的速度

索引建立的條件

  • 要在頻繁作為條件查詢的條件列上建立索引, 而不是在查詢列中, 也就是說最適合出現索引的列是出現在 where 子句中的列或者連線子句中的列, 而不是出現在 select 關鍵字後的列
  • 儘量使用唯一索引. 索引的列的基數越大, 索引的效果越好. 例如: 這個列是用來記錄性別的, 就只有 男 和 女 這兩個值, 那麼對該欄位加索引的話就沒有太大的用處
  • 使用短索引. 如果某一列中的值特別長, 而且要加索引的話, 應該指定一個字首長度
  • 利用最左字首. 這種情況出現在建立複合索引中, 例如以 a, b, c 的順序建立了一個複合索引後, 要儘量以 a = ? 或者 a = ? and b = ? 或者 a = ? and b = ? and c = ? 這樣的順序來查詢, 這樣就可以利用到這個複合索引
  • 查詢的時候與其他表關聯的欄位, 適合建立外來鍵索引
  • 頻繁更新的欄位, 不適合新增索引
  • 儘量選擇複合索引
  • 查詢中的排序欄位, 儘量新增索引, 會加快排序速度
  • 查詢中統計或者分組的欄位 (分組的前提是必排序)
  • 索引欄位的值太長的時候, 儘量使用字首索引

不適合建立索引的情況

  • 表記錄太少
  • 經常增刪改的表
  • 不是所有的表都需要建立索引. 通常來說, 常見的程式碼表, 配置表等, 除了主鍵索引外, 就沒有必要再去建立其他索引了
  • 不要過度索引. 索引不是越多越好, 每個額外的索引都需要佔用額外的磁碟空間, 並會降低寫操作. 此外, MySQL 在生成一個執行計劃時, 要考慮各個索引, 這個也要花時間
  • 如果某一列中包含了太多重複的內容, 則沒必要建立索引

設計索引的一般步驟

  • 整理表中的 SQL, 重點包括 select, update, delete 操作的 where 條件所用到的列組合, 關聯查詢的關聯條件等
  • 整理所有查詢 SQL 的語句的執行頻率
  • 整理所有設計的列的選擇度, 列中的值的差異度越高越好
  • 給表選擇合適的主鍵
  • 優先給執行頻率最高的 SQL 建立索引
  • 按照執行頻率, 確定是否需要為每個表建立索引
  • 索引合併, 利用複合索引來降低索引的總數, 充分利用最左字首

索引的操作

  • 索引的命名規則:
    • 主鍵索引: pk_欄位名
    • 唯一索引: uk_欄位名
    • 普通索引: idx_欄位名
  • 建立索引
      create [index|unique|fulltext](指的是索引型別) idx_...(索引名稱) on 表名(欄位名);
  // 或者是
  alter table 表名 add 索引型別 索引名稱(欄位名, 可以是多個欄位名, 多個欄位名之間用逗號隔開);
  • 檢視索引
      show index from 表名;
  • 刪除索引
      drop index 索引名稱 on 表名;
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章