CREATE INDEX 敘述句 (SQL CREATE INDEX Statement)

如同如果一本書有索引 (index) 您可以更快的找到需要的資料，資料庫也是同樣道理，如果一張資料表中沒有索引，在查詢資料時就必需先把整張資料表讀過一遍 (scan table) 再慢慢去找資料，非常的沒有效率，因此替資料表加上 “適當的” 索引會大大加快查詢速度哦。

為什麼說適當的？因為一個設有索引的資料表當你在 INSERT、UPDATE 或 DELETE 時，索引也需要額外花資源去更新，所以最好只在常被查詢到的欄位作索引，不然得不償失啊！

CREATE INDEX 語法 (SQL CREATE INDEX Syntax)

CREATE INDEX index_name ON table_name (column_name);

CREATE INDEX index_name ON table_name (column_name1, column_name2...);

什麼時候建立多欄位索引比較合適？如果您常對一張資料表查詢 WHERE column_name1='xxx' AND column_name2='yyy'，這時你就可以對 column_name1 及 column_name2 這兩個欄位建立一個共同索引。

我們要怎麼將索引移除掉？就是使用 DROP INDEX。

MySQL

ALTER TABLE table_name DROP INDEX index_name;

SQL Server

DROP INDEX table_name.index_name;

Oracle

DROP INDEX index_name;

MS Access

DROP INDEX index_name ON table_name;

建立索引後，要怎麼確認索引是否真的有被使用到呢？這時候就可以使用 EXPLAIN 來查看 SQL 查詢的執行計畫。執行計畫會告訴你資料庫是如何執行你的查詢，包括是否使用了索引、掃描了多少列資料等資訊，是效能優化時非常重要的工具。

在 MySQL 中，只需在 SELECT 語句前面加上 EXPLAIN 即可：

EXPLAIN SELECT * FROM table_name
WHERE column_name = value;

假設我們有一張 employees 資料表，我們想查詢特定部門的員工。先來看看在沒有索引的情況下，執行計畫長什麼樣子：

EXPLAIN SELECT * FROM employees
WHERE Department = '資訊部';

如果 Department 欄位沒有索引，執行計畫中的 type 會顯示為 ALL，代表進行了全表掃描（Full Table Scan），效率較差。

id	select_type	table	type	possible_keys	key	rows
1	SIMPLE	employees	ALL	NULL	NULL	5

接著我們為 Department 欄位建立索引：

CREATE INDEX idx_department ON employees (Department);

再次執行 EXPLAIN 查看：

EXPLAIN SELECT * FROM employees
WHERE Department = '資訊部';

建立索引後，type 變成了 ref，key 欄位也顯示使用了 idx_department 索引，掃描的列數也大幅減少，查詢效率明顯提升。

id	select_type	table	type	possible_keys	key	rows
1	SIMPLE	employees	ref	idx_department	idx_department	2

欄位	說明
id	查詢的序號
select_type	查詢類型，如 SIMPLE（簡單查詢）、SUBQUERY（子查詢）等
table	查詢的資料表
type	存取類型，效能由好到差為：system > const > eq_ref > ref > range > index > ALL
possible_keys	可能使用的索引
key	實際使用的索引
rows	預估需要掃描的列數，數字越小越好

透過 EXPLAIN 可以幫助我們判斷查詢是否有效地利用了索引，當 type 顯示為 ALL 時，通常代表需要考慮加上適當的索引來優化查詢效能。