MySQL 創建索引

對于一個具有大量數據行的表，如果你根據某個查詢條件檢索數據時很慢，可能是因為你沒有在檢索條件相關的列上創建索引。

索引類似于詞典中的目錄。如果您想要在詞典中查詢一個詞，正確的做法是先查看目錄，再根據目錄中的指示到指定的頁面找到相關的詞。正確的索引可以顯著提高從數據庫表中檢索數據行的速度。

MySQL 允許您使用 CREATE INDEX 語句在指定的表上為指定的列創建索引。

索引介紹

索引是一種數據結構，例如 B-Tree，它提高了從表中檢索數據行的速度，但需要額外的寫入和存儲來維護它。

查詢優化器可以使用索引來快速定位數據，而不必針對給定查詢掃描表中的每一行。

當您使用主鍵 或唯一鍵創建表時，MySQL 會自動創建一個名為 PRIMARY 的索引。 該索引稱為聚集索引。

PRIMARY 索引是特殊的，因為索引本身與數據一起存儲在同一個表中。聚集索引強制執行表中行的順序。

PRIMARY 索引以外的其他索引稱為二級索引或非聚集索引。

MySQL CREATE INDEX 語法

您應該按照如下的語法使用 CREATE INDEX 為一個表添加一個索引：

CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

這里，

• UNIQUE 關鍵字表明此索引為唯一索引。它是可選的。
• index_name 是索引的名字。一個表中不應該出現兩個相同名字的索引。
• table_name 是表的名字。
• column_list 是表中的列名。多個列名使用逗號分隔。
• USING 子句指定索引的類型。可選值：BTREE，HASH。 它是可選的。
• algorithm_option 指定刪除索引的算法。它使用以下的語法：

ALGORITHM [=] {DEFAULT | INPLACE | COPY}

ALGORITHM 子句是可選的。默認為 INSTANT。如果不支持 INSTANT，則使用 INPLACE。

使用 DEFAULT 和省略 ALGORITHM 子句效果相同。

以下是對各個算法的說明：

• COPY：對原表的副本進行操作，將原表中的表數據逐行復制到新表中。
• 不允許并發 DML。
• INPLACE: 操作避免復制表數據，但可能會就地重建表。在操作的準備和執行階段，可能會短暫地對表進行獨占元數據鎖定。通常，支持并發 DML。
• INSTANT: 操作只修改數據字典中的元數據。在操作的執行階段，可能會短暫地對表進行獨占元數據鎖定。表數據不受影響，使操作瞬間完成。允許并發 DML。（在 MySQL 8.0.12 中引入）

lock_option 指定刪除索引的并發控制策略。它使用以下的語法：

LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

LOCK 子句是可選的。以下是對各個并發策略的說明：

DEFAULT
給定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的最大并發級別：如果支持，則允許并發讀取和寫入。如果不是，則允許并發讀取（如果支持）。如果不是，則強制執行獨占訪問。
NONE
如果支持，允許并發讀取和寫入。否則，會發生錯誤。
SHARED
如果支持，允許并發讀取但阻止寫入。即使存儲引擎支持給定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并發寫入，寫入也會被阻止。如果不支持并發讀取，則會發生錯誤。
EXCLUSIVE
強制執行獨占訪問。即使存儲引擎支持給定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并發讀/寫，也會這樣做。
在 MySQL 內部，CREATE INDEX 語句被映射為 ALTER TABLE … ADD INDEX … 語句。

MySQL 索引類型

默認情況下，如果您不指定索引類型，MySQL 將創建 B-Tree 索引。下面顯示了基于表的存儲引擎允許的索引類型：

MySQL CREATE INDEX 實例

在以下實例中，我們使用students表進行演示。

創建students表，并添加數據

CREATE TABLE `students`  (`student_id` int(11) NULL DEFAULT NULL,`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`age` int(11) NULL DEFAULT NULL,INDEX `age`(`age` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES (1, '安靖', 18);
INSERT INTO `students` VALUES (2, '平安', 19);
INSERT INTO `students` VALUES (3, '總結', 20);
INSERT INTO `students` VALUES (3, '周杰倫', 18);
INSERT INTO `students` VALUES (4, '張三', 18);
INSERT INTO `students` VALUES (5, '李四', 18);
INSERT INTO `students` VALUES (6, '鐘意', 19);
INSERT INTO `students` VALUES (7, '張宇', 18);
INSERT INTO `students` VALUES (8, '劉浪', 18);

以下語句查找年齡等于18的學生信息：

SELECT * FROM students WHERE age = 18;

查詢結果如下：

您可以是使用 EXPLAIN 來查看以上 SELECT 語句的語句的執行計劃，以了解 MySQL 在內部如何執行此查詢，如下所示：

EXPLAIN SELECT * FROM students WHERE age = 18;

如您所見，MySQL 必須掃描由9行組成的整個表才能找到具有符合條件的行。

現在，使用以下 CREATE INDEX 語句為該列 age 創建索引 ：

 CREATE INDEX age ON students(age);

創建結果：

要查看索引是否創建成功，請使用以下 SHOW INDEXES 語句顯示表 actor 的索引，例如：

SHOW INDEXES FROM students;

查詢結果

然后，再次執行上面的 EXPLAIN 語句：

EXPLAIN SELECT * FROM students WHERE age = 18;

查詢結果如下：

如您所見，MySQL 只需從 age 指示的索引中定位其中的 6 行， 而無需掃描整個表。所以會提高查詢速度。

結論

在 MySQL 中，索引能提高從表中查詢數據的效率。您可以使用 CREATE INDEX 為表創建索引。