SQL CREATE INDEX 語句

在本教程中,你將學習如何在表上建立索引,以提高資料庫的效能。

什麼是索引

索引是與表關聯的資料結構,該表基於一個或多個列(索引鍵)中的值提供對錶中行的快速訪問。

假設你在資料庫中有一個 customers 表,並且你希望使用以下語句找出名稱以字母 A 開頭的所有客戶。

SELECT cust_id, cust_name, address FROM customers 
WHERE cust_name LIKE 'A%';

要查詢此類客戶,伺服器必須在 customers 表中逐個掃描每一行,並檢查名稱列的內容。雖然它適用於行數較少的表,但想象如果表包含數百萬行,則可能需要多長時間才能回答查詢。在這種情況下,你可以通過將索引應用於表來加快速度。

建立索引

你可以使用以下 CREATE INDEX 語句建立索引 :

CREATE INDEX index_name ON table_name (column_name); 

例如,要在 customers 表的 name 列上建立索引,可以使用:

CREATE INDEX cust_name_idx ON customers (cust_name);

預設情況下,索引將允許重複條目並按升序對條目進行排序。要要求唯一索引條目,請在 UNIQUE 之後新增關鍵字 CREATE ,如下所示:

CREATE UNIQUE INDEX cust_name_idx 
ON customers (cust_name);

在 MySQL 中,你可以檢視特定表上的可用索引,如下所示:

mysql> SHOW INDEXES FROM customers \G 

提示:\G 終止一個 SQL 語句,而不是用 ;,這樣假如他們對於當前視窗太寬的話,將垂直顯示結果,而不是正常的表格格式。

建立多列索引

你還可以構建跨多個列的索引。例如,假設你的資料庫中有一個名為 users 的表,其中包含 first_namelast_name 列,並且你經常使用這些列訪問使用者的記錄,那麼你可以在兩個列上一起構建索引以提高效能,如下所示:

CREATE INDEX user_name_idx ON users (first_name, last_name); 

提示: 你可以將資料庫索引視為書籍的索引部分,以幫助你快速查詢或查詢書籍中的特定主題。

索引的缺點

應謹慎建立索引。因為,每次在表中新增,更新或刪除行時,都必須修改該表上的所有索引。因此,你擁有的索引越多,伺服器需要執行的工作就越多,最終會導致效能降低。

以下是建立索引時可以遵循的一些基本準則:

  • 你經常用於檢索資料的索引列。
  • 不要為從未用作檢索鍵的列建立索引。
  • 用於連線的索引列,以提高連線效能。
  • 避免使用包含太多 NULL 值的列。

此外,小表不需要索引,因為在小表的情況下,伺服器掃描表通常更快,而不是首先檢視索引。

注意: 建立表時,大多數資料庫系統(如 MySQL,SQL 伺服器等)會自動為 PRIMARY KEYUNIQUE 列建立索引。

刪除索引

你可以使用以下語句刪除不再需要的索引。

DROP INDEX index_name ON table_name ; 

下面的語句將從 customers 表刪除索引 CUST_NAME_IDX

DROP INDEX cust_name_idx ON customers;

此外,如果刪除表,則還會刪除所有關聯的索引。

警告: 你應該在刪除索引之前徹底調查。作為一般經驗法則,不要盲目地建立或刪除索引。