MySQL索引操作命令學習筆記

下面我們一起來看看MySQL索引操作命令學習筆記，文章中包括了對mysql索引的創建、刪除、修改、查看等等操作是一篇非常適合新手哦。

mysq索引類型：普通索引、唯一索引和主索引

1. 普通索引

普通索引(由關鍵字KEY或INDEX定義的索引)的唯一任務是加快對數據的訪問速度。因此，應該只為那些最經常出現在查詢條件(WHERE column = …)或排序條件(ORDER BY column)中的數據列創建索引。只要有可能，就應該選擇一個數據最整齊、最緊湊的數據列(如一個整數類型的數據列)來創建索引。

2. 唯一索引

普通索引允許被索引的數據列包含重復的值。比如說，因為人有可能同名，所以同一個姓名在同一個”員工個人資料”數據表裡可能出現兩次或更多次。
如果能確定某個數據列將只包含彼此各不相同的值，在為這個數據列創建索引的時候就應該用關鍵字UNIQUE把它定義為一個唯一索引。這麼做的好處：一是簡化了MySQL對這個索引的管理工作，這個索引也因此而變得更有效率；二是MySQL會在有新記錄插入數據表時，自動檢查新記錄的這個字段的值是否已經在某個記錄的這個字段裡出現過了；如果是，MySQL將拒絕插入那條新記錄。也就是說，唯一索引可以保證數據記錄的唯一性。事實上，在許多場合，人們創建唯一索引的目的往往不是為了提高訪問速度，而只是為了避免數據出現重復。

3. 主索引

在前面已經反復多次強調過：必須為主鍵字段創建一個索引，這個索引就是所謂的”主索引”。主索引與唯一索引的唯一區別是：前者在定義時使用的關鍵字是PRIMARY而不是UNIQUE。

4. 外鍵索引

如果為某個外鍵字段定義了一個外鍵約束條件，MySQL就會定義一個內部索引來幫助自己以最有效率的方式去管理和使用外鍵約束條件。

5. 復合索引

索引可以覆蓋多個數據列，如像INDEX(columnA, columnB)索引。這種索引的特點是MySQL可以有選擇地使用一個這樣的索引。如果查詢操作只需要用到columnA數據列上的一個索引，就可以使用復合索引INDEX(columnA, columnB)。不過，這種用法僅適用於在復合索引中排列在前的數據列組合。比如說，INDEX(A, B, C)可以當做A或(A, B)的索引來使用，但不能當做B、C或(B, C)的索引來使用。

6. 索引的長度

在為CHAR和VARCHAR類型的數據列定義索引時，可以把索引的長度限制為一個給定的字符個數(這個數字必須小於這個字段所允許的最大字符個數)。這麼做的好處是可以生成一個尺寸比較小、檢索速度卻比較快的索引文件。在絕大多數應用裡，數據庫中的字符串數據大都以各種各樣的名字為主，把索引的長度設置為10~15個字符已經足以把搜索范圍縮小到很少的幾條數據記錄了。

在為BLOB和TEXT類型的數據列創建索引時，必須對索引的長度做出限制；MySQL所允許的最大索引長度是255個字符。

全文索引

文本字段上的普通索引只能加快對出現在字段內容最前面的字符串(也就是字段內容開頭的字符)進行檢索操作。如果字段裡存放的是由幾個、甚至是多個單詞構成的較大段文字，普通索引就沒什麼作用了。這種檢索往往以LIKE %word%的形式出現，這對MySQL來說很復雜，如果需要處理的數據量很大，響應時間就會很長。

這類場合正是全文索引(full-text index)可以大顯身手的地方。在生成這種類型的索引時，MySQL將把在文本中出現的所有單詞創建為一份清單，查詢操作將根據這份清單去檢索有關的數據記錄。全文索引即可以隨數據表一同創建，也可以等日後有必要時再使用下面這條命令添加：ALTER TABLE tablename ADD FULLTEXT(column1, column2) 有了全文索引，就可以用SELECT查詢命令去檢索那些包含著一個或多個給定單詞的數據記錄了。

