MySql中索引的建立和删除

索引的建立和删除

1.MySql数据库中创建索引

在mysql中创建表的时候可以直接创建索引

CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC])
)
示例：
CREATE TABLE index1 (id INT ,
name VARCHAR(20) ,
sex BOOLEAN ,
INDEX ( id) //声明索引
);

• UNIQUE：可选。表示索引为唯一性索引。
• FULLTEXT；可选。表示索引为全文索引。
• SPATIAL：可选。表示索引为空间索引。
• INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
• 索引名：可选。给创建的索引取一个新名称。
• 字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
• 长度：可选。指索引的长度，必须是字符串类型才可以使用。
• ASC：可选。表示升序排列。
• DESC：可选。表示降序排列。

查看索引是否建立：

show create table;

查看索引是否使用：

EXPLAIN SELECT * FROM index1 where id =1 \G

1.1创建普通索引

创建普通索引不需要添加任何UNIQUE、FULLTEXT、或者SPATIAL关键字
示例：创建一个名为index1的数据库，并在表ID上建立一个普通索引

create table index1(id INT,
name VARCHAR(20),
sex BOOLEAN,
INDEX(id)
);

1.2创建唯一索引

使用UNIQUE进行约束，可创建唯一性索引

实例：创建一个名为index2的数据表，在表内的id字段上建立一个唯一性索引，并且设置id字段以升序的形式排列。

创建一个唯一性索引的SQL代码如下：
CREATE TABLE index2(id INT UNIQUE,
name VARCHAR(20),
UNIQUE INDEX index2_id(id ASC)
);

如上，在id字段上建立了名为id和index2_id的两个唯一性索引。这样做，可以提高数据的查询速度。
index2_id是为唯一性索引起的一个新名字。

如果在创建表index2时，id字段没有进行唯一约束，也可成功创建唯一索引，但可能达不到提高查询速度的目的

CREATE TABLE index2(id INT,
name VARCHAR(20),
UNIQUE INDEX index2_id(id ASC)
);

1.3创建全文索引

使用全文索引使用FULLTEXT参数，并且只能在CHAR、VARCHAR或TEXT类型的字段上个创建。
示例：

CREATE TABLE index3(id INT,
info VARCHAR(20),
FULLTEXT INDEX index3_info(info)
)ENGINE=MyISAM;

注意

• ENGINE=MyISAM表明该表多用于查询，不支持事物，需经常备份数据库保证数据完成；
• ENGINE=InnoDB表明该表支持事物操作。该类型表需要mysql5.6.19版本之后才可支持使用FULLTEXT创建全文搜索。
• 全文索引适用于大型数据库，对于小的数据库用处较小。

1.4创建单列索引

单列索引是在数据表的单个字段上创建的索引。一个表中可以创建多个单列索引。唯一性索引和普通索引等都为单列索引。
实例：创建一个名为index4的数据表，在表中的subject字段上建立名为index4_st的单列索引。

CREATE TABLE index4(id INT,
subject VARCHAR(30),
INDEX index4_st(subject(10))
);

1.5创建多列索引

创建多列索引是在表的多个字段上创建一个索引。

实例：创建一个名为index5的数据表，在表中的name和sex字段上建立名为index5_ns的多列索引。

CREATE TABLE index5(id INT,
name VARCHAR(20),
sex CHAR(4),
INDEX index5_ns(name,sex)
);

注意

• 多列索引中，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用
• 在优化查询速度时，可以考虑优化多列索引。

1.6创建空间索引

使用SPATIAL参数能够创建空间索引。创建空间索引时，表的存储引擎必须是MyISAM类型。而且，索引字段必须有非空约束。

实例：创建一个名为index6的数据表，在表中的space字段上建立名为index6_sp的空间索引。

CREATE TABLE index6(id INT,
space GEOMETRY NOT NULL,
SPATIAL INDEX index6_sp(space)
)ENGINE=MyISAM;

注意

• space字段是非空的，而且数据类型是GEOMETRY类型。这个类型是空间数据类型。
• 空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGON类型等。这些空间数据类型平时很少用到

2 添加索引

1. 添加PRIMARY KEY（主键索引）

mysql>ALTER TABLE table_name ADD PRIMARY KEY ( column )

2. 添加UNIQUE(唯一索引)

mysql>ALTER TABLE table_name ADD UNIQUE ( column )

3. 添加INDEX(普通索引)

mysql>ALTER TABLE table_name ADD INDEX index_name ( column )

4. 添加FULLTEXT(全文索引)

mysql>ALTER TABLE table_name ADD FULLTEXT ( column)

5. 添加多列索引

mysql>ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

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索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。