MySQL 索引的增刪改查

1 建表時創建索引

[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY  [別名] (字段名 [(長度)] [ASC|DESC] )

主鍵直接寫：

PRIMARY KEY (Id)

例如：

CREATE TABLE people (id int NOT NULL PRIMARY KEY AUTO_INCREMENT,last_name varchar(10) NOT NULL,first_name varchar(10) NOT NULL,age int NOT NULL,brithday DATE NOT NULL,create_date date NOT NULL,KEY one_index (last_name, first_name, brithday),KEY two_index (last_name, first_name)
)

• UNIQUE是可選參數，表示索引為唯一性索引；
• FULLTEXT是可選參數，表示索引為全文索引；
• SPATIAL也是可選參數，表示索引為空間索引；
• INDEX和KEY參數用來指定字段為索引，選擇其中一個就可以了，作用一樣；
• 別名是可選參數，用來給創建的索引取的新名稱；
• 字段名參數指定索引對應的字段的名稱，該字段必須為前面定義好的字段；
• 長度是可選參數，其指索引的長度，必須是字符串類型才可以使用；
• ASC和DESC都是可選參數，"ASC"參數表示升序排列，"DESC"參數表示降序排列。

2 創建索引

alter table 方式

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

• table_name是要增加索引的表名
• column_list指出對哪些列進行索引，多列時各列之間用逗號分隔
• index_name索引名可選，缺省時，MySQL將根據第一個索引列賦一個名稱。
• PRIMARY KEY索引僅是一個具有名稱 PRIMARY 的UNIQUE索引。這表示一個表只能包含一個PRIMARY KEY，因為一個表中不可能具有兩個同名的索引。

另外，ALTER TABLE允許在單個語句中更改多個表，因此可以在同時創建多個索引。

create index 方式

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

• table_name、index_name和column_list與ALTER TABLE語句含義相同
• 索引名不可選

另外，不能用CREATE INDEX語句創建PRIMARY KEY索引。

3 刪除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

• 前兩條語句是等價的，刪除掉table_name中的索引index_name
• 第3條語句只在刪除PRIMARY KEY索引時使用，因為一個表只可能有一個PRIMARY KEY索引，因此不需要指定索引名。如果沒有創建PRIMARY KEY索引，但表具有一個或多個UNIQUE索引，則MySQL將刪除第一個UNIQUE索引。

4 查看索引

mysql> show index from tblname;
mysql> show keys from tblname;

字段	含義
Table	表的名稱
Non_unique	如果索引不能包括重復詞，則為0。如果可以，則為1
Key_name	索引的名稱
Seq_in_index	索引中的列序列號，從1開始
Column_name	列名稱
Collation	列以什么方式存儲在索引中。在MySQL中，有值‘A’（升序）或NULL（無分類）
Cardinality	索引中唯一值的數目的估計值。通過運行ANALYZE TABLE或myisamchk -a可以更新。基數根據被存儲為整數的統計數據來計數，所以即使對于小型表，該值也沒有必要是精確的。基數越大，當進行聯合時，MySQL使用該索引的機會就越大
Sub_part	如果列只是被部分地編入索引，則為被編入索引的字符的數目。如果整列被編入索引，則為NULL
Packed	指示關鍵字如何被壓縮。如果沒有被壓縮，則為NULL
Null	如果列含有NULL，則含有YES。如果沒有，則該列含有NO
Index_type	用過的索引方法（BTREE, FULLTEXT, HASH, RTREE）