【MySQL】索引的建立、刪除與閱讀資訊

https://www.flickr.com/photos/8058098@N07/2718571627/

本文將透過實際的 SQL 指令，進行索引的建立、刪除與查看資訊。並且以一般索引、唯一索引與複合索引為例子，在查看索引資訊時，閱讀它們的定義。

一、測試資料

以下是本文會用到的測試資料表，有員工表和產品表。

員工表包含編號、身份證字號與年齡，共 3 個欄位。其中「emp_id」欄位為主鍵。

CREATE TABLE `employee`(
    `emp_id` INT PRIMARY KEY,
    `id_card_number` CHAR(10),
    `age` INT
);

產品表包含產品編號、類別編號與價格，共 3 個欄位。其中「product_id」欄位為主鍵。

CREATE TABLE `product`(
    `product_id` INT PRIMARY KEY,
    `category` INT,
    `price` INT
);

建立索引的指令寫法為 CREATE INDEX 索引名稱 ON 資料表名稱(欄位1, 欄位2);。

以下是對員工表的年齡欄位建立索引：

CREATE INDEX `idx_employee_age` ON `employee`(`age`);

如果設計資料表時，有欄位是存在唯一性質的，意即該欄位不允許重複值，那麼可以透過 UNIQUE 關鍵字來修飾，建立「唯一索引」。

以下是對員工資料表的身份證字號欄位建立索引：

CREATE UNIQUE INDEX `idx_employee_id_card_number` ON `employee`(`id_card_number`);

若在建立唯一索引時，資料表中的該欄位已有重複的資料，則 MySQL 將不會允許建立。請先將現有的資料修改好，去除重複值。

因此在已知該欄位不會有重複值的情況下，建議當初在建立資料表的指令中，直接使用 UNIQUE 關鍵字來修飾欄位，讓 MySQL 自動建立唯一索引。

CREATE TABLE `employee`(
    `emp_id` INT PRIMARY KEY,
    `id_card_number` CHAR(10) UNIQUE,
    `age` INT
);

我們也能挑選多個欄位來建立一個索引，此時稱之為「聯合索引」或「複合索引」（compounded index）。它並不是一次建立多個獨立的索引，而是一個索引會囊括多個欄位。要留意的是，聯合索引的欄位是有先後順序之分的。

以下是針對產品資料表的「類別」與「價格」欄位，建立聯合索引。

CREATE INDEX `idx_product_category_price` ON `product`(`category`, `price`);

這種索引可能適合以下查詢情境：

若要查看指定資料表所擁有的索引，其語法為 SHOW INDEX FROM 資料表名稱;。

以下指令是查看員工資料表的索引資訊。

SHOW INDEX FROM `employee`;

節錄部份結果如下：

Table	Non_unique	Key_name	Column_name
employee	0	PRIMARY	emp_id
employee	0	idx_employee_id_card_number	id_card_number
employee	1	idx_employee_age	age

能看到資料表名稱、欄位是否唯一、索引名稱、欄位名稱等屬性。

其中有個索引叫做「PRIMARY」，那是根據資料表主鍵預先建立的，我們可以推論出它是個叢集索引。

以下指令是查看產品資料表的索引資訊。

SHOW INDEX FROM `product`;

節錄部份結果如下：

Table	Non_unique	Key_name	Seq_in_index	Column_name
product	0	PRIMARY	1	product_id
product	1	idx_product_category_price	1	category
product	1	idx_product_category_price	2	price

索引資訊中有個叫做「Seq_in_index」的屬性，代表聯合索引的欄位順序。

刪除索引的語法為 DROP INDEX 索引名稱 ON 資料表名稱;。其中索引名稱可以經由執行上述查看索引的指令得知，也就是「Key_name」屬性。

DROP INDEX `idx_employee_age` ON `employee`;

以下是刪除員工資料表中，針對年齡欄位所建立的索引。

DROP INDEX `idx_employee_age` ON `employee`;