学习MySQL数据库中表的索引维护(新增和删除)
索引的好处：
提高查询的效率
可限定特定的资料（如唯一）
索引的不足：
如果过多的索引，会影响Insert，Update的速率，因为资料插入和更新同时也会维护索引。
消耗更多的存储空间
索引按列可分为，单列索引和组合索引。单列只包含表中的一列，组合索引可包含当前表中多列栏位。
创建普通索引
CREATE INDEX <index_name> ON <tab_name>(<col_name>(length));
如果列的类型是CHAR，VARCHAR可以不指定length的值。
如果列的类型是BLOB，TEXT则需指定length的值
创建表时建立索引
CREATE TABLE <tab_name>(
<col_name> TYPE,....
[INDEX_TYPE] <index_name>(<col_name>(length))
engine=InnoDB default charset=utf8;
mysql> create table index_tab03( -> id int, -> col01 varchar(20), -> index index_01(col01) -> )engine=InnoDB default charset=utf8;Query OK, 0 rows affected (0.01 sec) mysql> desc index_tab03;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || col01 | varchar(20) | YES | MUL | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec) mysql> show index from index_tab03\G*************************** 1. row *************************** Table: index_tab03 Non_unique: 1 Key_name: index_01Seq_in_index: 1Column_name: col01 Collation: ACardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment:1 row in set (0.00 sec) m
ALTER语句维护索引
ALTER TABLE <tab_name> ADD PRIMARY KEY(<column_list>);#增加一个主键，唯一和非空
ALTER TABLE <tab_name> ADD UNIQUE <index_name>(<column_list>);#增加一个唯一索引，列中可以为NULL并且可以有多次
ALTER TABLE <tab_name> ADD INDEX <index_name>(<column_list>);#增加一个普通索引
ALTER TABLE <tab_name> ADD FULLTEXT <index_name>(<column_list>);增加一个全文索引，注意只有MyISAM引擎支持
mysql> alter table index_tab01 add primary key(id,col01);Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table index_tab01 add index indx_01(col01);Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table index_tab01 add fulltext indx_02(col01,col02(300)); ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes mysql> alter table index_tab01 engine=MyISAM; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table index_tab01 add fulltext index_02(col01,col02(30)); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from index_tab01\G *************************** 1. row *************************** Table: index_tab01 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: index_tab01 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 2 Column_name: col01 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: index_tab01 Non_unique: 0 Key_name: index_un Seq_in_index: 1 Column_name: col01 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 4. row *************************** Table: index_tab01 Non_unique: 1 Key_name: indx_01 Seq_in_index: 1 Column_name: col01 Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 5. row *************************** Table: index_tab01 Non_unique: 1 Key_name: index_02 Seq_in_index: 1 Column_name: col01 Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: FULLTEXT Comment: *************************** 6. row *************************** Table: index_tab01 Non_unique: 1 Key_name: index_02 Seq_in_index: 2 Column_name: col02 Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: FULLTEXT Comment: 6 rows in set (0.00 sec)
索引删除
DROP INDEX <index_name> ON <tab_name>;
ALTER TABLE <tab_name> DROP PRIMARY KEY;
ALTER TABLE <tab_name> DROP INDEX <index_name>;
mysql> alter table index_tab01 drop primary key;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table index_tab01 drop index index_02;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index index_01 on index_tab01;
查看索引结构
SHOW INDEX FROM <tab_name>\G