MySQL索引的詳解及實例教程

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
........

【相关推荐】

1. 特别推荐：“php程序员工具箱”V0.1版本下载

2. 免费mysql在线视频教程

3. 数据库设计那些事

以上是MySQL索引的詳解及實例教程的詳細內容。更多資訊請關注PHP中文網其他相關文章！