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 協議》，轉載必須註明作者和本文連結