MySQL 索引 · W3School Sql 教程

# MySQL 索引 MySQL索引的建立對于MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。打個比方，如果合理的設計且使用索引的MySQL是一輛蘭博基尼的話，那么沒有設計和使用索引的MySQL就是一個人力三輪車。索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索包含多個列。創建索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。實際上，索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄。上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。建立索引會占用磁盤空間的索引文件。 ## 普通索引 ### 創建索引這是最基本的索引，它沒有任何限制。它有以下幾種創建方式： ``` CREATE INDEX indexName ON mytable(username(length)); ``` 如果是CHAR，VARCHAR類型，length可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定 length。 ### 修改表結構 ``` ALTER mytable ADD INDEX [indexName] ON (username(length)) ``` ### 創建表的時候直接指定 ``` CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); ``` ### 刪除索引的語法 ``` DROP INDEX [indexName] ON mytable; ``` ## 唯一索引它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創建方式： ### 創建索引 ``` CREATE UNIQUE INDEX indexName ON mytable(username(length)) ``` ### 修改表結構 ``` ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ``` ### 創建表的時候直接指定 ``` CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) ); ``` ## 使用ALTER 命令添加和刪除索引有四種方式來添加數據表的索引： * **ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):** 該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL。 * **ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):** 這條語句創建索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）。 * **ALTER TABLE tbl_name ADD INDEX index_name (column_list):** 添加普通索引，索引值可出現多次。 * **ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):**該語句指定了索引為 FULLTEXT ，用于全文索引。以下實例為在表中添加索引。 ``` mysql> ALTER TABLE testalter_tbl ADD INDEX (c); ``` 你還可以在 ALTER 命令中使用 DROP 子句來刪除索引。嘗試以下實例刪除索引: ``` mysql> ALTER TABLE testalter_tbl DROP INDEX (c); ``` ## 使用 ALTER 命令添加和刪除主鍵主鍵只能作用于一個列上，添加主鍵索引時，你需要確保該主鍵默認不為空（NOT NULL）。實例如下： ``` mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i); ``` 你也可以使用 ALTER 命令刪除主鍵： ``` mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY; ``` 刪除指定時只需指定PRIMARY KEY，但在刪除索引時，你必須知道索引名。 ## 顯示索引信息你可以使用 SHOW INDEX 命令來列出表中的相關的索引信息。可以通過添加 \G 來格式化輸出信息。嘗試以下實例: ``` mysql> SHOW INDEX FROM table_name\G ........ ```