創建索引

MySQL創建索引的語法如下：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON table_name (index_col_name,...)

其中對應的語法變量信息如下：

[UNIQUE|FULLTEXT|SPATIAL]
    中括號中的這三個關鍵字表示創建的索引類型，它們分別表示唯一索引、全文索引、空間索引三種不同的索引類型。如果我們不指定任何關鍵字，則默認為普通索引。
index_name
    index_name表示索引的名稱，由用戶自行定義，以便於以後對該索引進行修改等管理操作。
index_type
    index_type表示索引的具體實現方式，在MySQL中，有兩種不同形式的索引——BTREE索引和HASH索引。在存儲引擎為MyISAM和InnoDB的表中只能使用BTREE，其默認值就是BTREE；在存儲引擎為MEMORY或者HEAP的表中可以使用HASH和BTREE兩種類型的索引，其默認值為HASH。
index_col_name
    index_col_name表示需要創建索引的字段名稱，我們還可以針對多個字段創建復合索引，只需要在多個字段名稱之間以英文逗號隔開即可。

    此外，對於CHAR或VARCHAR類型的字段，我們還可以只使用字段內容前面的一部分來創建索引，只需要在對應的字段名稱後面加上形如(length)的指令即可，表示只需要使用字段內容前面的length個字符來創建索引。在這裡，我們以User表的username字段(類型為VARCHAR(50))為例，使用username字段的6個字符前綴來創建索引。

    CREATE INDEX idx_user_username ON user (username(6));

    由於多數字段的前6個字符通常不同，所以此索引不會比使用字段的全部內容創建的索引速度慢很多。另外，使用字段的一部分創建索引可以使索引文件大大減小，從而節省了大量的磁盤空間，有可能提高INSERT操作的速度。

    在MySQL中，前綴長度最大值為255字節。對於存儲引擎為MyISAM或InnoDB的數據表，前綴最長為1000字節。

    必須注意的是，在MySQL中，對於TEXT和BLOB這種大數據類型的字段，必須給出前綴長度(length)才能成功創建索引。

備注1：上述創建索引的語法還具有以下變體：

 代碼如下 復制代碼

ALTER TABLE table_name
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name,...) [USING index_type]

備注2：
在MySQL中，只有當數據表的存儲引擎為MyISAM, InnoDB或BDB類型時，你才能向具有NULL值的列或者數據類型為TEXT或BLOB的列添加索引。
刪除索引

在MySQL中刪除索引的方法非常簡單，其完整語法如下：

 代碼如下 復制代碼

--刪除指定表中指定名稱的索引
ALTER TABLE table_name
DROP INDEX index_name;

在這裡，我們編寫SQL語句將上面創建索引的例子中的索引idx_user_username刪除掉，代碼詳情如下：

 代碼如下 復制代碼

--刪除名稱為idx_user_username的索引
ALTER TABLE user
DROP INDEX idx_user_username;

修改索引

在MySQL中並沒有提供修改索引的直接指令，一般情況下，我們需要先刪除掉原索引，再根據需要創建一個同名的索引，從而變相地實現修改索引操作。

 代碼如下 復制代碼

--先刪除
ALTER TABLE user
DROP INDEX idx_user_username;

--再以修改後的內容創建同名索引
CREATE INDEX idx_user_username ON user (username(8));

查看索引

在MySQL中，要查看某個數據庫表中的索引也非常簡單，只需要使用以下兩個命令中的任意一種即可。

 代碼如下 復制代碼

--如果查看索引前，沒有使用user db_name等命令指定具體的數據庫，則必須加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]

--如果查看索引前，沒有使用user db_name等命令指定具體的數據庫，則必須加上db_name.前綴
SHOW INDEX FROM [db_name.]table_name