关于“索引”的学习心得

索引的分类

1.普通索引(INDEX)

这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。

2.唯一性索引(UNIQUE)

这种索引和普通索引基本相同。唯一区别就是索引列队所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。

3.主键(PRIMARY KEY)

主键是一种唯一性索引，不同之处在于每个表只能有一个主键索引，它必须指定为“PRIMARY KEY”。一般在创建表的时候指定主键，也可以通过修改表的方式加入主键。

4.全文索引(FULLTEXT)

MySQL支持全文检索和全文索引。全文索引的索引类型为 FULLTEXT,只能在varchar或text类型的列上创建，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度，但只能在MyISAM表中创建全文索引。

创建索引

一、使用 CREATE INDEX 语句

使用CREATE INDEX语句可以在一个已有表上创建索引，一个表可以创建多个索引。语法格式如下：

CREATE [UNIQUE | FULLTEXT] INDEX 索引名
ON 表名 (列名 [(长度)] [ASC | DESC]...)

语法说明：

索引名：索引的名称，索引在一个表中的名称必须是唯一的。

列名：表示创建索引的列名。长度表示使用列的前面多少个字符创建索引。

例子：

1.1 根据book表，“书名”列上的前6个字符建立一个升序索引name_book。

CREATE INDEX name_book
    ON book(书名(6)  ASC);

一个索引的定义中可以包含多个列，中间用逗号隔开，但是它们要属于同一个表。这样的索引叫做复合索引。

1.2 在sell表的“用户表”列和“图书编号”列上建立一个复合索引user_bh_sell。

CREATE INDEX user_bh_sell
    
    ON sell(用户号,图书编号);

二、使用 ALTER TABLE 语句

对于已有的表，如果需要添加索引，也可以使用ALTER TABLE 语句向表中添加索引。
语法格式如下。

 ALTER TABLE 表名
 ADD INDEX [索引名] (列名,...)	              /*添加普通索引*/	
| ADD PRIMARY KEY [索引方式] (列名,...)	     /*添加主键*/	
| ADD UNIQUE [索引名] (列名,…)	            /*添加唯一性索引*/	
| ADD FULLTEXT [索引名] (列名,…)	            /*添加全文索引*/	

索引方式：语法格式为USING {BTREE | HASH},指定索引类型

语法说明：

索引方式：语法格式为 USING {BTREE | HASH},指定索引类型。

例子：

1.3在book表的“书名”列上创建一个普通索引。

ALTER TABLE book
 
    ADD INDEX (书名)；

使用ALTER TABLR语句可以同时添加多个索引。

1.4假设book表中主键未设定，为book表创建以“图书编号”为主键的索引，“出版社”“出版时间”为复合索引，以加快表的检索速度。

ALTER TABLE book

    ADD PRIMARY KEY (图书编号)，

        ADD INDEX (出版社，出版时间);

这个例子中，既包括PRIMARYKEY，也包括复合索引，说明MySQL可以同时创建多个索引。需要注意，使用PRIMARY KEY的列，必须是一个具有NOT NULL 属性的列。
如果想要查看表中创建的索引的情况，可以使用SHOW INDEX FROM表名语句,示例如下。
 

SHOW INDEX FROM book;

三、在创建表时创建索引

在前面两种情况下，索引都是在表创建之后创建的。索引也可以在创建表时一起创建，在创建表的CREATE TABLE语句中可以包含索引的定义。
语法格式如下。

CREATE TABLE表名(列名，… | [索引项])

其中，索引项语法格式如下：

PRIMARY KEY (列名,…)	                   /*主键索引*/	
| {INDEX|KEY} [索引名] (列名,…)	          /*普通索引*/	
| UNIQUE [INDEX] | [索引名] (列名,…)	     /*唯一性索引*/	
| [FULLTEXT] [INDEX] [索引名] (列名,…)	/*全文索引*/

KEY通常是INDEX的同义词。

例子：
1.5创建sell_copy表，设置“用户号”“图书编号”为联合主键，并在“订购册数”列上创建索引。

CREATE TABLE sell_copy (

    用户号	char(18) NOT NULL,	

    图书编号	char(20) NOT NULL,	

    订购册数	int(5),	

    订购时间	datetime,	

    PRIMARY KEY(用户号，图书编号)，
 
    INDEX(订购册数)
);

四、删除索引    

1.使用 DROP INDEX语句删除索引

语法格式如下：

DROP INDEX 索引名 ON 表名

这个语句的语法非常简单，索引名为要删除的索引的名称，表名为索引所在的表的名称。

例子：
1.6删除book表上索引名为“书名”的索引。

DROP INDEX 书名 ON book;

2.使用 ALTER TABLE 语句删除索引

语法格式如下：

ALTER [IGNORE] TABLE 表名
| DROP PRIMARY KEY	/*删除主键*/	
| DROP INDEX 索引名	/*删除索引*/	

其中，使用DROP INDEX子句可以删除各种类型的索引。使用DROP PRIMARY KEY子句时不需要提供索引名称，因为一个表中只有一个主键。

例子：

1.7删除book 表上的主键和name_book索弓引。

ALTER TABLE book

    DROP PRIMARY KEY,

    DROP INDEX name_book;

如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索子的所都被删除，则整个索引将被删除。